Insert data into oracle table with loop

oracle

How to use sql to insert line detail data into header with the output like below. 

line detail
MO_ID  SEQ_NO  QTY   LEVEL
121M   2        3
121M   3        9
125M   2        6
125M   3        7
125M   4        1

Header 
MO_ID  LEVEL SEQ_NO   QTY SERIAL_NO
121M    T      1           101
121M    T      1           102
125M    T      1           201
125M    T      1           202
125M    T      1           203

Insert line detail into header so output will be:

MO_ID  LEVEL SEQ_NO   QTY SERIAL_NO   Counter Seq
121M    T      1           101         001     1
121M           2       3               001     2
121M           3       9               001     3
121M    T      1           102         002     1
121M           2       3               002     2
121M           3       9               002     3
125M    T      1           201         003     1
125M           2       6               003     2
125M           3       7               003     3
125M           4       1               003     4
125M    T      1           202         004     1
125M           2       6               004     2
125M           3       7               004     3 
125M           4       1               004     4

… and so on until the last serial is reached in header

Best Answer

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
Related Question