AR Customer Credit Snapshot

âžœ Back to Library
Description
Categories: BI Publisher
Application: Receivables
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),