1 PACKAGE BODY igs_ps_fac_credt_wrkload AS
2 /* $Header: IGSPS74B.pls 120.1 2006/02/16 08:47:08 sommukhe noship $
3 CHANGE HISTORY
4 WHO : jbegum
5 WHEN : 02-June-2003
6 WHAT : modified functions calc_total_work_load_lecture,calc_total_work_load_lab
7 and calc_total_work_load as part of bug#2972950
8 WHO : ayedubat
9 WHEN : 24-MAY-2001
10 WHAT : Added two new Functions ,calc_total_work_load_lab and
11 calc_total_work_load_lecture
12 CHANGE HISTORY
13 WHO : pradhakr
14 WHEN : 05-Jun-2001
15 WHAT : Added one new Function ,calc_teach_work_load
16 Who When What
17 smvk 29-Apr-2004 Bug # 3568858. Created the function validate_workload and get_validation_type.
18 ***********************************************************************************************/
19
20
21
22 FUNCTION prgp_get_lead_instructor(
23 p_uoo_id IN NUMBER)
24 RETURN VARCHAR2
25 IS
26
27 /*************************************************************
28 Created By : venagara
29 Date Created By : 10-May-2000
30 Purpose :
31 Know limitations, enhancements or remarks
32 Change History
33 Who When What
34 (reverse chronological order - newest change first)
35 sommukhe 14-FEB-2006 Bug #3104276, replaced igs_pe_person with igs_pe_person_base_v for cursor cur_lead_instructor
36 ***************************************************************/
37
38 CURSOR cur_lead_instructor IS
39 SELECT pe.last_name || ', ' || pe.title || ' ' || NVL(pe.known_as,pe.first_name) instructor_name
40 FROM igs_ps_usec_tch_resp utr,
41 igs_pe_person_base_v pe,
42 igs_pe_person_id_typ ppi,
43 igs_pe_alt_pers_id pit
44 WHERE utr.instructor_id = pe.person_id AND
45 utr.uoo_id = p_uoo_id AND
46 pe.person_id = pit.pe_person_id (+) AND
47 pit.person_id_type = ppi.person_id_type AND
48 ppi.preferred_ind = 'Y' AND
49 utr.lead_instructor_flag = 'Y';
50 /* In this query, the IGS_PE_PERSON_V was demormalised to prevent pragma
51 restriction error */
52
53 lv_instructor_name VARCHAR2(30) DEFAULT NULL;
54
55 BEGIN
56 FOR cur_leads IN cur_lead_instructor LOOP
57 lv_instructor_name := cur_leads.instructor_name;
58 END LOOP;
59
60 RETURN lv_instructor_name;
61
62 END prgp_get_lead_instructor;
63
64
65 FUNCTION calc_total_credit_point (
66 p_instructor_id IN NUMBER)
67 RETURN NUMBER IS
68
69 /*************************************************************
70 Created By : venagara
71 Date Created By : 10-May-2000
72 Purpose :
73 Know limitations, enhancements or remarks
74 Change History
75 Who When / What
76 sarakshi 24-Jun-2003 Enh#2930935,removed the cursor cur_unit_version ad modified
77 cursor cur_cp and the usage of the cursor accordingly
78 (reverse chronological order - newest change first)
79 ***************************************************************/
80
81 CURSOR cur_uoo (cp_instructor_id IN NUMBER) IS
82 SELECT DISTINCT (uoo_id) uoo_id
83 FROM igs_ps_usec_tch_resp utr
84 WHERE instructor_id = cp_instructor_id;
85
86 CURSOR cur_cp (cp_uoo_id IN VARCHAR2) IS
87 SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points) enrolled_credit_points
88 FROM igs_ps_unit_ver uv,
89 igs_ps_unit_ofr_opt uoo,
90 igs_ps_usec_cps cps
91 WHERE uoo.uoo_id=cps.uoo_id(+)
92 AND uoo.unit_cd=uv.unit_cd
93 AND uoo.version_number=uv.version_number
94 AND uoo.uoo_id=cp_uoo_id;
95
96 lv_credit_points NUMBER DEFAULT 0;
97 lv_total_credit_points NUMBER DEFAULT 0;
98
99 BEGIN
100
101 FOR cur_uoos IN cur_uoo (p_instructor_id) LOOP
102 FOR cur_cps IN cur_cp (cur_uoos.uoo_id) LOOP
103 lv_total_credit_points := lv_total_credit_points + cur_cps.enrolled_credit_points;
104 END LOOP;
105 END LOOP;
106
107 RETURN lv_total_credit_points;
108
109 END calc_total_credit_point;
110
111 FUNCTION calc_total_work_load (
112 p_instructor_id IN NUMBER)
113 RETURN NUMBER IS
114
115 /*************************************************************
116 Created By : venagara
117 Date Created By : 10-May-2000
118 Purpose :
119 Know limitations, enhancements or remarks
120 Change History
121 Who When What
122 jbegum 02-June-2003 Enh#2972950,modified the cursor cur_wl
123
124 (reverse chronological order - newest change first)
125 ***************************************************************/
126
127 CURSOR cur_wl (cp_instructor_id IN NUMBER) IS
128 SELECT SUM(instructional_load) total_work_load
129 FROM igs_ps_usec_tch_resp utr
130 WHERE utr.confirmed_flag='Y'
131 AND utr.instructor_id = cp_instructor_id;
132
133 lv_total_work_load NUMBER := 0;
134 BEGIN
135 FOR cur_wls IN cur_wl(p_instructor_id) LOOP
136 lv_total_work_load := cur_wls.total_work_load;
137 END LOOP;
138 RETURN lv_total_work_load;
139 END calc_total_work_load;
140
141 FUNCTION calc_total_work_load_lab (
142 p_instructor_id IN NUMBER)
143 RETURN NUMBER IS
144
145 /*************************************************************
146 Created By : ayedubat
147 Date Created By : 24-May-2001
148 Purpose :According to the enhancements proposed in PSP001-US DLD.
149 Know limitations, enhancements or remarks
150 Change History
151 Who When What
152 jbegum 02-June-2003 Enh#2972950,modified the cursor cur_wl_lab
153
154 (reverse chronological order - newest change first)
155 ***************************************************************/
156
157 CURSOR cur_wl_lab (cp_instructor_id IN NUMBER) IS
158 SELECT SUM(instructional_load_lab) total_work_load_lab
159 FROM igs_ps_usec_tch_resp utr
160 WHERE utr.confirmed_flag='Y'
161 AND utr.instructor_id = cp_instructor_id;
162
163 lv_total_work_load_lab NUMBER := 0;
164 BEGIN
165 -- caluculating the total lab work load
166 FOR cur_wls_lab IN cur_wl_lab(p_instructor_id) LOOP
167 lv_total_work_load_lab := cur_wls_lab.total_work_load_lab;
168 END LOOP;
169 RETURN lv_total_work_load_lab;
170 END calc_total_work_load_lab;
171
172 FUNCTION calc_total_work_load_lecture (
173 p_instructor_id IN NUMBER)
174 RETURN NUMBER IS
175
176 /*************************************************************
177 Created By : ayedubat
178 Date Created By : 24-May-2001
179 Purpose :According to the enhancements proposed in PSP001-US DLD.
180 Know limitations, enhancements or remarks
181 Change History
182 Who When What
183 jbegum 02-June-2003 Enh#2972950,modified the cursor cur_wl_lecture
184
185 (reverse chronological order - newest change first)
186 ***************************************************************/
187
188 CURSOR cur_wl_lecture (cp_instructor_id IN NUMBER) IS
189 SELECT SUM(instructional_load_lecture) total_work_load_lecture
190 FROM igs_ps_usec_tch_resp utr
191 WHERE utr.confirmed_flag='Y'
192 AND utr.instructor_id = cp_instructor_id;
193
194 lv_total_work_load_lecture NUMBER DEFAULT 0;
195 BEGIN
196 -- caluculating the total lab work lecture
197 FOR cur_wls_lecture IN cur_wl_lecture(p_instructor_id) LOOP
198 lv_total_work_load_lecture := cur_wls_lecture.total_work_load_lecture;
199 END LOOP;
200 RETURN lv_total_work_load_lecture;
201 END calc_total_work_load_lecture;
202
203 PROCEDURE calculate_teach_work_load (
204 p_uoo_id IN igs_ps_usec_tch_resp_v.uoo_id%TYPE,
205 p_percent_allocation IN igs_ps_usec_tch_resp_v.percentage_allocation%TYPE,
206 p_wl_lab OUT NOCOPY igs_ps_usec_tch_resp_v.instructional_load_lab%TYPE,
207 p_wl_lecture OUT NOCOPY igs_ps_usec_tch_resp_v.instructional_load_lecture%TYPE,
208 p_wl_other OUT NOCOPY igs_ps_usec_tch_resp_v.instructional_load%TYPE
209 ) IS
210
211 ------------------------------------------------------------------
212 --Created by : pradhakr, Oracle IDC
213 --Date created: 05/06/2001
214 --
215 --Purpose:
216 -- This procedure calculates the workload if there is any
217 -- change in the percentage allocation.
218 --Known limitations/enhancements and/or remarks:
219 --
220 --Change History:
221 --Who When What
222 --smvk 01-Oct-2003 Bug # 3164752. Added the cursor c_unit_workload and it usage.
223 -------------------------------------------------------------------
224
225 CURSOR cur_workload IS
226 SELECT work_load_cp_lecture,
227 work_load_cp_lab,
228 work_load_other
229 FROM igs_ps_usec_cps uscp
230 WHERE uscp.uoo_id = p_uoo_id;
231
232 CURSOR c_unit_workload (cp_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
233 SELECT work_load_cp_lecture,
234 work_load_cp_lab,
235 work_load_other
236 FROM igs_ps_unit_ver_all unit,
237 igs_ps_unit_ofr_opt_all usec
238 WHERE unit.unit_cd = usec.unit_cd
239 AND unit.version_number = usec.version_number
240 AND usec.uoo_id = cp_n_uoo_id;
241
242 l_cur_workload_row cur_workload%ROWTYPE;
243 rec_unit_workload c_unit_workload%ROWTYPE;
244
245 BEGIN
246 OPEN cur_workload;
247 FETCH cur_workload INTO l_cur_workload_row;
248 IF cur_workload%FOUND THEN
249 p_wl_lecture := ((p_percent_allocation/100) * l_cur_workload_row.work_load_cp_lecture);
250 p_wl_lab := ((p_percent_allocation/100) * l_cur_workload_row.work_load_cp_lab);
251 p_wl_other := ((p_percent_allocation/100) * l_cur_workload_row.work_load_other);
252 ELSE -- Added as a part of
253 OPEN c_unit_workload(p_uoo_id);
254 FETCH c_unit_workload INTO rec_unit_workload;
255 IF c_unit_workload%FOUND THEN
256 p_wl_lecture := ((p_percent_allocation/100) * rec_unit_workload.work_load_cp_lecture);
257 p_wl_lab := ((p_percent_allocation/100) * rec_unit_workload.work_load_cp_lab);
258 p_wl_other := ((p_percent_allocation/100) * rec_unit_workload.work_load_other);
259 END IF;
260 CLOSE c_unit_workload;
261 END IF;
262 CLOSE cur_workload;
263
264 END calculate_teach_work_load;
265
266 FUNCTION validate_workload(p_n_uoo_id IN NUMBER,
267 p_n_tot_wl_lec OUT NOCOPY NUMBER,
268 p_n_tot_wl_lab OUT NOCOPY NUMBER,
269 p_n_tot_wl OUT NOCOPY NUMBER) RETURN BOOLEAN IS
270
271 /***********************************************************************************************
272
273 Created By: smvk
274 Date Created By: 29-Apr-2004
275 Purpose: This procedure validates the workload defined for the instructors in unit section teaching responsibilites
276 should match with the workload defined for the unit section in unit section credit points.
277 if the unit section credit points is not defined it should match with workload defined at unit level(As per Inheritance logic).
278 Procedure returns true if the values matches otherwise false.
279
280 Known limitations,enhancements,remarks:
281
282 Change History
283 sarakshi 14-May-2004 bug#3629483, added NVL to the cursor c_usec_workload and c_unit_workload
284 Who When What
285 ***********************************************************************************************/
286
287 -- Cursor to pick up lecture / laboratory / Other workload from unit section level
288 CURSOR c_usec_workload (cp_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
289 SELECT NVL(work_load_cp_lecture,-999),
290 NVL(work_load_cp_lab,-999),
291 NVL(work_load_other,-999)
292 FROM igs_ps_usec_cps uscp
293 WHERE uscp.uoo_id = cp_n_uoo_id;
294
295 -- Cursor to pick up lecture / laboratory / Other workload from unit section level
296 CURSOR c_unit_workload (cp_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
297 SELECT NVL(work_load_cp_lecture,-999),
298 NVL(work_load_cp_lab,-999),
299 NVL(work_load_other,-999)
300 FROM igs_ps_unit_ver_all unit,
301 igs_ps_unit_ofr_opt_all usec
302 WHERE unit.unit_cd = usec.unit_cd
303 AND unit.version_number = usec.version_number
304 AND usec.uoo_id = cp_n_uoo_id;
305
306 -- Cursor to pick up lecture / laboratory / other workload defined at unit section teaching responsibility.
307 CURSOR c_tr_workload(cp_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
308 SELECT NVL(SUM (instructional_load_lecture),-999) lecture_workload,
309 NVL(SUM (instructional_load_lab),-999) laboratory_workload,
310 NVL(SUM (instructional_load),-999) other_workload
311 FROM igs_ps_usec_tch_resp
312 WHERE uoo_id = cp_n_uoo_id
313 AND confirmed_flag = 'Y';
314
315 rec_tr_workload c_tr_workload%ROWTYPE;
316
317 BEGIN
318 -- get the workloads defined at unit section level (igs_ps_usec_cps).
319 OPEN c_usec_workload (p_n_uoo_id);
320 FETCH c_usec_workload INTO p_n_tot_wl_lec, p_n_tot_wl_lab, p_n_tot_wl;
321 IF c_usec_workload%NOTFOUND THEN
322 -- get the workloads defined at unit level (igs_ps_unit_ver_all) as per inheritance logic.
323 OPEN c_unit_workload (p_n_uoo_id);
324 FETCH c_unit_workload INTO p_n_tot_wl_lec, p_n_tot_wl_lab, p_n_tot_wl;
325 CLOSE c_unit_workload;
326 END IF;
327 CLOSE c_usec_workload;
328
329 -- get the confirmed workloads defined at unit section teaching responsibilities.
330 OPEN c_tr_workload (p_n_uoo_id);
331 FETCH c_tr_workload INTO rec_tr_workload;
332 CLOSE c_tr_workload;
333
334 /* Returns true if the workload defined at unit section teaching responsibilites matches
335 -- with workload defined at unit section credit points( As per inheritance if unit section
336 -- credit points is not defined at unit section then pick up the workload defined at unit version).
337 -- Retuns false if the workloads doesn't match. */
338 IF (rec_tr_workload.lecture_workload = p_n_tot_wl_lec AND
339 rec_tr_workload.laboratory_workload = p_n_tot_wl_lab AND
340 rec_tr_workload.other_workload = p_n_tot_wl ) THEN
341 RETURN TRUE;
342 END IF;
343
344 RETURN FALSE;
345
346 END validate_workload;
347
348 FUNCTION get_validation_type(p_c_unit_cd IN VARCHAR2,
349 p_n_ver_num IN NUMBER) RETURN VARCHAR2 IS
350 /***********************************************************************************************
351
352 Created By: smvk
353 Date Created By: 29-Apr-2004
354 Purpose: This procedure get the unit section teaching responsibility validation type.
355 if the validation type is overriden at unit version level then returns the validation type set at unit level.
356 if the validation type is not overriden at unit version level then returns the validation type set in the site level profile "IGS: Unit Section Teaching Responsibility Validation".
357 if the validation type is not overriden at unit version level, as well as the profile is not set it retuns 'DENY' as validation type.
358
359 Known limitations,enhancements,remarks:
360
361 Change History
362
363 Who When What
364 ***********************************************************************************************/
365
366 CURSOR c_workload_val (cp_c_unit_cd igs_ps_unit_ofr_opt_all.unit_cd%TYPE,
367 cp_n_version_num igs_ps_unit_ofr_opt_all.version_number%TYPE) IS
368 SELECT workload_val_code
369 FROM igs_ps_unit_ver_all
370 WHERE unit_cd = cp_c_unit_cd
371 AND version_number = cp_n_version_num
372 AND ovrd_wkld_val_flag = 'Y'
373 AND ROWNUM <2;
374
375 l_c_validation_type igs_ps_unit_ver_all.workload_val_code%TYPE;
376
377 BEGIN
378 OPEN c_workload_val(p_c_unit_cd, p_n_ver_num);
379 FETCH c_workload_val INTO l_c_validation_type;
380 IF c_workload_val%NOTFOUND THEN
381 l_c_validation_type := fnd_profile.value('IGS_PS_WKLD_VAL');
382 END IF;
383 CLOSE c_workload_val;
384 return NVL(l_c_validation_type,'DENY');
385 END get_validation_type;
386
387
388 END igs_ps_fac_credt_wrkload;
[Home] [Help]