Payables invoice prepayment query in Oracle Apps R12

SELECT   pv.vendor_name C_vendor_name,

         pvs.address_line1 C_address_line1,

         pvs.address_line2 C_address_line2,

         pvs.address_line3 C_address_line3,

            DECODE (pvs.city, '', '', pvs.city || ', ')

         || DECODE (pvs.state, '', '', pvs.state || ' ')

         || pvs.zip

            C_city_state_zip,

         pvs.country C_country,

         aipp.last_update_date C_application_date,

         aipp.prepayment_amount_applied C_amount_applied,

         inv.invoice_currency_code C_currency_code,

         pp.invoice_num C_prepay_num,

         inv.invoice_num C_invoice_num,

         NVL (inv.invoice_amount, 0) - NVL (inv.amount_paid, 0)

            C_amt_remaining

  FROM   ap_suppliers pv,

         ap_supplier_sites_all pvs,

         ap_invoices_all inv,

         ap_invoices_all pp,

         ap_invoice_prepays_all aipp

 WHERE       aipp.invoice_id = inv.invoice_id

         AND aipp.prepay_id = pp.invoice_id

         AND inv.vendor_id = pp.vendor_id

         AND inv.vendor_id = pv.vendor_id

         AND pv.vendor_id = pvs.vendor_id

         AND pvs.vendor_site_id = inv.vendor_site_id

         AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'

         AND aipp.last_update_date >= &InvDate

UNION

SELECT   pv.vendor_name C_vendor_name,

         pvs.address_line1 C_address_line1,

         pvs.address_line2 C_address_line2,

         pvs.address_line3 C_address_line3,

            DECODE (pvs.city, '', '', pvs.city || ', ')

         || DECODE (pvs.state, '', '', pvs.state || ' ')

         || pvs.zip

            C_city_state_zip,

         pvs.country C_country,

         aid2.last_update_date C_application_date,

         NVL (

            ap_invoices_utility_pkg.get_pp_amt_applied_on_date (

               inv.invoice_id,

               pp.invoice_id,

               aid2.last_update_date

            ),

            0

         )

            C_amount_applied,

         inv.invoice_currency_code C_currency_code,

         pp.invoice_num C_prepay_num,

         inv.invoice_num C_invoice_num,

         NVL (inv.invoice_amount, 0)

         - (ap_invoices_pkg.get_prepaid_amount (inv.invoice_id))

            C_amt_remaining

  FROM   ap_suppliers pv,

         ap_supplier_sites_all pvs,

         ap_invoices_all inv,

         ap_invoices_all pp,

         ap_invoice_distributions_all aid1,

         ap_invoice_distributions_all aid2

 WHERE       aid1.invoice_id = inv.invoice_id

         AND aid2.invoice_id = pp.invoice_id

         AND aid2.invoice_distribution_id = aid1.prepay_distribution_id

         AND aid1.line_type_lookup_code = 'PREPAY'

         AND inv.vendor_id = pp.vendor_id

         AND inv.vendor_id = pv.vendor_id

         AND pv.vendor_id = pvs.vendor_id

         AND pvs.vendor_site_id = inv.vendor_site_id

         AND NVL (aid1.reversal_flag, 'N') != 'Y'

         AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'

         AND inv.invoice_date >= &InvDat