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

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" Enter fullscreen mode Exit fullscreen mode 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" Enter fullscreen mode Exit fullscreen mode 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" Enter fullscreen mode Exit fullscreen mode 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" Enter fullscreen mode Exit fullscreen mode 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" Enter fullscreen mode Exit fullscreen mode 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" Enter fullscreen mode Exit fullscreen mode 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" Enter fullscreen mode Exit fullscreen mode 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 Enter fullscreen mode Exit fullscreen mode 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.