Oracle Applications: A single Query covering P2P life Cycle

In Finance, transaction management processing is one of labor intensive task in ERP, as it requires extensive data entry , chance are very very high for duplication/re-entry. As we know Procure to Pay life cycle start itself from contract management till making payment.

As we know the efficient Procure to pay process have these sub processes;

Contract Management
Purchase Requisitions
Purchase Orders
Accounts Payable - Managing invoice
Supplier Payment

p2p

In real business world, many time when system is running external/internal auditor are more interested in scrutiny of:

Goods received / invoices received
Inaccurate or duplicate vendor & material master records
Discrepancies in payment terms
Delays / long processing times
Detect duplicate vendor
Unusually large or small payments
Unauthorized changes made to invoices
Detect Duplicate invoice
Detect Duplicate payment
Approval status

Therefore, it is Inhouse ISD/Finance IT or implementing company responsibility is to provide such kind of adhoc reporting for auditor so that they can satisfy the audit requirement.

A 'P2P' query that made Auditors happy

It was brought by ISD team , as part year end audit for a ERP system which went live 3 month back. It was a one of requirement to display data for a particular PO which covers data from there all 5 five phases, means a particular PO line consist of:

Requisition Detail
Purchase Order Details
Receiving Details
Invoicing Detail
Payment Details

Therefore thought to share this query, hope this would be great help who have such kind of adhoc requirement from daily life.

Here is query:

SELECT
A.ORG_ID "ORG ID",
E.VENDOR_NAME "VENDOR NAME",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
F.VENDOR_SITE_CODE "VENDOR SITE",
F.ADDRESS_LINE1 "ADDRESS",
F.CITY "CITY",
F.COUNTRY "COUNTRY",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
D.SEGMENT1 "PO NUMBER",
D.TYPE_LOOKUP_CODE "PO TYPE",
C.QUANTITY_ORDERED "QTY ORDERED",
C.QUANTITY_CANCELLED "QTY CANCALLED",
G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
G.UNIT_PRICE "UNIT PRICE",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO STATUS",
A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
A.INVOICE_NUM "INVOICE NUMBER",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER "CHEQUE NUMBER",
TO_CHAR(TRUNC(I.CHECK_DATE)) "PAYMENT DATE"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
AND D.TYPE_LOOKUP_CODE != 'BLANKET';