[Home] [Help]
PACKAGE BODY: APPS.IGS_AU_GEN_003
Source
1 PACKAGE BODY igs_au_gen_003 AS
2 /* $Header: IGSAU03B.pls 115.21 2003/12/09 12:07:35 rvangala ship $ */
3 -- WHO WHEN WHAT
4 -- pradhakr 03/07/2001 Added a function enrp_get_sph_col in this package.
5 -- Changes in the function Audp_Get_scah_col, Audp_Get_Suah_Col.
6 -- smaddali 02/07/2001 adding a new function audp_get_enrs_stat in the
7 -- enrollment processes build of nov 2001 to calculate
8 -- seats reserved,number of students discontinued and
9 -- dropped from a unit section
10 -- adding a new function audp_get_enrs_stat in the enrollment processes
11 -- build of nov 2001 release , to get the statistics column values
12 -- bayadav 09-Nov-2001 Added 5 columns in audp_get_scah_col and 2 columns in audp_get_susah_col
13 -- pkpatel 25-OCT-2002 Bug No: 2613704
14 -- Replaced column inst_priority_code_id with inst_priority_cd
15 --rvangala 01-OCT-2003 Added variable cst_core_indicator and modified cursor c_suah to function
16 -- Audp_Get_Suah_Col, Enh Bug# 3052432
17 --ijeddy 06-Nov-2003 Build 3129913, Program completion Validation.
18 -- rvangala 09-Dec-2003 Added coo_id, igs_pr_class_std_id to Audp_Get_Scah_Col
19 -- Bug #2829263
20
21
22 FUNCTION Audp_Get_Enrs_Stat(
23 p_stat_column IN VARCHAR2 ,
24 p_uoo_id IN NUMBER )
25 RETURN NUMBER AS
26 BEGIN -- audp_get_enrs_stat
27 -- get the value for seats reserved or number of students dropped
28 -- or number of students discontinued depending on column passed
29 -- for the given unit section
30 DECLARE
31 -- get count of students discontinued in this unit section
32 CURSOR cur_disc IS
33 SELECT COUNT(*)
34 FROM igs_en_su_attempt
35 WHERE uoo_id = p_uoo_id
36 AND unit_attempt_status = 'DISCONTIN' ;
37
38 -- get count of students dropped in this unit section
39 CURSOR cur_drop IS
40 SELECT COUNT(*)
41 FROM igs_en_su_attempt
42 WHERE uoo_id = p_uoo_id
43 AND unit_attempt_status = 'DROPPED' ;
44
45 -- get unit section preferences
46 CURSOR cur_usec_preferences(cp_usec_priority_id IN NUMBER) IS
47 SELECT rsv_usec_pri_id ,
48 rsv_usec_prf_id,
49 percentage_reserved
50 FROM igs_ps_rsv_usec_prf
51 WHERE rsv_usec_pri_id = cp_usec_priority_id ;
52
53 --get unit section priorities
54 CURSOR cur_usec_priorities IS
55 SELECT rsv_usec_pri_id
56 FROM igs_ps_rsv_usec_pri
57 WHERE uoo_id = p_uoo_id ;
58
59 --get maximum enrollment for this unit section or unit
60 CURSOR cur_max_enrollment IS
61 SELECT NVL( NVL(usec.enrollment_maximum,uv.enrollment_maximum),0)
62 FROM igs_ps_usec_lim_wlst usec,
63 igs_ps_unit_ver uv,
64 igs_ps_unit_ofr_opt uoo
65 WHERE uoo.unit_cd = uv.unit_cd
66 AND uoo.version_number = uv.version_number
67 AND uoo.uoo_id = usec.uoo_id
68 AND uoo.uoo_id = p_uoo_id ;
69
70 l_stat_value NUMBER := 0;
71 l_max_enr NUMBER;
72 BEGIN
73 -- get the number of seats reserved for this unit section
74 IF p_stat_column = 'SEATS_RESERVED' THEN
75 OPEN cur_max_enrollment ;
76 FETCH cur_max_enrollment INTO l_max_enr ;
77 CLOSE cur_max_enrollment ;
78 FOR rec_usec_priorities IN cur_usec_priorities
79 LOOP
80 FOR rec_usec_preferences IN cur_usec_preferences( rec_usec_priorities.rsv_usec_pri_id )
81 LOOP
82 l_stat_value := l_stat_value + FLOOR((l_max_enr * rec_usec_preferences.percentage_reserved) / 100) ;
83 END LOOP;
84 END LOOP;
85 -- get the number of students discontinued for this unit section
86 ELSIF p_stat_column = 'DISCONTINUED' THEN
87 OPEN cur_disc ;
88 FETCH cur_disc INTO l_stat_value ;
89 CLOSE cur_disc ;
90 -- get the number of students dropped for this unit section
91 ELSIF p_stat_column = 'DROPPED' THEN
92 OPEN cur_drop ;
93 FETCH cur_drop INTO l_stat_value ;
94 CLOSE cur_drop ;
95 END IF;
96 RETURN l_stat_value ;
97 END;
98 END audp_get_enrs_stat;
99
100
101 FUNCTION Audp_Get_Gach_Col(
102 p_column_name IN user_tab_columns.column_name%TYPE ,
103 p_person_id IN NUMBER ,
104 p_create_dt IN DATE ,
105 p_grd_cal_type IN VARCHAR2 ,
106 p_grd_ci_sequence_number IN NUMBER ,
107 p_ceremony_number IN NUMBER ,
108 p_award_course_cd IN VARCHAR2 ,
109 p_award_crs_version_number IN NUMBER ,
110 p_award_cd IN VARCHAR2 ,
111 p_hist_date IN DATE )
112 RETURN VARCHAR2 AS
113 BEGIN -- audp_get_gach_col
114 -- get the oldest column value (after a given date) for a
115 -- specified column, unit_cd and version_number.
116 DECLARE
117 cst_us_group_number VARCHAR2(30) := 'US_GROUP_NUMBER';
118 cst_order_in_presentation VARCHAR2(30) := 'ORDER_IN_PRESENTATION';
119 cst_graduand_seat_number VARCHAR2(30) := 'GRADUAND_SEAT_NUMBER';
120 cst_name_pronunciation VARCHAR2(30) := 'NAME_PRONUNCIATION';
121 cst_name_announced VARCHAR2(30) := 'NAME_ANNOUNCED';
122 cst_academic_dress_rqrd_ind VARCHAR2(30) := 'ACADEMIC_DRESS_RQRD_IND';
123 cst_academic_gown_size VARCHAR2(30) := 'ACADEMIC_GOWN_SIZE';
124 cst_academic_hat_size VARCHAR2(30) := 'ACADEMIC_HAT_SIZE';
125 cst_guest_tickets_requested VARCHAR2(30) := 'GUEST_TICKETS_REQUESTED';
126 cst_guest_tickets_allocated VARCHAR2(30) := 'GUEST_TICKETS_ALLOCATED';
127 cst_guest_seats VARCHAR2(30) := 'GUEST_SEATS';
128 cst_fees_paid_ind VARCHAR2(30) := 'FEES_PAID_IND';
129 cst_special_requirements VARCHAR2(30) := 'SPECIAL_REQUIREMENTS';
130 cst_comments VARCHAR2(30) := 'COMMENTS';
131 v_column_value VARCHAR2(2000) := NULL;
132 CURSOR c_gach IS
133 SELECT DECODE (p_column_name,
134 cst_us_group_number, TO_CHAR(gach.us_group_number),
135 cst_order_in_presentation, TO_CHAR(gach.order_in_presentation),
136 cst_graduand_seat_number, gach.graduand_seat_number,
137 cst_name_pronunciation, gach.name_pronunciation,
138 cst_name_announced, gach.name_announced,
139 cst_academic_dress_rqrd_ind, gach.academic_dress_rqrd_ind,
140 cst_academic_gown_size, gach.academic_gown_size,
141 cst_academic_hat_size, gach.academic_hat_size,
142 cst_guest_tickets_requested, TO_CHAR(gach.guest_tickets_requested),
143 cst_guest_tickets_allocated, TO_CHAR(gach.guest_tickets_allocated),
144 cst_guest_seats, gach.guest_seats,
145 cst_fees_paid_ind, gach.fees_paid_ind,
146 cst_special_requirements, gach.special_requirements,
147 cst_comments, gach.comments)
148 FROM IGS_GR_AWD_CRMN_HIST gach
149 WHERE gach.person_id = p_person_id AND
150 gach.create_dt = p_create_dt AND
151 gach.grd_cal_type = p_grd_cal_type AND
152 gach.grd_ci_sequence_number = p_grd_ci_sequence_number AND
153 gach.ceremony_number = p_ceremony_number AND
154 gach.award_course_cd = p_award_course_cd AND
155 gach.award_crs_version_number = p_award_crs_version_number AND
156 gach.award_cd = p_award_cd AND
157 gach.hist_end_dt >= p_hist_date AND
158 DECODE (p_column_name,
159 cst_us_group_number, TO_CHAR(gach.us_group_number),
160 cst_order_in_presentation, TO_CHAR(gach.order_in_presentation),
161 cst_graduand_seat_number, gach.graduand_seat_number,
162 cst_name_pronunciation, gach.name_pronunciation,
163 cst_name_announced, gach.name_announced,
164 cst_academic_dress_rqrd_ind, gach.academic_dress_rqrd_ind,
165 cst_academic_gown_size, gach.academic_gown_size,
166 cst_academic_hat_size, gach.academic_hat_size,
167 cst_guest_tickets_requested, TO_CHAR(gach.guest_tickets_requested),
168 cst_guest_tickets_allocated, TO_CHAR(gach.guest_tickets_allocated),
169 cst_guest_seats, gach.guest_seats,
170 cst_fees_paid_ind, gach.fees_paid_ind,
171 cst_special_requirements, gach.special_requirements,
172 cst_comments, gach.comments) IS NOT NULL
173 ORDER BY
174 gach.hist_end_dt;
175 BEGIN
176 OPEN c_gach;
177 FETCH c_gach INTO v_column_value;
178 CLOSE c_gach;
179 RETURN v_column_value;
180 EXCEPTION
181 WHEN OTHERS THEN
182 IF c_gach%ISOPEN THEN
183 CLOSE c_gach;
184 END IF;
185 RAISE;
186 END;
187 END audp_get_gach_col;
188
189 FUNCTION Audp_Get_Grh_Col(
190 p_person_id IGS_GR_GRADUAND_HIST_ALL.person_id%TYPE ,
191 p_create_dt IGS_GR_GRADUAND_HIST_ALL.create_dt%TYPE ,
192 p_column_name user_tab_columns.column_name%TYPE ,
193 p_hist_dt IGS_GR_GRADUAND_HIST_ALL.hist_start_dt%TYPE )
194 RETURN VARCHAR2 AS
195 BEGIN -- audp_get_grh_col
196 -- Get the oldest column value(after a given date) for a
197 -- given p_person_id and p_create_dt.
198 DECLARE
199 cst_course_cd CONSTANT VARCHAR2(30) := 'COURSE_CD';
200 cst_award_course_cd CONSTANT VARCHAR2(30) := 'AWARD_COURSE_CD';
201 cst_award_crs_version_number CONSTANT VARCHAR2(30) := 'AWARD_CRS_VERSION_NUMBER';
202 cst_award_cd CONSTANT VARCHAR2(30) := 'AWARD_CD';
203 -- cst_honours_level CONSTANT VARCHAR2(30) := 'HONOURS_LEVEL';
204 -- cst_conferral_dt CONSTANT VARCHAR2(30) := 'CONFERRAL_DT';
205 cst_graduand_status CONSTANT VARCHAR2(30) := 'GRADUAND_STATUS';
206 cst_graduand_appr_status CONSTANT VARCHAR2(30) := 'GRADUAND_APPR_STATUS';
207 cst_s_graduand_type CONSTANT VARCHAR2(30) := 'S_GRADUAND_TYPE';
208 cst_proxy_award_ind CONSTANT VARCHAR2(30) := 'PROXY_AWARD_IND';
209 cst_proxy_award_person_id CONSTANT VARCHAR2(30) := 'PROXY_AWARD_PERSON_ID';
210 cst_previous_qualifications CONSTANT VARCHAR2(30) := 'PREVIOUS_QUALIFICATIONS';
211 cst_convocation_membership_ind CONSTANT VARCHAR2(30) := 'CONVOCATION_MEMBERSHIP_IND';
212 cst_sur_for_course_cd CONSTANT VARCHAR2(30) := 'SUR_FOR_COURSE_CD';
213 cst_sur_for_crs_version_number CONSTANT VARCHAR2(30) := 'SUR_FOR_CRS_VERSION_NUMBER';
214 cst_sur_for_award_cd CONSTANT VARCHAR2(30) := 'SUR_FOR_AWARD_CD';
215 cst_comments CONSTANT VARCHAR2(30) := 'COMMENTS';
216 v_column_value VARCHAR2(2000) := NULL;
217 CURSOR c_grh IS
218 SELECT DECODE (p_column_name,
219 cst_course_cd, grh.course_cd,
220 cst_award_course_cd, grh.award_course_cd,
221 cst_award_crs_version_number, TO_CHAR(grh.award_crs_version_number),
222 cst_award_cd, grh.award_cd,
223 -- cst_honours_level, grh.HONOURS_LEVEL,
224 -- cst_conferral_dt, IGS_GE_DATE.igscharDT(grh.conferral_dt),
225 cst_graduand_status, grh.GRADUAND_STATUS,
226 cst_graduand_appr_status, grh.GRADUAND_APPR_STATUS,
227 cst_s_graduand_type, grh.s_graduand_type,
228 cst_proxy_award_ind, grh.proxy_award_ind,
229 cst_proxy_award_person_id, TO_CHAR(grh.proxy_award_person_id),
230 cst_previous_qualifications, grh.previous_qualifications,
231 cst_convocation_membership_ind, grh.convocation_membership_ind,
232 cst_sur_for_course_cd, grh.sur_for_course_cd,
233 cst_sur_for_crs_version_number, TO_CHAR(grh.sur_for_crs_version_number),
234 cst_sur_for_award_cd, grh.sur_for_award_cd,
235 cst_comments, grh.comments)
236 FROM IGS_GR_GRADUAND_HIST grh
237 WHERE grh.person_id = p_person_id AND
238 grh.create_dt = p_create_dt AND
239 grh.hist_start_dt >= p_hist_dt AND
240 DECODE (p_column_name,
241 cst_course_cd, grh.course_cd,
242 cst_award_course_cd, grh.award_course_cd,
243 cst_award_crs_version_number, TO_CHAR(grh.award_crs_version_number),
244 cst_award_cd, grh.award_cd,
245 -- cst_honours_level, grh.HONOURS_LEVEL,
246 -- cst_conferral_dt, IGS_GE_DATE.igscharDT(grh.conferral_dt),
247 cst_graduand_status, grh.GRADUAND_STATUS,
248 cst_graduand_appr_status, grh.GRADUAND_APPR_STATUS,
249 cst_s_graduand_type, grh.s_graduand_type,
250 cst_proxy_award_ind, grh.proxy_award_ind,
251 cst_proxy_award_person_id, TO_CHAR(grh.proxy_award_person_id),
252 cst_previous_qualifications, grh.previous_qualifications,
253 cst_convocation_membership_ind, grh.convocation_membership_ind,
254 cst_sur_for_course_cd, grh.sur_for_course_cd,
255 cst_sur_for_crs_version_number, TO_CHAR(grh.sur_for_crs_version_number),
256 cst_sur_for_award_cd, grh.sur_for_award_cd,
257 cst_comments, grh.comments)IS NOT NULL
258 ORDER BY grh.hist_start_dt;
259 BEGIN
260 OPEN c_grh;
261 FETCH c_grh INTO v_column_value;
262 CLOSE c_grh;
263 RETURN v_column_value;
264 EXCEPTION
265 WHEN OTHERS THEN
266 IF c_grh%ISOPEN THEN
267 CLOSE c_grh;
268 END IF;
269 RAISE;
270 END;
271 END audp_get_grh_col;
272
273
274
275
276 FUNCTION Audp_Get_Ih_Col(
277 p_column_name IN user_tab_columns.column_name%TYPE ,
278 p_institution_cd IN IGS_OR_INST_HIST_ALL.institution_cd%TYPE ,
279 p_hist_end_dt IN IGS_OR_INST_HIST_ALL.hist_end_dt%TYPE )
280 RETURN VARCHAR2 AS
281 /*
282 || Created By : [email protected]
283 || Created On : 28-AUG-2000
284 || Purpose : Handles the LOCK mechanism for the table.
285 || Known limitations, enhancements or remarks :
286 || Change History :
287 || Who When What
288 || pkpatel 13-MAR-2002 Bug No: 2224621
289 || Removed the TO_CHAR for govt_institution_cd, since it is it is modified
290 || to VARCHAR2 now.
291 || pkpatel 25-OCT-2002 Bug No: 2613704
292 || Replaced column inst_priority_code_id with inst_priority_cd
293 || (reverse chronological order - newest change first)
294 */
295 BEGIN -- audp_get_ih_col
296 -- get the oldest column value (after a given date) for a
297 -- specified column and institution_cd for IGS_OR_INST_HIST table
298 DECLARE
299 cst_name VARCHAR2(4) := 'NAME';
300 -- added more columns as a fix for bug number 2251484
301 cst_inst_phone_country_code VARCHAR2(23) := 'INST_PHONE_COUNTRY_CODE';
302 cst_inst_phone_area_code VARCHAR2(20) := 'INST_PHONE_AREA_CODE';
303 cst_inst_phone_number VARCHAR2(17) := 'INST_PHONE_NUMBER';
304 cst_inst_priority_cd VARCHAR2(21) := 'INST_PRIORITY_CODE_CD';
305 cst_eps_code VARCHAR2(8) := 'EPS_CODE';
306 cst_institution_status VARCHAR2(18) := 'INSTITUTION_STATUS';
307 cst_local_institution_ind VARCHAR2(21) := 'LOCAL_INSTITUTION_IND';
308 cst_os_ind VARCHAR2(6) := 'OS_IND';
309 cst_govt_institution_cd VARCHAR2(19) := 'GOVT_INSTITUTION_CD';
310 cst_institution_type VARCHAR2(16) := 'INSTITUTION_TYPE';
311 cst_description VARCHAR2(11) := 'DESCRIPTION';
312 cst_inst_control_type VARCHAR2(17) := 'INST_CONTROL_TYPE';
313
314 CURSOR c_ih IS
315 SELECT
316 DECODE (
317 p_column_name,
318 cst_name, ih.name,
319
320 cst_inst_phone_country_code, ih.inst_phone_country_code ,
321 cst_inst_phone_area_code, ih.inst_phone_area_code,
322 cst_inst_phone_number, ih.inst_phone_number ,
323 cst_inst_priority_cd, ih.inst_priority_cd ,
324 cst_eps_code, ih.eps_code,
325 cst_institution_status, ih.INSTITUTION_STATUS,
326 cst_local_institution_ind, ih.local_institution_ind,
327 cst_os_ind, ih.os_ind,
328 cst_govt_institution_cd, ih.GOVT_INSTITUTION_CD,
329 cst_institution_type, ih.INSTITUTION_TYPE,
330 cst_description, ih.description,
331 cst_inst_control_type, ih.INST_CONTROL_TYPE )
332 FROM IGS_OR_INST_HIST_ALL ih
333 WHERE ih.institution_cd = p_institution_cd AND
334 ih.hist_start_dt >= p_hist_end_dt AND
335 DECODE (
336 p_column_name,
337 cst_name, ih.name,
338 cst_inst_phone_country_code, ih.inst_phone_country_code ,
339 cst_inst_phone_area_code, ih.inst_phone_area_code,
340 cst_inst_phone_number, ih.inst_phone_number ,
341 cst_inst_priority_cd, ih.inst_priority_cd,
342 cst_eps_code, ih.eps_code,
343 cst_institution_status, ih.INSTITUTION_STATUS,
344 cst_local_institution_ind, ih.local_institution_ind,
345 cst_os_ind, ih.os_ind,
346 cst_govt_institution_cd, ih.GOVT_INSTITUTION_CD,
347 cst_institution_type, ih.INSTITUTION_TYPE,
351 ih.hist_start_dt;
348 cst_description, ih.description,
349 cst_inst_control_type, ih.INST_CONTROL_TYPE ) IS NOT NULL
350 ORDER BY
352 v_column_value VARCHAR2(2000) := NULL;
353
354
355 BEGIN
356 OPEN c_ih;
357 FETCH c_ih INTO v_column_value;
358 CLOSE c_ih;
359
360 RETURN v_column_value;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 IF c_ih%ISOPEN THEN
365 CLOSE c_ih;
366 END IF;
367 RAISE;
368 END;
369 END audp_get_ih_col;
370
371
372
373
374 FUNCTION Audp_Get_Ouh_Col(
375 p_column_name IN user_tab_columns.column_name%TYPE ,
376 p_org_unit_cd IN IGS_OR_UNIT_HIST_ALL.institution_cd%TYPE ,
377 p_ou_start_dt IN IGS_OR_UNIT_HIST_ALL.ou_start_dt%TYPE ,
378 p_hist_end_dt IN IGS_OR_UNIT_HIST_ALL.hist_end_dt%TYPE )
379 RETURN VARCHAR2 AS
380 BEGIN -- audp_get_ouh_col
381 -- get the oldest column value (after a given date) for a
382 -- specified column and institution_cd for IGS_OR_UNIT_HIST table
383 DECLARE
384 cst_ou_end_dt VARCHAR2(10) := 'OU_END_DT';
385 cst_description VARCHAR2(11) := 'DESCRIPTION';
386 cst_org_status VARCHAR2(10) := 'ORG_STATUS';
387 cst_org_type VARCHAR2(8) := 'ORG_TYPE';
388 cst_member_type VARCHAR2(11) := 'MEMBER_TYPE';
389 cst_INSTITUTION_CD VARCHAR2(14) := 'INSTITUTION_CD';
390 cst_name VARCHAR2(4) := 'NAME';
391 CURSOR c_ouh IS
392 SELECT
393 DECODE (
394 p_column_name,
395 cst_ou_end_dt, IGS_GE_DATE.igscharDT(ouh.ou_end_dt),
396 cst_description, ouh.description,
397 cst_org_status, ouh.ORG_STATUS,
398 cst_org_type, ouh.ORG_TYPE,
399 cst_member_type, ouh.MEMBER_TYPE,
400 cst_INSTITUTION_CD, ouh.institution_cd,
401 cst_name, ouh.NAME)
402 FROM IGS_OR_UNIT_HIST ouh
403 WHERE ouh.org_unit_cd = p_org_unit_cd AND
404 ouh.ou_start_dt = p_ou_start_dt AND
405 ouh.hist_start_dt >= p_hist_end_dt AND
406 DECODE (
407 p_column_name,
408 cst_ou_end_dt, IGS_GE_DATE.igscharDT(ouh.ou_end_dt),
409 cst_description, ouh.description,
410 cst_org_status, ouh.ORG_STATUS,
411 cst_org_type, ouh.ORG_TYPE,
412 cst_member_type, ouh.MEMBER_TYPE,
413 cst_INSTITUTION_CD, ouh.institution_cd,
414 cst_name, ouh.NAME) IS NOT NULL
415 ORDER BY ouh.hist_start_dt;
416 v_column_value VARCHAR2(2000) := NULL;
417 BEGIN
418 OPEN c_ouh;
419 FETCH c_ouh INTO v_column_value;
420 CLOSE c_ouh;
421 RETURN v_column_value;
422 EXCEPTION
423 WHEN OTHERS THEN
424 IF c_ouh%ISOPEN THEN
425 CLOSE c_ouh;
426 END IF;
427 RAISE;
428 END;
429 END audp_get_ouh_col;
430
431
432 FUNCTION Audp_Get_Scah_Col(
433 p_column_name IN user_tab_columns.column_name%TYPE ,
434 p_person_id IN IGS_AS_SC_ATTEMPT_H_ALL.person_id%TYPE ,
435 p_course_cd IN IGS_AS_SC_ATTEMPT_H_ALL.course_cd%TYPE ,
436 p_hist_end_dt IN IGS_AS_SC_ATTEMPT_H_ALL.hist_end_dt%TYPE )
437 RETURN VARCHAR2 AS
438 BEGIN -- audp_get_scah_col
439 -- get the oldest column value (after a given date) for the
440 -- student course attempt history table
441 DECLARE
442 cst_version_number CONSTANT VARCHAR2(30) := 'VERSION_NUMBER';
443 cst_cal_type CONSTANT VARCHAR2(30) := 'CAL_TYPE';
444 cst_location_cd CONSTANT VARCHAR2(30) := 'LOCATION_CD';
445 cst_attendance_mode CONSTANT VARCHAR2(30) := 'ATTENDANCE_MODE';
446 cst_attendance_type CONSTANT VARCHAR2(30) := 'ATTENDANCE_TYPE';
447 cst_student_confirmed_ind CONSTANT VARCHAR2(30) := 'STUDENT_CONFIRMED_IND';
448 cst_commencement_dt CONSTANT VARCHAR2(30) := 'COMMENCEMENT_DT';
449 cst_course_attempt_status CONSTANT VARCHAR2(30) := 'COURSE_ATTEMPT_STATUS';
450 cst_progression_status CONSTANT VARCHAR2(30) := 'PROGRESSION_STATUS';
451 cst_derived_att_type CONSTANT VARCHAR2(30) := 'DERIVED_ATT_TYPE';
452 cst_derived_att_mode CONSTANT VARCHAR2(30) := 'DERIVED_ATT_MODE';
453 cst_provisional_ind CONSTANT VARCHAR2(30) := 'PROVISIONAL_IND';
454 cst_discontinued_dt CONSTANT VARCHAR2(30) := 'DISCONTINUED_DT';
455 cst_discontinuation_reason_cd CONSTANT VARCHAR2(30) := 'DISCONTINUATION_REASON_CD';
456 cst_funding_source CONSTANT VARCHAR2(30) := 'FUNDING_SOURCE';
457 cst_fs_description CONSTANT VARCHAR2(30) := 'FS_DESCRIPTION';
458 cst_exam_location_cd CONSTANT VARCHAR2(30) := 'EXAM_LOCATION_CD';
459 cst_elo_description CONSTANT VARCHAR2(30) := 'ELO_DESCRIPTION';
460 cst_derived_completion_yr CONSTANT VARCHAR2(30) := 'DERIVED_COMPLETION_YR';
461 cst_derived_completion_perd CONSTANT VARCHAR2(30) := 'DERIVED_COMPLETION_PERD';
462 cst_nominated_completion_yr CONSTANT VARCHAR2(30) := 'NOMINATED_COMPLETION_YR';
463 cst_nominated_completion_perd CONSTANT VARCHAR2(30) := 'NOMINATED_COMPLETION_PERD';
464 cst_rule_check_ind CONSTANT VARCHAR2(30) := 'RULE_CHECK_IND';
465 cst_waive_option_check_ind CONSTANT VARCHAR2(30) := 'WAIVE_OPTION_CHECK_IND';
466 cst_last_rule_check_dt CONSTANT VARCHAR2(30) := 'LAST_RULE_CHECK_DT';
467 cst_publish_outcomes_ind CONSTANT VARCHAR2(30) := 'PUBLISH_OUTCOMES_IND';
468 cst_course_rqrmnt_complete_ind CONSTANT VARCHAR2(30) := 'COURSE_RQRMNT_COMPLETE_IND';
469 cst_course_rqrmnts_complete_dt CONSTANT VARCHAR2(30) := 'COURSE_RQRMNTS_COMPLETE_DT';
473 cst_fee_cat CONSTANT VARCHAR2(30) := 'FEE_CAT';
470 cst_s_completed_source_type CONSTANT VARCHAR2(30) := 'S_COMPLETED_SOURCE_TYPE';
471 cst_override_time_limitation CONSTANT VARCHAR2(30) := 'OVERRIDE_TIME_LIMITATION';
472 cst_advanced_standing_ind CONSTANT VARCHAR2(30) := 'ADVANCED_STANDING_IND';
474 cst_fc_description CONSTANT VARCHAR2(30) := 'FC_DESCRIPTION';
475 cst_correspondence_cat CONSTANT VARCHAR2(30) := 'CORRESPONDENCE_CAT';
476 cst_cc_description CONSTANT VARCHAR2(30) := 'CC_DESCRIPTION';
477 cst_self_help_group_ind CONSTANT VARCHAR2(30) := 'SELF_HELP_GROUP_IND';
478 cst_lapsed_dt CONSTANT VARCHAR2(30) := 'LAPSED_DT';
479 cst_adm_admission_appl_number CONSTANT VARCHAR2(30) := 'ADM_ADMISSION_APPL_NUMBER';
480 cst_adm_nominated_course_cd CONSTANT VARCHAR2(30) := 'ADM_NOMINATED_COURSE_CD';
481 cst_adm_sequence_number CONSTANT VARCHAR2(30) := 'ADM_SEQUENCE_NUMBER';
482 cst_last_date_of_attendance CONSTANT VARCHAR2(30) := 'LAST_DATE_OF_ATTENDANCE';
483 cst_dropped_by CONSTANT VARCHAR2(30) := 'DROPPED_BY';
484 cst_key_program CONSTANT VARCHAR2(30) := 'KEY_PROGRAM';
485 cst_primary_program_type CONSTANT VARCHAR2(30) := 'PRIMARY_PROGRAM_TYPE';
486 cst_primary_prog_type_source CONSTANT VARCHAR2(30) := 'PRIMARY_PROG_TYPE_SOURCE';
487 cst_catalog_cal_type CONSTANT VARCHAR2(30) := 'CATALOG_CAL_TYPE';
488 cst_catalog_seq_num CONSTANT VARCHAR2(30) := 'CATALOG_SEQ_NUM';
489 cst_manual_ovr_cmpl_dt_ind CONSTANT VARCHAR2(30) := 'MANUAL_OVR_CMPL_DT_IND';
490 cst_override_cmpl_dt CONSTANT VARCHAR2(30) := 'OVERRIDE_CMPL_DT';
491 cst_coo_id CONSTANT VARCHAR2(30) := 'COO_ID';
492 cst_IGS_PR_CLASS_STD_ID CONSTANT VARCHAR2(30) := 'IGS_PR_CLASS_STD_ID';
493
494 CURSOR c_scah IS
495 SELECT DECODE (p_column_name,
496 cst_version_number, TO_CHAR(scah.version_number),
497 cst_cal_type, scah.CAL_TYPE,
498 cst_location_cd, scah.location_cd,
499 cst_attendance_mode, scah.ATTENDANCE_MODE,
500 cst_attendance_type, scah.ATTENDANCE_TYPE,
501 cst_student_confirmed_ind, scah.student_confirmed_ind,
502 cst_commencement_dt, IGS_GE_DATE.igscharDT(scah.commencement_dt),
503 cst_course_attempt_status, scah.course_attempt_status,
504 cst_progression_status, scah.progression_status,
505 cst_derived_att_type, scah.derived_att_type,
506 cst_derived_att_mode, scah.derived_att_mode,
507 cst_provisional_ind, scah.provisional_ind,
508 cst_discontinued_dt, IGS_GE_DATE.igscharDT(scah.discontinued_dt),
509 cst_discontinuation_reason_cd, scah.DISCONTINUATION_REASON_CD,
510 cst_funding_source, scah.FUNDING_SOURCE,
511 cst_fs_description, scah.fs_description,
512 cst_exam_location_cd, scah.exam_location_cd,
513 cst_elo_description, scah.elo_description,
514 cst_derived_completion_yr, TO_CHAR(scah.derived_completion_yr),
515 cst_derived_completion_perd, scah.derived_completion_perd,
516 cst_nominated_completion_yr, TO_CHAR(scah.nominated_completion_yr),
517 cst_nominated_completion_perd, scah.nominated_completion_perd,
518 cst_rule_check_ind, scah.rule_check_ind,
519 cst_waive_option_check_ind, scah.waive_option_check_ind,
520 cst_last_rule_check_dt, IGS_GE_DATE.igscharDT(scah.last_rule_check_dt),
521 cst_publish_outcomes_ind, scah.publish_outcomes_ind,
522 cst_course_rqrmnt_complete_ind, scah.course_rqrmnt_complete_ind,
523 cst_course_rqrmnts_complete_dt, IGS_GE_DATE.igscharDT(scah.course_rqrmnts_complete_dt),
524 cst_s_completed_source_type, scah.s_completed_source_type,
525 cst_override_time_limitation, TO_CHAR(scah.override_time_limitation),
526 cst_advanced_standing_ind, scah.advanced_standing_ind,
527 cst_fee_cat, scah.FEE_CAT,
528 cst_fc_description, scah.fc_description,
529 cst_correspondence_cat, scah.CORRESPONDENCE_CAT,
530 cst_cc_description, scah.cc_description,
531 cst_self_help_group_ind, scah.self_help_group_ind,
532 cst_lapsed_dt, IGS_GE_DATE.igscharDT(scah.lapsed_dt),
533 cst_adm_admission_appl_number, TO_CHAR(scah.adm_admission_appl_number),
534 cst_adm_nominated_course_cd, scah.adm_nominated_course_cd,
535 cst_adm_sequence_number, TO_CHAR(scah.adm_sequence_number),
536 cst_last_date_of_attendance, IGS_GE_DATE.igscharDT(scah.last_date_of_attendance),
537 cst_dropped_by, scah.dropped_by,
538 cst_key_program, scah.key_program,
539 cst_primary_program_type, scah.primary_program_type,
540 cst_primary_prog_type_source, scah.primary_prog_type_source,
541 cst_catalog_cal_type, scah.catalog_cal_type,
542 cst_catalog_seq_num, TO_CHAR(scah.catalog_seq_num),
543 cst_manual_ovr_cmpl_dt_ind, scah.manual_ovr_cmpl_dt_ind ,
544 cst_override_cmpl_dt, IGS_GE_DATE.igscharDT(scah.override_cmpl_dt),
545 cst_coo_id, TO_CHAR(scah.coo_id),
546 cst_IGS_PR_CLASS_STD_ID, TO_CHAR(scah.IGS_PR_CLASS_STD_ID)
547 )
548 FROM IGS_AS_SC_ATTEMPT_H scah
549 WHERE scah.person_id = p_person_id AND
550 scah.course_cd = p_course_cd AND
554 cst_cal_type, scah.CAL_TYPE,
551 scah.hist_start_dt >= p_hist_end_dt AND
552 DECODE (p_column_name,
553 cst_version_number, TO_CHAR(scah.version_number),
555 cst_location_cd, scah.location_cd,
556 cst_attendance_mode, scah.ATTENDANCE_MODE,
557 cst_attendance_type, scah.ATTENDANCE_TYPE,
558 cst_student_confirmed_ind, scah.student_confirmed_ind,
559 cst_commencement_dt, IGS_GE_DATE.igscharDT(scah.commencement_dt),
560 cst_course_attempt_status, scah.course_attempt_status,
561 cst_progression_status, scah.progression_status,
562 cst_derived_att_type, scah.derived_att_type,
563 cst_derived_att_mode, scah.derived_att_mode,
564 cst_provisional_ind, scah.provisional_ind,
565 cst_discontinued_dt, IGS_GE_DATE.igscharDT(scah.discontinued_dt),
566 cst_discontinuation_reason_cd, scah.DISCONTINUATION_REASON_CD,
567 cst_funding_source, scah.FUNDING_SOURCE,
568 cst_fs_description, scah.fs_description,
569 cst_exam_location_cd, scah.exam_location_cd,
570 cst_elo_description, scah.elo_description,
571 cst_derived_completion_yr, TO_CHAR(scah.derived_completion_yr),
572 cst_derived_completion_perd, scah.derived_completion_perd,
573 cst_nominated_completion_yr, TO_CHAR(scah.nominated_completion_yr),
574 cst_nominated_completion_perd, scah.nominated_completion_perd,
575 cst_rule_check_ind, scah.rule_check_ind,
576 cst_waive_option_check_ind, scah.waive_option_check_ind,
577 cst_last_rule_check_dt, IGS_GE_DATE.igscharDT(scah.last_rule_check_dt),
578 cst_publish_outcomes_ind, scah.publish_outcomes_ind,
579 cst_course_rqrmnt_complete_ind, scah.course_rqrmnt_complete_ind,
580 cst_course_rqrmnts_complete_dt, IGS_GE_DATE.igscharDT(scah.course_rqrmnts_complete_dt),
581 cst_s_completed_source_type, scah.s_completed_source_type,
582 cst_override_time_limitation, TO_CHAR(scah.override_time_limitation),
583 cst_advanced_standing_ind, scah.advanced_standing_ind,
584 cst_fee_cat, scah.FEE_CAT,
585 cst_fc_description, scah.fc_description,
586 cst_correspondence_cat, scah.CORRESPONDENCE_CAT,
587 cst_cc_description, scah.cc_description,
588 cst_self_help_group_ind, scah.self_help_group_ind,
589 cst_lapsed_dt, IGS_GE_DATE.igscharDT(scah.lapsed_dt),
590 cst_adm_admission_appl_number, TO_CHAR(scah.adm_admission_appl_number),
591 cst_adm_nominated_course_cd, scah.adm_nominated_course_cd,
592 cst_adm_sequence_number, TO_CHAR(scah.adm_sequence_number),
593 cst_last_date_of_attendance, IGS_GE_DATE.igscharDT(scah.last_date_of_attendance),
594 cst_dropped_by, scah.dropped_by ,
595 cst_key_program , scah.key_program,
596 cst_primary_program_type, scah.primary_program_type,
597 cst_primary_prog_type_source, scah.primary_prog_type_source,
598 cst_catalog_cal_type, scah.catalog_cal_type,
599 cst_catalog_seq_num, TO_CHAR(scah.catalog_seq_num),
600 cst_manual_ovr_cmpl_dt_ind, scah.manual_ovr_cmpl_dt_ind,
601 cst_override_cmpl_dt, IGS_GE_DATE.igscharDT(scah.override_cmpl_dt),
602 cst_coo_id, TO_CHAR(scah.coo_id),
603 cst_IGS_PR_CLASS_STD_ID, TO_CHAR(scah.IGS_PR_CLASS_STD_ID)
604 ) IS NOT NULL
605 ORDER BY scah.hist_start_dt;
606 v_column_value VARCHAR2(2000) := NULL;
607 BEGIN
608 OPEN c_scah;
609 FETCH c_scah INTO v_column_value;
610 CLOSE c_scah;
611 RETURN v_column_value;
612 EXCEPTION
613 WHEN OTHERS THEN
614 IF c_scah%ISOPEN THEN
615 CLOSE c_scah;
616 END IF;
617 RAISE;
618 END;
619 END audp_get_scah_col;
620
621
622 -- adding a new function Enrp_Get_Sph_col in the enrollment processes
623 -- build of nov 2001 release.
624
625 FUNCTION Enrp_Get_Sph_col (
626 p_column_name IN VARCHAR2,
627 p_spl_perm_request_h_id IN NUMBER,
628 p_hist_end_dt IN DATE )
629 RETURN VARCHAR2 AS
630 BEGIN
631 DECLARE
632 cst_date_submission CONSTANT VARCHAR2(20) := 'DATE_SUBMISSION';
633 cst_audit_the_course CONSTANT VARCHAR2(20) := 'AUDIT_THE_COURSE';
634 cst_approval_status CONSTANT VARCHAR2(20) := 'APPROVAL_STATUS';
635 cst_reason_for_request CONSTANT VARCHAR2(20) := 'REASON_FOR_REQUEST';
636 cst_instructor_more_info CONSTANT VARCHAR2(25) := 'INSTRUCTOR_MORE_INFO';
637 cst_instructor_deny_info CONSTANT VARCHAR2(25) := 'INSTRUCTOR_DENY_INFO';
638 cst_student_more_info CONSTANT VARCHAR2(25) := 'STUDENT_MORE_INFO';
639 cst_transaction_type CONSTANT VARCHAR2(20) := 'TRANSACTION_TYPE';
640
641 CURSOR c_sph IS
642 SELECT
643 DECODE (
644 p_column_name,
648 cst_instructor_deny_info, sph.instructor_deny_info,
645 cst_date_submission, IGS_GE_DATE.igscharDT(sph.date_submission),
646 cst_reason_for_request, sph.reason_for_request,
647 cst_instructor_more_info, sph.instructor_more_info,
649 cst_student_more_info, sph.student_more_info,
650 cst_approval_status, sph.approval_status,
651 cst_audit_the_course, sph.audit_the_course,
652 cst_transaction_type, sph.transaction_type
653 )
654 FROM IGS_EN_SPL_PERM_H sph
655 WHERE sph.spl_perm_request_h_id = p_spl_perm_request_h_id AND
656 sph.hist_start_dt >= p_hist_end_dt AND
657 DECODE (
658 p_column_name,
659 cst_date_submission, IGS_GE_DATE.igscharDT(sph.date_submission),
660 cst_reason_for_request, sph.reason_for_request,
661 cst_instructor_more_info, sph.instructor_more_info,
662 cst_instructor_deny_info, sph.instructor_deny_info,
663 cst_student_more_info, sph.student_more_info,
664 cst_approval_status, sph.approval_status,
665 cst_audit_the_course, sph.audit_the_course,
666 cst_transaction_type, sph.transaction_type
667 ) IS NOT NULL
668 ORDER BY sph.hist_start_dt;
669
670 v_column_value VARCHAR2(4000) := NULL;
671
672 BEGIN
673 OPEN c_sph;
674 FETCH c_sph INTO v_column_value;
675 CLOSE c_sph;
676 RETURN v_column_value;
677 EXCEPTION
678 WHEN OTHERS THEN
679 IF c_sph%ISOPEN THEN
680 CLOSE c_sph;
681 END IF;
682 RAISE;
683 END;
684 END Enrp_Get_Sph_col;
685
686
687 FUNCTION Audp_Get_Suah_Col(
688 p_column_name user_tab_columns.column_name%TYPE ,
689 p_person_id IGS_EN_SU_ATTEMPT_H_ALL.person_id%TYPE ,
690 p_course_cd IGS_EN_SU_ATTEMPT_H_ALL.course_cd%TYPE ,
691 p_hist_end_dt IGS_EN_SU_ATTEMPT_H_ALL.hist_end_dt%TYPE ,
692 p_uoo_id IGS_EN_SU_ATTEMPT_H_ALL.uoo_id%TYPE)
693 RETURN VARCHAR2 AS
694 --
695 -- Who When What
696 -- knaraset 29-Apr-03 added p_uoo_id as parameter used the same in cursor c_suah,also removed the other parameter unit_cd,cal_type and sequence_number
697 -- as part of MUS build bug 2829262
698 ----rvangala 01-OCT-2003 Added variable cst_core_indicator and modified cursor c_suah to function
699 -- Audp_Get_Suah_Col, Enh Bug# 3052432
700 gv_other_detail VARCHAR2(255);
701 BEGIN
702 DECLARE
703 cst_version_number CONSTANT VARCHAR2(30) := 'VERSION_NUMBER';
704 cst_location_cd CONSTANT VARCHAR2(30) := 'LOCATION_CD';
705 cst_unit_class CONSTANT VARCHAR2(30) := 'UNIT_CLASS';
706 cst_enrolled_dt CONSTANT VARCHAR2(30) := 'ENROLLED_DT';
707 cst_unit_attempt_status CONSTANT VARCHAR2(30) := 'UNIT_ATTEMPT_STATUS';
708 cst_administrative_unit_status CONSTANT VARCHAR2(30) := 'ADMINISTRATIVE_UNIT_STATUS';
709 cst_aus_description CONSTANT VARCHAR2(30) := 'AUS_DESCRIPTION';
710 cst_discontinued_dt CONSTANT VARCHAR2(30) := 'DISCONTINUED_DT';
711 cst_rule_waived_dt CONSTANT VARCHAR2(30) := 'RULE_WAIVED_DT';
712 cst_rule_waived_person_id CONSTANT VARCHAR2(30) := 'RULE_WAIVED_PERSON_ID';
713 cst_no_assessment_ind CONSTANT VARCHAR2(30) := 'NO_ASSESSMENT_IND';
714 cst_exam_location_cd CONSTANT VARCHAR2(30) := 'EXAM_LOCATION_CD';
715 cst_elo_description CONSTANT VARCHAR2(30) := 'ELO_DESCRIPTION';
716 cst_sup_unit_cd CONSTANT VARCHAR2(30) := 'SUP_UNIT_CD';
717 cst_sup_version_number CONSTANT VARCHAR2(30) := 'SUP_VERSION_NUMBER';
718 cst_alternative_title CONSTANT VARCHAR2(30) := 'ALTERNATIVE_TITLE';
719 cst_override_enrolled_cp CONSTANT VARCHAR2(30) := 'OVERRIDE_ENROLLED_CP';
720 cst_override_eftsu CONSTANT VARCHAR2(30) := 'OVERRIDE_EFTSU';
721 cst_override_achievable_cp CONSTANT VARCHAR2(30) := 'OVERRIDE_ACHIEVABLE_CP';
722 cst_override_outcome_due_dt CONSTANT VARCHAR2(30) := 'OVERRIDE_OUTCOME_DUE_DT';
723 cst_override_credit_reason CONSTANT VARCHAR2(30) := 'OVERRIDE_CREDIT_REASON';
724 cst_enr_method_type CONSTANT VARCHAR2(30) := 'ENR_METHOD_TYPE';
725 cst_grading_schema_code CONSTANT VARCHAR2(30) := 'GRADING_SCHEMA_CODE';
726 --added by rvangala 01-OCT-2003. Enh Bug# 3052432
727 cst_core_indicator CONSTANT VARCHAR2(30) := 'CORE_INDICATOR';
728
729 CURSOR c_suah IS
730 SELECT DECODE (p_column_name,
731 cst_version_number, TO_CHAR(suah.version_number),
732 cst_location_cd, suah.location_cd,
733 cst_unit_class, suah.UNIT_CLASS,
734 cst_enrolled_dt, IGS_GE_DATE.igscharDT(suah.enrolled_dt),
735 cst_unit_attempt_status, suah.unit_attempt_status,
736 cst_administrative_unit_status, suah.ADMINISTRATIVE_UNIT_STATUS,
737 cst_aus_description, suah.aus_description,
738 cst_discontinued_dt, IGS_GE_DATE.igscharDT(suah.discontinued_dt),
739 cst_rule_waived_dt, IGS_GE_DATE.igscharDT(suah.rule_waived_dt),
740 cst_rule_waived_person_id, TO_CHAR(suah.rule_waived_person_id),
741 cst_no_assessment_ind, suah.no_assessment_ind,
742 cst_exam_location_cd, suah.exam_location_cd,
743 cst_elo_description, suah.elo_description,
744 cst_sup_unit_cd, suah.sup_unit_cd,
745 cst_sup_version_number, TO_CHAR(suah.sup_version_number),
746 cst_alternative_title, suah.alternative_title,
747 cst_override_enrolled_cp, TO_CHAR(suah.override_enrolled_cp),
748 cst_override_eftsu, TO_CHAR(suah.override_eftsu),
749 cst_override_achievable_cp, TO_CHAR(suah.override_achievable_cp),
750 cst_override_outcome_due_dt, IGS_GE_DATE.igscharDT(suah.override_outcome_due_dt),
751 cst_override_credit_reason, suah.override_credit_reason,
755 cst_core_indicator, suah.core_indicator_code)
752 cst_grading_schema_code, suah.grading_schema_code,
753 cst_enr_method_type, suah.enr_method_type,
754 --added by rvangala 01-OCT-2003. Enh Bug# 3052432
756 FROM IGS_EN_SU_ATTEMPT_H suah
757 WHERE suah.person_id = p_person_id AND
758 suah.course_cd = p_course_cd AND
759 suah.uoo_id = p_uoo_id AND
760 suah.hist_start_dt >= p_hist_end_dt AND
761 DECODE (p_column_name,
762 cst_version_number, TO_CHAR(suah.version_number),
763 cst_location_cd, suah.location_cd,
764 cst_unit_class, suah.UNIT_CLASS,
765 cst_enrolled_dt, TO_CHAR(suah.enrolled_dt),
766 cst_unit_attempt_status, suah.unit_attempt_status,
767 cst_administrative_unit_status, suah.ADMINISTRATIVE_UNIT_STATUS,
768 cst_aus_description, suah.aus_description,
769 cst_discontinued_dt, TO_CHAR(suah.discontinued_dt),
770 cst_rule_waived_dt, TO_CHAR(suah.rule_waived_dt),
771 cst_rule_waived_person_id, TO_CHAR(suah.rule_waived_person_id),
772 cst_no_assessment_ind, suah.no_assessment_ind,
773 cst_exam_location_cd, suah.exam_location_cd,
774 cst_elo_description, suah.elo_description,
775 cst_sup_unit_cd, suah.sup_unit_cd,
776 cst_sup_version_number, TO_CHAR(suah.sup_version_number),
777 cst_alternative_title, suah.alternative_title,
778 cst_override_enrolled_cp, TO_CHAR(suah.override_enrolled_cp),
779 cst_override_eftsu, TO_CHAR(suah.override_eftsu),
780 cst_override_achievable_cp, TO_CHAR(suah.override_achievable_cp),
781 cst_override_outcome_due_dt, TO_CHAR(suah.override_outcome_due_dt),
782 cst_override_credit_reason, suah.override_credit_reason,
783 cst_grading_schema_code, suah.grading_schema_code,
784 cst_enr_method_type, suah.enr_method_type,
785 --added by rvangala 01-OCT-2003. Enh Bug# 3052432
786 cst_core_indicator, suah.core_indicator_code) IS NOT NULL
787 ORDER BY
788 suah.hist_start_dt ;
789 v_column_value VARCHAR2(2000);
790 BEGIN -- audp_get_suah_col
791 OPEN c_suah;
792 FETCH c_suah INTO v_column_value;
793 CLOSE c_suah;
794 RETURN v_column_value;
795 EXCEPTION
796 WHEN OTHERS THEN
797 IF (c_suah%ISOPEN) THEN
798 CLOSE c_suah;
799 END IF;
800 RAISE;
801 END;
802 END audp_get_suah_col;
803
804
805 FUNCTION Audp_Get_Suaoh_Col(
806 p_column_name IN user_tab_columns.column_name%TYPE ,
807 p_person_id IN NUMBER ,
808 p_course_cd IN VARCHAR2 ,
809 p_unit_cd IN VARCHAR2 ,
810 p_cal_type IN VARCHAR2 ,
811 p_ci_sequence_number IN NUMBER ,
812 p_outcome_dt IN DATE ,
813 p_hist_end_dt IN DATE,
814 p_uoo_id IN NUMBER)
815 RETURN VARCHAR2 AS
816 BEGIN -- audp_get_suaoh_col
817 -- get the oldest column value (after a given date) for a
818 -- specified column, person_id, course_cd, unit_cd, CAL_TYPE
819 -- ci_sequence_number and for table IGS_AS_SU_ATMPTOUT_H
820 DECLARE
821 cst_grading_schema_cd VARCHAR2(17) := 'GRADING_SCHEMA_CD';
822 cst_version_number VARCHAR2(14) := 'VERSION_NUMBER';
823 cst_grade VARCHAR2(5) := 'GRADE';
824 cst_s_grade_creatn_method_type VARCHAR2(28) := 'S_GRADE_CREATION_METHOD_TYPE';
825 cst_finalised_outcome_ind VARCHAR2(21) := 'FINALISED_OUTCOME_IND';
826 cst_mark VARCHAR2(4) := 'MARK';
827 cst_number_times_keyed VARCHAR2(18) := 'NUMBER_TIMES_KEYED';
828 cst_trnslted_grading_schema_cd VARCHAR2(28) := 'TRANSLATED_GRADING_SCHEMA_CD';
829 cst_translated_version_no VARCHAR2(25) := 'TRANSLATED_VERSION_NUMBER';
830 cst_translated_grade VARCHAR2(16) := 'TRANSLATED_GRADE';
831 cst_translated_dt VARCHAR2(13) := 'TRANSLATED_DT';
832 CURSOR c_suaoh IS
833 SELECT
834 DECODE (
835 p_column_name,
836 cst_grading_schema_cd, suaoh.grading_schema_cd,
837 cst_version_number, TO_CHAR(suaoh.version_number),
838 cst_grade, suaoh.grade,
839 cst_s_grade_creatn_method_type, suaoh.s_grade_creation_method_type,
840 cst_finalised_outcome_ind, suaoh.finalised_outcome_ind,
841 cst_mark, TO_CHAR(suaoh.mark),
842 cst_number_times_keyed, TO_CHAR(suaoh.number_times_keyed),
843 cst_trnslted_grading_schema_cd, suaoh.translated_grading_schema_cd,
844 cst_translated_version_no, TO_CHAR(suaoh.translated_version_number),
845 cst_translated_grade, suaoh.translated_grade,
846 cst_translated_dt, TO_CHAR(suaoh.translated_dt))
847 FROM IGS_AS_SU_ATMPTOUT_H suaoh
848 WHERE suaoh.person_id = p_person_id AND
849 suaoh.course_cd = p_course_cd AND
850 suaoh.uoo_id = p_uoo_id AND
851 suaoh.outcome_dt = p_outcome_dt AND
852 suaoh.hist_start_dt >= p_hist_end_dt AND
853 DECODE (
854 p_column_name,
855 cst_grading_schema_cd, suaoh.grading_schema_cd,
856 cst_version_number, TO_CHAR(suaoh.version_number),
857 cst_grade, suaoh.grade,
858 cst_s_grade_creatn_method_type, suaoh.s_grade_creation_method_type,
859 cst_finalised_outcome_ind, suaoh.finalised_outcome_ind,
860 cst_mark, TO_CHAR(suaoh.mark),
861 cst_number_times_keyed, TO_CHAR(suaoh.number_times_keyed),
862 cst_trnslted_grading_schema_cd, suaoh.translated_grading_schema_cd,
863 cst_translated_version_no, TO_CHAR(suaoh.translated_version_number),
864 cst_translated_grade, suaoh.translated_grade,
865 cst_translated_dt, TO_CHAR(suaoh.translated_dt)) IS NOT NULL
866 ORDER BY suaoh.hist_start_dt;
867 v_column_value VARCHAR2(2000) := NULL;
871 CLOSE c_suaoh;
868 BEGIN
869 OPEN c_suaoh;
870 FETCH c_suaoh INTO v_column_value;
872 RETURN v_column_value;
873 EXCEPTION
874 WHEN OTHERS THEN
875 IF c_suaoh%ISOPEN THEN
876 CLOSE c_suaoh;
877 END IF;
878 RAISE;
879 END;
880 END audp_get_suaoh_col;
881
882
883 FUNCTION Audp_Get_Susah_Col(
884 p_column_name IN user_tab_columns.column_name%TYPE ,
885 p_person_id IN NUMBER ,
886 p_course_cd IN VARCHAR2 ,
887 p_unit_set_cd IN VARCHAR2 ,
888 p_sequence_number IN NUMBER ,
889 p_hist_end_dt IN DATE )
890 RETURN VARCHAR2 AS
891 BEGIN -- audp_get_susah_col
892 -- get the oldest column value (after a given date) for a
893 -- specified column, person_id, course_cd, unit_set_cd, sequence_number
894 -- for IGS_AS_SU_SETATMPT_H table
895 DECLARE
896 cst_us_version_number VARCHAR2(17) := 'US_VERSION_NUMBER';
897 cst_selection_dt VARCHAR2(12) := 'SELECTION_DT';
898 cst_student_confirmed_ind VARCHAR2(21) := 'STUDENT_CONFIRMED_IND';
899 cst_end_dt VARCHAR2(6) := 'END_DT';
900 cst_parent_unit_set_cd VARCHAR2(18) := 'PARENT_UNIT_SET_CD';
901 cst_parent_sequence_number VARCHAR2(22) := 'PARENT_SEQUENCE_NUMBER';
902 cst_primary_set_ind VARCHAR2(15) := 'PRIMARY_SET_IND';
903 cst_voluntary_end_ind VARCHAR2(17) := 'VOLUNTARY_END_IND';
904 cst_authorised_person_id VARCHAR2(20) := 'AUTHORISED_PERSON_ID';
905 cst_authorised_on VARCHAR2(13) := 'AUTHORISED_ON';
906 cst_override_title VARCHAR2(14) := 'OVERRIDE_TITLE';
907 cst_rqrmnts_complete_ind VARCHAR2(20) := 'RQRMNTS_COMPLETE_IND';
908 cst_rqrmnts_complete_dt VARCHAR2(19) := 'RQRMNTS_COMPLETE_DT';
909 cst_s_completed_source_type VARCHAR2(23) := 'S_COMPLETED_SOURCE_TYPE';
910 cst_catalog_cal_type VARCHAR2(16) := 'CATALOG_CAL_TYPE';
911 cst_catalog_seq_num VARCHAR2(15) := 'CATALOG_SEQ_NUM';
912 CURSOR c_susah IS
913 SELECT
914 DECODE (
915 p_column_name,
916 cst_us_version_number, TO_CHAR(susah.us_version_number),
917 cst_selection_dt, IGS_GE_DATE.igscharDT(susah.selection_dt),
918 cst_student_confirmed_ind, susah.student_confirmed_ind,
919 cst_end_dt, IGS_GE_DATE.igscharDT(susah.end_dt),
920 cst_parent_unit_set_cd, susah.parent_unit_set_cd,
921 cst_parent_sequence_number, TO_CHAR(susah.parent_sequence_number),
922 cst_primary_set_ind, susah.primary_set_ind,
923 cst_voluntary_end_ind, susah.voluntary_end_ind,
924 cst_authorised_person_id, TO_CHAR(susah.authorised_person_id),
925 cst_authorised_on, TO_CHAR(susah.authorised_on),
926 cst_override_title, susah.override_title,
927 cst_rqrmnts_complete_ind, susah.rqrmnts_complete_ind,
928 cst_rqrmnts_complete_dt, IGS_GE_DATE.igscharDT(susah.rqrmnts_complete_dt),
929 cst_s_completed_source_type, susah.s_completed_source_type,
930 cst_catalog_cal_type, susah.catalog_cal_type ,
931 cst_catalog_seq_num, TO_CHAR(susah.catalog_seq_num) )
932 FROM IGS_AS_SU_SETATMPT_H susah
933 WHERE susah.person_id = p_person_id AND
934 susah.course_cd = p_course_cd AND
935 susah.unit_set_cd = p_unit_set_cd AND
936 susah.sequence_number = p_sequence_number AND
937 susah.hist_start_dt >= p_hist_end_dt AND
938 DECODE (
939 p_column_name,
940 cst_us_version_number, TO_CHAR(susah.us_version_number),
941 cst_selection_dt, IGS_GE_DATE.igscharDT(susah.selection_dt),
942 cst_student_confirmed_ind, susah.student_confirmed_ind,
943 cst_end_dt, IGS_GE_DATE.igscharDT(susah.end_dt),
944 cst_parent_unit_set_cd, susah.parent_unit_set_cd,
945 cst_parent_sequence_number, TO_CHAR(susah.parent_sequence_number),
946 cst_primary_set_ind, susah.primary_set_ind,
947 cst_voluntary_end_ind, susah.voluntary_end_ind,
948 cst_authorised_person_id, TO_CHAR(susah.authorised_person_id),
949 cst_authorised_on, IGS_GE_DATE.igscharDT(susah.authorised_on),
950 cst_override_title, susah.override_title,
951 cst_rqrmnts_complete_ind, susah.rqrmnts_complete_ind,
952 cst_rqrmnts_complete_dt, IGS_GE_DATE.igscharDT(susah.rqrmnts_complete_dt),
953 cst_s_completed_source_type, susah.s_completed_source_type,
954 cst_catalog_cal_type, susah.catalog_cal_type ,
955 cst_catalog_seq_num, TO_CHAR(susah.catalog_seq_num) ) IS NOT NULL
956 ORDER BY susah.hist_start_dt;
957 v_column_value VARCHAR2(2000) := NULL;
958 BEGIN
959 OPEN c_susah;
960 FETCH c_susah INTO v_column_value;
961 CLOSE c_susah;
962 RETURN v_column_value;
963 EXCEPTION
964 WHEN OTHERS THEN
965 IF c_susah%ISOPEN THEN
966 CLOSE c_susah;
967 END IF;
968 RAISE;
969 END;
970 END audp_get_susah_col;
971
972 FUNCTION ENRP_RET_WAIVE_PERSON_ID(
973 P_H_RULE_WAIVED_PERSON_ID IN NUMBER,
974 P_person_id IN NUMBER,
975 P_course_cd IN VARCHAR2,
976 P_hist_end_dt IN DATE,
977 p_uoo_id IN IGS_EN_SU_ATTEMPT_H_ALL.uoo_id%TYPE
978 )
979 RETURN NUMBER IS
980 ----------------------------------------------
981 -- This Function returns Rule_waived_person_id
982 -- Which will be used in WHERE clause of IGS_AS_SUA_H_V
983 -- This is to avoid error message for the outer Join
984 -- with HZ_PARTIES table
985 --
986 -- Who When What
987 -- knaraset 29-Apr-03 added p_uoo_id as parameter,also removed the other parameters unit_cd,cal_type and sequence_number
988 -- passed uoo_id in call of Igs_Au_Gen_003.audp_get_suah_col,
989 -- as part of MUS build bug 2829262
990 --
991 ----------------------------------------------
992 CURSOR CUR_RWP IS
993 SELECT rule_waived_person_id
994 FROM IGS_EN_SU_ATTEMPT
995 WHERE person_id=p_person_id AND
996 course_cd = p_course_cd AND
997 uoo_id = p_uoo_id;
998 P_RWP NUMBER;
999 p_pers_id_ret NUMBER;
1000 BEGIN
1001 --
1002 -- Get the rule_waived_person_id from the Main Table()
1003 -- which will be returned if rule_waived_person_id is null in History table.
1004 --
1005 OPEN CUR_RWP;
1006 FETCH CUR_RWP INTO P_RWP;
1007 p_pers_id_ret := NVL(P_H_rule_waived_person_id, NVL(Igs_Au_Gen_003.audp_get_suah_col('RULE_WAIVED_PERSON_ID', P_person_id, P_course_cd, P_hist_end_dt,p_uoo_id), P_RWP));
1008
1009 --
1010 -- Return the rule_waived_person_id calculated above.
1011 --
1012 RETURN p_pers_id_ret;
1013
1014 END ENRP_RET_WAIVE_PERSON_ID;
1015
1016 END igs_au_gen_003;