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| |
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.
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');
Advertisement
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;
Which, for your sample data, then:
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
db<>fiddle here