Monday 4 March 2019

SQL Query to find details from PO till Cheque payment


SQL Query to find details from PO till Cheque payment

The following five components of  P2P Cycle Query are
1.    Requisition Detail
2.    Purchase Order Details
3.    Receiving Details
4.    Invoicing Detail
5.    Payment Details
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




7 comments:

  1. Thanks for the tutorial!
    That was very helpful

    Melbourne Web Developer

    ReplyDelete
  2. 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,
    AP.AP_SUPPLIERS E,
    AP.AP_SUPPLIER_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 != 'STANDARD';

    ReplyDelete
  3. Thanks, this is generally helpful.
    Still, I followed step-by-step your method in this cpq salesforce training
    salesforce cpq learning path

    ReplyDelete
  4. Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp a great deal more around this condition. and I could assume you are an expert on this subject. Same as your blog i found another one Oracle Taleo .Actually I was looking for the same information on internet for Oracle Taleo and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.

    ReplyDelete
  5. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful content..... We are also providing the best services click on below links to visit our website.

    Oracle Fusion HCM Training
    Workday Training
    Okta Training
    Palo Alto Training
    Adobe Analytics Training

    ReplyDelete
  6. can you add journal name batch and line detail to this query?

    ReplyDelete