Thursday, December 13, 2012

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

1 comment:

hari babu said...

Thank you very much for sharing valuable information to beginners like me, keep it up .

Post a Comment

Best Blogger TipsGet Flower Effect