P2P Cycle SQL Script in Oracle Fusion

  --P2P Cycle SQL Script

Functional Flow

1 ) Purchase Requisition

Business user identifies need

Creates PR in system

Budget gets validated

2 ) PR Approval

Manager reviews and approves

Budget check happens

Approval routing complete

3 ) Purchase Order Creation

Approved PR converts to PO

Supplier gets selected

Terms & conditions set

4 ) PO Approval

Amount-based routing

Multi-level approval workflow

Final authorization received

5 ) Goods/Service Receipt

Warehouse receives items

Quantity verified

Quality inspection done

6 ) Invoice Receipt

Supplier sends invoice

Manual or EDI entry

Initial validation runs

7 ) 3-Way Match

PO vs Receipt vs Invoice

Quantity must match

Price tolerance checked

8 ) Invoice Approval

Holds resolved

Final invoice approval

Exceptions handled

9 ) Payment Processing

Payment terms applied

Payment run created

Bank file generated

10 ) GL Accounting

Technical Flow

SELECT 

-- 1. Requisition Data (PR)

    prh.requisition_number,

    prh.document_status            AS req_status,

    prl.line_number                AS req_line_number, 

    prl.item_description           AS req_description,

    prl.quantity                   AS req_quantity,

    -- 2. Purchasing Data (PO)

    pha.segment1                   AS po_number,

    pha.document_status            AS po_status,

    pla.line_num                   AS po_line_number,  -- PO stays as line_num

    plla.quantity                  AS po_quantity_ordered,   

    -- 3. Receiving Data (GRN)

    rsh.receipt_num,

    -- 4. Payables Data (Invoice,Payment)

    aia.invoice_num,

    aia.invoice_amount,

    aca.check_number               AS payment_number,

    -- 5. Subledger Accounting Data (SLA - The Rules Engine)

    xah.ae_header_id               AS sla_journal_id,

    xal.accounting_class_code      AS account_type, -- LIABILITY, EXPENSE, CASH

    xal.entered_dr                 AS sla_debit,

    xal.entered_cr                 AS sla_credit,

    -- 6. General Ledger Data (GL - The Final Destination)

    gjh.name                       AS gl_journal_name,

    gjh.status                     AS gl_posting_status, -- P = Posted, U = Unposted

    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 AS gl_account_string

FROM 

--POR Tables

    por_requisition_headers_all prh,   

    por_requisition_lines_all   prl,   

    por_req_distributions_all   prd,   

--PO Tables

    po_distributions_all        pda,   

    po_headers_all              pha,

    po_lines_all                pla,

    po_line_locations_all       plla,  

    rcv_shipment_headers        rsh,   

--Receipts Tables

    rcv_shipment_lines          rsl,   

    rcv_transactions            rt,    

-- Payable Tables

    ap_invoice_lines_all        aila,

    ap_invoices_all             aia,

--Payment Tables

    ap_invoice_payments_all     aipa,

    ap_checks_all               aca,

     --Subledger Accounting (SLA) Tables

    xla_transaction_entities    xte,

    xla_ae_headers              xah,

    xla_ae_lines                xal,

    -- General Ledger (GL) Tables

    gl_import_references        gir,

    gl_je_headers               gjh,

    gl_je_lines                 gjl,

    gl_code_combinations        gcc

WHERE 1=1

    -- JOIN: PO to PR Link

    AND pha.po_header_id = pla.po_header_id

    AND pla.po_line_id = plla.po_line_id

    AND plla.line_location_id = pda.line_location_id

    AND pda.req_distribution_id = prd.distribution_id(+)

    AND prd.requisition_line_id = prl.requisition_line_id(+)

    AND prl.requisition_header_id = prh.requisition_header_id(+)

    -- JOIN: PO to Receipt Link 

    AND plla.line_location_id = rsl.po_line_location_id(+)

    AND rsl.shipment_header_id = rsh.shipment_header_id(+)

    AND rsl.shipment_line_id = rt.shipment_line_id(+)

    AND rt.transaction_type(+) = 'RECEIVE' 

    -- JOIN: PO to AP Invoice Link

    AND pla.po_line_id = aila.po_line_id(+)

    AND aila.invoice_id = aia.invoice_id(+)

    -- JOIN: AP Invoice to Payment Link

    AND aia.invoice_id = aipa.invoice_id(+)

    AND aipa.check_id = aca.check_id(+)  

    -- JOINS: AP Invoice to SLA to GL

    -- JOIN: AP Invoice to SLA Transaction Entity (The Subledger Bridge)

    -- source_id_int_1 stores the Invoice ID when entity_code is 'AP_INVOICES'

    AND aia.invoice_id = xte.source_id_int_1(+)

    AND xte.entity_code(+) = 'AP_INVOICES'

    AND xte.application_id(+) = 200 -- 200 is the hardcoded ID for Payables

    -- JOIN: SLA Entity to SLA Headers and Lines

    AND xte.entity_id = xah.entity_id(+)

    AND xah.ae_header_id = xal.ae_header_id(+)

    -- JOIN: SLA Lines to GL Import References (The GL Bridge)

    AND xal.gl_sl_link_id = gir.gl_sl_link_id(+)

    AND xal.gl_sl_link_table(+) = 'XLA_AE_LINES'

    -- JOIN: GL Import References to GL Journal Headers Lines

    AND gir.je_header_id = gjh.je_header_id(+)

    AND gir.je_header_id = gjl.je_header_id(+)

    AND gir.je_line_num = gjl.je_line_num(+)

    -- JOIN: GL Line to Code Combinations (To get the Account String)

    AND gjl.code_combination_id = gcc.code_combination_id(+)

AND pha.segment1='162027'

ORDER BY 

    pha.segment1, 

    aia.invoice_num,

    xal.entered_dr DESC

Query Result: 

Click Here