AP Prepayment Remittance Notice - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Prepayment Remittance Notice (XML) - Not Supported: Reserved For Future Use
Short Name: APXPPREM_XML
DB package: AP_APXPPREM_XMLP_PKG
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
Parameter NameSQL textValidation
Phone of Sender
 
Title of Sender
 
Name of Sender
 
To
 
Date
From
 
Date
Prepayment Number (skip for All)
 
LOV Oracle
Invoice Number (skip for All)
 
LOV Oracle
Supplier Name (skip for All)
 
LOV Oracle