Dunning letter -Setup steps

Advanced Collections setup steps

1. Creating a user account and assigning responsibilities

In System Administrator Responsibility> Security>User>Define

Enter a Username and Password

Assign the following responsibilities:
• System Administrator
• Application Developer
• Receivables Manager
• Collections Agent
• Collections Forms Administrator
• CRM Resource Manager
• Collections HTML Admin
• Telesales Agent
• Human Resources Manager
• CRM Administrator

2. Setting up an Employee
• Navigate to HR Responsibility
• Setup employee
• People>Enter & Maintain
• Enter name, birthday, gender, SSN and Save
• Note the employee number

3. Attach the employee name to the user name
• Navigate to System Administrator Responsibility
• Security>User>Define
• Query your Username
• Enter the Full Name as it appeared in the HR setup screen above
• Save

4. Create a group

a. Navigation : CRM Resource Manager -> Maintain Resources -> Groups
b. Name/Start Date (you can use anything)
c. -Assign Usages
i. Collections
d. -Roles
e. Collections Agent

Test Dunning Group

5. Import Resource

a. Navigation : CRM Resource Manager -> Maintain Resources -> Import Resources
b. Resource Cat = Employee
c. Enter Employee # that was noted previously
d. Click search/select employee
e. Click start import

f. select start date of the resource

g. Click OK.

h. Save Resource.

i. Click Details
j. From the Resource Management Form, enter the Username
k. Click on Roles Tab (Security & Access)
Add the following roles:
Collections Role Type
Collections Agent Role

l. Click on the Group Tab
Query Group you created previously
Close the form

m. Add yourself as an employee to the group
Close the form.


1. Changing Data Template

a. Navigate to: XML Publisher Administrator
b. Click on Data Definition tab

c. Search for: Collection XML Data Source
d. Update the template you want to use for your dunning letters

e. Manage Template query

XXX -NL-BE Dun Letter – STG1
XXX -NL-BE Dun Letter – STG2
XXX -NL-BE Dun Letter – STG3

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,
per.person_first_name first_name1,
(select sum(aps.amount_due_remaining)
iex_delinquencies_all dd,
ar_payment_schedules_all aps
dd.payment_schedule_id = aps.payment_schedule_id and
dd.party_cust_id = org.party_id and
dd.cust_account_id = :ACCOUNT_ID and
dd.customer_site_use_id = :CUSTOMER_SITE_USE_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,
ct.trx_number invoice_number,
to_char(ar.due_date, ‘MM/DD/YYYY’) due_date,
ar.amount_due_remaining amount_due_remaining
iex_delinquencies_all d,
ar_payment_schedules_all ar,
ra_customer_trx_all ct
d.party_cust_id = org.party_id
and d.cust_account_id = :ACCOUNT_ID
and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID
and d.payment_schedule_id = ar.payment_schedule_id
and d.status = ‘DELINQUENT’
and ar.customer_trx_id = ct.customer_trx_id
and ar.amount_due_remaining <> 0
) as payment_history
,hz_parties org
,hz_parties per
,jtf_rs_resource_extns rs
loc.location_id = :LOCATION_ID
and org.party_id= :PARTY_ID
and per.party_id = nvl(:CONTACT_ID, org.party_id)
g. Verify reference exist between XDO Template and Collections Query
h. Review the existing queries in the table based on Dunning Level(Strategy level) in this case, we are using BillTo.
i. Insert into row into IEX_QUERY_TEMP_XREF table connecting Template and Query Use the sequence value from IEX_QUERY_TEMP_XREF_S for QUERY_TEMP_ID
Receivables -> Setup -> Collections -> Aging Buckets

XXX Aging Bucket

2. Setting up for Dunning Plans

a. Navigation: Collections Administrator -> Setup Checklist
b. Click on Home tab

c. Click on Edit Questionnaire for Operations Setup
d. Check the Bill To radio button and check box for collection / dunning level

e. Click Next leave default.
f. Click Next.
g. Click on Dunning Plan radio button for collections method

3. Create Scoring Components
a. Navigation: Collections Administrator -> Setup Checklist -> Click on tab Collections Method Setup
b. Click on Create Scoring Component

c. Give a name
d. Enable the check box
e. Select type as Bill To
f. Select Value Type as Select Statement
g. Give any valid SQL

XXX Dunning Comp


h. Click on apply.

4. Create Scoring Engine

