[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_PUB
Source
1 PACKAGE BODY IEX_STRATEGY_PUB AS
2 /* $Header: iexpstpb.pls 120.11.12010000.11 2009/11/30 14:25:33 pnaveenk ship $ */
3 /*
4 * This procedure needs to be called with an itemtype and workflow process
5 * which'll launch workflow .Start Workflow will call workflow based on
6 * Meth_flag in methodology base table
7 */
8
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_STRATEGY_PUB';
10
11
12 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
13 PG_DEBUG NUMBER ;
14
15 PROCEDURE create_strategy
16 (
17 P_Api_Version_Number IN NUMBER,
18 P_Init_Msg_List IN VARCHAR2 ,
19 P_Commit IN VARCHAR2 ,
20 p_validation_level IN NUMBER ,
21 X_Return_Status OUT NOCOPY VARCHAR2,
22 X_Msg_Count OUT NOCOPY NUMBER,
23 X_Msg_Data OUT NOCOPY VARCHAR2,
24 p_DelinquencyID IN number,
25 p_ObjectType IN varchar2,
26 p_ObjectID IN number,
27 p_Strategy_Temp_ID IN number := 0
28 ) IS
29
30 l_result VARCHAR2(10);
31 l_error_msg VARCHAR2(2000);
32 l_return_status VARCHAR2(20);
33 l_msg_count NUMBER;
34 l_msg_data VARCHAR2(2000);
35 l_api_name VARCHAR2(100) ;
36 l_api_version_number CONSTANT NUMBER := 2.0;
37
38 vStrategyStatus VARCHAR2(30);
39
40
41 fdelinquencyId number;
42 fPartyCustId number;
43 fCustAccountId number;
44 fCustomerSiteUseId number;
45 fTransactionId number;
46 fPaymentScheduleid number;
47 fObjectId number;
48 fobjectType varchar2(40);
49 fScoreValue number;
50 fJTFObjectId number;
51 fJTFobjectType varchar2(40);
52 fStrategyLevel number;
53
54 l_ObjectType VARCHAR2(30);
55 l_strategy_id number;
56 l_strategy_template_id number;
57 l_object_version_number number := 2.0;
58
59 c_DelSelect varchar2(1000) ;
60 c_Bankruptcy varchar2(1000);
61 C_WriteOff varchar2(1000) ;
62 C_Repossession varchar2(1000) ;
63 C_Litigation varchar2(1000) ;
64 C_Party varchar2(1000) ;
65 C_IEX_Account varchar2(1000) ;
66 C_IEX_BILLTO varchar2(1000) ;
67
68 --Start bug 6723540 gnramasa 02 Jan 08
69 C_Cont_Bankruptcy varchar2(1000);
70 C_Cont_WriteOff varchar2(1000) ;
71 C_Cont_Repossession varchar2(1000) ;
72 C_Cont_Litigation varchar2(1000) ;
73 --End bug 6723540 gnramasa 02 Jan 08
74
75 l_stry_cnt_rec IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE ;
76
77 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
78
79 Cursor c_score_exists( p_object_id number, p_object_type varchar2) is
80 select score_value
81 from iex_score_histories
82 where score_object_id = p_object_id
83 and score_object_code = p_object_type
84 order by creation_date desc;
85
86 -- Start Added for bug 6359338 gnramasa 23-Aug-07
87 Cursor c_score_exists_del(p_object_id number, p_object_type varchar2, p_object_id2 number, p_object_type2 varchar2) is
88 select score_value
89 from iex_score_histories
90 where score_object_id in (p_object_id, p_object_id2)
91 and score_object_code in (p_object_type, p_object_type2)
92 order by creation_date desc;
93 -- End Added for bug 6359338 gnramasa 23-Aug-07
94
95 TYPE c_strategy_existsCurTyp IS REF CURSOR; -- weak
96 c_strategy_exists c_strategy_existsCurTyp; -- declare cursor variable
97
98
99 l_default_rs_id number ;
100 l_resource_id NUMBER;
101 l_StrategyTempID number;
102 b_Skip varchar2(10);
103
104 l_Init_Msg_List VARCHAR2(10) ;
105 l_Commit VARCHAR2(10) ;
106 l_validation_level NUMBER ;
107
108 -- Start Added for bug 6359338 gnramasa 23-Aug-07
109 l_StrategyLevel VARCHAR2(20);
110 l_ObjectId NUMBER;
111 l_payment_schedule_id NUMBER;
112 l_Score_level VARCHAR2(20);
113 -- End Added for bug 6359338 gnramasa 23-Aug-07
114
115 BEGIN
116 -- initialize variable
117 l_Init_Msg_List := P_Init_Msg_List;
118 l_Commit := P_Commit;
119 l_validation_level := p_validation_level;
120 if (l_Init_msg_List is null) then
121 l_Init_Msg_List := FND_API.G_FALSE;
122 end if;
123 if (l_Commit is null) then
124 l_Commit := FND_API.G_FALSE;
125 end if;
126 if (l_validation_level is null) then
127 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
128 end if;
129
130 l_api_name := 'CREATE_STRATEGY';
131
132 --Start adding for bug 8834310 gnramasa 26th Aug 09
133 if (p_Strategy_temp_id is null) or (p_Strategy_temp_id = 0) then
134 begin
135 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
136 into l_DefaultStrategyLevel
137 from iex_app_preferences_b
138 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' and org_id is null; -- Changed for bug 8708271 multi level strategy
139 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
140 IEX_DEBUG_PUB.LogMessage( 'Default StrategyLevel ' || l_DefaultStrategyLevel);
141 END IF;
142 EXCEPTION
143 WHEN OTHERS THEN
144 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
145 IEX_DEBUG_PUB.LogMessage( 'Strategy Level Rised Exception ');
146 END IF;
147 l_DefaultStrategyLevel := 40;
148 END;
149 else
150 begin
151 select strategy_level
152 into l_DefaultStrategyLevel
153 from iex_strategy_templates_b
154 where strategy_temp_id = p_Strategy_temp_id;
155 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
156 IEX_DEBUG_PUB.LogMessage( 'Strategy template :' || p_Strategy_temp_id || ' ,StrategyLevel :' || l_DefaultStrategyLevel);
157 END IF;
158 EXCEPTION
159 WHEN OTHERS THEN
160 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
161 IEX_DEBUG_PUB.LogMessage( 'Strategy template Level Rised Exception ');
162 END IF;
163 l_DefaultStrategyLevel := 40;
164 END;
165 end if;
166 --End adding for bug 8834310 gnramasa 26th Aug 09
167
168 c_DelSelect :=
169 ' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
170 ' d.transaction_id, d.payment_schedule_id, ' ||
171 ' d.delinquency_id object_id, ''DELINQUENT'' object_type , ' ||
172 ' d.score_value ' ||
173 ' , 40 strategy_level, d.delinquency_id jtf_object_id, ''IEX_DELINQUENCY'' jtf_object_type ' ||
174 ' from iex_delinquencies d where (d.status = ''DELINQUENT'' ' ||
175 ' or d.status = ''PREDELINQUENT'') ' ||
176 ' and d.delinquency_id = :pObjectID ';
177
178 c_Bankruptcy :=
179 ' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
180 ' d.transaction_id, d.payment_schedule_id, ' ||
181 ' bankruptcy_id object_id, ''BANKRUPTCY'' object_type, ' ||
182 ' d.score_value ' ||
183 ' , NULL strategy_level, bankruptcy_id jtf_object_id, ''IEX_BANKRUPTCY'' jtf_object_type ' ||
184 ' from iex_delinquencies d, iex_bankruptcies b where (d.status = ''DELINQUENT'' ' ||
185 ' or d.status = ''PREDELINQUENT'') ' ||
186 ' and d.delinquency_id = b.delinquency_id ' ||
187 ' and d.delinquency_id = :p_DelinquencyID ' ||
188 ' and b.bankruptcy_id = :p_ObjectID ';
189
190 C_WriteOff :=
191 ' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
192 ' d.transaction_id, d.payment_schedule_id, ' ||
193 ' writeoff_id object_id, ''WRITEOFF'' object_type, ' ||
194 ' d.score_value ' ||
195 ' , NULL strategy_level, writeoff_id jtf_object_id, ''IEX_WRITEOFF'' jtf_object_type ' ||
196 ' from iex_delinquencies d, iex_writeoffs b where (d.status = ''DELINQUENT'' ' ||
197 ' or d.status = ''PREDELINQUENT'') ' ||
198 ' and d.delinquency_id = b.delinquency_id ' ||
199 ' and d.delinquency_id = :p_DelinquencyID ' ||
200 ' and b.writeoff_id = :p_ObjectID ';
201
202 C_Repossession :=
203 ' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
204 ' d.transaction_id, d.payment_schedule_id, ' ||
205 ' repossession_id object_id, ''REPOSSESSION'' object_type, ' ||
206 ' d.score_value ' ||
207 ' , NULL strategy_level, repossession_id jtf_object_id, ''IEX_REPOSSESSION'' jtf_object_type ' ||
208 ' from iex_delinquencies d, iex_repossessions b where (d.status = ''DELINQUENT'' ' ||
209 ' or d.status = ''PREDELINQUENT'') ' ||
210 ' and d.delinquency_id = b.delinquency_id ' ||
211 ' and d.delinquency_id = :p_DelinquencyID ' ||
212 ' and b.repossession_id = :p_ObjectID ';
213
214 C_Litigation :=
215 ' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
216 ' d.transaction_id, d.payment_schedule_id, ' ||
217 'litigation_id object_id, ''LITIGATION'' object_type, ' ||
218 ' d.score_value ' ||
219 ' , NULL strategy_level, litigation_id jtf_object_id, ''IEX_LITIGATION'' jtf_object_type ' ||
220 ' from iex_delinquencies d, iex_litigations b where (d.status = ''DELINQUENT'' ' ||
221 ' or d.status = ''PREDELINQUENT'') ' ||
222 ' and d.delinquency_id = b.delinquency_id ' ||
223 ' and d.delinquency_id = :p_DelinquencyID ' ||
224 ' and b.litigation_id = :p_ObjectID ';
225
226 C_Party :=
227 'select d.party_cust_id, null, null, null, null, null, ' ||
228 ' d.PARTY_CUST_ID object_id, ''PARTY'' object_type, null' ||
229 ' , 10 strategy_level, d.PARTY_CUST_ID jtf_object_id, ''PARTY'' jtf_object_type' ||
230 ' from iex_delinquencies d' ||
231 ' where (d.status = ''DELINQUENT'' ' ||
232 ' or d.status = ''PREDELINQUENT'') ' ||
233 ' and d.party_cust_id = :pObjectID ' ||
234 ' group by d.party_cust_id ';
235
236 C_IEX_Account :=
237 ' select d.party_cust_id, d.cust_account_id, null, null, null, null, ' ||
238 ' d.cust_account_id object_id, ''ACCOUNT'' object_type, null, ' ||
239 ' 20 strategy_level, d.cust_account_id jtf_object_id, ''IEX_ACCOUNT'' jtf_object_type ' ||
240 ' from iex_delinquencies d ' ||
241 ' where (d.status = ''DELINQUENT'' ' ||
242 ' or d.status = ''PREDELINQUENT'') ' ||
243 ' and d.cust_account_id = :pObjectID ' ||
244 ' group by d.party_cust_id, d.cust_account_id ';
245
246 C_IEX_BILLTO :=
247 ' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id , null, null, null, ' ||
248 ' d.customer_site_use_id object_id, ''BILL_TO'' object_type, null, ' ||
249 ' 30 strategy_level, d.customer_site_use_id jtf_object_id, ''IEX_BILLTO'' jtf_object_type ' ||
250 ' from iex_delinquencies d ' ||
251 ' where (d.status = ''DELINQUENT'' ' ||
252 ' or d.status = ''PREDELINQUENT'') ' ||
253 ' and d.customer_site_use_id = :pObjectID ' ||
254 ' group by d.party_cust_id, d.cust_account_id, d.customer_site_use_id ';
255
256 --Start bug 6723540 gnramasa 02 Jan 08
257 C_Cont_Bankruptcy :=
258 ' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
259 ' NULL transaction_id, NULL payment_schedule_id, ' ||
260 ' bankruptcy_id object_id, ''BANKRUPTCY'' object_type, ' ||
261 ' NULL score_value ' ||
262 ' , NULL strategy_level, bankruptcy_id jtf_object_id, ''IEX_BANKRUPTCY'' jtf_object_type ' ||
263 ' from iex_bankruptcies where bankruptcy_id = :p_ObjectID ';
264
265 C_Cont_WriteOff :=
266 ' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
267 ' NULL transaction_id, NULL payment_schedule_id, ' ||
268 ' writeoff_id object_id, ''WRITEOFF'' object_type, ' ||
269 ' NULL score_value ' ||
270 ' , NULL strategy_level, writeoff_id jtf_object_id, ''IEX_WRITEOFF'' jtf_object_type ' ||
271 ' from iex_writeoffs where writeoff_id = :p_ObjectID ';
272
273 C_Cont_Repossession :=
274 ' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
275 ' NULL transaction_id, NULL payment_schedule_id, ' ||
276 ' repossession_id object_id, ''REPOSSESSION'' object_type, ' ||
277 ' NULL score_value ' ||
278 ' , NULL strategy_level, repossession_id jtf_object_id, ''IEX_REPOSSESSION'' jtf_object_type ' ||
279 ' from iex_repossessions where repossession_id = :p_ObjectID ';
280
281 C_Cont_Litigation :=
282 ' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
283 ' NULL transaction_id, NULL payment_schedule_id, ' ||
284 ' litigation_id object_id, ''LITIGATION'' object_type, ' ||
285 ' NULL score_value ' ||
286 ' , NULL strategy_level, litigation_id jtf_object_id, ''IEX_LITIGATION'' jtf_object_type ' ||
287 ' from iex_litigations where litigation_id = :p_ObjectID ';
288 --End bug 6723540 gnramasa 02 Jan 08
289
290 l_stry_cnt_rec := IEX_STRATEGY_TYPE_PUB.INST_STRY_CNT_REC;
291
292 l_default_rs_id := fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE');
293 l_resource_id := fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE');
294 b_Skip := 'F';
295
296
297 -- Standard Start of API savepoint
298 SAVEPOINT CREATE_STRATEGY_PUB;
299
300 -- Initialize API return status to SUCCESS
301 l_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 /*
304 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
305 return;
306 end if;
307 */
308
309 /* check the default profile valuse */
310 /* Check the required profiles for Strategy Concurrent before starting */
311 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
312 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
313 IEX_DEBUG_PUB.LogMessage( 'Strategy creation aborted. ' );
314 IEX_DEBUG_PUB.LogMessage( 'Strategy Disabled by Profile ');
315 END IF;
316 return;
317 end if;
318
319 if (l_DefaultStrategyLevel = 50) Then
320 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
321 IEX_DEBUG_PUB.LogMessage( 'Strategy creation stopped. ' );
322 IEX_DEBUG_PUB.LogMessage( 'No Default Strategy Run Level from IEX_APP_PREFERENCES ');
323 END IF;
324 b_Skip := 'T';
325 end if;
326
327 l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
328 if (l_StrategyTempID = 0) Then
329 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
330 IEX_DEBUG_PUB.LogMessage( 'Strategy creation stopped. ' );
331 IEX_DEBUG_PUB.LogMessage( 'No Default Strategy Template Profile ');
332 END IF;
333 b_Skip := 'T';
334 end if;
335
336 if (l_default_rs_ID = 0) Then
337 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
338 IEX_DEBUG_PUB.LogMessage( 'Strategy creation stopped. ' );
339 IEX_DEBUG_PUB.LogMessage( 'Strategy Default Resource Profile not set. ');
340 IEX_DEBUG_PUB.LogMessage( 'Default Resource need to have view access all customers, if security enabled ');
341 END IF;
342 b_Skip := 'T';
343 end if;
344
345 if (l_resource_ID = 0) Then
346 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
347 IEX_DEBUG_PUB.LogMessage( 'Strategy creation stopped. ' );
348 IEX_DEBUG_PUB.LogMessage( 'Strategy Fulfilment Resource Profile not set. ');
349 IEX_DEBUG_PUB.LogMessage( 'Fulfilment Resource should be configured for fulfilment ');
350 END IF;
351 b_Skip := 'T';
352 end if;
353
354 if (b_Skip = 'T') then
355 /* retcode := '2'; */
356 return;
357 end if;
358
359
360 l_objectType := UPPER(p_ObjectType);
361
362
363 -- Initialize message list IF p_init_msg_list is set to TRUE.
364 IF FND_API.to_Boolean( l_init_msg_list ) THEN
365 FND_MSG_PUB.initialize;
366 END IF;
367
368 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
369 IEX_DEBUG_PUB.LogMessage(
370 debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name || ' Start',
371 print_date => 'Y');
372 END IF;
373
374 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
375 IEX_DEBUG_PUB.LogMessage(
376 debug_msg => '1. D.ID= ' || p_delinquencyID || ' OID= ' || P_objectid || ' OT.= ' || P_objectType,
377 print_date => 'Y');
378 END IF;
379
380 -- Initialize API return status to SUCCESS
381 x_return_status := FND_API.G_RET_STS_SUCCESS;
382
383 --Start bug 6798118 gnramasa 05 Feb 08
384 If l_ObjectType in ('PARTY','ACCOUNT','BILL_TO','DELINQUENT') THEN
385 if (l_ObjectType = 'PARTY') then
386 -- IF PG_DEBUG < 10 THEN
387 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
388 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || C_Party);
389 END IF;
390 Execute Immediate C_Party into fPartyCustID, fCustAccountID, fCustomerSiteUseId,
391 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
392 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
393 using p_ObjectID;
394 elsif (l_ObjectType = 'ACCOUNT') then
395 -- IF PG_DEBUG < 10 THEN
396 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
397 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || C_IEX_Account);
398 END IF;
399 Execute Immediate C_IEX_Account into fPartyCustID, fCustAccountID, fCustomerSiteUseId,
400 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
401 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
402 using p_ObjectID;
403 elsif (l_ObjectType = 'BILL_TO') then
404 -- IF PG_DEBUG < 10 THEN
405 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
406 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || C_IEX_BILLTO);
407 END IF;
408 Execute Immediate C_IEX_BILLTO into fPartyCustID, fCustAccountID, fCustomerSiteUseId,
409 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
410 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
411 using p_ObjectID;
412 elsif (l_objectType = 'DELINQUENT') then
413 -- IF PG_DEBUG < 10 THEN
414 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
415 iex_debug_pub.logmessage('create_strategy: ' || 'STEP 35 Query: ' || c_DelSelect );
416 END IF;
417 Execute Immediate c_DelSelect into fPartyCustID, fcustAccountID, fCustomerSiteUseId,
418 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
419 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
420 using p_ObjectID;
421
422 end if;
423 ELSE
424 --Start bug 6723540 gnramasa 02 Jan 08
425 if p_DelinquencyID IS NOT NULL then
426 BEGIN
427 if (l_ObjectType = 'BANKRUPTCY') then
428 -- IF PG_DEBUG < 10 THEN
429 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
430 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || c_Bankruptcy);
431 END IF;
432 Execute Immediate c_Bankruptcy into fPartyCustID, fcustAccountID, fCustomerSiteUseId,
433 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
434 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
435 using p_DelinquencyID, p_ObjectID;
436
437 elsif (l_ObjectType = 'WRITEOFF') then
438 -- IF PG_DEBUG < 10 THEN
439 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
440 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || c_WriteOff);
441 END IF;
442 Execute Immediate c_WriteOff into fPartyCustId, fcustAccountID, fCustomerSiteUseId,
443 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
444 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
445 using p_DelinquencyID, p_ObjectID;
446
447 elsif (l_ObjectType = 'REPOSSESSION') then
448 -- IF PG_DEBUG < 10 THEN
449 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
450 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || c_Repossession);
451 END IF;
452 Execute Immediate c_Repossession into fPartyCustId, fCustAccountID, fCustomerSiteUseId,
453 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
454 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
455 using p_DelinquencyID, p_ObjectID;
456
457 elsif (l_ObjectType = 'LITIGATION') then
458 -- IF PG_DEBUG < 10 THEN
459 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
460 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || c_Litigation);
461 END IF;
462 Execute Immediate c_Litigation into fPartyCustID, fCustAccountId, fCustomerSiteUseId,
463 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
464 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
465 using p_DelinquencyID, p_ObjectID;
466
467
468 else
469 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
470 IEX_DEBUG_PUB.LogMessage('ERROR: IEX_UNKNOWN_OBJTYPE ' || l_objectType);
471 END IF;
472
473 FND_MESSAGE.Set_Name('IEX', 'IEX_UNKNOWN_OBJTYPE');
474 FND_MESSAGE.Set_Token('OBJECT_TYPE', l_ObjectType);
475 FND_MSG_PUB.Add;
476
477 RAISE FND_API.G_EXC_ERROR;
478 return;
479 end if;
480
481 EXCEPTION
482 When NO_DATA_FOUND then
483 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
484 IEX_DEBUG_PUB.LogMessage('ERROR: IEX_OBJECT_NOT_EXISTS' || l_objectType);
485 END IF;
486
487 FND_MESSAGE.Set_Name('IEX', 'IEX_OBJECT_NOT_EXISTS');
488 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(fObjectID));
489 FND_MSG_PUB.Add;
490
491 RAISE FND_API.G_EXC_ERROR;
492 return;
493
494 END;
495 elsif p_ObjectID IS NOT NULL then
496 begin
497 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
498 IEX_DEBUG_PUB.logmessage('p_ObjectID IS NOT NULL, p_ObjectID : ' || p_ObjectID );
499 END IF;
500 if (l_ObjectType = 'BANKRUPTCY') then
501 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
502 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || C_Cont_Bankruptcy);
503 END IF;
504 Execute Immediate C_Cont_Bankruptcy into fPartyCustID, fcustAccountID, fCustomerSiteUseId,
505 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
506 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
507 using p_ObjectID;
508
509 elsif (l_ObjectType = 'WRITEOFF') then
510 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
511 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || C_Cont_WriteOff);
512 END IF;
513 Execute Immediate C_Cont_WriteOff into fPartyCustId, fcustAccountID, fCustomerSiteUseId,
514 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
515 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
516 using p_ObjectID;
517
518 elsif (l_ObjectType = 'REPOSSESSION') then
519 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
520 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || C_Cont_Repossession);
521 END IF;
522 Execute Immediate C_Cont_Repossession into fPartyCustId, fCustAccountID, fCustomerSiteUseId,
523 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
524 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
525 using p_ObjectID;
526
527 elsif (l_ObjectType = 'LITIGATION') then
528 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
529 IEX_DEBUG_PUB.logmessage('create_strategy: ' || 'STEP 35 Query: ' || C_Cont_Litigation);
530 END IF;
531 Execute Immediate C_Cont_Litigation into fPartyCustID, fCustAccountId, fCustomerSiteUseId,
532 fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
533 fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
534 using p_ObjectID;
535
536 else
537 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
538 IEX_DEBUG_PUB.LogMessage(
539 debug_msg => 'ERROR: IEX_UNKNOWN_OBJTYPE ' || l_objectType,
540 print_date => 'Y');
541 END IF;
542
543 FND_MESSAGE.Set_Name('IEX', 'IEX_UNKNOWN_OBJTYPE');
544 FND_MESSAGE.Set_Token('OBJECT_TYPE', l_ObjectType);
545 FND_MSG_PUB.Add;
546
547 RAISE FND_API.G_EXC_ERROR;
548 return;
549 end if;
550
551 EXCEPTION
552 When NO_DATA_FOUND then
553 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
554 IEX_DEBUG_PUB.LogMessage(
555 debug_msg => 'ERROR: IEX_OBJECT_NOT_EXISTS' || l_objectType,
556 print_date => 'Y');
557 END IF;
558
559 FND_MESSAGE.Set_Name('IEX', 'IEX_OBJECT_NOT_EXISTS');
560 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(fObjectID));
561 FND_MSG_PUB.Add;
562
563 RAISE FND_API.G_EXC_ERROR;
564 return;
565
566 END;
567
568 end if; -- p_DelinquencyID IS NOT NULL
569 END IF;
570 --End bug 6798118 gnramasa 05 Feb 08
571
572 /* Create the strategy record */
573 l_stry_cnt_rec.cust_account_id := fCustAccountID;
574 l_stry_cnt_rec.party_cust_id := fPartyCustId;
575 l_stry_cnt_rec.customer_site_use_id := fCustomerSiteUseId;
576 l_stry_cnt_rec.delinquency_id := fDelinquencyId;
577 l_stry_cnt_rec.transaction_id := fTransactionid;
578 l_stry_cnt_rec.object_id := fObjectId;
579 l_stry_cnt_rec.object_type := fObjectType;
580 -- l_stry_cnt_rec.score_value := fScoreValue; -- Commented by gnramasa for bug 6359338 23-Aug-07
581 fStrategyLevel := l_DefaultStrategyLevel;
582 l_stry_cnt_rec.strategy_level := fStrategyLevel;
583 l_stry_cnt_rec.jtf_object_id := fJTFObjectId;
584 l_stry_cnt_rec.jtf_object_type := fJTFObjectType;
585
586 --Select the strategy level score instead of the delinquency level score.
587 --Start added by gnramasa for bug 6359338 23-Aug-07
588 fScoreValue := '';
589 if l_ObjectType in ('LITIGATION', 'REPOSSESSION', 'BANKRUPTCY', 'WRITEOFF') then
590 BEGIN
591 select preference_value
592 into l_StrategyLevel
593 from iex_app_preferences_b
594 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' and org_id is null; -- Changed for bug 8708271 multi level strategy
595 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
596 IEX_DEBUG_PUB.LogMessage( 'StrategyLevel ' || l_StrategyLevel);
597 END IF;
598 EXCEPTION
599 WHEN OTHERS THEN
600 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
601 IEX_DEBUG_PUB.LogMessage( 'Strategy Level Rised Exception ');
602 END IF;
603 l_StrategyLevel := 'CUSTOMER';
604 END;
605
606 if l_StrategyLevel = 'CUSTOMER' then
607 l_Score_level := 'PARTY';
608 l_ObjectId := l_stry_cnt_rec.party_cust_id;
609 --fJTFObjectId := l_stry_cnt_rec.party_cust_id;
610 --fJTFObjectType := 'PARTY';
611 --fObjectId := l_stry_cnt_rec.party_cust_id;
612 --fObjectType := 'PARTY';
613 elsif l_StrategyLevel = 'ACCOUNT' then
614 l_Score_level := 'IEX_ACCOUNT';
615 l_ObjectId := l_stry_cnt_rec.cust_account_id;
616 --fJTFObjectId := l_stry_cnt_rec.cust_account_id;
617 --fJTFObjectType := 'IEX_ACCOUNT';
618 --fObjectId := l_stry_cnt_rec.cust_account_id;
619 --fObjectType := 'IEX_ACCOUNT';
620 elsif l_StrategyLevel = 'BILL_TO' then
621 l_Score_level := 'IEX_BILLTO';
622 l_ObjectId := l_stry_cnt_rec.customer_site_use_id;
623 --fJTFObjectId := l_stry_cnt_rec.customer_site_use_id;
624 --fJTFObjectType := 'IEX_BILLTO';
625 --fObjectId := l_stry_cnt_rec.customer_site_use_id;
626 --fObjectType := 'IEX_BILLTO';
627 end if;
628
629 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
630 IEX_DEBUG_PUB.LogMessage( 'l_ObjectId : ' || l_ObjectId);
631 IEX_DEBUG_PUB.LogMessage( 'l_Score_level : ' || l_Score_level);
632 IEX_DEBUG_PUB.LogMessage( 'l_DefaultStrategyLevel : ' || l_DefaultStrategyLevel);
633 END IF;
634
635 if l_DefaultStrategyLevel <> 40 then -- This will pick the scores for all levels but not for delinquency
636 Open c_Score_Exists(l_ObjectId, l_Score_level);
637 fetch c_Score_Exists into fScoreValue;
638 Close c_Score_Exists;
639
640 IF fScoreValue IS NOT NULL then
641 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
642 IEX_DEBUG_PUB.LogMessage( ' Got New Score using c_Score_Exists '
643 || '; l_ObjectId = ' || l_ObjectId
644 || '; Score Value = ' || fScoreValue );
645 end if;
646 ELSE
647 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
648 IEX_DEBUG_PUB.LogMessage('Score not exist for this object');
649 IEX_DEBUG_PUB.LogMessage('create_strategy: ' || 'NO score available ');
650 END IF;
651 l_strategy_rec.score_value := 0;
652 END IF;
653
654 else
655 -- When looking for scores for delinquencies we should look for the newest score from either the payment schedule OR Delinquency
656 -- This is so because the first score of a delinquency is the score of the delinquent payment schedule
657 -- but if a customer scores the delinquency we should use the delinquency score to set the strategy
658 begin
659 select payment_schedule_id
660 into l_payment_schedule_id
661 from iex_delinquencies
662 where delinquency_id = l_stry_cnt_rec.delinquency_id;
663 exception
664 WHEN OTHERS THEN
665 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
666 IEX_DEBUG_PUB.LogMessage( 'While selecting payment_schedule_id Rised Exception ');
667 END IF;
668 end;
669
670 Open c_score_exists_del(l_payment_schedule_id, 'IEX_INVOICES', l_stry_cnt_rec.delinquency_id, 'IEX_DELINQUENCY');
671 fetch c_score_exists_del into fScoreValue;
672 Close c_score_exists_del;
673
674 IF fScoreValue IS NOT NULL then
675 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
676 IEX_DEBUG_PUB.LogMessage( ' Got New Score using c_Score_Exists_del '
677 || '; l_payment_schedule_id = ' || l_payment_schedule_id
678 || '; delinquency_id = ' || l_stry_cnt_rec.delinquency_id
679 || '; Score Value = ' || fScoreValue );
680 end if;
681 ELSE
682 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
683 IEX_DEBUG_PUB.LogMessage('Score not exist for this object');
684 IEX_DEBUG_PUB.LogMessage('create_strategy: ' || 'NO score available ');
685 END IF;
686 l_strategy_rec.score_value := 0;
687 END IF;
688
689 --fJTFObjectId := l_stry_cnt_rec.delinquency_id;
690 --fJTFObjectType := 'IEX_DELINQUENCY';
691 --fObjectId := l_stry_cnt_rec.delinquency_id;
692 --fObjectType := 'IEX_DELINQUENCY';
693 end if;
694
695 else
696 if (l_ObjectType = 'PARTY' OR l_ObjectType = 'IEX_ACCOUNT' OR l_ObjectType = 'IEX_BILLTO') AND (fScoreValue IS NULL) THEN
697 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
698 IEX_DEBUG_PUB.LogMessage('create_strategy: l_ObjectType = ' || l_ObjectType);
699 IEX_DEBUG_PUB.LogMessage('create_strategy: fScoreValue = ' || fScoreValue);
700 END IF;
701 BEGIN
702 Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
703 fetch c_Score_Exists into fScoreValue;
704 Close c_Score_Exists;
705
706 EXCEPTION
707 WHEN OTHERS THEN
708 -- IF PG_DEBUG < 10 THEN
709 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
710 IEX_DEBUG_PUB.LogMessage('create_strategy: ' || 'NO score available ');
711 END IF;
712 l_strategy_rec.score_value := 0;
713 END;
714 end if;
715 end if;
716 l_stry_cnt_rec.score_value := fScoreValue;
717 --l_stry_cnt_rec.object_id := fObjectId;
718 --l_stry_cnt_rec.object_type := fObjectType;
719 --l_stry_cnt_rec.jtf_object_id := fJTFObjectId;
720 --l_stry_cnt_rec.jtf_object_type := fJTFObjectType;
721 --End added by gnramasa for bug 6359338 23-Aug-07
722
723
724 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
725 IEX_DEBUG_PUB.LogMessage( debug_msg => '2. D.ID= '
726 || l_stry_cnt_rec.delinquency_id || ' OId= ' || l_stry_cnt_rec.object_id
727 || ' OT= ' || l_stry_cnt_rec.object_type || ' SV= ' || l_stry_cnt_rec.score_value,
728 print_date => 'Y');
729 END IF;
730
731 if (p_Strategy_temp_id is null) or (p_Strategy_temp_id = 0) then
732
733 IEX_STRATEGY_PUB.GetStrategyTempID(
734 x_return_status=>l_return_status,
735 p_stry_cnt_rec => l_stry_cnt_rec,
736 x_strategy_template_id => l_strategy_template_id
737 );
738
739 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
740 IEX_DEBUG_PUB.LogMessage( debug_msg => '3. D.ID= ' || l_stry_cnt_rec.delinquency_id ||
741 ' S.TID=' || l_strategy_template_id, print_date => 'Y');
742 END IF;
743
744 else
745 l_strategy_template_id := p_strategy_temp_id;
746 end if;
747
748 l_strategy_rec.strategy_template_id := l_strategy_template_id;
749 l_strategy_rec.delinquency_id := fdelinquencyId;
750 l_strategy_rec.party_id := fPartyCustId;
751 l_strategy_rec.cust_account_id := fCustAccountId;
752 l_strategy_rec.customer_site_use_id := fCustomerSiteUseId;
753 l_strategy_rec.next_work_item_id := null;
754 l_strategy_rec.object_id := fObjectID;
755 l_strategy_rec.object_type := fObjectType;
756 l_strategy_rec.status_code := 'OPEN';
757 l_strategy_rec.score_value := fScoreValue;
758 l_strategy_rec.checklist_yn := 'N';
759 l_strategy_rec.strategy_level := fStrategyLevel;
760 l_strategy_rec.jtf_object_id := fJTFObjectId;
761 l_strategy_rec.jtf_object_type := fJTFObjectType;
762
763 l_object_version_number := 1;
764
765 --Added for Bug# 6870773 by pnaveenk
766 if fnd_profile.value('IEX_PROC_STR_ORG')='Y' then
767 --l_strategy_rec.org_id:=fnd_profile.value('ORG_ID');
768 l_strategy_rec.org_id:=mo_global.get_current_org_id;
769
770 else
771 l_strategy_rec.org_id:=NULL;
772 end if;
773 -- start for bug 9044667 PNAVEENK
774 if (p_strategy_temp_id>0) and (l_strategy_rec.score_value is null) then --Added for bug#8997969 by schekuri on 09-Oct-2009`
775
776 l_strategy_rec.score_value := 9999;
777 end if;
778 -- end for 9044667
779 /*
780 IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 then
781 begin
782 Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
783 fetch c_Score_Exists into l_strategy_rec.score_value;
784 Close c_Score_Exists;
785 EXCEPTION
786 WHEN OTHERS THEN
787 -- IF PG_DEBUG < 10 THEN
788 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
789 IEX_DEBUG_PUB.LogMessage('create_strategy: ' || 'NO score available ');
790 END IF;
791 l_strategy_rec.score_value := 0;
792 END;
793 end if;
794 */
795 --End bug 6723540 gnramasa 02 Jan 08
796
797 vStrategyStatus := NULL;
798 IF l_ObjectType not in ('LITIGATION', 'REPOSSESSION', 'BANKRUPTCY', 'WRITEOFF') THEN
799 IF l_DefaultStrategyLevel = 10 THEN
800 OPEN c_strategy_exists
801 FOR
802 select status_code from iex_strategies where party_id = l_strategy_rec.party_id and
803 jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
804 and (checklist_yn IS null or checklist_yn = 'N') ;
805 elsif l_DefaultStrategyLevel = 20 THEN
806 OPEN c_strategy_exists
807 FOR
808 select status_code from iex_strategies where CUST_ACCOUNT_ID = l_strategy_rec.CUST_ACCOUNT_ID and
809 jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
810 and (checklist_yn IS null or checklist_yn = 'N') ;
811 elsif l_DefaultStrategyLevel = 30 THEN
812 OPEN c_strategy_exists
813 FOR
814 select status_code from iex_strategies where customer_site_use_ID = l_strategy_rec.customer_site_use_ID and
815 jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
816 and (checklist_yn IS null or checklist_yn = 'N') ;
817 ELSE
818 OPEN c_strategy_exists
819 FOR
820 select status_code from iex_strategies where
821 delinquency_id = l_strategy_rec.delinquency_id and
822 jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
823 and (checklist_yn IS null or checklist_yn = 'N') ;
824 END IF;
825 -- Open c_Strategy_Exists(l_strategy_rec.delinquency_id,
826 -- l_strategy_rec.jtf_object_id, l_strategy_rec.jtf_object_type);
827 --
828 fetch c_Strategy_Exists into vStrategyStatus;
829 Close C_Strategy_Exists;
830 End if;
831
832 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
833 IEX_DEBUG_PUB.LogMessage( debug_msg => '4. S.St=' || vStrategyStatus, print_date => 'Y');
834 END IF;
835
836 if (vStrategyStatus IS NULL) or vStrategyStatus in ('CANCELLED', 'CLOSED') then
837
838
839 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
840 IEX_DEBUG_PUB.LogMessage( debug_msg => '5. Create strategy ', print_date => 'Y');
841 END IF;
842
843 Begin
844
845 iex_strategy_pvt.create_strategy(
846 P_Api_Version_Number=>2.0,
847 p_commit => FND_API.G_FALSE,
848 P_Init_Msg_List =>FND_API.G_FALSE,
849 p_strategy_rec => l_strategy_rec,
850 x_return_status=>l_return_status,
851 x_msg_count=>l_msg_count,
852 x_msg_data=>l_msg_data,
853 x_strategy_id => l_strategy_id
854 );
855
856 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
857 IEX_DEBUG_PUB.LogMessage( debug_msg => 'Return status = ' || l_return_status, print_date => 'Y');
858 END IF;
859
860 if (x_return_status <> 'S') then
861 RAISE FND_API.G_EXC_ERROR;
862 end if;
863
864 l_strategy_rec.strategy_id := l_strategy_id;
865
866 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
867 IEX_DEBUG_PUB.LogMessage( debug_msg => 'Strategy created. id = ' || l_strategy_id, print_date => 'Y');
868 END IF;
869
870 EXCEPTION
871 WHEN OTHERS THEN
872 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
873 IEX_DEBUG_PUB.LogMessage( debug_msg => 'IEX_STRATEGY_CREATE_FAILED' || to_char(fObjectID), print_date => 'Y');
874 END IF;
875
876 FND_MESSAGE.Set_Name('IEX', 'IEX_STRATEGY_CREATE_FAILED');
877 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(fObjectID));
878 FND_MSG_PUB.Add;
879
880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
881
882 END;
883
884
885 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
886 IEX_DEBUG_PUB.LogMessage( debug_msg => '6. Create Workflow ' || l_strategy_rec.strategy_id, print_date => 'Y');
887 END IF;
888
889 BEGIN
890
891 iex_strategy_wf_pub.start_workflow(
892 P_Api_Version =>2.0,
893 P_Init_Msg_List => FND_API.G_FALSE,
894 p_commit => FND_API.G_FALSE,
895 p_strategy_rec => l_strategy_rec,
896 x_return_status=>l_return_status,
897 x_msg_count=>l_msg_count,
898 x_msg_data=>l_msg_data,
899 bConcProg => 'NO'
900 );
901
902 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
903 IEX_DEBUG_PUB.LogMessage( debug_msg => 'Return status = ' || l_return_status, print_date => 'Y');
904 END IF;
905 IEX_DEBUG_PUB.LogMessage('Return status = ' || l_return_status);
906
907 if (x_return_status <> 'S') then
908 RAISE FND_API.G_EXC_ERROR;
909 end if;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914 IEX_DEBUG_PUB.LogMessage( debug_msg => 'IEX_LAUNCH_WORKFLOW_FAILED' || to_char(fObjectID), print_date => 'Y');
915 END IF;
916
917 FND_MESSAGE.Set_Name('IEX', 'IEX_LAUNCH_WORKFLOW_FAILED');
918 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(fObjectID));
919 FND_MSG_PUB.Add;
920
921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
922
923 END;
924
925 -- Standard check for p_commit
926 IF FND_API.to_Boolean(l_commit) THEN
927 COMMIT WORK;
928 END IF;
929
930 ELSE
931 x_return_status := 'F';
932 IF l_ObjectType in ('LITIGATION', 'REPOSSESSION', 'BANKRUPTCY', 'WRITEOFF') THEN
933 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
934 IEX_DEBUG_PUB.LogMessage( debug_msg => 'Strategy already exists with OPEN status, so strategy is not created.', print_date => 'Y');
935 END IF;
936 ELSE
937 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
938 IEX_DEBUG_PUB.LogMessage( debug_msg => 'IEX_LAUNCH_WORKFLOW_FAILED' || to_char(fObjectID), print_date => 'Y');
939 END IF;
940
941 FND_MESSAGE.Set_Name('IEX', 'IEX_LAUNCH_WORKFLOW_FAILED');
942 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(fObjectID));
943 FND_MSG_PUB.Add;
944 END IF;
945
946 end if;
947 -- Standard call to get message count and if count is 1, get message info.
948 FND_MSG_PUB.Count_And_Get
949 ( p_count => x_msg_count,
950 p_data => x_msg_data
951 );
952
953 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
954 IEX_DEBUG_PUB.LogMessage( debug_msg => 'Delinquency cursor ends', print_date => 'Y');
955 END IF;
956
957 IEX_DEBUG_PUB.LogMessage('Delinquency cursor ends' );
958
959 EXCEPTION
960
961 WHEN FND_API.G_EXC_ERROR THEN
962 x_return_status := FND_API.G_RET_STS_ERROR;
963 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
964
965 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
968
969 WHEN OTHERS THEN
970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
971 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
972 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
973 END IF;
974 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
975
976 END create_strategy;
977
978
979 PROCEDURE GetStrategyTempID(
980 p_stry_cnt_rec in IEX_STRATEGY_TYPE_PUB.STRY_CNT_REC_TYPE,
981 x_return_status out NOCOPY varchar2,
982 x_strategy_template_id out NOCOPY number) IS
983 /*
984 CURSOR c_strategyTemp(pCategoryType varchar2, pDelinquencyID number) IS
985 SELECT ST.strategy_temp_id, ST.strategy_rank, OBF.ENTITY_NAME
986 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
987 where ST.category_type = pCategoryType and ST.Check_List_YN = 'N' AND
988 OBF.OBJECT_ID(+) = ST.Strategy_temp_ID and
989 OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
990 and not exists
991 (select 'x' from iex_strategies SS where SS.delinquency_id = pDelinquencyID
992 and SS.OBJECT_TYPE = pCategoryType)
993 ORDER BY strategy_rank DESC;
994 */
995 C_DynSql varchar2(1000);
996 v_Exists varchar2(20);
997 v_SkipTemp varchar2(20);
998
999 l_StrategyTempID number ;
1000 TYPE c_strategyTempCurTyp IS REF CURSOR; -- weak
1001 c_strategyTemp c_strategyTempCurTyp; -- declare cursor variable
1002 c_rec_Strategy_temp_id NUMBER;
1003 c_Rec_Strategy_Rank varchar2(10);
1004 c_Rec_ENTITY_NAME varchar2(30);
1005 c_Rec_active_flag varchar2(1);
1006
1007 -- clchang updated for sql bind var 05/07/2003
1008 vstr1 varchar2(100) ;
1009 vstr2 varchar2(100) ;
1010 vstr3 varchar2(100) ;
1011 vstr4 varchar2(100) ;
1012 vstr5 varchar2(100) ;
1013 vstr6 varchar2(100) ;
1014
1015 BEGIN
1016
1017 -- initialize variable
1018 l_StrategyTempID := 0;
1019 vstr1 := ' select 1 from ' ;
1020 vstr2 := ' where delinquency_id = :DelId ' ;
1021 vstr3 := ' and rownum < 2 ';
1022 vstr4 := ' where CUST_ACCOUNT_id = :AcctId ';
1023 vstr5 := ' where party_id = :PartyId ';
1024 vstr6 := ' where customer_site_use_id = :CustomerSiteUseId ';
1025
1026 --Start adding for bug 8834310 gnramasa 26th Aug 09
1027 begin
1028 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
1029 into l_DefaultStrategyLevel
1030 from iex_app_preferences_b
1031 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' and org_id is null; -- Changed for bug 8708271 multi level strategy
1032 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1033 IEX_DEBUG_PUB.LogMessage( 'Default StrategyLevel ' || l_DefaultStrategyLevel);
1034 END IF;
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1038 IEX_DEBUG_PUB.LogMessage( 'Strategy Level Rised Exception ');
1039 END IF;
1040 l_DefaultStrategyLevel := 40;
1041 END;
1042 --End adding for bug 8834310 gnramasa 26th Aug 09
1043
1044 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1045 IEX_DEBUG_PUB.LogMessage( 'GetStrategyTempID: Object_Type = '
1046 || p_stry_cnt_rec.object_type || ' Delinquency ID = ' || p_stry_cnt_rec.delinquency_id );
1047 END IF;
1048
1049 x_Strategy_Template_id := l_DefaultTempID;
1050
1051 -- start for bug 8970972 PNAVEENK
1052 --Start added by gnramasa for bug 6359338 23-Aug-07
1053 if p_stry_cnt_rec.object_type in ('LITIGATION', 'REPOSSESSION', 'BANKRUPTCY', 'WRITEOFF') then
1054 OPEN c_strategyTemp
1055 FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
1056 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
1057 where ST.category_type = p_stry_cnt_rec.object_type and ST.Check_List_YN = 'N' AND
1058 ((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> 'N') and
1059 st.strategy_level = l_DefaultStrategyLevel and
1060 OBF.OBJECT_ID(+) = ST.Strategy_temp_group_ID and
1061 OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
1062 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
1063 AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
1064 and exists (select 1 from iex_strategy_template_groups tg
1065 where tg.group_id = st.strategy_temp_group_id
1066 and tg.enabled_flag <> 'N'
1067 and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
1068 and trunc(nvl(tg.valid_to_dt,sysdate)) )
1069 ORDER BY to_number(strategy_rank) DESC;
1070 -- end for bug 8970972
1071 else
1072
1073
1074 IF l_DefaultStrategyLevel = 10 or l_DefaultStrategyLevel = 20 or l_DefaultStrategyLevel = 30 THEN
1075 OPEN c_strategyTemp
1076 FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
1077 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
1078 where ST.Check_List_YN = 'N' AND
1079 ((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> 'N') and
1080 st.strategy_level = l_DefaultStrategyLevel and
1081 OBF.OBJECT_ID(+) = ST.Strategy_temp_ID and
1082 OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
1083 and nvl(st.valid_from_dt, sysdate) <= nvl(st.valid_to_dt,SYSDATE)
1084 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
1085 AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
1086 ORDER BY to_number(strategy_rank) DESC;
1087 ELSE
1088 OPEN c_strategyTemp
1089 FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
1090 from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
1091 where ST.category_type = p_stry_cnt_rec.object_type and ST.Check_List_YN = 'N' AND
1092 ((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> 'N') and
1093 st.strategy_level = l_DefaultStrategyLevel and
1094 OBF.OBJECT_ID(+) = ST.Strategy_temp_ID and
1095 OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
1096 and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
1097 AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
1098 ORDER BY to_number(strategy_rank) DESC;
1099 /* in sync with iexpstcb.pls -- retrieve the same strategy template
1100 where ST.Check_List_YN = 'N' AND
1101 st.strategy_level = l_DefaultStrategyLevel and
1102 OBF.OBJECT_ID(+) = ST.Strategy_temp_ID and
1103 OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
1104 and not exists
1105 (select 'x' from iex_strategies SS where SS.delinquency_id = p_stry_cnt_rec.delinquency_id
1106 and SS.OBJECT_TYPE = p_stry_cnt_rec.object_type)
1107 ORDER BY to_number(strategy_rank) DESC;
1108 */
1109 END IF;
1110 end if;
1111 -- End added by gnramasa for bug 6359338 23-Aug-07
1112
1113
1114 /* Get the Strategy Template for requested Category Type */
1115 -- for c_rec in C_StrategyTemp(p_stry_cnt_rec.object_type, p_stry_cnt_rec.delinquency_id) loop
1116 LOOP
1117 FETCH C_StrategyTemp INTO c_rec_Strategy_temp_id, c_Rec_Strategy_Rank, c_Rec_ENTITY_NAME, c_rec_active_flag ;
1118
1119 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1120 IEX_DEBUG_PUB.LogMessage( ' Get Strategy Template: Inside Cursor. Entity Name '
1121 || c_Rec_Entity_Name
1122 || c_Rec_active_flag
1123 || ' Rank ' || c_Rec_Strategy_Rank);
1124 END IF;
1125
1126 if C_StrategyTemp%FOUND then
1127 v_SkipTemp := 'F';
1128 if c_Rec_Entity_Name is not null and c_rec_active_flag <> 'N' then
1129 BEGIN
1130 IF l_DefaultStrategyLevel = 40 THEN
1131 -- clchang updated for sql bind var 05/07/2003
1132 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1133 vstr2 ||
1134 vstr3;
1135 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.delinquency_id;
1136 /*
1137 C_DynSql :=
1138 ' select 1 from ' || c_Rec_ENTITY_NAME ||
1139 ' where delinquency_id = ' || p_stry_cnt_rec.delinquency_id ||
1140 ' and rownum < 2 ';
1141 */
1142 elsif l_DefaultStrategyLevel = 30 THEN
1143 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1144 vstr6 ||
1145 vstr3;
1146
1147 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.customer_site_use_id;
1148
1149 elsif l_DefaultStrategyLevel = 20 THEN
1150 -- clchang updated for sql bind var 05/07/2003
1151 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1152 vstr4 ||
1153 vstr3;
1154
1155 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.cust_account_id;
1156 /*
1157 C_DynSql :=
1158 ' select 1 from ' || c_Rec_ENTITY_NAME ||
1159 ' where CUST_ACCOUNT_id = ' || p_stry_cnt_rec.CUST_ACCOUNT_id || ' and rownum < 2 ';
1160 */
1161 else
1162 C_DynSql := vstr1 || c_Rec_ENTITY_NAME ||
1163 vstr5 ||
1164 vstr3;
1165 Execute Immediate c_DynSql into v_Exists using p_stry_cnt_rec.party_cust_id;
1166
1167 /*
1168 C_DynSql :=
1169 ' select 1 from ' || c_Rec_ENTITY_NAME ||
1170 ' where party_id = ' || p_stry_cnt_rec.PARTY_CUST_ID ||
1171 ' and rownum < 2 ';
1172 */
1173 end if;
1174 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1175 IEX_DEBUG_PUB.LogMessage( ' Dynamic SQL in GetStrategyTemplate '
1176 || c_DynSql );
1177 END IF;
1178
1179 --Execute Immediate c_DynSql into v_Exists;
1180
1181 EXCEPTION
1182 When no_data_found then
1183 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1184 IEX_DEBUG_PUB.LogMessage( ' Get Strategy Template: No Data Found: ' || c_DynSql );
1185 END IF;
1186 v_SkipTemp := 'T';
1187 END;
1188 end if;
1189
1190 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1191 IEX_DEBUG_PUB.LogMessage(' Get Strategy Template: v_SkipTemp ' || v_SkipTemp );
1192 END IF;
1193
1194 if v_SkipTemp <> 'T' then
1195
1196 if (p_stry_cnt_rec.score_value >= c_rec_strategy_rank) then
1197 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1198 IEX_DEBUG_PUB.LogMessage(' Get Strategy Template: c_rec_Strategy_temp_id: ' || c_rec_Strategy_temp_id );
1199 END IF;
1200
1201 x_strategy_template_id := c_rec_Strategy_temp_id;
1202 return;
1203 end if;
1204 end if;
1205 ELSE -- fetch failed, so exit loop
1206 EXIT;
1207 end if;
1208 end loop;
1209 close C_StrategyTemp;
1210 EXCEPTION
1211 when others then
1212 close C_StrategyTemp;
1213 END;
1214
1215
1216 /* Sets the strategy to ONHOLD/OPEN
1217 P_API_VERSION_NUMBER := 2.0
1218 Delinquency_iD = delinquency ID
1219 Object_Type = DELINQUENT, BANKRUPTCY, WRITEOFF, LITIGATION, REPOSSESSION
1220 Object_ID = DelinquencyID, BankruptcyID, writeoffid, litigationid, repossessionid
1221 */
1222
1223 PROCEDURE set_strategy
1224 (
1225 P_Api_Version_Number IN NUMBER,
1226 P_Init_Msg_List IN VARCHAR2,
1227 P_Commit IN VARCHAR2,
1228 p_validation_level IN NUMBER,
1229 X_Return_Status OUT NOCOPY VARCHAR2,
1230 X_Msg_Count OUT NOCOPY NUMBER,
1231 X_Msg_Data OUT NOCOPY VARCHAR2,
1232 p_DelinquencyID IN number,
1233 p_ObjectType IN varchar2,
1234 p_ObjectID IN number,
1235 p_Status IN varchar2
1236 ) IS
1237
1238 l_result VARCHAR2(10);
1239 l_error_msg VARCHAR2(2000);
1240 l_return_status VARCHAR2(20);
1241 l_msg_count NUMBER;
1242 l_msg_data VARCHAR2(2000);
1243 l_api_name VARCHAR2(100) ;
1244 l_api_version_number CONSTANT NUMBER := 2.0;
1245
1246 fdelinquencyId number;
1247 fPartyCustId number;
1248 fCustAccountId number;
1249 fCustomerSiteUseId number;
1250 fTransactionId number;
1251 fPaymentScheduleid number;
1252 fObjectId number;
1253 fobjectType varchar2(40);
1254 fScoreValue number;
1255 fStrategyID number;
1256 fStrategyVersionNumber number ;
1257 workItemId number; -- Added for bug#7416344 by PNAVEENK on 16-3-2009
1258 l_ObjectType VARCHAR2(30);
1259 l_object_version_number number ;
1260 vStrategyStatus VARCHAR2(30);
1261
1262 vDelinquencyStatus VARCHAR2(30);
1263
1264 --Begin bug#2369298 schekuri 24-Feb-2006
1265
1266 /*Cursor c_strategy_exists(p_delinquency_id number, p_object_id number, p_object_type varchar2) is
1267 select strategy_id, status_code, object_version_number from iex_strategies
1268 where ((delinquency_id = p_delinquency_id and
1269 object_id = p_object_id and object_type = p_object_type)) and (checklist_yn IS NULL or checkList_YN = 'N')
1270 and (status_code in ( 'OPEN', 'ONHOLD'));*/
1271 Cursor c_strategy_exists(p_object_id number, p_object_type varchar2) is
1272 select strategy_id, status_code, object_version_number from iex_strategies
1273 where object_id = p_object_id and
1274 object_type = p_object_type and
1275 (checklist_yn IS NULL or checkList_YN = 'N') and
1276 status_code in ( 'OPEN', 'ONHOLD');
1277 --End bug#2369298 schekuri 24-Feb-2006
1278
1279
1280 Cursor c_delinquency_status(p_delinquency_id number) is
1281 select status from iex_delinquencies
1282 where delinquency_id = p_delinquency_id;
1283
1284 -- Added for bug#7416344 by PNAVEENK on 16-3-2009
1285 Cursor C_Strategy_Status(fStrategyID number) is
1286 select work_item_id from iex_strategy_work_items where strategy_id=fStrategyID and status_code in ('OPEN');
1287 -- End for bug#7416344
1288
1289 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
1290
1291 l_Init_Msg_List VARCHAR2(10) ;
1292 l_Commit VARCHAR2(10) ;
1293 l_validation_level NUMBER ;
1294 BEGIN
1295 -- initialize variable
1296 l_Init_Msg_List := P_Init_Msg_List;
1297 l_Commit := P_Commit;
1298 l_validation_level := p_validation_level;
1299 if (l_Init_msg_List is null) then
1300 l_Init_Msg_List := FND_API.G_FALSE;
1301 end if;
1302 if (l_Commit is null) then
1303 l_Commit := FND_API.G_FALSE;
1304 end if;
1305 if (l_validation_level is null) then
1306 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
1307 end if;
1308
1309 l_api_name := 'SET_STRATEGY';
1310
1311 --Start adding for bug 8834310 gnramasa 26th Aug 09
1312 begin
1313 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
1314 into l_DefaultStrategyLevel
1315 from iex_app_preferences_b
1316 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' and org_id is null; -- Changed for bug 8708271 multi level strategy
1317 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1318 IEX_DEBUG_PUB.LogMessage( 'Default StrategyLevel ' || l_DefaultStrategyLevel);
1319 END IF;
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1323 IEX_DEBUG_PUB.LogMessage( 'Strategy Level Rised Exception ');
1324 END IF;
1325 l_DefaultStrategyLevel := 40;
1326 END;
1327 --End adding for bug 8834310 gnramasa 26th Aug 09
1328
1329 fStrategyVersionNumber := 2.0;
1330 l_object_version_number := 2.0;
1331
1332 x_return_status := FND_API.G_RET_STS_SUCCESS;
1333 -- Standard Start of API savepoint
1334
1335
1336 -- Initialize API return status to SUCCESS
1337 l_return_status := FND_API.G_RET_STS_SUCCESS;
1338 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
1339 return;
1340 end if;
1341 l_objectType := UPPER(p_ObjectType);
1342 -- commenting for bug 8864768 PNAVEENK
1343 /* if (l_DefaultStrategyLevel = 10) or (l_DefaultStrategyLevel = 20) or (l_DefaultStrategyLevel = 30) then
1344 if (l_ObjectType not in ('BILL_TO', 'ACCOUNT', 'PARTY')) THEN
1345 return;
1346 end if;
1347 end if; */
1348 -- end for bug 8864768
1349 SAVEPOINT SET_STRATEGY;
1350
1351 -- Initialize message list IF p_init_msg_list is set to TRUE.
1352 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1353 FND_MSG_PUB.initialize;
1354 END IF;
1355
1356 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1357 IEX_DEBUG_PUB.LogMessage(
1358 debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name || ' Start',
1359 print_date => 'Y');
1360 END IF;
1361
1362 -- Debug Message
1363 IEX_DEBUG_PUB.LogMessage('PUB:' || G_PKG_NAME || '.' || l_api_name || ' Start');
1364
1365
1366 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1367 IEX_DEBUG_PUB.LogMessage(
1368 debug_msg => '1. D.ID= ' || p_delinquencyID || ' OID= ' || P_objectid || ' OT.= ' || P_objectType,
1369 print_date => 'Y');
1370 END IF;
1371
1372 -- Initialize API return status to SUCCESS
1373 x_return_status := FND_API.G_RET_STS_SUCCESS;
1374 -- Debug Message
1375 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1376 IEX_DEBUG_PUB.LogMessage(
1377 debug_msg => '2. CHECK VALID STATUS => ' || p_Status,
1378 print_date => 'Y');
1379 END IF;
1380
1381 if (p_Status NOT IN ('OPEN', 'ONHOLD')) then
1382
1383 FND_MESSAGE.Set_Name('IEX', 'IEX_UNKNOWN_STATUS');
1384 FND_MESSAGE.Set_Token('STATUS', p_Status);
1385 FND_MSG_PUB.Add;
1386
1387 RAISE FND_API.G_EXC_ERROR;
1388 return;
1389
1390 end if;
1391
1392
1393 vDelinquencyStatus := NULL;
1394 Open C_Delinquency_Status (p_delinquencyid);
1395 fetch C_Delinquency_Status into vDelinquencyStatus;
1396 Close C_Delinquency_Status;
1397
1398 l_object_version_number := 1;
1399 vStrategyStatus := NULL;
1400
1401 --begin bug#2369298 schekuri 24-Feb-2006
1402 --Open c_Strategy_Exists(p_delinquencyid, p_objectid, p_objecttype);
1403 Open c_Strategy_Exists(p_objectid, p_objecttype);
1404 --end bug#2369298 schekuri 24-Feb-2006
1405 fetch c_Strategy_Exists into fStrategyID, vStrategyStatus, fStrategyVersionNumber;
1406 Close C_Strategy_Exists;
1407
1408 -- Added for bug#7416344 by PNAVEENK on 16-3-2009
1409 Open C_Strategy_Status(fStrategyID);
1410 fetch C_Strategy_Status into workItemId;
1411 Close C_Strategy_Status;
1412 -- End for bug#7416344
1413
1414 --begin bug#2369298 schekuri 24-Feb-2006
1415 --if strategy is already open no need to update it again if p_status is OPEN
1416 if p_Status = vStrategyStatus then
1417 return;
1418 end if;
1419 --end bug#2369298 schekuri 24-Feb-2006
1420
1421 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1422 IEX_DEBUG_PUB.LogMessage(
1423 debug_msg => '4. Current S.St=' || vStrategyStatus ,
1424 print_date => 'Y');
1425 END IF;
1426
1427 if ((fStrategyID IS NOT NULL) and vStrategyStatus NOT IN ('CLOSED', 'CANCELLED')) then
1428
1429 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1430 IEX_DEBUG_PUB.LogMessage(
1431 debug_msg => '5. Update strategy ' || fStrategyID,
1432 print_date => 'Y');
1433 END IF;
1434
1435 l_strategy_rec.strategy_id := fStrategyId;
1436 l_strategy_rec.object_version_number := fStrategyVersionNumber;
1437 l_strategy_rec.status_code := p_status;
1438
1439 Begin
1440
1441 iex_strategy_pvt.update_strategy(
1442 P_Api_Version_Number=>2.0,
1443 p_commit => FND_API.G_FALSE,
1444 P_Init_Msg_List =>FND_API.G_FALSE,
1445 p_strategy_rec => l_strategy_rec,
1446 x_return_status=>l_return_status,
1447 x_msg_count=>l_msg_count,
1448 x_msg_data=>l_msg_data,
1449 xo_object_version_number => l_object_version_number
1450 );
1451
1452 if (x_return_status <> 'S') then
1453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1454 end if;
1455
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1459 IEX_DEBUG_PUB.LogMessage(
1460 debug_msg => 'IEX_STRATEGY_UPDATE_FAILED' || fObjectID,
1461 print_date => 'Y');
1462 END IF;
1463
1464 FND_MESSAGE.Set_Name('IEX', 'IEX_STRATEGY_UPDATE_FAILED');
1465 FND_MSG_PUB.ADD;
1466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1467
1468 END;
1469
1470 -- Standard check for p_commit
1471 IF FND_API.to_Boolean(l_commit) THEN
1472 COMMIT WORK;
1473 END IF;
1474
1475 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1476 strategy_id => fStrategyId,
1477 status => p_status ) ;
1478
1479 -- Added for bug#7416344 by PNAVEENK on 16-3-2009
1480 IF workItemID is not null THEN
1481 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(workItemID);
1482 END IF;
1483 -- End for bug#7416344
1484
1485 ELSE
1486
1487 if (vDelinquencyStatus = 'PREDELINQUENT') then
1488 return;
1489 end if;
1490
1491 x_return_status := 'F';
1492
1493 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1494 IEX_DEBUG_PUB.LogMessage(
1495 debug_msg => 'IEX_NO_STRATEGIES_EXIST ' || fObjectID,
1496 print_date => 'Y');
1497 END IF;
1498
1499 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_STRATEGIES_EXIST');
1500 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(fObjectID));
1501 FND_MSG_PUB.ADD;
1502
1503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1504
1505 end if;
1506
1507 -- Standard call to get message count and if count is 1, get message info.
1508 FND_MSG_PUB.Count_And_Get
1509 ( p_count => x_msg_count,
1510 p_data => x_msg_data
1511 );
1512
1513 IEX_DEBUG_PUB.LogMessage('Delinquency cursor ends' );
1514
1515
1516 EXCEPTION
1517
1518 WHEN FND_API.G_EXC_ERROR THEN
1519 x_return_status := FND_API.G_RET_STS_ERROR;
1520 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1521
1522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1524 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1525
1526 WHEN OTHERS THEN
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1528 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1529 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1530 END IF;
1531 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1532
1533 END;
1534
1535
1536 FUNCTION GetDefaultStrategyTempID return NUMBER IS
1537 l_StrategyTempID number;
1538 lCursorStrategyTempID number;
1539 Cursor C_getFirstTempID IS
1540 Select Strategy_Temp_ID FROM IEX_STRATEGY_TEMPLATES_B where
1541 Check_List_YN = 'N';
1542 BEGIN
1543 l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
1544 if (l_StrategyTempID = 0) Then
1545 Open C_getFirstTempID;
1546 fetch C_getFirstTempID into lCursorStrategyTempID;
1547 if C_getFirstTempID%FOUND then
1548 l_StrategyTempID := lCursorStrategyTempID;
1549 end if;
1550 Close C_getFirstTempID;
1551 end if;
1552 return l_StrategyTempID;
1553 END;
1554
1555 PROCEDURE GetStrategyCurrentWorkItem
1556 (
1557 p_DelinquencyID IN number,
1558 p_ObjectType IN varchar2,
1559 p_ObjectID IN number,
1560 x_StrategyID OUT NOCOPY number,
1561 x_StrategyName OUT NOCOPY varchar2,
1562 x_WorkItemID OUT NOCOPY number,
1563 x_WorkItemName OUT NOCOPY VARCHAR2,
1564 X_Return_Status OUT NOCOPY VARCHAR2,
1565 X_Msg_Count OUT NOCOPY NUMBER,
1566 X_Msg_Data OUT NOCOPY VARCHAR2
1567 ) IS
1568
1569 l_result VARCHAR2(10);
1570 l_error_msg VARCHAR2(2000);
1571 l_return_status VARCHAR2(20);
1572 l_msg_count NUMBER;
1573 l_msg_data VARCHAR2(2000);
1574 l_api_name VARCHAR2(100) ;
1575 l_api_version_number CONSTANT NUMBER := 2.0;
1576
1577 fStrategyID number;
1578 fWorkItemId number;
1579 fWorkItemName varchar2(240);
1580 fStrategyName varchar2(240);
1581 fStrategyVersionNumber number ;
1582
1583 l_ObjectType VARCHAR2(30);
1584 l_object_version_number number ;
1585
1586 Cursor c_strategy(p_delinquency_id number, p_object_id number, p_object_type varchar2) is
1587 select s.strategy_id, st.strategy_name, s.next_work_item_id, t.name from iex_strategies s,
1588 iex_strategy_templates_vl st,
1589 iex_strategy_work_items w,
1590 iex_stry_temp_work_items_vl t
1591 where ((s.delinquency_id = p_delinquency_id and
1592 s.object_id = p_object_id and s.object_type = p_object_type)) and
1593 (s.checklist_yn IS NULL or s.checkList_YN = 'N')
1594 and s.strategy_template_id = st.strategy_temp_id(+)
1595 and s.next_work_item_id = w.work_item_id
1596 and w.work_item_template_id = t.work_item_temp_id(+)
1597 order by s.creation_date desc;
1598
1599 --Start bug 6723540 gnramasa 02 Jan 08
1600 Cursor c_cont_strategy(p_object_id number, p_object_type varchar2) is
1601 select s.strategy_id, st.strategy_name, s.next_work_item_id, t.name from iex_strategies s,
1602 iex_strategy_templates_vl st,
1603 iex_strategy_work_items w,
1604 iex_stry_temp_work_items_vl t
1605 where (s.object_id = p_object_id and s.object_type = p_object_type) and
1606 (s.checklist_yn IS NULL or s.checkList_YN = 'N')
1607 and s.strategy_template_id = st.strategy_temp_id(+)
1608 and s.next_work_item_id = w.work_item_id
1609 and w.work_item_template_id = t.work_item_temp_id(+)
1610 order by s.creation_date desc;
1611 --End bug 6723540 gnramasa 02 Jan 08
1612
1613 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
1614
1615 begin
1616 -- initialize variables
1617 l_api_name := 'GetStrategyCurrentWorkItem';
1618 fStrategyVersionNumber := 2.0;
1619 l_object_version_number := 2.0;
1620
1621 x_return_status := 'T';
1622 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
1623 return;
1624 end if;
1625
1626 FND_MSG_PUB.initialize;
1627
1628 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1629 IEX_DEBUG_PUB.LogMessage(
1630 debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name || ' Start',
1631 print_date => 'Y');
1632 END IF;
1633
1634
1635 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1636 IEX_DEBUG_PUB.LogMessage(
1637 debug_msg => '1. D.ID= ' || p_delinquencyID || ' OID= ' || P_objectid || ' OT.= ' || P_objectType,
1638 print_date => 'Y');
1639 END IF;
1640
1641 -- Initialize API return status to SUCCESS
1642 x_return_status := FND_API.G_RET_STS_SUCCESS;
1643
1644 --Start bug 6723540 gnramasa 02 Jan 08
1645 if p_delinquencyid is not null then
1646 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1647 IEX_DEBUG_PUB.LogMessage(debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name ||' : p_delinquencyid = ' || p_delinquencyid,
1648 print_date => 'Y');
1649 END IF;
1650 Open c_Strategy(p_delinquencyid, p_objectid, p_objecttype);
1651 fetch c_Strategy into fStrategyID, fStrategyName, fWorkItemId, fWorkItemName;
1652 Close C_Strategy;
1653 elsif p_objectid is not null then
1654 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1655 IEX_DEBUG_PUB.LogMessage(debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name ||' : p_objectid = ' || p_objectid,
1656 print_date => 'Y');
1657 END IF;
1658 Open c_cont_Strategy(p_objectid, p_objecttype);
1659 fetch c_cont_Strategy into fStrategyID, fStrategyName, fWorkItemId, fWorkItemName;
1660 Close c_cont_Strategy;
1661 else
1662 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1663 IEX_DEBUG_PUB.LogMessage(debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name ||' : p_delinquencyid and p_objectid is NULL',
1664 print_date => 'Y');
1665 END IF;
1666 return;
1667 end if;
1668 --End bug 6723540 gnramasa 02 Jan 08
1669
1670 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1671 IEX_DEBUG_PUB.LogMessage(
1672 debug_msg => '4. WorkItemName=' || fWorkItemName,
1673 print_date => 'Y');
1674 END IF;
1675
1676 if (fStrategyID > 0) then
1677 x_strategyId := fStrategyId;
1678 x_strategyName := fStrategyName;
1679 x_workitemID := fWorkItemID;
1680 x_workItemName := fWorkItemName;
1681 else
1682 x_return_status := 'F';
1683
1684 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1685 IEX_DEBUG_PUB.LogMessage(debug_msg => 'IEX_NO_STRATEGY_EXISTS ' || p_DelinquencyID,
1686 print_date => 'Y');
1687 END IF;
1688
1689 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_STRATEGIES_EXIST');
1690 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(p_DelinquencyID));
1691 FND_MSG_PUB.ADD;
1692
1693 RAISE FND_API.G_EXC_ERROR;
1694
1695 end if;
1696
1697 EXCEPTION
1698 WHEN NO_DATA_FOUND then
1699 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1700 IEX_DEBUG_PUB.LogMessage(debug_msg => 'IEX_NO_STRATEGIES_EXIST ' || p_DelinquencyID,
1701 print_date => 'Y');
1702 END IF;
1703
1704 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_STRATEGIES_EXIST');
1705 FND_MESSAGE.Set_Token('OBJECT_ID', to_char(p_DelinquencyID));
1706 FND_MSG_PUB.ADD;
1707
1708 x_return_status := FND_API.G_RET_STS_ERROR;
1709 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1710
1711 WHEN FND_API.G_EXC_ERROR THEN
1712 x_return_status := FND_API.G_RET_STS_ERROR;
1713 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1714
1715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1717 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1718
1719 WHEN OTHERS THEN
1720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1721 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1722 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1723 END IF;
1724 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1725 end;
1726
1727 /* CLOSES STRATEGY
1728 P_API_VERSION_NUMBER := 2.0
1729 Delinquency_iD = delinquency ID
1730 Object_Type = DELINQUENT, BANKRUPTCY, WRITEOFF, LITIGATION, REPOSSESSION
1731 Object_ID = DelinquencyID, BankruptcyID, writeoffid, litigationid, repossessionid
1732 */
1733
1734 PROCEDURE close_strategy
1735 (
1736 P_Api_Version_Number IN NUMBER,
1737 P_Init_Msg_List IN VARCHAR2,
1738 P_Commit IN VARCHAR2,
1739 p_validation_level IN NUMBER,
1740 X_Return_Status OUT NOCOPY VARCHAR2,
1741 X_Msg_Count OUT NOCOPY NUMBER,
1742 X_Msg_Data OUT NOCOPY VARCHAR2,
1743 p_DelinquencyID IN number,
1744 p_ObjectType IN varchar2,
1745 p_ObjectID IN number
1746 ) IS
1747
1748 l_result VARCHAR2(10);
1749 l_error_msg VARCHAR2(2000);
1750 l_return_status VARCHAR2(20);
1751 l_msg_count NUMBER;
1752 l_msg_data VARCHAR2(2000);
1753 l_api_name VARCHAR2(100) ;
1754 l_api_version_number CONSTANT NUMBER := 2.0;
1755
1756 fdelinquencyId number;
1757 fPartyCustId number;
1758 fCustAccountId number;
1759 fCustomerSiteUseId number;
1760 fTransactionId number;
1761 fPaymentScheduleid number;
1762 fObjectId number;
1763 fobjectType varchar2(40);
1764 fScoreValue number;
1765 fStrategyID number;
1766 fStrategyVersionNumber number ;
1767
1768 l_ObjectType VARCHAR2(30);
1769 l_object_version_number number ;
1770 vStrategyStatus VARCHAR2(30);
1771
1772 TYPE c_open_strategiesCurTyp IS REF CURSOR; -- weak
1773 c_open_strategies c_open_strategiesCurTyp; -- declare cursor variable
1774 /*
1775 Cursor c_strategy_exists(p_delinquency_id number, p_object_id number, p_object_type varchar2) is
1776 select strategy_id, status_code, object_version_number from iex_strategies where ((delinquency_id = p_delinquency_id and
1777 object_id = p_object_id and object_type = p_object_type)) and (checklist_yn IS NULL or checkList_YN = 'N');
1778 */
1779 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
1780
1781 l_Init_Msg_List VARCHAR2(10) ;
1782 l_Commit VARCHAR2(10) ;
1783 l_validation_level NUMBER ;
1784 BEGIN
1785 -- initialize variable
1786 l_Init_Msg_List := P_Init_Msg_List;
1787 l_Commit := P_Commit;
1788 l_validation_level := p_validation_level;
1789 if (l_Init_msg_List is null) then
1790 l_Init_Msg_List := FND_API.G_FALSE;
1791 end if;
1792 if (l_Commit is null) then
1793 l_Commit := FND_API.G_FALSE;
1794 end if;
1795 if (l_validation_level is null) then
1796 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
1797 end if;
1798
1799 l_api_name := 'CLOSE_STRATEGY';
1800
1801 --Start adding for bug 8834310 gnramasa 26th Aug 09
1802 begin
1803 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
1804 into l_DefaultStrategyLevel
1805 from iex_app_preferences_b
1806 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' and org_id is null; -- Changed for bug 8708271 multi level strategy
1807 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1808 IEX_DEBUG_PUB.LogMessage( 'Default StrategyLevel ' || l_DefaultStrategyLevel);
1809 END IF;
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1813 IEX_DEBUG_PUB.LogMessage( 'Strategy Level Rised Exception ');
1814 END IF;
1815 l_DefaultStrategyLevel := 40;
1816 END;
1817 --End adding for bug 8834310 gnramasa 26th Aug 09
1818
1819 fStrategyVersionNumber := 2.0;
1820 l_object_version_number := 2.0;
1821
1822 x_return_status := FND_API.G_RET_STS_SUCCESS;
1823 if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
1824 return;
1825 end if;
1826 -- Standard Start of API savepoint
1827 SAVEPOINT SET_STRATEGY;
1828
1829 -- Initialize API return status to SUCCESS
1830 l_return_status := FND_API.G_RET_STS_SUCCESS;
1831 l_objectType := UPPER(p_ObjectType);
1832
1833
1834 -- Initialize message list IF p_init_msg_list is set to TRUE.
1835 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1836 FND_MSG_PUB.initialize;
1837 END IF;
1838
1839 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1840 IEX_DEBUG_PUB.LogMessage(
1841 debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name || ' Start',
1842 print_date => 'Y');
1843 END IF;
1844
1845 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1846 IEX_DEBUG_PUB.LogMessage(
1847 debug_msg => '1. D.ID= ' || p_delinquencyID || ' OID= ' || P_objectid || ' OT.= ' || P_objectType,
1848 print_date => 'Y');
1849 END IF;
1850
1851 -- Initialize API return status to SUCCESS
1852 x_return_status := FND_API.G_RET_STS_SUCCESS;
1853
1854 l_object_version_number := 1;
1855 vStrategyStatus := NULL;
1856
1857
1858 IF l_DefaultStrategyLevel = 10 THEN
1859 OPEN c_open_strategies
1860 FOR
1861 select strategy_id, status_code, object_version_number from iex_strategies
1862 where (party_id = p_objectid and object_id = p_objectid and object_type = p_objecttype)
1863 and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
1864
1865 elsif l_DefaultStrategyLevel = 20 THEN
1866 OPEN c_open_strategies
1867 FOR
1868 select strategy_id, status_code, object_version_number from iex_strategies
1869 where (cust_account_id = p_objectid and object_id = p_objectid and object_type = p_objecttype)
1870 and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
1871 elsif l_DefaultStrategyLevel = 30 THEN
1872 OPEN c_open_strategies
1873 FOR
1874 select strategy_id, status_code, object_version_number from iex_strategies
1875 where (customer_site_use_id = p_objectid and object_id = p_objectid and object_type = p_objecttype)
1876 and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
1877 ELSE
1878 OPEN c_open_strategies
1879 FOR
1880 select strategy_id, status_code, object_version_number from iex_strategies
1881 where (delinquency_id = p_delinquencyid and object_id = p_objectid and object_type = p_objecttype)
1882 and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
1883 END IF;
1884 -- Open c_Strategy_Exists(p_delinquencyid, p_objectid, p_objecttype);
1885 fetch c_open_strategies into fStrategyID, vStrategyStatus, fStrategyVersionNumber;
1886 Close c_open_strategies;
1887
1888 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1889 IEX_DEBUG_PUB.LogMessage(
1890 debug_msg => '4. Current S.St=' || vStrategyStatus ,
1891 print_date => 'Y');
1892 END IF;
1893
1894 if ((fStrategyID IS NOT NULL) and vStrategyStatus NOT IN ('CLOSED', 'CANCELLED')) then
1895
1896 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1897 IEX_DEBUG_PUB.LogMessage(
1898 debug_msg => '5. Update strategy ' || fStrategyID,
1899 print_date => 'Y');
1900 END IF;
1901
1902 l_strategy_rec.strategy_id := fStrategyId;
1903 l_strategy_rec.object_version_number := fStrategyVersionNumber;
1904 l_strategy_rec.status_code := 'CLOSED';
1905
1906 Begin
1907
1908 iex_strategy_pvt.update_strategy(
1909 P_Api_Version_Number=>2.0,
1910 p_commit => FND_API.G_FALSE,
1911 P_Init_Msg_List =>FND_API.G_FALSE,
1912 p_strategy_rec => l_strategy_rec,
1913 x_return_status=>l_return_status,
1914 x_msg_count=>l_msg_count,
1915 x_msg_data=>l_msg_data,
1916 xo_object_version_number => l_object_version_number
1917 );
1918
1919 if (x_return_status <> 'S') then
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 end if;
1922
1923 EXCEPTION
1924 WHEN OTHERS THEN
1925 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1926 IEX_DEBUG_PUB.LogMessage(
1927 debug_msg => 'IEX_STRATEGY_UPDATE_FAILED' || fObjectID,
1928 print_date => 'Y');
1929 END IF;
1930
1931 FND_MESSAGE.Set_Name('IEX', 'IEX_STRATEGY_UPDATE_FAILED');
1932 FND_MSG_PUB.ADD;
1933
1934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1935
1936 END;
1937
1938 -- Standard check for p_commit
1939 IF FND_API.to_Boolean(l_commit) THEN
1940 COMMIT WORK;
1941 END IF;
1942
1943 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1944 strategy_id => fStrategyId,
1945 status => 'CLOSED' ) ;
1946
1947 end if;
1948 -- Standard call to get message count and if count is 1, get message info.
1949 FND_MSG_PUB.Count_And_Get
1950 ( p_count => x_msg_count,
1951 p_data => x_msg_data
1952 );
1953
1954 IEX_DEBUG_PUB.LogMessage('Delinquency cursor ends' );
1955
1956
1957 EXCEPTION
1958
1959 WHEN FND_API.G_EXC_ERROR THEN
1960 x_return_status := FND_API.G_RET_STS_ERROR;
1961 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1962
1963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1965 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1966 WHEN OTHERS THEN
1967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1968 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1969 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1970 END IF;
1971 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1972
1973 END;
1974
1975
1976 BEGIN
1977 -- initialize variables
1978 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1979
1980 l_MsgLevel := NVL(to_number(FND_PROFILE.VALUE('FND_AS_MSG_LEVEL_THRESHOLD')), FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1981 l_DefaultTempID := GetDefaultStrategyTempID;
1982
1983 --Start adding for bug 8834310 gnramasa 26th Aug 09
1984 /*
1985 begin
1986 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
1987 into l_DefaultStrategyLevel
1988 from iex_app_preferences_b
1989 where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' and org_id is null; -- Changed for bug 8708271 multi level strategy
1990 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1991 IEX_DEBUG_PUB.LogMessage( 'Default StrategyLevel ' || l_DefaultStrategyLevel);
1992 END IF;
1993 EXCEPTION
1994 WHEN OTHERS THEN
1995 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1996 IEX_DEBUG_PUB.LogMessage( 'Strategy Level Rised Exception ');
1997 END IF;
1998 l_DefaultStrategyLevel := 40;
1999 END;
2000 */
2001 --End adding for bug 8834310 gnramasa 26th Aug 09
2002
2003 END IEX_STRATEGY_PUB;