Most of the businesses have requirement to get Indented BOM (Bill of Material) export of the assembly into the excel to validate the bill of material/item structure for the Engineering Change Request/Change Order changes, impacted Work orders/Sales orders etc., This is much needed for ETO (Engineer to Order) and CTO (Configure to Order) model items.
Secondly, Setting up/validating all the items from the indented BOM and their item attributes for Planning/Purchasing/Lead Times/ ATP (Available to Promise)/Lead Time Rollups etc., and defining work definitions for the top assembly and sub-assemblies.
Here is the SQL Query to get the item’s indented BOM. I used extensively to validate the item setups/work definitions for the CTO Model scheduling/work order generation issues.
SELECT LEVEL BOM_LEVEL,
— CONNECT_BY_ISCYCLE IS_CYCLE,
Q_BOM.COMPONENT_NUM ITEM,
Q_BOM.COMPONENT_DESCR ITEM_DESCR,
Q_BOM.QTY,
Q_BOM.UOM,
LEVEL – 1 PARENT_SELECTED,
Q_BOM.ASSEMBLY_NUM PARENT_BOM,
CONNECT_BY_ROOT Q_BOM.ASSEMBLY_NUM ROOT_ASSEMBLY,
SUBSTR (SYS_CONNECT_BY_PATH (Q_BOM.ASSEMBLY_NUM, ‘ <– ‘), 5) ASSEMBLY_PATH, Q_BOM.SEQUENCE FROM (SELECT MB1.ITEM_NUMBER ASSEMBLY_NUM, MB1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID, MB2.ITEM_NUMBER COMPONENT_NUM, MB2.DESCRIPTION COMPONENT_DESCR, BC.COMPONENT_QUANTITY QTY, MB2.PRIMARY_UOM_CODE UOM, BC.item_num SEQUENCE FROM EGP_STRUCTURES_B BS, EGP_SYSTEM_ITEMS_B MB1, EGP_COMPONENTS_B BC, EGP_SYSTEM_ITEMS_VL MB2, INV_ORG_PARAMETERS IOP WHERE 1 = 1 AND BS.ALTERNATE_BOM_DESIGNATOR = ‘Primary’ AND TO_NUMBER (BS.PK1_VALUE) = MB1.INVENTORY_ITEM_ID AND TO_NUMBER (BS.PK2_VALUE) = MB1.ORGANIZATION_ID AND BC.BILL_SEQUENCE_ID = BS.COMMON_BILL_SEQUENCE_ID AND TO_NUMBER (BC.PK1_VALUE) = MB2.INVENTORY_ITEM_ID AND TO_NUMBER (BC.PK2_VALUE) = MB2.ORGANIZATION_ID AND MB1.ORGANIZATION_ID = IOP.ORGANIZATION_ID AND IOP.ORGANIZATION_CODE = :P_ORGANIZATION AND (BS.EFFECTIVITY_CONTROL = 1 AND BC.EFFECTIVITY_DATE <= SYSDATE AND NVL(BC.DISABLE_DATE, SYSDATE + 1) > SYSDATE)) Q_BOM
START WITH Q_BOM.ASSEMBLY_NUM = :P_ITEM_NUMBER
CONNECT BY NOCYCLE PRIOR Q_BOM.COMPONENT_NUM = Q_BOM.ASSEMBLY_NUM
ORDER SIBLINGS BY Q_BOM.ASSEMBLY_NUM, Q_BOM.SEQUENCE
Sample Output of the Query for item# 296001
