[Home] [Help]
PACKAGE BODY: APPS.LNS_WORK_FLOW
Source
1 PACKAGE BODY LNS_WORK_FLOW as
2 /* $Header: LNS_WORK_FLOW_B.pls 120.11.12010000.2 2008/10/23 21:41:50 mbolli 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_FUNDING_PUB';
32 G_LOG_ENABLED varchar2(5);
33 G_MSG_LEVEL NUMBER;
34 g_org_id number;
35
36
37
38 /*========================================================================
39 | PRIVATE PROCEDURE LogMessage
40 |
41 | DESCRIPTION
42 | This procedure logs debug messages to db and to CM log
43 |
44 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
45 |
46 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
47 | None
48 |
49 | PARAMETERS
50 | p_msg_level IN Debug msg level
51 | p_msg IN Debug msg itself
52 |
53 | KNOWN ISSUES
54 | None
55 |
56 |
57 | NOTES
58 | Any interesting aspect of the code in the package body which needs
59 | to be stated.
60 |
61 | MODIFICATION HISTORY
62 | Date Author Description of Changes
63 | 17-Jan-2005 GBELLARY Created
64 |
65 *=======================================================================*/
66 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
67 IS
68 BEGIN
69 IF (p_msg_level >= G_MSG_LEVEL) then
70
71 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
72
73 END IF;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
78 END;
79
80
81 /*========================================================================
82 | PRIVATE PROCEDURE PROCESS_LOAN_STATUS_CHANGE
83 |
84 | DESCRIPTION
85 | This procedure logs debug messages to db and to CM log
86 |
87 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
88 |
89 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
90 | None
91 |
92 | PARAMETERS
93 | p_loan_id IN Loan Id
94 | p_from_status IN Old loan status
95 | p_to_status IN New Loan Status
96 |
97 | KNOWN ISSUES
98 | None
99 |
100 |
101 | NOTES
102 | Any interesting aspect of the code in the package body which needs
103 | to be stated.
104 |
105 | MODIFICATION HISTORY
106 | Date Author Description of Changes
107 | 17-Jan-2005 GBELLARY Created
108 |
109 *=======================================================================*/
110
111 PROCEDURE PROCESS_LOAN_STATUS_CHANGE( p_loan_id IN NUMBER
112 ,p_from_status IN VARCHAR2
113 ,p_to_status IN VARCHAR2) IS
114
115 /*-----------------------------------------------------------------------+
116 | Local Variable Declarations and initializations |
117 +-----------------------------------------------------------------------*/
118
119 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOAN_STATUS_CHANGE';
120 l_api_version CONSTANT NUMBER := 1.0;
121 l_event_name VARCHAR2(250);
122
123 /*-----------------------------------------------------------------------+
124 | Cursor Declarations |
125 +-----------------------------------------------------------------------*/
126 BEGIN
127 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
128 IF p_to_status = 'PENDING' then
129 l_event_name := 'LOAN_APPROVAL_PENDING';
130 ELSIF p_to_status = 'APPROVED' then
131 l_event_name := 'LOAN_APPROVAL_APPROVED';
132 ELSIF p_to_status = 'REJECTED' then
133 l_event_name := 'LOAN_APPROVAL_REJECTED';
134 ELSIF p_to_status = 'INCOMPLETE' then
135 l_event_name := 'LOAN_APPROVAL_NEEDINFO';
136 ELSIF p_to_status = 'DEFAULT' then
137 l_event_name := 'LOAN_DEFAULT';
138 ELSIF p_to_status = 'DELINQUENT' then
139 l_event_name := 'LOAN_DELINQUENT';
140 ELSIF p_to_status = 'PAIDOFF' then
141 l_event_name := 'LOAN_PAIDOFF';
142 ELSIF p_to_status = 'IN_FUNDING' then
143 l_event_name := 'LOAN_FUNDING_PENDING';
144 ELSIF p_from_status = 'IN_FUNDING'
145 AND p_to_status = 'ACTIVE' then
146 l_event_name := 'LOAN_FUNDING_SUCCESSFUL';
147 ELSIF p_from_status = 'IN_FUNDING'
148 AND p_to_status = 'FUNDING_ERROR' then
149 l_event_name := 'LOAN_FUNDING_ERROR';
150 ELSIF p_from_status in ('DEFAULT','DELINQUENT','PAIDOFF')
151 AND p_to_status = 'ACTIVE' then
152 l_event_name := 'LOAN_ACTIVE_AGAIN';
153 ELSIF p_to_status = 'PENDING_CANCELLATION' then
154 l_event_name := 'LOAN_DISB_CANCEL_PENDING';
155 END IF;
156 IF l_event_name is NOT NULL THEN
157 raise_event(p_event_name => l_event_name
158 ,p_loan_id => p_loan_id
159 ,p_from_status=> p_from_status);
160 END IF;
161 /* Commenting out this code since this is going to be moved to approval.
162 IF p_to_status = 'ACTIVE'
163 AND p_from_status NOT IN ('DEFAULT','DELINQUENT','PAIDOFF')
164 THEN
165 LNS_REP_UTILS.store_loan_agreement(p_loan_id => p_loan_id);
166 END IF;
167 */
168 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
169 EXCEPTION
170 WHEN OTHERS THEN
171 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
172 || ' -');
173 RAISE;
174 END PROCESS_LOAN_STATUS_CHANGE;
175 PROCEDURE PROCESS_STATUS_CHANGE( p_loan_id IN NUMBER
176 ,p_column_name IN VARCHAR2
177 ,p_from_status IN VARCHAR2
178 ,p_to_status IN VARCHAR2) IS
179
180 /*-----------------------------------------------------------------------+
181 | Local Variable Declarations and initializations |
182 +-----------------------------------------------------------------------*/
183
184 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_STATUS_CHANGE';
185 l_api_version CONSTANT NUMBER := 1.0;
186 l_event_name VARCHAR2(250);
187
188 /*-----------------------------------------------------------------------+
189 | Cursor Declarations |
190 +-----------------------------------------------------------------------*/
191 BEGIN
192 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
193 IF p_column_name = 'LOAN_STATUS' then
194 process_loan_status_change(p_loan_id => p_loan_id
195 ,p_from_status => p_from_status
196 ,p_to_status => p_to_status);
197 ELSIF p_column_name = 'SECONDARY_STATUS' then
198
199 process_sec_status_change(p_loan_id => p_loan_id
200 ,p_from_status => p_from_status
201 ,p_to_status => p_to_status);
202 ELSE null;
203 END IF;
204 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
205 EXCEPTION
206 WHEN OTHERS THEN
207 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
208 || ' -');
209 RAISE;
210 END PROCESS_STATUS_CHANGE;
211 PROCEDURE PROCESS_SEC_STATUS_CHANGE( p_loan_id IN NUMBER
212 ,p_from_status IN VARCHAR2
213 ,p_to_status IN VARCHAR2) IS
214
215 /*-----------------------------------------------------------------------+
216 | Local Variable Declarations and initializations |
217 +-----------------------------------------------------------------------*/
218
219 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SEC_STATUS_CHANGE';
220 l_api_version CONSTANT NUMBER := 1.0;
221 l_event_name VARCHAR2(250);
222 l_open_to_term_flag lns_loan_headers_all.open_to_term_flag%TYPE;
223 l_open_to_term_event lns_loan_headers_all.open_to_term_event%TYPE;
224
225 /*-----------------------------------------------------------------------+
226 | Cursor Declarations |
227 +-----------------------------------------------------------------------*/
228 CURSOR csr_loan_details IS
229 SELECT open_to_term_flag
230 ,open_to_term_event
231 FROM lns_loan_headers_all
232 WHERE loan_id = p_loan_id;
233
234 BEGIN
235 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
236 OPEN csr_loan_details;
237 FETCH csr_loan_details
238 INTO l_open_to_term_flag
239 ,l_open_to_term_event;
240 CLOSE csr_loan_details;
241 IF p_to_status = 'PENDING_CANCELLATION' then
242 l_event_name := 'LOAN_DISB_CANCEL_PENDING';
243 ELSIF p_to_status IN ('ALL_DISB_CANCELLED', 'REMAINING_DISB_CANCELLED') then
244 l_event_name := 'LOAN_DISB_CANCEL_APPROVED';
245 ELSIF p_to_status = 'MORE_INFO_REQUESTED'
246 AND p_from_status = 'PENDING_CANCELLATION' then
247 l_event_name := 'LOAN_DISB_CANCEL_INCOMPLETE';
248 ELSIF p_to_status NOT IN ('ALL_DISB_CANCELLED', 'REMAINING_DISB_CANCELLED')
249 AND p_from_status = 'PENDING_CANCELLATION' then
250 l_event_name := 'LOAN_DISB_CANCEL_REJECTED';
251 ELSIF p_to_status = 'FULLY_FUNDED'
252 AND l_open_to_term_flag = 'Y'
253 AND l_open_to_term_event <> 'AUTO_FINAL_DISBURSEMENT' then
254 l_event_name := 'LOAN_TERM_CONVERT_REQUIRED';
255 ELSIF p_to_status = 'PENDING_CONVERSION' then
256 l_event_name := 'LOAN_TERM_CONVERT_PENDING';
257 ELSIF p_to_status = 'CONVERTED_TO_TERM_PHASE'
258 AND l_open_to_term_flag = 'Y'
259 AND l_open_to_term_event = 'AUTO_FINAL_DISBURSEMENT' then
260 l_event_name := 'LOAN_TERM_AUTOCONVERT';
261 ELSIF p_to_status = 'CONVERTED_TO_TERM_PHASE' then
262 l_event_name := 'LOAN_TERM_CONVERT_APPROVED';
263 ELSIF p_to_status = 'MORE_INFO_REQUESTED'
264 AND p_from_status = 'PENDING_CONVERSION' then
265 l_event_name := 'LOAN_TERM_CONVERT_INCOMPLETE';
266 ELSIF p_to_status <> 'CONVERTED_TO_TERM_PHASE'
267 AND p_from_status = 'PENDING_CONVERSION' then
268 l_event_name := 'LOAN_TERM_CONVERT_REJECTED';
269 ELSIF p_to_status = 'IN_FUNDING' then
270 l_event_name := 'LOAN_FUNDING_PENDING';
271 ELSIF p_to_status IN ('PARTIALLY_FUNDED','FULLY_FUNDED') then
272 l_event_name := 'LOAN_FUNDING_SUCCESSFUL';
273 ELSIF p_to_status = 'FUNDING_ERROR' then
274 l_event_name := 'LOAN_FUNDING_ERROR';
275 END IF;
276 IF l_event_name is NOT NULL THEN
277 raise_event(p_event_name => l_event_name
278 ,p_loan_id => p_loan_id
279 ,p_from_status=> p_from_status);
280 END IF;
281 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
282 EXCEPTION
283 WHEN OTHERS THEN
284 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
285 || ' -');
286 RAISE;
287 END PROCESS_SEC_STATUS_CHANGE;
288 /*========================================================================
289 | PRIVATE PROCEDURE RAISE_EVENT
290 |
291 | DESCRIPTION
292 | This procedure logs debug messages to db and to CM log
293 |
294 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
295 |
296 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
297 | None
298 |
299 | PARAMETERS
300 | p_loan_id IN Loan Id
301 | p_loan_status IN Loan Status
302 | x_error_code OUT Error Code
303 |
304 | KNOWN ISSUES
305 | None
306 |
307 |
308 | NOTES
309 | Any interesting aspect of the code in the package body which needs
310 | to be stated.
311 |
312 | MODIFICATION HISTORY
313 | Date Author Description of Changes
314 | 17-Jan-2005 GBELLARY Created
315 |
316 *=======================================================================*/
317
318 PROCEDURE RAISE_EVENT ( p_loan_id IN NUMBER
319 ,p_event_name IN VARCHAR2
320 ,p_from_status IN VARCHAR2 DEFAULT NULL) IS
321 /*-----------------------------------------------------------------------+
322 | Local Variable Declarations and initializations |
323 +-----------------------------------------------------------------------*/
324
325 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_EVENT';
326 l_api_version CONSTANT NUMBER := 1.0;
327 l_loan_number lns_loan_headers_all.loan_number%TYPE;
328 l_requested_amount lns_loan_headers_all.requested_amount%TYPE;
329 l_loan_description lns_loan_headers_all.loan_description%TYPE;
330 l_loan_class_code lns_loan_headers_all.loan_class_code%TYPE;
331 l_loan_type lns_loan_types.loan_type_name%TYPE;
332 l_loan_type_id lns_loan_types.loan_type_id%TYPE;
333 l_current_user_id lns_loan_headers_all.created_by%TYPE;
334 l_loan_formatted_amount VARCHAR2(50);
335 l_loan_undisbursed_amount VARCHAR2(50);
336 l_function_name VARCHAR2(50);
337 ItemType VARCHAR2(30) ;
338 ItemKey NUMBER;
339 l_list WF_PARAMETER_LIST_T;
340 l_param WF_PARAMETER_T;
341 l_wf_event_name VARCHAR2(240);
342
343 /*-----------------------------------------------------------------------+
344 | Cursor Declarations |
345 +-----------------------------------------------------------------------*/
346 CURSOR csr_loan_event_details IS
347 SELECT wf_business_event
348 ,lh.loan_number
349 ,lh.requested_amount
350 ,lh.loan_description
351 ,lh.loan_class_code
352 ,lh.loan_type_id
353 ,lt.loan_type_name
354 ,to_char(lh.requested_amount,
355 FND_CURRENCY.SAFE_GET_FORMAT_MASK(lh.LOAN_CURRENCY,50))
356 || ' ' || lh.loan_currency loan_formatted_amount
357 ,to_char(nvl(lh.requested_amount,0)-nvl(lh.funded_amount,0),
358 FND_CURRENCY.SAFE_GET_FORMAT_MASK(lh.LOAN_CURRENCY,50))
359 || ' ' || lh.loan_currency loan_undisbursed_amount
360 ,decode(lh.loan_class_code,'ERS','LNS_ERS_CONTEXT_HOMEPAGE_MENU',
361 'LNS_LOAN_CONTEXT_HOMEPAGE_MENU') function_name
362 ,lh.last_updated_by current_user_id
363 FROM lns_events le, lns_loan_headers_all_vl lh, lns_loan_types_vl lt
364 WHERE lh.loan_class_code = le.loan_class_code
365 AND le.enabled_flag = 'Y'
366 AND le.event_name = p_event_name
367 AND lt.loan_type_id = lh.loan_type_id
368 AND lh.loan_id = p_loan_id;
369 BEGIN
370 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
371
372 ItemType := 'LNSWF';
373
374 SELECT lns_workflow_itemkey_s.nextval
375 INTO ItemKey
376 FROM dual;
377
378 OPEN csr_loan_event_details;
379 FETCH csr_loan_event_details
380 INTO l_wf_event_name
381 ,l_loan_number
382 ,l_requested_amount
383 ,l_loan_description
384 ,l_loan_class_code
385 ,l_loan_type_id
386 ,l_loan_type
387 ,l_loan_formatted_amount
388 ,l_loan_undisbursed_amount
389 ,l_function_name
390 ,l_current_user_id;
391 IF csr_loan_event_details%NOTFOUND THEN
392 return;
393 END IF;
394 CLOSE csr_loan_event_details;
395 -- initialization of object variables
396 l_list := WF_PARAMETER_LIST_T();
397
398 wf_event.AddParameterToList(p_name => 'LNS_LOAN_ID',
399 p_value => p_loan_id,
400 p_parameterlist => l_list);
401 wf_event.AddParameterToList(p_name => 'LNS_LOAN_NUMBER',
402 p_value => l_loan_number,
403 p_parameterlist => l_list);
404 wf_event.AddParameterToList(p_name => 'LNS_LOAN_DESCRIPTION',
405 p_value => l_loan_description,
406 p_parameterlist => l_list);
407 wf_event.AddParameterToList(p_name => 'LNS_REQUESTED_AMOUNT',
408 p_value => l_requested_amount,
409 p_parameterlist => l_list);
410 wf_event.AddParameterToList(p_name => 'LNS_FORMATTED_AMOUNT',
411 p_value => l_loan_formatted_amount,
412 p_parameterlist => l_list);
413 wf_event.AddParameterToList(p_name => 'LNS_LOAN_UNDISBURSED_AMOUNT',
414 p_value => l_loan_undisbursed_amount,
415 p_parameterlist => l_list);
416 wf_event.AddParameterToList(p_name => 'LNS_WF_INIT_DATE',
417 p_value => SYSDATE,
418 p_parameterlist => l_list);
419 wf_event.AddParameterToList(p_name => 'LNS_OLD_LOAN_STATUS',
420 p_value => p_from_status,
421 p_parameterlist => l_list);
422 wf_event.AddParameterToList(p_name => 'LNS_LOAN_CLASS_CODE',
423 p_value => l_loan_class_code,
424 p_parameterlist => l_list);
425 wf_event.AddParameterToList(p_name => 'LNS_LOAN_TYPE_ID',
426 p_value => l_loan_type_id,
427 p_parameterlist => l_list);
428 wf_event.AddParameterToList(p_name => 'LNS_CURRENT_USER_ID',
429 p_value => l_current_user_id,
430 p_parameterlist => l_list);
431 wf_event.AddParameterToList(p_name => 'LNS_LOAN_TYPE',
432 p_value => l_loan_type,
433 p_parameterlist => l_list);
434 wf_event.AddParameterToList(p_name => 'LNS_EVENT_NAME',
435 p_value => p_event_name,
436 p_parameterlist => l_list);
437 wf_event.AddParameterToList(p_name => 'LNS_ERS_FUNCTION_NAME',
438 p_value => l_function_name,
439 p_parameterlist => l_list);
440 wf_event.raise (p_event_name => l_wf_event_name,
441 p_event_key => itemkey,
442 p_parameters => l_list);
443 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
444 EXCEPTION
445 WHEN OTHERS
446 THEN
447 WF_CORE.CONTEXT('LNSWF',l_wf_event_name, itemkey);
448 RAISE;
449 END RAISE_EVENT;
450 /*========================================================================
451 | PRIVATE PROCEDURE LOG_EVENT_HISTORY
452 |
453 | DESCRIPTION
454 | This procedure logs debug messages to db and to CM log
455 |
456 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
457 |
458 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
459 | None
460 |
461 | PARAMETERS
462 | itemtype in Item Type
463 | itemkey in Item Key
464 | actid in Action Id
465 | funcmode in Function Mode
466 | resultout out Result Out
467 |
468 | KNOWN ISSUES
469 | None
470 |
471 |
472 | NOTES
473 | Any interesting aspect of the code in the package body which needs
474 | to be stated.
475 |
476 | MODIFICATION HISTORY
477 | Date Author Description of Changes
478 | 17-Jan-2005 GBELLARY Created
479 |
480 *=======================================================================*/
481 PROCEDURE LOG_EVENT_HISTORY(itemtype in varchar2,
482 itemkey in varchar2,
483 actid in number,
484 funcmode in varchar2,
485 resultout out NOCOPY varchar2 ) IS
486 /*-----------------------------------------------------------------------+
487 | Local Variable Declarations and initializations |
488 +-----------------------------------------------------------------------*/
489
490 l_api_name CONSTANT VARCHAR2(30) := 'LOG_EVENT_HISTORY';
491 l_api_version CONSTANT NUMBER := 1.0;
492 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
493 l_event_action_id LNS_EVT_ACTION_HISTORY_H.EVENT_ACTION_ID%TYPE;
494 l_ev_action_hist_id LNS_EVT_ACTION_HISTORY_H.EVENT_ACTION_HISTORY_ID%TYPE;
495
496 /*-----------------------------------------------------------------------+
497 | Cursor Declarations |
498 +-----------------------------------------------------------------------*/
499 BEGIN
500 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +'); IF (funcmode <> wf_engine.eng_run) THEN
501
502 resultout := wf_engine.eng_null;
503 return;
504 END IF;
505 l_loan_id := wf_engine.GetItemAttrNumber
506 ( itemtype => itemtype,
507 itemkey => itemkey,
508 aname => 'LNS_LOAN_ID');
509 l_event_action_id := wf_engine.GetItemAttrNumber
510 ( itemtype => itemtype,
511 itemkey => itemkey,
512 aname => 'LNS_EVENT_ACTION_ID');
513 LNS_EVT_ACTION_HISTORY_H_PKG.Insert_Row (
514 X_EVENT_ACTION_HISTORY_ID => l_ev_action_hist_id
515 ,P_EVENT_ACTION_ID => l_event_action_id
516 ,P_LOAN_ID => l_loan_id
517 ,P_WF_ITEMKEY => itemkey
518 ,P_WF_ITEMTYPE => itemtype
519 ,P_ACTIVITY_DATE => sysdate);
520 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
521 EXCEPTION
522 WHEN OTHERS THEN
523 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
524 || ' -');
525 wf_core.context('LNSWF', 'LOG_EVENT_HISTORY', itemtype, itemkey,
526 to_char(actid), funcmode); RAISE;
527 END LOG_EVENT_HISTORY;
528
529 /*========================================================================
530 | PRIVATE FUNCTION has_user_org_access
531 |
532 | DESCRIPTION
533 | This procedure checks if a user has access to an org (OU)
534 |
535 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
536 |
537 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
538 | None
539 |
540 | PARAMETERS
541 | l_user_id in Unique Identifier for the User
542 | l_org_id in the OU to check access for
543 |
544 | KNOWN ISSUES
545 | None
546 |
547 |
548 | NOTES
549 | Any interesting aspect of the code in the package body which needs
550 | to be stated.
551 |
552 | MODIFICATION HISTORY
553 | Date Author Description of Changes
554 | 01-Mar-2006 KARAMACH Created
555 --Modified the query with case when construct for loan_amount and loan_formatted_amount to fix bug5126957 --karamach
556 |
557 *=======================================================================*/
558 FUNCTION has_user_org_access (l_user_id in Number,
559 l_org_id in Number)
560 RETURN BOOLEAN IS
561
562 l_has_org_access BOOLEAN;
563 l_resp_id NUMBER;
564 l_appl_id NUMBER;
565 --Get all valid lns responsibilities for the user_id
566 CURSOR C_GET_USER_RESPS(p_user_id NUMBER,p_appl_id NUMBER) IS
567 SELECT usr_resp.responsibility_id
568 FROM fnd_user_resp_groups usr_resp
569 WHERE usr_resp.responsibility_application_id = p_appl_id
570 AND usr_resp.start_date < sysdate
571 AND usr_resp.end_date > sysdate
572 AND usr_resp.user_id = p_user_id;
573
574 BEGIN
575 --if no access to org_id with any lns responsibility, return false
576 l_has_org_access := FALSE;
577 l_appl_id := 206;
578
579 if (l_user_id is null OR l_org_id is null) then
580 RETURN l_has_org_access;
581 end if;
582
583 OPEN c_get_user_resps(l_user_id,l_appl_id);
584 <<USER_RESPS_LOOP>> LOOP
585 FETCH c_get_user_resps
586 INTO l_resp_id;
587 EXIT USER_RESPS_LOOP WHEN c_get_user_resps%NOTFOUND;
588 --initialize the session context with the user and resp info
589 fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
590 --begin R12 specific
591 MO_GLOBAL.INIT('LNS');
592 if (mo_global.check_access(l_org_id) = 'Y') then
593 --end R12 specific
594 --begin 11i specific
595 --if (fnd_profile.value('ORG_ID') = to_char(l_org_id)) then
596 --end 11i specific
597 l_has_org_access := TRUE;
598 EXIT USER_RESPS_LOOP;
599 end if;
600 END LOOP USER_RESPS_LOOP;
601 CLOSE c_get_user_resps;
602
603 RETURN l_has_org_access;
604
605 END has_user_org_access;
606
607 /*========================================================================
608 | PRIVATE PROCEDURE CREATE_NOTIFICATION_DETAILS
609 |
610 | DESCRIPTION
611 | This procedure gets approvers for a Loan
612 |
613 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
614 |
615 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
616 | None
617 |
618 | PARAMETERS
619 | itemtype in Item Type
620 | itemkey in Item Key
621 | actid in Action Id
622 | funcmode in Function Mode
623 | resultout out Result Out
624 |
625 | KNOWN ISSUES
626 | None
627 |
628 |
629 | NOTES
630 | Any interesting aspect of the code in the package body which needs
631 | to be stated.
632 |
633 | MODIFICATION HISTORY
634 | Date Author Description of Changes
635 | 17-Jan-2005 GBELLARY Created
636 | 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
637 |
638 *=======================================================================*/
639 FUNCTION CREATE_NOTIFICATION_DETAILS ( itemtype in varchar2,
640 itemkey in varchar2,
641 p_event_name in varchar2,
642 p_loan_id in NUMBER,
643 p_loan_class_code in varchar2,
644 p_loan_type in varchar2,
645 p_loan_type_id in number,
646 p_current_user_id in number)
647 RETURN VARCHAR2 IS
648
649 /*-----------------------------------------------------------------------+
650 | Local Variable Declarations and initializations |
651 +-----------------------------------------------------------------------*/
652
653 l_api_name CONSTANT VARCHAR2(30)
654 := 'CREATE_NOTIFICATION_DETAILS';
655 l_api_version CONSTANT NUMBER := 1.0;
656 l_user_roles VARCHAR2(32000);
657 l_user_name FND_USER.user_name%TYPE;
658 l_role_name VARCHAR2(100);
659 l_loan_number LNS_LOAN_HEADERS.LOAN_NUMBER%TYPE;
660 l_primary_recipient_type lns_event_actions.primary_recipient_type%TYPE;
661 l_primary_recipient_name lns_event_actions.primary_recipient_name%TYPE;
662 l_priority_num lns_event_actions.priority_num%TYPE;
663 l_active_for_num lns_event_actions.active_for_num%TYPE;
664 l_delivery_method lns_event_actions.delivery_method%TYPE;
665 l_loan_assigned_name jtf_rs_resource_extns.source_name%TYPE;
666 l_loan_assigned_user fnd_user.user_name%TYPE;
667 l_current_user fnd_user.user_name%TYPE;
668 l_borrower_name hz_parties.party_name%TYPE;
669 l_loan_class lns_lookups.meaning%TYPE;
670 l_loan_type lns_loan_types.loan_type_name%TYPE;
671 l_loan_purpose lns_lookups.meaning%TYPE;
672 l_loan_subtype lns_loan_headers_all.loan_subtype%TYPE;
673 l_collateral_percent VARCHAR2(10);
674 l_loan_amount lns_pay_sum_v.total_principal_balance%TYPE;
675 l_loan_formatted_amount VARCHAR2(30);
676 l_loan_start_date lns_loan_headers_all.loan_start_date%TYPE;
677 l_term VARCHAR2(15);
678 l_loan_maturity_date lns_loan_headers_all.loan_maturity_date%TYPE;
679 l_interest_rate VARCHAR2(30);
680 l_overdue_amount VARCHAR2(30);
681 l_overdue_num lns_pay_sum_overdue_v.number_overdue_bills%TYPE;
682 l_event_action_id LNS_EVENT_ACTIONS.EVENT_ACTION_ID%TYPE;
683 l_org_id NUMBER;
684 l_user_id NUMBER;
685
686
687 /*-----------------------------------------------------------------------+
688 | Cursor Declarations |
689 +-----------------------------------------------------------------------*/
690 CURSOR csr_notification_details IS
691 SELECT primary_recipient_type
692 , primary_recipient_name
693 , priority_num
694 , nvl(active_for_num,0)*24*60 -- This has to be converted into minutes
695 , delivery_method
696 , event_action_id
697 FROM lns_events le, lns_event_actions lea
698 WHERE le.event_name = p_event_name
699 AND le.enabled_flag = 'Y'
700 AND le.loan_class_code = p_loan_class_code
701 AND lea.event_id = le.event_id
702 AND lea.EVENT_ACTION_NAME = 'NOTIFICATION'
703 AND lea.enabled_flag = 'Y'
704 AND lea.loan_type_id = p_loan_type_id;
705 CURSOR csr_current_user IS
706 SELECT fndu.user_name
707 FROM fnd_user fndu
708 WHERE fndu.user_id = p_current_user_id;
709 CURSOR csr_loan_role_users IS
710 SELECT fndu.user_name
711 ,fndu.user_id
712 FROM jtf_rs_role_relations rel
713 ,jtf_rs_roles_b rol
714 ,jtf_rs_resource_extns res
715 ,fnd_user fndu
716 WHERE rel.role_id = rol.role_id
717 AND rel.delete_flag <> 'Y'
718 AND SYSDATE BETWEEN NVL(rel.start_date_active,sysdate)
719 AND NVL(rel.end_date_active,sysdate)
720 AND rol.role_type_code = 'LOANS'
721 AND rol.role_code = l_primary_recipient_name
722 AND rol.active_flag = 'Y'
723 AND rel.role_resource_id = res.resource_id
724 AND res.category = 'EMPLOYEE'
725 AND res.start_date_active <= SYSDATE
726 AND (res.end_date_active is null or res.end_date_active >= SYSDATE)
727 AND fndu.user_id = res.user_id;
728 CURSOR csr_loan_details IS
729 SELECT hp.party_name borrower_name
730 ,fnd.user_name
731 ,res.source_name
732 ,llklc.meaning loan_class
733 ,llklt.loan_type_name loan_type
734 ,llkst.meaning loan_subtype
735 ,to_char(nvl(llh.collateral_percent,0)) || '%' collateral_percent
736 ,llh.loan_start_date
737 ,llh.loan_term || ' ' || llktt.meaning term
738 ,llh.loan_maturity_date
739 ,LNS_FINANCIALS.getActiveRate(llh.LOAN_ID) interest_rate
740 ,llkp.meaning loan_purpose
741 ,llh.org_id
742 FROM lns_loan_headers_all_vl llh, hz_parties hp,
743 jtf_rs_resource_extns res, fnd_user fnd,
744 lns_payments_summary_v ps,
745 lns_lookups llktt,
746 lns_lookups llklc,
747 lns_loan_types_vl llklt,
748 lns_lookups llkp,
749 lns_lookups llkst
750 WHERE llh.primary_borrower_id = hp.party_id
751 AND llh.loan_assigned_to = res.resource_id
752 AND res.category = 'EMPLOYEE'
753 AND fnd.user_id = res.user_id
754 AND llktt.lookup_code = llh.loan_term_period
755 AND llktt.lookup_type = 'PERIOD'
756 AND llklc.lookup_code = llh.loan_class_code
757 AND llklc.lookup_type = 'LOAN_CLASS'
758 AND llklt.loan_type_id = llh.loan_type_id
759 AND llkp.lookup_code (+) = llh.loan_purpose_code
760 AND llkp.lookup_type (+) = 'LOAN_PURPOSE'
761 AND llkst.lookup_code (+) = llh.loan_subtype
762 AND llkst.lookup_type (+) = 'LOAN_SUBTYPE'
763 AND llh.loan_id = p_loan_id;
764
765 --Modified the query with case when construct for loan_amount and loan_formatted_amount to fix bug5126957 --karamach
766 CURSOR csr_loan_details1 IS
767 SELECT
768 (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
769 ELSE ps.total_principal_balance END) loan_amount
770 ,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
771 ELSE ps.total_principal_balance END),
772 FND_CURRENCY.SAFE_GET_FORMAT_MASK(llh.LOAN_CURRENCY,50))
773 || ' ' || llh.loan_currency loan_formatted_amount
774 FROM lns_loan_headers_all llh
775 ,lns_pay_sum_v ps
776 WHERE llh.loan_id = p_loan_id
777 AND ps.loan_id = llh.loan_id;
778 CURSOR csr_loan_details2 IS
779 SELECT to_char(ps.total_overdue,
780 FND_CURRENCY.SAFE_GET_FORMAT_MASK(llh.LOAN_CURRENCY,50))
781 || ' ' || llh.loan_currency overdue_amount
782 ,ps.number_overdue_bills overdue_num
783 FROM lns_loan_headers_all llh
784 ,lns_pay_sum_overdue_v ps
785 WHERE llh.loan_id = p_loan_id
786 AND ps.loan_id = llh.loan_id;
787
788 BEGIN
789 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
790 OPEN csr_notification_details;
791 FETCH csr_notification_details
792 INTO l_primary_recipient_type
793 , l_primary_recipient_name
794 , l_priority_num
795 , l_active_for_num
796 , l_delivery_method
797 , l_event_action_id;
798 IF csr_notification_details%NOTFOUND THEN
799 RETURN 'N';
800 END IF;
801 CLOSE csr_notification_details;
802 OPEN csr_loan_details;
803 FETCH csr_loan_details
804 INTO l_borrower_name
805 , l_loan_assigned_user
806 , l_loan_assigned_name
807 , l_loan_class
808 , l_loan_type
809 , l_loan_subtype
810 , l_collateral_percent
811 , l_loan_start_date
812 , l_term
813 , l_loan_maturity_date
814 , l_interest_rate
815 , l_loan_purpose
816 , l_org_id;
817 CLOSE csr_loan_details;
818 OPEN csr_loan_details1;
819 FETCH csr_loan_details1
820 INTO l_loan_amount
821 , l_loan_formatted_amount;
822 CLOSE csr_loan_details1;
823 OPEN csr_loan_details2;
824 FETCH csr_loan_details2
825 INTO l_overdue_amount
826 , l_overdue_num;
827 CLOSE csr_loan_details2;
828 OPEN csr_current_user;
829 FETCH csr_current_user
830 INTO l_current_user;
831 CLOSE csr_current_user;
832 l_loan_number := wf_engine.GetItemAttrText
833 ( itemtype => itemtype,
834 itemkey => itemkey,
835 aname => 'LNS_LOAN_NUMBER');
836 IF l_primary_recipient_type = 'ROLE' THEN
837 OPEN csr_loan_role_users;
838 FETCH csr_loan_role_users
839 INTO l_user_name,l_user_id;
840 IF csr_loan_role_users%NOTFOUND THEN
841 RETURN 'N';
842 END IF;
843 if (has_user_org_access(l_user_id,l_org_id)) then
844 l_user_roles := l_user_roles||','||l_user_name;
845 end if;
846 LOOP
847 FETCH csr_loan_role_users
848 INTO l_user_name,l_user_id;
849 EXIT WHEN csr_loan_role_users%NOTFOUND;
850 if (has_user_org_access(l_user_id,l_org_id)) then
851 l_user_roles := l_user_roles||','||l_user_name;
852 end if;
853 END LOOP;
854 CLOSE csr_loan_role_users;
855 IF substr(l_user_roles,1,1) = ','
856 THEN
857 l_user_roles := substr(l_user_roles, 2, (length(l_user_roles) - 1));
858 END IF;
859 if (nvl(length(l_user_roles),0) < 3) then
860 RETURN 'N';
861 end if;
862 l_role_name := 'Loan Managers'|| '(' ||l_loan_number || '-' || ItemKey
863 || ')';
864 ELSIF l_primary_recipient_type = 'INDIVIDUAL' AND l_primary_recipient_name =
865 'LOAN_ASSIGNED_TO' THEN
866 l_user_roles := l_loan_assigned_user;
867 l_role_name := l_loan_assigned_name || '(' ||l_loan_number || '-'
868 || ItemKey || ')';
869 END IF;
870 wf_directory.CreateAdhocRole(
871 role_name => l_role_name,
872 role_display_name => l_role_name,
873 notification_preference => 'MAILHTM2'
874 );
875 wf_directory.AddUsersToAdhocRole(role_name => l_role_name,
876 role_users => l_user_roles);
877
878 wf_engine.SetItemAttrText (itemtype => itemtype,
879 itemkey => itemkey,
880 aname => 'LNS_PRIMARY_ROLE',
881 avalue => l_role_name);
882 wf_engine.SetItemAttrText (itemtype => itemtype,
883 itemkey => itemkey,
884 aname => 'LNS_BORROWER_NAME',
885 avalue => l_borrower_name);
886 wf_engine.SetItemAttrNumber(itemtype => itemtype,
887 itemkey => itemkey,
888 aname => 'LNS_PRIORITY',
889 avalue => l_priority_num);
890 wf_engine.SetItemAttrNumber(itemtype => itemtype,
891 itemkey => itemkey,
892 aname => 'LNS_EVENT_ACTION_ID',
893 avalue => l_event_action_id);
894 wf_engine.SetItemAttrNumber(itemtype => itemtype,
895 itemkey => itemkey,
896 aname => 'LNS_TIMEOUT',
897 avalue => l_active_for_num);
898 wf_engine.SetItemAttrText (itemtype => itemtype,
899 itemkey => itemkey,
900 aname => 'LNS_LOAN_ASSIGNED_USER',
901 avalue => l_loan_assigned_user);
902 wf_engine.SetItemAttrText (itemtype => itemtype,
903 itemkey => itemkey,
904 aname => 'LNS_LOAN_ASSIGNED_NAME',
905 avalue => l_loan_assigned_name);
906 wf_engine.SetItemAttrText (itemtype => itemtype,
907 itemkey => itemkey,
908 aname => 'LNS_CURRENT_USER',
909 avalue => l_current_user);
910 wf_engine.SetItemAttrText (itemtype => itemtype,
911 itemkey => itemkey,
912 aname => 'LNS_LOAN_CLASS',
913 avalue => l_loan_class);
914 wf_engine.SetItemAttrText (itemtype => itemtype,
915 itemkey => itemkey,
916 aname => 'LNS_LOAN_TYPE',
917 avalue => l_loan_type);
918 wf_engine.SetItemAttrText (itemtype => itemtype,
919 itemkey => itemkey,
920 aname => 'LNS_LOAN_SUBTYPE',
921 avalue => l_loan_subtype);
922 wf_engine.SetItemAttrText (itemtype => itemtype,
923 itemkey => itemkey,
924 aname => 'LNS_COLLATERAL_PERCENT',
925 avalue => l_collateral_percent);
926 wf_engine.SetItemAttrNumber(itemtype => itemtype,
927 itemkey => itemkey,
928 aname => 'LNS_LOAN_AMOUNT',
929 avalue => l_loan_amount);
930 wf_engine.SetItemAttrText (itemtype => itemtype,
931 itemkey => itemkey,
932 aname => 'LNS_FORMATTED_AMOUNT',
933 avalue => l_loan_formatted_amount);
934 wf_engine.SetItemAttrDate (itemtype => itemtype,
935 itemkey => itemkey,
936 aname => 'LNS_LOAN_START_DATE',
937 avalue => l_loan_start_date);
938 wf_engine.SetItemAttrText (itemtype => itemtype,
939 itemkey => itemkey,
940 aname => 'LNS_TERM',
941 avalue => l_term);
942 wf_engine.SetItemAttrDate (itemtype => itemtype,
943 itemkey => itemkey,
944 aname => 'LNS_LOAN_MATURITY_DATE',
945 avalue => l_loan_maturity_date);
946 wf_engine.SetItemAttrNumber(itemtype => itemtype,
947 itemkey => itemkey,
948 aname => 'LNS_INTEREST_RATE',
949 avalue => l_interest_rate);
950 wf_engine.SetItemAttrText(itemtype => itemtype,
951 itemkey => itemkey,
952 aname => 'LNS_OVERDUE_AMOUNT',
953 avalue => l_overdue_amount);
954 wf_engine.SetItemAttrNumber(itemtype => itemtype,
955 itemkey => itemkey,
956 aname => 'LNS_OVERDUE_NUM',
957 avalue => l_overdue_num);
958 wf_engine.SetItemAttrText(itemtype => itemtype,
959 itemkey => itemkey,
960 aname => 'LNS_LOAN_PURPOSE',
961 avalue => l_loan_purpose);
962 return 'Y';
963 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
964 EXCEPTION
965 WHEN OTHERS THEN
966 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
967 || ' -');
968 RAISE;
969 END CREATE_NOTIFICATION_DETAILS;
970 /*========================================================================
971 | PRIVATE PROCEDURE PROCESS_EVENT
972 |
973 | DESCRIPTION
974 | This procedure logs debug messages to db and to CM log
975 |
976 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
977 |
978 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
979 | None
980 |
981 | PARAMETERS
982 | itemtype in Item Type
983 | itemkey in Item Key
984 | actid in Action Id
985 | funcmode in Function Mode
986 | resultout out Result Out
987 |
988 | KNOWN ISSUES
989 | None
990 |
991 |
992 | NOTES
993 | Any interesting aspect of the code in the package body which needs
994 | to be stated.
995 |
996 | MODIFICATION HISTORY
997 | Date Author Description of Changes
998 | 17-Jan-2005 GBELLARY Created
999 |
1000 *=======================================================================*/
1001 PROCEDURE PROCESS_EVENT(itemtype in varchar2,
1002 itemkey in varchar2,
1003 actid in number,
1004 funcmode in varchar2,
1005 resultout out NOCOPY varchar2 ) IS
1006 /*-----------------------------------------------------------------------+
1007 | Local Variable Declarations and initializations |
1008 +-----------------------------------------------------------------------*/
1009
1010 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_EVENT';
1011 l_api_version CONSTANT NUMBER := 1.0;
1012 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
1013 l_loan_class_code LNS_LOAN_HEADERS_ALL.LOAN_CLASS_CODE%TYPE;
1014 l_loan_type LNS_LOAN_TYPES.LOAN_TYPE_NAME%TYPE;
1015 l_loan_type_id LNS_LOAN_TYPES.LOAN_TYPE_ID%TYPE;
1016 l_current_user_id LNS_LOAN_HEADERS_ALL.CREATED_BY%TYPE;
1017 l_event_name LNS_EVENTS.EVENT_NAME%TYPE;
1018
1019 /*-----------------------------------------------------------------------+
1020 | Cursor Declarations |
1021 +-----------------------------------------------------------------------*/
1022 BEGIN
1023 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +'); IF (funcmode <> wf_engine.eng_run) THEN
1024
1025 resultout := wf_engine.eng_null;
1026 return;
1027 END IF;
1028 l_loan_id := wf_engine.GetItemAttrNumber
1029 ( itemtype => itemtype,
1030 itemkey => itemkey,
1031 aname => 'LNS_LOAN_ID');
1032 l_loan_class_code := wf_engine.GetItemAttrText
1033 ( itemtype => itemtype,
1034 itemkey => itemkey,
1035 aname => 'LNS_LOAN_CLASS_CODE');
1036 l_loan_type := wf_engine.GetItemAttrText
1037 ( itemtype => itemtype,
1038 itemkey => itemkey,
1039 aname => 'LNS_LOAN_TYPE');
1040 l_loan_type_id := wf_engine.GetItemAttrNumber
1041 ( itemtype => itemtype,
1042 itemkey => itemkey,
1043 aname => 'LNS_LOAN_TYPE_ID');
1044 l_current_user_id := wf_engine.GetItemAttrNumber
1045 ( itemtype => itemtype,
1046 itemkey => itemkey,
1047 aname => 'LNS_CURRENT_USER_ID');
1048 l_event_name := wf_engine.GetItemAttrText
1049 ( itemtype => itemtype,
1050 itemkey => itemkey,
1051 aname => 'LNS_EVENT_NAME');
1052 resultout := 'COMPLETE:' || create_notification_details(itemkey => itemkey
1053 ,itemtype => itemtype
1054 ,p_event_name => l_event_name
1055 ,p_loan_id => l_loan_id
1056 ,p_loan_class_code => l_loan_class_code
1057 ,p_loan_type => l_loan_type
1058 ,p_loan_type_id => l_loan_type_id
1059 ,p_current_user_id => l_current_user_id
1060 );
1061 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
1065 || ' -');
1066 wf_core.context('LNSWF', 'PROCESS_EVENT', itemtype, itemkey,
1067 to_char(actid), funcmode); RAISE;
1068 END PROCESS_EVENT;
1069 /*========================================================================
1070 | PUBLIC PROCEDURE SYNCH_EVENT_ACTIONS
1071 |
1072 | DESCRIPTION
1073 | This procedure adds event actions for newly created user extensible
1074 | Loan Types.
1075 | PSEUDO CODE/LOGIC
1076 |
1077 | PARAMETERS
1078 | NONE.
1079 |
1080 | KNOWN ISSUES
1081 | None
1082 |
1083 | NOTES
1084 |
1085 | MODIFICATION HISTORY
1086 | Date Author Description of Changes
1087 | 23-Feb-2005 GBELLARY Created
1088 |
1089 *=======================================================================*/
1090 PROCEDURE SYNCH_EVENT_ACTIONS IS
1091 /*-----------------------------------------------------------------------+
1092 | Local Variable Declarations and initializations |
1093 +-----------------------------------------------------------------------*/
1094
1095 l_api_name CONSTANT VARCHAR2(30) := 'SYNCH_EVENT_ACTIONS';
1096 l_api_version CONSTANT NUMBER := 1.0;
1097 BEGIN
1098 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1099 insert into lns_event_actions (
1100 EVENT_ACTION_ID
1101 , EVENT_ID
1102 , EVENT_ACTION_NAME
1103 , DESCRIPTION
1104 , LOAN_TYPE_ID
1105 , ACTION_TYPE
1106 , ENABLED_FLAG
1107 , API_NAME
1108 , NOTIFICATION_TYPE
1109 , SETUP_TYPE
1110 , PRIMARY_RECIPIENT_TYPE
1111 , PRIMARY_RECIPIENT_NAME
1112 , SECONDARY_RECIPIENT_TYPE
1113 , SECONDARY_RECIPIENT_NAME
1114 , PRIORITY_NUM
1115 , DAYS_PRIOR_NUM
1116 , ACTIVE_FOR_NUM
1117 , DELIVERY_METHOD
1118 , OBJECT_VERSION_NUMBER
1119 , CREATION_DATE
1120 , CREATED_BY
1121 , LAST_UPDATE_DATE
1122 , LAST_UPDATED_BY
1123 , LAST_UPDATE_LOGIN )
1124 select LNS_EVENT_ACTIONS_S.nextval --event_action_id
1125 , ea.EVENT_ID
1126 , ea.EVENT_ACTION_NAME
1127 , ea.DESCRIPTION
1128 , missingvalues.LOAN_TYPE_ID
1129 , ea.ACTION_TYPE
1130 , 'Y' --enabled_flag
1131 , ea.API_NAME
1132 , ea.NOTIFICATION_TYPE
1133 , ea.SETUP_TYPE
1134 , ea.PRIMARY_RECIPIENT_TYPE
1135 , ea.PRIMARY_RECIPIENT_NAME
1136 , ea.SECONDARY_RECIPIENT_TYPE
1137 , ea.SECONDARY_RECIPIENT_NAME
1138 , ea.PRIORITY_NUM
1139 , ea.DAYS_PRIOR_NUM
1140 , ea.ACTIVE_FOR_NUM
1141 , ea.DELIVERY_METHOD
1142 , ea.OBJECT_VERSION_NUMBER
1143 , sysdate
1144 , LNS_UTILITY_PUB.CREATED_BY
1145 , sysdate
1146 , LNS_UTILITY_PUB.LAST_UPDATED_BY
1147 , LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
1148 from lns_event_actions ea, lns_events ev,
1149 (select loan_class_code, loan_type_id
1150 from lns_loan_types_vl
1151 minus
1152 select ev.loan_class_code loan_class_code, ea.loan_type_id loan_type_id
1153 from lns_events ev, lns_event_actions ea
1154 where ea.event_id = ev.event_id
1155 and ea.event_action_name = 'NOTIFICATION') missingvalues
1156 where ev.loan_class_code = missingvalues.loan_class_code
1157 and ea.event_id = ev.event_id
1158 and ea.loan_type = decode(ev.loan_class_code,'ERS','ERS','BUSINESS')
1159 and ea.event_action_name = 'NOTIFICATION';
1160 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Error in synch event actions: ' || sqlerrm);
1164 /* This error message needs to be seeded in the future */
1165 FND_MESSAGE.SET_NAME('LNS', 'LNS_ERROR_SYNCH_EVTS');
1166 FND_MSG_PUB.ADD;
1167 raise;
1168 END;
1169 BEGIN
1170 G_LOG_ENABLED := 'N';
1171 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1172
1173 /* getting msg logging info */
1174 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1175 IF (G_LOG_ENABLED = 'N') then
1176 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1177 ELSE
1178 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1179 END IF;
1180 END LNS_WORK_FLOW;