Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Requirements
    (Order_Number int, Requirement_ID varchar2(6))
INSERT ALL 
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (300, 'AA-300')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (300, 'AA-516')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (300, 'AA-468')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (300, 'AA-744')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (500, 'AA-100')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (500, 'AA-823')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (500, 'AA-239')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (130, 'AA-785')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (130, 'AA-171')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (200, 'AA-171')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (200, 'AA-337')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (200, 'AA-533')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (100, 'AA-100')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (493, 'AA-400')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (493, 'AA-401')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (630, 'AA-010')
    INTO Requirements (Order_Number, Requirement_ID)
         VALUES (552, 'AA-112')



SELECT * FROM dual
;
17 rows affected

CREATE TABLE Lookup
    (Requirement_ID varchar2(6), Replenishment_ID varchar2(5))
;
INSERT ALL 
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO601')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO111')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO435')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO608')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-516', 'RO734')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-516', 'RO245')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-516', 'RO754')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-468', 'RO120')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-468', 'RO96')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO576')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO592')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO104')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO169')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO567')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO90')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO202')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO764')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO434')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO752')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO204')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-239', 'RO367')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-239', 'RO732')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO573')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO139')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO252')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO561')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO775')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-171', 'RO92')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-171', 'RO532')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-337', 'RO245')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-337', 'RO367')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-533', 'RO216')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', NULL)
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO438')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-400', 'RO4')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-401', NULL)

SELECT * FROM dual;
36 rows affected

CREATE TABLE Replenishments
    (Replenishment_ID varchar2(5), Replenishment_Type varchar2(6), Replenishment_Detail int, Replenishment_Date date)
;
INSERT ALL 
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO601', 'Bought', 963, '15-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO111', 'Make', 251, '23-Oct-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO435', 'Make', 837, '4-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO608', 'Make', 850, '27-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO734', 'Make', 415, '5-May-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO245', 'Bought', 130, '6-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO754', 'Bought', 874, '9-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO120', 'Make', 333, '28-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO96', 'Bought', 279, '11-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO576', 'Make', 452, '9-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO592', 'Bought', 967, '16-Jan-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO104', 'Make', 232, '30-Jan-19')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO169', 'Make', 804, '2-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO567', 'Make', 725, '22-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO90', 'Bought', 240, '14-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO202', 'Bought', 185, '26-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO764', 'Bought', 629, '15-Oct-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO434', 'Make', 314, '27-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO752', 'Bought', 504, '25-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO204', 'Make', 847, '9-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO367', 'Bought', 652, '14-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO732', 'Bought', 561, '3-Oct-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO573', 'Make', 616, '1-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO139', 'Make', 698, '16-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO252', 'Make', 190, '2-Aug-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO561', 'Make', 453, '13-May-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO775', 'Make', 974, '7-Aug-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO92', 'Bought', 493, '1-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO532', 'Make', 727, '17-May-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO29', 'Make', 402, '1-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO725', 'Make', 892, '9-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO216', 'Bought', 637, '1-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO438', 'Make', 125, '19-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO4', 'Bought', 591, '17-Apr-18')

SELECT * FROM dual;
34 rows affected

CREATE TABLE Orders
    (Order_Number int)
