[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_CGR_WF_PKG
Source
1 PACKAGE BODY igs_as_cgr_wf_pkg AS
2 /* $Header: IGSAS54B.pls 120.1 2006/07/11 07:04:11 sepalani 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 Change Grade Request Submission has to be sent. This notification requires a
8 --response from Approver/ Admin/ Lead Instructor ( who ever is it, based upon the Organization
9 --hierarchy set by the Institute or Organization). Approver can Reject OR Approve the
10 --notification OR ask for More Information.
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 ) IS
19 l_api_name CONSTANT VARCHAR2 (30) := 'Select_Approver';
20 l_return_status VARCHAR2 (1);
21 l_uoo_id NUMBER (7) := wf_engine.getitemattrtext (
22 itemtype,
23 itemkey,
24 'UOO_ID'
25 );
26 l_requester_id NUMBER (15) := wf_engine.getitemattrtext (
27 itemtype,
28 itemkey,
29 'REQUESTER_ID'
30 );
31 l_instructor_id igs_ps_usec_tch_resp.instructor_id%TYPE;
32 l_user_name_app fnd_user.user_name%TYPE;
33 l_user_name_req fnd_user.user_name%TYPE;
34 CURSOR cur_user (lv_requester_id fnd_user.person_party_id%TYPE) IS
35 SELECT user_name
36 FROM fnd_user
37 WHERE person_party_id = lv_requester_id;
38 CURSOR cur_instruct (lv_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
39 SELECT instructor_id
40 FROM igs_ps_usec_tch_resp
41 WHERE lead_instructor_flag = 'Y'
42 AND uoo_id = lv_uoo_id;
43 CURSOR cur_user1 (lv_instructor_id fnd_user.person_party_id%TYPE) IS
44 SELECT user_name
45 FROM fnd_user
46 WHERE person_party_id = lv_instructor_id;
47 BEGIN
48 SAVEPOINT select_approver;
49 IF (funcmode = 'RUN') THEN
50 /* Requester User Name */
51 BEGIN
52 OPEN cur_user (l_requester_id);
53 FETCH cur_user INTO l_user_name_req;
54 CLOSE cur_user;
55 wf_engine.setitemattrtext (itemtype, itemkey, 'REQUESTER_USER_NAME', l_user_name_req);
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 resultout := 'FAILURE';
59 RETURN;
60 END;
61 /* Finding Lead Instructor and corresponding User Name */
62 BEGIN
63 OPEN cur_instruct (l_uoo_id);
64 FETCH cur_instruct INTO l_instructor_id;
65 CLOSE cur_instruct;
66 wf_engine.setitemattrtext (itemtype, itemkey, 'TO_USER_ID', l_instructor_id);
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 resultout := 'FAILURE';
70 RETURN;
71 END;
72 /* Approver User Name */
73 BEGIN
74 OPEN cur_user1 (l_instructor_id);
75 FETCH cur_user1 INTO l_user_name_app;
76 CLOSE cur_user1;
77 wf_engine.setitemattrtext (itemtype, itemkey, 'TO_USER', l_user_name_app);
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 resultout := 'FAILURE';
81 RETURN;
82 END;
83 IF l_return_status <> fnd_api.g_ret_sts_success THEN
84 RAISE fnd_api.g_exc_error;
85 END IF;
86 /* ########################################################################
87 Consulting Solution : Organizations / Institutions will set their organization's hierarchy, to
88 whom this notification has to be sent .
89 ######################################################################## */
90 resultout := 'SUCCESS';
91 RETURN;
92 END IF;
93 IF (funcmode = 'CANCEL') THEN
94 resultout := 'FAILURE';
95 RETURN;
96 END IF;
97 IF (funcmode NOT IN ('RUN', 'CANCEL')) THEN
98 resultout := 'FAILURE';
99 RETURN;
100 END IF;
101 EXCEPTION
102 WHEN fnd_api.g_exc_error THEN
103 --If execution error, rollback all database changes, generate message text
104 --and return failure status to the WF
105 ROLLBACK TO select_approver;
106 resultout := 'FAILURE';
107 RETURN;
108 WHEN NO_DATA_FOUND THEN
109 resultout := 'FAILURE';
110 RETURN;
111 WHEN OTHERS THEN
112 RAISE;
113 END select_approver;
114
115 PROCEDURE approve_request (
116 itemtype IN VARCHAR2,
117 itemkey IN VARCHAR2,
118 actid IN NUMBER,
119 funcmode IN VARCHAR2,
120 resultout OUT NOCOPY VARCHAR2
121 ) IS
122 l_api_name CONSTANT VARCHAR2 (30) := 'Approve_Request';
123 l_return_status VARCHAR2 (1);
124 l_rowid VARCHAR2 (25);
125 l_org_id igs_as_su_stmptout.org_id%TYPE;
126 l_person_id NUMBER (15) := wf_engine.getitemattrtext (
127 itemtype,
128 itemkey,
129 'PERSON_ID'
130 );
131 l_unit_code VARCHAR2 (10) := wf_engine.getitemattrtext (
132 itemtype,
133 itemkey,
134 'UNIT_CODE'
135 );
136 l_course_cd VARCHAR2 (6) := wf_engine.getitemattrtext (
137 itemtype,
138 itemkey,
139 'COURSE_CD'
140 );
141 l_cal_type VARCHAR2 (10) := wf_engine.getitemattrtext (
142 itemtype,
143 itemkey,
144 'CAL_TYPE'
145 );
146 l_ci_sequence_number NUMBER (6) := wf_engine.getitemattrtext (
147 itemtype,
148 itemkey,
149 'CI_SEQUENCE_NUMBER'
150 );
151 l_ci_start_dt DATE := wf_engine.getitemattrtext (
152 itemtype,
153 itemkey,
154 'CI_START_DT'
155 );
156 l_ci_end_dt DATE := wf_engine.getitemattrtext (
157 itemtype,
158 itemkey,
159 'CI_END_DT'
160 );
161 l_grading_schema VARCHAR2 (10) := wf_engine.getitemattrtext (
162 itemtype,
163 itemkey,
164 'GRADING_SCHEMA'
165 );
166 l_change_grade_version_num NUMBER (6) := wf_engine.getitemattrtext (
167 itemtype,
168 itemkey,
169 'CHANGE_GRADE_VERSION_NUM'
170 );
171 l_change_grade VARCHAR2 (5) := wf_engine.getitemattrtext (
172 itemtype,
173 itemkey,
174 'CHANGE_GRADE'
175 );
176 l_change_mark NUMBER (6, 3) := wf_engine.getitemattrtext (
177 itemtype,
178 itemkey,
179 'CHANGE_MARK'
180 );
181 l_teach_cal_type VARCHAR2 (10) := wf_engine.getitemattrtext (
182 itemtype,
183 itemkey,
184 'TEACH_CAL_TYPE'
185 );
186 l_teach_ci_sequence_number NUMBER (6) := wf_engine.getitemattrtext (
187 itemtype,
188 itemkey,
189 'TEACH_CI_SEQUENCE_NUMBER'
190 );
191 l_request_date DATE := wf_engine.getitemattrtext (
192 itemtype,
193 itemkey,
194 'REQUEST_DATE'
195 );
196 l_uoo_id NUMBER (7) := wf_engine.getitemattrtext (
197 itemtype,
198 itemkey,
199 'UOO_ID'
200 );
201 l_grading_period_cd VARCHAR2 (30) := wf_engine.getitemattrtext (
202 itemtype,
203 itemkey,
204 'GRADING_PERIOD_CD'
205 );
206 l_approver_id NUMBER (15) := wf_engine.getitemattrtext (
207 itemtype,
208 itemkey,
209 'TO_USER_ID'
210 );
211 l_comment VARCHAR2 (360) := wf_engine.getitemattrtext (
212 itemtype,
213 itemkey,
214 'WF_NOTE'
215 );
216 l_sysdate DATE;
217 BEGIN
218 SAVEPOINT approve_request;
219 IF (l_grading_period_cd = 'EARLY_FINAL') THEN
220 l_grading_period_cd := 'FINAL';
221 END IF;
222
223 IF (funcmode = 'RUN') THEN
224 l_sysdate := SYSDATE;
225 /**************************Updating Change Grade Request Table *************************/
226 UPDATE igs_as_chn_grd_req
227 SET current_status = 'APPROVED',
228 approver_id = l_approver_id,
229 approver_date = l_sysdate,
230 approver_comments = l_comment
231 WHERE person_id = l_person_id
232 AND course_cd = l_course_cd
233 AND uoo_id = l_uoo_id
234 AND current_status = 'IN PROGRESS';
235 /***************** Inserting Record in Student Unit Attempt Outcome Table**************/
236 igs_as_su_stmptout_pkg.insert_row (
237 x_rowid => l_rowid,
238 x_org_id => NULL,
239 x_person_id => l_person_id,
240 x_course_cd => l_course_cd,
241 x_unit_cd => l_unit_code,
242 x_cal_type => l_teach_cal_type,
243 x_ci_sequence_number => l_teach_ci_sequence_number,
244 x_outcome_dt => l_sysdate,
245 x_ci_start_dt => l_ci_start_dt,
246 x_ci_end_dt => l_ci_end_dt,
247 x_grading_schema_cd => l_grading_schema,
248 x_version_number => l_change_grade_version_num,
249 x_grade => l_change_grade,
250 x_s_grade_creation_method_type => 'KEYED',
251 x_finalised_outcome_ind => 'N',
252 x_mark => l_change_mark,
253 x_number_times_keyed => NULL,
254 x_translated_grading_schema_cd => NULL,
255 x_translated_version_number => NULL,
256 x_translated_grade => NULL,
257 x_translated_dt => NULL,
258 x_mode => 'R',
259 x_grading_period_cd => l_grading_period_cd,
263 x_attribute3 => NULL,
260 x_attribute_category => NULL,
261 x_attribute1 => NULL,
262 x_attribute2 => NULL,
264 x_attribute4 => NULL,
265 x_attribute5 => NULL,
266 x_attribute6 => NULL,
267 x_attribute7 => NULL,
268 x_attribute8 => NULL,
269 x_attribute9 => NULL,
270 x_attribute10 => NULL,
271 x_attribute11 => NULL,
272 x_attribute12 => NULL,
273 x_attribute13 => NULL,
274 x_attribute14 => NULL,
275 x_attribute15 => NULL,
276 x_attribute16 => NULL,
277 x_attribute17 => NULL,
278 x_attribute18 => NULL,
279 x_attribute19 => NULL,
280 x_attribute20 => NULL,
281 x_incomp_deadline_date => NULL,
282 x_incomp_grading_schema_cd => NULL,
283 x_incomp_version_number => NULL,
284 x_incomp_default_grade => NULL,
285 x_incomp_default_mark => NULL,
286 x_comments => NULL,
287 x_uoo_id => l_uoo_id,
288 x_mark_capped_flag => 'N',
289 x_release_date => NULL,
290 x_manual_override_flag => 'N',
291 x_show_on_academic_histry_flag => 'Y'
292 );
293 IF l_return_status <> fnd_api.g_ret_sts_success THEN
294 RAISE fnd_api.g_exc_error;
295 END IF;
296 /****************** Start : Repeat Process / Translation / Finalization Process ***********/
297 igs_as_finalize_grade.finalize_process (
298 l_uoo_id,
299 l_person_id,
300 l_course_cd,
301 l_unit_code,
302 l_teach_cal_type,
303 l_teach_ci_sequence_number
304 );
305 /***************** End : Repeat Process / Translation / Finalization Process *************/
306 resultout := 'Y';
307 RETURN;
308 END IF;
309 IF (funcmode = 'CANCEL') THEN
310 resultout := 'N';
311 RETURN;
312 END IF;
313 IF (funcmode NOT IN ('RUN', 'CANCEL')) THEN
314 resultout := 'N';
315 RETURN;
316 END IF;
317 EXCEPTION
318 WHEN fnd_api.g_exc_error THEN
319 --If execution error, rollback all database changes, generate message text
320 --and return failure status to the WF
321 ROLLBACK TO approve_request;
322 resultout := 'N';
323 RETURN;
324 WHEN OTHERS THEN
325 RAISE;
326 END approve_request;
327
328 PROCEDURE reject_request (
329 itemtype IN VARCHAR2,
330 itemkey IN VARCHAR2,
331 actid IN NUMBER,
332 funcmode IN VARCHAR2,
333 resultout OUT NOCOPY VARCHAR2
334 ) IS
335 l_api_name CONSTANT VARCHAR2 (30) := 'Reject_Request';
336 l_return_status VARCHAR2 (1);
337 l_person_id NUMBER (15) := wf_engine.getitemattrtext (itemtype, itemkey, 'PERSON_ID');
338 l_unit_code VARCHAR2 (10) := wf_engine.getitemattrtext (itemtype, itemkey, 'UNIT_CODE');
339 l_course_cd VARCHAR2 (6) := wf_engine.getitemattrtext (itemtype, itemkey, 'COURSE_CD');
340 l_teach_cal_type VARCHAR2 (10) := wf_engine.getitemattrtext (itemtype, itemkey, 'TEACH_CAL_TYPE');
341 l_teach_ci_sequence_number NUMBER (6) := wf_engine.getitemattrtext (
342 itemtype,
343 itemkey,
344 'TEACH_CI_SEQUENCE_NUMBER'
345 );
346 l_request_date DATE := wf_engine.getitemattrtext (itemtype, itemkey, 'REQUEST_DATE');
347 l_approver_id NUMBER (15) := wf_engine.getitemattrtext (itemtype, itemkey, 'TO_USER_ID');
348 l_comment VARCHAR2 (360) := wf_engine.getitemattrtext (itemtype, itemkey, 'WF_NOTE');
349 -- anilk, 22-Apr-2003, Bug# 2829262
350 l_uoo_id NUMBER (7) := wf_engine.getitemattrtext (itemtype, itemkey, 'UOO_ID');
351 BEGIN
352 SAVEPOINT reject_request;
353 /**************************Start : Updating Change Grade Request Table *********************/
354 UPDATE igs_as_chn_grd_req
355 SET current_status = 'REJECTED',
356 approver_id = l_approver_id,
357 approver_date = SYSDATE,
358 approver_comments = l_comment
359 WHERE person_id = l_person_id
360 AND course_cd = l_course_cd
361 AND uoo_id = l_uoo_id
362 AND current_status = 'IN PROGRESS';
363 /**************************End : Updating Change Grade Request Table *********************/
364 IF l_return_status <> fnd_api.g_ret_sts_success THEN
365 RAISE fnd_api.g_exc_error;
366 END IF;
367 EXCEPTION
368 WHEN fnd_api.g_exc_error THEN
369 --If execution error, rollback all database changes, generate message text
370 --and return failure status to the WF
371 ROLLBACK TO approve_request;
372 WHEN OTHERS THEN
373 RAISE;
374 END reject_request;
375
376 PROCEDURE need_information (
377 itemtype IN VARCHAR2,
378 itemkey IN VARCHAR2,
379 actid IN NUMBER,
380 funcmode IN VARCHAR2,
381 resultout OUT NOCOPY VARCHAR2
382 ) IS
383 l_api_name CONSTANT VARCHAR2 (30) := 'Need_Information';
384 l_return_status VARCHAR2 (1);
385 l_person_id NUMBER (15) := wf_engine.getitemattrtext (itemtype, itemkey, 'PERSON_ID');
386 l_unit_code VARCHAR2 (10) := wf_engine.getitemattrtext (itemtype, itemkey, 'UNIT_CODE');
387 l_course_cd VARCHAR2 (6) := wf_engine.getitemattrtext (itemtype, itemkey, 'COURSE_CD');
388 l_teach_cal_type VARCHAR2 (10) := wf_engine.getitemattrtext (itemtype, itemkey, 'TEACH_CAL_TYPE');
389 l_teach_ci_sequence_number NUMBER (6) := wf_engine.getitemattrtext (
390 itemtype,
391 itemkey,
392 'TEACH_CI_SEQUENCE_NUMBER'
393 );
394 l_request_date DATE := wf_engine.getitemattrtext (itemtype, itemkey, 'REQUEST_DATE');
395 l_approver_id NUMBER (15) := wf_engine.getitemattrtext (itemtype, itemkey, 'TO_USER_ID');
396 l_comment VARCHAR2 (360) := wf_engine.getitemattrtext (itemtype, itemkey, 'WF_NOTE');
397 -- anilk, 22-Apr-2003, Bug# 2829262
398 l_uoo_id NUMBER (7) := wf_engine.getitemattrtext (itemtype, itemkey, 'UOO_ID');
399 BEGIN
400 SAVEPOINT need_information;
401 /**************************Start : Updating Change Grade Request Table *************************/
402 UPDATE igs_as_chn_grd_req
403 SET current_status = 'NEED MORE INFO',
404 approver_id = l_approver_id,
405 approver_date = SYSDATE,
406 approver_comments = l_comment
407 WHERE person_id = l_person_id
408 AND course_cd = l_course_cd
409 AND uoo_id = l_uoo_id
410 AND current_status = 'IN PROGRESS';
411 /**************************End : Updating Change Grade Request Table *************************/
412 IF l_return_status <> fnd_api.g_ret_sts_success THEN
413 RAISE fnd_api.g_exc_error;
414 END IF;
415 EXCEPTION
416 WHEN fnd_api.g_exc_error THEN
417 --If execution error, rollback all database changes, generate message text
418 --and return failure status to the WF
419 ROLLBACK TO approve_request;
420 WHEN OTHERS THEN
421 RAISE;
422 END need_information;
423 END igs_as_cgr_wf_pkg;