a. Click on Create scoring Engine tab under Collections Method Setup
b. Click on Create Scoring Engine button

c. Fill up the form as in screen shot and click next

XXX Dunning Scoring engine

d. Click on Add Score Component

e. Search you Scoring Component and select it
f. Key in the weight for that component and click on recalculate button
g. Define ranges for you component and click next

h. Define segments and click next

5. Create Dunning Plans

a. Go to create dunning plans tab under Collections Method Setup

b. Fill in the form and select the aging bucket you want to use for this dunning plan, click next

Dunning Plan
c. Select the scoring engine you created and click next

d. Define your dunning plan as per the score and assign dunning letter template and click next

e. Click finish

6. Define Contacts with dunning purpose

7. Running Concurrent Programs

• IEX: Scoring Engine Harness:

You can select up to five scoring engines to run at the same time. The scoring harness assigns a value to an object such as a customer, account, or bill to location. Another score determines whether transactions are delinquent, pre-delinquent, or current. At a minimum, you must run a scoring engine that scores transactions to create delinquencies; and then run a scoring engine to score the level of your dunning plan (customer, account, or bill to location).


• IEX: Collections Bucket profile for your aging bucket.
• IEX: Send Dunnings for delinquent customers:

This program sends the results of the scoring engine harness to Oracle One-to-One Fulfillment to send out dunning

IEX: Purge Score History Table
The concurrent program IEX: Score History Purge: Purges historical data stored in the IEX_SCORE_HISTORIES table. Run this program if you do not use historical data.

The parameters:
a) Score Object ID: This parameter will clean up score history for a single object, ‘Customer/Account/Site/Transaction’. For example if you pick a customer you can say to clean all scores for customer = ‘Business World’.

b) Score Object Code: This parameter will clean up all the history for the type of objects. For example, you can set it to clean up all scores for transactions.

c) Request ID: This parameters will clean up all the scores generated by a concurrent program request. If you ran a concurrent program with a scoring engine and did not like the result, you can basically wipe it clean by entering the request id.

d) The recommendation is for administrator to run this at least once a week to clean up transaction histories. OR to run this and leave all the fields blank and only select ‘Save Last Run’ = ‘Y’, which will cause the concurrent program to clean all the score history for all objects BUT it will leave the last score created for each object. So if you run scoring for customers for a year, if you use this option all the scores except the last one for each of your customers will be deleted.

About Asokan
I have around 10 years of Working Experience in the Industry. Have done various roles like Developer, Lead, Architect etc.

14 Responses to Dunning letter -Setup steps

  1. Candi Shelgren says:

    Would you happen to have something similar for setting up Dunning Strategies and where a strategy will auto close if a strategy has been sent and now the invoice is closed and new invoice needs letter one not the next letter that is in the sequence?

  2. prakash says:

    Excellent article.

    • Asokan says:

      Thank you

  3. shivprasad bajaj says:


    • Asokan says:

      Thank you

  4. Mike says:

    Great article. Thanks for taking the time to write this. Only asking this because you seem to have advanced knowledge of advanced collections. (pun intended). Have you ever been able to add the concurrent request id or the xml_request_id from iex_xml_request_histories table to the XML that is generated? I am trying to add this information to the RTF template of my dunning letter and can’t seem to do it via the query editor.

  5. Tony says:

    Good work – Brother || Excellent work you done here || you save my life ;-)

  6. Usman says:


    Could you please provide “Direct Debit Process” in AR with screen shots……

  7. Ali Hussien says:

    What a great article ..!
    many thanks for your efforts
    and sharing knowledge.
    Best Regards;

    • Asokan says:

      Thank you !!

  8. Great Article

  9. alok kumar says:

    hiii asokan, its indeed the most insightful article on this. we have a dunning plan configured based on days overdue method but we now want it to be staged. we have configured a staged dunning plan but the output remains empty.

  10. SSagar says:

    Hi, This article is so awesome.. I have done the setup according to the steps here. But can you please help me in explaining how to start using this. some thin I have missed I think . For example the profile options mentioned last , I could not get.

    Can you please send me the profile options to be set screenshots and user guide how to use this.

    My mail id is sagarorissa@gmail.com

  11. prasanna says:

    Hi it was fanstatic document, but i have a issue like I have changed my collector & assigned the same new collector at customer level but could not able to see that in Collections assigned was previous collector how to get the new collector at collection agent level

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 180 other followers

%d bloggers like this: