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 = 1guarantees 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.
Top comments (0)