VenkataK_Oracle Applications Blog: Setting up Advanced Collections Dunning plans (Both Standard and Custom)

Dunning Requirement was to have 4 letters sent to customer Accounts. All 4 had custom templates and in the initial phase were sent to 3rd Party for printing and mailing. First 2 letters were setup via Dunning plans and the remaining 2 were manually identified and sent to SR via XML transmission. Here I will be covering the first 2 letters only as they involve the standard IEX setups to be done in the system.

Steps:

1) Create Custom Aging bucket as per the business requirements.

AR Super User/ Receivables Manager --> Setup --> Collections --> Aging Buckets

2) Use this Aging bucket in the Collections module
Collections Administrator --> Setup Checklist --> Operations Setup (OAF Page) --> general collections Information

Set up the email host and the From address in the correspondence Tab.

Collections Method Setup --> Create Dunning Plans

Use the Standard Account Scoring engine

Add the Aging Bucket Line by selecting the custom aging bucket lines that we created as the initial step

If you want to create custom scoring components to suit the business requirements, you need to use the Create Scoring Components (Need to have Advanced collections Full license)

SELECT NVL(DECODE(hca.CUSTOMER_CLASS_CODE,'STRATEGIC',20,'ACUTE',40,'AMB',60,'FEDERAL',80),100)

FROM hz_cust_accounts hca

WHERE hca.cust_account_id = :cust_account_id

AND EXISTS (SELECT 1 FROM iex_delinquencies del

WHERE del.cust_account_id = hca.cust_account_id

AND del.creation_date >= SYSDATE - 365

)

Create a new Scoring engine to utilize the custom scoring component:

Use the same component details as the standard Account scoring. For custom scoring component set up your range

Use the IEX_F_ACCOUNTS_V standard segment. You can test the segment (It should return count of all the accounts in your system that have delinquent transactions)

Create a new Dunning plan

3) Setting up custom RTF templates

XML Publisher Administrator --> Home --> Data Definitions

This should be setup in Collections Application and not the custom XX application

Create a Template to be associated with the data definition

Repeat these steps for all the custom RTF templates that are needed for the business

4) Creating the custom query to populate the custom RTF’s

Collections Administrator --> Administration --> Manage Queries

SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') currsysdate,

DECODE((per.person_first_name || per.person_last_name), null, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('RESPONSIBILITY', 'APS'), per.person_first_name) first_name,

per.person_last_name last_name,

org.party_name org_name,

loc.address1 address1,

loc.address2 address2,

loc.city city,

loc.state state,

loc.postal_code postal_code,

NVL(per.person_first_name,'Valued Client') first_name1,

(SELECT SUM(aps.amount_due_remaining)

FROM

iex_delinquencies_all dd,

ar_payment_schedules_all aps

WHERE

dd.payment_schedule_id = aps.payment_schedule_id AND

dd.party_cust_id = org.party_id AND

dd.cust_account_id = :ACCOUNT_ID) total_amount_due_remaining,

TO_CHAR(SYSDATE+14, 'MM/DD/YYYY') required_pay_date,

rs.source_name collector_name,

rs.source_job_title collector_title,

rs.source_phone collector_phone,

rs.source_mgr_name collector_supervisor,

(SELECT account_number FROM hz_cust_accounts

WHERE cust_account_id = :ACCOUNT_ID) ACCT_NUM,

CURSOR

(SELECT

NVL(SUM( (CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN -99999 AND 0 THEN AMOUNT_DUE_REMAINING END)),0) inv_current

, NVL(SUM( (CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 1 AND 30 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_1_to_30

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 31 AND 60 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_31_to_60

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 61 AND 90 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_61_to_90

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 91 AND 120 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_91_to_120

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) > 120 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_120_days_plus

FROM

ar_payment_schedules_all ps

, hz_cust_accounts hca

, ra_cust_trx_line_gl_dist_all hcatlda

, gl_code_combinations_kfv gcc

, ra_customer_trx_all hcata

, hz_cust_accounts hca2

WHERE

ps.status = 'OP'

AND ps.CUSTOMER_ID=hca.cust_account_id

AND ps.CUSTOMER_TRX_ID=hcatlda.CUSTOMER_TRX_ID

AND hcatlda.ACCOUNT_CLASS='REC'

AND hcatlda.latest_rec_flag = 'Y'

AND hcatlda.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID

AND ps.CUSTOMER_TRX_ID=hcata.CUSTOMER_TRX_ID

AND NVL(hcata.ATTRIBUTE5,ps.CUSTOMER_ID)=hca2.cust_account_ID

AND ps.CUSTOMER_ID= :ACCOUNT_ID

ORDER BY hca.account_name

) AS payment_history

FROM

HZ_LOCATIONS loc

,hz_parties org

,hz_parties per

,jtf_rs_resource_extns rs

WHERE

loc.location_id = :LOCATION_ID

AND org.party_id= :PARTY_ID

AND per.party_id = NVL(:CONTACT_ID, org.party_id)

AND rs.RESOURCE_ID = :RESOURCE_ID

5) Associating the custom RTF’s with the custom collections query

Collections Administrator --> Administration --> Manage Templates Query

Select the custom template and the custom dunning query and click on Apply

6) After all these setups have been completed, we need to submit the IEX: Scoring Engine Harness with the standard Account scoring

If we are using the custom scoring engine and components that were created earlier then use that particular custom scoring engine instead of the standard Account scoring

The scoring engine harness scores the Account using the standard or custom components and this score can be seen on the collections screen in the Accounts tab.