AR Bills Receivable Reminder Letters

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Bills Receivable Reminder Letters (XML)
Short Name: ARBRRMLT_XML
DB package: AR_ARBRRMLT_XMLP_PKG
Select
                   invoice_currency_code                              currency_code,
                   substrb(party.party_name,1,50)       cust_name,
                   trx.trx_number               br_number,
                   br_amount            br_amount,
                   trx.trx_date             br_date,
                   rtrim(loc.address1)          remit_to_address1,
                   loc.address2         remit_to_address2,
                   loc.address3         remit_to_address3,
                   loc.address4         remit_to_address4,
                   loc.postal_code          remit_postal_code,
                   loc.state                                            remit_state,
                   loc.city             remit_city,
                   loc.country          remit_country,
                   loc2.address1            cust_address1,
                   loc2.address2            cust_address2,
                   loc2.address3            cust_address3,
                   loc2.address4            cust_address4,
                   loc2.postal_code             cust_postal_code,
                   loc2.state           cust_state,
                   loc2.city            cust_city,
                   loc2.country         cust_country,
                AR_ARBRRMLT_XMLP_PKG.cf_br_dateformula(trx.trx_date) CF_BR_DATE,
                AR_ARBRRMLT_XMLP_PKG.customer_address_stringformula(substrb ( party.party_name , 1 , 50 ), loc2.address1, loc2.address2, loc2.address3, loc2.address4, loc2.city, loc2.state, loc2.postal_code, loc2.country) customer_address_string
            from
                    hz_cust_accounts    cust,
                    hz_parties      party,
                    ra_cust_trx_types    type,
                    ra_customer_trx       trx,
                    hz_cust_acct_sites            ad,
                   hz_party_sites party_site,
                  hz_locations loc,
                    hz_cust_site_uses             su,
                    hz_cust_acct_sites           ad2,
                    hz_party_sites  party_site2,
                   hz_locations loc2,
                    ar_transaction_history th
            where
                       trx.set_of_books_id      =:p_set_of_books_id
            and     trx.cust_trx_type_id        =  type.cust_trx_type_id
            and     type.type                       = 'BR'
            and     trx.remit_to_address_id         = ad.cust_acct_site_id (+)
            and     ad.party_site_id        = party_site.party_site_id(+)
            and     loc.location_id(+)      = party_site.location_id
            and     cust.cust_account_id = trx.drawee_id
            and     cust.party_id = party.party_id
            and type.signed_flag = 'Y'
            and     su.site_use_id     = trx.drawee_site_use_id
            and     su.cust_acct_site_id      = ad2.cust_acct_site_id
            and     ad2.party_site_id   = party_site2.party_site_id
            and     loc2.location_id    = party_site2.location_id
            and     th.current_record_flag = 'Y'
            and     th.status = 'PENDING_ACCEPTANCE'
            and     th.customer_trx_id = trx.customer_trx_id
            &lp_customer_range
            &lp_creation_date
            &lp_customer_number_range
			ORDER BY cust_name, cust_address4, cust_address1, cust_address2, cust_address3,
			cust_postal_code, cust_state, cust_city, cust_country,
			remit_to_address3, remit_to_address1, remit_to_address2, remit_to_address4,
			remit_postal_code, remit_state, remit_city, remit_country,
			currency_code, br_date, br_number, br_amount, party.party_name
Parameter NameSQL textValidation
Ledger Id
 
LOV Oracle
Drawee Number To
 
LOV Oracle
Drawee Number From
 
LOV Oracle
Drawee Name To
 
LOV Oracle
Drawee Name From
 
LOV Oracle
Days Late Since Creation
 
Number
Creation Date To
 
Date
Creation Date From
 
Date