;
INSERT ALL 
    INTO Orders (Order_Number)
         VALUES (300)
    INTO Orders (Order_Number)
         VALUES (500)
    INTO Orders (Order_Number)
         VALUES (130)
    INTO Orders (Order_Number)
         VALUES (200)
    INTO Orders (Order_Number)
         VALUES (100)
    INTO Orders (Order_Number)
         VALUES (493)
    INTO Orders (Order_Number)
         VALUES (986)
    INTO Orders (Order_Number)
         VALUES (221)
    INTO Orders (Order_Number)
         VALUES (529)
    INTO Orders (Order_Number)
         VALUES (853)
    INTO Orders (Order_Number)
         VALUES (674)
    INTO Orders (Order_Number)
         VALUES (491)
    INTO Orders (Order_Number)
         VALUES (219)
    INTO Orders (Order_Number)
         VALUES (799)
    INTO Orders (Order_Number)
         VALUES (236)
    INTO Orders (Order_Number)
         VALUES (276)
    INTO Orders (Order_Number)
         VALUES (230)
    INTO Orders (Order_Number)
         VALUES (513)
    INTO Orders (Order_Number)
         VALUES (963)
    INTO Orders (Order_Number)
         VALUES (251)
    INTO Orders (Order_Number)
         VALUES (837)
    INTO Orders (Order_Number)
         VALUES (850)
    INTO Orders (Order_Number)
         VALUES (415)
    INTO Orders (Order_Number)
         VALUES (874)
    INTO Orders (Order_Number)
         VALUES (333)
    INTO Orders (Order_Number)
         VALUES (279)
    INTO Orders (Order_Number)
         VALUES (452)
    INTO Orders (Order_Number)
         VALUES (967)
    INTO Orders (Order_Number)
         VALUES (232)
    INTO Orders (Order_Number)
         VALUES (804)
    INTO Orders (Order_Number)
         VALUES (725)
    INTO Orders (Order_Number)
         VALUES (240)
    INTO Orders (Order_Number)
         VALUES (185)
    INTO Orders (Order_Number)
         VALUES (629)
    INTO Orders (Order_Number)
         VALUES (314)
    INTO Orders (Order_Number)
         VALUES (504)
    INTO Orders (Order_Number)
         VALUES (847)
    INTO Orders (Order_Number)
         VALUES (652)
    INTO Orders (Order_Number)
         VALUES (561)
    INTO Orders (Order_Number)
         VALUES (616)
    INTO Orders (Order_Number)
         VALUES (698)
    INTO Orders (Order_Number)
         VALUES (190)
    INTO Orders (Order_Number)
         VALUES (453)
    INTO Orders (Order_Number)
         VALUES (974)
    INTO Orders (Order_Number)
         VALUES (727)
    INTO Orders (Order_Number)
         VALUES (402)
    INTO Orders (Order_Number)
         VALUES (892)
    INTO Orders (Order_Number)
         VALUES (637)
    INTO Orders (Order_Number)
         VALUES (591)
    INTO Orders (Order_Number)
         VALUES (125)
SELECT * FROM dual
;
50 rows affected

SELECT
    Orders.Order_Number,
    Requirements.Requirement_ID,
    Replenishments.Replenishment_ID,
    Replenishments.Replenishment_Type,
    Replenishments.Replenishment_Detail,
    Replenishments.Replenishment_Date

  FROM
    Orders
      LEFT JOIN Requirements ON Orders.Order_Number = Requirements.Order_Number
      LEFT JOIN Lookup ON Requirements.Requirement_ID = Lookup.Requirement_ID
      LEFT JOIN Replenishments ON Lookup.Replenishment_ID = Replenishments.Replenishment_ID


ORDER_NUMBER
REQUIREMENT_ID
REPLENISHMENT_ID
REPLENISHMENT_TYPE
REPLENISHMENT_DETAIL
REPLENISHMENT_DATE


300
AA-300
RO601
Bought
963
15-JUL-18

300
AA-300
RO111
Make
251
23-OCT-18

300
AA-300
RO435
Make
837
04-MAR-18

300
AA-300
RO608
Make
850
27-APR-18

300
AA-516
RO734
Make
415
05-MAY-18

200
AA-337
RO245
Bought
130
06-FEB-18

300
AA-516
RO245
Bought
130
06-FEB-18

300
AA-516
RO754
Bought
874
09-JUN-18

300
AA-468
RO120
Make
333
28-JUL-18

300
AA-468
RO96
Bought
279
11-JUN-18

300
AA-744
RO576
Make
452
09-JUN-18

300
AA-744
RO592
Bought
967
16-JAN-18

300
AA-744
RO104
Make
232
30-JAN-19

300
AA-744
RO169
Make
804
02-FEB-18

500
AA-100
RO567
Make
725
22-MAR-18

100
AA-100
RO567
Make
725
22-MAR-18

500
AA-100
RO90
Bought
240
14-MAR-18

100
AA-100
RO90
Bought
240
14-MAR-18

500
AA-100
RO202
Bought
185
26-FEB-18

100
AA-100
RO202
Bought
185
26-FEB-18

500
AA-823
RO764
Bought
629
15-OCT-18

500
AA-823
RO434
Make
314
27-JUN-18

500
AA-823
RO752
Bought
504
25-APR-18

500
AA-823
RO204
Make
847
09-JUL-18

200
AA-337
RO367
Bought
652
14-FEB-18

500
AA-239
RO367
Bought
652
14-FEB-18

500
AA-239
RO732
Bought
561
03-OCT-18

130
AA-785
RO573
Make
616
01-APR-18

130
AA-785
RO139
Make
698
16-JUL-18

130
AA-785
RO252
Make
190
02-AUG-18

130
AA-785
RO561
Make
453
13-MAY-18

130
AA-785
RO775
Make
974
07-AUG-18

130
AA-171
RO92
Bought
493
01-APR-18

200
AA-171
RO92
Bought
493
01-APR-18

130
AA-171
RO532
Make
727
17-MAY-18

200
AA-171
RO532
Make
727
17-MAY-18

200
AA-533
RO216
Bought
637
01-JUN-18

500
AA-100
RO438
Make
125
19-MAR-18

100
AA-100
RO438
Make
125
19-MAR-18

493
AA-400
RO4
Bought
591
17-APR-18

725
null
null
null
null
null

232
null
null
null
null
null

986
null
null
null
null
null

251
null
null
null
null
null

637
null
null
null
null
null

185
null
null
null
null
null

221
null
null
null
null
null

974
null
null
null
null
null

850
null
null
null
null
null

853
null
null
null
null
null

892
null
null
null
null
null

314
null
null
null
null
null

504
null
null
null
null
null

799
null
null
null
null
null

652
null
null
null
null
null

276
null
null
null
null
null

963
null
null
null
null
null

236
null
null
null
null
null

125
null
null
null
null
null

727
null
null
null
null
null

698
null
null
null
null
null

415
null
null
null
null
null

240
null
null
null
null
null

616
null
null
null
null
null

402
null
null
null
null
null

674
null
null
null
null
null

219
null
null
null
null
null

513
null
null
null
null
null

874
null
null
null
null
null

333
null
null
null
null
null

591
null
null
null
null
null

837
null
null
null
null
null

529
null
null
null
null
null

453
null
null
null
null
null

847
null
null
null
null
null

491
null
null
null
null
null

967
null
null
null
null
null

190
null
null
null
null
null

230
null
null
null
null
null

804
null
null
null
null
null

629
null
null
null
null
null

279
null
null
null
null
null

452
null
null
null
null
null

561
null
null
null
null
null

493
AA-401
null
null
null
null

500
AA-100
null
null
null
null

100
AA-100
null
null
null
null


with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;


ROOT_ORDER
ORDER_NUMBER
REQUIREMENT_ID
REPLENISHMENT_ID
REPLENISHMENT_TYPE
REPLENISHMENT_DETAIL
REPLENISHMENT_DATE


100
100
AA-100
RO202
Bought
185
26-FEB-18

100
100
AA-100
RO90
Bought
240
14-MAR-18

100
100
AA-100
RO567
Make
725
22-MAR-18

100
100
AA-100
null
null
null
null

100
100
AA-100
RO438
Make
125
19-MAR-18

130
130
AA-171
RO92
Bought
493
01-APR-18

130
130
AA-171
RO532
Make
727
17-MAY-18

130
130
AA-785
RO775
Make
974
07-AUG-18

130
130
AA-785
RO561
Make
453
13-MAY-18

130
130
AA-785
RO252
Make
190
02-AUG-18

130
130
AA-785
RO573
Make
616
01-APR-18

130
130
AA-785
RO139
Make
698
16-JUL-18

130
493
AA-400
RO4
Bought
591
17-APR-18

130
493
AA-401
null
null
null
null

200
130
AA-171
RO92
Bought
493
01-APR-18

200
130
AA-171
RO532
Make
727
17-MAY-18

200
130
AA-785
RO252
Make
190
02-AUG-18

200
130
AA-785
RO573
Make
616
01-APR-18

200
130
AA-785
RO139
Make
698
16-JUL-18

200
130
AA-785
RO561
Make
453
13-MAY-18

200
130
AA-785
RO775
Make
974
07-AUG-18

200
200
AA-171
RO532
Make
727
17-MAY-18

200
200
AA-171
RO92
Bought
493
01-APR-18

200
200
AA-337
RO367
Bought
652
14-FEB-18

200
200
AA-337
RO245
Bought
130
06-FEB-18

200
200
AA-533
RO216
Bought
637
01-JUN-18

200
493
AA-400
RO4
Bought
591
17-APR-18

200
493
AA-400
RO4
Bought
591
17-APR-18

200
493
AA-401
null
null
null
null

200
493
AA-401
null
null
null
null

