BOM Bill of Material Loop

SELECT
               BEV.ORGANIZATION_ID            COMPO_ORG_ID,
               BEV.SORT_ORDER                 SORT_ORDER,
               LPAD(TO_CHAR(BEV.PLAN_LEVEL), LEAST(9,BEV.PLAN_LEVEL),'.') PLAN_LEVEL,
               BEV.TOP_BILL_SEQUENCE_ID       TOP_BILL_SEQUENCE_ID,
               BEV.COMPONENT_SEQUENCE_ID      COMPONENT_SEQUENCE_ID10,
               BEV.ITEM_NUM               ITEM_SEQ_NUM,
               BEV.OPERATION_SEQ_NUM          OPERATION_SEQ_NUM,
               BEV.COMPONENT_ITEM_ID          COMPONENT_ITEM_ID,
               BEV.ITEM_NUMBER                        COMPONENT_NUMBER,
               BEV.UNIT_OF_MEASURE           ITEM_UOM,
               BEV.EFFECTIVITY_DATE           EFFECTIVITY_DATE,
               TO_CHAR(BEV.EFFECTIVITY_DATE, 'HH24:MI') EFFECTIVITY_TIME,
               BEV.DISABLE_DATE               DISABLE_DATE,
               TO_CHAR(BEV.DISABLE_DATE, 'HH24:MI') DISABLE_TIME,
               ROUND(BEV.COMPONENT_QUANTITY,:P_qty_precision) COMPONENT_QUANTITY,
               ROUND(BEV.EXTENDED_QUANTITY,:P_qty_precision) EXTENDED_QUANTITY,
               BEV.CHANGE_NOTICE              CHANGE_NOTICE,
               SUBSTR(FL.MEANING,1,4)         ENG_ITEM_FLAG,
               BEV.PARENT_ALTERNATE           COMPONENT_ALTERNATE,
               BEV.ENG_BILL                   ENG_BILL,
               BEV.PLANNING_FACTOR            PLANNING_FACTOR,
               ROUND(BEV.COMPONENT_YIELD_FACTOR,:P_qty_precision) COMPONENT_YIELD_FACTOR,
               DECODE(BOM.COMMON_BILL_SEQUENCE_ID, BOM.BILL_SEQUENCE_ID, BEV.WIP_SUPPLY_TYPE, PARENT.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
               PARENT.ORGANIZATION_ID   PARENT_ORG_ID,
               DECODE(BOM.COMMON_BILL_SEQUENCE_ID, BOM.BILL_SEQUENCE_ID, BEV.SUPPLY_SUBINVENTORY, PARENT.WIP_SUPPLY_SUBINVENTORY) SUPPLY_SUBINVENTORY,
               null            C_LOCATOR_FLEXDATA,
               ROUND(BEV.MANUFACTURING_LEAD_TIME,:P_qty_precision) MANUFACTORYING_LEAD_TIME,
               BEV.PARENT_ITEM_ID             PARENT_ITEM_ID,
               BEV.OPERATION_LEAD_TIME_PERCENT OPERATION_LEAD_TIME_PERCENT,
               ROUND((1-(BEV.OPERATION_LEAD_TIME_PERCENT/100))*PARENT.FULL_LEAD_TIME,:P_qty_precision) OPERATION_OFFSET,
               ROUND(BEV.CUM_MANUFACTURING_LEAD_TIME,:P_qty_precision) CUM_MANUFACTURING_LEAN_TIME,
               ROUND(BEV.CUMULATIVE_TOTAL_LEAD_TIME,:P_qty_precision) CUMULATIVE_TOTAL_LEAD_TIME,
               SUBSTR(LU1.MEANING, 1, 20)      BOM_ITEM_TYPE,
               BEV.OPTIONAL                   OPTIONAL,
               BEV.MUTUALLY_EXCLUSIVE_OPTIONS MUTUALLY_EXCLUSIVE_OPTIONS,
               BEV.CHECK_ATP                  CHECK_ATP,
               BEV.SO_BASIS                   SO_BASIS,
               BEV.REQUIRED_TO_SHIP           REQUIRED_TO_SHIP,
               BEV.REQUIRED_FOR_REVENUE       REQUIRED_FOR_REVENUE,
               BEV.INCLUDE_ON_SHIP_DOCS       INCLUDE_ON_SHIP_DOCS,
               ROUND(BEV.MINIMUM_QUANTITY,:P_qty_precision) MINIMUM_QUANTITY,
               ROUND(BEV.MAXIMUM_QUANTITY,:P_qty_precision) MAXIMUM_QUANTITY,
               DECODE(BOM.COMMON_BILL_SEQUENCE_ID, BOM.BILL_SEQUENCE_ID, BEV.SUPPLY_LOCATOR_ID,
               PARENT.WIP_SUPPLY_LOCATOR_ID) SUPPLY_LOCATOR_ID,
               MCK.CONCATENATED_SEGMENTS CATEGORY,
               MSI.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS,
                BOM_BOMRBOMS_XMLP_PKG.cf_comp_descformula(BEV.COMPONENT_ITEM_ID, BEV.ORGANIZATION_ID) CF_COMP_DESC,
                BOM_BOMRBOMS_XMLP_PKG.get_rev(BEV.ORGANIZATION_ID, BEV.COMPONENT_ITEM_ID) C_D3_REVISION,
                BOM_BOMRBOMS_XMLP_PKG.cf_revision_descformula(BEV.COMPONENT_ITEM_ID, :C_D3_REVISION) CF_REVISION_DESC,
                BOM_BOMRBOMS_XMLP_PKG.supply_type_dispformula(DECODE ( BOM.COMMON_BILL_SEQUENCE_ID , BOM.BILL_SEQUENCE_ID , BEV.WIP_SUPPLY_TYPE , PARENT.WIP_SUPPLY_TYPE )) supply_type_disp,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_flexfield', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_LOCATOR_FLEXFIELD,
                BOM_BOMRBOMS_XMLP_PKG.eng_bill_dispformula(BEV.ENG_BILL) ENG_BILL_DISP,
                BOM_BOMRBOMS_XMLP_PKG.optional_dispformula(BEV.OPTIONAL) OPTIONAL_DISP,
                BOM_BOMRBOMS_XMLP_PKG.mutually_dispformula(BEV.MUTUALLY_EXCLUSIVE_OPTIONS) MUTUALLY_DISP,
                BOM_BOMRBOMS_XMLP_PKG.check_atp_dispformula(BEV.CHECK_ATP) CHECK_ATP_DISP,
                BOM_BOMRBOMS_XMLP_PKG.required_to_ship_dispformula(BEV.REQUIRED_TO_SHIP) REQUIRED_TO_SHIP_DISP,
                BOM_BOMRBOMS_XMLP_PKG.required_for_revenue_dispformu(BEV.REQUIRED_FOR_REVENUE) REQUIRED_FOR_REVENUE_DISP,
                BOM_BOMRBOMS_XMLP_PKG.include_on_ship_docs_dispformu(BEV.INCLUDE_ON_SHIP_DOCS) INCLUDE_ON_SHIP_DOCS_DISP
            FROM BOM_EXPLOSION_VIEW BEV,
                 MTL_ITEM_LOCATIONS MIL,
                 FND_COMMON_LOOKUPS LU1,
                 FND_LOOKUPS FL,
                 BOM_BILL_OF_MATERIALS BOM,
                 MTL_SYSTEM_ITEMS PARENT,
                 MTL_SYSTEM_ITEMS MSI,
                 MTL_ITEM_CATEGORIES MIC,
                 MTL_DEFAULT_CATEGORY_SETS MDCS,
                 MTL_CATEGORIES_KFV MCK,
                 MFG_LOOKUPS ML
            WHERE
             BEV.GROUP_ID = :P_GROUP_ID
            AND BEV.PLAN_LEVEL > 0
            AND BEV.SUPPLY_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
            AND MIL.ORGANIZATION_ID(+) = BEV.ORGANIZATION_ID
            AND LU1.APPLICATION_ID(+) = 401
            AND BEV.ITEM_TYPE = LU1.LOOKUP_CODE(+)
            AND LU1.LOOKUP_TYPE(+) = 'ITEM_TYPE'
            AND FL.LOOKUP_TYPE = 'YES_NO'
            AND FL.LOOKUP_CODE = BEV.ENG_ITEM_FLAG
            AND BEV.PARENT_ITEM_ID = PARENT.INVENTORY_ITEM_ID
            AND BEV.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
            AND MSI.ORGANIZATION_ID =  BEV.ORGANIZATION_ID
            AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
            AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
            AND MIC.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID
            AND MDCS.FUNCTIONAL_AREA_ID = ML.LOOKUP_CODE
            AND ML.LOOKUP_TYPE = 'MTL_FUNCTIONAL_AREAS'
            AND ML.LOOKUP_CODE =  1
            AND MIC.CATEGORY_ID = MCK.CATEGORY_ID
            AND BOM.BILL_SEQUENCE_ID = BEV.BILL_SEQUENCE_ID
            AND (   ( (:P_VERIFY_FLAG IS NULL) OR (:P_VERIFY_FLAG <> 1) )
                 OR ( (:P_VERIFY_FLAG = 1) AND (BEV.LOOP_FLAG = 1) ) )
             and BEV.TOP_BILL_SEQUENCE_ID=:M_TOP_BILL_SEQUENCE_ID
             and PARENT.ORGANIZATION_ID=:M_ORG_ID
            ORDER BY BEV.SORT_ORDER