AR Billing History
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
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 |