# Oracle Fusion SCM BI Publisher SQL Query Generator

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

## Short answer

Fill in organization, item and BU filters and get starter SQL for item, on-hand and open PO data models.

## Category

oracle

## Erp

Oracle Fusion Cloud SCM

## Audience

- Oracle Cloud consultant
- BI Publisher developer
- Technical analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Inventory organization ID

**Key:** org_id

**Type:** text

**Default:** 300000012345678

**Help:** Numeric ORGANIZATION_ID from your instance

#### Item number prefix

**Key:** item_prefix

**Type:** text

**Default:** FG-

**Help:** Used with LIKE

#### Subinventory code

**Key:** subinv

**Type:** text

**Default:** FGI

#### Procurement BU ID

**Key:** bu_id

**Type:** text

**Default:** 300000054321000

#### Days of PO history

**Key:** days

**Type:** number

**Default:** 90

**Blocks:**

#### Item master (items in an organization)

**Body:** SELECT esib.INVENTORY_ITEM_ID,
       esib.ORGANIZATION_ID,
       esib.ITEM_NUMBER,
       esib.PRIMARY_UOM_CODE,
       esib.INVENTORY_ITEM_STATUS_CODE,
       esib.CREATION_DATE
FROM   EGP_SYSTEM_ITEMS_B esib
WHERE  esib.ORGANIZATION_ID = {{org_id}}
AND    esib.ITEM_NUMBER LIKE '{{item_prefix}}%'
ORDER BY esib.ITEM_NUMBER

#### On-hand quantity by subinventory

**Body:** SELECT oh.INVENTORY_ITEM_ID,
       oh.ORGANIZATION_ID,
       oh.SUBINVENTORY_CODE,
       SUM(oh.PRIMARY_TRANSACTION_QUANTITY) AS ONHAND_QTY
FROM   INV_ONHAND_QUANTITIES_DETAIL oh
WHERE  oh.ORGANIZATION_ID = {{org_id}}
AND    oh.SUBINVENTORY_CODE = '{{subinv}}'
GROUP BY oh.INVENTORY_ITEM_ID, oh.ORGANIZATION_ID, oh.SUBINVENTORY_CODE
ORDER BY oh.INVENTORY_ITEM_ID

#### Recent purchase order headers

**Body:** SELECT poh.PO_HEADER_ID,
       poh.SEGMENT1 AS PO_NUMBER,
       poh.DOCUMENT_STATUS,
       poh.VENDOR_ID,
       poh.CREATION_DATE
FROM   PO_HEADERS_ALL poh
WHERE  poh.PRC_BU_ID = {{bu_id}}
AND    poh.CREATION_DATE >= SYSDATE - {{days}}
ORDER BY poh.CREATION_DATE DESC

#### PO lines joined to item

**Body:** SELECT poh.SEGMENT1 AS PO_NUMBER,
       pol.LINE_NUM,
       pol.ITEM_ID,
       esib.ITEM_NUMBER,
       pol.QUANTITY
FROM   PO_HEADERS_ALL poh,
       PO_LINES_ALL pol,
       EGP_SYSTEM_ITEMS_B esib
WHERE  pol.PO_HEADER_ID = poh.PO_HEADER_ID
AND    esib.INVENTORY_ITEM_ID(+) = pol.ITEM_ID
AND    esib.ORGANIZATION_ID(+) = {{org_id}}
AND    poh.PRC_BU_ID = {{bu_id}}
AND    poh.CREATION_DATE >= SYSDATE - {{days}}

**Notes:** Starter SQL for BI Publisher data models, not production reports. Verify table and column names against the Tables and Views guide for your release, test with a small filter first, and remember that data security is applied by your report data model and role, not by this text.

## Content

**Intro:** Most custom Oracle Fusion reports start as a BI Publisher data model with a SQL query against base tables. This generator produces starter queries for the three most requested datasets so you can paste, adapt and test.

**Sections:**

#### How to use the generator

**Body:** Enter your inventory organization ID, a subinventory, the procurement BU ID and a lookback window. The code blocks update instantly. Copy a block into a new SQL data set in BI Publisher and run it against a small filter first. Organization and BU IDs are numeric values from your own instance, found in setup or from a quick query on the organization tables.

**Bullets:**

- Replace sample IDs with your own
- Start with narrow filters
- Add joins one table at a time

#### What the queries read

**Body:** EGP_SYSTEM_ITEMS_B holds item definitions by organization. INV_ONHAND_QUANTITIES_DETAIL stores on-hand balances in the primary unit of measure. PO_HEADERS_ALL and PO_LINES_ALL hold purchasing documents, with the header procurement BU in PRC_BU_ID. These are shared across modules, so confirm you are reading the right columns for your release.

#### Common mistakes

**Body:** The most common issue is forgetting organization filters and returning every row in the instance. Second is summing quantities in mixed units of measure. Third is building reports directly on tables when a supported subject area in OTBI or a REST resource would be easier to maintain across quarterly updates.

**Bullets:**

- Always filter by organization or BU
- Check UOM before summing
- Prefer OTBI for ad hoc analysis, BIP for formatted output

**Cta:** If your team writes many of these queries, we can show how a read-only AI assistant over your Fusion data answers the same questions in plain English while showing the SQL it ran.

**Faq:**

#### Can I run these in any Oracle Fusion environment?

**Answer:** Only where you have access to build BI Publisher data models and the right roles. Many pods restrict direct table access in production, so validate in a test environment and follow your organization's rules on custom reporting.

#### Why use base tables instead of OTBI?

**Answer:** OTBI subject areas are easier and safer for ad hoc analysis. Base table SQL gives full control and pixel perfect layouts in BI Publisher, but takes more maintenance after quarterly updates.

#### Where do I find my organization ID?

**Answer:** Query the inventory organization parameters table or use the Manage Inventory Organizations task and a REST call for details. The numeric ID differs per instance, which is why the generator uses a placeholder.

#### Are these queries tuned?

**Answer:** No. They are starting points. Check execution plans on large tables, add date and organization filters, and avoid selecting columns you do not need in production data models.

## Search terms

oracle fusion sql query on hand quantity, oracle fusion egp_system_items_b query, oracle fusion bip data model sql, oracle fusion open purchase orders sql, oracle cloud scm sql queries, oracle fusion inv_onhand_quantities_detail, oracle fusion po_headers_all query, bi publisher sql oracle fusion

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