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
AS
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

Posted by: Eyup Tasan (3 post(s)).

Eyup Tasan is a Certified Oracle Financials Consultant and has been working for Logica for the past 4 years. Currently he's implementing Oracle eBS R12 Financials @ Waterschap Brabantse Delta in Breda.