APPS.BOMPBCMP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 45

/* 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));

Line: 148

/* 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;

Line: 256

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;

Line: 270

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;

Line: 281

/* 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;

Line: 321

/* 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));

Line: 422

/* 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;