PO Details SQL Script in Oracle Fusion

 /*

SQL Script for getting PO Details in Oracle Fusion

*/

SELECT
hou_prc.bu_name "Procurement BU",
hp.party_name "Supplier Name",
ss.vendor_site_code "Supplier Site",
hp_con.person_first_name || ' ' || hp_con.person_last_name "Supplier Contact",
hl_bill.location_code "Bill-to-Location",
hl_ship.location_code "Ship-to-Location",
poh.creation_date "Creation_date",
ppnf.full_name "Buyer",
poh.document_status "Status",
poh.segment1 "PO Number",
pol.line_num "Line Number",
pol.item_description "Item Description",
pol.quantity "Qty",
pol.uom_code "UOM",
pol.unit_price "Unit Price",
/* Added Line Amount derived column */
(pol.quantity * pol.unit_price) "Line Amount",
/* Concatenated Charge Account */
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || gcc.segment4 "Charge Account",
pod.distribution_num "Distribution Number"
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod,
poz_suppliers sup,
hz_parties hp,
hz_parties hp_con,
poz_supplier_sites_v ss,
fun_all_business_units_v hou_prc,
per_person_names_f ppnf,
gl_code_combinations gcc,
hr_locations_all hl_ship,
hr_locations_all hl_bill,
poz_supplier_contacts psc
WHERE 1=1
/* Core PO Table Joins */
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id

/* Supplier and Site Joins */
AND poh.vendor_id = sup.vendor_id
AND sup.party_id = hp.party_id
AND poh.vendor_site_id = ss.vendor_site_id

/* Contact Joins with Outer Join for stability */
AND poh.vendor_contact_id = psc.vendor_contact_id(+)
AND psc.per_party_id = hp_con.party_id(+)

/* Organization and Buyer Filter */
AND poh.prc_bu_id = hou_prc.bu_id
AND poh.agent_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

/* Accounting and Location Outer Joins */
AND pod.code_combination_id = gcc.code_combination_id
AND pll.ship_to_location_id = hl_ship.location_id(+)
AND poh.bill_to_location_id = hl_bill.location_id(+)

/* Parameters for BIP Data Model */
AND (poh.segment1 = :P_PO_NUM OR :P_PO_NUM IS NULL)
AND TRUNC(poh.creation_date) BETWEEN :P_FROM_DATE AND :P_TO_DATE
ORDER BY
poh.segment1 DESC,
pol.line_num ASC

LOV Script

select poh.segment1
from
po_headers_all poh

Result:

Creating Purchase Order and back-table in Oracle Fusion

Notes written in the session

==========================

Topic: Creating Purchase Order

P2P Cycle 

Sai Serservice -Maruthi Dealer 

   30 Yrs     

   Creating Requisition

     Ertiga 101 100 Qty 

Alto 101    10 qty       

    Create PO AutoCreate Requision --Supplier

Create PO --Supplier     

A2CF need 100  Laptops 

  venkat raised request to buy  100 laps with 1 tb HD 2 gb ram 

  may 10 th 2026 to llcation 101 Blor KR  Puram  A2CF Office   

Request for  Quotations from suppliers   

  HP  -- Madhu is supplier 

  Del  -- Dell outlet -- Dell is supplier 

  Compaq  --

  IBM

  WIPRO

Quotations -Quote Analysis 

AutoCreate PO with Madhu  

101 Blor KR  Puram  --Employee receiving role

Ramki - Receive good 

Create Receipt --Goods Receiving Receipt

Invoice --

Validate 

payment 

Create Acccounting 

Post to ledger 

EGP_  -- Item Master  PDH module

INV_ -- Inventory 

POR_   PO Module

PO_   PO Module

PON_  PO Module

Rcv_   PO Module

AP_  AP Module

XLA_AE_  Subledger Module

GL_  GL Module

US165299   PO number 

PO_Headers_all  

PO_Lines_ALL  

PO_Line_locations_all --Schedule

po_Distributions_all

============

Detailed Explanation:

Module	Purpose	Table Prefix
Product Development Hub (PDH)	Item Master data	`EGP_`
Inventory (INV)	On-hand quantities and inventory transactions	`INV_`
Purchasing (PO)	Requisitions and Purchase Orders	(Requisitions) / (Orders)
Sourcing (PON)	Negotiations, RFQs, and Quotations	`PON_`
Receiving (RCV)	Goods receiving receipts and shipment lines	`RCV_`
Accounts Payable (AP)	Invoices, lines, and distributions	`AP_`
Subledger Accounting (XLA)	Subledger accounting entries and entities	`XLA_`
General Ledger (GL)	General ledger and posting	`GL_`

