Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

A single Query covering P2P life Cycle

Posted on January 31st, 2008 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

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:

  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

1

2.Purchase Order details

2

3. Receiving Items Details

3

4.Invoice Details

4

5.Payment Details

5

You download the query and details here.download btn

Related Posts

Posted in Oracle Payable, Oracle Purchasing | 12 Comments »Email This Post Email This Post | Print This Post Print This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
12 Responses
  1. Nagaraj Says:

    Hi Sanjit,

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

  2. Raghu Says:

    Hi Sanjit,

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

    Cheers

  3. sinolee Says:

    It’s very useful. Thank you very much.

  4. Gary Horrocks Says:

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

    Many thanks for sharing this

  5. Gary Horrocks Says:

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

    Many thanks for sharing this

  6. Bill Lam Says:

    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. Vimalraj Says:

    Very good script. Many thanks.

  8. Vimalraj Says:

    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. Babu Says:

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

  10. Vrushali Says:

    Hi,

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

    Thanks

  11. Raj Says:

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

  12. Roopa Says:

    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

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.