[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_CNT_PUB
Source
1 PACKAGE BODY IEX_STRATEGY_CNT_PUB AS
2 /* $Header: iexpstcb.pls 120.37.12010000.15 2009/01/22 13:20:20 pnaveenk ship $ */
3 /*
4 * This procedure needs to be called with an itemtype and workflow process
5 * which'll launch workflow .Start Workflow will call workflow based on
6 * Meth_flag in methodology base table
7 */
8
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_STRATEGY_CNT_PUB';
10 PG_DEBUG NUMBER(2) ;
11 l_enabled varchar2(5) ;
12
13 l_DelStatusCurrent varchar2(30) ;
14 l_DelStatusDel varchar2(30) ;
15 l_DelStatusPreDel varchar2(30) ;
16 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
17 l_DelStatusClose varchar2(30) ;
18 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
19
20 l_StratStatusOpen varchar2(30) ;
21 l_StratStatusOnhold varchar2(30);
22 l_StratStatusPending varchar2(30);
23 l_StratStatusClosed varchar2(30) ;
24 l_StratStatusCancelled varchar2(30) ;
25 l_Yes varchar2(1) ;
26 l_No varchar2(1) ;
27 l_StratObjectFilterType varchar2(10) ;
28
29 --Bug# 6870773 Naveen
30 l_org_enabled varchar2(1);
31 l_org_id number;
32
33
34 /* Procedure for open strategy for customer, Account, bill_to, and delinquencies based on the strategy run level*/
35 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
36 --PROCEDURE open_strategies
37 --(
38 -- ERRBUF OUT NOCOPY VARCHAR2,
39 -- RETCODE OUT NOCOPY VARCHAR2
40 --) IS
41 --Bug# 6870773 Naveen
42 PROCEDURE update_strat_org
43 (
44 ERRBUF OUT NOCOPY VARCHAR2,
45 RETCODE OUT NOCOPY VARCHAR2
46 );
47
48 PROCEDURE open_strategies
49 (
50 ERRBUF OUT NOCOPY VARCHAR2,
51 RETCODE OUT NOCOPY VARCHAR2,
52 p_ignore_switch IN VARCHAR2
53 )
54 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
55 IS
56 l_result VARCHAR2(10);
57
58 l_error_msg VARCHAR2(2000);
59 l_return_status VARCHAR2(20);
60 l_msg_count NUMBER;
61 l_msg_data VARCHAR2(2000);
62 l_api_name VARCHAR2(100) ;
63 l_api_version_number CONSTANT NUMBER := 2.0;
64
65 vStrategyStatus VARCHAR2(30);
66 vStrategyStatus1 VARCHAR2(30); --Added for bug#5126770 schekuri 04-Apr-2006
67 vOrginalStrategyStatus VARCHAR2(30); --Added for bug#5202312 by schekuri on 05-May-2006
68 -- ctlee score tolerance checking
69 vScoreValue number;
70 vStrategyRank VARCHAR2(10);
71 vScoreTolerance number;
72 vStrategyId number;
73 vChangeStrategy VARCHAR2(4);
74 vStrategyTemplateId number;
75
76 l_strategy_processid NUMBER;
77 l_delinquency_id number;
78 l_party_cust_id number;
79 l_cust_account_id number;
80 l_transaction_id number;
81 l_payment_schedule_id number;
82 l_object_id number;
83 l_object_code varchar2(40);
84 l_strategy_id number;
85 l_strategy_template_id number;
86 l_object_version_number number := 1.0;
87 l_strat_count number:=0; --Added for bug#7594370 by PNAVEENK
88
89 Cursor c_score_exists( p_object_id number, p_object_type varchar2) is
90 select score_value
91 from iex_score_histories
92 where score_object_id = p_object_id
93 and score_object_code = p_object_type
94 order by creation_date desc;
95
96 l_stry_cnt_rec IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE ;
97
98 -- Begin - Andre - bug#4551569 - Change cursor to find 2 types of objects
99 Cursor c_score_exists_del(p_object_id number, p_object_type varchar2, p_object_id2 number, p_object_type2 varchar2) is
100 select score_value, score_object_id, score_object_code
101 from iex_score_histories
102 where score_object_id in (p_object_id, p_object_id2)
103 and score_object_code in (p_object_type, p_object_type2)
104 order by creation_date desc;
105
106 l_score_object_id number;
107 l_score_object_code varchar2(40);
108 -- End - Andre - bug#4551569 - Change cursor to find 2 types of objects
109
110 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
111 l_default_rs_id number ;
112 l_resource_id NUMBER;
113 l_StrategyTempID number;
114 b_Skip varchar2(10);
115
116
117 TYPE c_open_delinquenciesCurTyp IS REF CURSOR; -- weak
118 c_open_delinquencies c_open_delinquenciesCurTyp; -- declare cursor variable
119
120 TYPE c_strategy_existsCurTyp IS REF CURSOR; -- weak
121 c_strategy_exists c_strategy_existsCurTyp; -- declare cursor variable
122
123 -- get IEX Strategy grace period
124 vGracePeriod Date;
125 l_gracePeriod NUMBER ;
126 TYPE c_gracePeriodCurTyp IS REF CURSOR; -- weak
127 c_gracePeriod c_gracePeriodCurTyp; -- declare cursor variable
128
129 pre_delinquency_flag varchar2(1) ;
130 vCheckList varchar2(1) ;
131
132 -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
133 l_id_save number;
134 -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
135
136 -- bug 4141678 begin - ctlee
137 l_batch_size NUMBER ;
138 l_save_count NUMBER ;
139 l_commit_count NUMBER ;
140 -- TYPE STRATEGY_ID_TBL_type is Table of IEX_strategies.strategy_id%TYPE INDEX BY BINARY_INTEGER;
141 -- l_strategy_tbl STRATEGY_ID_TBL_TYPE;
142 -- bug 4141678 end - ctlee
143
144 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
145 l_ignore_switch varchar2(1) := 'N';
146 l_del_query varchar2(2500);
147 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
148
149 l_temp_grace_period number:=0; --Added for bug#7594370 by PNAVEENK
150
151
152
153 BEGIN
154 -- initialize variable here
155 l_api_name := 'START_WORKFLOW';
156 l_stry_cnt_rec := IEX_STRATEGY_TYPE_PUB.INST_STRY_CNT_REC;
157 l_default_rs_id := NVL(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'), 0);
158 l_resource_id := NVL(fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE'), 0);
159 b_Skip := 'F';
160 l_gracePeriod := NVL(to_number(FND_PROFILE.VALUE('IEX_STRY_GRACE_PERIOD')), 0);
161 pre_delinquency_flag := 'N';
162 vCheckList := 'N';
163 l_batch_size := NVL(to_number(FND_PROFILE.VALUE('IEX_BATCH_SIZE')), 5000);
164 l_save_count := 0;
165 l_commit_count := 0;
166 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
167 l_ignore_switch := NVL(p_ignore_switch, 'N');
168 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
169
170
171 -- dbms_session.set_sql_trace(true);
172 -- Initialize API return status to SUCCESS
173 l_return_status := FND_API.G_RET_STS_SUCCESS;
174
175 /* Check the required profiles for Strategy Concurrent before starting */
176 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
177 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation aborted. ' );
178 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Disabled by Profile ');
179 return;
180 end if;
181
182 if (l_DefaultStrategyLevel = 50) Then
183 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
184 write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Run Level from IEX_APP_PREFERENCES ');
185 b_Skip := 'T';
186 end if;
187
188 l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
189 if (l_StrategyTempID = 0) Then
190 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
191 write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Template Profile ');
192 b_Skip := 'T';
193 end if;
194
195 if (l_default_rs_ID = 0) Then
196 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
197 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Default Resource Profile not set. IEX: Strategy Default Resource ');
198 b_Skip := 'T';
199 end if;
200
201 if (l_resource_ID = 0) Then
202 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
203 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Fulfilment Resource Profile not set. ');
204 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Fulfilment Resource should be configured for fulfilment ');
205 b_Skip := 'T';
206 end if;
207
208 if (b_Skip = 'T') then
209 retcode := '2';
210 return;
211 end if;
212
213 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation started. ' );
214 -- Standard Start of API savepoint
215 SAVEPOINT START_STRY_CONT;
216
217 begin
218 SELECT decode(COUNT(*), 0, 'N', 'Y') into pre_delinquency_flag FROM IEX_STRATEGY_TEMPLATES_VL
219 WHERE CATEGORY_TYPE = l_DelStatusPreDel;
220 EXCEPTION
221 WHEN OTHERS THEN
222 fnd_file.put_line(FND_FILE.LOG, 'Pre Delinquency flag raised exception; sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
223 end;
224
225 write_log(FND_LOG.LEVEL_UNEXPECTED, 'pre-delinquency strategy template flag = ' ||pre_delinquency_flag);
226
227 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
228 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Do not automatically switch strategies flag = ' ||l_ignore_switch);
229 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Grace Period = ' || l_graceperiod);
230 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
231
232 write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor started ');
233
234 /* Check the strategy Run level */
235 /* ctlee - add status and pass it to GetTemplateId 7/3/2003 */
236 IF l_DefaultStrategyLevel = 10 THEN
237 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
238 --OPEN c_open_delinquencies
239 -- FOR
240 l_del_query := 'select d.party_cust_id, null, null, null, null, null,';
241 l_del_query := l_del_query || ' d.party_cust_id object_id, ''PARTY'' object_type, null';
242 l_del_query := l_del_query || ' , 10 strategy_level, d.PARTY_CUST_ID jtf_object_id, ''PARTY'' jtf_object_type';
243 l_del_query := l_del_query || ' , null status';
244 l_del_query := l_del_query || ' from iex_delinquencies_all d';
245 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
246 --Bug#6870773 Naveen
247 if l_org_enabled = 'Y' then
248 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
249 end if;
250
251 if l_ignore_switch = 'Y' then
252 --Bug#520231 schekuri 05-MAY-2006
253 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
254 l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''PARTY'' and JTF_OBJECT_ID = d.party_cust_id and STATUS_CODE = ''OPEN'') ';
255 end if;
256
257 l_del_query := l_del_query || ' group by d.party_cust_id';
258 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
259 elsif l_DefaultStrategyLevel = 20 THEN
260 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
261 --OPEN c_open_delinquencies
262 --FOR
263 l_del_query := 'select d.party_cust_id, d.cust_account_id, null, null, null, null,';
264 l_del_query := l_del_query || ' d.cust_account_id object_id, ''ACCOUNT'' object_type, null,';
265 l_del_query := l_del_query || ' 20 strategy_level, d.cust_account_id jtf_object_id, ''IEX_ACCOUNT'' jtf_object_type';
266 l_del_query := l_del_query || ' , null status';
267 l_del_query := l_del_query || ' from iex_delinquencies_all d';
268 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
269 --Bug#6870773 Naveen
270 if l_org_enabled = 'Y' then
271 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
272 end if;
273 if l_ignore_switch = 'Y' then
274 --Bug#520231 schekuri 05-MAY-2006
275 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
276 l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_ACCOUNT'' and JTF_OBJECT_ID = d.cust_account_id and STATUS_CODE = ''OPEN'') ';
277 end if;
278
279 l_del_query := l_del_query || ' group by d.party_cust_id, d.cust_account_id';
280 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
281 elsif l_DefaultStrategyLevel = 30 THEN
282 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
283 --OPEN c_open_delinquencies
284 --FOR
285 l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, null, null, null,';
286 l_del_query := l_del_query || ' d.customer_site_use_id object_id, ''BILL_TO'' object_type, null,';
287 l_del_query := l_del_query || ' 30 strategy_level, d.customer_site_use_id jtf_object_id, ''IEX_BILLTO'' jtf_object_type';
288 l_del_query := l_del_query || ' , null status';
289 l_del_query := l_del_query || ' from iex_delinquencies_all d';
290 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
291 --Bug#6870773 Naveen
292 if l_org_enabled = 'Y' then
293 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
294 end if;
295 if l_ignore_switch = 'Y' then
296 --Bug#520231 schekuri 05-MAY-2006
297 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
298 l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_BILLTO'' and JTF_OBJECT_ID = d.customer_site_use_id and STATUS_CODE = ''OPEN'') ';
299 end if;
300
301 l_del_query := l_del_query || ' group by d.party_cust_id, d.cust_account_id, d.customer_site_use_id';
302 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
303 ELSE
304 if (pre_delinquency_flag = 'Y') then
305 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
306 --OPEN c_open_delinquencies
307 --FOR
308 l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id,';
309 l_del_query := l_del_query || ' d.transaction_id, d.payment_schedule_id,';
310 l_del_query := l_del_query || ' d.delinquency_id object_id, ''DELINQUENT'' object_type ,';
311 l_del_query := l_del_query || ' d.score_value, 40 strategy_level, d.delinquency_id jtf_object_id,';
312 l_del_query := l_del_query || ' ''IEX_DELINQUENCY'' jtf_object_type';
313 l_del_query := l_del_query || ' , d.status status';
314 l_del_query := l_del_query || ' from iex_delinquencies_all d';
315 l_del_query := l_del_query || ' where (d.status = ''' || l_DelStatusDel || '''' || ' or d.status = ''' || l_DelStatusPreDel || '''' || ')';
316 --Bug#6870773 Naveen
317 if l_org_enabled = 'Y' then
318 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
319 end if;
320 if l_ignore_switch = 'Y' then
321 --Bug#520231 schekuri 05-MAY-2006
322 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
323 l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_DELINQUENCY'' and JTF_OBJECT_ID = d.delinquency_id and STATUS_CODE = ''OPEN'') ';
324 end if;
325 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
326 else
327 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
328 --OPEN c_open_delinquencies
329 --FOR
330 l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id,';
331 l_del_query := l_del_query || ' d.transaction_id, d.payment_schedule_id,';
332 l_del_query := l_del_query || ' d.delinquency_id object_id, ''DELINQUENT'' object_type ,';
333 l_del_query := l_del_query || ' d.score_value, 40 strategy_level, d.delinquency_id jtf_object_id,';
334 l_del_query := l_del_query || ' ''IEX_DELINQUENCY'' jtf_object_type';
335 l_del_query := l_del_query || ' , d.status status';
336 l_del_query := l_del_query || ' from iex_delinquencies_all d';
337 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
338 --Bug#6870773 Naveen
339 if l_org_enabled = 'Y' then
340 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
341 end if;
342 if l_ignore_switch = 'Y' then
343 --Bug#520231 schekuri 05-MAY-2006
344 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
345 l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_DELINQUENCY'' and JTF_OBJECT_ID = d.delinquency_id and STATUS_CODE = ''OPEN'') ';
346 end if;
347 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
348 end if;
349 END IF;
350 --Bug#6870773 Naveen
351
352 fnd_file.put_line(FND_FILE.LOG, l_del_query);
353 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
354 OPEN c_open_delinquencies
355 FOR l_del_query;
356 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
357
358 -- FOR f_delinquency_rec in C_Open_Delinquencies loop
359 LOOP
360
361 FETCH c_open_delinquencies INTO
362 l_stry_cnt_rec.party_cust_id,
363 l_stry_cnt_rec.cust_account_id,
364 l_stry_cnt_rec.customer_site_use_id,
365 l_stry_cnt_rec.delinquency_id,
366 l_stry_cnt_rec.transaction_id,
367 l_stry_cnt_rec.payment_schedule_id,
368 l_stry_cnt_rec.object_id,
369 l_stry_cnt_rec.object_type,
370 l_stry_cnt_rec.score_value,
371 l_stry_cnt_rec.strategy_level,
372 l_stry_cnt_rec.jtf_object_id,
373 l_stry_cnt_rec.jtf_object_type,
374 l_stry_cnt_rec.status;
375 /* ctlee - add status and pass it to GetTemplateId 7/3/2003 */
376
377 if c_open_delinquencies%FOUND then
378 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
379 write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency_id = '
380 || l_stry_cnt_rec.delinquency_id
381 || ' object Id = ' || l_stry_cnt_rec.object_id
382 || ' object_type = ' || l_stry_cnt_rec.object_type
383 || ' jtf_object Type ' || l_stry_cnt_rec.jtf_object_type
384 || ' jtf_object id ' || l_stry_cnt_rec.jtf_object_id
385 || ' Score Value = ' || l_stry_cnt_rec.score_value
386 || ' status = ' || l_stry_cnt_rec.status
387 || ' Strategy Level = ' || l_stry_cnt_rec.strategy_level );
388 END IF;
389
390 -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
391 --IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 then
392 IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 or l_DefaultStrategyLevel = 40 then
393 -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
394
395 begin
396 -- Begin - Andre Araujo -- bug#4551569 - 08/18/2005 - Scores for delinquencies still not being picked up
397 -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
398 -- if l_DefaultStrategyLevel = 40 then
399 -- l_id_save := l_stry_cnt_rec.jtf_object_id;
400 -- l_stry_cnt_rec.jtf_object_id := l_stry_cnt_rec.payment_schedule_id;
401 -- l_stry_cnt_rec.jtf_object_type := 'IEX_INVOICES';
402 -- end if;
403 -- -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
404 --
405 -- Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
406 -- fetch c_Score_Exists into l_stry_cnt_rec.score_value;
407 -- Close c_Score_Exists;
408 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
409 -- write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
410 -- || ' jtf_object Type ' || l_stry_cnt_rec.jtf_object_type
411 -- || ' jtf_object id ' || l_stry_cnt_rec.jtf_object_id
412 -- || ' Score Value = ' || l_stry_cnt_rec.score_value );
413 -- end if;
414 --
415 -- -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
416 -- if l_DefaultStrategyLevel = 40 then
417 -- l_stry_cnt_rec.jtf_object_id := l_id_save;
418 -- l_stry_cnt_rec.jtf_object_type := 'IEX_DELINQUENCY';
419 -- end if;
420 -- -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
421
422 if l_DefaultStrategyLevel <> 40 then -- This will pick the scores for all levels but not for delinquency
423 Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
424 fetch c_Score_Exists into l_stry_cnt_rec.score_value;
425 Close c_Score_Exists;
426
427 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
428 write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
429 || '; jtf_object Type= ' || l_stry_cnt_rec.jtf_object_type
430 || '; jtf_object id= ' || l_stry_cnt_rec.jtf_object_id
431 || '; Score Value = ' || l_stry_cnt_rec.score_value );
432 end if;
433 else
434 -- When looking for scores for delinquencies we should look for the newest score from either the payment schedule OR Delinquency
435 -- This is so because the first score of a delinquency is the score of the delinquent payment schedule
436 -- but if a customer scores the delinquency we should use the delinquency score to set the strategy
437 Open c_score_exists_del(l_stry_cnt_rec.payment_schedule_id, 'IEX_INVOICES', l_stry_cnt_rec.delinquency_id, 'IEX_DELINQUENCY');
438 fetch c_score_exists_del into l_stry_cnt_rec.score_value, l_score_object_id, l_score_object_code;
439 Close c_score_exists_del;
440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
441 write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists_del '
442 || '; jtf_object Type= ' || l_score_object_code
443 || '; jtf_object id= ' || l_score_object_id
444 || '; Score Value = ' || l_stry_cnt_rec.score_value );
445 end if;
446 end if;
447 -- End - Andre Araujo -- bug#4551569 - 08/18/2005 - Scores for delinquencies still not being picked up
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 fnd_file.put_line(FND_FILE.LOG, 'NO score available ' ||
452 ' object Type ' || l_stry_cnt_rec.jtf_object_type ||
453 ' object ID ' || l_stry_cnt_rec.jtf_object_id);
454 l_strategy_rec.score_value := 0;
455 l_stry_cnt_rec.score_value := 0;
456 retcode := '1';
457 END;
458 end if;
459
460
461 -- ctlee score tolerance checking
462 -- set to 0 if null
463 if (l_stry_cnt_rec.score_value is null) then
464 l_stry_cnt_rec.score_value := 0;
465 end if;
466
467 -- check grace period
468 -- c_gracePeriod c_gracePeriodCurTyp;
469 IF l_DefaultStrategyLevel = 10 THEN
470 OPEN c_gracePeriod FOR
471 select c.creation_date from iex_delinquencies_all c
472 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
473 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
474 order by c.creation_date desc;
475 elsif l_DefaultStrategyLevel = 20 THEN
476 OPEN c_gracePeriod FOR
477 select c.creation_date from iex_delinquencies_all c
478 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
479 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
480 and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
481 order by c.creation_date desc;
482 elsif l_DefaultStrategyLevel = 30 THEN
483 OPEN c_gracePeriod FOR
484 select c.creation_date from iex_delinquencies_all c
485 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
486 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
487 and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
488 and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
489 order by c.creation_date desc;
490 ELSE
491 OPEN c_gracePeriod FOR
492 select c.creation_date from iex_delinquencies_all c
493 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
494 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
495 and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
496 and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
497 and c.delinquency_id = l_stry_cnt_rec.delinquency_id
498 order by c.creation_date desc;
499 END IF;
500 loop
501 fetch c_gracePeriod into vGracePeriod;
502 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
503 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy grace Period ' || vGracePeriod );
504 end if;
505 if c_gracePeriod%notfound then
506 exit;
507 end if;
508 exit;
509 end loop;
510 Close c_gracePeriod;
511
512
513 -- check the status to see if the strategy has already run workflow
514 vStrategyStatus := NULL;
515
516 IF l_DefaultStrategyLevel = 10 THEN
517 OPEN c_strategy_exists FOR
518 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
519 from iex_strategies where party_id = l_stry_cnt_rec.PARTY_CUST_ID
520 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
521 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
522 and checklist_yn = vCheckList;
523 elsif l_DefaultStrategyLevel = 20 THEN
524 OPEN c_strategy_exists FOR
525 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
526 from iex_strategies where CUST_ACCOUNT_ID = l_stry_cnt_rec.CUST_ACCOUNT_ID
527 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
528 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
529 and checklist_yn = vCheckList;
530 elsif l_DefaultStrategyLevel = 30 THEN
531 OPEN c_strategy_exists FOR
532 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
533 from iex_strategies where customer_site_use_ID = l_stry_cnt_rec.customer_site_use_ID
534 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
535 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
536 and checklist_yn = vCheckList;
537 ELSE
538 OPEN c_strategy_exists FOR
539 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
540 from iex_strategies where delinquency_id = l_stry_cnt_rec.delinquency_id
541 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
542 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
543 and checklist_yn = vCheckList;
544 END IF;
545
546 /*
547 Check any strategy already running then skip
548 or if the open/pending strategy is out of score tolerance in its defined template then
549 cancel the old strategy and create a new one (change_strategy needs to be Y)
550 */
551 --Begin bug#5126770 schekuri 04-Apr-2006
552 --reset the variables in each iteration of the loop
553 vStrategyStatus := NULL;
554 vStrategyStatus1 := NULL;
555 vScoreValue := NULL;
556 vStrategyId := NULL;
557 vStrategyTemplateId := NULL;
558 --End bug#5126770 schekuri 04-Apr-2006
559
560 loop
561 fetch c_Strategy_Exists into vStrategyStatus, vScoreValue, vStrategyId, vStrategyTemplateId;
562 if c_Strategy_exists%notfound then
563 exit;
564 elsif vStrategyStatus in ( l_StratStatusOpen, l_StratStatusPending, l_StratStatusOnhold) then
565 exit;
566 end if;
567 end loop;
568 Close C_Strategy_Exists;
569
570 vOrginalStrategyStatus :=vStrategyStatus; --Added for bug#5202312 by schekuri on 05-May-2006
571
572 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status = ' || vStrategyStatus );
573 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Score Value = ' || vScoreValue );
574 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Id = ' || vStrategyId );
575 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Template Id = ' || vStrategyTemplateId );
576 write_log(FND_LOG.LEVEL_PROCEDURE, 'score_history/delinquency Score Value = ' || l_stry_cnt_rec.score_value );
577
578 if (vStrategyStatus = 'OPEN' or vStrategyStatus = 'PENDING') then
579 begin
580 select strategy_rank, decode(score_tolerance, null, 0, score_tolerance), change_strategy_yn
581 into vStrategyRank, vScoreTolerance, vChangeStrategy
582 from iex_strategy_templates_vl where strategy_temp_id = vStrategyTemplateId;
583 exception
584 when others then
585 vStrategyRank := '0';
586 vScoreTolerance := 0;
587 vChangeStrategy := 'N';
588 end;
589 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
590 write_log(FND_LOG.LEVEL_PROCEDURE, 'Get Strategy Template Details of = ' || vStrategyTemplateId || ' of Strategy ID = ' || vStrategyID );
591 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Rank = ' || vStrategyRank );
592 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
593 write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
594 END IF;
595
596 -- score in iex_strategies diff from score_histories/iex_delinquencies_all table
597 -- and score history out of the strategy template score tolerance
598 -- and strategy template change strategy flag is Y
599 if (
600 ( (vScoreValue <> l_stry_cnt_rec.score_value)
601 and
602 (
603 -- begin bug 4944801 ctlee 01/18/2006
604 -- (l_stry_cnt_rec.score_value > to_number(vStrategyRank) + vScoreTolerance)
605 -- or
606 -- (l_stry_cnt_rec.score_value < to_number(vStrategyRank) - vScoreTolerance)
607 (l_stry_cnt_rec.score_value > vScoreValue + vScoreTolerance)
608 or
609 (l_stry_cnt_rec.score_value < vScoreValue - vScoreTolerance)
610 -- end bug 4944801 ctlee 01/18/2006
611 )
612 )
613 and
614 (vChangeStrategy = 'Y')
615 ) then
616
617 -- cancel strategy
618 -- begin bug 4944801 ctlee 01/18/2006, cancel strategy only if the new strategy template id different from the old one
619 -- BEGIN
620 -- write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy id = ' || vStrategyId );
621 -- IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
622 -- strategy_id => vStrategyId,
623 -- status => 'CANCELLED' ) ;
624 -- EXCEPTION
625 -- WHEN OTHERS THEN
626 -- write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' );
627 -- UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED' WHERE STRATEGY_ID = vStrategyId;
628 -- END;
629 -- end bug 4944801 ctlee 01/18/2006, cancel strategy only if the new strategy template id different from the old one
630 vStrategyStatus := 'CANCELLED';
631 vStrategyStatus1 := 'CANCELLED'; --Added for bug#5126770 by schekuri on 04-Apr-2006
632 end if;
633 end if; -- if OPEN or PENDING
634
635
636 /* No Strategy exists or Existing running are closed, create a new strategy */
637 if (((vStrategyStatus IS NULL)) or (vStrategyStatus = l_StratStatusClosed)
638 or (vStrategyStatus = l_StratStatusCancelled)) then
639
640 /* Get the strategy template ID based on the score */
641 IEX_STRATEGY_CNT_PUB.GetStrategyTempID(
642 x_return_status=>l_return_status,
643 p_stry_cnt_rec => l_stry_cnt_rec,
644 x_strategy_template_id => l_strategy_template_id
645 );
646
647 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
648 write_log(FND_LOG.LEVEL_PROCEDURE, 'Delinquency ID ' || l_stry_cnt_rec.delinquency_id ||
649 ' Strategy Template ID selected ' || l_strategy_template_id );
650 end if;
651
652
653 -- begin bug 4944801 ctlee 01/18/2006
654 -- check if the template is the same
655 BEGIN
656 -- old template is vStrategyTemplateId
657 -- new template is l_strategy_template_id
658 --Begin bug#5202312 schekuri 05-May-2006
659 --Need to check the status of the strategy in the database(not the modified one)
660 /*if (l_strategy_template_id = vStrategyTemplateId and vStrategyStatus <> l_StratStatusClosed and
661 vStrategyStatus <> l_StratStatusCancelled) then*/
662 if (l_strategy_template_id = vStrategyTemplateId and vOrginalStrategyStatus <> l_StratStatusClosed and
663 vOrginalStrategyStatus <> l_StratStatusCancelled) then
664 --End bug#5202312 schekuri 05-May-2006
665
666 write_log(FND_LOG.LEVEL_PROCEDURE, 'same template and continue, strategy template id = ' || vStrategyTemplateId );
667 goto nextRec; -- continue to the loop for the next record, no need to change strategy
668
669 --Begin Bug#5126770 schekuri 04-Apr-2006
670 --Added IF condition to the following block of code to avoid junk Cancellation of Strategies
671 --Also enclosed the block of code between BEGIN and END and
672 --moved the exception handler from the outer block
673 elsif vStrategyId IS NOT NULL AND vStrategyStatus1 = 'CANCELLED' THEN
674 BEGIN
675 write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy id = ' || vStrategyId );
676 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
677 strategy_id => vStrategyId,
678 status => 'CANCELLED' ) ;
679 EXCEPTION
680 WHEN OTHERS THEN
681 -- Added for bug 5877743 by gnramasa on 28-02-2007
682 write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
683 UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED',
684 last_update_date=sysdate --Added for bug#7594370 by PNAVEENK
685 WHERE STRATEGY_ID = vStrategyId;
686 END;
687 --End Bug#5126770 schekuri 04-Apr-2006
688 end if;
689 EXCEPTION
690 WHEN OTHERS THEN
691 --Begin bug#5126770 schekuri 04-Apr-2006
692 --Moved the exception handler to the inner block
693 NULL;
694 /*write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' );
695 UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED' WHERE STRATEGY_ID = vStrategyId;*/
696 --End bug#5126770 schekuri 04-Apr-2006
697 END;
698 -- end bug 4944801 ctlee 01/18/2006
699
700
701 --Start bug 6794510 gnramasa 7th feb 2008
702 if (NVL(FND_PROFILE.VALUE('IEX_SKIP_DEFAULT_STRATEGY_ASSIGNMENT'), 'N') = 'Y') then
703 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
704 IEX_DEBUG_PUB.LogMessage( 'Skip Default Strategy Assignment Profile value is: Y' );
705 IEX_DEBUG_PUB.LogMessage( 'l_strategy_template_id: '|| l_strategy_template_id);
706 IEX_DEBUG_PUB.LogMessage( 'l_StrategyTempID: '|| l_StrategyTempID);
707 END IF;
708 IF l_strategy_template_id = l_StrategyTempID THEN
709 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
710 IEX_DEBUG_PUB.LogMessage( 'Strategy creation aborted. ' );
711 IEX_DEBUG_PUB.LogMessage( 'Skip Default Strategy Assignment by Profile ');
712 END IF;
713 goto nextRec; -- continue to the loop for the next record, no need to change strategy
714 END IF;
715 end if;
716 --End bug 6794510 gnramasa 7th feb 2008
717 /* ctlee - GetTemplateId could be -1 for predelinquent status (donot use default template)
718 7/3/2003
719 ctlee - Always use default template if not found; filtering at open_strategies()
720 no -1 is retrun 03/05/2004
721 */
722 /* --Begin bug#7565056 schekuri 19-N0v-2008
723 --if (l_strategy_template_id <> -1)
724 --and (trunc(sysdate) >= trunc(vGracePeriod) + l_gracePeriod) then
725 if vStrategyId is not null then
726 l_temp_grace_period:=0;
727 else
728 l_temp_grace_period:=l_gracePeriod;
729 end if;
730 if (l_strategy_template_id <> -1)
731 and (trunc(sysdate) >= trunc(vGracePeriod) + l_temp_grace_period) then
732 --End bug#7565056 schekuri 19-N0v-2008 */
733 -- Begin for bug#7594370 by PNAVEENK
734
735 if (vStrategyId is not null) and (l_strategy_template_id <> -1) then
736 if vOrginalStrategyStatus in ('OPEN','ONHOLD') then
737 l_temp_grace_period:=0;
738 else
739 select count(1)
740 into l_strat_count
741 from iex_strategies
742 where jtf_object_id = l_stry_cnt_rec.jtf_object_id
743 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
744 and checklist_yn = vCheckList
745 and last_update_date>=trunc(sysdate)-1
746 and status_code not in ('OPEN','ONHOLD');
747 if l_strat_count>0 then
748 l_temp_grace_period:=0;
749 else
750 l_temp_grace_period:=l_gracePeriod;
751 end if;
752 end if;
753 else
754 l_temp_grace_period:=l_gracePeriod;
755 end if;
756 if (l_strategy_template_id <> -1)
757 and (trunc(sysdate) >= trunc(vGracePeriod) + l_temp_grace_period) then
758 -- End for bug#7594370 by PNAVEENK
759 begin
760 l_strategy_rec.strategy_template_id := l_strategy_template_id;
761 l_strategy_rec.delinquency_id := l_stry_cnt_rec.delinquency_id;
762 l_strategy_rec.party_id := l_stry_cnt_rec.party_cust_id;
763 l_strategy_rec.cust_account_id := l_stry_cnt_rec.cust_account_id;
764 l_strategy_rec.customer_site_use_id := l_stry_cnt_rec.customer_site_use_id;
765 l_strategy_rec.next_work_item_id := null;
766 l_strategy_rec.object_id := l_stry_cnt_rec.object_id;
767 l_strategy_rec.object_type := l_stry_cnt_rec.object_type;
768 l_strategy_rec.status_code := l_StratStatusOpen;
769 l_strategy_rec.score_value := l_stry_cnt_rec.score_value;
770 l_strategy_rec.checklist_yn := 'N';
771 l_object_version_number := 1;
772 l_strategy_rec.strategy_level := l_stry_cnt_rec.strategy_level;
773 l_strategy_rec.jtf_object_type := l_stry_cnt_rec.jtf_object_type;
774 l_strategy_rec.jtf_object_id := l_stry_cnt_rec.jtf_object_id;
775
776 --Bug#6870773 Naveen
777 if l_org_enabled = 'Y' then
778 l_strategy_rec.org_id := l_org_id ;
779 else
780 l_strategy_rec.org_id := null;
781 end if;
782 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
783 write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Create strategy for Delinquency ID '
784 || l_strategy_rec.delinquency_id);
785 end if;
786 Begin
787 -- bug 4141678 begin - ctlee
788 -- p_commit set to false
789 -- bug 4141678 end - ctlee
790 fnd_file.put_line(FND_FILE.LOG,'Value of l_strategy_rec.org_id : '|| l_strategy_rec.org_id);
791 iex_strategy_pvt.create_strategy(
792 P_Api_Version_Number=>2.0,
793 p_commit => FND_API.G_FALSE,
794 P_Init_Msg_List =>FND_API.G_TRUE,
795 p_strategy_rec => l_strategy_rec,
796 x_return_status=>l_return_status,
797 x_msg_count=>l_msg_count,
798 x_msg_data=>l_msg_data,
799 x_strategy_id => l_strategy_id
800 );
801
802 l_strategy_rec.strategy_id := l_strategy_id;
803
804 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
805 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
806 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy created. id = ' || l_strategy_id);
807 end if;
808 -- bug 4141678 begin - ctlee
809 -- EXCEPTION
810 -- WHEN OTHERS THEN
811 -- write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy create Return status = ' ||
812 -- l_return_status || ' ' || sqlerrm );
813 -- retcode := '2';
814 -- return;
815 -- END;
816 -- bug 4141678 end - ctlee
817
818
819 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
820 write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Workflow creation for Delinquency ID '
821 || l_strategy_rec.strategy_id);
822 end if;
823
824 -- BEGIN
825 -- bug 4141678 begin - ctlee
826 -- p_commit set to false
827 -- bug 4141678 end - ctlee
828
829 iex_strategy_wf_pub.start_workflow(
830 P_Api_Version =>2.0,
831 P_Init_Msg_List => FND_API.G_TRUE,
832 p_commit => FND_API.G_FALSE,
833 p_strategy_rec => l_strategy_rec,
834 x_return_status=>l_return_status,
835 x_msg_count=>l_msg_count,
836 x_msg_data=>l_msg_data
837 );
838
839 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
840 write_log(FND_LOG.LEVEL_STATEMENT, 'Workflow Launch Return status = '
841 || l_return_status) ;
842 end if;
843 EXCEPTION
844 WHEN OTHERS THEN
845 fnd_file.put_line(FND_FILE.LOG, ' Exception: Create Strategy/Workflow Launch Return status = '
846 || l_return_status || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
847 retcode := '2';
848 -- bug 4141678 begin - ctlee
849 fnd_file.put_line(FND_FILE.LOG, 'commit count = ' || l_commit_count);
850 fnd_file.put_line(FND_FILE.LOG, 'save count = ' || l_save_count);
851 rollback;
852 l_save_count := 0;
853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
854 exit;
855 -- bug 4141678 end - ctlee
856 END;
857 -- bug 4141678 begin - ctlee
858 l_save_count := l_save_count + 1;
859 -- l_strategy_tbl(l_save_count) := l_strategy_id;
860 if (l_save_count = l_batch_size) then
861 l_save_count := 0;
862 l_commit_count := l_commit_count + 1;
863 commit work;
864 end if;
865 -- bug 4141678 end - ctlee
866 end;
867 end if; /* if template id is -1 then donot generate streategy */
868 end if; /* check status */
869 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
870 write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor ends' );
871 end if;
872 ELSE -- fetch failed, so exit loop
873 EXIT;
874 end if; /* found cursor */
875 <<nextRec>>
876 null;
877 END loop;
878
879 -- bug 4141678 begin - ctlee
880 if (l_save_count > 0) then
881 l_commit_count := l_commit_count + 1;
882 commit work;
883 end if;
884 write_log(FND_LOG.LEVEL_UNEXPECTED, 'commit count = ' || l_commit_count);
885 -- bug 4141678 end - ctlee
886
887 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Delinquency cursor EXIT ');
888 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation completed ' );
889
890 close c_open_delinquencies;
891
892 EXCEPTION
893 WHEN OTHERS THEN
894 fnd_file.put_line(FND_FILE.LOG, 'Delinquency Concurrent raised exception sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
895 close c_open_delinquencies;
896 -- bug 4141678 begin - ctlee
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 -- bug 4141678 end - ctlee
899 END open_strategies;
900
901 /* not used anywhere */
902 PROCEDURE CLOSE_ALL_STRY
903 (
904 ERRBUF OUT NOCOPY VARCHAR2,
905 RETCODE OUT NOCOPY VARCHAR2
906 ) IS
907 l_result VARCHAR2(10);
908
909 l_error_msg VARCHAR2(2000);
910 l_return_status VARCHAR2(20);
911 l_msg_count NUMBER;
912 l_msg_data VARCHAR2(2000);
913 l_api_name VARCHAR2(100) ;
914 l_api_version_number CONSTANT NUMBER := 2.0;
915
916 vStrategyStatus VARCHAR2(30);
917 l_strategy_processid NUMBER;
918
919 l_delinquency_id number;
920 l_party_cust_id number;
921 l_cust_account_id number;
922 l_object_id number;
923 l_object_code varchar2(40);
924 l_strategy_id number;
925 l_strategy_template_id number;
926 l_object_version_number number := 1.0;
927 l_strategy_process_id number;
928
929 Cursor c_open_strategies is
930 select s.strategy_id, s.delinquency_id,
931 s.object_id, s.object_type, s.strategy_template_id, s.jtf_object_type, s.jtf_object_id
932 from iex_strategies s where s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold) AND
933 checklist_yn = 'N';
934
935 l_stry_cnt_rec IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE ;
936
937 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
938
939 l_itemtype varchar2(30);
940 l_itemkey varchar2(50);
941
942 BEGIN
943
944 -- initialize variables
945 l_api_name := 'START_WORKFLOW';
946 l_stry_cnt_rec := IEX_STRATEGY_TYPE_PUB.INST_STRY_CNT_REC;
947
948 -- Initialize API return status to SUCCESS
949 l_return_status := FND_API.G_RET_STS_SUCCESS;
950 -- Standard Start of API savepoint
951 SAVEPOINT CLOSE_STRY_CONT;
952
953 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor started ');
954
955 FOR f_strategy_rec in C_Open_strategies loop
956
957 /* Create the strategy record */
958 l_stry_cnt_rec.strategy_id := f_strategy_rec.strategy_id;
959 l_stry_cnt_rec.delinquency_id := f_strategy_rec.delinquency_id;
960 l_stry_cnt_rec.object_id := f_strategy_rec.object_id;
961 l_stry_cnt_rec.object_type := f_strategy_rec.object_type;
962
963 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_stry_cnt_rec.strategy_id
964 || ' Delinquency ID ' || l_stry_cnt_rec.delinquency_id
965 || ' Object ID ' || l_stry_cnt_rec.object_id
966 || ' Object Type ' || l_stry_cnt_rec.object_type
967 || ' Strategy Template ID ' || l_strategy_template_id );
968
969 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status ' || vStrategyStatus );
970
971 l_itemtype := 'IEXSTRY';
972 l_itemkey := to_char(l_stry_cnt_rec.strategy_id);
973
974 BEGIN
975 IEX_STRATEGY_WF.Send_Signal(
976 process => l_itemtype,
977 strategy_id => l_itemkey,
978 status => l_StratStatusClosed
979 );
980
981 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Closed. id = ' || l_stry_cnt_rec.strategy_id);
982
983 EXCEPTION
984 WHEN OTHERS THEN
985 fnd_file.put_line(FND_FILE.LOG, 'Strategy Closed Raised Exception = ' ||
986 l_stry_cnt_rec.strategy_id || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
987 END;
988
989 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
990
991 END loop;
992 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor EXIT ');
993
994 EXCEPTION
995 WHEN OTHERS THEN
996 fnd_file.put_line(FND_FILE.LOG, 'Close Strategy raised exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
997
998 END CLOSE_ALL_STRY;
999
1000 /* Procedure for closing strategy when associated customer delinquencies are closed */
1001 PROCEDURE Close_strategies
1002 (
1003 ERRBUF OUT NOCOPY VARCHAR2,
1004 RETCODE OUT NOCOPY VARCHAR2
1005 ) IS
1006 l_result VARCHAR2(10);
1007
1008 l_error_msg VARCHAR2(2000);
1009 l_return_status VARCHAR2(20);
1010 l_msg_count NUMBER;
1011 l_msg_data VARCHAR2(2000);
1012 l_api_name VARCHAR2(100) ;
1013 l_api_version_number CONSTANT NUMBER := 2.0;
1014
1015 vStrategyStatus VARCHAR2(30);
1016 l_strategy_processid NUMBER;
1017
1018 l_delinquency_id number;
1019 l_party_cust_id number;
1020 l_cust_account_id number;
1021 l_object_id number;
1022 l_object_code varchar2(40);
1023 l_strategy_id number;
1024 l_strategy_template_id number;
1025 l_object_version_number number := 1.0;
1026 l_strategy_process_id number;
1027
1028 l_itemtype varchar2(30);
1029 l_itemkey varchar2(50);
1030
1031 TYPE c_open_strategiesCurTyp IS REF CURSOR; -- weak
1032 c_open_strategies c_open_strategiesCurTyp; -- declare cursor variable
1033
1034 BEGIN
1035 -- initialize variables
1036 l_api_name := 'START_WORKFLOW';
1037
1038 -- Initialize API return status to SUCCESS
1039 l_return_status := FND_API.G_RET_STS_SUCCESS;
1040
1041 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
1042 write_log(FND_LOG.LEVEL_STATEMENT,' Profile Name IEX: Strategy Disabled (IEX_STRATEGY_DISABLED) set to YES ');
1043 return;
1044 end if;
1045 -- Standard Start of API savepoint
1046 SAVEPOINT CLOSE_STRY_CONT;
1047
1048
1049 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN -- commented by gnramasa on 29/08/2006 for bug # 5487449
1050 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor started ');
1051 --end if;
1052
1053 IF l_DefaultStrategyLevel = 10 THEN
1054 OPEN c_open_strategies
1055 FOR
1056 select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d
1057 where s.strategy_level = l_DefaultStrategyLevel and
1058 s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending) and
1059 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1060 (d.status = l_DelStatusCurrent or d.status = l_DelStatusClose) and d.party_cust_id = s.party_id
1061 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1062 and not exists (select null from iex_delinquencies_all dd where dd.status
1063 = l_DelStatusDel and dd.party_cust_id = s.party_id)
1064 --and dd.org_id = decode(l_org_enabled,'Y',l_org_id,dd.org_id))
1065
1066 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99)) --Bug# 6870773 Naveen
1067
1068 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE;
1069
1070 elsif l_DefaultStrategyLevel = 20 THEN
1071 OPEN c_open_strategies
1072 FOR
1073 select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d
1074 where s.strategy_level = l_DefaultStrategyLevel and
1075 s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending) and
1076 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1077 (d.status = l_DelStatusCurrent or d.status = l_DelStatusClose) and d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id
1078 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1079 and not exists (select null from iex_delinquencies_all dd where dd.status
1080 = l_DelStatusDel and dd.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id)
1081 --and dd.org_id = decode(l_org_enabled,'Y',l_org_id,dd.org_id))
1082 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99)) --Bug# 6870773 Naveen
1083 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE;
1084
1085 elsif l_DefaultStrategyLevel = 30 THEN
1086 OPEN c_open_strategies
1087 FOR
1088 select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d
1089 where s.strategy_level = l_DefaultStrategyLevel and
1090 s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending) and
1091 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1092 (d.status = l_DelStatusCurrent or d.status = l_DelStatusClose) and d.customer_site_use_id = s.customer_site_use_id
1093 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1094 and not exists (select null from iex_delinquencies_all dd where dd.status
1095 = l_DelStatusDel and dd.customer_site_use_id = s.customer_site_use_id)
1096 -- and dd.org_id = decode(l_org_enabled,'Y',l_org_id,dd.org_id))
1097 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99)) --Bug# 6870773 Naveen
1098 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE;
1099
1100 else
1101 /* begin bug 4253030 by ctlee 03/29/2005 */
1102 /*
1103 OPEN c_open_strategies
1104 FOR
1105 select s.strategy_id, s.strategy_template_id, s.status_code
1106 from iex_strategies s, iex_delinquencies_all d where d.status = l_DelStatusCurrent and
1107 s.strategy_level = l_DefaultStrategyLevel and
1108 s.object_id = d.delinquency_id and
1109 s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending);
1110 */
1111 /* end bug 4253030 by ctlee 03/29/2005 */
1112 OPEN c_open_strategies
1113 FOR
1114 select s.strategy_id, s.strategy_template_id, s.status_code
1115 from iex_strategies s, iex_delinquencies_all d
1116 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1117 where (d.status = l_DelStatusCurrent or d.status = l_DelStatusClose) and
1118 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1119 s.strategy_level = l_DefaultStrategyLevel and
1120 s.jtf_object_id = d.delinquency_id and
1121 s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending)
1122 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99)); --Bug# 6870773 Naveen
1123 END IF;
1124
1125 -- FOR f_strategy_rec in C_Open_strategies loop
1126 LOOP
1127 FETCH c_open_strategies INTO l_strategy_id, l_Strategy_template_id, vStrategyStatus ;
1128 if c_open_strategies%FOUND then
1129
1130
1131 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1132 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_strategy_id
1133 || ' Strategy Status ' || vStrategyStatus
1134 || ' Strategy Template ID ' || l_strategy_template_id );
1135
1136 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status ' || vStrategyStatus );
1137 end if;
1138
1139 l_itemtype := 'IEXSTRY';
1140 l_itemkey := to_char(l_strategy_id);
1141
1142 BEGIN
1143 IEX_STRATEGY_WF.Send_Signal(
1144 process => l_itemtype,
1145 strategy_id => l_itemkey,
1146 status => l_StratStatusClosed
1147 );
1148
1149 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1150 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Closed. id = ' || l_strategy_id);
1151 end if;
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 fnd_file.put_line(FND_FILE.LOG, 'Strategy Closed Rised Exception = '
1156 || l_strategy_id || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1157 retcode := '2';
1158 END;
1159
1160 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1161 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
1162 end if;
1163 ELSE -- fetch failed, so exit loop
1164 EXIT;
1165 end if;
1166 END loop;
1167 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor EXIT ');
1168 close c_open_strategies;
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 fnd_file.put_line(FND_FILE.LOG, 'Close Strategy raised exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1172 close c_open_strategies;
1173
1174 END close_strategies;
1175
1176
1177 --Begin Bug#7248296 28-07-2008 barathsr
1178 PROCEDURE PROCESS_ONHOLD_STRATEGIES
1179 IS
1180 cursor c_party_onhold_st is
1181 select s.strategy_id strategy_id,
1182 s.strategy_template_id strategy_template_id,
1183 S.STATUS_CODE STATUS_CODE,
1184 d.party_cust_id party_id
1185 from iex_strategies s, iex_delinquencies_all d
1186 where s.strategy_level = 10 and
1187 s.status_code = 'ONHOLD' and
1188 d.status in ('DELINQUENT','PREDELINQUENT') and
1189 d.party_cust_id = s.party_id and
1190 not exists (select 1 from iex_promise_details p
1191 where p.status='COLLECTABLE'
1192 AND d.delinquency_id=p.delinquency_id)
1193 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
1194 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.party_cust_id;
1195
1196 cursor c_account_onhold_st is
1197 select s.strategy_id strategy_id,
1198 s.strategy_template_id strategy_template_id,
1199 S.STATUS_CODE STATUS_CODE,
1200 d.cust_account_id cust_account_id
1201 from iex_strategies s, iex_delinquencies_all d
1202 where s.strategy_level = 20 and
1203 s.status_code = 'ONHOLD' and
1204 d.status in ('DELINQUENT','PREDELINQUENT') and
1205 d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id and
1206 not exists (select 1 from iex_promise_details p
1207 where p.status='COLLECTABLE'
1208 AND d.delinquency_id=p.delinquency_id)
1209 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
1210 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.cust_account_id;
1211
1212 cursor c_billto_onhold_st is
1213 select s.strategy_id strategy_id,
1214 s.strategy_template_id strategy_template_id,
1215 S.STATUS_CODE STATUS_CODE,
1216 d.customer_site_use_id billto_id
1217 from iex_strategies s, iex_delinquencies_all d
1218 where s.strategy_level = 30 and
1219 s.status_code = 'ONHOLD' and
1220 d.status in ('DELINQUENT','PREDELINQUENT') and
1221 d.customer_site_use_id = s.customer_site_use_id and
1222 not exists (select 1 from iex_promise_details p
1223 where p.status='COLLECTABLE'
1224 AND d.delinquency_id=p.delinquency_id)
1225 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
1226 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.customer_site_use_id;
1227
1228 l_DefaultStrategyLevel number;
1229 l_StrategyLevelName varchar2(30);
1230
1231 l_return_status varchar2(10);
1232 l_msg_count number;
1233 l_msg_data varchar2(200);
1234
1235
1236 begin
1237
1238 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
1239 into l_DefaultStrategyLevel,l_StrategyLevelName
1240 from iex_app_preferences_vl
1241 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y';
1242
1243 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1244 write_log(FND_LOG.LEVEL_STATEMENT, ' Strategy level is: ' || l_DefaultStrategyLevel);
1245 END IF;
1246
1247 if l_DefaultStrategyLevel = 10 then
1248 for r_party in c_party_onhold_st loop
1249 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1250 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for party id : = ' || r_party.party_id);
1251 END IF;
1252 iex_strategy_pub.set_strategy(
1253 P_Api_Version_Number => 2.0,
1254 P_Init_Msg_List => 'F',
1255 P_Commit => 'F',
1256 p_validation_level => null,
1257 X_Return_Status => l_return_status,
1258 X_Msg_Count => l_msg_count,
1259 X_Msg_Data => l_msg_data,
1260 p_DelinquencyID => null,
1261 p_ObjectType => 'PARTY',
1262 p_ObjectID => r_party.party_id,
1263 p_Status => 'OPEN');
1264 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1265 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1266 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || r_party.strategy_id);
1267 END IF;
1268 end loop;
1269 elsif l_DefaultStrategyLevel = 20 then
1270 for r_account in c_account_onhold_st loop
1271 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1272 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for account id: = ' || r_account.cust_account_id);
1273 END IF;
1274 iex_strategy_pub.set_strategy(
1275 P_Api_Version_Number => 2.0,
1276 P_Init_Msg_List => 'F',
1277 P_Commit => 'F',
1278 p_validation_level => null,
1279 X_Return_Status => l_return_status,
1280 X_Msg_Count => l_msg_count,
1281 X_Msg_Data => l_msg_data,
1282 p_DelinquencyID => null,
1283 p_ObjectType => 'ACCOUNT',
1284 p_ObjectID => r_account.cust_account_id,
1285 p_Status => 'OPEN');
1286 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1287 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1288 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || r_account.strategy_id);
1289 END IF;
1290 end loop;
1291 elsif l_DefaultStrategyLevel = 30 then
1292 for r_billto in c_billto_onhold_st loop
1293 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1294 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for customer site use id : = ' || r_billto.billto_id);
1295 END IF;
1296 iex_strategy_pub.set_strategy(
1297 P_Api_Version_Number => 2.0,
1298 P_Init_Msg_List => 'F',
1299 P_Commit => 'F',
1300 p_validation_level => null,
1301 X_Return_Status => l_return_status,
1302 X_Msg_Count => l_msg_count,
1303 X_Msg_Data => l_msg_data,
1304 p_DelinquencyID => null,
1305 p_ObjectType => 'BILL_TO',
1306 p_ObjectID => r_billto.billto_id,
1307 p_Status => 'OPEN');
1308 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1309 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1310 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || r_billto.strategy_id);
1311 END IF;
1312 end loop;
1313 else
1314 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1315 write_log(FND_LOG.LEVEL_STATEMENT, ' Strategy level is: ' || l_DefaultStrategyLevel || ', no need to update the strategy');
1316 END IF;
1317 end if;
1318 EXCEPTION
1319 when others then
1320 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1321 write_log(FND_LOG.LEVEL_STATEMENT, ' In PROCESS_ONHOLD_STRATEGIES when others exception');
1322 END IF;
1323 END PROCESS_ONHOLD_STRATEGIES;
1324 --End Bug#7248296 28-07-2008 barathsr
1325
1326 PROCEDURE GetStrategyTempID(
1327 p_stry_cnt_rec in IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE,
1328 x_return_status out NOCOPY varchar2,
1329 x_strategy_template_id out NOCOPY number) IS
1330 /*
1331 CURSOR c_strategyTemp(pCategoryType varchar2, pDelinquencyID number) IS
1332 SELECT ST.strategy_temp_id, ST.strategy_rank, OBF.ENTITY_NAME
1333 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
1334 where ST.category_type = pCategoryType and ST.Check_List_YN = 'N' AND
1335 OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
1336 OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
1337 and not exists
1338 (select 'x' from iex_strategies SS where SS.delinquency_id = pDelinquencyID
1339 and SS.OBJECT_TYPE = pCategoryType)
1340 ORDER BY strategy_rank DESC;
1341 */
1342 C_DynSql varchar2(1000);
1343 v_Exists varchar2(20);
1344 v_SkipTemp varchar2(20);
1345
1346 l_StrategyTempID number := 0;
1347 TYPE c_strategyTempCurTyp IS REF CURSOR; -- weak
1348 c_strategyTemp c_strategyTempCurTyp; -- declare cursor variable
1349 c_rec_Strategy_temp_id NUMBER;
1350 c_Rec_Strategy_Rank varchar2(10);
1351 c_Rec_ENTITY_NAME varchar2(30);
1352 c_Rec_active_flag varchar2(1);
1353
1354 -- clchang updated for sql bind var 05/07/2003
1355 vstr1 varchar2(100) ;
1356 vstr2 varchar2(100) ;
1357 vstr3 varchar2(100) ;
1358 vstr4 varchar2(100) ;
1359 vstr5 varchar2(100) ;
1360 vstr6 varchar2(100) ;
1361
1362 /* ctlee - add status and pass it to GetTemplateId by stry_rec 7/3/2003 */
1363 chk_obj_type varchar2(30);
1364 BEGIN
1365
1366 -- initialize variables
1367 vstr1 := ' select 1 from ' ;
1368 vstr2 := ' where delinquency_id = :DelId ' ;
1369 vstr3 := ' and rownum < 2 ';
1370 vstr4 := ' where CUST_ACCOUNT_id = :AcctId ';
1371 vstr5 := ' where party_id = :PartyId ';
1372 vstr6 := ' where customer_site_use_id = :CustomerSiteUseId ';
1373
1374 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1375 write_log(FND_LOG.LEVEL_STATEMENT, 'GetStrategyTempID: Object_Type = '
1376 || p_stry_cnt_rec.object_type || ' Delinquency ID = ' || p_stry_cnt_rec.delinquency_id
1377 || ' Strategy Level ' || l_DefaultStrategyLevel || ' Score ' || p_stry_cnt_rec.score_value
1378 || ' Party ID ' || p_stry_cnt_rec.party_cust_id || ' Account ID ' || p_stry_cnt_rec.cust_account_id
1379 || ' CUSTOMER_SITE_USE ID ' || p_stry_cnt_rec.customer_site_use_id );
1380 end if;
1381
1382 x_Strategy_Template_id := l_DefaultTempID;
1383
1384
1385 /* ctlee - add status and pass it to GetTemplateId by stry_rec 7/3/2003 */
1386 /* C_StrategyTemp using chk_obj_type when strategy level = 30 */
1387 /* comment out to check if existing pre-delinquent strategy template for all 4 levels
1388 - filter when open_strategies() => always use the default one if not found
1389 03/05/2004 ctlee
1390 */
1391 chk_obj_type := p_stry_cnt_rec.object_type;
1392 if (p_stry_cnt_rec.object_type = 'DELINQUENT') then
1393 if (p_stry_cnt_rec.status = 'PREDELINQUENT') then
1394 chk_obj_type := p_stry_cnt_rec.status;
1395 end if;
1396 end if;
1397 /*
1398 if (p_stry_cnt_rec.object_type = 'DELINQUENT') then
1399 chk_obj_type := p_stry_cnt_rec.status;
1400 if (chk_obj_type = 'PREDELINQUENT') then
1401 x_strategy_template_id := -1;
1402 end if;
1403 end if;
1404 */
1405
1406 -- bug 4141678 begin - ctlee
1407 -- add checking on existing iex_strategy_work_temp_xref, at least one wi required
1408 -- bug 4141678 end - ctlee
1409
1410 IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 THEN
1411 OPEN c_strategyTemp
1412 FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
1413 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
1414 where ST.Check_List_YN = l_No AND
1415 ((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> l_No) and
1416 st.strategy_level = l_DefaultStrategyLevel and
1417 OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
1418 OBF.OBJECT_FILTER_TYPE(+) = l_StratObjectFilterType
1419 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
1420 AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
1421 and exists (select 1 from IEX_STRATEGY_WORK_TEMP_XREF strx
1422 where strx.strategy_temp_id = st.strategy_temp_id)
1423 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
1424 and ST.STRATEGY_RANK <= p_stry_cnt_rec.SCORE_VALUE
1425 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
1426 -- Bug 7392752 by Ehuh
1427 and exists (select 1 from iex_strategy_template_groups tg
1428 where tg.group_id = st.strategy_temp_group_id
1429 and tg.enabled_flag <> 'N'
1430 and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
1431 and trunc(nvl(tg.valid_to_dt,sysdate)) )
1432 and category_type in ('DELINQUENT','PREDELINQUENT') -- added for bug#7709114 by PNAVEENK on 22-1-2009
1433 ORDER BY to_number(strategy_rank) DESC;
1434 ELSE
1435 OPEN c_strategyTemp
1436 FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
1437 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
1438 where ST.category_type = chk_obj_type and ST.Check_List_YN = l_No AND
1439 ((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> l_No) and
1440 st.strategy_level = l_DefaultStrategyLevel and
1441 OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
1442 OBF.OBJECT_FILTER_TYPE(+) = l_StratObjectFilterType
1443 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
1444 AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
1445 and exists (select 1 from IEX_STRATEGY_WORK_TEMP_XREF strx
1446 where strx.strategy_temp_id = st.strategy_temp_id)
1447 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
1448 and ST.STRATEGY_RANK <= p_stry_cnt_rec.SCORE_VALUE
1449 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
1450 -- Bug 7392752 by Ehuh
1451 and exists (select 1 from iex_strategy_template_groups tg
1452 where tg.group_id = st.strategy_temp_group_id
1453 and tg.enabled_flag <> 'N'
1454 and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
1455 and trunc(nvl(tg.valid_to_dt,sysdate)) )
1456 ORDER BY to_number(strategy_rank) DESC;
1457 END IF;
1458
1459
1460 /* Get the Strategy Template for requested Category Type */
1461 LOOP
1462 FETCH C_StrategyTemp INTO c_rec_Strategy_temp_id, c_Rec_Strategy_Rank, c_Rec_ENTITY_NAME, c_rec_active_flag ;
1463
1464 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1465 write_log(FND_LOG.LEVEL_STATEMENT,
1466 ' Get Strategy Template: Inside Cursor. Entity Name '
1467 || c_Rec_Entity_Name
1468 || ' Temp ' || c_rec_Strategy_temp_id
1469 || ' c_rec_active_flag ' || c_rec_active_flag
1470 || ' Rank ' || c_Rec_Strategy_Rank);
1471 end if;
1472
1473 if C_StrategyTemp%FOUND then
1474 v_SkipTemp := 'F';
1475 if c_Rec_Entity_Name is not null and c_rec_active_flag <> 'N' then
1476 BEGIN
1477 IF l_DefaultStrategyLevel = 40 THEN
1478 -- clchang updated for sql bind var 05/07/2003
1479 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1480 vstr2 ||
1481 vstr3;
1482 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.delinquency_id;
1483 /*
1484 C_DynSql :=
1485 ' select 1 from ' || c_Rec_ENTITY_NAME ||
1486 ' where delinquency_id = ' || p_stry_cnt_rec.delinquency_id ||
1487 ' and rownum < 2 ';
1488 */
1489
1490 elsif l_DefaultStrategyLevel = 30 THEN
1491 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1492 vstr6 ||
1493 vstr3;
1494 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.customer_site_use_id;
1495 elsif l_DefaultStrategyLevel = 20 THEN
1496 -- clchang updated for sql bind var 05/07/2003
1497 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1498 vstr4 ||
1499 vstr3;
1500 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.cust_account_id;
1501 /*
1502 C_DynSql :=
1503 ' select 1 from ' || c_Rec_ENTITY_NAME ||
1504 ' where CUST_ACCOUNT_id = ' || p_stry_cnt_rec.CUST_ACCOUNT_id ||
1505 ' and rownum < 2 ';
1506 */
1507 else
1508 -- clchang updated for sql bind var 05/07/2003
1509 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1510 vstr5 ||
1511 vstr3;
1512 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.party_cust_id;
1513 /*
1514 C_DynSql :=
1515 ' select 1 from ' || c_Rec_ENTITY_NAME ||
1516 ' where party_id = ' || p_stry_cnt_rec.PARTY_CUST_ID ||
1517 ' and rownum < 2 ';
1518 */
1519 end if;
1520
1521 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1522 write_log(FND_LOG.LEVEL_STATEMENT, ' Dynamic SQL in GetStrategyTemplate '
1523 || c_DynSql );
1524 end if;
1525
1526 --Execute Immediate c_DynSql into v_Exists;
1527
1528 EXCEPTION
1529 When no_data_found then
1530 fnd_file.put_line(FND_FILE.LOG, ' Get Strategy Template: When No Data Found: ' || c_DynSql );
1531 v_SkipTemp := 'T';
1532 When Others then
1533 fnd_file.put_line(FND_FILE.LOG, ' Get Strategy Template: When Others: ' || c_DynSql );
1534 v_SkipTemp := 'T';
1535 END;
1536 end if;
1537
1538 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1539 write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: ' || c_Rec_Strategy_Temp_id ||
1540 ' Skip Flag ' || v_SkipTemp );
1541 end if;
1542
1543 if v_SkipTemp <> 'T' then
1544
1545 if p_stry_cnt_rec.score_value >= C_Rec_Strategy_Rank then
1546 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1547 write_log(FND_LOG.LEVEL_STATEMENT, ' Found Template: ' || c_Rec_Strategy_Temp_id );
1548 end if;
1549 x_strategy_template_id := c_rec_Strategy_temp_id;
1550 return;
1551 end if;
1552 end if;
1553 ELSE -- fetch failed, so exit loop
1554 EXIT;
1555 end if;
1556 end loop;
1557 close C_StrategyTemp;
1558 EXCEPTION
1559 when others then
1560 close C_StrategyTemp;
1561 END;
1562
1563
1564 -- apply to one default strategy only because the SA would set to one level only
1565 -- thus it will apply to the same level of the default strategy template too
1566 FUNCTION GetDefaultStrategyTempID return NUMBER IS
1567 l_StrategyTempID number;
1568 lCursorStrategyTempID number;
1569 Cursor C_getFirstTempID IS
1570 Select st.Strategy_Temp_ID FROM IEX_STRATEGY_TEMPLATES_B st where
1571 st.Check_List_YN = l_No AND st.ENABLED_FLAG <> l_No
1572 -- Bug 7392752 by Ehuh
1573 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE)) AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
1574 and exists (select 1 from iex_strategy_template_groups tg
1575 where tg.group_id = st.strategy_temp_group_id
1576 and tg.enabled_flag <> 'N'
1577 and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
1578 and trunc(nvl(tg.valid_to_dt,sysdate)) );
1579 BEGIN
1580 l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
1581 if (l_StrategyTempID = 0) Then
1582 Open C_getFirstTempID;
1583 fetch C_getFirstTempID into lCursorStrategyTempID;
1584 if C_getFirstTempID%FOUND then
1585 l_StrategyTempID := lCursorStrategyTempID;
1586 end if;
1587 Close C_getFirstTempID;
1588 end if;
1589 return l_StrategyTempID;
1590 END;
1591
1592
1593 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
1594 --PROCEDURE MAIN (
1595 -- ERRBUF OUT NOCOPY VARCHAR2,
1596 -- RETCODE OUT NOCOPY VARCHAR2,
1597 -- p_trace_mode IN VARCHAR2)
1598 PROCEDURE MAIN
1599 (
1600 ERRBUF OUT NOCOPY VARCHAR2,
1601 RETCODE OUT NOCOPY VARCHAR2,
1602 -- p_trace_mode IN VARCHAR2, Bug5022607. Fix By LKKUMAR. Removed this parameter.
1603 p_org_id IN number,
1604 p_ignore_switch IN VARCHAR2
1605 )
1606 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
1607 --Bug# 6870773 Naveen
1608 IS
1609
1610 l_count number;
1611 CURSOR c_org IS
1612 SELECT organization_id from hr_operating_units where
1613 mo_global.check_access(organization_id) = 'Y'
1614 AND organization_id = nvl(P_ORG_ID,organization_id);
1615
1616 BEGIN
1617 --Bug5022607. Fix By LKKUMAR. Remove p_trace_mode parameter. Start.
1618 -- IF p_trace_mode = 'Y' THEN
1619 fnd_file.put_line(FND_FILE.LOG,'Value of profile IEX: Debug Level is : '|| PG_DEBUG);
1620 IF PG_DEBUG = 1 THEN
1621 fnd_file.put_line(FND_FILE.LOG, ' Enabling the trace');
1622 dbms_session.set_sql_trace(TRUE);
1623 ELSE
1624 fnd_file.put_line(FND_FILE.LOG,' Trace not enabled');
1625 dbms_session.set_sql_trace(FALSE);
1626 END IF;
1627 --Bug# 6870773 Naveen
1628 l_org_enabled := nvl(fnd_profile.value('IEX_PROC_STR_ORG'),'N');
1629 -- l_org_id := fnd_profile.value('ORG_ID');
1630 l_org_id:=p_org_id;
1631
1632 if l_org_enabled = 'Y' then
1633 fnd_file.put_line(FND_FILE.LOG, 'Profile for processing strategies by operating unit is On ' || ' Org Id = ' || l_org_id);
1634
1635 select count(1)
1636 into l_count
1637 from iex_strategies
1638 where org_id is null
1639 and strategy_level=l_DefaultStrategyLevel;
1640
1641 if l_count>0 then
1642 fnd_file.put_line(FND_FILE.LOG, 'Found '||l_count||' strategies without having org_id.');
1643 fnd_file.put_line(FND_FILE.LOG, 'Please run the script iexstorg.sql before running this concurrent program.');
1644 return;
1645 end if;
1646 MO_GLOBAL.INIT('IEX');
1647 IF P_ORG_ID IS NOT NULL THEN
1648 MO_GLOBAL.SET_POLICY_CONTEXT('S',P_ORG_ID); -- Single Org.
1649 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MO: Operating Unit=' || p_org_id);
1650 ELSE
1651 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL); -- Multi Org.
1652 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MO: Operating Unit=' || 'All');
1653 END IF;
1654 FOR I_ORG IN C_ORG LOOP -- Moac Changes. Loop through for Party.
1655 MO_GLOBAL.SET_POLICY_CONTEXT('S',I_ORG.organization_id); -- Moac Changes. Set Org.
1656 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside Party Loop, Operating Unit Set =' ||I_ORG.organization_id);
1657 l_org_id:=mo_global.get_current_org_id;
1658
1659 CLOSE_STRATEGIES(errbuf => errbuf,
1660 retcode => retcode);
1661
1662
1663 PROCESS_ONHOLD_STRATEGIES;
1664
1665 OPEN_STRATEGIES(errbuf => errbuf,
1666 retcode => retcode, p_ignore_switch => p_ignore_switch);
1667
1668 END LOOP;
1669
1670 else
1671 fnd_file.put_line(FND_FILE.LOG, 'Profile for processing strategies by operating unit is Off ');
1672
1673 select count(1)
1674 into l_count
1675 from iex_strategies
1676 where org_id is not null
1677 and strategy_level=l_DefaultStrategyLevel;
1678
1679 if l_count>0 then
1680 fnd_file.put_line(FND_FILE.LOG, 'Found '||l_count||' strategies with org_id.');
1681 fnd_file.put_line(FND_FILE.LOG, 'Please run the script iexstorg.sql before running this concurrent program.');
1682 return;
1683 end if;
1684 l_org_id := null;
1685 CLOSE_STRATEGIES(errbuf => errbuf,
1686 retcode => retcode);
1687
1688 --Begin Bug#7248296 28-07-2008 barathsr
1689 PROCESS_ONHOLD_STRATEGIES;
1690 OPEN_STRATEGIES(errbuf => errbuf,
1691 retcode => retcode, p_ignore_switch => p_ignore_switch);
1692 end if;
1693 -- end Naveen
1694
1695 END;
1696
1697 PROCEDURE write_log(mesg_level IN NUMBER, mesg IN VARCHAR2) is
1698 l_schema varchar2(10);
1699 l_dot varchar2(10);
1700 l_module varchar2(10);
1701 BEGIN
1702 l_schema := 'iex';
1703 l_dot := '.';
1704 l_module := 'strategy';
1705 if (mesg_level >= l_msgLevel) then
1706 fnd_file.put_line(FND_FILE.LOG, mesg);
1707 FND_LOG.STRING(mesg_level, l_schema || l_dot || l_module , mesg);
1708 end if;
1709 END;
1710 --Bug# 6870773 Naveen
1711 PROCEDURE update_strat_org
1712 (
1713 ERRBUF OUT NOCOPY VARCHAR2,
1714 RETCODE OUT NOCOPY VARCHAR2
1715 ) IS
1716 cursor c_bill_strat_wo_ou(p_org_id number)
1717 is select st.strategy_id,su.org_id
1718 from iex_strategies st,
1719 hz_cust_site_uses_all su
1720 where st.object_type='BILL_TO'
1721 and st.org_id is null
1722 and st.object_id=su.site_use_id
1723 and su.org_id = p_org_id;
1724
1725 cursor c_cust_strat_wo_ou(p_org_id number)
1726 is select st.strategy_id,p_org_id
1727 from iex_strategies st,
1728 hz_parties hp
1729 where st.object_type='PARTY'
1730 and st.org_id is null
1731 and st.object_id=hp.party_id
1732 and not exists(select 1 from
1733 hz_cust_accounts ca,
1734 hz_cust_acct_sites_all cas,
1735 hz_cust_site_uses_all su
1736 where hp.party_id = ca.party_id
1737 and ca.cust_account_id=cas.cust_account_id
1738 and cas.cust_acct_site_id=su.cust_acct_site_id
1739 and su.org_id <> p_org_id)
1740 group by st.strategy_id,p_org_id;
1741
1742 cursor c_account_strat_wo_ou(p_org_id number)
1743 is select st.strategy_id,p_org_id
1744 from iex_strategies st,
1745 hz_cust_accounts ca
1746 where st.object_type='ACCOUNT'
1747 and st.org_id is null
1748 and st.object_id=ca.cust_account_id
1749 and not exists(select 1 from
1750 hz_cust_acct_sites_all cas,
1751 hz_cust_site_uses_all su
1752 where ca.cust_account_id=cas.cust_account_id
1753 and cas.cust_acct_site_id=su.cust_acct_site_id
1754 and su.org_id <> p_org_id);
1755
1756
1757 cursor c_del_strat_wo_ou(p_org_id number)
1758 is select st.strategy_id,del.org_id
1759 from iex_strategies st,
1760 iex_delinquencies_all del
1761 where st.object_type='DELINQUENT'
1762 and st.org_id is null
1763 and st.object_id=del.delinquency_id
1764 and del.org_id = p_org_id;
1765
1766 cursor c_strat_with_ou(p_object_type varchar2)
1767 is select st.strategy_id,null
1768 from iex_strategies st
1769 where st.object_type=p_object_type
1770 and st.org_id is not null;
1771 --and st.org_id = p_org_id;
1772
1773 TYPE strat_list IS TABLE OF IEX_STRATEGIES.STRATEGY_ID%TYPE;
1774 TYPE org_list IS TABLE OF IEX_STRATEGIES.ORG_ID%TYPE;
1775
1776 strategies strat_list;
1777 orgs org_list;
1778
1779
1780
1781 BEGIN
1782
1783 if l_DefaultStrategyLevel = 10 THEN
1784
1785 IF l_org_enabled = 'Y' THEN
1786 OPEN c_cust_strat_wo_ou(l_org_id);
1787 FETCH c_cust_strat_wo_ou BULK COLLECT INTO strategies,orgs;
1788 CLOSE c_cust_strat_wo_ou;
1789 /*for rec1 in c_cust_strat_wo_ou(l_org_id) loop
1790 update iex_strategies
1791 set org_id=rec1.org_id
1792 where strategy_id = rec1.strategy_id;
1793 end loop;*/
1794 ELSE
1795 OPEN c_strat_with_ou('PARTY');
1796 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
1797 CLOSE c_strat_with_ou;
1798 /*for rec1 in c_cust_strat_with_ou(l_org_id) loop
1799 update iex_strategies
1800 set org_id=null
1801 where strategy_id = rec1.strategy_id;
1802 end loop;*/
1803 END IF;
1804 elsif l_DefaultStrategyLevel = 20 THEN
1805
1806 IF l_org_enabled = 'Y' THEN
1807 OPEN c_account_strat_wo_ou(l_org_id);
1808 FETCH c_account_strat_wo_ou BULK COLLECT INTO strategies,orgs;
1809 CLOSE c_account_strat_wo_ou;
1810 ELSE
1811 OPEN c_strat_with_ou('ACCOUNT');
1812 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
1813 CLOSE c_strat_with_ou;
1814 END IF;
1815 elsif l_DefaultStrategyLevel = 30 THEN
1816
1817 IF l_org_enabled = 'Y' THEN
1818 OPEN c_bill_strat_wo_ou(l_org_id);
1819 FETCH c_bill_strat_wo_ou BULK COLLECT INTO strategies,orgs;
1820 CLOSE c_bill_strat_wo_ou;
1821 /*for rec1 in c_bill_strat_wo_ou(l_org_id) loop
1822 update iex_strategies
1823 set org_id=rec1.org_id
1824 where strategy_id = rec1.strategy_id;
1825 end loop;*/
1826 ELSE
1827 OPEN c_strat_with_ou('BILL_TO');
1828 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
1829 CLOSE c_strat_with_ou;
1830 /*for rec1 in c_bill_strat_with_ou(l_org_id) loop
1831 update iex_strategies
1832 set org_id=null
1833 where strategy_id = rec1.strategy_id;
1834 end loop;*/
1835 END IF;
1836 elsif l_DefaultStrategyLevel = 40 THEN
1837
1838 IF l_org_enabled = 'Y' THEN
1839 OPEN c_del_strat_wo_ou(l_org_id);
1840 FETCH c_del_strat_wo_ou BULK COLLECT INTO strategies,orgs;
1841 CLOSE c_del_strat_wo_ou;
1842 ELSE
1843 OPEN c_strat_with_ou('DELINQUENT');
1844 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
1845 CLOSE c_strat_with_ou;
1846 END IF;
1847
1848 end if;
1849 fnd_file.put_line(FND_FILE.LOG, 'Checking..');
1850 fnd_file.put_line(FND_FILE.LOG, 'Updating number of strategies ' || strategies.count);
1851 if strategies.count>0 then
1852
1853 forall i in strategies.first..strategies.last
1854 update iex_strategies
1855 set org_id=orgs(i)
1856 where strategy_id = strategies(i);
1857 commit;
1858 end if;
1859
1860 EXCEPTION
1861 WHEN OTHERS THEN
1862 write_log(FND_LOG.LEVEL_STATEMENT, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1863 fnd_file.put_line(FND_FILE.LOG, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1864 rollback;
1865
1866 END;
1867
1868 --end Naveen
1869
1870 BEGIN
1871 -- initialize values
1872 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
1873 l_enabled := 'N';
1874
1875 l_DelStatusCurrent := 'CURRENT';
1876 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1877 l_DelStatusClose := 'CLOSE';
1878 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1879 l_DelStatusDel := 'DELINQUENT';
1880 l_DelStatusPreDel := 'PREDELINQUENT';
1881
1882 l_StratStatusOpen := 'OPEN';
1883 l_StratStatusOnhold := 'ONHOLD';
1884 l_StratStatusPending := 'PENDING';
1885 l_StratStatusClosed := 'CLOSED';
1886 l_StratStatusCancelled := 'CANCELLED';
1887 l_Yes := 'Y';
1888 l_No := 'N';
1889 l_StratObjectFilterType := 'IEXSTRAT';
1890
1891 l_enabled := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1892 if (l_enabled = 'N') then
1893 l_MsgLevel := FND_LOG.LEVEL_UNEXPECTED;
1894 else
1895 l_MsgLevel := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1896 end if;
1897 l_DefaultTempID := GetDefaultStrategyTempID;
1898
1899 -- Start for bug # 5487449 on 28/08/2006 by gnramasa
1900 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Default Template ID ' || l_DefaultTempID || ' Profile Name IEX: Strategy Default Template (IEX_STRATEGY_DEFAULT_TEMPLATE)');
1901 begin
1902 select STRATEGY_NAME ,ENABLED_FLAG
1903 into l_DefaultTempName, l_EnabledFlag
1904 from iex_strategy_templates_vl
1905 where STRATEGY_TEMP_ID=l_DefaultTempID;
1906
1907 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Default Template Name :' || l_DefaultTempName || ' , Enabled Flag :' || l_EnabledFlag );
1908 EXCEPTION
1909 WHEN OTHERS THEN
1910 fnd_file.put_line(FND_FILE.LOG, 'Default Template Name raised Exception ' || SQLCODE || ' ' || SQLERRM);
1911 END;
1912
1913 begin
1914 l_default_rs_id := NVL(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'), 0);
1915 select SOURCE_NAME,USER_NAME
1916 into l_SourceName,l_UserName
1917 from jtf_rs_resource_extns
1918 where RESOURCE_ID=l_default_rs_id;
1919 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Resource Id :' || l_default_rs_id || ' Profile Name IEX: Strategy Assignment Default Resource (IEX_STRY_DEFAULT_RESOURCE) , Resource Name :' || l_SourceName || ' , User Name :' || l_UserName);
1920 EXCEPTION
1921 WHEN OTHERS THEN
1922 fnd_file.put_line(FND_FILE.LOG, 'Resource Name raised Exception ' || SQLCODE || ' ' || SQLERRM);
1923 END;
1924 -- Start for bug # 5877743 on 28/02/2007 by gnramasa
1925 begin
1926 --Begin Bug#7205287 31-Jul-2008 barathsr
1927 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Work Item Assignment Collector level from Profile (IEX: Territory Access Level) :'-- (IEX: Collector Access Level) :'
1928 || NVL(fnd_profile.value('IEX_ACCESS_LEVEL'), 'PARTY'));
1929 --End Bug#7205287 31-Jul-2008 barathsr
1930 EXCEPTION
1931 WHEN OTHERS THEN
1932 fnd_file.put_line(FND_FILE.LOG, 'Resource Name raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1933 END;
1934 -- End for bug # 5877743 on 28/02/2007 by gnramasa
1935 begin
1936 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
1937 into l_DefaultStrategyLevel,l_StrategyLevelName
1938 from iex_app_preferences_vl
1939 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = l_Yes;
1940 write_log(FND_LOG.LEVEL_STATEMENT, 'Current Strategy Level ' || l_DefaultStrategyLevel || ' , ' || l_StrategyLevelName);
1941 -- End for bug # 5487449 on 28/08/2006 by gnramasa
1942 EXCEPTION
1943 WHEN OTHERS THEN
1944 fnd_file.put_line(FND_FILE.LOG, 'Strategy Level Rised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1945 END;
1946 END IEX_STRATEGY_CNT_PUB;