EGP_ is the prefix for back-end tables belonging to the Product Development Hub (PDH) module, primarily used for storing Item Master data. These tables can be found in the link:

Go to  Product Model and check the tables.

EGP_SYSTEM_ITEMS_B

EGP_SYSTEM_ITEMS_TL

EGP_SYSTEM_ITEMS_TL

EGP_ITEM_REVISIONS_B

 Inventory Management

INV_ORG_PARAMETERS: 

INV_SECONDARY_INVENTORIES: 

INV_ITEM_LOCATIONS: 

INV_MATERIAL_TXNS (MMT): 

INV_TRANSACTION_LOT_NUMBERS: 

INV_UNIT_TRANSACTIONS (MUT): 

INV_TRANSACTION_TYPES_TL: 

INV_ONHAND_QUANTITIES_DETAIL: 

INV_LOT_NUMBERS: 

INV_SERIAL_NUMBERS: 

Open Interface & Temporary Tables (Data Conversion/Integration)

INV_TRANSACTIONS_INTERFACE: 

INV_MATERIAL_TXNS_TEMP (MMTT): 

EGP_SYSTEM_ITEMS_INTERFACE: 

INV_TXN_REQUEST_HEADERS: 

INV_TRANSFER_ORDER_HEADERS/LINES: 

On-hand reports: Query INV_ONHAND_QUANTITIES_DETAIL joined with EGP_SYSTEM_ITEMS_B.

Material Transaction History: Query INV_MATERIAL_TXNS with filters on TRANSACTION_TYPE_ID.

Stock Take/Physical Count: INV_PHYSICAL_INVENTORIES 

PO Tables

PO_HEADERS_ALL (PO):

PO_LINES_ALL (PO):

PO_LINE_LOCATIONS_ALL (PO):

PO_DISTRIBUTIONS_ALL (PO): 

PO_RELEASES_ALL (PO): 

PO_ACTION_HISTORY (PO): 

Self_Procurement Service

POR - Purchase Requisition Tables

PO_REQUISITION_HEADERS_ALL (POR/PO): 

PO_REQUISITION_LINES_ALL (POR/PO): 

POR_REQUISITION_LINES_ALL (POR - Fusion specific):  POR_REQUISITION_HEADERS_ALL.

PO_REQ_DISTRIBUTIONS_ALL (POR/PO): 

Sourcing:

PON

Sourcing (PON) tables are essential for managing negotiations (RFQs/Auctions) that result in purchase orders (POs) or agreements

PON_AUCTION_HEADERS_ALL 

PON_AUCTION_ITEM_PRICES_ALL 

PON_BID_HEADERS 

PON_BID_ITEM_PRICES 

PON_BID_PO_NUMBERS 

Other Related Key Tables

PON_AUCTION_PRICE_ELEMENTS 

PON_BID_PRICE_ELEMENTS

Key Linking Concepts

Negotiation to PO: The outcome of a negotiation (PON_AUCTION_HEADERS_ALL) is frequently a Blanket Purchase Agreement (BPA) or a standard Purchase Order (PO_HEADERS_ALL), with the link stored in PON_BID_PO_NUMBERS.

Line Item Linkage: PON_AUCTION_ITEM_PRICES_ALL connects to PON_BID_ITEM_PRICES, which in turn influences the PO_LINES_ALL table in the purchasing module.

Real-time status: These tables are updated instantly when a buyer publishes a negotiation, a supplier submits a bid, or a negotiation is awarded. 

RCV - Receiving Tables (Real-time Transactional) 

RCV_SHIPMENT_HEADERS (RCV): 

RCV_SHIPMENT_LINES (RCV): 

RCV_TRANSACTIONS (RCV): 

Other Key Tables & Interfaces

PO_AGENTS (PO): 

RCV_INTERFACE (RCV): 

PO_HEADERS_INTERFACE/PO_LINES_INTERFACE (PO): 

Key Relationships

PO_REQUISITION_LINES_ALL -> PO_LINE_LOCATIONS_ALL (Requisition converted to PO)

PO_HEADERS_ALL -> PO_LINES_ALL -> PO_LINE_LOCATIONS_ALL -> PO_DISTRIBUTIONS_ALL (Main PO flow)

PO_LINE_LOCATIONS_ALL -> RCV_TRANSACTIONS (Shipment received) 

XLA_AE_Subledger Module 

