Insert data into oracle table with loop?
This is only judging from the very little information you provide:

SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
       ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
  SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
  FROM "Header" h  
  ) h
JOIN (
  SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
  FROM "line detail"
  UNION
  SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
  FROM "Header"
  ) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO