BOM Bill of Material Structure

➜ Back to Library
Description
Categories: BI Publisher, Manufacturing
Application: Bills of Material
Source: Bill of Material Structure Report (XML)
Short Name: BOMRBOMS_XML
DB package: BOM_BOMRBOMS_XMLP_PKG 
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
Parameter Name SQL text Validation
Trace Flag 
 
Verify flag 
 
 LOV Oracle
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
Order By 
 
 LOV Oracle
Substitute Components 
 
 LOV Oracle
Reference Designators 
 
 LOV Oracle
Leadtime and Other Detail 
 
 LOV Oracle
Order Entry Detail 
 
 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
Ask a question
Download
Related Blitz Reports
BOM Bill of Material Structure Report GUI BOM Bill of Material Loop BOM Bill of Material Loop Report GUI BOM Structure Report for Lead Times CST Inventory Subledger BOM Routing Report GUI BOM Routing BOM Routing Report for Manufacturing Lead Time ENG Engineering Change Order Detail
Related Topics
BOM Structure Report for Lead TimesBOM DepartmentBOM Standard OperationBOM RoutingsBOM Calendar ExceptionsBOM All Inventories Value (1)BOM Margin Analysis Report with Order Management
Blitz Report In Action