AR Billing History

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Billing History
Application: Receivables
Source: Billing History (XML)
Short Name: ARXCBH_XML
DB package: AR_ARXCBH_XMLP_PKG 
select
         cust.cust_account_id customer_id,
         substrb(party.party_name,1,50) customer_name,
         cust.account_number customer_number,
         arpt_sql_func_util.get_trx_type_details(ct.cust_trx_type_id,'NAME')  Name,
         ps.customer_trx_id,
         ps.trx_number  Invoice_No,
         ct.purchase_order,
         ps.due_date,
         ps.amount_due_original Invoice_amt,
         ps.amount_due_remaining Balance_amt,
         ps.trx_date,
         ps.payment_schedule_id,
         ps.invoice_currency_code Currency_Code,
         adr.cust_acct_site_id address_id,
         loc.city,
         loc.state,
         nvl(ps.amount_applied,0) Payment_Amt,
         nvl(ps.amount_adjusted,0) Adjustments,
         nvl(ps.amount_credited,0) Credit_memo,
	AR_ARXCBH_XMLP_PKG.set_prn_flagformula(adr.cust_acct_site_id) Set_Prn_Flag,
	AR_ARXCBH_XMLP_PKG.Addr_Prn_Flag_p Addr_Prn_Flag,
	AR_ARXCBH_XMLP_PKG.Previous_Addr_Id_p Previous_Addr_Id,
	AR_ARXCBH_XMLP_PKG.cf_currency_flagformula(ps.payment_schedule_id) CF_CURRENCY_FLAG,
	AR_ARXCBH_XMLP_PKG.D_INVOICE_AMOUNTFormula(substrb(party.party_name,1,50))
from
         ar_payment_schedules ps,
         ra_customer_trx ct,
         hz_cust_site_uses su,
         hz_cust_acct_sites adr,
         hz_party_sites party_site,
          hz_locations loc,
&P_FROM_1              ra_terms ter,
         hz_cust_accounts cust,
         hz_parties party
where  ps.customer_trx_id      = ct.customer_trx_id
and      ct.previous_customer_trx_id is null
and    ct.bill_to_site_use_id = su.site_use_id
and      adr.cust_account_id         = cust.cust_account_id(+)
and     cust.party_id = party.party_id(+)
and      ct.term_id              = ter.term_id(+)
and      su.cust_acct_site_id           = adr.cust_acct_site_id
and  adr.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
&lp_in_customer_num_low
&lp_in_customer_num_high
&lp_in_invoice_number_low
&lp_in_invoice_number_high
&lp_in_trx_date_low
&lp_in_trx_date_high
&lp_in_collector_low
&lp_in_collector_high
&P_TERMS_NAME
&P_TERMS_NAME1
&P_WHERE_11
&P_WHERE_12
&P_WHERE_2
and     ps.class != 'PMT'
/*order
by     party.party_name,
        cust.cust_account_id,
        ps.invoice_currency_code,
        ps.trx_number,
        ps.due_date*/
order by 1,2,3,13,6,5,8,4,11,7,9,10,17,19,18,12
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Customer Name Low 
 
 LOV Oracle
Customer Name High 
 
 LOV Oracle
Customer Number Low 
 
 LOV Oracle
Customer Number High 
 
 LOV Oracle
Invoice Number Low 
 
 LOV Oracle
Invoice Number High 
 
 LOV Oracle
Collector Name Low 
 
 LOV Oracle
Collector Name High 
 
 LOV Oracle
Term Name Low 
 
 LOV Oracle
Term Name High 
 
 LOV Oracle
Transaction Date Low 
 
 Date
Transaction Date High 
 
 Date
Download
Related Blitz Reports
JG Regional Dunning Letter - draft AR Aging AR Receipt Analysis - Days Late AR Transaction Detail AR Open Items Revaluation AR Customer Credit Limits GL Account Analysis (Distributions) XX AR Aging JG Receipt Acknowledgment Letter- Not Supported: Reserved For Future Use
Blitz Report™

Blitz Report™ provides multiple benefits: