Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

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

3 comments:

hari said...

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

Prajyot said...

thank's a lot from all information.
you are really doing good work so plz keep it up
all the best you

Anonymous said...

Thanks for every thing you provide us

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect