[Home] [Help]
PACKAGE BODY: APPS.IGS_AU_GEN_002
Source
1 PACKAGE BODY igs_au_gen_002 AS
2 /* $Header: IGSAU02B.pls 120.0 2005/06/03 15:47:31 appldev noship $ */
3 /*
4 CHANGE HISTORY :
5 WHO WHEN WHAT
6 ijeddy 05-nov-2003 Bug# 3181938; Modified this object as per Summary Measurement Of Attainment FD.
7 smvk 10-Oct-2003 Enh # 3052445. Added MAX_WLST_PER_STUD to the function audp_get_cvh_col.
8 vvutukur 19_oct-2002 Enh#2608227.Removed references to obsoleted columns std_ft_completion_time,std_ft_completion_time.
9 Also removed references to DEFAULT keyword to avoid gscc File.Pkg.22 warnings.
10 vvutukur 29-Jul-2002 Bug#2425767.Removed references to payment_hierarchy_rank(obsoleted column)
11 from functions audp_get_fcflh_col,audp_get_ftcih_col.
12 vchappid 25-Apr-2002 Bug# 2329407, Removed reference to the columns fin_cal_type, fin_ci_sequence_number, account_cd
13 in the function audp_get_ftcih_col
14 ayedubat 25-MAY-2001 modified the function,audp_get_cvh_col to add the
15 new columns according to the DLD,PSP001-US
16 */
17
18 FUNCTION audp_get_culh_col(
19 p_unit_cd igs_ps_unit_lvl_hist_all.unit_cd%TYPE ,
20 p_course_type igs_ps_unit_lvl_hist_all.course_type%TYPE DEFAULT NULL,
21 p_version_number igs_ps_unit_lvl_hist_all.version_number%TYPE ,
22 p_column_name user_tab_columns.column_name%TYPE ,
23 p_hist_date igs_ps_unit_lvl_hist_all.hist_start_dt%TYPE,
24 p_course_cd IGS_PS_UNIT_LVL_HIST_ALL.course_cd%TYPE,
25 p_course_version_number IGS_PS_UNIT_LVL_HIST_ALL.course_version_number%TYPE
26
27 )
28 RETURN VARCHAR2 AS
29
30 BEGIN -- audp_get_culh_col
31 -- get the oldest column value (after a given date) for the
32 -- WAM_WEIGHTING column, unit_cd, and version_number.
33 DECLARE
34 v_column_value VARCHAR2(2000) := NULL;
35 CURSOR c_culh IS
36 SELECT decode (p_column_name,
37 'UNIT_LEVEL', culh.unit_level,
38 'WAM_WEIGHTING', TO_CHAR(culh.wam_weighting))
39 FROM igs_ps_unit_lvl_hist culh
40 WHERE culh.unit_cd = p_unit_cd AND
41 culh.version_number = p_version_number AND
42 culh.course_cd = p_course_cd AND
43 culh.course_version_number = p_course_version_number AND
44 culh.hist_start_dt >= p_hist_date AND
45 decode (p_column_name,
46 'UNIT_LEVEL', culh.unit_level,
47 'WAM_WEIGHTING', TO_CHAR(culh.wam_weighting)) IS NOT NULL
48 ORDER BY culh.hist_start_dt;
49 BEGIN
50 OPEN c_culh;
51 FETCH c_culh INTO v_column_value;
52 CLOSE c_culh;
53 RETURN v_column_value;
54 EXCEPTION
55 WHEN OTHERS THEN
56 IF (c_culh%isopen) THEN
57 CLOSE c_culh;
58 END IF;
59 RAISE;
60 END;
61 END audp_get_culh_col;
62
63 FUNCTION audp_get_cvh_col(
64 /*************************************************************
65 Created By :
66 Date Created By :
67 Purpose :
68 Know limitations, enhancements or remarks
69 Change History
70 Who When What
71 sarakshi 23-Jan-2004 Enh#3345205, added column annual_instruction_time in the select statement
72 vvutukur 19_oct-2002 Enh#2608227.removed references to std_ft_completion_time,std_pt_completion_time
73 ayedubat 25-MAY-2001 modified the procedure to add new columns
74 (reverse chronological order - newest change first)
75 ***************************************************************/
76 p_course_cd igs_ps_ver_hist_all.course_cd%TYPE ,
77 p_version_number igs_ps_ver_hist_all.version_number%TYPE ,
78 p_column_name user_tab_columns.column_name%TYPE ,
79 p_hist_date igs_ps_ver_hist_all.hist_start_dt%TYPE )
80 RETURN VARCHAR2 AS
81
82 BEGIN -- audp_get_cvh_col
83 -- Get the oldest column value (after a given date) for a given
84 -- column name, course_cd and version_number.
85 DECLARE
86 cst_start_dt VARCHAR2(30) := 'START_DT';
87 cst_review_dt VARCHAR2(30) := 'REVIEW_DT';
88 cst_expiry_dt VARCHAR2(30) := 'EXPIRY_DT';
89 cst_end_dt VARCHAR2(30) := 'END_DT';
90 cst_course_status VARCHAR2(30) := 'COURSE_STATUS';
91 cst_title VARCHAR2(30) := 'TITLE';
92 cst_short_title VARCHAR2(30) := 'SHORT_TITLE';
93 cst_abbreviation VARCHAR2(30) := 'ABBREVIATION';
94 cst_supp_exam_permitted_ind VARCHAR2(30) := 'SUPP_EXAM_PERMITTED_IND';
95 cst_generic_course_ind VARCHAR2(30) := 'GENERIC_COURSE_IND';
96 cst_graduate_students_ind VARCHAR2(30) := 'GRADUATE_STUDENTS_IND';
97 cst_count_intrmsn_in_time_ind VARCHAR2(30) := 'COUNT_INTRMSN_IN_TIME_IND';
98 cst_intrmsn_allowed_ind VARCHAR2(30) := 'INTRMSN_ALLOWED_IND';
99 cst_course_type VARCHAR2(30) := 'COURSE_TYPE';
100 cst_ct_description VARCHAR2(30) := 'CT_DESCRIPTION';
101 cst_responsible_org_unit_cd VARCHAR2(30) := 'RESPONSIBLE_ORG_UNIT_CD';
102 cst_responsible_ou_start_dt VARCHAR2(30) := 'RESPONSIBLE_OU_START_DT';
103 cst_ou_description VARCHAR2(30) := 'OU_DESCRIPTION';
104 cst_govt_special_course_type VARCHAR2(30) := 'GOVT_SPECIAL_COURSE_TYPE';
105 cst_gsct_description VARCHAR2(30) := 'GSCT_DESCRIPTION';
106 cst_qualification_recency VARCHAR2(30) := 'QUALIFICATION_RECENCY';
107 cst_external_adv_stnd_limit VARCHAR2(30) := 'EXTERNAL_ADV_STND_LIMIT';
108 cst_internal_adv_stnd_limit VARCHAR2(30) := 'INTERNAL_ADV_STND_LIMIT';
109 cst_contact_hours VARCHAR2(30) := 'CONTACT_HOURS';
110 cst_credit_points_required VARCHAR2(30) := 'CREDIT_POINTS_REQUIRED';
111 cst_govt_course_load VARCHAR2(30) := 'GOVT_COURSE_LOAD';
112 cst_std_annual_load VARCHAR2(30) := 'STD_ANNUAL_LOAD';
113 cst_course_total_eftsu VARCHAR2(30) := 'COURSE_TOTAL_EFTSU';
114 cst_max_intrmsn_duration VARCHAR2(30) := 'MAX_INTRMSN_DURATION';
115 cst_num_of_unts_bfr_intrmsn VARCHAR2(30) := 'NUM_OF_UNITS_BEFORE_INTRMSN';
116 cst_min_sbmsn_percentage VARCHAR2(30) := 'MIN_SBMSN_PERCENTAGE';
117 cst_min_cp_per_calendar VARCHAR2(30) := 'MIN_CP_PER_CALENDAR';
118 cst_approval_date VARCHAR2(30) := 'APPROVAL_DATE';
119 cst_external_approval_date VARCHAR2(30) := 'EXTERNAL_APPROVAL_DATE';
120 cst_federal_financial_aid VARCHAR2(30) := 'FEDERAL_FINANCIAL_AID';
121 c_institutional_financial_aid VARCHAR2(30) := 'INSTITUTIONAL_FINANCIAL_AID';
122 cst_max_cp_per_teaching_period VARCHAR2(30) := 'MAX_CP_PER_TEACHING_PERIOD';
123 cst_residency_cp_required VARCHAR2(30) := 'RESIDENCY_CP_REQUIRED';
124 cst_state_financial_aid VARCHAR2(30) := 'STATE_FINANCIAL_AID';
125 cst_primary_program_rank VARCHAR2(30) := 'PRIMARY_PROGRAM_RANK';
126 l_c_max_wlst_per_stud VARCHAR2(30) := 'MAX_WLST_PER_STUD';
127 l_c_annual_instruction_time VARCHAR2(30) := 'ANNUAL_INSTRUCTION_TIME';
128 v_column_value VARCHAR2(2000) := NULL;
129 CURSOR c_cvh IS
130 SELECT decode (p_column_name,
131 cst_start_dt, igs_ge_date.igschardt(cvh.start_dt),
132 cst_review_dt, igs_ge_date.igschardt(cvh.review_dt),
133 cst_expiry_dt, igs_ge_date.igschardt(cvh.expiry_dt),
134 cst_end_dt, igs_ge_date.igschardt(cvh.end_dt),
135 cst_course_status, cvh.course_status,
136 cst_title, cvh.title,
137 cst_short_title, cvh.short_title,
138 cst_abbreviation, cvh.abbreviation,
139 cst_supp_exam_permitted_ind, cvh.supp_exam_permitted_ind,
140 cst_generic_course_ind, cvh.generic_course_ind,
141 cst_graduate_students_ind, cvh.graduate_students_ind,
142 cst_count_intrmsn_in_time_ind, cvh.count_intrmsn_in_time_ind,
143 cst_intrmsn_allowed_ind, cvh.intrmsn_allowed_ind,
144 cst_course_type, cvh.course_type,
145 cst_ct_description, cvh.ct_description,
146 cst_responsible_org_unit_cd, cvh.responsible_org_unit_cd,
147 cst_responsible_ou_start_dt, igs_ge_date.igschardt(cvh.responsible_ou_start_dt),
148 cst_ou_description, cvh.ou_description,
149 cst_govt_special_course_type, cvh.govt_special_course_type,
150 cst_gsct_description, cvh.gsct_description,
151 cst_qualification_recency, TO_CHAR(cvh.qualification_recency),
152 cst_external_adv_stnd_limit, TO_CHAR(cvh.external_adv_stnd_limit),
153 cst_internal_adv_stnd_limit, TO_CHAR(cvh.internal_adv_stnd_limit),
154 cst_contact_hours, TO_CHAR(cvh.contact_hours),
155 cst_credit_points_required, TO_CHAR(cvh.credit_points_required),
156 cst_govt_course_load, TO_CHAR(cvh.govt_course_load),
157 cst_std_annual_load, TO_CHAR(cvh.std_annual_load),
158 cst_course_total_eftsu, TO_CHAR(cvh.course_total_eftsu),
159 cst_max_intrmsn_duration, TO_CHAR(cvh.max_intrmsn_duration),
160 cst_num_of_unts_bfr_intrmsn, TO_CHAR(cvh.num_of_units_before_intrmsn),
161 cst_min_sbmsn_percentage, TO_CHAR(cvh.min_sbmsn_percentage),
162 cst_min_cp_per_calendar, TO_CHAR(cvh.min_cp_per_calendar),
163 cst_approval_date, igs_ge_date.igschardt(cvh.approval_date),
164 cst_external_approval_date, igs_ge_date.igschardt(cvh.external_approval_date),
165 cst_federal_financial_aid, cvh.federal_financial_aid,
166 c_institutional_financial_aid, cvh.institutional_financial_aid,
167 cst_max_cp_per_teaching_period, TO_CHAR(cvh.max_cp_per_teaching_period),
168 cst_residency_cp_required, TO_CHAR(residency_cp_required),
169 cst_state_financial_aid, state_financial_aid,
170 cst_primary_program_rank, TO_CHAR(primary_program_rank),
171 l_c_max_wlst_per_stud, TO_CHAR(max_wlst_per_stud),
172 l_c_annual_instruction_time, TO_CHAR(annual_instruction_time))
173 FROM igs_ps_ver_hist cvh
174 WHERE cvh.course_cd = p_course_cd AND
175 cvh.version_number = p_version_number AND
176 cvh.hist_start_dt >= p_hist_date AND
177 decode (p_column_name,
178 cst_start_dt, igs_ge_date.igschardt(cvh.start_dt),
179 cst_review_dt, igs_ge_date.igschardt(cvh.review_dt),
180 cst_expiry_dt, igs_ge_date.igschardt(cvh.expiry_dt),
181 cst_end_dt, igs_ge_date.igschardt(cvh.end_dt),
182 cst_course_status, cvh.course_status,
183 cst_title, cvh.title,
184 cst_short_title, cvh.short_title,
185 cst_abbreviation, cvh.abbreviation,
186 cst_supp_exam_permitted_ind, cvh.supp_exam_permitted_ind,
187 cst_generic_course_ind, cvh.generic_course_ind,
188 cst_graduate_students_ind, cvh.graduate_students_ind,
189 cst_count_intrmsn_in_time_ind, cvh.count_intrmsn_in_time_ind,
190 cst_intrmsn_allowed_ind, cvh.intrmsn_allowed_ind,
191 cst_course_type, cvh.course_type,
192 cst_ct_description, cvh.ct_description,
193 cst_responsible_org_unit_cd, cvh.responsible_org_unit_cd,
194 cst_responsible_ou_start_dt, igs_ge_date.igschardt(cvh.responsible_ou_start_dt),
195 cst_ou_description, cvh.ou_description,
196 cst_govt_special_course_type, cvh.govt_special_course_type,
197 cst_gsct_description, cvh.gsct_description,
198 cst_qualification_recency, TO_CHAR(cvh.qualification_recency),
199 cst_external_adv_stnd_limit, TO_CHAR(cvh.external_adv_stnd_limit),
200 cst_internal_adv_stnd_limit, TO_CHAR(cvh.internal_adv_stnd_limit),
201 cst_contact_hours, TO_CHAR(cvh.contact_hours),
202 cst_credit_points_required, TO_CHAR(cvh.credit_points_required),
203 cst_govt_course_load, TO_CHAR(cvh.govt_course_load),
204 cst_std_annual_load, TO_CHAR(cvh.std_annual_load),
205 cst_course_total_eftsu, TO_CHAR(cvh.course_total_eftsu),
206 cst_max_intrmsn_duration, TO_CHAR(cvh.max_intrmsn_duration),
207 cst_num_of_unts_bfr_intrmsn, TO_CHAR(cvh.num_of_units_before_intrmsn),
208 cst_min_sbmsn_percentage, TO_CHAR(cvh.min_sbmsn_percentage),
209 cst_min_cp_per_calendar, TO_CHAR(cvh.min_cp_per_calendar),
210 cst_approval_date, igs_ge_date.igschardt(cvh.approval_date),
211 cst_external_approval_date, igs_ge_date.igschardt(cvh.external_approval_date),
212 cst_federal_financial_aid, cvh.federal_financial_aid,
213 c_institutional_financial_aid, cvh.institutional_financial_aid,
214 cst_max_cp_per_teaching_period, TO_CHAR(cvh.max_cp_per_teaching_period),
215 cst_residency_cp_required, TO_CHAR(residency_cp_required),
216 cst_state_financial_aid, state_financial_aid,
217 cst_primary_program_rank, TO_CHAR(primary_program_rank),
218 l_c_max_wlst_per_stud, TO_CHAR(max_wlst_per_stud),
219 l_c_annual_instruction_time, TO_CHAR(annual_instruction_time)) IS NOT NULL
220 ORDER BY cvh.hist_start_dt;
221 BEGIN
222 OPEN c_cvh;
223 FETCH c_cvh INTO v_column_value;
224 CLOSE c_cvh;
225 RETURN v_column_value;
226 EXCEPTION
227 WHEN OTHERS THEN
228 IF (c_cvh%isopen) THEN
229 CLOSE c_cvh;
230 END IF;
231 RAISE;
232 END;
233 END audp_get_cvh_col;
234
235
236 FUNCTION audp_get_dh_col(
237 p_column_name user_tab_columns.column_name%TYPE ,
238 p_dscplne_grp_cd igs_ps_dscp_hist_all.discipline_group_cd%TYPE ,
239 p_hist_end_dt igs_ps_dscp_hist_all.hist_end_dt%TYPE )
240 RETURN VARCHAR2 AS
241
242 BEGIN -- audp_get_dh_col
243 -- get the oldest column value (after a given date) for a given
244 -- discipline_cd
245 DECLARE
246 cst_description VARCHAR2(11) := 'DESCRIPTION';
247 cst_funding_index_1 VARCHAR2(15) := 'FUNDING_INDEX_1';
248 cst_funding_index_2 VARCHAR2(15) := 'FUNDING_INDEX_2';
249 cst_funding_index_3 VARCHAR2(15) := 'FUNDING_INDEX_3';
250 cst_gvt_dscplne_grp_cd VARCHAR2(24) := 'GOVT_DISCIPLINE_GROUP_CD';
251 cst_closed_ind VARCHAR2(10) := 'CLOSED_IND';
252 CURSOR c_dh IS
253 SELECT decode (p_column_name,
254 cst_description, dh.description,
255 cst_funding_index_1, TO_CHAR(dh.funding_index_1),
256 cst_funding_index_2, TO_CHAR(dh.funding_index_2),
257 cst_funding_index_3, TO_CHAR(dh.funding_index_3),
258 cst_gvt_dscplne_grp_cd, dh.govt_discipline_group_cd,
259 cst_closed_ind, dh.closed_ind)
260 FROM igs_ps_dscp_hist dh
261 WHERE dh.discipline_group_cd = p_dscplne_grp_cd AND
262 dh.hist_start_dt >= p_hist_end_dt AND
263 decode (p_column_name,
264 cst_description, dh.description,
265 cst_funding_index_1, TO_CHAR(dh.funding_index_1),
266 cst_funding_index_2, TO_CHAR(dh.funding_index_2),
267 cst_funding_index_3, TO_CHAR(dh.funding_index_3),
268 cst_gvt_dscplne_grp_cd, dh.govt_discipline_group_cd,
269 cst_closed_ind, dh.closed_ind) IS NOT NULL
270 ORDER BY
274 OPEN c_dh;
271 dh.hist_start_dt;
272 v_column_value VARCHAR2(2000) := NULL;
273 BEGIN
275 FETCH c_dh INTO v_column_value;
276 CLOSE c_dh;
277 RETURN v_column_value;
278 EXCEPTION
279 WHEN OTHERS THEN
280 IF (c_dh%isopen) THEN
281 CLOSE c_dh;
282 END IF;
283 RAISE;
284 END;
285 END audp_get_dh_col;
286
287 FUNCTION audp_get_fcflh_col(
288 p_column_name IN user_tab_columns.column_name%TYPE ,
289 p_fee_cat IN igs_fi_f_cat_f_lbl_h_all.fee_cat%TYPE ,
290 p_fee_cal_type IN VARCHAR2 ,
291 p_fee_ci_sequence_number IN NUMBER ,
292 p_fee_type IN igs_fi_f_cat_f_lbl_h_all.fee_type%TYPE ,
293 p_hist_end_dt IN DATE )
294 RETURN VARCHAR2 AS
295 /*************************************************************
296 Created By :
297 Date Created By :
298 Purpose :
299 Know limitations, enhancements or remarks
300 Change History
301 Who When What
302 (reverse chronological order - newest change first)
303 vvutukur 29-Jul-2002 Bug2425767.Removed references to payment_hierarchy_rank column from cursor
304 c_fcflh.
305 ***************************************************************/
306
307 BEGIN -- audp_get_fcflh_col
308 -- get the oldest column value (after a given date) for a given
309 -- fee_cat, fee_cal_type, fee_ci_sequence_number, fee_type
310 DECLARE
311 cst_fee_liability_status VARCHAR2(30) := 'FEE_LIABILITY_STATUS';
312 cst_start_dt_alias VARCHAR2(30) := 'START_DT_ALIAS';
313 cst_start_dai_sequence_number VARCHAR2(30) := 'START_DAI_SEQUENCE_NUMBER';
314 cst_s_chg_method_type VARCHAR2(30) := 'S_CHG_METHOD_TYPE';
315 cst_rul_sequence_number VARCHAR2(30) := 'RUL_SEQUENCE_NUMBER';
316 CURSOR c_fcflh IS
317 SELECT decode (p_column_name,
318 cst_fee_liability_status, fcflh.fee_liability_status,
319 cst_start_dt_alias, fcflh.start_dt_alias,
320 cst_start_dai_sequence_number, TO_CHAR(fcflh.start_dai_sequence_number),
321 cst_s_chg_method_type, fcflh.s_chg_method_type,
322 cst_rul_sequence_number, TO_CHAR(fcflh.rul_sequence_number))
323 FROM igs_fi_f_cat_f_lbl_h fcflh
324 WHERE fcflh.fee_cat = p_fee_cat AND
325 fcflh.fee_cal_type = p_fee_cal_type AND
326 fcflh.fee_ci_sequence_number = p_fee_ci_sequence_number AND
327 fcflh.fee_type = p_fee_type AND
328 fcflh.hist_start_dt >= p_hist_end_dt AND
329 decode (p_column_name,
330 cst_start_dt_alias, fcflh.start_dt_alias,
331 cst_start_dai_sequence_number, TO_CHAR(fcflh.start_dai_sequence_number),
332 cst_s_chg_method_type, fcflh.s_chg_method_type,
333 cst_rul_sequence_number, TO_CHAR(fcflh.rul_sequence_number)
334 ) IS NOT NULL
335 ORDER BY
336 fcflh.hist_start_dt;
337 v_column_value VARCHAR2(2000) := NULL;
338 BEGIN
339 OPEN c_fcflh;
340 FETCH c_fcflh INTO v_column_value;
341 CLOSE c_fcflh;
342 RETURN v_column_value;
343 EXCEPTION
344 WHEN OTHERS THEN
345 IF (c_fcflh%isopen) THEN
346 CLOSE c_fcflh;
347 END IF;
348 RAISE;
349 END;
350 END audp_get_fcflh_col;
351
352 FUNCTION audp_get_fosh_col(
353 p_column_name user_tab_columns.column_name%TYPE ,
354 p_field_of_study igs_ps_fld_stdy_hist_all.field_of_study%TYPE ,
355 p_hist_end_dt igs_ps_fld_stdy_hist_all.hist_end_dt%TYPE )
356 RETURN VARCHAR2 AS
357
358 BEGIN -- audp_get_fosh_col
359 -- get the oldest column value (after a given date) for a
360 -- specified field_of_study from IGS_PS_FLD_STDY_HIST
361 DECLARE
362 CURSOR c_fosh IS
363 SELECT
364 decode (p_column_name,
365 'DESCRIPTION', fosh.description,
366 'GOVT_FIELD_OF_STUDY', fosh.govt_field_of_study,
367 'CLOSED_IND', fosh.closed_ind)
368 FROM igs_ps_fld_stdy_hist fosh
369 WHERE fosh.field_of_study = p_field_of_study AND
370 fosh.hist_start_dt >= p_hist_end_dt AND
371 decode (p_column_name,
372 'DESCRIPTION', fosh.description,
373 'GOVT_FIELD_OF_STUDY', fosh.govt_field_of_study,
374 'CLOSED_IND', fosh.closed_ind) IS NOT NULL
375 ORDER BY
376 fosh.hist_start_dt;
377 v_column_value VARCHAR2(2000) := NULL;
378 BEGIN
379 OPEN c_fosh;
380 FETCH c_fosh INTO v_column_value;
381 CLOSE c_fosh;
382 RETURN v_column_value;
383 EXCEPTION
384 WHEN OTHERS THEN
385 IF c_fosh%isopen THEN
386 CLOSE c_fosh;
387 END IF;
388 RAISE;
389 END;
390 END audp_get_fosh_col;
391
392 FUNCTION audp_get_fsh_col(
393 p_funding_source igs_fi_fund_src_hist_all.funding_source%TYPE ,
394 p_column_name user_tab_columns.column_name%TYPE ,
395 p_hist_end_dt igs_fi_fund_src_hist_all.hist_end_dt%TYPE )
396 RETURN VARCHAR2 AS
397
398 BEGIN -- audp_get_fsh_col
399 -- get the oldest column value (after a given date) for a given
400 -- funding source
401 DECLARE
402 v_column_value VARCHAR2(2000) := NULL;
403 CURSOR c_fsh IS
404 SELECT
405 decode (p_column_name,
406 'DESCRIPTION', fsh.description,
407 'GOVT_FUNDING_SOURCE', TO_CHAR(fsh.govt_funding_source),
408 'CLOSED_IND', fsh.closed_ind)
412 decode (p_column_name,
409 FROM igs_fi_fund_src_hist fsh
410 WHERE fsh.funding_source = p_funding_source AND
411 fsh.hist_start_dt >= p_hist_end_dt AND
413 'DESCRIPTION', fsh.description,
414 'GOVT_FUNDING_SOURCE', TO_CHAR(fsh.govt_funding_source),
415 'CLOSED_IND', fsh.closed_ind)IS NOT NULL
416 ORDER BY
417 fsh.hist_start_dt;
418 BEGIN
419 OPEN c_fsh;
420 FETCH c_fsh INTO v_column_value;
421 CLOSE c_fsh;
422 RETURN v_column_value;
423 EXCEPTION
424 WHEN OTHERS THEN
425 IF(c_fsh%isopen) THEN
426 CLOSE c_fsh;
427 END IF;
428 END;
429 END audp_get_fsh_col;
430
431 FUNCTION audp_get_fsrh_col(
432 p_course_cd igs_fi_fd_src_rstn_h_all.course_cd%TYPE ,
433 p_version_number igs_fi_fd_src_rstn_h_all.version_number%TYPE ,
434 p_funding_source igs_fi_fd_src_rstn_h_all.funding_source%TYPE ,
435 p_column_name user_tab_columns.column_name%TYPE ,
436 p_hist_date igs_fi_fd_src_rstn_h_all.hist_start_dt%TYPE )
437 RETURN VARCHAR2 AS
438
439 BEGIN -- audp_get_fsrh_col
440 -- get the oldest column value (after a given date) for the dflt_ind column
441 -- and a given course_cd, version_number and funding_source.
442 DECLARE
443 v_column_value VARCHAR2(1) := NULL;
444 CURSOR c_fsrh IS
445 SELECT fsrh.dflt_ind
446 FROM igs_fi_fd_src_rstn_h fsrh
447 WHERE fsrh.course_cd = p_course_cd AND
448 fsrh.version_number = p_version_number AND
449 fsrh.funding_source = p_funding_source AND
450 fsrh.hist_start_dt >= p_hist_date AND
451 fsrh.dflt_ind IS NOT NULL
452 ORDER BY fsrh.hist_start_dt;
453 BEGIN
454 OPEN c_fsrh;
455 FETCH c_fsrh INTO v_column_value;
456 CLOSE c_fsrh;
457 RETURN v_column_value;
458 EXCEPTION
459 WHEN OTHERS THEN
460 IF (c_fsrh%isopen) THEN
461 CLOSE c_fsrh;
462 END IF;
463 RAISE;
464 END;
465 END audp_get_fsrh_col;
466
467 FUNCTION audp_get_ftcih_col(
468 p_column_name IN user_tab_columns.column_name%TYPE ,
469 p_fee_type IN igs_fi_fee_type_ci_h_all.fee_type%TYPE ,
470 p_fee_cal_type IN igs_fi_fee_type_ci_h_all.fee_cal_type%TYPE ,
471 p_fee_ci_sequence_number IN NUMBER ,
472 p_hist_end_dt IN igs_fi_fee_type_ci_h_all.hist_end_dt%TYPE )
473 RETURN VARCHAR2 AS
474 /*************************************************************
475 Created By :
476 Date Created By :
477 Purpose :
478 Know limitations, enhancements or remarks
479 Change History
480 Who When What
481 (reverse chronological order - newest change first)
482 vvutukur 29-Jul-2002 Bug2425767.Removed references payment_hierarchy_rank(obsoleted column)
483 from cursor c_ftcih.
484 ***************************************************************/
485 BEGIN -- audp_get_ftcih_col
486 -- get the oldest column value (after a given date) for a given
487 -- p_fee_type, p_fee_cal_type, p_fee_ci_sequence_number
488 DECLARE
489 cst_fee_type_ci_status VARCHAR2(30) := 'FEE_TYPE_CI_STATUS';
490 cst_start_dt_alias VARCHAR2(30) := 'START_DT_ALIAS';
491 cst_start_dai_sequence_number VARCHAR2(30) := 'START_DAI_SEQUENCE_NUMBER';
492 cst_end_dt_alias VARCHAR2(30) := 'END_DT_ALIAS';
493 cst_end_dai_sequence_number VARCHAR2(30) := 'END_DAI_SEQUENCE_NUMBER';
494 cst_retro_dt_alias VARCHAR2(30) := 'RETRO_DT_ALIAS';
495 cst_retro_dai_sequence_number VARCHAR2(30) := 'RETRO_DAI_SEQUENCE_NUMBER';
496 cst_s_chg_method_type VARCHAR2(30) := 'S_CHG_METHOD_TYPE';
497 cst_rul_sequence_number VARCHAR2(30) := 'RUL_SEQUENCE_NUMBER';
498 CURSOR c_ftcih IS
499 SELECT DECODE (p_column_name,
500 cst_fee_type_ci_status, ftcih.fee_type_ci_status,
501 cst_start_dt_alias, ftcih.start_dt_alias,
502 cst_start_dai_sequence_number, TO_CHAR(ftcih.start_dai_sequence_number),
503 cst_end_dt_alias, ftcih.end_dt_alias,
504 cst_end_dai_sequence_number, TO_CHAR(ftcih.end_dai_sequence_number),
505 cst_retro_dt_alias, ftcih.retro_dt_alias,
506 cst_retro_dai_sequence_number, TO_CHAR(ftcih.retro_dai_sequence_number),
507 cst_s_chg_method_type, ftcih.s_chg_method_type,
508 cst_rul_sequence_number, TO_CHAR(ftcih.rul_sequence_number))
509 FROM igs_fi_fee_type_ci_h ftcih
510 WHERE ftcih.fee_type = p_fee_type AND
511 ftcih.fee_cal_type = p_fee_cal_type AND
512 ftcih.fee_ci_sequence_number = p_fee_ci_sequence_number AND
513 ftcih.hist_start_dt >= p_hist_end_dt AND
514 decode (p_column_name,
515 cst_start_dt_alias, ftcih.start_dt_alias,
516 cst_start_dai_sequence_number, ftcih.start_dai_sequence_number,
517 cst_end_dt_alias, ftcih.end_dt_alias,
518 cst_end_dai_sequence_number, ftcih.end_dai_sequence_number,
519 cst_retro_dt_alias, ftcih.retro_dt_alias,
520 cst_retro_dai_sequence_number, ftcih.retro_dai_sequence_number,
521 cst_s_chg_method_type, ftcih.s_chg_method_type,
522 cst_rul_sequence_number, ftcih.rul_sequence_number) IS NOT NULL
523 ORDER BY ftcih.hist_start_dt;
524 v_column_value VARCHAR2(2000) := NULL;
525 BEGIN
526 OPEN c_ftcih;
527 FETCH c_ftcih INTO v_column_value;
528 CLOSE c_ftcih;
529 RETURN v_column_value;
530 EXCEPTION
531 WHEN OTHERS THEN
532 IF (c_ftcih%isopen) THEN
533 CLOSE c_ftcih;
534 END IF;
535 RAISE;
536 END;
537 END audp_get_ftcih_col;
538
539 END igs_au_gen_002;