--========================================================================
-- 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;