# Oracle Work Order Tables: WIP_ENTITIES, WIP_DISCRETE_JOBS and the REST Route > Source: https://netray.co/erp-help/oracle-work-order-tables-wip-entities-discrete-jobs-fusion-rest > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer In Oracle E-Business Suite, a discrete work order (job) is stored in WIP_ENTITIES (the identity and name) and WIP_DISCRETE_JOBS (the header detail such as item, quantities, status and dates), with operations in WIP_OPERATIONS, resources in WIP_OPERATION_RESOURCES and components in WIP_REQUIREMENT_OPERATIONS. In Fusion Cloud, do not query tables directly; use the work orders REST resource or a BI report. ## Kind how-to ## System oracle-ebs ## Erp Oracle E-Business Suite and Fusion Cloud SCM ## Module Work in Process (WIP) / Manufacturing ## Query oracle work order tables ## Alternate Queries - work order table in oracle - wip_entities wip_discrete_jobs join - rest api to get work order details - oracle ebs work order sql query ## Applies To Oracle E-Business Suite 12.1 and 12.2 discrete manufacturing. Fusion Cloud SCM Manufacturing for the REST section. Verify column names against your release. ## Steps ### How to pull work order details **Items:** - Find the job in WIP_ENTITIES by WIP_ENTITY_NAME and ORGANIZATION_ID. The WIP_ENTITY_ID is the key used by every other WIP table. - Join WIP_DISCRETE_JOBS on WIP_ENTITY_ID and ORGANIZATION_ID for the item, start quantity, completed quantity, status and scheduled dates. - Join MTL_SYSTEM_ITEMS_B on PRIMARY_ITEM_ID and ORGANIZATION_ID to show item number and description. - Decode STATUS_TYPE (3 is Released, 4 Complete, 12 Closed, and so on) with the WIP_JOB_STATUS lookup, or the meaning in the job form. - Add WIP_OPERATIONS for the routing steps and WIP_OPERATION_RESOURCES for resources, both keyed by WIP_ENTITY_ID. - Add WIP_REQUIREMENT_OPERATIONS for the components required and quantity issued. - Use WIP_TRANSACTIONS and WIP_MOVE_TRANSACTIONS for what was actually charged and moved. - In Fusion, call the work orders REST resource under the fscmRestApi path instead of reading tables. ## Sections ### The main EBS tables and what they hold **Body:** WIP_ENTITIES is the shared identity table for discrete jobs, repetitive schedules and flow schedules; its ENTITY_TYPE says which. WIP_DISCRETE_JOBS holds the discrete job header: primary item, start quantity, quantity completed and scrapped, class code, status and scheduled start and completion dates. Below the header, WIP_OPERATIONS stores the routing operations copied to the job, WIP_OPERATION_RESOURCES the resource usage per operation, and WIP_REQUIREMENT_OPERATIONS the material requirements with required and issued quantities. Actual activity is in WIP_TRANSACTIONS (issues, completions, resource charges) and WIP_MOVE_TRANSACTIONS (operation moves). Cost roll-ups appear in WIP_PERIOD_BALANCES. Always join on both WIP_ENTITY_ID and ORGANIZATION_ID. The same ID space is shared across organizations in joins to item data, and missing the organization filter returns wrong item descriptions. **Code:** SELECT we.wip_entity_name, msi.segment1 item, wdj.status_type, wdj.start_quantity, wdj.quantity_completed, wdj.scheduled_start_date, wdj.scheduled_completion_date FROM wip_entities we JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id = we.wip_entity_id AND wdj.organization_id = we.organization_id JOIN mtl_system_items_b msi ON msi.inventory_item_id = wdj.primary_item_id AND msi.organization_id = wdj.organization_id WHERE we.organization_id = :org_id AND we.wip_entity_name = :job_name ### Operations and components query **Body:** For a shop-floor view, drive from the job and fan out. Operations give sequence number, department and the quantity in each queue, run, to move or scrap step. Requirements give inventory item, required quantity, quantity issued and supply type. Keep reports read-only and run them against a reporting copy where possible. Writing to WIP tables directly corrupts costing; use the open interfaces (WIP_JOB_SCHEDULE_INTERFACE for job creation, WIP_MOVE_TXN_INTERFACE for moves) or public APIs. **Code:** SELECT wo.operation_seq_num, wo.department_id, wo.quantity_in_queue, wo.quantity_running, wo.quantity_completed FROM wip_operations wo WHERE wo.wip_entity_id = :wip_entity_id AND wo.organization_id = :org_id ORDER BY wo.operation_seq_num ### Getting work order details by REST in Fusion **Body:** Fusion Cloud SCM Manufacturing exposes work orders as a REST resource, normally reached under the fscmRestApi/resources// path with a workOrders collection, supporting query parameters such as q for filters, limit, offset, fields and expand for child collections like operations and materials. The exact version string and child resource names are in Oracle's REST API for Oracle Fusion Cloud SCM guide for your release, so confirm them there. Direct table access to Fusion is not available to customers, and Fusion table names differ from EBS. For bulk reporting use a BI Publisher report or OTBI subject area. For authentication and pagination see the Fusion REST page on this site. **Code:** - GET https://.fa..oraclecloud.com/fscmRestApi/resources//workOrders?q=WorkOrderNumber="WO1001"&onlyData=true&expand=WorkOrderOperation ## Pitfalls - Joining WIP tables without ORGANIZATION_ID, which mixes organizations. - Treating STATUS_TYPE numbers as obvious; decode them with the lookup rather than guessing. - Updating WIP tables directly instead of using interfaces or APIs, which breaks costing and inventory. - Expecting EBS table names to exist in Fusion Cloud. - Using SELECT * on large transaction tables during production hours. ## Ai Angle Writing the join is easy once you know the tables; the harder part is asking it in business terms. An assistant grounded in the EBS schema (ERPray with read-only access, showing the SQL it ran) can answer questions like which released jobs are late or short of components and display the query so a DBA can review it. For EBS sites on-premises, the model can be hosted locally and run against a reporting copy. A saved query or BI Publisher report is enough for one fixed report. ## Faq ### What is the work order table in Oracle EBS? **Answer:** There is no single table. Identity is in WIP_ENTITIES, and discrete job header data is in WIP_DISCRETE_JOBS. Operations, resources and components sit in WIP_OPERATIONS, WIP_OPERATION_RESOURCES and WIP_REQUIREMENT_OPERATIONS, all linked by WIP_ENTITY_ID. ### How do I get work order details through a REST API? **Answer:** In Fusion Cloud SCM, use the work orders resource under the fscmRestApi path, with query parameters to filter and expand operations. Plain EBS has no built-in WIP REST service; you can expose a PL/SQL API or a custom service through Integrated SOA Gateway. ### What do WIP_DISCRETE_JOBS status values mean? **Answer:** STATUS_TYPE is numeric: for example 1 Unreleased, 3 Released, 4 Complete, 6 On Hold, 7 Cancelled, 12 Closed. Confirm against the lookup in your instance, as additional statuses exist for pending and failed close. ### Can I insert work orders by SQL? **Answer:** No. Use the WIP_JOB_SCHEDULE_INTERFACE open interface with the Work Order Interface concurrent program, or the supported APIs. Direct inserts skip validation and costing and will corrupt WIP balances. ## Search terms oracle work order tables, work order table in oracle, wip_entities, wip_discrete_jobs, oracle ebs wip tables, oracle work order sql query, rest api to get work order details, fusion work order rest api, wip_operations wip_requirement_operations --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json