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