AR Bad Debt Provision

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Bad Debt Provision Report
Application: Receivables
Source: Bad Debt Provision Report (XML)
Short Name: ARXBDP_XML
DB package: AR_ARXBDP_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.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
Operating Unit
 
LOV
Order By
 
LOV Oracle
Account Status Low
 
LOV Oracle
Account Status High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Number High
 
LOV Oracle