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,
inv1.invoice_currency_code C_currency_code,
pp.invoice_num C_prepay_num,
inv1.invoice_num C_invoice_num,
nvl(inv1.invoice_amount,0)-
GL_Currency_API.Convert_Amount(inv1.payment_currency_code,
inv1.invoice_currency_code,
inv1.payment_cross_rate_date,
inv1.payment_cross_rate_type,
nvl(inv1.amount_paid, 0))
C_amt_remaining
from po_vendors pv,
po_vendor_sites pvs,
ap_invoices inv1,
ap_invoices pp,
ap_invoice_prepays aipp
where aipp.invoice_id = inv1.invoice_id
and aipp.prepay_id = pp.invoice_id
and inv1.vendor_id = pp.vendor_id
and inv1.vendor_id = pv.vendor_id
and pv.vendor_id = pvs.vendor_id
and pvs.vendor_site_id = inv1.vendor_site_id
&LP_LANGUAGE_WHERE
&C_VENDOR_ID_PREDICATE
&C_INVOICE_ID_PREDICATE
&C_PREPAY_ID_PREDICATE
&C_START_DATE_PREDICATE
&C_END_DATE_PREDICATE
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_prepay_utils_pkg.get_pp_amt_applied_on_date(inv1.invoice_id,
pp.invoice_id,aid2.last_update_date),0) C_amount_applied,
inv1.invoice_currency_code C_currency_code,
pp.invoice_num C_prepay_num,
inv1.invoice_num C_invoice_num,
nvl(inv1.invoice_amount,0)-
GL_Currency_API.Convert_Amount(inv1.payment_currency_code,
inv1.invoice_currency_code,
inv1.payment_cross_rate_date,
inv1.payment_cross_rate_type,
nvl(inv1.amount_paid, 0))
C_amt_remaining
from po_vendors pv,
po_vendor_sites_all pvs,
ap_invoices inv1,
ap_invoices_all pp,
ap_invoice_distributions_all aid1,
ap_invoice_distributions_all aid2
where aid1.invoice_id = inv1.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 inv1.vendor_id = pp.vendor_id
and inv1.vendor_id = pv.vendor_id
and pv.vendor_id = pvs.vendor_id
and pvs.vendor_site_id = inv1.vendor_site_id
and nvl(aid1.reversal_flag,'N') != 'Y'
&LP_LANGUAGE_WHERE
&C_VENDOR_ID_PREDICATE
&C_INVOICE_ID_PREDICATE
&C_PREPAY_ID_PREDICATE
&C_START_INVOICE_DATE_PREDICATE
&C_END_INVOICE_DATE_PREDICATE |