[Home] [Help]
PACKAGE BODY: APPS.LNS_WORK_FLOW
Source
1 PACKAGE BODY LNS_WORK_FLOW as
2 /* $Header: LNS_WORK_FLOW_B.pls 120.22.12020000.2 2012/07/19 21:01:21 scherkas ship $ */
3
4 /*========================================================================
5 | PUBLIC PROCEDURE SELECT_WF_PROCESS
6 |
7 | DESCRIPTION
8 | This process selects the process to run.
9 |
10 | PSEUDO CODE/LOGIC
11 |
12 | PARAMETERS
13 | P_PARAM1 IN Standard in parameter
14 | X_PARAM2 OUT NOCOPY Standard out parameter
15 |
16 | KNOWN ISSUES
17 | None
18 |
19 | NOTES
20 |
21 | MODIFICATION HISTORY
22 | Date Author Description of Changes
23 | 17-Jan-2005 GBELLARY Created
24 |
25 *=======================================================================*/
26
27
28 /*=======================================================================+
29 | Package Global Constants
30 +=======================================================================*/
31 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_WORKFLOW_PUB';
32 G_LOG_ENABLED varchar2(5);
33 G_MSG_LEVEL NUMBER;
34 g_org_id number;
35 --G_TRANSACTION_TYPE CONSTANT VARCHAR2(30):= FND_PROFILE.value('LNS_AME_TRANSACTION_TYPE');
36 G_LAST_ENTERED_NOTE VARCHAR2(2000);
37
38
39 /*========================================================================
40 | PRIVATE PROCEDURE LogMessage
41 |
42 | DESCRIPTION
43 | This procedure logs debug messages to db and to CM log
44 |
45 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
46 |
47 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
48 | None
49 |
50 | PARAMETERS
51 | p_msg_level IN Debug msg level
52 | p_msg IN Debug msg itself
53 |
54 | KNOWN ISSUES
55 | None
56 |
57 |
58 | NOTES
59 | Any interesting aspect of the code in the package body which needs
60 | to be stated.
61 |
62 | MODIFICATION HISTORY
63 | Date Author Description of Changes
64 | 17-Jan-2005 GBELLARY Created
65 |
66 *=======================================================================*/
67 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
68 IS
69 BEGIN
70 IF (p_msg_level >= G_MSG_LEVEL) then
71
72 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
73
74 END IF;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
79 END;
80
81
82 PROCEDURE SET_SESSION_CONTEXT(p_itemtype in varchar2,
83 p_itemkey in varchar2)
84 IS
85
86 /*-----------------------------------------------------------------------+
87 | Local Variable Declarations and initializations |
88 +-----------------------------------------------------------------------*/
89
90 l_api_name CONSTANT VARCHAR2(30) := 'SET_SESSION_CONTEXT';
91 l_user_id NUMBER;
92 l_user_name VARCHAR2(100);
93 l_resp_id NUMBER;
94 l_global_resp_id NUMBER;
95 l_resp_key VARCHAR2(30);
96 l_request_group_id NUMBER;
97 l_request_group_name VARCHAR2(30);
98
99 l_notification_id NUMBER;
100 l_index NUMBER;
101 l_RESPONDER VARCHAR2(320);
102 l_role varchar2(100);
103 l_DISPLAY_NAME varchar2(100);
104 l_DESCRIPTION varchar2(100);
105 l_NOTIFICATION_PREFERENCE varchar2(100);
106 l_LANGUAGE varchar2(100);
107 l_TERRITORY varchar2(100);
108 l_FAX varchar2(100);
109 l_EXPIRATION_DATE date;
110 l_STATUS varchar2(100);
111 l_ORIG_SYSTEM varchar2(100);
112 l_ORIG_SYSTEM_ID number;
113
114
115 /*-----------------------------------------------------------------------+
116 | Cursor Declarations |
117 +-----------------------------------------------------------------------*/
118
119 CURSOR csr_notification (p_itemtype varchar2, p_itemkey varchar2) IS
120 select NOTIFICATION_ID, responder
121 from wf_notifications
122 where MESSAGE_TYPE = p_itemtype and
123 ITEM_KEY = p_itemkey;
124
125 CURSOR csr_user_id (p_user_name varchar2) IS
126 select USER_ID
127 from fnd_user
128 where USER_NAME = p_user_name;
129
130 CURSOR csr_get_resp (p_user_id NUMBER, p_resp_id NUMBER) IS
131 select usr.user_name,
132 resp.responsibility_id,
133 resp.responsibility_key,
134 resp.request_group_id,
135 rgr.request_group_name
136 from fnd_user usr,
137 FND_USER_RESP_GROUPS urg,
138 fnd_responsibility resp,
139 FND_REQUEST_GROUPS rgr
140 where
141 usr.user_id = p_user_id and
142 usr.user_id = urg.user_id and
143 urg.responsibility_application_id = 206 and
144 urg.responsibility_id = nvl(p_resp_id, urg.responsibility_id) and
145 urg.responsibility_id = resp.responsibility_id and
146 resp.application_id = urg.responsibility_application_id and
147 rgr.request_group_id = resp.request_group_id and
148 rgr.application_id = urg.responsibility_application_id and
149 rgr.request_group_id in
150 (select rgu.request_group_id
151 from FND_CONCURRENT_PROGRAMS cp,
152 FND_REQUEST_GROUP_UNITS rgu
153 where
154 rgu.request_unit_id = cp.concurrent_program_id and
155 rgu.application_id = urg.responsibility_application_id and
156 cp.application_id = urg.responsibility_application_id and
157 cp.concurrent_program_name in ('LNS_BILLING','LNS_GL_TRANSFER'));
158
159 BEGIN
160
161 --fnd_global.apps_initialize(1013429, 62494, 206);
162
163 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' +');
164
165 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'p_itemtype: ' || p_itemtype);
166 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'p_itemkey: ' || p_itemkey);
167
168 -- set user from fnd_global first
169 l_user_id := fnd_global.user_id;
170 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '1. Trying to get user id from fnd_global.user_id = ' || l_user_id);
171
172 if l_user_id is null or l_user_id = -1 then
173
174 OPEN csr_notification(p_itemtype, p_itemkey);
175 FETCH csr_notification INTO l_notification_id, l_RESPONDER;
176 CLOSE csr_notification;
177
178 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_notification_id: ' || l_notification_id);
179 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_RESPONDER: ' || l_RESPONDER);
180
181 if l_RESPONDER is not null then
182
183 l_index := instr(l_RESPONDER, '@');
184 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_index: ' || l_index);
185
186 if l_index = 0 then
187
188 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '2. Trying to get user id from notification responder name...');
189 OPEN csr_user_id(l_RESPONDER);
190 FETCH csr_user_id INTO l_user_id;
191 CLOSE csr_user_id;
192 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_user_id: ' || l_user_id);
193
194 else
195
196 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '2. Trying to get user id from notification responder email...');
197 wf_directory.GETINFOFROMMAIL(MAILID => l_RESPONDER,
198 ROLE => l_ROLE,
199 DISPLAY_NAME => l_DISPLAY_NAME,
200 DESCRIPTION => l_DESCRIPTION,
201 NOTIFICATION_PREFERENCE => l_NOTIFICATION_PREFERENCE,
202 LANGUAGE => l_LANGUAGE,
203 TERRITORY => l_TERRITORY,
204 FAX => l_FAX,
205 EXPIRATION_DATE => l_EXPIRATION_DATE,
206 STATUS => l_STATUS,
207 ORIG_SYSTEM => l_ORIG_SYSTEM,
208 ORIG_SYSTEM_ID => l_ORIG_SYSTEM_ID);
209
210 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_ROLE = ' || l_ROLE);
211 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_DISPLAY_NAME = ' || l_DISPLAY_NAME);
212 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_DESCRIPTION = ' || l_DESCRIPTION);
213 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_NOTIFICATION_PREFERENCE = ' || l_NOTIFICATION_PREFERENCE);
214 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_EXPIRATION_DATE = ' || l_EXPIRATION_DATE);
215 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_STATUS = ' || l_STATUS);
216 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_ORIG_SYSTEM = ' || l_ORIG_SYSTEM);
217
218 if l_ROLE is not null then
219 OPEN csr_user_id(l_ROLE);
220 FETCH csr_user_id INTO l_user_id;
221 CLOSE csr_user_id;
222 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_user_id: ' || l_user_id);
223 end if;
224
225 end if;
226
227 end if;
228
229 if l_user_id is null or l_user_id = -1 then
230
231 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '3. Trying to get user id from LNS_CURRENT_USER_ID attribute...');
232 -- trying to get user id form LNS_CURRENT_USER_ID attribute
233 l_user_id := wf_engine.GetItemAttrNumber
234 ( itemtype => p_itemtype,
235 itemkey => p_itemkey,
236 aname => 'LNS_CURRENT_USER_ID');
237 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'From LNS_CURRENT_USER_ID = ' || l_user_id);
238 end if;
239
240 end if;
241
242 -- could not find user id
243 if l_user_id is null or l_user_id = -1 then
244 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Cannot set session context because cannot find user_id');
245 return;
246 end if;
247
248 -- set resp from fnd_global first
249 l_global_resp_id := fnd_global.resp_id;
250 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '4. Trying to get resp_id from fnd_global.resp_id = ' || l_global_resp_id);
251 OPEN csr_get_resp(l_user_id, l_global_resp_id);
252 FETCH csr_get_resp INTO
253 l_user_name,
254 l_resp_id,
255 l_resp_key,
256 l_request_group_id,
257 l_request_group_name;
258 CLOSE csr_get_resp;
259
260 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_user_name = ' || l_user_name);
261 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_resp_id = ' || l_resp_id);
262 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_resp_key = ' || l_resp_key);
263 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_request_group_id = ' || l_request_group_id);
264 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_request_group_name = ' || l_request_group_name);
265
266 if l_resp_id is null or l_resp_id = -1 then
267 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '5. Trying to get resp_id from sql...');
268
269 OPEN csr_get_resp(l_user_id, null);
270 FETCH csr_get_resp INTO
271 l_user_name,
272 l_resp_id,
273 l_resp_key,
274 l_request_group_id,
275 l_request_group_name;
276 CLOSE csr_get_resp;
277
278 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_user_name = ' || l_user_name);
279 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_resp_id = ' || l_resp_id);
280 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_resp_key = ' || l_resp_key);
281 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_request_group_id = ' || l_request_group_id);
282 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_request_group_name = ' || l_request_group_name);
283
284 end if;
285
286 if l_resp_id is null or l_resp_id = -1 then
287 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Cannot set session context because cannot find resp_id');
288 return;
289 end if;
290
291 if l_user_id is not null and l_user_id > -1 and l_resp_id is not null and l_resp_id > -1 then
292 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Setting session context with user_id = ' || l_user_id || ' and resp_id = ' || l_resp_id);
293 fnd_global.apps_initialize(l_user_id, l_resp_id, 206);
294 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Session context is set successfully!');
295 end if;
296
297 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' -');
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' ERROR: ' || sqlerrm);
302 END;
303
304
305
306 /*========================================================================
307 | PRIVATE PROCEDURE PROCESS_LOAN_STATUS_CHANGE
308 |
309 | DESCRIPTION
310 | This procedure logs debug messages to db and to CM log
311 |
312 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
313 |
314 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
315 | None
316 |
317 | PARAMETERS
318 | p_loan_id IN Loan Id
319 | p_from_status IN Old loan status
320 | p_to_status IN New Loan Status
321 |
322 | KNOWN ISSUES
323 | None
324 |
325 |
326 | NOTES
327 | Any interesting aspect of the code in the package body which needs
328 | to be stated.
329 |
330 | MODIFICATION HISTORY
331 | Date Author Description of Changes
332 | 17-Jan-2005 GBELLARY Created
333 |
334 *=======================================================================*/
335
336 PROCEDURE PROCESS_LOAN_STATUS_CHANGE( p_loan_id IN NUMBER
337 ,p_from_status IN VARCHAR2
338 ,p_to_status IN VARCHAR2) IS
339
340 /*-----------------------------------------------------------------------+
341 | Local Variable Declarations and initializations |
342 +-----------------------------------------------------------------------*/
343
344 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOAN_STATUS_CHANGE';
345 l_api_version CONSTANT NUMBER := 1.0;
346 l_event_name VARCHAR2(250);
347
348 /*-----------------------------------------------------------------------+
349 | Cursor Declarations |
350 +-----------------------------------------------------------------------*/
351 BEGIN
352 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
353
354 IF p_to_status = 'PENDING' then
355 l_event_name := 'LOAN_APPROVAL_PENDING';
356 ELSIF p_to_status = 'APPROVED' then
357 l_event_name := 'LOAN_APPROVAL_APPROVED';
358 ELSIF p_to_status = 'REJECTED' then
359 l_event_name := 'LOAN_APPROVAL_REJECTED';
360 ELSIF p_to_status = 'INCOMPLETE' then
361 l_event_name := 'LOAN_APPROVAL_NEEDINFO';
362 ELSIF p_to_status = 'DEFAULT' then
363 l_event_name := 'LOAN_DEFAULT';
364 ELSIF p_to_status = 'DELINQUENT' then
365 l_event_name := 'LOAN_DELINQUENT';
366 ELSIF p_to_status = 'PAIDOFF' then
367 l_event_name := 'LOAN_PAIDOFF';
368 ELSIF p_to_status = 'IN_FUNDING' then
369 l_event_name := 'LOAN_FUNDING_PENDING';
370 ELSIF p_from_status = 'IN_FUNDING'
371 AND p_to_status = 'ACTIVE' then
372 l_event_name := 'LOAN_FUNDING_SUCCESSFUL';
373 ELSIF p_from_status = 'IN_FUNDING'
374 AND p_to_status = 'FUNDING_ERROR' then
375 l_event_name := 'LOAN_FUNDING_ERROR';
376 ELSIF p_from_status in ('DEFAULT','DELINQUENT','PAIDOFF')
377 AND p_to_status = 'ACTIVE' then
378 l_event_name := 'LOAN_ACTIVE_AGAIN';
379 ELSIF p_to_status = 'PENDING_CANCELLATION' then
380 l_event_name := 'LOAN_DISB_CANCEL_PENDING';
381 END IF;
382
383 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_event_name: ' || l_event_name);
384
385
386 IF l_event_name is NOT NULL THEN
387 raise_event(p_event_name => l_event_name
388 ,p_loan_id => p_loan_id
389 ,p_from_status=> p_from_status);
390 END IF;
391 /* Commenting out this code since this is going to be moved to approval.
392 IF p_to_status = 'ACTIVE'
393 AND p_from_status NOT IN ('DEFAULT','DELINQUENT','PAIDOFF')
394 THEN
395 LNS_REP_UTILS.store_loan_agreement(p_loan_id => p_loan_id);
396 END IF;
397 */
398
399 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
400 EXCEPTION
401 WHEN OTHERS THEN
402 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
403 || ' Exception -');
404 RAISE;
405 END PROCESS_LOAN_STATUS_CHANGE;
406 PROCEDURE PROCESS_STATUS_CHANGE( p_loan_id IN NUMBER
407 ,p_column_name IN VARCHAR2
408 ,p_from_status IN VARCHAR2
409 ,p_to_status IN VARCHAR2) IS
410
411 /*-----------------------------------------------------------------------+
412 | Local Variable Declarations and initializations |
413 +-----------------------------------------------------------------------*/
414
415 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_STATUS_CHANGE';
416 l_api_version CONSTANT NUMBER := 1.0;
417 l_event_name VARCHAR2(250);
418
419 /*-----------------------------------------------------------------------+
420 | Cursor Declarations |
421 +-----------------------------------------------------------------------*/
422 BEGIN
423 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
424 IF p_column_name = 'LOAN_STATUS' then
425 process_loan_status_change(p_loan_id => p_loan_id
426 ,p_from_status => p_from_status
427 ,p_to_status => p_to_status);
428 ELSIF p_column_name = 'SECONDARY_STATUS' then
429
430 process_sec_status_change(p_loan_id => p_loan_id
431 ,p_from_status => p_from_status
432 ,p_to_status => p_to_status);
433 ELSE null;
434 END IF;
435 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
436 EXCEPTION
437 WHEN OTHERS THEN
438 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
439 || ' Exception -');
440 RAISE;
441 END PROCESS_STATUS_CHANGE;
442 PROCEDURE PROCESS_SEC_STATUS_CHANGE( p_loan_id IN NUMBER
443 ,p_from_status IN VARCHAR2
444 ,p_to_status IN VARCHAR2) IS
445
446 /*-----------------------------------------------------------------------+
447 | Local Variable Declarations and initializations |
448 +-----------------------------------------------------------------------*/
449
450 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SEC_STATUS_CHANGE';
451 l_api_version CONSTANT NUMBER := 1.0;
452 l_event_name VARCHAR2(250);
453 l_open_to_term_flag lns_loan_headers_all.open_to_term_flag%TYPE;
454 l_open_to_term_event lns_loan_headers_all.open_to_term_event%TYPE;
455
456 /*-----------------------------------------------------------------------+
457 | Cursor Declarations |
458 +-----------------------------------------------------------------------*/
459 CURSOR csr_loan_details IS
460 SELECT open_to_term_flag
461 ,open_to_term_event
462 FROM lns_loan_headers_all
463 WHERE loan_id = p_loan_id;
464
465 BEGIN
466 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
467 OPEN csr_loan_details;
468 FETCH csr_loan_details
469 INTO l_open_to_term_flag
470 ,l_open_to_term_event;
471 CLOSE csr_loan_details;
472 IF p_to_status = 'PENDING_CANCELLATION' then
473 l_event_name := 'LOAN_DISB_CANCEL_PENDING';
474 ELSIF p_to_status IN ('ALL_DISB_CANCELLED', 'REMAINING_DISB_CANCELLED') then
475 l_event_name := 'LOAN_DISB_CANCEL_APPROVED';
476 ELSIF p_to_status = 'MORE_INFO_REQUESTED'
477 AND p_from_status = 'PENDING_CANCELLATION' then
478 l_event_name := 'LOAN_DISB_CANCEL_INCOMPLETE';
479 ELSIF p_to_status NOT IN ('ALL_DISB_CANCELLED', 'REMAINING_DISB_CANCELLED')
480 AND p_from_status = 'PENDING_CANCELLATION' then
481 l_event_name := 'LOAN_DISB_CANCEL_REJECTED';
482 ELSIF p_to_status = 'FULLY_FUNDED'
483 AND l_open_to_term_flag = 'Y'
484 AND l_open_to_term_event <> 'AUTO_FINAL_DISBURSEMENT' then
485 l_event_name := 'LOAN_TERM_CONVERT_REQUIRED';
486 ELSIF p_to_status = 'PENDING_CONVERSION' then
487 l_event_name := 'LOAN_TERM_CONVERT_PENDING';
488 ELSIF p_to_status = 'CONVERTED_TO_TERM_PHASE'
489 AND l_open_to_term_flag = 'Y'
490 AND l_open_to_term_event = 'AUTO_FINAL_DISBURSEMENT' then
491 l_event_name := 'LOAN_TERM_AUTOCONVERT';
492 ELSIF p_to_status = 'CONVERTED_TO_TERM_PHASE' then
493 l_event_name := 'LOAN_TERM_CONVERT_APPROVED';
494 ELSIF p_to_status = 'MORE_INFO_REQUESTED'
495 AND p_from_status = 'PENDING_CONVERSION' then
496 l_event_name := 'LOAN_TERM_CONVERT_INCOMPLETE';
497 ELSIF p_to_status <> 'CONVERTED_TO_TERM_PHASE'
498 AND p_from_status = 'PENDING_CONVERSION' then
499 l_event_name := 'LOAN_TERM_CONVERT_REJECTED';
500 ELSIF p_to_status = 'IN_FUNDING' then
501 l_event_name := 'LOAN_FUNDING_PENDING';
502 ELSIF p_to_status IN ('PARTIALLY_FUNDED','FULLY_FUNDED') then
503 l_event_name := 'LOAN_FUNDING_SUCCESSFUL';
504 ELSIF p_to_status = 'FUNDING_ERROR' then
505 l_event_name := 'LOAN_FUNDING_ERROR';
506 END IF;
507 IF l_event_name is NOT NULL THEN
508 raise_event(p_event_name => l_event_name
509 ,p_loan_id => p_loan_id
510 ,p_from_status=> p_from_status);
511 END IF;
512 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
513 EXCEPTION
514 WHEN OTHERS THEN
515 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
516 || ' Exception -');
517 RAISE;
518 END PROCESS_SEC_STATUS_CHANGE;
519 /*========================================================================
520 | PRIVATE PROCEDURE RAISE_EVENT
521 |
522 | DESCRIPTION
523 | This procedure logs debug messages to db and to CM log
524 |
525 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
526 |
527 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
528 | None
529 |
530 | PARAMETERS
531 | p_loan_id IN Loan Id
532 | p_loan_status IN Loan Status
533 | x_error_code OUT Error Code
534 |
535 | KNOWN ISSUES
536 | None
537 |
538 |
539 | NOTES
540 | Any interesting aspect of the code in the package body which needs
541 | to be stated.
542 |
543 | MODIFICATION HISTORY
544 | Date Author Description of Changes
545 | 17-Jan-2005 GBELLARY Created
546 |
547 *=======================================================================*/
548
549 PROCEDURE RAISE_EVENT ( p_loan_id IN NUMBER
550 ,p_event_name IN VARCHAR2
551 ,p_from_status IN VARCHAR2 DEFAULT NULL) IS
552 /*-----------------------------------------------------------------------+
553 | Local Variable Declarations and initializations |
554 +-----------------------------------------------------------------------*/
555
556 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_EVENT';
557 l_api_version CONSTANT NUMBER := 1.0;
558 l_loan_number lns_loan_headers_all.loan_number%TYPE;
559 l_requested_amount lns_loan_headers_all.requested_amount%TYPE;
560 l_loan_description lns_loan_headers_all.loan_description%TYPE;
561 l_loan_class_code lns_loan_headers_all.loan_class_code%TYPE;
562 l_loan_type lns_loan_types.loan_type_name%TYPE;
563 l_loan_type_id lns_loan_types.loan_type_id%TYPE;
564 l_current_user_id lns_loan_headers_all.created_by%TYPE;
565 l_loan_formatted_amount VARCHAR2(50);
566 l_loan_undisbursed_amount VARCHAR2(50);
567 l_function_name VARCHAR2(50);
568 ItemType VARCHAR2(30) ;
569 ItemKey VARCHAR2(240); --NUMBER;
570 l_list WF_PARAMETER_LIST_T;
571 l_param WF_PARAMETER_T;
572 l_wf_event_name VARCHAR2(240);
573 l_last_entered_note VARCHAR2(2000);
574 l_sysdate VARCHAR2(50);
575
576 /*-----------------------------------------------------------------------+
577 | Cursor Declarations |
578 +-----------------------------------------------------------------------*/
579 CURSOR csr_loan_event_details IS
580 SELECT wf_business_event
581 ,lh.loan_number
582 ,lh.requested_amount
583 ,lh.loan_description
584 ,lh.loan_class_code
585 ,lh.loan_type_id
586 ,lt.loan_type_name
587 ,to_char(lh.requested_amount,
588 FND_CURRENCY.SAFE_GET_FORMAT_MASK(lh.LOAN_CURRENCY,50))
589 || ' ' || lh.loan_currency loan_formatted_amount
590 ,to_char(nvl(lh.requested_amount,0)-nvl(lh.funded_amount,0),
591 FND_CURRENCY.SAFE_GET_FORMAT_MASK(lh.LOAN_CURRENCY,50))
592 || ' ' || lh.loan_currency loan_undisbursed_amount
593 ,decode(lh.loan_class_code,'ERS','LNS_ERS_CONTEXT_HOMEPAGE_MENU',
594 'LNS_LOAN_CONTEXT_HOMEPAGE_MENU') function_name
595 ,lh.last_updated_by current_user_id
596 FROM lns_events le, lns_loan_headers_all_vl lh, lns_loan_types_vl lt
597 WHERE lh.loan_class_code = le.loan_class_code
598 AND le.enabled_flag = 'Y'
599 AND le.event_name = p_event_name
600 AND lt.loan_type_id = lh.loan_type_id
601 AND lh.loan_id = p_loan_id;
602
603 CURSOR get_last_entered_note IS
604 SELECT notes.notes
605 FROM jtf_notes_tl notes
606 WHERE notes.jtf_note_id = (SELECT max(note.jtf_note_id)
607 FROM jtf_notes_b note
608 WHERE note.source_object_code ='LNS_LOAN'
609 AND note.source_object_id = p_loan_id
610 )
611 AND notes.language = USERENV('LANG');
612
613 BEGIN
614 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
615
616 ItemType := 'LNSWF';
617
618 SELECT lns_workflow_itemkey_s.nextval
619 INTO ItemKey
620 FROM dual;
621
622 OPEN csr_loan_event_details;
623 FETCH csr_loan_event_details
624 INTO l_wf_event_name
625 ,l_loan_number
626 ,l_requested_amount
627 ,l_loan_description
628 ,l_loan_class_code
629 ,l_loan_type_id
630 ,l_loan_type
631 ,l_loan_formatted_amount
632 ,l_loan_undisbursed_amount
633 ,l_function_name
634 ,l_current_user_id;
635 IF csr_loan_event_details%NOTFOUND THEN
636 return;
637 END IF;
638 CLOSE csr_loan_event_details;
639
640
641 ItemKey := l_loan_number || '-' || ItemKey;
642
643 --This is required for populating the approvers comment upon loan final approval, rejection
644 --and request for additional information. As the above things a new event is raised and a
645 -- new flow triggered. So even if attribute is set in LNS_WF_AME_PROCESS it wont be retained
646 --in new flows
647 /* OPEN get_last_entered_note;
648 FETCH get_last_entered_note
649 INTO l_last_entered_note;
650 CLOSE get_last_entered_note;
651 */
652
653
654 -- initialization of object variables
655 l_list := WF_PARAMETER_LIST_T();
656 l_sysdate := to_char(sysdate, wf_core.canonical_date_mask);
657
658 wf_event.AddParameterToList(p_name => 'LNS_LOAN_ID',
659 p_value => p_loan_id,
660 p_parameterlist => l_list);
661 wf_event.AddParameterToList(p_name => 'LNS_LOAN_NUMBER',
662 p_value => l_loan_number,
663 p_parameterlist => l_list);
664 wf_event.AddParameterToList(p_name => 'LNS_LOAN_DESCRIPTION',
665 p_value => l_loan_description,
666 p_parameterlist => l_list);
667 wf_event.AddParameterToList(p_name => 'LNS_REQUESTED_AMOUNT',
668 p_value => l_requested_amount,
669 p_parameterlist => l_list);
670 wf_event.AddParameterToList(p_name => 'LNS_FORMATTED_AMOUNT',
671 p_value => l_loan_formatted_amount,
672 p_parameterlist => l_list);
673 wf_event.AddParameterToList(p_name => 'LNS_LOAN_UNDISBURSED_AMOUNT',
674 p_value => l_loan_undisbursed_amount,
675 p_parameterlist => l_list);
676 wf_event.AddParameterToList(p_name => 'LNS_WF_INIT_DATE',
677 p_value => l_sysdate,
678 p_parameterlist => l_list);
679 wf_event.AddParameterToList(p_name => 'LNS_OLD_LOAN_STATUS',
680 p_value => p_from_status,
681 p_parameterlist => l_list);
682 wf_event.AddParameterToList(p_name => 'LNS_LOAN_CLASS_CODE',
683 p_value => l_loan_class_code,
684 p_parameterlist => l_list);
685 wf_event.AddParameterToList(p_name => 'LNS_LOAN_TYPE_ID',
686 p_value => l_loan_type_id,
687 p_parameterlist => l_list);
688 wf_event.AddParameterToList(p_name => 'LNS_CURRENT_USER_ID',
689 p_value => l_current_user_id,
690 p_parameterlist => l_list);
691 wf_event.AddParameterToList(p_name => 'LNS_LOAN_TYPE',
692 p_value => l_loan_type,
693 p_parameterlist => l_list);
694 wf_event.AddParameterToList(p_name => 'LNS_EVENT_NAME',
695 p_value => p_event_name,
696 p_parameterlist => l_list);
697 wf_event.AddParameterToList(p_name => 'LNS_ERS_FUNCTION_NAME',
698 p_value => l_function_name,
699 p_parameterlist => l_list);
700 wf_event.AddParameterToList(p_name => 'LNS_LAST_ENTERED_NOTE',
701 p_value => G_LAST_ENTERED_NOTE,
702 p_parameterlist => l_list);
703
704 LogMessage(FND_LOG.LEVEL_STATEMENT,'Adding parameters:');
705 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LOAN_ID = ' || p_loan_id);
706 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LOAN_NUMBER = ' || l_loan_number);
707 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LOAN_DESCRIPTION = ' || l_loan_description);
708 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_REQUESTED_AMOUNT = ' || l_requested_amount);
709 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_FORMATTED_AMOUNT = ' || l_loan_formatted_amount);
710 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LOAN_UNDISBURSED_AMOUNT = ' || l_loan_undisbursed_amount);
711 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_WF_INIT_DATE = ' || l_sysdate);
712 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_OLD_LOAN_STATUS = ' || p_from_status);
713 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LOAN_CLASS_CODE = ' || l_loan_class_code);
714 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LOAN_TYPE_ID = ' || l_loan_type_id);
715 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_CURRENT_USER_ID = ' || l_current_user_id);
716 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LOAN_TYPE = ' || l_loan_type);
717 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_EVENT_NAME = ' || p_event_name);
718 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_ERS_FUNCTION_NAME = ' || l_function_name);
719 LogMessage(FND_LOG.LEVEL_STATEMENT,'LNS_LAST_ENTERED_NOTE = ' || G_LAST_ENTERED_NOTE);
720
721 G_LAST_ENTERED_NOTE := '';
722 LogMessage(FND_LOG.LEVEL_STATEMENT,'Setting G_LAST_ENTERED_NOTE to NULL');
723 LogMessage(FND_LOG.LEVEL_STATEMENT,'itemkey = ' || itemkey);
724 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_wf_event_name = ' || l_wf_event_name);
725
726 wf_event.raise (p_event_name => l_wf_event_name,
727 p_event_key => itemkey,
728 p_parameters => l_list);
729 LogMessage(FND_LOG.LEVEL_STATEMENT,'Event is raised');
730 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
731 EXCEPTION
732 WHEN OTHERS
733 THEN
734 WF_CORE.CONTEXT('LNSWF',l_wf_event_name, itemkey);
735 RAISE;
736 END RAISE_EVENT;
737 /*========================================================================
738 | PRIVATE PROCEDURE LOG_EVENT_HISTORY
739 |
740 | DESCRIPTION
741 | This procedure logs debug messages to db and to CM log
742 |
743 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
744 |
745 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
746 | None
747 |
748 | PARAMETERS
749 | itemtype in Item Type
750 | itemkey in Item Key
751 | actid in Action Id
752 | funcmode in Function Mode
753 | resultout out Result Out
754 |
755 | KNOWN ISSUES
756 | None
757 |
758 |
759 | NOTES
760 | Any interesting aspect of the code in the package body which needs
761 | to be stated.
762 |
763 | MODIFICATION HISTORY
764 | Date Author Description of Changes
765 | 17-Jan-2005 GBELLARY Created
766 |
767 *=======================================================================*/
768 PROCEDURE LOG_EVENT_HISTORY(itemtype in varchar2,
769 itemkey in varchar2,
770 actid in number,
771 funcmode in varchar2,
772 resultout out NOCOPY varchar2 ) IS
773 /*-----------------------------------------------------------------------+
774 | Local Variable Declarations and initializations |
775 +-----------------------------------------------------------------------*/
776
777 l_api_name CONSTANT VARCHAR2(30) := 'LOG_EVENT_HISTORY';
778 l_api_version CONSTANT NUMBER := 1.0;
779 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
780 l_event_action_id LNS_EVT_ACTION_HISTORY_H.EVENT_ACTION_ID%TYPE;
781 l_ev_action_hist_id LNS_EVT_ACTION_HISTORY_H.EVENT_ACTION_HISTORY_ID%TYPE;
782
783 /*-----------------------------------------------------------------------+
784 | Cursor Declarations |
785 +-----------------------------------------------------------------------*/
786 BEGIN
787 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
788
789 IF (funcmode <> wf_engine.eng_run) THEN
790 resultout := wf_engine.eng_null;
791 return;
792 END IF;
793 l_loan_id := wf_engine.GetItemAttrNumber
794 ( itemtype => itemtype,
795 itemkey => itemkey,
796 aname => 'LNS_LOAN_ID');
797 l_event_action_id := wf_engine.GetItemAttrNumber
798 ( itemtype => itemtype,
799 itemkey => itemkey,
800 aname => 'LNS_EVENT_ACTION_ID');
801 LNS_EVT_ACTION_HISTORY_H_PKG.Insert_Row (
802 X_EVENT_ACTION_HISTORY_ID => l_ev_action_hist_id
803 ,P_EVENT_ACTION_ID => l_event_action_id
804 ,P_LOAN_ID => l_loan_id
805 ,P_WF_ITEMKEY => itemkey
806 ,P_WF_ITEMTYPE => itemtype
807 ,P_ACTIVITY_DATE => sysdate);
808 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
809 EXCEPTION
810 WHEN OTHERS THEN
811 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
812 || ' Exception -');
813 wf_core.context('LNSWF', 'LOG_EVENT_HISTORY', itemtype, itemkey,
814 to_char(actid), funcmode); RAISE;
815 END LOG_EVENT_HISTORY;
816
817 /*========================================================================
818 | PRIVATE FUNCTION has_user_org_access
819 |
820 | DESCRIPTION
821 | This procedure checks if a user has access to an org (OU)
822 |
823 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
824 |
825 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
826 | None
827 |
828 | PARAMETERS
829 | l_user_id in Unique Identifier for the User
830 | l_org_id in the OU to check access for
831 |
832 | KNOWN ISSUES
833 | None
834 |
835 |
836 | NOTES
837 | Any interesting aspect of the code in the package body which needs
838 | to be stated.
839 |
840 | MODIFICATION HISTORY
841 | Date Author Description of Changes
842 | 01-Mar-2006 KARAMACH Created
843 --Modified the query with case when construct for loan_amount and loan_formatted_amount to fix bug5126957 --karamach
844 |
845 *=======================================================================*/
846 FUNCTION has_user_org_access (l_user_id in Number,
847 l_org_id in Number)
848 RETURN BOOLEAN IS
849
850 l_has_org_access BOOLEAN;
851 l_resp_id NUMBER;
852 l_appl_id NUMBER;
853 --Get all valid lns responsibilities for the user_id
854 CURSOR C_GET_USER_RESPS(p_user_id NUMBER,p_appl_id NUMBER) IS
855 SELECT usr_resp.responsibility_id
856 FROM fnd_user_resp_groups usr_resp
857 WHERE usr_resp.responsibility_application_id = p_appl_id
858 AND trunc(sysdate) between trunc(nvl(usr_resp.start_date, sysdate)) and trunc(nvl(usr_resp.end_date, sysdate))
859 -- AND usr_resp.start_date < sysdate AND nvl(usr_resp.end_date,sysdate-1) > sysdate -- Bug#8247186
860 AND usr_resp.user_id = p_user_id;
861
862 BEGIN
863 --if no access to org_id with any lns responsibility, return false
864 l_has_org_access := FALSE;
865 l_appl_id := 206;
866
867 if (l_user_id is null OR l_org_id is null) then
868 RETURN l_has_org_access;
869 end if;
870 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, 'In has_user_org_acc, the l_user_id is '||l_user_id);
871 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, 'In has_user_org_acc, the l_appl_id is '||l_appl_id);
872 OPEN c_get_user_resps(l_user_id,l_appl_id);
873 <<USER_RESPS_LOOP>> LOOP
874 FETCH c_get_user_resps
875 INTO l_resp_id;
876 EXIT USER_RESPS_LOOP WHEN c_get_user_resps%NOTFOUND;
877 --initialize the session context with the user and resp info
878 --fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
879 --begin R12 specific
880 MO_GLOBAL.INIT('LNS');
881 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, 'In has_user_org_acc, the l_org_id is '||l_org_id);
882 if (mo_global.check_access(l_org_id) = 'Y') then
883 --end R12 specific
884 --begin 11i specific
885 --if (fnd_profile.value('ORG_ID') = to_char(l_org_id)) then
886 --end 11i specific
887 l_has_org_access := TRUE;
888 EXIT USER_RESPS_LOOP;
889 end if;
890 END LOOP USER_RESPS_LOOP;
891 CLOSE c_get_user_resps;
892
893 RETURN l_has_org_access;
894
895 END has_user_org_access;
896
897 /*========================================================================
898 | PRIVATE PROCEDURE CREATE_NOTIFICATION_DETAILS
899 |
900 | DESCRIPTION
901 | This procedure gets approvers for a Loan
902 |
903 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
904 |
905 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
906 | None
907 |
908 | PARAMETERS
909 | itemtype in Item Type
910 | itemkey in Item Key
911 | actid in Action Id
912 | funcmode in Function Mode
913 | resultout out Result Out
914 |
915 | KNOWN ISSUES
916 | None
917 |
918 |
919 | NOTES
920 | Any interesting aspect of the code in the package body which needs
921 | to be stated.
922 |
923 | MODIFICATION HISTORY
924 | Date Author Description of Changes
925 | 17-Jan-2005 GBELLARY Created
926 | 29-Mar-2006 KARAMACH Modified the query for cursor csr_loan_details1 with case when construct for loan_amount and loan_formatted_amount to fix bug5126957
927 |
928 *=======================================================================*/
929 FUNCTION CREATE_NOTIFICATION_DETAILS ( itemtype in varchar2,
930 itemkey in varchar2,
931 p_event_name in varchar2,
932 p_loan_id in NUMBER,
933 p_loan_class_code in varchar2,
934 p_loan_type in varchar2,
935 p_loan_type_id in number,
936 p_current_user_id in number)
937 RETURN VARCHAR2 IS
938
939 /*-----------------------------------------------------------------------+
940 | Local Variable Declarations and initializations |
941 +-----------------------------------------------------------------------*/
942
943 l_api_name CONSTANT VARCHAR2(30)
944 := 'CREATE_NOTIFICATION_DETAILS';
945 l_api_version CONSTANT NUMBER := 1.0;
946 l_user_roles VARCHAR2(32000);
947 l_user_name FND_USER.user_name%TYPE;
948 l_role_name VARCHAR2(100);
949 l_loan_number LNS_LOAN_HEADERS.LOAN_NUMBER%TYPE;
950 l_primary_recipient_type lns_event_actions.primary_recipient_type%TYPE;
951 l_primary_recipient_name lns_event_actions.primary_recipient_name%TYPE;
952 l_priority_num lns_event_actions.priority_num%TYPE;
953 l_active_for_num lns_event_actions.active_for_num%TYPE;
954 l_delivery_method lns_event_actions.delivery_method%TYPE;
955 l_loan_assigned_name jtf_rs_resource_extns.source_name%TYPE;
956 l_loan_assigned_user fnd_user.user_name%TYPE;
957 l_current_user fnd_user.user_name%TYPE;
958 l_borrower_name hz_parties.party_name%TYPE;
959 l_loan_class lns_lookups.meaning%TYPE;
960 l_loan_type lns_loan_types.loan_type_name%TYPE;
961 l_loan_purpose lns_lookups.meaning%TYPE;
962 l_loan_subtype lns_loan_headers_all.loan_subtype%TYPE;
963 l_collateral_percent VARCHAR2(10);
964 l_loan_amount lns_pay_sum_v.total_principal_balance%TYPE;
965 l_loan_formatted_amount VARCHAR2(30);
966 l_loan_start_date lns_loan_headers_all.loan_start_date%TYPE;
967 l_term VARCHAR2(15);
968 l_loan_maturity_date lns_loan_headers_all.loan_maturity_date%TYPE;
969 l_interest_rate VARCHAR2(30);
970 l_overdue_amount VARCHAR2(30);
971 l_overdue_num lns_pay_sum_overdue_v.number_overdue_bills%TYPE;
972 l_event_action_id LNS_EVENT_ACTIONS.EVENT_ACTION_ID%TYPE;
973 l_org_id NUMBER;
974 l_user_id NUMBER;
975 l_product_name LNS_LOAN_PRODUCTS_ALL.LOAN_PRODUCT_NAME%TYPE;
976 l_loan_approval_agent_note JTF_NOTES_TL.NOTES%TYPE;
977
978
979 /*-----------------------------------------------------------------------+
980 | Cursor Declarations |
981 +-----------------------------------------------------------------------*/
982 CURSOR csr_notification_details IS
983 SELECT primary_recipient_type
984 , primary_recipient_name
985 , priority_num
986 , nvl(active_for_num,0)*24*60 -- This has to be converted into minutes
987 , delivery_method
988 , event_action_id
989 FROM lns_events le, lns_event_actions lea
990 WHERE le.event_name = p_event_name
991 AND le.enabled_flag = 'Y'
992 AND le.loan_class_code = p_loan_class_code
993 AND lea.event_id = le.event_id
994 AND lea.EVENT_ACTION_NAME = 'NOTIFICATION'
995 AND lea.enabled_flag = 'Y'
996 AND lea.loan_type_id = p_loan_type_id;
997
998 CURSOR csr_current_user IS
999 SELECT fndu.user_name
1000 FROM fnd_user fndu
1001 WHERE fndu.user_id = p_current_user_id;
1002
1003 CURSOR csr_loan_role_users IS
1004 SELECT fndu.user_name
1005 ,fndu.user_id
1006 FROM jtf_rs_role_relations rel
1007 ,jtf_rs_roles_b rol
1008 ,jtf_rs_resource_extns res
1009 ,fnd_user fndu
1010 WHERE rel.role_id = rol.role_id
1011 AND rel.delete_flag <> 'Y'
1012 AND SYSDATE BETWEEN NVL(rel.start_date_active,sysdate)
1013 AND NVL(rel.end_date_active,sysdate)
1014 AND rol.role_type_code = 'LOANS'
1015 AND rol.role_code = l_primary_recipient_name
1016 AND rol.active_flag = 'Y'
1017 AND rel.role_resource_id = res.resource_id
1018 AND res.category = 'EMPLOYEE'
1019 AND res.start_date_active <= SYSDATE
1020 AND (res.end_date_active is null or res.end_date_active >= SYSDATE)
1021 AND fndu.user_id = res.user_id;
1022
1023 CURSOR csr_loan_details IS
1024 SELECT hp.party_name borrower_name
1025 ,fnd.user_name
1026 ,res.source_name
1027 ,llklc.meaning loan_class
1028 ,llklt.loan_type_name loan_type
1029 ,llkst.meaning loan_subtype
1030 ,to_char(nvl(llh.collateral_percent,0)) || '%' collateral_percent
1031 ,llh.loan_start_date
1032 ,llh.loan_term || ' ' || llktt.meaning term
1033 ,llh.loan_maturity_date
1034 ,LNS_FINANCIALS.getActiveRate(llh.LOAN_ID) interest_rate
1035 ,llkp.meaning loan_purpose
1036 ,llh.org_id
1037 ,lprod.loan_product_name
1038 FROM lns_loan_headers_all_vl llh, hz_parties hp,
1039 jtf_rs_resource_extns res, fnd_user fnd,
1040 lns_payments_summary_v ps,
1041 lns_lookups llktt,
1042 lns_lookups llklc,
1043 lns_loan_types_vl llklt,
1044 lns_lookups llkp,
1045 lns_lookups llkst,
1046 lns_loan_products_all_vl lprod
1047 WHERE llh.primary_borrower_id = hp.party_id
1048 AND llh.loan_assigned_to = res.resource_id
1049 AND res.category = 'EMPLOYEE'
1050 AND fnd.user_id = res.user_id
1051 AND llktt.lookup_code = llh.loan_term_period
1052 AND llktt.lookup_type = 'PERIOD'
1053 AND llklc.lookup_code = llh.loan_class_code
1054 AND llklc.lookup_type = 'LOAN_CLASS'
1055 AND llklt.loan_type_id = llh.loan_type_id
1056 AND llkp.lookup_code (+) = llh.loan_purpose_code
1057 AND llkp.lookup_type (+) = 'LOAN_PURPOSE'
1058 AND llkst.lookup_code (+) = llh.loan_subtype
1059 AND llkst.lookup_type (+) = 'LOAN_SUBTYPE'
1060 AND llh.loan_id = p_loan_id
1061 AND llh.product_id=lprod.loan_product_id;
1062
1063 --Modified the query with case when construct for loan_amount and loan_formatted_amount to fix bug5126957 --karamach
1064 CURSOR csr_loan_details1 IS
1065 SELECT
1066 (CASE WHEN llh.loan_status in ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR','CANCELLED') OR llh.FUNDED_AMOUNT = 0 THEN llh.requested_amount
1067 ELSE ps.total_principal_balance END) loan_amount
1068 ,to_char((CASE WHEN llh.loan_status in ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR','CANCELLED') OR llh.FUNDED_AMOUNT = 0 THEN llh.requested_amount
1069 ELSE ps.total_principal_balance END),
1070 FND_CURRENCY.SAFE_GET_FORMAT_MASK(llh.LOAN_CURRENCY,50))
1071 || ' ' || llh.loan_currency loan_formatted_amount
1072 FROM lns_loan_headers_all llh
1073 ,lns_pay_sum_v ps
1074 WHERE llh.loan_id = p_loan_id
1075 AND ps.loan_id = llh.loan_id;
1076
1077 CURSOR csr_loan_details2 IS
1078 SELECT to_char(ps.total_overdue,
1079 FND_CURRENCY.SAFE_GET_FORMAT_MASK(llh.LOAN_CURRENCY,50))
1080 || ' ' || llh.loan_currency overdue_amount
1081 ,ps.number_overdue_bills overdue_num
1082 FROM lns_loan_headers_all llh
1083 ,lns_pay_sum_overdue_v ps
1084 WHERE llh.loan_id = p_loan_id
1085 AND ps.loan_id = llh.loan_id;
1086
1087 /* The below query will not work in case of multiple req for info and situations where user might
1088 not enter the note at all during submit for approval
1089 CURSOR get_agent_approval_comment IS
1090 SELECT notes.notes
1091 FROM jtf_notes_tl notes
1092 WHERE notes.jtf_note_id = (SELECT max(note.jtf_note_id)
1093 FROM jtf_notes_b note
1094 WHERE note.source_object_code ='LNS_LOAN'
1095 AND note.source_object_id = p_loan_id
1096 )
1097 AND notes.language = USERENV('LANG');
1098 */
1099
1100 CURSOR get_agent_approval_comment IS
1101 SELECT notes.notes
1102 FROM jtf_notes_tl notes
1103 WHERE notes.jtf_note_id = (select jtf_note_id
1104 from jtf_note_contexts
1105 where note_context_type_id = (select max(action_id)
1106 from LNS_APPROVAL_ACTIONS
1107 where loan_id = p_loan_id)
1108 )
1109 AND notes.language = USERENV('LANG');
1110
1111
1112 BEGIN
1113 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1114 OPEN csr_notification_details;
1115 FETCH csr_notification_details
1116 INTO l_primary_recipient_type
1117 , l_primary_recipient_name
1118 , l_priority_num
1119 , l_active_for_num
1120 , l_delivery_method
1121 , l_event_action_id;
1122 IF csr_notification_details%NOTFOUND THEN
1123 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Notification is not found. Returning');
1124 RETURN 'N';
1125 END IF;
1126
1127 CLOSE csr_notification_details;
1128
1129 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Notification details:');
1130 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_primary_recipient_type = ' || l_primary_recipient_type);
1131 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_primary_recipient_name = ' || l_primary_recipient_name);
1132 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_priority_num = ' || l_priority_num);
1133 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_active_for_num = ' || l_active_for_num);
1134 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_delivery_method = ' || l_delivery_method);
1135 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_event_action_id = ' || l_event_action_id);
1136
1137 OPEN csr_loan_details;
1138 FETCH csr_loan_details
1139 INTO l_borrower_name
1140 , l_loan_assigned_user
1141 , l_loan_assigned_name
1142 , l_loan_class
1143 , l_loan_type
1144 , l_loan_subtype
1145 , l_collateral_percent
1146 , l_loan_start_date
1147 , l_term
1148 , l_loan_maturity_date
1149 , l_interest_rate
1150 , l_loan_purpose
1151 , l_org_id
1152 , l_product_name;
1153 CLOSE csr_loan_details;
1154
1155 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Loan details:');
1156 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_borrower_name = ' || l_borrower_name);
1157 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_assigned_user = ' || l_loan_assigned_user);
1158 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_assigned_name = ' || l_loan_assigned_name);
1159 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_class = ' || l_loan_class);
1160 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_type = ' || l_loan_type);
1161 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_subtype = ' || l_loan_subtype);
1162 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_collateral_percent = ' || l_collateral_percent);
1163 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_start_date = ' || l_loan_start_date);
1164 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_term = ' || l_term);
1165 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_maturity_date = ' || l_loan_maturity_date);
1166 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_interest_rate = ' || l_interest_rate);
1167 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_purpose = ' || l_loan_purpose);
1168 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_org_id = ' || l_org_id);
1169 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_product_name = ' || l_product_name);
1170
1171 OPEN csr_loan_details1;
1172 FETCH csr_loan_details1
1173 INTO l_loan_amount
1174 , l_loan_formatted_amount;
1175 CLOSE csr_loan_details1;
1176
1177 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_amount = ' || l_loan_amount);
1178 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_formatted_amount = ' || l_loan_formatted_amount);
1179
1180 OPEN csr_loan_details2;
1181 FETCH csr_loan_details2
1182 INTO l_overdue_amount
1183 , l_overdue_num;
1184 CLOSE csr_loan_details2;
1185
1186 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_overdue_amount = ' || l_overdue_amount);
1187 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_overdue_num = ' || l_overdue_num);
1188
1189 OPEN csr_current_user;
1190 FETCH csr_current_user
1191 INTO l_current_user;
1192 CLOSE csr_current_user;
1193
1194 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_current_user = ' || l_current_user);
1195
1196 OPEN get_agent_approval_comment;
1197 FETCH get_agent_approval_comment
1198 INTO l_loan_approval_agent_note;
1199 CLOSE get_agent_approval_comment;
1200
1201 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_approval_agent_note = ' || l_loan_approval_agent_note);
1202
1203 l_loan_number := wf_engine.GetItemAttrText
1204 ( itemtype => itemtype,
1205 itemkey => itemkey,
1206 aname => 'LNS_LOAN_NUMBER');
1207 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_number from GetItemAttrText = ' || l_current_user);
1208
1209 IF l_primary_recipient_type = 'ROLE' THEN
1210
1211 OPEN csr_loan_role_users;
1212 FETCH csr_loan_role_users
1213 INTO l_user_name,l_user_id;
1214 IF csr_loan_role_users%NOTFOUND THEN
1215 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'cursor csr_loan_role_users returns 0 rows');
1216 RETURN 'N';
1217 END IF;
1218
1219 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_user_name = ' || l_user_name);
1220 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_user_id = ' || l_user_id);
1221
1222 if (has_user_org_access(l_user_id,l_org_id)) then
1223 l_user_roles := l_user_roles||','||l_user_name;
1224 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'The l_user_roles is '||l_user_roles);
1225 end if;
1226 LOOP
1227 FETCH csr_loan_role_users
1228 INTO l_user_name,l_user_id;
1229 EXIT WHEN csr_loan_role_users%NOTFOUND;
1230 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, 'The l_user_name is '||l_user_name);
1231 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, 'The l_user_id is '||l_user_id);
1232 if (has_user_org_access(l_user_id,l_org_id)) then
1233 l_user_roles := l_user_roles||','||l_user_name;
1234 end if;
1235 END LOOP;
1236 CLOSE csr_loan_role_users;
1237 IF substr(l_user_roles,1,1) = ','
1238 THEN
1239 l_user_roles := substr(l_user_roles, 2, (length(l_user_roles) - 1));
1240 END IF;
1241
1242 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'For the Role, l_user_roles is '||l_user_roles);
1243
1244 if (nvl(length(l_user_roles),0) < 3) then
1245 RETURN 'N';
1246 end if;
1247 l_role_name := 'Loan Managers'|| '(' ||l_loan_number || '-' || ItemKey
1248 || ')';
1249 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_role_name = '||l_role_name);
1250
1251 -- Bug#8709307 - Added Expiration Date to the new adhoc role
1252 wf_directory.CreateAdhocRole(
1253 role_name => l_role_name,
1254 role_display_name => l_role_name,
1255 notification_preference => 'MAILHTM2',
1256 expiration_date => (sysdate+90)
1257 );
1258 wf_directory.AddUsersToAdhocRole(role_name => l_role_name,
1259 role_users => l_user_roles);
1260
1261 wf_engine.SetItemAttrText (itemtype => itemtype,
1262 itemkey => itemkey,
1263 aname => 'LNS_PRIMARY_ROLE',
1264 avalue => l_role_name);
1265 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Set LNS_PRIMARY_ROLE = '||l_role_name);
1266
1267 ELSIF l_primary_recipient_type = 'INDIVIDUAL' AND l_primary_recipient_name = 'LOAN_ASSIGNED_TO' THEN
1268
1269 -- l_user_roles := l_loan_assigned_user;
1270 -- l_role_name :=l_loan_assigned_name || '(' ||l_loan_number || '-'|| ItemKey || ')'
1271
1272 -- Bug#8709307 - Instead of creating Adhoc Role, directly sending notification
1273 -- to the user (FND_USER)
1274 l_role_name := l_loan_assigned_user;
1275
1276 wf_engine.SetItemAttrText (itemtype => itemtype,
1277 itemkey => itemkey,
1278 aname => 'LNS_PRIMARY_ROLE',
1279 avalue => l_role_name);
1280 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Set LNS_PRIMARY_ROLE = '||l_role_name);
1281
1282 END IF;
1283
1284 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, 'At last, l_user_roles is '||l_user_roles);
1285 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, 'At last, l_role_name is '||l_role_name);
1286
1287 wf_engine.SetItemAttrText (itemtype => itemtype,
1288 itemkey => itemkey,
1289 aname => 'LNS_BORROWER_NAME',
1290 avalue => l_borrower_name);
1291
1292 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1293 itemkey => itemkey,
1294 aname => 'LNS_PRIORITY',
1295 avalue => l_priority_num);
1296 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1297 itemkey => itemkey,
1298 aname => 'LNS_EVENT_ACTION_ID',
1299 avalue => l_event_action_id);
1300 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1301 itemkey => itemkey,
1302 aname => 'LNS_TIMEOUT',
1303 avalue => l_active_for_num);
1304 wf_engine.SetItemAttrText (itemtype => itemtype,
1305 itemkey => itemkey,
1306 aname => 'LNS_LOAN_ASSIGNED_USER',
1307 avalue => l_loan_assigned_user);
1308 wf_engine.SetItemAttrText (itemtype => itemtype,
1309 itemkey => itemkey,
1310 aname => 'LNS_LOAN_ASSIGNED_NAME',
1311 avalue => l_loan_assigned_name);
1312 wf_engine.SetItemAttrText (itemtype => itemtype,
1313 itemkey => itemkey,
1314 aname => 'LNS_CURRENT_USER',
1315 avalue => l_current_user);
1316 wf_engine.SetItemAttrText (itemtype => itemtype,
1317 itemkey => itemkey,
1318 aname => 'LNS_LOAN_CLASS',
1319 avalue => l_loan_class);
1320 wf_engine.SetItemAttrText (itemtype => itemtype,
1321 itemkey => itemkey,
1322 aname => 'LNS_LOAN_TYPE',
1323 avalue => l_loan_type);
1324 wf_engine.SetItemAttrText (itemtype => itemtype,
1325 itemkey => itemkey,
1326 aname => 'LNS_LOAN_SUBTYPE',
1327 avalue => l_loan_subtype);
1328 wf_engine.SetItemAttrText (itemtype => itemtype,
1329 itemkey => itemkey,
1330 aname => 'LNS_COLLATERAL_PERCENT',
1331 avalue => l_collateral_percent);
1332 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1333 itemkey => itemkey,
1334 aname => 'LNS_LOAN_AMOUNT',
1335 avalue => l_loan_amount);
1336 wf_engine.SetItemAttrText (itemtype => itemtype,
1337 itemkey => itemkey,
1338 aname => 'LNS_FORMATTED_AMOUNT',
1339 avalue => l_loan_formatted_amount);
1340 wf_engine.SetItemAttrDate (itemtype => itemtype,
1341 itemkey => itemkey,
1342 aname => 'LNS_LOAN_START_DATE',
1343 avalue => l_loan_start_date);
1344 wf_engine.SetItemAttrText (itemtype => itemtype,
1345 itemkey => itemkey,
1346 aname => 'LNS_TERM',
1347 avalue => l_term);
1348 wf_engine.SetItemAttrDate (itemtype => itemtype,
1349 itemkey => itemkey,
1350 aname => 'LNS_LOAN_MATURITY_DATE',
1351 avalue => l_loan_maturity_date);
1352 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1353 itemkey => itemkey,
1354 aname => 'LNS_INTEREST_RATE',
1355 avalue => l_interest_rate);
1356 wf_engine.SetItemAttrText(itemtype => itemtype,
1357 itemkey => itemkey,
1358 aname => 'LNS_OVERDUE_AMOUNT',
1359 avalue => l_overdue_amount);
1360 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1361 itemkey => itemkey,
1362 aname => 'LNS_OVERDUE_NUM',
1363 avalue => l_overdue_num);
1364 wf_engine.SetItemAttrText(itemtype => itemtype,
1365 itemkey => itemkey,
1366 aname => 'LNS_LOAN_PURPOSE',
1367 avalue => l_loan_purpose);
1368 wf_engine.SetItemAttrText(itemtype => itemtype,
1369 itemkey => itemkey,
1370 aname => 'LNS_LOAN_PRODUCT_NAME',
1371 avalue => l_product_name);
1372
1373 --l_loan_approval_agent_note := 'Test Initial Loan Agent Request Approval Note';
1374 wf_engine.SetItemAttrText(itemtype => itemtype,
1375 itemkey => itemkey,
1376 aname => 'LNS_LOAN_APPROVAL_AGENT_NOTE',
1377 avalue => l_loan_approval_agent_note);
1378 return 'Y';
1379 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
1383 || ' Exception -');
1384 RAISE;
1385 END CREATE_NOTIFICATION_DETAILS;
1386 /*========================================================================
1387 | PRIVATE PROCEDURE PROCESS_EVENT
1388 |
1389 | DESCRIPTION
1390 | This procedure logs debug messages to db and to CM log
1391 |
1392 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1393 |
1394 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1395 | None
1396 |
1397 | PARAMETERS
1398 | itemtype in Item Type
1399 | itemkey in Item Key
1400 | actid in Action Id
1401 | funcmode in Function Mode
1402 | resultout out Result Out
1403 |
1404 | KNOWN ISSUES
1405 | None
1406 |
1407 |
1408 | NOTES
1409 | Any interesting aspect of the code in the package body which needs
1410 | to be stated.
1411 |
1412 | MODIFICATION HISTORY
1413 | Date Author Description of Changes
1414 | 17-Jan-2005 GBELLARY Created
1415 |
1416 *=======================================================================*/
1417 PROCEDURE PROCESS_EVENT(itemtype in varchar2,
1418 itemkey in varchar2,
1419 actid in number,
1420 funcmode in varchar2,
1421 resultout out NOCOPY varchar2 ) IS
1422 /*-----------------------------------------------------------------------+
1423 | Local Variable Declarations and initializations |
1424 +-----------------------------------------------------------------------*/
1425
1426 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_EVENT';
1427 l_api_version CONSTANT NUMBER := 1.0;
1428 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
1429 l_loan_number LNS_LOAN_HEADERS_ALL.LOAN_NUMBER%TYPE;
1430 l_loan_class_code LNS_LOAN_HEADERS_ALL.LOAN_CLASS_CODE%TYPE;
1431 l_loan_type LNS_LOAN_TYPES.LOAN_TYPE_NAME%TYPE;
1432 l_loan_type_id LNS_LOAN_TYPES.LOAN_TYPE_ID%TYPE;
1433 l_current_user_id LNS_LOAN_HEADERS_ALL.CREATED_BY%TYPE;
1434 l_event_name LNS_EVENTS.EVENT_NAME%TYPE;
1435 l_return_status Varchar2(1);
1436 l_notification_id WF_NOTIFICATIONS.NOTIFICATION_ID%TYPE;
1437 l_search_criteria LNS_LOAN_HEADERS_ALL.LOAN_NUMBER%TYPE;
1438 l_current_user_name fnd_user.user_name%TYPE;
1439
1440 /*-----------------------------------------------------------------------+
1441 | Cursor Declarations |
1442 +-----------------------------------------------------------------------*/
1443
1444 CURSOR csr_current_user(current_user_id number) IS
1445 SELECT fndu.user_name
1446 FROM fnd_user fndu
1447 WHERE fndu.user_id = current_user_id;
1448
1449 CURSOR get_notifications_for_close(search_criteria varchar2) IS
1450 SELECT notification_id
1451 FROM WF_NOTIFICATIONS
1452 WHERE item_key like search_criteria
1453 AND message_name = 'MSG_LOAN_TO_BE_APPROVED'
1454 AND status = 'OPEN';
1455
1456 BEGIN
1457 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1458 --test_proc('Inside PROCESS_EVENT');
1459
1460 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemtype = ' || itemtype);
1461 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemkey = ' || itemkey);
1462 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'actid = ' || actid);
1463 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'funcmode = ' || funcmode);
1464
1465 SET_SESSION_CONTEXT(itemtype, itemkey);
1466
1467 IF (funcmode <> wf_engine.eng_run) THEN
1468 resultout := wf_engine.eng_null;
1469 return;
1470 END IF;
1471
1472 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Getting attributes:');
1473
1474 l_loan_id := wf_engine.GetItemAttrNumber
1475 ( itemtype => itemtype,
1476 itemkey => itemkey,
1477 aname => 'LNS_LOAN_ID');
1478
1479 l_loan_number := wf_engine.GetItemAttrText
1480 ( itemtype => itemtype,
1481 itemkey => itemkey,
1482 aname => 'LNS_LOAN_NUMBER');
1483
1484 l_loan_class_code := wf_engine.GetItemAttrText
1485 ( itemtype => itemtype,
1486 itemkey => itemkey,
1487 aname => 'LNS_LOAN_CLASS_CODE');
1488 l_loan_type := wf_engine.GetItemAttrText
1489 ( itemtype => itemtype,
1490 itemkey => itemkey,
1491 aname => 'LNS_LOAN_TYPE');
1492 l_loan_type_id := wf_engine.GetItemAttrNumber
1493 ( itemtype => itemtype,
1494 itemkey => itemkey,
1495 aname => 'LNS_LOAN_TYPE_ID');
1496 l_current_user_id := wf_engine.GetItemAttrNumber
1497 ( itemtype => itemtype,
1498 itemkey => itemkey,
1499 aname => 'LNS_CURRENT_USER_ID');
1500 l_event_name := wf_engine.GetItemAttrText
1501 ( itemtype => itemtype,
1502 itemkey => itemkey,
1503 aname => 'LNS_EVENT_NAME');
1504
1505 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LNS_LOAN_ID = ' || l_loan_id );
1506 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LNS_LOAN_NUMBER = ' || l_loan_number );
1507 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LNS_LOAN_CLASS_CODE = ' || l_loan_class_code );
1508 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LNS_LOAN_TYPE = ' || l_loan_type );
1509 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LNS_LOAN_TYPE_ID = ' || l_loan_type_id );
1510 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LNS_CURRENT_USER_ID = ' || l_current_user_id );
1511 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LNS_EVENT_NAME = ' || l_event_name );
1512
1513 l_return_status := create_notification_details(itemkey => itemkey
1514 ,itemtype => itemtype
1515 ,p_event_name => l_event_name
1516 ,p_loan_id => l_loan_id
1517 ,p_loan_class_code => l_loan_class_code
1518 ,p_loan_type => l_loan_type
1519 ,p_loan_type_id => l_loan_type_id
1520 ,p_current_user_id => l_current_user_id
1521 );
1522
1523 --test_proc('l_event_name - ' || l_event_name );
1524 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status = ' || l_return_status );
1525 --test_proc('l_return_status_crt_ntf - ' || l_return_status);
1526 IF(l_event_name = 'LOAN_APPROVAL_APPROVED' AND l_return_status = 'Y') THEN
1527
1528 l_search_criteria := l_loan_number || '%';
1529 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_search_criteria = ' || l_search_criteria );
1530
1531 OPEN get_notifications_for_close(l_search_criteria);
1532
1533 <<PENDING_NOTIFICATIONS_LOOP>>LOOP
1534 FETCH get_notifications_for_close INTO l_notification_id;
1535 EXIT PENDING_NOTIFICATIONS_LOOP WHEN get_notifications_for_close%NOTFOUND;
1536
1537 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_notification_id = ' || l_notification_id );
1538 --test_proc('l_notification_id - ' || l_notification_id);
1539
1540 if(l_notification_id is not null) then
1541 OPEN csr_current_user(l_current_user_id);
1542 FETCH csr_current_user INTO l_current_user_name;
1543 CLOSE csr_current_user;
1544 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_current_user_name = ' || l_current_user_name );
1545
1546 --wf_notification.close(l_notification_id,l_current_user_name);
1547 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Before setting Approve programmatically');
1548 --test_proc('Before setting Approve programmatically');
1549 wf_notification.setattrtext ( nid => l_notification_id
1550 , aname => 'RESULT'
1551 , avalue => 'APPROVE' );
1552 wf_notification.respond ( nid => l_notification_id
1553 , respond_comment => null
1554 , responder => l_current_user_name );
1555 LogMessage(FND_LOG.LEVEL_STATEMENT, 'After responding programmatically');
1556 --test_proc('After responding programmatically');
1557 end if;
1558
1559 END LOOP PENDING_NOTIFICATIONS_LOOP;
1560
1561 END IF;
1562
1563 resultout := 'COMPLETE:' || l_return_status;
1564 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'PROCESS_EVENT - resultOut is '||resultout);
1565 EXCEPTION
1566 WHEN OTHERS THEN
1567 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
1568 || ' Exception -');
1569 wf_core.context('LNSWF', 'PROCESS_EVENT', itemtype, itemkey,
1570 to_char(actid), funcmode); RAISE;
1571 END PROCESS_EVENT;
1572
1573 /*========================================================================
1574 | PRIVATE PROCEDURE PROCESS_LOAN_APPROVAL
1575 |
1576 | DESCRIPTION
1577 | This procedure insters/updates the Loan Approval Status in LNS_APPROVAL_ACTIONS table.
1578 |
1579 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1580 |
1581 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1582 | None
1583 |
1584 | PARAMETERS
1585 | itemtype in Item Type
1586 | itemkey in Item Key
1587 | actid in Action Id
1588 | funcmode in Function Mode
1589 | resultout out Result Out
1590 |
1591 | KNOWN ISSUES
1592 | None
1593 |
1594 |
1595 | NOTES
1596 | Any interesting aspect of the code in the package body which needs
1597 | to be stated.
1598 |
1599 | MODIFICATION HISTORY
1600 | Date Author Description of Changes
1601 | 23-Aug-2009 avepati Created
1602 | 23-Aug-2009 avepati Bug 8764310 - Loan Notification Missing Approve and Reject Buttons
1603 | 10-Dec-2009 avepati Bug 9092928 - Loan Approval/Rejection Notes is not saved
1604 *=======================================================================*/
1605 PROCEDURE PROCESS_LOAN_APPROVAL(itemtype in varchar2,
1606 itemkey in varchar2,
1607 actid in number,
1608 funcmode in varchar2,
1609 resultout out NOCOPY varchar2 ) IS
1610 /*-----------------------------------------------------------------------+
1611 | Local Variable Declarations and initializations |
1612 +-----------------------------------------------------------------------*/
1613
1614 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOAN_APPROVAL';
1615 l_api_version CONSTANT NUMBER := 1.0;
1616 l_object_version_number NUMBER := 1;
1617 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
1618 l_loan_approve_note VARCHAR2(2000);
1619 l_loan_approval_action_rec LNS_APPROVAL_ACTION_PUB.APPROVAL_ACTION_REC_TYPE;
1620 l_RETURN_STATUS LNS_LOAN_PRODUCTS_ALL.LOAN_APPR_REQ_FLAG%TYPE;
1621 G_JTF_NOTE_CONTEXTS_TAB JTF_NOTES_PUB.JTF_NOTE_CONTEXTS_TBL_TYPE;
1622 l_action_id NUMBER;
1623 l_note_id NUMBER;
1624 l_MSG_DATA VARCHAR2(32767);
1625 l_MSG_COUNT NUMBER;
1626 l_msg_index_out NUMBER;
1627 l_org_id NUMBER;
1628
1629
1630 /*-----------------------------------------------------------------------+
1631 | Cursor Declarations |
1632 +-----------------------------------------------------------------------*/
1633
1634 BEGIN
1635 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1636
1637 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemtype = ' || itemtype);
1638 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemkey = ' || itemkey);
1639 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'actid = ' || actid);
1640 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'funcmode = ' || funcmode);
1641
1642 SET_SESSION_CONTEXT(itemtype, itemkey);
1643
1644 IF (funcmode <> wf_engine.eng_run) THEN
1645 resultout := 'COMPLETE:' || 'N';
1646 return;
1647 END IF;
1648
1649 l_loan_id := wf_engine.GetItemAttrNumber
1650 ( itemtype => itemtype,
1651 itemkey => itemkey,
1652 aname => 'LNS_LOAN_ID');
1653
1654 --gparuchu - Code added to make sure correct object_version_number is passed
1655 --inorder to make sure the header table gets updated correctly upon Approval process.
1656 IF (l_loan_id IS NOT NULL) THEN
1657 select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
1658 from lns_loan_headers_all loan
1659 where loan.loan_id = l_loan_id;
1660 END IF;
1661
1662 LogMessage(FND_LOG.LEVEL_STATEMENT,'In PROCESS_LOAN_APPROVAL l_loan_id : ' || l_loan_id);
1663
1664 l_loan_approve_note := wf_engine.GetItemAttrText
1665 ( itemtype => itemtype,
1666 itemkey => itemkey,
1667 aname => 'WF_NOTE');
1668
1669 G_LAST_ENTERED_NOTE := l_loan_approve_note;
1670 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In PROCESS_LOAN_APPROVAL setting G_LAST_ENTERED_NOTE : ' || G_LAST_ENTERED_NOTE);
1671
1672
1673 /* wf_engine.SetItemAttrText (itemtype => itemtype,
1674 itemkey => itemkey,
1675 aname => 'LNS_LAST_ENTERED_NOTE',
1676 avalue => l_loan_approve_note);
1677 */
1678 LogMessage(FND_LOG.LEVEL_STATEMENT,'In PROCESS_LOAN_APPROVAL l_loan_approve_note : ' || l_loan_approve_note);
1679
1680 select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_loan_approval_action_rec.action_id from dual;
1681
1682
1683 l_loan_approval_action_rec.created_by := LNS_UTILITY_PUB.CREATED_BY;
1684 l_loan_approval_action_rec.creation_date := LNS_UTILITY_PUB.CREATION_DATE;
1685 l_loan_approval_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
1686 l_loan_approval_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
1687 l_loan_approval_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
1688 l_loan_approval_action_rec.object_version_number := l_object_version_number;
1689 l_loan_approval_action_rec.loan_id := l_loan_id;
1690 l_loan_approval_action_rec.action_type := 'APPROVE';
1691 l_loan_approval_action_rec.amount := null;
1692 l_loan_approval_action_rec.reason_code := null;
1693 l_loan_approval_action_rec.attribute_category := null;
1694 l_loan_approval_action_rec.attribute1 := null;
1695 l_loan_approval_action_rec.attribute2 := null;
1696 l_loan_approval_action_rec.attribute3 := null;
1697 l_loan_approval_action_rec.attribute4 := null;
1698 l_loan_approval_action_rec.attribute5 := null;
1699 l_loan_approval_action_rec.attribute6 := null;
1700 l_loan_approval_action_rec.attribute7 := null;
1701 l_loan_approval_action_rec.attribute8 := null;
1702 l_loan_approval_action_rec.attribute9 := null;
1703 l_loan_approval_action_rec.attribute10 := null;
1704 l_loan_approval_action_rec.attribute11 := null;
1705 l_loan_approval_action_rec.attribute12 := null;
1706 l_loan_approval_action_rec.attribute13 := null;
1707 l_loan_approval_action_rec.attribute14 := null;
1708 l_loan_approval_action_rec.attribute15 := null;
1709 l_loan_approval_action_rec.attribute16 := null;
1710 l_loan_approval_action_rec.attribute17 := null;
1711 l_loan_approval_action_rec.attribute18 := null;
1712 l_loan_approval_action_rec.attribute19 := null;
1713 l_loan_approval_action_rec.attribute20 := null;
1714
1715 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Start calling LNS_APPROVAL_ACTION_PUB.create_approval_action' );
1716
1717 -- fix for bug 13785835: set org context before approving loan from notification
1718 MO_GLOBAL.INIT('LNS');
1719 MO_GLOBAL.set_policy_context('S',l_org_id);
1720
1721 LNS_APPROVAL_ACTION_PUB.create_approval_action (p_init_msg_list => FND_API.G_TRUE,
1722 p_approval_action_rec => l_loan_approval_action_rec,
1723 x_action_id => l_action_id,
1724 X_RETURN_STATUS => l_RETURN_STATUS,
1725 X_MSG_COUNT => l_MSG_COUNT,
1726 X_MSG_DATA => l_MSG_DATA );
1727
1728 LogMessage(FND_LOG.LEVEL_STATEMENT,'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1729 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_action_id : ' || l_action_id);
1730 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_RETURN_STATUS : ' || l_RETURN_STATUS);
1731 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_COUNT : ' || l_MSG_COUNT);
1732 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_DATA : ' || l_MSG_DATA);
1733
1734 LogMessage(FND_LOG.LEVEL_STATEMENT,'END calling LNS_APPROVAL_ACTION_PUB.create_approval_action' );
1735 LogMessage(FND_LOG.LEVEL_STATEMENT,'In PROCESS_LOAN_APPROVAL l_loan_approve_note : ' || l_loan_approve_note);
1736
1737
1738 IF (l_loan_approve_note is not null AND length(trim(l_loan_approve_note)) > 0) THEN
1739
1740 --added by GPARUCHU to set org correctly for JTF_NOTES_PUB.validate_object method
1741 -- to correctly pick the loan against given loan_id
1742 -- MO_GLOBAL.GET_CURRENT_ORG_ID() doesnt work here as it returns null when an external user
1743 -- is trying to approve a loan
1744
1745 -- fix for bug 13785835: moved calls to MO_GLOBAL before call to LNS_APPROVAL_ACTION_PUB.create_approval_action
1746
1747 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type := 'LNS_APPROVAL_ACTION';
1748 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type_id := l_loan_approval_action_rec.action_id;
1749 --G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type_id := l_loan_approval_action_rec.action_id;
1750 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
1751 G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
1752 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
1753 G_JTF_NOTE_CONTEXTS_TAB(1).creation_date := LNS_UTILITY_PUB.creation_date;
1754 G_JTF_NOTE_CONTEXTS_TAB(1).created_by := LNS_UTILITY_PUB.created_by;
1755
1756 LogMessage(FND_LOG.LEVEL_STATEMENT,'Start calling JTF_NOTES_PUB.CREATE_NOTE' );
1757 JTF_NOTES_PUB.CREATE_NOTE(
1758 P_API_VERSION => 1.0,
1759 P_INIT_MSG_LIST => FND_API.G_TRUE,
1760 P_COMMIT => FND_API.G_TRUE,
1761 P_JTF_NOTE_ID => FND_API.G_MISS_NUM,
1762 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1763 P_SOURCE_OBJECT_ID => l_loan_id,
1764 P_SOURCE_OBJECT_CODE => 'LNS_LOAN',
1765 P_NOTES => l_loan_approve_note,
1766 P_NOTES_DETAIL => null,
1767 P_ENTERED_BY => LNS_UTILITY_PUB.created_by,
1768 P_ENTERED_DATE => LNS_UTILITY_PUB.creation_date,
1769 P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
1770 P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
1771 P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
1772 P_CREATED_BY => LNS_UTILITY_PUB.created_by,
1773 P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
1774 X_JTF_NOTE_ID => l_note_id ,
1775 P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
1776 P_NOTE_STATUS => 'I', -- Visibility : public
1777 X_RETURN_STATUS => l_return_status,
1778 X_MSG_COUNT => l_msg_count,
1779 X_MSG_DATA => l_msg_data,
1780 P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
1781
1782 );
1783
1784 -- Check for errors
1785
1786 IF (fnd_msg_pub.count_msg > 0) THEN
1787 FOR i IN 1..fnd_msg_pub.count_msg
1788 LOOP
1789 fnd_msg_pub.get( p_msg_index => i,
1790 p_encoded => 'F',
1791 p_data => l_msg_data,
1792 p_msg_index_out => l_msg_index_out
1793 );
1794 END LOOP;
1795 ELSE
1796 LogMessage(FND_LOG.LEVEL_STATEMENT,'Created note : ' || to_char(l_note_id));
1797 END IF;
1798
1799 LogMessage(FND_LOG.LEVEL_STATEMENT,'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1800 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_note_id : ' || l_note_id);
1801 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_RETURN_STATUS : ' || l_RETURN_STATUS);
1802 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_COUNT : ' || l_MSG_COUNT);
1803 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_DATA : ' || l_MSG_DATA);
1804 LogMessage(FND_LOG.LEVEL_STATEMENT,'End calling JTF_NOTES_PUB.CREATE_NOTE' );
1805
1806 END IF;
1807
1808 resultout := 'COMPLETE:' || 'Y';
1809
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 resultout := 'COMPLETE:' || 'N';
1813 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
1814 || ' Exception -');
1815 wf_core.context('LNSWF', 'PROCESS_LOAN_APPROVAL', itemtype, itemkey,
1816 to_char(actid), funcmode); RAISE;
1817 END PROCESS_LOAN_APPROVAL;
1818
1819 /*========================================================================
1820 | PRIVATE PROCEDURE PROCESS_LOAN_REJECTION
1821 |
1822 | DESCRIPTION
1823 | This procedure insters/updates the Loan Rejection Status in LNS_APPROVAL_ACTIONS table.
1824 |
1825 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1826 |
1827 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1828 | None
1829 |
1830 | PARAMETERS
1831 | itemtype in Item Type
1832 | itemkey in Item Key
1833 | actid in Action Id
1834 | funcmode in Function Mode
1835 | resultout out Result Out
1836 |
1837 | KNOWN ISSUES
1838 | None
1839 |
1840 |
1841 | NOTES
1842 | Any interesting aspect of the code in the package body which needs
1843 | to be stated.
1844 |
1845 | MODIFICATION HISTORY
1846 | Date Author Description of Changes
1847 | 23-Aug-2009 avepati Created
1848 | 23-Aug-2009 avepati bug 8764310 - Loan Notification Missing Approve and Reject Buttons
1849 | 10-Dec-2009 avepati Bug 9092928 - Loan Approval/Rejection Notes is not saved
1850 *=======================================================================*/
1851 PROCEDURE PROCESS_LOAN_REJECTION(itemtype in varchar2,
1852 itemkey in varchar2,
1853 actid in number,
1854 funcmode in varchar2,
1855 resultout out NOCOPY varchar2 ) IS
1856 /*-----------------------------------------------------------------------+
1857 | Local Variable Declarations and initializations |
1858 +-----------------------------------------------------------------------*/
1859
1860 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOAN_REJECTION';
1861 l_api_version CONSTANT NUMBER := 1.0;
1862 l_object_version_number NUMBER := 1;
1863 l_nid NUMBER;
1864 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
1865 l_loan_reject_action_rec LNS_APPROVAL_ACTION_PUB.APPROVAL_ACTION_REC_TYPE;
1866 l_loan_reject_note VARCHAR2(2000);
1867 l_RETURN_STATUS LNS_LOAN_PRODUCTS_ALL.LOAN_APPR_REQ_FLAG%TYPE;
1868 G_JTF_NOTE_CONTEXTS_TAB JTF_NOTES_PUB.JTF_NOTE_CONTEXTS_TBL_TYPE;
1869 l_action_id NUMBER;
1870 l_note_id NUMBER;
1871 l_MSG_DATA VARCHAR2(32767);
1872 l_MSG_COUNT NUMBER;
1873 l_msg_index_out NUMBER;
1874 l_org_id NUMBER;
1875
1876 BEGIN
1877 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1878
1879 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemtype = ' || itemtype);
1880 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemkey = ' || itemkey);
1881 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'actid = ' || actid);
1882 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'funcmode = ' || funcmode);
1883
1884 SET_SESSION_CONTEXT(itemtype, itemkey);
1885
1886 IF (funcmode <> wf_engine.eng_run) THEN
1887 resultout := 'COMPLETE:' || 'N';
1888 return;
1889 END IF;
1890
1891 l_loan_id := wf_engine.GetItemAttrNumber
1892 ( itemtype => itemtype,
1893 itemkey => itemkey,
1894 aname => 'LNS_LOAN_ID');
1895
1896 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In PROCESS_LOAN_REJECTION l_loan_id : ' || l_loan_id);
1897
1898 --gparuchu - Code added to make sure correct object_version_number is passed
1899 --inorder to make sure the header table gets updated correctly upon Rejection process.
1900
1901 IF (l_loan_id IS NOT NULL) THEN
1902 select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
1903 from lns_loan_headers_all loan
1904 where loan.loan_id = l_loan_id;
1905 END IF;
1906
1907 l_loan_reject_note := wf_engine.GetItemAttrText
1908 ( itemtype => itemtype,
1909 itemkey => itemkey,
1910 aname => 'WF_NOTE');
1911
1912 G_LAST_ENTERED_NOTE := l_loan_reject_note;
1913 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In PROCESS_LOAN_REJECTION setting G_LAST_ENTERED_NOTE : ' || G_LAST_ENTERED_NOTE);
1914 --GK Test. Remove this later
1915 /* wf_engine.SetItemAttrText (itemtype => itemtype,
1916 itemkey => itemkey,
1917 aname => 'LNS_LAST_ENTERED_NOTE',
1918 avalue => l_loan_reject_note);
1919 */
1920 select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_loan_reject_action_rec.action_id from dual;
1921 l_loan_reject_action_rec.created_by := LNS_UTILITY_PUB.CREATED_BY;
1922 l_loan_reject_action_rec.creation_date := LNS_UTILITY_PUB.CREATION_DATE;
1923 l_loan_reject_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
1924 l_loan_reject_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
1925 l_loan_reject_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
1926 l_loan_reject_action_rec.object_version_number := l_object_version_number;
1927 l_loan_reject_action_rec.loan_id := l_loan_id;
1928 l_loan_reject_action_rec.action_type := 'REJECT';
1929 l_loan_reject_action_rec.amount := null;
1930 l_loan_reject_action_rec.reason_code := null;
1931 l_loan_reject_action_rec.attribute_category := null;
1932 l_loan_reject_action_rec.attribute1 := null;
1933 l_loan_reject_action_rec.attribute2 := null;
1934 l_loan_reject_action_rec.attribute3 := null;
1935 l_loan_reject_action_rec.attribute4 := null;
1936 l_loan_reject_action_rec.attribute5 := null;
1937 l_loan_reject_action_rec.attribute6 := null;
1938 l_loan_reject_action_rec.attribute7 := null;
1939 l_loan_reject_action_rec.attribute8 := null;
1940 l_loan_reject_action_rec.attribute9 := null;
1941 l_loan_reject_action_rec.attribute10 := null;
1942 l_loan_reject_action_rec.attribute11 := null;
1943 l_loan_reject_action_rec.attribute12 := null;
1944 l_loan_reject_action_rec.attribute13 := null;
1945 l_loan_reject_action_rec.attribute14 := null;
1946 l_loan_reject_action_rec.attribute15 := null;
1947 l_loan_reject_action_rec.attribute16 := null;
1948 l_loan_reject_action_rec.attribute17 := null;
1949 l_loan_reject_action_rec.attribute18 := null;
1950 l_loan_reject_action_rec.attribute19 := null;
1951 l_loan_reject_action_rec.attribute20 := null;
1952
1953 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Start calling LNS_APPROVAL_ACTION_PUB.create_approval_action' );
1954
1955 LNS_APPROVAL_ACTION_PUB.create_approval_action (p_init_msg_list => FND_API.G_TRUE,
1956 p_approval_action_rec => l_loan_reject_action_rec,
1957 x_action_id => l_action_id,
1958 X_RETURN_STATUS => l_RETURN_STATUS,
1959 X_MSG_COUNT => l_MSG_COUNT,
1960 X_MSG_DATA => l_MSG_DATA );
1961
1962 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1963 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_action_id : ' || l_action_id);
1964 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_RETURN_STATUS : ' || l_RETURN_STATUS);
1965 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_COUNT : ' || l_MSG_COUNT);
1966 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_DATA : ' || l_MSG_DATA);
1967
1968 LogMessage(FND_LOG.LEVEL_STATEMENT, 'END calling LNS_APPROVAL_ACTION_PUB.create_approval_action' );
1969 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In PROCESS_LOAN_REJECTION l_loan_reject_note : ' || l_loan_reject_note);
1970
1971 IF (l_loan_reject_note is not null AND length(trim(l_loan_reject_note)) > 0) THEN
1972
1973 --added by GPARUCHU to set org correctly for JTF_NOTES_PUB.validate_object method
1974 -- to correctly pick the loan against given loan_id
1975 -- MO_GLOBAL.GET_CURRENT_ORG_ID() doesnt work here as it returns null when an external user
1976 -- is trying to approve a loan
1977
1978 --mo_global.set_policy_context('M', null);
1979 MO_GLOBAL.INIT('LNS');
1980 MO_GLOBAL.set_policy_context('S',l_org_id);
1981
1982 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type := 'LNS_APPROVAL_ACTION';
1983 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type_id := l_loan_reject_action_rec.action_id;
1984 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
1985 G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
1986 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
1987 G_JTF_NOTE_CONTEXTS_TAB(1).creation_date := LNS_UTILITY_PUB.creation_date;
1988 G_JTF_NOTE_CONTEXTS_TAB(1).created_by := LNS_UTILITY_PUB.created_by;
1989
1990 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Start calling JTF_NOTES_PUB.CREATE_NOTE' );
1991
1992 JTF_NOTES_PUB.CREATE_NOTE(
1993 P_API_VERSION => 1.0,
1994 P_INIT_MSG_LIST => FND_API.G_TRUE,
1995 P_COMMIT => FND_API.G_TRUE,
1996 P_JTF_NOTE_ID => FND_API.G_MISS_NUM,
1997 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1998 P_SOURCE_OBJECT_ID => l_loan_id,
1999 P_SOURCE_OBJECT_CODE => 'LNS_LOAN',
2000 P_NOTES => l_loan_reject_note,
2001 P_NOTES_DETAIL => null,
2002 P_ENTERED_BY => LNS_UTILITY_PUB.created_by,
2003 P_ENTERED_DATE => LNS_UTILITY_PUB.creation_date,
2004 P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
2005 P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
2006 P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
2007 P_CREATED_BY => LNS_UTILITY_PUB.created_by,
2008 P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
2009 X_JTF_NOTE_ID => l_note_id ,
2010 P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
2011 P_NOTE_STATUS => 'I', -- Visibility : public
2012 X_RETURN_STATUS => l_return_status,
2013 X_MSG_COUNT => l_msg_count,
2014 X_MSG_DATA => l_msg_data,
2015 P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
2016 );
2017
2018 -- Check for errors
2019
2020 IF (fnd_msg_pub.count_msg > 0) THEN
2021 FOR i IN 1..fnd_msg_pub.count_msg
2022 LOOP
2023 fnd_msg_pub.get( p_msg_index => i,
2024 p_encoded => 'F',
2025 p_data => l_msg_data,
2026 p_msg_index_out => l_msg_index_out
2027 );
2028
2029 END LOOP;
2030 ELSE
2031 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Created note : ' || to_char(l_note_id));
2032 END IF;
2033
2034 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
2035 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_note_id : ' || l_note_id);
2036 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_RETURN_STATUS : ' || l_RETURN_STATUS);
2037 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_COUNT : ' || l_MSG_COUNT);
2038 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_DATA : ' || l_MSG_DATA);
2039
2040 LogMessage(FND_LOG.LEVEL_STATEMENT, 'END calling JTF_NOTES_PUB.CREATE_NOTE' );
2041
2042 END IF;
2043
2044 resultout := 'COMPLETE:' || 'Y';
2045
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 resultout := 'COMPLETE:' || 'N';
2049 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
2050 || ' Exception -');
2051 wf_core.context('LNSWF', 'PROCESS_LOAN_REJECTION', itemtype, itemkey,
2052 to_char(actid), funcmode); RAISE;
2053 END PROCESS_LOAN_REJECTION;
2054
2055
2056 /*========================================================================
2057 | PRIVATE PROCEDURE REQUEST_LOAN_INFORMATION
2058 |
2059 | DESCRIPTION
2060 | This procedure updates the Loan Status back to Incomplete in LNS_APPROVAL_ACTIONS table.
2061 |
2062 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2063 |
2064 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2065 | None
2066 |
2067 | PARAMETERS
2068 | itemtype in Item Type
2069 | itemkey in Item Key
2070 | actid in Action Id
2071 | funcmode in Function Mode
2072 | resultout out Result Out
2073 |
2074 | KNOWN ISSUES
2075 | None
2076 |
2077 |
2078 | NOTES
2079 | Any interesting aspect of the code in the package body which needs
2080 | to be stated.
2081 |
2082 | MODIFICATION HISTORY
2083 | Date Author Description of Changes
2084 | 23-Aug-2009 avepati Created
2085 | 23-Aug-2009 avepati bug 8764310 - Loan Notification Missing Approve and Reject Buttons
2086 | 10-Dec-2009 avepati Bug 9092928 - Loan Approval/Rejection Notes is not saved
2087 *=======================================================================*/
2088 PROCEDURE REQUEST_LOAN_INFORMATION(itemtype in varchar2,
2089 itemkey in varchar2,
2090 actid in number,
2091 funcmode in varchar2,
2092 resultout out NOCOPY varchar2 ) IS
2093 /*-----------------------------------------------------------------------+
2094 | Local Variable Declarations and initializations |
2095 +-----------------------------------------------------------------------*/
2096
2097 l_api_name CONSTANT VARCHAR2(30) := 'REQUEST_LOAN_INFORMATION';
2098 l_api_version CONSTANT NUMBER := 1.0;
2099 l_object_version_number NUMBER := 1;
2100 l_nid NUMBER;
2101 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
2102 l_req_loan_info_action_rec LNS_APPROVAL_ACTION_PUB.APPROVAL_ACTION_REC_TYPE;
2103 l_req_loan_info_note VARCHAR2(2000);
2104 l_RETURN_STATUS LNS_LOAN_PRODUCTS_ALL.LOAN_APPR_REQ_FLAG%TYPE;
2105 G_JTF_NOTE_CONTEXTS_TAB JTF_NOTES_PUB.JTF_NOTE_CONTEXTS_TBL_TYPE;
2106 l_action_id NUMBER;
2107 l_note_id NUMBER;
2108 l_MSG_DATA VARCHAR2(32767);
2109 l_MSG_COUNT NUMBER;
2110 l_msg_index_out NUMBER;
2111 l_org_id NUMBER;
2112 l_approver_user_name VARCHAR2(320);
2113 approverRecord ame_util.approverRecord2;
2114 l_transaction_type fnd_profile_option_values.profile_option_value%TYPE;
2115 l_IS_AME_INSTALLED fnd_profile_option_values.profile_option_value%TYPE;
2116
2117 BEGIN
2118 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' +');
2119
2120 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemtype = ' || itemtype);
2121 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'itemkey = ' || itemkey);
2122 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'actid = ' || actid);
2123 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'funcmode = ' || funcmode);
2124
2125 SET_SESSION_CONTEXT(itemtype, itemkey);
2126
2127 IF (funcmode <> wf_engine.eng_run) THEN
2128 resultout := 'COMPLETE:' || 'N';
2129 return;
2130 END IF;
2131
2132 l_loan_id := wf_engine.GetItemAttrNumber
2133 ( itemtype => itemtype,
2134 itemkey => itemkey,
2135 aname => 'LNS_LOAN_ID');
2136 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_loan_id from GetItemAttrNumber = ' || l_loan_id);
2137
2138 --gparuchu - Code added to make sure correct object_version_number is passed
2139 --inorder to make sure the header table gets updated correctly upon Approval process.
2140 IF (l_loan_id IS NOT NULL) THEN
2141 select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
2142 from lns_loan_headers_all loan
2143 where loan.loan_id = l_loan_id;
2144 END IF;
2145
2146 l_req_loan_info_note := wf_engine.GetItemAttrText
2147 ( itemtype => itemtype,
2148 itemkey => itemkey,
2149 aname => 'WF_NOTE');
2150
2151 G_LAST_ENTERED_NOTE := l_req_loan_info_note;
2152 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'G_LAST_ENTERED_NOTE = ' || G_LAST_ENTERED_NOTE);
2153
2154 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Setting l_req_loan_info_action_rec...');
2155 select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_req_loan_info_action_rec.action_id from dual;
2156 l_req_loan_info_action_rec.created_by := LNS_UTILITY_PUB.CREATED_BY;
2157 l_req_loan_info_action_rec.creation_date := LNS_UTILITY_PUB.CREATION_DATE;
2158 l_req_loan_info_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
2159 l_req_loan_info_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
2160 l_req_loan_info_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
2161 l_req_loan_info_action_rec.object_version_number := l_object_version_number;
2162 l_req_loan_info_action_rec.loan_id := l_loan_id;
2163 l_req_loan_info_action_rec.action_type := 'REQUEST_FOR_INFO';
2164 l_req_loan_info_action_rec.amount := null;
2165 l_req_loan_info_action_rec.reason_code := null;
2166 l_req_loan_info_action_rec.attribute_category := null;
2167 l_req_loan_info_action_rec.attribute1 := null;
2168 l_req_loan_info_action_rec.attribute2 := null;
2169 l_req_loan_info_action_rec.attribute3 := null;
2170 l_req_loan_info_action_rec.attribute4 := null;
2171 l_req_loan_info_action_rec.attribute5 := null;
2172 l_req_loan_info_action_rec.attribute6 := null;
2173 l_req_loan_info_action_rec.attribute7 := null;
2174 l_req_loan_info_action_rec.attribute8 := null;
2175 l_req_loan_info_action_rec.attribute9 := null;
2176 l_req_loan_info_action_rec.attribute10 := null;
2177 l_req_loan_info_action_rec.attribute11 := null;
2178 l_req_loan_info_action_rec.attribute12 := null;
2179 l_req_loan_info_action_rec.attribute13 := null;
2180 l_req_loan_info_action_rec.attribute14 := null;
2181 l_req_loan_info_action_rec.attribute15 := null;
2182 l_req_loan_info_action_rec.attribute16 := null;
2183 l_req_loan_info_action_rec.attribute17 := null;
2184 l_req_loan_info_action_rec.attribute18 := null;
2185 l_req_loan_info_action_rec.attribute19 := null;
2186 l_req_loan_info_action_rec.attribute20 := null;
2187
2188 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Calling LNS_APPROVAL_ACTION_PUB.create_approval_action...');
2189 LNS_APPROVAL_ACTION_PUB.create_approval_action (p_init_msg_list => FND_API.G_TRUE,
2190 p_approval_action_rec => l_req_loan_info_action_rec,
2191 x_action_id => l_action_id,
2192 X_RETURN_STATUS => l_RETURN_STATUS,
2193 X_MSG_COUNT => l_MSG_COUNT,
2194 X_MSG_DATA => l_MSG_DATA );
2195
2196 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
2197 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_action_id : ' || l_action_id);
2198 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_RETURN_STATUS : ' || l_RETURN_STATUS);
2199 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_COUNT : ' || l_MSG_COUNT);
2200 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_DATA : ' || l_MSG_DATA);
2201
2202 LogMessage(FND_LOG.LEVEL_STATEMENT, 'END calling LNS_APPROVAL_ACTION_PUB.create_approval_action' );
2203
2204 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In REQUEST_LOAN_INFORMATION l_req_loan_info_note : ' || l_req_loan_info_note);
2205
2206 --added by GPARUCHU to make sure that when an Approver requests for information, his status is made
2207 --null in AME Engine so that next time AME tries to get the Approver the same Approver is picked up.
2208 l_approver_user_name := wf_engine.GetItemAttrText ( itemtype => itemtype,
2209 itemkey => itemkey,
2210 aname => 'LNS_PRIMARY_ROLE');
2211 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Approver User Name : ' || l_approver_user_name);
2212
2213 --added to get the profile name correctly
2214 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In REQUEST_LOAN_INFORMATION fnd_global.user_id : ' || fnd_global.user_id);
2215 --FND_GLOBAL.APPS_INITIALIZE(fnd_global.user_id,fnd_global.resp_id,206);
2216 l_transaction_type := FND_PROFILE.value('LNS_AME_TRANSACTION_TYPE');
2217 l_IS_AME_INSTALLED := FND_PROFILE.value('AME_INSTALLED_FLAG');
2218 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In REQUEST_LOAN_INFORMATION l_IS_AME_INSTALLED : ' || l_IS_AME_INSTALLED);
2219 LogMessage(FND_LOG.LEVEL_STATEMENT, 'In REQUEST_LOAN_INFORMATION l_transaction_type : ' || l_transaction_type);
2220
2221 --Only when AME:Installed and LNS_AME_TRANSACTION_TYPE profile is setup, need to perform following action
2222
2223 IF(l_transaction_type IS NOT NULL AND l_IS_AME_INSTALLED IS NOT NULL) THEN
2224 approverRecord.name := l_approver_user_name;
2225 approverRecord.approval_status := ame_util.nullStatus;
2226 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating AME Engine with Null Status for Approver - ' || l_approver_user_name);
2227
2228 AME_API2.updateApprovalStatus(applicationIdIn => 206,
2229 transactionTypeIn => l_transaction_type,
2230 transactionIdIn => l_loan_id,
2231 approverIn => approverRecord,
2232 updateItemIn => TRUE);
2233 END IF;
2234
2235 IF (l_req_loan_info_note is not null AND length(trim(l_req_loan_info_note)) > 0) THEN
2236
2237 --added by GPARUCHU to set org correctly for JTF_NOTES_PUB.validate_object method
2238 -- to correctly pick the loan against given loan_id
2239 -- MO_GLOBAL.GET_CURRENT_ORG_ID() doesnt work here as it returns null when an external user
2240 -- is trying to approve a loan
2241
2242 --mo_global.set_policy_context('M', null);
2243 MO_GLOBAL.INIT('LNS');
2244 MO_GLOBAL.set_policy_context('S',l_org_id);
2245
2246 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type := 'LNS_APPROVAL_ACTION';
2247 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type_id := l_req_loan_info_action_rec.action_id;
2248 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
2249 G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
2250 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
2251 G_JTF_NOTE_CONTEXTS_TAB(1).creation_date := LNS_UTILITY_PUB.creation_date;
2252 G_JTF_NOTE_CONTEXTS_TAB(1).created_by := LNS_UTILITY_PUB.created_by;
2253
2254 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Before calling JTF_NOTES_PUB.CREATE_NOTE' );
2255
2256 JTF_NOTES_PUB.CREATE_NOTE(
2257 P_API_VERSION => 1.0,
2258 P_INIT_MSG_LIST => FND_API.G_TRUE,
2259 P_COMMIT => FND_API.G_TRUE,
2260 P_JTF_NOTE_ID => FND_API.G_MISS_NUM,
2261 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2262 P_SOURCE_OBJECT_ID => l_loan_id,
2263 P_SOURCE_OBJECT_CODE => 'LNS_LOAN',
2264 P_NOTES => l_req_loan_info_note,
2265 P_NOTES_DETAIL => null,
2266 P_ENTERED_BY => LNS_UTILITY_PUB.created_by,
2267 P_ENTERED_DATE => LNS_UTILITY_PUB.creation_date,
2268 P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
2269 P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
2270 P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
2271 P_CREATED_BY => LNS_UTILITY_PUB.created_by,
2272 P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
2273 X_JTF_NOTE_ID => l_note_id ,
2274 P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
2275 P_NOTE_STATUS => 'I', -- Visibility : public
2276 X_RETURN_STATUS => l_return_status,
2277 X_MSG_COUNT => l_msg_count,
2278 X_MSG_DATA => l_msg_data,
2279 P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
2280 );
2281
2282 -- Check for errors
2283
2284 IF (fnd_msg_pub.count_msg > 0) THEN
2285 FOR i IN 1..fnd_msg_pub.count_msg
2286 LOOP
2287 fnd_msg_pub.get( p_msg_index => i,
2288 p_encoded => 'F',
2289 p_data => l_msg_data,
2290 p_msg_index_out => l_msg_index_out
2291 );
2292 END LOOP;
2293 ELSE
2294 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Created note : ' || to_char(l_note_id));
2295 END IF;
2296
2297 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
2298 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_note_id : ' || l_note_id);
2299 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_RETURN_STATUS : ' || l_RETURN_STATUS);
2300 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_COUNT : ' || l_MSG_COUNT);
2301 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MSG_DATA : ' || l_MSG_DATA);
2302
2303
2304 LogMessage(FND_LOG.LEVEL_STATEMENT, 'END calling JTF_NOTES_PUB.CREATE_NOTE' );
2305
2306 END IF;
2307
2308 resultout := 'COMPLETE:' || 'Y';
2309
2310 EXCEPTION
2311 WHEN OTHERS THEN
2312 resultout := 'COMPLETE:' || 'N';
2313 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
2314 || ' Exception -');
2315 wf_core.context('LNSWF', 'REQUEST_LOAN_INFORMATION', itemtype, itemkey,
2316 to_char(actid), funcmode); RAISE;
2317 END REQUEST_LOAN_INFORMATION;
2318
2319
2320
2321 /*========================================================================
2322 | PUBLIC PROCEDURE APPROVE_LOAN_FROM_NTF
2323 |
2324 | DESCRIPTION
2325 | This procedure hides/displays the loan Approve/Reject button in Loan Approval Notification.
2326 | Based on the profile LNS: Loan Approval From Notificaiton
2327 | PSEUDO CODE/LOGIC
2328 |
2329 | PARAMETERS
2330 | ITEMKEY IN Standard in parameter
2331 | ACTID IN Standard in parameter
2332 | FUNCMODE IN Standard in parameter
2333 | RESULTOUT OUT Standard out parameter
2334 |
2335 | KNOWN ISSUES
2336 | None
2337 |
2338 | NOTES
2339 |
2340 | MODIFICATION HISTORY
2341 | Date Author Description of Changes
2342 | 10-Dec-2009 avepati Created
2343 | 10-Dec-2009 avepati bug 9092928 - Loan Approval/Rejection Notes is not saved
2344 |
2345 *=======================================================================*/
2346 PROCEDURE APPROVE_LOAN_FROM_NTF(itemtype in varchar2,
2347 itemkey in varchar2,
2348 actid in number,
2349 funcmode in varchar2,
2350 resultout out NOCOPY varchar2 ) IS
2351
2352 l_loan_approval_from_ntf fnd_profile_option_values.profile_option_value%TYPE;
2353 BEGIN
2354 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || 'LNS_WORK_FLOW.APPROVE_LOAN_FROM_NTF' || ' +');
2355
2356 IF (funcmode <> wf_engine.eng_run) THEN
2357 resultout := wf_engine.eng_null;
2358 return;
2359 END IF;
2360
2361 l_loan_approval_from_ntf := FND_PROFILE.value('LNS_APPROVE_LOAN_FROM_NTF');
2362
2363 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_approval_from_ntf : ' || l_loan_approval_from_ntf);
2364
2365 IF NVL(l_loan_approval_from_ntf,'N') = 'N' THEN
2366 resultout := 'COMPLETE:' || 'N';
2367 ELSE
2368 resultout := 'COMPLETE:' || 'Y';
2369 END IF;
2370
2371 EXCEPTION
2372 WHEN OTHERS THEN
2373 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || 'LNS_WORK_FLOW.APPROVE_LOAN_FROM_NTF'
2374 || ' Exception -');
2375 wf_core.context('LNSWF', 'APPROVE_LOAN_FROM_NTF', itemtype, itemkey,
2376 to_char(actid), funcmode); RAISE;
2377
2378 END APPROVE_LOAN_FROM_NTF;
2379
2380 /*========================================================================
2381 | PUBLIC PROCEDURE SYNCH_EVENT_ACTIONS
2382 |
2383 | DESCRIPTION
2384 | This procedure adds event actions for newly created user extensible
2385 | Loan Types.
2386 | PSEUDO CODE/LOGIC
2387 |
2388 | PARAMETERS
2389 | NONE.
2390 |
2391 | KNOWN ISSUES
2392 | None
2393 |
2394 | NOTES
2395 |
2396 | MODIFICATION HISTORY
2397 | Date Author Description of Changes
2398 | 23-Feb-2005 GBELLARY Created
2399 |
2400 *=======================================================================*/
2401 PROCEDURE SYNCH_EVENT_ACTIONS IS
2402 /*-----------------------------------------------------------------------+
2403 | Local Variable Declarations and initializations |
2404 +-----------------------------------------------------------------------*/
2405
2406 l_api_name CONSTANT VARCHAR2(30) := 'SYNCH_EVENT_ACTIONS';
2407 l_api_version CONSTANT NUMBER := 1.0;
2408 BEGIN
2409 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2410 insert into lns_event_actions (
2411 EVENT_ACTION_ID
2412 , EVENT_ID
2413 , EVENT_ACTION_NAME
2414 , DESCRIPTION
2415 , LOAN_TYPE_ID
2416 , ACTION_TYPE
2417 , ENABLED_FLAG
2418 , API_NAME
2419 , NOTIFICATION_TYPE
2420 , SETUP_TYPE
2421 , PRIMARY_RECIPIENT_TYPE
2422 , PRIMARY_RECIPIENT_NAME
2423 , SECONDARY_RECIPIENT_TYPE
2424 , SECONDARY_RECIPIENT_NAME
2425 , PRIORITY_NUM
2426 , DAYS_PRIOR_NUM
2427 , ACTIVE_FOR_NUM
2428 , DELIVERY_METHOD
2429 , OBJECT_VERSION_NUMBER
2430 , CREATION_DATE
2431 , CREATED_BY
2432 , LAST_UPDATE_DATE
2433 , LAST_UPDATED_BY
2434 , LAST_UPDATE_LOGIN )
2435 select LNS_EVENT_ACTIONS_S.nextval --event_action_id
2436 , ea.EVENT_ID
2437 , ea.EVENT_ACTION_NAME
2438 , ea.DESCRIPTION
2439 , missingvalues.LOAN_TYPE_ID
2440 , ea.ACTION_TYPE
2441 , 'Y' --enabled_flag
2442 , ea.API_NAME
2443 , ea.NOTIFICATION_TYPE
2444 , ea.SETUP_TYPE
2445 , ea.PRIMARY_RECIPIENT_TYPE
2446 , ea.PRIMARY_RECIPIENT_NAME
2447 , ea.SECONDARY_RECIPIENT_TYPE
2448 , ea.SECONDARY_RECIPIENT_NAME
2449 , ea.PRIORITY_NUM
2450 , ea.DAYS_PRIOR_NUM
2451 , ea.ACTIVE_FOR_NUM
2452 , ea.DELIVERY_METHOD
2453 , ea.OBJECT_VERSION_NUMBER
2454 , sysdate
2455 , LNS_UTILITY_PUB.CREATED_BY
2456 , sysdate
2457 , LNS_UTILITY_PUB.LAST_UPDATED_BY
2458 , LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
2459 from lns_event_actions ea, lns_events ev,
2460 (select loan_class_code, loan_type_id
2461 from lns_loan_types_vl
2462 minus
2463 select ev.loan_class_code loan_class_code, ea.loan_type_id loan_type_id
2464 from lns_events ev, lns_event_actions ea
2465 where ea.event_id = ev.event_id
2466 and ea.event_action_name = 'NOTIFICATION') missingvalues
2467 where ev.loan_class_code = missingvalues.loan_class_code
2468 and ea.event_id = ev.event_id
2469 and ea.loan_type = decode(ev.loan_class_code,'ERS','ERS','BUSINESS')
2470 and ea.event_action_name = 'NOTIFICATION';
2471 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Error in synch event actions: ' || sqlerrm);
2475 /* This error message needs to be seeded in the future */
2476 FND_MESSAGE.SET_NAME('LNS', 'LNS_ERROR_SYNCH_EVTS');
2477 FND_MSG_PUB.ADD;
2478 raise;
2479 END;
2480
2481 /*========================================================================
2482 | PUBLIC PROCEDURE DELETE_LNS_EVENT_ACTIONS
2483 |
2484 | DESCRIPTION
2485 | This procedure deletes the event action records from the table
2486 | lns_event_actions table for the provided loanType.
2487 |
2488 | PSEUDO CODE/LOGIC
2489 |
2490 | PARAMETERS
2491 | p_loan_type_id IN Standard in parameter
2492 |
2493 | KNOWN ISSUES
2494 | None
2495 |
2496 | NOTES
2497 |
2498 | MODIFICATION HISTORY
2499 | Date Author Description of Changes
2500 | 16-Mar-2009 MBOLLI Created
2501 |
2502 *=======================================================================*/
2503 PROCEDURE DELETE_LNS_EVENT_ACTIONS ( p_loan_type_id IN NUMBER) IS
2504 /*-----------------------------------------------------------------------+
2505 | Local Variable Declarations and initializations |
2506 +-----------------------------------------------------------------------*/
2507
2508 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_LNS_EVENT_ACTIONS';
2509 l_api_version CONSTANT NUMBER := 1.0;
2510 BEGIN
2511 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2512
2513 DELETE FROM lns_event_actions
2514 WHERE loan_type_id = p_loan_type_id;
2515
2516 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Error in delete_lns_event_actions: ' || sqlerrm);
2520 /* This error message needs to be seeded in the future */
2521 FND_MESSAGE.SET_NAME('LNS', 'LNS_ERR_DEL_EVNT_ACTION');
2522 FND_MSG_PUB.ADD;
2523 raise;
2524 END;
2525
2526 /*========================================================================
2527 | PUBLIC PROCEDURE FindNextApprover
2528 |
2529 | DESCRIPTION
2530 | This procedure is called to find the next approver from the transaction
2531 | type for a given loan.
2532 |
2533 | PSEUDO CODE/LOGIC
2534 |
2535 | PARAMETERS
2536 | p_loan_type_id IN Standard in parameter
2537 |
2538 | KNOWN ISSUES
2539 | None
2540 |
2541 | NOTES
2542 |
2543 | MODIFICATION HISTORY
2544 | Date Author Description of Changes
2545 |
2546 *=======================================================================*/
2547 PROCEDURE FindNextApprover (
2548 p_item_type IN VARCHAR2,
2549 p_item_key IN VARCHAR2,
2550 p_ame_trx_type IN VARCHAR2,
2551 x_approver_user_name OUT NOCOPY VARCHAR2) IS
2552
2553 l_next_approver ame_util.approversTable2;
2554 l_error_message fnd_new_messages.message_text%TYPE;
2555 approvalComplete varchar2(20);
2556 i PLS_INTEGER := 1 ;
2557 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
2558 l_api_name CONSTANT VARCHAR2(30) := 'FindNextApprover';
2559
2560 BEGIN
2561 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2562 l_loan_id := wf_engine.GetItemAttrNumber
2563 ( itemtype => p_item_type,
2564 itemkey => p_item_key,
2565 aname => 'LNS_LOAN_ID');
2566 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_id : ' || l_loan_id);
2567
2568 ame_api2.getNextApprovers4(applicationIdIn => 206 ,
2569 transactionTypeIn => p_ame_trx_type,
2570 transactionIdIn => l_loan_id,
2571 approvalProcessCompleteYNOut => approvalComplete,
2572 nextApproversOut => l_next_approver) ;
2573
2574 LogMessage(FND_LOG.LEVEL_STATEMENT, 'After calling ame_api2.getNextApprovers4');
2575
2576 IF (l_next_approver.count < 1) THEN
2577 -- no more approvers left
2578 x_approver_user_name := null;
2579 RETURN;
2580 else
2581 x_approver_user_name := l_next_approver(i).name;
2582 LogMessage(FND_LOG.LEVEL_STATEMENT, 'x_approver_user_name : ' || x_approver_user_name);
2583 end if;
2584
2585 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2586 EXCEPTION
2587 WHEN OTHERS THEN
2588
2589 wf_core.context(
2590 pkg_name => 'LNS_WORK_FLOW',
2591 proc_name => 'FINDNEXTAPPROVER',
2592 arg1 => p_item_type,
2593 arg2 => p_item_key,
2594 arg3 => NULL,
2595 arg4 => NULL,
2596 arg5 => null);
2597
2598 RAISE;
2599
2600 END FindNextApprover;
2601
2602 /*========================================================================
2603 | PUBLIC PROCEDURE AME_FIND_PRIMARY_APPROVER
2604 |
2605 | DESCRIPTION
2606 | This procedure is called to find the the first primary approver,
2607 | as well as any subsequent approver from AME. After retrieving the
2608 | person id from AME, it stores in a workflow attribute appropriately
2609 | named LNS_PRIMARY_ROLE. It also calls getemployeeinfo to set some
2610 | attributes to make sure notifications are sent smoothly to this approver.
2611 |
2612 | PSEUDO CODE/LOGIC
2613 |
2614 | PARAMETERS
2615 | p_loan_type_id IN Standard in parameter
2616 |
2617 | KNOWN ISSUES
2618 | None
2619 |
2620 | NOTES
2621 |
2622 | MODIFICATION HISTORY
2623 | Date Author Description of Changes
2624 |
2625 *=======================================================================*/
2626 /*************************************************************************/
2627
2628
2629 PROCEDURE AME_FIND_PRIMARY_APPROVER(
2630 p_item_type IN VARCHAR2,
2631 p_item_key IN VARCHAR2,
2632 p_actid IN NUMBER,
2633 p_funcmode IN VARCHAR2,
2634 p_result OUT NOCOPY VARCHAR2) IS
2635
2636
2637 l_next_approver ame_util.approverrecord;
2638 l_error_message fnd_new_messages.message_text%TYPE;
2639 l_approver_user_name VARCHAR2(320);
2640 l_transaction_type fnd_profile_option_values.profile_option_value%TYPE;
2641
2642
2643 BEGIN
2644 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || 'LNS_WORK_FLOW.AME_FIND_PRIMARY_APPROVER' || ' +');
2645 --
2646 -- RUN mode - normal process execution
2647 --
2648 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_funcmode : ' || p_funcmode);
2649 IF (p_funcmode = 'RUN') THEN
2650
2651
2652 l_transaction_type := FND_PROFILE.value('LNS_AME_TRANSACTION_TYPE');
2653 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_transaction_type : ' || l_transaction_type);
2654 FindNextApprover(
2655 p_item_type => p_item_type,
2656 p_item_key => p_item_key,
2657 p_ame_trx_type => l_transaction_type, --G_TRANSACTION_TYPE,
2658 x_approver_user_name => l_approver_user_name);
2659
2660 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_approver_user_name : ' || l_approver_user_name);
2661
2662
2663
2664 IF (l_approver_user_name IS NOT NULL) THEN
2665 wf_engine.SetItemAttrText(
2666 itemtype => p_item_type,
2667 itemkey => p_item_key,
2668 aname => 'LNS_PRIMARY_ROLE',
2669 avalue => l_approver_user_name);
2670
2671 p_result := 'COMPLETE:T';
2672
2673 RETURN;
2674
2675 END IF;
2676
2677
2678 IF l_approver_user_name IS NULL THEN
2679 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_approver_user_name : ' || 'IS NULL');
2680 wf_engine.SetItemAttrText(
2681 itemtype => p_item_type,
2682 itemkey => p_item_key,
2683 aname => 'LNS_PRIMARY_ROLE',
2684 avalue => null);
2685
2686 -- no issue, return F to indicate no more approvers left.
2687 p_result := 'COMPLETE:F';
2688 RETURN;
2689
2690 END IF;
2691
2692
2693
2694 END IF; -- END of run mode
2695
2696 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || 'LNS_WORK_FLOW.AME_FIND_PRIMARY_APPROVER' || ' -');
2697 EXCEPTION
2698 WHEN OTHERS THEN
2699 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || 'LNS_WORK_FLOW.AME_FIND_PRIMARY_APPROVER'
2700 || ' Exception -');
2701
2702 wf_core.context(
2703 pkg_name => 'LNS_WORK_FLOW',
2704 proc_name => 'AME_FIND_PRIMARY_APPROVER',
2705 arg1 => p_item_type,
2706 arg2 => p_item_key,
2707 arg3 => p_funcmode,
2708 arg4 => to_char(p_actid),
2709 arg5 => null);
2710
2711 RAISE;
2712
2713 END AME_FIND_PRIMARY_APPROVER;
2714
2715 /*========================================================================
2716 | PUBLIC PROCEDURE UPDATE_APPROVAL_STATUS_TO_AME
2717 |
2718 | DESCRIPTION
2719 | This procedure is used to notify the AME_ENGINE when an approver Approves a loan.
2720 |
2721 | PSEUDO CODE/LOGIC
2722 |
2723 | PARAMETERS
2724 | p_loan_type_id IN Standard in parameter
2725 |
2726 | KNOWN ISSUES
2727 | None
2728 |
2729 | NOTES
2730 |
2731 | MODIFICATION HISTORY
2732 | Date Author Description of Changes
2733 |
2734 *=======================================================================*/
2735 /*************************************************************************/
2736
2737
2738 PROCEDURE UPDATE_APPROVAL_STATUS_TO_AME(p_item_type IN VARCHAR2,
2739 p_item_key IN VARCHAR2,
2740 p_actid IN NUMBER,
2741 p_funcmode IN VARCHAR2,
2742 p_result OUT NOCOPY VARCHAR2) IS
2743
2744
2745 l_approver_user_name VARCHAR2(320);
2746 approverRecord ame_util.approverRecord2;
2747 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
2748 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_APPROVAL_STATUS_TO_AME';
2749 l_transaction_type fnd_profile_option_values.profile_option_value%TYPE;
2750 l_loan_approve_note VARCHAR2(2000);
2751
2752 BEGIN
2753
2754 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2755 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_funcmode : ' || p_funcmode);
2756 l_approver_user_name := wf_engine.GetItemAttrText
2757 ( itemtype => p_item_type,
2758 itemkey => p_item_key,
2759 aname => 'LNS_PRIMARY_ROLE');
2760
2761 --Need to set l_approver_user_name into the LNS_INTERMEDIATE_APPROVER attribute
2762 -- for correctly having From value in the intermediate Approval Notification being send to loan agent
2763 wf_engine.SetItemAttrText (itemtype => p_item_type,
2764 itemkey => p_item_key,
2765 aname => 'LNS_INTERMEDIATE_APPROVER',
2766 avalue => l_approver_user_name);
2767
2768 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Approver User Name : ' || l_approver_user_name);
2769 l_loan_id := wf_engine.GetItemAttrNumber
2770 ( itemtype => p_item_type,
2771 itemkey => p_item_key,
2772 aname => 'LNS_LOAN_ID');
2773
2774 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_id : ' || l_loan_id);
2775 l_loan_approve_note := wf_engine.GetItemAttrText
2776 ( itemtype => p_item_type,
2777 itemkey => p_item_key,
2778 aname => 'WF_NOTE');
2779
2780 wf_engine.SetItemAttrText (itemtype => p_item_type,
2781 itemkey => p_item_key,
2782 aname => 'LNS_LAST_ENTERED_NOTE',
2783 avalue => l_loan_approve_note);
2784
2785
2786 LogMessage(FND_LOG.LEVEL_STATEMENT, 'fnd_global.user_id : ' || fnd_global.user_id);
2787 LogMessage(FND_LOG.LEVEL_STATEMENT, 'fnd_global.resp_id : ' || fnd_global.resp_id);
2788 FND_GLOBAL.APPS_INITIALIZE(fnd_global.user_id,fnd_global.resp_id,206);
2789 l_transaction_type := FND_PROFILE.value('LNS_AME_TRANSACTION_TYPE');
2790 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_transaction_type : ' || l_transaction_type);
2791
2792
2793 approverRecord.name := l_approver_user_name;
2794 approverRecord.approval_status := ame_util.approvedStatus;
2795
2796 AME_API2.updateApprovalStatus(applicationIdIn => 206,
2797 transactionTypeIn => l_transaction_type,
2798 transactionIdIn => l_loan_id,
2799 approverIn => approverRecord,
2800 updateItemIn => TRUE);
2801 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating AME Engine with Status Approved for user - '|| l_approver_user_name);
2802
2803
2804
2805 p_result := 'COMPLETE:'||'';
2806 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2807
2808 EXCEPTION
2809 WHEN OTHERS THEN
2810
2811 wf_core.context(
2812 pkg_name => 'LOANS_WORK_FLOW',
2813 proc_name => 'UPDATE_APPROVAL_STATUS_TO_AME',
2814 arg1 => p_item_type,
2815 arg2 => p_item_key,
2816 arg3 => null,
2817 arg4 => null,
2818 arg5 => null);
2819
2820 RAISE;
2821
2822 END UPDATE_APPROVAL_STATUS_TO_AME;
2823
2824
2825 /*========================================================================
2826 | PUBLIC PROCEDURE UPDATE_REJECTED_STATUS_TO_AME
2827 |
2828 | DESCRIPTION
2829 | This procedure is used to notify the AME_ENGINE when an Approver Rejects a loan.
2830 |
2831 | PSEUDO CODE/LOGIC
2832 |
2833 | PARAMETERS
2834 | p_loan_type_id IN Standard in parameter
2835 |
2836 | KNOWN ISSUES
2837 | None
2838 |
2839 | NOTES
2840 |
2841 | MODIFICATION HISTORY
2842 | Date Author Description of Changes
2843 |
2844 *=======================================================================*/
2845 /*************************************************************************/
2846 PROCEDURE UPDATE_REJECTED_STATUS_TO_AME(p_item_type IN VARCHAR2,
2847 p_item_key IN VARCHAR2,
2848 p_actid IN NUMBER,
2849 p_funcmode IN VARCHAR2,
2850 p_result OUT NOCOPY VARCHAR2) IS
2851
2852
2853 l_approver_user_name VARCHAR2(320);
2854 approverRecord ame_util.approverRecord2;
2855 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
2856 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REJECTED_STATUS_TO_AME';
2857 l_transaction_type fnd_profile_option_values.profile_option_value%TYPE;
2858
2859 BEGIN
2860
2861 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2862 l_approver_user_name := wf_engine.GetItemAttrText
2863 ( itemtype => p_item_type,
2864 itemkey => p_item_key,
2865 aname => 'LNS_PRIMARY_ROLE');
2866
2867 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_approver_user_name : ' || l_approver_user_name);
2868
2869 l_loan_id := wf_engine.GetItemAttrNumber
2870 ( itemtype => p_item_type,
2871 itemkey => p_item_key,
2872 aname => 'LNS_LOAN_ID');
2873 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_id : ' || l_loan_id);
2874
2875 LogMessage(FND_LOG.LEVEL_STATEMENT, 'fnd_global.user_id : ' || fnd_global.user_id);
2876 FND_GLOBAL.APPS_INITIALIZE(fnd_global.user_id,fnd_global.resp_id,206);
2877 l_transaction_type := FND_PROFILE.value('LNS_AME_TRANSACTION_TYPE');
2878 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_transaction_type : ' || l_transaction_type);
2879
2880
2881 approverRecord.name := l_approver_user_name;
2882 approverRecord.approval_status := ame_util.rejectStatus;
2883
2884 AME_API2.updateApprovalStatus(applicationIdIn => 206,
2885 transactionTypeIn => l_transaction_type,
2886 transactionIdIn => l_loan_id,
2887 approverIn => approverRecord,
2888 updateItemIn => TRUE);
2889
2890
2891 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating AME Engine with Status Rejected');
2892 p_result := 'COMPLETE:'||'';
2893 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2894
2895 EXCEPTION
2896 WHEN OTHERS THEN
2897
2898 wf_core.context(
2899 pkg_name => 'LOANS_WORK_FLOW',
2900 proc_name => 'UPDATE_REJECTED_STATUS_TO_AME',
2901 arg1 => p_item_type,
2902 arg2 => p_item_key,
2903 arg3 => null,
2904 arg4 => null,
2905 arg5 => null);
2906
2907 RAISE;
2908
2909 END UPDATE_REJECTED_STATUS_TO_AME;
2910
2911
2912 /*========================================================================
2913 | PUBLIC PROCEDURE APPROVE_LOAN_USING_AME
2914 |
2915 | DESCRIPTION
2916 | This procedure doesnt seems to be used from anywhere. Instead AMEEnabled
2917 | procedure is being used. After thoroughly verifying remove this procedure
2918 | GPARUCHU
2919 |
2920 | PSEUDO CODE/LOGIC
2921 |
2922 | PARAMETERS
2923 | p_loan_type_id IN Standard in parameter
2924 |
2925 | KNOWN ISSUES
2926 | None
2927 |
2928 | NOTES
2929 |
2930 | MODIFICATION HISTORY
2931 | Date Author Description of Changes
2932 |
2933 *=======================================================================*/
2934 /*************************************************************************/
2935 PROCEDURE APPROVE_LOAN_USING_AME(itemtype in varchar2,
2936 itemkey in varchar2,
2937 actid in number,
2938 funcmode in varchar2,
2939 resultout out NOCOPY varchar2 ) IS
2940
2941 l_loan_approval_using_ame fnd_profile_option_values.profile_option_value%TYPE;
2942 l_api_name CONSTANT VARCHAR2(30) := 'APPROVE_LOAN_USING_AME';
2943 BEGIN
2944 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2945
2946 IF (funcmode <> wf_engine.eng_run) THEN
2947 resultout := wf_engine.eng_null;
2948 return;
2949 END IF;
2950
2951 l_loan_approval_using_ame := FND_PROFILE.value('AME:Installed');
2952
2953 LogMessage(FND_LOG.LEVEL_STATEMENT, 'AME:Installed : ' || l_loan_approval_using_ame);
2954
2955 IF NVL(l_loan_approval_using_ame,'N') = 'N' THEN
2956 resultout := 'COMPLETE:' || 'N';
2957 ELSE
2958 resultout := 'COMPLETE:' || 'Y';
2959 END IF;
2960 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2961
2962 EXCEPTION
2963 WHEN OTHERS THEN
2964 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' Exception -');
2965 wf_core.context('LNSWF', 'APPROVE_LOAN_USING_AME', itemtype, itemkey,
2966 to_char(actid), funcmode); RAISE;
2967
2968 END APPROVE_LOAN_USING_AME;
2969
2970
2971 /*========================================================================
2972 | PUBLIC PROCEDURE AME_VERIFY_LOANS_MGR_ROLE
2973 |
2974 | DESCRIPTION
2975 | This procedure is used to check whether the user under process has got
2976 | the required Loan Manager Role or not
2977 |
2978 | PSEUDO CODE/LOGIC
2979 |
2980 | PARAMETERS
2981 | p_loan_type_id IN Standard in parameter
2982 |
2983 | KNOWN ISSUES
2984 | None
2985 |
2986 | NOTES
2987 |
2988 | MODIFICATION HISTORY
2989 | Date Author Description of Changes
2990 |
2991 *=======================================================================*/
2992 /*************************************************************************/
2993 PROCEDURE AME_VERIFY_LOANS_MGR_ROLE(itemtype in varchar2,
2994 itemkey in varchar2,
2995 actid in number,
2996 funcmode in varchar2,
2997 resultout out NOCOPY varchar2 ) IS
2998 l_approver_user_name VARCHAR2(320);
2999 l_manager_flag VARCHAR2(30);
3000 l_user_id FND_USER.user_id%TYPE;
3001 l_api_name CONSTANT VARCHAR2(30) := 'AME_VERIFY_LOANS_MGR_ROLE';
3002
3003
3004 CURSOR verify_lns_mgr_role (p_user_id IN FND_USER.user_id%TYPE ) IS
3005 select
3006 nvl(manager.manager_flag,'N') manager_flag
3007 from
3008 (select
3009 rol.manager_flag,rel.role_resource_id
3010 FROM
3011 jtf_rs_role_relations rel ,
3012 jtf_rs_roles_b rol
3013 where rel.role_id = rol.role_id
3014 and rel.delete_flag <> 'Y'
3015 and sysdate between nvl(rel.start_date_active,sysdate) and nvl(rel.end_date_active,sysdate)
3016 and rol.role_type_code = 'LOANS'
3017 and rol.role_code = 'LOAN_MGR'
3018 and rol.active_flag = 'Y'
3019 ) manager,
3020 jtf_rs_resource_extns res
3021 where
3022 manager.role_resource_id(+) = res.resource_id
3023 and category = 'EMPLOYEE'
3024 and res.start_date_active <= sysdate
3025 and (res.end_date_active is null or res.end_date_active >= sysdate)
3026 and res.user_id = p_user_id;
3027
3028 CURSOR find_userId_from_user_name(p_approver_user_name IN varchar2) IS
3029 select user_id from fnd_user where user_name = p_approver_user_name;
3030
3031 BEGIN
3032
3033 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
3034 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_funcmode : ' || funcmode);
3035 resultout := 'COMPLETE:Y';
3036
3037 /* l_approver_user_name := wf_engine.GetItemAttrNumber
3038 ( itemtype => itemtype,
3039 itemkey => itemkey,
3040 aname => 'LNS_PRIMARY_ROLE');
3041
3042 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_approver_user_name : ' || l_approver_user_name);
3043 if (l_approver_user_name is not null) then
3044 OPEN find_userId_from_user_name(l_approver_user_name);
3045 FETCH find_userId_from_user_name INTO l_user_id;
3046
3047 IF find_userId_from_user_name%NOTFOUND THEN
3048 resultout := 'COMPLETE:N';
3049 END IF;
3050
3051 CLOSE find_userId_from_user_name;
3052
3053 end if;
3054 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_user_id : ' || l_user_id);
3055 if(l_user_id is not null ) then
3056
3057 OPEN verify_lns_mgr_role(l_user_id);
3058 FETCH verify_lns_mgr_role INTO l_manager_flag;
3059
3060 IF verify_lns_mgr_role%NOTFOUND THEN
3061 resultout := 'COMPLETE:N';
3062 END IF;
3063
3064 CLOSE verify_lns_mgr_role;
3065
3066 end if;
3067 LogMessage(FND_LOG.LEVEL_STATEMENT, 'manager_flag : ' || l_manager_flag);
3068 */
3069 EXCEPTION
3070 WHEN OTHERS THEN
3071
3072 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Exception in AME_VERIFY_LOANS_MGR_ROLE');
3073 wf_core.context(
3074 pkg_name => 'LOANS_WORK_FLOW',
3075 proc_name => 'APPROVE_LOAN_USING_AME',
3076 arg1 => itemtype,
3077 arg2 => itemkey,
3078 arg3 => null,
3079 arg4 => null,
3080 arg5 => null);
3081
3082 RAISE;
3083
3084 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
3085 --resultout := 'COMPLETE:Y';
3086 END AME_VERIFY_LOANS_MGR_ROLE;
3087
3088 /*========================================================================
3089 | PUBLIC PROCEDURE AMEEnabled
3090 |
3091 | DESCRIPTION
3092 | This procedure is used to check whether AME is enabled for Loans Approval
3093 | process. This is called from workflow for determining whether approval process
3094 | should follow AME or pre-existing approach of UI enabled approval process.
3095 |
3096 | PSEUDO CODE/LOGIC
3097 |
3098 | PARAMETERS
3099 | p_loan_type_id IN Standard in parameter
3100 |
3101 | KNOWN ISSUES
3102 | None
3103 |
3104 | NOTES
3105 |
3106 | MODIFICATION HISTORY
3107 | Date Author Description of Changes
3108 |
3109 *=======================================================================*/
3110 /*************************************************************************/
3111 /*****************************************************************************/
3112 -- Procedure
3113 -- AMEEnabled
3114 --
3115 -- DESCRIPTION
3116 -- This procedure is used by workflow to determine whether AME is
3117 -- enabled or not
3118 --
3119 -- PARAMETERS
3120 --
3121 -- RETURNS
3122 -- none
3123 /*****************************************************************************/
3124 ---------------------------------------------------------
3125 PROCEDURE AMEEnabled(p_item_type IN VARCHAR2,
3126 p_item_key IN VARCHAR2,
3127 p_actid IN NUMBER,
3128 p_funmode IN VARCHAR2,
3129 p_result OUT NOCOPY VARCHAR2) IS
3130 ---------------------------------------------------------
3131 l_find_approver_count NUMBER;
3132 l_debug_info VARCHAR2(200);
3133 l_AMEEnabled VARCHAR2(1);
3134 l_bAMEProfileDefined BOOLEAN;
3135 l_nRespId Number;
3136 l_api_name CONSTANT VARCHAR2(30) := 'AMEEnabled';
3137 l_api_version CONSTANT NUMBER := 1.0;
3138
3139 BEGIN
3140
3141 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
3142
3143 IF (p_funmode = 'RUN') THEN
3144
3145 BEGIN
3146 ------------------------------------------------------------
3147 l_debug_info := 'Get responsibility id';
3148 ------------------------------------------------------------
3149 l_nRespId := WF_ENGINE.GetItemAttrNumber(p_item_type,
3150 p_item_key,
3151 'RESPONSIBILITY_ID');
3152 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_nRespId : ' || l_nRespId);
3153 EXCEPTION
3154 WHEN OTHERS THEN
3155 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
3156 null;
3157 else
3158 raise;
3159 end if;
3160 END;
3161
3162 ----------------------------------------------------
3163 l_debug_info := 'Retrieve profile option AME Enabled';
3164 ----------------------------------------------------
3165 FND_PROFILE.GET_SPECIFIC('AME_INSTALLED_FLAG', null, l_nRespId, 206, l_AMEEnabled, l_bAMEProfileDefined);
3166
3167 if l_bAMEProfileDefined then
3168 -----------------------------------------------
3169 -- check the profile option value if it's defined
3170 -----------------------------------------------
3171 if (NVL(l_AMEEnabled,'N') = 'Y') then
3172 p_result := 'COMPLETE:Y';
3173 else
3174 p_result := 'COMPLETE:N';
3175 end if;
3176 else
3177 ---------------------------------------------------
3178 -- return no if AME Installed profile option is not defined
3179 ---------------------------------------------------
3180 p_result := 'COMPLETE:N';
3181 end if;
3182
3183 ELSIF (p_funmode = 'CANCEL') THEN
3184
3185 p_result := 'COMPLETE';
3186
3187 END IF;
3188
3189 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
3190
3191 EXCEPTION
3192 WHEN OTHERS THEN
3193 wf_core.context('LNSWF', 'AMEEnabled', p_item_type, p_item_key,
3194 to_char(p_actid), p_funmode);
3195 RAISE;
3196
3197 END AMEEnabled;
3198
3199
3200 /*========================================================================
3201 | PROCEDURE PROCESS_AME_APPROVAL_ACTION
3202 |
3203 | DESCRIPTION
3204 | This procedure inserts the loan Approval Actions in LNS_APPROVAL_ACTIONS table
3205 | whenever an approver takes any action from notification.
3206 |
3207 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3208 |
3209 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3210 | None
3211 |
3212 | PARAMETERS
3213 | itemtype in Item Type
3214 | itemkey in Item Key
3215 | actid in Action Id
3216 | funcmode in Function Mode
3217 | resultout out Result Out
3218 |
3219 | KNOWN ISSUES
3220 | None
3221 |
3222 |
3223 | NOTES
3224 | Any interesting aspect of the code in the package body which needs
3225 | to be stated.
3226 |
3227 | MODIFICATION HISTORY
3228 | Date Author Description of Changes
3229 | 12-Nov-2010 gparuchu Created
3230 *=======================================================================*/
3231 PROCEDURE PROCESS_AME_APPROVAL_ACTION(itemtype in varchar2,
3232 itemkey in varchar2,
3233 actid in number,
3234 funcmode in varchar2,
3235 resultout out NOCOPY varchar2 ) IS
3236
3237
3238 /*-----------------------------------------------------------------------+
3239 | Local Variable Declarations and initializations |
3240 +-----------------------------------------------------------------------*/
3241
3242 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_AME_APPROVAL_ACTION';
3243 l_api_version CONSTANT NUMBER := 1.0;
3244 l_object_version_number NUMBER := 1;
3245 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
3246 l_loan_approve_note VARCHAR2(2000);
3247 l_loan_approval_action_rec LNS_APPROVAL_ACTION_PUB.APPROVAL_ACTION_REC_TYPE;
3248 l_RETURN_STATUS LNS_LOAN_PRODUCTS_ALL.LOAN_APPR_REQ_FLAG%TYPE;
3249 G_JTF_NOTE_CONTEXTS_TAB JTF_NOTES_PUB.JTF_NOTE_CONTEXTS_TBL_TYPE;
3250 l_action_id NUMBER;
3251 l_note_id NUMBER;
3252 l_MSG_DATA VARCHAR2(32767);
3253 l_MSG_COUNT NUMBER;
3254 l_msg_index_out NUMBER;
3255 l_org_id NUMBER;
3256
3257
3258 /*-----------------------------------------------------------------------+
3259 | Cursor Declarations |
3260 +-----------------------------------------------------------------------*/
3261
3262 BEGIN
3263 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
3264 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_funcmode : ' || funcmode);
3265 IF (funcmode <> wf_engine.eng_run) THEN
3266 resultout := 'COMPLETE:' || 'N';
3267 return;
3268 END IF;
3269 l_loan_id := wf_engine.GetItemAttrNumber
3270 ( itemtype => itemtype,
3271 itemkey => itemkey,
3272 aname => 'LNS_LOAN_ID');
3273
3274 --gparuchu - Code added to make sure correct object_version_number is passed
3275 --inorder to make sure the header table gets updated correctly upon Approval process.
3276 IF (l_loan_id IS NOT NULL) THEN
3277 select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
3278 from lns_loan_headers_all loan
3279 where loan.loan_id = l_loan_id;
3280 END IF;
3281
3282 LogMessage(FND_LOG.LEVEL_STATEMENT,'In PROCESS_AME_APPROVAL_ACTION l_loan_id : ' || l_loan_id);
3283
3284 l_loan_approve_note := wf_engine.GetItemAttrText
3285 ( itemtype => itemtype,
3286 itemkey => itemkey,
3287 aname => 'WF_NOTE');
3288
3289
3290 wf_engine.SetItemAttrText (itemtype => itemtype,
3291 itemkey => itemkey,
3292 aname => 'LNS_LAST_ENTERED_NOTE',
3293 avalue => l_loan_approve_note);
3294
3295 LogMessage(FND_LOG.LEVEL_STATEMENT,'In PROCESS_AME_APPROVAL_ACTION l_loan_approve_note : ' || l_loan_approve_note);
3296
3297 select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_loan_approval_action_rec.action_id from dual;
3298
3299 l_loan_approval_action_rec.created_by := LNS_UTILITY_PUB.CREATED_BY;
3300 l_loan_approval_action_rec.creation_date := LNS_UTILITY_PUB.CREATION_DATE;
3301 l_loan_approval_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
3302 l_loan_approval_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
3303 l_loan_approval_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
3304 l_loan_approval_action_rec.object_version_number := l_object_version_number;
3305 l_loan_approval_action_rec.loan_id := l_loan_id;
3306 l_loan_approval_action_rec.action_type := 'APPROVE_SINGLE'; --for intermediate approver
3307 l_loan_approval_action_rec.amount := null;
3308 l_loan_approval_action_rec.reason_code := null;
3309 l_loan_approval_action_rec.attribute_category := null;
3310 l_loan_approval_action_rec.attribute1 := null;
3311 l_loan_approval_action_rec.attribute2 := null;
3312 l_loan_approval_action_rec.attribute3 := null;
3313 l_loan_approval_action_rec.attribute4 := null;
3314 l_loan_approval_action_rec.attribute5 := null;
3315 l_loan_approval_action_rec.attribute6 := null;
3316 l_loan_approval_action_rec.attribute7 := null;
3317 l_loan_approval_action_rec.attribute8 := null;
3318 l_loan_approval_action_rec.attribute9 := null;
3319 l_loan_approval_action_rec.attribute10 := null;
3320 l_loan_approval_action_rec.attribute11 := null;
3321 l_loan_approval_action_rec.attribute12 := null;
3322 l_loan_approval_action_rec.attribute13 := null;
3323 l_loan_approval_action_rec.attribute14 := null;
3324 l_loan_approval_action_rec.attribute15 := null;
3325 l_loan_approval_action_rec.attribute16 := null;
3326 l_loan_approval_action_rec.attribute17 := null;
3327 l_loan_approval_action_rec.attribute18 := null;
3328 l_loan_approval_action_rec.attribute19 := null;
3329 l_loan_approval_action_rec.attribute20 := null;
3330
3331 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Start calling LNS_APPROVAL_ACTION_PUB.create_approval_action' );
3332
3333 LNS_APPROVAL_ACTION_PUB.create_approval_action (p_init_msg_list => FND_API.G_TRUE,
3334 p_approval_action_rec => l_loan_approval_action_rec,
3335 x_action_id => l_action_id,
3336 X_RETURN_STATUS => l_RETURN_STATUS,
3337 X_MSG_COUNT => l_MSG_COUNT,
3338 X_MSG_DATA => l_MSG_DATA );
3339
3340 LogMessage(FND_LOG.LEVEL_STATEMENT,'G_LOG_ENABLED: ' || G_LOG_ENABLED);
3341 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_action_id : ' || l_action_id);
3342 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_RETURN_STATUS : ' || l_RETURN_STATUS);
3343 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_COUNT : ' || l_MSG_COUNT);
3344 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_DATA : ' || l_MSG_DATA);
3345
3346 LogMessage(FND_LOG.LEVEL_STATEMENT,'END calling LNS_APPROVAL_ACTION_PUB.create_approval_action' );
3347 LogMessage(FND_LOG.LEVEL_STATEMENT,'In PROCESS_AME_APPROVAL_ACTION l_loan_approve_note : ' || l_loan_approve_note);
3348
3349
3350 IF (l_loan_approve_note is not null AND length(trim(l_loan_approve_note)) > 0) THEN
3351
3352 --added by GPARUCHU to set org correctly for JTF_NOTES_PUB.validate_object method
3353 -- to correctly pick the loan against given loan_id
3354 -- MO_GLOBAL.GET_CURRENT_ORG_ID() doesnt work here as it returns null when an external user
3355 -- is trying to approve a loan
3356
3357 --mo_global.set_policy_context('M', null);
3358 MO_GLOBAL.INIT('LNS');
3359 MO_GLOBAL.set_policy_context('S',l_org_id);
3360
3361 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type := 'LNS_APPROVAL_ACTION';
3362 G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type_id := l_loan_approval_action_rec.action_id;
3363 --G_JTF_NOTE_CONTEXTS_TAB(1).note_context_type_id := l_loan_approval_action_rec.action_id;
3364 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
3365 G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
3366 G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
3367 G_JTF_NOTE_CONTEXTS_TAB(1).creation_date := LNS_UTILITY_PUB.creation_date;
3368 G_JTF_NOTE_CONTEXTS_TAB(1).created_by := LNS_UTILITY_PUB.created_by;
3369
3370 LogMessage(FND_LOG.LEVEL_STATEMENT,'Start calling JTF_NOTES_PUB.CREATE_NOTE' );
3371 JTF_NOTES_PUB.CREATE_NOTE(
3372 P_API_VERSION => 1.0,
3373 P_INIT_MSG_LIST => FND_API.G_TRUE,
3374 P_COMMIT => FND_API.G_TRUE,
3375 P_JTF_NOTE_ID => FND_API.G_MISS_NUM,
3376 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3377 P_SOURCE_OBJECT_ID => l_loan_id,
3378 P_SOURCE_OBJECT_CODE => 'LNS_LOAN',
3379 P_NOTES => l_loan_approve_note,
3380 P_NOTES_DETAIL => null,
3381 P_ENTERED_BY => LNS_UTILITY_PUB.created_by,
3382 P_ENTERED_DATE => LNS_UTILITY_PUB.creation_date,
3383 P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
3384 P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
3385 P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
3386 P_CREATED_BY => LNS_UTILITY_PUB.created_by,
3387 P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
3388 X_JTF_NOTE_ID => l_note_id ,
3389 P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
3390 P_NOTE_STATUS => 'I', -- Visibility : public
3391 X_RETURN_STATUS => l_return_status,
3392 X_MSG_COUNT => l_msg_count,
3393 X_MSG_DATA => l_msg_data,
3394 P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
3395
3396 );
3397
3398 -- Check for errors
3399
3400 IF (fnd_msg_pub.count_msg > 0) THEN
3401 FOR i IN 1..fnd_msg_pub.count_msg
3402 LOOP
3403 fnd_msg_pub.get( p_msg_index => i,
3404 p_encoded => 'F',
3405 p_data => l_msg_data,
3406 p_msg_index_out => l_msg_index_out
3407 );
3408 END LOOP;
3409 ELSE
3410 LogMessage(FND_LOG.LEVEL_STATEMENT,'Created note : ' || to_char(l_note_id));
3411 END IF;
3412
3413
3414 LogMessage(FND_LOG.LEVEL_STATEMENT,'G_LOG_ENABLED: ' || G_LOG_ENABLED);
3415 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_note_id : ' || l_note_id);
3416 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_RETURN_STATUS : ' || l_RETURN_STATUS);
3417 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_COUNT : ' || l_MSG_COUNT);
3418 LogMessage(FND_LOG.LEVEL_STATEMENT,'l_MSG_DATA : ' || l_MSG_DATA);
3419 LogMessage(FND_LOG.LEVEL_STATEMENT,'End calling JTF_NOTES_PUB.CREATE_NOTE' );
3420
3421
3422 END IF;
3423
3424 resultout := 'COMPLETE:' || 'Y';
3425
3426 EXCEPTION
3427 WHEN OTHERS THEN
3428 resultout := 'COMPLETE:' || 'N';
3429 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
3430 || ' Exception -');
3431 wf_core.context('LNSWF', 'PROCESS_AME_APPROVAL_ACTION', itemtype, itemkey,
3432 to_char(actid), funcmode);
3433 RAISE;
3434
3435 END PROCESS_AME_APPROVAL_ACTION;
3436
3437
3438 BEGIN
3439 G_LOG_ENABLED := 'N';
3440 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
3441
3442 /* getting msg logging info */
3443 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
3444 IF (G_LOG_ENABLED = 'N') then
3445 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
3446 ELSE
3447 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
3448 END IF;
3449 END LNS_WORK_FLOW;