[Home] [Help]
PACKAGE BODY: APPS.IGS_AU_GEN_004
Source
1 PACKAGE BODY IGS_AU_GEN_004 AS
2 /* $Header: IGSAU04B.pls 120.0 2005/06/01 23:26:49 appldev noship $ */
3 /* Change History
4 Who When What
5 sarakshi 30-Apr-2004 Bug#3568858, Added columns ovrd_wkld_val_flag, workload_val_code related logic
6 sarakshi 03-Nov-2003 Enh#3116171, modified audp_get_uvh_col to add the column billing_credit_points related logic
7 sarakshi 02-Sep-2003 Enh#3052452,removed the reference of the column sup_unit_allowed_ind and sub_unit_allowed_ind
8 shtatiko 25-OCT-2002 Modified c_uvh cursor in audp_get_uvh_col procedure as per Bug# 2636716.
9 jbegum 19 April 02 As part of bug fix of bug #2322290 and bug#2250784
10 Removed the following 4 columns
11 BILLING_CREDIT_POINTS,BILLING_HRS,FIN_AID_CP,FIN_AID_HRS
12 from Function Audp_Get_Uvh_Col.*/
13 Function Audp_Get_Trh_Col(
14 p_unit_cd IGS_PS_TCH_RESP_HIST_ALL.unit_cd%TYPE ,
15 p_version_number IGS_PS_TCH_RESP_HIST_ALL.version_number%TYPE ,
16 p_org_unit_cd IGS_PS_TCH_RESP_HIST_ALL.org_unit_cd%TYPE ,
17 p_ou_start_dt IGS_PS_TCH_RESP_HIST_ALL.ou_start_dt%TYPE ,
18 p_hist_date IGS_PS_TCH_RESP_HIST_ALL.hist_start_dt%TYPE )
19 RETURN NUMBER AS
20
21 BEGIN -- audp_get_trh_col
22 -- get the oldest column value (after a given date) of the
23 -- percentage column for a specified unit_cd, version_number,
24 -- org_unit_cd, ou_start_dt and hist_start_dt.
25 DECLARE
26 v_column_value IGS_PS_TCH_RESP_HIST.percentage%TYPE := NULL;
27 CURSOR c_trh IS
28 SELECT trh.percentage
29 FROM IGS_PS_TCH_RESP_HIST trh
30 WHERE trh.unit_cd = p_unit_cd AND
31 trh.version_number = p_version_number AND
32 trh.org_unit_cd = p_org_unit_cd AND
33 trh.ou_start_dt = p_ou_start_dt AND
34 trh.hist_start_dt >=p_hist_date AND
35 trh.percentage IS NOT NULL
36 ORDER BY trh.hist_start_dt;
37 BEGIN
38 OPEN c_trh;
39 FETCH c_trh INTO v_column_value;
40 CLOSE c_trh;
41 RETURN v_column_value;
42 EXCEPTION
43 WHEN OTHERS THEN
44 IF (c_trh%ISOPEN) THEN
45 CLOSE c_trh;
46 END IF;
47 RAISE;
48 END;
49 END audp_get_trh_col;
50
51 Function Audp_Get_Troh_Col(
52 p_unit_cd IGS_PS_TCH_RSOV_HIST_ALL.unit_cd%TYPE ,
53 p_version_number IGS_PS_TCH_RSOV_HIST_ALL.version_number%TYPE ,
54 p_cal_type IGS_PS_TCH_RSOV_HIST_ALL.cal_type%TYPE ,
55 p_ci_sequence_number IGS_PS_TCH_RSOV_HIST_ALL.ci_sequence_number%TYPE ,
56 p_location_cd IGS_PS_TCH_RSOV_HIST_ALL.location_cd%TYPE ,
57 p_unit_class IGS_PS_TCH_RSOV_HIST_ALL.unit_class%TYPE ,
58 p_org_unit_cd IGS_PS_TCH_RSOV_HIST_ALL.org_unit_cd%TYPE ,
59 p_ou_start_dt IGS_PS_TCH_RSOV_HIST_ALL.ou_start_dt%TYPE ,
60 p_hist_date IGS_PS_TCH_RSOV_HIST_ALL.hist_start_dt%TYPE )
61 RETURN NUMBER AS
62
63 BEGIN -- audp_get_troh_col
64 -- get the oldest column value (after a given date) for the percentage column
65 -- and a given unit_cd, version_number, cal_type, ci_sequence-number,
66 -- location_cd, unit_class, org_unit_cd and ou_start_dt.
67 DECLARE
68 v_column_value IGS_PS_TCH_RSOV_HIST.percentage%TYPE := NULL;
69 CURSOR c_troh IS
70 SELECT troh.percentage
71 FROM IGS_PS_TCH_RSOV_HIST troh
72 WHERE troh.unit_cd = p_unit_cd AND
73 troh.version_number = p_version_number AND
74 troh.cal_type = p_cal_type AND
75 troh.ci_sequence_number = p_ci_sequence_number AND
76 troh.location_cd = p_location_cd AND
77 troh.unit_class = p_unit_class AND
78 troh.org_unit_cd = p_org_unit_cd AND
79 troh.ou_start_dt = p_ou_start_dt AND
80 troh.hist_start_dt >= p_hist_date AND
81 troh.percentage IS NOT NULL
82 ORDER BY troh.hist_start_dt;
83 BEGIN
84 OPEN c_troh;
85 FETCH c_troh INTO v_column_value;
86 CLOSE c_troh;
87 RETURN v_column_value;
88 EXCEPTION
89 WHEN OTHERS THEN
90 IF (c_troh%ISOPEN) THEN
91 CLOSE c_troh;
92 END IF;
93 RAISE;
94 END;
95 END audp_get_troh_col;
96
97 Function Audp_Get_Udh_Col(
98 p_unit_cd IGS_PS_UNT_DSCP_HIST_ALL.unit_cd%TYPE ,
99 p_version_number IGS_PS_UNT_DSCP_HIST_ALL.version_number%TYPE ,
100 p_discipline_group_cd IGS_PS_UNT_DSCP_HIST_ALL.discipline_group_cd%TYPE ,
101 p_hist_date IGS_PS_UNT_DSCP_HIST_ALL.hist_start_dt%TYPE )
102 RETURN NUMBER AS
103
104 BEGIN -- audp_get_udh_col
105 -- Get the oldest column value (after a given date) for the percentage
106 -- column, unit_cd, version_number and discipline_group_cd.
107 DECLARE
108 v_column_value igs_ps_unt_dscp_hist.percentage%TYPE := NULL;
109 CURSOR c_udh IS
110 SELECT udh.percentage
111 FROM igs_ps_unt_dscp_hist udh
112 WHERE udh.unit_cd = p_unit_cd AND
113 udh.version_number = p_version_number AND
114 udh.discipline_group_cd = p_discipline_group_cd AND
115 udh.hist_start_dt >= p_hist_date AND
116 udh.percentage IS NOT NULL
117 ORDER BY udh.percentage;
118 BEGIN
119 OPEN c_udh;
120 FETCH c_udh INTO v_column_value;
121 CLOSE c_udh;
122 RETURN v_column_value;
123 EXCEPTION
124 WHEN OTHERS THEN
125 IF (c_udh%ISOPEN) THEN
126 CLOSE c_udh;
127 END IF;
128 RAISE;
129 END;
130 END audp_get_udh_col;
131
132 Function Audp_Get_Uiclh_Col(
133 p_column_name user_tab_columns.column_name%TYPE ,
134 p_unit_int_course_level_cd IGS_PS_UNT_INLV_HIST_ALL.unit_int_course_level_cd%TYPE ,
135 p_hist_end_dt IGS_PS_UNT_INLV_HIST_ALL.hist_end_dt%TYPE )
136 RETURN VARCHAR2 AS
137
138 BEGIN -- audp_get_uiclh_col
139 -- get the oldest column value (after a given date) for a given
140 -- unit_int_course_level_cd
141 DECLARE
142 cst_description VARCHAR2(15) := 'DESCRIPTION';
143 cst_weftsu_factor VARCHAR2(15) := 'WEFTSU_FACTOR';
144 cst_closed_ind VARCHAR2(10) := 'CLOSED_IND';
145 CURSOR c_uiclh IS
146 SELECT DECODE (p_column_name,
147 cst_description, uiclh.description,
148 cst_weftsu_factor, TO_CHAR(uiclh.weftsu_factor),
149 cst_closed_ind, uiclh.closed_ind)
150 FROM IGS_PS_UNT_INLV_HIST uiclh
151 WHERE uiclh.unit_int_course_level_cd = p_unit_int_course_level_cd AND
152 uiclh.hist_start_dt >= p_hist_end_dt AND
153 DECODE (p_column_name,
154 cst_description, uiclh.description,
155 cst_weftsu_factor, TO_CHAR(uiclh.weftsu_factor),
156 cst_closed_ind, uiclh.closed_ind) IS NOT NULL
157 ORDER BY
158 uiclh.hist_start_dt;
159 v_column_value VARCHAR2(2000) := NULL;
160 BEGIN
161 OPEN c_uiclh;
162 FETCH c_uiclh INTO v_column_value;
163 CLOSE c_uiclh;
164 RETURN v_column_value;
165 EXCEPTION
166 WHEN OTHERS THEN
167 IF (c_uiclh%ISOPEN) THEN
168 CLOSE c_uiclh;
169 END IF;
170 RAISE;
171 END;
172 END audp_get_uiclh_col;
173
174 Function Audp_Get_Urch_Col(
175 p_unit_cd IGS_PS_UNIT_REF_HIST_ALL.unit_cd%TYPE ,
176 p_version_number IGS_PS_UNIT_REF_HIST_ALL.version_number%TYPE ,
177 p_reference_cd_type IGS_PS_UNIT_REF_HIST_ALL.reference_cd_type%TYPE ,
178 p_reference_cd IGS_PS_UNIT_REF_HIST_ALL.reference_cd%TYPE ,
179 p_hist_date IGS_PS_UNIT_REF_HIST_ALL.hist_start_dt%TYPE )
180 RETURN VARCHAR2 AS
181
182 BEGIN -- audp_get_urch_col
183 -- Get the oldest column value(after a given date) for a given unit_cd,
184 -- version_number, reference_type and reference_cd
185 DECLARE
186 v_column_value igs_ps_unit_ref_hist.description%TYPE := NULL;
187 CURSOR c_urch IS
188 SELECT urch.description
189 FROM IGS_PS_UNIT_REF_HIST urch
190 WHERE urch.unit_cd = p_unit_cd AND
191 urch.version_number = p_version_number AND
192 urch.reference_cd_type = p_reference_cd_type AND
193 urch.reference_cd = p_reference_cd AND
194 urch.hist_start_dt >= p_hist_date AND
195 urch.description IS NOT NULL
196 ORDER BY urch.hist_start_dt;
197 BEGIN
198 OPEN c_urch;
199 FETCH c_urch INTO v_column_value;
200 CLOSE c_urch;
201 RETURN v_column_value;
202 EXCEPTION
203 WHEN OTHERS THEN
204 IF (c_urch%ISOPEN) THEN
205 CLOSE c_urch;
206 END IF;
207 RAISE;
208 END;
209 END audp_get_urch_col;
210
211 Function Audp_Get_Ush_Col(
212 p_unit_set_cd IGS_EN_UNIT_SET_HIST_ALL.unit_set_cd%TYPE ,
213 p_version_number IGS_EN_UNIT_SET_HIST_ALL.version_number%TYPE ,
214 p_column_name user_tab_columns.column_name%TYPE ,
215 p_hist_end_dt IGS_EN_UNIT_SET_HIST_ALL.hist_end_dt%TYPE )
216 RETURN VARCHAR2 AS
217
218 BEGIN -- audp_get_ush_col
219 -- get the oldest column value (after a given date) for a
220 -- specified column and version_number
221 DECLARE
222 v_column_value VARCHAR2(2000) := NULL;
223 CURSOR c_ush IS
224 SELECT DECODE (p_column_name,
225 'UNIT_SET_STATUS', ush.UNIT_SET_STATUS,
226 'UNIT_SET_CAT', ush.UNIT_SET_CAT,
227 'START_DT', IGS_GE_DATE.igscharDT(ush.start_dt),
228 'REVIEW_DT', IGS_GE_DATE.igscharDT(ush.review_dt),
229 'EXPIRY_DT', IGS_GE_DATE.igscharDT(ush.expiry_dt),
230 'END_DT', IGS_GE_DATE.igscharDT(ush.end_dt),
231 'TITLE', ush.TITLE,
232 'SHORT_TITLE', ush.short_title,
233 'ABBREVIATION', ush.abbreviation,
234 'RESPONSIBLE_ORG_UNIT_CD', ush.responsible_org_unit_cd,
235 'RESPONSIBLE_OU_START_DT', IGS_GE_DATE.igscharDT(ush.responsible_ou_start_dt),
236 'OU_DESCRIPTION', ush.ou_description,
237 'ADMINISTRATIVE_IND', ush.administrative_ind,
238 'AUTHORISATION_RQRD_IND', ush.authorisation_rqrd_ind)
239 FROM IGS_EN_UNIT_SET_HIST ush
240 WHERE ush.unit_set_cd = p_unit_set_cd AND
241 ush.version_number = p_version_number AND
242 ush.hist_start_dt >= p_hist_end_dt AND
243 DECODE (p_column_name,
244 'UNIT_SET_STATUS', ush.UNIT_SET_STATUS,
245 'UNIT_SET_CAT', ush.UNIT_SET_CAT,
246 'START_DT', IGS_GE_DATE.igscharDT(ush.start_dt),
247 'REVIEW_DT', IGS_GE_DATE.igscharDT(ush.review_dt),
248 'EXPIRY_DT', IGS_GE_DATE.igscharDT(ush.expiry_dt),
249 'END_DT', IGS_GE_DATE.igscharDT(ush.end_dt),
250 'TITLE', ush.TITLE,
251 'SHORT_TITLE', ush.short_title,
252 'ABBREVIATION', ush.abbreviation,
253 'RESPONSIBLE_ORG_UNIT_CD', ush.responsible_org_unit_cd,
254 'RESPONSIBLE_OU_START_DT', IGS_GE_DATE.igscharDT(ush.responsible_ou_start_dt),
255 'OU_DESCRIPTION', ush.ou_description,
256 'ADMINISTRATIVE_IND', ush.administrative_ind,
257 'AUTHORISATION_RQRD_IND', ush.authorisation_rqrd_ind) IS NOT NULL
258 ORDER BY ush.hist_start_dt;
259 BEGIN
260 OPEN c_ush;
261 FETCH c_ush INTO v_column_value;
262 CLOSE c_ush;
263 RETURN v_column_value;
264 EXCEPTION
265 WHEN OTHERS THEN
266 IF (c_ush%ISOPEN) THEN
267 CLOSE c_ush;
268 END IF;
269 RAISE;
270 END;
271 END audp_get_ush_col;
272
273 -- Added auditable_ind, audit_permission_ind and max_auditors_allowed
274 -- parameters in SELECT statement as per Bug# 2636716 by shtatiko.
275
276 Function Audp_Get_Uvh_Col(
277 p_unit_cd IGS_PS_UNIT_VER_HIST_ALL.unit_cd%TYPE ,
278 p_version_number IGS_PS_UNIT_VER_HIST_ALL.version_number%TYPE ,
279 p_column_name user_tab_columns.column_name%TYPE ,
280 p_hist_date IGS_PS_UNIT_VER_HIST_ALL.hist_end_dt%TYPE )
281 RETURN VARCHAR2 AS
282
283 BEGIN -- audp_get_uvh_col
284 -- get the oldest column value (after a given date) for a
285 -- specified column, unit_cd and version_number.
286 DECLARE
287 cst_start_dt VARCHAR2(30) := 'START_DT';
288 cst_review_dt VARCHAR2(30) := 'REVIEW_DT';
289 cst_expiry_dt VARCHAR2(30) := 'EXPIRY_DT';
290 cst_end_dt VARCHAR2(30) := 'END_DT';
291 cst_unit_status VARCHAR2(30) := 'UNIT_STATUS';
292 cst_title VARCHAR2(30) := 'TITLE';
293 cst_short_title VARCHAR2(30) := 'SHORT_TITLE';
294 cst_title_override_ind VARCHAR2(30) := 'TITLE_OVERRIDE_IND';
295 cst_abbreviation VARCHAR2(30) := 'ABBREVIATION';
296 cst_unit_level VARCHAR2(30) := 'UNIT_LEVEL';
297 cst_ul_description VARCHAR2(30) := 'UL_DESCRIPTION';
298 cst_credit_point_descriptor VARCHAR2(30) := 'CREDIT_POINT_DESCRIPTOR';
299 cst_enrolled_credit_points VARCHAR2(30) := 'ENROLLED_CREDIT_POINTS';
300 cst_points_override_ind VARCHAR2(30) := 'POINTS_OVERRIDE_IND';
301 cst_supp_exam_permitted_ind VARCHAR2(30) := 'SUPP_EXAM_PERMITTED_IND';
302 cst_coord_person_id VARCHAR2(30) := 'COORD_PERSON_ID';
303 cst_owner_org_unit_cd VARCHAR2(30) := 'OWNER_ORG_UNIT_CD';
304 cst_owner_ou_start_dt VARCHAR2(30) := 'OWNER_OU_START_DT';
305 cst_ou_description VARCHAR2(30) := 'OU_DESCRIPTION';
306 cst_award_course_only_ind VARCHAR2(30) := 'AWARD_COURSE_ONLY_IND';
307 cst_research_unit_ind VARCHAR2(30) := 'RESEARCH_UNIT_IND';
308 cst_industrial_ind VARCHAR2(30) := 'INDUSTRIAL_IND';
309 cst_practical_ind VARCHAR2(30) := 'PRACTICAL_IND';
310 cst_repeatable_ind VARCHAR2(30) := 'REPEATABLE_IND';
311 cst_assessable_ind VARCHAR2(30) := 'ASSESSABLE_IND';
312 cst_achievable_credit_points VARCHAR2(30) := 'ACHIEVABLE_CREDIT_POINTS';
313 cst_points_increment VARCHAR2(30) := 'POINTS_INCREMENT';
314 cst_points_min VARCHAR2(30) := 'POINTS_MIN';
315 cst_points_max VARCHAR2(30) := 'POINTS_MAX';
316 cst_unit_int_course_level_cd VARCHAR2(30) := 'UNIT_INT_COURSE_LEVEL_CD';
317 cst_uicl_description VARCHAR2(30) := 'UICL_DESCRIPTION';
318 v_column_value VARCHAR2(2000) := NULL;
319 -- Added by rbezawad as per PSP001-US on 24-May-2001
320 cst_advance_maximum VARCHAR2(30) := 'ADVANCE_MAXIMUM';
321 cst_approval_date VARCHAR2(30) := 'APPROVAL_DATE';
322 cst_cal_type_enrol_load_cal VARCHAR2(30) := 'CAL_TYPE_ENROL_LOAD_CAL';
323 cst_cal_type_offer_load_cal VARCHAR2(30) := 'CAL_TYPE_OFFER_LOAD_CAL';
324 cst_clock_hours VARCHAR2(30) := 'CLOCK_HOURS';
325 cst_contact_hrs_lab VARCHAR2(30) := 'CONTACT_HRS_LAB';
326 cst_contact_hrs_lecture VARCHAR2(30) := 'CONTACT_HRS_LECTURE';
327 cst_contact_hrs_other VARCHAR2(30) := 'CONTACT_HRS_OTHER';
331 cst_enrollment_maximum VARCHAR2(30) := 'ENROLLMENT_MAXIMUM';
328 cst_continuing_education_units VARCHAR2(30) := 'CONTINUING_EDUCATION_UNITS';
329 cst_curriculum_id VARCHAR2(30) := 'CURRICULUM_ID';
330 cst_enrollment_expected VARCHAR2(30) := 'ENROLLMENT_EXPECTED';
332 cst_enrollment_minimum VARCHAR2(30) := 'ENROLLMENT_MINIMUM';
333 cst_exclude_from_max_cp_limit VARCHAR2(30) := 'EXCLUDE_FROM_MAX_CP_LIMIT';
334 cst_federal_financial_aid VARCHAR2(30) := 'FEDERAL_FINANCIAL_AID';
335 c_institutional_financial_aid VARCHAR2(30) := 'INSTITUTIONAL_FINANCIAL_AID';
336 cst_lab_credit_points VARCHAR2(30) := 'LAB_CREDIT_POINTS';
337 cst_lecture_credit_points VARCHAR2(30) := 'LECTURE_CREDIT_POINTS';
338 cst_level_code VARCHAR2(30) := 'LEVEL_CODE';
339 cst_max_repeat_credit_points VARCHAR2(30) := 'MAX_REPEAT_CREDIT_POINTS';
340 cst_max_repeats_for_credit VARCHAR2(30) := 'MAX_REPEATS_FOR_CREDIT';
341 cst_max_repeats_for_funding VARCHAR2(30) := 'MAX_REPEATS_FOR_FUNDING';
342 cst_non_schd_required_hrs VARCHAR2(30) := 'NON_SCHD_REQUIRED_HRS';
343 cst_other_credit_points VARCHAR2(30) := 'OTHER_CREDIT_POINTS';
344 cst_override_enrollment_max VARCHAR2(30) := 'OVERRIDE_ENROLLMENT_MAX';
345 cst_record_exclusion_flag VARCHAR2(30) := 'RECORD_EXCLUSION_FLAG';
346 c_ss_display_ind VARCHAR2(30) := 'SS_DISPLAY_IND';
347 cst_repeat_code VARCHAR2(30) := 'REPEAT_CODE';
348 cst_rpt_fmly_id VARCHAR2(30) := 'RPT_FMLY_ID';
349 cst_same_teach_period_repeats VARCHAR2(30) := 'SAME_TEACH_PERIOD_REPEATS';
350 c_same_teach_period_repeats_cp VARCHAR2(30) := 'SAME_TEACH_PERIOD_REPEATS_CP';
351 cst_same_teaching_period VARCHAR2(30) := 'SAME_TEACHING_PERIOD';
352 c_sequence_num_enrol_load_cal VARCHAR2(30) := 'SEQUENCE_NUM_ENROL_LOAD_CAL';
353 c_sequence_num_offer_load_cal VARCHAR2(30) := 'SEQUENCE_NUM_OFFER_LOAD_CAL';
354 cst_special_permission_ind VARCHAR2(30) := 'SPECIAL_PERMISSION_IND';
355 cst_state_financial_aid VARCHAR2(30) := 'STATE_FINANCIAL_AID';
356 cst_subtitle VARCHAR2(30) := 'SUBTITLE';
357 cst_subtitle_id VARCHAR2(30) := 'SUBTITLE_ID';
358 cst_subtitle_modifiable_flag VARCHAR2(30) := 'SUBTITLE_MODIFIABLE_FLAG';
359 cst_unit_type_id VARCHAR2(30) := 'UNIT_TYPE_ID';
360 cst_work_load_cp_lab VARCHAR2(30) := 'WORK_LOAD_CP_LAB';
361 cst_work_load_cp_lecture VARCHAR2(30) := 'WORK_LOAD_CP_LECTURE';
362 cst_work_load_other VARCHAR2(30) := 'WORK_LOAD_OTHER';
363 cst_claimable_hours VARCHAR2(30) := 'CLAIMABLE_HOURS';
364 cst_auditable_ind VARCHAR2(30) := 'AUDITABLE_IND';
365 cst_audit_permission_ind VARCHAR2(30) := 'AUDIT_PERMISSION_IND';
366 cst_max_auditors_allowed VARCHAR2(30) := 'MAX_AUDITORS_ALLOWED';
367 l_c_billing_credit_points VARCHAR2(30) := 'BILLING_CREDIT_POINTS';
368 l_c_ovrd_wkld_val_flag VARCHAR2(30) := 'OVRD_WKLD_VAL_FLAG';
369 l_c_workload_val_code VARCHAR2(30) := 'WORKLOAD_VAL_CODE';
370 l_c_billing_hrs VARCHAR2(30) := 'BILLING_HRS';
371
372 CURSOR c_uvh IS
373 SELECT DECODE (p_column_name,
374 cst_start_dt, IGS_GE_DATE.igscharDT(uvh.start_dt),
375 cst_review_dt, IGS_GE_DATE.igscharDT(uvh.review_dt),
376 cst_expiry_dt, IGS_GE_DATE.igscharDT(uvh.expiry_dt),
377 cst_end_dt, IGS_GE_DATE.igscharDT(uvh.end_dt),
378 cst_unit_status, uvh.UNIT_STATUS,
379 cst_title, uvh.TITLE,
380 cst_short_title, uvh.short_title,
381 cst_title_override_ind, uvh.title_override_ind,
382 cst_abbreviation, uvh.abbreviation,
383 cst_unit_level, uvh.UNIT_LEVEL,
384 cst_ul_description, uvh.ul_description,
385 cst_credit_point_descriptor, uvh.CREDIT_POINT_DESCRIPTOR,
386 cst_enrolled_credit_points, TO_CHAR(uvh.enrolled_credit_points),
387 cst_points_override_ind, uvh.points_override_ind,
388 cst_supp_exam_permitted_ind, uvh.supp_exam_permitted_ind,
389 cst_coord_person_id, TO_CHAR(uvh.coord_person_id),
390 cst_owner_org_unit_cd, uvh.owner_org_unit_cd,
391 cst_owner_ou_start_dt, IGS_GE_DATE.igscharDT(uvh.owner_ou_start_dt),
392 cst_ou_description, uvh.ou_description,
393 cst_award_course_only_ind, uvh.award_course_only_ind,
394 cst_research_unit_ind, uvh.research_unit_ind,
395 cst_industrial_ind, uvh.industrial_ind,
396 cst_practical_ind, uvh.practical_ind,
397 cst_repeatable_ind, uvh.repeatable_ind,
398 cst_assessable_ind, uvh.assessable_ind,
399 cst_achievable_credit_points, TO_CHAR(uvh.achievable_credit_points),
400 cst_points_increment, TO_CHAR(uvh.points_increment),
401 cst_points_min, TO_CHAR(uvh.points_min),
402 cst_points_max, TO_CHAR(uvh.points_max),
403 cst_unit_int_course_level_cd, uvh.unit_int_course_level_cd,
404 cst_uicl_description, uvh.uicl_description,
405 cst_advance_maximum, TO_CHAR(uvh.advance_maximum),
406 cst_approval_date, IGS_GE_DATE.igscharDT( uvh.approval_date ),
407 cst_cal_type_enrol_load_cal, uvh.cal_type_enrol_load_cal,
408 cst_cal_type_offer_load_cal, uvh.cal_type_offer_load_cal,
412 cst_contact_hrs_other, TO_CHAR(uvh.contact_hrs_other),
409 cst_clock_hours, TO_CHAR(uvh.clock_hours),
410 cst_contact_hrs_lab, TO_CHAR(uvh.contact_hrs_lab),
411 cst_contact_hrs_lecture, TO_CHAR(uvh.contact_hrs_lecture),
413 cst_continuing_education_units, TO_CHAR(uvh.continuing_education_units),
414 cst_curriculum_id, uvh.curriculum_id,
415 cst_enrollment_expected, TO_CHAR(uvh.enrollment_expected),
416 cst_enrollment_maximum, TO_CHAR(uvh.enrollment_maximum),
417 cst_enrollment_minimum, TO_CHAR(uvh.enrollment_minimum),
418 cst_exclude_from_max_cp_limit, uvh.exclude_from_max_cp_limit,
419 cst_federal_financial_aid, uvh.federal_financial_aid,
420 c_institutional_financial_aid, uvh.institutional_financial_aid,
421 cst_lab_credit_points, TO_CHAR(uvh.lab_credit_points),
422 cst_lecture_credit_points, TO_CHAR(uvh.lecture_credit_points),
423 cst_level_code, uvh.level_code,
424 cst_max_repeat_credit_points, TO_CHAR(uvh.max_repeat_credit_points),
425 cst_max_repeats_for_credit, TO_CHAR(uvh.max_repeats_for_credit),
426 cst_max_repeats_for_funding, TO_CHAR(uvh.max_repeats_for_funding),
427 cst_non_schd_required_hrs, TO_CHAR(uvh.non_schd_required_hrs),
428 cst_other_credit_points, TO_CHAR(uvh.other_credit_points),
429 cst_override_enrollment_max, TO_CHAR(uvh.override_enrollment_max),
430 cst_record_exclusion_flag, uvh.record_exclusion_flag,
431 c_ss_display_ind, uvh.ss_display_ind,
432 cst_repeat_code, uvh.repeat_code,
433 cst_rpt_fmly_id, TO_CHAR(uvh.rpt_fmly_id),
434 cst_same_teach_period_repeats, TO_CHAR(uvh.same_teach_period_repeats),
435 c_same_teach_period_repeats_cp, TO_CHAR(uvh.same_teach_period_repeats_cp),
436 cst_same_teaching_period, uvh.same_teaching_period,
437 c_sequence_num_enrol_load_cal, TO_CHAR(uvh.sequence_num_enrol_load_cal),
438 c_sequence_num_offer_load_cal, TO_CHAR(uvh.sequence_num_offer_load_cal),
439 cst_special_permission_ind, uvh.special_permission_ind,
440 cst_state_financial_aid, uvh.state_financial_aid,
441 cst_subtitle, uvh.subtitle,
442 cst_subtitle_id, TO_CHAR(uvh.subtitle_id),
443 cst_subtitle_modifiable_flag, uvh.subtitle_modifiable_flag,
444 cst_unit_type_id, TO_CHAR(uvh.unit_type_id),
445 cst_work_load_cp_lab, TO_CHAR(uvh.work_load_cp_lab),
446 cst_work_load_cp_lecture, TO_CHAR(uvh.work_load_cp_lecture),
447 cst_work_load_other, TO_CHAR(uvh.work_load_other) ,
448 cst_claimable_hours, TO_CHAR(uvh.claimable_hours),
449 cst_auditable_ind, uvh.auditable_ind,
450 cst_audit_permission_ind, uvh.audit_permission_ind,
451 cst_max_auditors_allowed, TO_CHAR(uvh.max_auditors_allowed),
452 l_c_billing_credit_points, TO_CHAR(uvh.billing_credit_points),
453 l_c_ovrd_wkld_val_flag, uvh.ovrd_wkld_val_flag,
454 l_c_workload_val_code, uvh.workload_val_code,
455 l_c_billing_hrs, TO_CHAR(uvh.billing_hrs)
456 )
457 FROM IGS_PS_UNIT_VER_HIST uvh
458 WHERE uvh.unit_cd = p_unit_cd AND
459 uvh.version_number = p_version_number AND
460 uvh.hist_start_dt >= p_hist_date AND
461 DECODE (p_column_name,
462 cst_start_dt, IGS_GE_DATE.igscharDT(uvh.start_dt),
463 cst_review_dt, IGS_GE_DATE.igscharDT(uvh.review_dt),
464 cst_expiry_dt, IGS_GE_DATE.igscharDT(uvh.expiry_dt),
465 cst_end_dt, IGS_GE_DATE.igscharDT(uvh.end_dt),
466 cst_unit_status, uvh.UNIT_STATUS,
467 cst_title, uvh.TITLE,
468 cst_short_title, uvh.short_title,
469 cst_title_override_ind, uvh.title_override_ind,
470 cst_abbreviation, uvh.abbreviation,
471 cst_unit_level, uvh.UNIT_LEVEL,
472 cst_ul_description, uvh.ul_description,
473 cst_credit_point_descriptor, uvh.CREDIT_POINT_DESCRIPTOR,
474 cst_enrolled_credit_points, TO_CHAR(uvh.enrolled_credit_points),
475 cst_points_override_ind, uvh.points_override_ind,
476 cst_supp_exam_permitted_ind, uvh.supp_exam_permitted_ind,
477 cst_coord_person_id, TO_CHAR(uvh.coord_person_id),
478 cst_owner_org_unit_cd, uvh.owner_org_unit_cd,
479 cst_owner_ou_start_dt, IGS_GE_DATE.igscharDT(uvh.owner_ou_start_dt),
480 cst_ou_description, uvh.ou_description,
481 cst_award_course_only_ind, uvh.award_course_only_ind,
485 cst_repeatable_ind, uvh.repeatable_ind,
482 cst_research_unit_ind, uvh.research_unit_ind,
483 cst_industrial_ind, uvh.industrial_ind,
484 cst_practical_ind, uvh.practical_ind,
486 cst_assessable_ind, uvh.assessable_ind,
487 cst_achievable_credit_points, TO_CHAR(uvh.achievable_credit_points),
488 cst_points_increment, TO_CHAR(uvh.points_increment),
489 cst_points_min, TO_CHAR(uvh.points_min),
490 cst_points_max, TO_CHAR(uvh.points_max),
491 cst_unit_int_course_level_cd, uvh.unit_int_course_level_cd,
492 cst_uicl_description, uvh.uicl_description,
493 cst_advance_maximum, TO_CHAR(uvh.advance_maximum),
494 cst_approval_date, IGS_GE_DATE.igscharDT( uvh.approval_date ),
495 cst_cal_type_enrol_load_cal, uvh.cal_type_enrol_load_cal,
496 cst_cal_type_offer_load_cal, uvh.cal_type_offer_load_cal,
497 cst_clock_hours, TO_CHAR(uvh.clock_hours),
498 cst_contact_hrs_lab, TO_CHAR(uvh.contact_hrs_lab),
499 cst_contact_hrs_lecture, TO_CHAR(uvh.contact_hrs_lecture),
500 cst_contact_hrs_other, TO_CHAR(uvh.contact_hrs_other),
501 cst_continuing_education_units, TO_CHAR(uvh.continuing_education_units),
502 cst_curriculum_id, uvh.curriculum_id,
503 cst_enrollment_expected, TO_CHAR(uvh.enrollment_expected),
504 cst_enrollment_maximum, TO_CHAR(uvh.enrollment_maximum),
505 cst_enrollment_minimum, TO_CHAR(uvh.enrollment_minimum),
506 cst_exclude_from_max_cp_limit, uvh.exclude_from_max_cp_limit,
507 cst_federal_financial_aid, uvh.federal_financial_aid,
508 c_institutional_financial_aid, uvh.institutional_financial_aid,
509 cst_lab_credit_points, TO_CHAR(uvh.lab_credit_points),
510 cst_lecture_credit_points, TO_CHAR(uvh.lecture_credit_points),
511 cst_level_code, uvh.level_code,
512 cst_max_repeat_credit_points, TO_CHAR(uvh.max_repeat_credit_points),
513 cst_max_repeats_for_credit, TO_CHAR(uvh.max_repeats_for_credit),
514 cst_max_repeats_for_funding, TO_CHAR(uvh.max_repeats_for_funding),
515 cst_non_schd_required_hrs, TO_CHAR(uvh.non_schd_required_hrs),
516 cst_other_credit_points, TO_CHAR(uvh.other_credit_points),
517 cst_override_enrollment_max, TO_CHAR(uvh.override_enrollment_max),
518 cst_record_exclusion_flag, uvh.record_exclusion_flag,
519 c_ss_display_ind, uvh.ss_display_ind,
520 cst_repeat_code, uvh.repeat_code,
521 cst_rpt_fmly_id, TO_CHAR(uvh.rpt_fmly_id),
522 cst_same_teach_period_repeats, TO_CHAR(uvh.same_teach_period_repeats),
523 c_same_teach_period_repeats_cp, TO_CHAR(uvh.same_teach_period_repeats_cp),
524 cst_same_teaching_period, uvh.same_teaching_period,
525 c_sequence_num_enrol_load_cal, TO_CHAR(uvh.sequence_num_enrol_load_cal),
526 c_sequence_num_offer_load_cal, TO_CHAR(uvh.sequence_num_offer_load_cal),
527 cst_special_permission_ind, uvh.special_permission_ind,
528 cst_state_financial_aid, uvh.state_financial_aid,
529 cst_subtitle, uvh.subtitle,
530 cst_subtitle_id, TO_CHAR(uvh.subtitle_id),
531 cst_subtitle_modifiable_flag, uvh.subtitle_modifiable_flag,
532 cst_unit_type_id, TO_CHAR(uvh.unit_type_id),
533 cst_work_load_cp_lab, TO_CHAR(uvh.work_load_cp_lab),
534 cst_work_load_cp_lecture, TO_CHAR(uvh.work_load_cp_lecture),
535 cst_work_load_other, TO_CHAR(uvh.work_load_other),
536 cst_claimable_hours, TO_CHAR(uvh.claimable_hours),
537 cst_auditable_ind, uvh.auditable_ind,
538 cst_audit_permission_ind, uvh.audit_permission_ind,
539 cst_max_auditors_allowed, TO_CHAR(uvh.max_auditors_allowed),
540 l_c_billing_credit_points, TO_CHAR(uvh.billing_credit_points),
541 l_c_ovrd_wkld_val_flag, uvh.ovrd_wkld_val_flag,
542 l_c_workload_val_code, uvh.workload_val_code,
543 l_c_billing_hrs, uvh.billing_hrs
544
545 ) IS NOT NULL
546 ORDER BY hist_start_dt;
547 BEGIN
548 OPEN c_uvh;
549 FETCH c_uvh INTO v_column_value;
550 CLOSE c_uvh;
551 RETURN v_column_value;
552 EXCEPTION
553 WHEN OTHERS THEN
554 IF (c_uvh%ISOPEN) THEN
555 CLOSE c_uvh;
556 END IF;
557 RAISE;
558 END;
559 END audp_get_uvh_col;
560
561 END IGS_AU_GEN_004;