1 PACKAGE IGS_HE_EXTRACT_FIELDS_PKG AS
2 /* $Header: IGSHE9CS.pls 120.5 2006/09/15 01:48:52 jtmathew noship $ */
3
4 -- Structure to hold Cost Centre details
5 TYPE cc IS TABLE of igs_he_poous_ou_cc.cost_centre%TYPE
6 INDEX BY binary_integer;
7 TYPE subj IS TABLE of igs_he_poous_ou_cc.subject%TYPE
8 INDEX BY binary_integer;
9 TYPE prop IS TABLE of igs_he_poous_ou_cc.proportion%TYPE
10 INDEX BY binary_integer;
11
12 TYPE cc_rec IS RECORD
13 (cost_centre cc,
14 subject subj,
15 proportion prop);
16
17 -- Structure to hold Module details
18 TYPE unit IS TABLE of VARCHAR2(20)
19 INDEX BY binary_integer;
20 TYPE result IS TABLE of igs_he_code_map_val.map1%TYPE
21 INDEX BY binary_integer;
22 TYPE welsh_prop IS TABLE of igs_he_st_unt_vs.prop_of_teaching_in_welsh%TYPE
23 INDEX BY binary_integer;
24
25 TYPE mod_rec IS RECORD
26 (module_id unit,
27 module_result result,
28 prop_in_welsh welsh_prop);
29
30 -- Index Table to hold the award conferral dates for the submission
31 TYPE awd_defn IS RECORD
32 (type igs_he_submsn_awd.type%TYPE,
33 key1 igs_he_submsn_awd.key1%TYPE,
34 award_start_date igs_he_submsn_awd.award_start_date%TYPE,
35 award_end_date igs_he_submsn_awd.award_end_date%TYPE);
36
37 TYPE awd_table IS TABLE of awd_defn
38 INDEX BY binary_integer;
39
40 PROCEDURE get_hesa_inst_id
41 (p_hesa_inst_id OUT NOCOPY VARCHAR2);
42
43 PROCEDURE get_campus_id
44 (p_location_cd IN igs_en_stdnt_ps_att.location_cd%TYPE,
45 p_campus_id OUT NOCOPY VARCHAR2);
46
47 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
48 PROCEDURE get_alt_pers_id
49 (p_person_id IN igs_pe_person.person_id%TYPE,
50 p_id_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
51 p_api_id OUT NOCOPY VARCHAR2,
52 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
53 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
54
55 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
56 PROCEDURE get_stdnt_id
57 (p_person_id IN igs_en_stdnt_ps_att.person_id%TYPE,
58 p_inst_id IN igs_or_institution.govt_institution_cd%TYPE,
59 p_stdnt_id OUT NOCOPY VARCHAR2,
60 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
61 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
62
63 PROCEDURE get_fe_stdnt_mrker
64 (p_spa_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
65 p_fe_program_marker IN igs_he_st_prog.fe_program_marker%TYPE,
66 p_funding_src IN igs_he_ex_rn_dat_fd.value%TYPE,
67 p_fundability_cd IN igs_he_ex_rn_dat_fd.value%TYPE,
68 p_oss_fe_stdnt_mrker OUT NOCOPY VARCHAR2,
69 p_hesa_fe_stdnt_mrker OUT NOCOPY VARCHAR2);
70
71
72 PROCEDURE get_funding_src
73 (p_course_cd IN igs_ps_ver.course_cd%TYPE,
74 p_version_number IN igs_ps_ver.version_number%TYPE,
75 p_spa_fund_src IN igs_en_stdnt_ps_att.funding_source%TYPE,
76 p_poous_fund_src IN igs_he_poous.funding_source%TYPE,
77 p_oss_fund_src OUT NOCOPY VARCHAR2,
78 p_hesa_fund_src OUT NOCOPY VARCHAR2);
79
80 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
81 PROCEDURE get_fundability_cd
82 (p_person_id IN igs_pe_person.person_id%TYPE,
83 p_susa_fund_cd IN igs_he_en_susa.fundability_code%TYPE,
84 p_spa_funding_source IN igs_en_stdnt_ps_att.funding_source%TYPE,
85 p_poous_fund_cd IN igs_he_poous.fundability_cd%TYPE,
86 p_prg_fund_cd IN igs_he_st_prog.fundability%TYPE,
87 p_prg_funding_source IN igs_fi_fnd_src_rstn.funding_source%TYPE,
88 p_oss_fund_cd OUT NOCOPY VARCHAR2,
89 p_hesa_fund_cd OUT NOCOPY VARCHAR2,
90 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
91 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
92
93 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
94 PROCEDURE get_fmly_name_on_16_bday
95 (p_person_id IN igs_pe_person.person_id%TYPE,
96 p_fmly_name OUT NOCOPY VARCHAR2,
97 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
98 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
99
100 PROCEDURE get_gender
101 (p_gender IN igs_pe_person.sex%TYPE,
102 p_hesa_gender OUT NOCOPY VARCHAR2);
103
104 PROCEDURE get_domicile
105 (p_ad_domicile IN igs_he_ad_dtl.domicile_cd%TYPE,
106 p_spa_domicile IN igs_he_st_spa.domicile_cd%TYPE,
107 p_hesa_domicile OUT NOCOPY VARCHAR2);
108
109 -- smaddali 11-dec-03 Modified for bug#3235753 , added 1 new parameter
110 PROCEDURE get_nationality
111 (p_person_id IN igs_pe_person.person_id%TYPE,
112 p_nationality OUT NOCOPY VARCHAR2,
113 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL);
114
115 PROCEDURE get_ethnicity
116 (p_person_id IN igs_pe_person.person_id%TYPE,
117 p_oss_eth IN igs_pe_stat_v.ethnic_origin_id%TYPE,
118 p_hesa_eth OUT NOCOPY VARCHAR2);
119
120 PROCEDURE get_disablity_allow
121 (p_oss_dis_allow IN igs_he_en_susa.disability_allow%TYPE,
122 p_hesa_dis_allow OUT NOCOPY VARCHAR2);
123
124 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
125 PROCEDURE get_disablity
126 (p_person_id IN igs_pe_person.person_id%TYPE,
127 p_disability OUT NOCOPY VARCHAR2,
128 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
129 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
130
131 PROCEDURE get_addnl_supp_band
132 (p_oss_supp_band IN igs_he_en_susa.additional_sup_band%TYPE,
133 p_hesa_supp_band OUT NOCOPY VARCHAR2);
134
135 PROCEDURE get_yr_left_last_inst
136 (p_person_id IN igs_pe_person.person_id%TYPE,
137 p_com_dt IN DATE,
138 p_hesa_gen_qaim IN VARCHAR2,
139 p_ucasnum IN igs_pe_alt_pers_id.api_person_id%TYPE,
140 p_year OUT NOCOPY VARCHAR2);
141
142 PROCEDURE get_new_ent_to_he
143 (p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
144 p_susa_new_ent_to_he IN igs_he_en_susa.new_he_entrant_cd%TYPE,
145 p_yop IN VARCHAR2,
146 p_high_qual_on_ent IN igs_he_st_spa.highest_qual_on_entry%TYPE,
147 p_domicile IN igs_he_st_spa.domicile_cd%TYPE,
148 p_hesa_new_ent_to_he OUT NOCOPY VARCHAR2);
149
150 PROCEDURE get_year_of_prog
151 (p_unit_set_cd IN igs_he_en_susa.unit_set_cd%TYPE,
152 p_year_of_prog OUT NOCOPY VARCHAR2);
153
154 PROCEDURE get_special_student
155 (p_ad_special_student IN igs_he_ad_dtl.special_student_cd%TYPE,
156 p_spa_special_student IN igs_he_st_spa.special_student%TYPE,
157 p_oss_special_student OUT NOCOPY VARCHAR2,
158 p_hesa_special_student OUT NOCOPY VARCHAR2);
159
160 -- smaddali 29-oct-03 modified procedure get_year_of_student to add 1 new parameter for bug#3224246
161 -- jbaber 30-aug-04 - added new parameter p_susa_year_of_student for HEFD350
162 PROCEDURE get_year_of_student
163 (p_person_id IN igs_he_en_susa.person_id%TYPE,
164 p_course_cd IN igs_he_en_susa.course_cd%TYPE,
165 p_unit_set_cd IN igs_he_en_susa.unit_set_cd%TYPE,
166 p_sequence_number IN igs_he_en_susa.sequence_number%TYPE,
167 p_year_of_student OUT NOCOPY VARCHAR2,
168 p_enrl_end_dt IN DATE DEFAULT NULL,
169 p_susa_year_of_student IN igs_he_en_susa.year_stu%TYPE);
170
171
172 PROCEDURE get_study_location
173 (p_susa_study_location IN igs_he_en_susa.study_location%TYPE,
174 p_poous_study_location IN igs_he_poous.location_of_study%TYPE,
175 p_prg_study_location IN igs_he_st_prog.location_of_study%TYPE,
176 p_oss_study_location OUT NOCOPY VARCHAR2,
177 p_hesa_study_location OUT NOCOPY VARCHAR2);
178
179 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
180 PROCEDURE get_term_time_acc
181 (p_person_id IN igs_pe_person.person_id%TYPE,
182 p_susa_term_time_acc IN igs_he_en_susa.term_time_accom%TYPE,
183 p_study_location IN VARCHAR2,
184 p_hesa_term_time_acc OUT NOCOPY VARCHAR2,
185 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
186 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
187
188 PROCEDURE get_min_max_awd_dates
189 (p_submission_name IN igs_he_submsn_header.submission_name%TYPE,
190 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
191 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
192 p_min_start_dt OUT NOCOPY igs_he_submsn_awd.award_start_date%TYPE,
193 p_max_start_dt OUT NOCOPY igs_he_submsn_awd.award_end_date%TYPE);
194
195 PROCEDURE get_awd_dtls
196 (p_submission_name IN igs_he_submsn_awd.submission_name%TYPE,
197 p_awd_table OUT NOCOPY awd_table,
198 p_search_prog OUT NOCOPY BOOLEAN,
199 p_search_prog_type OUT NOCOPY BOOLEAN);
200
201 PROCEDURE get_awd_conferral_dates
202 (p_awd_table IN awd_table,
203 p_submission_name IN igs_he_ext_run_dtls.submission_name%TYPE,
204 p_search_prog IN BOOLEAN,
205 p_search_prog_type IN BOOLEAN,
206 p_course_cd IN igs_ps_ver_all.course_cd%TYPE,
207 p_course_type IN igs_ps_ver_all.course_type%TYPE,
208 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
209 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
210 p_awd_conf_start_dt OUT NOCOPY igs_he_submsn_awd.award_start_date%TYPE,
211 p_awd_conf_end_dt OUT NOCOPY igs_he_submsn_awd.award_end_date%TYPE);
212
213 PROCEDURE get_rsn_inst_left
214 (p_person_id IN igs_he_en_susa.person_id%TYPE,
215 p_course_cd IN igs_he_en_susa.course_cd%TYPE,
216 p_crs_req_comp_ind IN igs_en_stdnt_ps_att.course_rqrmnt_complete_ind%TYPE,
217 p_crs_req_comp_dt IN igs_en_stdnt_ps_att.course_rqrmnts_complete_dt%TYPE,
218 p_disc_reason_cd IN igs_en_stdnt_ps_att.discontinuation_reason_cd%TYPE,
219 p_disc_dt IN igs_en_stdnt_ps_att.discontinued_dt%TYPE,
220 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
221 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
222 p_rsn_inst_left OUT NOCOPY VARCHAR2);
223
224
225 --smaddali 6-jun-2002 bug 2396174 added parameter p_course_cd
226 PROCEDURE get_completion_status
227 (p_person_id IN igs_pe_person.person_id%TYPE,
228 p_course_cd IN igs_he_st_spa.course_cd%TYPE ,
229 p_susa_comp_status IN igs_he_en_susa.completion_status%TYPE,
230 p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
231 p_crs_req_comp_ind IN igs_en_stdnt_ps_att.course_rqrmnt_complete_ind%TYPE ,
232 p_discont_date IN igs_en_stdnt_ps_att.discontinued_dt%TYPE,
233 p_hesa_comp_status OUT NOCOPY VARCHAR2);
234
235 PROCEDURE get_good_stand_mrkr
236 (p_susa_good_st_mk IN igs_he_en_susa.good_stand_marker%TYPE,
237 p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
238 p_crs_req_comp_ind IN igs_en_stdnt_ps_att.course_rqrmnt_complete_ind%TYPE ,
239 p_discont_date IN igs_en_stdnt_ps_att.discontinued_dt%TYPE,
240 p_hesa_good_st_mk OUT NOCOPY VARCHAR2);
241
242 PROCEDURE get_qual_obtained
243 (p_person_id IN igs_pe_person.person_id%TYPE,
244 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
245 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
246 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
247 p_oss_qual_obt1 OUT NOCOPY VARCHAR2,
248 p_oss_qual_obt2 OUT NOCOPY VARCHAR2,
249 p_hesa_qual_obt1 OUT NOCOPY VARCHAR2,
250 p_hesa_qual_obt2 OUT NOCOPY VARCHAR2,
251 p_classification OUT NOCOPY VARCHAR2);
252
253 PROCEDURE get_fe_qual_aim
254 (p_spa_fe_qual_aim IN igs_he_st_spa.student_fe_qual_aim%TYPE,
255 p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
256 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
257 p_version_number IN igs_he_st_spa.version_number%TYPE,
258 p_hesa_fe_qual_aim OUT NOCOPY VARCHAR2);
259
260 PROCEDURE get_qual_aim_sbj
261 (p_course_cd IN igs_he_st_spa.course_cd%TYPE,
262 p_version_number IN igs_he_st_spa.version_number%TYPE,
263 p_subject1 OUT NOCOPY VARCHAR2,
264 p_subject2 OUT NOCOPY VARCHAR2,
265 p_subject3 OUT NOCOPY VARCHAR2,
266 p_prop_ind OUT NOCOPY VARCHAR2);
267
268 -- smaddali added 2 new parameters for bug#3360646
269 PROCEDURE get_gen_qual_aim
270 (p_person_id IN igs_pe_person.person_id%TYPE,
271 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
272 p_version_number IN igs_he_st_spa.version_number%TYPE,
273 p_spa_gen_qaim IN igs_he_st_spa.student_qual_aim%TYPE,
274 p_hesa_gen_qaim OUT NOCOPY VARCHAR2,
275 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
276 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL,
277 p_awd_conf_start_dt IN igs_he_submsn_awd.award_start_date%TYPE DEFAULT NULL);
278
279 PROCEDURE get_awd_body_12
280 (p_course_cd IN igs_he_st_spa.course_cd%TYPE,
281 p_version_number IN igs_he_st_spa.version_number%TYPE,
282 p_awd1 IN VARCHAR2,
283 p_awd2 IN VARCHAR2,
284 p_awd_body1 OUT NOCOPY VARCHAR2,
285 p_awd_body2 OUT NOCOPY VARCHAR2);
286
287 PROCEDURE get_prog_length
288 (p_spa_attendance_type IN igs_en_stdnt_ps_att.attendance_type%TYPE,
289 p_ft_compl_time IN igs_ps_ver.std_ft_completion_time%TYPE,
290 p_pt_compl_time IN igs_ps_ver.std_pt_completion_time%TYPE,
291 p_length OUT NOCOPY VARCHAR2,
292 p_units OUT NOCOPY VARCHAR2);
293
294 PROCEDURE get_teach_train_crs_id
295 (p_prg_ttcid IN igs_he_st_prog.teacher_train_prog_id%TYPE,
296 p_spa_ttcid IN igs_he_st_spa.teacher_train_prog_id%TYPE,
297 p_hesa_ttcid OUT NOCOPY VARCHAR2);
298
299 PROCEDURE get_itt_phsc
300 (p_prg_itt_phsc IN igs_he_st_prog.itt_phase%TYPE,
301 p_spa_itt_phsc IN igs_he_st_spa.itt_phase%TYPE,
302 p_hesa_itt_phsc OUT NOCOPY VARCHAR2);
303
304 PROCEDURE get_itt_mrker
308
305 (p_prg_itt_mrker IN igs_he_st_prog.bilingual_itt_marker%TYPE,
306 p_spa_itt_mrker IN igs_he_st_spa.bilingual_itt_marker%TYPE,
307 p_hesa_itt_mrker OUT NOCOPY VARCHAR2);
309 PROCEDURE get_teach_qual_sect
310 (p_oss_teach_qual_sect IN igs_he_st_prog.teaching_qual_sought_sector%TYPE,
311 p_hesa_teach_qual_sect OUT NOCOPY VARCHAR2);
312
313 PROCEDURE get_teach_qual_sbj
314 (p_oss_teach_qual_sbj IN igs_he_st_prog.teaching_qual_sought_subj1%TYPE,
315 p_hesa_teach_qual_sbj OUT NOCOPY VARCHAR2);
316
317 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
318 PROCEDURE get_fee_elig
319 (p_person_id IN igs_pe_person.person_id%TYPE,
320 p_susa_fee_elig IN igs_he_en_susa.fee_eligibility%TYPE,
321 p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
322 p_study_mode IN VARCHAR2,
323 p_special_student IN VARCHAR2,
324 p_hesa_fee_elig OUT NOCOPY VARCHAR2,
325 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
326 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
327
328 PROCEDURE get_fee_band
329 (p_hesa_fee_elig IN igs_he_en_susa.fee_eligibility%TYPE,
330 p_susa_fee_band IN igs_he_en_susa.fee_band%TYPE,
331 p_poous_fee_band IN igs_he_poous.fee_band%TYPE,
332 p_prg_fee_band IN igs_he_st_prog.fee_band%TYPE,
333 p_hesa_fee_band OUT NOCOPY VARCHAR2);
334
335 -- smaddali 13-oct-03 modified procedure to add 2 new parameters for bug# 3179544
336 PROCEDURE get_amt_tuition_fees
337 (p_person_id IN igs_pe_person.person_id%TYPE,
338 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
339 p_cal_type IN igs_en_stdnt_ps_att.cal_type%TYPE,
340 p_fe_prg_mrker IN igs_he_st_prog.fe_program_marker%TYPE,
341 p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
342 p_oss_amt OUT NOCOPY NUMBER,
343 p_hesa_amt OUT NOCOPY VARCHAR2,
344 p_enrl_start_dt IN DATE DEFAULT NULL,
345 p_enrl_end_dt IN DATE DEFAULT NULL);
346
347 PROCEDURE get_maj_src_tu_fee
348 (p_person_id IN igs_pe_person.person_id%TYPE,
349 p_enrl_start_dt IN DATE,
350 p_enrl_end_dt IN DATE,
351 p_special_stdnt IN VARCHAR2,
352 p_study_mode IN VARCHAR2,
353 p_amt_tu_fee IN NUMBER,
354 p_susa_mstufee IN igs_he_en_susa.student_fee%TYPE,
355 p_hesa_mstufee OUT NOCOPY VARCHAR2);
356
357 PROCEDURE get_religion
358 (p_oss_religion IN igs_pe_stat_v.religion%TYPE,
359 p_hesa_religion OUT NOCOPY VARCHAR2);
360
361 PROCEDURE get_sldd_disc_prv
362 (p_oss_sldd_disc_prv IN igs_he_en_susa.sldd_discrete_prov%TYPE,
363 p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
364 p_hesa_sldd_disc_prv OUT NOCOPY VARCHAR2);
365
366
367
368 PROCEDURE get_non_payment_rsn
369 (p_oss_non_payment_rsn IN igs_he_en_susa.non_payment_reason%TYPE,
370 p_fe_stdnt_mrker IN igs_he_st_spa.fe_student_marker%TYPE,
371 p_hesa_non_payment_rsn OUT NOCOPY VARCHAR2);
372
373
374
375 PROCEDURE get_oth_teach_inst
376 (p_person_id IN igs_pe_person.person_id%TYPE,
377 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
378 p_program_calc IN igs_he_st_prog.program_calc%TYPE,
379 p_susa_inst1 IN igs_he_en_susa.teaching_inst1%TYPE,
380 p_poous_inst1 IN igs_he_poous.other_instit_teach1%TYPE,
381 p_prog_inst1 IN igs_he_st_prog.other_inst_prov_teaching1%TYPE,
382 p_susa_inst2 IN igs_he_en_susa.teaching_inst1%TYPE,
383 p_poous_inst2 IN igs_he_poous.other_instit_teach1%TYPE,
384 p_prog_inst2 IN igs_he_st_prog.other_inst_prov_teaching1%TYPE,
385 p_hesa_inst1 OUT NOCOPY VARCHAR2,
386 p_hesa_inst2 OUT NOCOPY VARCHAR2,
387 p_enrl_start_dt IN DATE,
388 p_enrl_end_dt IN DATE);
389
390 -- smaddali added new parameters p_enrl_start_dt , p_enrl_end_dt for bug 2437081
391 PROCEDURE get_prop_not_taught
392 (p_person_id IN igs_pe_person.person_id%TYPE,
393 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
394 p_enrl_start_dt IN DATE,
395 p_enrl_end_dt IN DATE,
396 p_program_calc IN igs_he_st_prog.program_calc%TYPE,
397 p_susa_prop IN igs_he_en_susa.pro_not_taught%TYPE,
398 p_poous_prop IN igs_he_poous.prop_not_taught%TYPE,
399 p_prog_prop IN igs_he_st_prog.prop_not_taught%TYPE,
400 p_hesa_prop OUT NOCOPY VARCHAR2);
401
402 PROCEDURE get_credit_trans_sch
403 (p_oss_credit_trans_sch IN igs_he_st_prog.credit_transfer_scheme%TYPE,
404 p_hesa_credit_trans_sch OUT NOCOPY VARCHAR2);
405 --smaddali added new parameter p_susa_credit_level for bug 2415879
406 PROCEDURE get_credit_level
410
407 (p_susa_credit_level IN igs_he_en_susa.credit_level1%TYPE ,
408 p_poous_credit_level IN igs_he_poous.level_credit1%TYPE,
409 p_hesa_credit_level OUT NOCOPY VARCHAR2);
411 -- jbaber added crd_pt3-4, lvl_crd_pt3-4 for HEFD350
412 PROCEDURE get_credit_obtained
413 (p_person_id IN igs_pe_person.person_id%TYPE,
414 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
415 p_prog_calc IN igs_he_st_prog.program_calc%TYPE,
416 p_susa_crd_pt1 IN igs_he_en_susa.credit_pt_achieved1%TYPE,
417 p_susa_crd_pt2 IN igs_he_en_susa.credit_pt_achieved2%TYPE,
418 p_susa_crd_pt3 IN igs_he_en_susa.credit_pt_achieved3%TYPE,
419 p_susa_crd_pt4 IN igs_he_en_susa.credit_pt_achieved4%TYPE,
420 p_susa_crd_lvl1 IN igs_he_en_susa.credit_level_achieved1%TYPE,
421 p_susa_crd_lvl2 IN igs_he_en_susa.credit_level_achieved2%TYPE,
422 p_susa_crd_lvl3 IN igs_he_en_susa.credit_level_achieved3%TYPE,
423 p_susa_crd_lvl4 IN igs_he_en_susa.credit_level_achieved4%TYPE,
424 p_no_crd_pt1 OUT NOCOPY VARCHAR2,
425 p_no_crd_pt2 OUT NOCOPY VARCHAR2,
426 p_no_crd_pt3 OUT NOCOPY VARCHAR2,
427 p_no_crd_pt4 OUT NOCOPY VARCHAR2,
428 p_lvl_crd_pt1 OUT NOCOPY VARCHAR2,
429 p_lvl_crd_pt2 OUT NOCOPY VARCHAR2,
430 p_lvl_crd_pt3 OUT NOCOPY VARCHAR2,
431 p_lvl_crd_pt4 OUT NOCOPY VARCHAR2,
432 p_enrl_start_dt IN DATE,
433 p_enrl_end_dt IN DATE );
434
435 --jbaber added new parameter p_validation_country for HEFD350 process 2
436 PROCEDURE get_cost_centres
437 (p_person_id IN igs_pe_person.person_id%TYPE,
438 p_course_cd IN igs_en_stdnt_ps_att.course_cd%TYPE,
439 p_version_number IN igs_en_stdnt_ps_att.version_number%TYPE,
440 p_unit_set_cd IN igs_he_poous.unit_set_cd%TYPE,
441 p_us_version_number IN igs_he_poous.us_version_number%TYPE,
442 p_cal_type IN igs_he_poous.cal_type%TYPE,
443 p_attendance_mode IN igs_he_poous.attendance_mode%TYPE,
444 p_attendance_type IN igs_he_poous.attendance_type%TYPE,
445 p_location_cd IN igs_he_poous.location_cd%TYPE,
446 p_program_calc IN igs_he_st_prog.program_calc%TYPE,
447 p_unit_cd IN igs_he_st_unt_vs.unit_cd%TYPE,
448 p_uv_version_number IN igs_he_st_unt_vs.version_number%TYPE,
449 p_return_type IN VARCHAR2,
450 p_cost_ctr_rec IN OUT NOCOPY cc_rec,
451 p_total_recs OUT NOCOPY NUMBER,
452 p_enrl_start_dt IN DATE,
453 p_enrl_end_dt IN DATE,
454 p_sequence_number IN NUMBER DEFAULT NULL,
455 p_validation_country IN igs_he_submsn_header.validation_country%TYPE);
456
457 -- jbaber 25-Nov-2004 Included p_version_number for bug # 4037237
458 PROCEDURE get_studies_susp
459 (p_person_id IN igs_pe_person.person_id%TYPE,
460 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
461 p_version_number IN igs_he_st_spa.version_number%TYPE,
462 p_enrl_start_dt IN DATE,
463 p_enrl_end_dt IN DATE,
464 p_susp_act_std OUT NOCOPY VARCHAR2);
465
466 PROCEDURE get_pyr_type
467 (p_oss_pyr_type IN igs_he_poous.type_of_year%TYPE,
468 p_hesa_pyr_type OUT NOCOPY VARCHAR2);
469
470 PROCEDURE get_lvl_appl_to_fund
471 (p_poous_lvl_appl_fund IN igs_he_poous.level_applicable_to_funding%TYPE,
472 p_prg_lvl_appl_fund IN igs_he_st_prog.level_applicable_to_funding%TYPE,
473 p_hesa_lvl_appl_fund OUT NOCOPY VARCHAR2);
474
475 PROCEDURE get_comp_pyr_study(
476 p_susa_comp_pyr_study IN igs_he_en_susa.complete_pyr_study_cd%TYPE,
477 p_fundlev IN VARCHAR2,
478 p_spcstu IN VARCHAR2,
479 p_notact IN VARCHAR2,
480 p_mode IN VARCHAR2,
481 p_typeyr IN VARCHAR2,
482 p_crse_rqr_complete_ind IN igs_en_stdnt_ps_att.course_rqrmnt_complete_ind%TYPE,
483 p_crse_req_complete_dt IN igs_en_stdnt_ps_att.course_rqrmnts_complete_dt%TYPE,
484 p_disc_reason_cd IN igs_en_stdnt_ps_att.discontinuation_reason_cd%TYPE,
485 p_discont_dt IN igs_en_stdnt_ps_att.discontinued_dt%TYPE,
486 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
487 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
488 p_person_id IN igs_en_stdnt_ps_att.person_id%TYPE,
489 p_course_cd IN igs_en_stdnt_ps_att.course_cd%TYPE,
490 p_hesa_comp_pyr_study OUT NOCOPY VARCHAR2);
491
492 PROCEDURE get_destination
493 (p_oss_destination IN igs_he_st_spa.destination%TYPE,
494 p_hesa_destination OUT NOCOPY VARCHAR2);
495
496 PROCEDURE get_itt_outcome
497 (p_oss_itt_outcome IN igs_he_st_spa.itt_prog_outcome%TYPE,
498 p_teach_train_prg IN igs_he_st_spa.teacher_train_prog_id%TYPE,
499 p_hesa_itt_outcome OUT NOCOPY VARCHAR2);
500
501 PROCEDURE get_ufi_place
502 (p_oss_ufi_place IN igs_he_st_spa.ufi_place%TYPE,
503 p_hesa_ufi_place OUT NOCOPY VARCHAR2);
504
505 PROCEDURE get_franchising_activity
506 (p_susa_franch_activity IN igs_he_en_susa.franchising_activity%TYPE,
507 p_poous_franch_activity IN igs_he_poous.franchising_activity%TYPE,
508 p_prog_franch_activity IN igs_he_st_prog.franchising_activity%TYPE,
509 p_hesa_franch_activity OUT NOCOPY VARCHAR2);
510
511 PROCEDURE get_social_class_ind
512 (p_spa_social_class_ind IN igs_he_st_spa.social_class_ind%TYPE,
513 p_adm_social_class_ind IN igs_he_ad_dtl.social_class_cd%TYPE,
514 p_hesa_social_class_ind OUT NOCOPY VARCHAR2);
515
516 PROCEDURE get_occupation_code
517 (p_spa_occupation_code IN igs_he_st_spa.occupation_code%TYPE,
518 p_hesa_occupation_code OUT NOCOPY VARCHAR2);
519
520 PROCEDURE get_inst_last_attended
521 (p_person_id IN igs_pe_person.person_id%TYPE,
522 p_com_date IN DATE,
523 p_inst_last_att OUT NOCOPY VARCHAR2,
524 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
525 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
526
527 PROCEDURE get_regulatory_body
528 (p_course_cd IN igs_he_st_spa.course_cd%TYPE,
529 p_version_number IN igs_he_st_spa.version_number%TYPE,
530 p_hesa_regulatory_body OUT NOCOPY VARCHAR2);
531
532 PROCEDURE get_nhs_fund_src
533 (p_spa_nhs_fund_src IN igs_he_st_spa.nhs_funding_source%TYPE,
534 p_prg_nhs_fund_src IN igs_he_st_spa.nhs_funding_source%TYPE,
535 p_hesa_nhs_fund_src OUT NOCOPY VARCHAR2);
536
537 PROCEDURE get_nhs_employer
538 (p_spa_nhs_employer IN igs_he_st_spa.nhs_employer%TYPE,
539 p_hesa_nhs_employer OUT NOCOPY VARCHAR2);
540
541 PROCEDURE get_qual_dets
542 (p_person_id IN igs_pe_person.person_id%TYPE,
543 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
544 p_hesa_qual IN VARCHAR2,
545 p_no_of_qual OUT NOCOPY NUMBER,
546 p_tariff_score OUT NOCOPY NUMBER);
547
548
549 PROCEDURE get_module_dets
550 (p_person_id IN igs_pe_person.person_id%TYPE,
551 p_course_cd IN igs_en_stdnt_ps_att.course_cd%TYPE,
552 p_version_number IN igs_en_stdnt_ps_att.version_number%TYPE,
553 p_student_inst_number IN igs_he_st_spa.student_inst_number%TYPE,
554 p_cal_type IN igs_en_stdnt_ps_att.cal_type%TYPE,
555 p_enrl_start_dt IN DATE,
556 p_enrl_end_dt IN DATE,
557 p_offset_days IN NUMBER,
558 p_module_rec IN OUT NOCOPY mod_rec,
559 p_total_recs OUT NOCOPY NUMBER);
560
561 -- jbaber 25-Nov-2004 Included p_version_number for bug # 4037237
562 PROCEDURE get_mode_of_study
563 (p_person_id IN igs_pe_person.person_id%TYPE,
564 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
565 p_version_number IN igs_he_st_spa.version_number%TYPE,
566 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
567 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
568 p_susa_study_mode IN igs_he_en_susa.study_mode%TYPE,
569 p_poous_study_mode IN igs_he_poous.attendance_mode%TYPE,
570 p_attendance_type IN igs_en_stdnt_ps_att.attendance_type%TYPE,
571 p_mode_of_study OUT NOCOPY VARCHAR2);
572
573 FUNCTION isDormant
574 (p_person_id IN igs_pe_person.person_id%TYPE,
575 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
576 p_version_number IN igs_he_st_spa.version_number%TYPE,
577 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
578 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE)
579 RETURN BOOLEAN;
580
581 FUNCTION is_ucas_ftug
582 (p_hesa_qual_aim IN igs_he_ex_rn_dat_fd.value%TYPE,
583 p_hesa_commdate IN igs_he_ex_rn_dat_fd.value%TYPE,
584 p_ucasnum IN igs_he_ex_rn_dat_fd.value%TYPE,
585 p_min_commdate IN DATE) RETURN BOOLEAN;
586
587 PROCEDURE limit_no_of_qual
588 (p_field_number IN NUMBER,
589 p_person_number IN igs_pe_person.person_number%TYPE,
590 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
591 p_hesa_qual IN VARCHAR2,
592 p_no_of_qual IN OUT NOCOPY NUMBER);
593
594 PROCEDURE limit_tariff_score
595 (p_field_number IN NUMBER,
596 p_person_number IN igs_pe_person.person_number%TYPE,
597 p_course_cd IN igs_he_st_spa.course_cd%TYPE,
598 p_hesa_qual IN VARCHAR2,
599 p_tariff_score IN OUT NOCOPY igs_he_ex_rn_dat_fd.value%TYPE);
600
601 PROCEDURE get_mod_prop_fte
602 (p_enrolled_credit_points IN igs_ps_unit_ver_v.enrolled_credit_points%TYPE,
603 p_unit_level IN igs_ps_unit_ver_v.unit_level%TYPE,
604 p_prop_of_fte OUT NOCOPY VARCHAR2);
605
606 PROCEDURE get_mod_prop_not_taught
607 (p_unit_cd IN igs_he_st_unt_vs.unit_cd%TYPE,
608 p_version_number IN igs_he_st_unt_vs.version_number%TYPE,
609 p_prop_not_taught OUT NOCOPY VARCHAR2);
610
611 PROCEDURE get_mod_oth_teach_inst
612 (p_unit_cd IN igs_he_st_unt_vs.unit_cd%TYPE,
613 p_version_number IN igs_he_st_unt_vs.version_number%TYPE,
614 p_oth_teach_inst OUT NOCOPY VARCHAR2);
615
616 PROCEDURE get_pgce_class
617 (p_person_id IN igs_pe_person.person_id%TYPE,
618 p_pgce_class OUT NOCOPY VARCHAR2);
619
620
621 PROCEDURE get_commencement_dt
622 ( p_hesa_commdate IN igs_he_st_spa_all.commencement_dt%TYPE DEFAULT NULL,
623 p_enstdnt_commdate IN igs_en_stdnt_ps_att.commencement_dt%TYPE DEFAULT NULL,
624 p_person_id IN igs_pe_person.person_id%TYPE ,
625 p_course_cd IN igs_he_st_spa_all.course_cd%TYPE,
626 p_version_number IN igs_he_st_spa_all.version_number%TYPE,
627 p_student_inst_number IN igs_he_st_spa_all.student_inst_number%TYPE,
628 p_final_commdate OUT NOCOPY igs_he_ex_rn_dat_fd.value%TYPE );
629
630
631 PROCEDURE get_qual_aim_sbj1
632 ( p_qual_aim_subj1 IN igs_he_st_spa.qual_aim_subj1%TYPE,
633 p_qual_aim_subj2 IN igs_he_st_spa.qual_aim_subj2%TYPE,
634 p_qual_aim_subj3 IN igs_he_st_spa.qual_aim_subj3%TYPE,
635 p_oss_qualaim_sbj IN igs_he_code_values.value%TYPE,
636 p_hesa_qualaim_sbj OUT NOCOPY igs_he_code_values.value%TYPE);
637
638
639 PROCEDURE get_new_prog_length
640 (p_spa_attendance_type IN igs_en_stdnt_ps_att.attendance_type%TYPE,
641 p_program_length IN igs_ps_ofr_opt_all.program_length%TYPE,
642 p_program_length_measurement IN igs_ps_ofr_opt_all.program_length_measurement%TYPE,
643 p_length OUT NOCOPY NUMBER,
644 p_units OUT NOCOPY NUMBER);
645
646 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
647 PROCEDURE get_ucasnum
648 (p_person_id IN igs_pe_person.person_id%TYPE,
649 p_ucasnum OUT NOCOPY igs_pe_alt_pers_id.api_person_id%TYPE,
650 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE DEFAULT NULL,
651 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE DEFAULT NULL);
652
653 PROCEDURE get_marital_status
654 (p_oss_marital_status IN igs_pe_stat_v.marital_status%TYPE,
655 p_hesa_marital_status OUT NOCOPY VARCHAR2);
656
657 PROCEDURE get_dependants
658 (p_oss_dependants IN igs_he_st_spa.dependants_cd%TYPE,
659 p_hesa_dependants OUT NOCOPY VARCHAR2);
660
661 PROCEDURE get_enh_fund_elig
662 (p_susa_enh_fund_elig IN igs_he_en_susa.enh_fund_elig_cd%TYPE ,
663 p_spa_enh_fund_elig IN igs_he_st_spa.enh_fund_elig_cd%TYPE,
664 p_hesa_enh_fund_elig OUT NOCOPY VARCHAR2);
665
666 PROCEDURE get_learn_dif
667 (p_person_id IN igs_pe_person.person_id%TYPE,
668 p_enrl_start_dt IN igs_he_submsn_header.enrolment_start_date%TYPE,
669 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
670 p_hesa_disability_type OUT NOCOPY VARCHAR2);
671
672 PROCEDURE get_gov_init
673 (p_spa_gov_initiatives_cd IN igs_he_st_spa.gov_initiatives_cd%TYPE,
674 p_prog_gov_initiatives_cd IN igs_he_st_prog.gov_initiatives_cd%TYPE,
675 p_hesa_gov_initiatives_cd OUT NOCOPY VARCHAR2);
676
677 PROCEDURE get_units_completed
678 (p_person_id IN igs_pe_person.person_id%TYPE,
679 p_course_cd IN igs_ps_ver.course_cd%TYPE,
680 p_enrl_end_dt IN igs_he_submsn_header.enrolment_end_date%TYPE,
681 p_spa_units_completed IN igs_he_st_spa.units_completed%TYPE,
682 p_hesa_units_completed OUT NOCOPY VARCHAR2);
683
684 PROCEDURE get_disadv_uplift_elig
685 (p_spa_disadv_uplift_elig_cd IN igs_he_st_spa.disadv_uplift_elig_cd%TYPE,
686 p_prog_disadv_uplift_elig_cd IN igs_he_st_prog.disadv_uplift_elig_cd%TYPE,
687 p_hesa_disadv_uplift_elig_cd OUT NOCOPY VARCHAR2);
688
689 PROCEDURE get_franch_out_arr
690 (p_spa_franch_out_arr_cd IN igs_he_st_spa.franch_out_arr_cd%TYPE,
691 p_prog_franch_out_arr_cd IN igs_he_st_prog.franch_out_arr_cd%TYPE,
692 p_hesa_franch_out_arr_cd OUT NOCOPY VARCHAR2);
693
694 PROCEDURE get_employer_role
695 (p_spa_employer_role_cd IN igs_he_st_spa.employer_role_cd%TYPE,
696 p_hesa_employer_role_cd OUT NOCOPY VARCHAR2);
697
698 PROCEDURE get_franchise_partner
699 (p_spa_franch_partner_cd IN igs_he_st_spa.franch_partner_cd%TYPE,
700 p_hesa_franch_partner_cd OUT NOCOPY VARCHAR2);
701
702 PROCEDURE get_welsh_speaker_ind
703 (p_person_id IN igs_pe_person.person_id%TYPE,
704 p_hesa_welsh_speaker_ind OUT NOCOPY VARCHAR2);
705
706 PROCEDURE get_national_id
707 (p_person_id IN igs_pe_person.person_id%TYPE,
711 -- anwest 19-Dec-05 Changes as per (4731723) HE360 - HESA REQUIREMENTS FOR 2005/06 REPORTING
708 p_hesa_national_id1 OUT NOCOPY VARCHAR2,
709 p_hesa_national_id2 OUT NOCOPY VARCHAR2);
710
712 PROCEDURE get_welsh_bacc_qual
713 (p_person_id IN igs_pe_person.person_id%TYPE,
714 p_welsh_bacc OUT NOCOPY VARCHAR2);
715
716 END IGS_HE_EXTRACT_FIELDS_PKG;
[Home] [Help]