[Home] [Help]
PACKAGE BODY: APPS.AHL_GENERIC_APRV_PVT
Source
1 PACKAGE BODY AHL_GENERIC_APRV_PVT as
2 /* $Header: AHLVGWFB.pls 115.7 2003/12/30 06:27:55 rroy noship $ */
3
4 /*****************************************************************
5 -- Private API Specifications
6 *****************************************************************/
7
8 PROCEDURE Get_User_Role(
9 p_resource_id IN NUMBER,
10 x_role_name OUT NOCOPY VARCHAR2,
11 x_role_display_name OUT NOCOPY VARCHAR2 ,
12 x_return_status OUT NOCOPY VARCHAR2);
13
14 PROCEDURE Check_Approval_Required(
15 p_rule_id IN NUMBER,
16 p_current_seq IN NUMBER,
17 x_next_seq OUT NOCOPY NUMBER,
18 x_required_flag OUT NOCOPY VARCHAR2);
19
20 PROCEDURE Check_Appl_Usg_Code(
21 p_appl_usg_code IN VARCHAR2,
22 x_return_status OUT NOCOPY VARCHAR2
23 );
24
25 --======================================================================
26 -- PROCEDURE
27 -- Start_WF_Process
28 --
29 -- PURPOSE
30 -- Start Workflow Process
31 --
32 --======================================================================
33
34 PROCEDURE Start_WF_Process
35 (p_object IN VARCHAR2,
36 p_activity_id IN NUMBER,
37 p_approval_type IN VARCHAR2,
38 p_object_version_number IN NUMBER,
39 p_orig_status_code IN VARCHAR2,
40 p_new_status_code IN VARCHAR2,
41 p_reject_status_code IN VARCHAR2,
42 p_requester_userid IN NUMBER,
43 p_notes_from_requester IN VARCHAR2,
44 p_workflowprocess IN VARCHAR2,
45 p_item_type IN VARCHAR2,
46 p_application_usg_code IN VARCHAR2 DEFAULT 'AHL'
47 )
48 IS
49 itemtype VARCHAR2(30) := nvl(p_item_type,'AHLGAPP');
50 itemkey VARCHAR2(30) := p_approval_type||':'||p_object||':'||to_char(p_activity_id)||':'||to_char(p_object_version_number);
51 itemuserkey VARCHAR2(80) := p_object||':'||to_char(p_activity_id);
52
53 l_msg_count NUMBER;
54 l_msg_data VARCHAR2(4000);
55 l_error_msg VARCHAR2(4000);
56 l_save_threshold NUMBER := wf_engine.threshold;
57 l_index NUMBER;
58 l_return_status varchar2(1);
59 l_counter NUMBER;
60 l_timeout NUMBER;
61
62 l_requester_role VARCHAR2(30);
63 l_resource_id NUMBER;
64 l_display_name VARCHAR2(80);
65 l_application_usg_code VARCHAR2(30);
66
67 CURSOR c_resource IS
68 SELECT resource_id
69 FROM ahl_jtf_rs_emp_v
70 WHERE user_id = p_requester_userid ;
71 BEGIN
72
73 fnd_msg_pub.initialize;
74
75 -- wf_engine.threshold := -1;
76 WF_ENGINE.CreateProcess (itemtype => itemtype,
77 itemkey => itemkey ,
78 process => p_workflowprocess);
79
80 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
81 itemkey => itemkey ,
82 userkey => itemuserkey);
83
84
85 /*****************************************************************
86 Initialize Workflow Item Attributes
87 *****************************************************************/
88 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
89 itemkey => itemkey,
90 aname => 'OBJECT_TYPE',
91 avalue => p_object );
92
93 -- Reema: Validate Application Usage Code
94 l_application_usg_code := p_application_usg_code;
95 Check_Appl_Usg_Code(p_appl_usg_code => l_application_usg_code,
96 x_return_status => l_return_status
97 );
98 IF l_return_status <> Fnd_Api.g_ret_sts_error
99 THEN
100 -- Reema:
101 -- Set the Value of Application Usage Code
102 -- This value can then be used in other procedures.
103 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
104 itemkey => itemkey,
105 aname => 'APPLICATION_USG_CODE',
106 avalue => p_application_usg_code);
107 END IF;
108
109 -- Activity ID (primary Id of Activity Object)
110 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype ,
111 itemkey => itemkey,
112 aname => 'OBJECT_ID',
113 avalue => p_activity_id );
114
115 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
116 itemkey => itemkey,
117 aname => 'ORG_STATUS_ID',
118 avalue => p_orig_status_code );
119
120 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
121 itemkey => itemkey,
122 aname => 'NEW_STATUS_ID',
123 avalue => P_NEW_STATUS_CODE );
124
125 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
126 itemkey => itemkey,
127 aname => 'REJECT_STATUS_ID',
128 avalue => p_reject_status_code );
129
130 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
131 itemkey => itemkey,
132 aname => 'OBJECT_VER',
133 avalue => p_object_version_number );
134
135 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
136 itemkey => itemkey,
137 aname => 'REQUESTER_ID',
138 avalue => p_requester_userid );
139
140 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
141 itemkey => itemkey,
142 aname => 'REQUESTER_NOTE',
143 avalue => nvl(p_notes_from_requester,''));
144
145 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
146 itemkey => itemkey,
147 aname => 'APPROVAL_TYPE',
148 avalue => p_approval_type );
149
150 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
151 itemkey => itemkey,
152 aname => 'REQUEST_TIME',
153 avalue => SYSDATE );
154
155 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
156 itemkey => itemkey,
157 aname => 'DOCUMENT_ID',
158 avalue => itemtype || ':' ||itemkey);
159
160
161 -- Set up Loop Counter here from profile option value!
162 l_counter := FND_PROFILE.VALUE('AHL_WF_COUNTER');
163
164 if l_counter is null
165 then
166
167 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_COUNTER');
168 FND_MSG_PUB.Add;
169 else
170 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
171 itemkey => itemkey,
172 aname => 'COUNTER',
173 avalue => l_counter );
174
175 END IF;
176
177 -- Set up Timeout here from profile option value!
178 l_timeout := FND_PROFILE.VALUE('AHL_WF_TIMEOUT');
179
180 if l_timeout is null
181 then
182
183 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_TIMEOUT');
184 FND_MSG_PUB.Add;
185 else
186 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
187 itemkey => itemkey,
188 aname => 'TIMEOUT',
189 avalue => l_timeout );
190
191 END IF;
192
193 --Standard Call to count messages
194 l_msg_count := FND_MSG_PUB.count_msg;
195
196 IF l_msg_count > 0 THEN
197 RAISE FND_API.G_EXC_ERROR;
198 END IF;
199
200
201 -- Set up requester Role
202 l_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 -- Get Resource ID of the Requester
205 OPEN c_resource ;
206 FETCH c_resource INTO l_resource_id ;
207 IF c_resource%NOTFOUND THEN
208 l_return_status := FND_API.G_RET_STS_ERROR;
209 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_RESOURCE');
210 FND_MSG_PUB.Add;
211 CLOSE c_resource ;
212 ELSE
213 CLOSE c_resource ;
214 Get_User_Role(p_resource_id => l_resource_id ,
215 x_role_name => l_requester_role,
216 x_role_display_name => l_display_name,
217 x_return_status => l_return_status);
218
219 END IF;
220
221 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
222 RAISE FND_API.G_EXC_ERROR;
223 ELSE
224 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
225 itemkey => itemkey,
226 aname => 'REQUESTER',
227 avalue => l_requester_role );
228
229 -- Setting the WF Owner
230 WF_ENGINE.SetItemOwner(itemtype => itemtype,
231 itemkey => itemkey,
232 owner => l_requester_role);
233 END IF;
234
235 -- Start the Process
236 WF_ENGINE.StartProcess (itemtype => itemtype,
237 itemkey => itemkey);
238
239
240 -- wf_engine.threshold := l_save_threshold ;
241 EXCEPTION
242 WHEN OTHERS THEN
243 -- wf_engine.threshold := l_save_threshold ;
244 FND_MSG_PUB.Count_And_Get (
245 p_encoded => FND_API.G_FALSE,
246 p_count => l_msg_count,
247 p_data => l_msg_data);
248
249 if(l_msg_count > 0)then
250 for I in 1 .. l_msg_count LOOP
251 fnd_msg_pub.Get
252 (p_msg_index => FND_MSG_PUB.G_NEXT,
253 p_encoded => FND_API.G_FALSE,
254 p_data => l_msg_data,
255 p_msg_index_out => l_index);
256
257 -- dbms_output.put_line('error message :'||l_msg_data);
258 end loop;
259 end if;
260 RAISE;
261
262 END Start_WF_Process;
263
264 /*****************************************************************
265 -- Wrapper API Body
266 *****************************************************************/
267
268
269 /*****************************************************************
270 -- Start of Comments
271 --
272 -- NAME
273 -- set_activity_details
274 --
275 -- PURPOSE
276 -- NOTES
277 -- HISTORY
278 -- End of Comments
279 *****************************************************************/
280
281 PROCEDURE Set_Activity_Details(itemtype IN VARCHAR2,
282 itemkey IN VARCHAR2,
283 actid IN NUMBER,
284 funcmode IN VARCHAR2,
285 resultout OUT NOCOPY VARCHAR2)
286 IS
287 l_object VARCHAR(30);
288 l_approval_type VARCHAR2(30);
289 l_pkg_name varchar2(80);
290 l_proc_name varchar2(80);
291 l_msg_count NUMBER;
292 l_msg_data VARCHAR2(4000);
293 l_error_msg VARCHAR2(4000);
294 l_return_status varchar2(1);
295 dml_str VARCHAR2 (2000);
296
297 BEGIN
298
299 FND_MSG_PUB.initialize();
300 IF (funcmode = 'RUN') THEN
301
302 -- get approval object
303 l_object := wf_engine.GetItemAttrText(
304 itemtype => itemtype,
305 itemkey => itemkey,
306 aname => 'OBJECT_TYPE' );
307
308
309 l_approval_type := wf_engine.GetItemAttrText(
310 itemtype => itemtype,
311 itemkey => itemkey,
312 aname => 'APPROVAL_TYPE' );
313
314 Get_Api_Name('WORKFLOW', l_object, 'SET_ACTIVITY_DETAILS', l_approval_type, l_pkg_name, l_proc_name, l_return_status);
315 if (l_return_status = fnd_api.g_ret_sts_success) then
316 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:itemtype,:itemkey,:actid,:funcmode,:resultout); END;';
317 EXECUTE IMMEDIATE dml_str USING IN itemtype,IN itemkey,IN actid,IN funcmode, OUT resultout;
318 else
319 RAISE FND_API.G_EXC_ERROR;
320 return;
321 end if;
322
323 END IF;
324
325 --
326 -- CANCEL mode
327 --
328 IF (funcmode = 'CANCEL') THEN
329 resultout := 'COMPLETE:';
330 return;
331 END IF;
332
333 --
334 -- TIMEOUT mode
335 --
336 IF (funcmode = 'TIMEOUT') THEN
337 resultout := 'COMPLETE:';
338 return;
339 END IF;
340 --
341
342 EXCEPTION
343
344 WHEN FND_API.G_EXC_ERROR THEN
345 FND_MSG_PUB.Count_And_Get (
346 p_encoded => FND_API.G_FALSE,
347 p_count => l_msg_count,
348 p_data => l_msg_data
349 );
350 Handle_Error
351 (p_itemtype => itemtype ,
352 p_itemkey => itemkey ,
353 p_msg_count => l_msg_count, -- Number of error Messages
354 p_msg_data => l_msg_data ,
355 p_attr_name => 'ERROR_MSG',
356 x_error_msg => l_error_msg
357 ) ;
358 wf_core.context('AHL_GENERIC_APRV_PVT','Set_Activity_Details',
359 itemtype,itemkey,actid,funcmode,l_error_msg);
360
361 resultout := 'COMPLETE:ERROR';
362
363 WHEN OTHERS THEN
364
365 wf_core.context('AHL_GENERIC_APRV_PVT','Set_Activity_Details',
366 itemtype,itemkey,actid,funcmode,'Unexpected Error!');
367
368 RAISE;
372
369
370 END Set_Activity_Details ;
371
373
374 -------------------------------------------------------------------------------
375 --
376 -- NAME
377 -- PREPARE_DOC
378 --
379 -- PURPOSE
380 -- Serve as a connection point. Dose nothing.
381 -------------------------------------------------------------------------------
382
383 PROCEDURE Prepare_Doc( itemtype in varchar2,
384 itemkey in varchar2,
385 actid in number,
386 funcmode in varchar2,
387 resultout out nocopy varchar2 )
388 IS
389 BEGIN
390 FND_MSG_PUB.initialize();
391 IF (funcmode = 'RUN') THEN
392 resultout := 'COMPLETE:SUCCESS';
393 END IF;
394
395 --
396 -- CANCEL mode
397 --
398 IF (funcmode = 'CANCEL') THEN
399 resultout := 'COMPLETE:';
400 return;
401 END IF;
402
403 --
404 -- TIMEOUT mode
405 --
406 IF (funcmode = 'TIMEOUT') THEN
407 resultout := 'COMPLETE:';
408 return;
409 END IF;
410 --
411
412 END Prepare_Doc;
413
414
415 -------------------------------------------------------------------------------
416 --
417 -- Set_Approver_Details
418 --
419 -------------------------------------------------------------------------------
420 PROCEDURE Set_Approver_Details( itemtype in varchar2,
421 itemkey in varchar2,
422 actid in number,
423 funcmode in varchar2,
424 resultout out nocopy varchar2 )
425 IS
426 l_current_seq NUMBER;
427 l_approval_rule_id NUMBER;
428 l_approver_id NUMBER;
429 l_approver VARCHAR2(30);
430 l_approver_display_name VARCHAR2(80);
431 l_approver_type VARCHAR2(30);
432 l_object_approver_id NUMBER;
433
434 l_return_status VARCHAR2(1);
435 l_msg_count NUMBER;
436 l_msg_data VARCHAR2(4000);
437 l_error_msg VARCHAR2(4000);
438
439
440 BEGIN
441
442 FND_MSG_PUB.initialize();
443
444 IF (funcmode = 'RUN') THEN
445 l_approval_rule_id := wf_engine.GetItemAttrNumber(
446 itemtype => itemtype,
447 itemkey => itemkey,
448 aname => 'RULE_ID' );
449
450 l_current_seq := wf_engine.GetItemAttrNumber(
451 itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'APPROVER_SEQ' );
454
455 Get_approver_Info
456 ( p_rule_id => l_approval_rule_id,
457 p_current_seq => l_current_seq ,
458 x_approver_id => l_approver_id,
459 x_approver_type => l_approver_type,
460 x_object_approver_id => l_object_approver_id,
461 x_return_status => l_return_status);
462
463 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
464
465 RAISE FND_API.G_EXC_ERROR;
466 END IF;
467
468 Get_User_Role(p_resource_id => l_object_approver_id ,
469 x_role_name => l_approver,
470 x_role_display_name => l_approver_display_name,
471 x_return_status => l_return_status);
472
473 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
474
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477
478 wf_engine.SetItemAttrText( itemtype => itemtype,
479 itemkey => itemkey,
480 aname => 'APPROVER',
481 avalue => l_approver);
482
483 wf_engine.SetItemAttrNumber(itemtype => itemtype,
484 itemkey => itemkey,
485 aname => 'APPROVER_ID',
486 avalue => l_object_approver_id);
487
488
489 resultout := 'COMPLETE:';
490 END IF;
491
492 --
493 -- CANCEL mode
494 --
495 IF (funcmode = 'CANCEL') THEN
496 resultout := 'COMPLETE:';
497 return;
498 END IF;
499
500 --
501 -- TIMEOUT mode
502 --
503 IF (funcmode = 'TIMEOUT') THEN
504 resultout := 'COMPLETE:';
505 return;
506 END IF;
507 EXCEPTION
508 WHEN FND_API.G_EXC_ERROR THEN
509 FND_MSG_PUB.Count_And_Get (
510 p_encoded => FND_API.G_FALSE,
511 p_count => l_msg_count,
512 p_data => l_msg_data
513 );
514
515 Handle_Error
516 (p_itemtype => itemtype ,
517 p_itemkey => itemkey ,
518 p_msg_count => l_msg_count, -- Number of error Messages
519 p_msg_data => l_msg_data ,
520 p_attr_name => 'ERROR_MSG',
524
521 x_error_msg => l_error_msg
522 ) ;
523
525 wf_core.context('AHL_GENERIC_APRV_PVT',
526 'set_approver_Details',
527 itemtype, itemkey,to_char(actid),l_error_msg);
528
529 RAISE;
530 WHEN OTHERS THEN
531
532 wf_core.context('AHL_GENERIC_APRV_PVT',
533 'set_approver_Details',
534 itemtype, itemkey,to_char(actid),'Unexpected Error!');
535
536 RAISE;
537 --
538
539 END Set_Approver_Details;
540
541
542 -------------------------------------------------------------------------------
543 --
544 -- Set_Further_Approvals
545 --
546 -------------------------------------------------------------------------------
547 PROCEDURE Set_Further_Approval( itemtype in varchar2,
548 itemkey in varchar2,
549 actid in number,
550 funcmode in varchar2,
551 resultout out nocopy varchar2 )
552 IS
553 l_current_seq NUMBER;
554 l_next_seq NUMBER;
555 l_approval_rule_id NUMBER;
556 l_required_flag VARCHAR2(1);
557 l_approver_id NUMBER;
558 l_msg_count NUMBER;
559 l_msg_data VARCHAR2(4000);
560 l_return_status VARCHAR2(1);
561 l_error_msg VARCHAR2(4000);
562 BEGIN
563
564 FND_MSG_PUB.initialize();
565 IF (funcmode = 'RUN') THEN
566 l_approval_rule_id := wf_engine.GetItemAttrNumber(
567 itemtype => itemtype,
568 itemkey => itemkey,
569 aname => 'RULE_ID' );
570
571 l_current_seq := wf_engine.GetItemAttrNumber(
572 itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'APPROVER_SEQ' );
575
576 l_approver_id := wf_engine.GetItemAttrNumber(
577 itemtype => itemtype,
578 itemkey => itemkey,
579 aname => 'APPROVER_ID' );
580
581
582 Check_Approval_Required
583 ( p_rule_id => l_approval_rule_id,
584 p_current_seq => l_current_seq,
585 x_next_seq => l_next_seq,
586 x_required_flag => l_required_flag);
587
588 IF l_next_seq is not null THEN
589 wf_engine.SetItemAttrNumber(itemtype => itemtype,
590 itemkey => itemkey,
591 aname => 'APPROVER_SEQ',
592 avalue => l_next_seq);
593 resultout := 'COMPLETE:Y';
594 ELSE
595 resultout := 'COMPLETE:N';
596 END IF;
597 END IF;
598
599 --
600 -- CANCEL mode
601 --
602 IF (funcmode = 'CANCEL') THEN
603 resultout := 'COMPLETE:';
604 return;
605 END IF;
606
607 --
608 -- TIMEOUT mode
609 --
610 IF (funcmode = 'TIMEOUT') THEN
611 resultout := 'COMPLETE:';
612 return;
613 END IF;
614
615 EXCEPTION
616 WHEN FND_API.G_EXC_ERROR THEN
617 FND_MSG_PUB.Count_And_Get (
618 p_encoded => FND_API.G_FALSE,
619 p_count => l_msg_count,
620 p_data => l_msg_data
621 );
622 Handle_Error
623 (p_itemtype => itemtype ,
624 p_itemkey => itemkey ,
625 p_msg_count => l_msg_count, -- Number of error Messages
626 p_msg_data => l_msg_data ,
627 p_attr_name => 'ERROR_MSG',
628 x_error_msg => l_error_msg
629 ) ;
630
631 wf_core.context('AHL_GENERIC_APRV_PVT',
632 'set_further_approval',
633 itemtype, itemkey,to_char(actid),l_error_msg);
634 RAISE;
635 WHEN OTHERS THEN
636
637 wf_core.context('AHL_GENERIC_APRV_PVT',
638 'set_further_approval',
639 itemtype, itemkey,to_char(actid),'Unexpected Error!');
640 RAISE;
641 --
642
643 END Set_Further_Approval;
644
645
646 --------------------------------------------------------------------------------
647 --
648 -- Procedure
649 -- Ntf_Approval(document_id in varchar2,
650 -- display_type in varchar2,
651 -- document in out varchar2,
652 -- document_type in out varchar2 )
653 ---------------------------------------------------------------------------------
654 PROCEDURE Ntf_Approval(document_id in varchar2,
655 display_type in varchar2,
656 document in out nocopy varchar2,
657 document_type in out nocopy varchar2 )
658 IS
659 dml_str varchar2(2000);
663 l_proc_name varchar2(80);
660 l_itemType varchar2(80);
661 l_itemKey varchar2(80);
662 l_pkg_name varchar2(80);
664 l_approval_type varchar2(80);
665 l_object varchar2(30);
666 l_msg_data VARCHAR2(4000);
667 l_msg_count number;
668 l_error_msg VARCHAR2(4000);
669 l_return_stat varchar2(1);
670 BEGIN
671 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AHLGAPP');
672 l_itemKey := substr(document_id, instr(document_id,':')+1);
673
674 l_object := wf_engine.GetItemAttrText(
675 itemtype => l_itemtype,
676 itemkey => l_itemkey,
677 aname => 'OBJECT_TYPE' );
678
679 l_approval_type := wf_engine.GetItemAttrText(
680 itemtype => l_itemtype,
681 itemkey => l_itemkey,
682 aname => 'APPROVAL_TYPE' );
683
684 Get_Api_Name('WORKFLOW', l_object, 'NTF_APPROVAL',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
685
686 if (l_return_stat = fnd_api.g_ret_sts_success) then
687 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
688 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
689 else
690 RAISE FND_API.G_EXC_ERROR;
691 end if;
692
693 EXCEPTION
694 WHEN FND_API.G_EXC_ERROR THEN
695 FND_MSG_PUB.Count_And_Get (
696 p_encoded => FND_API.G_FALSE,
697 p_count => l_msg_count,
698 p_data => l_msg_data
699 );
700 Handle_Error
701 (p_itemtype => l_itemtype ,
702 p_itemkey => l_itemkey ,
703 p_msg_count => l_msg_count, -- Number of error Messages
704 p_msg_data => l_msg_data ,
705 p_attr_name => 'ERROR_MSG',
706 x_error_msg => l_error_msg
707 ) ;
708 wf_core.context('AHL_GENERIC_APRV_PVT',
709 'ntf_approval',
710 l_itemtype, l_itemkey,l_error_msg);
711 RAISE;
712 WHEN OTHERS THEN
713
714 wf_core.context('AHL_GENERIC_APRV_PVT',
715 'ntf_approval',
716 l_itemtype, l_itemkey,'Unexpected Error!');
717 RAISE;
718 --
719
720 END Ntf_Approval;
721
722 --------------------------------------------------------------------------------
723 --
724 -- Procedure
725 -- Ntf_Error_Act(document_id in varchar2,
726 -- display_type in varchar2,
727 -- document in out varchar2,
728 -- document_type in out varchar2 )
729 ---------------------------------------------------------------------------------
730 PROCEDURE Ntf_Error_Act(document_id in varchar2,
731 display_type in varchar2,
732 document in out nocopy varchar2,
733 document_type in out nocopy varchar2 )
734 IS
735 dml_str varchar2(2000);
736 l_itemType varchar2(80);
737 l_itemKey varchar2(80);
738 l_pkg_name varchar2(80);
739 l_proc_name varchar2(80);
740 l_approval_type varchar2(80);
741 l_object varchar2(30);
742 l_msg_data VARCHAR2(4000);
743 l_msg_count number;
744 l_error_msg VARCHAR2(4000);
745 l_return_stat varchar2(1);
746 BEGIN
747 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AHLGAPP');
748 l_itemKey := substr(document_id, instr(document_id,':')+1);
749
750 l_object := wf_engine.GetItemAttrText(
751 itemtype => l_itemtype,
752 itemkey => l_itemkey,
753 aname => 'OBJECT_TYPE' );
754
755 l_approval_type := wf_engine.GetItemAttrText(
756 itemtype => l_itemtype,
757 itemkey => l_itemkey,
758 aname => 'APPROVAL_TYPE' );
759
760 Get_Api_Name('WORKFLOW', l_object, 'NTF_ERROR_ACT',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
761
762 if (l_return_stat = fnd_api.g_ret_sts_success) then
763 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
764 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
765 else
766 RAISE FND_API.G_EXC_ERROR;
767 end if;
768
769 EXCEPTION
770 WHEN FND_API.G_EXC_ERROR THEN
771 FND_MSG_PUB.Count_And_Get (
772 p_encoded => FND_API.G_FALSE,
773 p_count => l_msg_count,
774 p_data => l_msg_data
775 );
776 Handle_Error
777 (p_itemtype => l_itemtype ,
778 p_itemkey => l_itemkey ,
779 p_msg_count => l_msg_count, -- Number of error Messages
780 p_msg_data => l_msg_data ,
781 p_attr_name => 'ERROR_MSG',
785 'ntf_error_act',
782 x_error_msg => l_error_msg
783 ) ;
784 wf_core.context('AHL_GENERIC_APRV_PVT',
786 l_itemtype, l_itemkey,l_error_msg);
787 RAISE;
788 WHEN OTHERS THEN
789
790 wf_core.context('AHL_GENERIC_APRV_PVT',
791 'ntf_error_act',
792 l_itemtype, l_itemkey,'Unexpected Error!');
793 RAISE;
794 --
795
796 END Ntf_Error_Act;
797
798 --------------------------------------------------------------------------------
799 --
800 -- Procedure
801 -- Ntf_Approval_Reminder(document_id in varchar2,
802 -- display_type in varchar2,
803 -- document in out varchar2,
804 -- document_type in out varchar2 )
805 ---------------------------------------------------------------------------------
806 PROCEDURE Ntf_Approval_Reminder(document_id in varchar2,
807 display_type in varchar2,
808 document in out nocopy varchar2,
809 document_type in out nocopy varchar2 )
810 IS
811 dml_str varchar2(2000);
812 l_itemType varchar2(80);
813 l_itemKey varchar2(80);
814 l_pkg_name varchar2(80);
815 l_proc_name varchar2(80);
816 l_approval_type varchar2(80);
817 l_object varchar2(30);
818 l_msg_data VARCHAR2(4000);
819 l_msg_count number;
820 l_error_msg VARCHAR2(4000);
821 l_return_stat varchar2(1);
822 BEGIN
823 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AHLGAPP');
824 l_itemKey := substr(document_id, instr(document_id,':')+1);
825
826 l_object := wf_engine.GetItemAttrText(
827 itemtype => l_itemtype,
828 itemkey => l_itemkey,
829 aname => 'OBJECT_TYPE' );
830
831 l_approval_type := wf_engine.GetItemAttrText(
832 itemtype => l_itemtype,
833 itemkey => l_itemkey,
834 aname => 'APPROVAL_TYPE' );
835
836 Get_Api_Name('WORKFLOW', l_object, 'NTF_APPROVAL_REMINDER',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
837
838 if (l_return_stat = fnd_api.g_ret_sts_success) then
839 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
840 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
841 else
842 RAISE FND_API.G_EXC_ERROR;
843 end if;
844
845 EXCEPTION
846 WHEN FND_API.G_EXC_ERROR THEN
847 FND_MSG_PUB.Count_And_Get (
848 p_encoded => FND_API.G_FALSE,
849 p_count => l_msg_count,
850 p_data => l_msg_data
851 );
852 Handle_Error
853 (p_itemtype => l_itemtype ,
854 p_itemkey => l_itemkey ,
855 p_msg_count => l_msg_count, -- Number of error Messages
856 p_msg_data => l_msg_data ,
857 p_attr_name => 'ERROR_MSG',
858 x_error_msg => l_error_msg
859 ) ;
860 wf_core.context('AHL_GENERIC_APRV_PVT',
861 'ntf_approval_reminder',
862 l_itemtype, l_itemkey,l_error_msg);
863 RAISE;
864 WHEN OTHERS THEN
865
866 wf_core.context('AHL_GENERIC_APRV_PVT',
867 'ntf_approval_reminder',
868 l_itemtype, l_itemkey,'Unexpected Error!');
869 RAISE;
870 --
871
872 END Ntf_Approval_Reminder;
873
874 --------------------------------------------------------------------------------
875 --
876 -- Procedure
877 -- Ntf_Forward_FYI(document_id in varchar2,
878 -- display_type in varchar2,
879 -- document in out varchar2,
880 -- document_type in out varchar2 )
881 ---------------------------------------------------------------------------------
882 PROCEDURE Ntf_Forward_FYI(document_id in varchar2,
883 display_type in varchar2,
884 document in out nocopy varchar2,
885 document_type in out nocopy varchar2 )
886 IS
887 dml_str varchar2(2000);
888 l_itemType varchar2(80);
889 l_itemKey varchar2(80);
890 l_pkg_name varchar2(80);
891 l_proc_name varchar2(80);
892 l_approval_type varchar2(80);
893 l_object varchar2(30);
894 l_msg_data VARCHAR2(4000);
895 l_msg_count number;
896 l_error_msg VARCHAR2(4000);
897 l_return_stat varchar2(1);
898 BEGIN
899
900 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AHLGAPP');
901
902 l_itemKey := substr(document_id, instr(document_id,':')+1);
903
904 l_object := wf_engine.GetItemAttrText(
905 itemtype => l_itemtype,
906 itemkey => l_itemkey,
910 itemtype => l_itemtype,
907 aname => 'OBJECT_TYPE' );
908
909 l_approval_type := wf_engine.GetItemAttrText(
911 itemkey => l_itemkey,
912 aname => 'APPROVAL_TYPE' );
913
914 Get_Api_Name('WORKFLOW', l_object, 'NTF_FORWARD_FYI',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
915
916 if (l_return_stat = fnd_api.g_ret_sts_success) then
917 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
918 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
919 else
920 RAISE FND_API.G_EXC_ERROR;
921 end if;
922
923 EXCEPTION
924 WHEN FND_API.G_EXC_ERROR THEN
925 FND_MSG_PUB.Count_And_Get (
926 p_encoded => FND_API.G_FALSE,
927 p_count => l_msg_count,
928 p_data => l_msg_data
929 );
930 Handle_Error
931 (p_itemtype => l_itemtype ,
932 p_itemkey => l_itemkey ,
933 p_msg_count => l_msg_count, -- Number of error Messages
934 p_msg_data => l_msg_data ,
935 p_attr_name => 'ERROR_MSG',
936 x_error_msg => l_error_msg
937 ) ;
938 wf_core.context('AHL_GENERIC_APRV_PVT',
939 'ntf_forward_fyi',
940 l_itemtype, l_itemkey,l_error_msg);
941 RAISE;
942 WHEN OTHERS THEN
943
944 wf_core.context('AHL_GENERIC_APRV_PVT',
945 'ntf_forward_fyi',
946 l_itemtype, l_itemkey,'Unexpected Error!');
947 RAISE;
948 --
949
950 END Ntf_Forward_FYI;
951
952 --------------------------------------------------------------------------------
953 --
954 -- Procedure
955 -- Ntf_Approved_FYI(document_id in varchar2,
956 -- display_type in varchar2,
957 -- document in out varchar2,
958 -- document_type in out varchar2 )
959 ---------------------------------------------------------------------------------
960 PROCEDURE Ntf_Approved_FYI(document_id in varchar2,
961 display_type in varchar2,
962 document in out nocopy varchar2,
963 document_type in out nocopy varchar2 )
964 IS
965 dml_str varchar2(2000);
966 l_itemType varchar2(80);
967 l_itemKey varchar2(80);
968 l_pkg_name varchar2(80);
969 l_proc_name varchar2(80);
970 l_approval_type varchar2(80);
971 l_object varchar2(30);
972 l_msg_data VARCHAR2(4000);
973 l_msg_count number;
974 l_error_msg VARCHAR2(4000);
975 l_return_stat varchar2(1);
976 BEGIN
977 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AHLGAPP');
978 l_itemKey := substr(document_id, instr(document_id,':')+1);
979
980 l_object := wf_engine.GetItemAttrText(
981 itemtype => l_itemtype,
982 itemkey => l_itemkey,
983 aname => 'OBJECT_TYPE' );
984
985 l_approval_type := wf_engine.GetItemAttrText(
986 itemtype => l_itemtype,
987 itemkey => l_itemkey,
988 aname => 'APPROVAL_TYPE' );
989
990 Get_Api_Name('WORKFLOW', l_object, 'NTF_APPROVED_FYI',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
991
992 if (l_return_stat = fnd_api.g_ret_sts_success) then
993 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
994 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
995 else
996 RAISE FND_API.G_EXC_ERROR;
997 end if;
998
999 EXCEPTION
1000 WHEN FND_API.G_EXC_ERROR THEN
1001 FND_MSG_PUB.Count_And_Get (
1002 p_encoded => FND_API.G_FALSE,
1003 p_count => l_msg_count,
1004 p_data => l_msg_data
1005 );
1006 Handle_Error
1007 (p_itemtype => l_itemtype ,
1008 p_itemkey => l_itemkey ,
1009 p_msg_count => l_msg_count, -- Number of error Messages
1010 p_msg_data => l_msg_data ,
1011 p_attr_name => 'ERROR_MSG',
1012 x_error_msg => l_error_msg
1013 ) ;
1014 wf_core.context('AHL_GENERIC_APRV_PVT',
1015 'ntf_approved_fyi',
1016 l_itemtype, l_itemkey,l_error_msg);
1017 RAISE;
1018 WHEN OTHERS THEN
1019
1020 wf_core.context('AHL_GENERIC_APRV_PVT',
1021 'ntf_approved_fyi',
1022 l_itemtype, l_itemkey,'Unexpected Error!');
1023 RAISE;
1024 --
1025
1026 END Ntf_Approved_FYI;
1027
1028 --------------------------------------------------------------------------------
1029 --
1030 -- Procedure
1031 -- Ntf_Rejected_FYI(document_id in varchar2,
1035 ---------------------------------------------------------------------------------
1032 -- display_type in varchar2,
1033 -- document in out varchar2,
1034 -- document_type in out varchar2 )
1036 PROCEDURE Ntf_Rejected_FYI(document_id in varchar2,
1037 display_type in varchar2,
1038 document in out nocopy varchar2,
1039 document_type in out nocopy varchar2 )
1040 IS
1041 dml_str varchar2(2000);
1042 l_itemType varchar2(80);
1043 l_itemKey varchar2(80);
1044 l_pkg_name varchar2(80);
1045 l_proc_name varchar2(80);
1046 l_approval_type varchar2(80);
1047 l_object varchar2(30);
1048 l_msg_data VARCHAR2(4000);
1049 l_msg_count number;
1050 l_error_msg VARCHAR2(4000);
1051 l_return_stat varchar2(1);
1052 BEGIN
1053 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AHLGAPP');
1054 l_itemKey := substr(document_id, instr(document_id,':')+1);
1055
1056 l_object := wf_engine.GetItemAttrText(
1057 itemtype => l_itemtype,
1058 itemkey => l_itemkey,
1059 aname => 'OBJECT_TYPE' );
1060
1061 l_approval_type := wf_engine.GetItemAttrText(
1062 itemtype => l_itemtype,
1063 itemkey => l_itemkey,
1064 aname => 'APPROVAL_TYPE' );
1065
1066 Get_Api_Name('WORKFLOW', l_object, 'NTF_REJECTED_FYI',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
1067
1068 if (l_return_stat = fnd_api.g_ret_sts_success) then
1069 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
1070 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
1071 else
1072 RAISE FND_API.G_EXC_ERROR;
1073 end if;
1074
1075 EXCEPTION
1076 WHEN FND_API.G_EXC_ERROR THEN
1077 FND_MSG_PUB.Count_And_Get (
1078 p_encoded => FND_API.G_FALSE,
1079 p_count => l_msg_count,
1080 p_data => l_msg_data
1081 );
1082 Handle_Error
1083 (p_itemtype => l_itemtype ,
1084 p_itemkey => l_itemkey ,
1085 p_msg_count => l_msg_count, -- Number of error Messages
1086 p_msg_data => l_msg_data ,
1087 p_attr_name => 'ERROR_MSG',
1088 x_error_msg => l_error_msg
1089 ) ;
1090 wf_core.context('AHL_GENERIC_APRV_PVT',
1091 'ntf_rejected_fyi',
1092 l_itemtype, l_itemkey,l_error_msg);
1093 RAISE;
1094 WHEN OTHERS THEN
1095
1096 wf_core.context('AHL_GENERIC_APRV_PVT',
1097 'ntf_rejected_fyi',
1098 l_itemtype, l_itemkey,'Unexpected Error!');
1099 RAISE;
1100 --
1101
1102 END Ntf_Rejected_FYI;
1103 --------------------------------------------------------------------------------
1104 --
1105 -- Procedure
1106 -- Ntf_Final_Approval_FYI(document_id in varchar2,
1107 -- display_type in varchar2,
1108 -- document in out varchar2,
1109 -- document_type in out varchar2 )
1110 ---------------------------------------------------------------------------------
1111 PROCEDURE Ntf_Final_Approval_FYI(document_id in varchar2,
1112 display_type in varchar2,
1113 document in out nocopy varchar2,
1114 document_type in out nocopy varchar2 )
1115 IS
1116 dml_str varchar2(2000);
1117 l_itemType varchar2(80);
1118 l_itemKey varchar2(80);
1119 l_pkg_name varchar2(80);
1120 l_proc_name varchar2(80);
1121 l_approval_type varchar2(80);
1122 l_object varchar2(30);
1123 l_msg_data VARCHAR2(4000);
1124 l_msg_count number;
1125 l_error_msg VARCHAR2(4000);
1126 l_return_stat varchar2(1);
1127 BEGIN
1128 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AHLGAPP');
1129 l_itemKey := substr(document_id, instr(document_id,':')+1);
1130
1131 l_object := wf_engine.GetItemAttrText(
1132 itemtype => l_itemtype,
1133 itemkey => l_itemkey,
1134 aname => 'OBJECT_TYPE' );
1135
1136 l_approval_type := wf_engine.GetItemAttrText(
1137 itemtype => l_itemtype,
1138 itemkey => l_itemkey,
1139 aname => 'APPROVAL_TYPE' );
1140
1141 Get_Api_Name('WORKFLOW', l_object, 'NTF_FINAL_APPROVAL_FYI',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
1142
1143 if (l_return_stat = fnd_api.g_ret_sts_success) then
1144 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
1145 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
1146 else
1147 RAISE FND_API.G_EXC_ERROR;
1148 end if;
1149
1150 EXCEPTION
1154 p_count => l_msg_count,
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 FND_MSG_PUB.Count_And_Get (
1153 p_encoded => FND_API.G_FALSE,
1155 p_data => l_msg_data
1156 );
1157 Handle_Error
1158 (p_itemtype => l_itemtype ,
1159 p_itemkey => l_itemkey ,
1160 p_msg_count => l_msg_count, -- Number of error Messages
1161 p_msg_data => l_msg_data ,
1162 p_attr_name => 'ERROR_MSG',
1163 x_error_msg => l_error_msg
1164 ) ;
1165 wf_core.context('AHL_GENERIC_APRV_PVT',
1166 'ntf_final_approval_fyi',
1167 l_itemtype, l_itemkey,l_error_msg);
1168 RAISE;
1169 WHEN OTHERS THEN
1170
1171 wf_core.context('AHL_GENERIC_APRV_PVT',
1172 'ntf_final_approval_fyi',
1173 l_itemtype, l_itemkey,'Unexpected Error!');
1174 RAISE;
1175 --
1176
1177 END Ntf_Final_Approval_FYI;
1178
1179 -------------------------------------------------------------------------------
1180 --
1181 -- Procedure
1182 -- Update_Status(itemtype in varchar2,
1183 -- itemkey in varchar2,
1184 -- actid in number,
1185 -- funcmode in varchar2,
1186 -- resultout out varchar2 )
1187 ---------------------------------------------------------------------------------
1188 PROCEDURE Update_Status(itemtype IN varchar2,
1189 itemkey IN varchar2,
1190 actid in number,
1191 funcmode in varchar2,
1192 resultout out nocopy varchar2 )
1193 IS
1194 dml_str varchar2(2000);
1195 l_pkg_name varchar2(80);
1196 l_proc_name varchar2(80);
1197 l_approval_type varchar2(80);
1198 l_object varchar2(30);
1199 l_msg_data VARCHAR2(4000);
1200 l_msg_count number;
1201 l_error_msg VARCHAR2(4000);
1202 l_return_status varchar2(1);
1203 BEGIN
1204 FND_MSG_PUB.initialize();
1205 l_object := wf_engine.GetItemAttrText(
1206 itemtype => itemtype,
1207 itemkey => itemkey,
1208 aname => 'OBJECT_TYPE' );
1209
1210 l_approval_type := wf_engine.GetItemAttrText(
1211 itemtype => itemtype,
1212 itemkey => itemkey,
1213 aname => 'APPROVAL_TYPE' );
1214
1215 Get_Api_Name('WORKFLOW', l_object, 'UPDATE_STATUS',l_approval_type, l_pkg_name, l_proc_name, l_return_status);
1216
1217 if (l_return_status = fnd_api.g_ret_sts_success) then
1218 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:itemtype,:itemkey,:actid,:funcmode,:resultout); END;';
1219 EXECUTE IMMEDIATE dml_str USING IN itemtype,IN itemkey,IN actid,IN funcmode, OUT resultout;
1220 else
1221 RAISE FND_API.G_EXC_ERROR;
1222 end if;
1223
1224 EXCEPTION
1225 WHEN FND_API.G_EXC_ERROR THEN
1226 FND_MSG_PUB.Count_And_Get (
1227 p_encoded => FND_API.G_FALSE,
1228 p_count => l_msg_count,
1229 p_data => l_msg_data
1230 );
1231 Handle_Error
1232 (p_itemtype => itemtype ,
1233 p_itemkey => itemkey ,
1234 p_msg_count => l_msg_count, -- Number of error Messages
1235 p_msg_data => l_msg_data ,
1236 p_attr_name => 'ERROR_MSG',
1237 x_error_msg => l_error_msg
1238 );
1239 wf_core.context('AHL_GENERIC_APRV_PVT',
1240 'update_status',
1241 itemtype, itemkey,l_error_msg);
1242 RAISE;
1243 WHEN OTHERS THEN
1244
1245 wf_core.context('AHL_GENERIC_APRV_PVT',
1246 'update_status',
1247 itemtype, itemkey,'Unexpected Error!');
1248 RAISE;
1249 --
1250 END Update_Status;
1251
1252 -------------------------------------------------------------------------------
1253 --
1254 -- Procedure
1255 -- Approved_Update_Status(itemtype in varchar2,
1256 -- itemkey in varchar2,
1257 -- actid in number,
1258 -- funcmode in varchar2,
1259 -- resultout out varchar2 )
1260 ---------------------------------------------------------------------------------
1261 PROCEDURE Approved_Update_Status(itemtype IN varchar2,
1262 itemkey IN varchar2,
1263 actid in number,
1264 funcmode in varchar2,
1265 resultout out nocopy varchar2 )
1266 IS
1267 l_approved_status VARCHAR(30);
1268 l_msg_data VARCHAR2(4000);
1269 l_msg_count number;
1270 l_error_msg VARCHAR2(4000);
1271
1272 BEGIN
1273
1274 l_approved_status := wf_engine.GetItemAttrText(
1275 itemtype => itemtype,
1279 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1276 itemkey => itemkey,
1277 aname => 'NEW_STATUS_ID' );
1278
1280 itemkey => itemkey,
1281 aname => 'UPDATE_GEN_STATUS',
1282 avalue => l_approved_status );
1283
1284 Update_Status(itemtype => itemtype,
1285 itemkey => itemkey,
1286 actid => actid,
1287 funcmode => funcmode,
1288 resultout => resultout);
1289 EXCEPTION
1290
1291 WHEN OTHERS THEN
1292
1293 wf_core.context('AHL_GENERIC_APRV_PVT',
1294 'Approved_Update_Status',
1295 itemtype, itemkey,to_char(actid),'Unexpected Error!');
1296 RAISE;
1297 --
1298
1299 END Approved_Update_Status;
1300
1301 -------------------------------------------------------------------------------
1302 --
1303 -- Procedure
1304 -- Reject_Update(itemtype in varchar2,
1305 -- itemkey in varchar2,
1306 -- actid in number,
1307 -- funcmode in varchar2,
1308 -- resultout out varchar2 )
1309 ---------------------------------------------------------------------------------
1310 PROCEDURE Rejected_Update_Status(itemtype IN varchar2,
1311 itemkey IN varchar2,
1312 actid in number,
1313 funcmode in varchar2,
1314 resultout out nocopy varchar2 )
1315 IS
1316 l_rejected_status VARCHAR(30);
1317 l_msg_data VARCHAR2(4000);
1318 l_msg_count number;
1319 l_error_msg VARCHAR2(4000);
1320
1321 BEGIN
1322
1323 l_rejected_status := wf_engine.GetItemAttrText(
1324 itemtype => itemtype,
1325 itemkey => itemkey,
1326 aname => 'REJECT_STATUS_ID' );
1327
1328 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1329 itemkey => itemkey,
1330 aname => 'UPDATE_GEN_STATUS',
1331 avalue => l_rejected_status );
1332
1333 Update_Status(itemtype => itemtype,
1334 itemkey => itemkey,
1335 actid => actid,
1336 funcmode => funcmode,
1337 resultout => resultout);
1338 EXCEPTION
1339
1340 WHEN OTHERS THEN
1341
1342 wf_core.context('AHL_GENERIC_APRV_PVT',
1343 'Rejected_Update_Status',
1344 itemtype, itemkey,to_char(actid),'Unexpected Error!');
1345 RAISE;
1346 --
1347
1348 END Rejected_Update_Status;
1349 -------------------------------------------------------------------------------
1350 --
1351 -- Procedure
1352 -- Revert_Status(itemtype in varchar2,
1353 -- itemkey in varchar2,
1354 -- actid in number,
1355 -- funcmode in varchar2,
1356 -- resultout out varchar2 )
1357 ---------------------------------------------------------------------------------
1358 PROCEDURE Revert_Status(itemtype IN varchar2,
1359 itemkey IN varchar2,
1360 actid in number,
1361 funcmode in varchar2,
1362 resultout out nocopy varchar2 )
1363 IS
1364 dml_str varchar2(2000);
1365 l_pkg_name varchar2(80);
1366 l_proc_name varchar2(80);
1367 l_approval_type varchar2(80);
1368 l_object varchar2(30);
1369 l_msg_data VARCHAR2(4000);
1370 l_msg_count number;
1371 l_error_msg VARCHAR2(4000);
1372 l_return_stat varchar2(1);
1373 BEGIN
1374 FND_MSG_PUB.initialize();
1375 l_object := wf_engine.GetItemAttrText(
1376 itemtype => itemtype,
1377 itemkey => itemkey,
1378 aname => 'OBJECT_TYPE' );
1379
1380 l_approval_type := wf_engine.GetItemAttrText(
1381 itemtype => itemtype,
1382 itemkey => itemkey,
1383 aname => 'APPROVAL_TYPE' );
1384
1385 Get_Api_Name('WORKFLOW', l_object, 'REVERT_STATUS',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
1386
1387 if (l_return_stat = fnd_api.g_ret_sts_success) then
1388 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:itemtype,:itemkey,:actid,:funcmode,:resultout); END;';
1389 EXECUTE IMMEDIATE dml_str USING IN itemtype,IN itemkey,IN actid,IN funcmode, OUT resultout;
1390 else
1391 RAISE FND_API.G_EXC_ERROR;
1392 end if;
1393
1394 EXCEPTION
1395 WHEN FND_API.G_EXC_ERROR THEN
1396 FND_MSG_PUB.Count_And_Get (
1397 p_encoded => FND_API.G_FALSE,
1398 p_count => l_msg_count,
1399 p_data => l_msg_data
1400 );
1401 Handle_Error
1405 p_msg_data => l_msg_data ,
1402 (p_itemtype => itemtype ,
1403 p_itemkey => itemkey ,
1404 p_msg_count => l_msg_count, -- Number of error Messages
1406 p_attr_name => 'ERROR_MSG',
1407 x_error_msg => l_error_msg
1408 ) ;
1409 wf_core.context('AHL_GENERIC_APRV_PVT',
1410 'Revert_Status',
1411 itemtype, itemkey,l_error_msg);
1412 RAISE;
1413 WHEN OTHERS THEN
1414
1415 wf_core.context('AHL_GENERIC_APRV_PVT',
1416 'REVERT_STATUS',
1417 itemtype, itemkey,'Unexpected Error!');
1418 RAISE;
1419 --
1420 END Revert_Status;
1421
1422
1423 /*****************************************************************
1424 -- Helper APIs
1425 *****************************************************************/
1426
1427 /*==============================================================================================*/
1428
1429 -- Start of Comments
1430 -- NAME
1431 -- Get_Approval_Details
1432 -- PURPOSE
1433 -- This Procedure get all the approval details
1434 --
1435 -- Used By Objects
1436 -- p_object Objects(Route, MC .. )
1437 -- p_approval_type CONCEPT
1438 -- p_object_details Object details contains the detail of objects
1439 -- x_approval_rule_id Approval detail Id macthing the criteria
1440 -- x_approver_seq Approval Sequence
1441 -- x_return_status Return Status
1442 -- NOTES
1443 -- HISTORY
1444 -- 1. Reema : Added Application Usage Logic (18/09/2003)
1445 -- End of Comments
1446 /*****************************************************************/
1447
1448 PROCEDURE Get_Approval_Details
1449 ( p_object IN VARCHAR2,
1450 p_approval_type IN VARCHAR2,
1451 p_object_details IN ObjRecTyp,
1452 x_approval_rule_id OUT NOCOPY NUMBER,
1453 x_approver_seq OUT NOCOPY NUMBER,
1454 x_return_status OUT NOCOPY VARCHAR2)
1455 IS
1456
1457 l_operating_unit_id NUMBER :=1; --default value
1458 l_priority VARCHAR2(30) :=' ';---default value
1459 l_approval_type_code VARCHAR2(30) := ' ';
1460 l_approver_id NUMBER;
1461 l_object_details ObjRecTyp;
1462 l_object VARCHAR2(30);
1463 l_object_id NUMBER;
1464 l_seeded_flag varchar2(1);
1465 l_application_usg_code varchar2(30) := 'AHL';
1466
1467 -- Get Approval Detail Id matching the Criteria
1468 -- Approval Object (CAMP, DELV.. ) is mandatory
1469 -- Approval type (BUDGET .. ) is mandatory
1470
1471 CURSOR c_approval_rule_id IS
1472 SELECT approval_rule_id, seeded_flag
1473 FROM ahl_approval_rules_b
1474 WHERE nvl(operating_unit_id,l_operating_unit_id) = l_operating_unit_id
1475 AND approval_object_code = p_object
1476 AND nvl(approval_type_code, l_approval_type_code) = l_approval_type_code
1477 AND nvl(approval_priority_code,l_priority) = l_priority
1478 AND seeded_flag = 'N'
1479 AND nvl(application_usg_code, l_application_usg_code) = l_application_usg_code
1480 AND status_code = 'ACTIVE'
1481 and sysdate between nvl(active_start_date,sysdate -1 ) and nvl(active_end_date,sysdate + 1)
1482 order by (power(2,decode(operating_unit_id,'',0,2)) +
1483 power(2,decode(approval_priority_code,'',0,1) )) desc ;
1484
1485 -- If the there are no matching records it takes the default Rule
1486 CURSOR c_default_rule IS
1487 SELECT approval_rule_id, seeded_flag
1488 FROM ahl_approval_rules_b
1489 WHERE seeded_flag = 'Y'
1490 AND nvl(application_usg_code, l_application_usg_code) = l_application_usg_code;
1491
1492 -- Takes Min Approver Sequence From Ahl_approvers Once matching records are
1493 -- Found form ahl_approval_rules_b
1494 CURSOR c_approver_seq IS
1495 SELECT min(approver_sequence)
1496 FROM ahl_approvers
1497 WHERE approval_rule_id = x_approval_rule_id;
1498
1499 BEGIN
1500
1501 x_return_status := FND_API.G_RET_STS_SUCCESS;
1502
1503 l_operating_unit_id := nvl(p_object_details.operating_unit_id, l_operating_unit_id);
1504 l_priority := nvl(p_object_details.priority, l_priority);
1505 l_application_usg_code := nvl(p_object_details.application_usg_code, 'AHL');
1506 l_approval_type_code := nvl(p_approval_type, l_approval_type_code);
1507
1508 -- Get Approval Rule ID
1509 OPEN c_approval_rule_id ;
1510 FETCH c_approval_rule_id INTO x_approval_rule_id, l_seeded_flag;
1511
1512 IF c_approval_rule_id%NOTFOUND THEN
1513 CLOSE c_approval_rule_id;
1514
1515 -- Get Default Rule ID if no rule has been defined for the given combination of qualifiers
1516 OPEN c_default_rule;
1517 FETCH c_default_rule INTO x_approval_rule_id, l_seeded_flag;
1518
1519 IF c_default_rule%NOTFOUND THEN
1520 CLOSE c_default_rule ;
1521
1522 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_RULE_ID');
1523 FND_MSG_PUB.Add;
1524
1525 x_return_status := FND_API.G_RET_STS_ERROR;
1526 return;
1527
1528 END IF;
1529 CLOSE c_default_rule;
1533
1530 ELSE
1531 CLOSE c_approval_rule_id;
1532 END IF;
1534 -- Get Approver Sequence with Approval Rule ID
1535
1536 OPEN c_approver_seq ;
1537 FETCH c_approver_seq INTO x_approver_seq ;
1538
1539 --IF c_approver_seq%NOTFOUND THEN
1540 IF x_approver_seq is null THEN
1541 CLOSE c_approver_seq;
1542
1543 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_SEQ');
1544 FND_MSG_PUB.Add;
1545 x_return_status := FND_API.G_RET_STS_ERROR;
1546
1547 return;
1548
1549 END IF;
1550
1551 CLOSE c_approver_seq;
1552
1553 END Get_Approval_Details;
1554
1555 -------------------------------------------------------------------------------
1556 --
1557 -- Gets approver info
1558 -- Approvers Can be user or Role
1559 -- If it is role it should of role_type AHLAPPR or AHLGAPPR
1560 -- The Seeded role is AHL_DEFAULT_APPROVER
1561 --
1562 -------------------------------------------------------------------------------
1563 PROCEDURE Get_Approver_Info
1564 ( p_rule_id IN NUMBER,
1565 p_current_seq IN NUMBER,
1566 x_approver_id OUT NOCOPY VARCHAR2,
1567 x_approver_type OUT NOCOPY VARCHAR2,
1568 x_object_approver_id OUT NOCOPY VARCHAR2,
1569 x_return_status OUT NOCOPY VARCHAR2)
1570 IS
1571 l_count number;
1572 l_pkg_name VARCHAR2(80);
1573 l_proc_name VARCHAR2(80);
1574 dml_str VARCHAR2(2000);
1575 x_msg_count NUMBER;
1576 x_msg_data VARCHAR2(2000);
1577
1578 CURSOR c_approver_info IS
1579 SELECT approval_approver_id,
1580 approver_type_code,
1581 approver_id
1582 FROM ahl_approvers
1583 WHERE approval_rule_id = p_rule_id
1584 AND approver_sequence = p_current_seq;
1585
1586 CURSOR c_role_info IS
1587 SELECT rr.ROLE_RESOURCE_ID
1588 FROM JTF_RS_ROLE_RELATIONS rr, JTF_RS_ROLES_B rb
1589 WHERE rr.role_id = rb.role_id
1590 AND rb.role_type_code in( 'AHLGAPPR','AHLAPPR')
1591 AND rr.ROLE_ID = x_object_approver_id
1592 AND rr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
1593 AND rr.delete_flag = 'N'
1594 and sysdate between nvl(rr.start_date_active,sysdate -1 ) and nvl(rr.end_date_active,sysdate + 1);
1595
1596 CURSOR c_role_info_count IS
1597 SELECT count(1)
1598 FROM JTF_RS_ROLE_RELATIONS rr, JTF_RS_ROLES_B rb
1599 WHERE rr.role_id = rb.role_id
1600 AND rb.role_type_code in( 'AHLGAPPR','AHLAPPR')
1601 AND rr.ROLE_ID = x_object_approver_id
1602 AND rr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
1603 AND rr.delete_flag = 'N'
1604 and sysdate between nvl(rr.start_date_active,sysdate -1 ) and nvl(rr.end_date_active,sysdate + 1);
1605
1606 CURSOR c_default_role_info IS
1607 SELECT rr.role_id
1608 FROM jtf_rs_role_relations rr,
1609 jtf_rs_roles_vl rl
1610 WHERE rr.role_id = rl.role_id
1611 and rl.role_type_code ='AHLAPPR'
1612 AND rl.role_code = 'AHL_DEFAULT_APPROVER'
1613 AND rr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
1614 AND delete_flag = 'N'
1615 AND sysdate between rr.start_date_active and nvl(rr.end_date_active,sysdate);
1616
1617 BEGIN
1618 x_return_status := FND_API.G_RET_STS_SUCCESS;
1619
1620 OPEN c_approver_info;
1621 FETCH c_approver_info
1622 INTO x_approver_id,
1623 x_approver_type,
1624 x_object_approver_id;
1625
1626 IF c_approver_info%NOTFOUND THEN
1627 CLOSE c_approver_info;
1628 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_APPROVER_ID');
1629 FND_MSG_PUB.Add;
1630 x_return_status := FND_API.G_RET_STS_ERROR;
1631 return;
1632 END IF;
1633
1634 IF x_approver_type = 'ROLE' THEN
1635 if x_object_approver_id is null then
1636 -- use default approver
1637 OPEN c_default_role_info ;
1638 FETCH c_default_role_info
1639 INTO x_object_approver_id;
1640
1641 IF c_default_role_info%NOTFOUND THEN
1642 CLOSE c_default_role_info ;
1643 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_DEFAULT_ROLE');
1644 FND_MSG_PUB.Add;
1645 x_return_status := FND_API.G_RET_STS_ERROR;
1646 return;
1647 END IF;
1648 CLOSE c_default_role_info ;
1649 end if;
1650
1651 -- More than one role found with given approver ID
1652 OPEN c_role_info_count;
1653 FETCH c_role_info_count
1654 INTO l_count;
1655 IF l_count > 1 THEN
1656 CLOSE c_role_info_count;
1657
1658 FND_MESSAGE.Set_Name('AHL','AHL_APRV_MORE_ROLE');
1659 FND_MSG_PUB.Add;
1660 x_return_status := FND_API.G_RET_STS_ERROR;
1661 return;
1662 END IF;
1663 CLOSE c_role_info_count;
1664
1665 OPEN c_role_info;
1666 FETCH c_role_info
1667 INTO x_object_approver_id;
1668 IF c_role_info%NOTFOUND THEN
1669 CLOSE c_role_info;
1670
1671 FND_MESSAGE.Set_Name('AHL','AHL_APRV_INVALID_ROLE');
1672 FND_MSG_PUB.Add;
1673 x_return_status := FND_API.G_RET_STS_ERROR;
1674 return;
1675 END IF;
1676 CLOSE c_role_info;
1677 END IF; --x_approval_type = ROLE;
1678
1679 CLOSE c_approver_info;
1680
1681 END Get_Approver_Info;
1682
1683
1684 --------------------------------------------------------------------------------
1685 --
1686 -- Procedure
1687 -- Get_Api_Name
1688 --
1692 p_activity_type in VARCHAR2,
1689 ---------------------------------------------------------------------------------
1690 PROCEDURE Get_Api_Name( p_api_used_by in varchar2,
1691 p_object in varchar2,
1693 p_approval_type in VARCHAR2,
1694 x_pkg_name out nocopy varchar2,
1695 x_proc_name out nocopy varchar2,
1696 x_return_status out nocopy varchar2)
1697 IS
1698
1699 CURSOR c_API_Name IS
1700 SELECT package_name, procedure_name
1701 FROM ahl_approval_api
1702 WHERE api_used_by = p_api_used_by
1703 AND approval_object_type = p_object
1704 AND activity_type = p_activity_type
1705 AND approval_type = p_approval_type;
1706
1707 BEGIN
1708 x_return_status := FND_API.G_RET_STS_SUCCESS;
1709
1710 open c_API_Name;
1711 fetch c_API_Name into x_pkg_name, x_proc_name;
1712
1713 if c_API_Name%NOTFOUND THEN
1714 x_return_status := FND_API.G_RET_STS_ERROR;
1715 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_API');
1716 FND_MSG_PUB.Add;
1717 end if;
1718 close c_API_Name;
1719
1720 EXCEPTION
1721
1722 WHEN OTHERS THEN
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724 RAISE;
1725 END Get_Api_Name;
1726
1727 --------------------------------------------------------------------------------
1728 --
1729 -- Procedure
1730 -- Handle_Error
1731 --
1732 ---------------------------------------------------------------------------------
1733
1734 PROCEDURE Handle_Error
1735 (p_itemtype IN VARCHAR2 ,
1736 p_itemkey IN VARCHAR2 ,
1737 p_msg_count IN NUMBER , -- Number of error Messages
1738 p_msg_data IN VARCHAR2 ,
1739 p_attr_name IN VARCHAR2,
1740 x_error_msg OUT NOCOPY VARCHAR2
1741 )
1742 IS
1743 l_msg_count NUMBER ;
1744 l_msg_data VARCHAR2(2000);
1745 l_final_data VARCHAR2(4000);
1746 l_msg_index NUMBER ;
1747 l_cnt NUMBER := 0 ;
1748 BEGIN
1749 -- Retriveing Error Message from FND_MSG_PUB
1750 -- Called by most of the procedures if it encounter error
1751
1752 WHILE l_cnt < p_msg_count
1753 LOOP
1754 FND_MSG_PUB.Get
1755 (p_msg_index => l_cnt + 1,
1756 p_encoded => FND_API.G_FALSE,
1757 p_data => l_msg_data,
1758 p_msg_index_out => l_msg_index ) ;
1759
1760 l_final_data := l_final_data ||l_msg_index||': '
1761 ||l_msg_data||fnd_global.local_chr(10) ;
1762 l_cnt := l_cnt + 1 ;
1763 END LOOP ;
1764
1765 x_error_msg := l_final_data;
1766
1767 WF_ENGINE.SetItemAttrText
1768 (itemtype => p_itemtype,
1769 itemkey => p_itemkey ,
1770 aname => p_attr_name,
1771 avalue => l_final_data );
1772
1773
1774 END Handle_Error;
1775
1776
1777 /*****************************************************************
1778 -- Private API Specifications
1779 *****************************************************************/
1780 -------------------------------------------------------------------------------
1781 -- Start of Comments
1782 -- NAME
1783 -- Check_Appl_Usg_Code
1784 --
1785 -- PURPOSE
1786 -- This Procedure will validate the
1787 -- application usage code
1788 -- Called By
1789 -- NOTES
1790 -- End of Comments
1791 -------------------------------------------------------------------------------
1792 PROCEDURE Check_Appl_Usg_Code(
1793 p_appl_usg_code IN VARCHAR2,
1794 x_return_status OUT NOCOPY VARCHAR2
1795 )
1796 IS
1797 l_count NUMBER;
1798
1799 CURSOR chk_appl_usg_code IS
1800 SELECT 1 FROM FND_LOOKUPS
1801 WHERE LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE'
1802 AND LOOKUP_CODE = p_appl_usg_code;
1803 BEGIN
1804 OPEN chk_appl_usg_code;
1805 FETCH chk_appl_usg_code INTO l_count;
1806 IF chk_appl_usg_code%NOTFOUND THEN
1807 CLOSE chk_appl_usg_code;
1808 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1809 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
1810 Fnd_Msg_Pub.ADD;
1811 END IF;
1812 x_return_status := Fnd_Api.g_ret_sts_error;
1813 RETURN;
1814 ELSE
1815 CLOSE chk_appl_usg_code;
1816 END IF;
1817 END Check_Appl_Usg_Code;
1818 -------------------------------------------------------------------------------
1819 -- Start of Comments
1820 -- NAME
1821 -- Get_User_Role
1822 --
1823 -- PURPOSE
1824 -- This Procedure will return the User role for
1825 -- the resource id sent
1826 -- Called By
1827 -- NOTES
1828 -- End of Comments
1829 -------------------------------------------------------------------------------
1830 PROCEDURE Get_User_Role(
1831 p_resource_id IN NUMBER,
1832 x_role_name OUT NOCOPY VARCHAR2,
1833 x_role_display_name OUT NOCOPY VARCHAR2 ,
1834 x_return_status OUT NOCOPY VARCHAR2)
1835 IS
1836 l_person_id number;
1837
1838 CURSOR c_resource IS
1842
1839 SELECT employee_id source_id
1840 FROM ahl_jtf_rs_emp_v
1841 WHERE resource_id = p_resource_id ;
1843 BEGIN
1844
1845 x_return_status := FND_API.G_RET_STS_SUCCESS;
1846
1847 -- Get Employee ID
1848 OPEN c_resource ;
1849 FETCH c_resource INTO l_person_id ;
1850
1851 IF c_resource%NOTFOUND THEN
1852 x_return_status := FND_API.G_RET_STS_ERROR;
1853 FND_MESSAGE.Set_Name('AHL','AHL_APRV_NO_RESOURCE');
1854 FND_MSG_PUB.Add;
1855
1856 END IF;
1857 CLOSE c_resource ;
1858
1859 -- Pass the Employee ID to get the Role
1860 WF_DIRECTORY.getrolename(
1861 p_orig_system => 'PER',
1862 p_orig_system_id => l_person_id ,
1863 p_name => x_role_name,
1864 p_display_name => x_role_display_name );
1865
1866 IF x_role_name is null then
1867 x_return_status := FND_API.G_RET_STS_ERROR;
1868 FND_MESSAGE.Set_Name('AHL','AHL_APRV_WF_NO_ROLE');
1869 FND_MSG_PUB.Add;
1870
1871 END IF;
1872
1873 END Get_User_Role;
1874
1875
1876 -------------------------------------------------------------------------------
1877 --
1878 -- Checks if there are more approvers required
1879 --
1880 -------------------------------------------------------------------------------
1881 PROCEDURE Check_Approval_Required(
1882 p_rule_id IN NUMBER,
1883 p_current_seq IN NUMBER,
1884 x_next_seq OUT NOCOPY NUMBER,
1885 x_required_flag OUT NOCOPY VARCHAR2)
1886 IS
1887
1888 CURSOR c_check_app IS
1889 SELECT approver_sequence
1890 FROM ahl_approvers
1891 WHERE approval_rule_id = p_rule_id
1892 AND approver_sequence > p_current_seq
1893 order by approver_sequence ;
1894
1895 BEGIN
1896 OPEN c_check_app;
1897 FETCH c_check_app
1898 INTO x_next_seq;
1899 if c_check_app%NOTFOUND THEN
1900 x_required_flag := FND_API.G_FALSE;
1901 ELSE
1902 x_required_flag := FND_API.G_TRUE;
1903 END IF;
1904 CLOSE c_check_app;
1905 END Check_Approval_Required;
1906
1907 END ahl_generic_aprv_pvt;