AR Customer Credit Snapshot
Description
Categories: BI Publisher
Application: Receivables
Source: Customer Credit Snapshot (XML)
Short Name: ARXCCS_XML
DB package: AR_ARXCCS_XMLP_PKG
Source: Customer Credit Snapshot (XML)
Short Name: ARXCCS_XML
DB package: AR_ARXCCS_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
select ps.invoice_currency_code Currency_Bucket, sum(ps.amount_due_remaining) Aging_Balance_Outstanding, sum(decode(:rp_bucket_line_type_0, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_0, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_0, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1) Total_Cust_b0, sum(decode(:rp_bucket_line_type_1, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(to_number(:rp_bucket_days_from_1), ceil(trunc(sysdate)-ps.due_date)), least(to_number(:rp_bucket_days_to_1), ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1) Total_Cust_b1, sum(decode(:rp_bucket_line_type_2, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_2, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_2, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1) Total_Cust_b2, sum(decode(:rp_bucket_line_type_3, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_3, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_3, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1) Total_Cust_b3, sum(decode(:rp_bucket_line_type_4, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_4, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_4, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1) Total_Cust_b4, sum(decode(:rp_bucket_line_type_5, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_5, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_5, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1) Total_Cust_b5, sum(decode(:rp_bucket_line_type_6, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_6, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_6, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1) Total_Cust_b6, max(decode(ps.invoice_currency_code, :functional_currency, NULL, decode(ps.exchange_rate, NULL, '*', NULL))) Aging_Convert_Bucket , ps.customer_id ps_customer , ps.Customer_site_use_id ps_site, --AR_ARXCCS_XMLP_PKG.c_calc_percentformula() C_CALC_PERCENT, AR_ARXCCS_XMLP_PKG.C_CALC_PERCENTFormula(sum(ps.amount_due_remaining),sum(decode(:rp_bucket_line_type_0, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_0, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_0, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1), sum(decode(:rp_bucket_line_type_1, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(to_number(:rp_bucket_days_from_1), ceil(trunc(sysdate)-ps.due_date)), least(to_number(:rp_bucket_days_to_1), ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1),sum(decode(:rp_bucket_line_type_2, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_2, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_2, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1), sum(decode(:rp_bucket_line_type_3, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_3, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_3, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1), sum(decode(:rp_bucket_line_type_4, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_4, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_4, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1), sum(decode(:rp_bucket_line_type_5, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_5, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_5, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1),sum(decode(:rp_bucket_line_type_6, 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1), decode( greatest(:rp_bucket_days_from_6, ceil(trunc(sysdate)-ps.due_date)), least(:rp_bucket_days_to_6, ceil(trunc(sysdate)-ps.due_date)),1, 0) * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) * ps.amount_due_remaining * 1)) C_CALC_PERCENT, AR_ARXCCS_XMLP_PKG.c_compute_amtformula(:FUNCTIONAL_CURRENCY, :CUSTOMER_ID, :SITE_USE_ID, ps.invoice_currency_code, sum ( ps.amount_due_remaining )) C_COMPUTE_AMT, AR_ARXCCS_XMLP_PKG.c_high_inv_formulaformula(:FUNCTIONAL_CURRENCY, :CUSTOMER_ID, :SITE_USE_ID, ps.invoice_currency_code) C_HIGH_INV_FORMULA, AR_ARXCCS_XMLP_PKG.c_cust_hist_high_limit_amtform(:CUSTOMER_ID, :SITE_USE_ID) c_cust_hist_high_limit_amt, AR_ARXCCS_XMLP_PKG.c_rolling_summary_calcformula(:FUNCTIONAL_CURRENCY, :CUSTOMER_ID, :SITE_USE_ID, ps.invoice_currency_code) C_ROLLING_SUMMARY_CALC, AR_ARXCCS_XMLP_PKG.c_currency_lookupformula(:SITE_USE_ID, ps.invoice_currency_code) C_currency_lookup, AR_ARXCCS_XMLP_PKG.CP_DEFAULT_FLAG_p CP_DEFAULT_FLAG, AR_ARXCCS_XMLP_PKG.c_percent_b0_p c_percent_b0, AR_ARXCCS_XMLP_PKG.c_percent_b1_p c_percent_b1, AR_ARXCCS_XMLP_PKG.c_percent_b2_p c_percent_b2, AR_ARXCCS_XMLP_PKG.c_percent_b3_p c_percent_b3, AR_ARXCCS_XMLP_PKG.c_percent_b4_p c_percent_b4, AR_ARXCCS_XMLP_PKG.c_percent_b5_p c_percent_b5, AR_ARXCCS_XMLP_PKG.c_percent_b6_p c_percent_b6, AR_ARXCCS_XMLP_PKG.c_aging_on_account_p c_aging_on_account, AR_ARXCCS_XMLP_PKG.c_aging_unapplied_p c_aging_unapplied, AR_ARXCCS_XMLP_PKG.c_aging_convert_on_account_p c_aging_convert_on_account, AR_ARXCCS_XMLP_PKG.c_aging_convert_unapplied_p c_aging_convert_unapplied, AR_ARXCCS_XMLP_PKG.c_aging_credit_p c_aging_credit, AR_ARXCCS_XMLP_PKG.c_aging_convert_credit_p c_aging_convert_credit, AR_ARXCCS_XMLP_PKG.c_adjusted_balance_p c_adjusted_balance, AR_ARXCCS_XMLP_PKG.c_aging_convert_collection_p c_aging_convert_collection, AR_ARXCCS_XMLP_PKG.c_aging_in_collection_p c_aging_in_collection, AR_ARXCCS_XMLP_PKG.c_cust_hist_high_invoice_amt_p c_cust_hist_high_invoice_amt, --AR_ARXCCS_XMLP_PKG.c_cust_hist_conv_high_invoice c_cust_hist_conv_high_invoice, AR_ARXCCS_XMLP_PKG.c_cust_hist_conv_high_inv_p c_cust_hist_conv_high_invoice, -- AR_ARXCCS_XMLP_PKG.c_cust_hist_high_invoice_date c_cust_hist_high_invoice_date, AR_ARXCCS_XMLP_PKG.c_cust_hist_high_inv_date_p c_cust_hist_high_invoice_date, AR_ARXCCS_XMLP_PKG.c_cust_hist_high_limit_date_p c_cust_hist_high_limit_date, AR_ARXCCS_XMLP_PKG.c_ytd_nsf_count_p c_ytd_nsf_count, AR_ARXCCS_XMLP_PKG.c_ytd_convert_nsf_p c_ytd_convert_nsf, AR_ARXCCS_XMLP_PKG.c_ytd_nsf_amount_p c_ytd_nsf_amount, AR_ARXCCS_XMLP_PKG.c_ytd_conv_unearned_discount_p c_ytd_conv_unearned_discount, AR_ARXCCS_XMLP_PKG.c_ytd_unearned_discount_amoun c_ytd_unearned_discount_amount, -- AR_ARXCCS_XMLP_PKG.c_ytd_convert_earned_discount c_ytd_convert_earned_discount, AR_ARXCCS_XMLP_PKG.c_ytd_convert_earned_dis_p c_ytd_convert_earned_discount, AR_ARXCCS_XMLP_PKG.c_ytd_earned_discount_amount_p c_ytd_earned_discount_amount, AR_ARXCCS_XMLP_PKG.c_ytd_on_time_payments_count_p c_ytd_on_time_payments_count, AR_ARXCCS_XMLP_PKG.c_ytd_late_payments_count_p c_ytd_late_payments_count, AR_ARXCCS_XMLP_PKG.c_ytd_average_days_late_p c_ytd_average_days_late, AR_ARXCCS_XMLP_PKG.c_ytd_average_payment_days_p c_ytd_average_payment_days, AR_ARXCCS_XMLP_PKG.c_ytd_finance_charge_count_p c_ytd_finance_charge_count, AR_ARXCCS_XMLP_PKG.c_ytd_convert_finance_charge_p c_ytd_convert_finance_charge, AR_ARXCCS_XMLP_PKG.c_ytd_finance_charge_amount_p c_ytd_finance_charge_amount, AR_ARXCCS_XMLP_PKG.c_ytd_credit_count_p c_ytd_credit_count, AR_ARXCCS_XMLP_PKG.c_ytd_convert_credit_p c_ytd_convert_credit, AR_ARXCCS_XMLP_PKG.c_ytd_credit_amount_p c_ytd_credit_amount, AR_ARXCCS_XMLP_PKG.c_ytd_payment_count_p c_ytd_payment_count, AR_ARXCCS_XMLP_PKG.c_ytd_convert_payment_p c_ytd_convert_payment, AR_ARXCCS_XMLP_PKG.c_ytd_payment_amount_p c_ytd_payment_amount, AR_ARXCCS_XMLP_PKG.c_ytd_sales_count_p c_ytd_sales_count, AR_ARXCCS_XMLP_PKG.c_ytd_convert_sales_p c_ytd_convert_sales, AR_ARXCCS_XMLP_PKG.c_ytd_sales_amount_p c_ytd_sales_amount, AR_ARXCCS_XMLP_PKG.c_ytd_convert_writeoff_p c_ytd_convert_writeoff, AR_ARXCCS_XMLP_PKG.c_ytd_writeoff_amount_p c_ytd_writeoff_amount, AR_ARXCCS_XMLP_PKG.CP_limit_currency_p CP_limit_currency, AR_ARXCCS_XMLP_PKG.CP_related_currencies_p CP_related_currencies, AR_ARXCCS_XMLP_PKG.CP_txn_cur_p CP_txn_cur from ar_payment_schedules_all ps where ps.customer_id = :customer_id and ps.customer_site_use_id = :site_use_id and ps.class not in ('CM', 'PMT') and ps.org_id = :qcust_org_id &p_org_where_ps and ps.customer_id=:Customer_id and ps.Customer_site_use_id=:Site_Use_id group by ps.invoice_currency_code, ps.customer_id, ps.customer_site_use_id order by ps.invoice_currency_code |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Reporting Level |
|
LOV Oracle | |
Reporting Context |
|
LOV Oracle | |
Collector Name Low |
|
LOV Oracle | |
Collector Name High |
|
LOV Oracle | |
Customer Name Low |
|
LOV Oracle | |
Customer Name High |
|
LOV Oracle | |
Customer Number Low |
|
LOV Oracle | |
Customer Number High |
|
LOV Oracle | |
Bucket Name Low |
|
LOV Oracle | |
Bucket Name High |
|
LOV Oracle |