Procure to Pay (P2P) Life Cycle

P2P Query :

SELECT   DISTINCT
         RH.SEGMENT1 "REQ NUMBER",
         RH.PREPARER_ID,
         RH.CREATION_DATE,
         PH.SEGMENT1 "PO NUMBER",
         RL.ITEM_DESCRIPTION,
         RL.TO_PERSON_ID,
         RL.SOURCE_TYPE_CODE,
         RL.NEED_BY_DATE,
         PH.VENDOR_ID,
         PH.VENDOR_SITE_ID,
         PV.VENDOR_NAME "VENDOR NAME",
         UPPER (PV.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         PVS.VENDOR_SITE_CODE "VENDOR SITE",
         PVS.ADDRESS_LINE1 "ADDRESS",
         PVS.CITY "CITY",
         PVS.COUNTRY "COUNTRY",
         TO_CHAR (TRUNC (PH.CREATION_DATE)) "PO DATE",
         PH.TYPE_LOOKUP_CODE "PO TYPE",
         PD.QUANTITY_ORDERED "QTY ORDERED",
         PD.QUANTITY_CANCELLED "QTY CANCALLED",
         PL.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         PL.UNIT_PRICE "UNIT PRICE",
         (NVL (PD.QUANTITY_ORDERED, 0) - NVL (PD.QUANTITY_CANCELLED, 0))
         * NVL (PL.UNIT_PRICE, 0)
            "PO LINE AMOUNT",
         DECODE (PH.APPROVED_FLAG, 'Y', 'Approved') "PO STATUS",
         PL.ITEM_ID,
         AIA.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         AIA.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR (TRUNC (AIA.INVOICE_DATE)) "INVOICE DATE",
         AIA.INVOICE_NUM "INVOICE NUMBER",
         (SELECT   DESCRIPTION
            FROM   APPS.AP_TERMS_TL Y
           WHERE   Y.TERM_ID = AIA.TERMS_ID)
            "INVOICE TERMS",
         (SELECT   DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   AP.AP_INVOICE_DISTRIBUTIONS_ALL X
           WHERE   X.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID)
            "INVOICE APPROVED ?",
         AIA.AMOUNT_PAID,
         AIPA.AMOUNT,
         AIA.INVOICE_CURRENCY_CODE,
         AIA.PAYMENT_CURRENCY_CODE,
         AIA.PAYMENT_METHOD_LOOKUP_CODE "PAYMENT METHOD",
         ACA.CHECK_NUMBER "PAYMENT NUMBER",
         TO_CHAR (TRUNC (ACA.CHECK_DATE)) "PAYMENT DATE"
  FROM   APPS.PO_HEADERS_ALL PH,
         APPS.PO_DISTRIBUTIONS_ALL PD,
         APPS.PO_REQ_DISTRIBUTIONS_ALL RD,
         APPS.PO_REQUISITION_LINES_ALL RL,
         APPS.PO_REQUISITION_HEADERS_ALL RH,
         APPS.PO_LINES_ALL PL,
         APPS.PO_VENDORS PV,
         APPS.PO_VENDOR_SITES_ALL PVS,
         APPS.RCV_TRANSACTIONS RT,
         APPS.RCV_SHIPMENT_HEADERS RSH,
         APPS.RCV_SHIPMENT_LINES RSL,
         APPS.AP_INVOICES_ALL AIA,
         APPS.AP_INVOICE_LINES_ALL AILA,
         APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
         APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
         APPS.AP_CHECKS_ALL ACA
 WHERE       PH.PO_HEADER_ID = PD.PO_HEADER_ID
         AND PD.REQ_DISTRIBUTION_ID = RD.DISTRIBUTION_ID
         AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
         AND RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID
         AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
         AND PV.VENDOR_ID(+) = PH.VENDOR_ID
         AND PVS.VENDOR_SITE_ID(+) = PH.VENDOR_SITE_ID
         AND RT.PO_HEADER_ID = PH.PO_HEADER_ID
         AND RT.PO_LINE_ID = PL.PO_LINE_ID
         AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
         AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
         AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
         AND AILA.PO_HEADER_ID = PH.PO_HEADER_ID
         AND AILA.PO_LINE_ID = PL.PO_LINE_ID
         AND AIA.INVOICE_ID = AILA.INVOICE_ID
         AND AIDA.INVOICE_ID = AILA.INVOICE_ID
         AND AIDA.INVOICE_LINE_NUMBER = AILA.LINE_NUMBER
         AND AIPA.INVOICE_ID = AIA.INVOICE_ID
         AND ACA.CHECK_ID = AIPA.CHECK_ID

Joins and Tables :

 

1. Requisition:

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='XXXX' --REQUISITION_HEADER_ID SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=XXXX  --REQUISITION_LINE_ID SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=XXX

   2. Purchase Order :

             PO_HEADERS_ALL

 

SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1 =;

SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID =;

 

PO_LINES_ALL

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID =;

 PO_LINE_LOCATIONS_ALL

 SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_HEADER_ID =;

 

             PO_DISTRIBUTIONS_ALL 

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =;

 PO_RELEASES_ALL

 SELECT * FROM PO_RELEASES_ALL WHERE PO_HEADER_ID =; 

    3. Receiving :

RCV_SHIPMENT_HEADERS

 SELECT * FROM RCV_SHIPMENT_HEADERS WHERE SHIPMENT_HEADER_ID IN

(SELECT SHIPMENT_HEADER_ID FROM RCV_SHIPMENT_LINES

WHERE PO_HEADER_ID =);

 RCV_SHIPMENT_LINES

 SELECT * FROM RCV_SHIPMENT_LINES WHERE PO_HEADER_ID =;

 RCV_TRANSACTIONS 

SELECT * FROM RCV_TRANSACTIONS WHERE PO_HEADER_ID =;

 RCV_ACCOUNTING_EVENTS

 SELECT * FROM RCV_ACCOUNTING_EVENTS WHERE RCV_TRANSACTION_ID IN

(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS

WHERE PO_HEADER_ID =);

 RCV_RECEIVING_SUB_LEDGER 

SELECT * FROM RCV_RECEIVING_SUB_LEDGER WHERE RCV_TRANSACTION_ID IN

(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS WHERE PO_HEADER_ID =);

 RCV_SUB_LEDGER_DETAILS

 SELECT * FROM RCV_SUB_LEDGER_DETAILS

WHERE RCV_TRANSACTION_ID IN (SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS

WHERE PO_HEADER_ID =);

     4. Invoices :

AP_INVOICE_DISTRIBUTIONS_ALL

 SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =);

 AP_INVOICES_ALL

 SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_ID IN

(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =));

 AP_INVOICE_LINES_ALL  

 SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID IN

(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =));

       5. Payments :

         We Can join AP_INVOICE_LINES_ALL with PO_HEADER_ID & PO_LINE_ID from        PO_HEADERS_ALL table.

 

FOR VIEW LIST OF PAYMENTS:

            SELECT * FROM AP_INVOICE_PAYMENTS_ALL;

            SELECT * FROM AP_PAYMENT_SCHEDULES_ALL;

 FOR CHECK’S INFORMATION:

            SELECT * FROM AP_CHECKS_ALL;

 FOR CHECK FORMAT:

            SELECT * FROM AP_CHECK_FORMATS;

            SELECT * FROM AP_CHECKRUN_CONC_PROCESSES_ALL;