[Home] [Help]
PACKAGE BODY: APPS.AMW_AP_APPROVAL_PVT
Source
1 PACKAGE BODY amw_ap_approval_pvt AS
2 /* $Header: amwvaapb.pls 120.0 2005/05/31 21:51:22 appldev noship $ */
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- amw_AP_Approval_PVT
8 --
9 -- PURPOSE
10 -- This package contains all transactions to be done for
11 -- AP Approvals in Oracle Internal APs
12 --
13 -- HISTORY
14 -- 6/4/2003 MUMU PANDE CREATION
15 -- 6/25/2003 KARTHI MUTHUSWAMY Modified update_AP_status()
16 -- 7/2/2003 mpande Updated for All Message and formatting
17 g_pkg_name CONSTANT VARCHAR2 (30) := 'amw_AP_Approval_PVT';
18 g_file_name CONSTANT VARCHAR2 (15) := 'amwvaapb.pls';
19 g_ap_mode CONSTANT VARCHAR2 (15) := 'WORKFLOW';
20 g_debug CONSTANT 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 CONSTANT VARCHAR2 (61)
47 := g_pkg_name || 'Notify_Requestor_FYI';
48 l_ap_rev_id NUMBER;
49 l_ap_id NUMBER;
50 l_ap_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_ap_rec (p_ap_rev_id IN NUMBER) IS
68 SELECT audit_procedure_id, NAME, creation_date, requestor_id
69 FROM amw_audit_procedures_vl
70 WHERE audit_procedure_rev_id = p_ap_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_ap_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_ap_rec (l_ap_rev_id);
115 FETCH c_ap_rec
116 INTO l_ap_id, l_ap_name, l_start_date, l_requestor_id;
117 CLOSE c_ap_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_ap_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_AP_REQ_INFO');
133 fnd_message.set_token ('AP_NAME', l_ap_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 /*
138 l_note := wf_engine.getitemattrtext(
139 itemtype => l_item_type
140 ,itemkey => l_item_key
141 ,aname => 'NOTE');
142
143
144 l_forwarder :=
145 wf_engine.getitemattrtext(
146 itemtype => l_item_type
147 ,itemkey => l_item_key
148 ,aname => 'AMW_FORWARD_FROM_USERNAME');
149 */
150 -- IF (display_type = 'text/plain') THEN
151 l_fyi_notification :=
152 SUBSTR ( l_string
153 || fnd_global.local_chr (10)
154 || l_string1
155 || fnd_global.local_chr (10)
156 || l_string2,
157 1,
158 10000
159 );
160 document := document || l_fyi_notification;
161 document_type := 'text/plain';
162 RETURN;
163 -- END IF;
164
165 /* IF (display_type = 'text/html') THEN
166 l_fyi_notification :=
167 l_string ||
168 FND_GLOBAL.LOCAL_CHR(10) ||
169 l_string1 ||
170 FND_GLOBAL.LOCAL_CHR(10) ||
171 l_string2;
172 document := document||l_appreq_notification;
173 document_type := 'text/html';
174 RETURN;
175 END IF;
176 */
177 EXCEPTION
178 WHEN OTHERS THEN
179 wf_core.CONTEXT ('AMWGAPP',
180 'Notify_requestor_FYI',
181 l_item_type,
182 l_item_key
183 );
184 RAISE;
185 END notify_requestor_fyi;
186 --------------------------------------------------------------------------
187 -- PROCEDURE
188 -- Notify_requestor_of Approval
189 --
190 -- PURPOSE
191 -- Generate the Approval Document for display in messages, either
192 -- text or html
193 -- IN
194 -- document_id - Item Key
195 -- display_type - either 'text/plain' or 'text/html'
196 -- document - document buffer
197 -- document_type - type of document buffer created, either 'text/plain'
198 -- or 'text/html'
199 -- OUT
200 -- USED BY
201 -- - Oracle Internal Controls Generic Apporval
202 -- HISTORY
203 -- 6/4/2003 MUMU PANDE CREATION
204 ----------------------------------------------------------------------------
205 PROCEDURE notify_requestor_of_approval (
206 document_id IN VARCHAR2,
207 display_type IN VARCHAR2,
208 document IN OUT NOCOPY VARCHAR2,
209 document_type IN OUT NOCOPY VARCHAR2
210 ) IS
211 l_api_name CONSTANT VARCHAR2 (100)
212 := g_pkg_name || 'Notify_Requestor_of_approval';
213 l_ap_rev_id NUMBER;
214 l_ap_id NUMBER;
215 l_ap_name VARCHAR2 (240);
216 l_hyphen_pos1 NUMBER;
217 l_appr_notification VARCHAR2 (10000);
218 l_object_type VARCHAR2 (30);
219 l_item_type VARCHAR2 (80);
220 l_item_key VARCHAR2 (80);
221 l_approval_type VARCHAR2 (30);
222 l_approver VARCHAR2 (200);
223 l_note VARCHAR2 (4000);
224 l_approver_note VARCHAR2 (4000);
225 l_string VARCHAR2 (1000);
229 l_string2 VARCHAR2 (2500);
226 l_string1 VARCHAR2 (2500);
227 l_start_date DATE;
228 l_requestor_name VARCHAR2 (360);
230 l_object_meaning VARCHAR2 (80);
231 l_requestor_id NUMBER;
232 l_approval_date DATE;
233 CURSOR c_ap_rec (p_ap_rev_id IN NUMBER) IS
234 SELECT audit_procedure_id, NAME, creation_date,
235 requestor_id -- --s hould be a name
236 FROM amw_audit_procedures_vl
237 WHERE audit_procedure_rev_id = p_ap_rev_id;
238 BEGIN
239 IF g_debug THEN
240 amw_utility_pvt.debug_message ( l_api_name
241 || 'Entering'
242 || 'document id '
243 || document_id
244 );
245 END IF;
246 document_type := 'text/plain';
247 -- parse document_id for the ':' dividing item type name from item key value
248 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
249 -- release 2.5 version of this demo
250 l_hyphen_pos1 := INSTR (document_id, ':');
251 l_item_type := SUBSTR (document_id, 1, l_hyphen_pos1 - 1);
252 l_item_key := SUBSTR (document_id, l_hyphen_pos1 + 1);
253 l_object_type :=
254 wf_engine.getitemattrtext (itemtype => l_item_type,
255 itemkey => l_item_key,
256 aname => 'AMW_OBJECT_TYPE'
257 );
258 l_object_meaning :=
259 wf_engine.getitemattrtext (itemtype => l_item_type,
260 itemkey => l_item_key,
261 aname => 'AMW_APPROVAL_OBJECT_MEANING'
262 );
263 l_ap_rev_id :=
264 wf_engine.getitemattrtext (itemtype => l_item_type,
265 itemkey => l_item_key,
266 aname => 'AMW_OBJECT_ID'
267 );
268 l_note :=
269 wf_engine.getitemattrtext (itemtype => l_item_type,
270 itemkey => l_item_key,
271 aname => 'AMW_NOTES_FROM_REQUESTOR'
272 );
273 l_approver :=
274 wf_engine.getitemattrtext (itemtype => l_item_type,
275 itemkey => l_item_key,
276 aname => 'AMW_APPROVER_DISPLAY_NAME'
277 );
278 OPEN c_ap_rec (l_ap_rev_id);
279 FETCH c_ap_rec
280 INTO l_ap_id, l_ap_name, l_start_date, l_requestor_id;
281 CLOSE c_ap_rec;
285 l_string := SUBSTR (fnd_message.get, 1, 1000);
282 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_APP_SUB');
283 fnd_message.set_token ('NAME', l_ap_name, FALSE);
284 fnd_message.set_token ('OBJECT_TYPE', l_ap_name, FALSE);
286 l_requestor_name :=
287 amw_utility_pvt.get_employee_name (l_requestor_id);
288 fnd_message.set_name ('AMW', 'AMW_WF_NTF_AP_REQ_INFO');
289 fnd_message.set_token ('AP_NAME', l_ap_name, FALSE);
290 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
291 fnd_message.set_token ('REQUESTOR_NAME', l_requestor_name, FALSE);
292 l_string1 := SUBSTR (fnd_message.get, 1, 2500);
293 /*
294 l_note := wf_engine.getitemattrtext(
295 itemtype => l_item_type
296 ,itemkey => l_item_key
297 ,aname => 'NOTE');
298
299
300 l_forwarder :=
301 wf_engine.getitemattrtext(
302 itemtype => l_item_type
303 ,itemkey => l_item_key
304 ,aname => 'AMW_FORWARD_FROM_USERNAME');
305 */
306 l_approver_note :=
307 wf_engine.getitemattrtext (itemtype => l_item_type,
308 itemkey => l_item_key,
309 aname => 'APPROVAL_NOTE'
310 );
311 SELECT SYSDATE
312 INTO l_approval_date
313 FROM DUAL;
314 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_ADDENDUM');
315 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
316 fnd_message.set_token ('COMMENTS', l_approver_note, FALSE);
317 fnd_message.set_token ('APPROVAL_DATE', l_approval_date, FALSE);
318 l_string2 := SUBSTR (fnd_message.get, 1, 2500);
319 -- IF (display_type = 'text/plain') THEN
320 l_appr_notification :=
321 SUBSTR ( l_string
322 || fnd_global.local_chr (10)
323 || l_string1
324 || fnd_global.local_chr (10)
325 || l_string2,
326 1,
327 10000
328 );
329 document := document || l_appr_notification;
330 document_type := 'text/plain';
331 RETURN;
332 -- END IF;
333
334 /* IF (display_type = 'text/html') THEN
335 l_appreq_notification :=
336 l_string ||
337 FND_GLOBAL.LOCAL_CHR(10) ||
338 l_string1 ||
339 FND_GLOBAL.LOCAL_CHR(10) ||
340 l_string2;
341 document := document||l_appreq_notification;
342 document_type := 'text/html';
343 RETURN;
344 END IF;
345 */
346 EXCEPTION
347 WHEN OTHERS THEN
348 wf_core.CONTEXT ('AMWGAPP',
349 'Notify_Requestor_of_approval',
350 l_item_type,
351 l_item_key
352 );
353 RAISE;
354 END notify_requestor_of_approval;
355 --------------------------------------------------------------------------
356 -- PROCEDURE
357 -- Notify_requestor_of rejection
358 --
359 -- PURPOSE
360 -- Generate the Rejection Document for display in messages, either
361 -- text or html
362 -- IN
363 -- document_id - Item Key
364 -- display_type - either 'text/plain' or 'text/html'
365 -- document - document buffer
366 -- document_type - type of document buffer created, either 'text/plain'
367 -- or 'text/html'
368 -- OUT
369 -- USED BY
370 -- - Oracle Internal Controls Generic Apporval
371 -- HISTORY
372 -- 6/4/2003 MUMU PANDE CREATION
373 -------------------------------------------------------------------------------
374 PROCEDURE notify_requestor_of_rejection (
375 document_id IN VARCHAR2,
376 display_type IN VARCHAR2,
377 document IN OUT NOCOPY VARCHAR2,
378 document_type IN OUT NOCOPY VARCHAR2
379 ) IS
380 l_api_name CONSTANT VARCHAR2 (100)
381 := g_pkg_name || 'Notify_Requestor_of_rejection';
382 l_ap_rev_id NUMBER;
383 l_ap_id NUMBER;
384 l_ap_name VARCHAR2 (240);
385 l_hyphen_pos1 NUMBER;
386 l_rej_notification VARCHAR2 (10000);
387 l_object_type VARCHAR2 (30);
388 l_item_type VARCHAR2 (80);
389 l_item_key VARCHAR2 (80);
390 l_approval_type VARCHAR2 (30);
391 l_approver VARCHAR2 (200);
392 l_note VARCHAR2 (4000);
393 l_string VARCHAR2 (1000);
394 l_string1 VARCHAR2 (2500);
395 l_start_date DATE;
396 l_requestor_name VARCHAR2 (360);
397 l_string2 VARCHAR2 (2500);
398 l_object_meaning VARCHAR2 (80);
399 l_requestor_id NUMBER;
400 CURSOR c_ap_rec (p_ap_rev_id IN NUMBER) IS
401 SELECT audit_procedure_id, NAME, creation_date, requestor_id
402 FROM amw_audit_procedures_vl
403 WHERE audit_procedure_rev_id = p_ap_rev_id;
407 || 'Entering'
404 BEGIN
405 IF g_debug THEN
406 amw_utility_pvt.debug_message ( l_api_name
408 || 'document id '
409 || document_id
410 );
411 END IF;
412 document_type := 'text/plain';
413 -- parse document_id for the ':' dividing item type name from item key value
414 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
415 -- release 2.5 version of this demo
416 l_hyphen_pos1 := INSTR (document_id, ':');
417 l_item_type := SUBSTR (document_id, 1, l_hyphen_pos1 - 1);
418 l_item_key := SUBSTR (document_id, l_hyphen_pos1 + 1);
419 l_object_type :=
420 wf_engine.getitemattrtext (itemtype => l_item_type,
421 itemkey => l_item_key,
422 aname => 'AMW_OBJECT_TYPE'
423 );
424 l_object_meaning :=
425 wf_engine.getitemattrtext (itemtype => l_item_type,
426 itemkey => l_item_key,
427 aname => 'AMW_APPROVAL_OBJECT_MEANING'
428 );
429 l_ap_rev_id :=
430 wf_engine.getitemattrtext (itemtype => l_item_type,
431 itemkey => l_item_key,
432 aname => 'AMW_OBJECT_ID'
433 );
434 l_note :=
435 wf_engine.getitemattrtext (itemtype => l_item_type,
436 itemkey => l_item_key,
437 aname => 'AMW_NOTES_FROM_REQUESTOR'
438 );
439 l_approver :=
440 wf_engine.getitemattrtext (itemtype => l_item_type,
441 itemkey => l_item_key,
442 aname => 'AMW_APPROVER'
443 );
444 /*
445 l_requestor :=
446 wf_engine.getitemattrtext (
447 itemtype=> l_item_type,
448 itemkey=> l_item_key,
449 aname => 'AMW_REQUESTOR'
450 );
451 */
452 OPEN c_ap_rec (l_ap_rev_id);
453 FETCH c_ap_rec
454 INTO l_ap_id, l_ap_name, l_start_date, l_requestor_id;
455 CLOSE c_ap_rec;
456 fnd_message.set_name ('AMW', 'AMW_WF_NTF_REQUESTOR_REJ_SUB');
457 fnd_message.set_token ('NAME', l_ap_name, FALSE);
458 fnd_message.set_token ('OBJECT_TYPE', l_object_meaning, FALSE);
459 l_string := SUBSTR (fnd_message.get, 1, 1000);
460 /*
461 wf_engine.setitemattrtext (
462 itemtype=> l_item_type,
463 itemkey=> l_item_key,
464 aname => 'REJECT_SUBJECT',
465 avalue=> l_string
466 );
467 */
468 l_requestor_name :=
469 amw_utility_pvt.get_employee_name (l_requestor_id);
470 fnd_message.set_name ('AMW', 'AMW_WF_NTF_AP_REQ_INFO');
471 fnd_message.set_token ('AP_NAME', l_ap_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',
518 RAISE;
515 l_item_type,
516 l_item_key
517 );
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 CONSTANT VARCHAR2 (100)
545 := g_pkg_name || 'Notify_approval_required';
546 l_ap_rev_id NUMBER;
547 l_ap_id NUMBER;
548 l_ap_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_id NUMBER;
562 l_string2 VARCHAR2 (2500);
563 l_approval_date VARCHAR2 (30);
564 l_lookup_meaning VARCHAR2 (80);
565 l_requestor_name VARCHAR2 (360);
566 CURSOR c_ap_rec (p_ap_rev_id IN NUMBER) IS
567 SELECT audit_procedure_id, NAME, creation_date,
568 requestor_id -- needs to be the name
569 FROM amw_audit_procedures_vl
570 WHERE audit_procedure_rev_id = p_ap_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, ':');
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 :=
587 wf_engine.getitemattrtext (itemtype => l_item_type,
588 itemkey => l_item_key,
589 aname => 'AMW_OBJECT_TYPE'
590 );
591 l_ap_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 );
609 itemtype=> l_item_type,
606 /*
607 l_requestor :=
608 wf_engine.getitemattrtext (
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', 'AP');
616 OPEN c_ap_rec (l_ap_rev_id);
617 FETCH c_ap_rec
618 INTO l_ap_id, l_ap_name, l_start_date, l_requestor_id;
619 CLOSE c_ap_rec;
620 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_OF_REQ_SUB');
621 fnd_message.set_token ('NAME', l_ap_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_AP_REQ_INFO');
635 fnd_message.set_token ('AP_NAME', l_ap_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',
694 'notify_approval_required',
695 l_item_type,
696 l_item_key
697 );
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 CONSTANT VARCHAR2 (100)
725 := g_pkg_name || 'notify_appr_req_reminder';
726 l_ap_rev_id NUMBER;
730 l_apprem_notification VARCHAR2 (10000);
727 l_ap_id NUMBER;
728 l_ap_name VARCHAR2 (240);
729 l_hyphen_pos1 NUMBER;
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_ap_rec (p_ap_rev_id IN NUMBER) IS
746 SELECT audit_procedure_id, NAME,
747 creation_date requestor_id -- needs to be the name
748 FROM amw_audit_procedures_vl
749 WHERE audit_procedure_rev_id = p_ap_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_ap_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'
784 );
785 l_approver :=
786 wf_engine.getitemattrtext (itemtype => l_item_type,
787 itemkey => l_item_key,
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_ap_rec (l_ap_rev_id);
796 FETCH c_ap_rec
797 INTO l_ap_id, l_ap_name, l_start_date;
798 CLOSE c_ap_rec;
799 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_OF_REQ_SUB');
800 fnd_message.set_token ('AP_NAME', l_ap_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_AP_REQ_INFO');
812 fnd_message.set_token ('AP_NAME', l_ap_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 :=
835 )
832 NVL (TO_CHAR (wf_engine.getitemattrdate (itemtype => l_item_type,
833 itemkey => l_item_key,
834 aname => 'AMW_APPROVAL_DATE'
836 ),
837 '-'
838 );
839 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_ADDENDUM');
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);
843 l_string2 := SUBSTR (fnd_message.get, 1, 2500);
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;
889 END notify_appr_req_reminder;
890 ---------------------------------------------------------------------
891 -- PROCEDURE
892 -- Set_AP_OBJECT_details
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_ap_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 CONSTANT VARCHAR2 (30) := 'AP';
923 l_approval_type CONSTANT 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_ap_name VARCHAR2 (240);
929 l_full_name VARCHAR2 (60);
930 l_start_date DATE;
931 l_ap_id NUMBER;
932 l_approver VARCHAR2 (200);
933 l_string VARCHAR2 (3000);
934 l_lookup_meaning VARCHAR2 (240);
935 CURSOR c_ap_rec (p_ap_rev_id IN NUMBER) IS
936 SELECT audit_procedure_id, NAME, creation_date
937 FROM amw_audit_procedures_vl
938 WHERE audit_procedure_rev_id = p_ap_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_ap_rec (l_object_id);
949 FETCH c_ap_rec
950 INTO l_ap_id, l_ap_name, l_start_date;
951 CLOSE c_ap_rec;
952 l_lookup_meaning :=
953 amw_utility_pvt.get_lookup_meaning ('AMW_OBJECT_TYPE', 'AP');
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_ap_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_ap_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_ap_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',
980 avalue => l_string
981 );
982 fnd_message.set_name ('AMW', 'AMW_WF_NTF_APPROVER_OF_REQ_SUB');
983 fnd_message.set_token ('NAME', l_ap_name, FALSE);
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,
993 aname => 'AMW_APPROVAL_OBJECT_MEANING',
994 avalue => l_lookup_meaning
995 );
996 wf_engine.setitemattrtext (itemtype => itemtype,
997 itemkey => itemkey,
998 aname => 'AMW_APPROVAL_OBJECT_NAME',
999 avalue => l_ap_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,
1016 wf_core.CONTEXT ('amw_gen_approval_pvt',
1013 p_attr_name => 'AMW_ERROR_MSG',
1014 x_error_msg => l_error_msg
1015 );
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_APApproval_pvt',
1044 'Set_AP_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_ap_object_details;
1059 ---------------------------------------------------------------------
1060 -- PROCEDURE
1061 -- Update_AP_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
1084 -------------------------------------------------------------------
1081 -- 6/17/2003 KARTHI MUTHUSWAMY Added code to update AP based on approval status
1082 -- 6/25/2003 KARTHI MUTHUSWAMY Fixed funcmode = 'RUN' logic not returning resultout
1083 -- End of Comments
1085 PROCEDURE update_ap_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);
1094 l_msg_count NUMBER;
1095 l_msg_data VARCHAR2 (4000);
1096 l_api_name CONSTANT VARCHAR2 (30) := 'Update_AP_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_ap_rev_id NUMBER;
1103 l_validation_level NUMBER;
1104 l_approver VARCHAR2 (320);
1105 l_requestor_id NUMBER;
1106 l_approver_id NUMBER;
1107 l_old_appr_ap_rev_id NUMBER;
1108 l_approval_date DATE;
1109 l_ap_id NUMBER;
1110 CURSOR c_old_appr_ap (p_ap_rev_id IN NUMBER) IS
1111 SELECT ap2.audit_procedure_rev_id
1112 FROM amw_audit_procedures_b ap1, amw_audit_procedures_b ap2
1113 WHERE ap1.audit_procedure_id = ap2.audit_procedure_id
1114 AND ap1.audit_procedure_rev_id = p_ap_rev_id
1115 AND ap2.curr_approved_flag = 'Y'
1116 AND ap2.latest_revision_flag = 'N';
1117 BEGIN
1118 SAVEPOINT update_ap_status;
1119 l_return_status := fnd_api.g_ret_sts_success;
1120 l_validation_level := fnd_api.g_valid_level_full;
1121 IF (funcmode = 'RUN') THEN
1122 -- Item attribute UPDATE_GEN_STATUS will be'set to APPROVED'if the Object is approved
1123 -- and 'REJECTED' if the object is rejected.
1124 l_update_status :=
1125 wf_engine.getitemattrtext (itemtype => itemtype,
1126 itemkey => itemkey,
1127 aname => 'UPDATE_GEN_STATUS'
1128 );
1129 l_approver :=
1130 wf_engine.getitemattrtext (itemtype => itemtype,
1131 itemkey => itemkey,
1132 aname => 'AMW_APPROVER'
1133 );
1134 l_approver_id :=
1135 wf_engine.getitemattrtext (itemtype => itemtype,
1136 itemkey => itemkey,
1137 aname => 'AMW_APPROVER_ID'
1138 );
1139 l_requestor_id :=
1140 wf_engine.getitemattrtext (itemtype => itemtype,
1141 itemkey => itemkey,
1142 aname => 'AMW_REQUESTOR_ID'
1143 );
1144 l_object_version_number :=
1145 wf_engine.getitemattrnumber (itemtype => itemtype,
1146 itemkey => itemkey,
1147 aname => 'AMW_OBJECT_VERSION_NUMBER'
1148 );
1149 l_ap_rev_id :=
1150 wf_engine.getitemattrnumber (itemtype => itemtype,
1151 itemkey => itemkey,
1152 aname => 'AMW_OBJECT_ID'
1153 );
1154 OPEN c_old_appr_ap (l_ap_rev_id);
1155 FETCH c_old_appr_ap
1156 INTO l_old_appr_ap_rev_id;
1157 CLOSE c_old_appr_ap;
1158
1159 IF (l_update_status = 'APPROVED') THEN
1160 -- Update the status of the AP object to 'A' -- Approved
1161 l_approval_date := SYSDATE;
1162 UPDATE amw_audit_procedures_b
1163 SET approval_status = 'A',
1164 object_version_number = object_version_number + 1,
1165 curr_approved_flag = 'Y',
1166 latest_revision_flag = 'Y',
1167 approval_date = l_approval_date
1168 WHERE audit_procedure_rev_id = l_ap_rev_id
1169 AND object_version_number = l_object_version_number;
1170 IF l_old_appr_ap_rev_id IS NOT NULL THEN
1171 UPDATE amw_audit_procedures_b
1172 SET object_version_number = object_version_number + 1,
1173 curr_approved_flag = 'N',
1174 latest_revision_flag = 'N',
1175 end_date = l_approval_date
1176 WHERE audit_procedure_rev_id = l_old_appr_ap_rev_id;
1177 END IF;
1178 -- AMW.D Update the control associations (set approval_date)
1179 select audit_procedure_id into l_ap_id
1180 from amw_audit_procedures_b
1181 where audit_procedure_rev_id = l_ap_rev_id;
1182 update amw_ap_associations
1183 set approval_date = l_approval_date
1184 where audit_procedure_id = l_ap_id
1185 and object_type = 'CTRL'
1186 and approval_date is null;
1187
1188 update amw_ap_associations
1189 set deletion_approval_date = l_approval_date
1190 where audit_procedure_id = l_ap_id
1191 and object_type = 'CTRL'
1195 -- Update the status of the AP object to 'R' -- Rejected
1192 and deletion_date is not null
1193 and deletion_approval_date is null;
1194 ELSIF (l_update_status = 'REJECTED') THEN
1196 IF l_old_appr_ap_rev_id IS NOT NULL THEN
1197 UPDATE amw_audit_procedures_b
1198 SET object_version_number = object_version_number + 1,
1199 latest_revision_flag = 'Y'
1200 WHERE audit_procedure_rev_id = l_old_appr_ap_rev_id;
1201 UPDATE amw_audit_procedures_b
1202 SET approval_status = 'R',
1203 object_version_number = object_version_number + 1,
1204 curr_approved_flag = 'N',
1205 latest_revision_flag = 'N',
1206 end_date = SYSDATE
1207 WHERE audit_procedure_rev_id = l_ap_rev_id
1208 AND object_version_number = l_object_version_number;
1209 ELSE
1210 UPDATE amw_audit_procedures_b
1211 SET approval_status = 'R',
1212 object_version_number = object_version_number + 1,
1213 curr_approved_flag = 'N',
1214 latest_revision_flag = 'Y',
1215 end_date = SYSDATE
1216 WHERE audit_procedure_rev_id = l_ap_rev_id
1217 AND object_version_number = l_object_version_number;
1218 END IF;
1219 ELSE
1220 -- Update the status of the AP object to 'D' -- Draft
1221 UPDATE amw_audit_procedures_b
1222 SET approval_status = 'D',
1223 object_version_number = object_version_number + 1
1224 --curr_approved_flag = 'N',
1225 --latest_revision_flag ='Y'
1226 WHERE audit_procedure_rev_id = l_ap_rev_id
1227 AND object_version_number = l_object_version_number;
1228 END IF;
1229 /**************************IS THIS REQUIRED********************/
1230 /*
1231 --amw_APs_pkg.update_AP (
1232 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1233 IF G_DEBUG THEN
1234 amw_utility_pvt.debug_message(l_full_name || ' failed to update AP to status ' || l_status_code);
1235 END IF;
1236 amw_gen_approval_pvt.handle_err (
1237 p_itemtype=> itemtype,
1238 p_itemkey=> itemkey,
1239 p_msg_count=> l_msg_count, -- Number of error Messages
1240 p_msg_data=> l_msg_data,
1241 p_attr_name=> 'AMW_ERROR_MSG',
1242 x_error_msg=> l_error_msg
1243 );
1244 resultout := 'COMPLETE:ERROR';
1245 ELSE
1246 resultout := 'COMPLETE:SUCCESS';
1247 END IF;
1248 */
1249 /**************************IS THIS REQUIRED********************/
1250 resultout := 'COMPLETE:SUCCESS';
1251 RETURN;
1252 END IF;
1253 --
1254 -- CANCEL mode
1255 --
1256 IF (funcmode = 'CANCEL') THEN
1257 resultout := 'COMPLETE:';
1258 RETURN;
1259 END IF;
1260 --
1261 -- TIMEOUT mode
1262 --
1263 IF (funcmode = 'TIMEOUT') THEN
1264 resultout := 'COMPLETE:';
1265 RETURN;
1266 END IF;
1267 /*
1268 fnd_msg_pub.count_and_get (
1269 p_encoded=> fnd_api.g_false,
1270 p_count=> l_msg_count,
1271 p_data=> l_msg_data
1272 );
1273 IF G_DEBUG THEN
1274 amw_utility_pvt.debug_message (
1275 l_full_name
1276 || ': l_return_status'
1277 || l_return_status
1278 );
1279 END IF;
1280 */
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 ROLLBACK TO update_ap_status;
1284 resultout := 'COMPLETE:ERROR';
1285 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1286 p_count => l_msg_count,
1287 p_data => l_msg_data
1288 );
1289 amw_gen_approval_pvt.handle_err
1290 (p_itemtype => itemtype,
1291 p_itemkey => itemkey,
1292 p_msg_count => l_msg_count,
1293 -- Number of error Messages
1294 p_msg_data => l_msg_data,
1295 p_attr_name => 'AMW_ERROR_MSG',
1296 x_error_msg => l_error_msg
1297 );
1298 --RAISE;
1299 END update_ap_status;
1300 END amw_ap_approval_pvt;