Supplier Prepayment Balance Report

SELECT AIA.INVOICE_ID                                          ,
       aia.description,
       aia.org_id                                              ,
       aia.INVOICE_NUM                                         ,
       aia.INVOICE_DATE                                        ,
       aia.INVOICE_AMOUNT                                      ,
       aia.EXCHANGE_RATE                                       ,
       aia.INVOICE_AMOUNT*NVL(aia.EXCHANGE_RATE,1) Invoice_Inr ,
aia.attribute10 requested_by,
       aps.VENDOR_NAME                                         ,
 apC.CHECK_id,    
 apC.CHECK_NUMBER                                         ,
       apc.CHECK_DATE                      payment_date                             ,
       (apc.amount )                         paid_amount                              ,
       --apa.amount*NVL(apa.EXCHANGE_RATE,1) Paid_Inr                                 ,
       avp.invoice_num                     app_std_inv_num                          ,
       aia.invoice_amount                                                           ,
       avp.PREPAY_AMOUNT_APPLIED                                                    ,
       avp.INVOICE_ID APP_INVOICE_ID                                                ,
       avp.ACCOUNTING_DATE                                                          ,
       aia.invoice_currency_code                                                    ,
       hou.name                                                                     ,
       apss.VENDOR_SITE_CODE                                                        ,
       DECODE(EARLIEST_SETTLEMENT_DATE,'','PERMENENT','TEMPORARY') STATUS
FROM   AP_INVOICES_ALL aia            ,
       AP_INVOICE_PAYMENTS_ALL APA    ,
       AP_SUPPLIERS aps               ,
       ap_checks_all apc              ,
       AP_VIEW_PREPAYS_FR_PREPAY_V avp,
       hr_operating_units hou         ,
       ap_supplier_sites_All apss
WHERE  INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
AND    aps.vendor_id           =aia.vendor_id
AND    AIA.INVOICE_ID          =APA.INVOICE_ID
AND    apa.check_id            =apc.check_id
AND    aia.invoice_id          =avp.prepay_invoice_id
AND    APA.INVOICE_ID          =avp.prepay_invoice_id
AND    nvl(apa.reversal_flag,'a')      !='Y'
AND    aia.org_id              =hou.organization_id
AND    aia.vendor_id           =apss.vendor_id
AND    aia.vendor_site_id      =apss.vendor_site_id
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NAME_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NAME_TO,aps.VENDOR_id)
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NUM_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NUM_TO,aps.VENDOR_id)
AND    HOU.NAME=NVL(:P_OPERATING_UNIT,HOU.NAME)
AND    APA.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)
AND    AVP.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)


UNION

SELECT AIA.INVOICE_ID                                          ,
       aia.description,
       aia.org_id                                              ,
       aia.INVOICE_NUM                                         ,
       aia.INVOICE_DATE                                        ,
       aia.INVOICE_AMOUNT                                      ,
       aia.EXCHANGE_RATE                                       ,
       aia.INVOICE_AMOUNT*NVL(aia.EXCHANGE_RATE,1) Invoice_Inr ,
aia.attribute10 requested_by,
       aps.VENDOR_NAME                                         ,
 apC.CHECK_id,         
   apC.CHECK_NUMBER                                         ,
       apc.CHECK_DATE                      payment_date                             ,
      (apc.amount)                          paid_amount                              ,
     --  apa.amount*NVL(apa.EXCHANGE_RATE,1) Paid_Inr                                 ,
       NULL                                                                         ,
       aia.invoice_amount                                                           ,
       NULL                                                                         ,
       NULL                                                                         ,
       NULL                                                                         ,
       aia.invoice_currency_code                                                    ,
       hou.name                                                                     ,
       apss.VENDOR_SITE_CODE                                                        ,
       DECODE(EARLIEST_SETTLEMENT_DATE,'','PERMENENT','TEMPORARY') STATUS
FROM   AP_INVOICES_ALL aia         ,
       AP_INVOICE_PAYMENTS_ALL APA ,
       AP_SUPPLIERS aps            ,
       ap_checks_all apc           ,
       hr_operating_units hou      ,
       ap_supplier_sites_All apss
WHERE  INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
AND    aps.vendor_id           =aia.vendor_id
AND    AIA.INVOICE_ID          =APA.INVOICE_ID
AND    apa.check_id            =apc.check_id
AND    nvl(apa.reversal_flag,'a')      !='Y'
AND    aia.org_id              =hou.organization_id
AND    aia.vendor_id           =apss.vendor_id
AND    aia.vendor_site_id      =apss.vendor_site_id
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NAME_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NAME_TO,aps.VENDOR_id)
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NUM_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NUM_TO,aps.VENDOR_id)
AND    HOU.NAME=NVL(:P_OPERATING_UNIT,HOU.NAME)
AND    APA.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)