- OracleApps Epicenter - http://www.oracleappshub.com -

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;

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

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:

  1. Requisition Detail
  2. Purchase Order Details
  3. Receiving Details
  4. Invoicing Detail
  5. 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';

The important section which cover in the query output is as:

1. Information for Supplier

2.Purchase Order details

3. Receiving Items Details

4.Invoice Details

5.Payment Details

You download the query and details here.

12 Comments (Open | Close)

12 Comments To "A single Query covering P2P life Cycle"

#1 Comment By Nagaraj On February 7, 2008 @ 6:59 am

Hi Sanjit,

I think this query will useful to everyone those who are in P2P cycle. I appreciate you.

#2 Comment By Raghu On February 21, 2008 @ 2:47 am

Hi Sanjit,

Really a useful one. Very much appreciated. Hoping some one can up similarly for O2C.

Cheers

#3 Comment By sinolee On February 25, 2008 @ 6:35 pm

It’s very useful. Thank you very much.

#4 Comment By Gary Horrocks On February 26, 2008 @ 8:53 am

Superb script, have managed to incorporate this into the nd User Layer to support P2P processes / reporting.

Many thanks for sharing this

#5 Comment By Gary Horrocks On February 26, 2008 @ 8:55 am

Superb script, have managed to incorporate this into a Discoverer End User Layer to support P2P processes / reporting.

Many thanks for sharing this

#6 Comment By Bill Lam On May 7, 2008 @ 12:49 am

Very useful script. Mank thanks for sharing. But one point,if the received quantity of po lines in receiving details would be more helpful.

#7 Comment By Vimalraj On July 9, 2008 @ 9:08 pm

Very good script. Many thanks.

#8 Comment By Vimalraj On July 9, 2008 @ 9:14 pm

During Invoice verification, I need to illustrate .

Material receipt for Rs.80
Supplier is given bill for Rs.100
During matching , we need to give Invoice amt. as Rs.100 and Rs.-20 to (separate GL a/c.say 123)match with receipt.Also make a DNote for Rs.20 and refer GL 123 and clear both amts.This process is done by user.

Please tell me,Is any other short cut like in SAP ,the difference automatically hits another GL a/c as Invoice reduction.?

Regards
D.Vimalraj

#9 Comment By Babu On February 27, 2009 @ 3:32 am

its very useful to all who are working in po module.
Thanks
Regards,
Babu

#10 Comment By Vrushali On September 17, 2009 @ 2:25 am

Hi,

Please explain abount which is GL account for PO and how to fetch GL description for PO

Thanks

#11 Comment By Raj On January 15, 2010 @ 12:26 am

Can we please have the R12 version of this query as well?

#12 Comment By Roopa On February 16, 2010 @ 2:10 am

Hi Sanjit,

Thanks for sharing such a good script. is there any way to include the Sales order details too. I’m working on Dropship sales which includes SO,PO,Inv,Payables,Recievables.

Thanks In Advance.
Regards,
Roopa