AR Bad Debt Provision

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Bad Debt Provision Report (XML)
Short Name: ARXBDP_XML
DB package: AR_ARXBDP_XMLP_PKG
select
                ps.customer_id customer_id_ps,
               ps.trx_number trx_number,
               ctt.name   cust_trx_name,
                ps.trx_date   trx_date,
                ps.due_date  due_date,
                ps.invoice_currency_code  invoice_currency_code,
                ROUND(ps.amount_due_original * nvl(ps.exchange_rate,
                                         decode(ps.invoice_currency_code,
                                                :functional_currency_code,1,
                                                null)), 2)  amount_due_original,
                ROUND(ps.amount_due_remaining * nvl(ps.exchange_rate,
                                         decode(ps.invoice_currency_code,
                                                :functional_currency_code,1,
                                                null)), 2)  amount_due_remaining,
                ROUND(ps.amount_due_remaining * nvl(ps.exchange_rate,
                                         decode(ps.invoice_currency_code,
                                                :functional_currency_code,1,
                                                null)) *
                        (1 - :percent_collectable / 100), 2) provision,
                to_char(cct.collection_forecast) ||
                        decode(cct.collection_forecast,
                                null,'',
                                '%')  forecast_percent,
                cct.forecast_date  forecast_date,
                AR_ARXBDP_XMLP_PKG.D_Amount_Due_OriginalFormula(ROUND(ps.amount_due_original * nvl(ps.exchange_rate,
                                         decode(ps.invoice_currency_code,
                                                :functional_currency_code,1,
                                                null)), 2)) Temp
from
                ar_customer_call_topics cct,
                ra_cust_trx_types       ctt,
                ar_payment_schedules    ps
        where
                ps.cust_trx_type_id = ctt.cust_trx_type_id(+) and
                ps.payment_schedule_id  = cct.payment_schedule_id(+) and
                ps.class               != 'GUAR' and
                not exists (
                        select 'x'
                        from ar_customer_call_topics cct2
                        where cct2.payment_schedule_id = ps.payment_schedule_id
                        and   (cct2.call_date > cct.call_date
                                OR   (cct2.call_date = cct.call_date
                                and   cct2.customer_call_topic_id >
                                        cct.customer_call_topic_id))) and
                ps.status || ''         = 'OP'
 and ps.customer_id=:customer_id
order by
               -- ps.trx_number
               1,2,3,4,5,6,7,8,9,10,11
Parameter Name SQL text Validation
Group ID
 
User ID
 
Ledger
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Account Status High
 
LOV Oracle
Account Status Low
 
LOV Oracle
Order By
 
LOV Oracle