A ‘P2P’ query which Populates all PO details:

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.

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';