1 PACKAGE BODY IGS_AS_ATT_WF_PKG AS
2 /* $Header: IGSAS52B.pls 120.0 2005/07/05 12:38:45 appldev noship $ */
3
4 /* ***********************************************************************************************************/
5 -- Procedure : Select_Approver
6 --This Procedure relates to selecting an Approver (Admin or Lead Instructor), to whom this
7 --Notification of Attendance Submission has to be sent. It is just a notification, it does not
8 --require any response from Approver/ Admin/ Lead Instrustor ( who ever is it, based upon the
9 --Organization hierarch set by the Institute or Organization)
10 /* **********************************************************************************************************/
11
12 PROCEDURE Select_Approver (
13 Itemtype IN VARCHAR2,
14 Itemkey IN VARCHAR2,
15 Actid IN NUMBER,
16 Funcmode IN VARCHAR2,
17 Resultout OUT NOCOPY VARCHAR2
18 )
19 IS
20
21
22
23 l_api_name CONSTANT VARCHAR2(30) := 'Select_Approver' ;
24 l_return_status VARCHAR2(1);
25 l_uoo_id NUMBER(7) := wf_engine.getItemAttrText(itemtype, itemkey,'UOO_ID');
26 l_INSTRUCTOR_ID IGS_PS_USEC_TCH_RESP.INSTRUCTOR_ID%TYPE;
27 l_user_name FND_USER.USER_NAME%TYPE;
28
29
30 CURSOR cur_instruct(lv_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
31 SELECT INSTRUCTOR_ID
32 FROM IGS_PS_USEC_TCH_RESP
33 WHERE LEAD_INSTRUCTOR_FLAG = 'Y' AND
34 UOO_ID = lv_uoo_id;
35
36 CURSOR cur_user(lv_instructor_id igs_ps_usec_tch_resp.instructor_id%TYPE) IS
37 SELECT user_name
38 FROM FND_USER
39 WHERE person_party_id = lv_instructor_id ;
40
41
42 BEGIN
43
44 SAVEPOINT Select_Approver;
45
46 IF ( funcmode = 'RUN' ) THEN
47
48 /* Finding Lead Instructor and corresponding User Name */
49
50
51 OPEN cur_instruct(l_uoo_id);
52 FETCH cur_instruct INTO l_instructor_id ;
53 CLOSE cur_instruct;
54
55 OPEN cur_user(l_instructor_id);
56 FETCH cur_user INTO l_user_name ;
57 CLOSE cur_user;
58
59
60
61 wf_engine.setItemAttrText(itemtype, itemkey,'TO_USER_ID',l_INSTRUCTOR_ID);
62 wf_engine.setItemAttrText(itemtype, itemkey,'TO_USER',l_user_name);
63
64 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
65 raise FND_API.G_EXC_ERROR;
66 END IF;
67
68 /* ########################################################################
69
70 Consulting Solution : Organizations / Institutions will set their organization?s hierarchy, to
71 whom this notification has to be sent .
72
73 ######################################################################## */
74
75 resultout := 'SUCCESS';
76 return;
77 END IF ;
78
79 IF ( funcmode = 'CANCEL' ) THEN
80 resultout := 'FAILURE';
81 return;
82 END IF;
83
84 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
85 resultout := 'FAILURE';
86 return;
87 END IF;
88
89 EXCEPTION
90 WHEN FND_API.G_EXC_ERROR THEN
91 --If execution error, rollback all database changes, generate message text
92 --and return failure status to the WF
93 ROLLBACK TO Select_Approver;
94 resultout := 'FAILURE';
95
96 return;
97
98 WHEN NO_DATA_FOUND THEN
99 resultout := 'FAILURE';
100 return;
101
102 WHEN OTHERS THEN
103
104 RAISE ;
105
106 END Select_Approver ;
107
108 END IGS_AS_ATT_WF_PKG;
[Home] [Help]