[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_CNT_PUB
Source
1 PACKAGE BODY IEX_STRATEGY_CNT_PUB AS
2 /* $Header: iexpstcb.pls 120.93.12020000.7 2013/04/08 11:04:51 gnramasa 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 --Start adding for bug 8630852 by gnramasa 9-July-09
34 l_new_line VARCHAR2(1);
35 tempResult CLOB;
36 l_seq_no number := 1;
37 l_custom_select varchar2(2000);
38 l_no_closed_rec number := 0;
39 l_no_reopen_rec number := 0;
40 l_no_reassign_rec number := 0;
41 l_no_new_rec number := 0;
42 g_sty_level varchar2(15);
43 l_coll_at_ous varchar2(3);
44 l_unPocessed_orgids number := 0; -- added by snuthala for bug 10221334 on 21-10-2010
45 l_unregistered_org_ids number := 0; -- added by snuthala for bug 10221334 on 11/18/2010
46 --End adding for bug 8630852 by gnramasa 9-July-09
47
48 -- Start for bug 8708271 multi level strategy
49 l_party_override varchar(1);
50 l_org_override varchar(1);
51 l_system_strategy_level varchar2(30);
52 PROCEDURE cancel_strategy( p_party_id number, p_str_level varchar2 , p_str_mode varchar2,
53 p_cust_acc_id number, p_site_use_id number, p_del_id number, p_show_output varchar2);
54 -- End for bug 8708271 bug 8708271 multi level strategy
55 Procedure WriteLog ( p_msg IN VARCHAR2)
56 IS
57 BEGIN
58
59 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
60 iex_debug_pub.LogMessage (p_msg);
61 END IF;
62
63 END WriteLog;
64
65 /* Procedure for open strategy for customer, Account, bill_to, and delinquencies based on the strategy run level*/
66 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
67 --PROCEDURE open_strategies
68 --(
69 -- ERRBUF OUT NOCOPY VARCHAR2,
70 -- RETCODE OUT NOCOPY VARCHAR2
71 --) IS
72 --Bug# 6870773 Naveen
73 PROCEDURE update_strat_org
74 (
75 ERRBUF OUT NOCOPY VARCHAR2,
76 RETCODE OUT NOCOPY VARCHAR2
77 );
78
79 Procedure gen_xml_header_data_strategy (p_strategy_mode IN VARCHAR2);
80 Procedure gen_xml_body_strategy (p_strategy_id IN NUMBER DEFAULT NULL,
81 p_strategy_rec IN IEX_STRATEGY_PVT.STRATEGY_REC_TYPE DEFAULT NULL,
82 p_strategy_status IN VARCHAR2,
83 p_default_sty_level IN NUMBER DEFAULT NULL,
84 p_party_id IN NUMBER DEFAULT NULL,
85 p_cust_acc_id IN NUMBER DEFAULT NULL,
86 p_site_use_id IN NUMBER DEFAULT NULL,
87 p_del_id IN NUMBER DEFAULT NULL);
88
89 Procedure gen_xml_append_closetag_sty (p_customer_name_low IN VARCHAR2 DEFAULT NULL, -- added by gnramasa for bug 8833868 3-Sep-09
90 p_customer_name_high IN VARCHAR2 DEFAULT NULL, -- added by gnramasa for bug 8833868 3-Sep-09
91 p_account_number_low IN VARCHAR2 DEFAULT NULL, -- added by gnramasa for bug 8833868 3-Sep-09
92 p_account_number_high IN VARCHAR2 DEFAULT NULL, -- added by gnramasa for bug 8833868 3-Sep-09
93 p_billto_location_low IN VARCHAR2 DEFAULT NULL, -- added by gnramasa for bug 8833868 3-Sep-09
94 p_billto_location_high IN VARCHAR2 DEFAULT NULL, -- added by gnramasa for bug 8833868 3-Sep-09
95 p_org_id IN Number, -- added by snuthala for 10/21/2010
96 p_unprocessed_orgid_count IN NUMBER, -- added by snuthala for 10/21/2010
97 p_unregistered_org_ids IN NUMBER); -- added by snuthala for 11/18/2010
98
99 --Start adding by gnramasa for bug 8630852 13-July-09
100 Procedure custom_where_clause
101 (p_customer_name_low IN VARCHAR2,
102 p_customer_name_high IN VARCHAR2,
103 p_account_number_low IN VARCHAR2,
104 p_account_number_high IN VARCHAR2,
105 p_billto_location_low IN VARCHAR2,
106 p_billto_location_high IN VARCHAR2,
107 p_strategy_level IN NUMBER)
108 IS
109 l_api_name varchar2(50) := 'custom_where_clause';
110 BEGIN
111 --if l_StrategyLevelName <> 'DELINQUENCY' then
112 if p_strategy_level <> 40 then
113 l_custom_select := ' SELECT p.party_name ' ||
114 ' From hz_cust_acct_sites_all acct_sites, ' ||
115 ' hz_party_sites party_site, ' ||
116 ' hz_cust_accounts ca, ' ||
117 ' hz_cust_site_uses_all site_uses, ' ||
118 ' hz_parties p ' ||
119 ' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
120 ' AND acct_sites.party_site_id = party_site.party_site_id ' ||
121 ' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
122 ' AND site_uses.site_use_code = ''BILL_TO'' ' ||
123 ' AND ca.party_id = p.party_id ';
124 else
125 l_custom_select := 'SELECT p.party_name ' ||
126 ' From hz_cust_acct_sites_all acct_sites, ' ||
127 ' hz_party_sites party_site, ' ||
128 ' hz_cust_accounts ca, ' ||
129 ' hz_cust_site_uses_all site_uses, ' ||
130 ' hz_parties p,' ||
131 ' iex_delinquencies_all del ' ||
132 ' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
133 ' AND acct_sites.party_site_id = party_site.party_site_id ' ||
134 ' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
135 ' AND site_uses.site_use_code = ''BILL_TO'' ' ||
136 ' AND ca.party_id = p.party_id ' ||
137 ' AND del.customer_site_use_id = site_uses.site_use_id ';
138 end if;
139
140 if p_customer_name_low IS NOT NULL then
141 l_custom_select := l_custom_select || ' AND upper(p.party_name) >= upper(''' || p_customer_name_low || ''') ';
142 end if;
143
144 if p_customer_name_high IS NOT NULL then
145 l_custom_select := l_custom_select || ' AND upper(p.party_name) <= upper(''' || p_customer_name_high || ''') ';
146 end if;
147
148 if p_account_number_low IS NOT NULL then
149 l_custom_select := l_custom_select || ' AND upper(ca.account_number) >= upper(''' || p_account_number_low || ''') ';
150 end if;
151
152 if p_account_number_high IS NOT NULL then
153 l_custom_select := l_custom_select || ' AND upper(ca.account_number) <= upper(''' || p_account_number_high || ''') ';
154 end if;
155
156 if p_billto_location_low IS NOT NULL then
157 l_custom_select := l_custom_select || ' AND upper(site_uses.location) >= upper(''' || p_billto_location_low || ''') ';
158 end if;
159
160 if p_billto_location_high IS NOT NULL then
161 l_custom_select := l_custom_select || ' AND upper(site_uses.location) <= upper(''' || p_billto_location_high || ''') ';
162 end if;
163
164 /*
165 if l_StrategyLevelName = 'CUSTOMER' then
166 l_custom_select := l_custom_select || ' AND p.party_id ';
167 elsif l_StrategyLevelName = 'ACCOUNT' then
168 l_custom_select := l_custom_select || ' AND ca.cust_account_id ';
169 elsif l_StrategyLevelName = 'BILL_TO' then
170 l_custom_select := l_custom_select || ' AND site_uses.site_use_id ';
171 else
172 l_custom_select := l_custom_select || ' AND del.delinquency_id ';
173 end if;
174 */
175
176 if p_strategy_level = 10 then
177 l_custom_select := l_custom_select || ' AND p.party_id ';
178 elsif p_strategy_level = 20 then
179 l_custom_select := l_custom_select || ' AND ca.cust_account_id ';
180 elsif p_strategy_level = 30 then
181 l_custom_select := l_custom_select || ' AND site_uses.site_use_id ';
182 else
183 l_custom_select := l_custom_select || ' AND del.delinquency_id ';
184 end if;
185
186 write_log(FND_LOG.LEVEL_STATEMENT,G_PKG_NAME || ' ' || l_api_name || ' - l_custom_select : '||l_custom_select);
187
188 END custom_where_clause;
189 --End adding by gnramasa for bug 8630852 13-July-09
190
191 PROCEDURE open_strategies
192 (
193 ERRBUF OUT NOCOPY VARCHAR2,
194 RETCODE OUT NOCOPY VARCHAR2,
195 p_ignore_switch IN VARCHAR2,
196 p_strategy_mode IN VARCHAR2,
197 p_show_output IN VARCHAR2 ) -- added by gnramasa for bug 8630852 13-July-09
198 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
199 IS
200 l_result VARCHAR2(10);
201
202 l_error_msg VARCHAR2(2000);
203 l_return_status VARCHAR2(20);
204 l_msg_count NUMBER;
205 l_msg_data VARCHAR2(2000);
206 l_api_name VARCHAR2(100) ;
207 l_api_version_number CONSTANT NUMBER := 2.0;
208
209 vStrategyStatus VARCHAR2(30);
210 vStrategyStatus1 VARCHAR2(30); --Added for bug#5126770 schekuri 04-Apr-2006
211 vOrginalStrategyStatus VARCHAR2(30); --Added for bug#5202312 by schekuri on 05-May-2006
212 -- ctlee score tolerance checking
213 vScoreValue number;
214 vStrategyRank VARCHAR2(10);
215 vScoreTolerance number;
216 vStrategyId number;
217 vChangeStrategy VARCHAR2(4);
218 vStrategyTemplateId number;
219
220 l_strategy_processid NUMBER;
221 l_delinquency_id number;
222 l_party_cust_id number;
223 l_cust_account_id number;
224 l_transaction_id number;
225 l_payment_schedule_id number;
226 l_object_id number;
227 l_object_code varchar2(40);
228 l_strategy_id number;
229 l_strategy_template_id number;
230 l_object_version_number number := 1.0;
231 l_strat_count number:=0; --Added for bug#7594370 by PNAVEENK
232
233 Cursor c_score_exists( p_object_id number, p_object_type varchar2) is
234 select score_value,score_id
235 from iex_score_histories
236 where score_object_id = p_object_id
237 and score_object_code = p_object_type
238 order by creation_date desc;
239
240 l_stry_cnt_rec IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE ;
241
242 -- Begin - Andre - bug#4551569 - Change cursor to find 2 types of objects
243 Cursor c_score_exists_del(p_object_id number, p_object_type varchar2, p_object_id2 number, p_object_type2 varchar2) is
244 select score_value, score_object_id, score_object_code,score_id
245 from iex_score_histories
246 where score_object_id in (p_object_id, p_object_id2)
247 and score_object_code in (p_object_type, p_object_type2)
248 order by creation_date desc;
249
250 l_score_object_id number;
251 l_score_object_code varchar2(40);
252 -- End - Andre - bug#4551569 - Change cursor to find 2 types of objects
253
254 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
255 l_default_rs_id number ;
256 l_resource_id NUMBER;
257 l_StrategyTempID number;
258 b_Skip varchar2(10);
259
260
261 TYPE c_open_delinquenciesCurTyp IS REF CURSOR; -- weak
262 c_open_delinquencies c_open_delinquenciesCurTyp; -- declare cursor variable
263
264 TYPE c_strategy_existsCurTyp IS REF CURSOR; -- weak
265 c_strategy_exists c_strategy_existsCurTyp; -- declare cursor variable
266
267 -- get IEX Strategy grace period
268 -- Added default value for vGracePeriod for bug 14013369 bibeura
269 vGracePeriod Date := SYSDATE;
270 l_gracePeriod NUMBER ;
271 TYPE c_gracePeriodCurTyp IS REF CURSOR; -- weak
272 c_gracePeriod c_gracePeriodCurTyp; -- declare cursor variable
273
274 pre_delinquency_flag varchar2(1) ;
275 vCheckList varchar2(1) ;
276
277 -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
278 l_id_save number;
279 -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
280
281 -- bug 4141678 begin - ctlee
282 l_batch_size NUMBER ;
283 l_save_count NUMBER ;
284 l_commit_count NUMBER ;
285 l_str_count NUMBER := 0;
286 -- TYPE STRATEGY_ID_TBL_type is Table of IEX_strategies.strategy_id%TYPE INDEX BY BINARY_INTEGER;
287 -- l_strategy_tbl STRATEGY_ID_TBL_TYPE;
288 -- bug 4141678 end - ctlee
289
290 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
291 l_ignore_switch varchar2(1) := 'N';
292 l_del_query varchar2(2500);
293 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
294
295 l_temp_grace_period number:=0; --Added for bug#7594370 by PNAVEENK
296 l_reassign_sty varchar2(1):= 'N'; -- added by gnramasa for bug 8630852 13-July-09
297
298 l_turnoff_coll_on_bankru varchar2(10);
299 l_no_of_bankruptcy number;
300
301 cursor c_no_of_bankruptcy (p_par_id number)
302 is
303 select nvl(count(*),0)
304 from iex_bankruptcies
305 where party_id = p_par_id
306 and (disposition_code in ('GRANTED','NEGOTIATION')
307 OR (disposition_code is NULL));
308
309 CURSOR pre_del_strategy IS
310 SELECT predel_strategy_enabled
311 FROM iex_questionnaire_items;
312
313 l_pre_del_strategy VARCHAR2(1);
314 l_unpro_dels number;
315
316 BEGIN
317 -- initialize variable here
318 l_api_name := 'START_WORKFLOW';
319 l_stry_cnt_rec := IEX_STRATEGY_TYPE_PUB.INST_STRY_CNT_REC;
320 l_default_rs_id := NVL(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'), 0);
321 l_resource_id := NVL(fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE'), 0);
322 b_Skip := 'F';
323 l_gracePeriod := NVL(to_number(FND_PROFILE.VALUE('IEX_STRY_GRACE_PERIOD')), 0);
324 pre_delinquency_flag := 'N';
325 vCheckList := 'N';
326 l_batch_size := NVL(to_number(FND_PROFILE.VALUE('IEX_BATCH_SIZE')), 5000);
327 l_save_count := 0;
328 l_commit_count := 0;
329 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
330 l_ignore_switch := NVL(p_ignore_switch, 'N');
331 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
332
333
334 -- dbms_session.set_sql_trace(true);
335 -- Initialize API return status to SUCCESS
336 l_return_status := FND_API.G_RET_STS_SUCCESS;
337
338 /* Check the required profiles for Strategy Concurrent before starting */
339 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
340 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation aborted. ' );
341 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Disabled by Profile ');
342 return;
343 end if;
344
345 if (l_DefaultStrategyLevel = 50) Then
346 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
347 write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Run Level from IEX_APP_PREFERENCES ');
348 b_Skip := 'T';
349 end if;
350
351 l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
352 if (l_StrategyTempID = 0) Then
353 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
354 write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Template Profile ');
355 b_Skip := 'T';
356 end if;
357
358 if (l_default_rs_ID = 0) Then
359 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
360 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Default Resource Profile not set. IEX: Strategy Default Resource ');
361 b_Skip := 'T';
362 end if;
363
364 if (l_resource_ID = 0) Then
365 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
366 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Fulfilment Resource Profile not set. ');
367 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Fulfilment Resource should be configured for fulfilment ');
368 b_Skip := 'T';
369 end if;
370
371 if (b_Skip = 'T') then
372 retcode := '2';
373 return;
374 end if;
375
376 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation started. ' );
377 -- Standard Start of API savepoint
378 SAVEPOINT START_STRY_CONT;
379
380 begin
381 SELECT decode(COUNT(*), 0, 'N', 'Y') into pre_delinquency_flag FROM IEX_STRATEGY_TEMPLATES_VL
382 WHERE CATEGORY_TYPE = l_DelStatusPreDel;
383 EXCEPTION
384 WHEN OTHERS THEN
385 fnd_file.put_line(FND_FILE.LOG, 'Pre Delinquency flag raised exception; sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
386 end;
387
388 write_log(FND_LOG.LEVEL_UNEXPECTED, 'pre-delinquency strategy template flag = ' ||pre_delinquency_flag);
389
390 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
391 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Do not automatically switch strategies flag = ' ||l_ignore_switch);
392 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Grace Period = ' || l_graceperiod);
393 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
394
395 write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor started ');
396
397 write_log(FND_LOG.LEVEL_STATEMENT, 'System Strategy Level' || l_system_strategy_level);
398
399 OPEN pre_del_strategy;
400 FETCH pre_del_strategy INTO l_pre_del_strategy;
401 CLOSE pre_del_strategy;
402
403 /* Check the strategy Run level */
404 /* ctlee - add status and pass it to GetTemplateId 7/3/2003 */
405 IF l_DefaultStrategyLevel = 10 THEN
406 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
407 --OPEN c_open_delinquencies
408 -- FOR
409 l_del_query := 'select d.party_cust_id, null, null, null, null, null,';
410 l_del_query := l_del_query || ' d.party_cust_id object_id, ''PARTY'' object_type, null';
411 l_del_query := l_del_query || ' , 10 strategy_level, d.PARTY_CUST_ID jtf_object_id, ''PARTY'' jtf_object_type';
412 l_del_query := l_del_query || ' , null status';
413 l_del_query := l_del_query || ' from iex_delinquencies_all d';
414 -- Start for bug 8708271 multi level strategy
415 if l_party_override = 'Y' then
416 l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
417 end if;
418
419 IF l_pre_del_strategy = 'Y' THEN
420 l_del_query := l_del_query || ' where (d.status = ''' || l_DelStatusDel || '''' || ' or d.status = ''' || l_DelStatusPreDel || '''' || ')';
421 ELSE
422 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
423 END IF;
424
425 if l_party_override = 'Y' then
426 l_del_query := l_del_query || ' and partyPrf.party_id(+) = d.party_cust_id '
427 || ' and partyPrf.module(+) = ''COLLECTIONS'' '
428 || ' and partyPrf.category(+)=''COLLECTIONS LEVEL'' '
429 || ' and partyPrf.preference_code(+)=''PARTY_ID'' '
430 || ' and nvl(partyPrf.value_varchar2,'''||l_system_strategy_level||''')=''CUSTOMER'' ';
431 else
432
433 if l_system_strategy_level = 'CUSTOMER' then -- proceed when system strategy level is customer otherwise return
434 null;
435 else
436 return;
437 end if;
438 -- l_del_query := l_del_query || ' and l_system_strategy_level=''PARTY''';
439 end if;
440 -- end for bug 8708271 bug 8708271 multi level strategy
441 if l_custom_select IS NOT NULL then
442 l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.party_cust_id ) ';
443 end if;
444 --Bug#6870773 Naveen
445 if l_org_enabled = 'Y' then
446 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
447 end if;
448
449 if l_ignore_switch = 'Y' then
450 --Bug#520231 schekuri 05-MAY-2006
451 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
452 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'') ';
453 end if;
454
455 l_del_query := l_del_query || ' group by d.party_cust_id';
456 l_del_query := l_del_query || ' order by d.party_cust_id';
457 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
458 elsif l_DefaultStrategyLevel = 20 THEN
459 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
460 --OPEN c_open_delinquencies
461 --FOR
462 l_del_query := 'select d.party_cust_id, d.cust_account_id, null, null, null, null,';
463 l_del_query := l_del_query || ' d.cust_account_id object_id, ''ACCOUNT'' object_type, null,';
464 l_del_query := l_del_query || ' 20 strategy_level, d.cust_account_id jtf_object_id, ''IEX_ACCOUNT'' jtf_object_type';
465 l_del_query := l_del_query || ' , null status';
466 l_del_query := l_del_query || ' from iex_delinquencies_all d';
467 -- Start for bug 8708271 multi level strategy
468 if l_party_override = 'Y' then
469 l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
470 end if;
471
472 IF l_pre_del_strategy = 'Y' THEN
473 l_del_query := l_del_query || ' where (d.status = ''' || l_DelStatusDel || '''' || ' or d.status = ''' || l_DelStatusPreDel || '''' || ')';
474 ELSE
475 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
476 END IF;
477
478 if l_party_override = 'Y' then
479 l_del_query := l_del_query || ' and partyPrf.party_id(+) = d.party_cust_id '
480 || ' and partyPrf.module(+) = ''COLLECTIONS'' '
481 || ' and partyPrf.category(+)=''COLLECTIONS LEVEL'' '
482 || ' and partyPrf.preference_code(+)=''PARTY_ID'' '
483 || ' and nvl(partyPrf.value_varchar2,'''||l_system_strategy_level||''')=''ACCOUNT'' ';
484 else
485 if l_system_strategy_level = 'ACCOUNT' then
486 null;
487 else
488 return;
489 end if;
490 -- l_del_query := l_del_query || ' and l_system_strategy_level=''ACCOUNT''';
491 end if;
492 -- end for bug 8708271 bug 8708271 multi level strategy
493 if l_custom_select IS NOT NULL then
494 l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.cust_account_id ) ';
495 end if;
496 --Bug#6870773 Naveen
497 if l_org_enabled = 'Y' then
498 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
499 end if;
500 if l_ignore_switch = 'Y' then
501 --Bug#520231 schekuri 05-MAY-2006
502 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
503 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'') ';
504 end if;
505
506 l_del_query := l_del_query || ' group by d.party_cust_id, d.cust_account_id';
507 l_del_query := l_del_query || ' order by d.party_cust_id, d.cust_account_id';
508 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
509 elsif l_DefaultStrategyLevel = 30 THEN
510 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
511 --OPEN c_open_delinquencies
512 --FOR
513 l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, null, null, null,';
514 l_del_query := l_del_query || ' d.customer_site_use_id object_id, ''BILL_TO'' object_type, null,';
515 l_del_query := l_del_query || ' 30 strategy_level, d.customer_site_use_id jtf_object_id, ''IEX_BILLTO'' jtf_object_type';
516 l_del_query := l_del_query || ' , null status';
517 l_del_query := l_del_query || ' from iex_delinquencies_all d';
518 -- Start for bug 8708271 multi level strategy
519 if l_party_override = 'Y' then
520 l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
521 end if;
522
523 IF l_pre_del_strategy = 'Y' THEN
524 l_del_query := l_del_query || ' where (d.status = ''' || l_DelStatusDel || '''' || ' or d.status = ''' || l_DelStatusPreDel || '''' || ')';
525 ELSE
526 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
527 END IF;
528
529 if l_party_override = 'Y' then
530 l_del_query := l_del_query || ' and partyPrf.party_id(+) = d.party_cust_id '
531 || ' and partyPrf.module(+) = ''COLLECTIONS'' '
532 || ' and partyPrf.category(+)=''COLLECTIONS LEVEL'' '
533 || ' and partyPrf.preference_code(+)=''PARTY_ID'' '
534 || ' and nvl(partyPrf.value_varchar2,'''||l_system_strategy_level||''')=''BILL_TO'' ';
535
536 else
537 if l_system_strategy_level = 'BILL_TO' then
538 null;
539 else
540 return;
541 end if;
542 -- l_del_query := l_del_query || ' and l_system_strategy_level=''BILL_TO''';
543 end if;
544 -- end for bug 8708271 bug 8708271 multi level strategy
545 if l_custom_select IS NOT NULL then
546 l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.customer_site_use_id ) ';
547 end if;
548 --Bug#6870773 Naveen
549 if l_org_enabled = 'Y' then
550 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
551 end if;
552 if l_ignore_switch = 'Y' then
553 --Bug#520231 schekuri 05-MAY-2006
554 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
555 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'') ';
556 end if;
557
558 l_del_query := l_del_query || ' group by d.party_cust_id, d.cust_account_id, d.customer_site_use_id';
559 l_del_query := l_del_query || ' order by d.party_cust_id, d.cust_account_id, d.customer_site_use_id';
560 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
561 ELSE
562 if (pre_delinquency_flag = 'Y') then
563 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
564 --OPEN c_open_delinquencies
565 --FOR
566 l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id,';
567 l_del_query := l_del_query || ' d.transaction_id, d.payment_schedule_id,';
568 l_del_query := l_del_query || ' d.delinquency_id object_id, ''DELINQUENT'' object_type ,';
569 l_del_query := l_del_query || ' d.score_value, 40 strategy_level, d.delinquency_id jtf_object_id,';
570 l_del_query := l_del_query || ' ''IEX_DELINQUENCY'' jtf_object_type';
571 l_del_query := l_del_query || ' , d.status status';
572 l_del_query := l_del_query || ' from iex_delinquencies_all d';
573 -- Start for bug 8708271 multi level strategy
574 if l_party_override = 'Y' then
575 l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
576 end if;
577 l_del_query := l_del_query || ' where (d.status = ''' || l_DelStatusDel || '''' || ' or d.status = ''' || l_DelStatusPreDel || '''' || ')';
578 if l_party_override = 'Y' then
579 l_del_query := l_del_query || ' and partyPrf.party_id(+) = d.party_cust_id '
580 || ' and partyPrf.module(+) = ''COLLECTIONS'' '
581 || ' and partyPrf.category(+)=''COLLECTIONS LEVEL'' '
582 || ' and partyPrf.preference_code(+)=''PARTY_ID'' '
583 || ' and nvl(partyPrf.value_varchar2,'''||l_system_strategy_level||''')=''DELINQUENCY'' ';
584 else
585 if l_system_strategy_level = 'DELINQUENCY' then
586 null;
587 else
588 return;
589 end if;
590 -- l_del_query := l_del_query || ' and l_system_strategy_level=''DELINQUENCY''';
591 end if;
592 -- end for bug 8708271 multi level strategy
593 if l_custom_select IS NOT NULL then
594 l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.delinquency_id ) ';
595 end if;
596 --Bug#6870773 Naveen
597 if l_org_enabled = 'Y' then
598 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
599 end if;
600 if l_ignore_switch = 'Y' then
601 --Bug#520231 schekuri 05-MAY-2006
602 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
603 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'') ';
604 end if;
605 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
606 else
607 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
608 --OPEN c_open_delinquencies
609 --FOR
610 l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id,';
611 l_del_query := l_del_query || ' d.transaction_id, d.payment_schedule_id,';
612 l_del_query := l_del_query || ' d.delinquency_id object_id, ''DELINQUENT'' object_type ,';
613 l_del_query := l_del_query || ' d.score_value, 40 strategy_level, d.delinquency_id jtf_object_id,';
614 l_del_query := l_del_query || ' ''IEX_DELINQUENCY'' jtf_object_type';
615 l_del_query := l_del_query || ' , d.status status';
616 l_del_query := l_del_query || ' from iex_delinquencies_all d';
617 -- Start for bug 8708271 multi level strategy
618 if l_party_override = 'Y' then
619 l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
620 end if;
621 l_del_query := l_del_query || ' where d.status = ''' || l_DelStatusDel || '''';
622 if l_party_override = 'Y' then
623 l_del_query := l_del_query || ' and partyPrf.party_id(+) = d.party_cust_id '
624 || ' and partyPrf.module(+) = ''COLLECTIONS'' '
625 || ' and partyPrf.category(+)=''COLLECTIONS LEVEL'' '
626 || ' and partyPrf.preference_code(+)=''PARTY_ID'' '
627 || ' and nvl(partyPrf.value_varchar2,'''||l_system_strategy_level||''')=''DELINQUENCY'' ';
628 else
629 if l_system_strategy_level = 'DELINQUENCY' then
630 null;
631 else
632 return;
633 end if;
634 -- l_del_query := l_del_query || ' and l_system_strategy_level=''DELINQUENCY''';
635 end if;
636 -- end for bug 8708271 multi level strategy
637 if l_custom_select IS NOT NULL then
638 l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.delinquency_id ) ';
639 end if;
640 --Bug#6870773 Naveen
641 if l_org_enabled = 'Y' then
642 l_del_query := l_del_query || ' and d.org_id = ' || l_org_id ;
643 end if;
644 if l_ignore_switch = 'Y' then
645 --Bug#520231 schekuri 05-MAY-2006
646 --Changed OBJECT_TYPE and OBJECT_ID to JTF_OBJECT_TYPE and JTF_OBJECT_ID
647 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'') ';
648 end if;
649 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
650 end if;
651 END IF;
652 --Bug#6870773 Naveen
653
654 fnd_file.put_line(FND_FILE.LOG, l_del_query);
655 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
656 OPEN c_open_delinquencies
657 FOR l_del_query;
658 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
659
660 -- FOR f_delinquency_rec in C_Open_Delinquencies loop
661 LOOP
662
663 FETCH c_open_delinquencies INTO
664 l_stry_cnt_rec.party_cust_id,
665 l_stry_cnt_rec.cust_account_id,
666 l_stry_cnt_rec.customer_site_use_id,
667 l_stry_cnt_rec.delinquency_id,
668 l_stry_cnt_rec.transaction_id,
669 l_stry_cnt_rec.payment_schedule_id,
670 l_stry_cnt_rec.object_id,
671 l_stry_cnt_rec.object_type,
672 l_stry_cnt_rec.score_value,
673 l_stry_cnt_rec.strategy_level,
674 l_stry_cnt_rec.jtf_object_id,
675 l_stry_cnt_rec.jtf_object_type,
676 l_stry_cnt_rec.status;
677 /* ctlee - add status and pass it to GetTemplateId 7/3/2003 */
678
679 if c_open_delinquencies%FOUND then
680 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
681 write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency_id = '
682 || l_stry_cnt_rec.delinquency_id
683 || ' object Id = ' || l_stry_cnt_rec.object_id
684 || ' object_type = ' || l_stry_cnt_rec.object_type
685 || ' jtf_object Type ' || l_stry_cnt_rec.jtf_object_type
686 || ' jtf_object id ' || l_stry_cnt_rec.jtf_object_id
687 || ' Score Value = ' || l_stry_cnt_rec.score_value
688 || ' status = ' || l_stry_cnt_rec.status
689 || ' Strategy Level = ' || l_stry_cnt_rec.strategy_level );
690 END IF;
691
692 -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
693 --IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 then
694 IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 or l_DefaultStrategyLevel = 40 then
695 -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
696
697 begin
698 -- Begin - Andre Araujo -- bug#4551569 - 08/18/2005 - Scores for delinquencies still not being picked up
699 -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
700 -- if l_DefaultStrategyLevel = 40 then
701 -- l_id_save := l_stry_cnt_rec.jtf_object_id;
702 -- l_stry_cnt_rec.jtf_object_id := l_stry_cnt_rec.payment_schedule_id;
703 -- l_stry_cnt_rec.jtf_object_type := 'IEX_INVOICES';
704 -- end if;
705 -- -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
706 --
707 -- Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
708 -- fetch c_Score_Exists into l_stry_cnt_rec.score_value;
709 -- Close c_Score_Exists;
710 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
711 -- write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
712 -- || ' jtf_object Type ' || l_stry_cnt_rec.jtf_object_type
713 -- || ' jtf_object id ' || l_stry_cnt_rec.jtf_object_id
714 -- || ' Score Value = ' || l_stry_cnt_rec.score_value );
715 -- end if;
716 --
717 -- -- Begin - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
718 -- if l_DefaultStrategyLevel = 40 then
719 -- l_stry_cnt_rec.jtf_object_id := l_id_save;
720 -- l_stry_cnt_rec.jtf_object_type := 'IEX_DELINQUENCY';
721 -- end if;
722 -- -- End - Andre Araujo -- 01/18/2005 - We should look at score histories for delinquency scores too
723
724 if l_DefaultStrategyLevel <> 40 then -- This will pick the scores for all levels but not for delinquency
725 Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
726 fetch c_Score_Exists into l_stry_cnt_rec.score_value,l_stry_cnt_rec.score_id;
727 Close c_Score_Exists;
728
729 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
730 write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
731 || '; jtf_object Type= ' || l_stry_cnt_rec.jtf_object_type
732 || '; jtf_object id= ' || l_stry_cnt_rec.jtf_object_id
733 || '; Score Value = ' || l_stry_cnt_rec.score_value );
734 end if;
735 else
736 -- When looking for scores for delinquencies we should look for the newest score from either the payment schedule OR Delinquency
737 -- This is so because the first score of a delinquency is the score of the delinquent payment schedule
738 -- but if a customer scores the delinquency we should use the delinquency score to set the strategy
739 Open c_score_exists_del(l_stry_cnt_rec.payment_schedule_id, 'IEX_INVOICES', l_stry_cnt_rec.delinquency_id, 'IEX_DELINQUENCY');
740 fetch c_score_exists_del into l_stry_cnt_rec.score_value, l_score_object_id, l_score_object_code,l_stry_cnt_rec.score_id;
741 Close c_score_exists_del;
742 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
743 write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists_del '
744 || '; jtf_object Type= ' || l_score_object_code
745 || '; jtf_object id= ' || l_score_object_id
746 || '; Score Value = ' || l_stry_cnt_rec.score_value );
747 end if;
748 end if;
749 -- End - Andre Araujo -- bug#4551569 - 08/18/2005 - Scores for delinquencies still not being picked up
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 fnd_file.put_line(FND_FILE.LOG, 'NO score available ' ||
754 ' object Type ' || l_stry_cnt_rec.jtf_object_type ||
755 ' object ID ' || l_stry_cnt_rec.jtf_object_id);
756 l_strategy_rec.score_value := 0;
757 l_stry_cnt_rec.score_value := 0;
758 retcode := '1';
759 END;
760 end if;
761
762
763 -- ctlee score tolerance checking
764 -- set to 0 if null
765 if (l_stry_cnt_rec.score_value is null) then
766 l_stry_cnt_rec.score_value := 0;
767 end if;
768
769 -- check grace period
770 -- c_gracePeriod c_gracePeriodCurTyp;
771 -- Added the if condition for bug 14013369 bibeura
772 -- The IF condition is added to avoid execution of the following piece of code
773 -- when the profile value for grace period is not set which will improve the performance
774 IF NVL(l_gracePeriod,0) <> 0 THEN
775 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
776 write_log(FND_LOG.LEVEL_PROCEDURE, 'l_gracePeriod-Bina ' || l_gracePeriod );
777 END IF;
778 IF l_DefaultStrategyLevel = 10 THEN
779 OPEN c_gracePeriod FOR
780 select c.creation_date from iex_delinquencies_all c
781 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
782 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
783 order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
784 elsif l_DefaultStrategyLevel = 20 THEN
785 OPEN c_gracePeriod FOR
786 select c.creation_date from iex_delinquencies_all c
787 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
788 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
789 and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
790 order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
791 elsif l_DefaultStrategyLevel = 30 THEN
792 OPEN c_gracePeriod FOR
793 select c.creation_date from iex_delinquencies_all c
794 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
795 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
796 and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
797 and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
798 order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
799 ELSE
800 OPEN c_gracePeriod FOR
801 select c.creation_date from iex_delinquencies_all c
802 where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
803 and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
804 and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
805 and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
806 and c.delinquency_id = l_stry_cnt_rec.delinquency_id
807 order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
808 END IF;
809 loop
810 fetch c_gracePeriod into vGracePeriod;
811 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
812 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy grace Period ' || vGracePeriod );
813 end if;
814 if c_gracePeriod%notfound then
815 exit;
816 end if;
817 exit;
818 end loop;
819 Close c_gracePeriod;
820 END IF;
821 l_turnoff_coll_on_bankru := nvl(fnd_profile.value('IEX_TURNOFF_COLLECT_BANKRUPTCY'),'N');
822 write_log(FND_LOG.LEVEL_PROCEDURE, 'l_turnoff_coll_on_bankru: ' || l_turnoff_coll_on_bankru);
823
824 if l_turnoff_coll_on_bankru = 'Y' then
825 open c_no_of_bankruptcy (l_stry_cnt_rec.PARTY_CUST_ID);
826 fetch c_no_of_bankruptcy into l_no_of_bankruptcy;
827 close c_no_of_bankruptcy;
828 end if;
829 write_log(FND_LOG.LEVEL_PROCEDURE, 'l_no_of_bankruptcy: ' || l_no_of_bankruptcy);
830
831 if (l_turnoff_coll_on_bankru = 'Y' and l_no_of_bankruptcy >0) then
832 write_log(FND_LOG.LEVEL_PROCEDURE, 'Profile IEX: Turn Off Collections Activity for Bankruptcy is Yes and bankruptcy record is exist, so will skip assigning strategy');
833 goto nextRec;
834 end if;
835
836
837 -- check the status to see if the strategy has already run workflow
838 vStrategyStatus := NULL;
839
840 IF l_DefaultStrategyLevel = 10 THEN
841 OPEN c_strategy_exists FOR
842 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
843 from iex_strategies where party_id = l_stry_cnt_rec.PARTY_CUST_ID
844 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
845 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
846 and checklist_yn = vCheckList;
847 elsif l_DefaultStrategyLevel = 20 THEN
848 OPEN c_strategy_exists FOR
849 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
850 from iex_strategies where CUST_ACCOUNT_ID = l_stry_cnt_rec.CUST_ACCOUNT_ID
851 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
852 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
853 and checklist_yn = vCheckList;
854 elsif l_DefaultStrategyLevel = 30 THEN
855 OPEN c_strategy_exists FOR
856 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
857 from iex_strategies where customer_site_use_ID = l_stry_cnt_rec.customer_site_use_ID
858 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
859 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
860 and checklist_yn = vCheckList;
861 ELSE
862 OPEN c_strategy_exists FOR
863 select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
864 from iex_strategies where delinquency_id = l_stry_cnt_rec.delinquency_id
865 and jtf_object_id = l_stry_cnt_rec.jtf_object_id
866 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
867 and checklist_yn = vCheckList;
868 END IF;
869
870 /*
871 Check any strategy already running then skip
872 or if the open/pending strategy is out of score tolerance in its defined template then
873 cancel the old strategy and create a new one (change_strategy needs to be Y)
874 */
875 --Begin bug#5126770 schekuri 04-Apr-2006
876 --reset the variables in each iteration of the loop
877 vStrategyStatus := NULL;
878 vStrategyStatus1 := NULL;
879 vScoreValue := NULL;
880 vStrategyId := NULL;
881 vStrategyTemplateId := NULL;
882 --End bug#5126770 schekuri 04-Apr-2006
883
884 loop
885 fetch c_Strategy_Exists into vStrategyStatus, vScoreValue, vStrategyId, vStrategyTemplateId;
886 if c_Strategy_exists%notfound then
887 exit;
888 elsif vStrategyStatus in ( l_StratStatusOpen, l_StratStatusPending, l_StratStatusOnhold) then
889 exit;
890 end if;
891 end loop;
892 Close C_Strategy_Exists;
893
894 vOrginalStrategyStatus :=vStrategyStatus; --Added for bug#5202312 by schekuri on 05-May-2006
895
896 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status = ' || vStrategyStatus );
897 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Score Value = ' || vScoreValue );
898 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Id = ' || vStrategyId );
899 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Template Id = ' || vStrategyTemplateId );
900 write_log(FND_LOG.LEVEL_PROCEDURE, 'score_history/delinquency Score Value = ' || l_stry_cnt_rec.score_value );
901
902 if (vStrategyStatus = 'OPEN' or vStrategyStatus = 'PENDING') then
903 begin
904 select strategy_rank, decode(score_tolerance, null, 0, score_tolerance), change_strategy_yn
905 into vStrategyRank, vScoreTolerance, vChangeStrategy
906 from iex_strategy_templates_vl where strategy_temp_id = vStrategyTemplateId;
907 exception
908 when others then
909 vStrategyRank := '0';
910 vScoreTolerance := 0;
911 vChangeStrategy := 'N';
912 end;
913 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914 write_log(FND_LOG.LEVEL_PROCEDURE, 'Get Strategy Template Details of = ' || vStrategyTemplateId || ' of Strategy ID = ' || vStrategyID );
915 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Rank = ' || vStrategyRank );
916 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
917 write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
918 END IF;
919
920 -- score in iex_strategies diff from score_histories/iex_delinquencies_all table
921 -- and score history out of the strategy template score tolerance
922 -- and strategy template change strategy flag is Y
923 if (
924 ( (vScoreValue <> l_stry_cnt_rec.score_value)
925 and
926 (
927 -- begin bug 4944801 ctlee 01/18/2006
928 -- (l_stry_cnt_rec.score_value > to_number(vStrategyRank) + vScoreTolerance)
929 -- or
930 -- (l_stry_cnt_rec.score_value < to_number(vStrategyRank) - vScoreTolerance)
931 (l_stry_cnt_rec.score_value > vScoreValue + vScoreTolerance)
932 or
933 (l_stry_cnt_rec.score_value < vScoreValue - vScoreTolerance)
934 -- end bug 4944801 ctlee 01/18/2006
935 )
936 )
937 and
938 (vChangeStrategy = 'Y')
939 ) then
940
941 -- cancel strategy
942 -- begin bug 4944801 ctlee 01/18/2006, cancel strategy only if the new strategy template id different from the old one
943 -- BEGIN
944 -- write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy id = ' || vStrategyId );
945 -- IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
946 -- strategy_id => vStrategyId,
947 -- status => 'CANCELLED' ) ;
948 -- EXCEPTION
949 -- WHEN OTHERS THEN
950 -- write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' );
951 -- UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED' WHERE STRATEGY_ID = vStrategyId;
952 -- END;
953 -- end bug 4944801 ctlee 01/18/2006, cancel strategy only if the new strategy template id different from the old one
954 vStrategyStatus := 'CANCELLED';
955 vStrategyStatus1 := 'CANCELLED'; --Added for bug#5126770 by schekuri on 04-Apr-2006
956 end if;
957 end if; -- if OPEN or PENDING
958
959
960 /* No Strategy exists or Existing running are closed, create a new strategy */
961 if (((vStrategyStatus IS NULL)) or (vStrategyStatus = l_StratStatusClosed)
962 or (vStrategyStatus = l_StratStatusCancelled)) then
963
964 fnd_file.put_line(FND_FILE.LOG, ' Get Template for object '|| l_stry_cnt_rec.jtf_object_id);
965 /* Get the strategy template ID based on the score */
966 IEX_STRATEGY_CNT_PUB.GetStrategyTempID(
967 x_return_status=>l_return_status,
968 p_stry_cnt_rec => l_stry_cnt_rec,
969 x_strategy_template_id => l_strategy_template_id
970 );
971 fnd_file.put_line(FND_FILE.LOG, ' Template ID selected ' || l_strategy_template_id);
972 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
973 write_log(FND_LOG.LEVEL_PROCEDURE, 'Delinquency ID ' || l_stry_cnt_rec.delinquency_id ||
974 ' Strategy Template ID selected ' || l_strategy_template_id );
975 end if;
976
977 -- start for bug 8708271 multi level strategy
978 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
979 write_log(FND_LOG.LEVEL_PROCEDURE, 'Cancelling strategies for Party ID ' || l_stry_cnt_rec.party_cust_id ||
980 ' if exists other than level ' || l_DefaultStrategyLevel );
981 --end if;
982 cancel_strategy( l_stry_cnt_rec.party_cust_id, l_DefaultStrategyLevel,p_strategy_mode,
983 l_stry_cnt_rec.cust_account_id, l_stry_cnt_rec.customer_site_use_id, l_stry_cnt_rec.delinquency_id, p_show_output);
984 -- end for bug 8708271 multi level strategy
985 fnd_file.put_line(FND_FILE.LOG, 'Cancelletion of other level Strategies completed for object ' || l_stry_cnt_rec.party_cust_id);
986 -- begin bug 4944801 ctlee 01/18/2006
987 -- check if the template is the same
988 BEGIN
989 -- old template is vStrategyTemplateId
990 -- new template is l_strategy_template_id
991 --Begin bug#5202312 schekuri 05-May-2006
992 --Need to check the status of the strategy in the database(not the modified one)
993 /*if (l_strategy_template_id = vStrategyTemplateId and vStrategyStatus <> l_StratStatusClosed and
994 vStrategyStatus <> l_StratStatusCancelled) then*/
995 if (l_strategy_template_id = vStrategyTemplateId and vOrginalStrategyStatus <> l_StratStatusClosed and
996 vOrginalStrategyStatus <> l_StratStatusCancelled) then
997 --End bug#5202312 schekuri 05-May-2006
998
999 write_log(FND_LOG.LEVEL_PROCEDURE, 'same template and continue, strategy template id = ' || vStrategyTemplateId );
1000 goto nextRec; -- continue to the loop for the next record, no need to change strategy
1001
1002 --Begin Bug#5126770 schekuri 04-Apr-2006
1003 --Added IF condition to the following block of code to avoid junk Cancellation of Strategies
1004 --Also enclosed the block of code between BEGIN and END and
1005 --moved the exception handler from the outer block
1006 elsif vStrategyId IS NOT NULL AND vStrategyStatus1 = 'CANCELLED' THEN
1007 --Start adding by gnramasa for bug 8630852 13-July-09
1008 if p_strategy_mode = 'FINAL' then
1009 BEGIN
1010 write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy id = ' || vStrategyId );
1011 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1012 strategy_id => vStrategyId,
1013 status => 'CANCELLED' ) ;
1014 EXCEPTION
1015 WHEN OTHERS THEN
1016 -- Added for bug 5877743 by gnramasa on 28-02-2007
1017 write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1018 UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED',
1019 last_update_date=sysdate --Added for bug#7594370 by PNAVEENK
1020 WHERE STRATEGY_ID = vStrategyId;
1021 END;
1022 end if; --if p_strategy_mode = 'FINAL' then
1023
1024 l_reassign_sty := 'Y';
1025 --End adding by gnramasa for bug 8630852 13-July-09
1026
1027 --End Bug#5126770 schekuri 04-Apr-2006
1028 end if;
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 --Begin bug#5126770 schekuri 04-Apr-2006
1032 --Moved the exception handler to the inner block
1033 NULL;
1034 /*write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' );
1035 UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED' WHERE STRATEGY_ID = vStrategyId;*/
1036 --End bug#5126770 schekuri 04-Apr-2006
1037 END;
1038 -- end bug 4944801 ctlee 01/18/2006
1039
1040
1041 --Start bug 6794510 gnramasa 7th feb 2008
1042 if (NVL(FND_PROFILE.VALUE('IEX_SKIP_DEFAULT_STRATEGY_ASSIGNMENT'), 'N') = 'Y') then
1043 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1044 IEX_DEBUG_PUB.LogMessage( 'Skip Default Strategy Assignment Profile value is: Y' );
1045 IEX_DEBUG_PUB.LogMessage( 'l_strategy_template_id: '|| l_strategy_template_id);
1046 IEX_DEBUG_PUB.LogMessage( 'l_StrategyTempID: '|| l_StrategyTempID);
1047 END IF;
1048 IF l_strategy_template_id = l_StrategyTempID THEN
1049 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1050 IEX_DEBUG_PUB.LogMessage( 'Strategy creation aborted. ' );
1051 IEX_DEBUG_PUB.LogMessage( 'Skip Default Strategy Assignment by Profile ');
1052 END IF;
1053 goto nextRec; -- continue to the loop for the next record, no need to change strategy
1054 END IF;
1055 end if;
1056 --End bug 6794510 gnramasa 7th feb 2008
1057 /* ctlee - GetTemplateId could be -1 for predelinquent status (donot use default template)
1058 7/3/2003
1059 ctlee - Always use default template if not found; filtering at open_strategies()
1060 no -1 is retrun 03/05/2004
1061 */
1062 /* --Begin bug#7565056 schekuri 19-N0v-2008
1063 --if (l_strategy_template_id <> -1)
1064 --and (trunc(sysdate) >= trunc(vGracePeriod) + l_gracePeriod) then
1065 if vStrategyId is not null then
1066 l_temp_grace_period:=0;
1067 else
1068 l_temp_grace_period:=l_gracePeriod;
1069 end if;
1070 if (l_strategy_template_id <> -1)
1071 and (trunc(sysdate) >= trunc(vGracePeriod) + l_temp_grace_period) then
1072 --End bug#7565056 schekuri 19-N0v-2008 */
1073 -- Begin for bug#7594370 by PNAVEENK
1074
1075 if (vStrategyId is not null) and (l_strategy_template_id <> -1) then
1076 if vOrginalStrategyStatus in ('OPEN','ONHOLD') then
1077 l_temp_grace_period:=0;
1078 else
1079 select count(1)
1080 into l_strat_count
1081 from iex_strategies
1082 where jtf_object_id = l_stry_cnt_rec.jtf_object_id
1083 and jtf_object_type = l_stry_cnt_rec.jtf_object_type
1084 and checklist_yn = vCheckList
1085 and last_update_date>=trunc(sysdate)-1
1086 and status_code not in ('OPEN','ONHOLD');
1087 if l_strat_count>0 then
1088 l_temp_grace_period:=0;
1089 else
1090 l_temp_grace_period:=l_gracePeriod;
1091 end if;
1092 end if;
1093 else
1094 l_temp_grace_period:=l_gracePeriod;
1095 end if;
1096 if (l_strategy_template_id <> -1)
1097 and (trunc(sysdate) >= trunc(vGracePeriod) + l_temp_grace_period) then
1098 -- End for bug#7594370 by PNAVEENK
1099 begin
1100 l_strategy_rec.strategy_template_id := l_strategy_template_id;
1101 l_strategy_rec.delinquency_id := l_stry_cnt_rec.delinquency_id;
1102 l_strategy_rec.party_id := l_stry_cnt_rec.party_cust_id;
1103 l_strategy_rec.cust_account_id := l_stry_cnt_rec.cust_account_id;
1104 l_strategy_rec.customer_site_use_id := l_stry_cnt_rec.customer_site_use_id;
1105 l_strategy_rec.next_work_item_id := null;
1106 l_strategy_rec.object_id := l_stry_cnt_rec.object_id;
1107 l_strategy_rec.object_type := l_stry_cnt_rec.object_type;
1108 -- l_strategy_rec.status_code := l_StratStatusOpen;
1109 l_strategy_rec.score_value := l_stry_cnt_rec.score_value;
1110 l_strategy_rec.checklist_yn := 'N';
1111 l_object_version_number := 1;
1112 l_strategy_rec.strategy_level := l_stry_cnt_rec.strategy_level;
1113 l_strategy_rec.jtf_object_type := l_stry_cnt_rec.jtf_object_type;
1114 l_strategy_rec.jtf_object_id := l_stry_cnt_rec.jtf_object_id;
1115
1116 --Start adding for bug 9032245 gnramasa 19th Oct 09
1117 if l_strategy_rec.strategy_level = 10 then
1118
1119 SELECT count(1)
1120 into l_unpro_dels
1121 FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1122 WHERE del.party_cust_id=l_strategy_rec.object_id
1123 AND ps.payment_schedule_id = del.payment_schedule_id
1124 AND ps.status = 'OP'
1125 AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1126 and not exists(select 1
1127 from iex_promise_details pd,
1128 gl_sets_of_books gl,
1129 ar_system_parameters_all sys
1130 where pd.delinquency_id=del.delinquency_id
1131 and pd.status='COLLECTABLE'
1132 AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
1133 and gl.set_of_books_id = sys.set_of_books_id
1134 and ps.org_id = sys.org_id
1135 group by pd.delinquency_id
1136 having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1137
1138 IF l_unpro_dels <=0 then
1139 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1140 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for party id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1141 END IF;
1142 l_strategy_rec.status_code := l_StratStatusOnhold;
1143 ELSE
1144 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1145 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for party id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1146 END IF;
1147 l_strategy_rec.status_code := l_StratStatusOpen;
1148 END IF;
1149 elsif l_strategy_rec.strategy_level = 20 then
1150
1151 SELECT count(1)
1152 into l_unpro_dels
1153 FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1154 WHERE del.cust_account_id=l_strategy_rec.object_id
1155 AND ps.payment_schedule_id = del.payment_schedule_id
1156 AND ps.status = 'OP'
1157 AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1158 and not exists(select 1
1159 from iex_promise_details pd,
1160 gl_sets_of_books gl,
1161 ar_system_parameters_all sys
1162 where pd.delinquency_id=del.delinquency_id
1163 and pd.status='COLLECTABLE'
1164 AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
1165 and gl.set_of_books_id = sys.set_of_books_id
1166 and ps.org_id = sys.org_id
1167 group by pd.delinquency_id
1168 having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1169
1170 IF l_unpro_dels <=0 then
1171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1172 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for account id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1173 END IF;
1174 l_strategy_rec.status_code := l_StratStatusOnhold;
1175 ELSE
1176 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1177 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for account id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1178 END IF;
1179 l_strategy_rec.status_code := l_StratStatusOpen;
1180 END IF;
1181 elsif l_strategy_rec.strategy_level = 30 then
1182
1183 SELECT count(1)
1184 into l_unpro_dels
1185 FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1186 WHERE del.customer_site_use_id=l_strategy_rec.object_id
1187 AND ps.payment_schedule_id = del.payment_schedule_id
1188 AND ps.status = 'OP'
1189 AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1190 and not exists(select 1
1191 from iex_promise_details pd,
1192 gl_sets_of_books gl,
1193 ar_system_parameters_all sys
1194 where pd.delinquency_id=del.delinquency_id
1195 and pd.status='COLLECTABLE'
1196 AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
1197 and gl.set_of_books_id = sys.set_of_books_id
1198 and ps.org_id = sys.org_id
1199 group by pd.delinquency_id
1200 having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1201
1202 IF l_unpro_dels <=0 then
1203 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1204 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for site use id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1205 END IF;
1206 l_strategy_rec.status_code := l_StratStatusOnhold;
1207 ELSE
1208 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1209 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for site use id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1210 END IF;
1211 l_strategy_rec.status_code := l_StratStatusOpen;
1212 END IF;
1213 else
1214
1215 SELECT count(1)
1216 into l_unpro_dels
1217 FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1218 WHERE del.delinquency_id=l_strategy_rec.object_id
1219 AND ps.payment_schedule_id = del.payment_schedule_id
1220 AND ps.status = 'OP'
1221 AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1222 and not exists(select 1
1223 from iex_promise_details pd,
1224 gl_sets_of_books gl,
1225 ar_system_parameters_all sys
1226 where pd.delinquency_id=del.delinquency_id
1227 and pd.status='COLLECTABLE'
1228 AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
1229 and gl.set_of_books_id = sys.set_of_books_id
1230 and ps.org_id = sys.org_id
1231 group by pd.delinquency_id
1232 having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1233
1234 IF l_unpro_dels <=0 then
1235 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1236 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for delinquency id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1237 END IF;
1238 l_strategy_rec.status_code := l_StratStatusOnhold;
1239 ELSE
1240 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1241 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for delinquency id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1242 END IF;
1243 l_strategy_rec.status_code := l_StratStatusOpen;
1244 END IF;
1245 end if;
1246 --End adding for bug 9032245 gnramasa 19th Oct 09
1247
1248
1249 --Bug#6870773 Naveen
1250 if l_org_enabled = 'Y' then
1251 l_strategy_rec.org_id := l_org_id ;
1252 else
1253 l_strategy_rec.org_id := null;
1254 end if;
1255
1256 --Start adding by gnramasa for bug 8630852 13-July-09
1257 if p_strategy_mode = 'FINAL' then
1258
1259 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1260 write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Create strategy for Delinquency ID '
1261 || l_strategy_rec.delinquency_id);
1262 end if;
1263 Begin
1264 -- bug 4141678 begin - ctlee
1265 -- p_commit set to false
1266 -- bug 4141678 end - ctlee
1267 fnd_file.put_line(FND_FILE.LOG,'Value of l_strategy_rec.org_id : '|| l_strategy_rec.org_id);
1268 fnd_file.put_line(FND_FILE.LOG, ' Creating Strategy for ' || l_strategy_rec.object_id || ' of type ' || l_strategy_rec.object_type);
1269 iex_strategy_pvt.create_strategy(
1270 P_Api_Version_Number=>2.0,
1271 p_commit => FND_API.G_FALSE,
1272 P_Init_Msg_List =>FND_API.G_TRUE,
1273 p_strategy_rec => l_strategy_rec,
1274 x_return_status=>l_return_status,
1275 x_msg_count=>l_msg_count,
1276 x_msg_data=>l_msg_data,
1277 x_strategy_id => l_strategy_id
1278 );
1279
1280 l_strategy_rec.strategy_id := l_strategy_id;
1281 fnd_file.put_line(FND_FILE.LOG, 'Strategy Created . Id = ' || l_strategy_id);
1282 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1283 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1284 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy created. id = ' || l_strategy_id);
1285 end if;
1286 -- bug 4141678 begin - ctlee
1287 -- EXCEPTION
1288 -- WHEN OTHERS THEN
1289 -- write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy create Return status = ' ||
1290 -- l_return_status || ' ' || sqlerrm );
1291 -- retcode := '2';
1292 -- return;
1293 -- END;
1294 -- bug 4141678 end - ctlee
1295
1296
1297 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1298 write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Workflow creation for Delinquency ID '
1299 || l_strategy_rec.strategy_id);
1300 end if;
1301
1302 -- BEGIN
1303 -- bug 4141678 begin - ctlee
1304 -- p_commit set to false
1305 -- bug 4141678 end - ctlee
1306
1307 iex_strategy_wf_pub.start_workflow(
1308 P_Api_Version =>2.0,
1309 P_Init_Msg_List => FND_API.G_TRUE,
1310 p_commit => FND_API.G_FALSE,
1311 p_strategy_rec => l_strategy_rec,
1312 x_return_status=>l_return_status,
1313 x_msg_count=>l_msg_count,
1314 x_msg_data=>l_msg_data
1315 );
1316
1317 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1318 write_log(FND_LOG.LEVEL_STATEMENT, 'Workflow Launch Return status = '
1319 || l_return_status) ;
1320 end if;
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 fnd_file.put_line(FND_FILE.LOG, ' Exception: Create Strategy/Workflow Launch Return status = '
1324 || l_return_status || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1325 retcode := '2';
1326 -- bug 4141678 begin - ctlee
1327 fnd_file.put_line(FND_FILE.LOG, 'commit count = ' || l_commit_count);
1328 fnd_file.put_line(FND_FILE.LOG, 'save count = ' || l_save_count);
1329 rollback;
1330 l_save_count := 0;
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332 exit;
1333 -- bug 4141678 end - ctlee
1334 END;
1335 end if; --if p_strategy_mode = 'FINAL' then
1336
1337 --Call gen_xml_body_strategy to insert this record to xml body
1338 IF p_show_output = 'Y' THEN
1339 if l_reassign_sty = 'N' THEN
1340 gen_xml_body_strategy (p_strategy_rec => l_strategy_rec,
1341 p_strategy_status => 'CREATE');
1342 elsif l_reassign_sty = 'Y' then
1343 gen_xml_body_strategy (p_strategy_id => vStrategyId,
1344 p_strategy_rec => l_strategy_rec,
1345 p_strategy_status => 'RECREATE');
1346 end if; --if l_reassign_sty 'N' then
1347 END IF; -- p_show_output
1348 l_reassign_sty := 'N';
1349 --End adding by gnramasa for bug 8630852 13-July-09
1350
1351 -- bug 4141678 begin - ctlee
1352 l_save_count := l_save_count + 1;
1353 l_str_count := l_str_count +1;
1354
1355 -- l_strategy_tbl(l_save_count) := l_strategy_id;
1356 if (l_save_count = l_batch_size) then
1357 l_save_count := 0;
1358 l_commit_count := l_commit_count + 1;
1359 commit work;
1360 end if;
1361 -- bug 4141678 end - ctlee
1362 end;
1363 end if; /* if template id is -1 then donot generate streategy */
1364 end if; /* check status */
1365 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1366 write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor ends' );
1367 end if;
1368 ELSE -- fetch failed, so exit loop
1369 EXIT;
1370 end if; /* found cursor */
1371 <<nextRec>>
1372 null;
1373 END loop;
1374
1375 -- bug 4141678 begin - ctlee
1376 if (l_save_count > 0) then
1377 l_commit_count := l_commit_count + 1;
1378 commit work;
1379 end if;
1380 write_log(FND_LOG.LEVEL_UNEXPECTED, 'commit count = ' || l_commit_count);
1381 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Total number of strategies created in running Operating Unit = '|| l_str_count );
1382 -- bug 4141678 end - ctlee
1383
1384 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Delinquency cursor EXIT ');
1385 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation completed ' );
1386
1387 close c_open_delinquencies;
1388
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 fnd_file.put_line(FND_FILE.LOG, 'Delinquency Concurrent raised exception sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1392 close c_open_delinquencies;
1393 -- bug 4141678 begin - ctlee
1394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1395 -- bug 4141678 end - ctlee
1396 END open_strategies;
1397
1398 /* not used anywhere */
1399 PROCEDURE CLOSE_ALL_STRY
1400 (
1401 ERRBUF OUT NOCOPY VARCHAR2,
1402 RETCODE OUT NOCOPY VARCHAR2
1403 ) IS
1404 l_result VARCHAR2(10);
1405
1406 l_error_msg VARCHAR2(2000);
1407 l_return_status VARCHAR2(20);
1408 l_msg_count NUMBER;
1409 l_msg_data VARCHAR2(2000);
1410 l_api_name VARCHAR2(100) ;
1411 l_api_version_number CONSTANT NUMBER := 2.0;
1412
1413 vStrategyStatus VARCHAR2(30);
1414 l_strategy_processid NUMBER;
1415
1416 l_delinquency_id number;
1417 l_party_cust_id number;
1418 l_cust_account_id number;
1419 l_object_id number;
1420 l_object_code varchar2(40);
1421 l_strategy_id number;
1422 l_strategy_template_id number;
1423 l_object_version_number number := 1.0;
1424 l_strategy_process_id number;
1425
1426 Cursor c_open_strategies is
1427 select s.strategy_id, s.delinquency_id,
1428 s.object_id, s.object_type, s.strategy_template_id, s.jtf_object_type, s.jtf_object_id
1429 from iex_strategies s where s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold) AND
1430 checklist_yn = 'N';
1431
1432 l_stry_cnt_rec IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE ;
1433
1434 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
1435
1436 l_itemtype varchar2(30);
1437 l_itemkey varchar2(50);
1438
1439 BEGIN
1440
1441 -- initialize variables
1442 l_api_name := 'START_WORKFLOW';
1443 l_stry_cnt_rec := IEX_STRATEGY_TYPE_PUB.INST_STRY_CNT_REC;
1444
1445 -- Initialize API return status to SUCCESS
1446 l_return_status := FND_API.G_RET_STS_SUCCESS;
1447 -- Standard Start of API savepoint
1448 SAVEPOINT CLOSE_STRY_CONT;
1449
1450 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor started ');
1451
1452 FOR f_strategy_rec in C_Open_strategies loop
1453
1454 /* Create the strategy record */
1455 l_stry_cnt_rec.strategy_id := f_strategy_rec.strategy_id;
1456 l_stry_cnt_rec.delinquency_id := f_strategy_rec.delinquency_id;
1457 l_stry_cnt_rec.object_id := f_strategy_rec.object_id;
1458 l_stry_cnt_rec.object_type := f_strategy_rec.object_type;
1459
1460 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_stry_cnt_rec.strategy_id
1461 || ' Delinquency ID ' || l_stry_cnt_rec.delinquency_id
1462 || ' Object ID ' || l_stry_cnt_rec.object_id
1463 || ' Object Type ' || l_stry_cnt_rec.object_type
1464 || ' Strategy Template ID ' || l_strategy_template_id );
1465
1466 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status ' || vStrategyStatus );
1467
1468 l_itemtype := 'IEXSTRY';
1469 l_itemkey := to_char(l_stry_cnt_rec.strategy_id);
1470
1471 BEGIN
1472 IEX_STRATEGY_WF.Send_Signal(
1473 process => l_itemtype,
1474 strategy_id => l_itemkey,
1475 status => l_StratStatusClosed
1476 );
1477
1478 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Closed. id = ' || l_stry_cnt_rec.strategy_id);
1479
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 fnd_file.put_line(FND_FILE.LOG, 'Strategy Closed Raised Exception = ' ||
1483 l_stry_cnt_rec.strategy_id || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1484 END;
1485
1486 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
1487
1488 END loop;
1489 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor EXIT ');
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 fnd_file.put_line(FND_FILE.LOG, 'Close Strategy raised exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1494
1495 END CLOSE_ALL_STRY;
1496
1497 /* Procedure for closing strategy when associated customer delinquencies are closed */
1498 PROCEDURE Close_strategies
1499 (
1500 ERRBUF OUT NOCOPY VARCHAR2,
1501 RETCODE OUT NOCOPY VARCHAR2,
1502 p_strategy_mode IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
1503 p_show_output IN VARCHAR2
1504 ) IS
1505 l_result VARCHAR2(10);
1506
1507 l_error_msg VARCHAR2(2000);
1508 l_return_status VARCHAR2(20);
1509 l_msg_count NUMBER;
1510 l_msg_data VARCHAR2(2000);
1511 l_api_name VARCHAR2(100) ;
1512 l_api_version_number CONSTANT NUMBER := 2.0;
1513
1514 vStrategyStatus VARCHAR2(30);
1515 l_strategy_processid NUMBER;
1516
1517 l_delinquency_id number;
1518 l_party_cust_id number;
1519 l_cust_account_id number;
1520 l_object_id number;
1521 l_object_code varchar2(40);
1522 l_strategy_id number;
1523 l_strategy_template_id number;
1524 l_object_version_number number := 1.0;
1525 l_strategy_process_id number;
1526
1527 l_itemtype varchar2(30);
1528 l_itemkey varchar2(50);
1529
1530 TYPE c_open_strategiesCurTyp IS REF CURSOR; -- weak
1531 c_open_strategies c_open_strategiesCurTyp; -- declare cursor variable
1532
1533 --Start added by gnramasa for bug 8630852 13-July-09
1534 vPLSQL VARCHAR2(5000);
1535
1536 BEGIN
1537 -- initialize variables
1538 l_api_name := 'START_WORKFLOW';
1539
1540 -- Initialize API return status to SUCCESS
1541 l_return_status := FND_API.G_RET_STS_SUCCESS;
1542
1543 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
1544 write_log(FND_LOG.LEVEL_STATEMENT,' Profile Name IEX: Strategy Disabled (IEX_STRATEGY_DISABLED) set to YES ');
1545 return;
1546 end if;
1547 -- Standard Start of API savepoint
1548 SAVEPOINT CLOSE_STRY_CONT;
1549
1550
1551 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN -- commented by gnramasa on 29/08/2006 for bug # 5487449
1552 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor started ');
1553 --end if;
1554
1555 --Start adding for bug 8756947 gnramasa 3rd Aug 09
1556 IF l_DefaultStrategyLevel = 10 THEN
1557 vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
1558 ' where s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
1559 ' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
1560 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1561 ' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.party_cust_id = s.party_id '||
1562 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1563 /* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
1564 ' and s.JTF_OBJECT_TYPE = ''PARTY''' ||
1565 /* End add for bug 16568002 gnramasa 29th Mar 2013 */
1566 ' and not exists (select null from iex_delinquencies_all dd where dd.status '||
1567 /* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
1568 --' = ''' || l_DelStatusDel || ''' and dd.party_cust_id = s.party_id) ';
1569 ' in ( ''' || l_DelStatusDel || ''', ''' || l_DelStatusPreDel || ''') and dd.party_cust_id = s.party_id) ';
1570 /* End add for bug 16563459 gnramasa 8th Apr 2013 */
1571 --and dd.org_id = decode(l_org_enabled,'Y',l_org_id,dd.org_id))
1572
1573 if l_org_enabled = 'Y' then
1574 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode( ''' || l_org_enabled || ''',''Y'', ' || l_org_id || ',nvl(s.org_id,-99)) '; --Bug# 6870773 Naveen
1575 end if;
1576 if l_custom_select IS NOT NULL then
1577 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
1578 end if;
1579 vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE';
1580
1581 elsif l_DefaultStrategyLevel = 20 THEN
1582 vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
1583 ' where s.strategy_level = ' || l_DefaultStrategyLevel ||' and '||
1584 ' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
1585 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1586 ' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id '||
1587 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1588 /* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
1589 ' and s.JTF_OBJECT_TYPE = ''IEX_ACCOUNT''' ||
1590 /* End add for bug 16568002 gnramasa 29th Mar 2013 */
1591 ' and not exists (select null from iex_delinquencies_all dd where dd.status '||
1592 /* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
1593 --' = ''' || l_DelStatusDel || ''' and dd.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id) ';
1594 ' in ( ''' || l_DelStatusDel || ''', ''' || l_DelStatusPreDel || ''') and dd.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id) ';
1595 /* End add for bug 16563459 gnramasa 8th Apr 2013 */
1596 --and dd.org_id = decode(l_org_enabled,'Y',l_org_id,dd.org_id))
1597 if l_org_enabled = 'Y' then
1598 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode(''' || l_org_enabled || ''',''Y'',' || l_org_id || ',nvl(s.org_id,-99)) '; --Bug# 6870773 Naveen
1599 end if;
1600 if l_custom_select IS NOT NULL then
1601 vPLSQL := vPLSQL || ' and exists (' || l_custom_select || ' = s.cust_Account_id) ';
1602 end if;
1603 vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE';
1604
1605 elsif l_DefaultStrategyLevel = 30 THEN
1606 vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
1607 ' where s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
1608 ' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
1609 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1610 ' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.customer_site_use_id = s.customer_site_use_id '||
1611 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1612 /* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
1613 ' and s.JTF_OBJECT_TYPE = ''IEX_BILLTO''' ||
1614 /* End add for bug 16568002 gnramasa 29th Mar 2013 */
1615 ' and not exists (select null from iex_delinquencies_all dd where dd.status '||
1616 /* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
1617 --' = ''' || l_DelStatusDel || ''' and dd.customer_site_use_id = s.customer_site_use_id) ';
1618 ' in ( ''' || l_DelStatusDel || ''', ''' || l_DelStatusPreDel || ''') and dd.customer_site_use_id = s.customer_site_use_id) ';
1619 /* End add for bug 16563459 gnramasa 8th Apr 2013 */
1620 -- and dd.org_id = decode(l_org_enabled,'Y',l_org_id,dd.org_id))
1621 if l_org_enabled = 'Y' then
1622 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode(''' || l_org_enabled || ''',''Y'',' || l_org_id || ',nvl(s.org_id,-99)) '; --Bug# 6870773 Naveen
1623 end if;
1624 if l_custom_select IS NOT NULL then
1625 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.customer_site_use_id) ';
1626 end if;
1627 vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE';
1628
1629 else
1630 /* begin bug 4253030 by ctlee 03/29/2005 */
1631 /*
1632 OPEN c_open_strategies
1633 FOR
1634 select s.strategy_id, s.strategy_template_id, s.status_code
1635 from iex_strategies s, iex_delinquencies_all d where d.status = l_DelStatusCurrent and
1636 s.strategy_level = l_DefaultStrategyLevel and
1637 s.object_id = d.delinquency_id and
1638 s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending);
1639 */
1640 /* end bug 4253030 by ctlee 03/29/2005 */
1641 vPLSQL := 'select s.strategy_id, s.strategy_template_id, s.status_code '||
1642 ' from iex_strategies s, iex_delinquencies_all d '||
1643 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1644 ' where (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and '||
1645 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1646 /* Begin add for bug 16568002 gnramasa 1st Apr 2013 */
1647 ' s.JTF_OBJECT_TYPE = ''IEX_DELINQUENCY'' and ' ||
1648 /* End add for bug 16568002 gnramasa 1st Apr 2013 */
1649 ' s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
1650 ' s.jtf_object_id = d.delinquency_id and '||
1651 ' s.status_code IN (''' || l_StratStatusOpen || ''' , ''' || l_StratStatusOnhold || ''' , ''' || l_StratStatusPending || ''') ';
1652 if l_org_enabled = 'Y' then
1653 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode(''' || l_org_enabled || ''',''Y'',' || l_org_id ||',nvl(s.org_id,-99)) ';
1654 end if;
1655 if l_custom_select IS NOT NULL then
1656 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || ' = s.delinquency_id)'; --Bug# 6870773 Naveen
1657 end if;
1658 END IF;
1659 --End adding for bug 8756947 gnramasa 3rd Aug 09
1660
1661 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1662 write_log(FND_LOG.LEVEL_PROCEDURE, 'Close Strategies vPLSQL :' || vPLSQL);
1663 END IF;
1664 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Close Strategies vPLSQL :' || vPLSQL);
1665
1666 OPEN c_open_strategies FOR vPLSQL;
1667
1668 -- FOR f_strategy_rec in C_Open_strategies loop
1669 LOOP
1670 FETCH c_open_strategies INTO l_strategy_id, l_Strategy_template_id, vStrategyStatus ;
1671 if c_open_strategies%FOUND then
1672
1673
1674 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1675 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_strategy_id
1676 || ' Strategy Status ' || vStrategyStatus
1677 || ' Strategy Template ID ' || l_strategy_template_id );
1678
1679 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status ' || vStrategyStatus );
1680 end if;
1681
1682 if p_strategy_mode = 'FINAL' then
1683 l_itemtype := 'IEXSTRY';
1684 l_itemkey := to_char(l_strategy_id);
1685
1686 BEGIN
1687 IEX_STRATEGY_WF.Send_Signal(
1688 process => l_itemtype,
1689 strategy_id => l_itemkey,
1690 status => l_StratStatusClosed
1691 );
1692
1693 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1694 write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Closed. id = ' || l_strategy_id);
1695 end if;
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 fnd_file.put_line(FND_FILE.LOG, 'Strategy Closed Rised Exception = '
1700 || l_strategy_id || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1701 retcode := '2';
1702 END;
1703 end if; --if p_strategy_mode = 'FINAL' then
1704
1705 --Call gen_xml_body_strategy to insert this record to xml body
1706 IF p_show_output = 'Y' THEN
1707 gen_xml_body_strategy (p_strategy_id => l_strategy_id,
1708 p_strategy_status => 'CLOSE');
1709 END IF;
1710 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1711 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
1712 end if;
1713 ELSE -- fetch failed, so exit loop
1714 EXIT;
1715 end if;
1716 END loop;
1717 write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor EXIT ');
1718 close c_open_strategies;
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 fnd_file.put_line(FND_FILE.LOG, 'Close Strategy raised exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1722 close c_open_strategies;
1723
1724 END close_strategies;
1725
1726
1727 --Begin Bug#7248296 28-07-2008 barathsr
1728 PROCEDURE PROCESS_ONHOLD_STRATEGIES (p_strategy_mode IN VARCHAR2, p_show_output IN VARCHAR2 )
1729 IS
1730 TYPE c_onhold_strategiesCurTyp IS REF CURSOR;
1731 c_onhold_strategies c_onhold_strategiesCurTyp;
1732
1733 vPLSQL VARCHAR2(5000);
1734 /*
1735 cursor c_party_onhold_st is
1736 select s.strategy_id strategy_id,
1737 s.strategy_template_id strategy_template_id,
1738 S.STATUS_CODE STATUS_CODE,
1739 d.party_cust_id party_id
1740 from iex_strategies s, iex_delinquencies_all d
1741 where s.strategy_level = 10 and
1742 s.status_code = 'ONHOLD' and
1743 d.status in ('DELINQUENT','PREDELINQUENT') and
1744 d.party_cust_id = s.party_id and
1745 not exists (select 1 from iex_promise_details p
1746 where p.status='COLLECTABLE'
1747 AND d.delinquency_id=p.delinquency_id)
1748 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
1749 --and exists ( l_custom_select = s.party_id)
1750 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.party_cust_id;
1751
1752 cursor c_account_onhold_st is
1753 select s.strategy_id strategy_id,
1754 s.strategy_template_id strategy_template_id,
1755 S.STATUS_CODE STATUS_CODE,
1756 d.cust_account_id cust_account_id
1757 from iex_strategies s, iex_delinquencies_all d
1758 where s.strategy_level = 20 and
1759 s.status_code = 'ONHOLD' and
1760 d.status in ('DELINQUENT','PREDELINQUENT') and
1761 d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id and
1762 not exists (select 1 from iex_promise_details p
1763 where p.status='COLLECTABLE'
1764 AND d.delinquency_id=p.delinquency_id)
1765 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
1766 --and exists ( l_custom_select = s.cust_Account_id)
1767 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.cust_account_id;
1768
1769 cursor c_billto_onhold_st is
1770 select s.strategy_id strategy_id,
1771 s.strategy_template_id strategy_template_id,
1772 S.STATUS_CODE STATUS_CODE,
1773 d.customer_site_use_id billto_id
1774 from iex_strategies s, iex_delinquencies_all d
1775 where s.strategy_level = 30 and
1776 s.status_code = 'ONHOLD' and
1777 d.status in ('DELINQUENT','PREDELINQUENT') and
1778 d.customer_site_use_id = s.customer_site_use_id and
1779 not exists (select 1 from iex_promise_details p
1780 where p.status='COLLECTABLE'
1781 AND d.delinquency_id=p.delinquency_id)
1782 and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
1783 --and exists ( l_custom_select = s.customer_site_use_id)
1784 group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.customer_site_use_id;
1785 */
1786 -- l_DefaultStrategyLevel number; -- commented for bug 8708271 multi level strategy
1787 -- l_StrategyLevelName varchar2(30); -- commented for bug 8708271 multi level strategy
1788
1789 l_return_status varchar2(10);
1790 l_msg_count number;
1791 l_msg_data varchar2(200);
1792 l_strategy_id number;
1793 l_strategy_template_id number;
1794 l_status_code varchar2(50);
1795 l_party_id number;
1796 l_cust_account_id number;
1797 l_cust_site_use_id number;
1798 l_delinquency_id number;
1799 l_release_date date; -- Bug 14804876 bibeura
1800
1801 begin
1802
1803 /* select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
1804 into l_DefaultStrategyLevel,l_StrategyLevelName
1805 from iex_app_preferences_vl
1806 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y'; */
1807
1808 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1809 write_log(FND_LOG.LEVEL_STATEMENT, ' Strategy level is: ' || l_DefaultStrategyLevel);
1810 END IF;
1811
1812 if l_DefaultStrategyLevel = 10 then
1813 vPLSQL := 'select s.strategy_id strategy_id, '||
1814 ' s.strategy_template_id strategy_template_id, '||
1815 ' S.STATUS_CODE STATUS_CODE, '||
1816 ' d.party_cust_id party_id '||
1817 ' from iex_strategies s, iex_delinquencies_all d '||
1818 ' where s.strategy_level = 10 and '||
1819 ' s.status_code = ''ONHOLD'' and '||
1820 ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
1821 ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
1822 ' d.party_cust_id = s.party_id and '||
1823 ' not exists (select 1 from iex_promise_details p '||
1824 ' where p.status=''COLLECTABLE'' '||
1825 ' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
1826 ' AND d.delinquency_id=p.delinquency_id) ';
1827 --Start adding for bug 8756947 gnramasa 3rd Aug 09
1828 if l_org_enabled = 'Y' then
1829 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode(''' || l_org_enabled || ''',''Y'',' || l_org_id ||',nvl(s.org_id,-99)) ';
1830 end if;
1831 if l_custom_select IS NOT NULL then
1832 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
1833 end if;
1834 vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.party_cust_id';
1835
1836 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1837 write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1838 END IF;
1839 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1840
1841 open c_onhold_strategies for vPLSQL;
1842 loop
1843 fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_party_id;
1844
1845 if c_onhold_strategies%FOUND then
1846 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1847 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for party id : = ' || l_party_id);
1848 END IF;
1849
1850 if p_strategy_mode = 'FINAL' then
1851 iex_strategy_pub.set_strategy(
1852 P_Api_Version_Number => 2.0,
1853 P_Init_Msg_List => 'F',
1854 P_Commit => 'F',
1855 p_validation_level => null,
1856 X_Return_Status => l_return_status,
1857 X_Msg_Count => l_msg_count,
1858 X_Msg_Data => l_msg_data,
1859 p_DelinquencyID => null,
1860 p_ObjectType => 'PARTY',
1861 p_ObjectID => l_party_id,
1862 p_Status => 'OPEN');
1863 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1864 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1865 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1866 END IF;
1867 end if; --if p_strategy_mode = 'FINAL' then
1868
1869 --Call gen_xml_body_strategy to insert this record to xml body
1870 IF p_show_output = 'Y' THEN
1871 gen_xml_body_strategy (p_strategy_id => l_strategy_id,
1872 p_strategy_status => 'REOPEN');
1873 END IF;
1874 ELSE -- fetch failed, so exit loop
1875 EXIT;
1876 end if;
1877 end loop;
1878 close c_onhold_strategies;
1879 write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
1880
1881 elsif l_DefaultStrategyLevel = 20 then
1882 vPLSQL := 'select s.strategy_id strategy_id, '||
1883 ' s.strategy_template_id strategy_template_id, '||
1884 ' S.STATUS_CODE STATUS_CODE, '||
1885 ' d.cust_account_id cust_account_id '||
1886 ' from iex_strategies s, iex_delinquencies_all d '||
1887 ' where s.strategy_level = 20 and '||
1888 ' s.status_code = ''ONHOLD'' and '||
1889 ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
1890 ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
1891 ' d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id and '||
1892 ' not exists (select 1 from iex_promise_details p '||
1893 ' where p.status=''COLLECTABLE'' '||
1894 ' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
1895 ' AND d.delinquency_id=p.delinquency_id) ';
1896 if l_org_enabled = 'Y' then
1897 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode(''' || l_org_enabled || ''',''Y'',' || l_org_id ||' ,nvl(s.org_id,-99)) ';
1898 end if;
1899 if l_custom_select IS NOT NULL then
1900 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || ' = s.cust_Account_id) ';
1901 end if;
1902 vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.cust_account_id';
1903
1904 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1905 write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1906 END IF;
1907 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1908
1909 open c_onhold_strategies for vPLSQL;
1910 loop
1911 fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_cust_account_id;
1912 if c_onhold_strategies%FOUND then
1913
1914 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1915 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for account id: = ' || l_cust_account_id);
1916 END IF;
1917
1918 if p_strategy_mode = 'FINAL' then
1919 iex_strategy_pub.set_strategy(
1920 P_Api_Version_Number => 2.0,
1921 P_Init_Msg_List => 'F',
1922 P_Commit => 'F',
1923 p_validation_level => null,
1924 X_Return_Status => l_return_status,
1925 X_Msg_Count => l_msg_count,
1926 X_Msg_Data => l_msg_data,
1927 p_DelinquencyID => null,
1928 p_ObjectType => 'ACCOUNT',
1929 p_ObjectID => l_cust_account_id,
1930 p_Status => 'OPEN');
1931 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1932 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1933 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1934 END IF;
1935 end if; --if p_strategy_mode = 'FINAL' then
1936
1937 --Call gen_xml_body_strategy to insert this record to xml body
1938 IF p_show_output ='Y' THEN
1939 gen_xml_body_strategy (p_strategy_id => l_strategy_id,
1940 p_strategy_status => 'REOPEN');
1941
1942 END IF;
1943 ELSE -- fetch failed, so exit loop
1944 EXIT;
1945 end if;
1946 end loop;
1947 close c_onhold_strategies;
1948 write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
1949
1950 elsif l_DefaultStrategyLevel = 30 then
1951 vPLSQL := 'select s.strategy_id strategy_id, '||
1952 ' s.strategy_template_id strategy_template_id, '||
1953 ' S.STATUS_CODE STATUS_CODE, '||
1954 ' d.customer_site_use_id billto_id '||
1955 ' from iex_strategies s, iex_delinquencies_all d '||
1956 ' where s.strategy_level = 30 and '||
1957 ' s.status_code = ''ONHOLD'' and '||
1958 ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
1959 ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
1960 ' d.customer_site_use_id = s.customer_site_use_id and '||
1961 ' not exists (select 1 from iex_promise_details p '||
1962 ' where p.status=''COLLECTABLE'' '||
1963 ' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
1964 ' AND d.delinquency_id=p.delinquency_id) ';
1965 if l_org_enabled = 'Y' then
1966 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode( ''' || l_org_enabled ||''',''Y'',' || l_org_id ||',nvl(s.org_id,-99)) ';
1967 end if;
1968 if l_custom_select IS NOT NULL then
1969 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.customer_site_use_id) ';
1970 end if;
1971 --End adding for bug 8756947 gnramasa 3rd Aug 09
1972 vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.customer_site_use_id';
1973
1974 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1975 write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1976 END IF;
1977 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1978
1979 open c_onhold_strategies for vPLSQL;
1980 loop
1981 fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_cust_site_use_id;
1982 if c_onhold_strategies%FOUND then
1983 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1984 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for customer site use id : = ' || l_cust_site_use_id);
1985 END IF;
1986
1987 if p_strategy_mode = 'FINAL' then
1988 iex_strategy_pub.set_strategy(
1989 P_Api_Version_Number => 2.0,
1990 P_Init_Msg_List => 'F',
1991 P_Commit => 'F',
1992 p_validation_level => null,
1993 X_Return_Status => l_return_status,
1994 X_Msg_Count => l_msg_count,
1995 X_Msg_Data => l_msg_data,
1996 p_DelinquencyID => null,
1997 p_ObjectType => 'BILL_TO',
1998 p_ObjectID => l_cust_site_use_id,
1999 p_Status => 'OPEN');
2000 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2001 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2002 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2003 END IF;
2004 end if; --if p_strategy_mode = 'FINAL' then
2005
2006 --Call gen_xml_body_strategy to insert this record to xml body
2007 IF p_show_output = 'Y' THEN
2008 gen_xml_body_strategy (p_strategy_id => l_strategy_id,
2009 p_strategy_status => 'REOPEN');
2010 END IF;
2011 ELSE -- fetch failed, so exit loop
2012 EXIT;
2013 end if;
2014 end loop;
2015 close c_onhold_strategies;
2016 write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
2017 else
2018 -- added for bug 11693277 PNAVEENK
2019 vPLSQL := 'select s.strategy_id strategy_id, '||
2020 ' s.strategy_template_id strategy_template_id, '||
2021 ' S.STATUS_CODE STATUS_CODE, '||
2022 ' d.delinquency_id delinquency_id '||
2023 ' from iex_strategies s, iex_delinquencies_all d '||
2024 ' where s.strategy_level = 40 and '||
2025 ' s.status_code = ''ONHOLD'' and '||
2026 ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
2027 ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
2028 ' d.delinquency_id = s.delinquency_id and '||
2029 ' not exists (select 1 from iex_promise_details p '||
2030 ' where p.status=''COLLECTABLE'' '||
2031 ' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
2032 ' AND d.delinquency_id=p.delinquency_id) ';
2033 if l_org_enabled = 'Y' then
2034 vPLSQL := vPLSQL || ' and nvl(s.org_id,-99) = decode( ''' || l_org_enabled ||''',''Y'',' || l_org_id ||',nvl(s.org_id,-99)) ';
2035 end if;
2036 if l_custom_select IS NOT NULL then
2037 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= d.delinquency_id) ';
2038 end if;
2039 --End adding for bug 8756947 gnramasa 3rd Aug 09
2040 vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.delinquency_id';
2041
2042 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2043 write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
2044 END IF;
2045 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
2046
2047 open c_onhold_strategies for vPLSQL;
2048 loop
2049 fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_delinquency_id;
2050 if c_onhold_strategies%FOUND then
2051 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2052 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for delinquency id : = ' || l_delinquency_id);
2053 END IF;
2054
2055 if p_strategy_mode = 'FINAL' then
2056 iex_strategy_pub.set_strategy(
2057 P_Api_Version_Number => 2.0,
2058 P_Init_Msg_List => 'F',
2059 P_Commit => 'F',
2060 p_validation_level => null,
2061 X_Return_Status => l_return_status,
2062 X_Msg_Count => l_msg_count,
2063 X_Msg_Data => l_msg_data,
2064 p_DelinquencyID => null,
2065 p_ObjectType => 'DELINQUENT',
2066 p_ObjectID => l_delinquency_id,
2067 p_Status => 'OPEN');
2068 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2069 write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2070 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2071 END IF;
2072 end if; --if p_strategy_mode = 'FINAL' then
2073
2074 --Call gen_xml_body_strategy to insert this record to xml body
2075 IF p_show_output = 'Y' THEN
2076 gen_xml_body_strategy (p_strategy_id => l_strategy_id,
2077 p_strategy_status => 'REOPEN');
2078 END IF;
2079 ELSE -- fetch failed, so exit loop
2080 EXIT;
2081 end if;
2082 end loop;
2083 close c_onhold_strategies;
2084 write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
2085
2086 -- end for bug 11693277
2087 end if;
2088 EXCEPTION
2089 when others then
2090 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2091 write_log(FND_LOG.LEVEL_STATEMENT, ' In PROCESS_ONHOLD_STRATEGIES when others exception');
2092 END IF;
2093 END PROCESS_ONHOLD_STRATEGIES;
2094 --End Bug#7248296 28-07-2008 barathsr
2095 --End by gnramasa for bug 8630852 13-July-09
2096
2097 PROCEDURE GetStrategyTempID(
2098 p_stry_cnt_rec in IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE,
2099 x_return_status out NOCOPY varchar2,
2100 x_strategy_template_id out NOCOPY number) IS
2101 /*
2102 CURSOR c_strategyTemp(pCategoryType varchar2, pDelinquencyID number) IS
2103 SELECT ST.strategy_temp_id, ST.strategy_rank, OBF.ENTITY_NAME
2104 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
2105 where ST.category_type = pCategoryType and ST.Check_List_YN = 'N' AND
2106 OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
2107 OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
2108 and not exists
2109 (select 'x' from iex_strategies SS where SS.delinquency_id = pDelinquencyID
2110 and SS.OBJECT_TYPE = pCategoryType)
2111 ORDER BY strategy_rank DESC;
2112 */
2113 C_DynSql varchar2(1000);
2114 v_Exists varchar2(20);
2115 v_SkipTemp varchar2(20);
2116
2117 l_StrategyTempID number := 0;
2118 TYPE c_strategyTempCurTyp IS REF CURSOR; -- weak
2119 c_strategyTemp c_strategyTempCurTyp; -- declare cursor variable
2120 c_rec_Strategy_temp_id NUMBER;
2121 c_Rec_Strategy_Rank varchar2(10);
2122 c_Rec_ENTITY_NAME varchar2(30);
2123 c_Rec_active_flag varchar2(1);
2124
2125 -- clchang updated for sql bind var 05/07/2003
2126 vstr1 varchar2(100) ;
2127 vstr2 varchar2(100) ;
2128 vstr3 varchar2(100) ;
2129 vstr4 varchar2(100) ;
2130 vstr5 varchar2(100) ;
2131 vstr6 varchar2(100) ;
2132
2133 /* ctlee - add status and pass it to GetTemplateId by stry_rec 7/3/2003 */
2134 chk_obj_type varchar2(30);
2135
2136
2137 BEGIN
2138
2139 -- initialize variables
2140 vstr1 := ' select 1 from ' ;
2141 vstr2 := ' where delinquency_id = :DelId ' ;
2142 vstr3 := ' and rownum < 2 ';
2143 vstr4 := ' where CUST_ACCOUNT_id = :AcctId ';
2144 vstr5 := ' where party_id = :PartyId ';
2145 vstr6 := ' where customer_site_use_id = :CustomerSiteUseId ';
2146
2147 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2148 write_log(FND_LOG.LEVEL_STATEMENT, 'GetStrategyTempID: Object_Type = '
2149 || p_stry_cnt_rec.object_type || ' Delinquency ID = ' || p_stry_cnt_rec.delinquency_id
2150 || ' Strategy Level ' || l_DefaultStrategyLevel || ' Score ' || p_stry_cnt_rec.score_value
2151 || ' Party ID ' || p_stry_cnt_rec.party_cust_id || ' Account ID ' || p_stry_cnt_rec.cust_account_id
2152 || ' CUSTOMER_SITE_USE ID ' || p_stry_cnt_rec.customer_site_use_id );
2153 end if;
2154
2155 x_Strategy_Template_id := l_DefaultTempID;
2156
2157
2158 /* ctlee - add status and pass it to GetTemplateId by stry_rec 7/3/2003 */
2159 /* C_StrategyTemp using chk_obj_type when strategy level = 30 */
2160 /* comment out to check if existing pre-delinquent strategy template for all 4 levels
2161 - filter when open_strategies() => always use the default one if not found
2162 03/05/2004 ctlee
2163 */
2164 chk_obj_type := p_stry_cnt_rec.object_type;
2165 if (p_stry_cnt_rec.object_type = 'DELINQUENT') then
2166 if (p_stry_cnt_rec.status = 'PREDELINQUENT') then
2167 chk_obj_type := p_stry_cnt_rec.status;
2168 end if;
2169 end if;
2170 /*
2171 if (p_stry_cnt_rec.object_type = 'DELINQUENT') then
2172 chk_obj_type := p_stry_cnt_rec.status;
2173 if (chk_obj_type = 'PREDELINQUENT') then
2174 x_strategy_template_id := -1;
2175 end if;
2176 end if;
2177 */
2178
2179 -- bug 4141678 begin - ctlee
2180 -- add checking on existing iex_strategy_work_temp_xref, at least one wi required
2181 -- bug 4141678 end - ctlee
2182
2183
2184 IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 THEN
2185 OPEN c_strategyTemp
2186 FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
2187 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF , iex_strategy_template_groups temgp
2188 where ST.Check_List_YN = l_No AND
2189 ((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> l_No) and
2190 st.strategy_level = l_DefaultStrategyLevel and
2191 OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
2192 OBF.OBJECT_FILTER_TYPE(+) = l_StratObjectFilterType
2193 AND st.strategy_temp_group_id = temgp.group_id -- added scoring engine filter
2194 AND DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',temgp.scoring_engine_id,'-1') =
2195 DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',p_stry_cnt_rec.score_id , '-1') -- for bug 13388975 pnaveenk
2196 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
2197 AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
2198 and exists (select 1 from IEX_STRATEGY_WORK_TEMP_XREF strx
2199 where strx.strategy_temp_id = st.strategy_temp_id)
2200 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
2201 and ST.STRATEGY_RANK <= p_stry_cnt_rec.SCORE_VALUE
2202 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
2203 -- Bug 7392752 by Ehuh
2204 and exists (select 1 from iex_strategy_template_groups tg
2205 where tg.group_id = st.strategy_temp_group_id
2206 and tg.enabled_flag <> 'N'
2207 and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
2208 and trunc(nvl(tg.valid_to_dt,sysdate)) )
2209 and st.category_type in ('DELINQUENT','PREDELINQUENT') -- added for bug#7709114 by PNAVEENK on 22-1-2009
2210 ORDER BY to_number(st.strategy_rank) DESC;
2211 ELSE
2212 OPEN c_strategyTemp
2213 FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
2214 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF , iex_strategy_template_groups temgp
2215 where ST.category_type = chk_obj_type and ST.Check_List_YN = l_No AND
2216 ((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> l_No) and
2217 st.strategy_level = l_DefaultStrategyLevel and
2218 OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
2219 OBF.OBJECT_FILTER_TYPE(+) = l_StratObjectFilterType
2220 AND st.strategy_temp_group_id = temgp.group_id -- added for bug 13388975
2221 AND DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',temgp.scoring_engine_id,'-1') =
2222 DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',p_stry_cnt_rec.score_id , '-1')
2223 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
2224 AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
2225 and exists (select 1 from IEX_STRATEGY_WORK_TEMP_XREF strx
2226 where strx.strategy_temp_id = st.strategy_temp_id)
2227 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
2228 and ST.STRATEGY_RANK <= p_stry_cnt_rec.SCORE_VALUE
2229 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
2230 -- Bug 7392752 by Ehuh
2231 and exists (select 1 from iex_strategy_template_groups tg
2232 where tg.group_id = st.strategy_temp_group_id
2233 and tg.enabled_flag <> 'N'
2234 and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
2235 and trunc(nvl(tg.valid_to_dt,sysdate)) )
2236 ORDER BY to_number(st.strategy_rank) DESC;
2237 END IF;
2238
2239
2240 /* Get the Strategy Template for requested Category Type */
2241 LOOP
2242 FETCH C_StrategyTemp INTO c_rec_Strategy_temp_id, c_Rec_Strategy_Rank, c_Rec_ENTITY_NAME, c_rec_active_flag ;
2243
2244 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2245 write_log(FND_LOG.LEVEL_STATEMENT,
2246 ' Get Strategy Template: Inside Cursor. Entity Name '
2247 || c_Rec_Entity_Name
2248 || ' Temp ' || c_rec_Strategy_temp_id
2249 || ' c_rec_active_flag ' || c_rec_active_flag
2250 || ' Rank ' || c_Rec_Strategy_Rank);
2251 end if;
2252
2253 if C_StrategyTemp%FOUND then
2254 v_SkipTemp := 'F';
2255 if c_Rec_Entity_Name is not null and c_rec_active_flag <> 'N' then
2256 BEGIN
2257 IF l_DefaultStrategyLevel = 40 THEN
2258 -- clchang updated for sql bind var 05/07/2003
2259 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
2260 vstr2 ||
2261 vstr3;
2262 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.delinquency_id;
2263 /*
2264 C_DynSql :=
2265 ' select 1 from ' || c_Rec_ENTITY_NAME ||
2266 ' where delinquency_id = ' || p_stry_cnt_rec.delinquency_id ||
2267 ' and rownum < 2 ';
2268 */
2269
2270 elsif l_DefaultStrategyLevel = 30 THEN
2271 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
2272 vstr6 ||
2273 vstr3;
2274 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.customer_site_use_id;
2275 elsif l_DefaultStrategyLevel = 20 THEN
2276 -- clchang updated for sql bind var 05/07/2003
2277 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
2278 vstr4 ||
2279 vstr3;
2280 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.cust_account_id;
2281 /*
2282 C_DynSql :=
2283 ' select 1 from ' || c_Rec_ENTITY_NAME ||
2284 ' where CUST_ACCOUNT_id = ' || p_stry_cnt_rec.CUST_ACCOUNT_id ||
2285 ' and rownum < 2 ';
2286 */
2287 else
2288 -- clchang updated for sql bind var 05/07/2003
2289 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
2290 vstr5 ||
2291 vstr3;
2292 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.party_cust_id;
2293 /*
2294 C_DynSql :=
2295 ' select 1 from ' || c_Rec_ENTITY_NAME ||
2296 ' where party_id = ' || p_stry_cnt_rec.PARTY_CUST_ID ||
2297 ' and rownum < 2 ';
2298 */
2299 end if;
2300
2301 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2302 write_log(FND_LOG.LEVEL_STATEMENT, ' Dynamic SQL in GetStrategyTemplate '
2303 || c_DynSql );
2304 end if;
2305
2306 --Execute Immediate c_DynSql into v_Exists;
2307
2308 EXCEPTION
2309 When no_data_found then
2310 -- fnd_file.put_line(FND_FILE.LOG, ' Get Strategy Template: When No Data Found: ' || c_DynSql );
2311 write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: When No Data Found: ' || c_DynSql ); -- changed for bug 9039794
2312 v_SkipTemp := 'T';
2313 When Others then
2314 -- fnd_file.put_line(FND_FILE.LOG, ' Get Strategy Template: When Others: ' || c_DynSql );
2315 write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: When Others: ' || c_DynSql ); -- changed for bug 9039794
2316 v_SkipTemp := 'T';
2317 END;
2318 end if;
2319
2320 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2321 write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: ' || c_Rec_Strategy_Temp_id ||
2322 ' Skip Flag ' || v_SkipTemp );
2323 end if;
2324
2325 if v_SkipTemp <> 'T' then
2326
2327 if p_stry_cnt_rec.score_value >= C_Rec_Strategy_Rank then
2328 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2329 write_log(FND_LOG.LEVEL_STATEMENT, ' Found Template: ' || c_Rec_Strategy_Temp_id );
2330 end if;
2331 x_strategy_template_id := c_rec_Strategy_temp_id;
2332 return;
2333 end if;
2334 end if;
2335 ELSE -- fetch failed, so exit loop
2336 EXIT;
2337 end if;
2338 end loop;
2339 close C_StrategyTemp;
2340
2341 EXCEPTION
2342 when others then
2343 close C_StrategyTemp;
2344
2345 END;
2346
2347
2348 -- apply to one default strategy only because the SA would set to one level only
2349 -- thus it will apply to the same level of the default strategy template too
2350 FUNCTION GetDefaultStrategyTempID return NUMBER IS
2351 l_StrategyTempID number;
2352 lCursorStrategyTempID number;
2353 Cursor C_getFirstTempID IS
2354 Select st.Strategy_Temp_ID FROM IEX_STRATEGY_TEMPLATES_B st where
2355 st.Check_List_YN = l_No AND st.ENABLED_FLAG <> l_No
2356 -- Bug 7392752 by Ehuh
2357 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE)) AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
2358 and exists (select 1 from iex_strategy_template_groups tg
2359 where tg.group_id = st.strategy_temp_group_id
2360 and tg.enabled_flag <> 'N'
2361 and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
2362 and trunc(nvl(tg.valid_to_dt,sysdate)) );
2363 BEGIN
2364 l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
2365 if (l_StrategyTempID = 0) Then
2366 Open C_getFirstTempID;
2367 fetch C_getFirstTempID into lCursorStrategyTempID;
2368 if C_getFirstTempID%FOUND then
2369 l_StrategyTempID := lCursorStrategyTempID;
2370 end if;
2371 Close C_getFirstTempID;
2372 end if;
2373 return l_StrategyTempID;
2374 END;
2375
2376
2377 -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
2378 --PROCEDURE MAIN (
2379 -- ERRBUF OUT NOCOPY VARCHAR2,
2380 -- RETCODE OUT NOCOPY VARCHAR2,
2381 -- p_trace_mode IN VARCHAR2)
2382 PROCEDURE MAIN
2383 (
2384 ERRBUF OUT NOCOPY VARCHAR2,
2385 RETCODE OUT NOCOPY VARCHAR2,
2386 -- p_trace_mode IN VARCHAR2, Bug5022607. Fix By LKKUMAR. Removed this parameter.
2387 p_org_id IN number,
2388 p_ignore_switch IN VARCHAR2,
2389 p_strategy_mode IN VARCHAR2 DEFAULT 'FINAL', -- added by gnramasa for bug 8630852 13-July-09
2390 p_show_output IN VARCHAR2 DEFAULT 'N', -- added for bug 13377466 pnaveenk
2391 p_coll_bus_level_dummy IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
2392 p_customer_name_low IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
2393 p_customer_name_high IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
2394 p_account_number_low IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
2395 p_account_number_high IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
2396 p_billto_location_dummy IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
2397 p_billto_location_low IN VARCHAR2, -- added by gnramasa for bug 8630852 13-July-09
2398 p_billto_location_high IN VARCHAR2 -- added by gnramasa for bug 8630852 13-July-09
2399 )
2400 -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
2401 --Bug# 6870773 Naveen
2402 IS
2403
2404 l_count number;
2405 l_api_name VARCHAR2(100) ;
2406 -- Start added by snuthala for bug 10221334 on 21-10-2010
2407 --added by snuthala
2408 l_collection_method VARCHAR2(30) ;
2409 l_org_id_coll_method VARCHAR2(30) ;
2410 --:= 'STRATEGIES';
2411 l_con_update_re_st boolean;
2412 l_c_org_count number := 0;
2413 -- end added by snuthala for bug 10221334 on 21-10-2010
2414 CURSOR c_org IS
2415 SELECT organization_id from hr_operating_units where
2416 mo_global.check_access(organization_id) = 'Y'
2417 AND organization_id = nvl(P_ORG_ID,organization_id);
2418
2419 -- Start for bug 8708271 multi level strategy
2420
2421 CURSOR c_str_levels IS
2422 SELECT lookup_code FROM IEX_LOOKUPS_V
2423 WHERE LOOKUP_TYPE='IEX_RUNNING_LEVEL'
2424 AND iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
2425
2426 CURSOR c_system_str_level(p_org_id number) IS
2427 select preference_value
2428 from iex_app_preferences_b
2429 where preference_name='COLLECTIONS STRATEGY LEVEL'
2430 and enabled_flag='Y'
2431 and (org_id = p_org_id or org_id is null)
2432 order by nvl(org_id,0) desc;
2433
2434 CURSOR c_system_strategy_level IS
2435 select preference_value
2436 from iex_app_preferences_b
2437 where preference_name='COLLECTIONS STRATEGY LEVEL'
2438 and enabled_flag='Y'
2439 and org_id is null;
2440
2441 -- End for bug 8708271 multi level strategy
2442
2443 -- Start for
2444 cursor c_collections_method is
2445 select collections_methods
2446 from iex_questionnaire_items;
2447
2448 cursor c_org_id_coll_method(p_org_id number) is
2449 select nvl(collections_method,'STRATEGIES')
2450 from IEX_app_preferences_b where
2451 org_id = p_org_id and enabled_flag ='Y';
2452
2453 CURSOR c_check_ou_methods (p_org_id number) IS
2454 SELECT COUNT(1)
2455 from hr_operating_units a, IEX_app_preferences_b b
2456 where mo_global.check_access(a.organization_id) = 'Y'
2457 and a.organization_id = b.org_id
2458 and b.collections_method = 'STRATEGIES'
2459 and b.enabled_flag ='Y'
2460 AND organization_id = nvl(p_org_id,organization_id);
2461
2462 l_ou_methods NUMBER := 0;
2463
2464 BEGIN
2465
2466
2467
2468 --Start adding for bug 8630852 by gnramasa 13-July-09
2469 l_api_name := 'Main ';
2470 FND_FILE.PUT_LINE(FND_FILE.LOG, 'STRATEGYMODE : ' || p_strategy_mode);
2471 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SHOW OUTPUT : '|| p_show_output);
2472 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CUSTOMER NAME LOW : ' || p_customer_name_low);
2473 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CUSTOMER NAME HIGH : ' || p_customer_name_high);
2474 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ACCOUNT NUMBER LOW : ' || p_account_number_low);
2475 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ACCOUNT NUMBER HIGH : ' || p_account_number_high);
2476 FND_FILE.PUT_LINE(FND_FILE.LOG, 'BILLTO LOCATION LOW : ' || p_billto_location_low);
2477 FND_FILE.PUT_LINE(FND_FILE.LOG, 'BILLTO LOCATION HIGH : ' || p_billto_location_high);
2478
2479 writelog(G_PKG_NAME || ' ' || l_api_name || ' - strategy mode :' || p_strategy_mode);
2480 writelog(G_PKG_NAME || ' ' || l_api_name || ' - p_show_output :'|| p_show_output);
2481 writelog(G_PKG_NAME || ' ' || l_api_name || ' - p_customer_name_low :' || p_customer_name_low);
2482 writelog(G_PKG_NAME || ' ' || l_api_name || ' - p_customer_name_high :' || p_customer_name_high);
2483 writelog(G_PKG_NAME || ' ' || l_api_name || ' - p_account_number_low :' || p_account_number_low);
2484 writelog(G_PKG_NAME || ' ' || l_api_name || ' - p_account_number_high :' || p_account_number_high);
2485 writelog(G_PKG_NAME || ' ' || l_api_name || ' - p_billto_location_low :' || p_billto_location_low);
2486 writelog(G_PKG_NAME || ' ' || l_api_name || ' - p_billto_location_high :' || p_billto_location_high);
2487
2488 /*
2489 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2490 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2491 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2492 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
2493 custom_where_clause
2494 (p_customer_name_low => p_customer_name_low,
2495 p_customer_name_high => p_customer_name_high,
2496 p_account_number_low => p_account_number_low,
2497 p_account_number_high => p_account_number_high,
2498 p_billto_location_low => p_billto_location_low,
2499 p_billto_location_high => p_billto_location_high);
2500
2501 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
2502 end if;
2503 */
2504
2505 --End adding for bug 8630852 by gnramasa 13-July-09
2506
2507 --Bug5022607. Fix By LKKUMAR. Remove p_trace_mode parameter. Start.
2508 -- IF p_trace_mode = 'Y' THEN
2509 fnd_file.put_line(FND_FILE.LOG,'Value of profile IEX: Debug Level is : '|| PG_DEBUG);
2510 IF PG_DEBUG = 1 THEN
2511 fnd_file.put_line(FND_FILE.LOG, ' Enabling the trace');
2512 dbms_session.set_sql_trace(TRUE);
2513 ELSE
2514 fnd_file.put_line(FND_FILE.LOG,' Trace not enabled');
2515 dbms_session.set_sql_trace(FALSE);
2516 END IF;
2517 --Bug# 6870773 Naveen
2518 l_org_enabled := nvl(fnd_profile.value('IEX_PROC_STR_ORG'),'N');
2519 -- l_org_id := fnd_profile.value('ORG_ID');
2520 l_org_id:=p_org_id;
2521
2522 --call gen_xml_header_data_strategy to generate the xml header data
2523 IF p_show_output ='Y' THEN
2524 gen_xml_header_data_strategy (p_strategy_mode => p_strategy_mode);
2525 END IF;
2526 fnd_file.put_line(FND_FILE.LOG, 'Update Multi Level Strategy Setup in Questionnaire table');
2527 writelog(' Update Multi Level Strategy Setup in Questionnaire table');
2528 IEX_CHECKLIST_UTILITY.UPDATE_MLSETUP;
2529 writelog(' End update Multi Level Setup in Questionnaire table');
2530 OPEN c_collections_method;
2531 FETCH c_collections_method INTO l_collection_method;
2532 close c_collections_method;
2533 -- below condition added by snuthala for bug 10221334 on 12/2/2010
2534 if l_collection_method <> 'DUNNING' then
2535 if l_org_enabled = 'Y' then
2536 fnd_file.put_line(FND_FILE.LOG, 'Profile for processing strategies by operating unit is On ' || ' Org Id = ' || l_org_id);
2537
2538 select count(1)
2539 into l_count
2540 from iex_strategies
2541 where org_id is null
2542 and strategy_level=l_DefaultStrategyLevel;
2543
2544 if l_count>0 then
2545 fnd_file.put_line(FND_FILE.LOG, 'Found '||l_count||' strategies without having org_id.');
2546 fnd_file.put_line(FND_FILE.LOG, 'Please run the script iexstorg.sql before running this concurrent program.');
2547 return;
2548 end if;
2549
2550 -- start for bug 11844672 pnaveenk
2551 -- check whether atleast one OU registered if collections method is Dunnning and strategies
2552
2553 OPEN c_check_ou_methods(p_org_id);
2554 FETCH c_check_ou_methods INTO l_ou_methods;
2555 CLOSE c_check_ou_methods;
2556
2557 IF l_ou_methods = 0 AND l_collection_method = 'DUN_STR' THEN
2558 fnd_file.put_line(FND_FILE.LOG,' Collections method is set up as Dunning and Strategies');
2559 IF p_org_id IS NULL THEN
2560 fnd_file.put_line(FND_FILE.LOG,' But none of the Operating Units got registered with business level ');
2561 ELSE
2562 fnd_file.put_line(FND_FILE.LOG,' Operating Unit '|| p_org_id|| ' is not registered with business level');
2563 END IF;
2564 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
2565 message => 'Atleast one OU must be registered with business level to run the program ');
2566 return;
2567 END IF;
2568 -- end for bug 11844672
2569 fnd_file.put_line(FND_FILE.LOG, ' Party Level Strategy Override value ' || l_party_override );
2570 fnd_file.put_line(FND_FILE.LOG, ' Operating Unit Level Strategy Override value ' || l_org_override);
2571 MO_GLOBAL.INIT('IEX');
2572 IF P_ORG_ID IS NOT NULL THEN
2573 MO_GLOBAL.SET_POLICY_CONTEXT('S',P_ORG_ID); -- Single Org.
2574 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MO: Operating Unit=' || p_org_id);
2575 ELSE
2576 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL); -- Multi Org.
2577 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MO: Operating Unit=' || 'All');
2578 END IF;
2579 FOR I_ORG IN C_ORG LOOP -- Moac Changes. Loop through for Party.
2580 MO_GLOBAL.SET_POLICY_CONTEXT('S',I_ORG.organization_id); -- Moac Changes. Set Org.
2581 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside Party Loop, Operating Unit Set =' ||I_ORG.organization_id);
2582 l_org_id:=mo_global.get_current_org_id;
2583 -- Start for bug 8708271 multi level strategy
2584 l_c_org_count := l_c_org_count + 1; -- added by snuthala for bug 10221334 on 21-10-2010
2585 if l_org_override ='Y' then
2586 fnd_file.put_line(FND_FILE.LOG, 'Operating Unit Level override is on');
2587 -- Start added by snuthala for bug 10221334 on 21-10-2010
2588 FND_FILE.PUT_LINE(FND_FILE.LOG, 'org_id : '|| l_org_id);
2589 OPEN c_collections_method;
2590 FETCH c_collections_method INTO l_collection_method;
2591 close c_collections_method;
2592 -- select collections_methods into from iex_questionnaire_items;
2593 FND_FILE.PUT_LINE(FND_FILE.LOG, 'collections_methods : '|| l_collection_method);
2594 OPEN C_org_id_coll_method (l_org_id);
2595 FETCH C_org_id_coll_method INTO l_org_id_coll_method;
2596 if l_collection_method = 'DUN_STR' and C_org_id_coll_method%NOTFOUND THEN
2597 l_unregistered_org_ids := l_unregistered_org_ids +1;
2598 fnd_file.put_line(FND_FILE.LOG, 'Operating Unit is ' || l_org_id || ' is not registered');
2599 END IF;
2600 close C_org_id_coll_method;
2601 -- select nvl(collections_method,'STRATEGIES') into l_org_id_coll_method from IEX_app_preferences_b where org_id = l_org_id and enabled_flag ='Y';
2602 FND_FILE.PUT_LINE(FND_FILE.LOG, 'collections_methods set up for Org id : '|| l_org_id_coll_method);
2603
2604 if l_org_id_coll_method is not null and l_collection_method = 'DUN_STR' and l_org_id_coll_method <> 'STRATEGIES' then
2605 fnd_file.put_line(FND_FILE.LOG, 'Operating Unit is ' || l_org_id || ' setup for ' || l_org_id_coll_method);
2606 fnd_file.put_line(FND_FILE.LOG, 'Not running Strategy Management for this ORG_ID' || l_org_id);
2607 l_unPocessed_orgids := l_unPocessed_orgids + 1;
2608 fnd_file.put_line(FND_FILE.LOG, ' l_unPocessed_orgids : ' || l_unPocessed_orgids);
2609
2610 else
2611 -- End added by snuthala for bug 10221334 on 21-10-2010
2612
2613 open c_system_str_level(l_org_id);
2614 fetch c_system_str_level into l_system_strategy_level;
2615 close c_system_str_level;
2616
2617 fnd_file.put_line(FND_FILE.LOG, 'Strategy Level set for Operating Unit' || l_org_id || 'is' || l_system_strategy_level);
2618
2619 for l_str_levels in c_str_levels loop
2620
2621 l_StrategyLevelName := l_str_levels.lookup_code;
2622
2623 select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
2624 into l_DefaultStrategyLevel from dual;
2625
2626 write_log(FND_LOG.LEVEL_STATEMENT, ' Org_id ' || l_org_id || ' Itearation Level ' || l_DefaultStrategyLevel);
2627
2628 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2629 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2630 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2631 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
2632 custom_where_clause
2633 (p_customer_name_low => p_customer_name_low,
2634 p_customer_name_high => p_customer_name_high,
2635 p_account_number_low => p_account_number_low,
2636 p_account_number_high => p_account_number_high,
2637 p_billto_location_low => p_billto_location_low,
2638 p_billto_location_high => p_billto_location_high,
2639 p_strategy_level => l_DefaultStrategyLevel);
2640
2641 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
2642 end if;
2643
2644 CLOSE_STRATEGIES(errbuf => errbuf,
2645 retcode => retcode,
2646 p_strategy_mode => p_strategy_mode,
2647 p_show_output => p_show_output);
2648
2649
2650 PROCESS_ONHOLD_STRATEGIES(p_strategy_mode => p_strategy_mode, p_show_output => p_show_output);
2651
2652 OPEN_STRATEGIES(errbuf => errbuf,
2653 retcode => retcode,
2654 p_ignore_switch => p_ignore_switch,
2655 p_strategy_mode => p_strategy_mode,
2656 p_show_output => p_show_output);
2657
2658 end loop;
2659 end if; -- added by snuthala for bug 10221334 on 21-10-2010
2660 else
2661 if l_party_override = 'Y' then
2662
2663 open c_system_strategy_level;
2664 fetch c_system_strategy_level into l_system_strategy_level;
2665 close c_system_strategy_level;
2666
2667 fnd_file.put_line(FND_FILE.LOG, 'Operating Unit Level override is off and Party Level override is on');
2668 fnd_file.put_line(FND_FILE.LOG, 'System Strategy Level ' || l_system_strategy_level);
2669
2670 for l_str_levels in c_str_levels loop
2671
2672 l_StrategyLevelName := l_str_levels.lookup_code;
2673
2674 select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
2675 into l_DefaultStrategyLevel from dual;
2676
2677 write_log(FND_LOG.LEVEL_STATEMENT, ' Org_id ' || l_org_id || ' Itearation Level ' || l_DefaultStrategyLevel);
2678
2679 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2680 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2681 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2682 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
2683 custom_where_clause
2684 (p_customer_name_low => p_customer_name_low,
2685 p_customer_name_high => p_customer_name_high,
2686 p_account_number_low => p_account_number_low,
2687 p_account_number_high => p_account_number_high,
2688 p_billto_location_low => p_billto_location_low,
2689 p_billto_location_high => p_billto_location_high,
2690 p_strategy_level => l_DefaultStrategyLevel);
2691
2692 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
2693 end if;
2694
2695 CLOSE_STRATEGIES(errbuf => errbuf,
2696 retcode => retcode,
2697 p_strategy_mode => p_strategy_mode,
2698 p_show_output => p_show_output);
2699
2700
2701 PROCESS_ONHOLD_STRATEGIES(p_strategy_mode => p_strategy_mode,p_show_output => p_show_output);
2702
2703 OPEN_STRATEGIES(errbuf => errbuf,
2704 retcode => retcode,
2705 p_ignore_switch => p_ignore_switch,
2706 p_strategy_mode => p_strategy_mode,
2707 p_show_output => p_show_output);
2708
2709 end loop;
2710
2711 else
2712
2713 fnd_file.put_line(FND_FILE.LOG, 'Operating Unit Level override and Party Level override are off');
2714
2715 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
2716 into l_DefaultStrategyLevel,l_StrategyLevelName
2717 from iex_app_preferences_b
2718 where preference_name='COLLECTIONS STRATEGY LEVEL'
2719 and enabled_flag='Y'
2720 and org_id is null;
2721
2722 l_system_strategy_level := l_StrategyLevelName;
2723
2724 write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2725 write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level ' || l_system_strategy_level);
2726
2727 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2728 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2729 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2730 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
2731 custom_where_clause
2732 (p_customer_name_low => p_customer_name_low,
2733 p_customer_name_high => p_customer_name_high,
2734 p_account_number_low => p_account_number_low,
2735 p_account_number_high => p_account_number_high,
2736 p_billto_location_low => p_billto_location_low,
2737 p_billto_location_high => p_billto_location_high,
2738 p_strategy_level => l_DefaultStrategyLevel);
2739
2740 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
2741 end if;
2742
2743 CLOSE_STRATEGIES(errbuf => errbuf,
2744 retcode => retcode,
2745 p_strategy_mode => p_strategy_mode,
2746 p_show_output => p_show_output);
2747
2748
2749 PROCESS_ONHOLD_STRATEGIES (p_strategy_mode => p_strategy_mode, p_show_output => p_show_output);
2750
2751 OPEN_STRATEGIES(errbuf => errbuf,
2752 retcode => retcode,
2753 p_ignore_switch => p_ignore_switch,
2754 p_strategy_mode => p_strategy_mode,
2755 p_show_output => p_show_output);
2756 end if; -- party override
2757 end if; -- org override
2758
2759
2760
2761 END LOOP;
2762
2763 else
2764 fnd_file.put_line(FND_FILE.LOG, 'Profile for processing strategies by operating unit is Off ');
2765 fnd_file.put_line(FND_FILE.LOG, ' Party Level Strategy Override value ' || l_party_override );
2766 fnd_file.put_line(FND_FILE.LOG, ' Operating Unit Level Strategy Override value ' || l_org_override);
2767
2768 select count(1)
2769 into l_count
2770 from iex_strategies
2771 where org_id is not null
2772 and strategy_level=l_DefaultStrategyLevel;
2773
2774 if l_count>0 then
2775 fnd_file.put_line(FND_FILE.LOG, 'Found '||l_count||' strategies with org_id.');
2776 fnd_file.put_line(FND_FILE.LOG, 'Please run the script iexstorg.sql before running this concurrent program.');
2777 return;
2778 end if;
2779 l_org_id := null;
2780
2781 if l_org_override = 'Y' then
2782 fnd_file.put_line(FND_FILE.LOG, ' Operating unit override is set. So enable profile for processing strategies by opearating');
2783 return;
2784 end if;
2785
2786 if l_party_override ='Y' then
2787
2788 fnd_file.put_line(FND_FILE.LOG, 'Party Level override is on');
2789
2790 open c_system_strategy_level;
2791 fetch c_system_strategy_level into l_system_strategy_level;
2792 close c_system_strategy_level;
2793
2794 write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level' || l_system_strategy_level);
2795 for l_str_levels in c_str_levels loop
2796
2797 l_StrategyLevelName := l_str_levels.lookup_code;
2798
2799 select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
2800 into l_DefaultStrategyLevel from dual;
2801
2802 write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2803 write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level ' || l_system_strategy_level);
2804
2805 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2806 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2807 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2808 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
2809 custom_where_clause
2810 (p_customer_name_low => p_customer_name_low,
2811 p_customer_name_high => p_customer_name_high,
2812 p_account_number_low => p_account_number_low,
2813 p_account_number_high => p_account_number_high,
2814 p_billto_location_low => p_billto_location_low,
2815 p_billto_location_high => p_billto_location_high,
2816 p_strategy_level => l_DefaultStrategyLevel);
2817
2818 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
2819 end if;
2820
2821 CLOSE_STRATEGIES(errbuf => errbuf,
2822 retcode => retcode,
2823 p_strategy_mode => p_strategy_mode,
2824 p_show_output => p_show_output);
2825
2826 PROCESS_ONHOLD_STRATEGIES (p_strategy_mode => p_strategy_mode,p_show_output => p_show_output);
2827
2828 OPEN_STRATEGIES( errbuf => errbuf,
2829 retcode => retcode,
2830 p_ignore_switch => p_ignore_switch,
2831 p_strategy_mode => p_strategy_mode,
2832 p_show_output => p_show_output);
2833
2834 end loop;
2835 else
2836 fnd_file.put_line(FND_FILE.LOG, 'Party Level override is off');
2837
2838 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
2839 into l_DefaultStrategyLevel,l_StrategyLevelName
2840 from iex_app_preferences_b
2841 where preference_name='COLLECTIONS STRATEGY LEVEL'
2842 and enabled_flag='Y'
2843 and org_id is null;
2844
2845 l_system_strategy_level := l_StrategyLevelName;
2846
2847 write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2848
2849 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2850 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2851 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2852 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
2853 custom_where_clause
2854 (p_customer_name_low => p_customer_name_low,
2855 p_customer_name_high => p_customer_name_high,
2856 p_account_number_low => p_account_number_low,
2857 p_account_number_high => p_account_number_high,
2858 p_billto_location_low => p_billto_location_low,
2859 p_billto_location_high => p_billto_location_high,
2860 p_strategy_level => l_DefaultStrategyLevel);
2861
2862 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
2863 end if;
2864
2865 CLOSE_STRATEGIES(errbuf => errbuf,
2866 retcode => retcode,
2867 p_strategy_mode => p_strategy_mode,
2868 p_show_output => p_show_output);
2869
2870 --Begin Bug#7248296 28-07-2008 barathsr
2871 PROCESS_ONHOLD_STRATEGIES (p_strategy_mode => p_strategy_mode, p_show_output => p_show_output);
2872
2873 OPEN_STRATEGIES(errbuf => errbuf,
2874 retcode => retcode,
2875 p_ignore_switch => p_ignore_switch,
2876 p_strategy_mode => p_strategy_mode,
2877 p_show_output => p_show_output);
2878
2879 end if; -- party override
2880
2881
2882
2883 end if; -- org enabled
2884 else
2885 fnd_file.put_line(FND_FILE.LOG, 'Iex Strategy Management concurrent program failed to run as the collections method is set up for dunning.');
2886 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2887 message => 'Iex Strategy Management concurrent program failed to run as the collections method is set up for dunning.');
2888 end if; -- Added by snuthala for 10221334 12/2/2010
2889 -- end Naveen
2890
2891 --Call gen_xml_append_closetag_sty to append the close tag and write the xml data to cp o/p
2892 -- Start adding by gnramasa for bug 8833868 3-Sep-09
2893 IF p_show_output = 'Y' THEN
2894 gen_xml_append_closetag_sty (p_customer_name_low => p_customer_name_low,
2895 p_customer_name_high => p_customer_name_high,
2896 p_account_number_low => p_account_number_low,
2897 p_account_number_high => p_account_number_high,
2898 p_billto_location_low => p_billto_location_low,
2899 p_billto_location_high => p_billto_location_high,
2900 p_org_id => p_org_id,-- added by snuthala for bug 10221334 on 21-10-2010
2901 p_unprocessed_orgid_count => l_unPocessed_orgids, -- added by snuthala for bug 10221334 on 21-10-2010
2902 p_unregistered_org_ids => l_unregistered_org_ids); -- added by snuthala for bug 10221334 on 21-10-2010
2903 END IF;
2904 -- End adding by gnramasa for bug 8833868 3-Sep-09
2905 -- Start added by snuthala for bug 10221334 on 21-10-2010
2906 fnd_file.put_line(FND_FILE.LOG, 'After gen_xml_append_closetag_sty ');
2907 fnd_file.put_line(FND_FILE.LOG, ' l_unPocessed_orgids : '||l_unPocessed_orgids);
2908 fnd_file.put_line(FND_FILE.LOG, ' Number of Rows returned bu c_org cursor : '||l_c_org_count);
2909 if l_unPocessed_orgids > 0 and l_collection_method <> 'DUNNING' then
2910
2911 if l_c_org_count = l_unPocessed_orgids then
2912 if p_org_id is not null then
2913 fnd_file.put_line(FND_FILE.LOG, ' Opearting Unit passed is Not Setup for Startegy please check Setup');
2914 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2915 message => 'Opearting Unit passed is Not Setup for Startegy please check Setup');
2916 else
2917 fnd_file.put_line(FND_FILE.LOG, 'No Opearting Unit is Setup for Startegy please check Setup');
2918 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2919 message => 'No Opearting Unit is Setup for Startegy please check Setup');
2920 end if;
2921 else
2922 fnd_file.put_line(FND_FILE.LOG, 'At least one Opearting Unit is Setup for Dunning please check Setup');
2923 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2924 message => 'At least one Opearting Unit is Setup for Dunning please check Setup');
2925 end if;
2926 end if;
2927
2928
2929
2930 EXCEPTION
2931
2932 WHEN OTHERS THEN
2933 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
2934 writelog('In Main Procedure, err: '||sqlerrm);
2935 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
2936 message => 'Exception occured while running the Concurrent Program : '||sqlerrm); -- added by snuthala for bug 10221334 on 21-10-2010
2937
2938 END;
2939
2940 -- Start for bug 8708271 multi level strategy
2941 PROCEDURE cancel_strategy( p_party_id number, p_str_level varchar2, p_str_mode varchar2,
2942 p_cust_acc_id number, p_site_use_id number, p_del_id number, p_show_output VARCHAR2 ) is
2943
2944 --Start for bug 9742245 gnramasa 7th June 10
2945 cursor c_str_ids (c_party_id number , c_str_level varchar2) is
2946 select strategy_id
2947 from iex_strategies
2948 where jtf_object_type in ('PARTY','IEX_ACCOUNT','IEX_BILLTO','IEX_DELINQUENCY')
2949 and party_id = c_party_id
2950 and strategy_level <> c_str_level
2951 and status_code in ('OPEN' , 'ONHOLD');
2952 --End for bug 9742245 gnramasa 7th June 10
2953
2954 l_itemtype varchar2(30);
2955 l_itemkey varchar2(50);
2956 l_party_id number;
2957 l_cust_account_id number;
2958 l_site_use_id number;
2959 l_del_id number;
2960 BEGIN
2961 --Start adding for bug 8761053 gnramasa 18th Aug 09
2962 for l_str_id in c_str_ids(p_party_id, p_str_level) loop
2963
2964
2965 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2966 write_log(FND_LOG.LEVEL_STATEMENT, 'In procedure cancel_strategy');
2967 write_log(FND_LOG.LEVEL_STATEMENT, 'Strategy ID ' || l_str_id.strategy_id );
2968 end if;
2969
2970 l_itemtype := 'IEXSTRY';
2971 l_itemkey := to_char(l_str_id.strategy_id);
2972 fnd_file.put_line(FND_FILE.LOG, ' Found other level strategy exists for party ' || p_party_id || ' Strategy id is ' || l_str_id.strategy_id);
2973
2974 if p_str_level = 10 then
2975 l_party_id := p_party_id;
2976 elsif p_str_level = 20 then
2977 l_cust_account_id := p_cust_acc_id;
2978 elsif p_str_level = 30 then
2979 l_site_use_id := p_site_use_id;
2980 elsif p_str_level = 40 then
2981 l_del_id := p_del_id;
2982 end if;
2983 IF p_show_output = 'Y' THEN
2984 gen_xml_body_strategy (p_strategy_id => l_str_id.strategy_id,
2985 p_strategy_status => 'CANCEL',
2986 p_default_sty_level => p_str_level,
2987 p_party_id => l_party_id,
2988 p_cust_acc_id => l_cust_account_id,
2989 p_site_use_id => l_site_use_id,
2990 p_del_id => l_del_id);
2991 END IF;
2992 if p_str_mode = 'FINAL' then
2993 BEGIN
2994 IEX_STRATEGY_WF.Send_Signal(
2995 process => l_itemtype,
2996 strategy_id => l_itemkey,
2997 status => l_StratStatusCancelled
2998 );
2999 fnd_file.put_line(FND_FILE.LOG, 'Strategy Cancelled. id = '|| l_str_id.strategy_id);
3000 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3001 write_log(FND_LOG.LEVEL_STATEMENT, 'Strategy Cancelled. id = ' || l_str_id.strategy_id);
3002 end if;
3003
3004 EXCEPTION
3005 WHEN OTHERS THEN
3006 fnd_file.put_line(FND_FILE.LOG, 'Strategy Cancelled Raised Exception = '
3007 || l_str_id.strategy_id || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
3008 update iex_strategies set status_code='CANCELLED' where strategy_id = l_str_id.strategy_id;
3009 commit;
3010 END;
3011 end if;
3012
3013 end loop;
3014
3015 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3016 write_log(FND_LOG.LEVEL_STATEMENT, 'Cancelled strategies for Party ID ' || p_party_id );
3017 write_log(FND_LOG.LEVEL_STATEMENT, 'End procedure cancel_strategy');
3018 end if;
3019
3020 END;
3021 --End adding for bug 8761053 gnramasa 18th Aug 09
3022 -- end for bug 8708271 multi level strategy
3023
3024 PROCEDURE write_log(mesg_level IN NUMBER, mesg IN VARCHAR2) is
3025 l_schema varchar2(10);
3026 l_dot varchar2(10);
3027 l_module varchar2(10);
3028 BEGIN
3029 l_schema := 'iex';
3030 l_dot := '.';
3031 l_module := 'strategy';
3032 if (mesg_level >= l_msgLevel) then
3033 fnd_file.put_line(FND_FILE.LOG, mesg);
3034 FND_LOG.STRING(mesg_level, l_schema || l_dot || l_module , mesg);
3035 end if;
3036 END;
3037 --Bug# 6870773 Naveen
3038 PROCEDURE update_strat_org
3039 (
3040 ERRBUF OUT NOCOPY VARCHAR2,
3041 RETCODE OUT NOCOPY VARCHAR2
3042 ) IS
3043 cursor c_bill_strat_wo_ou(p_org_id number)
3044 is select st.strategy_id,su.org_id
3045 from iex_strategies st,
3046 hz_cust_site_uses_all su
3047 where st.object_type='BILL_TO'
3048 and st.org_id is null
3049 and st.object_id=su.site_use_id
3050 and su.org_id = p_org_id;
3051
3052 cursor c_cust_strat_wo_ou(p_org_id number)
3053 is select st.strategy_id,p_org_id
3054 from iex_strategies st,
3055 hz_parties hp
3056 where st.object_type='PARTY'
3057 and st.org_id is null
3058 and st.object_id=hp.party_id
3059 and not exists(select 1 from
3060 hz_cust_accounts ca,
3061 hz_cust_acct_sites_all cas,
3062 hz_cust_site_uses_all su
3063 where hp.party_id = ca.party_id
3064 and ca.cust_account_id=cas.cust_account_id
3065 and cas.cust_acct_site_id=su.cust_acct_site_id
3066 and su.org_id <> p_org_id)
3067 group by st.strategy_id,p_org_id;
3068
3069 cursor c_account_strat_wo_ou(p_org_id number)
3070 is select st.strategy_id,p_org_id
3071 from iex_strategies st,
3072 hz_cust_accounts ca
3073 where st.object_type='ACCOUNT'
3074 and st.org_id is null
3075 and st.object_id=ca.cust_account_id
3076 and not exists(select 1 from
3077 hz_cust_acct_sites_all cas,
3078 hz_cust_site_uses_all su
3079 where ca.cust_account_id=cas.cust_account_id
3080 and cas.cust_acct_site_id=su.cust_acct_site_id
3081 and su.org_id <> p_org_id);
3082
3083
3084 cursor c_del_strat_wo_ou(p_org_id number)
3085 is select st.strategy_id,del.org_id
3086 from iex_strategies st,
3087 iex_delinquencies_all del
3088 where st.object_type='DELINQUENT'
3089 and st.org_id is null
3090 and st.object_id=del.delinquency_id
3091 and del.org_id = p_org_id;
3092
3093 cursor c_strat_with_ou(p_object_type varchar2)
3094 is select st.strategy_id,null
3095 from iex_strategies st
3096 where st.object_type=p_object_type
3097 and st.org_id is not null;
3098 --and st.org_id = p_org_id;
3099
3100 TYPE strat_list IS TABLE OF IEX_STRATEGIES.STRATEGY_ID%TYPE;
3101 TYPE org_list IS TABLE OF IEX_STRATEGIES.ORG_ID%TYPE;
3102
3103 strategies strat_list;
3104 orgs org_list;
3105
3106
3107
3108 BEGIN
3109
3110 if l_DefaultStrategyLevel = 10 THEN
3111
3112 IF l_org_enabled = 'Y' THEN
3113 OPEN c_cust_strat_wo_ou(l_org_id);
3114 FETCH c_cust_strat_wo_ou BULK COLLECT INTO strategies,orgs;
3115 CLOSE c_cust_strat_wo_ou;
3116 /*for rec1 in c_cust_strat_wo_ou(l_org_id) loop
3117 update iex_strategies
3118 set org_id=rec1.org_id
3119 where strategy_id = rec1.strategy_id;
3120 end loop;*/
3121 ELSE
3122 OPEN c_strat_with_ou('PARTY');
3123 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
3124 CLOSE c_strat_with_ou;
3125 /*for rec1 in c_cust_strat_with_ou(l_org_id) loop
3126 update iex_strategies
3127 set org_id=null
3128 where strategy_id = rec1.strategy_id;
3129 end loop;*/
3130 END IF;
3131 elsif l_DefaultStrategyLevel = 20 THEN
3132
3133 IF l_org_enabled = 'Y' THEN
3134 OPEN c_account_strat_wo_ou(l_org_id);
3135 FETCH c_account_strat_wo_ou BULK COLLECT INTO strategies,orgs;
3136 CLOSE c_account_strat_wo_ou;
3137 ELSE
3138 OPEN c_strat_with_ou('ACCOUNT');
3139 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
3140 CLOSE c_strat_with_ou;
3141 END IF;
3142 elsif l_DefaultStrategyLevel = 30 THEN
3143
3144 IF l_org_enabled = 'Y' THEN
3145 OPEN c_bill_strat_wo_ou(l_org_id);
3146 FETCH c_bill_strat_wo_ou BULK COLLECT INTO strategies,orgs;
3147 CLOSE c_bill_strat_wo_ou;
3148 /*for rec1 in c_bill_strat_wo_ou(l_org_id) loop
3149 update iex_strategies
3150 set org_id=rec1.org_id
3151 where strategy_id = rec1.strategy_id;
3152 end loop;*/
3153 ELSE
3154 OPEN c_strat_with_ou('BILL_TO');
3155 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
3156 CLOSE c_strat_with_ou;
3157 /*for rec1 in c_bill_strat_with_ou(l_org_id) loop
3158 update iex_strategies
3159 set org_id=null
3160 where strategy_id = rec1.strategy_id;
3161 end loop;*/
3162 END IF;
3163 elsif l_DefaultStrategyLevel = 40 THEN
3164
3165 IF l_org_enabled = 'Y' THEN
3166 OPEN c_del_strat_wo_ou(l_org_id);
3167 FETCH c_del_strat_wo_ou BULK COLLECT INTO strategies,orgs;
3168 CLOSE c_del_strat_wo_ou;
3169 ELSE
3170 OPEN c_strat_with_ou('DELINQUENT');
3171 FETCH c_strat_with_ou BULK COLLECT INTO strategies,orgs;
3172 CLOSE c_strat_with_ou;
3173 END IF;
3174
3175 end if;
3176 fnd_file.put_line(FND_FILE.LOG, 'Checking..');
3177 fnd_file.put_line(FND_FILE.LOG, 'Updating number of strategies ' || strategies.count);
3178 if strategies.count>0 then
3179
3180 forall i in strategies.first..strategies.last
3181 update iex_strategies
3182 set org_id=orgs(i)
3183 where strategy_id = strategies(i);
3184 commit;
3185 end if;
3186
3187 EXCEPTION
3188 WHEN OTHERS THEN
3189 write_log(FND_LOG.LEVEL_STATEMENT, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
3190 fnd_file.put_line(FND_FILE.LOG, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
3191 rollback;
3192
3193 END;
3194
3195 --end Naveen
3196
3197 --Start adding for bug 8630852 by gnramasa 9-July-09
3198 PROCEDURE PRINT_CLOB
3199 (
3200 lob_loc IN CLOB)
3201 IS
3202 /*-----------------------------------------------------------------------+
3203 | Local Variable Declarations and initializations |
3204 +-----------------------------------------------------------------------*/
3205 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
3206 l_api_version CONSTANT NUMBER := 1.0;
3207 c_endline CONSTANT VARCHAR2 (1) := '
3208 ';
3209 c_endline_len CONSTANT NUMBER := LENGTH (c_endline);
3210 l_start NUMBER := 1;
3211 l_end NUMBER;
3212 l_one_line VARCHAR2 (7000);
3213 l_charset VARCHAR2(100);
3214 /*-----------------------------------------------------------------------+
3215 | Cursor Declarations |
3216 +-----------------------------------------------------------------------*/
3217 BEGIN
3218 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
3219 LOOP
3220 l_end := DBMS_LOB.INSTR (lob_loc => lob_loc, pattern => c_endline, offset => l_start, nth => 1 );
3221 --FND_FILE.put_line( FND_FILE.LOG,'l_end-->'||l_end);
3222 IF (NVL (l_end, 0) < 1) THEN
3223 EXIT;
3224 END IF;
3225 l_one_line := DBMS_LOB.SUBSTR (lob_loc => lob_loc, amount => l_end - l_start, offset => l_start );
3226 --FND_FILE.put_line( FND_FILE.LOG,'l_one_line-->'||l_one_line);
3227 --FND_FILE.put_line( FND_FILE.LOG,'c_endline_len-->'||c_endline_len);
3228 l_start := l_end + c_endline_len;
3229 --FND_FILE.put_line( FND_FILE.LOG,'l_start-->'||l_start);
3230 --FND_FILE.put_line( FND_FILE.LOG,'32');
3231 Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
3232 END LOOP;
3233 END PRINT_CLOB;
3234
3235 /*========================================================================+
3236 Function which replaces the special characters in the strings to form
3237 a valid XML string
3238 +========================================================================*/
3239 FUNCTION format_string(p_string varchar2) return varchar2 IS
3240
3241 l_string varchar2(2000);
3242 BEGIN
3243
3244 l_string := replace(p_string,'&','&'||'amp;');
3245 l_string := replace(l_string,'<','&'||'lt;');
3246 l_string := replace(l_string,'>','&'||'gt;');
3247
3248 RETURN l_string;
3249
3250 END format_string;
3251
3252 Procedure get_resource (p_strategy_rec IN IEX_STRATEGY_PVT.STRATEGY_REC_TYPE,
3253 p_work_item_template_id IN NUMBER,
3254 x_resource_id OUT NOCOPY NUMBER,
3255 x_resource_name OUT NOCOPY VARCHAR2)
3256 is
3257 l_resource_id number;
3258 l_resource_name VARCHAR2(360);
3259 l_Assignment_level varchar2(100);
3260 l_competence_tab IEX_STRATEGY_WF.tab_of_comp_id;
3261 l_index NUMBER :=1;
3262 l_default_resource_id number;
3263 bReturn Boolean;
3264 l_party_id number;
3265 l_cust_acct_id number;
3266 l_cust_site_use_id number;
3267
3268 cursor c_get_competence_id (p_work_item_temp_id NUMBER) IS
3269 SELECT competence_id
3270 from iex_strategy_work_skills
3271 where work_item_temp_id = p_work_item_temp_id;
3272
3273 cursor c_resource_name (p_resource_id number)
3274 is
3275 select source_name
3276 from jtf_rs_resource_extns
3277 where resource_id = p_resource_id;
3278
3279 begin
3280 writelog('Begin get_resource');
3281 l_Assignment_Level := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
3282
3283 l_default_resource_id := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
3284 l_resource_id := l_default_resource_id;
3285
3286 l_party_id := p_strategy_rec.party_id;
3287 l_cust_acct_id := p_strategy_rec.cust_account_id;
3288 l_cust_site_use_id := p_strategy_rec.customer_site_use_id;
3289
3290 FOR c_rec IN c_get_competence_id(p_work_item_template_id)
3291 LOOP
3292 l_competence_tab(l_index) := c_rec.competence_id;
3293 l_index := l_index +1;
3294 END LOOP;
3295
3296 if p_strategy_rec.object_type = 'PARTY' then
3297
3298 if l_Assignment_Level = 'PARTY' then
3299 IEX_STRATEGY_WF.get_resource(p_party_id => l_party_id,
3300 p_competence_tab => l_competence_tab,
3301 x_resource_id => l_resource_id);
3302 end if;
3303 elsif p_strategy_rec.object_type = 'IEX_ACCOUNT' then
3304
3305 if l_Assignment_Level = 'PARTY' then
3306 IEX_STRATEGY_WF.get_resource(l_party_id,l_competence_tab,l_resource_id);
3307 elsif l_Assignment_level = 'ACCOUNT' then
3308 bReturn := IEX_STRATEGY_WF.get_account_resource(l_cust_acct_id, l_competence_tab, l_resource_id);
3309 end if;
3310
3311 else
3312 if l_Assignment_Level = 'PARTY' then
3313 IEX_STRATEGY_WF.get_resource(l_party_id,l_competence_tab,l_resource_id);
3314 elsif l_Assignment_level = 'ACCOUNT' then
3315 bReturn := IEX_STRATEGY_WF.get_account_resource(l_cust_acct_id, l_competence_tab, l_resource_id);
3316 else
3317 bReturn := IEX_STRATEGY_WF.get_billto_resource(l_cust_site_use_id,l_competence_tab,l_resource_id);
3318 end if;
3319
3320 end if;
3321
3322 if l_resource_id is null then
3323 l_resource_id := l_default_resource_id;
3324 end if;
3325
3326 x_resource_id := l_resource_id;
3327
3328 open c_resource_name (l_resource_id);
3329 fetch c_resource_name into l_resource_name;
3330 close c_resource_name;
3331
3332 x_resource_name := l_resource_name;
3333
3334 writelog('In get_resource raised Exception l_resource_name: ' || l_resource_name);
3335 EXCEPTION
3336 WHEN OTHERS THEN
3337 writelog('In get_resource raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
3338 fnd_file.put_line(FND_FILE.LOG, 'In get_resource raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
3339 end get_resource;
3340
3341 Procedure gen_xml_header_data_strategy (p_strategy_mode IN VARCHAR2)
3342 is
3343 l_api_version CONSTANT NUMBER := 1.0;
3344 l_xml_header varchar2(4000);
3345 l_xml_header_length number;
3346 l_close_tag VARCHAR2(500);
3347 l_report_date varchar2(100);
3348 l_pro_sty_by_ou varchar2(100); --Changed for bug 9173177 gnramasa 11th Dec 09
3349 l_skip_def_sty_assign varchar2(100); --Changed for bug 9173177 gnramasa 11th Dec 09
3350 l_grace_preiod number;
3351 l_resource_id number;
3352 l_fulfilment_rs VARCHAR2(700); --Changed for bug 9027990 gnramasa 5th Nov 09
3353 l_terr_acc_level varchar2(100); --Changed for bug 9173177 gnramasa 11th Dec 09
3354 l_lookup_code varchar2(100); --Changed for bug 9173177 gnramasa 11th Dec 09
3355 --Start adding for bug 8708244 gnramasa 31stJuly 09
3356 l_process_sty_by_cust varchar2(100);
3357 l_process_sty_by_acc varchar2(100);
3358 l_process_sty_by_billto varchar2(100);
3359 l_process_sty_by_del varchar2(100);
3360 l_org_override_patry varchar2(100);
3361 l_strategy_level_name varchar2(100);
3362 l_sty_at_multi_level varchar2(100) := 'N';
3363 l_no_sty_level number := 0;
3364 l_encoding VARCHAR2(100); --Added for bug 9094791 gnramasa 17th Nov 09
3365 l_collection_method varchar2(30); --Added by snuthala for 10221334 12/2/2010
3366
3367 cursor c_get_lookup_meaning (p_lookup_code varchar2)
3368 is
3369 select
3370 meaning
3371 from fnd_lookups
3372 where lookup_type= 'YES_NO'
3373 and lookup_code = p_lookup_code;
3374
3375 cursor c_resource_name (p_resource_id number)
3376 is
3377 select source_name
3378 from jtf_rs_resource_extns
3379 where resource_id = p_resource_id;
3380
3381 cursor c_sty_quest_items
3382 is
3383 select using_customer_level,
3384 using_account_level,
3385 using_billto_level,
3386 using_delinquency_level,
3387 define_party_running_level,
3388 define_ou_running_level
3389 from iex_questionnaire_items;
3390
3391 cursor c_system_str_level
3392 is
3393 SELECT iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',preference_value)
3394 FROM iex_app_preferences_b
3395 WHERE preference_name='COLLECTIONS STRATEGY LEVEL'
3396 AND enabled_flag = 'Y'
3397 AND org_id IS NULL;
3398
3399 cursor c_collections_method is
3400 select collections_methods
3401 from iex_questionnaire_items;
3402
3403
3404 begin
3405 writelog('Begin gen_xml_header_data_strategy');
3406 FND_FILE.put_line( FND_FILE.LOG,'XML header data generation starts');
3407
3408 select to_char(sysdate, 'YYYY-MM-DD')
3409 into l_report_date
3410 from dual;
3411 writelog('gen_xml_header_data_strategy: l_report_date: ' || l_report_date);
3412
3413 l_terr_acc_level := NVL(fnd_profile.value('IEX_ACCESS_LEVEL'), 'PARTY');
3414 writelog('gen_xml_header_data_strategy: l_terr_acc_level: ' || l_terr_acc_level);
3415
3416 -- l_lookup_code := nvl(fnd_profile.value('IEX_PROC_STR_ORG'),'N');
3417 l_pro_sty_by_ou := nvl(fnd_profile.value('IEX_PROC_STR_ORG'),'N'); --Added for Bug 10037019 03-Sep-2010 barathsr
3418 writelog('gen_xml_header_data_strategy: l_pro_sty_by_ou: ' || l_pro_sty_by_ou);
3419
3420 /* open c_get_lookup_meaning (l_lookup_code);
3421 fetch c_get_lookup_meaning into l_pro_sty_by_ou;
3422 close c_get_lookup_meaning;*/
3423
3424 -- l_lookup_code := NVL(FND_PROFILE.VALUE('IEX_SKIP_DEFAULT_STRATEGY_ASSIGNMENT'), 'N');
3425 l_skip_def_sty_assign:= NVL(FND_PROFILE.VALUE('IEX_SKIP_DEFAULT_STRATEGY_ASSIGNMENT'), 'N');--Added for Bug 10037019 03-Sep-2010 barathsr
3426 writelog('gen_xml_header_data_strategy: l_skip_def_sty_assign: ' || l_skip_def_sty_assign);
3427 /* open c_get_lookup_meaning (l_lookup_code);
3428 fetch c_get_lookup_meaning into l_skip_def_sty_assign;
3429 close c_get_lookup_meaning;*/
3430
3431 l_resource_id := NVL(fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE'), 0);
3432 open c_resource_name (l_resource_id);
3433 fetch c_resource_name into l_fulfilment_rs;
3434 close c_resource_name;
3435
3436 open c_sty_quest_items;
3437 fetch c_sty_quest_items into l_process_sty_by_cust, l_process_sty_by_acc, l_process_sty_by_billto, l_process_sty_by_del,
3438 l_org_override_patry, l_coll_at_ous;
3439 close c_sty_quest_items;
3440
3441 OPEN c_collections_method;
3442 FETCH c_collections_method INTO l_collection_method;
3443 close c_collections_method;
3444 --Begin of comment for Bug 10037019 03-Sep-2010 barathsr
3445 /* open c_get_lookup_meaning (l_process_sty_by_cust);
3446 fetch c_get_lookup_meaning into l_process_sty_by_cust;
3447 close c_get_lookup_meaning;
3448
3449 open c_get_lookup_meaning (l_process_sty_by_acc);
3450 fetch c_get_lookup_meaning into l_process_sty_by_acc;
3451 close c_get_lookup_meaning;
3452
3453 open c_get_lookup_meaning (l_process_sty_by_billto);
3454 fetch c_get_lookup_meaning into l_process_sty_by_billto;
3455 close c_get_lookup_meaning;
3456
3457 open c_get_lookup_meaning (l_process_sty_by_del);
3458 fetch c_get_lookup_meaning into l_process_sty_by_del;
3459 close c_get_lookup_meaning;
3460
3461 open c_get_lookup_meaning (l_org_override_patry);
3462 fetch c_get_lookup_meaning into l_org_override_patry;
3463 close c_get_lookup_meaning;
3464
3465 open c_get_lookup_meaning (l_coll_at_ous);
3466 fetch c_get_lookup_meaning into l_coll_at_ous;
3467 close c_get_lookup_meaning;*/
3468 --End of comment for Bug 10037019 03-Sep-2010 barathsr
3469
3470 open c_system_str_level;
3471 fetch c_system_str_level into l_strategy_level_name;
3472 close c_system_str_level;
3473
3474 --Start adding for bug 8761053 gnramasa 18th Aug 09
3475 if l_process_sty_by_cust = 'Y' then--Added for Bug 10037019 03-Sep-2010 barathsr
3476 l_no_sty_level := l_no_sty_level + 1;
3477 end if;
3478 if l_process_sty_by_acc = 'Y' then--Added for Bug 10037019 03-Sep-2010 barathsr
3479 l_no_sty_level := l_no_sty_level + 1;
3480 end if;
3481 if l_process_sty_by_billto = 'Y' then--Added for Bug 10037019 03-Sep-2010 barathsr
3482 l_no_sty_level := l_no_sty_level + 1;
3483 end if;
3484 if l_process_sty_by_del = 'Y' then--Added for Bug 10037019 03-Sep-2010 barathsr
3485 l_no_sty_level := l_no_sty_level + 1;
3486 end if;
3487
3488 if l_no_sty_level >1 then
3489 l_sty_at_multi_level := 'Y';
3490 else
3491 l_sty_at_multi_level := 'N';
3492 end if;
3493
3494
3495
3496 l_grace_preiod := NVL(to_number(FND_PROFILE.VALUE('IEX_STRY_GRACE_PERIOD')), 0);
3497
3498 FND_FILE.put_line( FND_FILE.LOG,'Start constructing the XML Header');
3499 l_new_line := '
3500 ';
3501 /*Get the special characters replaced */
3502 l_report_date := format_string(l_report_date);
3503 l_fulfilment_rs := format_string(l_fulfilment_rs);
3504
3505 /* Prepare the tag for the report heading */
3506 --Start adding for bug 9094791 gnramasa 17th Nov 09
3507 --l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
3508 -- Instead of hard coding the value, pick the charcter set value from "ICX: Client IANA Encoding" profile.
3509 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
3510 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
3511 --End adding for bug 9094791 gnramasa 17th Nov 09
3512 l_xml_header := l_xml_header ||l_new_line||'<STRATEGYSET>';
3513 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
3514 l_xml_header := l_xml_header ||l_new_line||' <STRATEGY_LEVEL>'|| l_strategy_level_name ||'</STRATEGY_LEVEL>';
3515 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_CUST>'|| l_process_sty_by_cust ||'</PROCESS_STY_BY_CUST>';
3516 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_CUST1>'|| iex_utilities.get_lookup_meaning('IEX_YES_NO',l_process_sty_by_cust) ||'</PROCESS_STY_BY_CUST1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3517 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_ACC>'|| l_process_sty_by_acc ||'</PROCESS_STY_BY_ACC>';
3518 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_ACC1>'|| iex_utilities.get_lookup_meaning('IEX_YES_NO',l_process_sty_by_acc)||'</PROCESS_STY_BY_ACC1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3519 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_BILLTO>'|| l_process_sty_by_billto ||'</PROCESS_STY_BY_BILLTO>';
3520 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_BILLTO1>'|| iex_utilities.get_lookup_meaning('IEX_YES_NO',l_process_sty_by_billto)||'</PROCESS_STY_BY_BILLTO1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3521 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_DEL>'|| l_process_sty_by_del ||'</PROCESS_STY_BY_DEL>';
3522 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_DEL1>'|| iex_utilities.get_lookup_meaning('IEX_YES_NO',l_process_sty_by_del)||'</PROCESS_STY_BY_DEL1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3523 l_xml_header := l_xml_header ||l_new_line||' <ORG_OVERRIDE_PATRY>'|| l_org_override_patry ||'</ORG_OVERRIDE_PATRY>';
3524 l_xml_header := l_xml_header ||l_new_line||' <ORG_OVERRIDE_PATRY1>'||iex_utilities.get_lookup_meaning('IEX_YES_NO',l_org_override_patry) ||'</ORG_OVERRIDE_PATRY1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3525 l_xml_header := l_xml_header ||l_new_line||' <COLL_AT_OUS>'|| l_coll_at_ous ||'</COLL_AT_OUS>';
3526 l_xml_header := l_xml_header ||l_new_line||' <COLL_AT_OUS1>'|| iex_utilities.get_lookup_meaning('IEX_YES_NO',l_coll_at_ous) ||'</COLL_AT_OUS1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3527 l_xml_header := l_xml_header ||l_new_line||' <TERR_ACCESS_LEVEL>'|| l_terr_acc_level ||'</TERR_ACCESS_LEVEL>';
3528 l_xml_header := l_xml_header ||l_new_line||' <STRATEGY_MODE>' || p_strategy_mode ||'</STRATEGY_MODE>';
3529 l_xml_header := l_xml_header ||l_new_line||' <CONC_REQUEST_ID>' || FND_GLOBAL.CONC_REQUEST_ID ||'</CONC_REQUEST_ID>';
3530 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_OU>' || l_pro_sty_by_ou ||'</PROCESS_STY_BY_OU>';
3531 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_OU1>' || iex_utilities.get_lookup_meaning('IEX_YES_NO',l_pro_sty_by_ou) ||'</PROCESS_STY_BY_OU1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3532 l_xml_header := l_xml_header ||l_new_line||' <SKIP_DEF_STY_ASSIGN>' || l_skip_def_sty_assign ||'</SKIP_DEF_STY_ASSIGN>';
3533 l_xml_header := l_xml_header ||l_new_line||' <SKIP_DEF_STY_ASSIGN1>' ||iex_utilities.get_lookup_meaning('IEX_YES_NO',l_skip_def_sty_assign) ||'</SKIP_DEF_STY_ASSIGN1>';--Added for Bug 10037019 03-Sep-2010 barathsr
3534 l_xml_header := l_xml_header ||l_new_line||' <STY_DEFAULT_TEMPLATE>' || format_string(l_DefaultTempName) ||'</STY_DEFAULT_TEMPLATE>';
3535 l_xml_header := l_xml_header ||l_new_line||' <DEFAULT_RESOURCE>' || format_string(l_SourceName) ||'</DEFAULT_RESOURCE>';
3536 l_xml_header := l_xml_header ||l_new_line||' <FULFILMENT_RESOURCE>' || l_fulfilment_rs ||'</FULFILMENT_RESOURCE>';
3537 l_xml_header := l_xml_header ||l_new_line||' <STY_GRACE_PERIOD>' || l_grace_preiod ||'</STY_GRACE_PERIOD>';
3538 l_xml_header := l_xml_header ||l_new_line||' <STY_AT_MULTI_LEVEL>' || l_sty_at_multi_level || '</STY_AT_MULTI_LEVEL>';
3539 l_xml_header := l_xml_header ||l_new_line||' <COLL_METHOD>' || l_collection_method || '</COLL_METHOD>'; -- Added by snuthala for bug 10221334 12/2/2010
3540 l_xml_header := l_xml_header ||l_new_line||'<ROWSET>';
3541
3542 --End adding for bug 8761053 gnramasa 18th Aug 09
3543 --End adding for bug 8708244 gnramasa 31stJuly 09
3544
3545
3546 l_xml_header_length := length(l_xml_header);
3547 tempResult := l_xml_header;
3548 FND_FILE.put_line( FND_FILE.LOG,'Constructing the XML Header is success');
3549
3550 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
3551 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
3552 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
3553
3554 writelog('End gen_xml_header_data_strategy');
3555
3556 EXCEPTION
3557 WHEN OTHERS THEN
3558 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
3559 RAISE;
3560 END gen_xml_header_data_strategy;
3561
3562 Procedure gen_xml_body_strategy (p_strategy_id IN NUMBER DEFAULT NULL,
3563 p_strategy_rec IN IEX_STRATEGY_PVT.STRATEGY_REC_TYPE DEFAULT NULL,
3564 p_strategy_status IN VARCHAR2,
3565 p_default_sty_level IN NUMBER DEFAULT NULL,
3566 p_party_id IN NUMBER DEFAULT NULL,
3567 p_cust_acc_id IN NUMBER DEFAULT NULL,
3568 p_site_use_id IN NUMBER DEFAULT NULL,
3569 p_del_id IN NUMBER DEFAULT NULL)
3570 is
3571 l_api_version CONSTANT NUMBER := 1.0;
3572 l_xml_body varchar2(4000);
3573 l_party_id number;
3574 l_cust_Account_id number;
3575 l_customer_site_use_id number;
3576 l_delinquency_id number;
3577 l_score number;
3578 l_new_score number;
3579 --Start changing for bug 9027990 gnramasa 5th Nov 09
3580 l_strategy_name VARCHAR2(500);
3581 l_new_strategy_name VARCHAR2(500);
3582 l_party_name VARCHAR2(700);
3583 l_account_number VARCHAR2(100);
3584 l_location VARCHAR2(100);
3585 l_trx_number VARCHAR2(100);
3586 l_first_work_item VARCHAR2(500);
3587 l_work_item_temp_id number;
3588 l_resource_id number;
3589 l_resource_name VARCHAR2(700);
3590 l_sty_workitem_st varchar2(500);
3591 --Start adding for bug 8708244 gnramasa 31stJuly 09
3592 l_strategy_level_name varchar2(100);
3593 l_strategy_level number;
3594 l_org_name varchar2(500); --Added for bug 8761053 gnramasa 18th Aug 09
3595 --End changing for bug 9027990 gnramasa 5th Nov 09
3596
3597 cursor c_strategy
3598 is
3599 select
3600 sty.party_id,
3601 sty.cust_Account_id,
3602 sty.customer_site_use_id,
3603 sty.delinquency_id,
3604 sty.score_value,
3605 tpl.strategy_name,
3606 stry_temp_wkitem.name,
3607 iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',swi.status_code) STATUS_MEANING,
3608 jtf.source_name,
3609 iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',(decode(sty.strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT',
3610 30, 'BILL_TO', 40, 'DELINQUENCY'))) strategy_level_name,
3611 sty.strategy_level strategy_level
3612 from iex_strategies sty,
3613 iex_strategy_templates_tl tpl,
3614 iex_strategy_work_items swi,
3615 iex_stry_temp_work_items_vl stry_temp_wkitem,
3616 jtf_rs_resource_extns jtf
3617 where sty.strategy_id = p_strategy_id
3618 and sty.strategy_template_id = tpl.strategy_temp_id
3619 and tpl.language = userenv('LANG')
3620 and sty.next_work_item_id = swi.work_item_id
3621 and swi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
3622 and stry_temp_wkitem.language = userenv('LANG')
3623 and swi.resource_id = jtf.resource_id;
3624
3625 cursor c_strategy1
3626 is
3627 select
3628 sty.party_id,
3629 sty.cust_Account_id,
3630 sty.customer_site_use_id,
3631 sty.delinquency_id,
3632 sty.score_value,
3633 tpl.strategy_name,
3634 iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',(decode(sty.strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT',
3635 30, 'BILL_TO', 40, 'DELINQUENCY'))) strategy_level_name,
3636 sty.strategy_level strategy_level
3637 from iex_strategies sty,
3638 iex_strategy_templates_tl tpl
3639 where sty.strategy_id = p_strategy_id
3640 and sty.strategy_template_id = tpl.strategy_temp_id
3641 and tpl.language = userenv('LANG');
3642
3643 cursor c_strategy_name (l_sty_template_id number)
3644 is
3645 SELECT tpl.strategy_name,
3646 iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',(DECODE(p_strategy_rec.strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT',
3647 30, 'BILL_TO', 40, 'DELINQUENCY'))) strategy_level_name
3648 FROM iex_strategy_templates_tl tpl,
3649 iex_strategy_templates_b tpb
3650 WHERE tpb.strategy_temp_id = tpl.strategy_temp_id
3651 AND tpl.strategy_temp_id = l_sty_template_id
3652 AND tpl.language = userenv('LANG');
3653 --End added for bug 8708244 gnramasa 31stJuly 09
3654
3655 cursor c_first_work_item (l_sty_template_id number)
3656 is
3657 select stry_temp_wkitem.name,
3658 stry_temp_wkitem.work_item_temp_id,
3659 iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',(decode(stry_temp_wkitem.pre_execution_wait,0,'OPEN','PRE-WAIT'))) STATUS_MEANING
3660 from iex_strategy_work_temp_xref xref
3661 ,iex_stry_temp_work_items_vl stry_temp_wkitem
3662 where xref.work_item_temp_id = stry_temp_wkitem.work_item_temp_id
3663 and xref.strategy_temp_id = l_sty_template_id
3664 and stry_temp_wkitem.language = userenv('LANG')
3665 order by xref.work_item_order;
3666
3667 cursor c_party (p_party_id number)
3668 is
3669 select
3670 party_name
3671 from hz_parties
3672 where party_id = p_party_id;
3673
3674 cursor c_account (p_cust_acct_id number)
3675 is
3676 select
3677 p.party_name,
3678 c.account_number
3679 from hz_parties p,
3680 hz_cust_accounts c
3681 where c.cust_account_id = p_cust_acct_id
3682 and c.party_id = p.party_id;
3683
3684 cursor c_billto (p_cust_site_use_id number)
3685 is
3686 select
3687 p.party_name,
3688 c.account_number,
3689 site_uses.location
3690 from hz_parties p,
3691 hz_cust_accounts c,
3692 hz_cust_acct_sites_all acct_sites,
3693 hz_cust_site_uses_all site_uses
3694 where site_uses.site_use_id = p_cust_site_use_id
3695 and acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
3696 and c.cust_account_id = acct_sites.cust_account_id
3697 and p.party_id = c.party_id;
3698
3699 -- changed for bug 9736759 pnaveenk
3700 -- added account number, location to delinquency level strategy
3701 cursor c_delinquency (p_delinquency_id number)
3702 is
3703 select
3704 p.party_name,
3705 aps.trx_number TRANSACTION_NUMBER,
3706 b.account_number,
3707 c.location
3708 from iex_delinquencies_all del,
3709 ar_payment_schedules_all aps ,
3710 hz_parties p,
3711 iex_strategies a,
3712 hz_cust_accounts b,
3713 hz_cust_site_uses_all c
3714 where del.delinquency_id = p_delinquency_id
3715 and del.payment_Schedule_id = aps.payment_Schedule_id
3716 and del.party_cust_id = p.party_id
3717 and del.delinquency_id = a.delinquency_id
3718 and a.cust_account_id = b.cust_account_id
3719 and a.customer_site_use_id = c.site_use_id;
3720
3721 --Start adding for bug 8761053 gnramasa 18th Aug 09
3722 cursor c_org_name
3723 is
3724 select
3725 name
3726 from hr_all_organization_units_tl
3727 where organization_id = l_org_id
3728 and language = userenv('LANG');
3729
3730 begin
3731 writelog('Begin gen_xml_body_strategy');
3732 writelog('gen_xml_body_strategy, p_strategy_id: ' || p_strategy_id);
3733 writelog('gen_xml_body_strategy, p_strategy_status: ' || p_strategy_status);
3734
3735 if p_strategy_rec.strategy_template_id IS NOT NULL then
3736 l_party_id := p_strategy_rec.party_id;
3737 l_cust_Account_id := p_strategy_rec.cust_account_id;
3738 l_customer_site_use_id := p_strategy_rec.customer_site_use_id;
3739 l_delinquency_id := p_strategy_rec.delinquency_id;
3740 l_strategy_level := p_strategy_rec.strategy_level;
3741
3742 --Start adding for bug 8708244 gnramasa 31stJuly 09
3743 open c_strategy_name (p_strategy_rec.strategy_template_id);
3744 if p_strategy_status = 'RECREATE' then
3745 fetch c_strategy_name into l_new_strategy_name, l_strategy_level_name;
3746 l_new_score := p_strategy_rec.score_value;
3747 else
3748 fetch c_strategy_name into l_strategy_name, l_strategy_level_name;
3749 l_score := p_strategy_rec.score_value;
3750 end if;
3751 close c_strategy_name;
3752
3753 open c_first_work_item (p_strategy_rec.strategy_template_id);
3754 fetch c_first_work_item into l_first_work_item, l_work_item_temp_id,l_sty_workitem_st;
3755 close c_first_work_item;
3756
3757 writelog('gen_xml_body_strategy, before get_resource');
3758 get_resource(p_strategy_rec => p_strategy_rec,
3759 p_work_item_template_id => l_work_item_temp_id,
3760 x_resource_id => l_resource_id,
3761 x_resource_name => l_resource_name);
3762 writelog('gen_xml_body_strategy, after get_resource');
3763 end if;
3764
3765 if p_strategy_id IS NOT NULL then
3766 if p_strategy_status = 'RECREATE' then
3767 open c_strategy1;
3768 fetch c_strategy1 into l_party_id, l_cust_Account_id, l_customer_site_use_id, l_delinquency_id,
3769 l_score, l_strategy_name, l_strategy_level_name, l_strategy_level;
3770 close c_strategy1;
3771 else
3772 open c_strategy;
3773 fetch c_strategy into l_party_id, l_cust_Account_id, l_customer_site_use_id,
3774 l_delinquency_id, l_score, l_strategy_name,l_first_work_item,
3775 l_sty_workitem_st,l_resource_name, l_strategy_level_name, l_strategy_level;
3776 close c_strategy;
3777 --End adding for bug 8708244 gnramasa 31stJuly 09
3778 end if;
3779 end if;
3780
3781 --if l_StrategyLevelName = 'CUSTOMER' then
3782 if l_strategy_level = 10 then
3783 open c_party (l_party_id);
3784 fetch c_party into l_party_name;
3785 close c_party;
3786 --elsif l_StrategyLevelName = 'ACCOUNT' then
3787 elsif l_strategy_level = 20 then
3788 open c_account (l_cust_Account_id);
3789 fetch c_account into l_party_name, l_account_number;
3790 close c_account;
3791 --elsif l_StrategyLevelName = 'BILL_TO' then
3792 elsif l_strategy_level = 30 then
3793 open c_billto (l_customer_site_use_id);
3794 fetch c_billto into l_party_name, l_account_number, l_location;
3795 close c_billto;
3796 else
3797 open c_delinquency (l_delinquency_id);
3798 fetch c_delinquency into l_party_name, l_trx_number, l_account_number, l_location;
3799 close c_delinquency;
3800 end if;
3801
3802 if p_strategy_status = 'CANCEL' then
3803 if p_default_sty_level = 10 then
3804 if l_party_id is NULL then
3805 l_party_id := p_party_id;
3806 open c_party (l_party_id);
3807 fetch c_party into l_party_name;
3808 close c_party;
3809 end if;
3810 elsif p_default_sty_level = 20 then
3811 if l_cust_Account_id is NULL then
3812 l_cust_Account_id := p_cust_acc_id;
3813 open c_account (l_cust_Account_id);
3814 fetch c_account into l_party_name, l_account_number;
3815 close c_account;
3816 end if;
3817 elsif p_default_sty_level = 30 then
3818 if l_customer_site_use_id is NULL then
3819 l_customer_site_use_id := p_site_use_id;
3820 open c_billto (l_customer_site_use_id);
3821 fetch c_billto into l_party_name, l_account_number, l_location;
3822 close c_billto;
3823 end if;
3824 elsif p_default_sty_level = 40 then
3825 if l_delinquency_id is NULL then
3826 l_delinquency_id := p_del_id;
3827 open c_delinquency (l_delinquency_id);
3828 fetch c_delinquency into l_party_name, l_trx_number, l_account_number, l_location;
3829 close c_delinquency;
3830 end if;
3831 end if;
3832 end if;
3833
3834 if l_coll_at_ous = 'Yes' then
3835 open c_org_name;
3836 fetch c_org_name into l_org_name;
3837 close c_org_name;
3838 end if;
3839
3840 writelog('gen_xml_body_strategy, before format_string');
3841 /*Get the special characters replaced */
3842 l_party_name := format_string(l_party_name);
3843 l_account_number := format_string(l_account_number);
3844 l_location := format_string(l_location);
3845 l_trx_number := format_string(l_trx_number);
3846 l_strategy_name := format_string(l_strategy_name);
3847 l_new_strategy_name := format_string(l_new_strategy_name);
3848 l_org_name := format_string(l_org_name);
3849 writelog('gen_xml_body_strategy, after format_string');
3850
3851 --l_xml_body := l_xml_body ||l_new_line||'<ROW num="' || l_seq_no || '">';
3852 l_xml_body := l_xml_body ||l_new_line||'<'|| p_strategy_status||' num="' || l_seq_no || '">';
3853 l_xml_body := l_xml_body ||l_new_line||'<PARTY_ID> ' || l_party_id || '</PARTY_ID>';
3854 l_xml_body := l_xml_body ||l_new_line||'<PARTY_NAME>' || l_party_name || '</PARTY_NAME>';
3855 l_xml_body := l_xml_body ||l_new_line||'<ACCOUNT_NUMBER>' || l_account_number || '</ACCOUNT_NUMBER>';
3856 l_xml_body := l_xml_body ||l_new_line||'<LOCATION>' || l_location || '</LOCATION>';
3857 l_xml_body := l_xml_body ||l_new_line||'<CUST_ACCOUNT_ID> ' || l_cust_Account_id || '</CUST_ACCOUNT_ID>';
3858 l_xml_body := l_xml_body ||l_new_line||'<CUST_SITE_USE_ID> ' || l_customer_site_use_id || '</CUST_SITE_USE_ID>';
3859 l_xml_body := l_xml_body ||l_new_line||'<TRANSACTION_NUMBER> ' || l_trx_number || '</TRANSACTION_NUMBER>';
3860 l_xml_body := l_xml_body ||l_new_line||'<DELINQUENCY_ID> ' || l_delinquency_id || '</DELINQUENCY_ID>';
3861 l_xml_body := l_xml_body ||l_new_line||'<STRATEGY_LEVEL> ' || l_strategy_level_name || '</STRATEGY_LEVEL>'; --Added for bug 8708244 gnramasa 31stJuly 09
3862 l_xml_body := l_xml_body ||l_new_line||'<SCORE>' || l_score || '</SCORE>';
3863 l_xml_body := l_xml_body ||l_new_line||'<STRATEGY_NAME>' || l_strategy_name || '</STRATEGY_NAME>';
3864 l_xml_body := l_xml_body ||l_new_line||'<NEW_SCORE>' || l_new_score || '</NEW_SCORE>';
3865 l_xml_body := l_xml_body ||l_new_line||'<NEW_STRATEGY_NAME>' || l_new_strategy_name || '</NEW_STRATEGY_NAME>';
3866 l_xml_body := l_xml_body ||l_new_line||'<WORKITEM_NAME>' || l_first_work_item || '</WORKITEM_NAME>';
3867 l_xml_body := l_xml_body ||l_new_line||'<WORKITEM_ASSIGNEE>' || l_resource_name ||'</WORKITEM_ASSIGNEE>';
3868 l_xml_body := l_xml_body ||l_new_line||'<WORKITEM_STATUS>' || l_sty_workitem_st ||'</WORKITEM_STATUS>';
3869 l_xml_body := l_xml_body ||l_new_line||'<ORGANIZATION_NAME>' || l_org_name ||'</ORGANIZATION_NAME>';
3870 l_xml_body := l_xml_body ||l_new_line||'</'|| p_strategy_status ||'>';
3871
3872 writelog('gen_xml_body_strategy, end of constructing body text');
3873
3874 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
3875 l_seq_no := l_seq_no + 1;
3876
3877 if p_strategy_status = 'CLOSE' then
3878 l_no_closed_rec := l_no_closed_rec + 1;
3879 elsif p_strategy_status = 'REOPEN' then
3880 l_no_reopen_rec := l_no_reopen_rec + 1;
3881 elsif p_strategy_status = 'RECREATE' then
3882 l_no_reassign_rec := l_no_reassign_rec + 1;
3883 elsif p_strategy_status = 'CREATE' then
3884 l_no_new_rec := l_no_new_rec + 1;
3885 end if;
3886
3887 writelog('End gen_xml_body_strategy');
3888
3889 EXCEPTION
3890 WHEN OTHERS THEN
3891 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
3892 writelog('in gen_xml_body_strategy, err: '||sqlerrm);
3893 RAISE;
3894 END gen_xml_body_strategy;
3895
3896 -- Start adding by gnramasa for bug 8833868 3-Sep-09
3897 Procedure gen_xml_append_closetag_sty (p_customer_name_low IN VARCHAR2 DEFAULT NULL,
3898 p_customer_name_high IN VARCHAR2 DEFAULT NULL,
3899 p_account_number_low IN VARCHAR2 DEFAULT NULL,
3900 p_account_number_high IN VARCHAR2 DEFAULT NULL,
3901 p_billto_location_low IN VARCHAR2 DEFAULT NULL,
3902 p_billto_location_high IN VARCHAR2 DEFAULT NULL,
3903 p_org_id IN NUMBER, -- added by snuthala for bug 10221334 on 21-10-2010
3904 p_unprocessed_orgid_count IN NUMBER, -- added by snuthala for bug 10221334 on 21-10-2010
3905 p_unregistered_org_ids IN NUMBER ) -- added by snuthala for bug 10221334 on 21-10-2010
3906 is
3907 l_api_version CONSTANT NUMBER := 1.0;
3908 l_close_tag VARCHAR2(4000) := '';
3909 l_mou_party_tag VARCHAR2(4000) := '';
3910 l_mou_account_tag VARCHAR2(4000) := '';
3911 l_party_id number;
3912 --Start changing for bug 9027990 gnramasa 5th Nov 09
3913 l_party_name VARCHAR2(700);
3914 l_account_number VARCHAR2(100);
3915 --End changing for bug 9027990 gnramasa 5th Nov 09
3916 l_cust_account_id number;
3917 -- Start added by snuthala for bug 10221334 on 21-10-2010
3918 l_org_id number;
3919 l_org_name VARCHAR2(100);
3920 l_mou_org_tag VARCHAR2(4000) := '';
3921 l_mou_org_unreg_tag VARCHAR2(4000) := '';
3922 l_collection_method VARCHAR2(30) ;
3923 l_org_id_coll_method VARCHAR2(30) := 'STRATEGIES';
3924 -- end added by snuthala for bug 10221334 on 21-10-2010
3925 /*
3926 cursor c_mou_party is
3927 select p.party_id party_id,
3928 p.party_name party_name
3929 from hz_parties p
3930 where p.party_id in (
3931 select
3932 d.party_cust_id
3933 from
3934 iex_delinquencies_all d
3935 group by d.party_cust_id
3936 having count(distinct d.org_id) > 1);
3937
3938 cursor c_mou_account is
3939 select p.party_id party_id,
3940 p.party_name party_name,
3941 ca.account_number account_number,
3942 ca.cust_account_id cust_account_id
3943 from hz_parties p,
3944 hz_cust_accounts ca
3945 where p.party_id = ca.party_id
3946 and ca.cust_account_id in (
3947 select
3948 d.cust_account_id
3949 from
3950 iex_delinquencies_all d
3951 group by d.cust_account_id
3952 having count(distinct d.org_id) > 1);
3953
3954
3955 l_c_mou_party c_mou_party%rowtype;
3956 l_c_mou_account c_mou_account%rowtype;
3957 */
3958
3959 TYPE c_mou_partyCurTyp IS REF CURSOR;
3960 c_mou_party c_mou_partyCurTyp;
3961 TYPE c_mou_accountCurTyp IS REF CURSOR;
3962 c_mou_account c_mou_accountCurTyp;
3963 -- added by snuthala for bug 10221334 on 21-10-2010
3964 TYPE c_ouCurTyp IS REF CURSOR;
3965 c_org c_ouCurTyp;
3966
3967 vPLSQL VARCHAR2(5000);
3968 vPLSQL1 VARCHAR2(5000);
3969 vPLSQL2 VARCHAR2(5000); -- added by snuthala for bug 10221334 on 21-10-2010
3970 l_api_name varchar2(100) := 'gen_xml_append_closetag_sty';
3971
3972 cursor c_collections_method is
3973 select collections_methods
3974 from iex_questionnaire_items;
3975
3976 cursor c_org_id_coll_method(p_org_id number) is
3977 select nvl(collections_method,'STRATEGIES')
3978 from IEX_app_preferences_b where
3979 org_id = p_org_id and enabled_flag ='Y';
3980
3981
3982
3983 begin
3984 writelog('Begin gen_xml_append_closetag_sty');
3985 FND_FILE.put_line( FND_FILE.LOG,'XML append close tag generation starts');
3986 FND_FILE.put_line( FND_FILE.LOG,'p_org_id : '||p_org_id); -- added by snuthala for bug 10221334 on 21-10-2010
3987
3988
3989 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
3990 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
3991 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
3992 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
3993 custom_where_clause
3994 (p_customer_name_low => p_customer_name_low,
3995 p_customer_name_high => p_customer_name_high,
3996 p_account_number_low => p_account_number_low,
3997 p_account_number_high => p_account_number_high,
3998 p_billto_location_low => p_billto_location_low,
3999 p_billto_location_high => p_billto_location_high,
4000 p_strategy_level => 10);
4001
4002 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
4003 end if;
4004
4005 vPLSQL := 'select s.party_id party_id, s.party_name party_name from hz_parties s ' ||
4006 ' where s.party_id in ( select d.party_cust_id from iex_delinquencies_all d '||
4007 ' group by d.party_cust_id having count(distinct d.org_id) > 1)';
4008 if l_custom_select IS NOT NULL then
4009 vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
4010 end if;
4011 writelog('gen_xml_append_closetag_sty: vPLSQL = ' || vPLSQL);
4012 FND_FILE.put_line( FND_FILE.LOG,'gen_xml_append_closetag_sty: vPLSQL = ' || vPLSQL);
4013
4014 open c_mou_party for vPLSQL;
4015 loop
4016 fetch c_mou_party into l_party_id, l_party_name;
4017 if c_mou_party%FOUND then
4018 --for l_c_mou_party in c_mou_party loop
4019
4020 l_party_name := format_string(l_party_name);
4021
4022 l_mou_party_tag := l_new_line||'<MOU_PARTY num="' || l_seq_no || '">';
4023 l_mou_party_tag := l_mou_party_tag ||l_new_line||'<PARTY_ID> ' || l_party_id || '</PARTY_ID>';
4024 l_mou_party_tag := l_mou_party_tag ||l_new_line||'<PARTY_NAME>' || l_party_name || '</PARTY_NAME>';
4025 l_mou_party_tag := l_mou_party_tag ||l_new_line||'</MOU_PARTY>';
4026 dbms_lob.writeAppend(tempResult, length(l_mou_party_tag), l_mou_party_tag);
4027 l_seq_no := l_seq_no + 1;
4028 ELSE -- fetch failed, so exit loop
4029 EXIT;
4030 end if;
4031 end loop;
4032 close c_mou_party;
4033
4034 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
4035 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
4036 writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
4037 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
4038 custom_where_clause
4039 (p_customer_name_low => p_customer_name_low,
4040 p_customer_name_high => p_customer_name_high,
4041 p_account_number_low => p_account_number_low,
4042 p_account_number_high => p_account_number_high,
4043 p_billto_location_low => p_billto_location_low,
4044 p_billto_location_high => p_billto_location_high,
4045 p_strategy_level => 20);
4046
4047 writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
4048 end if;
4049 vPLSQL1 := 'select s.party_id party_id, s.party_name party_name, cu_ac.account_number account_number, cu_ac.cust_account_id cust_account_id '||
4050 ' from hz_parties s, hz_cust_accounts cu_ac where s.party_id = cu_ac.party_id and cu_ac.cust_account_id in ( '||
4051 ' select d.cust_account_id from iex_delinquencies_all d group by d.cust_account_id having count(distinct d.org_id) > 1)';
4052 if l_custom_select IS NOT NULL then
4053 vPLSQL1 := vPLSQL1 || ' and exists ( ' || l_custom_select || '= cu_ac.cust_account_id) ';
4054 end if;
4055 writelog('gen_xml_append_closetag_sty: vPLSQL1 = ' || vPLSQL1);
4056 FND_FILE.put_line( FND_FILE.LOG,'gen_xml_append_closetag_sty: vPLSQL1 = ' || vPLSQL1);
4057
4058 open c_mou_account for vPLSQL1;
4059 loop
4060 fetch c_mou_account into l_party_id, l_party_name, l_account_number, l_cust_account_id;
4061 if c_mou_account%FOUND then
4062 --for l_c_mou_account in c_mou_account loop
4063
4064 l_party_name := format_string(l_party_name);
4065 l_account_number := format_string(l_account_number);
4066
4067 l_mou_account_tag := l_new_line||'<MOU_ACCOUNT num="' || l_seq_no || '">';
4068 l_mou_account_tag := l_mou_account_tag ||l_new_line||'<PARTY_ID> ' || l_party_id || '</PARTY_ID>';
4069 l_mou_account_tag := l_mou_account_tag ||l_new_line||'<PARTY_NAME>' || l_party_name || '</PARTY_NAME>';
4070 l_mou_account_tag := l_mou_account_tag ||l_new_line||'<ACCOUNT_NUMBER>' || l_account_number || '</ACCOUNT_NUMBER>';
4071 l_mou_account_tag := l_mou_account_tag ||l_new_line||'<CUST_ACCOUNT_ID>' || l_cust_account_id || '</CUST_ACCOUNT_ID>';
4072 l_mou_account_tag := l_mou_account_tag ||l_new_line||'</MOU_ACCOUNT>';
4073 dbms_lob.writeAppend(tempResult, length(l_mou_account_tag), l_mou_account_tag);
4074 l_seq_no := l_seq_no + 1;
4075 ELSE -- fetch failed, so exit loop
4076 EXIT;
4077 end if;
4078 end loop;
4079 close c_mou_account;
4080 -- End adding by gnramasa for bug 8833868 3-Sep-09
4081 -- Start added by snuthala for bug 10221334 on 21-10-2010
4082 vPLSQL2 := 'SELECT organization_id,name from hr_operating_units where '||
4083 ' mo_global.check_access(organization_id) = ''Y''';
4084 if p_org_id IS NOT NULL then
4085 vPLSQL2 := vPLSQL2 || ' and organization_id = nvl(' || p_org_id ||' ,organization_id) ';
4086 end if;
4087
4088
4089 writelog('gen_xml_append_closetag_sty: vPLSQL2 = ' || vPLSQL2);
4090 FND_FILE.put_line( FND_FILE.LOG,'gen_xml_append_closetag_sty: vPLSQL2 = ' || vPLSQL2);
4091
4092 open c_org for vPLSQL2;
4093 loop
4094 fetch c_org into l_org_id, l_org_name ;
4095 if c_org%FOUND then
4096 --for l_c_mou_account in c_mou_account loop
4097
4098 l_org_name := format_string(l_org_name);
4099 --l_org_id := format_string(l_org_id);
4100 OPEN c_collections_method;
4101 FETCH c_collections_method INTO l_collection_method;
4102 close c_collections_method;
4103 -- select collections_methods into l_collection_method from iex_questionnaire_items;
4104 FND_FILE.PUT_LINE(FND_FILE.LOG, 'collections_methods : '|| l_collection_method);
4105
4106 OPEN C_ORG_ID_COLL_METHOD (l_org_id);
4107 FETCH C_ORG_ID_COLL_METHOD INTO l_org_id_coll_method;
4108 if l_collection_method = 'DUN_STR' and (C_ORG_ID_COLL_METHOD%NOTFOUND) THEN
4109
4110 l_mou_org_unreg_tag := l_new_line||'<MOU_ORG_UNREG num="' || l_seq_no || '">';
4111 l_mou_org_unreg_tag := l_mou_org_unreg_tag ||l_new_line||'<UNREG_ORG_ID> ' || l_org_id || '</UNREG_ORG_ID>';
4112 l_mou_org_unreg_tag := l_mou_org_unreg_tag ||l_new_line||'<UNREG_ORG_NAME>' || l_org_name || '</UNREG_ORG_NAME>';
4113 l_mou_org_unreg_tag := l_mou_org_unreg_tag ||l_new_line||'</MOU_ORG_UNREG>';
4114 dbms_lob.writeAppend(tempResult, length(l_mou_org_unreg_tag), l_mou_org_unreg_tag);
4115
4116 l_seq_no := l_seq_no + 1;
4117 end if;
4118
4119 close C_ORG_ID_COLL_METHOD;
4120
4121 FND_FILE.PUT_LINE(FND_FILE.LOG, 'collections_methods set up for Org id : '|| l_org_id_coll_method);
4122
4123 if l_org_id_coll_method is not null and l_collection_method = 'DUN_STR' and l_org_id_coll_method <> 'STRATEGIES' then
4124 l_mou_org_tag := l_new_line||'<MOU_ORG num="' || l_seq_no || '">';
4125 l_mou_org_tag := l_mou_org_tag ||l_new_line||'<ORG_ID> ' || l_org_id || '</ORG_ID>';
4126 l_mou_org_tag := l_mou_org_tag ||l_new_line||'<ORG_NAME>' || l_org_name || '</ORG_NAME>';
4127 l_mou_org_tag := l_mou_org_tag ||l_new_line||'</MOU_ORG>';
4128 dbms_lob.writeAppend(tempResult, length(l_mou_org_tag), l_mou_org_tag);
4129
4130 l_seq_no := l_seq_no + 1;
4131 end if;
4132 ELSE -- fetch failed, so exit loop
4133 EXIT;
4134 end if;
4135 end loop;
4136
4137 close c_org;
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148 -- end added by snuthala for bug 10221334 on 21-10-2010
4149 l_close_tag := l_new_line||'</ROWSET>';
4150 l_close_tag := l_close_tag ||l_new_line||'<NO_CLOSED_REC>' || l_no_closed_rec || '</NO_CLOSED_REC>';
4151 l_close_tag := l_close_tag ||l_new_line||'<NO_REOPEN_REC>' || l_no_reopen_rec || '</NO_REOPEN_REC>';
4152 l_close_tag := l_close_tag ||l_new_line||'<NO_REASSIGN_REC>' || l_no_reassign_rec || '</NO_REASSIGN_REC>';
4153 l_close_tag := l_close_tag ||l_new_line||'<NO_NEW_REC>' || l_no_new_rec || '</NO_NEW_REC>';
4154 l_close_tag := l_close_tag ||l_new_line||'<UNREGISTERED_ORG_ID_COUNT>' || p_unregistered_org_ids || '</UNREGISTERED_ORG_ID_COUNT>'; -- added by snuthala for bug 10221334 on 21-10-2010
4155 l_close_tag := l_close_tag ||l_new_line||'<UNPROCESSED_ORG_ID_COUNT>' || p_unprocessed_orgid_count || '</UNPROCESSED_ORG_ID_COUNT>'; -- added by snuthala for bug 10221334 on 21-10-2010
4156 l_close_tag := l_close_tag ||l_new_line||'</STRATEGYSET>'||l_new_line;
4157
4158 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
4159 FND_FILE.put_line( FND_FILE.LOG,'Appended close tag to XML data');
4160 --Fnd_File.PUT_line(Fnd_File.OUTPUT,tempResult);
4161 print_clob(lob_loc => tempResult);
4162 FND_FILE.put_line( FND_FILE.LOG,'XML generation is success');
4163 writelog('End gen_xml_append_closetag_sty');
4164
4165 EXCEPTION
4166 WHEN OTHERS THEN
4167 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
4168 RAISE;
4169 END gen_xml_append_closetag_sty;
4170 --End adding for bug 8761053 gnramasa 18th Aug 09
4171 --End adding for bug 8630852 by gnramasa 9-July-09
4172
4173 BEGIN
4174 -- initialize values
4175 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
4176 l_enabled := 'N';
4177
4178 l_DelStatusCurrent := 'CURRENT';
4179 /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
4180 l_DelStatusClose := 'CLOSE';
4181 /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
4182 l_DelStatusDel := 'DELINQUENT';
4183 l_DelStatusPreDel := 'PREDELINQUENT';
4184
4185 l_StratStatusOpen := 'OPEN';
4186 l_StratStatusOnhold := 'ONHOLD';
4187 l_StratStatusPending := 'PENDING';
4188 l_StratStatusClosed := 'CLOSED';
4189 l_StratStatusCancelled := 'CANCELLED';
4190 l_Yes := 'Y';
4191 l_No := 'N';
4192 l_StratObjectFilterType := 'IEXSTRAT';
4193
4194 l_enabled := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
4195 if (l_enabled = 'N') then
4196 l_MsgLevel := FND_LOG.LEVEL_UNEXPECTED;
4197 else
4198 l_MsgLevel := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
4199 end if;
4200 -- l_DefaultTempID := GetDefaultStrategyTempID;
4201 -- chaged for bug 9795455 pnaveenk
4202 l_DefaultTempID := NVL(TO_NUMBER(fnd_profile.value('IEX_STRATEGY_DEFAULT_TEMPLATE')),0);
4203
4204 -- Start for bug # 5487449 on 28/08/2006 by gnramasa
4205 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Default Template ID ' || l_DefaultTempID || ' Profile Name IEX: Strategy Default Template (IEX_STRATEGY_DEFAULT_TEMPLATE)');
4206 begin
4207 select STRATEGY_NAME ,ENABLED_FLAG
4208 into l_DefaultTempName, l_EnabledFlag
4209 from iex_strategy_templates_vl
4210 where STRATEGY_TEMP_ID=l_DefaultTempID;
4211
4212 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Default Template Name :' || l_DefaultTempName || ' , Enabled Flag :' || l_EnabledFlag );
4213 EXCEPTION
4214 WHEN OTHERS THEN
4215 l_DefaultTempName := 'N';
4216 fnd_file.put_line(FND_FILE.LOG, 'Default Template Name raised Exception ' || SQLCODE || ' ' || SQLERRM);
4217 END;
4218
4219 begin
4220 l_default_rs_id := NVL(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'), 0);
4221 select SOURCE_NAME,USER_NAME
4222 into l_SourceName,l_UserName
4223 from jtf_rs_resource_extns
4224 where RESOURCE_ID=l_default_rs_id;
4225 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);
4226 EXCEPTION
4227 WHEN OTHERS THEN
4228 fnd_file.put_line(FND_FILE.LOG, 'Resource Name raised Exception ' || SQLCODE || ' ' || SQLERRM);
4229 END;
4230 -- Start for bug # 5877743 on 28/02/2007 by gnramasa
4231 begin
4232 --Begin Bug#7205287 31-Jul-2008 barathsr
4233 write_log(FND_LOG.LEVEL_UNEXPECTED, 'Work Item Assignment Collector level from Profile (IEX: Territory Access Level) :'-- (IEX: Collector Access Level) :'
4234 || NVL(fnd_profile.value('IEX_ACCESS_LEVEL'), 'PARTY'));
4235 --End Bug#7205287 31-Jul-2008 barathsr
4236 EXCEPTION
4237 WHEN OTHERS THEN
4238 fnd_file.put_line(FND_FILE.LOG, 'Work Item Assignment Collector level raised exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
4239 END;
4240 -- End for bug # 5877743 on 28/02/2007 by gnramasa
4241 -- Start for bug 8708271 multi level strategy
4242 /* begin
4243 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
4244 into l_DefaultStrategyLevel,l_StrategyLevelName
4245 from iex_app_preferences_vl
4246 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = l_Yes;
4247 write_log(FND_LOG.LEVEL_STATEMENT, 'Current Strategy Level ' || l_DefaultStrategyLevel || ' , ' || l_StrategyLevelName);
4248
4249 -- End for bug # 5487449 on 28/08/2006 by gnramasa
4250 EXCEPTION
4251 WHEN OTHERS THEN
4252 fnd_file.put_line(FND_FILE.LOG, 'Strategy Level Rised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
4253 END; */
4254
4255 begin
4256
4257 select DEFINE_PARTY_RUNNING_LEVEL,DEFINE_OU_RUNNING_LEVEL
4258 into l_party_override,l_org_override
4259 from IEX_QUESTIONNAIRE_ITEMS;
4260
4261 write_log(FND_LOG.LEVEL_STATEMENT, 'Party Level Strategy Override and Operating Unit Level Override values ' || l_party_override || ' , ' || l_org_override);
4262
4263 EXCEPTION
4264 WHEN OTHERS THEN
4265 fnd_file.put_line(FND_FILE.LOG, 'Strategy Level Rised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
4266 END;
4267 -- end for bug 8708271 multi level strategy
4268 END IEX_STRATEGY_CNT_PUB;