[Home] [Help]
PACKAGE BODY: APPS.OKL_FE_WF
Source
1 PACKAGE BODY OKL_FE_WF AS
2 /* $Header: OKLFEWFB.pls 120.3 2010/12/21 03:51:05 rpillay ship $ */
3 -- constants used in the package
4
5 G_MSG_TOKEN_OBJECT_NAME CONSTANT VARCHAR2(20) := 'OBJECT_NAME';
6 G_MSG_TOKEN_NAME CONSTANT VARCHAR2(20) := 'NAME';
7 G_WF_ITM_APPLICATION_ID CONSTANT VARCHAR2(20) := 'APPLICATION_ID';
8 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT VARCHAR2(20) := 'TRX_TYPE_ID';
9 G_FE_APPROVAL_WF CONSTANT VARCHAR2(2) := 'WF';
10 G_FE_APPROVAL_AME CONSTANT VARCHAR2(3) := 'AME';
11 G_WF_ITM_TRANSACTION_ID CONSTANT VARCHAR2(20) := 'TRANSACTION_ID';
12 G_WF_ITM_REQUESTER CONSTANT VARCHAR2(20) := 'REQUESTER';
13 G_WF_ITM_REQUESTER_ID CONSTANT VARCHAR2(20) := 'REQUESTOR_ID';
14 G_WF_ITM_APPROVAL_REQ_MSG CONSTANT VARCHAR2(30) := 'APPROVAL_REQUEST_MESSAGE';
15 G_WF_ITM_PARENT_ITEM_KEY CONSTANT VARCHAR2(20) := 'PARENT_ITEM_KEY';
16 G_WF_ITM_PARENT_ITEM_TYPE CONSTANT VARCHAR2(20) := 'PARENT_ITEM_TYPE';
17 G_WF_ITM_APPROVED_YN CONSTANT VARCHAR2(15) := 'APPROVED_YN';
18 G_WF_ITM_MASTER CONSTANT VARCHAR2(10) := 'MASTER';
19 G_WF_ITM_MESSAGE_SUBJECT CONSTANT VARCHAR2(20) := 'MESSAGE_SUBJECT';
20 G_WF_ITM_APP_REQUEST_SUB CONSTANT VARCHAR2(30) := 'APP_REQUEST_SUB';
21 G_WF_ITM_APP_REMINDER_SUB CONSTANT VARCHAR2(30) := 'APP_REMINDER_SUB';
22 G_WF_ITM_APP_APPROVED_SUB CONSTANT VARCHAR2(30) := 'APP_APPROVED_SUB';
23 G_WF_ITM_APP_REJECTED_SUB CONSTANT VARCHAR2(30) := 'APP_REJECTED_SUB';
24 G_WF_ITM_APP_REMINDER_HEAD CONSTANT VARCHAR2(30) := 'APP_REMINDER_HEAD';
25 G_WF_ITM_APP_APPROVED_HEAD CONSTANT VARCHAR2(30) := 'APP_APPROVED_HEAD';
26 G_WF_ITM_APP_REJECTED_HEAD CONSTANT VARCHAR2(30) := 'APP_REJECTED_HEAD';
27 G_WF_ITM_APPROVER CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVER';
28 G_WF_ITM_MESSAGE_SUBJECT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_SUBJECT';
29 G_WF_ITM_MESSAGE_DESCR CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DESCRIPTION';
30 G_WF_ITM_MESSAGE_BODY CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DOC';
31 G_WF_ITM_RESULT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'RESULT';
32 G_WF_ITM_APPROVED_YN_YES CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVED';
33 G_WF_ITM_APPROVED_YN_NO CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REJECTED';
34
35 --function to return the message from fnd message for the subject of the notifications
36
37 FUNCTION get_message(p_msg_name IN VARCHAR2, object_name IN VARCHAR2,
38 object_value IN VARCHAR2) RETURN VARCHAR2 IS
39 l_message VARCHAR2(100);
40
41 BEGIN
42
43 IF p_msg_name IS NOT NULL THEN
44 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME, NAME => p_msg_name);
45 Fnd_Message.SET_TOKEN(TOKEN => G_MSG_TOKEN_OBJECT_NAME,
46 VALUE => object_name);
47 Fnd_Message.SET_TOKEN(TOKEN => G_MSG_TOKEN_NAME, VALUE => object_value);
48 l_message := fnd_message.get();
49 END IF;
50 RETURN l_message;
51 EXCEPTION
52 WHEN OTHERS THEN
53 RETURN NULL;
54 END get_message;
55
56 -- get the message for a message name frm fnd messages
57
58 FUNCTION get_token(p_msg_name IN VARCHAR2, token_name IN VARCHAR2,
59 token_value IN VARCHAR2) RETURN VARCHAR2 IS
60 l_message VARCHAR2(100);
61
62 BEGIN
63
64 IF p_msg_name IS NOT NULL THEN
65 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME, NAME => p_msg_name);
66 Fnd_Message.SET_TOKEN(TOKEN => token_name, VALUE => token_value);
67 l_message := fnd_message.get();
68 END IF;
69 RETURN l_message;
70 EXCEPTION
71 WHEN OTHERS THEN
72 RETURN NULL;
73 END get_token;
74
75 -- get the message body for Pricing Adjustment Matrix
76
77 FUNCTION get_pam_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
78 l_name VARCHAR2(240);
79 l_currency VARCHAR2(30);
80 l_type VARCHAR2(30);
81 l_version_number VARCHAR2(24);
82 l_effective_from DATE;
83 l_effective_to DATE;
84 lv_message_body VARCHAR2(4000);
85
86 BEGIN
87 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'PAM_NAME');
88 l_version_number := wf_engine.GetItemAttrText(itemtype,
89 itemkey,
90 'VERSION_NUMBER');
91 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
92 itemkey,
93 'EFFECTIVE_FROM');
94 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
95 itemkey,
96 'EFFECTIVE_TO');
97 lv_message_body := '<body>' ||
98 get_token('OKL_NAME', 'NAME', l_name) ||
99 '<br>' ||
100 get_token('OKL_VERSION',
101 'VERSION',
102 l_version_number) ||
103 '<br>' ||
104 get_token('OKL_EFFECTIVE_FROM',
105 'FROM_DATE',
106 fnd_Date.date_to_displaydate(l_effective_from, calendar_aware => FND_DATE.calendar_aware)) ||
107 '<br>' ||
108 get_token('OKL_EFFECTIVE_TO',
109 'TO_DATE',
110 fnd_Date.date_to_displaydate(l_effective_to, calendar_aware => FND_DATE.calendar_aware)) ||
111 '<br>' ||
112 '</body>';
113 RETURN lv_message_body;
114 END get_pam_msg_body;
115
116 -- this method generates the message body
117
118 PROCEDURE get_pam_msg_doc(document_id IN VARCHAR2,
119 display_type IN VARCHAR2,
120 document IN OUT NOCOPY VARCHAR2,
121 document_type IN OUT NOCOPY VARCHAR2) IS
122
123 BEGIN
124
125 -- get the message body
126
127 document := get_pam_msg_body('OKLFEPAM', document_id);
128 document_type := display_type;
129 END get_pam_msg_doc;
130
131 -- get the message body for Standard Rate Template
132
133 FUNCTION get_srt_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
134 l_name VARCHAR2(240);
135 l_currency VARCHAR2(30);
136 l_type VARCHAR2(30);
137 l_version_number VARCHAR2(24);
138 l_effective_from DATE;
139 l_effective_to DATE;
140 lv_message_body VARCHAR2(4000);
141
142 BEGIN
143 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'SRT_NAME');
144 l_version_number := wf_engine.GetItemAttrText(itemtype,
145 itemkey,
146 'VERSION_NUMBER');
147 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
148 itemkey,
149 'EFFECTIVE_FROM');
150 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
151 itemkey,
152 'EFFECTIVE_TO');
153 lv_message_body := '<body>' ||
154 get_token('OKL_NAME', 'NAME', l_name) ||
155 '<br>' ||
156 get_token('OKL_VERSION',
157 'VERSION',
158 l_version_number) ||
159 '<br>' ||
160 get_token('OKL_EFFECTIVE_FROM',
161 'FROM_DATE',
162 fnd_Date.date_to_displaydate(l_effective_from, calendar_aware => FND_DATE.calendar_aware)) ||
163 '<br>' ||
164 get_token('OKL_EFFECTIVE_TO',
165 'TO_DATE',
166 fnd_Date.date_to_displaydate(l_effective_to, calendar_aware => FND_DATE.calendar_aware)) ||
167 '<br>' ||
168 '</body>';
169 RETURN lv_message_body;
170 END get_srt_msg_body;
171
172 -- this method generates the message body
173
174 PROCEDURE get_srt_msg_doc(document_id IN VARCHAR2,
175 display_type IN VARCHAR2,
176 document IN OUT NOCOPY VARCHAR2,
177 document_type IN OUT NOCOPY VARCHAR2) IS
178
179 BEGIN
180
181 -- get the message body
182
183 document := get_srt_msg_body('OKLFESRT', document_id);
184 document_type := display_type;
185 END get_srt_msg_doc;
186
187 -- get the message body for End of Term Options
188
189 FUNCTION get_eot_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
190 l_name VARCHAR2(240);
191 l_currency VARCHAR2(30);
192 l_type VARCHAR2(30);
193 l_version_number VARCHAR2(24);
194 l_effective_from DATE;
195 l_effective_to DATE;
196 lv_message_body VARCHAR2(4000);
197
198 BEGIN
199 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'EOT_NAME');
200 l_version_number := wf_engine.GetItemAttrText(itemtype,
201 itemkey,
202 'VERSION_NUMBER');
203 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
204 itemkey,
205 'EFFECTIVE_FROM');
206 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
207 itemkey,
208 'EFFECTIVE_TO');
209 lv_message_body := '<body>' ||
210 get_token('OKL_NAME', 'NAME', l_name) ||
211 '<br>' ||
212 get_token('OKL_VERSION',
213 'VERSION',
214 l_version_number) ||
215 '<br>' ||
216 get_token('OKL_EFFECTIVE_FROM',
217 'FROM_DATE',
218 fnd_Date.date_to_displaydate(l_effective_from, calendar_aware => FND_DATE.calendar_aware)) ||
219 '<br>' ||
220 get_token('OKL_EFFECTIVE_TO',
221 'TO_DATE',
222 fnd_Date.date_to_displaydate(l_effective_to, calendar_aware => FND_DATE.calendar_aware)) ||
223 '<br>' ||
224 '</body>';
225 RETURN lv_message_body;
226 END get_eot_msg_body;
227
228 -- this method generates the message body
229
230 PROCEDURE get_eot_msg_doc(document_id IN VARCHAR2,
231 display_type IN VARCHAR2,
232 document IN OUT NOCOPY VARCHAR2,
233 document_type IN OUT NOCOPY VARCHAR2) IS
234
235 BEGIN
236
237 -- get the message body
238
239 document := get_eot_msg_body('OKLFEEOT', document_id);
240 document_type := display_type;
241 END get_eot_msg_doc;
242
243 -- get the message body for Item Residuals
244
245 FUNCTION get_irs_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
246 l_name VARCHAR2(240);
247 l_currency VARCHAR2(30);
248 l_type VARCHAR2(30);
249 l_version_number VARCHAR2(24);
250 l_effective_from DATE;
251 l_effective_to DATE;
252 lv_message_body VARCHAR2(4000);
253
254 BEGIN
255 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'IRS_NAME');
256 l_version_number := wf_engine.GetItemAttrText(itemtype,
257 itemkey,
258 'VERSION_NUMBER');
259 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
260 itemkey,
261 'EFFECTIVE_FROM');
262 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
263 itemkey,
264 'EFFECTIVE_TO');
265 lv_message_body := '<body>' ||
266 get_token('OKL_NAME', 'NAME', l_name) ||
267 '<br>' ||
268 get_token('OKL_VERSION',
269 'VERSION',
270 l_version_number) ||
271 '<br>' ||
272 get_token('OKL_EFFECTIVE_FROM',
273 'FROM_DATE',
274 fnd_Date.date_to_displaydate(l_effective_from, calendar_aware => FND_DATE.calendar_aware)) ||
275 '<br>' ||
276 get_token('OKL_EFFECTIVE_TO',
277 'TO_DATE',
278 fnd_Date.date_to_displaydate(l_effective_to, calendar_aware => FND_DATE.calendar_aware)) ||
279 '<br>' ||
280 '</body>';
281 RETURN lv_message_body;
282 END get_irs_msg_body;
283
284 -- this method generates the message body
285
286 PROCEDURE get_irs_msg_doc(document_id IN VARCHAR2,
287 display_type IN VARCHAR2,
288 document IN OUT NOCOPY VARCHAR2,
289 document_type IN OUT NOCOPY VARCHAR2) IS
290
291 BEGIN
292
293 -- get the message body
294
295 document := get_irs_msg_body('OKLFEIRS', document_id);
296 document_type := display_type;
297 END get_irs_msg_doc;
298
299 -- get the message body for Lease Rate Sets
300
301 FUNCTION get_lrs_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
302 l_name VARCHAR2(240);
303 l_currency VARCHAR2(30);
304 l_type VARCHAR2(30);
305 l_version_number VARCHAR2(24);
306 l_effective_from DATE;
307 l_effective_to DATE;
308 lv_message_body VARCHAR2(4000);
309
310 BEGIN
311 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LRS_NAME');
312 l_version_number := wf_engine.GetItemAttrText(itemtype,
313 itemkey,
314 'VERSION_NUMBER');
315 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
316 itemkey,
317 'EFFECTIVE_FROM');
318 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
319 itemkey,
320 'EFFECTIVE_TO');
321 lv_message_body := '<body>' ||
322 get_token('OKL_NAME', 'NAME', l_name) ||
323 '<br>' ||
324 get_token('OKL_VERSION',
325 'VERSION',
326 l_version_number) ||
327 '<br>' ||
328 get_token('OKL_EFFECTIVE_FROM',
329 'FROM_DATE',
330 fnd_Date.date_to_displaydate(l_effective_from, calendar_aware => FND_DATE.calendar_aware)) ||
331 '<br>' ||
332 get_token('OKL_EFFECTIVE_TO',
333 'TO_DATE',
334 fnd_Date.date_to_displaydate(l_effective_to, calendar_aware => FND_DATE.calendar_aware)) ||
335 '<br>' ||
336 '</body>';
337 RETURN lv_message_body;
338 END get_lrs_msg_body;
339
340 -- this method generates the message body
341
342 PROCEDURE get_lrs_msg_doc(document_id IN VARCHAR2,
343 display_type IN VARCHAR2,
344 document IN OUT NOCOPY VARCHAR2,
345 document_type IN OUT NOCOPY VARCHAR2) IS
346
347 BEGIN
348
349 -- get the message body
350
351 document := get_lrs_msg_body('OKLFELRS', document_id);
352 document_type := display_type;
353 END get_lrs_msg_doc;
354
355 -- method to set the messages and the message desciption
356
357 PROCEDURE set_messages(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
358 object_type IN VARCHAR2) IS
359 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'set_messages';
360 l_name VARCHAR2(100);
361 l_object_name VARCHAR2(50);
362 l_request_message VARCHAR2(500);
363 l_approved_message VARCHAR2(500);
364 l_rejected_message VARCHAR2(500);
365 l_reminder_message VARCHAR2(500);
366 x_msg_count NUMBER;
367 x_msg_data VARCHAR2(32767);
368 x_return_status VARCHAR2(1);
369 l_api_version NUMBER := 1.0;
370 p_api_version NUMBER := 1.0;
371
372 CURSOR obj_name_csr(object_type IN VARCHAR2) IS
373 SELECT MEANING
374 FROM FND_LOOKUPS
375 WHERE LOOKUP_TYPE = 'OKL_PRICING_OBJECTS' AND LOOKUP_CODE = object_type;
376
377 BEGIN
378 x_return_status := okl_api.start_activity(l_api_name,
379 g_pkg_name,
380 'T',
381 l_api_version,
382 p_api_version,
383 '_PVT',
384 x_return_status);
385
386 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
387 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
388 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
389 RAISE OKL_API.G_EXCEPTION_ERROR;
390 END IF;
391
392 -- RUN mode
393
394 OPEN obj_name_csr(object_type);
395 FETCH obj_name_csr INTO l_object_name ;
396 CLOSE obj_name_csr;
397
398 -- according to Object Type, the message is set
399
400 IF (object_type = 'PAM') THEN
401 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'PAM_NAME');
402 wf_engine.SetItemAttrDocument(itemtype => itemtype,
403 itemkey => itemkey,
404 aname => G_WF_ITM_MESSAGE_BODY,
405 documentid => 'plsql:okl_fe_wf.get_pam_msg_doc/' ||
406 itemkey);
407 wf_engine.SetItemAttrText(itemtype => itemtype,
408 itemkey => itemkey,
409 aname => G_WF_ITM_MESSAGE_DESCR,
410 avalue => get_pam_msg_body(itemtype,
411 itemkey));
412 ELSIF (object_type = 'SRT') THEN
413 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'SRT_NAME');
414 wf_engine.SetItemAttrText(itemtype => itemtype,
415 itemkey => itemkey,
416 aname => G_WF_ITM_MESSAGE_BODY,
417 avalue => 'plsql:okl_fe_wf.get_srt_msg_doc/' ||
418 itemkey);
419 wf_engine.SetItemAttrText(itemtype => itemtype,
420 itemkey => itemkey,
421 aname => G_WF_ITM_MESSAGE_DESCR,
422 avalue => get_srt_msg_body(itemtype,
423 itemkey));
424 ELSIF (object_type = 'LRS') THEN
425 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LRS_NAME');
426 wf_engine.SetItemAttrText(itemtype => itemtype,
427 itemkey => itemkey,
428 aname => G_WF_ITM_MESSAGE_BODY,
429 avalue => 'plsql:okl_fe_wf.get_lrs_msg_doc/' ||
430 itemkey);
431 wf_engine.SetItemAttrText(itemtype => itemtype,
432 itemkey => itemkey,
433 aname => G_WF_ITM_MESSAGE_DESCR,
434 avalue => get_lrs_msg_body(itemtype,
435 itemkey));
436 ELSIF (object_type = 'EOT') THEN
437 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'EOT_NAME');
438 wf_engine.SetItemAttrText(itemtype => itemtype,
439 itemkey => itemkey,
440 aname => G_WF_ITM_MESSAGE_BODY,
441 avalue => 'plsql:okl_fe_wf.get_eot_msg_doc/' ||
442 itemkey);
443 wf_engine.SetItemAttrText(itemtype => itemtype,
444 itemkey => itemkey,
445 aname => G_WF_ITM_MESSAGE_DESCR,
446 avalue => get_eot_msg_body(itemtype,
447 itemkey));
448 ELSIF (object_type = 'IRS') THEN
449 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'IRS_NAME');
450 wf_engine.SetItemAttrText(itemtype => itemtype,
451 itemkey => itemkey,
452 aname => G_WF_ITM_MESSAGE_BODY,
453 avalue => 'plsql:okl_fe_wf.get_irs_msg_doc/' ||
454 itemkey);
455 wf_engine.SetItemAttrText(itemtype => itemtype,
456 itemkey => itemkey,
457 aname => G_WF_ITM_MESSAGE_DESCR,
458 avalue => get_irs_msg_body(itemtype,
459 itemkey));
460 END IF;
461
462 -- set the messages of the notification
463
464 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
465 l_object_name,
466 l_name);
467 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
468 l_object_name,
469 l_name);
470 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
471 l_object_name,
472 l_name);
473 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
474 l_object_name,
475 l_name);
476 wf_engine.SetItemAttrText(itemtype,
477 itemkey,
478 G_WF_ITM_APP_REQUEST_SUB,
479 l_request_message);
480 wf_engine.SetItemAttrText(itemtype,
481 itemkey,
482 G_WF_ITM_APP_REMINDER_SUB,
483 l_reminder_message);
484 wf_engine.SetItemAttrText(itemtype,
485 itemkey,
486 G_WF_ITM_APP_REMINDER_HEAD,
487 l_reminder_message);
488 wf_engine.SetItemAttrText(itemtype,
489 itemkey,
490 G_WF_ITM_APP_APPROVED_SUB,
491 l_approved_message);
492 wf_engine.SetItemAttrText(itemtype,
493 itemkey,
494 G_WF_ITM_APP_APPROVED_HEAD,
495 l_approved_message);
496 wf_engine.SetItemAttrText(itemtype,
497 itemkey,
498 G_WF_ITM_APP_REJECTED_SUB,
499 l_rejected_message);
500 wf_engine.SetItemAttrText(itemtype,
501 itemkey,
502 G_WF_ITM_APP_REJECTED_HEAD,
503 l_rejected_message);
504 EXCEPTION
505
506 -- handle the exceptions
507
508 WHEN OTHERS THEN
509 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
510 G_PKG_NAME,
511 'OTHERS',
512 x_msg_count,
513 x_msg_data,
514 '_PVT');
515 RAISE;
516 END set_messages;
517
518 -- procedure to check the approval process
519
520 PROCEDURE check_approval_process(itemtype IN VARCHAR2,
521 itemkey IN VARCHAR2, actid IN NUMBER,
522 funcmode IN VARCHAR2,
523 resultout OUT NOCOPY VARCHAR2) IS
524 l_approval_option VARCHAR2(10);
525 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'check_approval_process';
526
527 BEGIN
528
529 IF (funcmode = 'RUN') THEN
530
531 -- get the profile option
532
533 l_approval_option := fnd_profile.value('OKL_PE_APPROVAL_PROCESS');
534
535 -- depending on the profile option, take the workflow branch or the AME branch
536
537 IF l_approval_option = G_FE_APPROVAL_AME THEN
538 resultout := 'COMPLETE:AME';
539 ELSIF l_approval_option = G_FE_APPROVAL_WF THEN
540 resultout := 'COMPLETE:WF';
541 END IF;
542 RETURN;
543 END IF;
544
545 -- CANCEL mode
546
547 IF (funcmode = 'CANCEL') THEN
548 resultout := 'COMPLETE:';
549 RETURN;
550 END IF;
551
552 -- TIMEOUT mode
553
554 IF (funcmode = 'TIMEOUT') THEN
555 resultout := 'COMPLETE:';
556 RETURN;
557 END IF;
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 wf_core.context(G_PKG_NAME,
562 l_api_name,
563 itemtype,
564 itemkey,
565 actid,
566 funcmode);
567 RAISE;
568 END check_approval_process;
569
570 -- get the Standard Rate Template Data
571
572 PROCEDURE get_std_rate_tmpl_data(itemtype IN varchar2, itemkey IN varchar2) IS
573 l_srt_id NUMBER;
574 l_version_id NUMBER;
575 l_user_name VARCHAR2(240);
576 x_return_status VARCHAR2(1);
577 l_api_name VARCHAR2(40) := 'get_std_rate_tmpl_data';
578 x_msg_count NUMBER;
579 x_msg_data VARCHAR2(32767);
580 l_application_id fnd_application.application_id%TYPE;
581 l_api_version NUMBER := 1.0;
582 p_api_version NUMBER := 1.0;
583
584 CURSOR get_srt_attributes(p_srt_version_id NUMBER) IS
585 SELECT STD_RATE_TMPL_VER_ID,
586 OBJECT_VERSION_NUMBER,
587 VERSION_NUMBER,
588 STD_RATE_TMPL_ID,
589 EFFECTIVE_FROM_DATE,
590 EFFECTIVE_TO_DATE,
591 STS_CODE,
592 ADJ_MAT_VERSION_ID,
593 SRT_RATE,
594 SPREAD,
595 DAY_CONVENTION_CODE,
596 MIN_ADJ_RATE,
597 MAX_ADJ_RATE,
598 CURRENCY_CODE,
599 RATE_CARD_YN,
600 PRICING_ENGINE_CODE,
601 ORIG_STD_RATE_TMPL_ID,
602 RATE_TYPE_CODE,
603 FREQUENCY_CODE,
604 INDEX_ID,
605 HDR_STS_CODE,
606 HDR_EFFECTIVE_FROM_DATE,
607 HDR_EFFECTIVE_TO_DATE,
608 HDR_SRT_RATE,
609 TEMPLATE_NAME,
610 TEMPLATE_DESC
611 FROM OKL_FE_STD_RT_TMP_VERS_V
612 WHERE STD_RATE_TMPL_VER_ID = p_srt_version_id;
613
614 CURSOR fnd_user_csr IS
615 SELECT USER_NAME
616 FROM FND_USER
617 WHERE USER_ID = fnd_global.user_id;
618
619 -- Get the valid application id from FND
620
621 CURSOR c_get_app_id_csr IS
622 SELECT APPLICATION_ID
623 FROM FND_APPLICATION
624 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
625
626 BEGIN
627 x_return_status := okl_api.start_activity(l_api_name,
628 g_pkg_name,
629 'T',
630 l_api_version,
631 p_api_version,
632 '_PVT',
633 x_return_status);
634
635 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
636 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
637 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
638 RAISE OKL_API.G_EXCEPTION_ERROR;
639 END IF;
640
641 -- get the value of the version id from the workflow
642
643 l_version_id := wf_engine.GetItemAttrText(itemtype,
644 itemkey,
645 'VERSION_ID');
646
647 -- set the values of the attributes from the values of the cursor
648
649 FOR l_srt_rec IN get_srt_attributes(l_version_id)
650 LOOP
651 wf_engine.SetItemAttrText(itemtype,
652 itemkey,
653 'SRT_NAME',
654 l_srt_rec.TEMPLATE_NAME);
655 wf_engine.SetItemAttrText(itemtype,
656 itemkey,
657 'DESCRIPTION',
658 l_srt_rec.TEMPLATE_DESC);
659 wf_engine.SetItemAttrText(itemtype,
660 itemkey,
661 'SRT_TYPE',
662 l_srt_rec.RATE_TYPE_CODE);
663 wf_engine.SetItemAttrText(itemtype,
664 itemkey,
665 'VERSION_NUMBER',
666 l_srt_rec.VERSION_NUMBER);
667 wf_engine.SetItemAttrDate(itemtype,
668 itemkey,
669 'EFFECTIVE_FROM',
670 l_srt_rec.EFFECTIVE_FROM_DATE);
671 wf_engine.SetItemAttrDate(itemtype,
672 itemkey,
673 'EFFECTIVE_TO',
674 l_srt_rec.EFFECTIVE_TO_DATE);
675 END LOOP;
676 OPEN fnd_user_csr;
677 FETCH fnd_user_csr INTO l_user_name ;
678 CLOSE fnd_user_csr;
679
680 -- get the application id
681
682 OPEN c_get_app_id_csr;
683 FETCH c_get_app_id_csr INTO l_application_id ;
684 CLOSE c_get_app_id_csr;
685
686 -- set the values of the approver and the requestor
687
688 wf_engine.SetItemAttrText(itemtype,
689 itemkey,
690 G_WF_ITM_APPROVER,
691 l_user_name);
692 wf_engine.SetItemAttrText(itemtype,
693 itemkey,
694 G_WF_ITM_REQUESTER,
695 l_user_name);
696 wf_engine.SetItemAttrText(itemtype,
697 itemkey,
698 G_WF_ITM_REQUESTER_ID,
699 fnd_global.user_id);
700 wf_engine.SetItemAttrText(itemtype,
701 itemkey,
702 G_WF_ITM_TRANSACTION_TYPE_ID,
703 itemtype);
704 wf_engine.SetItemAttrText(itemtype,
705 itemkey,
706 G_WF_ITM_TRANSACTION_ID,
707 l_version_id);
708 wf_engine.SetItemAttrText(itemtype,
709 itemkey,
710 G_WF_ITM_APPLICATION_ID,
711 l_application_id);
712 EXCEPTION
713 WHEN OTHERS THEN
714 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
715 G_PKG_NAME,
716 'OTHERS',
717 x_msg_count,
718 x_msg_data,
719 '_PVT');
720 RAISE;
721 END get_std_rate_tmpl_data;
722
723 -- hdnle the approval of Standard Rate Template
724
725 PROCEDURE handle_srt_approval(itemtype IN varchar2, itemkey IN varchar2,
726 actid IN number, funcmode IN varchar2,
727 resultout OUT NOCOPY varchar2) IS
728 l_srt_id NUMBER;
729 l_api_version NUMBER := 1.0;
730 l_api_name VARCHAR2(40) := 'handle_srt_approval';
731 p_init_msg_list VARCHAR2(1) := 'T';
732 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
733 lx_msg_count NUMBER;
734 l_result VARCHAR2(30);
735 lv_approval_status_ame VARCHAR2(30);
736 lx_msg_data VARCHAR2(32767);
737 lx_return_status VARCHAR2(1);
738 l_srv_rec okl_srv_rec;
739 x_srv_rec okl_srv_rec;
740
741 BEGIN
742
743 IF (funcmode = 'RUN') THEN
744 l_result := wf_engine.GetItemAttrText(itemtype, itemkey, 'RESULT');
745 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
749 -- if approved, then change the status
746 itemkey => itemkey,
747 aname => 'APPROVED_YN');
748
750
751 IF (l_result = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = 'Y') THEN
752 l_srt_id := wf_engine.GetItemAttrText(itemtype,
753 itemkey,
754 'VERSION_ID');
755
756 -- change the version and header status and end date referenced objects
757
758 okl_fe_std_rate_tmpl_pvt.handle_approval(1,
759 'T',
760 lx_return_status,
761 lx_msg_count,
762 lx_msg_data,
763 l_srt_id);
764 IF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
765 RAISE OKL_API.G_EXCEPTION_ERROR;
766 ELSIF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
767 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
768 END IF;
769 ELSE
770 l_srt_id := wf_engine.GetItemAttrText(itemtype,
771 itemkey,
772 'VERSION_ID');
773
774 -- populate the version attributes
775
776 l_srv_rec.adj_mat_version_id := l_srt_id;
777 l_srv_rec.sts_code := 'NEW';
778
779 -- change the version status back to new
780
781 okl_srv_pvt.update_row(l_api_version,
782 p_init_msg_list,
783 x_return_status,
784 lx_msg_count,
785 lx_msg_data,
786 l_srv_rec,
787 x_srv_rec);
788 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
789 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
790 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
791 RAISE OKL_API.G_EXCEPTION_ERROR;
792 END IF;
793 END IF;
794 resultout := 'COMPLETE';
795 RETURN;
796 END IF;
797
798 EXCEPTION
799 WHEN OTHERS THEN
800 WF_CORE.CONTEXT('okl_fe_wf',
801 'handle_srt_approval',
802 itemtype,
803 itemkey,
804 actid,
805 funcmode);
806 RAISE;
807 END handle_srt_approval;
808
809 -- get the adjustment matrix data
810
811 PROCEDURE get_adj_matrix_get_data(itemtype IN varchar2, itemkey IN varchar2) IS
812 l_adj_mat_version_id NUMBER;
813 l_user_name VARCHAR2(240);
814 x_return_status VARCHAR2(1);
815 l_api_name VARCHAR2(40) := 'get_adj_matrix_get_data';
816 x_msg_count NUMBER;
817 x_msg_data VARCHAR2(32767);
818 l_application_id fnd_application.application_id%TYPE;
819 l_api_version NUMBER := 1.0;
820 p_api_version NUMBEr := 1.0;
821
822 CURSOR get_adj_mat_attr(p_adj_mat_ver_id NUMBER) IS
823 SELECT ADJ_MAT_VERSION_ID,
824 OBJECT_VERSION_NUMBER,
825 VERSION_NUMBER,
826 ADJ_MAT_ID,
827 STS_CODE,
828 EFFECTIVE_FROM_DATE,
829 EFFECTIVE_TO_DATE,
830 CURRENCY_CODE,
831 ADJ_MAT_TYPE_CODE,
832 ORIG_ADJ_MAT_ID,
833 HDR_STS_CODE,
834 HDR_EFFECTIVE_FROM_DATE,
835 HDR_EFFECTIVE_TO_DATE,
836 ADJ_MAT_NAME,
837 ADJ_MAT_DESC
838 FROM OKL_FE_ADJ_MAT_VERS_V
839 WHERE ADJ_MAT_VERSION_ID = p_adj_mat_ver_id;
840
841 CURSOR fnd_user_csr IS
842 SELECT USER_NAME
843 FROM FND_USER
844 WHERE USER_ID = fnd_global.user_id;
845
846 -- Get the valid application id from FND
847
848 CURSOR c_get_app_id_csr IS
849 SELECT APPLICATION_ID
850 FROM FND_APPLICATION
851 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
852
853 BEGIN
854 x_return_status := okl_api.start_activity(l_api_name,
855 g_pkg_name,
856 'T',
857 l_api_version,
858 p_api_version,
859 '_PVT',
860 x_return_status);
861
862 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
864 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
865 RAISE OKL_API.G_EXCEPTION_ERROR;
866 END IF;
867
868 -- get the value of the version id from the workflow
869
870 l_adj_mat_version_id := wf_engine.GetItemAttrText(itemtype,
871 itemkey,
872 'VERSION_ID');
873
874 -- set the values of the attributes from the values of the cursor
875
876 FOR l_adj_mat_rec IN get_adj_mat_attr(l_adj_mat_version_id)
877 LOOP
878 wf_engine.SetItemAttrText(itemtype,
879 itemkey,
880 'PAM_NAME',
881 l_adj_mat_rec.ADJ_MAT_NAME);
882 wf_engine.SetItemAttrText(itemtype,
883 itemkey,
884 'CURRENCY',
885 l_adj_mat_rec.CURRENCY_CODE);
886 wf_engine.SetItemAttrText(itemtype,
887 itemkey,
888 'VERSION_NUMBER',
889 l_adj_mat_rec.VERSION_NUMBER);
890 wf_engine.SetItemAttrDate(itemtype,
891 itemkey,
892 'EFFECTIVE_FROM',
893 l_adj_mat_rec.EFFECTIVE_FROM_DATE);
894 wf_engine.SetItemAttrDate(itemtype,
895 itemkey,
896 'EFFECTIVE_TO',
897 l_adj_mat_rec.EFFECTIVE_TO_DATE);
898 END LOOP;
899 OPEN fnd_user_csr;
900 FETCH fnd_user_csr INTO l_user_name ;
901 CLOSE fnd_user_csr;
902
903 -- get the application id
904
905 OPEN c_get_app_id_csr;
906 FETCH c_get_app_id_csr INTO l_application_id ;
907 CLOSE c_get_app_id_csr;
908
909 -- set the values of the approver and the requestor
910
911 wf_engine.SetItemAttrText(itemtype,
912 itemkey,
913 G_WF_ITM_APPROVER,
914 l_user_name);
915 wf_engine.SetItemAttrText(itemtype,
916 itemkey,
917 G_WF_ITM_REQUESTER,
918 l_user_name);
919 wf_engine.SetItemAttrText(itemtype,
920 itemkey,
921 G_WF_ITM_REQUESTER_ID,
922 fnd_global.user_id);
923 wf_engine.SetItemAttrText(itemtype,
924 itemkey,
925 G_WF_ITM_TRANSACTION_TYPE_ID,
926 itemtype);
927 wf_engine.SetItemAttrText(itemtype,
928 itemkey,
929 G_WF_ITM_TRANSACTION_ID,
930 l_adj_mat_version_id);
931 wf_engine.SetItemAttrText(itemtype,
932 itemkey,
933 G_WF_ITM_APPLICATION_ID,
934 l_application_id);
935 EXCEPTION
936 WHEN OTHERS THEN
937 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
938 G_PKG_NAME,
939 'OTHERS',
940 x_msg_count,
941 x_msg_data,
942 '_PVT');
943 RAISE;
944 END get_adj_matrix_get_data;
945
946 -- handle Pricing Adjustment Matrix Approval
947
948 PROCEDURE handle_pam_approval(itemtype IN varchar2, itemkey IN varchar2,
949 actid IN number, funcmode IN varchar2,
950 resultout OUT NOCOPY varchar2) IS
951 l_api_version NUMBER := 1.0;
952 l_api_name VARCHAR2(40) := 'handle_pam_approval';
953 p_init_msg_list VARCHAR2(1) := 'T';
954 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
955 l_pam_id NUMBER;
956 lx_msg_count NUMBER;
957 l_result VARCHAR2(30);
958 lv_approval_status_ame VARCHAR2(30);
959 lx_msg_data VARCHAR2(32767);
960 lx_return_status VARCHAR2(1);
961 l_pal_rec okl_pal_rec;
962 x_pal_rec okl_pal_rec;
963
964 BEGIN
965
966 IF (funcmode = 'RUN') THEN
967 l_result := wf_engine.GetItemAttrText(itemtype, itemkey, 'RESULT');
968 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
969 itemkey => itemkey,
970 aname => 'APPROVED_YN');
971 IF (l_result = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = 'Y') THEN
972 l_pam_id := wf_engine.GetItemAttrText(itemtype,
973 itemkey,
974 'VERSION_ID');
975
976 -- change the version and header status and end date referenced objects
977
978 okl_fe_adj_matrix_pvt.handle_approval(1,
979 'T',
980 lx_return_status,
981 lx_msg_count,
982 lx_msg_data,
983 l_pam_id);
984 IF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
985 RAISE OKL_API.G_EXCEPTION_ERROR;
986 ELSIF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
987 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
988 END IF;
989 ELSE
990 l_pam_id := wf_engine.GetItemAttrText(itemtype,
991 itemkey,
992 'VERSION_ID');
993
994 -- populate the version attributes
995
996 l_pal_rec.adj_mat_version_id := l_pam_id;
997 l_pal_rec.sts_code := 'NEW';
998
999 -- change the version status back to new
1000
1001 okl_pal_pvt.update_row(l_api_version,
1002 p_init_msg_list,
1003 x_return_status,
1004 lx_msg_count,
1005 lx_msg_data,
1006 l_pal_rec,
1007 x_pal_rec);
1008 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1009 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1010 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1011 RAISE OKL_API.G_EXCEPTION_ERROR;
1012 END IF;
1013 END IF;
1014 resultout := 'COMPLETE';
1015 RETURN;
1016 END IF;
1017
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 WF_CORE.CONTEXT('okl_fe_wf',
1021 'handle_pam_approval',
1022 itemtype,
1023 itemkey,
1024 actid,
1025 funcmode);
1026 RAISE;
1027 END handle_pam_approval;
1028
1029 -- get end of Term Options Data
1030
1031 PROCEDURE get_end_of_term_data(itemtype IN varchar2, itemkey IN varchar2) IS
1032 l_eot_version_id NUMBER;
1033 l_user_name VARCHAR2(240);
1034 x_return_status VARCHAR2(1);
1035 l_api_name VARCHAR2(40) := 'get_end_of_term_data';
1036 x_msg_count NUMBER;
1037 x_msg_data VARCHAR2(32767);
1038 l_application_id fnd_application.application_id%TYPE;
1039 l_api_version NUMBER := 1.0;
1040 p_api_version NUMBER := 1.0;
1041
1042 CURSOR get_end_of_term_attr(p_eot_version_id NUMBER) IS
1043 SELECT END_OF_TERM_VER_ID,
1044 OBJECT_VERSION_NUMBER,
1045 VERSION_NUMBER,
1046 END_OF_TERM_ID,
1047 STS_CODE,
1048 EFFECTIVE_FROM_DATE,
1049 EFFECTIVE_TO_DATE,
1050 CURRENCY_CODE,
1051 EOT_TYPE_CODE,
1052 PRODUCT_ID,
1053 CATEGORY_TYPE_CODE,
1054 ORIG_END_OF_TERM_ID,
1055 HDR_STS_CODE,
1056 HDR_EFFECTIVE_FROM_DATE,
1057 HDR_EFFECTIVE_TO_DATE,
1058 END_OF_TERM_NAME,
1059 END_OF_TERM_DESC
1060 FROM OKL_FE_EO_TERM_VERS_V
1061 WHERE END_OF_TERM_VER_ID = p_eot_version_id;
1062
1063 -- find the user
1064
1065 CURSOR fnd_user_csr IS
1066 SELECT USER_NAME
1067 FROM FND_USER
1068 WHERE USER_ID = fnd_global.user_id;
1069
1070 -- Get the valid application id from FND
1071
1072 CURSOR c_get_app_id_csr IS
1073 SELECT APPLICATION_ID
1074 FROM FND_APPLICATION
1075 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
1076
1077 BEGIN
1078 x_return_status := okl_api.start_activity(l_api_name,
1079 g_pkg_name,
1080 'T',
1081 l_api_version,
1082 p_api_version,
1083 '_PVT',
1084 x_return_status);
1085
1086 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1087 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1088 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1089 RAISE OKL_API.G_EXCEPTION_ERROR;
1090 END IF;
1091
1092 -- get the value of the version id from the workflow
1093
1094 l_eot_version_id := wf_engine.GetItemAttrText(itemtype,
1095 itemkey,
1096 'VERSION_ID');
1097
1098 -- set the values of the attributes from the values of the cursor
1099
1100 FOR l_eot_val_rec IN get_end_of_term_attr(l_eot_version_id)
1101 LOOP
1102 wf_engine.SetItemAttrText(itemtype,
1103 itemkey,
1104 'EOT_NAME',
1105 l_eot_val_rec.END_OF_TERM_NAME);
1106 wf_engine.SetItemAttrText(itemtype,
1107 itemkey,
1108 'DESCRIPTION',
1109 l_eot_val_rec.END_OF_TERM_DESC);
1110 wf_engine.SetItemAttrText(itemtype,
1111 itemkey,
1112 'SOURCE',
1113 l_eot_val_rec.CATEGORY_TYPE_CODE);
1114 wf_engine.SetItemAttrText(itemtype,
1115 itemkey,
1116 'VALUE_TYPE',
1117 l_eot_val_rec.CATEGORY_TYPE_CODE);
1118 wf_engine.SetItemAttrText(itemtype,
1119 itemkey,
1120 'CURRENCY',
1121 l_eot_val_rec.CURRENCY_CODE);
1122 wf_engine.SetItemAttrText(itemtype,
1123 itemkey,
1124 'VERSION_NUMBER',
1125 l_eot_val_rec.VERSION_NUMBER);
1126 wf_engine.SetItemAttrDate(itemtype,
1127 itemkey,
1128 'EFFECTIVE_FROM',
1129 l_eot_val_rec.EFFECTIVE_FROM_DATE);
1130 wf_engine.SetItemAttrDate(itemtype,
1131 itemkey,
1132 'EFFECTIVE_TO',
1133 l_eot_val_rec.EFFECTIVE_TO_DATE);
1134 END LOOP;
1135 OPEN fnd_user_csr;
1136 FETCH fnd_user_csr INTO l_user_name ;
1137 CLOSE fnd_user_csr;
1138
1139 -- get the application id
1140
1141 OPEN c_get_app_id_csr;
1142 FETCH c_get_app_id_csr INTO l_application_id ;
1143 CLOSE c_get_app_id_csr;
1144
1145 -- set the values of the approver and the requestor
1146
1147 wf_engine.SetItemAttrText(itemtype,
1148 itemkey,
1149 G_WF_ITM_APPROVER,
1150 l_user_name);
1151 wf_engine.SetItemAttrText(itemtype,
1152 itemkey,
1153 G_WF_ITM_REQUESTER,
1154 l_user_name);
1155 wf_engine.SetItemAttrText(itemtype,
1156 itemkey,
1157 G_WF_ITM_REQUESTER_ID,
1158 fnd_global.user_id);
1159 wf_engine.SetItemAttrText(itemtype,
1160 itemkey,
1161 G_WF_ITM_TRANSACTION_TYPE_ID,
1162 itemtype);
1163 wf_engine.SetItemAttrText(itemtype,
1164 itemkey,
1165 G_WF_ITM_TRANSACTION_ID,
1166 l_eot_version_id);
1167 wf_engine.SetItemAttrText(itemtype,
1168 itemkey,
1169 G_WF_ITM_APPLICATION_ID,
1170 l_application_id);
1171 EXCEPTION
1172 WHEN OTHERS THEN
1173 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
1174 G_PKG_NAME,
1175 'OTHERS',
1176 x_msg_count,
1177 x_msg_data,
1178 '_PVT');
1179 RAISE;
1180 END get_end_of_term_data;
1181
1182 -- Handle end of term approval
1183
1184 PROCEDURE handle_eot_approval(itemtype IN varchar2, itemkey IN varchar2,
1185 actid IN number, funcmode IN varchar2,
1186 resultout OUT NOCOPY varchar2) IS
1187 l_api_version NUMBER := 1.0;
1188 l_api_name VARCHAR2(40) := 'handle_eot_approval';
1189 p_init_msg_list VARCHAR2(1) := 'T';
1190 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1191 l_eot_id NUMBER;
1192 lx_msg_count NUMBER;
1193 l_result VARCHAR2(30);
1194 lv_approval_status_ame VARCHAR2(30);
1195 lx_msg_data VARCHAR2(32767);
1196 lx_return_status VARCHAR2(1);
1197 l_eve_rec okl_eve_rec;
1198 x_eve_rec okl_eve_rec;
1199
1200 BEGIN
1201
1202 IF (funcmode = 'RUN') THEN
1203 l_result := wf_engine.GetItemAttrText(itemtype, itemkey, 'RESULT');
1204 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
1205 itemkey => itemkey,
1206 aname => 'APPROVED_YN');
1207 IF (l_result = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = 'Y') THEN
1208 l_eot_id := wf_engine.GetItemAttrText(itemtype,
1209 itemkey,
1210 'VERSION_ID');
1211
1212 -- change the version and header status and end date referenced objects
1213
1214 okl_fe_eo_term_options_pvt.handle_approval(1,
1215 'T',
1216 lx_return_status,
1217 lx_msg_count,
1218 lx_msg_data,
1219 l_eot_id);
1220 IF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1221 RAISE OKL_API.G_EXCEPTION_ERROR;
1222 ELSIF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1223 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1224 END IF;
1225 ELSE
1226 l_eot_id := wf_engine.GetItemAttrText(itemtype,
1227 itemkey,
1228 'VERSION_ID');
1229
1230 -- populate the version attributes
1231
1232 l_eve_rec.end_of_term_ver_id := l_eot_id;
1233 l_eve_rec.sts_code := 'NEW';
1234
1235 -- change the version status back to new
1236
1237 okl_eve_pvt.update_row(l_api_version,
1238 p_init_msg_list,
1239 x_return_status,
1240 lx_msg_count,
1241 lx_msg_data,
1242 l_eve_rec,
1243 x_eve_rec);
1244 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1245 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1246 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1247 RAISE OKL_API.G_EXCEPTION_ERROR;
1248 END IF;
1249 END IF;
1250 resultout := 'COMPLETE';
1251 RETURN;
1252 END IF;
1253
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 WF_CORE.CONTEXT('okl_fe_wf',
1257 'handle_eot_approval',
1258 itemtype,
1259 itemkey,
1260 actid,
1261 funcmode);
1265 -- get the Lease Rate Set data
1262 RAISE;
1263 END handle_eot_approval;
1264
1266
1267 PROCEDURE get_lrs_data(itemtype IN varchar2, itemkey IN varchar2) IS
1268 l_rate_set_ver_id NUMBER;
1269 l_user_name VARCHAR2(240);
1270 x_return_status VARCHAR2(1);
1271 l_api_name VARCHAR2(40) := 'get_item_residual_data';
1272 x_msg_count NUMBER;
1273 x_msg_data VARCHAR2(32767);
1274 l_application_id fnd_application.application_id%TYPE;
1275 l_api_version NUMBER := 1.0;
1276 p_api_version NUMBER := 1.0;
1277
1278 CURSOR get_lrs_attr(p_rate_set_ver_id NUMBER) IS
1279 SELECT A.ID,
1280 A.NAME,
1281 A.DESCRIPTION,
1282 A.LRS_TYPE_CODE,
1283 A.END_OF_TERM_ID,
1284 A.CURRENCY_CODE,
1285 A.FRQ_CODE,
1286 B.RATE_SET_VERSION_ID,
1287 B.STS_CODE,
1288 B.ARREARS_YN,
1289 B.EFFECTIVE_FROM_DATE,
1290 B.EFFECTIVE_TO_DATE,
1291 B.END_OF_TERM_VER_ID,
1292 B.STD_RATE_TMPL_VER_ID,
1293 B.ADJ_MAT_VERSION_ID,
1294 B.VERSION_NUMBER,
1295 B.LRS_RATE,
1296 B.RATE_TOLERANCE,
1297 B.RESIDUAL_TOLERANCE,
1298 B.DEFERRED_PMTS,
1299 B.ADVANCE_PMTS
1300 FROM OKL_LS_RT_FCTR_SETS_V A,
1301 OKL_FE_RATE_SET_VERSIONS B
1302 WHERE A.ID = B.RATE_SET_ID
1303 AND B.RATE_SET_VERSION_ID = p_rate_set_ver_id;
1304 l_lrv_rec get_lrs_attr%ROWTYPE;
1305
1306 CURSOR fnd_user_csr IS
1307 SELECT USER_NAME
1308 FROM FND_USER
1309 WHERE USER_ID = fnd_global.user_id;
1310
1311 -- Get the valid application id from FND
1312
1313 CURSOR c_get_app_id_csr IS
1314 SELECT APPLICATION_ID
1315 FROM FND_APPLICATION
1316 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
1317
1318 BEGIN
1319 x_return_status := okl_api.start_activity(l_api_name,
1320 g_pkg_name,
1321 'T',
1322 l_api_version,
1323 p_api_version,
1324 '_PVT',
1325 x_return_status);
1326
1327 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1328 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1329 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1330 RAISE OKL_API.G_EXCEPTION_ERROR;
1331 END IF;
1332
1333 -- get the value of the version id from the workflow
1334
1335 l_rate_set_ver_id := wf_engine.GetItemAttrText(itemtype,
1336 itemkey,
1337 'VERSION_ID');
1338
1339 -- set the values of the attributes from the values of the cursor
1340
1341 OPEN get_lrs_attr(l_rate_set_ver_id);
1342 FETCH get_lrs_attr INTO l_lrv_rec ;
1343 CLOSE get_lrs_attr;
1344
1345 -- set all the attributes of Lease Rate Set
1346
1347 wf_engine.SetItemAttrText(itemtype,
1348 itemkey,
1349 'LRS_NAME',
1350 l_lrv_rec.name);
1351 wf_engine.SetItemAttrText(itemtype,
1352 itemkey,
1353 'DESCRIPTION',
1354 l_lrv_rec.description);
1355 wf_engine.SetItemAttrText(itemtype,
1356 itemkey,
1357 'TYPE',
1358 l_lrv_rec.lrs_type_code);
1359 wf_engine.SetItemAttrText(itemtype,
1360 itemkey,
1361 'EOT_ID',
1362 l_lrv_rec.end_of_term_id);
1363 wf_engine.SetItemAttrText(itemtype,
1364 itemkey,
1365 'CURRENCY',
1366 l_lrv_rec.currency_code);
1367 wf_engine.SetItemAttrText(itemtype,
1368 itemkey,
1369 'FREQUENCY',
1370 l_lrv_rec.frq_code);
1371 wf_engine.SetItemAttrText(itemtype,
1372 itemkey,
1373 'VERSION_ID',
1374 l_lrv_rec.rate_set_version_id);
1375 wf_engine.SetItemAttrText(itemtype,
1376 itemkey,
1377 'VERSION_NUMBER',
1378 l_lrv_rec.version_number);
1379 wf_engine.SetItemAttrText(itemtype,
1380 itemkey,
1381 'VERSION_STATUS',
1382 l_lrv_rec.STS_CODE);
1383 wf_engine.SetItemAttrDate(itemtype,
1384 itemkey,
1385 'EFFECTIVE_FROM',
1386 l_lrv_rec.EFFECTIVE_FROM_DATE);
1387 wf_engine.SetItemAttrDate(itemtype,
1388 itemkey,
1389 'EFFECTIVE_TO',
1390 l_lrv_rec.EFFECTIVE_TO_DATE);
1391 wf_engine.SetItemAttrText(itemtype,
1392 itemkey,
1393 'EOT_VERSION_ID',
1394 l_lrv_rec.end_of_term_ver_id);
1395 wf_engine.SetItemAttrText(itemtype,
1399 wf_engine.SetItemAttrNumber(itemtype,
1396 itemkey,
1397 'SRT_VERSION_ID',
1398 l_lrv_rec.STD_RATE_TMPL_VER_ID);
1400 itemkey,
1401 'RATE',
1402 l_lrv_rec.lrs_Rate);
1403 wf_engine.SetItemAttrText(itemtype,
1404 itemkey,
1405 'ARREARS',
1406 l_lrv_rec.arrears_yn);
1407 wf_engine.SetItemAttrNumber(itemtype,
1408 itemkey,
1409 'RATE_TOLERANCE',
1410 l_lrv_rec.RATE_TOLERANCE);
1411 wf_engine.SetItemAttrNumber(itemtype,
1412 itemkey,
1413 'RESIDUAL_TOLERANCE',
1414 l_lrv_rec.RESIDUAL_TOLERANCE);
1415 wf_engine.SetItemAttrNumber(itemtype,
1416 itemkey,
1417 'DEFERRED_PAYMENT',
1418 l_lrv_rec.deferred_pmts);
1419 wf_engine.SetItemAttrNumber(itemtype,
1420 itemkey,
1421 'ADVANCE_PAYMENT',
1422 l_lrv_rec.advance_pmts);
1423 wf_engine.SetItemAttrText(itemtype,
1424 itemkey,
1425 'ADJ_MAT_ID',
1426 l_lrv_rec.ADJ_MAT_VERSION_ID);
1427
1428 -- get the user name
1429
1430 OPEN fnd_user_csr;
1431 FETCH fnd_user_csr INTO l_user_name ;
1432 CLOSE fnd_user_csr;
1433
1434 -- get the application id
1435
1436 OPEN c_get_app_id_csr;
1437 FETCH c_get_app_id_csr INTO l_application_id ;
1438 CLOSE c_get_app_id_csr;
1439
1440 -- set the values of the approver and the requestor
1441
1442 wf_engine.SetItemAttrText(itemtype,
1443 itemkey,
1444 G_WF_ITM_APPROVER,
1445 l_user_name);
1446 wf_engine.SetItemAttrText(itemtype,
1447 itemkey,
1448 G_WF_ITM_REQUESTER,
1449 l_user_name);
1450 wf_engine.SetItemAttrText(itemtype,
1451 itemkey,
1452 G_WF_ITM_REQUESTER_ID,
1453 fnd_global.user_id);
1454 wf_engine.SetItemAttrText(itemtype,
1455 itemkey,
1456 G_WF_ITM_TRANSACTION_TYPE_ID,
1457 itemtype);
1458 wf_engine.SetItemAttrText(itemtype,
1459 itemkey,
1460 G_WF_ITM_TRANSACTION_ID,
1461 l_rate_set_ver_id);
1462 wf_engine.SetItemAttrText(itemtype,
1463 itemkey,
1464 G_WF_ITM_APPLICATION_ID,
1465 l_application_id);
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
1469 G_PKG_NAME,
1470 'OTHERS',
1471 x_msg_count,
1472 x_msg_data,
1473 '_PVT');
1474 RAISE;
1475 END get_lrs_data;
1476
1477 -- handle the Lease Rate Set approval
1478
1479 PROCEDURE handle_lrs_approval(itemtype IN varchar2, itemkey IN varchar2,
1480 actid IN number, funcmode IN varchar2,
1481 resultout OUT NOCOPY varchar2) IS
1482 l_api_version NUMBER := 1.0;
1483 l_api_name VARCHAR2(40) := 'handle_lrs_approval';
1484 p_init_msg_list VARCHAR2(1) := 'T';
1485 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1486 l_rate_set_ver_id NUMBER;
1487 lx_msg_count NUMBER;
1488 lx_msg_data VARCHAR2(32767);
1489 lx_return_status VARCHAR2(1);
1490 l_return_status VARCHAR2(1);
1491 l_result VARCHAR2(30);
1492 lv_approval_status_ame VARCHAR2(30);
1493 l_lrvv_rec okl_lrvv_rec;
1494 x_lrvv_rec okl_lrvv_rec;
1495
1496 BEGIN
1497
1498 IF (funcmode = 'RUN') THEN
1499 l_result := wf_engine.GetItemAttrText(itemtype, itemkey, 'RESULT');
1500 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
1501 itemkey => itemkey,
1502 aname => 'APPROVED_YN');
1503
1504 -- check if the workflow is approved or rejected
1505
1506 IF (l_result = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = 'Y') THEN
1507
1508 -- change the status of the Lease Rate Set
1509
1510 l_rate_set_ver_id := wf_engine.GetItemAttrText(itemtype,
1511 itemkey,
1512 'VERSION_ID');
1513 okl_lease_rate_Sets_pvt.activate_lease_rate_set(1,
1514 'T',
1515 lx_return_status,
1516 lx_msg_count,
1517 lx_msg_data,
1518 l_rate_set_ver_id);
1519 IF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1520 RAISE OKL_API.G_EXCEPTION_ERROR;
1524 ELSE
1521 ELSIF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1522 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1523 END IF;
1525 l_rate_set_ver_id := wf_engine.GetItemAttrText(itemtype,
1526 itemkey,
1527 'VERSION_ID');
1528
1529 -- populate the version attributes
1530
1531 l_lrvv_rec.rate_set_version_id := l_rate_set_ver_id;
1532 l_lrvv_rec.sts_code := 'NEW';
1533
1534 -- change the version status back to new
1535
1536 okl_lrv_pvt.update_row(l_api_version,
1537 p_init_msg_list,
1538 x_return_status,
1539 lx_msg_count,
1540 lx_msg_data,
1541 l_lrvv_rec,
1542 x_lrvv_rec);
1543 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1544 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1545 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1546 RAISE OKL_API.G_EXCEPTION_ERROR;
1547 END IF;
1548 END IF;
1549 resultout := 'COMPLETE';
1550 RETURN;
1551 END IF;
1552
1553 EXCEPTION
1554 WHEN OTHERS THEN
1555 WF_CORE.CONTEXT('okl_fe_wf',
1556 'handle_lrs_approval',
1557 itemtype,
1558 itemkey,
1559 actid,
1560 funcmode);
1561 RAISE;
1562 NULL;
1563 END handle_lrs_approval;
1564
1565 -- Get the data of item Residual
1566
1567 PROCEDURE get_item_residual_data(itemtype IN varchar2, itemkey IN varchar2) IS
1568 l_item_resdl_version_id NUMBER;
1569 l_src_code VARCHAR2(30) := NULL;
1570 l_user_name VARCHAR2(240);
1571 x_return_status VARCHAR2(1);
1572 l_api_name VARCHAR2(40) := 'get_item_residual_data';
1573 x_msg_count NUMBER;
1574 x_msg_data VARCHAR2(32767);
1575 l_application_id fnd_application.application_id%TYPE;
1576 l_api_version NUMBER := 1.0;
1577 p_api_version NUMBER := 1.0;
1578
1579 -- Cursor for Item
1580
1581 CURSOR get_irs_attr_item(p_itm_resdl_ver_id NUMBER) IS
1582 SELECT IRHV.ITEM_RESIDUAL_ID,
1583 IRHV.CATEGORY_TYPE_CODE,
1584 IRHV.INVENTORY_ITEM_ID,
1585 IRHV.ORGANIZATION_ID,
1586 IRHV.CATEGORY_ID,
1587 IRHV.CATEGORY_SET_ID,
1588 IRHV.RESI_CATEGORY_SET_ID,
1589 IRHV.RESIDUAL_TYPE_CODE,
1590 IRHV.CURRENCY_CODE,
1591 INV.CONCATENATED_SEGMENTS NAME,
1592 ICPV.VERSION_NUMBER,
1593 ICPV.STS_CODE,
1594 ICPV.START_DATE,
1595 ICPV.END_DATE,
1596 ICPV.ID
1597 FROM OKL_FE_ITEM_RESIDUAL IRHV,
1598 OKL_ITM_CAT_RV_PRCS_V ICPV,
1599 MTL_SYSTEM_ITEMS_VL INV
1600 WHERE IRHV.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
1601 AND IRHV.ORGANIZATION_ID = INV.ORGANIZATION_ID
1602 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
1603 AND ICPV.ID = p_itm_resdl_ver_id;
1604
1605 -- Cursor for Item category
1606
1607 CURSOR get_irs_attr_item_cat(p_itm_resdl_ver_id NUMBER) IS
1608 SELECT IRHV.ITEM_RESIDUAL_ID,
1609 IRHV.CATEGORY_TYPE_CODE,
1610 IRHV.INVENTORY_ITEM_ID,
1611 IRHV.ORGANIZATION_ID,
1612 IRHV.CATEGORY_ID,
1613 IRHV.CATEGORY_SET_ID,
1614 IRHV.RESI_CATEGORY_SET_ID,
1615 IRHV.RESIDUAL_TYPE_CODE,
1616 IRHV.CURRENCY_CODE,
1617 INVCAT.CATEGORY_CONCAT_SEGS NAME,
1618 ICPV.VERSION_NUMBER,
1619 ICPV.STS_CODE,
1620 ICPV.START_DATE,
1621 ICPV.END_DATE,
1622 ICPV.ID
1623 FROM OKL_FE_ITEM_RESIDUAL IRHV,
1624 OKL_ITM_CAT_RV_PRCS_V ICPV,
1625 MTL_CATEGORIES_V INVCAT
1626 WHERE IRHV.CATEGORY_ID = INVCAT.CATEGORY_ID
1627 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
1628 AND ICPV.ID = p_itm_resdl_ver_id;
1629
1630 -- Cursor for Residual category set
1631
1632 CURSOR get_irs_attr_res_cat(p_itm_resdl_ver_id NUMBER) IS
1633 SELECT IRHV.ITEM_RESIDUAL_ID,
1634 IRHV.CATEGORY_TYPE_CODE,
1635 IRHV.INVENTORY_ITEM_ID,
1636 IRHV.ORGANIZATION_ID,
1637 IRHV.CATEGORY_ID,
1638 IRHV.CATEGORY_SET_ID,
1639 IRHV.RESI_CATEGORY_SET_ID,
1640 IRHV.RESIDUAL_TYPE_CODE,
1641 IRHV.CURRENCY_CODE,
1642 RCSV.RESI_CAT_NAME NAME,
1643 ICPV.VERSION_NUMBER,
1644 ICPV.STS_CODE,
1645 ICPV.START_DATE,
1646 ICPV.END_DATE,
1647 ICPV.ID
1648 FROM OKL_FE_ITEM_RESIDUAL IRHV,
1649 OKL_ITM_CAT_RV_PRCS_V ICPV,
1650 OKL_FE_RESI_CAT_V RCSV
1651 WHERE IRHV.RESI_CATEGORY_SET_ID = RCSV.RESI_CATEGORY_SET_ID
1652 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
1653 AND ICPV.ID = p_itm_resdl_ver_id;
1654 l_attr_rec get_irs_attr_item%ROWTYPE;
1655
1656 CURSOR fnd_user_csr IS
1657 SELECT USER_NAME
1658 FROM FND_USER
1659 WHERE USER_ID = fnd_global.user_id;
1660
1661 -- Get the valid application id from FND
1662
1663 CURSOR c_get_app_id_csr IS
1664 SELECT APPLICATION_ID
1665 FROM FND_APPLICATION
1669 SELECT IRHV.CATEGORY_TYPE_CODE
1666 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
1667
1668 CURSOR c_get_cat_type(p_item_resdl_version_id NUMBER) IS
1670 FROM OKL_FE_ITEM_RESIDUAL IRHV,
1671 OKL_ITM_CAT_RV_PRCS_V ICPV
1672 WHERE IRHV.ITEM_RESIDUAL_ID = ICPV.ITEM_RESIDUAL_ID
1673 AND ICPV.ID = p_item_resdl_version_id; -- Item residual version ID
1674
1675 BEGIN
1676 x_return_status := okl_api.start_activity(l_api_name,
1677 g_pkg_name,
1678 'T',
1679 l_api_version,
1680 p_api_version,
1681 '_PVT',
1682 x_return_status);
1683
1684 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1685 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1686 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1687 RAISE OKL_API.G_EXCEPTION_ERROR;
1688 END IF;
1689
1690 -- get the value of the version id from the workflow
1691
1692 l_item_resdl_version_id := wf_engine.GetItemAttrText(itemtype,
1693 itemkey,
1694 'VERSION_ID');/*
1695 Select the category type of the item residual.
1696 */
1697 OPEN c_get_cat_type(l_item_resdl_version_id);
1698 FETCH c_get_cat_type INTO l_src_code ;-- variable that indicates whether it is an Item or item category or a residual category set
1699 CLOSE c_get_cat_type;
1700 CASE l_src_code
1701 WHEN G_CAT_ITEM THEN
1702 OPEN get_irs_attr_item(l_item_resdl_version_id);
1703 FETCH get_irs_attr_item INTO l_attr_rec ;
1704 CLOSE get_irs_attr_item;
1705 WHEN G_CAT_ITEM_CAT THEN
1706 OPEN get_irs_attr_item_cat(l_item_resdl_version_id);
1707 FETCH get_irs_attr_item_cat INTO l_attr_rec ;
1708 CLOSE get_irs_attr_item_cat;
1709 WHEN G_CAT_RES_CAT THEN
1710 OPEN get_irs_attr_res_cat(l_item_resdl_version_id);
1711 FETCH get_irs_attr_res_cat INTO l_attr_rec ;
1712 CLOSE get_irs_attr_res_cat;
1713 END CASE;
1714
1715 -- set the attributes of the workflow
1716
1717 wf_engine.SetItemAttrText(itemtype,
1718 itemkey,
1719 'IRS_NAME',
1720 l_attr_rec.name);
1721 wf_engine.SetItemAttrText(itemtype,
1722 itemkey,
1723 'SOURCE',
1724 l_attr_rec.category_type_code);
1725 wf_engine.SetItemAttrText(itemtype,
1726 itemkey,
1727 'INVENTORY_ITEM_ID',
1728 l_attr_rec.inventory_item_id);
1729 wf_engine.SetItemAttrText(itemtype,
1730 itemkey,
1731 'ORGANIZATION_ID',
1732 l_attr_rec.organization_id);
1733 wf_engine.SetItemAttrText(itemtype,
1734 itemkey,
1735 'CATEGORY_ID',
1736 l_attr_rec.category_id);
1737 wf_engine.SetItemAttrText(itemtype,
1738 itemkey,
1739 'CATEGORY_SET_ID',
1740 l_attr_rec.category_set_id);
1741 wf_engine.SetItemAttrText(itemtype,
1742 itemkey,
1743 'RCS_ID',
1744 l_attr_rec.resi_category_set_id);
1745 wf_engine.SetItemAttrText(itemtype,
1746 itemkey,
1747 'RESIDUAL_TYPE',
1748 l_attr_rec.residual_type_code);
1749 wf_engine.SetItemAttrText(itemtype,
1750 itemkey,
1751 'CURRENCY',
1752 l_attr_rec.currency_code);
1753 wf_engine.SetItemAttrText(itemtype,
1754 itemkey,
1755 'IRS_NAME',
1756 l_attr_rec.name);
1757 wf_engine.SetItemAttrText(itemtype,
1758 itemkey,
1759 'VERSION_NUMBER',
1760 l_attr_rec.version_number);
1761 wf_engine.SetItemAttrText(itemtype,
1762 itemkey,
1763 'VERSION_STATUS',
1764 l_attr_rec.sts_code);
1765 wf_engine.SetItemAttrDate(itemtype,
1766 itemkey,
1767 'EFFECTIVE_FROM',
1768 l_attr_rec.start_date);
1769 wf_engine.SetItemAttrDate(itemtype,
1770 itemkey,
1771 'EFFECTIVE_TO',
1772 l_attr_rec.end_date);
1773 OPEN fnd_user_csr;
1774 FETCH fnd_user_csr INTO l_user_name ;
1775 CLOSE fnd_user_csr;
1776
1777 -- get the application id
1778
1779 OPEN c_get_app_id_csr;
1780 FETCH c_get_app_id_csr INTO l_application_id ;
1781 CLOSE c_get_app_id_csr;
1782
1783 -- set the values of the approver and the requestor
1784
1785 wf_engine.SetItemAttrText(itemtype,
1786 itemkey,
1787 G_WF_ITM_APPROVER,
1791 G_WF_ITM_REQUESTER,
1788 l_user_name);
1789 wf_engine.SetItemAttrText(itemtype,
1790 itemkey,
1792 l_user_name);
1793 wf_engine.SetItemAttrText(itemtype,
1794 itemkey,
1795 G_WF_ITM_REQUESTER_ID,
1796 fnd_global.user_id);
1797 wf_engine.SetItemAttrText(itemtype,
1798 itemkey,
1799 G_WF_ITM_TRANSACTION_TYPE_ID,
1800 itemtype);
1801 wf_engine.SetItemAttrText(itemtype,
1802 itemkey,
1803 G_WF_ITM_TRANSACTION_ID,
1804 l_item_resdl_version_id);
1805 wf_engine.SetItemAttrText(itemtype,
1806 itemkey,
1807 G_WF_ITM_APPLICATION_ID,
1808 l_application_id);
1809 EXCEPTION
1810 WHEN OTHERS THEN
1811 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
1812 G_PKG_NAME,
1813 'OTHERS',
1814 x_msg_count,
1815 x_msg_data,
1816 '_PVT');
1817 RAISE;
1818 END get_item_residual_data;
1819
1820 -- Handle Item Residual Approval process
1821
1822 PROCEDURE handle_irs_approval(itemtype IN varchar2, itemkey IN varchar2,
1823 actid IN number, funcmode IN varchar2,
1824 resultout OUT NOCOPY varchar2) IS
1825 l_api_version NUMBER := 1.0;
1826 l_api_name VARCHAR2(40) := 'handle_irs_approval';
1827 p_init_msg_list VARCHAR2(1) := 'T';
1828 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1829 l_item_resdl_version_id NUMBER;
1830 lx_msg_count NUMBER;
1831 l_return_status VARCHAR2(1);
1832 l_result VARCHAR2(30);
1833 lv_approval_status_ame VARCHAR2(30);
1834 lx_msg_data VARCHAR2(32767);
1835 lx_return_status VARCHAR2(1);
1836 l_icpv_rec okl_icpv_rec;
1837 x_icpv_rec okl_icpv_rec;
1838
1839 BEGIN
1840
1841 IF (funcmode = 'RUN') THEN
1842 l_result := wf_engine.GetItemAttrText(itemtype, itemkey, 'RESULT');
1843 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
1844 itemkey => itemkey,
1845 aname => 'APPROVED_YN');
1846 IF (l_result = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = 'Y') THEN
1847 l_item_resdl_version_id := wf_engine.GetItemAttrText(itemtype,
1848 itemkey,
1849 'VERSION_ID');
1850
1851 -- change the version and header status and end date referenced objects
1852
1853 OKL_ITEM_RESIDUALS_PVT.activate_item_residual(1,
1854 'T',
1855 l_return_status,
1856 lx_msg_count,
1857 lx_msg_data,
1858 l_item_resdl_version_id);
1859 IF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1860 RAISE OKL_API.G_EXCEPTION_ERROR;
1861 ELSIF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1862 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1863 END IF;
1864 ELSE
1865 l_item_resdl_version_id := wf_engine.GetItemAttrText(itemtype,
1866 itemkey,
1867 'VERSION_ID');
1868
1869 -- populate the version attributes
1870
1871 l_icpv_rec.id := l_item_resdl_version_id;
1872 l_icpv_rec.sts_code := 'NEW';
1873
1874 -- change the version status back to new
1875
1876 okl_icp_pvt.update_row(l_api_version,
1877 p_init_msg_list,
1878 x_return_status,
1879 lx_msg_count,
1880 lx_msg_data,
1881 l_icpv_rec,
1882 x_icpv_rec);
1883 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1884 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1885 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1886 RAISE OKL_API.G_EXCEPTION_ERROR;
1887 END IF;
1888 END IF;
1889 resultout := 'COMPLETE';
1890 RETURN;
1891 END IF;
1892
1893 EXCEPTION
1894 WHEN OTHERS THEN
1895 WF_CORE.CONTEXT('okl_fe_wf',
1896 'handle_irs_approval',
1897 itemtype,
1898 itemkey,
1899 actid,
1900 funcmode);
1901 RAISE;
1902 END handle_irs_approval;
1903
1904 -- method to set the messages and the message desciption
1905
1906 PROCEDURE adj_mat_ame(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
1907 actid IN NUMBER, funcmode IN VARCHAR2,
1911 l_debug_enabled VARCHAR2(10);
1908 resultout OUT NOCOPY VARCHAR2) IS
1909 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'process_pool_ame';
1910 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.PROCESS_POOL_AME';
1912 is_debug_procedure_on BOOLEAN;
1913 l_name VARCHAR2(100);
1914 l_object_name VARCHAR2(50);
1915 l_request_message VARCHAR2(500);
1916 l_approved_message VARCHAR2(500);
1917 l_rejected_message VARCHAR2(500);
1918 l_reminder_message VARCHAR2(500);
1919
1920 CURSOR obj_name_csr IS
1921 SELECT MEANING
1922 FROM FND_LOOKUPS
1923 WHERE LOOKUP_TYPE = 'OKL_PRICING_OBJECTS' AND LOOKUP_CODE = 'PAM';
1924
1925 BEGIN
1926
1927 -- RUN mode
1928
1929 IF (funcmode = 'RUN') THEN
1930 get_adj_matrix_get_data(itemtype, itemkey);
1931 OPEN obj_name_csr;
1932 FETCH obj_name_csr INTO l_object_name ;
1933 CLOSE obj_name_csr;
1934
1935 -- get the messages and set the messages accordingly depending on the object type
1936
1937 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'PAM_NAME');
1938 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
1939 l_object_name,
1940 l_name);
1941 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
1942 l_object_name,
1943 l_name);
1944 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
1945 l_object_name,
1946 l_name);
1947 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
1948 l_object_name,
1949 l_name);
1950 wf_engine.SetItemAttrText(itemtype,
1951 itemkey,
1952 G_WF_ITM_APP_REQUEST_SUB,
1953 l_request_message);
1954 wf_engine.SetItemAttrText(itemtype,
1955 itemkey,
1956 G_WF_ITM_APP_REMINDER_SUB,
1957 l_reminder_message);
1958 wf_engine.SetItemAttrText(itemtype,
1959 itemkey,
1960 G_WF_ITM_APP_REMINDER_HEAD,
1961 l_reminder_message);
1962 wf_engine.SetItemAttrText(itemtype,
1963 itemkey,
1964 G_WF_ITM_APP_APPROVED_SUB,
1965 l_approved_message);
1966 wf_engine.SetItemAttrText(itemtype,
1967 itemkey,
1968 G_WF_ITM_APP_APPROVED_HEAD,
1969 l_approved_message);
1970 wf_engine.SetItemAttrText(itemtype,
1971 itemkey,
1972 G_WF_ITM_APP_REJECTED_SUB,
1973 l_rejected_message);
1974 wf_engine.SetItemAttrText(itemtype,
1975 itemkey,
1976 G_WF_ITM_APP_REJECTED_HEAD,
1977 l_rejected_message);
1978 wf_engine.SetItemAttrText(itemtype => itemtype,
1979 itemkey => itemkey,
1980 aname => G_WF_ITM_MESSAGE_BODY,
1981 avalue => 'plsql:okl_fe_wf.get_pam_msg_doc/' ||
1982 itemkey);
1983 wf_engine.SetItemAttrText(itemtype => itemtype,
1984 itemkey => itemkey,
1985 aname => G_WF_ITM_MESSAGE_DESCR,
1986 avalue => get_pam_msg_body(itemtype,
1987 itemkey));
1988 resultout := 'COMPLETE:';
1989 END IF;
1990
1991 -- CANCEL mode
1992
1993 IF (funcmode = 'CANCEL') THEN
1994 resultout := 'COMPLETE:';
1995 RETURN;
1996 END IF;
1997
1998 -- TIMEOUT mode
1999
2000 IF (funcmode = 'TIMEOUT') THEN
2001 resultout := 'COMPLETE:';
2002 RETURN;
2003 END IF;
2004
2005 EXCEPTION
2006 WHEN OTHERS THEN
2007 wf_core.context(G_PKG_NAME,
2008 l_api_name,
2009 itemtype,
2010 itemkey,
2011 actid,
2012 funcmode);
2013 RAISE;
2014 END adj_mat_ame;
2015
2016 -- method to set the messages and the message desciption
2017
2018 PROCEDURE adj_mat_wf(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2019 actid IN NUMBER, funcmode IN VARCHAR2,
2020 resultout OUT NOCOPY VARCHAR2) IS
2021 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'adj_mat_wf';
2022
2023 BEGIN
2024
2025 -- RUN mode
2026
2027 IF (funcmode = 'RUN') THEN
2028
2029 -- set all the attributes required
2030
2031 get_adj_matrix_get_data(itemtype, itemkey);
2032
2033 -- set all the messages for the notification
2034
2035 set_messages(itemtype, itemkey, 'PAM');
2036 resultout := 'COMPLETE';
2037 RETURN;
2038 END IF;
2039
2040 -- CANCEL mode
2041
2042 IF (funcmode = 'CANCEL') THEN
2043 resultout := 'COMPLETE:';
2044 RETURN;
2045 END IF;
2046
2047 -- TIMEOUT mode
2048
2052 END IF;
2049 IF (funcmode = 'TIMEOUT') THEN
2050 resultout := 'COMPLETE:';
2051 RETURN;
2053
2054 EXCEPTION
2055 WHEN OTHERS THEN
2056 wf_core.context(G_PKG_NAME,
2057 l_api_name,
2058 itemtype,
2059 itemkey,
2060 actid,
2061 funcmode);
2062 RAISE;
2063 END adj_mat_wf;
2064
2065 -- method to set the messages and the message desciption
2066
2067 PROCEDURE std_rate_tmpl_ame(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2068 actid IN NUMBER, funcmode IN VARCHAR2,
2069 resultout OUT NOCOPY VARCHAR2) IS
2070 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'std_rate_tmpl_ame';
2071 l_name VARCHAR2(100);
2072 l_object_name VARCHAR2(50);
2073 l_request_message VARCHAR2(500);
2074 l_approved_message VARCHAR2(500);
2075 l_rejected_message VARCHAR2(500);
2076 l_reminder_message VARCHAR2(500);
2077
2078 CURSOR obj_name_csr IS
2079 SELECT MEANING
2080 FROM FND_LOOKUPS
2081 WHERE LOOKUP_TYPE = 'OKL_PRICING_OBJECTS' AND LOOKUP_CODE = 'SRT';
2082
2083 BEGIN
2084
2085 -- RUN mode
2086
2087 IF (funcmode = 'RUN') THEN
2088
2089 get_std_rate_tmpl_data(itemtype, itemkey);
2090
2091 OPEN obj_name_csr;
2092 FETCH obj_name_csr INTO l_object_name ;
2093 CLOSE obj_name_csr;
2094 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'SRT_NAME');
2095 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
2096 l_object_name,
2097 l_name);
2098 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
2099 l_object_name,
2100 l_name);
2101 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
2102 l_object_name,
2103 l_name);
2104 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
2105 l_object_name,
2106 l_name);
2107 wf_engine.SetItemAttrText(itemtype,
2108 itemkey,
2109 G_WF_ITM_APP_REQUEST_SUB,
2110 l_request_message);
2111 wf_engine.SetItemAttrText(itemtype,
2112 itemkey,
2113 G_WF_ITM_APP_REMINDER_SUB,
2114 l_reminder_message);
2115 wf_engine.SetItemAttrText(itemtype,
2116 itemkey,
2117 G_WF_ITM_APP_REMINDER_HEAD,
2118 l_reminder_message);
2119 wf_engine.SetItemAttrText(itemtype,
2120 itemkey,
2121 G_WF_ITM_APP_APPROVED_SUB,
2122 l_approved_message);
2123 wf_engine.SetItemAttrText(itemtype,
2124 itemkey,
2125 G_WF_ITM_APP_APPROVED_HEAD,
2126 l_approved_message);
2127 wf_engine.SetItemAttrText(itemtype,
2128 itemkey,
2129 G_WF_ITM_APP_REJECTED_SUB,
2130 l_rejected_message);
2131 wf_engine.SetItemAttrText(itemtype,
2132 itemkey,
2133 G_WF_ITM_APP_REJECTED_HEAD,
2134 l_rejected_message);
2135 wf_engine.SetItemAttrText(itemtype => itemtype,
2136 itemkey => itemkey,
2137 aname => G_WF_ITM_MESSAGE_BODY,
2138 avalue => 'plsql:okl_fe_wf.get_srt_msg_doc/' ||
2139 itemkey);
2140 wf_engine.SetItemAttrText(itemtype => itemtype,
2141 itemkey => itemkey,
2142 aname => G_WF_ITM_MESSAGE_DESCR,
2143 avalue => get_srt_msg_body(itemtype,
2144 itemkey));
2145 resultout := 'COMPLETE:';
2146 END IF;
2147
2148 -- CANCEL mode
2149
2150 IF (funcmode = 'CANCEL') THEN
2151 resultout := 'COMPLETE:';
2152 RETURN;
2153 END IF;
2154
2155 -- TIMEOUT mode
2156
2157 IF (funcmode = 'TIMEOUT') THEN
2158 resultout := 'COMPLETE:';
2159 RETURN;
2160 END IF;
2161
2162 EXCEPTION
2163 WHEN OTHERS THEN
2164 wf_core.context(G_PKG_NAME,
2165 l_api_name,
2166 itemtype,
2167 itemkey,
2168 actid,
2169 funcmode);
2170 RAISE;
2171 END std_rate_tmpl_ame;
2172
2173 -- method to set the messages and the message desciption
2174
2175 PROCEDURE std_rate_tmpl_wf(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2176 actid IN NUMBER, funcmode IN VARCHAR2,
2177 resultout OUT NOCOPY VARCHAR2) IS
2178 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'std_rate_tmpl_wf';
2179
2180 BEGIN
2181
2182 -- RUN mode
2183
2184 IF (funcmode = 'RUN') THEN
2185
2186 -- set all the attributes required
2187
2188 get_std_rate_tmpl_data(itemtype, itemkey);
2189
2193 END IF;
2190 -- set all the messages for the notification
2191
2192 set_messages(itemtype, itemkey, 'SRT');
2194
2195 -- CANCEL mode
2196
2197 IF (funcmode = 'CANCEL') THEN
2198 resultout := 'COMPLETE:';
2199 RETURN;
2200 END IF;
2201
2202 -- TIMEOUT mode
2203
2204 IF (funcmode = 'TIMEOUT') THEN
2205 resultout := 'COMPLETE:';
2206 RETURN;
2207 END IF;
2208
2209 EXCEPTION
2210 WHEN OTHERS THEN
2211 wf_core.context(G_PKG_NAME,
2212 l_api_name,
2213 itemtype,
2214 itemkey,
2215 actid,
2216 funcmode);
2217 RAISE;
2218 END std_rate_tmpl_wf;
2219
2220 -- method to set the message body and message description
2221
2222 PROCEDURE eo_term_ame(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2223 actid IN NUMBER, funcmode IN VARCHAR2,
2224 resultout OUT NOCOPY VARCHAR2) IS
2225 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'eo_term_ame';
2226 l_name VARCHAR2(100);
2227 l_object_name VARCHAR2(50);
2228 l_request_message VARCHAR2(500);
2229 l_approved_message VARCHAR2(500);
2230 l_rejected_message VARCHAR2(500);
2231 l_reminder_message VARCHAR2(500);
2232
2233 CURSOR obj_name_csr IS
2234 SELECT MEANING
2235 FROM FND_LOOKUPS
2236 WHERE LOOKUP_TYPE = 'OKL_PRICING_OBJECTS' AND LOOKUP_CODE = 'EOT';
2237
2238 BEGIN
2239
2240 -- RUN mode
2241
2242 IF (funcmode = 'RUN') THEN
2243 -- set all the attributes required
2244 get_end_of_term_data(itemtype, itemkey);
2245
2246 OPEN obj_name_csr;
2247 FETCH obj_name_csr INTO l_object_name ;
2248 CLOSE obj_name_csr;
2249 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'EOT_NAME');
2250 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
2251 l_object_name,
2252 l_name);
2253 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
2254 l_object_name,
2255 l_name);
2256 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
2257 l_object_name,
2258 l_name);
2259 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
2260 l_object_name,
2261 l_name);
2262 wf_engine.SetItemAttrText(itemtype,
2263 itemkey,
2264 G_WF_ITM_APP_REQUEST_SUB,
2265 l_request_message);
2266 wf_engine.SetItemAttrText(itemtype,
2267 itemkey,
2268 G_WF_ITM_APP_REMINDER_SUB,
2269 l_reminder_message);
2270 wf_engine.SetItemAttrText(itemtype,
2271 itemkey,
2272 G_WF_ITM_APP_REMINDER_HEAD,
2273 l_reminder_message);
2274 wf_engine.SetItemAttrText(itemtype,
2275 itemkey,
2276 G_WF_ITM_APP_APPROVED_SUB,
2277 l_approved_message);
2278 wf_engine.SetItemAttrText(itemtype,
2279 itemkey,
2280 G_WF_ITM_APP_APPROVED_HEAD,
2281 l_approved_message);
2282 wf_engine.SetItemAttrText(itemtype,
2283 itemkey,
2284 G_WF_ITM_APP_REJECTED_SUB,
2285 l_rejected_message);
2286 wf_engine.SetItemAttrText(itemtype,
2287 itemkey,
2288 G_WF_ITM_APP_REJECTED_HEAD,
2289 l_rejected_message);
2290 wf_engine.SetItemAttrText(itemtype => itemtype,
2291 itemkey => itemkey,
2292 aname => G_WF_ITM_MESSAGE_BODY,
2293 avalue => 'plsql:okl_fe_wf.get_eot_msg_doc/' ||
2294 itemkey);
2295 wf_engine.SetItemAttrText(itemtype => itemtype,
2296 itemkey => itemkey,
2297 aname => G_WF_ITM_MESSAGE_DESCR,
2298 avalue => get_eot_msg_body(itemtype,
2299 itemkey));
2300 resultout := 'COMPLETE:';
2301 END IF;
2302
2303 -- CANCEL mode
2304
2305 IF (funcmode = 'CANCEL') THEN
2306 resultout := 'COMPLETE:';
2307 RETURN;
2308 END IF;
2309
2310 -- TIMEOUT mode
2311
2312 IF (funcmode = 'TIMEOUT') THEN
2313 resultout := 'COMPLETE:';
2314 RETURN;
2315 END IF;
2316
2317 EXCEPTION
2318 WHEN OTHERS THEN
2319 wf_core.context(G_PKG_NAME,
2320 l_api_name,
2321 itemtype,
2322 itemkey,
2323 actid,
2324 funcmode);
2325 RAISE;
2326 END eo_term_ame;
2327
2328 -- method to set the messages and the message desciption
2329
2330 PROCEDURE eo_term_wf(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2331 actid IN NUMBER, funcmode IN VARCHAR2,
2335 BEGIN
2332 resultout OUT NOCOPY VARCHAR2) IS
2333 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'eo_term_wf';
2334
2336
2337 -- RUN mode
2338
2339 IF (funcmode = 'RUN') THEN
2340
2341 -- set all the attributes required
2342
2343 get_end_of_term_data(itemtype, itemkey);
2344
2345 -- set all the messages for the notification
2346
2347 set_messages(itemtype, itemkey, 'EOT');
2348 END IF;
2349
2350 -- CANCEL mode
2351
2352 IF (funcmode = 'CANCEL') THEN
2353 resultout := 'COMPLETE:';
2354 RETURN;
2355 END IF;
2356
2357 -- TIMEOUT mode
2358
2359 IF (funcmode = 'TIMEOUT') THEN
2360 resultout := 'COMPLETE:';
2361 RETURN;
2362 END IF;
2363
2364 EXCEPTION
2365 WHEN OTHERS THEN
2366 wf_core.context(G_PKG_NAME,
2367 l_api_name,
2368 itemtype,
2369 itemkey,
2370 actid,
2371 funcmode);
2372 RAISE;
2373 END eo_term_wf;
2374
2375 -- method to set the message and message description
2376
2377 PROCEDURE item_res_ame(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2378 actid IN NUMBER, funcmode IN VARCHAR2,
2379 resultout OUT NOCOPY VARCHAR2) IS
2380 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'item_res_ame';
2381 l_name VARCHAR2(100);
2382 l_object_name VARCHAR2(50);
2383 l_request_message VARCHAR2(500);
2384 l_approved_message VARCHAR2(500);
2385 l_rejected_message VARCHAR2(500);
2386 l_reminder_message VARCHAR2(500);
2387
2388 CURSOR obj_name_csr IS
2389 SELECT MEANING
2390 FROM FND_LOOKUPS
2391 WHERE LOOKUP_TYPE = 'OKL_PRICING_OBJECTS' AND LOOKUP_CODE = 'IRS';
2392
2393 BEGIN
2394
2395 -- RUN mode
2396
2397 IF (funcmode = 'RUN') THEN
2398 -- set all the attributes required
2399 get_item_residual_data(itemtype, itemkey);
2400
2401 OPEN obj_name_csr;
2402 FETCH obj_name_csr INTO l_object_name ;
2403 CLOSE obj_name_csr;
2404 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'IRS_NAME');
2405 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
2406 l_object_name,
2407 l_name);
2408 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
2409 l_object_name,
2410 l_name);
2411 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
2412 l_object_name,
2413 l_name);
2414 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
2415 l_object_name,
2416 l_name);
2417 wf_engine.SetItemAttrText(itemtype,
2418 itemkey,
2419 G_WF_ITM_APP_REQUEST_SUB,
2420 l_request_message);
2421 wf_engine.SetItemAttrText(itemtype,
2422 itemkey,
2423 G_WF_ITM_APP_REMINDER_SUB,
2424 l_reminder_message);
2425 wf_engine.SetItemAttrText(itemtype,
2426 itemkey,
2427 G_WF_ITM_APP_REMINDER_HEAD,
2428 l_reminder_message);
2429 wf_engine.SetItemAttrText(itemtype,
2430 itemkey,
2431 G_WF_ITM_APP_APPROVED_SUB,
2432 l_approved_message);
2433 wf_engine.SetItemAttrText(itemtype,
2434 itemkey,
2435 G_WF_ITM_APP_APPROVED_HEAD,
2436 l_approved_message);
2437 wf_engine.SetItemAttrText(itemtype,
2438 itemkey,
2439 G_WF_ITM_APP_REJECTED_SUB,
2440 l_rejected_message);
2441 wf_engine.SetItemAttrText(itemtype,
2442 itemkey,
2443 G_WF_ITM_APP_REJECTED_HEAD,
2444 l_rejected_message);
2445 wf_engine.SetItemAttrText(itemtype => itemtype,
2446 itemkey => itemkey,
2447 aname => G_WF_ITM_MESSAGE_BODY,
2448 avalue => 'plsql:okl_fe_wf.get_irs_msg_doc/' ||
2449 itemkey);
2450 wf_engine.SetItemAttrText(itemtype => itemtype,
2451 itemkey => itemkey,
2452 aname => G_WF_ITM_MESSAGE_DESCR,
2453 avalue => get_irs_msg_body(itemtype,
2454 itemkey));
2455 resultout := 'COMPLETE:';
2456 END IF;
2457
2458 -- CANCEL mode
2459
2460 IF (funcmode = 'CANCEL') THEN
2461 resultout := 'COMPLETE:';
2462 RETURN;
2463 END IF;
2464
2465 -- TIMEOUT mode
2466
2467 IF (funcmode = 'TIMEOUT') THEN
2468 resultout := 'COMPLETE:';
2469 RETURN;
2470 END IF;
2471
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 wf_core.context(G_PKG_NAME,
2475 l_api_name,
2476 itemtype,
2477 itemkey,
2481 END item_res_ame;
2478 actid,
2479 funcmode);
2480 RAISE;
2482
2483 -- method to set the messages and the message desciption
2484
2485 PROCEDURE item_res_wf(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2486 actid IN NUMBER, funcmode IN VARCHAR2,
2487 resultout OUT NOCOPY VARCHAR2) IS
2488 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'item_res_wf';
2489
2490 BEGIN
2491
2492 -- RUN mode
2493
2494 IF (funcmode = 'RUN') THEN
2495
2496 -- set all the attributes required
2497
2498 get_item_residual_data(itemtype, itemkey);
2499
2500 -- set all the messages for the notification
2501
2502 set_messages(itemtype, itemkey, 'IRS');
2503 END IF;
2504
2505 -- CANCEL mode
2506
2507 IF (funcmode = 'CANCEL') THEN
2508 resultout := 'COMPLETE:';
2509 RETURN;
2510 END IF;
2511
2512 -- TIMEOUT mode
2513
2514 IF (funcmode = 'TIMEOUT') THEN
2515 resultout := 'COMPLETE:';
2516 RETURN;
2517 END IF;
2518
2519 EXCEPTION
2520 WHEN OTHERS THEN
2521 wf_core.context(G_PKG_NAME,
2522 l_api_name,
2523 itemtype,
2524 itemkey,
2525 actid,
2526 funcmode);
2527 RAISE;
2528 END item_res_wf;
2529
2530 -- method to set the Lease Rate Set messages and message description
2531
2532 PROCEDURE lease_rate_set_ame(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2533 actid IN NUMBER, funcmode IN VARCHAR2,
2534 resultout OUT NOCOPY VARCHAR2) IS
2535 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'lease_rate_set_ame';
2536 l_name VARCHAR2(100);
2537 l_object_name VARCHAR2(50);
2538 l_request_message VARCHAR2(500);
2539 l_approved_message VARCHAR2(500);
2540 l_rejected_message VARCHAR2(500);
2541 l_reminder_message VARCHAR2(500);
2542
2543 CURSOR obj_name_csr IS
2544 SELECT MEANING
2545 FROM FND_LOOKUPS
2546 WHERE LOOKUP_TYPE = 'OKL_PRICING_OBJECTS' AND LOOKUP_CODE = 'LRS';
2547
2548 BEGIN
2549
2550 -- RUN mode
2551
2552 IF (funcmode = 'RUN') THEN
2553 -- set all the attributes required
2554 get_lrs_data(itemtype, itemkey);
2555
2556 OPEN obj_name_csr;
2557 FETCH obj_name_csr INTO l_object_name ;
2558 CLOSE obj_name_csr;
2559 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'LRS_NAME');
2560 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
2561 l_object_name,
2562 l_name);
2563 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
2564 l_object_name,
2565 l_name);
2566 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
2567 l_object_name,
2568 l_name);
2569 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
2570 l_object_name,
2571 l_name);
2572 wf_engine.SetItemAttrText(itemtype,
2573 itemkey,
2574 G_WF_ITM_APP_REQUEST_SUB,
2575 l_request_message);
2576 wf_engine.SetItemAttrText(itemtype,
2577 itemkey,
2578 G_WF_ITM_APP_REMINDER_SUB,
2579 l_reminder_message);
2580 wf_engine.SetItemAttrText(itemtype,
2581 itemkey,
2582 G_WF_ITM_APP_REMINDER_HEAD,
2583 l_reminder_message);
2584 wf_engine.SetItemAttrText(itemtype,
2585 itemkey,
2586 G_WF_ITM_APP_APPROVED_SUB,
2587 l_approved_message);
2588 wf_engine.SetItemAttrText(itemtype,
2589 itemkey,
2590 G_WF_ITM_APP_APPROVED_HEAD,
2591 l_approved_message);
2592 wf_engine.SetItemAttrText(itemtype,
2593 itemkey,
2594 G_WF_ITM_APP_REJECTED_SUB,
2595 l_rejected_message);
2596 wf_engine.SetItemAttrText(itemtype,
2597 itemkey,
2598 G_WF_ITM_APP_REJECTED_HEAD,
2599 l_rejected_message);
2600 wf_engine.SetItemAttrText(itemtype => itemtype,
2601 itemkey => itemkey,
2602 aname => G_WF_ITM_MESSAGE_BODY,
2603 avalue => 'plsql:okl_fe_wf.get_lrs_msg_doc/' ||
2604 itemkey);
2605 wf_engine.SetItemAttrText(itemtype => itemtype,
2606 itemkey => itemkey,
2607 aname => G_WF_ITM_MESSAGE_DESCR,
2608 avalue => get_lrs_msg_body(itemtype,
2609 itemkey));
2610 resultout := 'COMPLETE:';
2611 END IF;
2612
2613 -- CANCEL mode
2614
2615 IF (funcmode = 'CANCEL') THEN
2616 resultout := 'COMPLETE:';
2617 RETURN;
2618 END IF;
2619
2620 -- TIMEOUT mode
2621
2622 IF (funcmode = 'TIMEOUT') THEN
2623 resultout := 'COMPLETE:';
2624 RETURN;
2625 END IF;
2626
2627 EXCEPTION
2628 WHEN OTHERS THEN
2629 wf_core.context(G_PKG_NAME,
2630 l_api_name,
2631 itemtype,
2632 itemkey,
2633 actid,
2634 funcmode);
2635 RAISE;
2636 END lease_rate_set_ame;
2637
2638 -- method to set the messages and the message desciption
2639
2640 PROCEDURE lease_rate_set_wf(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
2641 actid IN NUMBER, funcmode IN VARCHAR2,
2642 resultout OUT NOCOPY VARCHAR2) IS
2643 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'lease_rate_set_wf';
2644
2645 BEGIN
2646
2647 -- RUN mode
2648
2649 IF (funcmode = 'RUN') THEN
2650
2651 -- set all the attributes required
2652
2653 get_lrs_data(itemtype, itemkey);
2654
2655 -- set all the messages for the notification
2656
2657 set_messages(itemtype, itemkey, 'LRS');
2658 END IF;
2659
2660 -- CANCEL mode
2661
2662 IF (funcmode = 'CANCEL') THEN
2663 resultout := 'COMPLETE:';
2664 RETURN;
2665 END IF;
2666
2667 -- TIMEOUT mode
2668
2669 IF (funcmode = 'TIMEOUT') THEN
2670 resultout := 'COMPLETE:';
2671 RETURN;
2672 END IF;
2673
2674 EXCEPTION
2675 WHEN OTHERS THEN
2676 wf_core.context(G_PKG_NAME,
2677 l_api_name,
2678 itemtype,
2679 itemkey,
2680 actid,
2681 funcmode);
2682 RAISE;
2683 END lease_rate_set_wf;
2684
2685 END okl_fe_wf;