# Oracle EBS SQL Query Generator for Items and WIP Jobs

> Source: https://netray.co/tools/oracle-ebs-sql-query-generator-items-wip-jobs
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build ready-to-run SELECT statements over MTL_SYSTEM_ITEMS_B, WIP_DISCRETE_JOBS and on-hand quantity tables.

## Category

developer

## Erp

Oracle E-Business Suite

## Industry

Discrete Manufacturing

## Audience

- Oracle EBS consultant
- Report developer
- Support analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Organization ID

**Key:** org_id

**Type:** number

**Default:** 207

**Help:** ORGANIZATION_ID from MTL_PARAMETERS

#### Item number pattern

**Key:** item_like

**Type:** text

**Default:** PUMP%

**Help:** Use % as wildcard

#### Job status code

**Key:** status

**Type:** select

**Options:**

- 1
- 3
- 4
- 5
- 6
- 7
- 12

**Default:** 3

**Help:** 3 is Released

#### Scheduled start from

**Key:** from_date

**Type:** date

**Default:** 2026-10-01

#### Scheduled start to

**Key:** to_date

**Type:** date

**Default:** 2026-12-31

#### Subinventory

**Key:** subinv

**Type:** text

**Default:** STORES

**Blocks:**

#### Item master query

**Body:** SELECT msib.inventory_item_id,
       msib.segment1 AS item_number,
       msib.description,
       msib.primary_uom_code,
       msib.planning_make_buy_code,
       msib.inventory_item_status_code
FROM   mtl_system_items_b msib
WHERE  msib.organization_id = {{org_id}}
AND    msib.segment1 LIKE '{{item_like}}'
ORDER BY msib.segment1;

#### Discrete jobs query

**Body:** SELECT we.wip_entity_name AS job_name,
       msib.segment1 AS assembly,
       wdj.status_type,
       wdj.class_code,
       wdj.start_quantity,
       wdj.quantity_completed,
       wdj.quantity_scrapped,
       wdj.scheduled_start_date,
       wdj.scheduled_completion_date
FROM   wip_discrete_jobs wdj
JOIN   wip_entities we
       ON we.wip_entity_id = wdj.wip_entity_id
      AND we.organization_id = wdj.organization_id
JOIN   mtl_system_items_b msib
       ON msib.inventory_item_id = wdj.primary_item_id
      AND msib.organization_id = wdj.organization_id
WHERE  wdj.organization_id = {{org_id}}
AND    wdj.status_type = {{status}}
AND    msib.segment1 LIKE '{{item_like}}'
AND    wdj.scheduled_start_date BETWEEN TO_DATE('{{from_date}}', 'YYYY-MM-DD')
                                    AND TO_DATE('{{to_date}}', 'YYYY-MM-DD')
ORDER BY wdj.scheduled_start_date;

#### On-hand by subinventory

**Body:** SELECT msib.segment1 AS item_number,
       moqd.subinventory_code,
       SUM(moqd.transaction_quantity) AS on_hand_qty
FROM   mtl_onhand_quantities_detail moqd
JOIN   mtl_system_items_b msib
       ON msib.inventory_item_id = moqd.inventory_item_id
      AND msib.organization_id = moqd.organization_id
WHERE  moqd.organization_id = {{org_id}}
AND    moqd.subinventory_code = '{{subinv}}'
AND    msib.segment1 LIKE '{{item_like}}'
GROUP BY msib.segment1, moqd.subinventory_code
ORDER BY msib.segment1;

**Notes:** These queries are read-only SELECT statements. Test them on a non-production instance or a read-only schema first. Table and column names are standard for 11i and R12, but verify them in your version, apply the right operating unit or organization security, and never write to these tables directly.

## Content

**Intro:** Support analysts and report developers on Oracle EBS write the same few queries again and again: item master lookups, open jobs and on-hand by subinventory. This generator fills in the joins and filters so you start from a working statement.

**Sections:**

#### How to use this generator

**Body:** Enter your organization ID, an item pattern, a job status and a date range, and the SQL updates immediately. Copy it into SQL Developer, TOAD or a custom report. Find the ORGANIZATION_ID by querying MTL_PARAMETERS for your organization code. Use the status code from the discrete job statuses reference to filter for released, complete or on-hold jobs.

#### How the queries are built

**Body:** MTL_SYSTEM_ITEMS_B holds one row per item per organization, so every join must include ORGANIZATION_ID as well as INVENTORY_ITEM_ID. WIP_DISCRETE_JOBS holds the job data, and WIP_ENTITIES provides the job name. On-hand quantity is the sum of MTL_ONHAND_QUANTITIES_DETAIL rows, which are held at lot, locator and subinventory level.

**Bullets:**

- Always join items by item ID and organization ID
- Join WIP_ENTITIES for the job name
- Sum on-hand detail for quantity
- Use views such as MTL_ITEM_QUANTITIES where you can

#### Performance and safety

**Body:** Filter on the organization first, and avoid unbounded queries over transaction tables. In production, prefer a read-only reporting replica or a restricted schema so your queries cannot interfere with the Cost Manager or WIP processing. Where supported, use the seeded views, which respect multi-org and security setups better than raw tables.

#### Common mistakes

**Body:** Omitting the organization join is the biggest error, as it multiplies rows across plants. Another is comparing STATUS_TYPE to text instead of the numeric code. Finally, people sometimes update tables to fix a status, which bypasses validation and accounting. Use the application forms or supported APIs for changes.

**Cta:** If you want to ask these questions in plain English instead of writing SQL, we can set up a read-only assistant over your EBS data that shows the query it ran.

**Faq:**

#### How do I find the organization ID in Oracle EBS?

**Answer:** Query MTL_PARAMETERS by ORGANIZATION_CODE to get ORGANIZATION_ID. You can also see it in HR organization definitions. Use the ID in all item and WIP queries.

#### Why do I get duplicate item rows?

**Answer:** MTL_SYSTEM_ITEMS_B has a row for each organization. If you do not filter or join on ORGANIZATION_ID, the same item appears once per plant.

#### Is it safe to run these on production?

**Answer:** They are read-only, but heavy queries can still affect performance. Run them during quiet hours, add filters, or use a read-only replica if your team has one.

#### Does it work on R12 and 12.2?

**Answer:** The tables used are present in both 11i and R12, including 12.2. Multi-org and edition-based changes can affect some views, so verify in your own instance.

## Search terms

oracle ebs sql query items, mtl_system_items_b sql query, wip_discrete_jobs sql query, oracle ebs open jobs query, oracle on hand quantity sql, oracle apps wip job query, oracle ebs item master sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json