SQL Query to Update the Start and End Date in a table from a different column where the dates do not match

Part       | Quantity|Mapping                                                                                                                                                                                  |First_Date|Las_Date
6684MC6906 |    1    |-> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-12-SEP-17-20-SEP-21 -> FSD PHANTOM SUBASSY-07-JUN-17-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22|          |
6684MC6906 |    1    |-> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-20-SEP-21-15-AUG-22 -> FSD PHANTOM SUBASSY-22-JUN-21-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22|          |

​x
 
Part       | Quantity|Mapping                                                                                                                                                                                  |First_Date|Las_Date6684MC6906 |    1    |-> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-12-SEP-17-20-SEP-21 -> FSD PHANTOM SUBASSY-07-JUN-17-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22|          |6684MC6906 |    1    |-> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-20-SEP-21-15-AUG-22 -> FSD PHANTOM SUBASSY-22-JUN-21-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22|          |​

I need a query which will Update the Start_Date and End_Date in this table. By Checking the Mapping column.When the value of date starts to change that should become the new start and end date.

For example :

Record_1 -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-12-SEP-17-20-SEP-21 -> FSD PHANTOM SUBASSY-07-JUN-17-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22

Record_2  -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-20-SEP-21-15-AUG-22 -> FSD PHANTOM SUBASSY-22-JUN-21-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22.

 
Record_1 -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-12-SEP-17-20-SEP-21 -> FSD PHANTOM SUBASSY-07-JUN-17-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22​Record_2  -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-20-SEP-21-15-AUG-22 -> FSD PHANTOM SUBASSY-22-JUN-21-20-OCT-21 -> FSD PHANTOM SUBASSY-06-APR-12-15-AUG-22.​

The above two are same till the part 25-NOV-15-15-AUG-22. So our new Start_date for 1st record should be =12-SEP-17 and End_date should be =20-SEP-21. For second record Start_date =20-SEP-21 and 15-AUG-22.

Please let me know if any clarification needed. I’ll try my best to explain.

Below are the table scripts and insert statements.