Core Subledger Accounting (XLA) Tables XLA_EVENTS: Records all accounting events (e.g., invoice validation, payment creation) that require accounting. It is the primary table for event management. XLA_TRANSACTION_ENTITIES: Stores information about subledger documents or transactions (e.g., which AP invoice or AR transaction is being accounted). XLA_AE_HEADERS: Stores the subledger journal entry headers, acting as the primary container for a subledger journal entry. XLA_AE_LINES: Stores the subledger journal entry lines, containing debit and credit amounts, code combinations (CCIDs), and descriptions. XLA_DISTRIBUTION_LINKS: Connects transaction distribution lines (e.g., from AP_INVOICE_DISTRIBUTIONS) to the corresponding XLA_AE_LINES. This table is crucial for auditing from GL back to the subledger transaction. XLA_ACCOUNTING_ERRORS: Stores error messages encountered during the "Create Accounting" process. Setup and Configuration Tables XLA_SUBLEDGERS: Contains information about subledger applications (e.g., Payables, Receivables) registered in the Accounting Hub. XLA_ACCTG_METHODS_B: Stores Subledger Accounting Methods (SLAM). XLA_SOURCES_B: Stores the sources used for creating accounting rules and conditions. Typical Join Query Strategy To create reports, these tables are often joined in this order: Transaction Table (e.g., AP_INVOICES_ALL) XLA_TRANSACTION_ENTITIES (Links transaction to SLA) XLA_EVENTS (Links entity to events) XLA_AE_HEADERS (Links event to journal) XLA_AE_LINES (Links header to lines) XLA_DISTRIBUTION_LINKS (Links lines to transaction distributions)

GL Module

Core Transaction & Balance Tables 

GL_BALANCES: The most critical table for reporting, storing actual, budget, and encumbrance balances for each account, currency, and period.

GL_JE_HEADERS: Stores header-level information for journal entries (e.g., journal name, source, category, ledger, status).

GL_JE_LINES: Contains the detailed debit and credit lines for journal entries, connected to the header via JE_HEADER_ID.

GL_JE_BATCHES: Stores information about journal entry batches. 

Oracle Help Center

Oracle Help Center

Setup & Configuration Tables 

GL_CODE_COMBINATIONS: Stores valid account combinations (Accounting Flexfield segments) used in journals and balances.

GL_LEDGERS: Contains the ledger definitions, including ledger currency, calendar, and chart of accounts.

GL_PERIOD_STATUSES: Stores the open/closed status of accounting periods. 

Interface & Reference Tables

GL_INTERFACE: The primary staging table used to import journal entries from external systems or subledgers into the GL.

GL_DAILY_RATES: Stores currency conversion rates used for foreign currency transactions.

GL_IMPORT_REFERENCES: Links journals to their source transactions (subledger entries), often used for drill-down. 

Key Relationships

GL_JE_LINES links to GL_JE_HEADERS via JE_HEADER_ID.

GL_JE_LINES links to GL_CODE_COMBINATIONS via CODE_COMBINATION_ID.

GL_BALANCES links to GL_CODE_COMBINATIONS and GL_LEDGERS.

===================================

Core concepts of the Procure-to-Pay (P2P) cycle as demonstrated in the video, ranging from initial business processes to technical back-end structures.1. Procurement Business Processes

Purchase Requisitions: Understand how a requisition is the starting point for internal requests, which can include multiple items and quantities for specific locations.

Purchase Orders (PO): Learn the two primary ways to create a PO:

From an Approved Requisition: Converting an existing, internally approved request into a PO.

Direct Creation: Creating a PO directly against a supplier without a prior requisition when business terms are already established.3

The Request for Quotation (RFQ) Cycle: Study how businesses handle negotiations by getting quotes from multiple suppliers, performing quote analysis, and then auto-creating a PO for the best quote.

2. The P2P Lifecycle Steps

Study the hierarchical flow of the Procure-to-Pay cycle:

Approval: Internal supervisor or departmental approval of requisitions and POs

Goods Receiving: The role of the storekeeper in logging into the application to create a "goods receiving receipt" upon delivery.

Invoicing and Payment: The progression from creating an invoice to validation, payment, and finally posting to the general ledger.8

3. Oracle Fusion Technical Architecture

For those in technofunctional roles, focus on the naming conventions and structure of the back-end database objects:

Module Prefixes:

PO Module: Requisitions (PR_), Purchase Orders (PO_), and Negotiations (PON_).

Receiving: Receiving transactions (RCV_).

Inventory & Items: Item master (EGP_) and Inventory (INV_).

Financials: Invoices (AP_), Subledger Accounting (XLA_), and General Ledger (GL_).

PO Table Hierarchy: Understand the primary-foreign key relationships between these four levels:

PO_HEADERS_ALL: General information (PO number, BU, Supplier).

PO_LINES_ALL: Specific item and quantity details.

PO_LINE_LOCATIONS_ALL (Schedules): Delivery dates and quantities.

PO_DISTRIBUTIONS_ALL: Accounting and distribution data for each schedule.

