Thursday, September 9th, 2010

R12: Create dunning letters at delinquency level

Since receivables dunning letters have been obsoleted in R12, it’s hard to find useful information on the Oracle Support site about dunning letters in R12, especially about running dunning letters at delinquency level. In R12 now you will need to run Oracle Advanced Collections Dunning Plans instead.

A couple of months ago one of our customers asked me to find out if it’s possible to send dunning letters at delinquency level per customer/transaction type. They were using 4 transaction types and wanted also a different dunning letter template for each transaction type, so I was facing another challenge on that.

I knew before I started my research that dunning letters in R12 were based on transaction scoring. You have to define one or more score components, score-engines and dunning plans to get this all working. Oracle delivers seeded score components and score-engines, but all that seeded stuff wasn’t really helpful for me.

First thing I did was searching for more information on the Oracle Support site and reading the implementation guide to get an idea where to begin with the setup. As you can guess I could find hardly any information about dunning letters at delinquency level. All information I found was about dunning letters at customer level, because 95% of the Oracle eBS customers is using dunning letters at customer level.

The second step of my research was contacting Oracle Support by asking them if they had experience on this. The answer of Oracle Support to my question was as follows: “Basically we can only provide limited support on custom setup on scoring components and scoring engines”.

I Finally got it all working by executing the following 10 steps:

Step 1. Create a view per transaction type id. The custom(new) view must be named with IEX_F as the first 5 characters. Advanced Collections will pick up the view and display it in the LOV.

CREATE OR REPLACE view iex_f_score_trans1_v
SELECT ar.payment_schedule_id
FROM   ar_payment_schedules ar
WHERE  ar.cust_trx_type_id = 1004
AND (( ar.CLASS IN (‘INV’,'DM’,'CB’) AND ar.status = ‘OP’ )
OR ( ar.CLASS IN (‘INV’,'DM’,'CB’) AND ar.status = ‘CL’ AND EXISTS (SELECT 1
FROM iex_delinquencies_all dll
WHERE dll.payment_schedule_id = ar.payment_schedule_id
AND  dll.status = ‘DELINQUENT’ )))

Note: create the view with your own transaction type id. You can find the transaction type id from ‘RA_CUST_TRX_TYPES_ALL’


Step 2.  Set up collection level. You have to set up collection level at which you are going to do business. Set the level to ‘Delinquency’.

Step 3.  Set up collection method. You have set the collection method to Dunning Plan.


Step 4.  Duplicate ‘Delinquency Status Determination’ Score Engine.

Step 5.   Rename the template.


Step 6.  Add segment to score engine by selecting the view name created from step 1.

Step 7.  Create a new Dunning plan. Dunning level must be set to ‘Delinquency’.

Step 8.  Repeat step 1-7  for every transaction type you want to send dunning letters for.

Step 9.  Run IEX: Scoring Engine Harness Concurrent Program by the following order. You can run this program for all your transaction types at once to get a score.


Step 10.  Run IEX: Send Dunnings for delinquent customers Concurrent Program. You can only run this program for one dunning plan at the time.

 Viewed 5752 times by 2549 visitors

Category: Finance
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses (last comment shown first)

June 1, 2011

Hi Eyup,

What is the difference running dunning plan between dunning level ACCOUNT and DELIQUENCY?


[Reply to this comment]

May 17, 2011
Aniruddha Rave
Aniruddha Rave

Hi Eyou,
Good & useful information,
Just wanted one clarification.

How does one implement the same without using Advanced Collections License?
OR is it that is we intend to send Dunning Letters, it has to be through Oracle Advanced Collections only?

Hong Kong

[Reply to this comment]

Eyup Tasan

Eyup Tasan Reply:

You can just use the basic functionality of Oracle Advanced Collection without purchasing a license.


Eyup Tasan

[Reply to this comment]