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
Run
AR Customer Credit Snapshot and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |