Work Definition defines the manufacturing or maintenance process for a product. It is essentially a template to create a work order for the work execution.
It is important to regularly validate all work definitions, including their operation items and supply types, to ensure that all required materials are included. Missing materials in a work definition can create significant challenges during work order execution.
Below is the SQL query to extract all work definitions and their associated operation items Excel for a specific organization.
SELECT ORG.organization_code Org_Code,
— WD.WORK_DEFINITION_ID, WD.ORGANIZATION_ID,
WD.WORK_DEF_NAME WORK_DEF_NAME,
WV.VERSION_NUMBER Version,
WV.EFFECTIVE_FROM_DATE Start_Date,
WV.EFFECTIVE_TO_DATE End_Date,
WD.WORK_METHOD_NAME Work_Method,
WD.STRUCTURE_DISPLAY_NAME Structure_Name,
— WD.OBJECT_VERSION_NUMBER, WD.INACTIVE_DATE, WD.WD_NAME_EFFECTIVE_FROM_DATE, WD.WD_NAME_EFFECTIVE_TO_DATE
ASSY.ITEM_NUMBER ASSY_ITEM,
ASSY.description Assy_Descr,
NVL(WD.QUANTITY,1) WD_Qty,
NVL(WD.UOM_CODE,ASSY.PRIMARY_UOM_CODE) WD_UOM,
WD.SERIAL_TRACKING_FLAG Serial_Tracked,
WV.COMPLETION_SUBINVENTORY_NAME Completion_Subinventory,
WO.OPERATION_SEQ_NUMBER Operation_Seq,
WO.OPERATION_NAME Operation_Name,
WO.OPERATION_TYPE Operation_Type,
WO.WORK_CENTER_NAME WORK_CENTER,
WO.COUNT_POINT_FLAG COUNT_POINT,
WO.AUTO_TRANSACT_FLAG Automatically_Transact,
WO.EFFECTIVE_FROM_DATE Oper_Start_Date,
WO.EFFECTIVE_TO_DATE Oper_End_Date,
wom.MATERIAL_SEQ_NUMBER Material_Seq,
— WOM.INVENTORY_ITEM_ID COMP_ITEM_ID,
Comp.Item_Number Comp_Item,
Comp.description Comp_Descr,
DECODE(WOM.BASIS_TYPE,1,’Variable’,’2′,’Fixed’,WOM.BASIS_TYPE) Basis,
WOM.COMPONENT_QUANTITY Comp_Qty,
WOM.UOM_CODE COMP_UOM,
WOM.YIELD_FACTOR Comp_Yield,
(SELECT meaning FROM fnd_lookup_values_vl where lookup_type = ‘EGP_WIP_SUP_TYPE’ and lookup_code = WOM.SUPPLY_TYPE) Comp_SUPPLY_TYPE,
WOM.SUPPLY_SUBINVENTORY_NAME Supply_Subinventory,
WOM.EFFECTIVITY_DATE Oper_Item_Start_Date,
WOM.DISABLE_DATE Oper_Item_End_Date
— WOR.RESOURCE_SEQ_NUMBER, WOR.RESOURCE_NAME, WOR.RESOURCE_TYPE
FROM WIS_WORK_DEFINITIONS_V WD,
WIS_WD_VERSIONS WV,
INV_ORG_PARAMETERS ORG,
EGP_SYSTEM_ITEMS_VL ASSY,
WIS_WD_OPERATIONS_V WO,
WIS_WD_OPERATION_MATERIALS_V WOM,
— WIS_WD_OPERATION_RESOURCES_V WOR,
EGP_SYSTEM_ITEMS_VL Comp
WHERE WD.WORK_DEFINITION_ID = WV.WORK_DEFINITION_ID
AND WV.VERSION_NUMBER = (SELECT MAX(WV.VERSION_NUMBER) FROM WIS_WD_VERSIONS WHERE (SYSDATE BETWEEN WV.EFFECTIVE_FROM_DATE AND NVL(EFFECTIVE_TO_DATE,SYSDATE+1)))
AND WD.ORGANIZATION_ID = ORG.organization_id
AND ORG.organization_code = :P_ORG_CODE
AND WD.INVENTORY_ITEM_ID = ASSY.INVENTORY_ITEM_ID
AND WD.ORGANIZATION_ID = ASSY.ORGANIZATION_ID
— AND ASSY.ITEM_NUMBER IN (‘AS6647331’)
AND WD.WORK_DEFINITION_ID = WO.WORK_DEFINITION_ID
AND WO.EFFECTIVE_TO_DATE IS NULL
AND WO.WORK_DEFINITION_ID = WOM.WORK_DEFINITION_ID
AND WO.WD_OPERATION_ID = WOM.WD_OPERATION_ID
AND WOM.DISABLE_DATE IS NULL
AND WOM.ORGANIZATION_ID = Comp.ORGANIZATION_ID
AND WOM.INVENTORY_ITEM_ID = Comp.INVENTORY_ITEM_ID
— AND WO.WD_OPERATION_ID = WOR.WD_OPERATION_ID
ORDER BY ORG.organization_code, WD.WORK_DEF_NAME, WO.OPERATION_SEQ_NUMBER, wom.MATERIAL_SEQ_NUMBER
Sample Report Output:
