[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_APPROVALS_PVT
Source
1 PACKAGE BODY AHL_MC_Approvals_PVT AS
2 /* $Header: AHLVMWFB.pls 120.0 2005/05/26 10:59:35 appldev noship $ */
3
4 -- Define cursor to check MC with particular mc_header_id, object_version_number exists
5 CURSOR check_mc_exists(p_mc_header_id in number, p_object_version_number in number)
6 IS
7 SELECT mc_header_id, name
8 FROM ahl_mc_headers_b
9 WHERE mc_header_id = p_mc_header_id and
10 object_version_number = nvl(p_object_version_number, object_version_number);
11
12 PROCEDURE SET_ACTIVITY_DETAILS
13 (
14 itemtype IN VARCHAR2
15 ,itemkey IN VARCHAR2
16 ,actid IN NUMBER
17 ,funcmode IN VARCHAR2
18 ,resultout OUT NOCOPY VARCHAR2
19 )
20 IS
21
22 l_object_id NUMBER;
23 l_object VARCHAR2(30) := 'MC';
24 l_approval_type VARCHAR2(30) := 'CONCEPT';
25 l_object_details AHL_GENERIC_APRV_PVT.OBJRECTYP;
26 l_approval_rule_id NUMBER;
27 l_approver_seq NUMBER;
28 l_return_status VARCHAR2(1);
29 l_msg_count NUMBER;
30 l_msg_data VARCHAR2(4000);
31 l_subject VARCHAR2(500);
32 l_error_msg VARCHAR2(2000);
33 l_mc_header_id NUMBER := 0;
34
35 l_mc_header_rec check_mc_exists%rowtype;
36
37 BEGIN
38
39 fnd_msg_pub.initialize;
40
41 l_return_status := FND_API.g_ret_sts_success;
42
43 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
44 THEN
45 fnd_log.string
46 (
47 fnd_log.level_procedure,
48 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details.begin',
49 'At the start of PLSQL procedure'
50 );
51 END IF;
52
53 l_object_id := wf_engine.getitemattrnumber
54 (
55 itemtype => itemtype
56 ,itemkey => itemkey
57 ,aname => 'OBJECT_ID'
58 );
59
60 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
61 THEN
62 fnd_log.string
63 (
64 fnd_log.level_statement,
65 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
66 'OBJECT_ID='||l_object_id
67 );
68 END IF;
69
70 l_object_details.operating_unit_id := NULL;
71 l_object_details.priority := NULL;
72
73 --
74 -- RUN mode
75 --
76
77 IF (funcmode = 'RUN')
78 THEN
79 OPEN check_mc_exists(l_object_id, null);
80 FETCH check_mc_exists into l_mc_header_rec;
81 IF check_mc_exists%NOTFOUND
82 THEN
83 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
84 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
85 fnd_message.set_token('NAME', l_mc_header_rec.NAME, false);
86 fnd_msg_pub.add;
87 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
88 THEN
89 fnd_log.message
90 (
91 fnd_log.level_exception,
92 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
93 false
94 );
95 END IF;
96 RAISE FND_API.G_EXC_ERROR;
97 END IF;
98 CLOSE check_mc_exists;
99
100 fnd_message.set_name('AHL', 'AHL_MC_NTF_FORWARD_SUBJECT');
101 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
102 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
103 l_subject := fnd_message.get;
104
105 wf_engine.setitemattrtext
106 (
107 itemtype => itemtype
108 ,itemkey => itemkey
109 ,aname => 'FORWARD_SUBJECT'
110 ,avalue => l_subject
111 );
112
113 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
114 THEN
115 fnd_log.string
116 (
117 fnd_log.level_statement,
118 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
119 'FORWARD_SUBJECT='||l_subject
120 );
121 END IF;
122
123 fnd_message.set_name('AHL', 'AHL_MC_NTF_APPROVAL_SUBJECT');
124 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
125 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
126 l_subject := fnd_message.get;
127
128 wf_engine.setitemattrtext
129 (
130 itemtype => itemtype
131 ,itemkey => itemkey
132 ,aname => 'APPROVAL_SUBJECT'
133 ,avalue => l_subject
134 );
135
136 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
137 THEN
138 fnd_log.string
139 (
140 fnd_log.level_statement,
141 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
142 'APPROVAL_SUBJECT='||l_subject
143 );
144 END IF;
145
146 fnd_message.set_name('AHL', 'AHL_MC_NTF_REJECT_SUBJECT');
147 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
148 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
149 l_subject := fnd_message.get;
150
151 wf_engine.setitemattrtext
152 (
153 itemtype => itemtype
154 ,itemkey => itemkey
155 ,aname => 'REJECT_SUBJECT'
156 ,avalue => l_subject
157 );
158
159 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
160 THEN
161 fnd_log.string
162 (
163 fnd_log.level_statement,
164 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
165 'REJECT_SUBJECT='||l_subject
166 );
167 END IF;
168
169 fnd_message.set_name('AHL', 'AHL_MC_NTF_APPROVED_SUBJECT');
170 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
171 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
172 l_subject := fnd_message.get;
173
174 wf_engine.setitemattrtext
175 (
176 itemtype => itemtype
177 ,itemkey => itemkey
178 ,aname => 'APPROVED_SUBJECT'
179 ,avalue => l_subject
180 );
181
182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
183 THEN
184 fnd_log.string
185 (
186 fnd_log.level_statement,
187 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
188 'APPROVED_SUBJECT='||l_subject
189 );
190 END IF;
191
192 fnd_message.set_name('AHL', 'AHL_MC_NTF_FINAL_SUBJECT');
193 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
194 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
195 l_subject := fnd_message.get;
196
197 wf_engine.setitemattrtext
198 (
199 itemtype => itemtype
200 ,itemkey => itemkey
201 ,aname => 'FINAL_SUBJECT'
202 ,avalue => l_subject
203 );
204
205 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
206 THEN
207 fnd_log.string
208 (
209 fnd_log.level_statement,
210 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
211 'FINAL_SUBJECT='||l_subject
212 );
213 END IF;
214
215 fnd_message.set_name('AHL', 'AHL_MC_NTF_REMIND_SUBJECT');
216 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
217 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
218 l_subject := fnd_message.get;
219
220 wf_engine.setitemattrtext
221 (
222 itemtype => itemtype
223 ,itemkey => itemkey
224 ,aname => 'REMIND_SUBJECT'
225 ,avalue => l_subject
226 );
227
228 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
229 THEN
230 fnd_log.string
231 (
232 fnd_log.level_statement,
233 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
234 'REMIND_SUBJECT='||l_subject
235 );
236 END IF;
237
238 fnd_message.set_name('AHL', 'AHL_MC_NTF_ERROR_SUBJECT');
239 fnd_message.set_token('MC_HEADER_ID', l_mc_header_rec.mc_header_id, false);
240 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
241 l_subject := fnd_message.get;
242
243 wf_engine.setitemattrtext
244 (
245 itemtype => itemtype
246 ,itemkey => itemkey
247 ,aname => 'ERROR_SUBJECT'
248 ,avalue => l_subject
249 );
250
251 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
252 THEN
253 fnd_log.string
254 (
255 fnd_log.level_statement,
256 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
257 'ERROR_SUBJECT='||l_subject
258 );
259 END IF;
260
261 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
262 THEN
263 fnd_log.string
264 (
265 fnd_log.level_statement,
266 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
267 'Calling AHL_GENERIC_APRV_PVT.GET_APPROVAL_DETAILS'
268 );
269 END IF;
270
271 AHL_GENERIC_APRV_PVT.GET_APPROVAL_DETAILS
272 (
273 p_object => l_object,
274 p_approval_type => l_approval_type,
275 p_object_details => l_object_details,
276 x_approval_rule_id => l_approval_rule_id,
277 x_approver_seq => l_approver_seq,
278 x_return_status => l_return_status
279 );
280
281 IF l_return_status = FND_API.g_ret_sts_success
282 THEN
283 wf_engine.setitemattrnumber
284 (
285 itemtype => itemtype,
286 itemkey => itemkey,
287 aname => 'RULE_ID',
288 avalue => l_approval_rule_id
289 );
290
291 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
292 THEN
293 fnd_log.string
294 (
295 fnd_log.level_statement,
296 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
297 'RULE_ID='||l_approval_rule_id
298 );
299 END IF;
300
301 wf_engine.setitemattrnumber
302 (
303 itemtype => itemtype,
304 itemkey => itemkey,
305 aname => 'APPROVER_SEQ',
306 avalue => l_approver_seq
307 );
308
309 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
310 THEN
311 fnd_log.string
312 (
313 fnd_log.level_statement,
314 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
315 'APPROVER_SEQ='||l_approver_seq
316 );
317 END IF;
318
319 resultout := 'COMPLETE:SUCCESS';
320 RETURN;
321
322 ELSE
323 RAISE FND_API.G_EXC_ERROR;
324 END IF;
325 END IF;
326
327 --
328 -- CANCEL mode
329 --
330
331 IF (funcmode = 'CANCEL')
332 THEN
333 resultout := 'COMPLETE:';
334 RETURN;
335 END IF;
336
337 --
338 -- TIMEOUT mode
339 --
340
341 IF (funcmode = 'TIMEOUT')
342 THEN
343 resultout := 'COMPLETE:';
344 RETURN;
345 END IF;
346
347 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
348 THEN
349 fnd_log.string
350 (
351 fnd_log.level_procedure,
352 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details.end',
353 'At the end of PLSQL procedure'
354 );
355 END IF;
356
357 EXCEPTION
358 WHEN FND_API.G_EXC_ERROR THEN
359 FND_MSG_PUB.Count_And_Get
360 (
361 p_encoded => FND_API.G_FALSE,
362 p_count => l_msg_count,
363 p_data => l_msg_data
364 );
365 AHL_GENERIC_APRV_PVT.handle_error
366 (
367 p_itemtype => itemtype ,
368 p_itemkey => itemkey ,
369 p_msg_count => l_msg_count,
370 p_msg_data => l_msg_data ,
371 p_attr_name => 'ERROR_MSG',
372 x_error_msg => l_error_msg
373 );
374 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
375 THEN
376 fnd_log.string
377 (
378 fnd_log.level_exception,
379 'ahl.plsql.AHL_MC_Approvals_PVT.Set_Activity_Details',
380 l_error_msg
381 );
382 END IF;
383 wf_core.context
384 (
385 'AHL_MC_APPROVAL_PVT',
386 'SET_ACTIVITY_DETAILS',
387 itemtype,
388 itemkey,
389 actid,
390 funcmode,
391 l_error_msg
392 );
393 resultout := 'COMPLETE:ERROR';
394
395 WHEN OTHERS THEN
396 wf_core.context
397 (
398 'AHL_MC_APPROVAL_PVT',
399 'SET_ACTIVITY_DETAILS',
400 itemtype,
401 itemkey,
402 actid,
403 'Unexpected Error!'
404 );
405 RAISE;
406
407 END SET_ACTIVITY_DETAILS;
408
409 PROCEDURE NTF_FORWARD_FYI
410 (
411 document_id IN VARCHAR2
412 ,display_type IN VARCHAR2
413 ,document IN OUT NOCOPY VARCHAR2
414 ,document_type IN OUT NOCOPY VARCHAR2
415 )
416 IS
417
418 l_hyphen_pos1 NUMBER;
419 l_object VARCHAR2(30);
420 l_item_type VARCHAR2(30);
421 l_item_key VARCHAR2(30);
422 l_approver VARCHAR2(30);
423 l_body VARCHAR2(3500);
424 l_object_id NUMBER;
425
429
426 l_msg_count NUMBER;
427 l_msg_data VARCHAR2(4000);
428 l_error_msg VARCHAR2(2000);
430 l_mc_header_rec check_mc_exists%rowtype;
431
432 BEGIN
433
434 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
435 THEN
436 fnd_log.string
437 (
438 fnd_log.level_procedure,
439 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FORWARD_FYI.begin',
440 'At the start of PLSQL procedure'
441 );
442 END IF;
443
444 document_type := 'text/plain';
445
446 -- parse document_id for the ':' dividing item type name from item key value
447 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
448 -- release 2.5 version of this demo
449
450 l_hyphen_pos1 := INSTR(document_id, ':');
451 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
452 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
453
454 l_object := wf_engine.getitemattrtext
455 (
456 itemtype => l_item_type
457 ,itemkey => l_item_key
458 ,aname => 'OBJECT_TYPE'
459 );
460
461 l_object_id := wf_engine.getitemattrNumber
462 (
463 itemtype => l_item_type
464 ,itemkey => l_item_key
465 ,aname => 'OBJECT_ID'
466 );
467
468 l_approver := wf_engine.getitemattrtext
469 (
470 itemtype => l_item_type
471 ,itemkey => l_item_key
472 ,aname => 'APPROVER'
473 );
474
475 OPEN check_mc_exists(l_object_id, null);
476 FETCH check_mc_exists into l_mc_header_rec;
477
478 IF check_mc_exists%NOTFOUND
479 THEN
480 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
481 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
482 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
483 fnd_msg_pub.add;
484 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
485 THEN
486 fnd_log.message
487 (
488 fnd_log.level_exception,
489 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FORWARD_FYI',
490 false
491 );
492 END IF;
493 RAISE FND_API.G_EXC_ERROR;
494 ELSE
495 fnd_message.set_name('AHL', 'AHL_MC_NTF_FWD_FYI_FWD');
496 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id ,false);
497 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
498 fnd_message.set_token('APPR_NAME',l_approver, false);
499 l_body := fnd_message.get;
500 END IF;
501 CLOSE check_mc_exists;
502
503 document := document || l_body;
504
505 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
506 THEN
507 fnd_log.string
508 (
509 fnd_log.level_statement,
510 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FORWARD_FYI',
511 'document='||document
512 );
513
514 fnd_log.string
515 (
516 fnd_log.level_statement,
517 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FORWARD_FYI.end',
518 'At the end of PLSQL procedure'
519 );
520 END IF;
521
522 RETURN;
523
524 EXCEPTION
525 WHEN FND_API.G_EXC_ERROR THEN
526 FND_MSG_PUB.Count_And_Get
527 (
528 p_encoded => FND_API.G_FALSE,
529 p_count => l_msg_count,
530 p_data => l_msg_data
531 );
532 AHL_GENERIC_APRV_PVT.Handle_Error
533 (
534 p_itemtype => l_item_type ,
535 p_itemkey => l_item_key ,
536 p_msg_count => l_msg_count,
537 p_msg_data => l_msg_data ,
538 p_attr_name => 'ERROR_MSG',
539 x_error_msg => l_error_msg
540 );
541 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
542 THEN
543 fnd_log.string
544 (
545 fnd_log.level_exception,
546 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FORWARD_FYI',
547 l_error_msg
548 );
549 END IF;
550 wf_core.context
551 (
552 'AHL_MC_APPROVAL_PVT',
553 'NTF_FORWARD_FYI',
554 l_item_type,
555 l_item_key,
556 l_error_msg
557 );
558 RAISE;
559
560 WHEN OTHERS THEN
561 wf_core.context(
562 'AHL_MC_APPROVAL_PVT',
563 'NTF_FORWARD_FYI',
564 l_item_type,
565 l_item_key
566 );
567 RAISE;
568
569 END NTF_FORWARD_FYI;
570
571 PROCEDURE NTF_APPROVED_FYI
572 (
573 document_id IN VARCHAR2
574 ,display_type IN VARCHAR2
575 ,document IN OUT NOCOPY VARCHAR2
576 ,document_type IN OUT NOCOPY VARCHAR2
577 )
578 IS
579
580 l_hyphen_pos1 NUMBER;
581 l_object VARCHAR2(30);
582 l_item_type VARCHAR2(30);
583 l_item_key VARCHAR2(30);
584 l_approver VARCHAR2(30);
585 l_body VARCHAR2(3500);
586 l_object_id NUMBER;
587
588 l_msg_count NUMBER;
589 l_msg_data VARCHAR2(4000);
590 l_error_msg VARCHAR2(2000);
591
592 l_mc_header_rec check_mc_exists%rowtype;
593
594 BEGIN
595
596 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
597 THEN
598 fnd_log.string
599 (
600 fnd_log.level_procedure,
601 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVED_FYI.begin',
602 'At the start of PLSQL procedure'
603 );
604 END IF;
605
606 document_type := 'text/plain';
607
608 -- parse document_id for the ':' dividing item type name from item key value
609 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
610 -- release 2.5 version of this demo
611
615
612 l_hyphen_pos1 := INSTR(document_id, ':');
613 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
614 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
616 l_object := wf_engine.getitemattrtext
617 (
618 itemtype => l_item_type
619 ,itemkey => l_item_key
620 ,aname => 'OBJECT_TYPE'
621 );
622
623 l_object_id := wf_engine.getitemattrNumber
624 (
625 itemtype => l_item_type
626 ,itemkey => l_item_key
627 ,aname => 'OBJECT_ID'
628 );
629
630 l_approver := wf_engine.getitemattrtext
631 (
632 itemtype => l_item_type
633 ,itemkey => l_item_key
634 ,aname => 'APPROVER'
635 );
636
637 OPEN check_mc_exists(l_object_id, null);
638 FETCH check_mc_exists into l_mc_header_rec;
639
640 IF check_mc_exists%NOTFOUND
641 THEN
642 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
643 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
644 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
645 fnd_msg_pub.add;
646 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
647 THEN
648 fnd_log.message
649 (
650 fnd_log.level_exception,
651 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVED_FYI',
652 false
653 );
654 END IF;
655 RAISE FND_API.G_EXC_ERROR;
656 ELSE
657 fnd_message.set_name('AHL', 'AHL_MC_NTF_FWD_FYI_APPRVD');
658 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id ,false);
659 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
660 fnd_message.set_token('APPR_NAME',l_approver, false);
661 l_body := fnd_message.get;
662 END IF;
663 CLOSE check_mc_exists;
664
665 document := document || l_body;
666
667 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
668 THEN
669 fnd_log.string
670 (
671 fnd_log.level_statement,
672 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVED_FYI',
673 'document='||document
674 );
675
676 fnd_log.string
677 (
678 fnd_log.level_statement,
679 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVED_FYI.end',
680 'At the end of PLSQL procedure'
681 );
682 END IF;
683
684 RETURN;
685
686 EXCEPTION
687 WHEN FND_API.G_EXC_ERROR THEN
688 FND_MSG_PUB.Count_And_Get
689 (
690 p_encoded => FND_API.G_FALSE,
691 p_count => l_msg_count,
692 p_data => l_msg_data
693 );
694 AHL_GENERIC_APRV_PVT.Handle_Error
695 (
696 p_itemtype => l_item_type ,
697 p_itemkey => l_item_key ,
698 p_msg_count => l_msg_count,
699 p_msg_data => l_msg_data ,
700 p_attr_name => 'ERROR_MSG',
701 x_error_msg => l_error_msg
702 );
703 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
704 THEN
705 fnd_log.string
706 (
707 fnd_log.level_exception,
708 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVED_FYI',
709 l_error_msg
710 );
711 END IF;
712 wf_core.context
713 (
714 'AHL_MC_APPROVAL_PVT',
715 'NTF_APPROVED_API',
716 l_item_type,
717 l_item_key,
718 l_error_msg
719 );
720 RAISE;
721 WHEN OTHERS THEN
722 wf_core.context
723 (
724 'AHL_MC_APPROVAL_PVT',
725 'NTF_APPROVED_API',
726 l_item_type,
727 l_item_key
728 );
729 RAISE;
730
731 END NTF_APPROVED_FYI;
732
733 PROCEDURE NTF_FINAL_APPROVAL_FYI
734 (
735 document_id IN VARCHAR2
736 ,display_type IN VARCHAR2
737 ,document IN OUT NOCOPY VARCHAR2
738 ,document_type IN OUT NOCOPY VARCHAR2
739 )
740 IS
741
742 l_hyphen_pos1 NUMBER;
743 l_object VARCHAR2(30);
744 l_item_type VARCHAR2(30);
745 l_item_key VARCHAR2(30);
746 l_body VARCHAR2(3500);
747 l_object_id NUMBER;
748 l_msg_count NUMBER;
749 l_msg_data VARCHAR2(4000);
750 l_error_msg VARCHAR2(2000);
751
752 l_mc_header_rec check_mc_exists%rowtype;
753
754 BEGIN
755
756 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
757 THEN
758 fnd_log.string
759 (
760 fnd_log.level_procedure,
761 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FINAL_APPROVAL_FYI.begin',
762 'At the start of PLSQL procedure'
763 );
764 END IF;
765
766 document_type := 'text/plain';
767
768 -- parse document_id for the ':' dividing item type name from item key value
769 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
770 -- release 2.5 version of this demo
771
772 l_hyphen_pos1 := INSTR(document_id, ':');
773 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
774 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
775
776 l_object := wf_engine.getitemattrtext
777 (
778 itemtype => l_item_type
779 ,itemkey => l_item_key
780 ,aname => 'OBJECT_TYPE'
781 );
782
783 l_object_id := wf_engine.getitemattrNumber
784 (
785 itemtype => l_item_type
786 ,itemkey => l_item_key
787 ,aname => 'OBJECT_ID'
788 );
789
790 OPEN check_mc_exists(l_object_id, null);
791 FETCH check_mc_exists into l_mc_header_rec;
792
793 IF check_mc_exists%NOTFOUND
794 THEN
795 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
796 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
800 THEN
797 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
798 fnd_msg_pub.add;
799 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
801 fnd_log.message
802 (
803 fnd_log.level_exception,
804 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FINAL_APPROVAL_FYI',
805 false
806 );
807 END IF;
808 RAISE FND_API.G_EXC_ERROR;
809 ELSE
810 fnd_message.set_name('AHL', 'AHL_MC_NTF_FWD_FYI_FINAL');
811 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id ,false);
812 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
813 l_body := fnd_message.get;
814 END IF;
815 CLOSE check_mc_exists;
816
817 document := document || l_body;
818
819 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
820 THEN
821 fnd_log.string
822 (
823 fnd_log.level_statement,
824 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FINAL_APPROVAL_FYI',
825 'document='||document
826 );
827
828 fnd_log.string
829 (
830 fnd_log.level_statement,
831 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FINAL_APPROVAL_FYI.end',
832 'At the end of PLSQL procedure'
833 );
834 END IF;
835
836 RETURN;
837
838 EXCEPTION
839 WHEN FND_API.G_EXC_ERROR THEN
840 FND_MSG_PUB.Count_And_Get
841 (
842 p_encoded => FND_API.G_FALSE,
843 p_count => l_msg_count,
844 p_data => l_msg_data
845 );
846 AHL_GENERIC_APRV_PVT.Handle_Error
847 (
848 p_itemtype => l_item_type ,
849 p_itemkey => l_item_key ,
850 p_msg_count => l_msg_count,
851 p_msg_data => l_msg_data ,
852 p_attr_name => 'ERROR_MSG',
853 x_error_msg => l_error_msg
854 );
855 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
856 THEN
857 fnd_log.string
858 (
859 fnd_log.level_exception,
860 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_FINAL_APPROVAL_FYI',
861 l_error_msg
862 );
863 END IF;
864 wf_core.context
865 (
866 'AHL_MC_APPROVAL_PVT',
867 'NTF_FINAL_APPROVAL_FYI',
868 l_item_type,
869 l_item_key,
870 l_error_msg
871 );
872 RAISE;
873 WHEN OTHERS THEN
874 wf_core.context
875 (
876 'AHL_MC_APPROVAL_PVT',
877 'NTF_FINAL_APPROVAL_FYI',
878 l_item_type,
879 l_item_key
880 );
881 RAISE;
882
883 END NTF_FINAL_APPROVAL_FYI;
884
885
886 PROCEDURE NTF_REJECTED_FYI
887 (
888 document_id IN VARCHAR2
889 ,display_type IN VARCHAR2
890 ,document IN OUT NOCOPY VARCHAR2
891 ,document_type IN OUT NOCOPY VARCHAR2
892 )
893 IS
894
895 l_hyphen_pos1 NUMBER;
896 l_object VARCHAR2(30);
897 l_item_type VARCHAR2(30);
898 l_item_key VARCHAR2(30);
899 l_approver VARCHAR2(30);
900 l_body VARCHAR2(3500);
901 l_object_id NUMBER;
902 l_msg_count NUMBER;
903 l_msg_data VARCHAR2(4000);
904 l_error_msg VARCHAR2(2000);
905
906 l_mc_header_rec check_mc_exists%rowtype;
907
908 BEGIN
909
910 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
911 THEN
912 fnd_log.string
913 (
914 fnd_log.level_procedure,
915 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_REJECTED_FYI.begin',
916 'At the start of PLSQL procedure'
917 );
918 END IF;
919
920 document_type := 'text/plain';
921
922 -- parse document_id for the ':' dividing item type name from item key value
923 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
924 -- release 2.5 version of this demo
925
926 l_hyphen_pos1 := INSTR(document_id, ':');
927 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
928 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
929
930 l_object := wf_engine.getitemattrtext
931 (
932 itemtype => l_item_type
933 ,itemkey => l_item_key
934 ,aname => 'OBJECT_TYPE'
935 );
936
937 l_object_id := wf_engine.getitemattrNumber
938 (
939 itemtype => l_item_type
940 ,itemkey => l_item_key
941 ,aname => 'OBJECT_ID'
942 );
943
944 l_approver := wf_engine.getitemattrtext
945 (
946 itemtype => l_item_type
947 ,itemkey => l_item_key
948 ,aname => 'APPROVER'
949 );
950
951 OPEN check_mc_exists(l_object_id, null);
952 FETCH check_mc_exists into l_mc_header_rec;
953
954 IF check_mc_exists%NOTFOUND
955 THEN
956 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
957 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
958 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
959 fnd_msg_pub.add;
960 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
961 THEN
962 fnd_log.message
963 (
964 fnd_log.level_exception,
965 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_REJECTED_FYI',
966 false
967 );
968 END IF;
969 RAISE FND_API.G_EXC_ERROR;
970 ELSE
971 fnd_message.set_name('AHL', 'AHL_MC_NTF_FWD_FYI_RJCT');
972 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id ,false);
973 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
974 fnd_message.set_token('APPR_NAME',l_approver, false);
975 l_body := fnd_message.get;
976 END IF;
977 CLOSE check_mc_exists;
978
979 document := document || l_body;
980
984 (
981 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
982 THEN
983 fnd_log.string
985 fnd_log.level_statement,
986 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_REJECTED_FYI',
987 'document='||document
988 );
989
990 fnd_log.string
991 (
992 fnd_log.level_statement,
993 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_REJECTED_FYI.end',
994 'At the end of PLSQL procedure'
995 );
996 END IF;
997
998 RETURN;
999
1000 EXCEPTION
1001 WHEN FND_API.G_EXC_ERROR THEN
1002 FND_MSG_PUB.Count_And_Get
1003 (
1004 p_encoded => FND_API.G_FALSE,
1005 p_count => l_msg_count,
1006 p_data => l_msg_data
1007 );
1008 AHL_GENERIC_APRV_PVT.Handle_Error
1009 (
1010 p_itemtype => l_item_type ,
1011 p_itemkey => l_item_key ,
1012 p_msg_count => l_msg_count,
1013 p_msg_data => l_msg_data ,
1014 p_attr_name => 'ERROR_MSG',
1015 x_error_msg => l_error_msg
1016 );
1017 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1018 THEN
1019 fnd_log.string
1020 (
1021 fnd_log.level_exception,
1022 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_REJECTED_FYI',
1023 l_error_msg
1024 );
1025 END IF;
1026 wf_core.context
1027 (
1028 'AHL_MC_APPROVAL_PVT',
1029 'NTF_REJECTED_FYI',
1030 l_item_type,
1031 l_item_key,
1032 l_error_msg
1033 );
1034 RAISE;
1035 WHEN OTHERS THEN
1036 wf_core.context(
1037 'AHL_MC_APPROVAL_PVT',
1038 'NTF_REJECTED_FYI',
1039 l_item_type,
1040 l_item_key
1041 );
1042 RAISE;
1043
1044 END NTF_REJECTED_FYI;
1045
1046 PROCEDURE NTF_APPROVAL
1047 (
1048 document_id IN VARCHAR2
1049 ,display_type IN VARCHAR2
1050 ,document IN OUT NOCOPY VARCHAR2
1051 ,document_type IN OUT NOCOPY VARCHAR2
1052 )
1053 IS
1054
1055 l_hyphen_pos1 NUMBER;
1056 l_object VARCHAR2(30);
1057 l_item_type VARCHAR2(30);
1058 l_item_key VARCHAR2(30);
1059 l_requester VARCHAR2(30);
1060 l_requester_note VARCHAR2(4000);
1061 l_body VARCHAR2(5000);
1062 l_object_id NUMBER;
1063 l_msg_count NUMBER;
1064 l_msg_data VARCHAR2(4000);
1065 l_error_msg VARCHAR2(2000);
1066
1067 l_mc_header_rec check_mc_exists%rowtype;
1068
1069 BEGIN
1070
1071 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1072 THEN
1073 fnd_log.string
1074 (
1075 fnd_log.level_procedure,
1076 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL.begin',
1077 'At the start of PLSQL procedure'
1078 );
1079 END IF;
1080
1081 document_type := 'text/plain';
1082
1083 -- parse document_id for the ':' dividing item type name from item key value
1084 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1085 -- release 2.5 version of this demo
1086
1087 l_hyphen_pos1 := INSTR(document_id, ':');
1088 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1089 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1090
1091 l_object := wf_engine.getitemattrtext
1092 (
1093 itemtype => l_item_type
1094 ,itemkey => l_item_key
1095 ,aname => 'OBJECT_TYPE'
1096 );
1097
1098 l_object_id := wf_engine.getitemattrNumber
1099 (
1100 itemtype => l_item_type
1101 ,itemkey => l_item_key
1102 ,aname => 'OBJECT_ID'
1103 );
1104
1105 l_requester := wf_engine.getitemattrtext
1106 (
1107 itemtype => l_item_type
1108 ,itemkey => l_item_key
1109 ,aname => 'REQUESTER'
1110 );
1111
1112 l_requester_note := wf_engine.getitemattrtext
1113 (
1114 itemtype => l_item_type
1115 ,itemkey => l_item_key
1116 ,aname => 'REQUESTER_NOTE'
1117 );
1118
1119
1120 OPEN check_mc_exists(l_object_id, null);
1121 FETCH check_mc_exists into l_mc_header_rec;
1122
1123 IF check_mc_exists%NOTFOUND
1124 THEN
1125 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
1126 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
1127 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1128 fnd_msg_pub.add;
1129 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1130 THEN
1131 fnd_log.message
1132 (
1133 fnd_log.level_exception,
1134 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL',
1135 false
1136 );
1137 END IF;
1138 RAISE FND_API.G_EXC_ERROR;
1139 ELSE
1140 fnd_message.set_name('AHL', 'AHL_MC_NTF_APPROVAL');
1141 fnd_message.set_token('REQUESTER',l_requester, false);
1142 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id ,false);
1143 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1144 fnd_message.set_token('NOTE',l_requester_note, false);
1145 l_body := fnd_message.get;
1146 END IF;
1147 CLOSE check_mc_exists;
1148
1149 document := document || l_body;
1150
1151 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1152 THEN
1153 fnd_log.string
1154 (
1155 fnd_log.level_statement,
1156 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL_FYI',
1157 'document='||document
1158 );
1159
1160 fnd_log.string
1161 (
1162 fnd_log.level_statement,
1163 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL_FYI.end',
1164 'At the end of PLSQL procedure'
1165 );
1166 END IF;
1167
1168 RETURN;
1169
1173 (
1170 EXCEPTION
1171 WHEN FND_API.G_EXC_ERROR THEN
1172 FND_MSG_PUB.Count_And_Get
1174 p_encoded => FND_API.G_FALSE,
1175 p_count => l_msg_count,
1176 p_data => l_msg_data
1177 );
1178 AHL_GENERIC_APRV_PVT.Handle_Error
1179 (
1180 p_itemtype => l_item_type ,
1181 p_itemkey => l_item_key ,
1182 p_msg_count => l_msg_count,
1183 p_msg_data => l_msg_data ,
1184 p_attr_name => 'ERROR_MSG',
1185 x_error_msg => l_error_msg
1186 );
1187 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1188 THEN
1189 fnd_log.string
1190 (
1191 fnd_log.level_exception,
1192 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_REJECTED_FYI',
1193 l_error_msg
1194 );
1195 END IF;
1196 wf_core.context
1197 (
1198 'AHL_MC_APPROVAL_PVT',
1199 'NTF_APPROVAL',
1200 l_item_type,
1201 l_item_key,
1202 l_error_msg
1203 );
1204 RAISE;
1205 WHEN OTHERS THEN
1206 wf_core.context(
1207 'AHL_MC_APPROVAL_PVT',
1208 'NTF_APPROVAL',
1209 l_item_type,
1210 l_item_key
1211 );
1212 RAISE;
1213
1214 END NTF_APPROVAL;
1215
1216 PROCEDURE NTF_APPROVAL_REMINDER
1217 (
1218 document_id IN VARCHAR2
1219 ,display_type IN VARCHAR2
1220 ,document IN OUT NOCOPY VARCHAR2
1221 ,document_type IN OUT NOCOPY VARCHAR2
1222 )
1223 IS
1224
1225 l_hyphen_pos1 NUMBER;
1226 l_object VARCHAR2(30);
1227 l_item_type VARCHAR2(30);
1228 l_item_key VARCHAR2(30);
1229 l_requester VARCHAR2(30);
1230 l_requester_note VARCHAR2(4000);
1231 l_body VARCHAR2(5000);
1232 l_object_id NUMBER;
1233 l_msg_count NUMBER;
1234 l_msg_data VARCHAR2(4000);
1235 l_error_msg VARCHAR2(2000);
1236
1237 l_mc_header_rec check_mc_exists%rowtype;
1238
1239 BEGIN
1240
1241 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1242 THEN
1243 fnd_log.string
1244 (
1245 fnd_log.level_procedure,
1246 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL_REMINDER.begin',
1247 'At the start of PLSQL procedure'
1248 );
1249 END IF;
1250
1251 document_type := 'text/plain';
1252
1253 -- parse document_id for the ':' dividing item type name from item key value
1254 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1255 -- release 2.5 version of this demo
1256
1257 l_hyphen_pos1 := INSTR(document_id, ':');
1258 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1259 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1260
1261 l_object := wf_engine.getitemattrtext
1262 (
1263 itemtype => l_item_type
1264 ,itemkey => l_item_key
1265 ,aname => 'OBJECT_TYPE'
1266 );
1267
1268 l_object_id := wf_engine.getitemattrNumber
1269 (
1270 itemtype => l_item_type
1271 ,itemkey => l_item_key
1272 ,aname => 'OBJECT_ID'
1273 );
1274
1275 l_requester := wf_engine.getitemattrtext
1276 (
1277 itemtype => l_item_type
1278 ,itemkey => l_item_key
1279 ,aname => 'REQUESTER'
1280 );
1281
1282 l_requester_note := wf_engine.getitemattrtext
1283 (
1284 itemtype => l_item_type
1285 ,itemkey => l_item_key
1286 ,aname => 'REQUESTER_NOTE'
1287 );
1288
1289
1290 OPEN check_mc_exists(l_object_id, null);
1291 FETCH check_mc_exists into l_mc_header_rec;
1292
1293 IF check_mc_exists%NOTFOUND
1294 THEN
1295 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
1296 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
1297 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1298 fnd_msg_pub.add;
1299 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1300 THEN
1301 fnd_log.message
1302 (
1303 fnd_log.level_exception,
1304 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL_REMINDER',
1305 false
1306 );
1307 END IF;
1308 RAISE FND_API.G_EXC_ERROR;
1309 ELSE
1310 fnd_message.set_name('AHL', 'AHL_MC_NTF_REMIND');
1311 fnd_message.set_token('REQUESTER',l_requester, false);
1312 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id ,false);
1313 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1314 fnd_message.set_token('NOTE',l_requester_note, false);
1315 l_body := fnd_message.get;
1316 END IF;
1317 CLOSE check_mc_exists;
1318
1319 document := document || l_body;
1320
1321 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1322 THEN
1323 fnd_log.string
1324 (
1325 fnd_log.level_statement,
1326 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL_REMINDER',
1327 'document='||document
1328 );
1329
1330 fnd_log.string
1331 (
1332 fnd_log.level_statement,
1333 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL_REMINDER.end',
1334 'At the end of PLSQL procedure'
1335 );
1336 END IF;
1337
1338 RETURN;
1339
1340 EXCEPTION
1341 WHEN FND_API.G_EXC_ERROR THEN
1342 FND_MSG_PUB.Count_And_Get
1343 (
1344 p_encoded => FND_API.G_FALSE,
1345 p_count => l_msg_count,
1346 p_data => l_msg_data
1347 );
1348 AHL_GENERIC_APRV_PVT.Handle_Error
1349 (
1350 p_itemtype => l_item_type ,
1351 p_itemkey => l_item_key ,
1352 p_msg_count => l_msg_count,
1353 p_msg_data => l_msg_data ,
1354 p_attr_name => 'ERROR_MSG',
1358 THEN
1355 x_error_msg => l_error_msg
1356 );
1357 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1359 fnd_log.string
1360 (
1361 fnd_log.level_exception,
1362 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_APPROVAL_REMINDER',
1363 l_error_msg
1364 );
1365 END IF;
1366 wf_core.context
1367 (
1368 'AHL_MC_APPROVAL_PVT',
1369 'NTF_APPROVAL_REMINDER',
1370 l_item_type,
1371 l_item_key,
1372 l_error_msg
1373 );
1374 RAISE;
1375 WHEN OTHERS THEN
1376 wf_core.context(
1377 'AHL_MC_APPROVAL_PVT',
1378 'NTF_APPROVAL_REMINDER',
1379 l_item_type,
1380 l_item_key
1381 );
1382 RAISE;
1383
1384 END NTF_APPROVAL_REMINDER;
1385
1386 PROCEDURE NTF_ERROR_ACT
1387 (
1388 document_id IN VARCHAR2
1389 ,display_type IN VARCHAR2
1390 ,document IN OUT NOCOPY VARCHAR2
1391 ,document_type IN OUT NOCOPY VARCHAR2
1392 )
1393 IS
1394
1395 l_hyphen_pos1 NUMBER;
1396 l_object VARCHAR2(30);
1397 l_item_type VARCHAR2(30);
1398 l_item_key VARCHAR2(30);
1399 l_body VARCHAR2(3500);
1400 l_object_id NUMBER;
1401 l_error_msg VARCHAR2(4000);
1402 l_msg_count NUMBER;
1403 l_msg_data VARCHAR2(4000);
1404
1405 l_mc_header_rec check_mc_exists%rowtype;
1406
1407 BEGIN
1408
1409 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1410 THEN
1411 fnd_log.string
1412 (
1413 fnd_log.level_procedure,
1414 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_ERROR_ACT.begin',
1415 'At the start of PLSQL procedure'
1416 );
1417 END IF;
1418
1419 document_type := 'text/plain';
1420
1421 -- parse document_id for the ':' dividing item type name from item key value
1422 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1423 -- release 2.5 version of this demo
1424
1425 l_hyphen_pos1 := INSTR(document_id, ':');
1426 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1427 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1428
1429 l_object := wf_engine.getitemattrtext
1430 (
1431 itemtype => l_item_type
1432 ,itemkey => l_item_key
1433 ,aname => 'OBJECT_TYPE'
1434 );
1435
1436 l_object_id := wf_engine.getitemattrNumber
1437 (
1438 itemtype => l_item_type
1439 ,itemkey => l_item_key
1440 ,aname => 'OBJECT_ID'
1441 );
1442
1443 l_error_msg := wf_engine.getitemattrText
1444 (
1445 itemtype => l_item_type
1446 ,itemkey => l_item_key
1447 ,aname => 'ERROR_MSG'
1448 );
1449
1450 OPEN check_mc_exists(l_object_id, null);
1451 FETCH check_mc_exists into l_mc_header_rec;
1452
1453 IF check_mc_exists%NOTFOUND
1454 THEN
1455 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
1456 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
1457 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1458 fnd_msg_pub.add;
1459 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1460 THEN
1461 fnd_log.message
1462 (
1463 fnd_log.level_exception,
1464 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_ERROR_ACT',
1465 false
1466 );
1467 END IF;
1468 RAISE FND_API.G_EXC_ERROR;
1469 ELSE
1470 fnd_message.set_name('AHL', 'AHL_MC_NTF_ERROR_ACT');
1471 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id ,false);
1472 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1473 fnd_message.set_token('ERR_MSG',l_error_msg, false);
1474 l_body := fnd_message.get;
1475 END IF;
1476 CLOSE check_mc_exists;
1477
1478 document := document || l_body;
1479
1480 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1481 THEN
1482 fnd_log.string
1483 (
1484 fnd_log.level_statement,
1485 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_ERROR_ACT',
1486 'document='||document
1487 );
1488
1489 fnd_log.string
1490 (
1491 fnd_log.level_statement,
1492 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_ERROR_ACT.end',
1493 'At the end of PLSQL procedure'
1494 );
1495 END IF;
1496
1497 RETURN;
1498
1499 EXCEPTION
1500 WHEN FND_API.G_EXC_ERROR THEN
1501 FND_MSG_PUB.Count_And_Get
1502 (
1503 p_encoded => FND_API.G_FALSE,
1504 p_count => l_msg_count,
1505 p_data => l_msg_data
1506 );
1507 AHL_GENERIC_APRV_PVT.Handle_Error
1508 (
1509 p_itemtype => l_item_type ,
1510 p_itemkey => l_item_key ,
1511 p_msg_count => l_msg_count,
1512 p_msg_data => l_msg_data ,
1513 p_attr_name => 'ERROR_MSG',
1514 x_error_msg => l_error_msg
1515 );
1516 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1517 THEN
1518 fnd_log.string
1519 (
1520 fnd_log.level_exception,
1521 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_ERROR_ACT',
1522 l_error_msg
1523 );
1524 END IF;
1525 wf_core.context
1526 (
1527 'AHL_MC_APPROVAL_PVT',
1528 'NTF_ERROR_ACT',
1529 l_item_type,
1530 l_item_key,
1531 l_error_msg
1532 );
1533 RAISE;
1534 WHEN OTHERS THEN
1535 wf_core.context
1536 (
1537 'AHL_MC_APPROVAL_PVT',
1538 'NTF_ERROR_ACT',
1539 l_item_type,
1540 l_item_key
1541 );
1542 RAISE;
1543
1544 END NTF_ERROR_ACT;
1545
1546 PROCEDURE UPDATE_STATUS
1547 (
1551 ,funcmode IN VARCHAR2
1548 itemtype IN VARCHAR2
1549 ,itemkey IN VARCHAR2
1550 ,actid IN NUMBER
1552 ,resultout OUT NOCOPY VARCHAR2
1553 )
1554 IS
1555
1556 l_error_msg VARCHAR2(4000);
1557 l_next_status VARCHAR2(30);
1558 l_object_version_number NUMBER;
1559 l_object_id NUMBER;
1560 l_status_date DATE;
1561 l_msg_count NUMBER;
1562 l_msg_data VARCHAR2(4000);
1563
1564 l_mc_header_rec check_mc_exists%ROWTYPE;
1565
1566 BEGIN
1567
1568 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1569 THEN
1570 fnd_log.string
1571 (
1572 fnd_log.level_procedure,
1573 'ahl.plsql.AHL_MC_Approvals_PVT.UPDATE_STATUS.begin',
1574 'At the start of PLSQL procedure'
1575 );
1576 END IF;
1577
1578 --
1579 -- RUN Mode
1580 --
1581 IF (funcmode = 'RUN')
1582 THEN
1583 l_next_status := wf_engine.getitemattrtext
1584 (
1585 itemtype => itemtype
1586 ,itemkey => itemkey
1587 ,aname => 'UPDATE_GEN_STATUS'
1588 );
1589
1590 l_object_version_number := wf_engine.getitemattrnumber
1591 (
1592 itemtype => itemtype
1593 ,itemkey => itemkey
1594 ,aname => 'OBJECT_VER'
1595 );
1596
1597 l_object_id := wf_engine.getitemattrnumber
1598 (
1599 itemtype => itemtype
1600 ,itemkey => itemkey
1601 ,aname => 'OBJECT_ID'
1602 );
1603
1604 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1605 THEN
1606 fnd_log.string
1607 (
1608 fnd_log.level_statement,
1609 'ahl.plsql.AHL_MC_Approvals_PVT.UPDATE_STATUS',
1610 'UPDATE_GEN_STATUS='||l_next_status||' -- OBJECT_VER='||l_object_version_number|| ' -- OBJECT_ID='||l_object_id
1611 );
1612 END IF;
1613
1614 OPEN check_mc_exists(l_object_id, l_object_version_number);
1615 FETCH check_mc_exists into l_mc_header_rec;
1616
1617 IF check_mc_exists%NOTFOUND
1618 THEN
1619 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
1620 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
1621 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1622 fnd_msg_pub.add;
1623 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1624 THEN
1625 fnd_log.message
1626 (
1627 fnd_log.level_exception,
1628 'ahl.plsql.AHL_MC_Approvals_PVT.UPDATE_STATUS',
1629 false
1630 );
1631 END IF;
1632 RAISE FND_API.G_EXC_ERROR;
1633 END IF;
1634 CLOSE check_mc_exists;
1635
1636 UPDATE ahl_mc_headers_b
1637 SET config_status_code = l_next_status,
1638 object_version_number = l_object_version_number + 1,
1639 last_update_date = sysdate,
1640 last_updated_by = to_number(fnd_global.login_id),
1641 last_update_login = to_number(fnd_global.login_id)
1642 WHERE mc_header_id = l_object_id;
1643
1644 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1645 THEN
1646 fnd_log.string
1647 (
1648 fnd_log.level_statement,
1649 'ahl.plsql.AHL_MC_Approvals_PVT.UPDATE_STATUS',
1650 'Successfully completed the MC with mc_header_id='||l_object_id
1651 );
1652 END IF;
1653
1654 resultout := 'COMPLETE:SUCCESS';
1655 RETURN;
1656 END IF;
1657
1658 --
1659 -- CANCEL mode
1660 --
1661 IF (funcmode = 'CANCEL')
1662 THEN
1663 resultout := 'COMPLETE:';
1664 RETURN;
1665 END IF;
1666
1667 --
1668 -- TIMEOUT mode
1669 --
1670 IF (funcmode = 'TIMEOUT')
1671 THEN
1672 resultout := 'COMPLETE:';
1673 RETURN;
1674 END IF;
1675
1676 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1677 THEN
1678 fnd_log.string
1679 (
1680 fnd_log.level_procedure,
1681 'ahl.plsql.AHL_MC_Approvals_PVT.UPDATE_STATUS.end',
1682 'At the end of PLSQL procedure'
1683 );
1684 END IF;
1685
1686 EXCEPTION
1687 WHEN FND_API.g_exc_error THEN
1688 FND_MSG_PUB.Count_And_Get
1689 (
1690 p_encoded => FND_API.G_FALSE,
1691 p_count => l_msg_count,
1692 p_data => l_msg_data
1693 );
1694 AHL_GENERIC_APRV_PVT.Handle_Error
1695 (
1696 p_itemtype => itemtype ,
1697 p_itemkey => itemkey ,
1698 p_msg_count => l_msg_count,
1699 p_msg_data => l_msg_data ,
1700 p_attr_name => 'ERROR_MSG',
1701 x_error_msg => l_error_msg
1702 );
1703 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1704 THEN
1705 fnd_log.string
1706 (
1707 fnd_log.level_exception,
1708 'ahl.plsql.AHL_MC_Approvals_PVT.NTF_REJECTED_FYI',
1709 l_error_msg
1710 );
1711 END IF;
1712 wf_core.context
1713 (
1714 'AHL_MC_APPROVAL_PVT',
1715 'UPDATE_STATUS',
1716 itemtype,
1717 itemkey,
1718 actid,
1719 funcmode,
1720 l_error_msg
1721 );
1722 RAISE;
1723
1724 WHEN OTHERS THEN
1725 wf_core.context(
1726 'AHL_MC_APPROVAL_PVT',
1727 'UPDATE_STATUS',
1728 itemtype,
1729 itemkey,
1730 actid,
1731 funcmode,
1732 l_error_msg
1733 );
1734 RAISE;
1735
1736 END UPDATE_STATUS;
1737
1738 PROCEDURE REVERT_STATUS
1739 (
1740 itemtype IN VARCHAR2
1741 ,itemkey IN VARCHAR2
1742 ,actid IN NUMBER
1743 ,funcmode IN VARCHAR2
1747
1744 ,resultout OUT NOCOPY VARCHAR2
1745 )
1746 IS
1748 l_error_msg VARCHAR2(4000);
1749
1750 l_next_status VARCHAR2(30);
1751 l_object_version_number NUMBER;
1752 l_object_id NUMBER;
1753 l_status_date DATE;
1754 l_msg_count NUMBER;
1755 l_msg_data VARCHAR2(4000);
1756
1757 l_mc_header_rec check_mc_exists%rowtype;
1758
1759 BEGIN
1760
1761 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1762 THEN
1763 fnd_log.string
1764 (
1765 fnd_log.level_procedure,
1766 'ahl.plsql.AHL_MC_Approvals_PVT.REVERT_STATUS.begin',
1767 'At the start of PLSQL procedure'
1768 );
1769 END IF;
1770
1771 --
1772 -- RUN mode
1773 --
1774 IF (funcmode = 'RUN')
1775 THEN
1776 l_next_status := wf_engine.getitemattrText
1777 (
1778 itemtype => itemtype
1779 ,itemkey => itemkey
1780 ,aname => 'ORG_STATUS_ID'
1781 );
1782 l_object_version_number := wf_engine.getitemattrnumber
1783 (
1784 itemtype => itemtype
1785 ,itemkey => itemkey
1786 ,aname => 'OBJECT_VER'
1787 );
1788
1789 l_object_id := wf_engine.getitemattrnumber
1790 (
1791 itemtype => itemtype
1792 ,itemkey => itemkey
1793 ,aname => 'OBJECT_ID'
1794 );
1795
1796 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1797 THEN
1798 fnd_log.string
1799 (
1800 fnd_log.level_statement,
1801 'ahl.plsql.AHL_MC_Approvals_PVT.UPDATE_STATUS',
1802 'ORG_STATUS_ID='||l_next_status||' -- OBJECT_VER='||l_object_version_number|| ' -- OBJECT_ID='||l_object_id
1803 );
1804 END IF;
1805
1806 OPEN check_mc_exists(l_object_id, l_object_version_number);
1807 FETCH check_mc_exists into l_mc_header_rec;
1808
1809 IF check_mc_exists%NOTFOUND
1810 THEN
1811 fnd_message.set_name('AHL', 'AHL_MC_HEADER_ID_INVALID');
1812 fnd_message.set_token('MC_HEADER_ID',l_mc_header_rec.mc_header_id,false);
1813 fnd_message.set_token('NAME',l_mc_header_rec.NAME, false);
1814 fnd_msg_pub.add;
1815 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1816 THEN
1817 fnd_log.message
1818 (
1819 fnd_log.level_exception,
1820 'ahl.plsql.AHL_MC_Approvals_PVT.REVERT_STATUS',
1821 false
1822 );
1823 END IF;
1824 RAISE FND_API.G_EXC_ERROR;
1825 END IF;
1826 CLOSE check_mc_exists;
1827
1828 UPDATE ahl_mc_headers_b
1829 SET config_status_code = l_next_status,
1830 object_version_number = l_object_version_number + 1,
1831 last_update_date = sysdate,
1832 last_updated_by = to_number(fnd_global.login_id),
1833 last_update_login = to_number(fnd_global.login_id)
1834 WHERE mc_header_id = l_object_id;
1835
1836 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1837 THEN
1838 fnd_log.string
1839 (
1840 fnd_log.level_statement,
1841 'ahl.plsql.AHL_MC_Approvals_PVT.UPDATE_STATUS',
1842 'Successfully reverted status of the MC with mc_header_id='||l_object_id
1843 );
1844 END IF;
1845
1846 resultout := 'COMPLETE:';
1847 RETURN;
1848 END IF;
1849
1850 --
1851 -- CANCEL mode
1852 --
1853 IF (funcmode = 'CANCEL')
1854 THEN
1855 resultout := 'COMPLETE:';
1856 RETURN;
1857 END IF;
1858
1859 --
1860 -- TIMEOUT mode
1861 --
1862 IF (funcmode = 'TIMEOUT')
1863 THEN
1864 resultout := 'COMPLETE:';
1865 RETURN;
1866 END IF;
1867
1868 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1869 THEN
1870 fnd_log.string
1871 (
1872 fnd_log.level_procedure,
1873 'ahl.plsql.AHL_MC_Approvals_PVT.REVERT_STATUS.end',
1874 'At the end of PLSQL procedure'
1875 );
1876 END IF;
1877
1878 EXCEPTION
1879 WHEN FND_API.g_exc_error THEN
1880 FND_MSG_PUB.Count_And_Get
1881 (
1882 p_encoded => FND_API.G_FALSE,
1883 p_count => l_msg_count,
1884 p_data => l_msg_data
1885 );
1886 AHL_GENERIC_APRV_PVT.Handle_Error
1887 (
1888 p_itemtype => itemtype ,
1889 p_itemkey => itemkey ,
1890 p_msg_count => l_msg_count,
1891 p_msg_data => l_msg_data ,
1892 p_attr_name => 'ERROR_MSG',
1893 x_error_msg => l_error_msg
1894 );
1895 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1896 THEN
1897 fnd_log.string
1898 (
1899 fnd_log.level_exception,
1900 'ahl.plsql.AHL_MC_Approvals_PVT.REVERT_STATUS',
1901 l_error_msg
1902 );
1903 END IF;
1904 wf_core.context
1905 (
1906 'AHL_MC_APPROVAL_PVT',
1907 'REVERT_STATUS',
1908 itemtype,
1909 itemkey,
1910 actid,
1911 funcmode,
1912 l_error_msg
1913 );
1914 RAISE;
1915 WHEN OTHERS THEN
1916 wf_core.context
1917 (
1918 'AHL_MC_APPROVAL_PVT'
1919 ,'REVERT_STATUS'
1920 ,itemtype
1921 ,itemkey
1922 ,actid
1923 ,funcmode
1924 ,'Unexpected Error!'
1925 );
1926 RAISE;
1927
1928 END REVERT_STATUS;
1929
1930 End AHL_MC_Approvals_PVT;