[Home] [Help]
PACKAGE BODY: APPS.IGS_AU_GEN_001
Source
1 PACKAGE BODY IGS_AU_GEN_001 AS
2 /* $Header: IGSAU01B.pls 115.7 2003/12/03 20:48:58 knag ship $ */
3 Function Audp_Get_Aah_Col(
4 p_column_name IN user_tab_columns.column_name%TYPE ,
5 p_person_id IN IGS_AD_APPL_HIST_ALL.person_id%TYPE ,
6 p_admission_appl_number IN NUMBER ,
7 p_hist_end_dt IN IGS_AD_APPL_HIST_ALL.hist_end_dt%TYPE )
8 RETURN VARCHAR2 AS
9
10 BEGIN -- audp_get_aah_col
11 -- get the oldest column value (after a given date) for a
12 -- specified column and person_id, admission_appl_number
13 -- for IGS_AD_APPL_HIST table
14 DECLARE
15 cst_appl_dt CONSTANT VARCHAR2(7) := 'APPL_DT';
16 cst_acad_cal_type CONSTANT VARCHAR2(13) := 'ACAD_CAL_TYPE';
17 cst_acad_ci_sequence_number CONSTANT VARCHAR2(23) := 'ACAD_CI_SEQUENCE_NUMBER';
18 cst_adm_cal_type CONSTANT VARCHAR2(12) := 'ADM_CAL_TYPE';
19 cst_adm_ci_sequence_number CONSTANT VARCHAR2(22) := 'ADM_CI_SEQUENCE_NUMBER';
20 cst_admission_cat CONSTANT VARCHAR2(13) := 'ADMISSION_CAT';
21 cst_s_admission_process_type CONSTANT
22 VARCHAR2(24) := 'S_ADMISSION_PROCESS_TYPE';
23 cst_adm_appl_status CONSTANT VARCHAR2(15) := 'ADM_APPL_STATUS';
24 cst_adm_fee_status CONSTANT VARCHAR2(14) := 'ADM_FEE_STATUS';
25 cst_tac_appl_ind CONSTANT VARCHAR2(12) := 'TAC_APPL_IND';
26 CURSOR c_aah IS
27 SELECT
28 DECODE (
29 p_column_name,
30 cst_appl_dt, IGS_GE_DATE.igschar(aah.appl_dt),
31 cst_acad_cal_type, aah.acad_cal_type,
32 cst_acad_ci_sequence_number, TO_CHAR(aah.acad_ci_sequence_number),
33 cst_adm_cal_type, aah.adm_cal_type,
34 cst_adm_ci_sequence_number, TO_CHAR(aah.adm_ci_sequence_number),
35 cst_admission_cat, aah.ADMISSION_CAT,
36 cst_s_admission_process_type, aah.s_admission_process_type,
37 cst_adm_appl_status, aah.ADM_APPL_STATUS,
38 cst_adm_fee_status, aah.ADM_FEE_STATUS,
39 cst_tac_appl_ind, aah.tac_appl_ind)
40 FROM IGS_AD_APPL_HIST aah
41 WHERE aah.person_id = p_person_id AND
42 aah.admission_appl_number = p_admission_appl_number AND
43 aah.hist_start_dt >= p_hist_end_dt AND
44 DECODE (
45 p_column_name,
46 cst_appl_dt, IGS_GE_DATE.igschar(aah.appl_dt),
47 cst_acad_cal_type, aah.acad_cal_type,
48 cst_acad_ci_sequence_number, TO_CHAR(aah.acad_ci_sequence_number),
49 cst_adm_cal_type, aah.adm_cal_type,
50 cst_adm_ci_sequence_number, TO_CHAR(aah.adm_ci_sequence_number),
51 cst_admission_cat, aah.ADMISSION_CAT,
52 cst_s_admission_process_type, aah.s_admission_process_type,
53 cst_adm_appl_status, aah.ADM_APPL_STATUS,
54 cst_adm_fee_status, aah.ADM_FEE_STATUS,
55 cst_tac_appl_ind, aah.tac_appl_ind) IS NOT NULL
56 ORDER BY
57 aah.hist_start_dt;
58 v_column_value VARCHAR2(2000) := NULL;
59 BEGIN
60 OPEN c_aah;
61 FETCH c_aah INTO v_column_value;
62 CLOSE c_aah;
63 RETURN v_column_value;
64 EXCEPTION
65 WHEN OTHERS THEN
66 IF c_aah%ISOPEN THEN
67 CLOSE c_aah;
68 END IF;
69 RAISE;
70 END;
71 END audp_get_aah_col;
72
73 Function Audp_Get_Acah_Col(
74 p_column_name IN user_tab_columns.column_name%TYPE ,
75 p_person_id IN IGS_AD_PS_APPL_HIST_ALL.person_id%TYPE ,
76 p_admission_appl_number IN NUMBER ,
77 p_nominated_course_cd IN VARCHAR2 ,
78 p_hist_end_dt IN IGS_AD_PS_APPL_HIST_ALL.hist_end_dt%TYPE )
79 RETURN VARCHAR2 AS
80
81 BEGIN -- audp_get_acah_col
82 -- get the oldest column value (after a given date) for a
83 -- specified column and admission_cd, nominated_course_cd for
84 -- IGS_AD_PS_APPL_HIST table
85 DECLARE
86 cst_transfer_course_cd CONSTANT VARCHAR2(18) := 'TRANSFER_COURSE_CD';
87 cst_basis_for_admission_type CONSTANT
88 VARCHAR2(24) := 'BASIS_FOR_ADMISSION_TYPE';
89 cst_admission_cd CONSTANT VARCHAR2(12) := 'ADMISSION_CD';
90 cst_course_rank_set CONSTANT VARCHAR2(15) := 'COURSE_RANK_SET';
91 cst_course_rank_schedule CONSTANT VARCHAR2(20) := 'COURSE_RANK_SCHEDULE';
92 cst_req_for_reconsideratn_ind CONSTANT
93 VARCHAR2(27) :='REQ_FOR_RECONSIDERATION_IND';
94 cst_req_for_adv_standing_ind CONSTANT
95 VARCHAR2(24) := 'REQ_FOR_ADV_STANDING_IND';
96 CURSOR c_acah IS
97 SELECT
98 DECODE (
99 p_column_name,
100 cst_transfer_course_cd, acah.transfer_course_cd,
101 cst_basis_for_admission_type, acah.basis_for_admission_type,
102 cst_admission_cd, acah.ADMISSION_CD,
103 cst_course_rank_set, acah.course_rank_set,
104 cst_course_rank_schedule, acah.course_rank_schedule,
105 cst_req_for_reconsideratn_ind, acah.req_for_reconsideration_ind,
106 cst_req_for_adv_standing_ind, acah.req_for_adv_standing_ind)
107 FROM IGS_AD_PS_APPL_HIST acah
108 WHERE acah.person_id = p_person_id AND
109 acah.admission_appl_number = p_admission_appl_number AND
110 acah.nominated_course_cd = p_nominated_course_cd AND
111 acah.hist_start_dt >= p_hist_end_dt AND
112 DECODE (
113 p_column_name,
114 cst_transfer_course_cd, acah.transfer_course_cd,
115 cst_basis_for_admission_type, acah.basis_for_admission_type,
116 cst_admission_cd, acah.ADMISSION_CD,
117 cst_course_rank_set, acah.course_rank_set,
118 cst_course_rank_schedule, acah.course_rank_schedule,
119 cst_req_for_reconsideratn_ind, acah.req_for_reconsideration_ind,
120 cst_req_for_adv_standing_ind, acah.req_for_adv_standing_ind) IS NOT NULL
121 ORDER BY
122 acah.hist_start_dt;
123 v_column_value VARCHAR2(2000) := NULL;
124 BEGIN
125 OPEN c_acah;
126 FETCH c_acah INTO v_column_value;
127 CLOSE c_acah;
128 RETURN v_column_value;
129 EXCEPTION
130 WHEN OTHERS THEN
131 IF c_acah%ISOPEN THEN
132 CLOSE c_acah;
133 END IF;
134 RAISE;
135 END;
136 END audp_get_acah_col;
137
138 Function Audp_Get_Acaih_Col(
139 p_column_name IN user_tab_columns.column_name%TYPE ,
140 p_person_id IN NUMBER ,
141 p_admission_appl_number IN NUMBER ,
142 p_nominated_course_cd IN VARCHAR2 ,
143 p_sequence_number IN NUMBER ,
144 p_hist_end_dt IN DATE )
145 RETURN VARCHAR2 AS
146
147 BEGIN -- audp_get_acaih_col
148 -- get the oldest column value (after a given date) for a
149 -- specified column and person_id, admission_appl_number,
150 -- nominated_course_cd and sequence_number for
151 -- IGS_AD_PS_APLINSTHST table
152 DECLARE
153 cst_hist_offer_round_number CONSTANT
154 VARCHAR2(23) := 'HIST_OFFER_ROUND_NUMBER';
155 cst_adm_cal_type CONSTANT VARCHAR2(12) := 'ADM_CAL_TYPE';
156 cst_adm_ci_sequence_number CONSTANT VARCHAR2(22) := 'ADM_CI_SEQUENCE_NUMBER';
157 cst_course_cd CONSTANT VARCHAR2(9) := 'COURSE_CD';
158 cst_crv_version_number CONSTANT VARCHAR2(18) := 'CRV_VERSION_NUMBER';
159 cst_location_cd CONSTANT VARCHAR2(11) := 'LOCATION_CD';
160 cst_attendance_mode CONSTANT VARCHAR2(15) := 'ATTENDANCE_MODE';
161 cst_attendance_type CONSTANT VARCHAR2(15) := 'ATTENDANCE_TYPE';
162 cst_unit_set_cd CONSTANT VARCHAR2(11) := 'UNIT_SET_CD';
163 cst_us_version_number CONSTANT VARCHAR2(17) := 'US_VERSION_NUMBER';
164 cst_preference_number CONSTANT VARCHAR2(17) := 'PREFERENCE_NUMBER';
165 cst_adm_doc_status CONSTANT VARCHAR2(14) := 'ADM_DOC_STATUS';
166 cst_adm_entry_qual_status CONSTANT VARCHAR2(21) := 'ADM_ENTRY_QUAL_STATUS';
167 cst_late_adm_fee_status CONSTANT VARCHAR2(19) := 'LATE_ADM_FEE_STATUS';
168 cst_adm_outcome_status CONSTANT VARCHAR2(18) := 'ADM_OUTCOME_STATUS';
169 cst_adm_otcm_stus_auth_prsn_id CONSTANT
170 VARCHAR2(30) := 'ADM_OTCM_STATUS_AUTH_PERSON_ID';
171 cst_adm_outcome_status_auth_dt CONSTANT
172 VARCHAR2(26) := 'ADM_OUTCOME_STATUS_AUTH_DT';
173 cst_adm_outcome_status_reason CONSTANT
174 VARCHAR2(25) := 'ADM_OUTCOME_STATUS_REASON';
175 cst_offer_dt CONSTANT VARCHAR2(8) := 'OFFER_DT';
176 cst_offer_response_dt CONSTANT VARCHAR2(17) := 'OFFER_RESPONSE_DT';
177 cst_prpsd_commencement_dt CONSTANT VARCHAR2(30) := 'PRPSD_COMMENCEMENT_DT';
178 cst_adm_cndtnl_offer_status CONSTANT
179 VARCHAR2(23) := 'ADM_CNDTNL_OFFER_STATUS';
180 cst_cndtnl_offer_satisfied_dt CONSTANT
181 VARCHAR2(25) := 'CNDTNL_OFFER_SATISFIED_DT';
182 cst_cndtnl_offer_must_be_stsfd CONSTANT
183 VARCHAR2(30) := 'CNDTNL_OFFER_MUST_BE_STSFD_IND';
184 cst_adm_offer_resp_status CONSTANT VARCHAR2(21) := 'ADM_OFFER_RESP_STATUS';
185 cst_actual_response_dt CONSTANT VARCHAR2(18) := 'ACTUAL_RESPONSE_DT';
186 cst_adm_offer_dfrmnt_status CONSTANT
187 VARCHAR2(23) := 'ADM_OFFER_DFRMNT_STATUS';
188 cst_deferred_adm_cal_type CONSTANT
189 VARCHAR2(21) := 'DEFERRED_ADM_CAL_TYPE';
190 cst_defer_adm_ci_sequnc_num CONSTANT
191 VARCHAR2(28) := 'DEFERRED_ADM_CI_SEQUENCE_NUM';
192 cst_deferred_tracking_id CONSTANT
193 VARCHAR2(20) := 'DEFERRED_TRACKING_ID';
194 cst_ass_rank CONSTANT VARCHAR2(8) := 'ASS_RANK';
195 cst_secondary_ass_rank CONSTANT VARCHAR2(18) := 'SECONDARY_ASS_RANK';
196 cst_intrnnl_accptnce_adv_num CONSTANT
197 VARCHAR2(30) := 'INTRNTNL_ACCEPTANCE_ADVICE_NUM';
198 cst_ass_tracking_id CONSTANT VARCHAR2(15) := 'ASS_TRACKING_ID';
199 cst_fee_cat CONSTANT VARCHAR2(7) := 'FEE_CAT';
200 cst_hecs_payment_option CONSTANT VARCHAR2(19) := 'HECS_PAYMENT_OPTION';
201 cst_expected_completion_yr CONSTANT VARCHAR2(22) := 'EXPECTED_COMPLETION_YR';
202 cst_expected_completion_perd CONSTANT
203 VARCHAR2(24) := 'EXPECTED_COMPLETION_PERD';
204 cst_correspondence_cat CONSTANT VARCHAR2(18) := 'CORRESPONDENCE_CAT';
205 cst_enrolment_cat CONSTANT VARCHAR2(13) := 'ENROLMENT_CAT';
206 cst_funding_source CONSTANT VARCHAR2(15) := 'FUNDING_SOURCE';
207 cst_applicant_acptnce_cndtn CONSTANT
208 VARCHAR2(23) := 'APPLICANT_ACPTNCE_CNDTN';
209 cst_cndtnl_offer_cndtn CONSTANT VARCHAR2(18) := 'CNDTNL_OFFER_CNDTN';
210 CURSOR c_acaih IS
211 SELECT
212 DECODE (
213 p_column_name,
214 cst_hist_offer_round_number, TO_CHAR(acaih.hist_offer_round_number),
215 cst_adm_cal_type, acaih.adm_cal_type,
216 cst_adm_ci_sequence_number, TO_CHAR(acaih.adm_ci_sequence_number),
217 cst_course_cd, acaih.course_cd,
218 cst_crv_version_number, TO_CHAR(acaih.crv_version_number),
219 cst_location_cd, acaih.location_cd,
220 cst_attendance_mode, acaih.ATTENDANCE_MODE,
221 cst_attendance_type, acaih.ATTENDANCE_TYPE,
222 cst_unit_set_cd, acaih.unit_set_cd,
223 cst_us_version_number, TO_CHAR(acaih.us_version_number),
224 cst_preference_number, TO_CHAR(acaih.preference_number),
225 cst_adm_doc_status, acaih.ADM_DOC_STATUS,
226 cst_adm_entry_qual_status, acaih.ADM_ENTRY_QUAL_STATUS,
227 cst_late_adm_fee_status, acaih.late_adm_fee_status,
228 cst_adm_outcome_status, acaih.ADM_OUTCOME_STATUS,
229 cst_adm_otcm_stus_auth_prsn_id,
230 TO_CHAR(acaih.adm_otcm_status_auth_person_id),
231 cst_adm_outcome_status_auth_dt,
232 IGS_GE_DATE.igschar(acaih.adm_outcome_status_auth_dt),
233 cst_adm_outcome_status_reason, acaih.adm_outcome_status_reason,
234 cst_offer_dt, IGS_GE_DATE.igschar(acaih.offer_dt),
235 cst_offer_response_dt, IGS_GE_DATE.igschar(acaih.offer_response_dt),
236 cst_prpsd_commencement_dt,
237 IGS_GE_DATE.igschar(acaih.prpsd_commencement_dt),
238 cst_adm_cndtnl_offer_status, acaih.ADM_CNDTNL_OFFER_STATUS,
239 cst_cndtnl_offer_satisfied_dt,
240 IGS_GE_DATE.igschar(acaih.cndtnl_offer_satisfied_dt),
241 cst_cndtnl_offer_must_be_stsfd, acaih.cndtnl_offer_must_be_stsfd_ind,
242 cst_adm_offer_resp_status, acaih.ADM_OFFER_RESP_STATUS,
243 cst_actual_response_dt, IGS_GE_DATE.igschar(acaih.actual_response_dt),
244 cst_adm_offer_dfrmnt_status, acaih.ADM_OFFER_DFRMNT_STATUS,
245 cst_deferred_adm_cal_type, acaih.deferred_adm_cal_type,
246 cst_defer_adm_ci_sequnc_num, TO_CHAR(acaih.deferred_adm_ci_sequence_num),
247 cst_deferred_tracking_id, TO_CHAR(acaih.deferred_tracking_id),
248 cst_ass_rank, TO_CHAR(acaih.ass_rank),
249 cst_secondary_ass_rank, TO_CHAR(acaih.secondary_ass_rank),
250 cst_intrnnl_accptnce_adv_num,
251 TO_CHAR(acaih.intrntnl_acceptance_advice_num),
252 cst_ass_tracking_id, TO_CHAR(acaih.ass_tracking_id),
253 cst_fee_cat, acaih.FEE_CAT,
254 cst_hecs_payment_option, acaih.HECS_PAYMENT_OPTION,
255 cst_expected_completion_yr, TO_CHAR(acaih.expected_completion_yr),
256 cst_expected_completion_perd, acaih.expected_completion_perd,
257 cst_correspondence_cat, acaih.CORRESPONDENCE_CAT,
258 cst_enrolment_cat, acaih.ENROLMENT_CAT,
259 cst_funding_source, acaih.FUNDING_SOURCE,
260 cst_applicant_acptnce_cndtn, acaih.applicant_acptnce_cndtn,
261 cst_cndtnl_offer_cndtn, acaih.cndtnl_offer_cndtn)
262 FROM IGS_AD_PS_APLINSTHST acaih
263 WHERE acaih.person_id = p_person_id AND
264 acaih.admission_appl_number = p_admission_appl_number AND
265 acaih.nominated_course_cd = p_nominated_course_cd AND
266 acaih.sequence_number = p_sequence_number AND
267 acaih.hist_start_dt >= p_hist_end_dt AND
268 DECODE (
269 p_column_name,
270 cst_hist_offer_round_number, TO_CHAR(acaih.hist_offer_round_number),
271 cst_adm_cal_type, acaih.adm_cal_type,
272 cst_adm_ci_sequence_number, TO_CHAR(acaih.adm_ci_sequence_number),
273 cst_course_cd, acaih.course_cd,
274 cst_crv_version_number, TO_CHAR(acaih.crv_version_number),
275 cst_location_cd, acaih.location_cd,
276 cst_attendance_mode, acaih.ATTENDANCE_MODE,
277 cst_attendance_type, acaih.ATTENDANCE_TYPE,
278 cst_unit_set_cd, acaih.unit_set_cd,
279 cst_us_version_number, TO_CHAR(acaih.us_version_number),
280 cst_preference_number, TO_CHAR(acaih.preference_number),
281 cst_adm_doc_status, acaih.ADM_DOC_STATUS,
282 cst_adm_entry_qual_status, acaih.ADM_ENTRY_QUAL_STATUS,
283 cst_late_adm_fee_status, acaih.late_adm_fee_status,
284 cst_adm_outcome_status, acaih.ADM_OUTCOME_STATUS,
285 cst_adm_otcm_stus_auth_prsn_id,
286 TO_CHAR(acaih.adm_otcm_status_auth_person_id),
287 cst_adm_outcome_status_auth_dt,
288 IGS_GE_DATE.igschar(acaih.adm_outcome_status_auth_dt),
289 cst_adm_outcome_status_reason, acaih.adm_outcome_status_reason,
290 cst_offer_dt, IGS_GE_DATE.igschar(acaih.offer_dt),
291 cst_offer_response_dt, IGS_GE_DATE.igschar(acaih.offer_response_dt),
292 cst_prpsd_commencement_dt,
293 IGS_GE_DATE.igschar(acaih.prpsd_commencement_dt),
294 cst_adm_cndtnl_offer_status, acaih.ADM_CNDTNL_OFFER_STATUS,
295 cst_cndtnl_offer_satisfied_dt,
296 IGS_GE_DATE.igschar(acaih.cndtnl_offer_satisfied_dt),
297 cst_cndtnl_offer_must_be_stsfd, acaih.cndtnl_offer_must_be_stsfd_ind,
298 cst_adm_offer_resp_status, acaih.ADM_OFFER_RESP_STATUS,
299 cst_actual_response_dt, IGS_GE_DATE.igschar(acaih.actual_response_dt),
300 cst_adm_offer_dfrmnt_status, acaih.ADM_OFFER_DFRMNT_STATUS,
301 cst_deferred_adm_cal_type, acaih.deferred_adm_cal_type,
302 cst_defer_adm_ci_sequnc_num, TO_CHAR(acaih.deferred_adm_ci_sequence_num),
303 cst_deferred_tracking_id, TO_CHAR(acaih.deferred_tracking_id),
304 cst_ass_rank, TO_CHAR(acaih.ass_rank),
305 cst_secondary_ass_rank, TO_CHAR(acaih.secondary_ass_rank),
306 cst_intrnnl_accptnce_adv_num, TO_CHAR(acaih.intrntnl_acceptance_advice_num),
307 cst_ass_tracking_id, TO_CHAR(acaih.ass_tracking_id),
308 cst_fee_cat, acaih.FEE_CAT,
309 cst_hecs_payment_option, acaih.HECS_PAYMENT_OPTION,
310 cst_expected_completion_yr, TO_CHAR(acaih.expected_completion_yr),
311 cst_expected_completion_perd, acaih.expected_completion_perd,
312 cst_correspondence_cat, acaih.CORRESPONDENCE_CAT,
313 cst_enrolment_cat, acaih.ENROLMENT_CAT,
314 cst_funding_source, acaih.FUNDING_SOURCE,
315 cst_applicant_acptnce_cndtn, acaih.applicant_acptnce_cndtn,
316 cst_cndtnl_offer_cndtn, acaih.cndtnl_offer_cndtn) IS NOT NULL
317 ORDER BY
318 acaih.hist_start_dt;
319 v_column_value VARCHAR2(2000) := NULL;
320 BEGIN
321 OPEN c_acaih;
322 FETCH c_acaih INTO v_column_value;
323 CLOSE c_acaih;
324 RETURN v_column_value;
325 EXCEPTION
326 WHEN OTHERS THEN
327 IF c_acaih%ISOPEN THEN
328 CLOSE c_acaih;
329 END IF;
330 RAISE;
331 END;
332 END audp_get_acaih_col;
333
334 Function Audp_Get_Acaiuh_Col(
335 p_column_name IN user_tab_columns.column_name%TYPE ,
336 p_adm_ps_appl_inst_unit_id IN NUMBER ,
337 p_person_id IN NUMBER ,
338 p_admission_appl_number IN NUMBER ,
339 p_nominated_course_cd IN VARCHAR2 ,
343 RETURN VARCHAR2 AS
340 p_acai_sequence_number IN NUMBER ,
341 p_unit_cd IN IGS_AD_PS_APINTUNTHS_ALL.unit_cd%TYPE ,
342 p_hist_end_dt IN DATE )
344
345 BEGIN
346 DECLARE
347 cst_uv_version_number CONSTANT
348 user_tab_columns.column_name%TYPE := 'UV_VERSION_NUMBER';
349 cst_cal_type CONSTANT user_tab_columns.column_name%TYPE := 'CAL_TYPE';
350 cst_ci_sequence_number CONSTANT
351 user_tab_columns.column_name%TYPE := 'CI_SEQUENCE_NUMBER';
352 cst_location_cd CONSTANT user_tab_columns.column_name%TYPE := 'LOCATION_CD';
353 cst_unit_class CONSTANT user_tab_columns.column_name%TYPE := 'UNIT_CLASS';
354 cst_unit_mode CONSTANT user_tab_columns.column_name%TYPE := 'UNIT_MODE';
355 cst_adm_unit_outcome_status CONSTANT
356 user_tab_columns.column_name%TYPE := 'ADM_UNIT_OUTCOME_STATUS';
357 cst_ass_tracking_id CONSTANT
358 user_tab_columns.column_name%TYPE := 'ASS_TRACKING_ID';
359 cst_rule_waived_dt CONSTANT
360 user_tab_columns.column_name%TYPE := 'RULE_WAIVED_DT';
361 cst_rule_waived_person_id CONSTANT
362 user_tab_columns.column_name%TYPE := 'RULE_WAIVED_PERSON_ID';
363 cst_sup_unit_cd CONSTANT user_tab_columns.column_name%TYPE := 'SUP_UNIT_CD';
364 cst_sup_uv_version_number CONSTANT
365 user_tab_columns.column_name%TYPE := 'SUP_UV_VERSION_NUMBER';
366 v_column_value VARCHAR2(2000) := NULL;
367 CURSOR c_acaiuh IS
368 SELECT DECODE(p_column_name,
369 cst_uv_version_number, TO_CHAR(acaiuh.uv_version_number),
370 cst_cal_type, acaiuh.CAL_TYPE,
371 cst_ci_sequence_number, TO_CHAR(acaiuh.ci_sequence_number),
372 cst_location_cd, acaiuh.location_cd,
373 cst_unit_class, acaiuh.UNIT_CLASS,
374 cst_unit_mode, acaiuh.UNIT_MODE,
375 cst_adm_unit_outcome_status, acaiuh.ADM_UNIT_OUTCOME_STATUS,
376 cst_ass_tracking_id, TO_CHAR(acaiuh.ass_tracking_id),
377 cst_rule_waived_dt,
378 IGS_GE_DATE.igscharDT(acaiuh.rule_waived_dt),
379 cst_rule_waived_person_id, TO_CHAR(acaiuh.rule_waived_person_id),
380 cst_sup_unit_cd, acaiuh.sup_unit_cd,
381 cst_sup_uv_version_number, TO_CHAR(acaiuh.sup_uv_version_number))
382 FROM IGS_AD_PS_APINTUNTHS acaiuh
383 WHERE acaiuh.adm_ps_appl_inst_unit_id = p_adm_ps_appl_inst_unit_id AND
384 /*********************** 3083148 ***********************
385 acaiuh.person_id = p_person_id AND
386 acaiuh.admission_appl_number = p_admission_appl_number AND
387 acaiuh.nominated_course_cd = p_nominated_course_cd AND
388 acaiuh.acai_sequence_number = p_acai_sequence_number AND
389 acaiuh.unit_cd = p_unit_cd AND
390 *********************** 3083148 ***********************/
391 acaiuh.hist_start_dt >= p_hist_end_dt AND
392 DECODE(p_column_name,
393 cst_uv_version_number, TO_CHAR(acaiuh.uv_version_number),
394 cst_cal_type, acaiuh.CAL_TYPE,
395 cst_ci_sequence_number, TO_CHAR(acaiuh.ci_sequence_number),
396 cst_location_cd, acaiuh.location_cd,
397 cst_unit_class, acaiuh.UNIT_CLASS,
398 cst_unit_mode, acaiuh.UNIT_MODE,
399 cst_adm_unit_outcome_status, acaiuh.ADM_UNIT_OUTCOME_STATUS,
400 cst_ass_tracking_id, TO_CHAR(acaiuh.ass_tracking_id),
401 cst_rule_waived_dt,
402 IGS_GE_DATE.igscharDT(acaiuh.rule_waived_dt),
403 cst_rule_waived_person_id, TO_CHAR(acaiuh.rule_waived_person_id),
404 cst_sup_unit_cd, acaiuh.sup_unit_cd,
405 cst_sup_uv_version_number, TO_CHAR(acaiuh.sup_uv_version_number))
406 IS NOT NULL
407 ORDER BY acaiuh.hist_start_dt;
408 BEGIN
409 OPEN c_acaiuh;
410 FETCH c_acaiuh INTO v_column_value;
411 CLOSE c_acaiuh;
412 RETURN v_column_value;
413 EXCEPTION
414 WHEN OTHERS THEN
415 IF (c_acaiuh%ISOPEN) THEN
416 CLOSE c_acaiuh;
417 END IF;
418 RAISE;
419 END;
420 END audp_get_acaiuh_col;
421
422 Function Audp_Get_Cfosh_Col(
423 p_course_cd IGS_PS_FLD_STD_HIST_ALL.course_cd%TYPE ,
424 p_version_number IGS_PS_FLD_STD_HIST_ALL.version_number%TYPE ,
425 p_field_of_study IGS_PS_FLD_STD_HIST_ALL.field_of_study%TYPE ,
426 p_column_name user_tab_columns.column_name%TYPE ,
427 p_hist_date IGS_PS_FLD_STD_HIST_ALL.hist_start_dt%TYPE )
428 RETURN VARCHAR2 AS
429
430 BEGIN -- audp_get_cfosh_col
431 -- get the column_value (after a given date) for a given
432 -- column_name, course_cd, version_number and field_of_study
433 DECLARE
434 v_column_value VARCHAR2(2000) := NULL;
435 cst_percentage VARCHAR2(30) := 'PERCENTAGE';
436 cst_major_field_ind VARCHAR2(30) := 'MAJOR_FIELD_IND';
437 CURSOR c_cfosh IS
438 SELECT DECODE (p_column_name,
439 cst_percentage, TO_CHAR(cfosh.percentage),
440 cst_major_field_ind, cfosh.major_field_ind)
441 FROM IGS_PS_FLD_STD_HIST cfosh
442 WHERE cfosh.course_cd = p_course_cd AND
443 cfosh.version_number = p_version_number AND
444 cfosh.field_of_study = p_field_of_study AND
445 cfosh.hist_start_dt >= p_hist_date AND
446 DECODE (p_column_name,
447 cst_percentage, TO_CHAR(cfosh.percentage),
448 cst_major_field_ind, cfosh.major_field_ind) IS NOT NULL
449 ORDER BY cfosh.hist_start_dt;
450 BEGIN
451 OPEN c_cfosh;
452 FETCH c_cfosh INTO v_column_value;
453 CLOSE c_cfosh;
454 RETURN v_column_value;
455 EXCEPTION
456 WHEN OTHERS THEN
457 IF (c_cfosh%ISOPEN) THEN
458 CLOSE c_cfosh;
459 END IF;
460 RAISE;
461 END;
462 END audp_get_cfosh_col;
463
464 Function Audp_Get_Coh_Col(
465 p_course_cd IGS_PS_OWN_HIST_ALL.course_cd%TYPE ,
466 p_version_number IGS_PS_OWN_HIST_ALL.version_number%TYPE ,
467 p_org_unit_cd IGS_PS_OWN_HIST_ALL.org_unit_cd%TYPE ,
468 p_ou_start_dt IGS_PS_OWN_HIST_ALL.ou_start_dt%TYPE ,
469 p_hist_date IGS_PS_OWN_HIST_ALL.hist_start_dt%TYPE )
470 RETURN NUMBER AS
471
472 BEGIN -- audp_get_coh_col
473 -- get the oldest column value (after a given date) for the percentage
474 -- column for a given course_cd, version_number, org_unit_cd and
475 -- ou_start_dt
476 DECLARE
477 v_column_value IGS_PS_OWN_HIST.percentage%TYPE := NULL;
478 CURSOR c_coh IS
479 SELECT coh.percentage
480 FROM IGS_PS_OWN_HIST coh
481 WHERE coh.course_cd = p_course_cd AND
482 coh.version_number = p_version_number AND
483 coh.org_unit_cd = p_org_unit_cd AND
484 coh.ou_start_dt = p_ou_start_dt AND
485 coh.hist_start_dt >= p_hist_date AND
486 coh.percentage IS NOT NULL
487 ORDER BY coh.hist_start_dt;
488 BEGIN
489 OPEN c_coh;
490 FETCH c_coh INTO v_column_value;
491 CLOSE c_coh;
492 RETURN v_column_value;
493 EXCEPTION
494 WHEN OTHERS THEN
495 IF (c_coh%ISOPEN) THEN
496 CLOSE c_coh;
497 END IF;
498 RAISE;
499 END;
500 END audp_get_coh_col;
501
502 Function Audp_Get_Crch_Col(
503 p_course_cd IGS_PS_REF_CD_HIST_ALL.course_cd%TYPE ,
504 p_version_number IGS_PS_REF_CD_HIST_ALL.version_number%TYPE ,
505 p_reference_cd_type IGS_PS_REF_CD_HIST_ALL.reference_cd_type%TYPE ,
506 p_reference_cd IGS_PS_REF_CD_HIST_ALL.reference_cd%TYPE ,
507 p_hist_date IGS_PS_REF_CD_HIST_ALL.hist_start_dt%TYPE )
508 RETURN VARCHAR2 AS
509
510 BEGIN -- audp_get_crch_col
511 -- get the oldest column value (after a given date) for the descritpion
512 -- column for a given course_cd, version_number, reference_cd_type and
513 -- reference_cd
514 DECLARE
515 v_column_value VARCHAR2(2000) := NULL;
516 CURSOR c_crch IS
517 SELECT crch.description
518 FROM IGS_PS_REF_CD_HIST crch
519 WHERE crch.course_cd = p_course_cd AND
520 crch.version_number = p_version_number AND
521 crch.reference_cd_type = p_reference_cd_type AND
522 crch.reference_cd = p_reference_cd AND
523 crch.hist_start_dt >= p_hist_date AND
524 crch.description IS NOT NULL
525 ORDER BY crch.hist_start_dt;
526 BEGIN
527 OPEN c_crch;
528 FETCH c_crch INTO v_column_value;
529 CLOSE c_crch;
530 RETURN v_column_value;
531 EXCEPTION
532 WHEN OTHERS THEN
533 IF (c_crch%ISOPEN) THEN
534 CLOSE c_crch;
535 END IF;
536 RAISE;
537 END;
538 END audp_get_crch_col;
539
540 Function Audp_Get_Cth_Col(
541 p_column_name user_tab_columns.column_name%TYPE ,
542 p_course_type IGS_PS_TYPE_ALL.course_type%TYPE ,
543 p_hist_end_dt IGS_PS_TYPE_HIST_ALL.hist_end_dt%TYPE )
544 RETURN VARCHAR2 AS
545 -- HISTORY
546 -- WHO WHEN WHAT
547 -- sarakshi 3-Oct-2002 Enh#2603626,the cursor c_cth added column fin_aid_program_type in the decode
548 -- statement for both select and where clause
549
550 BEGIN -- audp_get_cth_col
551 -- get the oldest column value (after a given date) for a
552 -- specified column and course_type
553 -- added the primary_auto_select column as part of Career impact dld, bug 2027984,pmarada
554 DECLARE
555 CURSOR c_cth IS
556 SELECT
557 DECODE (p_column_name,
558 'DESCRIPTION', cth.description,
559 'GOVT_COURSE_TYPE', TO_CHAR(cth.GOVT_COURSE_TYPE),
560 'COURSE_TYPE_GROUP_CD', cth.course_type_group_cd,
561 'TAC_COURSE_LEVEL', cth.tac_course_level,
562 'CLOSED_IND', cth.closed_ind,
563 'AWARD_COURSE_IND', cth.award_course_ind,
564 'RESEARCH_TYPE_IND', cth.research_type_ind,
565 'PRIMARY_AUTO_SELECT', cth.primary_auto_select,
566 'FIN_AID_PROGRAM_TYPE', cth.fin_aid_program_type)
567 FROM IGS_PS_TYPE_HIST cth
568 WHERE cth.course_type = p_course_type AND
569 cth.hist_start_dt >= p_hist_end_dt AND
570 DECODE (p_column_name,
571 'DESCRIPTION', cth.description,
572 'GOVT_COURSE_TYPE', TO_CHAR(cth.GOVT_COURSE_TYPE),
573 'COURSE_TYPE_GROUP_CD', cth.course_type_group_cd,
574 'TAC_COURSE_LEVEL', cth.tac_course_level,
575 'CLOSED_IND', cth.closed_ind,
576 'AWARD_COURSE_IND', cth.award_course_ind,
577 'RESEARCH_TYPE_IND', cth.research_type_ind,
578 'PRIMARY_AUTO_SELECT', cth.primary_auto_select,
579 'FIN_AID_PROGRAM_TYPE', cth.fin_aid_program_type) IS NOT NULL
580 ORDER BY
581 cth.hist_start_dt;
582 v_column_value VARCHAR2(2000) := NULL;
583 BEGIN
584 OPEN c_cth;
585 FETCH c_cth INTO v_column_value;
586 CLOSE c_cth;
587 RETURN v_column_value;
588 EXCEPTION
589 WHEN OTHERS THEN
590 IF c_cth%ISOPEN THEN
591 CLOSE c_cth;
592 END IF;
593 RAISE;
594 END;
595 END audp_get_cth_col;
596
597 END IGS_AU_GEN_001;