--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:
=================
--P2P Cycle SQL Script
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_requisition_headers_all prh,
por_requisition_lines_all prl,
por_req_distributions_all prd,
po_distributions_all pda,
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt,
ap_invoice_lines_all aila,
ap_invoices_all aia,
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
==================
PO Query
SELECT
hou_prc.bu_name,
poh.segment1,
poh.document_status,
poh.type_lookup_code,
poh.currency_code,
poh.creation_date,
ppnf.full_name,
hp.party_name,
sup.segment1 supplier_num,
ss.vendor_site_code,
pol.line_num,
pol.item_description,
pol.unit_price,
pol.quantity,
pol.uom_code,
(pol.unit_price * pol.quantity) line_amount,
pll.shipment_num,
pll.quantity shipment_qty,
pll.quantity_received,
pll.quantity_billed,
pll.promised_date,
hl.location_code,
pod.distribution_num,
pod.quantity_ordered,
/* Manually concatenating segments since KFV is unavailable */
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || gcc.segment4 "Account"
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod,
poz_suppliers_v sup,
hz_parties hp,
poz_supplier_sites_all_m ss,
fun_all_business_units_v hou_prc,
per_person_names_f ppnf,
gl_code_combinations gcc,
hr_locations_all hl
WHERE 1=1
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
AND poh.vendor_id = sup.vendor_id
AND sup.party_id = hp.party_id
AND poh.vendor_site_id = ss.vendor_site_id
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
AND pod.code_combination_id = gcc.code_combination_id
AND pll.ship_to_location_id = hl.location_id(+)
ORDER BY
poh.segment1 DESC,
pol.line_num ASC
===========================
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 SQL Script
---------------
select poh.segment1
from
po_headers_all poh
No comments:
Post a Comment