How to Explode BOM in Oracle ( Bill of Material)
Declare
Cursor cur_rec is
select * from BOM_BILL_OF_MATERIALS_V
where organization_id=85
and assembly_item_id in (835);--,1024,1022,1029);
l_err_msg varchar2(2000); l_error_code varchar2(2000);
BEGIN
delete from bom_explosion_temp;
For I in cur_rec loop
---FND_MESSAGE.DEBUG('1---'||sql%rowcount);
bompexpl.exploder_userexit (verify_flag => 0,
org_id => i.ORGANIZATION_ID,
order_by => 1,
grp_id => 99,
session_id => 5,
levels_to_explode => 60,
bom_or_eng => 1,
impl_flag => 1,
plan_factor_flag => 2,
explode_option => 2,
module => 2,
cst_type_id => 0,
std_comp_flag => 2,
expl_qty => 1,
item_id => i.assembly_item_id,
alt_desg => '',
comp_code => '',
rev_date => NULL,
err_msg => l_err_msg,
error_code => l_error_code
);
dbms_output.put_line(l_err_msg);
/* Formatted on 2015/03/04 17:26 (Formatter Plus v4.8.8) */
INSERT INTO xx_bom_explosion_temp
(top_item_id, plan_level, item_code,
item_desc, item_num,
operation_seq_num, component_quantity,attribute15)
SELECT xbet.top_item_id, plan_level,
LPAD (' ', 2 * (LEVEL - 1)) || TO_CHAR (msi.concatenated_segments) bom_item_code,
msi.description, xbet.item_num,
xbet.operation_seq_num,
xbet.component_quantity -- bet.uom
,
UPPER (bic.inventory_item_status_code) status
FROM bom_explosion_temp xbet,
bom_inventory_components_v bic,
mtl_system_items_kfv msi
WHERE /*xbet.top_item_id = :sample_bom_item_id
AND */ xbet.organization_id = :inv_org_id
AND xbet.component_item_id = bic.component_item_id(+)
AND xbet.bill_sequence_id = bic.bill_sequence_id(+)
AND xbet.component_item_id = msi.inventory_item_id
AND xbet.organization_id = msi.organization_id
START WITH xbet.assembly_item_id IS NULL
CONNECT BY PRIOR xbet.component_item_id = xbet.assembly_item_id;
end loop;
commit;
END;
=========================================================================
Table Scripts to create BOM TEMP TABLE
=========================================================================
CREATE TABLE XX_BOM_EXPLOSION_TEMP
(
TOP_BILL_SEQUENCE_ID NUMBER NOT NULL,
BILL_SEQUENCE_ID NUMBER NOT NULL,
ORGANIZATION_ID NUMBER NOT NULL,
COMPONENT_SEQUENCE_ID NUMBER,
COMPONENT_ITEM_ID NUMBER,
PLAN_LEVEL NUMBER NOT NULL,
EXTENDED_QUANTITY NUMBER,
SORT_ORDER VARCHAR2(2000 BYTE) NOT NULL,
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
GROUP_ID NUMBER,
SESSION_ID NUMBER,
SELECT_FLAG VARCHAR2(1 BYTE),
SELECT_QUANTITY NUMBER,
EXTEND_COST_FLAG NUMBER,
TOP_ALTERNATE_DESIGNATOR VARCHAR2(10 BYTE),
TOP_ITEM_ID NUMBER,
CONTEXT VARCHAR2(30 BYTE),
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE),
ATTRIBUTE3 VARCHAR2(150 BYTE),
ATTRIBUTE4 VARCHAR2(150 BYTE),
ATTRIBUTE5 VARCHAR2(150 BYTE),
ATTRIBUTE6 VARCHAR2(150 BYTE),
ATTRIBUTE7 VARCHAR2(150 BYTE),
ATTRIBUTE8 VARCHAR2(150 BYTE),
ATTRIBUTE9 VARCHAR2(150 BYTE),
ATTRIBUTE10 VARCHAR2(150 BYTE),
ATTRIBUTE11 VARCHAR2(150 BYTE),
ATTRIBUTE12 VARCHAR2(150 BYTE),
ATTRIBUTE13 VARCHAR2(150 BYTE),
ATTRIBUTE14 VARCHAR2(150 BYTE),
ATTRIBUTE15 VARCHAR2(150 BYTE),
HEADER_ID NUMBER,
LINE_ID NUMBER,
LIST_PRICE NUMBER,
SELLING_PRICE NUMBER,
COMPONENT_YIELD_FACTOR NUMBER,
ITEM_COST NUMBER,
INCLUDE_IN_ROLLUP_FLAG NUMBER,
BASED_ON_ROLLUP_FLAG NUMBER,
ACTUAL_COST_TYPE_ID NUMBER,
COMPONENT_QUANTITY NUMBER,
SHRINKAGE_RATE NUMBER,
SO_BASIS NUMBER,
OPTIONAL NUMBER,
MUTUALLY_EXCLUSIVE_OPTIONS NUMBER,
CHECK_ATP NUMBER,
SHIPPING_ALLOWED NUMBER,
REQUIRED_TO_SHIP NUMBER,
REQUIRED_FOR_REVENUE NUMBER,
INCLUDE_ON_SHIP_DOCS NUMBER,
INCLUDE_ON_BILL_DOCS NUMBER,
LOW_QUANTITY NUMBER,
HIGH_QUANTITY NUMBER,
PICK_COMPONENTS NUMBER,
PRIMARY_UOM_CODE VARCHAR2(3 BYTE),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(25 BYTE),
BASE_ITEM_ID NUMBER,
ATP_COMPONENTS_FLAG VARCHAR2(1 BYTE),
ATP_FLAG VARCHAR2(1 BYTE),
BOM_ITEM_TYPE NUMBER,
PICK_COMPONENTS_FLAG VARCHAR2(1 BYTE),
REPLENISH_TO_ORDER_FLAG VARCHAR2(1 BYTE),
SHIPPABLE_ITEM_FLAG VARCHAR2(1 BYTE),
CUSTOMER_ORDER_FLAG VARCHAR2(1 BYTE),
INTERNAL_ORDER_FLAG VARCHAR2(1 BYTE),
CUSTOMER_ORDER_ENABLED_FLAG VARCHAR2(1 BYTE),
INTERNAL_ORDER_ENABLED_FLAG VARCHAR2(1 BYTE),
SO_TRANSACTIONS_FLAG VARCHAR2(1 BYTE),
MTL_TRANSACTIONS_ENABLED_FLAG VARCHAR2(1 BYTE),
STOCK_ENABLED_FLAG VARCHAR2(1 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
ASSEMBLY_ITEM_ID NUMBER,
ALTERNATE_BOM_DESIGNATOR VARCHAR2(10 BYTE),
CONFIGURATOR_FLAG VARCHAR2(1 BYTE),
PRICE_LIST_ID NUMBER,
ROUNDING_FACTOR NUMBER,
PRICING_CONTEXT VARCHAR2(30 BYTE),
PRICING_ATTRIBUTE1 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE2 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE3 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE4 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE5 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE6 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE7 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE8 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE9 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE10 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE11 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE12 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE13 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE14 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE15 VARCHAR2(150 BYTE),
COMPONENT_CODE VARCHAR2(1000 BYTE),
LOOP_FLAG NUMBER,
INVENTORY_ASSET_FLAG NUMBER,
PLANNING_FACTOR NUMBER,
OPERATION_SEQ_NUM NUMBER,
PARENT_BOM_ITEM_TYPE NUMBER,
WIP_SUPPLY_TYPE NUMBER,
ITEM_NUM NUMBER,
EFFECTIVITY_DATE DATE,
DISABLE_DATE DATE,
IMPLEMENTATION_DATE DATE,
SUPPLY_SUBINVENTORY VARCHAR2(10 BYTE),
SUPPLY_LOCATOR_ID NUMBER,
COMPONENT_REMARKS VARCHAR2(240 BYTE),
CHANGE_NOTICE VARCHAR2(10 BYTE),
OPERATION_LEAD_TIME_PERCENT NUMBER,
REXPLODE_FLAG NUMBER,
COMMON_BILL_SEQUENCE_ID NUMBER,
COMMON_ORGANIZATION_ID NUMBER,
PRIMARY_PATH_FLAG NUMBER,
AUTO_REQUEST_MATERIAL VARCHAR2(1 BYTE),
EXPLOSION_TYPE VARCHAR2(20 BYTE),
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
COMP_BILL_SEQ_ID NUMBER,
COMP_COMMON_BILL_SEQ_ID NUMBER,
NUM_COL1 NUMBER,
NUM_COL2 NUMBER,
NUM_COL3 NUMBER,
DATE_COL1 DATE,
DATE_COL2 DATE,
DATE_COL3 DATE,
CHAR_COL1 VARCHAR2(80 BYTE),
CHAR_COL2 VARCHAR2(80 BYTE),
CHAR_COL3 VARCHAR2(80 BYTE),
INCLUDE_IN_COST_ROLLUP NUMBER,
PARENT_SORT_ORDER VARCHAR2(2000 BYTE),
STRUCTURE_TYPE_ID NUMBER,
OBJ_NAME VARCHAR2(30 BYTE),
PK1_VALUE VARCHAR2(240 BYTE),
PK2_VALUE VARCHAR2(240 BYTE),
PK3_VALUE VARCHAR2(240 BYTE),
PK4_VALUE VARCHAR2(240 BYTE),
PK5_VALUE VARCHAR2(240 BYTE),
HGRID_FLAG VARCHAR2(240 BYTE),
OPERATION_OFFSET NUMBER,
CURRENT_REVISION VARCHAR2(3 BYTE),
LOCATOR VARCHAR2(40 BYTE),
SUGGESTED_VENDOR_NAME VARCHAR2(240 BYTE),
UNIT_PRICE NUMBER,
VENDOR_ID NUMBER,
FROM_END_ITEM_UNIT_NUMBER VARCHAR2(30 BYTE),
TO_END_ITEM_UNIT_NUMBER VARCHAR2(30 BYTE),
FROM_END_ITEM_REV_ID NUMBER,
FROM_END_ITEM_MINOR_REV_ID NUMBER,
TO_END_ITEM_REV_ID NUMBER,
TO_END_ITEM_MINOR_REV_ID NUMBER,
NEW_COMPONENT_CODE VARCHAR2(4000 BYTE),
FROM_BILL_REVISION_ID NUMBER,
TO_BILL_REVISION_ID NUMBER,
REVISION_ID NUMBER,
REVISION_LABEL VARCHAR2(260 BYTE),
EFFECTIVITY_CONTROL NUMBER,
ACCESS_FLAG VARCHAR2(1 BYTE),
ENG_ITEM_FLAG VARCHAR2(1 BYTE),
ASSEMBLY_TYPE NUMBER,
FROM_OBJECT_REVISION_ID NUMBER,
FROM_MINOR_REVISION_ID NUMBER,
TO_OBJECT_REVISION_ID NUMBER,
TO_MINOR_REVISION_ID NUMBER,
OBJECT_REVISION_ID NUMBER,
MINOR_REVISION_ID NUMBER,
MINOR_REVISION_CODE VARCHAR2(30 BYTE),
COMPONENT_ITEM_REVISION_ID NUMBER,
COMPONENT_MINOR_REVISION_ID NUMBER,
BOM_IMPLEMENTATION_DATE DATE,
GTIN_NUMBER VARCHAR2(25 BYTE),
GTIN_DESCRIPTION VARCHAR2(240 BYTE),
TRADE_ITEM_DESCRIPTOR VARCHAR2(35 BYTE),
TRADE_ITEM_DESCRIPTOR_DESC VARCHAR2(80 BYTE),
GTIN_PUBLICATION_STATUS VARCHAR2(1 BYTE),
TOP_GTIN_NUMBER VARCHAR2(25 BYTE),
TOP_GTIN_DESCRIPTION VARCHAR2(240 BYTE),
TOP_TRADE_ITEM_DESCRIPTOR VARCHAR2(35 BYTE),
PARENT_GTIN_NUMBER VARCHAR2(25 BYTE),
PARENT_GTIN_DESCRIPTION VARCHAR2(240 BYTE),
PARENT_TRADE_ITEM_DESCRIPTOR VARCHAR2(35 BYTE),
QUANTITY_OF_CHILDREN NUMBER,
TOTAL_QTY_AT_NEXT_LEVEL NUMBER,
CHANGE_ID NUMBER,
ACD_TYPE NUMBER,
QUANTITY_RELATED NUMBER,
CHANGE_POLICY_VALUE NUMBER,
EXPLODED_DATE DATE,
EXPLODED_UNIT_NUMBER VARCHAR2(30 BYTE),
EXPLODED_END_ITEM_REV NUMBER,
EXPLODED_OPTION NUMBER,
BASIS_TYPE NUMBER,
ITEM_CODE VARCHAR2(240 BYTE),
ITEM_DESC VARCHAR2(2000 BYTE)
);
=========================================================================
Query to get BOM Explosion from TEMP TABLE
=========================================================================
SELECT xxleg.top_item_id,plan_level,LPAD (' ', 2 * (LEVEL - 1)) || TO_CHAR (msi.CONCATENATED_SEGMENTS) bom_item_code,
msi.DESCRIPTION,
xxleg.item_num,
xxleg.operation_seq_num, xxleg.component_quantity-- bet.uom
,UPPER (bic.inventory_item_status_code) status
FROM bom_explosion_temp xxleg,bom_inventory_components_v bic, mtl_system_items_kfv msi
WHERE /*xxleg.top_item_id = :sample_bom_item_id
AND */xxleg.organization_id = :inv_org_id
AND xxleg.component_item_id = bic.component_item_id (+)
AND xxleg.bill_sequence_id = bic.bill_sequence_id (+)
and xxleg.COMPONENT_ITEM_ID=msi.INVENTORY_ITEM_ID
and xxleg.ORGANIZATION_ID=msi.ORGANIZATION_ID
START WITH xxleg.assembly_item_id IS NULL
CONNECT BY PRIOR xxleg.component_item_id = xxleg.assembly_item_id;
Order by xxleg.top_item_id
--xxleg.plan_level,item_num,operation_seq_num;
***********************************************************************************
#Oracle Apps #EBS #BOM #Bill Of Material #WIP #BOM Explode, Explosion #Intended Bill of Material
Some Important BOM Table:-
- BOM_BILL_OF_MATERIALS
- BOM_RESOURCES
- BOM_DEPARTMENTS
- BOM_OPERATIONAL_ROUTINGS
- BOM_OPERATION_SEQUENCES
- BOM_OPERATION_SEQUENCES
- BOM_INVENTORY_COMPONENTS
- BOM_STANDARD_OPERATIONS
