Procure to Pay Cycle Query

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'