APPS.BOM_REVISIONS SQL Statements

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

Line: 56

	SELECT substrb(ITEM_NUMBER, 1, 40)
	INTO   part_number
	FROM   MTL_ITEM_FLEXFIELDS
	WHERE  ORGANIZATION_ID = org_id
	AND    INVENTORY_ITEM_ID = part_id;

Line: 66

** ITEM_NUMBER: select from mtl_item_flexfields
*/

	FND_MESSAGE.SET_NAME('BOM', 'BOM_GET_REV');

Line: 126

	SELECT REVISION,REVISION_LABEL,REVISION_ID
        FROM   MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
        WHERE  MIR.INVENTORY_ITEM_ID = item_id
        AND    MIR.ORGANIZATION_ID = org_id
        AND    MIR.EFFECTIVITY_DATE  <= rev_date  --Bug 3020310
        AND    MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
        AND   (
                 (eco_status = 'EXCLUDE_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (2)
                 )
                 OR
                 (eco_status = 'EXCLUDE_OPEN_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
                 )
                  OR
                (eco_status = 'EXCLUDE_ALL'
                 AND  NVL(ERI.STATUS_TYPE,0) IN (0,6)
                )
              )
         ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;

Line: 148

       SELECT REVISION,REVISION_LABEL,REVISION_ID
       FROM   MTL_ITEM_REVISIONS_B MIR
       WHERE  INVENTORY_ITEM_ID = item_id
       AND    ORGANIZATION_ID = org_id
       AND    MIR.EFFECTIVITY_DATE  <= rev_date  --Bug 3020310
       AND    ( (examine_type = 'ALL')
                 OR
		(examine_type = 'IMPL_ONLY'
                     AND IMPLEMENTATION_DATE IS NOT NULL
                )
                 OR
		(examine_type = 'PEND_ONLY'
                     AND IMPLEMENTATION_DATE IS NULL
                )
              )
        ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;

Line: 291

	SELECT REVISION
        FROM   MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
        WHERE  MIR.INVENTORY_ITEM_ID = item_id
        AND    MIR.ORGANIZATION_ID = org_id
        AND    MIR.EFFECTIVITY_DATE  <= rev_date  --Bug 3020310
        AND    MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
        AND   (
                 (eco_status = 'EXCLUDE_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (2)
                 )
                 OR
                 (eco_status = 'EXCLUDE_OPEN_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
                 )
                  OR
                (eco_status = 'EXCLUDE_ALL'
                 AND  NVL(ERI.STATUS_TYPE,0) IN (0,6)
                )
              )
         ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;

Line: 313

       SELECT REVISION
       FROM   MTL_ITEM_REVISIONS_B MIR
       WHERE  INVENTORY_ITEM_ID = item_id
       AND    ORGANIZATION_ID = org_id
       AND    MIR.EFFECTIVITY_DATE  <= rev_date  --Bug 3020310
       AND    ( (examine_type = 'ALL')
                 OR
		(examine_type = 'IMPL_ONLY'
                     AND IMPLEMENTATION_DATE IS NOT NULL
                )
                 OR
		(examine_type = 'PEND_ONLY'
                     AND IMPLEMENTATION_DATE IS NULL
                )
              )
        ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;

Line: 403

	SELECT REVISION
        FROM   MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
        WHERE  MIR.INVENTORY_ITEM_ID = item_id
        AND    MIR.ORGANIZATION_ID = org_id
        AND    MIR.EFFECTIVITY_DATE <= rev_date
        AND    MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
        AND   (
                 (eco_status = 'EXCLUDE_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (2)
                 )
                 OR
                 (eco_status = 'EXCLUDE_OPEN_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
                 )
                 /*BUG 9214280 Added the or condition*/
 	         OR
 	         (eco_status = 'EXCLUDE_ALL'
 	          AND  NVL(ERI.STATUS_TYPE,0) IN (0,6)
 	         )
 	         /*End of BUG 9214280*/
              )
         ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;

Line: 427

       SELECT REVISION
       FROM   MTL_ITEM_REVISIONS_B MIR
       WHERE  INVENTORY_ITEM_ID = item_id
       AND    ORGANIZATION_ID = org_id
       AND    MIR.EFFECTIVITY_DATE <= rev_date
       AND    ( (examine_type = 'ALL')
                 OR
		(examine_type = 'IMPL_ONLY'
                     AND IMPLEMENTATION_DATE IS NOT NULL
                )
                 OR
		(examine_type = 'PEND_ONLY'
                     AND IMPLEMENTATION_DATE IS NULL
                )
              )
        ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;