300
130
AA-171
RO532
Make
727
17-MAY-18

300
130
AA-171
RO92
Bought
493
01-APR-18

300
130
AA-785
RO573
Make
616
01-APR-18

300
130
AA-785
RO561
Make
453
13-MAY-18

300
130
AA-785
RO775
Make
974
07-AUG-18

300
130
AA-785
RO139
Make
698
16-JUL-18

300
130
AA-785
RO252
Make
190
02-AUG-18

300
300
AA-300
RO601
Bought
963
15-JUL-18

300
300
AA-300
RO111
Make
251
23-OCT-18

300
300
AA-300
RO435
Make
837
04-MAR-18

300
300
AA-300
RO608
Make
850
27-APR-18

300
300
AA-468
RO96
Bought
279
11-JUN-18

300
300
AA-468
RO120
Make
333
28-JUL-18

300
300
AA-516
RO754
Bought
874
09-JUN-18

300
300
AA-516
RO245
Bought
130
06-FEB-18

300
300
AA-516
RO734
Make
415
05-MAY-18

300
300
AA-744
RO169
Make
804
02-FEB-18

300
300
AA-744
RO576
Make
452
09-JUN-18

300
300
AA-744
RO592
Bought
967
16-JAN-18

300
300
AA-744
RO104
Make
232
30-JAN-19

300
493
AA-400
RO4
Bought
591
17-APR-18

300
493
AA-401
null
null
null
null

493
493
AA-400
RO4
Bought
591
17-APR-18

493
493
AA-401
null
null
null
null

500
500
AA-100
RO438
Make
125
19-MAR-18

500
500
AA-100
RO567
Make
725
22-MAR-18

500
500
AA-100
null
null
null
null

500
500
AA-100
RO202
Bought
185
26-FEB-18

500
500
AA-100
RO90
Bought
240
14-MAR-18

500
500
AA-239
RO367
Bought
652
14-FEB-18

500
500
AA-239
RO732
Bought
561
03-OCT-18

500
500
AA-823
RO752
Bought
504
25-APR-18

500
500
AA-823
RO434
Make
314
27-JUN-18

500
500
AA-823
RO764
Bought
629
15-OCT-18

500
500
AA-823
RO204
Make
847
09-JUL-18


with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  where to_char(orders.order_number) like '2%'
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;


ROOT_ORDER
ORDER_NUMBER
REQUIREMENT_ID
REPLENISHMENT_ID
REPLENISHMENT_TYPE
REPLENISHMENT_DETAIL
REPLENISHMENT_DATE


200
130
AA-171
RO92
Bought
493
01-APR-18

200
130
AA-171
RO532
Make
727
17-MAY-18

200
130
AA-785
RO561
Make
453
13-MAY-18

200
130
AA-785
RO573
Make
616
01-APR-18

200
130
AA-785
RO252
Make
190
02-AUG-18

200
130
AA-785
RO139
Make
698
16-JUL-18

200
130
AA-785
RO775
Make
974
07-AUG-18

200
200
AA-171
RO92
Bought
493
01-APR-18

200
200
AA-171
RO532
Make
727
17-MAY-18

200
200
AA-337
RO245
Bought
130
06-FEB-18

200
200
AA-337
RO367
Bought
652
14-FEB-18

200
200
AA-533
RO216
Bought
637
01-JUN-18

200
493
AA-400
RO4
Bought
591
17-APR-18

200
493
AA-400
RO4
Bought
591
17-APR-18

200
493
AA-401
null
null
null
null

200
493
AA-401
null
null
null
null


with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  where orders.order_number = 300
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;


ROOT_ORDER
ORDER_NUMBER
REQUIREMENT_ID
REPLENISHMENT_ID
REPLENISHMENT_TYPE
REPLENISHMENT_DETAIL
REPLENISHMENT_DATE


300
130
AA-171
RO532
Make
727
17-MAY-18

300
130
AA-171
RO92
Bought
493
01-APR-18

300
130
AA-785
RO561
Make
453
13-MAY-18

300
130
AA-785
RO252
Make
190
02-AUG-18

300
130
AA-785
RO775
Make
974
07-AUG-18

300
130
AA-785
RO139
Make
698
16-JUL-18

300
130
AA-785
RO573
Make
616
01-APR-18

300
300
AA-300
RO111
Make
251
23-OCT-18

300
300
AA-300
RO435
Make
837
04-MAR-18

300
300
AA-300
RO608
Make
850
27-APR-18

300
300
AA-300
RO601
Bought
963
15-JUL-18

300
300
AA-468
RO96
Bought
279
11-JUN-18

300
300
AA-468
RO120
Make
333
28-JUL-18

300
300
AA-516
RO734
Make
415
05-MAY-18

300
300
AA-516
RO754
Bought
874
09-JUN-18

300
300
AA-516
RO245
Bought
130
06-FEB-18

300
300
AA-744
RO169
Make
804
02-FEB-18

300
300
AA-744
RO104
Make
232
30-JAN-19

300
300
AA-744
RO576
Make
452
09-JUN-18

300
300
AA-744
RO592
Bought
967
16-JAN-18

300
493
AA-400
RO4
Bought
591
17-APR-18

300
493
AA-401
null
null
null
null


with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  where not exists (
    select *
    from replenishments
    where replenishment_detail = orders.order_number
  )
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;


ROOT_ORDER
ORDER_NUMBER
REQUIREMENT_ID
REPLENISHMENT_ID
REPLENISHMENT_TYPE
REPLENISHMENT_DETAIL
REPLENISHMENT_DATE


100
100
AA-100
RO90
Bought
240
14-MAR-18

100
100
AA-100
null
null
null
null

100
100
AA-100
RO567
Make
725
22-MAR-18

100
100
AA-100
RO202
Bought
185
26-FEB-18

100
100
AA-100
RO438
Make
125
19-MAR-18

200
130
AA-171
RO532
Make
727
17-MAY-18

200
130
AA-171
RO92
Bought
493
01-APR-18

200
130
AA-785
RO139
Make
698
16-JUL-18

200
130
AA-785
RO561
Make
453
13-MAY-18

200
130
AA-785
RO775
Make
974
07-AUG-18

200
130
AA-785
RO252
Make
190
02-AUG-18

200
130
AA-785
RO573
Make
616
01-APR-18

200
200
AA-171
RO92
Bought
493
01-APR-18

200
200
AA-171
RO532
Make
727
17-MAY-18

200
200
AA-337
RO367
Bought
652
14-FEB-18

200
200
AA-337
RO245
Bought
130
06-FEB-18

200
200
AA-533
RO216
Bought
637
01-JUN-18

200
493
AA-400
RO4
Bought
591
17-APR-18

200
493
AA-400
RO4
Bought
591
17-APR-18

200
493
AA-401
null
null
null
null

200
493
AA-401
null
null
null
null

300
130
AA-171
RO92
Bought
493
01-APR-18

300
130
AA-171
RO532
Make
727
17-MAY-18

300
130
AA-785
RO561
Make
453
13-MAY-18

300
130
AA-785
RO252
Make
190
02-AUG-18

300
130
AA-785
RO139
Make
698
16-JUL-18

300
130
AA-785
RO573
Make
616
01-APR-18

300
130
AA-785
RO775
Make
974
07-AUG-18

300
300
AA-300
RO601
Bought
963
15-JUL-18

300
300
AA-300
RO111
Make
251
23-OCT-18

300
300
AA-300
RO435
Make
837
04-MAR-18

300
300
AA-300
RO608
Make
850
27-APR-18

300
300
AA-468
RO96
Bought
279
11-JUN-18

300
300
AA-468
RO120
Make
333
28-JUL-18

300
300
AA-516
RO754
Bought
874
09-JUN-18

300
300
AA-516
RO734
Make
415
05-MAY-18

300
300
AA-516
RO245
Bought
130
06-FEB-18

300
300
AA-744
RO169
Make
804
02-FEB-18

300
300
AA-744
RO592
Bought
967
16-JAN-18

300
300
AA-744
RO576
Make
452
09-JUN-18

300
300
AA-744
RO104
Make
232
30-JAN-19

300
493
AA-400
RO4
Bought
591
17-APR-18

300
493
AA-401
null
null
null
null

500
500
AA-100
RO567
Make
725
22-MAR-18

500
500
AA-100
RO90
Bought
240
14-MAR-18

500
500
AA-100
RO202
Bought
185
26-FEB-18

500
500
AA-100
RO438
Make
125
19-MAR-18

500
500
AA-100
null
null
null
null

500
500
AA-239
RO732
Bought
561
03-OCT-18

500
500
AA-239
RO367
Bought
652
14-FEB-18

