AR Billing History

Description
Categories: BI Publisher, Financials
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
Ledger Id
 
LOV Oracle
Transaction Date High
 
Date
Transaction Date Low
 
Date
Term Name High
 
LOV Oracle
Term Name Low
 
LOV Oracle
Collector Name High
 
LOV Oracle
Collector Name Low
 
LOV Oracle
Invoice Number High
 
LOV Oracle
Invoice Number Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle