Oracle Fusion vs EBS: 7 SQL Patterns Every BIP Report Developer Needs

David Jiang — Fri, 15 May 2026 06:05:21 +0000

If you've spent any time writing BIP (or OTBI) reports against Oracle Fusion Procurement Cloud, you've probably hit a wall that didn't exist back on EBS: half the columns you "remember" don't exist anymore, and the display names you actually want to show live on a different table than the ID you're joining on.

The result is a familiar loop:

Write the SQL the way you'd have written it in EBS
ORA-00904: invalid identifier
Hunt through docs.oracle.com/en/cloud/saas/procurement/25d/oedmp/ for the right column
Find a view that has the column the base table doesn't
Repeat for the next field

Multiply by 30 columns in a typical report. That's 30 minutes to 2 hours, per report.

This post is the cheat sheet I wish I'd had on day one — the 7 ID → Display join patterns that cover ~80% of Procurement BIP report needs, written in traditional Oracle WHERE-clause style (because when you're debugging 15-table joins, ANSI JOIN syntax fragments your relationship map).

A note on style: why scalar subqueries

Each of these patterns is a scalar subquery in the SELECT list, not a direct join in the FROM/WHERE clause. Two reasons:

No Fan Trap risk. A direct join to a one-to-many table can multiply your row count silently. The scalar subquery + ROWNUM = 1 guarantees exactly one display value per source row, no matter what the lookup table looks like.
The driving table stays clean. Your FROM clause stays as the inventory of "real" tables; the lookups stay where they're used.

Oracle's optimizer handles scalar subqueries efficiently when the lookup column has a PK index (every one of these does). For >500K-row reports, profile both — but for typical BIP needs, the scalar form is the safer default.

1. Supplier Name — from `VENDOR_ID`

(SELECT psv.vendor_name
 FROM   poz_suppliers_v             psv
 WHERE  psv.vendor_id               = source.vendor_id
 AND    ROWNUM                      = 1)              "Supplier"

The trap: POZ_SUPPLIERS (the base table) does not have a VENDOR_NAME column. The name lives on HZ_PARTIES.PARTY_NAME and is exposed via the view POZ_SUPPLIERS_V. Always use the view for names; use the base table only for flags like ENABLED_FLAG that the view may not surface.

If you're coming from EBS: forget AP_SUPPLIERS and PO_VENDORS. Neither exists in Fusion.

2. Buyer / Person Name — from `PERSON_ID` or `AGENT_ID`

(SELECT pn.full_name
 FROM   per_person_names_f          pn
 WHERE  pn.person_id                = source.agent_id
 AND    pn.name_type                = 'GLOBAL'
 AND    SYSDATE BETWEEN pn.effective_start_date
                    AND pn.effective_end_date
 AND    ROWNUM                      = 1)              "Buyer"

The trap: EBS used PER_ALL_PEOPLE_F.FULL_NAME. Fusion uses PER_PERSON_NAMES_F (note the _NAMES_). And because the table is date-effective with multiple NAME_TYPE rows per person, you need both the NAME_TYPE = 'GLOBAL' filter and the SYSDATE BETWEEN clause to get a single current name.

3. Item Number — from `ITEM_ID`

(SELECT item.item_number
 FROM   egp_system_items_b          item
 WHERE  item.inventory_item_id      = source.item_id
 AND    ROWNUM                      = 1)              "Item Number"

The trap: PO_LINES_ALL.ITEM_NUMBER doesn't exist. Only ITEM_ID does. And if you remember MTL_SYSTEM_ITEMS_B from EBS — that's gone. Fusion uses EGP_SYSTEM_ITEMS_B.

For some contexts (like Sourcing — PON_AUCTION_ITEM_PRICES_ALL) you'll also need the INV_ORG_ID for the item join, because items are organization-scoped.

4. Category Name — from `CATEGORY_ID`

(SELECT cat_tl.category_name
 FROM   egp_categories_tl           cat_tl
 WHERE  cat_tl.category_id          = source.category_id
 AND    cat_tl.language             = USERENV('LANG')
 AND    ROWNUM                      = 1)              "Category"

The trap: The display name lives on the _TL (translation) table. Always filter by LANGUAGE = USERENV('LANG') so users get their localized labels. If you forget the filter, you get N copies of every row (one per language).

5. UOM Display — from `UOM_CODE` (the B → TL pattern)

This is the trap that catches everyone. The UOM display name doesn't live on the table you'd expect, and the _TL table doesn't have the code you'd join on. You have to go through the _B table first.

(SELECT uom_tl.unit_of_measure
 FROM   inv_units_of_measure_b      uom_b   ,
        inv_units_of_measure_tl     uom_tl
 WHERE  uom_b.uom_code              = source.uom_code
 AND    uom_b.unit_of_measure_id    = uom_tl.unit_of_measure_id
 AND    uom_tl.language             = USERENV('LANG')
 AND    ROWNUM                      = 1)              "UOM"

Why the two-step? UOM_CODE (like "Ea", "Kg", "Box") lives on INV_UNITS_OF_MEASURE_B. The display string (like "Kilogram", "Each") lives on INV_UNITS_OF_MEASURE_TL. The two are joined by UNIT_OF_MEASURE_ID. The _TL table does not have UOM_CODE.

Common failure attempts I've personally written:

paip.unit_of_measure → ORA-00904 (no such column)
paip.uom → ORA-00904 (no such column)
Join paip.uom_code = uom_tl.uom_code → ORA-00904 (TL has no UOM_CODE)

The B → TL chain is the only thing that works.

6. BU Name — from `PRC_BU_ID` or `REQ_BU_ID`

(SELECT bu.bu_name
 FROM   fun_all_business_units_v    bu
 WHERE  bu.bu_id                    = source.prc_bu_id
 AND    ROWNUM                      = 1)              "Proc BU"

Procurement BIP reports almost always need to filter or display the BU. FUN_ALL_BUSINESS_UNITS_V is your friend. The view exposes BU_NAME, STATUS, and PRIMARY_LEDGER_ID.

7. Inventory Organization Name — from `ORGANIZATION_ID`

(SELECT iod.organization_name
 FROM   inv_organization_definitions_v   iod
 WHERE  iod.organization_id              = source.organization_id
 AND    ROWNUM                           = 1)         "Inv Org"

The _V view has the display name and is one of the rare places where the view-only data lines up neatly with the base table's PK. Use it freely.

Putting it all together

Here's a 12-line snippet that uses 5 of the 7 patterns to produce a "PO Line with everything humans want to see" query:

SELECT COUNT(*) OVER()                                          TOTAL_COUNT       ,
       pha.segment1                                             "PO Number"       ,
       pla.line_num                                             "Line"            ,
       (SELECT item.item_number
        FROM   egp_system_items_b          item
        WHERE  item.inventory_item_id      = pla.item_id
        AND    ROWNUM                      = 1)                 "Item Number"     ,
       pla.item_description                                     "Description"     ,
       (SELECT cat_tl.category_name
        FROM   egp_categories_tl           cat_tl
        WHERE  cat_tl.category_id          = pla.category_id
        AND    cat_tl.language             = USERENV('LANG')
        AND    ROWNUM                      = 1)                 "Category"        ,
       (SELECT uom_tl.unit_of_measure
        FROM   inv_units_of_measure_b      uom_b   ,
               inv_units_of_measure_tl     uom_tl
        WHERE  uom_b.uom_code              = pla.uom_code
        AND    uom_b.unit_of_measure_id    = uom_tl.unit_of_measure_id
        AND    uom_tl.language             = USERENV('LANG')
        AND    ROWNUM                      = 1)                 "UOM"             ,
       pla.quantity                                             "Qty"             ,
       pla.unit_price                                           "Unit Price"
FROM
       po_headers_all                  pha   ,
       po_lines_all                    pla
WHERE 1                                = 1
    AND pha.po_header_id               = pla.po_header_id
ORDER BY
       pha.segment1                                             ,
       pla.line_num

Notice:

COUNT(*) OVER() as the first column — a BIP idiom that lets the front-end report show "Total Rows: 1,234" without a second query
Vertical alignment of aliases, equal signs, and column names — your future self thanks you when debugging
Traditional Oracle WHERE-clause joins instead of ANSI — the WHERE clause becomes the ERD diagram

What didn't fit in this post

The other recipes I reach for regularly — Active BPAs by BU, Requisitions waiting in the PR Pool (EXT029-style), BPAs Expiring Soon, Supplier Master Search, Sourcing Negotiation Template inventory, and the ASE security tables (FUSION.ASE_USER_B etc.) for the dreaded "this user can't approve a PR" debugging session — would each need a similar deep dive.

I packaged the lot, plus a 30-row EBS-to-Fusion column mistake reference and a pre-flight checklist, into a PDF Cookbook for anyone who'd rather paste than hunt:

👉 Oracle Fusion Procurement BIP SQL Cookbook — v0.1 Foundations

Launch week price USD 19. v0.2 (PR-to-PO traceability, Receipts, Catalog hierarchy, DFF extraction, Approval decomposition) ships free to v0.1 buyers.

If you've hit a different ORA-00904 that bit you on Fusion, drop it in the comments — I'll add the fix to v0.2's appendix.

DEV Community: David Jiang

Oracle Fusion vs EBS: 7 SQL Patterns Every BIP Report Developer Needs

A note on style: why scalar subqueries

1. Supplier Name — from VENDOR_ID

2. Buyer / Person Name — from PERSON_ID or AGENT_ID

3. Item Number — from ITEM_ID

4. Category Name — from CATEGORY_ID

5. UOM Display — from UOM_CODE (the B → TL pattern)

6. BU Name — from PRC_BU_ID or REQ_BU_ID

7. Inventory Organization Name — from ORGANIZATION_ID