OracleFree generatorPDF, Excel, Word exportOracle Fusion Cloud SCM

Oracle Fusion SCM BI Publisher SQL Query Generator

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.

Fill in

Inventory organization ID

Numeric ORGANIZATION_ID from your instance

Item number prefix

Used with LIKE

Subinventory code

Procurement BU ID

Days of PO history

Generated code

Download:

Item master (items in an organization)

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 = 300000012345678
AND    esib.ITEM_NUMBER LIKE 'FG-%'
ORDER BY esib.ITEM_NUMBER

On-hand quantity by subinventory

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 = 300000012345678
AND    oh.SUBINVENTORY_CODE = 'FGI'
GROUP BY oh.INVENTORY_ITEM_ID, oh.ORGANIZATION_ID, oh.SUBINVENTORY_CODE
ORDER BY oh.INVENTORY_ITEM_ID

Recent purchase order headers

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 = 300000054321000
AND    poh.CREATION_DATE >= SYSDATE - 90
ORDER BY poh.CREATION_DATE DESC

PO lines joined to item

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(+) = 300000012345678
AND    poh.PRC_BU_ID = 300000054321000
AND    poh.CREATION_DATE >= SYSDATE - 90

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.

Ask Fusion in plain English

See how ERPray answers these data questions read-only and shows the query it ran.

Optional. The tool and every export work without this. No spam.

How to use the generator

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.

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

What the queries read

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

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.

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

Frequently Asked Questions

Can I run these in any Oracle Fusion environment?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Oracle

More Oracle tools

See all

Back to All Tools