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