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: 124

	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: 146

       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: 289

	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: 311

       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: 400

	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)
                 )
              )
         ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;

Line: 418

       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: 502

	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: 520

       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: 594

	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: 617

	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: 797

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

Line: 804

        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: 884

            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: 918

                 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: 933

	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: 945

	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: 967

            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: 975

            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: 998

    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: 1049

       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: 1067

       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: 1129

  SELECT high_date INTO l_date FROM mtl_item_rev_highdate_v WHERE revision_id = p_revision_id;