AP Prepayment Report Query

/********************************************** 

    Creation Date : 04 October, 2016

    Purpose       : AP Prepayment Report

    Module        : Payables

    Parameters    : Supplier and Organization

    Created By    : Muhammad Waqas Khan

   

*********************************************/

SELECT VENDOR_NAME,

       ORG_ID,

       VOUCHER,

       C_INVOICE_NUM,

       CURR,

       INVOICE_DATE,

       GL_DATE,

       DESCRIP,

       (INVOICE_AMOUNT * EXCHANGE_RATE) INVOICE_AMOUNT,

       (ABS(PREPAY_AMOUNT) * EXCHANGE_RATE) PREPAY_AMOUNT,

       (REMAINING_AMOUNT * EXCHANGE_RATE) REMAINING_AMOUNT

  FROM (SELECT UPPER(C_VENDOR_NAME) VENDOR_NAME,

               ORG_ID,

               DESCRIP,

               DOC_SEQUENCE_VALUE,

               C_INVOICE_NUM,

               INVOICE_DATE,

               INVOICE_AMOUNT,

               PREPAY_AMOUNT,

               INVOICE_AMOUNT + PREPAY_AMOUNT REMAINING_AMOUNT,

               EXCHANGE_RATE,

               GL_DATE,

               CURR,

               (SELECT ACA.DOC_SEQUENCE_VALUE

                  FROM AP_INVOICE_PAYMENTS_ALL AIPA, AP_CHECKS_ALL ACA

                 WHERE AIPA.INVOICE_ID = X.INVOICE_ID

                   AND ACA.STATUS_LOOKUP_CODE = 'RECONCILED'

                   AND ACA.CHECK_ID = AIPA.CHECK_ID) VOUCHER

          FROM (SELECT DISTINCT PV.VENDOR_NAME AS C_VENDOR_NAME,

                                INV.DOC_SEQUENCE_VALUE,

                                INV.DESCRIPTION DESCRIP,

                                INV.ORG_ID,

                                INV.INVOICE_ID,

                                INV.INVOICE_NUM AS C_INVOICE_NUM,

                                INV.INVOICE_DATE,

                                NVL(INV.EXCHANGE_RATE, 1) EXCHANGE_RATE,

                                INV.INVOICE_CURRENCY_CODE CURR,

                                INV.INVOICE_CURRENCY_CODE AS C_CURRENCY_CODE,

                                INV.GL_DATE,

                                NVL(INV.INVOICE_AMOUNT, 0) AS INVOICE_AMOUNT,

                                (SELECT SUM(AMOUNT)

                                   FROM AP_INVOICE_DISTRIBUTIONS_ALL IDA

                                  WHERE LINE_TYPE_LOOKUP_CODE = 'PREPAY'

                                    AND AID.INVOICE_DISTRIBUTION_ID =

                                        IDA.PREPAY_DISTRIBUTION_ID) AS PREPAY_AMOUNT

                  FROM PO_VENDORS                   PV,

                       AP_INVOICES_ALL              INV,

                       AP_INVOICE_DISTRIBUTIONS_ALL AID,

                       AP_INVOICE_PREPAYS_ALL       AIPP

                 WHERE PV.VENDOR_ID = INV.VENDOR_ID

                   AND INV.INVOICE_ID = AID.INVOICE_ID

                   AND AIPP.INVOICE_ID(+) = INV.INVOICE_ID

                   AND PV.VENDOR_ID = NVL(:P_VENDOR_ID, PV.VENDOR_ID)

                   AND AID.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'

                   AND INV.ORG_ID = :P_ORG_ID --234

                --AND INV.INVOICE_NUM = 'PTC2014/380'

                ) X

         WHERE INVOICE_AMOUNT - PREPAY_AMOUNT IS NOT NULL)

 WHERE REMAINING_AMOUNT > 0

 ORDER BY 1, 6;