4. Hands-on Application Navigation

Procurement Dashboard: Navigating to "Purchase Requisitions" and "Purchase Orders".

Document Builder: Using the "Add to Document Builder" feature to consolidate requisition lines into a new PO.

Order Management: Managing existing orders and querying them for modifications or approval.

Purchase Requisition in Oracle Fusion

 Purchase Requisition in Oracle Fusion

To check the roles for creating the Purchase Requisition

Click on the User and Add Roles

We need the role called

Advanced Procurement Requester or Procurement Requester 

And also check for Data Access Role

Under 

Setup and Maintenance-->Choose Financials-->Manage Data Access for Users.

Click on Done

Run the scheduled process called LDAP

Now, go to Procurement-->Choose Purchase Requisition(Redwood pages) Purchase Requisition(ADF) classic method

Click on Edit Update requisition preferences

We can choose specific BU in which user want to do Purchase Requisition.

Choose Deliver to Location

Click on More Tasks

Enter Requisition Line

Destination Type would be Inventory

Subinventory leave blank

Click on Add to Cart

Enter another item details and click on Add to Cart

And then click on Add to Cart and click on Review

Click on Manage Approvals

It will show  Purchase Requisition Approval Flow

If you want to add more items in the cart then we need to use Shop option

Create the requisition

Enter into requisition line

proceed with filling the data and add to cart

And then click on Submit and view the PDF

=======================

Back-End Tables

SELECT PRH.requisition_number AS "PR_Number",
       PRH.document_status    AS "Status",
       PRH.description        AS "Header_Description",
       PNF.full_name          AS "Requester_Name",
       PRL.line_number        AS "Line_Num",
       PRL.item_description   AS "Item_Description",
       PRL.quantity           AS "Quantity",
       PRL.unit_price         AS "Price",
       PRL.currency_code      AS "Currency",
       -- Accounting Data from GL_CODE_COMBINATIONS
       GCC.segment1
       || '-'
       || GCC.segment2
       || '-'
       || GCC.segment3        AS "Account_Combination",
       -- Linked PO Number
       PHA.segment1           AS "Linked_PO_Number",
       PRH.creation_date      AS "Created_On"
FROM   por_requisition_headers_all PRH,
       por_requisition_lines_all PRL,
       por_req_distributions_all PRD,
       per_person_names_f PNF,
       po_distributions_all PDA,
       po_headers_all PHA,
       gl_code_combinations GCC
WHERE
  -- Header to Line
  PRH.requisition_header_id = PRL.requisition_header_id
  -- Line to PR Distribution
  AND PRL.requisition_line_id = PRD.requisition_line_id (+)
  -- PR Distribution to GL Code Combinations
  AND PRD.code_combination_id = GCC.code_combination_id (+)
  -- Header to Requester
  AND PRH.preparer_id = PNF.person_id (+)
  AND PNF.name_type (+) = 'GLOBAL'
  AND PNF.effective_end_date (+) > SYSDATE
  -- PR Distribution to PO Distribution
  AND PRD.distribution_id = PDA.req_distribution_id (+)
  -- PO Distribution to PO Header
  AND PDA.po_header_id = PHA.po_header_id (+)
-- Optional: Limit to last 30 days for performance
-- AND PRH.CREATION_DATE >= TRUNC(SYSDATE) - 30
ORDER  BY PRH.creation_date DESC,
          PRH.requisition_number DESC

Front-End Fusion User Interface (UI)

Fetching Data from Back-End

Aggregate Functions in Oracle SQL

                                 Aggregate Functions

                                     POC done by Raghava

 Perform a calculation on multiple records and return a single record

If we want go with aggregate Function’s with other Column’s we Must go with Group by Columns

 AVG

 MIN

 MAX

 SUM

 COUNT

 RULES TO USE Aggregate Functions

 Mostly used with Group by Clause

 -->Columns which are used in select clause with aggregate functions must be available in group by Clause

 Ex:select deptno,job,avg(sal) from xx_emp Group by deptno,job order by deptno;

 Select avg(sal) from xx_emp;

select Max(sal)from xx_emp;

select Min(sal)from xx_emp;

select SUM(sal)from xx_emp;

select count(*)from xx_emp;

If we use count(1)

select count(Null) from xx_emp;

Difference b/w Count (1) and Count (*)

Specific Columns: COUNT(1) does not count the first column of a table; it evaluates the literal "1" for each row and counts how many times it is non-null (which is always).

Recommendation: COUNT(*) is generally preferred as it is the standard SQL syntax and more commonly understood by other developers

COUNT(column_name):

Unlike the two above, COUNT(column_name) behaves differently because it excludes NULL values in that specific column from the final count