[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_APP_WF
Source
1 PACKAGE BODY OKL_LEASE_APP_WF AS
2 /* $Header: OKLLAWFB.pls 120.3 2010/12/21 04:17:35 rpillay ship $ */
3
4 ---------------------------------------------------
5 -- Global Constants
6 ---------------------------------------------------
7 G_FE_APPROVAL_WF CONSTANT VARCHAR2(2) := 'WF';
8 G_FE_APPROVAL_AME CONSTANT VARCHAR2(3) := 'AME';
9
10 G_WF_ITM_APPLICATION_ID CONSTANT VARCHAR2(20) := 'APPLICATION_ID';
11 G_WF_ITM_TRANSACTION_ID CONSTANT VARCHAR2(20) := 'TRANSACTION_ID';
12 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT VARCHAR2(20) := 'TRX_TYPE_ID';
13 G_WF_ITM_APPROVER CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVER';
14 G_WF_ITM_REQUESTER CONSTANT VARCHAR2(20) := 'REQUESTER';
15 G_WF_ITM_REQUESTER_ID CONSTANT VARCHAR2(20) := 'REQUESTOR_ID';
16
17 G_WF_ITM_APP_REQUEST_SUB CONSTANT VARCHAR2(30) := 'APP_REQUEST_SUB';
18 G_WF_ITM_APP_REMINDER_SUB CONSTANT VARCHAR2(30) := 'APP_REMINDER_SUB';
19 G_WF_ITM_APP_APPROVED_SUB CONSTANT VARCHAR2(30) := 'APP_APPROVED_SUB';
20 G_WF_ITM_APP_REJECTED_SUB CONSTANT VARCHAR2(30) := 'APP_REJECTED_SUB';
21 G_WF_ITM_APP_REMINDER_HEAD CONSTANT VARCHAR2(30) := 'APP_REMINDER_HEAD';
22 G_WF_ITM_APP_APPROVED_HEAD CONSTANT VARCHAR2(30) := 'APP_APPROVED_HEAD';
23 G_WF_ITM_APP_REJECTED_HEAD CONSTANT VARCHAR2(30) := 'APP_REJECTED_HEAD';
24
25 G_WF_ITM_MESSAGE_SUBJECT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_SUBJECT';
26 G_WF_ITM_MESSAGE_DESCR CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DESCRIPTION';
27 G_WF_ITM_MESSAGE_BODY CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DOC';
28
29 G_WF_ITM_APPROVED_YN_YES CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVED';
30 ---------------------------------------------------
31 -- Global Cursor definition
32 ---------------------------------------------------
33 -- Get the requester name
34 CURSOR fnd_user_csr IS
35 SELECT USER_NAME
36 FROM FND_USER
37 WHERE USER_ID = fnd_global.user_id;
38
39 -- Get the valid application id from FND
40 CURSOR c_get_app_id_csr IS
41 SELECT APPLICATION_ID
42 FROM FND_APPLICATION
43 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
44
45 CURSOR obj_name_csr(object_type IN VARCHAR2) IS
46 SELECT MEANING
47 FROM FND_LOOKUPS
48 WHERE LOOKUP_TYPE = 'OKL_FRONTEND_OBJECTS'
49 AND LOOKUP_CODE = object_type;
50
51 --function to return the message from fnd message for the subject of the notifications
52
53 FUNCTION get_message(p_msg_name IN VARCHAR2, object_name IN VARCHAR2,
54 object_value IN VARCHAR2) RETURN VARCHAR2 IS
55 l_message VARCHAR2(100);
56
57 BEGIN
58
59 IF p_msg_name IS NOT NULL THEN
60 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME, NAME => p_msg_name);
61 Fnd_Message.SET_TOKEN(TOKEN => 'OBJECT_NAME',
62 VALUE => object_name);
63 Fnd_Message.SET_TOKEN(TOKEN => 'NAME', VALUE => object_value);
64 l_message := fnd_message.get();
65 END IF;
66 RETURN l_message;
67 EXCEPTION
68 WHEN OTHERS THEN
69 RETURN NULL;
70 END get_message;
71
72 -- get the message for a message name frm fnd messages
73 FUNCTION get_token(p_msg_name IN VARCHAR2, token_name IN VARCHAR2,
74 token_value IN VARCHAR2) RETURN VARCHAR2 IS
75 l_message VARCHAR2(100);
76
77 BEGIN
78
79 IF p_msg_name IS NOT NULL THEN
80 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME, NAME => p_msg_name);
81 Fnd_Message.SET_TOKEN(TOKEN => token_name, VALUE => token_value);
82 l_message := fnd_message.get();
83 END IF;
84 RETURN l_message;
85 EXCEPTION
86 WHEN OTHERS THEN
87 RETURN NULL;
88 END get_token;
89
90 -- get the message body for Lease Application Template
91 FUNCTION get_lat_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
92 l_name VARCHAR2(240);
93 l_currency VARCHAR2(30);
94 l_type VARCHAR2(30);
95 l_version_number VARCHAR2(24);
96 l_effective_from DATE;
97 l_effective_to DATE;
98 lv_message_body VARCHAR2(4000);
99
100 BEGIN
101 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LAT_NAME');
102 l_version_number := wf_engine.GetItemAttrText(itemtype,
103 itemkey,
104 'VERSION_NUMBER');
105 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
106 itemkey,
107 'EFFECTIVE_FROM');
108 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
109 itemkey,
110 'EFFECTIVE_TO');
111 lv_message_body := '<body>' ||
112 get_token('OKL_NAME', 'NAME', l_name) ||
113 '<br>' ||
114 get_token('OKL_VERSION',
115 'VERSION',
116 l_version_number) ||
117 '<br>' ||
118 get_token('OKL_EFFECTIVE_FROM',
119 'FROM_DATE',
120 fnd_Date.date_to_displaydate(l_effective_from, calendar_aware => FND_DATE.calendar_aware)) ||
121 '<br>' ||
122 get_token('OKL_EFFECTIVE_TO',
123 'TO_DATE',
124 fnd_Date.date_to_displaydate(l_effective_to, calendar_aware => FND_DATE.calendar_aware)) ||
125 '<br>' ||
126 '</body>';
127 RETURN lv_message_body;
128 END get_lat_msg_body;
129
130 -- get the message body for Lease Application
131 FUNCTION get_lease_app_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
132 l_name VARCHAR2(240);
133 l_effective_from DATE;
134 l_effective_to DATE;
135 lv_message_body VARCHAR2(4000);
136
137 BEGIN
138 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LA_NAME');
139
140 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
141 itemkey,
142 'EFFECTIVE_FROM');
143 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
144 itemkey,
145 'EFFECTIVE_TO');
146 lv_message_body := '<body>' ||
147 get_token('OKL_NAME', 'NAME', l_name) ||
148 '<br>' ||
149 get_token('OKL_EFFECTIVE_FROM',
150 'FROM_DATE',
151 fnd_Date.date_to_displaydate(l_effective_from, calendar_aware => FND_DATE.calendar_aware)) ||
152 '<br>' ||
153 get_token('OKL_EFFECTIVE_TO',
154 'TO_DATE',
155 fnd_Date.date_to_displaydate(l_effective_to, calendar_aware => FND_DATE.calendar_aware)) ||
156 '<br>' ||
157 '</body>';
158 RETURN lv_message_body;
159 END get_lease_app_msg_body;
160
161 PROCEDURE get_lease_app_details (itemtype IN VARCHAR2,
162 itemkey IN VARCHAR2) IS
163 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'get_lease_app_details';
164 x_return_status VARCHAR2(1);
165 x_msg_count NUMBER;
166 x_msg_data VARCHAR2(32767);
167
168 l_lease_app_id OKL_LEASE_APPLICATIONS_V.ID%TYPE;
169 l_user_name VARCHAR2(240);
170 l_application_id fnd_application.application_id%TYPE;
171
172 CURSOR c_get_lease_app_details (cp_la_id OKL_LEASE_APPLICATIONS_V.ID%TYPE) IS
173 SELECT LAP.REFERENCE_NUMBER LEASE_APP_NAME
174 , LAP.APPLICATION_STATUS LEASE_APP_STATUS
175 , LAP.VALID_FROM EFFECTIVE_FROM
176 , LAP.VALID_TO EFFECTIVE_TO
177 FROM OKL_LEASE_APPLICATIONS_V LAP
178 WHERE LAP.ID = cp_la_id;
179
180 l_lease_app_rec c_get_lease_app_details%ROWTYPE;
181 BEGIN
182 -- get the value of the version id from the workflow
183 l_lease_app_id := wf_engine.GetItemAttrText(itemtype,
184 itemkey,
185 'LA_ID');
186
187 -- set the attributes required in the workflow
188 OPEN c_get_lease_app_details(l_lease_app_id);
189 FETCH c_get_lease_app_details INTO l_lease_app_rec;
190 CLOSE c_get_lease_app_details;
191
192 -- set the attributes of the workflow
193 wf_engine.SetItemAttrText(itemtype,
194 itemkey,
195 'LA_NAME',
196 l_lease_app_rec.lease_app_name);
197
198 wf_engine.SetItemAttrText(itemtype,
199 itemkey,
200 'EFFECTIVE_FROM',
201 l_lease_app_rec.effective_from);
202
203 wf_engine.SetItemAttrText(itemtype,
204 itemkey,
205 'EFFECTIVE_TO',
206 l_lease_app_rec.effective_to);
207
208 -- Set the attributes on the Approver, requestor
209 OPEN fnd_user_csr;
210 FETCH fnd_user_csr INTO l_user_name ;
211 CLOSE fnd_user_csr;
212
213 -- get the application id
214
215 OPEN c_get_app_id_csr;
216 FETCH c_get_app_id_csr INTO l_application_id ;
217 CLOSE c_get_app_id_csr;
218
219 -- set the values of the approver and the requestor
220
221 wf_engine.SetItemAttrText(itemtype,
222 itemkey,
223 G_WF_ITM_APPROVER,
224 l_user_name);
225
226 wf_engine.SetItemAttrText(itemtype,
227 itemkey,
228 G_WF_ITM_REQUESTER,
229 l_user_name);
230
231 wf_engine.SetItemAttrText(itemtype,
232 itemkey,
233 G_WF_ITM_REQUESTER_ID,
234 fnd_global.user_id);
235 wf_engine.SetItemAttrText(itemtype,
236 itemkey,
237 G_WF_ITM_TRANSACTION_TYPE_ID,
238 itemtype);
239 wf_engine.SetItemAttrText(itemtype,
240 itemkey,
241 G_WF_ITM_TRANSACTION_ID,
242 l_lease_app_id);
243 wf_engine.SetItemAttrText(itemtype,
244 itemkey,
245 G_WF_ITM_APPLICATION_ID,
246 l_application_id);
247
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
252 G_PKG_NAME,
253 'OTHERS',
254 x_msg_count,
255 x_msg_data,
256 '_WF');
257 RAISE;
258 END get_lease_app_details;
259
260 -- Start of comments
261 --
262 -- Procedure Name : get_la_approval_msg_doc
263 -- Description : Sets the message document for notification for Lease
264 -- Application approval
265 -- Business Rules :
266 -- Parameters :
267 -- Version : 1.0
268 -- End of comments
269 PROCEDURE get_la_approval_msg_doc(document_id IN VARCHAR2,
270 display_type IN VARCHAR2,
271 document IN OUT NOCOPY VARCHAR2,
272 document_type IN OUT NOCOPY VARCHAR2) IS
273
274 BEGIN
275 -- get the message body
276 document := get_lease_app_msg_body('OKLSOLAP', document_id);
277 document_type := display_type;
278 END get_la_approval_msg_doc;
279
280 -- Sets the subjects of messages which are sent from the workflow notifications
281 PROCEDURE set_messages(itemtype IN VARCHAR2
282 , itemkey IN VARCHAR2
283 , object_type IN VARCHAR2) IS
284 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'set_messages';
285 l_name VARCHAR2(100);
286 l_object_name VARCHAR2(50);
287 l_request_message VARCHAR2(500);
288 l_approved_message VARCHAR2(500);
289 l_rejected_message VARCHAR2(500);
290 l_reminder_message VARCHAR2(500);
291 x_return_status VARCHAR2(1);
292
293 x_msg_count NUMBER;
294 x_msg_data VARCHAR2(32767);
295
296 BEGIN
297 OPEN obj_name_csr(object_type);
298 FETCH obj_name_csr INTO l_object_name;
299 CLOSE obj_name_csr;
300
301 IF (itemtype = 'OKLSTLAT') THEN
302 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LAT_NAME');
303
304 wf_engine.SetItemAttrDocument(itemtype => itemtype,
305 itemkey => itemkey,
306 aname => G_WF_ITM_MESSAGE_BODY,
307 documentid => 'plsql:okl_lease_app_wf.get_lat_msg_doc/' ||
308 itemkey);
309 wf_engine.SetItemAttrText(itemtype => itemtype,
310 itemkey => itemkey,
311 aname => G_WF_ITM_MESSAGE_DESCR,
312 avalue => get_lat_msg_body(itemtype,
313 itemkey));
314 ELSIF (itemtype = 'OKLSOLAW') THEN
315 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LA_NAME');
316
317 wf_engine.SetItemAttrDocument(itemtype => itemtype,
318 itemkey => itemkey,
319 aname => G_WF_ITM_MESSAGE_BODY,
320 documentid => 'plsql:okl_lease_app_wf.get_la_withdraw_msg_doc/' ||
321 itemkey);
322 wf_engine.SetItemAttrText(itemtype => itemtype,
323 itemkey => itemkey,
324 aname => G_WF_ITM_MESSAGE_DESCR,
325 avalue => get_lease_app_msg_body(itemtype,
326 itemkey));
327 ELSIF (itemtype = 'OKLSOLAP') THEN
328 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LA_NAME');
329
330 wf_engine.SetItemAttrDocument(itemtype => itemtype,
331 itemkey => itemkey,
332 aname => G_WF_ITM_MESSAGE_BODY,
333 documentid => 'plsql:okl_lease_app_wf.get_la_approval_msg_doc/' ||
334 itemkey);
335 wf_engine.SetItemAttrText(itemtype => itemtype,
336 itemkey => itemkey,
337 aname => G_WF_ITM_MESSAGE_DESCR,
338 avalue => get_lease_app_msg_body(itemtype,
339 itemkey));
340
341 END IF;
342
343 -- set the messages of the notification
344 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
345 l_object_name,
346 l_name);
347 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
348 l_object_name,
349 l_name);
350 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
351 l_object_name,
352 l_name);
353 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
354 l_object_name,
355 l_name);
356
357 wf_engine.SetItemAttrText(itemtype,
358 itemkey,
359 G_WF_ITM_APP_REQUEST_SUB,
360 l_request_message);
361 wf_engine.SetItemAttrText(itemtype,
362 itemkey,
363 G_WF_ITM_APP_REMINDER_SUB,
364 l_reminder_message);
365 wf_engine.SetItemAttrText(itemtype,
366 itemkey,
367 G_WF_ITM_APP_REMINDER_HEAD,
368 l_reminder_message);
369 wf_engine.SetItemAttrText(itemtype,
370 itemkey,
371 G_WF_ITM_APP_APPROVED_SUB,
372 l_approved_message);
373 wf_engine.SetItemAttrText(itemtype,
374 itemkey,
375 G_WF_ITM_APP_APPROVED_HEAD,
376 l_approved_message);
377 wf_engine.SetItemAttrText(itemtype,
378 itemkey,
379 G_WF_ITM_APP_REJECTED_SUB,
380 l_rejected_message);
381 wf_engine.SetItemAttrText(itemtype,
382 itemkey,
383 G_WF_ITM_APP_REJECTED_HEAD,
384 l_rejected_message);
385 EXCEPTION
386 WHEN OTHERS THEN
387 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
388 G_PKG_NAME,
389 'OTHERS',
390 x_msg_count,
391 x_msg_data,
392 '_WF');
393 RAISE;
394 END set_messages;
395
396 -- Start of comments
397 --
398 -- Procedure Name : get_lat_msg_doc
399 -- Description : Sets the message document for notification for Lease
400 -- Application template
401 -- Business Rules :
402 -- Parameters :
403 -- Version : 1.0
404 -- End of comments
405 PROCEDURE get_lat_msg_doc(document_id IN VARCHAR2,
406 display_type IN VARCHAR2,
407 document IN OUT NOCOPY VARCHAR2,
408 document_type IN OUT NOCOPY VARCHAR2) IS
409
410 BEGIN
411 -- get the message body
412 document := get_lat_msg_body('OKLSTLAT', document_id);
413 document_type := display_type;
414 END get_lat_msg_doc;
415
416 -- Start of comments
417 --
418 -- Procedure Name : get_la_withdraw_msg_doc
419 -- Description : Sets the message document for notification for Lease
420 -- Application withdrawal
421 -- Business Rules :
422 -- Parameters :
423 -- Version : 1.0
424 -- End of comments
425 PROCEDURE get_la_withdraw_msg_doc(document_id IN VARCHAR2,
426 display_type IN VARCHAR2,
427 document IN OUT NOCOPY VARCHAR2,
428 document_type IN OUT NOCOPY VARCHAR2) IS
429
430 BEGIN
431 -- get the message body
432 document := get_lease_app_msg_body('OKLSOLAW', document_id);
433 document_type := display_type;
434 END get_la_withdraw_msg_doc;
435
436
437 -- Query the Lease Application Template details and set in Workflow attributes
438 -- for displaying notifications
439 PROCEDURE get_lat_ver_data(itemtype IN VARCHAR2,
440 itemkey IN VARCHAR2) IS
441 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'set_messages';
442 x_return_status VARCHAR2(1);
443 x_msg_count NUMBER;
444 x_msg_data VARCHAR2(32767);
445
446 l_user_name VARCHAR2(240);
447 l_application_id fnd_application.application_id%TYPE;
448 l_lat_version_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE;
449
450 CURSOR c_get_lat_details(cp_lat_ver_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
451 SELECT LATV.NAME LAT_NAME
452 , LAVV.VERSION_NUMBER
453 , LAVV.VALID_FROM EFFECTIVE_FROM
454 , LAVV.VALID_TO EFFECTIVE_TO
455 , LAVV.VERSION_STATUS
456 FROM OKL_LEASEAPP_TEMPLATES LATV
457 , OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
458 WHERE LAVV.LEASEAPP_TEMPLATE_ID = LATV.ID
459 AND LAVV.ID = cp_lat_ver_id;
460
461 lp_lat_dtls_rec c_get_lat_details%ROWTYPE;
462
463 BEGIN
464
465 -- get the value of the version id from the workflow
466 l_lat_version_id := wf_engine.GetItemAttrText(itemtype,
467 itemkey,
468 'VERSION_ID');
469
470 -- Query the details of the Lease Application Template
471 OPEN c_get_lat_details(l_lat_version_id);
472 FETCH c_get_lat_details INTO lp_lat_dtls_rec;
473 CLOSE c_get_lat_details;
474
475 -- set the attributes of the workflow
476 wf_engine.SetItemAttrText(itemtype,
477 itemkey,
478 'LAT_NAME',
479 lp_lat_dtls_rec.lat_name);
480
481 wf_engine.SetItemAttrText(itemtype,
482 itemkey,
483 'VERSION_NUMBER',
484 lp_lat_dtls_rec.version_number);
485
486 wf_engine.SetItemAttrText(itemtype,
487 itemkey,
488 'VERSION_STATUS',
489 lp_lat_dtls_rec.version_number);
490
491 wf_engine.SetItemAttrText(itemtype,
492 itemkey,
493 'EFFECTIVE_FROM',
494 lp_lat_dtls_rec.effective_from);
495
496 wf_engine.SetItemAttrText(itemtype,
497 itemkey,
498 'EFFECTIVE_TO',
499 lp_lat_dtls_rec.effective_to);
500
501 -- Set the attributes on the Approver, requestor
502 OPEN fnd_user_csr;
503 FETCH fnd_user_csr INTO l_user_name ;
504 CLOSE fnd_user_csr;
505
506 -- get the application id
507
508 OPEN c_get_app_id_csr;
509 FETCH c_get_app_id_csr INTO l_application_id ;
510 CLOSE c_get_app_id_csr;
511
512 -- set the values of the approver and the requestor
513
514 wf_engine.SetItemAttrText(itemtype,
515 itemkey,
516 G_WF_ITM_APPROVER,
517 l_user_name);
518
519 wf_engine.SetItemAttrText(itemtype,
520 itemkey,
521 G_WF_ITM_REQUESTER,
522 l_user_name);
523
524 wf_engine.SetItemAttrText(itemtype,
525 itemkey,
526 G_WF_ITM_REQUESTER_ID,
527 fnd_global.user_id);
528 wf_engine.SetItemAttrText(itemtype,
529 itemkey,
530 G_WF_ITM_TRANSACTION_TYPE_ID,
531 itemtype);
532 wf_engine.SetItemAttrText(itemtype,
533 itemkey,
534 G_WF_ITM_TRANSACTION_ID,
535 l_lat_version_id);
536 wf_engine.SetItemAttrText(itemtype,
537 itemkey,
538 G_WF_ITM_APPLICATION_ID,
539 l_application_id);
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
544 G_PKG_NAME,
545 'OTHERS',
546 x_msg_count,
547 x_msg_data,
548 '_WF');
549 RAISE;
550 END get_lat_ver_data;
551
552 -- Start of comments
553 --
554 -- Procedure Name : check_approval_process
555 -- Description : Procedure to check if the Approval Process is Workflow driven
556 -- or through AME.
557 -- Business Rules : Checks the Frontend profile and directs the approval flow
558 -- accordingly
559 -- Parameters :
560 -- Version : 1.0
561 -- End of comments
562
563 PROCEDURE check_approval_process(itemtype IN VARCHAR2,
564 itemkey IN VARCHAR2,
565 actid IN NUMBER,
566 funcmode IN VARCHAR2,
567 resultout OUT NOCOPY VARCHAR2) IS
568
569 l_approval_option VARCHAR2(10);
570 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'check_approval_process';
571
572 BEGIN
573
574 IF (funcmode = 'RUN') THEN
575 -- get the profile option
576 l_approval_option := fnd_profile.value('OKL_SO_APPROVAL_PROCESS');
577
578 -- depending on the profile option, take the workflow branch or the AME branch
579 IF l_approval_option = G_FE_APPROVAL_AME THEN
580 resultout := 'COMPLETE:AME';
581 ELSIF l_approval_option = G_FE_APPROVAL_WF THEN
582 resultout := 'COMPLETE:WF';
583 END IF;
584 RETURN;
585 END IF;
586
587 -- CANCEL or TIMEOUT mode
588 IF (funcmode = 'CANCEL' OR funcmode = 'TIMEOUT') THEN
589 resultout := 'COMPLETE:';
590 RETURN;
591 END IF;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 wf_core.context(G_PKG_NAME,
596 l_api_name,
597 itemtype,
598 itemkey,
599 actid,
600 funcmode);
601 RAISE;
602 END check_approval_process;
603
604 -- Start of comments
605 --
606 -- Procedure Name : check_la_credit_status
607 -- Description : Procedure to check if credit processing has been done on the
608 -- Lease Application.
609 -- Business Rules : Checks if the Lease Application is in CR-APPROVED or CR-REJECTED
610 -- Parameters :
611 -- Version : 1.0
612 -- End of comments
613
614 PROCEDURE check_la_credit_status(itemtype IN VARCHAR2,
615 itemkey IN VARCHAR2,
616 actid IN NUMBER,
617 funcmode IN VARCHAR2,
618 resultout OUT NOCOPY VARCHAR2) IS
619
620 l_approval_option VARCHAR2(10);
621 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'check_la_credit_status';
622 l_object_name VARCHAR2(50);
623 l_name VARCHAR2(100);
624 l_rejected_message VARCHAR2(500);
625
626 l_la_id OKL_LEASE_APPLICATIONS_B.ID%TYPE;
627 l_la_status OKL_LEASE_APPLICATIONS_B.APPLICATION_STATUS%TYPE;
628
629 CURSOR c_get_la_status(cp_la_id OKL_LEASE_APPLICATIONS_B.ID%TYPE) IS
630 SELECT LAB.APPLICATION_STATUS
631 FROM OKL_LEASE_APPLICATIONS_B LAB
632 WHERE LAB.ID = cp_la_id;
633
634 BEGIN
635
636 IF (funcmode = 'RUN') THEN
637 l_la_id := wf_engine.GetItemAttrText(itemtype,
638 itemkey,
639 'LA_ID');
640
641 OPEN c_get_la_status(l_la_id);
642 FETCH c_get_la_status INTO l_la_status;
643 CLOSE c_get_la_status;
644
645 IF (l_la_status = 'CR-APPROVED' OR l_la_status = 'CR-REJECTED') THEN
646 resultout := 'COMPLETE:YES';
647
648 -- set the attributes in workflow
649 get_lease_app_details(itemtype,itemkey);
650
651 --Set the message for rejection
652 OPEN obj_name_csr('LAP');
653 FETCH obj_name_csr INTO l_object_name;
654 CLOSE obj_name_csr;
655 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LA_NAME');
656
657 wf_engine.SetItemAttrDocument(itemtype => itemtype,
658 itemkey => itemkey,
659 aname => G_WF_ITM_MESSAGE_BODY,
660 documentid => 'plsql:okl_lease_app_wf.get_la_withdraw_msg_doc/' ||
661 itemkey);
662 wf_engine.SetItemAttrText(itemtype => itemtype,
663 itemkey => itemkey,
664 aname => G_WF_ITM_MESSAGE_DESCR,
665 avalue => get_lease_app_msg_body(itemtype,
666 itemkey));
667
668 l_rejected_message := get_message('OKL_FE_SALES_CR_DECIDED',
669 l_object_name,
670 l_name);
671
672 -- Set the message header
673 wf_engine.SetItemAttrText(itemtype,
674 itemkey,
675 G_WF_ITM_APP_REJECTED_SUB,
676 l_rejected_message);
677 wf_engine.SetItemAttrText(itemtype,
678 itemkey,
679 G_WF_ITM_APP_REJECTED_HEAD,
680 l_rejected_message);
681
682 ELSE
683 resultout := 'COMPLETE:NO';
684 END IF; -- end of check for credit staus
685 RETURN;
686 END IF;
687
688 -- CANCEL or TIMEOUT mode
689 IF (funcmode = 'CANCEL' OR funcmode = 'TIMEOUT') THEN
690 resultout := 'COMPLETE:';
691 RETURN;
692 END IF;
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 wf_core.context(G_PKG_NAME,
697 l_api_name,
698 itemtype,
699 itemkey,
700 actid,
701 funcmode);
702 RAISE;
703 END check_la_credit_status;
704
705 -- Start of comments
706 --
707 -- Procedure Name : get_lat_ver_details
708 -- Description : Gets the details of the Lease Application Template version
709 -- Business Rules :
710 -- Parameters :
711 -- Version : 1.0
712 -- End of comments
713
714 PROCEDURE get_lat_ver_details (itemtype IN VARCHAR2,
715 itemkey IN VARCHAR2,
716 actid IN NUMBER,
717 funcmode IN VARCHAR2,
718 resultout OUT NOCOPY VARCHAR2) IS
719 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'get_lat_ver_details';
720 BEGIN
721 -- RUN mode
722 IF (funcmode = 'RUN') THEN
723 -- set the attributes required in the workflow
724 get_lat_ver_data(itemtype,itemkey);
725 --set the messages
726 set_messages(itemtype,itemkey,'LAT');
727
728 RETURN;
729 END IF;
730
731 -- CANCEL or TIMEOUT mode
732 IF (funcmode = 'CANCEL' OR funcmode = 'TIMEOUT') THEN
733 resultout := 'COMPLETE:';
734 RETURN;
735 END IF;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 wf_core.context(G_PKG_NAME,
740 l_api_name,
741 itemtype,
742 itemkey,
743 actid,
744 funcmode);
745 RAISE;
746 END get_lat_ver_details;
747
748 -- Get the details of Lease app and set in the workflow attributes
749
750
751 -- Start of comments
752 --
753 -- Procedure Name : get_la_withdraw_details
754 -- Description : Gets the details of the Lease Application details and
755 -- Sets the message for this operation
756 -- Business Rules :
757 -- Parameters :
758 -- Version : 1.0
759 -- End of comments
760 PROCEDURE get_la_withdraw_details (itemtype IN VARCHAR2,
761 itemkey IN VARCHAR2,
762 actid IN NUMBER,
763 funcmode IN VARCHAR2,
764 resultout OUT NOCOPY VARCHAR2) IS
765 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'get_la_withdraw_details';
766
767 l_lease_app_id OKL_LEASE_APPLICATIONS_V.ID%TYPE;
768
769 BEGIN
770 -- RUN mode
771 IF (funcmode = 'RUN') THEN
772 -- set the attributes in workflow
773 get_lease_app_details(itemtype,itemkey);
774
775 --set the messages for Lease Application identified by lookup code 'LAP'
776 set_messages(itemtype,itemkey,'LAP');
777
778 RETURN;
779 END IF;
780
781 -- CANCEL or TIMEOUT mode
782 IF (funcmode = 'CANCEL' OR funcmode = 'TIMEOUT') THEN
783 resultout := 'COMPLETE:';
784 RETURN;
785 END IF;
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 wf_core.context(G_PKG_NAME,
790 l_api_name,
791 itemtype,
792 itemkey,
793 actid,
794 funcmode);
795 RAISE;
796 END get_la_withdraw_details;
797
798
799 -- Start of comments
800 --
801 -- Procedure Name : handle_lat_approval
802 -- Description : Handles the process after the process is approved or rejected
803 -- Business Rules : If Approved, call the API to activate the LAT
804 -- Else change the version status of LAT to NEW
805 -- Parameters :
806 -- Version : 1.0
807 -- End of comments
808
809 PROCEDURE handle_lat_approval (itemtype IN VARCHAR2,
810 itemkey IN VARCHAR2,
811 actid IN NUMBER,
812 funcmode IN VARCHAR2,
813 resultout OUT NOCOPY VARCHAR2) IS
814 l_api_version NUMBER := 1.0;
815 l_api_name CONSTANT VARCHAR2(30) := 'handle_lat_approval';
816 l_msg_count NUMBER;
817 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
818 l_msg_data VARCHAR2(2000);
819 l_return_status VARCHAR2(1);
820
821 l_lat_version_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE;
822
823 l_result VARCHAR2(30);
824 lv_approval_status_ame VARCHAR2(30);
825
826 l_lavv_rec lavv_rec_type;
827 lx_lavv_rec lavv_rec_type;
828
829 CURSOR c_get_ver_dtls ( cp_version_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
830 SELECT LAVV.OBJECT_VERSION_NUMBER
831 FROM OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
832 WHERE LAVV.ID = cp_version_id;
833 BEGIN
834 -- RUN mode
835 IF (funcmode = 'RUN') THEN
836 l_result := wf_engine.GetItemAttrText(itemtype, itemkey, 'RESULT');
837 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
838 itemkey => itemkey,
839 aname => 'APPROVED_YN');
840 -- If the approver has approved
841 IF (l_result = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = 'Y') THEN
842 l_lat_version_id := wf_engine.GetItemAttrText(itemtype,
843 itemkey,
844 'VERSION_ID');
845
846 -- Call API to activate the Lease Application template
847 OKL_LEASEAPP_TEMPLATE_PVT.activate_lat (
848 p_api_version => l_api_version
849 , p_init_msg_list => l_init_msg_list
850 , x_return_status => l_return_status
851 , x_msg_count => l_msg_count
852 , x_msg_data => l_msg_data
853 , p_lat_version_id => l_lat_version_id);
854 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
855 RAISE OKL_API.G_EXCEPTION_ERROR;
856 END IF;
857 ELSE -- if the approver has rejected, change the status of version to NEW
858
859 OPEN c_get_ver_dtls(l_lat_version_id);
860 FETCH c_get_ver_dtls INTO l_lavv_rec.object_version_number ;
861 CLOSE c_get_ver_dtls;
862 l_lavv_rec.id := l_lat_version_id;
863 l_lavv_rec.version_status := G_STATUS_NEW;
864
865 -- call the TAPI insert_row to update lease application template version
866 OKL_LAV_PVT.update_row(p_api_version => l_api_version
867 , p_init_msg_list => l_init_msg_list
868 , x_return_status => l_return_status
869 , x_msg_count => l_msg_count
870 , x_msg_data => l_msg_data
871 , p_lavv_rec => l_lavv_rec
872 , x_lavv_rec => lx_lavv_rec);
873 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
874 RAISE OKL_API.G_EXCEPTION_ERROR;
875 END IF;
876
877 END IF; -- end of check for approval or rejection
878
879 RETURN;
880 END IF;
881
882 -- CANCEL or TIMEOUT mode
883 IF (funcmode = 'CANCEL' OR funcmode = 'TIMEOUT') THEN
884 resultout := 'COMPLETE:';
885 RETURN;
886 END IF;
887
888 EXCEPTION
889 WHEN OTHERS THEN
890 WF_CORE.CONTEXT(G_PKG_NAME,
891 l_api_version,
892 itemtype,
893 itemkey,
894 actid,
895 funcmode);
896 RAISE;
897 END handle_lat_approval;
898
899 -- Start of comments
900 --
901 -- Procedure Name : handle_la_withdraw_approval
902 -- Description : Handles the process after the Lease Application withdrawal
903 -- is approved or rejected by Credit Analyst.
904 -- Business Rules : If Approved, call the API to withdraw the Lease Application
905 -- Else the Lease Application status is not changed.
906 -- Parameters :
907 -- Version : 1.0
908 -- End of comments
909
910 PROCEDURE handle_la_withdraw_approval (itemtype IN VARCHAR2,
911 itemkey IN VARCHAR2,
912 actid IN NUMBER,
913 funcmode IN VARCHAR2,
914 resultout OUT NOCOPY VARCHAR2) IS
915 l_api_version NUMBER := 1.0;
916 l_api_name CONSTANT VARCHAR2(30) := 'handle_la_withdraw_approval';
917 l_msg_count NUMBER;
918 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
919 l_msg_data VARCHAR2(2000);
920 l_return_status VARCHAR2(1);
921
922 lv_approval_status_ame VARCHAR2(30);
923 l_lease_app_id OKL_LEASE_APPLICATIONS_V.ID%TYPE;
924
925 BEGIN
926 -- RUN mode
927 IF (funcmode = 'RUN') THEN
928
929 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
930 itemkey => itemkey,
931 aname => 'APPROVED_YN');
932 -- If the approver has approved
933 IF (lv_approval_status_ame = 'Y') THEN
934 l_lease_app_id := wf_engine.GetItemAttrText(itemtype,
935 itemkey,
936 'LA_ID');
937
938 -- Call API to change the Lease Application status to WITHDRAWN
939 OKL_LEASE_APP_PVT.set_lease_app_status(
940 p_api_version => l_api_version,
941 p_init_msg_list => l_init_msg_list,
942 x_return_status => l_return_status,
943 x_msg_count => l_msg_count,
944 x_msg_data => l_msg_data,
945 p_lap_id => l_lease_app_id,
946 p_lap_status => 'WITHDRAWN');
947 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
948 RAISE OKL_API.G_EXCEPTION_ERROR;
949 END IF;
950 END IF; -- end of check for approval or rejection
951
952 RETURN;
953 END IF;
954
955 -- CANCEL or TIMEOUT mode
956 IF (funcmode = 'CANCEL' OR funcmode = 'TIMEOUT') THEN
957 resultout := 'COMPLETE:';
958 RETURN;
959 END IF;
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 WF_CORE.CONTEXT(G_PKG_NAME,
964 l_api_version,
965 itemtype,
966 itemkey,
967 actid,
968 funcmode);
969 RAISE;
970 END handle_la_withdraw_approval;
971
972 END OKL_LEASE_APP_WF;