--======================================================================== -- Procedure : exploder_userexit -- Parameters: org_id organization_id -- order_by 1 - Op seq, item seq -- 2 - Item seq, op seq -- grp_id unique value to identify current explosion -- use value from sequence bom_small_expl_temp_s -- session_id unique value to identify current session -- use value from bom_small_expl_temp_session_s -- levels_to_explode -- bom_or_eng 1 - BOM -- 2 - ENG -- impl_flag 1 - implemented only -- 2 - both impl and unimpl -- explode_option 1 - All -- 2 - Current -- 3 - Current and future -- module 1 - Costing -- 2 - Bom -- 3 - Order entry -- cst_type_id cost type id for costed explosion -- std_comp_flag 1 - explode only standard components -- 2 - all components -- expl_qty explosion quantity -- item_id item id of asembly to explode -- list_id unique id for lists in bom_lists for range -- report_option 1 - cost rollup with report -- 2 - cost rollup no report -- 3 - temp cost rollup with report -- cst_rlp_id rollup_id -- req_id request id -- prgm_appl_id program application id -- prg_id program id -- user_id user id -- lock_flag 1 - do not lock the table -- 2 - lock the table -- alt_rtg_desg alternate routing designator -- rollup_option 1 - single level rollup -- 2 - full rollup -- plan_factor_flag1 - Yes -- 2 - No -- alt_desg alternate bom designator -- rev_date explosion date -- comp_code concatenated component code lpad 16 -- show_rev 1 - obtain current revision of component -- 2 - don't obtain current revision -- material_ctrl 1 - obtain subinventory locator -- 2 - don't obtain subinventory locator -- lead_time 1 - calculate offset percent -- 2 - don't calculate offset percent -- eff_control 1 - date effectivity -- 2 - serial effectivity -- err_msg error message out buffer -- error_code error code out. returns sql error code -- if sql error, 9999 if loop detected. --========================================================================
DECLARE ERR_MSG VARCHAR2 (100); ERROR_CODE VARCHAR2 (100); BEGIN BOMPXINQ.EXPLODER_USEREXIT (VERIFY_FLAG => 0 --DEFAULT 0 , ORG_ID => 91 --select organization_id, name from hr_all_organization_units , ORDER_BY => 1 --DEFAULT 1 , GRP_ID => 0 --user define , SESSION_ID => 0 --DEFAULT 0 , LEVELS_TO_EXPLODE => 1 --DEFAULT 1 , BOM_OR_ENG => 1 --DEFAULT 1 , IMPL_FLAG => 1 --DEFAULT 1 , PLAN_FACTOR_FLAG => 2 --DEFAULT 2 , EXPLODE_OPTION => 2 --DEFAULT 2 , MODULE => 2 --DEFAULT 2 , CST_TYPE_ID => 0 --DEFAULT 0 , STD_COMP_FLAG => 0 --DEFAULT 0 , EXPL_QTY => 1 --DEFAULT 1 , ITEM_ID => 11097 --select inventory_item_id from mtl_system_items_b where segment1 = 'ITEM-NAME' , UNIT_NUMBER_FROM => NULL -- , UNIT_NUMBER_TO => NULL -- , ALT_DESG => '' --DEFAULT '' , COMP_CODE => '' --DEFAULT '' , REV_DATE => SYSDATE --DEFAULT sysdate , SHOW_REV => 2 --DEFAULT 2 , MATERIAL_CTRL => 2 --DEFAULT 2 , LEAD_TIME => 2 --DEFAULT 2 , ERR_MSG => ERR_MSG -- , ERROR_CODE => ERROR_CODE -- );
DBMS_OUTPUT.PUT_LINE ('ERR_MSG = ' || ERR_MSG); DBMS_OUTPUT.PUT_LINE ('ERROR_CODE = ' || ERROR_CODE); END;
-- Search Export bom list result --======================================================================== DELETE FROM BOM_SMALL_EXPL_TEMP WHERE GROUP_ID = 0;
SELECT EXP.ORGANIZATION_ID , EXP.PLAN_LEVEL , EXP.TOP_ITEM_ID , EXP.ASSEMBLY_ITEM_ID , EXP.COMPONENT_ITEM_ID , (SELECT MSI.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = EXP.TOP_ITEM_ID AND MSI.ORGANIZATION_ID = EXP.ORGANIZATION_ID) TOP_ITEM , (SELECT MSI.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = EXP.ASSEMBLY_ITEM_ID AND MSI.ORGANIZATION_ID = EXP.ORGANIZATION_ID) ASSEMBLY_ITEM , (SELECT MSI.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = EXP.COMPONENT_ITEM_ID AND MSI.ORGANIZATION_ID = EXP.ORGANIZATION_ID) COMPNENT_ITEM , (SELECT MSI.PRIMARY_UOM_CODE FROM MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = EXP.COMPONENT_ITEM_ID AND MSI.ORGANIZATION_ID = EXP.ORGANIZATION_ID) UNIT_OF_MEASURE , (SELECT MSI.PRIMARY_UNIT_OF_MEASURE FROM MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = EXP.COMPONENT_ITEM_ID AND MSI.ORGANIZATION_ID = EXP.ORGANIZATION_ID) UNIT_OF_MEASURE_NAME , EXP.ITEM_NUM , EXP.OPERATION_SEQ_NUM , EXP.EXTENDED_QUANTITY , EXP.SORT_ORDER , EXP.GROUP_ID , EXP.COMPONENT_YIELD_FACTOR , EXP.ITEM_COST , EXP.INCLUDE_IN_ROLLUP_FLAG , EXP.BASED_ON_ROLLUP_FLAG , EXP.ACTUAL_COST_TYPE_ID , EXP.COMPONENT_QUANTITY , EXP.SHRINKAGE_RATE --, SO_BASIS, OPTIONAL, MUTUALLY_EXCLUSIVE_OPTIONS, CHECK_ATP, SHIPPING_ALLOWED, REQUIRED_TO_SHIP, REQUIRED_FOR_REVENUE, INCLUDE_ON_SHIP_DOCS --, PICK_COMPONENTS, PRIMARY_UOM_CODE, PRIMARY_UNIT_OF_MEASURE, BASE_ITEM_ID, ATP_COMPONENTS_FLAG, ATP_FLAG, BOM_ITEM_TYPE, PICK_COMPONENTS_FLAG, REPLENISH_TO_ORDER_FLAG, SHIPPABLE_ITEM_FLAG, CUSTOMER_ORDER_FLAG, INTERNAL_ORDER_FLAG, CUSTOMER_ORDER_ENABLED_FLAG, INTERNAL_ORDER_ENABLED_FLAG, SO_TRANSACTIONS_FLAG, MTL_TRANSACTIONS_ENABLED_FLAG, STOCK_ENABLED_FLAG, DESCRIPTION, CONFIGURATOR_FLAG, PRICE_LIST_ID, ROUNDING_FACTOR, PRICING_CONTEXT , COMPONENT_CODE, LOOP_FLAG, INVENTORY_ASSET_FLAG, PLANNING_FACTOR, PARENT_BOM_ITEM_TYPE, WIP_SUPPLY_TYPE, EFFECTIVITY_DATE, DISABLE_DATE, IMPLEMENTATION_DATE, SUPPLY_SUBINVENTORY, BASIS_TYPE FROM BOM_SMALL_EXPL_TEMP EXP WHERE 1 = 1 AND EXP.GROUP_ID = 0; |
|