[Home] [Help]
PACKAGE BODY: APPS.AMW_CTRL_APPROVAL_PVT
Source
1 PACKAGE BODY AMW_CTRL_APPROVAL_PVT AS
2 /* $Header: amwvcapb.pls 115.5 2003/11/25 22:33:40 mpande noship $ */
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- amw_CTRL_Approval_PVT
8 --
9 -- PURPOSE
10 -- This package contains all transactions to be done for
11 -- CTRL Approvals in Oracle Internal CTRLs
12 --
13 -- HISTORY
14 -- 6/4/2003 MUMU PANDE CREATION
15 -- 6/25/2003 KARTHI MUTHUSWAMY Modified update_CTRL_status()
16 -- 7/2/2003 mpande Updated for All Message and formatting
17
18 g_pkg_name CONSTANT VARCHAR2 (30) := 'amw_CTRL_Approval_PVT';
19 g_file_name CONSTANT VARCHAR2 (15) := 'amwvcapb.pls';
20 g_CTRL_mode CONSTANT VARCHAR2 (15) := 'WORKFLOW';
21 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
22
23
24 --------------------------------------------------------------------------
25 -- PROCEDURE
26 -- Notify_requestor_FYI
27 --
28 -- PURPOSE
29 -- Generate the FYI Document for display in messages, either
30 -- text or html
31 -- IN
32 -- document_id - Item Key
33 -- display_type - either 'text/plain' or 'text/html'
34 -- document - document buffer
35 -- document_type - type of document buffer created, either 'text/plain'
36 -- or 'text/html'
37 -- OUT
38 -- USED BY
39 -- - Oracle Internal Controls Generic Apporval
40 -- HISTORY
41 -- 6/4/2003 MUMU PANDE CREATION
42
43
44 PROCEDURE notify_requestor_fyi (
45 document_id IN VARCHAR2,
46 display_type IN VARCHAR2,
47 document IN OUT NOCOPY VARCHAR2,
48 document_type IN OUT NOCOPY VARCHAR2
49 ) IS
50 l_api_name VARCHAR2 (61)
51 := g_pkg_name
52 || 'Notify_Requestor_FYI';
53 l_CTRL_rev_id NUMBER;
54 l_CTRL_id NUMBER;
55 l_CTRL_name VARCHAR2 (240);
56 l_hyphen_pos1 NUMBER;
57 l_fyi_notification VARCHAR2 (10000);
58 l_OBJECT_type VARCHAR2 (30);
59 l_item_type VARCHAR2 (100);
60 l_item_key VARCHAR2 (100);
61 l_approval_type VARCHAR2 (30);
62 l_approver VARCHAR2 (200);
63 l_note VARCHAR2 (4000);
64 l_string VARCHAR2 (1000);
65 l_string1 VARCHAR2 (2500);
66 l_start_date DATE;
67 l_requestor_id NUMBER;
68 l_string2 VARCHAR2 (2500);
69 l_requested_amt NUMBER;
70 l_OBJECT_meaning VARCHAR2(80);
71 l_requestor_name VARCHAR2(260);
72
73 CURSOR c_CTRL_rec (p_CTRL_rev_id IN NUMBER) IS
74 SELECT Control_id, name, creation_date,
75 requestor_id
76 FROM amw_Controls_all_vl
77 WHERE control_rev_id = p_CTRL_rev_id;
78 BEGIN
79 IF G_DEBUG THEN
80 amw_utility_pvt.debug_message (
81 l_api_name
82 || 'Entering'
83 || 'document id '
84 || document_id
85 );
86 END IF;
87 document_type := 'text/plain';
88 -- parse document_id for the ':' dividing item type name from item key value
89 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
90 -- release 2.5 version of this demo
91 l_hyphen_pos1 := INSTR (document_id, ':');
92 l_item_type :=
93 SUBSTR (document_id, 1, l_hyphen_pos1
94 - 1);
95 l_item_key := SUBSTR (document_id, l_hyphen_pos1
96 + 1);
97 l_OBJECT_type :=
98 wf_engine.getitemattrtext (
99 itemtype=> l_item_type,
100 itemkey=> l_item_key,
101 aname => 'AMW_OBJECT_TYPE'
102 );
103 l_OBJECT_meaning :=
104 wf_engine.getitemattrtext (
105 itemtype=> l_item_type,
106 itemkey=> l_item_key,
107 aname => 'AMW_APPROVAL_OBJECT_MEANING'
108 );
109
110 l_CTRL_rev_id :=
111 wf_engine.getitemattrtext (
112 itemtype=> l_item_type,
113 itemkey=> l_item_key,
114 aname => 'AMW_OBJECT_ID'
115 );
116 /*7/2/2003 mpande not required
117 l_note :=
118 wf_engine.getitemattrtext (
119 itemtype=> l_item_type,
120 itemkey=> l_item_key,
121 aname => 'AMW_NOTES_FROM_REQUESTOR'
122 );
123 */
124 l_approver :=
125 wf_engine.getitemattrtext (
126 itemtype=> l_item_type,
127 itemkey=> l_item_key,
128 aname => 'AMW_APPROVER_DISPLAY_NAME'
129 );
130
131 OPEN c_CTRL_rec (l_CTRL_rev_id);
132 FETCH c_CTRL_rec INTO l_CTRL_id,
133 l_CTRL_name,
134 l_start_date,
135 l_requestor_id;
136 CLOSE c_CTRL_rec;
137 l_requestor_name := amw_utility_pvt.get_employee_name(l_requestor_id);
138 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_FYI_SUB');
139 fnd_message.set_token ('NAME', l_CTRL_name, FALSE);
140 fnd_message.set_token ('OBJECT_TYPE', l_object_meaning, FALSE);
141 l_string := SUBSTR(fnd_message.get,1,1000);
142 /*
143 wf_engine.setitemattrtext (
144 itemtype=> l_item_type,
145 itemkey=> l_item_key,
146 aname => 'FYI_SUBJECT',
147 avalue=> l_string
148 );
149 */
150 fnd_message.set_name ('AMW', 'AMW_WF_NTF_CTRL_REQ_INFO');
151 fnd_message.set_token ('CTRL_NAME', l_CTRL_name, FALSE);
152 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
153 fnd_message.set_token ('REQUESTOR_NAME', l_requestor_name, FALSE);
154 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
155
156 /*
157 l_note := wf_engine.getitemattrtext(
158 itemtype => l_item_type
159 ,itemkey => l_item_key
160 ,aname => 'NOTE');
161
162
163 l_forwarder :=
164 wf_engine.getitemattrtext(
165 itemtype => l_item_type
166 ,itemkey => l_item_key
167 ,aname => 'AMW_FORWARD_FROM_USERNAME');
168 */
169 -- IF (display_type = 'text/plain') THEN
170 l_fyi_notification := SUBSTR(l_string
171 || fnd_global.local_chr (10)
172 || l_string1
173 || fnd_global.local_chr (10)
174 || l_string2,1,10000);
175 document := document
176 || l_fyi_notification;
177 document_type := 'text/plain';
178 RETURN;
179 -- END IF;
180
181 /* IF (display_type = 'text/html') THEN
182 l_fyi_notification :=
183 l_string ||
184 FND_GLOBAL.LOCAL_CHR(10) ||
185 l_string1 ||
186 FND_GLOBAL.LOCAL_CHR(10) ||
187 l_string2;
188 document := document||l_appreq_notification;
189 document_type := 'text/html';
190 RETURN;
191 END IF;
192 */
193 EXCEPTION
194 WHEN OTHERS THEN
195 wf_core.context (
196 'AMWGAPP',
197 'Notify_requestor_FYI',
198 l_item_type,
199 l_item_key
200 );
201 RAISE;
202 END notify_requestor_fyi;
203
204
205 --------------------------------------------------------------------------
206 -- PROCEDURE
207 -- Notify_requestor_of Approval
208 --
209 -- PURPOSE
210 -- Generate the Approval Document for display in messages, either
211 -- text or html
212 -- IN
213 -- document_id - Item Key
214 -- display_type - either 'text/plain' or 'text/html'
215 -- document - document buffer
216 -- document_type - type of document buffer created, either 'text/plain'
217 -- or 'text/html'
218 -- OUT
219 -- USED BY
220 -- - Oracle Internal Controls Generic Apporval
221 -- HISTORY
222 -- 6/4/2003 MUMU PANDE CREATION
223 ----------------------------------------------------------------------------
224
225 PROCEDURE notify_requestor_of_approval (
226 document_id IN VARCHAR2,
227 display_type IN VARCHAR2,
228 document IN OUT NOCOPY VARCHAR2,
229 document_type IN OUT NOCOPY VARCHAR2
230 ) IS
231 l_api_name VARCHAR2 (100)
232 := g_pkg_name
233 || 'Notify_Requestor_of_approval';
234 l_CTRL_rev_id NUMBER;
235 l_CTRL_id NUMBER;
236 l_CTRL_name VARCHAR2 (240);
237 l_hyphen_pos1 NUMBER;
238 l_appr_notification VARCHAR2 (10000);
239 l_OBJECT_type VARCHAR2 (30);
240 l_item_type VARCHAR2 (80);
241 l_item_key VARCHAR2 (80);
242 l_approval_type VARCHAR2 (30);
243 l_approver VARCHAR2 (200);
244 l_note VARCHAR2 (4000);
245 l_approver_note VARCHAR2 (4000);
246 l_string VARCHAR2 (1000);
247 l_string1 VARCHAR2 (2500);
248 l_start_date DATE;
249 l_requestor_name VARCHAR2 (360);
250 l_string2 VARCHAR2 (2500);
251 l_OBJECT_meaning VARCHAR2 (80);
252 l_requestor_id NUMBER;
253 l_approval_date DATE ;
254 CURSOR c_CTRL_rec (p_CTRL_rev_id IN NUMBER) IS
255 SELECT control_id, name, creation_date,
256 requestor_id -- --s hould be a name
257 FROM amw_controls_all_vl
258 WHERE control_rev_id = p_CTRL_rev_id;
259 BEGIN
260 IF G_DEBUG THEN
261 amw_utility_pvt.debug_message (
262 l_api_name
263 || 'Entering'
264 || 'document id '
265 || document_id
266 );
267 END IF;
268 document_type := 'text/plain';
269 -- parse document_id for the ':' dividing item type name from item key value
270 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
271 -- release 2.5 version of this demo
272 l_hyphen_pos1 := INSTR (document_id, ':');
273 l_item_type :=
274 SUBSTR (document_id, 1, l_hyphen_pos1
275 - 1);
276 l_item_key := SUBSTR (document_id, l_hyphen_pos1
277 + 1);
278 l_OBJECT_type :=
279 wf_engine.getitemattrtext (
280 itemtype=> l_item_type,
281 itemkey=> l_item_key,
282 aname => 'AMW_OBJECT_TYPE'
283 );
284 l_OBJECT_meaning :=
285 wf_engine.getitemattrtext (
286 itemtype=> l_item_type,
287 itemkey=> l_item_key,
288 aname => 'AMW_APPROVAL_OBJECT_MEANING'
289 );
290
291 l_CTRL_rev_id :=
292 wf_engine.getitemattrtext (
293 itemtype=> l_item_type,
294 itemkey=> l_item_key,
295 aname => 'AMW_OBJECT_ID'
296 );
297 l_note :=
298 wf_engine.getitemattrtext (
299 itemtype=> l_item_type,
300 itemkey=> l_item_key,
301 aname => 'AMW_NOTES_FROM_REQUESTOR'
302 );
303 l_approver :=
304 wf_engine.getitemattrtext (
305 itemtype=> l_item_type,
306 itemkey=> l_item_key,
307 aname => 'AMW_APPROVER_DISPLAY_NAME'
308 );
309 OPEN c_CTRL_rec (l_CTRL_rev_id);
310 FETCH c_CTRL_rec INTO l_CTRL_id,
311 l_CTRL_name,
312 l_start_date,
313 l_requestor_id;
314 CLOSE c_CTRL_rec;
315 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_APP_SUB');
316 fnd_message.set_token ('NAME', l_CTRL_name, FALSE);
317 fnd_message.set_token ('OBJECT_TYPE', l_CTRL_name, FALSE);
318 l_string := Substr(FND_MESSAGE.Get,1,1000);
319 l_requestor_name := amw_utility_pvt.get_employee_name(l_requestor_id);
320 fnd_message.set_name ('AMW', 'AMW_WF_NTF_CTRL_REQ_INFO');
321 fnd_message.set_token ('CTRL_NAME', l_CTRL_name, FALSE);
322 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
323 fnd_message.set_token ('REQUESTOR_NAME', l_requestor_name, FALSE);
324 l_string1 := Substr(FND_MESSAGE.Get,1,2500);
325
326 /*
327 l_note := wf_engine.getitemattrtext(
328 itemtype => l_item_type
329 ,itemkey => l_item_key
330 ,aname => 'NOTE');
331
332
333 l_forwarder :=
334 wf_engine.getitemattrtext(
335 itemtype => l_item_type
336 ,itemkey => l_item_key
337 ,aname => 'AMW_FORWARD_FROM_USERNAME');
338 */
339
340
341 l_approver_note :=
342 wf_engine.getitemattrtext (
343 itemtype=> l_item_type,
344 itemkey=> l_item_key,
345 aname => 'APPROVAL_NOTE'
346 );
347 SELECT SYSDATE
348 INTO l_approval_date
349 FROM DUAL;
350
351 fnd_message.set_name('AMW', 'AMW_WF_NTF_REQUESTOR_ADDENDUM');
352 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
353 fnd_message.set_token('COMMENTS', l_approver_note, FALSE);
354 fnd_message.set_token('APPROVAL_DATE', l_approval_date , FALSE);
355 l_string2 := Substr(FND_MESSAGE.Get,1,2500);
356
357
358 -- IF (display_type = 'text/plain') THEN
359 l_appr_notification := SUBSTR(l_string
360 || fnd_global.local_chr (10)
361 || l_string1
362 || fnd_global.local_chr (10)
363 || l_string2,1,10000);
364 document := document
365 || l_appr_notification;
366 document_type := 'text/plain';
367 RETURN;
371 l_appreq_notification :=
368 -- END IF;
369
370 /* IF (display_type = 'text/html') THEN
372 l_string ||
373 FND_GLOBAL.LOCAL_CHR(10) ||
374 l_string1 ||
375 FND_GLOBAL.LOCAL_CHR(10) ||
376 l_string2;
377 document := document||l_appreq_notification;
378 document_type := 'text/html';
379 RETURN;
380 END IF;
381 */
382 EXCEPTION
383 WHEN OTHERS THEN
384 wf_core.context (
385 'AMWGAPP',
386 'Notify_Requestor_of_approval',
387 l_item_type,
388 l_item_key
389 );
390 RAISE;
391 END notify_requestor_of_approval;
392
393
394 --------------------------------------------------------------------------
395 -- PROCEDURE
396 -- Notify_requestor_of rejection
397 --
398 -- PURPOSE
399 -- Generate the Rejection Document for display in messages, either
400 -- text or html
401 -- IN
402 -- document_id - Item Key
403 -- display_type - either 'text/plain' or 'text/html'
404 -- document - document buffer
405 -- document_type - type of document buffer created, either 'text/plain'
406 -- or 'text/html'
407 -- OUT
408 -- USED BY
409 -- - Oracle Internal Controls Generic Apporval
410 -- HISTORY
411 -- 6/4/2003 MUMU PANDE CREATION
412 -------------------------------------------------------------------------------
413
414 PROCEDURE notify_requestor_of_rejection (
415 document_id IN VARCHAR2,
416 display_type IN VARCHAR2,
417 document IN OUT NOCOPY VARCHAR2,
418 document_type IN OUT NOCOPY VARCHAR2
419 ) IS
420 l_api_name VARCHAR2 (100)
421 := g_pkg_name
422 || 'Notify_Requestor_of_rejection';
423 l_CTRL_rev_id NUMBER;
424 l_CTRL_id NUMBER;
425 l_CTRL_name VARCHAR2 (240);
426 l_hyphen_pos1 NUMBER;
427 l_rej_notification VARCHAR2 (10000);
428 l_OBJECT_type VARCHAR2 (30);
429 l_item_type VARCHAR2 (80);
430 l_item_key VARCHAR2 (80);
431 l_approval_type VARCHAR2 (30);
432 l_approver VARCHAR2 (200);
433 l_note VARCHAR2 (4000);
434 l_string VARCHAR2 (1000);
435 l_string1 VARCHAR2 (2500);
436 l_start_date DATE;
437 l_requestor_name VARCHAR2 (360);
438 l_string2 VARCHAR2 (2500);
439 l_object_meaning VARCHAR2 (80);
440 l_requestor_id NUMBER;
441
442 CURSOR c_CTRL_rec (p_CTRL_rev_id IN NUMBER) IS
443 SELECT control_id, name, creation_date, requestor_id
444 FROM amw_controls_all_vl
445 WHERE Control_rev_id = p_CTRL_rev_id;
446 BEGIN
447 IF G_DEBUG THEN
448 amw_utility_pvt.debug_message (
449 l_api_name
450 || 'Entering'
451 || 'document id '
452 || document_id
453 );
454 END IF;
455 document_type := 'text/plain';
456 -- parse document_id for the ':' dividing item type name from item key value
457 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
458 -- release 2.5 version of this demo
459 l_hyphen_pos1 := INSTR (document_id, ':');
460 l_item_type :=
461 SUBSTR (document_id, 1, l_hyphen_pos1
462 - 1);
463 l_item_key := SUBSTR (document_id, l_hyphen_pos1
464 + 1);
465 l_OBJECT_type :=
466 wf_engine.getitemattrtext (
467 itemtype=> l_item_type,
468 itemkey=> l_item_key,
469 aname => 'AMW_OBJECT_TYPE'
470 );
471 l_OBJECT_meaning :=
472 wf_engine.getitemattrtext (
473 itemtype=> l_item_type,
474 itemkey=> l_item_key,
475 aname => 'AMW_APPROVAL_OBJECT_MEANING'
476 );
477
478 l_CTRL_rev_id :=
479 wf_engine.getitemattrtext (
480 itemtype=> l_item_type,
481 itemkey=> l_item_key,
482 aname => 'AMW_OBJECT_ID'
483 );
484 l_note :=
485 wf_engine.getitemattrtext (
486 itemtype=> l_item_type,
487 itemkey=> l_item_key,
491 wf_engine.getitemattrtext (
488 aname => 'AMW_NOTES_FROM_REQUESTOR'
489 );
490 l_approver :=
492 itemtype=> l_item_type,
493 itemkey=> l_item_key,
494 aname => 'AMW_APPROVER'
495 );
496 /*
497 l_requestor :=
498 wf_engine.getitemattrtext (
499 itemtype=> l_item_type,
500 itemkey=> l_item_key,
501 aname => 'AMW_REQUESTOR'
502 );
503 */
504 OPEN c_CTRL_rec (l_CTRL_rev_id);
505 FETCH c_CTRL_rec INTO l_CTRL_id,
506 l_CTRL_name,
507 l_start_date,
508 l_requestor_id;
509 CLOSE c_CTRL_rec;
510 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_REJ_SUB');
511 fnd_message.set_token ('NAME', l_CTRL_name, FALSE);
512 fnd_message.set_token ('OBJECT_TYPE', l_object_meaning, FALSE);
513 l_string := Substr(FND_MESSAGE.Get,1,1000);
514 /*
515 wf_engine.setitemattrtext (
516 itemtype=> l_item_type,
517 itemkey=> l_item_key,
518 aname => 'REJECT_SUBJECT',
519 avalue=> l_string
520 );
521 */
522 --l_requestor_name := amw_utility_pvt.get_employee_name(l_requestor_id);
523 fnd_message.set_name ('AMW', 'AMW_WF_NTF_CTRL_REQ_INFO');
524 fnd_message.set_token ('CTRL_NAME', l_CTRL_name, FALSE);
525 --fnd_message.set_token ('OWNER', l_requestor, FALSE);
526 --fnd_message.set_token ('START_DATE', l_start_date, FALSE);
527 fnd_message.set_token ('APPROVER_NAME', '-', FALSE);
528 fnd_message.set_token ('REQUESTOR_NAME', '-', FALSE);
529 --fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
530 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
531 /*
532 l_note := wf_engine.getitemattrtext(
533 itemtype => l_item_type
534 ,itemkey => l_item_key
535 ,aname => 'NOTE');
536
537
538 l_forwarder :=
539 wf_engine.getitemattrtext(
540 itemtype => l_item_type
541 ,itemkey => l_item_key
542 ,aname => 'AMW_FORWARD_FROM_USERNAME');
543 */
544
545
546 l_note :=
547 wf_engine.getitemattrtext (
548 itemtype=> l_item_type,
549 itemkey=> l_item_key,
550 aname => 'APPROVAL_NOTE'
551 );
552 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_NOTE');
553 fnd_message.set_token ('NOTES_FROM_APPROVER', l_note, FALSE);
554
555 l_string2 := SUBSTR(FND_MESSAGE.Get, 1, 2500);
556
557 l_rej_notification := SUBSTR(l_string
558 || fnd_global.local_chr (10)
559 || l_string1
560 || fnd_global.local_chr (10)
561 || l_string2,1,10000);
562 document := document
563 || l_rej_notification;
564 document_type := 'text/plain';
565 RETURN;
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 wf_core.context (
570 'AMWGAPP',
571 'Notify_requestor_of_rejection',
572 l_item_type,
573 l_item_key
574 );
575 RAISE;
576 END notify_requestor_of_rejection;
577
578
579 --------------------------------------------------------------------------
580 -- PROCEDURE
581 -- notify_approval_required
582 --
583 -- PURPOSE
584 -- Generate the Rejection Document for display in messages, either
585 -- text or html
586 -- IN
587 -- document_id - Item Key
588 -- display_type - either 'text/plain' or 'text/html'
589 -- document - document buffer
590 -- document_type - type of document buffer created, either 'text/plain'
591 -- or 'text/html'
592 -- OUT
593 -- USED BY
594 -- - Oracle Internal Controls Generic Apporval
595 -- HISTORY
596 -- 6/4/2003 MUMU PANDE CREATION
597
598
599 PROCEDURE notify_approval_required (
600 document_id IN VARCHAR2,
601 display_type IN VARCHAR2,
602 document IN OUT NOCOPY VARCHAR2,
603 document_type IN OUT NOCOPY VARCHAR2
604 ) IS
605 l_api_name VARCHAR2 (100)
606 := g_pkg_name
607 || 'Notify_approval_required';
608 l_CTRL_rev_id NUMBER;
609 l_CTRL_id NUMBER;
610 l_CTRL_name VARCHAR2 (240);
611 l_hyphen_pos1 NUMBER;
612 l_appreq_notification VARCHAR2 (10000);
613 l_OBJECT_type VARCHAR2 (30);
614 l_item_type VARCHAR2 (30);
615 l_item_key VARCHAR2 (30);
616 l_approval_type VARCHAR2 (30);
617 l_forwarder VARCHAR2 (150);
618 l_note VARCHAR2 (4000);
619 l_string VARCHAR2 (1000);
620 l_string1 VARCHAR2 (2500);
621 l_approver VARCHAR2 (200);
622 l_start_date DATE;
623 l_requestor_name VARCHAR2 (360);
624 l_string2 VARCHAR2 (2500);
625 l_approval_date VARCHAR2(30);
626 l_lookup_meaning VARCHAR2 (80);
627 l_requestor_id NUMBER;
628
629 CURSOR c_CTRL_rec (p_CTRL_rev_id IN NUMBER) IS
633 WHERE control_rev_id = p_CTRL_rev_id;
630 SELECT control_id, name, creation_date,
631 requestor_id -- needs to be the name
632 FROM amw_controls_all_vl
634 BEGIN
635 IF G_DEBUG THEN
636 amw_utility_pvt.debug_message (
637 l_api_name
638 || 'Entering'
639 || 'document id '
640 || document_id
641 );
642 END IF;
643 document_type := 'text/plain';
644 -- parse document_id for the ':' dividing item type name from item key value
645 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
646 -- release 2.5 version of this demo
647 l_hyphen_pos1 := INSTR (document_id, ':');
648 l_item_type :=
649 SUBSTR (document_id, 1, l_hyphen_pos1
650 - 1);
651 l_item_key := SUBSTR (document_id, l_hyphen_pos1
652 + 1);
653 l_OBJECT_type :=
654 wf_engine.getitemattrtext (
655 itemtype=> l_item_type,
656 itemkey=> l_item_key,
657 aname => 'AMW_OBJECT_TYPE'
658 );
659 l_CTRL_rev_id :=
660 wf_engine.getitemattrtext (
661 itemtype=> l_item_type,
662 itemkey=> l_item_key,
663 aname => 'AMW_OBJECT_ID'
664 );
665 l_note :=
666 wf_engine.getitemattrtext (
667 itemtype=> l_item_type,
668 itemkey=> l_item_key,
669 aname => 'AMW_NOTES_FROM_REQUESTOR'
670 );
671 l_approver :=
672 wf_engine.getitemattrtext (
673 itemtype=> l_item_type,
674 itemkey=> l_item_key,
675 aname => 'AMW_APPROVER_DISPLAY_NAME'
676 );
677 /*
678 l_requestor :=
679 wf_engine.getitemattrtext (
680 itemtype=> l_item_type,
681 itemkey=> l_item_key,
682 aname => 'AMW_REQUESTOR'
683 );
684 */
685 l_lookup_meaning := amw_utility_pvt.get_lookup_meaning('AMW_OBJECT_TYPE','CTRL');
686 OPEN c_CTRL_rec (l_CTRL_rev_id);
687 FETCH c_CTRL_rec INTO l_CTRL_id,
688 l_CTRL_name,
689 l_start_date,
690 l_requestor_id;
691 CLOSE c_CTRL_rec;
692 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_OF_REQ_SUB');
693 fnd_message.set_token ('NAME', l_CTRL_name, FALSE);
694 fnd_message.set_token ('OBJECT_TYPE', l_lookup_meaning, FALSE);
695 l_string := SUBSTR(fnd_message.get,1,1000);
696 /*
697 wf_engine.setitemattrtext (
698 itemtype=> l_item_type,
699 itemkey=> l_item_key,
700 aname => 'APP_SUBJECT',
701 avalue=> l_string
702 );
703 */
704 l_requestor_name := amw_utility_pvt.get_employee_name(l_requestor_id);
705 fnd_message.set_name ('AMW', 'AMW_WF_NTF_CTRL_REQ_INFO');
706 fnd_message.set_token ('CTRL_NAME', l_CTRL_name, FALSE);
707 fnd_message.set_token ('REQUESTOR_NAME', l_requestor_name, FALSE);
708 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
709 --fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
710 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
711 l_note :=
712 NVL(wf_engine.getitemattrtext (
713 itemtype=> l_item_type,
714 itemkey=> l_item_key,
715 aname => 'AMW_PREV_APPROVER_NOTE'
716 ),'-');
717 l_forwarder :=
718 NVL(wf_engine.getitemattrtext (
719 itemtype=> l_item_type,
720 itemkey=> l_item_key,
721 aname => 'AMW_PREV_APPROVER_DISP_NAME'
722 ),'-');
723 l_approval_date := NVL(to_char(wf_engine.getitemattrdate (
727
724 itemtype => l_item_type,
725 itemkey => l_item_key,
726 aname => 'AMW_APPROVAL_DATE')),'-');
728 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_ADDENDUM');
729 fnd_message.set_token ('PREV_APPROVER_NAME', l_forwarder, FALSE);
730 fnd_message.set_token ('APPROVAL_DATE', l_approval_date, FALSE);
731 fnd_message.set_token ('COMMENTS', l_note, FALSE);
732 l_string2 := SUBSTR(fnd_message.get,1,2500);
733 -- IF (display_type = 'text/plain') THEN
734 l_appreq_notification := l_string
735 || fnd_global.local_chr (10)
736 || l_string1
737 || fnd_global.local_chr (10)
738 || l_string2;
739 document := document
740 || l_appreq_notification;
741 document_type := 'text/plain';
742 RETURN;
743 -- END IF;
744
745 /* IF (display_type = 'text/html') THEN
746 l_appreq_notification :=
747 l_string ||
748 FND_GLOBAL.LOCAL_CHR(10) ||
749 l_string1 ||
750 FND_GLOBAL.LOCAL_CHR(10) ||
751 l_string2;
752 document := document||l_appreq_notification;
753 document_type := 'text/html';
754 RETURN;
755 END IF;
756 */
757
758 EXCEPTION
759 WHEN OTHERS THEN
760 wf_core.context (
761 'AMWGAPP',
762 'notify_approval_required',
763 l_item_type,
764 l_item_key
765 );
766 RAISE;
767 END notify_approval_required;
768
769
770 --------------------------------------------------------------------------
771 -- PROCEDURE
772 -- notify_appr_req_reminder
773 --
774 -- PURPOSE
775 -- Generate the Rejection Document for display in messages, either
776 -- text or html
777 -- IN
778 -- document_id - Item Key
779 -- display_type - either 'text/plain' or 'text/html'
780 -- document - document buffer
781 -- document_type - type of document buffer created, either 'text/plain'
782 -- or 'text/html'
783 -- OUT
784 -- USED BY
785 -- - Oracle Internal Controls Generic Apporval
786 -- HISTORY
787 -- 6/4/2003 MUMU PANDE CREATION
788
789 PROCEDURE notify_appr_req_reminder (
790 document_id IN VARCHAR2,
791 display_type IN VARCHAR2,
792 document IN OUT NOCOPY VARCHAR2,
793 document_type IN OUT NOCOPY VARCHAR2
794 ) IS
795 l_api_name VARCHAR2 (100)
796 := g_pkg_name
797 || 'notify_appr_req_reminder';
798 l_CTRL_rev_id NUMBER;
799 l_CTRL_id NUMBER;
800 l_CTRL_name VARCHAR2 (240);
801 l_hyphen_pos1 NUMBER;
802 l_apprem_notification VARCHAR2 (10000);
803 l_OBJECT_type VARCHAR2 (30);
804 l_item_type VARCHAR2 (80);
805 l_item_key VARCHAR2 (80);
806 l_approval_type VARCHAR2 (30);
807 l_approver VARCHAR2 (200);
808 l_note VARCHAR2 (4000);
809 l_forwarder VARCHAR2 (150);
810 l_string VARCHAR2 (1000);
811 l_string1 VARCHAR2 (2500);
812 l_start_date DATE;
813 l_requestor VARCHAR2 (360);
814 l_string2 VARCHAR2 (2500);
815 l_approval_date VARCHAR2(30);
816 l_object_meaning VARCHAR2 (80);
817 CURSOR c_CTRL_rec (p_CTRL_rev_id IN NUMBER) IS
818 SELECT control_id, name, creation_date
819 requestor_id -- needs to be the name
820 FROM amw_controls_all_vl
821 WHERE control_rev_id = p_CTRL_rev_id;
822 BEGIN
823 IF G_DEBUG THEN
824 amw_utility_pvt.debug_message (
825 l_api_name
826 || 'Entering'
827 || 'document id '
828 || document_id
829 );
830 END IF;
831 document_type := 'text/plain';
832 -- parse document_id for the ':' dividing item type name from item key value
833 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
834 -- release 2.5 version of this demo
835 l_hyphen_pos1 := INSTR (document_id, ':');
836 l_item_type :=
837 SUBSTR (document_id, 1, l_hyphen_pos1
838 - 1);
839 l_item_key := SUBSTR (document_id, l_hyphen_pos1
840 + 1);
841 l_OBJECT_type :=
842 wf_engine.getitemattrtext (
843 itemtype=> l_item_type,
844 itemkey=> l_item_key,
845 aname => 'AMW_OBJECT_TYPE'
846 );
847 l_OBJECT_meaning :=
848 wf_engine.getitemattrtext (
849 itemtype=> l_item_type,
850 itemkey=> l_item_key,
851 aname => 'AMW_APPROVAL_OBJECT_MEANING'
852 );
856 itemkey=> l_item_key,
853 l_CTRL_rev_id :=
854 wf_engine.getitemattrtext (
855 itemtype=> l_item_type,
857 aname => 'AMW_OBJECT_ID'
858 );
859 l_note :=
860 wf_engine.getitemattrtext (
861 itemtype=> l_item_type,
862 itemkey=> l_item_key,
863 aname => 'AMW_NOTES_FROM_REQUESTOR'
864 );
865 l_approver :=
866 wf_engine.getitemattrtext (
867 itemtype=> l_item_type,
868 itemkey=> l_item_key,
869 aname => 'AMW_APPROVER_DISPLAY_NAME'
870 );
871 l_requestor :=
872 wf_engine.getitemattrtext (
873 itemtype=> l_item_type,
874 itemkey=> l_item_key,
875 aname => 'AMW_REQUESTOR'
876 );
877 OPEN c_CTRL_rec (l_CTRL_rev_id);
878 FETCH c_CTRL_rec INTO l_CTRL_id,
879 l_CTRL_name,
880 l_start_date;
881 CLOSE c_CTRL_rec;
882 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_OF_REQ_SUB');
883 fnd_message.set_token ('CTRL_NAME', l_CTRL_name, FALSE);
884 fnd_message.set_token ('OBJECT_TYPE', l_object_meaning, FALSE);
885 l_string := SUBSTR(fnd_message.get,1,1000);
886 /*
887 wf_engine.setitemattrtext (
888 itemtype=> l_item_type,
889 itemkey=> l_item_key,
890 aname => 'APP_SUBJECT',
891 avalue=> l_string
892 );
893 */
894 fnd_message.set_name ('AMW', 'AMW_WF_NTF_CTRL_REQ_INFO');
895 fnd_message.set_token ('CTRL_NAME', l_CTRL_name, FALSE);
896 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
897 fnd_message.set_token ('REQUESTOR_NAME', l_approver, FALSE);
898 --fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
899
900
901 l_note :=
902 NVL(wf_engine.getitemattrtext (
903 itemtype=> l_item_type,
904 itemkey=> l_item_key,
905 aname => 'AMW_PREV_APPROVER_NOTE'
906 ),'-');
907 l_forwarder :=
908 NVL(wf_engine.getitemattrtext (
909 itemtype=> l_item_type,
910 itemkey=> l_item_key,
911 aname => 'AMW_PREV_APPROVER_DISP_NAME'
912 ),'-');
913 l_approval_date := NVL(to_char(wf_engine.getitemattrdate (
914 itemtype => l_item_type,
915 itemkey => l_item_key,
916 aname => 'AMW_APPROVAL_DATE')),'-');
917
918 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
919 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_ADDENDUM');
920 fnd_message.set_token ('PREV_APPROVER_NAME', l_forwarder, FALSE);
921 fnd_message.set_token ('APPROVAL_DATE', l_approval_date, FALSE);
922 fnd_message.set_token ('COMMENTS', l_note, FALSE);
923 l_string2 := SUBSTR(fnd_message.get,1,2500);
924 /*
925 l_note := wf_engine.getitemattrtext(
926 itemtype => l_item_type
927 ,itemkey => l_item_key
928 ,aname => 'NOTE');
929
930
931 l_forwarder :=
932 wf_engine.getitemattrtext(
933 itemtype => l_item_type
934 ,itemkey => l_item_key
935 ,aname => 'AMW_FORWARD_FROM_USERNAME');
936 */
937 -- IF (display_type = 'text/plain') THEN
938 l_apprem_notification := l_string
939 || fnd_global.local_chr (10)
940 || l_string1
941 || fnd_global.local_chr (10)
942 || l_string2;
943 document := document
944 || l_apprem_notification;
945 document_type := 'text/plain';
946 RETURN;
947 -- END IF;
948
949 /* IF (display_type = 'text/html') THEN
950 l_appreq_notification :=
951 l_string ||
952 FND_GLOBAL.LOCAL_CHR(10) ||
953 l_string1 ||
954 FND_GLOBAL.LOCAL_CHR(10) ||
955 l_string2;
956 document := document||l_appreq_notification;
957 document_type := 'text/html';
958 RETURN;
959 END IF;
960 */
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 wf_core.context (
965 'AMWGAPP',
966 'notify_appr_req_reminder',
967 l_item_type,
968 l_item_key
969 );
970 RAISE;
971 END notify_appr_req_reminder;
972
973
974 ---------------------------------------------------------------------
975 -- PROCEDURE
976 -- Set_CTRL_OBJECT_details
977 --
978 --
979 -- PURPOSE
980 -- This Procedure will set all the item attribute details
981 --
982 --
983 -- IN
984 --
985 --
986 -- OUT
987 --
988 -- Used By Activities
989 --
990 -- NOTES
991 --
992 --
993 --
994 -- HISTORY
995 -- 6/4/2003 MUMU PANDE CREATION
996 -- End of Comments
997 --------------------------------------------------------------------
998 PROCEDURE set_CTRL_OBJECT_details (
999 itemtype IN VARCHAR2,
1000 itemkey IN VARCHAR2,
1001 actid IN NUMBER,
1002 funcmode IN VARCHAR2,
1003 resultout OUT NOCOPY VARCHAR2
1004 ) IS
1005 l_OBJECT_id NUMBER;
1006 l_OBJECT_type VARCHAR2 (30) := 'CTRL';
1007 l_approval_type VARCHAR2 (30) := 'OBJECT';
1008 l_return_status VARCHAR2 (1);
1009 l_msg_count NUMBER;
1010 l_msg_data VARCHAR2 (4000);
1011 l_error_msg VARCHAR2 (4000);
1012 l_CTRL_name VARCHAR2 (240);
1013 l_full_name VARCHAR2 (60);
1014 l_start_date DATE ;
1015 l_CTRL_id NUMBER;
1016 l_approver VARCHAR2 (200);
1017 l_string VARCHAR2 (3000);
1018 l_lookup_meaning VARCHAR2(240);
1019
1023 WHERE control_rev_id = p_CTRL_rev_id;
1020 CURSOR c_CTRL_rec (p_CTRL_rev_id IN NUMBER) IS
1021 SELECT control_id, name, creation_date
1022 FROM amw_controls_all_vl
1024 BEGIN
1025 fnd_msg_pub.initialize;
1026 l_OBJECT_id :=
1027 wf_engine.getitemattrnumber (
1028 itemtype=> itemtype,
1029 itemkey=> itemkey,
1030 aname => 'AMW_OBJECT_ID'
1031 );
1032
1033 IF (funcmode = 'RUN') THEN
1034 -- OPen cursor here and get the values
1035 OPEN c_CTRL_rec (l_object_id);
1036 FETCH c_CTRL_rec INTO l_CTRL_id,
1037 l_CTRL_name,
1038 l_start_date;
1039 CLOSE c_CTRL_rec;
1040 l_lookup_meaning := amw_utility_pvt.get_lookup_meaning('AMW_OBJECT_TYPE','CTRL');
1041 --- set all the subjects here
1042 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_FYI_SUB');
1043 fnd_message.set_token (
1044 'NAME',
1045 l_CTRL_name,
1046 FALSE
1047 );
1048 fnd_message.set_token (
1049 'OBJECT_TYPE',
1050 l_lookup_meaning,
1051 FALSE
1052 );
1053
1054 l_string := Substr(FND_MESSAGE.Get,1,1000);
1055 wf_engine.setitemattrtext (
1056 itemtype=> itemtype,
1057 itemkey=> itemkey,
1058 aname => 'FYI_SUBJECT',
1059 avalue=> l_string
1060 );
1061
1062 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_APP_SUB');
1063 fnd_message.set_token ('NAME', l_CTRL_name, FALSE );
1064 fnd_message.set_token (
1065 'OBJECT_TYPE',
1066 l_lookup_meaning,
1067 FALSE
1068 );
1069
1070 l_string := Substr(FND_MESSAGE.Get,1,1000);
1071 wf_engine.setitemattrtext (
1072 itemtype=> itemtype,
1073 itemkey=> itemkey,
1074 aname => 'APRV_SUBJECT',
1075 avalue=> l_string
1076 );
1077 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_REJ_SUB');
1078 fnd_message.set_token (
1079 'NAME',
1080 l_CTRL_name,
1081 FALSE
1082 );
1083 fnd_message.set_token (
1084 'OBJECT_TYPE',
1085 l_lookup_meaning,
1086 FALSE
1087 );
1088
1089 l_string := Substr(FND_MESSAGE.Get,1,1000);
1090 wf_engine.setitemattrtext (
1091 itemtype=> itemtype,
1092 itemkey=> itemkey,
1093 aname => 'REJECT_SUBJECT',
1094 avalue=> l_string
1095 );
1096 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_OF_REQ_SUB');
1097 fnd_message.set_token (
1098 'NAME',
1099 l_CTRL_name,
1100 FALSE
1101 );
1102 fnd_message.set_token (
1103 'OBJECT_TYPE',
1104 l_lookup_meaning,
1105 FALSE
1106 );
1107
1108 l_string := Substr(FND_MESSAGE.Get,1,1000);
1109 wf_engine.setitemattrtext (
1110 itemtype=> itemtype,
1111 itemkey=> itemkey,
1112 aname => 'APP_SUBJECT',
1113 avalue=> l_string
1114 );
1115
1116 wf_engine.setitemattrtext (
1117 itemtype=> itemtype,
1118 itemkey=> itemkey,
1119 aname => 'AMW_APPROVAL_OBJECT_MEANING',
1120 avalue=> l_lookup_meaning
1121 );
1122 wf_engine.setitemattrtext (
1123 itemtype=> itemtype,
1124 itemkey=> itemkey,
1125 aname => 'AMW_APPROVAL_OBJECT_NAME',
1126 avalue=> l_CTRL_name
1127 );
1128 resultout := 'COMPLETE:SUCCESS';
1129
1130 ELSE
1131 fnd_msg_pub.count_and_get (
1132 p_encoded=> fnd_api.g_false,
1133 p_count=> l_msg_count,
1134 p_data=> l_msg_data
1135 );
1136 amw_gen_approval_pvt.handle_err (
1137 p_itemtype=> itemtype,
1138 p_itemkey=> itemkey,
1139 p_msg_count=> l_msg_count, -- Number of error Messages
1140 p_msg_data=> l_msg_data,
1141 p_attr_name=> 'AMW_ERROR_MSG',
1142 x_error_msg=> l_error_msg
1143 );
1144 wf_core.context (
1145 'amw_gen_approval_pvt',
1146 'Set_OBJECT_Details',
1147 itemtype,
1148 itemkey,
1149 actid,
1150 l_error_msg
1151 );
1152 -- RAISE FND_API.G_EXC_ERROR;
1153 resultout := 'COMPLETE:ERROR';
1154 END IF;
1155
1156 --
1157 -- CANCEL mode
1158 --
1159 IF (funcmode = 'CANCEL') THEN
1160 resultout := 'COMPLETE:';
1161 RETURN;
1162 END IF;
1163
1164 --
1165 -- TIMEOUT mode
1166 --
1167 IF (funcmode = 'TIMEOUT') THEN
1168 resultout := 'COMPLETE:';
1169 RETURN;
1170 END IF;
1171 --
1172
1176 'amw_CTRL_Approval_pvt',
1173 EXCEPTION
1174 WHEN fnd_api.g_exc_error THEN
1175 wf_core.context (
1177 'Set_CTRL_OBJECT_Details',
1178 itemtype,
1179 itemkey,
1180 actid,
1181 funcmode,
1182 l_error_msg
1183 );
1184 RAISE;
1185 WHEN OTHERS THEN
1186 fnd_msg_pub.count_and_get (
1187 p_encoded=> fnd_api.g_false,
1188 p_count=> l_msg_count,
1189 p_data=> l_msg_data
1190 );
1191 RAISE;
1192 END set_CTRL_OBJECT_details;
1193
1194
1195 ---------------------------------------------------------------------
1196 -- PROCEDURE
1197 -- Update_CTRL_Status
1198 --
1199 --
1200 -- PURPOSE
1201 -- This Procedure will update the status
1202 --
1203 --
1204 -- IN
1205 --
1206 --
1207 -- OUT
1208 --
1209 -- Used By Activities
1210 --
1211 -- NOTES
1212 --
1213 --
1214 --
1215 -- HISTORY
1216 -- 6/4/2003 MUMU PANDE CREATION
1217 -- 6/17/2003 KARTHI MUTHUSWAMY Added code to update CTRL based on approval status
1218 -- 6/25/2003 KARTHI MUTHUSWAMY Fixed funcmode = 'RUN' logic not returning resultout
1219 -- End of Comments
1220 -------------------------------------------------------------------
1221
1222 PROCEDURE update_CTRL_status (
1223 itemtype IN VARCHAR2,
1224 itemkey IN VARCHAR2,
1225 actid IN NUMBER,
1226 funcmode IN VARCHAR2,
1227 resultout OUT NOCOPY VARCHAR2
1228 ) IS
1229 l_status_code VARCHAR2 (30);
1230 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1231 l_msg_count NUMBER;
1232 l_msg_data VARCHAR2 (4000);
1233 l_api_name CONSTANT VARCHAR2 (30) := 'Update_CTRL_Status';
1234 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
1235 || '.'
1236 || l_api_name;
1237 l_update_status VARCHAR2 (12);
1238 l_error_msg VARCHAR2 (4000);
1239 l_object_version_number NUMBER;
1240 l_CTRL_rev_id NUMBER;
1241 l_validation_level NUMBER := fnd_api.g_valid_level_full;
1242 l_approver VARCHAR2(320);
1243 l_requestor_id NUMBER;
1244 l_approver_id NUMBER;
1245 l_old_appr_CTRL_rev_id NUMBER;
1246
1247 CURSOR c_old_appr_CTRL(p_CTRL_rev_id In NUMBER) IS
1248 SELECT CTRL2.Control_REV_ID
1249 FROM amw_controls_b CTRL1 , amw_controls_b CTRL2
1250 WHERE CTRL1.Control_id = CTRL2.control_id
1251 AND CTRL1.control_rev_id = p_CTRL_rev_id
1252 AND CTRL2.curr_approved_flag= 'Y' and CTRL2.latest_revision_flag = 'N' ;
1253
1254
1255
1256
1257 BEGIN
1258 SAVEPOINT update_CTRL_status ;
1259 IF ( funcmode = 'RUN' ) THEN
1260 -- Item attribute UPDATE_GEN_STATUS will be'set to APPROVED'if the Object is approved
1261 -- and 'REJECTED' if the object is rejected.
1262 l_update_status :=
1263 wf_engine.getitemattrtext (
1264 itemtype=> itemtype,
1265 itemkey=> itemkey,
1266 aname => 'UPDATE_GEN_STATUS'
1267 );
1268 l_approver :=
1269 wf_engine.getitemattrtext (
1270 itemtype=> itemtype,
1271 itemkey=> itemkey,
1272 aname => 'AMW_APPROVER'
1273 );
1274 l_approver_id :=
1275 wf_engine.getitemattrtext (
1276 itemtype=> itemtype,
1277 itemkey=> itemkey,
1278 aname => 'AMW_APPROVER_ID'
1279 );
1280 l_requestor_id :=
1281 wf_engine.getitemattrtext (
1282 itemtype=> itemtype,
1283 itemkey=> itemkey,
1284 aname => 'AMW_REQUESTOR_ID'
1285 );
1286
1287 l_object_version_number :=
1288 wf_engine.getitemattrnumber (
1289 itemtype=> itemtype,
1290 itemkey=> itemkey,
1291 aname => 'AMW_OBJECT_VERSION_NUMBER'
1292 );
1293 l_CTRL_rev_id :=
1294 wf_engine.getitemattrnumber (
1295 itemtype=> itemtype,
1296 itemkey=> itemkey,
1297 aname => 'AMW_OBJECT_ID'
1298 );
1299
1300 OPEN c_old_appr_CTRL (l_CTRL_rev_id) ;
1301 FETCH c_old_appr_CTRL INTO l_old_appr_CTRL_rev_id;
1302 CLOSE c_old_appr_CTRL;
1303
1307 set
1304 IF ( l_update_status = 'APPROVED' ) THEN
1305 -- Update the status of the CTRL object to 'A' -- Approved
1306 update amw_controls_b
1308 approval_status = 'A',
1309 object_version_number = object_version_number+1,
1310 curr_approved_flag = 'Y',
1311 latest_revision_flag ='Y' ,
1312 approval_date = SYSDATE
1313 where control_rev_id = l_CTRL_rev_id
1314 and object_version_number = l_object_version_number;
1315
1316
1317 IF l_old_appr_CTRL_rev_id IS NOT NULL THEN
1318
1319 update amw_controls_b
1320 set
1321 object_version_number = object_version_number+1,
1322 curr_approved_flag = 'N',
1323 latest_revision_flag ='N' ,
1324 end_date = SYSDATE
1325 where control_rev_id = l_old_appr_CTRL_rev_id;
1326
1327 END IF;
1328 ELSIF ( l_update_status = 'REJECTED' ) THEN
1329 -- Update the status of the CTRL object to 'R' -- Rejected
1330 IF l_old_appr_CTRL_rev_id IS NOT NULL THEN
1331 UPDATE amw_controls_b
1332 SET object_version_number = object_version_number + 1,
1333 latest_revision_flag = 'Y'
1334 WHERE control_rev_id = l_old_appr_CTRL_rev_id;
1335
1336 UPDATE amw_controls_b
1337 SET approval_status = 'R',
1338 object_version_number = object_version_number + 1,
1339 curr_approved_flag = 'N',
1340 latest_revision_flag = 'N',
1341 end_date = SYSDATE
1342 WHERE control_rev_id = l_CTRL_rev_id
1343 AND object_version_number = l_object_version_number;
1344 ELSE
1345 UPDATE amw_controls_b
1346 SET approval_status = 'R',
1347 object_version_number = object_version_number + 1,
1348 curr_approved_flag = 'N',
1349 latest_revision_flag = 'Y',
1350 end_date = SYSDATE
1351 WHERE control_rev_id = l_CTRL_rev_id
1352 AND object_version_number = l_object_version_number;
1353 END IF;
1354 /* mpande commented 11/25/2003 and added the above code to take care of first time rejection
1355 -- Update the status of the CTRL object to 'R' -- Rejected
1356 update amw_controls_b
1357 set
1358 approval_status = 'R',
1359 object_version_number = object_version_number+1,
1360 curr_approved_flag = 'N',
1361 latest_revision_flag ='N' ,
1362 end_date = SYSDATE
1363 where control_rev_id = l_CTRL_rev_id
1364 and object_version_number = l_object_version_number ;
1365
1366 IF l_old_appr_CTRL_rev_id IS NOT NULL THEN
1367 update amw_controls_b
1368 set
1369 object_version_number = object_version_number+1,
1370 latest_revision_flag ='Y'
1371 where control_rev_id = l_old_appr_CTRL_rev_id;
1372
1373 END IF;
1374 */
1375 ELSE
1376 -- Update the status of the CTRL object to 'D' -- Draft
1377 update amw_controls_b
1378 set
1379 approval_status = 'D',
1380 object_version_number = object_version_number+1
1381 --curr_approved_flag = 'N',
1382 --latest_revision_flag ='Y'
1383 where control_rev_id = l_CTRL_rev_id
1384 and object_version_number = l_object_version_number ;
1385 END IF;
1386
1387
1388 /**************************IS THIS REQUIRED********************/
1389 /*
1390 --amw_CTRLs_pkg.update_CTRL (
1391 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1392 IF G_DEBUG THEN
1393 amw_utility_pvt.debug_message(l_full_name || ' failed to update CTRL to status ' || l_status_code);
1394 END IF;
1395 amw_gen_approval_pvt.handle_err (
1396 p_itemtype=> itemtype,
1397 p_itemkey=> itemkey,
1398 p_msg_count=> l_msg_count, -- Number of error Messages
1399 p_msg_data=> l_msg_data,
1400 p_attr_name=> 'AMW_ERROR_MSG',
1401 x_error_msg=> l_error_msg
1402 );
1403 resultout := 'COMPLETE:ERROR';
1404 ELSE
1405 resultout := 'COMPLETE:SUCCESS';
1406 END IF;
1407 */
1408 /**************************IS THIS REQUIRED********************/
1409
1410 resultout := 'COMPLETE:SUCCESS' ;
1411
1412 return ;
1413 END IF;
1414
1415 --
1416 -- CANCEL mode
1417 --
1418 IF (funcmode = 'CANCEL') THEN
1419 resultout := 'COMPLETE:';
1420 RETURN;
1421 END IF;
1422
1423 --
1424 -- TIMEOUT mode
1425 --
1426 IF (funcmode = 'TIMEOUT') THEN
1427 resultout := 'COMPLETE:';
1428 RETURN;
1429 END IF;
1430
1431 /*
1432 fnd_msg_pub.count_and_get (
1433 p_encoded=> fnd_api.g_false,
1434 p_count=> l_msg_count,
1435 p_data=> l_msg_data
1436 );
1437 IF G_DEBUG THEN
1438 amw_utility_pvt.debug_message (
1439 l_full_name
1440 || ': l_return_status'
1441 || l_return_status
1442 );
1443 END IF;
1444 */
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447 ROLLBACK TO update_CTRL_status ;
1448 resultout := 'COMPLETE:ERROR';
1449 fnd_msg_pub.count_and_get (
1450 p_encoded=> fnd_api.g_false,
1451 p_count=> l_msg_count,
1452 p_data=> l_msg_data
1453 );
1454 amw_gen_approval_pvt.handle_err (
1455 p_itemtype=> itemtype,
1456 p_itemkey=> itemkey,
1457 p_msg_count=> l_msg_count, -- Number of error Messages
1458 p_msg_data=> l_msg_data,
1459 p_attr_name=> 'AMW_ERROR_MSG',
1460 x_error_msg=> l_error_msg
1461 );
1462 --RAISE;
1463 END update_CTRL_status;
1464 END amw_CTRL_approval_pvt;