BOM Consolidated Bill of Material

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')