How-toOracle E-Business Suite and Fusion Cloud SCMWork in Process (WIP) / Manufacturing

Oracle Work Order Tables: WIP_ENTITIES, WIP_DISCRETE_JOBS and the REST Route

Question
oracle work order tables

Also searched as

  • work order table in oracle
  • wip_entities wip_discrete_jobs join
  • rest api to get work order details
  • oracle ebs work order sql query

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.

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.

How to pull work order details

  1. 1Find 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.
  2. 2Join WIP_DISCRETE_JOBS on WIP_ENTITY_ID and ORGANIZATION_ID for the item, start quantity, completed quantity, status and scheduled dates.
  3. 3Join MTL_SYSTEM_ITEMS_B on PRIMARY_ITEM_ID and ORGANIZATION_ID to show item number and description.
  4. 4Decode 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.
  5. 5Add WIP_OPERATIONS for the routing steps and WIP_OPERATION_RESOURCES for resources, both keyed by WIP_ENTITY_ID.
  6. 6Add WIP_REQUIREMENT_OPERATIONS for the components required and quantity issued.
  7. 7Use WIP_TRANSACTIONS and WIP_MOVE_TRANSACTIONS for what was actually charged and moved.
  8. 8In Fusion, call the work orders REST resource under the fscmRestApi path instead of reading tables.

The main EBS tables and what they hold

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.

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

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.

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

Fusion Cloud SCM Manufacturing exposes work orders as a REST resource, normally reached under the fscmRestApi/resources/<version>/ 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.

GET https://<pod>.fa.<region>.oraclecloud.com/fscmRestApi/resources/<version>/workOrders?q=WorkOrderNumber="WO1001"&onlyData=true&expand=WorkOrderOperation

Try it here, free

Oracle EBS WIP Tables Reference

Open full tool

Lookup of Oracle EBS Work in Process tables with purpose and key columns, covering discrete jobs, operations, requirements, resources and interfaces.

Common 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.

How an ERP-grounded AI assistant handles this

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.

See ERPray

Frequently asked questions

What is the work order table in Oracle EBS?

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?

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?

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?

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.

Related

Error fix

Oracle Fusion Manufacturing: Work Order Cannot Complete or Close

A Fusion work order will not complete or close when operations are still open, required components are not issued, completion quantity or transactions are pending, or the work order is on hold. Close additionally depends on costing and variance processing. Check operations, material and pending transactions first, then status and cost.

How-to

Paginate And Authenticate Oracle Fusion REST API Calls Correctly

Oracle Fusion Cloud REST APIs authenticate with HTTP Basic Authentication, an integration user's credentials, over HTTPS by default, and paginate with limit and offset query parameters rather than a page number. Loop on the hasMore flag in the response until it returns false to retrieve a full result set.

How-to

How to Close an Inventory Accounting Period in Oracle EBS

Before closing an Inventory period, run the Pending Transactions report to clear every uncosted or erroring material transaction, then close through Inventory > Accounting Close Cycle > Inventory Accounting Periods. Inventory will refuse to close while pending transactions remain, and any that get force-closed into the next period will misstate both months' costed inventory value.

How-to

Oracle EBS Outside Processing and Production Scheduling

Outside processing (OSP) in Oracle EBS lets a discrete job send work to a supplier, such as plating or heat treat. You define an outside processing item, an OSP resource with a charge type, a department that owns it, and a routing operation that uses it. When the job is released, WIP drives a requisition to Purchasing; receiving against the PO charges the job.

Error fix

Fix FRM-40735: WHEN-VALIDATE-RECORD Trigger Raised Unhandled Exception ORA-06508

FRM-40735 with ORA-06508 almost always means a PL/SQL package used by the form was recompiled while your session still held the old cached state, or the trigger's own exception handling does not trap a real data error. Exit the form completely and re-enter it; if the error repeats for all users, recompile invalid objects on the database and check for a bad custom trigger.

Error fix

Oracle EBS Concurrent Manager Will Not Start: How to Fix It

A concurrent manager that will not start in Oracle EBS is almost always one of three things: the node registered in FND_NODES no longer matches the actual hostname (common after cloning), stale OS processes are blocking a fresh start, or the database and listener the manager connects to are unreachable. Check the internal manager log, verify the node name, clear stale processes, and restart with adcmctl.sh.

AI for ERP

AI for Oracle E-Business Suite, Without Leaving On-Prem

Hosting Oracle EBS 12.2 on-prem or in a private cloud? Add a private LLM over concurrent programs, interface tables and AP/PO data without moving to Fusion.

AI for ERP

AI for Oracle Cloud SCM and Manufacturing, Grounded in Your Planning Data

Add grounded AI to Oracle Cloud SCM and Fusion Manufacturing: demand plans, work orders, and supplier data, answered on-prem or in a private cloud with full query visibility.

Stuck on Oracle E-Business Suite and Fusion Cloud SCM?

Talk to engineers who work inside Oracle E-Business Suite and Fusion Cloud SCM every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

AI on your ERP

Integrations

Comparisons

Software

TermsOracle EBS Concurrent Program