create table bom_duplicates
(
LVL NUMBER,
TOP_PART    VARCHAR2(300 BYTE),
ASSEMBLY_ITEM_ID    NUMBER,
FLAT_COMPONENT  VARCHAR2(300 BYTE),
COMPONENT_ID    NUMBER,
ORGANIZATION_ID NUMBER,
USAGE_QUANTITY  NUMBER,
PLANNING_FACTOR NUMBER,
TRUE_TOP_PART   VARCHAR2(300 BYTE),
PATH_USAGE_QUANTITY VARCHAR2(4000 BYTE),
TYPE_PATH   VARCHAR2(4000 BYTE),
PATH_START_DATE VARCHAR2(4000 BYTE),
ORGANIZATION_CODE   VARCHAR2(180 BYTE),
PARENT_MAKE_BUY_CODE    VARCHAR2(40 BYTE),
CHILD_MAKE_BUY_CODE VARCHAR2(40 BYTE),
START_DATE  DATE,
END_DATE    DATE,
ITEM_TYPE   VARCHAR2(90 BYTE),
PARENT_ITEM_TYPE    VARCHAR2(90 BYTE)
);

Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'007-9714401',17644,10007,6,100,'6684MC6906','*1*1*1*6',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'007-9714401',17644,10007,6,100,'6684MC6906','*1*1*1*6',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006590',18027,10007,14,100,'6684MC6906','*1*1*1*14',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006590',18027,10007,14,100,'6684MC6906','*1*1*1*14',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006591',18028,10007,4,100,'6684MC6906','*1*1*1*4',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006591',18028,10007,4,100,'6684MC6906','*1*1*1*4',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0030192',22158,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0030192',22158,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754353',116356,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754353',116356,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754507',116441,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754507',116441,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754508',116442,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754508',116442,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754509',116443,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754509',116443,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0761622',122703,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0761622',122703,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763050',123517,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763050',123517,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763319',123628,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');
Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763319',123628,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');

 
create table bom_duplicates(LVL NUMBER,TOP_PART    VARCHAR2(300 BYTE),ASSEMBLY_ITEM_ID    NUMBER,FLAT_COMPONENT  VARCHAR2(300 BYTE),COMPONENT_ID    NUMBER,ORGANIZATION_ID NUMBER,USAGE_QUANTITY  NUMBER,PLANNING_FACTOR NUMBER,TRUE_TOP_PART   VARCHAR2(300 BYTE),PATH_USAGE_QUANTITY VARCHAR2(4000 BYTE),TYPE_PATH   VARCHAR2(4000 BYTE),PATH_START_DATE VARCHAR2(4000 BYTE),ORGANIZATION_CODE   VARCHAR2(180 BYTE),PARENT_MAKE_BUY_CODE    VARCHAR2(40 BYTE),CHILD_MAKE_BUY_CODE VARCHAR2(40 BYTE),START_DATE  DATE,END_DATE    DATE,ITEM_TYPE   VARCHAR2(90 BYTE),PARENT_ITEM_TYPE    VARCHAR2(90 BYTE));​Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'007-9714401',17644,10007,6,100,'6684MC6906','*1*1*1*6',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'007-9714401',17644,10007,6,100,'6684MC6906','*1*1*1*6',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006590',18027,10007,14,100,'6684MC6906','*1*1*1*14',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006590',18027,10007,14,100,'6684MC6906','*1*1*1*14',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006591',18028,10007,4,100,'6684MC6906','*1*1*1*4',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0006591',18028,10007,4,100,'6684MC6906','*1*1*1*4',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0030192',22158,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'009-0030192',22158,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754353',116356,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754353',116356,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754507',116441,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754507',116441,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754508',116442,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754508',116442,10007,2,100,'6684MC6906','*1*1*1*2',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754509',116443,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0754509',116443,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0761622',122703,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0761622',122703,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763050',123517,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763050',123517,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763319',123628,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-25-NOV-15-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');Insert into BOM_DUPLICATES (LVL,TOP_PART,ASSEMBLY_ITEM_ID,FLAT_COMPONENT,COMPONENT_ID,ORGANIZATION_ID,USAGE_QUANTITY,PLANNING_FACTOR,TRUE_TOP_PART,PATH_USAGE_QUANTITY,TYPE_PATH,PATH_START_DATE,ORGANIZATION_CODE,PARENT_MAKE_BUY_CODE,CHILD_MAKE_BUY_CODE,START_DATE,END_DATE,ITEM_TYPE,PARENT_ITEM_TYPE) values (4,'445-0773300',127180,'445-0763319',123628,10007,1,100,'6684MC6906','*1*1*1*1',' -> CID -> FEATURE -> FSD PHANTOM SUBASSY -> FSD PHANTOM SUBASSY',' -> CID-29-JAN-21-15-AUG-22 -> FEATURE-27-JUN-16-15-AUG-22 -> FSD PHANTOM SUBASSY-22-NOV-18-15-AUG-22 -> FSD PHANTOM SUBASSY-13-NOV-18-15-AUG-22','BUD','Make','Buy',to_date('22-NOV-18','DD-MON-RR'),to_date('15-AUG-22','DD-MON-RR'),'FSD BUY RAW MAT','FSD PHANTOM SUBASSY');​

Answer

You can use:

MERGE INTO bom_duplicates dst
USING (
  WITH numbered_parts (rid, component_id, path_start_date, rn) AS ( 
    SELECT rowid,
           component_id,
           path_start_date,
           ROW_NUMBER() OVER (PARTITION BY component_id ORDER BY rowid)
    FROM   bom_duplicates
  ),
  matched_parts (rid1, rid2, path_start_date1, path_start_date2) AS (
    SELECT p1.rid,
           p2.rid,
           p1.path_start_date,
           p2.path_start_date
    FROM   numbered_parts p1
           INNER JOIN numbered_parts p2
           ON (p1.component_id = p2.component_id)
    WHERE  p1.rn = 1
    AND    p2.rn = 2
  ),
  paths (rid1, rid2, path_start_date1, path_start_date2, idx, path1, path2, num_paths) AS (
    SELECT rid1,
           rid2,
           path_start_date1,
           path_start_date2,
           2,
           REGEXP_SUBSTR(path_start_date1, '[^>]+', 1, 2),
           REGEXP_SUBSTR(path_start_date2, '[^>]+', 1, 2),
           GREATEST(
             REGEXP_COUNT(path_start_date1, '[^>]+'),
             REGEXP_COUNT(path_start_date2, '[^>]+')
           )
    FROM   matched_parts
  UNION ALL
    SELECT rid1,
           rid2,
           path_start_date1,
           path_start_date2,
           idx + 1,
           REGEXP_SUBSTR(path_start_date1, '[^>]+', 1, idx + 1),
           REGEXP_SUBSTR(path_start_date2, '[^>]+', 1, idx + 1),
           num_paths
    FROM   paths
    WHERE  idx < num_paths
    AND    path1 = path2
  ),
  changed_dates (rid1, rid2, start_date1, end_date1, start_date2, end_date2) AS (
    SELECT rid1,
           rid2,
           TO_DATE(
             REGEXP_SUBSTR(
               path1,
               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',
               1,
               1,
               NULL,
               1
             ),
             'DD-MON-RR'
           ) AS start_date1,
           TO_DATE(
             REGEXP_SUBSTR(
               path1,
               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',
               1,
               1,
               NULL,
               2
             ),
             'DD-MON-RR'
           ) AS end_date1,
           TO_DATE(
             REGEXP_SUBSTR(
               path2,
               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',
               1,
               1,
               NULL,
               1
             ),
             'DD-MON-RR'
           ) AS start_date2,
           TO_DATE(
             REGEXP_SUBSTR(
               path2,
               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',
               1,
               1,
               NULL,
               2
             ),
             'DD-MON-RR'
           ) AS end_date2
    FROM   paths
    WHERE  path1 <> path2
  )
  SELECT rid,
         start_date,
         end_date
  FROM   changed_dates
  UNPIVOT (
    (rid, start_date, end_date)
    FOR type IN (
      (rid1, start_date1, end_date1) AS 1,
      (rid2, start_date2, end_date2) AS 2
    )
  )
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
  UPDATE
  SET start_date = src.start_date,
      end_date  = src.end_date;

 
MERGE INTO bom_duplicates dstUSING (  WITH numbered_parts (rid, component_id, path_start_date, rn) AS (     SELECT rowid,           component_id,           path_start_date,           ROW_NUMBER() OVER (PARTITION BY component_id ORDER BY rowid)    FROM   bom_duplicates  ),  matched_parts (rid1, rid2, path_start_date1, path_start_date2) AS (    SELECT p1.rid,           p2.rid,           p1.path_start_date,           p2.path_start_date    FROM   numbered_parts p1           INNER JOIN numbered_parts p2           ON (p1.component_id = p2.component_id)    WHERE  p1.rn = 1    AND    p2.rn = 2  ),  paths (rid1, rid2, path_start_date1, path_start_date2, idx, path1, path2, num_paths) AS (    SELECT rid1,           rid2,           path_start_date1,           path_start_date2,           2,           REGEXP_SUBSTR(path_start_date1, '[^>]+', 1, 2),           REGEXP_SUBSTR(path_start_date2, '[^>]+', 1, 2),           GREATEST(             REGEXP_COUNT(path_start_date1, '[^>]+'),             REGEXP_COUNT(path_start_date2, '[^>]+')           )    FROM   matched_parts  UNION ALL    SELECT rid1,           rid2,           path_start_date1,           path_start_date2,           idx + 1,           REGEXP_SUBSTR(path_start_date1, '[^>]+', 1, idx + 1),           REGEXP_SUBSTR(path_start_date2, '[^>]+', 1, idx + 1),           num_paths    FROM   paths    WHERE  idx < num_paths    AND    path1 = path2  ),  changed_dates (rid1, rid2, start_date1, end_date1, start_date2, end_date2) AS (    SELECT rid1,           rid2,           TO_DATE(             REGEXP_SUBSTR(               path1,               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',               1,               1,               NULL,               1             ),             'DD-MON-RR'           ) AS start_date1,           TO_DATE(             REGEXP_SUBSTR(               path1,               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',               1,               1,               NULL,               2             ),             'DD-MON-RR'           ) AS end_date1,           TO_DATE(             REGEXP_SUBSTR(               path2,               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',               1,               1,               NULL,               1             ),             'DD-MON-RR'           ) AS start_date2,           TO_DATE(             REGEXP_SUBSTR(               path2,               '(d{2}-[A-Z]{3}-d{2})-(d{2}-[A-Z]{3}-d{2})',               1,               1,               NULL,               2             ),             'DD-MON-RR'           ) AS end_date2    FROM   paths    WHERE  path1 <> path2  )  SELECT rid,         start_date,         end_date  FROM   changed_dates  UNPIVOT (    (rid, start_date, end_date)    FOR type IN (      (rid1, start_date1, end_date1) AS 1,      (rid2, start_date2, end_date2) AS 2    )  )) srcON (dst.ROWID = src.rid)WHEN MATCHED THEN  UPDATE  SET start_date = src.start_date,      end_date  = src.end_date;​

Which, for your sample data, then:

SELECT component_id, start_date, end_date FROM bom_duplicates;

 
SELECT component_id, start_date, end_date FROM bom_duplicates;​

Outputs:

COMPONENT_ID START_DATE END_DATE

17644 25-NOV-15 15-AUG-22

17644 27-JUN-16 15-AUG-22

18027 25-NOV-15 15-AUG-22

18027 27-JUN-16 15-AUG-22

18028 25-NOV-15 15-AUG-22

18028 27-JUN-16 15-AUG-22

22158 25-NOV-15 15-AUG-22

22158 27-JUN-16 15-AUG-22

116356 25-NOV-15 15-AUG-22

116356 27-JUN-16 15-AUG-22

116441 25-NOV-15 15-AUG-22

116441 27-JUN-16 15-AUG-22

116442 25-NOV-15 15-AUG-22

116442 27-JUN-16 15-AUG-22

116443 25-NOV-15 15-AUG-22

116443 27-JUN-16 15-AUG-22

122703 25-NOV-15 15-AUG-22

122703 27-JUN-16 15-AUG-22

123517 25-NOV-15 15-AUG-22

123517 27-JUN-16 15-AUG-22

123628 25-NOV-15 15-AUG-22

123628 27-JUN-16 15-AUG-22

COMPONENT_ID	START_DATE	END_DATE
17644	25-NOV-15	15-AUG-22
17644	27-JUN-16	15-AUG-22
18027	25-NOV-15	15-AUG-22
18027	27-JUN-16	15-AUG-22
18028	25-NOV-15	15-AUG-22
18028	27-JUN-16	15-AUG-22
22158	25-NOV-15	15-AUG-22
22158	27-JUN-16	15-AUG-22
116356	25-NOV-15	15-AUG-22
116356	27-JUN-16	15-AUG-22
116441	25-NOV-15	15-AUG-22
116441	27-JUN-16	15-AUG-22
116442	25-NOV-15	15-AUG-22
116442	27-JUN-16	15-AUG-22
116443	25-NOV-15	15-AUG-22
116443	27-JUN-16	15-AUG-22
122703	25-NOV-15	15-AUG-22
122703	27-JUN-16	15-AUG-22
123517	25-NOV-15	15-AUG-22
123517	27-JUN-16	15-AUG-22
123628	25-NOV-15	15-AUG-22
123628	27-JUN-16	15-AUG-22

db<>fiddle here

Advertisement

Answer