1 PACKAGE BODY IGS_AS_GR_WF_PKG AS
2 /* $Header: IGSAS55B.pls 120.0 2005/07/05 11:29:44 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 Grade Submission has to be sent. It is just a notification, it does not
8 --require any response from Approver/ Admin/ Lead Instructor ( who ever is it, based upon the
9 --Organization hierarch set by the Institute or Organization)
10 /* **********************************************************************************************************/
11
12
13 PROCEDURE Select_Approver (
14 Itemtype IN VARCHAR2,
15 Itemkey IN VARCHAR2,
16 Actid IN NUMBER,
17 Funcmode IN VARCHAR2,
18 Resultout OUT NOCOPY VARCHAR2
19 )
20 /*=============================================================================================================+
21 |
22 | Change History
23 | Who When What
24 | Aiyer 07-25-2002 Fix done for the bug 2403814.
25 | Populate the workflow requestor item_attribute when a lead instructor record
26 | has not set up in the system.
27 ===============================================================================================================+*/
28 IS
29
30 CURSOR c_set_requestor (cp_requestor_id FND_USER.person_party_id%TYPE)
31 IS
32 SELECT
33 user_name
34 FROM
35 fnd_user
36 WHERE
37 person_party_id = cp_requestor_id;
38
39 CURSOR c_get_lead_instructor (cp_uoo_id IGS_EN_SU_ATTEMPT_ALL.UOO_ID%TYPE)
40 IS
41 SELECT
42 pur.instructor_id instructor_id,
43 fu.user_name user_name
44 FROM
45 igs_ps_usec_tch_resp pur,
46 fnd_user fu
47 WHERE
48 fu.person_party_id = pur.instructor_id
49 AND
50 pur.lead_instructor_flag = 'Y'
51 AND
52 pur.uoo_id = cp_uoo_id;
53
54 l_api_name CONSTANT VARCHAR2(30) := 'Select_Approver' ;
55 l_return_status VARCHAR2(1);
56 l_INSTRUCTOR_ID IGS_PS_USEC_TCH_RESP.INSTRUCTOR_ID%TYPE;
57 l_UOO_ID NUMBER(7) := wf_engine.getItemAttrText(itemtype, itemkey,'UOO_ID');
58 rec_c_set_requestor C_SET_REQUESTOR%ROWTYPE;
59 rec_c_get_lead_instructor C_GET_LEAD_INSTRUCTOR%ROWTYPE;
60
61 BEGIN
62
63 SAVEPOINT Select_Approver;
64
65 IF ( funcmode = 'RUN' ) THEN
66
67 /******************************** Validation 1 - Set value for the Workflow Attribute REQUESTER_USER_NAME *****************************/
68
69 OPEN c_set_requestor (wf_engine.getItemAttrText(itemtype, itemkey,'FROM_USER_ID'));
70 FETCH c_set_requestor INTO rec_c_set_requestor;
71 CLOSE c_set_requestor;
72 wf_engine.setItemAttrText(itemtype, itemkey,'FROM_USER',rec_c_set_requestor.user_name);
73
74 /******************************** Validation 2 - Set the Value For The Workflow Attributes TO_USER_ID ,TO_USER****************************/
75
76 OPEN c_get_lead_instructor(wf_engine.getItemAttrText(itemtype, itemkey,'UOO_ID'));
77 FETCH c_get_lead_instructor INTO rec_c_get_lead_instructor;
78
79 IF c_get_lead_instructor%NOTFOUND THEN
80 /* If Lead Instructor not found then return failure */
81 CLOSE c_get_lead_instructor;
82 resultout := 'COMPLETE:FAILURE';
83 RETURN;
84 ELSE
85
86 /* Finding Lead Instructor and corresponding User Name */
87 wf_engine.setItemAttrText(itemtype, itemkey, 'TO_USER_ID', rec_c_get_lead_instructor.instructor_id);
88 wf_engine.setItemAttrText(itemtype, itemkey, 'TO_USER' , rec_c_get_lead_instructor.user_name );
89 END IF;
90
91 CLOSE c_get_lead_instructor;
92 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
93 raise FND_API.G_EXC_ERROR;
94 END IF;
95 resultout := 'COMPLETE:SUCCESS';
96 RETURN;
97
98 END IF;
99
100 /* ########################################################################
101
102 Consulting Solution : Organizations / Institutions will set their organization?s hierarchy, to
103 whom this notification has to be sent .
104
105 ######################################################################## */
106
107 IF ( funcmode = 'CANCEL' ) THEN
108 resultout := 'COMPLETE:FAILURE';
109 return;
110 END IF;
111
112 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
113 resultout := 'COMPLETE:FAILURE';
114 return;
115 END IF;
116
117 EXCEPTION
118
119 WHEN FND_API.G_EXC_ERROR THEN
120 --If execution error, rollback all database changes, generate message text
121 --and return failure status to the WF
122 ROLLBACK TO Select_Approver;
123 resultout := 'COMPLETE:FAILURE';
124 return;
125
126 WHEN OTHERS THEN
127 RAISE ;
128
129 END Select_Approver ;
130
131 PROCEDURE Repeat_Process ( Itemtype IN VARCHAR2,
132 Itemkey IN VARCHAR2,
133 Actid IN NUMBER,
134 Funcmode IN VARCHAR2,
135 Resultout OUT NOCOPY VARCHAR2
136 )
137 IS
138
139 l_api_name CONSTANT VARCHAR2(30) := 'Repeat_Process' ;
140 l_return_status VARCHAR2(1);
141 l_PERSON_ID NUMBER(15) := wf_engine.getItemAttrText(itemtype, itemkey,'PERSON_ID');
142 l_UNIT_CD VARCHAR2(10) := wf_engine.getItemAttrText(itemtype, itemkey,'UNIT_CD');
143 l_COURSE_CD VARCHAR2(6) := wf_engine.getItemAttrText(itemtype, itemkey,'COURSE_CD');
144 l_TEACH_CAL_TYPE VARCHAR2(10) := wf_engine.getItemAttrText(itemtype, itemkey,'TEACH_CAL_TYPE');
145 l_TEACH_CI_SEQUENCE_NUMBER NUMBER(6) := wf_engine.getItemAttrText(itemtype, itemkey,'TEACH_CI_SEQUENCE_NUMBER');
146 l_UOO_ID NUMBER(7) := wf_engine.getItemAttrText(itemtype, itemkey,'UOO_ID');
147 BEGIN
148
149 SAVEPOINT Repeat_Process;
150
151 IF ( funcmode = 'RUN' ) THEN
152
153 /****************** Start : Repeat Process / Translation / Finalization Process ***********/
154
155 IGS_AS_FINALIZE_GRADE.finalize_process( l_UOO_ID,
156 l_PERSON_ID,
157 l_COURSE_CD,
158 l_UNIT_CD,
159 l_TEACH_CAL_TYPE,
160 l_TEACH_CI_SEQUENCE_NUMBER
161 );
162
163 /***************** End : Repeat Process / Translation / Finalization Process *************/
164 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
165 raise FND_API.G_EXC_ERROR;
166 END IF;
167 resultout := 'Y';
168 return;
169 IF ( funcmode = 'CANCEL' ) THEN
170 resultout := 'N';
171 return;
172 END IF;
173
174 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
175 resultout := 'N';
176 return;
177 END IF;
178 END IF;
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 --If execution error, rollback all database changes, generate message text
182 --and return failure status to the WF
183 ROLLBACK TO Repeat_Process;
184
185 WHEN OTHERS THEN
186
187 RAISE ;
188
189 END Repeat_Process;
190
191
192 END IGS_AS_GR_WF_PKG;