Line: 511

	SELECT REVISION_ID
        FROM   MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
        WHERE  MIR.INVENTORY_ITEM_ID = item_id
        AND    MIR.ORGANIZATION_ID = org_id
        AND    MIR.EFFECTIVITY_DATE <= rev_date
        AND    MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
        AND   (
                 (eco_status = 'EXCLUDE_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (2)
                 )
                 OR
                 (eco_status = 'EXCLUDE_OPEN_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
                 )
              )
         ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;

Line: 529

       SELECT REVISION_ID
       FROM   MTL_ITEM_REVISIONS_B MIR
       WHERE  INVENTORY_ITEM_ID = item_id
       AND    ORGANIZATION_ID = org_id
       AND    MIR.EFFECTIVITY_DATE <= rev_date
       AND    ( (examine_type = 'ALL')
                 OR
		(examine_type = 'IMPL_ONLY'
                     AND IMPLEMENTATION_DATE IS NOT NULL
                )
                 OR
		(examine_type = 'PEND_ONLY'
                     AND IMPLEMENTATION_DATE IS NULL
                )
              )
        ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;

Line: 603

	SELECT PROCESS_REVISION
        FROM   MTL_RTG_ITEM_REVISIONS MIR, ENG_REVISED_ITEMS ERI
        WHERE  MIR.INVENTORY_ITEM_ID = item_id
        AND    MIR.ORGANIZATION_ID = org_id
        AND    MIR.EFFECTIVITY_DATE  <= rev_date  --Bug 3020310
        AND    MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
        AND   (
                 (eco_status = 'EXCLUDE_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (2)
                 )
                 OR
                 (eco_status = 'EXCLUDE_OPEN_HOLD'
                 AND  NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
                 )
                 OR					-- BUG 4127493
                (eco_status = 'EXCLUDE_ALL'
                 AND  NVL(ERI.STATUS_TYPE,0) IN (0,6)
		)
              )
         ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.PROCESS_REVISION DESC;

Line: 626

	SELECT PROCESS_REVISION
	FROM   MTL_RTG_ITEM_REVISIONS
	WHERE  INVENTORY_ITEM_ID = item_id
	AND    ORGANIZATION_ID = org_id
--	AND    trunc(EFFECTIVITY_DATE) <= trunc(rev_date)  -- changed for bug 2631052
	AND    EFFECTIVITY_DATE <= rev_date
        AND    ( (examine_type = 'ALL')                    -- BUG 3779027
                 OR
                 (examine_type = 'IMPL_ONLY'
                    AND IMPLEMENTATION_DATE IS NOT NULL
                 )
                 OR
                 (examine_type = 'PEND_ONLY'
                    AND IMPLEMENTATION_DATE IS NULL
                 )
               )
	ORDER BY EFFECTIVITY_DATE DESC, PROCESS_REVISION DESC;

Line: 806

        SELECT  EFFECTIVITY_DATE
        FROM    MTL_ITEM_REVISIONS_B
        WHERE   INVENTORY_ITEM_ID = item_id
        AND     REVISION = itm_rev
        AND     ORGANIZATION_ID = org_id;

Line: 813

        SELECT  EFFECTIVITY_DATE
        FROM    MTL_RTG_ITEM_REVISIONS
        WHERE   INVENTORY_ITEM_ID = item_id
        AND     PROCESS_REVISION = itm_rev
        AND     ORGANIZATION_ID = org_id;

Line: 893

            SELECT MIN(A.EFFECTIVITY_DATE - 60/(60*60*24))
                 INTO   l_rev_date
                 FROM   MTL_ITEM_REVISIONS_B A
                 WHERE  A.INVENTORY_ITEM_ID = item_id
                 AND    A.ORGANIZATION_ID = org_id
                 AND    A.EFFECTIVITY_DATE >
                           (SELECT EFFECTIVITY_DATE
                            FROM   MTL_ITEM_REVISIONS_B
                            WHERE  INVENTORY_ITEM_ID = item_id
                            AND    ORGANIZATION_ID = org_id
                            AND    REVISION = itm_rev
                           )
                 AND    NOT EXISTS
                          ( SELECT 'X'
                            FROM   ENG_REVISED_ITEMS B
                            WHERE  A.REVISED_ITEM_SEQUENCE_ID =
                                       B.REVISED_ITEM_SEQUENCE_ID
                            AND
                            (
                               (eco_status = 'EXCLUDE_HOLD'
                                  AND  B.STATUS_TYPE = 2
                               )
                               OR
                               (eco_status = 'EXCLUDE_OPEN_HOLD'
                                  AND  B.STATUS_TYPE IN (1,2)
                               )
                               OR
                              (eco_status = 'EXCLUDE_ALL'
                                  AND  B.STATUS_TYPE = 6
                               )
                            )
                          );

