In Oracle Fusion Manufacturing Cloud, Work Orders play a critical role in managing production activities on the shop floor. A work order is created to produce or build a specific product and provides detailed instructions on what needs to be produced, how it should be manufactured, how many units are required, and when the production should take place.
Each work order contains important information such as the product item, work definition, operations, resources, components, quantities, and scheduled production dates. These details guide manufacturing teams through the entire production process. Oracle Manufacturing Cloud supports Discrete, Process, and Flow Manufacturing, making it suitable for a wide range of industries.
Work orders can be created from multiple sources, including Supply Planning, Sales Orders for back-to-back make items, sales orders for configured items, and contract manufactured items. They can also be created manually or through the FBDI import process.
In addition to automated creation, work orders can also be created manually by users or imported through the FBDI (File-Based Data Import) process.
There is a common requirement for the operations team to review all open work orders along with their operations, material requirements, and resource requirements to effectively manage shop floor activities.
Additionally, this report can be used to validate material requirements against the assembly Bill of Materials (BOM). By comparing work order component requirements with the BOM, organizations can identify missing or incomplete material requirements early in the production process and prevent delays on the shop floor.
Here is the SQL Query to get all the Open Work Orders with Operations, Material, and Resource Requirements details. The report can be run a specific manufacturing plant.
SELECT –WO.WORK_ORDER_ID, WO.ORGANIZATION_ID, WO.INVENTORY_ITEM_ID,
ORG.organization_code Org_Code,
WO.work_order_number Work_Order,
wo.WORK_ORDER_DESCRIPTION,
WO.system_status_code WO_STATUS,
TO_CHAR(WO.RELEASED_DATE,’MM/DD/YYYY HH24:MI:SS’) RELEASED_DATE,
–CLOSED_DATE
TO_CHAR(WO.PLANNED_START_DATE,’MM/DD/YYYY HH24:MI:SS’) Scheduled_Start_Date,
TO_CHAR(WO.PLANNED_COMPLETION_DATE,’MM/DD/YYYY HH24:MI:SS’) Scheduled_Comp_Date,
(SELECT ITEM_NUMBER FROM EGP_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = WO.ORGANIZATION_ID AND INVENTORY_ITEM_ID = WO.INVENTORY_ITEM_ID) Assembly_Item,
WO.PLANNED_START_QUANTITY WO_QTY,
WO.UOM_CODE WO_UOM,
— (SELECT meaning FROM fnd_lookup_values_vl where lookup_type = ‘ORA_WIE_WO_SCHEDULING_METHOD’ and lookup_code = WO.SCHEDULING_METHOD) Scheduling_Method,
WO.FIRM_PLANNED_FLAG FIRM,
WO.WORK_METHOD_CODE Work_Method,
WO.WORK_ORDER_TYPE, — Std, Rework, NonStd, Tranform
WO.WORK_ORDER_SUB_TYPE,
WD.WORK_DEF_NAME WORK_DEF_NAME,
WD.STRUCTURE_DISPLAY_NAME ITEM_STRUCTURE,
WO.COMPL_SUBINVENTORY_CODE COMPL_SUBINVENTORY,
— WO.COMPL_LOCATOR_ID
DECODE(WO.WORK_DEFINITION_ID,NULL,’Push’,’Based on Work Definition’) DEFAULT_SUPPLY_TYPE, WOO.OPERATION_SEQ_NUMBER OPERATION_SEQ,
WOO.OPERATION_NAME OPERATION,
WOO.OPERATION_TYPE, WOO.WORK_CENTER_NAME WORK_CENTER, WOO.COUNT_POINT_OPERATION_FLAG COUNT_POINT, WOO.AUTO_TRANSACT_FLAG,WOO.OP_YIELD_FACTOR Operation_Yield,
WOO.COMPLETED_QUANTITY COMPLETED_QTY,
— WOM.MATERIAL_TYPE,
WOM.MATERIAL_SEQ_NUMBER,
— WOM.ORGANIZATION_ID,
— WOM.INVENTORY_ITEM_ID,
Comp.ITEM_NUMBER Comp_Item ,
Comp.description Comp_Descr,
DECODE(WOM.BASIS_TYPE,1,’Variable’,2,’Fixed’,WOM.BASIS_TYPE) BASIS_TYPE,
WOM.QUANTITY_PER_PRODUCT Qty_Per_Prod,
WOM.QUANTITY Required_Qty,
— WOM.PICKED_QUANTITY, WOM.ALLOCATED_QUANTITY, WOM.REMAINING_ALLOCATED_QUANTITY
WOM.ISSUED_QUANTITY,
WOM.UOM_CODE,
WOM.REQUIRED_DATE,
(SELECT meaning FROM fnd_lookup_values_vl where lookup_type = ‘EGP_WIP_SUP_TYPE’ and lookup_code = WOM.SUPPLY_TYPE) Supply_Type,
WOM.YIELD_FACTOR ITEM_YIELD,
WOM.SUPPLY_SUBINVENTORY,
WOR.RESOURCE_SEQ_NUMBER Resource_Seq, (SELECT resource_name FROM WIS_RESOURCES_VL where RESOURCE_ID = WOR.RESOURCE_ID) Resource_Name,
DECODE(WOR.BASIS_TYPE,1,’Variable’,2,’Fixed’,WOM.BASIS_TYPE) Res_Basis, WOR.USAGE_RATE Res_Usage_Rage, WOR.REQUIRED_USAGE Res_Req_Usage,
WOR.UOM_CODE Res_UOM, WOR.CHARGE_TYPE, WOR.SCHEDULED_FLAG
FROM WIE_WORK_ORDERS_V WO,
WIE_WO_OPERATIONS_V WOO,
WIE_WO_OPERATION_MATERIALS_V WOM,
EGP_SYSTEM_ITEMS_VL Comp,
INV_ORG_PARAMETERS ORG,
WIS_WORK_DEFINITIONS_V WD,
WIE_WO_OPERATION_RESOURCES_V WOR
WHERE WO.organization_id = ORG.organization_id
AND ORG.organization_code = :P_ORG_CODE
AND WO.user_status_code IN ( ‘ORA_RELEASED’, ‘ORA_ON_HOLD’, ‘ORA_UNRELEASED’ ) — WIE_WO_STATUSES_VL
— AND WO.WORK_METHOD_CODE = ‘DISCRETE_MANUFACTURING’ — Discrete WOs only for now–, since Bakery work orders are not calculating correctly
AND WO.work_order_id = WOO.work_order_id
AND WOO.work_order_id = WOM.work_order_id (+)
AND WOO.wo_operation_id = WOM.wo_operation_id (+)
AND WOM.ORGANIZATION_ID = Comp.ORGANIZATION_ID (+)
AND WOM.INVENTORY_ITEM_ID = Comp.INVENTORY_ITEM_ID(+)
AND WO.WORK_DEFINITION_ID = WD.WORK_DEFINITION_ID (+)
AND WOO.work_order_id = WOM.work_order_id (+)
AND WOO.wo_operation_id = WOR.wo_operation_id (+)
ORDER BY ORG.organization_code, WO.work_order_number, WOO.OPERATION_SEQ_NUMBER, WOM.MATERIAL_SEQ_NUMBER,WOR.RESOURCE_SEQ_NUMBER
Sample Report Output:
