[Home] [Help]
Skip to content
PACKAGE BODY: APPS.LNS_BUS_EVENT_SUB_PVT
Source
1 PACKAGE BODY LNS_BUS_EVENT_SUB_PVT AS
2 /* $Header: LNS_BUS_EVENT_B.pls 120.7 2011/04/28 19:24:47 scherkas ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_BUS_EVENT_SUB_PVT';
8 G_LOG_ENABLED varchar2(5);
9 G_MSG_LEVEL NUMBER;
10
11
12 /*========================================================================
13 | PRIVATE PROCEDURE LogMessage
14 |
15 | DESCRIPTION
16 | This procedure logs debug messages to db and to CM log
17 |
18 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
19 | Delinquency_Create
20 |
21 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
22 | None
23 |
24 | PARAMETERS
25 | p_msg_level IN Debug msg level
26 | p_msg IN Debug msg itself
27 |
28 | KNOWN ISSUES
29 | None
30 |
31 | NOTES
32 | Any interesting aspect of the code in the package body which needs
33 | to be stated.
34 |
35 | MODIFICATION HISTORY
36 | Date Author Description of Changes
37 | 01-01-2004 scherkas Created
38 |
39 *=======================================================================*/
40 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
41 IS
42 BEGIN
43 if (p_msg_level >= G_MSG_LEVEL) then
44
45 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
46
47 end if;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR in LogMessage: ' || sqlerrm);
52 END;
53
54
55
56
57 /*========================================================================
58 | PUBLIC FUNCTION Delinquency_Create
59 |
60 | DESCRIPTION
61 | This function processes oracle.apps.iex.delinquency.create event
62 |
63 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
64 | None
65 |
66 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
67 | LogMessage
68 |
69 | PARAMETERS
70 | p_subscription_guid IN Standard in parameter
71 | p_event IN Standard in parameter
72 |
73 | KNOWN ISSUES
74 | None
75 |
76 | NOTES
77 | Any interesting aspect of the code in the package body which needs
78 | to be stated.
79 |
80 | MODIFICATION HISTORY
81 | Date Author Description of Changes
82 | 01-01-2004 scherkas Created
83 |
84 *=======================================================================*/
85 FUNCTION Delinquency_Create(p_subscription_guid In RAW, p_event IN OUT NOCOPY WF_EVENT_T)RETURN VARCHAR2
86 IS
87 /*-----------------------------------------------------------------------+
88 | Local Variable Declarations and initializations |
89 +-----------------------------------------------------------------------*/
90 l_api_name CONSTANT VARCHAR2(30) := 'Delinquency_Create';
91 l_return_status VARCHAR2(1);
92 l_msg_count NUMBER;
93 l_msg_data VARCHAR2(32767);
94 l_status varchar2(30);
95 l_loan_number varchar2(60);
96 l_loan_id number;
97 l_org_id number;
98 l_request_id number;
99 l_num_del_cr number;
100 l_num_del_upd number;
101 l_count number;
102 l_amortization_schedule_id number;
103 l_cust_trx_id number;
104 l_scoring_engine number;
105
106 /*-----------------------------------------------------------------------+
107 | Cursor Declarations |
108 +-----------------------------------------------------------------------*/
109
110 /* query for loans trx */
111 CURSOR loan_trx_cur(P_REQUEST_ID number) IS
112 select ams.LOAN_ID,
113 loan.loan_number,
114 loan.org_id,
115 ams.AMORTIZATION_SCHEDULE_ID,
116 del.transaction_id
117 from
118 lns_loan_headers_all loan,
119 lns_amortization_scheds ams,
120 IEX_DELINQUENCIES_ALL del
121 where
122 del.request_id = P_REQUEST_ID and
123 del.transaction_id in (ams.PRINCIPAL_TRX_ID, ams.INTEREST_TRX_ID, ams.FEE_TRX_ID) and
124 ams.loan_id = loan.loan_id;
125
126 BEGIN
127 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
128 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Received event ' || p_event.getEventName());
129
130 /* Established savepoint */
131 SAVEPOINT Delinquency_Create;
132 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Established savepoint');
133
134 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
135 l_num_del_cr := p_event.GetValueForParameter('NOOFDELCREATED');
136 l_num_del_upd := p_event.GetValueForParameter('NOOFDELUPDATED');
137
138 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Event parameters:');
139 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'request id: ' || l_request_id);
140 LogMessage(FND_LOG.LEVEL_STATEMENT, 'number of created del: ' || l_num_del_cr);
141 LogMessage(FND_LOG.LEVEL_STATEMENT, 'number of updated del: ' || l_num_del_upd);
142
143 if l_request_id is not null then
144
145 l_count := 0;
146
147 -- fix for bug 10121038
148 l_scoring_engine := NVL(FND_PROFILE.VALUE('LNS_SCORING_ENGINE'), 8);
149 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Scoring engine = ' || l_scoring_engine);
150
151 /* query for loans trx */
152 open loan_trx_cur(to_number(l_request_id));
153 LOOP
154
155 fetch loan_trx_cur into l_loan_id,
156 l_loan_number,
157 l_org_id,
158 l_amortization_schedule_id,
159 l_cust_trx_id;
160 exit when loan_trx_cur%NOTFOUND;
161
162 l_count := l_count + 1;
163 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Found trx #' || l_count);
164 LogMessage(FND_LOG.LEVEL_STATEMENT, 'cust_trx_id: ' || l_cust_trx_id);
165 LogMessage(FND_LOG.LEVEL_STATEMENT, 'amortization_schedule_id: ' || l_amortization_schedule_id);
166 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_id: ' || l_loan_id);
167 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_number: ' || l_loan_number);
168 LogMessage(FND_LOG.LEVEL_STATEMENT, 'org_id: ' || l_org_id);
169
170 MO_GLOBAL.INIT('LNS');
171 MO_GLOBAL.set_policy_context('S', l_org_id);
172
173 UPDATE_LOAN_STATUS_FROM_IEX(
174 P_API_VERSION => 1.0,
175 P_INIT_MSG_LIST => FND_API.G_FALSE,
176 P_COMMIT => FND_API.G_FALSE,
177 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
178 P_LOAN_ID => l_loan_id,
179 P_SCORING_ENGINE_ID => l_scoring_engine,
180 X_NEW_STATUS => l_status,
181 X_RETURN_STATUS => l_return_status,
182 X_MSG_COUNT => l_msg_count,
183 X_MSG_DATA => l_msg_data);
184
185 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188
189 END LOOP;
190
191 close loan_trx_cur;
192
193 end if;
194
195 -- commiting
196 if l_count > 0 then
197 COMMIT WORK;
198 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
199 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total processed ' || l_count || ' transactions');
200 else
201 LogMessage(FND_LOG.LEVEL_STATEMENT, 'No loan invoices found - no actions will be performed');
202 end if;
203
204 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully processed event ' || p_event.getEventName());
205 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
206 RETURN 'SUCCESS';
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 ROLLBACK TO Delinquency_Create;
211
212 WF_CORE.CONTEXT('LNS_BUS_EVENT_SUB_PVT', 'Delinquency_Create', p_event.getEventName(), p_subscription_guid);
213 WF_EVENT.setErrorInfo(p_event, 'ERROR');
214 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to process event ' || p_event.getEventName());
215
216 RETURN 'ERROR';
217
218 END Delinquency_Create;
219
220
221
222
223 PROCEDURE UPDATE_LOAN_STATUS_FROM_IEX(
224 P_API_VERSION IN NUMBER,
225 P_INIT_MSG_LIST IN VARCHAR2,
226 P_COMMIT IN VARCHAR2,
227 P_VALIDATION_LEVEL IN NUMBER,
228 P_LOAN_ID IN NUMBER,
229 P_SCORING_ENGINE_ID IN NUMBER,
230 X_NEW_STATUS OUT NOCOPY VARCHAR2,
231 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
232 X_MSG_COUNT OUT NOCOPY NUMBER,
233 X_MSG_DATA OUT NOCOPY VARCHAR2)
234 IS
235
236 /*-----------------------------------------------------------------------+
237 | Local Variable Declarations and initializations |
238 +-----------------------------------------------------------------------*/
239
240 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LOAN_STATUS_FROM_IEX';
241 l_api_version CONSTANT NUMBER := 1.0;
242 l_return_status VARCHAR2(1);
243 l_msg_count NUMBER;
244 l_msg_data VARCHAR2(32767);
245 l_version_number number;
246 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
247 l_score number;
248 l_index number := 0;
249 l_indexNo number := 1;
250 l_msg varchar2(4000) := null;
251 l_scoring_engine number;
252 l_status varchar2(30);
253 l_loan_status varchar2(30);
254
255 /*-----------------------------------------------------------------------+
256 | Cursor Declarations |
257 +-----------------------------------------------------------------------*/
258
259 -- getting loan version
260 CURSOR loan_version_cur(P_LOAN_ID number) IS
261 select OBJECT_VERSION_NUMBER, LOAN_STATUS
262 from LNS_LOAN_HEADERS_ALL
263 where LOAN_ID = P_LOAN_ID;
264
265 BEGIN
266
267 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
268
269 -- Standard start of API savepoint
270 SAVEPOINT UPDATE_LOAN_STATUS_FROM_IEX;
271 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
272
273 -- Standard call to check for call compatibility
274 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278 -- Initialize message list if p_init_msg_list is set to TRUE
279 IF FND_API.To_Boolean(p_init_msg_list) THEN
280 FND_MSG_PUB.initialize;
281 END IF;
282
283 -- Initialize API return status to success
284 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
285
286 -- START OF BODY OF API
287 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input loan_id = ' || P_LOAN_ID);
288 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input scoring_engine_id = ' || P_SCORING_ENGINE_ID);
289
290 if P_LOAN_ID is null then
291 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
292 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
293 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
294 FND_MSG_PUB.ADD;
295 RAISE FND_API.G_EXC_ERROR;
296 end if;
297
298 -- update status to paid off if the loan is already paid off
299 LNS_BILLING_BATCH_PUB.PROCESS_PAID_LOANS(
300 P_API_VERSION => 1.0,
301 P_INIT_MSG_LIST => FND_API.G_TRUE,
302 P_COMMIT => FND_API.G_TRUE,
303 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
304 P_LOAN_ID => P_LOAN_ID,
305 P_PAYOFF_DATE => sysdate,
306 X_RETURN_STATUS => l_return_status,
307 X_MSG_COUNT => l_msg_count,
308 X_MSG_DATA => l_msg_data);
309
310 -- getting loan version and current loan status
311 open loan_version_cur(P_LOAN_ID);
312 fetch loan_version_cur into l_version_number, l_loan_status;
313 close loan_version_cur;
314
315 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current status: ' || l_loan_status);
316
317 if l_loan_status = 'APPROVED' or l_loan_status = 'ACTIVE' or
318 l_loan_status = 'DELINQUENT' or l_loan_status = 'DEFAULT'
319 then
320
321 -- update status from funding
322 LNS_FUNDING_PUB.CHECK_FOR_VOIDED_INVOICES(
323 P_API_VERSION => 1.0,
324 P_INIT_MSG_LIST => FND_API.G_TRUE,
325 P_COMMIT => FND_API.G_TRUE,
326 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
327 P_LOAN_ID => P_LOAN_ID,
328 X_RETURN_STATUS => l_return_status,
329 X_MSG_COUNT => l_msg_count,
330 X_MSG_DATA => l_msg_data);
331
332 l_scoring_engine := P_SCORING_ENGINE_ID;
333 if l_scoring_engine is null then
334 l_scoring_engine := NVL(FND_PROFILE.VALUE('LNS_SCORING_ENGINE'), 8);
335 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Scoring engine = ' || l_scoring_engine);
336 end if;
337
338 /* calling scoring engine to get new loan status */
339 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling collections scoring engine to get new loan status...');
340
341 BEGIN
342
343 IEX_SCOREAPI_PUB.GETSCORESTATUS(
344 P_API_VERSION => 1.0,
345 P_INIT_MSG_LIST => FND_API.G_TRUE,
346 P_SCORE_ID => l_scoring_engine,
347 P_OBJECT_ID => P_LOAN_ID,
348 X_STATUS => l_status,
349 X_SCORE => l_score,
350 X_RETURN_STATUS => l_return_status,
351 X_MSG_COUNT => l_msg_count,
352 X_MSG_DATA => l_msg_data);
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 LogMessage(FND_LOG.LEVEL_ERROR, 'Collections scoring engine API is not installed. Please install it first.');
357 RAISE FND_API.G_EXC_ERROR;
358 END;
359
360 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
361
362 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
363
364 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to collections scoring engine failed with following error:');
365 while (l_indexNo <= l_msg_Count ) loop
366 fnd_msg_pub.get(l_indexNo, 'F', l_msg, l_index);
367 LogMessage(FND_LOG.LEVEL_UNEXPECTED, l_msg);
368 l_indexNo := l_indexNo + 1;
369 End Loop;
370
371 RAISE FND_API.G_EXC_ERROR;
372
373 END IF;
374
375 LogMessage(FND_LOG.LEVEL_STATEMENT, 'New score: ' || l_score);
376 LogMessage(FND_LOG.LEVEL_STATEMENT, 'New status: ' || l_status);
377
378 -- getting loan version and current loan status
379 open loan_version_cur(P_LOAN_ID);
380 fetch loan_version_cur into l_version_number, l_loan_status;
381 close loan_version_cur;
382
383 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current status: ' || l_loan_status);
384
385 if l_status is not null and l_loan_status <> l_status then
386
387 -- updating loan header table
388 l_loan_header_rec.loan_id := P_LOAN_ID;
389 l_loan_header_rec.LOAN_STATUS := l_status;
390
391 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating loan header...');
392
393 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
394 P_LOAN_HEADER_REC => l_loan_header_rec,
395 P_INIT_MSG_LIST => FND_API.G_TRUE,
396 X_RETURN_STATUS => l_return_status,
397 X_MSG_COUNT => l_msg_count,
398 X_MSG_DATA => l_msg_data);
399
400 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
401
402 IF l_return_status = 'S' THEN
403 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL with status ' || l_status);
404 ELSE
405 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
406 FND_MSG_PUB.Add;
407 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
408 RAISE FND_API.G_EXC_ERROR;
409 END IF;
410
411 end if;
412
413 end if;
414
415 if P_COMMIT = FND_API.G_TRUE then
416 COMMIT WORK;
417 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
418 end if;
419
420 -- END OF BODY OF API
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422 X_NEW_STATUS := l_status;
423
424 -- Standard call to get message count and if count is 1, get message info
425 FND_MSG_PUB.Count_And_Get(
426 p_encoded => FND_API.G_FALSE,
427 p_count => x_msg_count,
428 p_data => x_msg_data);
429
430 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
431
432 EXCEPTION
433 WHEN FND_API.G_EXC_ERROR THEN
434 ROLLBACK TO UPDATE_LOAN_STATUS_FROM_IEX;
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
437 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
439 ROLLBACK TO UPDATE_LOAN_STATUS_FROM_IEX;
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
442 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
443 WHEN OTHERS THEN
444 ROLLBACK TO UPDATE_LOAN_STATUS_FROM_IEX;
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
446 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
447 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
448 END IF;
449 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
450 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
451 END;
452
453
454
455 BEGIN
456
457 G_LOG_ENABLED := 'N';
458 G_MSG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
459
460 /* getting msg logging info */
461 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
462 /*
463 if (G_LOG_ENABLED = 'N') then
464 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
465 else
466 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
467 end if;
468 */
469 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
470 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
471
472 END LNS_BUS_EVENT_SUB_PVT; -- Package body