BOM Consolidated Bill of Material

Description
Categories: BI Publisher, Manufacturing
Application: Bills of Material
Source: Consolidated Bill of Material Report (XML)
Short Name: BOMRBOMC_XML
DB package: BOM_BOMRBOMC_XMLP_PKG
SELECT         
     BET.TOP_BILL_SEQUENCE_ID        M_TOP_BILL_SEQUENCE_ID,
     MIF.ITEM_NUMBER                 M_ASSEMBLY,
     INV_MEANING_SEL.C_ITEM_DESCRIPTION(MIF.ITEM_ID,MIF.ORGANIZATION_ID)    M_DESCRIPTION,
     BBOM.ALTERNATE_BOM_DESIGNATOR   M_ASS_DESIG,
     BAD.DESCRIPTION                 M_ASS_DESIG_DESC,
     MIF.PRIMARY_UOM_CODE            M_ASS_UOM,
     SUBSTR(:LP_REVISION_DATE,12,5)   M_REVISION_TIME,
     MIF.BOM_ITEM_TYPE               M_BOM_ITEM_TYPE,
     MIF.ITEM_CATALOG_GROUP_ID       M_ITEM_CATALOG_GROUP_ID,
     REV.REVISION                    M_REVISION,
     LU1.MEANING                     M_ENG_BILL
FROM BOM_EXPLOSION_TEMP BET,
     BOM_BILL_OF_MATERIALS BBOM,
     MTL_ITEM_FLEXFIELDS MIF,
     MTL_ITEM_REVISIONS REV,
     MFG_LOOKUPS LU1, 
     BOM_ALTERNATE_DESIGNATORS BAD
WHERE
     BET.GROUP_ID = :P_GROUP_ID
AND  BET.PLAN_LEVEL = 0
AND  BET.TOP_BILL_SEQUENCE_ID = BBOM.BILL_SEQUENCE_ID
AND  BBOM.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND  BBOM.ASSEMBLY_ITEM_ID = MIF.ITEM_ID
AND  MIF.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND  ( (BBOM.ALTERNATE_BOM_DESIGNATOR IS NULL AND
        BAD.ORGANIZATION_ID = -1) OR
       (BBOM.ALTERNATE_BOM_DESIGNATOR IS NOT NULL AND
        BBOM.ALTERNATE_BOM_DESIGNATOR = BAD.ALTERNATE_DESIGNATOR_CODE AND
        BAD.ORGANIZATION_ID = :P_ORGANIZATION_ID) 
     )
AND REV.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND  REV.EFFECTIVITY_DATE <= :LP_REVISION_DATE
AND  REV.INVENTORY_ITEM_ID = BBOM.ASSEMBLY_ITEM_ID
AND  NOT EXISTS (SELECT 'max rev date'
        FROM MTL_ITEM_REVISIONS REV2
        WHERE REV2.INVENTORY_ITEM_ID = BBOM.ASSEMBLY_ITEM_ID
        AND   REV2.ORGANIZATION_ID = :P_ORGANIZATION_ID
        AND   REV2.EFFECTIVITY_DATE <= :LP_REVISION_DATE
        AND   REV2.EFFECTIVITY_DATE > REV.EFFECTIVITY_DATE)
AND  NOT EXISTS (SELECT 'max rev'
                     FROM MTL_ITEM_REVISIONS REV3
                     WHERE REV3.INVENTORY_ITEM_ID = BBOM.ASSEMBLY_ITEM_ID
                     AND   REV3.ORGANIZATION_ID = :P_ORGANIZATION_ID  
                     AND   REV3.EFFECTIVITY_DATE = REV.EFFECTIVITY_DATE
                     AND   REV3.REVISION > REV.REVISION)
AND  LU1.LOOKUP_CODE = BBOM.ASSEMBLY_TYPE
AND  LU1.LOOKUP_TYPE = 'BOM_NO_YES'
ORDER BY MIF.PADDED_ITEM_NUMBER,
         NVL(BBOM.ALTERNATE_BOM_DESIGNATOR, '0')
Parameter Name SQL text Validation
Item
 
Number
Trace Flag
 
Dynamic Precision Option
 
LOV Oracle
Group id
 
Number
Category Structure
 
Number
BOM_SRS_ORG_ID
 
Number
BOM_SRS_NONE_CHAR
 
Use Planning Percent
 
LOV Oracle
Assembly Detail
 
LOV Oracle
Explosion Quantity
 
Number
Display Option
 
LOV Oracle
Implemented Only
 
LOV Oracle
Levels to Explode
 
Number
To
 
Categories From
 
Category Set
 
LOV Oracle
To
 
Items From
 
Date
 
DateTime
Revision
 
LOV Oracle
Alternate
 
LOV Oracle
Alternate Selection
 
LOV Oracle
BOM_SRS_SPECIFIC_BILL
 
Item Selection
 
LOV Oracle