BOM Consolidated Bill of Material Report GUI

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Consolidated Bill of Material Report
Application: Bills of Material
Source: Consolidated Bill of Material Report GUI (XML)
Short Name: BOMRBOMCG_XML
DB package: BOM_BOMRBOMC_XMLP_PKG
Run BOM Consolidated Bill of Material Report GUI and other Oracle EBS reports with Blitz Report™ on our demo environment
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 Selection
 
LOV Oracle
BOM_SRS_SPECIFIC_BILL
 
Char
Alternate Selection
 
LOV Oracle
Alternate
 
LOV Oracle
Revision
 
LOV Oracle
Date
 
DateTime
Items From
 
Char
To
 
Char
Category Set
 
LOV Oracle
Categories From
 
Char
To 2
 
Char
Levels to Explode
 
Number
Implemented Only
 
LOV Oracle
Display Option
 
LOV Oracle
Explosion Quantity
 
Number
Assembly Detail
 
LOV Oracle
Use Planning Percent
 
LOV Oracle
Blitz Report™