Line: 927

                 SELECT MIN(EFFECTIVITY_DATE - 60/(60*60*24))
                 INTO   l_rev_date
                 FROM   MTL_ITEM_REVISIONS_B
                 WHERE  INVENTORY_ITEM_ID = item_id
                 AND    ORGANIZATION_ID = org_id
                 AND    EFFECTIVITY_DATE >
                           (SELECT EFFECTIVITY_DATE
                            FROM   MTL_ITEM_REVISIONS_B
                            WHERE  INVENTORY_ITEM_ID = item_id
                            AND    ORGANIZATION_ID = org_id
                            AND    REVISION = itm_rev
                           );

Line: 942

	SELECT MIN(EFFECTIVITY_DATE - 1/(60*60*24))
        INTO   l_rev_date
        FROM   MTL_RTG_ITEM_REVISIONS
        WHERE  INVENTORY_ITEM_ID = item_id
        AND    ORGANIZATION_ID = org_id
        AND    EFFECTIVITY_DATE >
               (SELECT EFFECTIVITY_DATE
                FROM   MTL_RTG_ITEM_REVISIONS
                WHERE  INVENTORY_ITEM_ID = item_id
                AND    ORGANIZATION_ID = org_id
                AND    PROCESS_REVISION = itm_rev);

Line: 954

	SELECT MIN(EFFECTIVITY_DATE - 60/(60*60*24))  -- changed for bug 2631052
        INTO   l_rev_date
        FROM   MTL_RTG_ITEM_REVISIONS
        WHERE  INVENTORY_ITEM_ID = item_id
        AND    ORGANIZATION_ID = org_id
        AND    trunc(EFFECTIVITY_DATE) >
               (SELECT trunc(EFFECTIVITY_DATE)
                FROM   MTL_RTG_ITEM_REVISIONS
                WHERE  INVENTORY_ITEM_ID = item_id
                AND    ORGANIZATION_ID = org_id
                AND    PROCESS_REVISION = itm_rev);

Line: 976

            SELECT GREATEST(EFFECTIVITY_DATE,SYSDATE)
            INTO   l_rev_date
            FROM   MTL_ITEM_REVISIONS_B
            WHERE  INVENTORY_ITEM_ID = item_id
            AND    ORGANIZATION_ID = org_id
            AND    REVISION = itm_rev;

Line: 984

            SELECT GREATEST(EFFECTIVITY_DATE,SYSDATE)
            INTO   l_rev_date
            FROM   MTL_RTG_ITEM_REVISIONS
            WHERE  INVENTORY_ITEM_ID = item_id
            AND    ORGANIZATION_ID = org_id
            AND    PROCESS_REVISION = itm_rev;

Line: 1007

    SELECT  msivl.CONCATENATED_SEGMENTS
    INTO    l_item_name
    FROM    MTL_SYSTEM_ITEMS_VL msivl
    WHERE   msivl.INVENTORY_ITEM_ID = item_id
    AND     msivl.ORGANIZATION_ID   = org_id;

Line: 1058

       SELECT REVISION
       FROM   MTL_ITEM_REVISIONS_B
       WHERE  INVENTORY_ITEM_ID = item_id
       AND    ORGANIZATION_ID = org_id
       AND    (
                (examine_type = 'ALL')
                OR
		(examine_type = 'IMPL_ONLY'
                   AND IMPLEMENTATION_DATE IS NOT NULL
                )
                OR
	 	(examine_type = 'PEND_ONLY'
                   AND IMPLEMENTATION_DATE IS NULL
                )
              )
       ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;

Line: 1076

       SELECT PROCESS_REVISION
       FROM   MTL_RTG_ITEM_REVISIONS
       WHERE  INVENTORY_ITEM_ID = item_id
       AND    ORGANIZATION_ID = org_id
       AND    (
                (examine_type = 'ALL')
                OR
		(examine_type = 'IMPL_ONLY'
                   AND IMPLEMENTATION_DATE IS NOT NULL
                )
                OR
	 	(examine_type = 'IMPL_AND_PEND'
                   AND IMPLEMENTATION_DATE IS NULL
                )
              )
       ORDER BY EFFECTIVITY_DATE DESC, PROCESS_REVISION DESC;

Line: 1138

  SELECT high_date INTO l_date FROM mtl_item_rev_highdate_v WHERE revision_id = p_revision_id;