Oracle SCM: Procure to Pay(P2P) Query

Last post we have tried to understand the high level process flow of P2P cycle along with table effected by this process. Today we will try to have a SQL query for the same.

Sometime customer (Specially Contract Holders) wants to a have report to monitor their contract budget and expenses. This report basically should have all details for a blanket release, like

    1. Contract Details ( Contract Start and End date, Contract Agreed Amount.. )
    2. PO Release Number & Amount.
    3. Receipt Amount and Quantity for a Item under a release.
    4. Invoice Amount for a received Item.
    5. Payment Amount. ( Payment is done in Item or Line level, so payment amount always will be in         Invoice Number wise)
    6. Sometime they want to see the Work Order Information as well if any WO attached to it. 

In summary , this report will give PO amount, Receipt Amount , Invoice Amount and Payment Amount for each release. Its a combine report from PO and AP module. Here the query.



/* Formatted on 07/12/2015 09:56:30 (QP5 v5.126.903.23003) */
SELECT   PO_HEADERS_ALL.TYPE_LOOKUP_CODE PO_TYPE,
         PO_HEADERS_ALL.SEGMENT1 PO_NUMNER,
         PO_HEADERS_ALL.AGENT_ID BUYER_ID,
         PO_HEADERS_ALL.VENDOR_ID,
         PO_HEADERS_ALL.VENDOR_SITE_ID,
         PO_HEADERS_ALL.START_DATE CONTRACT_START_DATE,
         PO_HEADERS_ALL.END_DATE CONTRACT_END_DATE,
         PO_HEADERS_ALL.AMOUNT_LIMIT CONTRACT_AMOUNT_LIMIT,
         PO_HEADERS_ALL.COMMENTS PO_DESCRIPTION,
         PO_HEADERS_ALL.CREATION_DATE PO_CREATION_DATE,
         PO_HEADERS_ALL.APPROVED_DATE PO_APPROVED_DATE,
         PO_HEADERS_ALL.REVISION_NUM REVISION_NUM,
         DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
                 NULL,
                 NVL (PO_HEADERS_ALL.AUTHORIZATION_STATUS, 'INCOMPLETE'),
                 NVL (PO_RELEASES_ALL.AUTHORIZATION_STATUS, 'INCOMPLETE'))
            AUTHORIZATION_STATUS,
         PO_LINE_LOCATIONS_ALL.CLOSED_CODE PO_LINE_CLOSED_CODE,
         PO_HEADERS_ALL.ORG_ID,
         PO_LINES_ALL.ITEM_ID PRODUCT_ID,
         PO_LINES_ALL.LINE_NUM PO_LINE_NUM,
         PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM,
         PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID,
         PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID,
         PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID,
         PO_DISTRIBUTIONS_ALL.DELIVER_TO_PERSON_ID REQUESTER_ID,
         CASE
            WHEN PO_DISTRIBUTIONS_ALL.REQ_HEADER_REFERENCE_NUM IS NULL
            THEN
               PO_REQUISITION_HEADERS_ALL.SEGMENT1
            ELSE
               PO_DISTRIBUTIONS_ALL.REQ_HEADER_REFERENCE_NUM
         END
            PO_REQUISITION_NUM,
         PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,
         PO_REQUISITION_LINES_ALL.URGENT_FLAG,
         po_requisition_headers_all.CREATION_DATE PR_CREATION_DATE,
         PO_REQUISITION_HEADERS_ALL.APPROVED_DATE PR_APPROVED_DATE,
         (SELECT   APPROVED_DATE
            FROM   PO_HEADERS_ARCHIVE_ALL
           WHERE       REVISION_NUM = 0
                   AND APPROVED_FLAG = 'Y'
                   AND po_header_id = PO_HEADERS_ALL.po_header_id)
            PO_APPROVED_DATE_ORG,
         PO_RELEASES_ALL.RELEASE_NUM PO_RELEASE_NUM,
         PO_RELEASES_ALL.APPROVED_DATE PO_RELEASE_DATE,
         PO_RELEASES_ALL.AUTHORIZATION_STATUS PO_RELEASE_STATUS,
         PO_RELEASES_ALL.APPROVED_DATE PO_RELEASE_APPROVED_DATE,
         RCV_TRANSACTIONS.TRANSACTION_TYPE,
         RCV_TRANSACTIONS.UNIT_OF_MEASURE,
         PO_LINES_ALL.UNIT_PRICE PO_LINE_UNIT_PRICE,
         PO_HEADERS_ALL.CURRENCY_CODE PO_CURRENCY_CODE,
         NVL (PO_LINES_ALL.QUANTITY, PO_LINE_LOCATIONS_ALL.QUANTITY)
            QUANTITY_ORDERED,
         PO_DISTRIBUTIONS_ALL.QUANTITY_CANCELLED,
         RCV_SHIPMENT_HEADERS.RECEIPT_NUM,
         RCV_TRANSACTIONS.TRANSACTION_DATE RECEIPT_DATE,
         NVL (RCV_TRANSACTIONS.QUANTITY, 0) RECEIPT_QUANTITY,
         NVL (RCV_TRANSACTIONS.QUANTITY, 0) * RCV_TRANSACTIONS.PO_UNIT_PRICE
            RECEIPT_DOC_AMOUNT,
         AP_INVOICES_ALL.INVOICE_NUM,
         AP_INVOICES_ALL.INVOICE_CURRENCY_CODE,
         AP_INVOICES_ALL.INVOICE_RECEIVED_DATE,
         AP_INVOICES_ALL.INVOICE_DATE,
         AP_INVOICES_ALL.CREATION_DATE INVOICE_CREATION_DATE,
         NVL (AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED, 0)
            INVOICED_QUANTITY,
         NVL (AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT, 0) INV_DOC_AMOUNT,
         NVL (AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT, 0) INV_AMOUNT,
         AP_INVOICES_ALL.PAYMENT_STATUS_FLAG,
         --AP_INVOICES_ALL.AMOUNT_PAID,
         AP_INVOICE_PAYMENTS_ALL.AMOUNT PAID_AMOUNT,
         AP_CHECKS_ALL.CHECK_NUMBER,
         TO_CHAR (TRUNC (AP_CHECKS_ALL.CHECK_DATE)) PAYMENT_DATE,
         PO_REQUISITION_LINES_ALL.wip_entity_id,
         PO_REQUISITION_LINES_ALL.wip_operation_seq_num OPERATION_SEQ_NUM,
         WIP_DISCRETE_JOBS.OWNING_DEPARTMENT WO_OWNING_DEPARTMENT_ID
  FROM   PO_HEADERS_ALL,
         PO_LINES_ALL,
         PO_DISTRIBUTIONS_ALL,
         PO_LINE_LOCATIONS_ALL,
         PO_RELEASES_ALL,
         PO_REQ_DISTRIBUTIONS_ALL,
         PO_REQUISITION_LINES_ALL,
         PO_REQUISITION_HEADERS_ALL,
         (SELECT   *
            FROM   RCV_TRANSACTIONS
           WHERE   RCV_TRANSACTIONS.TRANSACTION_TYPE IN
                         ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT', 'MATCH'))
         RCV_TRANSACTIONS,
         RCV_SHIPMENT_HEADERS,
         RCV_SHIPMENT_LINES,
         AP_INVOICE_DISTRIBUTIONS_ALL,
         AP_INVOICE_LINES_ALL,
         AP_INVOICES_ALL,
         AP_INVOICE_PAYMENTS_ALL,
         AP_CHECKS_ALL,
         WIP_DISCRETE_JOBS
 WHERE   PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
         AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
         AND PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID =
               PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
         AND PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID =
               PO_RELEASES_ALL.PO_RELEASE_ID(+)
         AND PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID =
               PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID(+)
         AND PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID =
               PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID(+)
         AND PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID =
               PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID =
               RCV_TRANSACTIONS.PO_DISTRIBUTION_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID =
               AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID =
               AP_INVOICES_ALL.INVOICE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID =
               AP_INVOICE_LINES_ALL.INVOICE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER =
               AP_INVOICE_LINES_ALL.LINE_NUMBER(+)
         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
               RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID(+)
         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
               RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID(+)
         AND RCV_TRANSACTIONS.SHIPMENT_LINE_ID =
               RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID(+)
         AND AP_INVOICES_ALL.INVOICE_ID =
               AP_INVOICE_PAYMENTS_ALL.INVOICE_ID(+)
         AND AP_INVOICE_PAYMENTS_ALL.CHECK_ID = AP_CHECKS_ALL.CHECK_ID(+)
         AND PO_REQUISITION_LINES_ALL.wip_entity_id =
               WIP_DISCRETE_JOBS.wip_entity_id(+)


Enjoy and Stay Well !! :)