AR Billing History

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