P2P Cycle Complete Query

P2P Cycle Complete Query , askhareesh blog for Oracle Apps

SELECT PRHA.SEGMENT1 REQNO,
       PRHA.TYPE_LOOKUP_CODE REQTYPE,
       PHA.SEGMENT1 PONO,
       PHA.TYPE_LOOKUP_CODE POTYPE,
       APS.VENDOR_NAME SUPPLIERNAME,
       ASSA.VENDOR_SITE_CODE SUPPLIERSITE,
       RSH.SHIPMENT_NUM RECEIPTNO,
       AIA.INVOICE_ID INVID,
       AIA.INVOICE_NUM INVOICENO,
       AIA.INVOICE_AMOUNT INVAMOUNT,
       AIA.INVOICE_TYPE_LOOKUP_CODE INVTYPE,
       AIA.INVOICE_CURRENCY_CODE INVCURRENCY,
       AIPA.INVOICE_PAYMENT_ID PAYMENTID,
       AIPA.AMOUNT PAYMENTAMOUNT,
       ACA.CHECK_ID CHECKID,
       ACA.BANK_ACCOUNT_NAME BANKNAME,
       ACA.BANK_ACCOUNT_NUM BANKNO,
       ACA.BANK_ACCOUNT_TYPE BANKTYPE,
       GJH.JE_HEADER_ID BATCHHEADERID,
       GJH.JE_SOURCE BATCHSOURCE,
       GJB.JE_BATCH_ID BATCHID,
       GJB.NAME BATCHNAME
  FROM PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLA,
       PO_REQ_DISTRIBUTIONS_ALL PRDA,
       PO_DISTRIBUTIONS_ALL PDA,
       PO_LINE_LOCATIONS_ALL PLLA,
       PO_LINES_ALL PLA,
       PO_HEADERS_ALL PHA,
       AP_SUPPLIERS APS,
       AP_SUPPLIER_SITES_ALL ASSA,
       RCV_SHIPMENT_HEADERS RSH,
       RCV_SHIPMENT_LINES RSL,
       AP_INVOICES_ALL AIA,
       AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
       AP_INVOICE_PAYMENTS_ALL AIPA,
       AP_CHECKS_ALL ACA,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_JE_BATCHES GJB
 WHERE     PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
       AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
       AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
       AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
       AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
       AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
       AND APS.VENDOR_ID = PHA.VENDOR_ID
       AND ASSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
       AND APS.VENDOR_ID = ASSA.VENDOR_ID
       AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
       AND RSL.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
       AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
       AND APS.VENDOR_ID = AIA.VENDOR_ID
       AND AIDA.INVOICE_ID = AIA.INVOICE_ID
       AND AIPA.INVOICE_ID = AIA.INVOICE_ID
       AND AIPA.CHECK_ID = ACA.CHECK_ID
       AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
       AND TO_CHAR (AIDA.INVOICE_ID) = GJL.REFERENCE_2
       AND TO_CHAR (AIDA.DISTRIBUTION_LINE_NUMBER) = GJL.REFERENCE_3
       AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
       AND PRHA.SEGMENT1 = '100'                                            --Requisition Number

Also read : Procure to Pay Cycle flow


*/