A work definition defines the manufacturing process for a product and serves as a template for creating work orders during execution. When a final product includes subassemblies that are also manufactured in-house, you define a work definition for the finished good as well as separate work definitions for each subassembly.
A phantom assembly is a non-stocked subassembly used to group materials. Rather than being built as a separate intermediate item, its components are issued directly to the parent assembly’s work def/work order. The phantom components are exploded into the parent’s work definition, simplifying inventory management and eliminating the need for separate jobs for intermediate assemblies. In some cases, organizations may still choose to define work definitions for phantom assemblies to maintain better process visibility and consistency.
Businesses often have a requirement to retrieve all components and subassemblies from parent-child work definitions in order to build the finished good, validate against the item structure for any changes, and perform cost estimation.
Here is a query that gets all operation items and subassemblies, along with their quantities, by traversing parent-child work definitions. This provides a comprehensive view of the manufacturing structure and is highly useful for operations planning and costing analysis.
SELECT LEVEL LEVEL1,
-- CONNECT_BY_ISCYCLE IS_CYCLE,
CONNECT_BY_ROOT Q_WD.ASSY_ITEM ROOT_ASSEMBLY,
SUBSTR (SYS_CONNECT_BY_PATH (Q_WD.ASSY_ITEM, ' <– '), 5) ASSEMBLY_PATH,
LEVEL-1 PARENT_SELECTED,
Q_WD.WORK_DEF_NAME,
Q_WD.WORK_DEF_VERSION,
-- Q_WD.ASSY_ITEM_ID, Q_WD.ASSY_ORG_ID,
Q_WD.ASSY_ITEM, --Q_WD.Assy_Descr,
Q_WD.Oper_Seq, Q_WD.Material_Seq, Q_WD.COMPONENT_SEQUENCE_ID,
-- Q_WD.RM_ITEM_ID, Q_WD.RM_ORG_ID,
Q_WD.RM_ITEM_NO, --Q_WD.RM_DESCRIPTION,
Q_WD.RM_ITEM_TYPE, Q_WD.RM_UOM,
ROUND(egp_exploder_pub.calculate_extended_quantity(SUBSTR(SYS_CONNECT_BY_PATH(Q_WD.YIELD_FACTOR,'*'),2)),5) YIELD_FACTOR,
ROUND(DECODE (Q_WD.basis_type, 1, egp_exploder_pub.calculate_extended_quantity(SUBSTR(SYS_CONNECT_BY_PATH(((Q_WD.COMPONENT_QUANTITY*(Q_WD.PLANNING_FACTOR/100))/Q_WD.YIELD_FACTOR/Q_WD.PRIMARY_OUTPUT_QTY),'*'),2)),
Q_WD.COMPONENT_QUANTITY*(Q_WD.PLANNING_FACTOR/100)/Q_WD.YIELD_FACTOR/Q_WD.PRIMARY_OUTPUT_QTY),5) QUANTITY_PER_PRODUCT
FROM (SELECT WD.WORK_DEFINITION_ID, WD.WORK_DEF_NAME WORK_DEF_NAME,
WV.VERSION_NUMBER WORK_DEF_VERSION,
WD.INVENTORY_ITEM_ID ASSY_ITEM_ID, WD.ORGANIZATION_ID ASSY_ORG_ID,
ASSY.ITEM_NUMBER ASSY_ITEM,
ASSY.description Assy_Descr,
NVL(WD.QUANTITY,1) WD_Qty, WD.UOM_CODE WD_UOM, WD.COSTING_PRIORITY,
WO.OPERATION_SEQ_NUMBER Oper_Seq,
WOM.MATERIAL_SEQ_NUMBER Material_Seq, WOM.COMPONENT_SEQUENCE_ID,
WOM.INVENTORY_ITEM_ID RM_ITEM_ID, WOM.ORGANIZATION_ID RM_ORG_ID,
Comp.Item_Number RM_ITEM_NO,
Comp.description RM_DESCRIPTION,
Comp.ITEM_TYPE RM_ITEM_TYPE,
WOM.COMPONENT_QUANTITY, WOM.UOM_CODE RM_UOM,
WOM.BASIS_TYPE, NVL(WOM.YIELD_FACTOR,1) YIELD_FACTOR,
WOM.PLANNING_FACTOR, WOM.SUPPLY_TYPE,
NVL(WDOO.OUTPUT_QUANTITY,1) PRIMARY_OUTPUT_QTY
FROM WIS_WORK_DEFINITIONS_V WD,
WIS_WD_VERSIONS WV,
EGP_SYSTEM_ITEMS_VL ASSY,
WIS_WD_OPERATIONS_B WO,
WIS_WD_OPERATION_MATERIALS_V WOM,
EGP_SYSTEM_ITEMS_VL Comp,
WIS_WD_OPERATION_OUTPUTS WDOO
WHERE WD.WORK_DEFINITION_ID = WV.WORK_DEFINITION_ID
AND WD.status_code = 'ACTIVE'
-- AND WD.COSTING_PRIORITY = 1 -- Costing Priority 1 only
AND WV.work_definition_version_id = (SELECT MAX(work_definition_version_id) FROM WIS_WD_VERSIONS
WHERE (SYSDATE BETWEEN EFFECTIVE_FROM_DATE AND NVL(EFFECTIVE_TO_DATE,SYSDATE+1))
AND WORK_DEFINITION_ID = WD.WORK_DEFINITION_ID)
AND WD.INVENTORY_ITEM_ID = ASSY.INVENTORY_ITEM_ID
AND WD.ORGANIZATION_ID = ASSY.ORGANIZATION_ID
AND WD.WORK_DEFINITION_ID = WO.WORK_DEFINITION_ID
AND (SYSDATE BETWEEN NVL(WO.EFFECTIVE_FROM_DATE, SYSDATE-1) AND NVL(WO.EFFECTIVE_TO_DATE, SYSDATE+1))
AND WO.WD_OPERATION_ID = WOM.WD_OPERATION_ID
AND (SYSDATE BETWEEN NVL(WOM.EFFECTIVITY_DATE, SYSDATE-1) AND NVL(WOM.DISABLE_DATE, SYSDATE+1))
AND WOM.ORGANIZATION_ID = Comp.ORGANIZATION_ID
AND WOM.INVENTORY_ITEM_ID = Comp.INVENTORY_ITEM_ID
AND WD.WORK_DEFINITION_ID = WDOO.WORK_DEFINITION_ID (+)
AND WDOO.PRIMARY_FLAG (+) = 'Y'
) Q_WD
WHERE Q_WD.SUPPLY_TYPE <> 6 -- Exclude Phantom Supply Type
START WITH Q_WD.ASSY_ITEM = :P_ITEM AND Q_WD.ASSY_ORG_ID = :P_ORG_ID
CONNECT BY NOCYCLE (PRIOR Q_WD.RM_ITEM_ID = Q_WD.ASSY_ITEM_ID AND Q_WD.RM_ORG_ID = Q_WD.ASSY_ORG_ID
AND PRIOR Q_WD.SUPPLY_TYPE = 6)
--ORDER SIBLINGS BY Q_WD.ASSY_ITEM, Q_WD.COMPONENT_SEQUENCE_IDSample Report output
