BOM Bill of Material Structure Report GUI

Description
Categories: BI Publisher, Manufacturing
Application: Bills of Material
Source: Bill of Material Structure Report GUI (XML)
Short Name: BOMRBOMSG_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
Show Full Description
 
LOV Oracle
Component Item 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
Organization Hierarchy
 
LOV Oracle
All Organizations
 
LOV Oracle