500
500
AA-823
RO764
Bought
629
15-OCT-18

500
500
AA-823
RO752
Bought
504
25-APR-18

500
500
AA-823
RO204
Make
847
09-JUL-18

500
500
AA-823
RO434
Make
314
27-JUN-18
ORDER_NUMBER	REQUIREMENT_ID	REPLENISHMENT_ID	REPLENISHMENT_TYPE	REPLENISHMENT_DETAIL	REPLENISHMENT_DATE
300	AA-300	RO601	Bought	963	15-JUL-18
300	AA-300	RO111	Make	251	23-OCT-18
300	AA-300	RO435	Make	837	04-MAR-18
300	AA-300	RO608	Make	850	27-APR-18
300	AA-516	RO734	Make	415	05-MAY-18
200	AA-337	RO245	Bought	130	06-FEB-18
300	AA-516	RO245	Bought	130	06-FEB-18
300	AA-516	RO754	Bought	874	09-JUN-18
300	AA-468	RO120	Make	333	28-JUL-18
300	AA-468	RO96	Bought	279	11-JUN-18
300	AA-744	RO576	Make	452	09-JUN-18
300	AA-744	RO592	Bought	967	16-JAN-18
300	AA-744	RO104	Make	232	30-JAN-19
300	AA-744	RO169	Make	804	02-FEB-18
500	AA-100	RO567	Make	725	22-MAR-18
100	AA-100	RO567	Make	725	22-MAR-18
500	AA-100	RO90	Bought	240	14-MAR-18
100	AA-100	RO90	Bought	240	14-MAR-18
500	AA-100	RO202	Bought	185	26-FEB-18
100	AA-100	RO202	Bought	185	26-FEB-18
500	AA-823	RO764	Bought	629	15-OCT-18
500	AA-823	RO434	Make	314	27-JUN-18
500	AA-823	RO752	Bought	504	25-APR-18
500	AA-823	RO204	Make	847	09-JUL-18
200	AA-337	RO367	Bought	652	14-FEB-18
500	AA-239	RO367	Bought	652	14-FEB-18
500	AA-239	RO732	Bought	561	03-OCT-18
130	AA-785	RO573	Make	616	01-APR-18
130	AA-785	RO139	Make	698	16-JUL-18
130	AA-785	RO252	Make	190	02-AUG-18
130	AA-785	RO561	Make	453	13-MAY-18
130	AA-785	RO775	Make	974	07-AUG-18
130	AA-171	RO92	Bought	493	01-APR-18
200	AA-171	RO92	Bought	493	01-APR-18
130	AA-171	RO532	Make	727	17-MAY-18
200	AA-171	RO532	Make	727	17-MAY-18
200	AA-533	RO216	Bought	637	01-JUN-18
500	AA-100	RO438	Make	125	19-MAR-18
100	AA-100	RO438	Make	125	19-MAR-18
493	AA-400	RO4	Bought	591	17-APR-18
725	null	null	null	null	null
232	null	null	null	null	null
986	null	null	null	null	null
251	null	null	null	null	null
637	null	null	null	null	null
185	null	null	null	null	null
221	null	null	null	null	null
974	null	null	null	null	null
850	null	null	null	null	null
853	null	null	null	null	null
892	null	null	null	null	null
314	null	null	null	null	null
504	null	null	null	null	null
799	null	null	null	null	null
652	null	null	null	null	null
276	null	null	null	null	null
963	null	null	null	null	null
236	null	null	null	null	null
125	null	null	null	null	null
727	null	null	null	null	null
698	null	null	null	null	null
415	null	null	null	null	null
240	null	null	null	null	null
616	null	null	null	null	null
402	null	null	null	null	null
674	null	null	null	null	null
219	null	null	null	null	null
513	null	null	null	null	null
874	null	null	null	null	null
333	null	null	null	null	null
591	null	null	null	null	null
837	null	null	null	null	null
529	null	null	null	null	null
453	null	null	null	null	null
847	null	null	null	null	null
491	null	null	null	null	null
967	null	null	null	null	null
190	null	null	null	null	null
230	null	null	null	null	null
804	null	null	null	null	null
629	null	null	null	null	null
279	null	null	null	null	null
452	null	null	null	null	null
561	null	null	null	null	null
493	AA-401	null	null	null	null
500	AA-100	null	null	null	null
100	AA-100	null	null	null	null