The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* First, insert all the appropriate rows from the first bill of material,
according to display code and implemented code:
DISPLAY CODE 1 = All
2 = Current
3 = Current and future
IMPEMENTED CODE 1 = Implemented only
2 = Implemented and unimplemented
*/
INSERT INTO BOM_COMPARISON_TEMP
(SEQUENCE_ID,
ASSEMBLY_ID1,
ITEM_NUM,
COMPONENT_QUANTITY,
OPERATION_SEQ_NUM,
PLANNING_FACTOR,
EFFECTIVITY_DATE,
DISABLE_DATE,
IMPLEMENTATION_DATE,
OPTIONAL,
COMPONENT_SEQUENCE_ID1)
SELECT SEQ_ID,
COMPONENT_ITEM_ID,
ITEM_NUM,
COMPONENT_QUANTITY,
OPERATION_SEQ_NUM,
PLANNING_FACTOR,
EFFECTIVITY_DATE,
DISABLE_DATE,
IMPLEMENTATION_DATE,
OPTIONAL,
COMPONENT_SEQUENCE_ID
FROM BOM_INVENTORY_COMPONENTS C1
WHERE C1.BILL_SEQUENCE_ID = BILL_SEQ_ID1
AND NVL(ECO_FOR_PRODUCTION,2) = 2
AND ((IMPLEMENTED_CODE1 = 2)
OR
(IMPLEMENTED_CODE1 = 1
AND IMPLEMENTATION_DATE IS NOT NULL)
)
AND (DISPLAY_CODE1 = 1
OR (DISPLAY_CODE1 = 2
AND CUTOFF_DATE1 BETWEEN EFFECTIVITY_DATE AND
NVL((DISABLE_DATE-(1/86400)), CUTOFF_DATE1 + 1)) --Added for Bug 6689734
OR (DISPLAY_CODE1 = 3
AND NVL(DISABLE_DATE, CUTOFF_DATE1 + 1) > CUTOFF_DATE1) --Changed from >= to > for Bug 6689734
)
AND ((IMPLEMENTED_CODE1 = 2
AND (DISPLAY_CODE1 = 1
OR
(DISPLAY_CODE1 = 2
AND EFFECTIVITY_DATE =
(SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS MV2
WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID1
AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
AND MV2.COMPONENT_ITEM_ID = C1.COMPONENT_ITEM_ID
AND (DECODE(IMPLEMENTATION_DATE, NULL,
MV2.OLD_COMPONENT_SEQUENCE_ID,
MV2.COMPONENT_SEQUENCE_ID) =
DECODE(C1.IMPLEMENTATION_DATE, NULL,
C1.OLD_COMPONENT_SEQUENCE_ID, C1.COMPONENT_SEQUENCE_ID)
OR MV2.OPERATION_SEQ_NUM = C1.OPERATION_SEQ_NUM)
AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE1
AND NVL(DISABLE_DATE, CUTOFF_DATE1+1) > CUTOFF_DATE1
AND NOT EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS BICN
WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID1
AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
AND BICN.OLD_COMPONENT_SEQUENCE_ID = C1.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE = 3
AND BICN.DISABLE_DATE < CUTOFF_DATE1 )) --Changed from <= to < for Bug 6689734
)
OR
(DISPLAY_CODE1 = 3
AND EFFECTIVITY_DATE =
(SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS MV2
WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID1
AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
AND MV2.COMPONENT_ITEM_ID = C1.COMPONENT_ITEM_ID
AND (DECODE(IMPLEMENTATION_DATE, NULL,
MV2.OLD_COMPONENT_SEQUENCE_ID,
MV2.COMPONENT_SEQUENCE_ID) =
DECODE(C1.IMPLEMENTATION_DATE, NULL,
C1.OLD_COMPONENT_SEQUENCE_ID, C1.COMPONENT_SEQUENCE_ID)
OR MV2.OPERATION_SEQ_NUM = C1.OPERATION_SEQ_NUM)
AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE1
AND NVL(DISABLE_DATE, CUTOFF_DATE1+1) > CUTOFF_DATE1
AND NOT EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS BICN
WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID1
AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
AND BICN.OLD_COMPONENT_SEQUENCE_ID = C1.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE = 3
AND BICN.DISABLE_DATE < CUTOFF_DATE1 )) --Changed from <= to < for Bug 6689734
OR EFFECTIVITY_DATE > CUTOFF_DATE1
)))
OR (IMPLEMENTED_CODE1 = 1
AND C1.IMPLEMENTATION_DATE IS NOT NULL));
/* Now, update the rows in the temp table if there is a match with the
second bill of material */
UPDATE BOM_COMPARISON_TEMP T
SET T.COMPONENT_SEQUENCE_ID2 =
(SELECT MIN(C.COMPONENT_SEQUENCE_ID)
FROM BOM_INVENTORY_COMPONENTS C
WHERE C.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(C.ECO_FOR_PRODUCTION,2) = 2
AND C.COMPONENT_ITEM_ID = T.ASSEMBLY_ID1
AND NOT EXISTS (SELECT 'x'
FROM BOM_COMPARISON_TEMP T3
WHERE T3.COMPONENT_SEQUENCE_ID2 =
C.COMPONENT_SEQUENCE_ID
AND T3.SEQUENCE_ID = T.SEQUENCE_ID)
AND DECODE(ITEM_NUM_CODE, 1, C.ITEM_NUM, T.ITEM_NUM) = T.ITEM_NUM
AND DECODE(OP_SEQ_CODE, 1, C.OPERATION_SEQ_NUM,
T.OPERATION_SEQ_NUM) = T.OPERATION_SEQ_NUM
AND DECODE(EFF_DATE_CODE, 1, C.EFFECTIVITY_DATE,
T.EFFECTIVITY_DATE) = T.EFFECTIVITY_DATE
AND NVL(DECODE(DIS_DATE_CODE, 1, C.DISABLE_DATE, T.DISABLE_DATE),
SYSDATE) = NVL(T.DISABLE_DATE, SYSDATE)
AND ((IMPL_CODE = 1
AND C.IMPLEMENTATION_DATE IS NOT NULL
AND T.IMPLEMENTATION_DATE IS NOT NULL)
OR
(IMPL_CODE = 1
AND C.IMPLEMENTATION_DATE IS NULL
AND T.IMPLEMENTATION_DATE IS NULL)
OR
(IMPL_CODE = 2))
AND DECODE(QUANTITY_CODE, 1, C.COMPONENT_QUANTITY,
T.COMPONENT_QUANTITY) = T.COMPONENT_QUANTITY
AND NVL(DECODE(PLAN_FACT_CODE, 1, C.PLANNING_FACTOR,
T.PLANNING_FACTOR), 1) = NVL(T.PLANNING_FACTOR, 1)
AND NVL(DECODE(OPTIONAL_CODE, 1, C.OPTIONAL, T.OPTIONAL), 2) =
NVL(T.OPTIONAL, 2)
AND ((IMPLEMENTED_CODE2 = 2)
OR
(IMPLEMENTED_CODE2 = 1
AND C.IMPLEMENTATION_DATE IS NOT NULL))
AND (DISPLAY_CODE2 = 1
OR (DISPLAY_CODE2 = 2
AND CUTOFF_DATE2 BETWEEN EFFECTIVITY_DATE AND
NVL((DISABLE_DATE-(1/86400)) , CUTOFF_DATE2 + 1)) --Added for Bug 6689734
OR (DISPLAY_CODE2 = 3
AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2)
)
AND ((IMPLEMENTED_CODE2 = 2
AND (DISPLAY_CODE2 = 1
OR
(DISPLAY_CODE2 = 2
AND EFFECTIVITY_DATE =
(SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS MV2
WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
AND (DECODE(IMPLEMENTATION_DATE, NULL,
MV2.OLD_COMPONENT_SEQUENCE_ID,
MV2.COMPONENT_SEQUENCE_ID) =
DECODE(C.IMPLEMENTATION_DATE, NULL,
C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
AND NVL(DISABLE_DATE, CUTOFF_DATE2+1) > CUTOFF_DATE2
AND NOT EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS BICN
WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE = 3
AND BICN.DISABLE_DATE < CUTOFF_DATE2 )) --changed from <= to < for Bug 6689734
)
OR
(DISPLAY_CODE2 = 3
AND EFFECTIVITY_DATE =
(SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS MV2
WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
AND (DECODE(IMPLEMENTATION_DATE, NULL,
MV2.OLD_COMPONENT_SEQUENCE_ID,
MV2.COMPONENT_SEQUENCE_ID) =
DECODE(C.IMPLEMENTATION_DATE, NULL,
C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2
AND NOT EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS BICN
WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE = 3
AND BICN.DISABLE_DATE < CUTOFF_DATE2 ))--changed from <= to < for Bug 6689734
OR EFFECTIVITY_DATE > CUTOFF_DATE2
)))
OR (IMPLEMENTED_CODE2 = 1
AND C.IMPLEMENTATION_DATE IS NOT NULL))
)
WHERE T.SEQUENCE_ID = SEQ_ID
AND T.COMPONENT_SEQUENCE_ID2 IS NULL;
SELECT COUNT(*)
INTO COUNT_DUP
FROM BOM_COMPARISON_TEMP T
WHERE EXISTS
(SELECT NULL FROM BOM_COMPARISON_TEMP T2
WHERE T.ROWID < T2.ROWID
AND T.SEQUENCE_ID = T2.SEQUENCE_ID
AND T.COMPONENT_SEQUENCE_ID2 = T2.COMPONENT_SEQUENCE_ID2)
AND T.SEQUENCE_ID = SEQ_ID;
UPDATE BOM_COMPARISON_TEMP T
SET COMPONENT_SEQUENCE_ID2 = ''
WHERE EXISTS
(SELECT NULL FROM BOM_COMPARISON_TEMP T2
WHERE T.ROWID < T2.ROWID
AND T.SEQUENCE_ID = T2.SEQUENCE_ID
AND T.COMPONENT_SEQUENCE_ID2 = T2.COMPONENT_SEQUENCE_ID2)
AND T.SEQUENCE_ID = SEQ_ID;
/* Update the temp table to indicate whether columns that are not
comparison criteria are matches */
UPDATE BOM_COMPARISON_TEMP T
SET (ITEM_NUM,
COMPONENT_QUANTITY,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
DISABLE_DATE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
OPTIONAL) =
(SELECT DECODE(C.ITEM_NUM, T.ITEM_NUM, T.ITEM_NUM, NULL),
DECODE(C.COMPONENT_QUANTITY, T.COMPONENT_QUANTITY,
T.COMPONENT_QUANTITY, NULL),
DECODE(C.OPERATION_SEQ_NUM, T.OPERATION_SEQ_NUM,
T.OPERATION_SEQ_NUM, NULL),
DECODE(TRUNC(C.EFFECTIVITY_DATE), TRUNC(T.EFFECTIVITY_DATE),
T.EFFECTIVITY_DATE, NULL),
DECODE(NVL(TRUNC(C.DISABLE_DATE),TO_DATE('01-01-1901','DD-MM-YYYY')),
NVL(TRUNC(T.DISABLE_DATE),TO_DATE('01-01-1901','DD-MM-YYYY')),
T.DISABLE_DATE, NULL),
DECODE(DECODE(C.IMPLEMENTATION_DATE, NULL,
TO_DATE('01-01-1901','DD-MM-YYYY'),
TO_DATE('02-01-1901','DD-MM-YYYY')),
DECODE(T.IMPLEMENTATION_DATE,
NULL, TO_DATE('01-01-1901','DD-MM-YYYY'),
TO_DATE('02-01-1901','DD-MM-YYYY')),
T.IMPLEMENTATION_DATE, TO_DATE('01-01-1901','DD-MM-YYYY')),
DECODE(NVL(C.PLANNING_FACTOR, -99999),
NVL(T.PLANNING_FACTOR, -99999),
T.PLANNING_FACTOR, NULL),
DECODE(NVL(C.OPTIONAL, -9), NVL(T.OPTIONAL, -9),
T.OPTIONAL, NULL)
FROM BOM_INVENTORY_COMPONENTS C
WHERE C.COMPONENT_SEQUENCE_ID = T.COMPONENT_SEQUENCE_ID2)
WHERE COMPONENT_SEQUENCE_ID1 IS NOT NULL
AND COMPONENT_SEQUENCE_ID2 IS NOT NULL
AND SEQUENCE_ID = SEQ_ID;
/* Insert rows from the second bill of material that did not match anything
in the first bill of material */
INSERT INTO BOM_COMPARISON_TEMP
(SEQUENCE_ID,
ASSEMBLY_ID2,
ITEM_NUM,
COMPONENT_QUANTITY,
OPERATION_SEQ_NUM,
PLANNING_FACTOR,
EFFECTIVITY_DATE,
DISABLE_DATE,
IMPLEMENTATION_DATE,
OPTIONAL,
COMPONENT_SEQUENCE_ID2)
SELECT SEQ_ID,
COMPONENT_ITEM_ID,
ITEM_NUM,
COMPONENT_QUANTITY,
OPERATION_SEQ_NUM,
PLANNING_FACTOR,
EFFECTIVITY_DATE,
DISABLE_DATE,
IMPLEMENTATION_DATE,
OPTIONAL,
COMPONENT_SEQUENCE_ID
FROM BOM_INVENTORY_COMPONENTS C
WHERE BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(C.ECO_FOR_PRODUCTION,2) = 2
AND NOT EXISTS
(SELECT 'x'
FROM BOM_COMPARISON_TEMP T2
WHERE T2.SEQUENCE_ID = SEQ_ID
AND T2.COMPONENT_SEQUENCE_ID2 = C.COMPONENT_SEQUENCE_ID)
AND ((IMPLEMENTED_CODE2 = 2)
OR
(IMPLEMENTED_CODE2 = 1
AND C.IMPLEMENTATION_DATE IS NOT NULL)
)
AND (DISPLAY_CODE2 = 1
OR (DISPLAY_CODE2 = 2
AND CUTOFF_DATE2 BETWEEN EFFECTIVITY_DATE AND
NVL((DISABLE_DATE-(1/86400)), CUTOFF_DATE2 + 1)) --Added for Bug 6689734
OR (DISPLAY_CODE2 = 3
AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2) --changed from >= to > for Bug 6689734
)
AND ((IMPLEMENTED_CODE2 = 2
AND (DISPLAY_CODE2 = 1
OR
(DISPLAY_CODE2 = 2
AND EFFECTIVITY_DATE =
(SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS MV2
WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
AND (DECODE(IMPLEMENTATION_DATE, NULL,
MV2.OLD_COMPONENT_SEQUENCE_ID,
MV2.COMPONENT_SEQUENCE_ID) =
DECODE(C.IMPLEMENTATION_DATE, NULL,
C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
AND NVL(DISABLE_DATE, CUTOFF_DATE2+1) > CUTOFF_DATE2
AND NOT EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS BICN
WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE = 3
AND BICN.DISABLE_DATE < CUTOFF_DATE2 )) --changed from <= to < for Bug 6689734
)
OR
(DISPLAY_CODE2 = 3
AND EFFECTIVITY_DATE =
(SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS MV2
WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
AND (DECODE(IMPLEMENTATION_DATE, NULL,
MV2.OLD_COMPONENT_SEQUENCE_ID,
MV2.COMPONENT_SEQUENCE_ID) =
DECODE(C.IMPLEMENTATION_DATE, NULL,
C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2
AND NOT EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS BICN
WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE = 3
AND BICN.DISABLE_DATE < CUTOFF_DATE2 )) --changed from <= to < for Bug 6689734
OR EFFECTIVITY_DATE > CUTOFF_DATE2
)))
OR (IMPLEMENTED_CODE2 = 1
AND C.IMPLEMENTATION_DATE IS NOT NULL));
/* If we are only displaying differences, then delete all matches from
the temporary table */
IF DIFF_CODE = 1 THEN
DELETE FROM BOM_COMPARISON_TEMP
WHERE SEQUENCE_ID = SEQ_ID
AND COMPONENT_SEQUENCE_ID1 IS NOT NULL
AND COMPONENT_SEQUENCE_ID2 IS NOT NULL;