[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_GEN_004
Source
1 PACKAGE BODY IGS_ST_GEN_004 AS
2 /* $Header: IGSST04B.pls 120.0 2005/06/02 03:54:49 appldev noship $ */
3 /*------------------------------------------------------------------------------+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA |
5 | All rights reserved. |
6 +==============================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: IGS_ST_GEN_004 |
10 | |
11 | NOTES |
12 | |
13 | |
14 | HISTORY |
15 | Who When What |
16 | knaraset 15-May-2003 Modified code to have unit attempt context with
17 | either uoo_id or location_cd and unit_class,
18 | as part of MUS build bug 2829262
19 | smvk 09-Jul-2004 Bug # 3676145. Modified the cursor c_sua_ucl_um
20 | to select active (not closed) unit classes.
21 | gmaheswa 25-Jan-2005 Bug 3882788 Modified c_get_api to exclude person
22 | identifiers whose start_dt = end_dt.
23 |
24 | ctyagi 15-Apr-2004 Obsolete the procedure Stas_Ins_Ess for bug 4293239
25 +------------------------------------------------------------------------------*/
26
27 Function Stap_Get_Supp_Fos(
28 p_course_cd IN VARCHAR2 ,
29 p_version_number IN NUMBER )
30 RETURN VARCHAR2 AS
31 gv_other_detail VARCHAR2(255);
32 BEGIN -- stap_get_supp_fos
33 -- Derive the supplementary field of study to which
34 -- a combined IGS_PS_COURSE is classified
35 -- DEETYA element 389
36 DECLARE
37 cst_combined CONSTANT IGS_PS_GRP_TYPE.s_course_group_type%TYPE := 'COMBINED';
38 v_dummy VARCHAR2(1);
39 v_govt_field_of_study IGS_PS_FLD_OF_STUDY.govt_field_of_study%TYPE;
40 CURSOR c_cgm_cgr_cgt IS
41 SELECT 'X'
42 FROM IGS_PS_GRP_MBR cgm,
43 IGS_PS_GRP cgr,
44 IGS_PS_GRP_TYPE cgt
45 WHERE cgm.course_cd = p_course_cd AND
46 cgm.version_number = p_version_number AND
47 cgr.course_group_cd = cgm.course_group_cd AND
48 cgt.course_group_type = cgr.course_group_type AND
49 cgt.s_course_group_type = cst_combined;
50 CURSOR c_cfos (
51 cp_course_cd IGS_PS_VER.course_cd%TYPE,
52 cp_version_number IGS_PS_VER.version_number%TYPE) IS
53 SELECT fos.govt_field_of_study
54 FROM IGS_PS_FIELD_STUDY cfos,
55 IGS_PS_FLD_OF_STUDY fos
56 WHERE cfos.course_cd = cp_course_cd AND
57 cfos.version_number = cp_version_number AND
58 fos.field_of_study = cfos.field_of_study
59 ORDER BY
60 cfos.major_field_ind ASC,
61 cfos.percentage DESC,
62 cfos.field_of_study ASC;
63
64 BEGIN
65 OPEN c_cgm_cgr_cgt;
66 FETCH c_cgm_cgr_cgt INTO v_dummy;
67 IF c_cgm_cgr_cgt%NOTFOUND THEN
68 CLOSE c_cgm_cgr_cgt;
69 -- IGS_PS_COURSE version is not a combined IGS_PS_COURSE
70 RETURN '000000';
71 END IF;
72 CLOSE c_cgm_cgr_cgt;
73 -- IGS_PS_COURSE version is a combined IGS_PS_COURSE
74 OPEN c_cfos (
75 p_course_cd,
76 p_version_number);
77 FETCH c_cfos INTO v_govt_field_of_study;
78 IF (c_cfos%NOTFOUND) THEN
79 v_govt_field_of_study := '000000';
80 END IF;
81 CLOSE c_cfos;
82 RETURN v_govt_field_of_study;
83 EXCEPTION
84 WHEN OTHERS THEN
85 IF c_cgm_cgr_cgt%ISOPEN THEN
86 CLOSE c_cgm_cgr_cgt;
87 END IF;
88 IF c_cfos%ISOPEN THEN
89 CLOSE c_cfos;
90 END IF;
91 RAISE;
92 END;
93 EXCEPTION
94 WHEN OTHERS THEN
95 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
96 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stap_get_supp_fos');
97 IGS_GE_MSG_STACK.ADD;
98 App_Exception.Raise_Exception;
99 END stap_get_supp_fos;
100
101 Function Stap_Get_Tot_Exmpt(
102 p_person_id IN NUMBER ,
103 p_course_cd IN VARCHAR2 ,
104 p_version_number IN NUMBER )
105 RETURN NUMBER AS
106 gv_other_detail VARCHAR2(255);
107 BEGIN -- stap_get_tot_exmp
108 -- Description: This module retrieves the total exemption for a IGS_PS_COURSE
109 -- granted to a student.
110 DECLARE
111 v_total_exmptn_perc_grntd NUMBER (5);
112 CURSOR c_ast IS
113 SELECT trunc(total_exmptn_perc_grntd)
114 FROM IGS_AV_ADV_STANDING ast
115 WHERE ast.person_id = p_person_id and
116 ast.course_cd = p_course_cd and
117 ast.version_number = p_version_number;
118 BEGIN
119 v_total_exmptn_perc_grntd := 0;
120 OPEN c_ast;
121 FETCH c_ast INTO v_total_exmptn_perc_grntd;
122 IF (c_ast%FOUND) THEN
123 CLOSE c_ast;
124 IF (v_total_exmptn_perc_grntd > 99) THEN
125 v_total_exmptn_perc_grntd := 99;
126 RETURN v_total_exmptn_perc_grntd;
127 ELSE
128 RETURN v_total_exmptn_perc_grntd;
129 END IF;
130 END IF;
131 CLOSE c_ast;
132 RETURN 0;
133 EXCEPTION
134 WHEN OTHERS THEN
135 IF (c_ast%ISOPEN) THEN
136 CLOSE c_ast;
137 END IF;
138 RAISE;
139 END;
140 EXCEPTION
141 WHEN OTHERS THEN
142 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
143 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stap_get_tot_exmpt');
144 IGS_GE_MSG_STACK.ADD;
145 App_Exception.Raise_Exception;
146 END stap_get_tot_exmpt;
147
148 Function Stap_Get_Un_Comp_Sts(
149 p_person_id IN NUMBER ,
150 p_course_cd IN VARCHAR2 ,
151 p_unit_cd IN VARCHAR2 ,
152 p_sua_cal_type IN VARCHAR2 ,
153 p_sua_ci_sequence_number IN NUMBER,
154 p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE)
155 RETURN NUMBER AS
156 gv_other_detail VARCHAR2(255);
157 BEGIN -- stap_get_un_comp_sts
158 -- Derive the IGS_PS_UNIT of study completion status
159 -- DEETYA element 355
160 DECLARE
161 v_ret_val NUMBER(2);
162 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
163 v_dummy_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
164 v_dummy_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
165 v_dummy_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
166 v_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
167 CURSOR c_get_unit_attempt_status IS
168 SELECT unit_attempt_status
169 FROM IGS_EN_SU_ATTEMPT
170 WHERE person_id = p_person_id AND
171 course_cd = p_course_cd AND
172 uoo_id = p_uoo_id;
173 BEGIN
174 OPEN c_get_unit_attempt_status;
175 FETCH c_get_unit_attempt_status INTO v_unit_attempt_status;
176 IF (c_get_unit_attempt_status%NOTFOUND) THEN
177 CLOSE c_get_unit_attempt_status;
178 RETURN 1;
179 END IF;
180 CLOSE c_get_unit_attempt_status;
181 v_s_result_type := IGS_AS_GEN_003.assp_get_sua_grade (
182 p_person_id,
183 p_course_cd,
184 p_unit_cd,
185 p_sua_cal_type,
186 p_sua_ci_sequence_number,
187 v_unit_attempt_status,
188 'Y',
189 v_dummy_grading_schema_cd,
190 v_dummy_version_number,
191 v_dummy_grade,
192 p_uoo_id);
193 IF (NVL(v_s_result_type, 'NULL_VAL') = 'WITHDRAWN') THEN
194 -- Withdrew without penalty
195 v_ret_val := 1;
196 ELSIF (NVL(v_s_result_type, 'NULL_VAL') = 'FAIL') THEN
197 -- Failed
198 v_ret_val := 2;
199 ELSIF (NVL(v_s_result_type, 'NULL_VAL') = 'PASS') THEN
200 -- Successfully completed all the requirements
201 v_ret_val := 3;
202 ELSE
203 -- IGS_PS_UNIT of student to be completed later in the year or still
204 -- in the process of completing or completion status not yet determined
205 v_ret_val := 4;
206 END IF;
207 RETURN v_ret_val;
208 END;
209 EXCEPTION
210 WHEN OTHERS THEN
211 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
212 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stap_get_un_comp_sts');
213 IGS_GE_MSG_STACK.ADD;
214 App_Exception.Raise_Exception;
215 END stap_get_un_comp_sts;
216
217
218 Function Stap_Ins_Govt_Snpsht(
219 p_submission_yr IN NUMBER ,
220 p_submission_number IN NUMBER ,
221 p_ess_snapshot_dt_time IN DATE ,
222 p_use_most_recent_ess_ind IN VARCHAR2 DEFAULT 'N',
223 p_message_name OUT NOCOPY VARCHAR2 ,
224 p_log_creation_dt OUT NOCOPY DATE )
225 RETURN BOOLEAN AS
226 gv_other_detail VARCHAR2(255);
227 gv_extra_details VARCHAR2(255) DEFAULT NULL;
228 BEGIN
229 DECLARE
230 e_resource_busy EXCEPTION;
231 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
232 v_dummy VARCHAR2(1);
233 v_other_detail VARCHAR2(255);
234 v_ess_rec_found BOOLEAN DEFAULT FALSE;
235 v_message_name VARCHAR2(30) DEFAULT NULL;
236 v_sub_yr IGS_ST_GVT_SPSHT_CTL.submission_yr%TYPE;
237 -- define the submission census dates
238 -- these can be changed if required
239 v_submission_1_census_dt DATE :=
240 IGS_GE_DATE.igsdate(TO_CHAR(p_submission_yr)||'03/31');
241 v_submission_2_census_dt DATE :=
242 IGS_GE_DATE.igsdate(TO_CHAR(p_submission_yr)||'08/31');
243 v_ess_snapshot_dt_time IGS_ST_GVT_SPSHT_CTL.ess_snapshot_dt_time%TYPE;
244 v_effective_dt DATE;
245 v_unit_effective_dt DATE;
246 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
247 v_derived_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
248 v_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE;
249 v_govt_attendance_type IGS_EN_ATD_TYPE.govt_attendance_type%TYPE;
250 v_attendance_mode_1 IGS_EN_ATD_MODE.attendance_mode%TYPE;
251 v_govt_attendance_mode_1 IGS_EN_ATD_MODE.govt_attendance_mode%TYPE;
252 v_attendance_mode_2 IGS_EN_ATD_MODE.attendance_mode%TYPE;
253 v_govt_attendance_mode_2 IGS_EN_ATD_MODE.govt_attendance_mode%TYPE;
254 v_attendance_mode_3 IGS_EN_ATD_MODE.attendance_mode%TYPE;
255 v_govt_attendance_mode_3 IGS_EN_ATD_MODE.govt_attendance_mode%TYPE;
256 v_load_cal_type IGS_EN_ST_SNAPSHOT.ci_cal_type%TYPE DEFAULT NULL;
257 v_load_ci_sequence_number
258 IGS_EN_ST_SNAPSHOT.ci_sequence_number%TYPE DEFAULT NULL;
259 v_teach_cal_type IGS_EN_ST_SNAPSHOT.sua_cal_type%TYPE DEFAULT NULL;
260 v_teach_ci_sequence_number
261 IGS_EN_ST_SNAPSHOT.sua_ci_sequence_number%TYPE DEFAULT NULL;
262 v_person_id IGS_PE_STATISTICS.person_id%TYPE DEFAULT NULL;
263 v_course_cd IGS_EN_ST_SNAPSHOT.course_cd%TYPE DEFAULT NULL;
264 v_gse_person_id IGS_PE_STATISTICS.person_id%TYPE DEFAULT NULL;
265 v_gse_course_cd IGS_EN_ST_SNAPSHOT.course_cd%TYPE DEFAULT NULL;
266 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE DEFAULT NULL;
267 v_govt_semester IGS_ST_GVT_STDNTLOAD.govt_semester%TYPE DEFAULT NULL;
268 v_govt_reportable IGS_EN_ST_SNAPSHOT.govt_reportable_ind%TYPE;
269 v_birth_dt IGS_ST_GOVT_STDNT_EN.birth_dt%TYPE;
270 v_sex IGS_ST_GOVT_STDNT_EN.sex%TYPE;
271 v_govt_disability IGS_ST_GOVT_STDNT_EN.govt_disability%TYPE;
272 v_aborig_torres_cd IGS_ST_GOVT_STDNT_EN.aborig_torres_cd%TYPE;
273 v_govt_aborig_torres_cd IGS_ST_GOVT_STDNT_EN.govt_aborig_torres_cd%TYPE;
274 v_citizenship_cd IGS_ST_GOVT_STDNT_EN.citizenship_cd%TYPE;
275 v_govt_citizenship_cd IGS_ST_GOVT_STDNT_EN.govt_citizenship_cd%TYPE;
276 v_perm_resident_cd IGS_ST_GOVT_STDNT_EN.perm_resident_cd%TYPE;
277 v_govt_perm_resident_cd IGS_ST_GOVT_STDNT_EN.govt_perm_resident_cd%TYPE;
278 v_home_location_cd IGS_ST_GOVT_STDNT_EN.home_location%TYPE;
279 v_govt_home_location_cd IGS_ST_GOVT_STDNT_EN.govt_home_location%TYPE;
280 v_term_location_cd IGS_ST_GOVT_STDNT_EN.term_location%TYPE;
281 v_govt_term_location_cd IGS_ST_GOVT_STDNT_EN.govt_term_location%TYPE;
282 v_home_location_postcode IGS_PE_STATISTICS.home_location_postcode%TYPE;
283 v_home_location_country IGS_PE_STATISTICS.home_location_country%TYPE;
284 v_term_location_postcode IGS_PE_STATISTICS.term_location_postcode%TYPE;
285 v_term_location_country IGS_PE_STATISTICS.term_location_country%TYPE;
286 v_birth_country_cd IGS_ST_GOVT_STDNT_EN.birth_country_cd%TYPE;
287 v_govt_birth_country_cd IGS_ST_GOVT_STDNT_EN.govt_birth_country_cd%TYPE;
288 v_yr_arrival IGS_ST_GOVT_STDNT_EN.yr_arrival%TYPE;
289 v_home_language_cd IGS_ST_GOVT_STDNT_EN.home_language_cd%TYPE;
290 v_govt_home_language_cd IGS_ST_GOVT_STDNT_EN.govt_home_language_cd%TYPE;
291 v_prior_ug_inst IGS_ST_GOVT_STDNT_EN.prior_ug_inst%TYPE;
292 v_govt_prior_ug_inst IGS_ST_GOVT_STDNT_EN.govt_prior_ug_inst%TYPE;
293 v_prior_other_qual IGS_ST_GOVT_STDNT_EN.prior_other_qual%TYPE;
294 v_prior_post_grad IGS_ST_GOVT_STDNT_EN.prior_post_grad%TYPE;
295 v_prior_degree IGS_ST_GOVT_STDNT_EN.prior_degree%TYPE;
296 v_prior_subdeg_notafe IGS_ST_GOVT_STDNT_EN.prior_subdeg_notafe%TYPE;
297 v_prior_subdeg_tafe IGS_ST_GOVT_STDNT_EN.prior_subdeg_tafe%TYPE;
298 v_prior_seced_tafe IGS_ST_GOVT_STDNT_EN.prior_seced_tafe%TYPE;
299 v_prior_seced_school IGS_ST_GOVT_STDNT_EN.prior_seced_school%TYPE;
300 v_prior_tafe_award IGS_ST_GOVT_STDNT_EN.prior_tafe_award%TYPE;
301 v_prior_studies_exemption IGS_ST_GOVT_STDNT_EN.prior_studies_exemption%TYPE;
302 v_exempt_institution_cd
303 IGS_ST_GOVT_STDNT_EN.exemption_institution_cd%TYPE;
304 v_govt_exempt_institution_cd
305 IGS_ST_GOVT_STDNT_EN.govt_exemption_institution_cd%TYPE;
306 v_tertiary_entrance_score IGS_ST_GOVT_STDNT_EN.tertiary_entrance_score%TYPE;
307 v_basis_for_admission_type
308 IGS_ST_GOVT_STDNT_EN.basis_for_admission_type%TYPE;
309 v_govt_basis_for_adm_type
310 IGS_ST_GOVT_STDNT_EN.govt_basis_for_admission_type% TYPE;
311 v_hecs_amount_pd IGS_ST_GVT_STDNT_LBL.hecs_amount_paid%TYPE;
312 v_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE;
313 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
314 v_tuition_fee IGS_ST_GVT_STDNT_LBL.tuition_fee%TYPE;
315 v_hecs_fee NUMBER;
316 v_differential_hecs_ind IGS_ST_GVT_STDNT_LBL.differential_hecs_ind%TYPE;
317 v_industrial_ind IGS_ST_GVT_STDNTLOAD.industrial_ind%TYPE;
318 v_unit_cd IGS_ST_GVT_STDNTLOAD.unit_cd%TYPE DEFAULT NULL;
319 v_uv_version_number IGS_ST_GVT_STDNTLOAD.uv_version_number%TYPE DEFAULT NULL;
320 v_unit_completion_status IGS_ST_GVT_STDNTLOAD.unit_completion_status%TYPE;
321 v_indus_eftsu IGS_ST_GVT_STDNT_LBL.industrial_eftsu%TYPE;
322 v_total_eftsu IGS_ST_GVT_STDNT_LBL.total_eftsu%TYPE;
323 v_hecs_prexmt_exie_update IGS_ST_GVT_STDNT_LBL.hecs_prexmt_exie%TYPE;
324 v_hecs_prexmt_exie IGS_ST_GVT_STDNT_LBL.hecs_prexmt_exie%TYPE;
325 v_first_flag BOOLEAN;
326 v_temp_person_id NUMBER;
327 v_tmp_person_id NUMBER;
328 v_flag BOOLEAN;
329 v_prev_sub BOOLEAN;
330 v_s_unit_mode IGS_AS_UNIT_MODE.s_unit_mode%TYPE;
331 v_govt_attendance_mode IGS_EN_ATD_MODE.govt_attendance_mode%TYPE;
332 v_attendance_mode IGS_EN_ATD_MODE.attendance_mode%TYPE;
333 v_lower_enr_load_range IGS_EN_ATD_TYPE.lower_enr_load_range%TYPE;
334 v_major_course IGS_ST_GOVT_STDNT_EN.major_course%TYPE;
335 v_on BOOLEAN DEFAULT FALSE;
336 v_off BOOLEAN DEFAULT FALSE;
337 v_composite BOOLEAN DEFAULT FALSE;
338 v_unit_total_eftsu IGS_ST_GVT_STDNTLOAD.eftsu%TYPE;
339 v_unit_industrial_eftsu IGS_ST_GVT_STDNTLOAD.eftsu%TYPE;
340 v_logged_ind BOOLEAN;
341 v_s_log_type Varchar2(10);
342 v_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
343 v_upd_total_eftsu IGS_ST_GVT_STDNT_LBL.total_eftsu%TYPE;
344 v_upd_indus_eftsu IGS_ST_GVT_STDNT_LBL.industrial_eftsu%TYPE;
345 v_owner all_indexes.owner%TYPE;
346 v_command VARCHAR2(600);
347 v_command_cursor INTEGER;
348 v_ret INTEGER;
349 v_start_dt_time DATE;
350 v_award_course_ind IGS_PS_TYPE.award_course_ind%TYPE;
351 v_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE;
352 v_old_govt_semester IGS_ST_GVT_STDNTLOAD.govt_semester%TYPE DEFAULT NULL;
353 v_old_person_id IGS_PE_PERSON.person_id%TYPE;
354 v_old_course_cd IGS_PS_VER.course_cd%TYPE;
355 v_appl_owner all_indexes.owner%TYPE;
356 v_start_dt DATE;
357 v_end_dt DATE;
358 v_start_dt_2 DATE;
359 v_end_dt_2 DATE;
360 v_other_detail VARCHAR2(255);
361 v_ret INTEGER;
362 v_unit_completion_stat NUMBER;
363 CURSOR Cur_si_st_govtstdldtmp IS
364 SELECT sgs.*,uoo.uoo_id
365 FROM IGS_ST_GVT_STDNTLOAD sgs,
366 igs_ps_unit_ofr_opt uoo
367 WHERE sgs.submission_yr = p_submission_yr AND
368 sgs.submission_number = (p_submission_number - 1) AND
369 sgs.unit_cd = uoo.unit_cd AND
370 sgs.uv_version_number = uoo.version_number AND
371 sgs.sua_cal_type = uoo.cal_type AND
372 sgs.sua_ci_sequence_number = uoo.ci_sequence_number AND
373 sgs.sua_location_cd = uoo.location_cd AND
374 sgs.unit_class = uoo.unit_class
375 ORDER BY Person_id;
376
377 CURSOR c_govt_snpsht_ctl IS
378 SELECT gsc.submission_yr
379 FROM IGS_ST_GVT_SPSHT_CTL gsc
380 WHERE gsc.submission_yr = p_submission_yr AND
381 gsc.submission_number = p_submission_number;
382 CURSOR c_gsc_upd IS
383 SELECT rowid, gsc.*
384 FROM IGS_ST_GVT_SPSHT_CTL gsc
385 WHERE gsc.submission_yr = p_submission_yr AND
386 gsc.submission_number = p_submission_number
387 FOR UPDATE OF gsc.ess_snapshot_dt_time NOWAIT;
388 CURSOR c_gsc IS
389 SELECT gsc.ess_snapshot_dt_time
390 FROM IGS_ST_GVT_SPSHT_CTL gsc
391 WHERE gsc.submission_yr = p_submission_yr AND
392 gsc.submission_number = 2;
393 CURSOR c_essc IS
394 SELECT essc.snapshot_dt_time
395 FROM IGS_EN_ST_SPSHT_CTL essc
396 ORDER BY essc.snapshot_dt_time DESC;
397 CURSOR c_essc_upd (
398 cp_ess_snapshot_dt_time IGS_EN_ST_SPSHT_CTL.snapshot_dt_time%TYPE) IS
399 SELECT rowid, essc.*
400 FROM IGS_EN_ST_SPSHT_CTL essc
401 WHERE snapshot_dt_time = cp_ess_snapshot_dt_time
402 FOR UPDATE OF essc.delete_snapshot_ind NOWAIT;
403 CURSOR c_get_att_type IS
404 SELECT aty.attendance_type,
405 aty.govt_attendance_type
406 FROM IGS_EN_ATD_TYPE aty
407 WHERE aty.govt_attendance_type = 2 AND
408 aty.upper_enr_load_range > 0;
409 CURSOR c_get_att_mode_1 IS
410 SELECT atm.attendance_mode,
411 atm.govt_attendance_mode
412 FROM IGS_EN_ATD_MODE atm
413 WHERE atm.govt_attendance_mode = '1'
414 ORDER BY atm.attendance_mode ASC;
415 CURSOR c_get_att_mode_2 IS
416 SELECT atm.attendance_mode,
417 atm.govt_attendance_mode
418 FROM IGS_EN_ATD_MODE atm
419 WHERE atm.govt_attendance_mode = '2'
420 ORDER BY atm.attendance_mode ASC;
421 CURSOR c_get_att_mode_3 IS
422 SELECT atm.attendance_mode,
423 atm.govt_attendance_mode
424 FROM IGS_EN_ATD_MODE atm
425 WHERE atm.govt_attendance_mode = '3'
426 ORDER BY atm.attendance_mode ASC;
427 CURSOR c_enr_snpsht_rec (
428 cp_dt_time IGS_ST_GVT_SPSHT_CTL.ess_snapshot_dt_time%TYPE) IS
429 SELECT ess.ci_cal_type,
430 ess.ci_sequence_number,
431 ess.person_id,
432 ess.course_cd,
433 ess.crv_version_number,
434 ess.unit_cd,
435 ess.uv_version_number,
436 ess.sua_cal_type,
437 ess.sua_ci_sequence_number,
438 ess.tr_org_unit_cd,
439 ess.tr_ou_start_dt,
440 ess.discipline_group_cd,
441 ess.govt_discipline_group_cd,
442 ess.unit_class,
443 ess.enrolled_dt,
444 ess.discontinued_dt,
445 ess.eftsu,
446 ess.commencing_student_ind,
447 ct.award_course_ind,
448 uoo.uoo_id,
449 ess.sua_location_cd
450 FROM IGS_EN_ST_SNAPSHOT ess,
451 IGS_PS_TYPE ct,
452 igs_ps_unit_ofr_opt uoo
453 WHERE ess.snapshot_dt_time = cp_dt_time AND
454 ess.govt_reportable_ind <> 'X' AND
455 ct.course_type = ess.course_type AND
456 ess.unit_cd = uoo.unit_cd AND
457 ess.uv_version_number = uoo.version_number AND
458 ess.sua_cal_type = uoo.cal_type AND
459 ess.sua_ci_sequence_number = uoo.ci_sequence_number AND
460 ess.sua_location_cd = uoo.location_cd AND
461 ess.unit_class = uoo.unit_class
462 ORDER BY ess.person_id ASC,
463 ess.course_cd ASC,
464 ess.ci_cal_type ASC,
465 ess.ci_sequence_number ASC,
466 ess.sua_cal_type ASC,
467 ess.sua_ci_sequence_number ASC,
468 ess.unit_cd ASC;
469 CURSOR c_alias_val (
470 cp_teach_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
471 cp_teach_ci_seq_num IGS_CA_DA_INST_V.ci_sequence_number%TYPE) IS
472 SELECT daiv.alias_val
473 FROM IGS_CA_DA_INST_V daiv,
474 IGS_GE_S_GEN_CAL_CON sgcc
475 WHERE daiv.cal_type = cp_teach_cal_type AND
476 daiv.ci_sequence_number = cp_teach_ci_seq_num AND
477 daiv.dt_alias = sgcc.census_dt_alias
478 ORDER BY daiv.alias_val ASC;
479 CURSOR c_ci (
480 cp_teach_cal_type IGS_EN_ST_SNAPSHOT.sua_cal_type%TYPE,
481 cp_teach_ci_sequence_number
482 IGS_EN_ST_SNAPSHOT.sua_ci_sequence_number%TYPE) IS
483 SELECT ci.start_dt,
484 ci.end_dt
485 FROM IGS_CA_INST ci
486 WHERE ci.cal_type = cp_teach_cal_type AND
487 ci.sequence_number = cp_teach_ci_sequence_number;
488 CURSOR c_get_api (
489 cp_person_id IGS_PE_PERSON.person_id%TYPE,
490 cp_eff_dt DATE) IS
491 SELECT api.pe_person_id
492 FROM IGS_PE_ALT_PERS_ID api,
493 IGS_PE_PERSON_ID_TYP pit
494 WHERE api.api_person_id = TO_CHAR(cp_person_id) AND
495 pit.person_id_type = api.person_id_type AND
496 pit.s_person_id_type = 'OBSOLETE' AND
497 api.start_dt IS NOT NULL AND
498 api.start_dt <= cp_eff_dt AND
499 (api.end_dt IS NULL OR
500 api.end_dt >= cp_eff_dt) AND
501 (api.end_dt IS NULL OR
502 api.start_dt <> api.end_dt)
503 ORDER BY api.end_dt ASC;
504 CURSOR c_gse_att_mode IS
505 SELECT DISTINCT
506 gse.person_id,
507 gse.course_cd
508 FROM IGS_ST_GOVT_STDNT_EN gse
509 WHERE gse.submission_yr = p_submission_yr AND
510 gse.submission_number = p_submission_number;
511 CURSOR c_gslo (
512 cp_person_id IGS_ST_GVT_STDNTLOAD.person_id%TYPE,
513 cp_course_cd IGS_ST_GVT_STDNTLOAD.course_cd%TYPE) IS
514 SELECT gslo.unit_cd,
515 gslo.sua_cal_type,
516 gslo.sua_ci_sequence_number,
517 gslo.govt_semester,
518 gslo.tr_org_unit_cd,
519 gslo.tr_ou_start_dt,
520 gslo.discipline_group_cd,
521 gslo.govt_discipline_group_cd,
522 gslo.sua_location_cd,
523 gslo.unit_class
524 FROM IGS_ST_GVT_STDNTLOAD gslo
525 WHERE gslo.submission_yr = p_submission_yr AND
526 gslo.submission_number = p_submission_number AND
527 gslo.person_id = cp_person_id AND
528 gslo.course_cd = cp_course_cd AND
529 gslo.eftsu <> 0;
530 CURSOR c_sua_ucl_um (
531 cp_person_id IGS_ST_GOVT_STDNT_EN.person_id%TYPE,
532 cp_course_cd IGS_ST_GOVT_STDNT_EN.course_cd%TYPE,
533 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
534 cp_cal_type IGS_CA_TYPE.cal_type%TYPE,
535 cp_ci_seq_num IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
536 cp_location_cd IGS_EN_SU_ATTEMPT.location_cd%TYPE,
537 cp_unit_class IGS_EN_SU_ATTEMPT.unit_class%TYPE) IS
538 SELECT um.s_unit_mode
539 FROM IGS_EN_SU_ATTEMPT sua,
540 IGS_AS_UNIT_CLASS ucl,
541 IGS_AS_UNIT_MODE um
542 WHERE sua.person_id = cp_person_id AND
543 sua.course_cd = cp_course_cd AND
544 sua.unit_cd = cp_unit_cd AND
545 sua.cal_type = cp_cal_type AND
546 sua.ci_sequence_number = cp_ci_seq_num AND
547 sua.location_cd = cp_location_cd AND
548 sua.unit_class = cp_unit_class AND
549 ucl.unit_class = sua.unit_class AND
550 ucl.closed_ind = 'N' AND
551 um.unit_mode = ucl.unit_mode;
552 CURSOR c_att IS
553 SELECT att.attendance_type,
554 att.govt_attendance_type,
555 att.lower_enr_load_range
556 FROM IGS_EN_ATD_TYPE att
557 WHERE att.govt_attendance_type = 1 AND
558 att.lower_enr_load_range > 0;
559 CURSOR c_gse (
560 cp_lower_enr_load_range IGS_EN_ATD_TYPE.lower_enr_load_range%TYPE) IS
561 SELECT DISTINCT
562 gse.person_id
563 FROM IGS_ST_GOVT_STDNT_EN gse
564 WHERE gse.submission_yr = p_submission_yr AND
565 gse.submission_number = p_submission_number AND
566 cp_lower_enr_load_range <=
567 (SELECT SUM(gsl.eftsu)
568 FROM IGS_ST_GVT_STDNTLOAD gsl
569 WHERE gsl.submission_yr = p_submission_yr AND
570 gsl.submission_number = p_submission_number AND
571 gsl.person_id = gse.person_id);
572 -- Cursor for searching through ess records to find students
573 -- with more than one IGS_PS_COURSE
574 CURSOR c_gse2 IS
575 SELECT gse.person_id,
576 gse.course_cd
577 FROM IGS_ST_GOVT_STDNT_EN gse
578 WHERE gse.submission_yr = p_submission_yr AND
579 gse.submission_number = p_submission_number
580 ORDER BY
581 gse.person_id;
582 -- Called when a student is found more than once in gse
583 CURSOR c_gse_sca (
584 cp_person_id IGS_ST_GOVT_STDNT_EN.person_id%TYPE) IS
585 SELECT gse.person_id,
586 gse.course_cd,
587 SUM(gslo.eftsu)
588 FROM IGS_ST_GOVT_STDNT_EN gse,
589 IGS_ST_GVT_STDNTLOAD gslo,
590 IGS_EN_STDNT_PS_ATT sca
591 WHERE gse.submission_yr = p_submission_yr AND
592 gse.submission_number = p_submission_number AND
593 gse.person_id = cp_person_id AND
594 1 < (SELECT COUNT(DISTINCT gse2.course_cd)
595 FROM IGS_ST_GOVT_STDNT_EN gse2
596 WHERE gse2.submission_yr = p_submission_yr AND
597 gse2.submission_number = p_submission_number AND
598 gse2.person_id = cp_person_id) AND
599 sca.person_id = gse.person_id AND
600 sca.course_cd = gse.course_cd AND
601 gslo.submission_yr = gse.submission_yr AND
602 gslo.submission_number = gse.submission_number AND
603 gslo.person_id = gse.person_id AND
604 gslo.course_cd = gse.course_cd
605 GROUP BY gse.person_id,
606 gse.course_cd,
607 sca.commencement_dt
608 ORDER BY gse.person_id ASC,
609 SUM(gslo.eftsu) DESC,
610 sca.commencement_dt ASC;
611 CURSOR c_gsli_upd IS
612 SELECT rowid, gsli.*
613 FROM IGS_ST_GVT_STDNT_LBL gsli
614 WHERE submission_yr = p_submission_yr AND
615 submission_number = p_submission_number
616 FOR UPDATE OF gsli.last_updated_by NOWAIT;
617 CURSOR c_gsli_upd2 (
618 cp_person_id IGS_ST_GVT_STDNT_LBL.person_id%TYPE,
619 cp_course_cd IGS_ST_GVT_STDNT_LBL.course_cd%TYPE,
620 cp_govt_semester IGS_ST_GVT_STDNT_LBL.govt_semester%TYPE) IS
621 SELECT rowid, gsli.*
622 FROM IGS_ST_GVT_STDNT_LBL gsli
623 WHERE gsli.submission_yr = p_submission_yr AND
624 gsli.submission_number = p_submission_number AND
625 gsli.person_id = cp_person_id AND
626 gsli.course_cd = cp_course_cd AND
627 gsli.govt_semester = cp_govt_semester
628 FOR UPDATE OF gsli.last_updated_by NOWAIT;
629 CURSOR c_gslo_upd IS
630 SELECT rowid,gslo.*
631 FROM IGS_ST_GVT_STDNTLOAD gslo
632 WHERE submission_yr = p_submission_yr AND
633 submission_number = p_submission_number
634 FOR UPDATE OF gslo.last_updated_by NOWAIT;
635 CURSOR c_get_indus_ind (
636 cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
637 cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
638 SELECT uv.industrial_ind
639 FROM IGS_PS_UNIT_VER uv
640 WHERE uv.unit_cd = cp_unit_cd AND
641 uv.version_number = cp_version_number;
642 CURSOR c_update_total_eftsu IS
643 SELECT gslo.person_id,
644 gslo.course_cd,
645 gslo.govt_semester,
646 NVL(SUM(gslo.eftsu), 0) v_upd_total_eftsu
647 FROM IGS_ST_GVT_STDNTLOAD gslo
648 WHERE gslo.submission_yr = p_submission_yr AND
649 gslo.submission_number = p_submission_number
650 GROUP BY gslo.person_id,
651 gslo.course_cd,
652 gslo.govt_semester;
653 CURSOR c_update_indus_eftsu IS
654 SELECT gslo.person_id,
655 gslo.course_cd,
656 gslo.govt_semester,
657 NVL(SUM(gslo.eftsu), 0) v_upd_indus_eftsu
658 FROM IGS_ST_GVT_STDNTLOAD gslo
659 WHERE gslo.submission_yr = p_submission_yr AND
660 gslo.submission_number = p_submission_number AND
661 gslo.industrial_ind = 'Y'
662 GROUP BY gslo.person_id,
663 gslo.course_cd,
664 gslo.govt_semester;
665 CURSOR c_gse_upd IS
666 SELECT rowid,gse.*
667 FROM IGS_ST_GOVT_STDNT_EN gse
668 WHERE submission_yr = p_submission_yr AND
669 submission_number = p_submission_number
670 FOR UPDATE OF gse.last_updated_by NOWAIT;
671 CURSOR c_gse_upd2 (
672 cp_person_id IGS_ST_GOVT_STDNT_EN.person_id%TYPE) IS
673 SELECT rowid, gse.*
674 FROM IGS_ST_GOVT_STDNT_EN gse
675 WHERE submission_yr = p_submission_yr AND
676 submission_number = p_submission_number AND
677 person_id = cp_person_id
678 FOR UPDATE OF gse.last_updated_by NOWAIT;
679 CURSOR c_gse_upd3 (
680 cp_person_id IGS_ST_GOVT_STDNT_EN.person_id%TYPE,
681 cp_course_cd IGS_ST_GOVT_STDNT_EN.course_cd%TYPE) IS
682 SELECT rowid, gse.*
683 FROM IGS_ST_GOVT_STDNT_EN gse
684 WHERE gse.submission_yr = p_submission_yr AND
685 gse.submission_number = p_submission_number AND
686 gse.person_id = cp_person_id AND
687 gse.course_cd = cp_course_cd
688 FOR UPDATE OF gse.last_updated_by NOWAIT;
689 CURSOR c_gsli IS
690 SELECT UNIQUE
691 gsli.person_id,
692 gsli.course_cd,
693 gsli.version_number,
694 gsli.commencement_dt
695 FROM IGS_ST_GVT_STDNT_LBL gsli
696 WHERE gsli.submission_yr = p_submission_yr AND
697 gsli.submission_number = p_submission_number;
698 CURSOR c_gsli_upd_commencement (
699 cp_person_id IGS_ST_GVT_STDNT_LBL.person_id%TYPE,
700 cp_course_cd IGS_ST_GVT_STDNT_LBL.course_cd%TYPE) IS
701 SELECT rowid,gsli.*
702 FROM IGS_ST_GVT_STDNT_LBL gsli
703 WHERE gsli.submission_yr = p_submission_yr AND
704 gsli.submission_number = p_submission_number AND
705 gsli.person_id = cp_person_id AND
706 gsli.course_cd = cp_course_cd
707 FOR UPDATE OF gsli.commencement_dt NOWAIT;
708 CURSOR c_gse_enrolment IS
709 SELECT UNIQUE
710 gse.person_id,
711 gse.course_cd,
712 gse.version_number,
713 gse.commencement_dt
714 FROM IGS_ST_GOVT_STDNT_EN gse
715 WHERE gse.submission_yr = p_submission_yr AND
716 gse.submission_number = p_submission_number;
717 CURSOR c_gse_upd_commencement (
718 cp_person_id IGS_ST_GVT_STDNT_LBL.person_id%TYPE,
719 cp_course_cd IGS_ST_GVT_STDNT_LBL.course_cd%TYPE) IS
720 SELECT rowid,gse.*
721 FROM IGS_ST_GOVT_STDNT_EN gse
722 WHERE gse.submission_yr = p_submission_yr AND
723 gse.submission_number = p_submission_number AND
724 gse.person_id = cp_person_id AND
725 gse.course_cd = cp_course_cd
726 FOR UPDATE OF gse.commencement_dt NOWAIT;
727
728 PROCEDURE stapl_val_unit_compltn_status(
729 p_person_id IGS_EN_ST_SNAPSHOT.person_id%TYPE,
730 p_course_cd IGS_EN_ST_SNAPSHOT.course_cd%TYPE,
731 p_unit_cd IGS_EN_ST_SNAPSHOT.unit_cd%TYPE,
732 p_sua_cal_type IGS_EN_ST_SNAPSHOT.sua_cal_type%TYPE,
733 p_sua_ci_sequence_number IGS_EN_ST_SNAPSHOT.sua_ci_sequence_number%TYPE,
734 p_unit_completion_status OUT NOCOPY NUMBER,
735 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE)
736 AS
737 gv_other_detail VARCHAR2(255);
738 BEGIN
739 DECLARE
740 BEGIN
741 -- get the course UNIT completion status
742 p_unit_completion_status := stap_get_un_comp_sts(
743 p_person_id,
744 p_course_cd,
745 p_unit_cd,
746 p_sua_cal_type,
747 p_sua_ci_sequence_number,
748 p_uoo_id);
749 END;
750 EXCEPTION
751 WHEN OTHERS THEN
752 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
753 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stapl_val_unit_compltn_status');
754 IGS_GE_MSG_STACK.ADD;
755 App_Exception.Raise_Exception;
756 END stapl_val_unit_compltn_status;
757 PROCEDURE stapl_ins_govt_sdnt_load_rec(
758 p_sub_yr IGS_ST_GVT_STDNTLOAD.submission_yr%TYPE,
759 p_sub_number IGS_ST_GVT_STDNTLOAD.submission_number%TYPE,
760 p_person_id IGS_ST_GVT_STDNTLOAD.person_id%TYPE,
761 p_course_cd IGS_ST_GVT_STDNTLOAD.course_cd%TYPE,
762 p_crv_version_number IGS_ST_GVT_STDNTLOAD.crv_version_number%TYPE,
763 p_govt_semester IGS_ST_GVT_STDNTLOAD.govt_semester%TYPE,
764 p_unit_cd IGS_ST_GVT_STDNTLOAD.unit_cd%TYPE,
765 p_uv_version_number IGS_ST_GVT_STDNTLOAD.uv_version_number%TYPE,
766 p_sua_cal_type IGS_ST_GVT_STDNTLOAD.sua_cal_type%TYPE,
767 p_sua_ci_sequence_number IGS_ST_GVT_STDNTLOAD.sua_ci_sequence_number%TYPE,
768 p_tr_org_unit_cd IGS_ST_GVT_STDNTLOAD.tr_org_unit_cd%TYPE,
769 p_tr_ou_start_dt IGS_ST_GVT_STDNTLOAD.tr_ou_start_dt%TYPE,
770 p_discipline_group_cd IGS_ST_GVT_STDNTLOAD.discipline_group_cd%TYPE,
771 p_govt_discipline_group_cd IGS_ST_GVT_STDNTLOAD.govt_discipline_group_cd%TYPE,
772 p_industrial_ind IGS_ST_GVT_STDNTLOAD.industrial_ind%TYPE,
773 p_eftsu NUMBER,
774 p_unit_completion_status IGS_ST_GVT_STDNTLOAD.unit_completion_status%TYPE,
775 p_logged_ind IN OUT NOCOPY BOOLEAN,
776 p_s_log_type VARCHAR2,
777 p_creation_dt DATE,
778 p_sua_location_cd IN IGS_ST_GVT_STDNTLOAD.sua_location_cd%TYPE,
779 p_unit_class IN IGS_ST_GVT_STDNTLOAD.unit_class%TYPE)
780 AS
781 gv_other_detail VARCHAR2(255);
782 BEGIN
783 DECLARE
784 v_rowid VARCHAR2(25);
785 BEGIN
786 -- insert a record into IGS_ST_GVT_STDNTLOAD
787 -- to insert row using the insertrow of TBH package
788 IGS_ST_GVT_STDNTLOAD_PKG.INSERT_ROW(
789 X_ROWID => v_rowid,
790 X_SUBMISSION_YR => p_sub_yr,
791 X_SUBMISSION_NUMBER => p_sub_number,
792 X_PERSON_ID => p_person_id,
793 X_COURSE_CD => p_course_cd,
794 X_CRV_VERSION_NUMBER => p_crv_version_number,
795 X_GOVT_SEMESTER => p_govt_semester,
796 X_UNIT_CD => p_unit_cd,
797 X_UV_VERSION_NUMBER => p_uv_version_number,
798 X_SUA_CAL_TYPE => p_sua_cal_type,
799 X_SUA_CI_SEQUENCE_NUMBER => p_sua_ci_sequence_number,
800 X_TR_ORG_UNIT_CD => p_tr_org_unit_cd,
801 X_TR_OU_START_DT => p_tr_ou_start_dt,
802 X_DISCIPLINE_GROUP_CD => p_discipline_group_cd,
803 X_GOVT_DISCIPLINE_GROUP_CD => p_govt_discipline_group_cd,
804 X_INDUSTRIAL_IND => p_industrial_ind,
805 X_EFTSU => p_eftsu,
806 X_UNIT_COMPLETION_STATUS => p_unit_completion_status,
807 X_MODE => 'R',
808 X_SUA_LOCATION_CD => p_sua_location_cd,
809 X_UNIT_CLASS => p_unit_class);
810
811 IF p_eftsu > 1 THEN
812 --Check if an entry has been written to the error log
813 IF p_logged_ind = FALSE THEN
814
815 -- set that an error has been logged
816 p_logged_ind := TRUE;
817 END IF;
818 --Create an entry in the system log entry
819 IGS_GE_GEN_003.genp_ins_log_entry (
820 p_s_log_type,
821 p_creation_dt,
822 'IGS_PE_PERSON IGS_PS_COURSE IGS_PS_UNIT ' || ',' ||
823 TO_CHAR(p_person_id) || ',' ||
824 p_course_cd || ',' ||
825 p_unit_cd,
826 4221,
827 NULL);
828 END IF;
829 END;
830 EXCEPTION
831 WHEN OTHERS THEN
832 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
833 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stapl_ins_govt_sdnt_load_rec');
834 IGS_GE_MSG_STACK.ADD;
835 App_Exception.Raise_Exception;
836 END stapl_ins_govt_sdnt_load_rec;
837 PROCEDURE stapl_get_sca_data_for_liab(
838 p_current_person_id IGS_EN_ST_SNAPSHOT.person_id%TYPE,
839 -- current person_id
840 p_old_person_id IGS_EN_ST_SNAPSHOT.person_id%TYPE,
841 -- last person_id retrieved
842 p_current_course_cd IGS_EN_ST_SNAPSHOT.course_cd%TYPE,
843 -- current course_cd
844 p_old_course_cd IGS_EN_ST_SNAPSHOT.course_cd%TYPE,
845 -- last course_cdretrieved
846 p_effective_dt DATE,
847 p_logged_ind IN OUT NOCOPY BOOLEAN,
848 p_s_log_type VARCHAR2,
849 p_creation_dt IGS_GE_S_LOG.creation_dt%TYPE,
850 p_birth_dt IN OUT NOCOPY IGS_ST_GOVT_STDNT_EN.birth_dt%TYPE,
851 p_sex IN OUT NOCOPY IGS_ST_GOVT_STDNT_EN.sex%TYPE,
852 p_citizenship_cd IN OUT NOCOPY IGS_ST_GOVT_STDNT_EN.citizenship_cd%TYPE,
853 p_govt_citizenship_cd IN OUT NOCOPY IGS_ST_GOVT_STDNT_EN.govt_citizenship_cd%TYPE,
854 p_perm_resident_cd IN OUT NOCOPY IGS_ST_GOVT_STDNT_EN.perm_resident_cd%TYPE,
855 p_govt_perm_resident_cd IN OUT NOCOPY
856 IGS_ST_GOVT_STDNT_EN.govt_perm_resident_cd%TYPE)
857 AS
858 gv_other_detail VARCHAR2(255);
859 GE_SCA_NOTFOUND EXCEPTION;
860 BEGIN
861 DECLARE
862 v_other_detail VARCHAR2(255);
863 v_current_log_ind BOOLEAN;
864 CURSOR c_get_person_dtls IS
865 SELECT pe.birth_dt,
866 pe.sex
867 FROM IGS_PE_PERSON pe
868 WHERE pe.person_id = p_current_person_id;
869 CURSOR c_prsn_stats IS
870 SELECT ps.citizenship_cd,
871 ps.perm_resident_cd
872 FROM IGS_PE_STATISTICS ps
873 WHERE ps.person_id = p_current_person_id AND
874 ps.start_dt <= p_effective_dt AND
875 (ps.end_dt IS NULL OR
876 ps.end_dt >= p_effective_dt)
877 ORDER BY ps.end_dt ASC;
878 CURSOR c_citz IS
879 SELECT ccd.govt_citizenship_cd
880 FROM IGS_ST_CITIZENSHP_CD ccd
881 WHERE ccd.citizenship_cd = p_citizenship_cd;
882 CURSOR c_perm_res (
883 cp_perm_res_cd IGS_PE_PERM_RES_CD.perm_resident_cd%TYPE) IS
884 SELECT prcd.govt_perm_resident_cd
885 FROM IGS_PE_PERM_RES_CD prcd
886 WHERE prcd.perm_resident_cd = cp_perm_res_cd;
887 BEGIN
888 -- only get the IGS_PE_PERSON data if the IGS_PE_PERSON has changed
889 IF p_old_person_id IS NULL OR
890 p_old_person_id <> p_current_person_id THEN
891 p_birth_dt := NULL;
892 p_sex := NULL;
893 p_citizenship_cd := NULL;
894 p_govt_citizenship_cd := NULL;
895 p_perm_resident_cd := NULL;
896 p_govt_perm_resident_cd := NULL;
897 -- get the IGS_PE_PERSON data
898 OPEN c_get_person_dtls;
899 FETCH c_get_person_dtls INTO p_birth_dt,
900 p_sex;
901 -- raise an exception if no IGS_PE_PERSON record found
902 IF (c_get_person_dtls%NOTFOUND) THEN
903 CLOSE c_get_person_dtls;
904 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
905 IGS_GE_MSG_STACK.ADD;
906 App_Exception.Raise_Exception;
907 END IF;
908 CLOSE c_get_person_dtls;
909 IF (p_birth_dt IS NULL) THEN
910 IF (p_logged_ind = FALSE) THEN
911 -- log an error to the IGS_GE_S_ERROR_LOG
912 -- using IGS_GE_GEN_003.genp_log_error
913
914 -- set that an error has been logged
915 p_logged_ind := TRUE;
916 END IF;
917 -- create an entry in the system log entry
918 IGS_GE_GEN_003.genp_ins_log_entry (
919 p_s_log_type,
920 p_creation_dt,
921 'IGS_PE_PERSON' || ',' ||
922 TO_CHAR(p_current_person_id),
923 4194,
924 NULL);
925 END IF;
926 -- retrieve the IGS_PE_PERSON statistics data
927 -- get the first record only, which will
928 -- be the end dated record if one exists
929 OPEN c_prsn_stats;
930 FETCH c_prsn_stats INTO p_citizenship_cd,
931 p_perm_resident_cd;
932 -- raise a user exception if no record exists
933 IF (c_prsn_stats%NOTFOUND) THEN
934 CLOSE c_prsn_stats;
935 IF (p_logged_ind = FALSE) THEN
936 -- log an error to the IGS_GE_S_ERROR_LOG
937 -- using IGS_GE_GEN_003.genp_log_error
938
939 p_logged_ind := TRUE;
940 END IF;
941 -- create an entry in the system log entry
942 IGS_GE_GEN_003.genp_ins_log_entry (
943 p_s_log_type,
944 p_creation_dt,
945 'IGS_PE_PERSON' || ',' || TO_CHAR(p_current_person_id),
946 4196,
947 NULL);
948 ELSE
949 CLOSE c_prsn_stats;
950 END IF;
951 -- reset statistics values and set the government values
952 -- citizenship code
953 IF (p_citizenship_cd IS NOT NULL) THEN
954 -- a record will always exist because of
955 -- referential integrity
956 OPEN c_citz;
957 FETCH c_citz INTO p_govt_citizenship_cd;
958 CLOSE c_citz;
959 ELSE
960 p_citizenship_cd := '9';
961 p_govt_citizenship_cd := 9;
962 IF (p_logged_ind = FALSE) THEN
963 -- log an error to the IGS_GE_S_ERROR_LOG
964 -- using IGS_GE_GEN_003.genp_log_error
965
966 p_logged_ind := TRUE;
967 END IF;
968 -- create an entry in the system log entry
969 IGS_GE_GEN_003.genp_ins_log_entry (
970 p_s_log_type,
971 p_creation_dt,
972 'IGS_PE_PERSON' || ',' || TO_CHAR(p_current_person_id),
973 4202,
974 NULL);
975 END IF;
976 -- permanent resident code
977 IF (p_perm_resident_cd IS NOT NULL) THEN
978 -- a record will always exist because of
979 -- referential integrity
980 OPEN c_perm_res(p_perm_resident_cd);
981 FETCH c_perm_res INTO p_govt_perm_resident_cd;
982 CLOSE c_perm_res;
983 ELSE
984 IF p_govt_citizenship_cd in (2, 3, 9) then
985 p_perm_resident_cd := '9';
986 p_govt_perm_resident_cd := 9;
987 IF (p_logged_ind = FALSE) THEN
988 -- log an error to the IGS_GE_S_ERROR_LOG
989 -- using IGS_GE_GEN_003.genp_log_error
990
991 p_logged_ind := TRUE;
992 END IF;
993 -- create an entry in the system log entry
994 IGS_GE_GEN_003.genp_ins_log_entry (
995 p_s_log_type,
996 p_creation_dt,
997 'IGS_PE_PERSON' || ',' ||
998 TO_CHAR(p_current_person_id),
999 4203,
1000 NULL);
1001 ELSE
1002 p_perm_resident_cd := '0';
1003 p_govt_perm_resident_cd := 0;
1004 IF (p_logged_ind = FALSE) THEN
1005 -- log an error to the IGS_GE_S_ERROR_LOG
1006 -- using IGS_GE_GEN_003.genp_log_error
1007
1008 -- set that an error has been logged
1009 p_logged_ind := TRUE;
1010 END IF;
1011 -- create an entry in the system log entry
1012 IGS_GE_GEN_003.genp_ins_log_entry (
1013 p_s_log_type,
1014 p_creation_dt,
1015 'IGS_PE_PERSON' || ',' ||
1016 TO_CHAR(p_current_person_id),
1017 4644,
1018 NULL);
1019 END IF;
1020 END IF;
1021 END IF;
1022 EXCEPTION
1023 WHEN GE_SCA_NOTFOUND THEN
1024 IF c_get_person_dtls%ISOPEN THEN
1025 CLOSE c_get_person_dtls;
1026 END IF;
1027 IF c_prsn_stats%ISOPEN THEN
1028 CLOSE c_prsn_stats;
1029 END IF;
1030 IF c_citz%ISOPEN THEN
1031 CLOSE c_citz;
1032 END IF;
1033 IF c_perm_res%ISOPEN THEN
1034 CLOSE c_perm_res;
1035 END IF;
1036 v_other_detail := 'Cannot find student IGS_PS_COURSE attempt data- ' ||
1037 TO_CHAR(p_current_person_id);
1038 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1039 IGS_GE_MSG_STACK.ADD;
1040
1041 App_Exception.Raise_Exception;
1042 WHEN OTHERS THEN
1043 IF c_get_person_dtls%ISOPEN THEN
1044 CLOSE c_get_person_dtls;
1045 END IF;
1046 IF c_prsn_stats%ISOPEN THEN
1047 CLOSE c_prsn_stats;
1048 END IF;
1049 IF c_citz%ISOPEN THEN
1050 CLOSE c_citz;
1051 END IF;
1052 IF c_perm_res%ISOPEN THEN
1053 CLOSE c_perm_res;
1054 END IF;
1055 RAISE;
1056 END;
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1060 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stapl_get_sca_data_for_liab');
1061 IGS_GE_MSG_STACK.ADD;
1062 App_Exception.Raise_Exception;
1063 END stapl_get_sca_data_for_liab;
1064 PROCEDURE stapl_ins_govt_sdnt_liab_rec (
1065 p_sub_yr IGS_ST_GVT_STDNT_LBL.submission_yr%TYPE,
1066 p_sub_number IGS_ST_GVT_STDNT_LBL.submission_number%TYPE,
1067 p_person_id IGS_ST_GVT_STDNT_LBL.person_id%TYPE,
1068 p_course_cd IGS_ST_GVT_STDNT_LBL.course_cd%TYPE,
1069 p_version_number IGS_ST_GVT_STDNT_LBL.version_number%TYPE,
1070 p_govt_semester IGS_ST_GVT_STDNT_LBL.govt_semester%TYPE,
1071 p_hecs_payment_option IGS_ST_GVT_STDNT_LBL.hecs_payment_option%TYPE,
1072 p_govt_hpo IGS_ST_GVT_STDNT_LBL.govt_hecs_payment_option%TYPE,
1073 p_hecs_amount_pd IGS_ST_GVT_STDNT_LBL.hecs_amount_paid%TYPE,
1074 p_tuition_fee IGS_ST_GVT_STDNT_LBL.tuition_fee%TYPE,
1075 p_differential_hecs_ind IGS_ST_GVT_STDNT_LBL.differential_hecs_ind%TYPE,
1076 p_birth_dt IGS_ST_GVT_STDNT_LBL.birth_dt%TYPE,
1077 p_sex IGS_ST_GVT_STDNT_LBL.sex%TYPE,
1078 p_citizenship_cd IGS_ST_GVT_STDNT_LBL.citizenship_cd%TYPE,
1079 p_govt_citizenship_cd IGS_ST_GVT_STDNT_LBL.govt_citizenship_cd%TYPE,
1080 p_perm_resident_cd IGS_ST_GVT_STDNT_LBL.perm_resident_cd%TYPE,
1081 p_govt_perm_resident_cd IGS_ST_GVT_STDNT_LBL.govt_perm_resident_cd%TYPE,
1082 p_commencement_dt IGS_ST_GVT_STDNT_LBL.commencement_dt%TYPE,
1083 p_hecs_fee IGS_ST_GVT_STDNT_LBL.hecs_prexmt_exie%TYPE)
1084 AS
1085 gv_other_detail VARCHAR2(255);
1086 BEGIN
1087 DECLARE
1088 v_other_detail VARCHAR2(255);
1089 v_rowid VARCHAR2(25);
1090 BEGIN
1091 -- create the student liability record
1092 IF ((p_sub_number = 1 AND
1093 p_govt_semester IN (1, 3, 5)) OR
1094 (p_sub_number = 2 AND
1095 p_govt_semester IN (2, 4))) THEN
1096 -- insert liability record
1097 BEGIN
1098
1099 -- to insert row using the insert row of the respective TBH package
1100
1101 IGS_ST_GVT_STDNT_LBL_PKG.INSERT_ROW(
1102 X_ROWID => v_rowid,
1103 X_SUBMISSION_YR => p_sub_yr,
1104 X_SUBMISSION_NUMBER => p_sub_number,
1105 X_PERSON_ID => p_person_id,
1106 X_COURSE_CD => p_course_cd,
1107 X_VERSION_NUMBER => p_version_number,
1108 X_GOVT_SEMESTER => p_govt_semester,
1109 X_HECS_PAYMENT_OPTION => p_hecs_payment_option,
1110 X_GOVT_HECS_PAYMENT_OPTION => p_govt_hpo,
1111 X_TOTAL_EFTSU => 0,
1112 X_INDUSTRIAL_EFTSU => 0,
1113 X_HECS_PREXMT_EXIE => p_hecs_fee,
1114 X_HECS_AMOUNT_PAID => p_hecs_amount_pd,
1115 X_TUITION_FEE => p_tuition_fee,
1116 X_DIFFERENTIAL_HECS_IND => p_differential_hecs_ind,
1117 X_BIRTH_DT => p_birth_dt,
1118 X_SEX => p_sex,
1119 X_CITIZENSHIP_CD => p_citizenship_cd,
1120 X_GOVT_CITIZENSHIP_CD => p_govt_citizenship_cd,
1121 X_PERM_RESIDENT_CD => p_perm_resident_cd,
1122 X_GOVT_PERM_RESIDENT_CD => p_govt_perm_resident_cd,
1123 X_COMMENCEMENT_DT => p_commencement_dt,
1124 X_MODE => 'R' );
1125
1126 EXCEPTION
1127 WHEN DUP_VAL_ON_INDEX THEN
1128 -- don't raise an exception, just handle it
1129 NULL;
1130 END;
1131 END IF;
1132 END;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1136 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stapl_ins_govt_sdnt_liab_rec');
1137 IGS_GE_MSG_STACK.ADD;
1138 App_Exception.Raise_Exception;
1139 END stapl_ins_govt_sdnt_liab_rec;
1140
1141 BEGIN -- main
1142 -- This process is used to create a snapshot of
1143 -- data to be reported to the government for a
1144 -- specified submission year and number
1145
1146 v_start_dt_time := SYSDATE;
1147 -- validate the input parameters
1148 OPEN c_govt_snpsht_ctl;
1149 FETCH c_govt_snpsht_ctl INTO v_sub_yr;
1150 IF c_govt_snpsht_ctl%NOTFOUND THEN
1151 CLOSE c_govt_snpsht_ctl;
1152 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1153 RETURN FALSE;
1154 END IF;
1155 CLOSE c_govt_snpsht_ctl;
1156 -- an enrolment statistics snapshot is only
1157 -- required for submissions 1 and 2
1158 IF (p_submission_number IN (1, 2) AND
1159 (p_ess_snapshot_dt_time IS NULL AND
1160 (p_use_most_recent_ess_ind IS NULL OR
1161 p_use_most_recent_ess_ind <> 'Y'))) THEN
1162 p_message_name := 'IGS_ST_ENRL_STAT_REQUIRED';
1163 RETURN FALSE;
1164 END IF;
1165 -- initialise the system log variables
1166 -- this flag is used to indicate whether or not an entry has
1167 -- been written to the IGS_GE_S_ERROR_LOG table (using IGS_GE_GEN_003.genp_log_error),
1168 -- stating that entries have been written to the system log
1169 v_logged_ind := FALSE;
1170 v_s_log_type := 'GOVT-SBMSN';
1171 -- check if the submission is complete
1172 IF NOT IGS_ST_VAL_GSC.stap_val_gsc_sdt_upd(
1173 p_submission_yr,
1174 p_submission_number,
1175 v_message_name) THEN
1176 p_message_name := v_message_name;
1177 RETURN FALSE;
1178 END IF;
1179 -- determine which enrolment statistics snapshot to use
1180 IF p_submission_number IN (1, 2) THEN
1181 IF p_ess_snapshot_dt_time IS NOT NULL THEN
1182 v_ess_snapshot_dt_time := p_ess_snapshot_dt_time;
1183 ELSIF p_use_most_recent_ess_ind = 'Y' THEN
1184 -- determine the most recent enrolment
1185 -- statistics snapshot
1186 -- select the first record only
1187 OPEN c_essc;
1188 FETCH c_essc INTO v_ess_snapshot_dt_time;
1189 IF c_essc%NOTFOUND THEN
1190 CLOSE c_essc;
1191 p_message_name := 'IGS_ST_COULD_NOT_DET_ENR_STAT';
1192 RETURN FALSE;
1193 END IF;
1194 CLOSE c_essc;
1195 ELSE
1196 p_message_name := 'IGS_ST_COULD_NOT_DET_ENR_STAT';
1197 RETURN FALSE;
1198 END IF;
1199 -- validate the enrolment statistics snapshot to be used
1200 IF NOT IGS_ST_VAL_GSC.stap_val_gsc_sdt (
1201 p_submission_yr,
1202 v_ess_snapshot_dt_time,
1203 v_message_name) THEN
1204 IF v_message_name = 'IGS_ST_CANT_USE+ENRL_STATIST' THEN
1205 -- the enrolment statistics snapshot
1206 -- is marked for delete. Update the
1207 -- delete snapshot indicator
1208 BEGIN
1209 FOR v_essc_upd_rec IN c_essc_upd(v_ess_snapshot_dt_time) LOOP
1210
1211 v_essc_upd_rec.delete_snapshot_ind := 'N';
1212
1213 IGS_EN_ST_SPSHT_CTL_PKG.UPDATE_ROW(
1214 X_ROWID => v_essc_upd_rec.rowid,
1215 X_SNAPSHOT_DT_TIME => v_essc_upd_rec.snapshot_dt_time,
1216 X_DELETE_SNAPSHOT_IND => v_essc_upd_rec.delete_snapshot_ind,
1217 X_COMMENTS => v_essc_upd_rec.comments,
1218 X_MODE => 'R');
1219
1220 END LOOP;
1221 EXCEPTION
1222 WHEN e_resource_busy THEN
1223 p_message_name := 'IGS_GR_CER_CL_DT_LE_CERM_DT';
1224 RETURN FALSE;
1225 WHEN OTHERS THEN
1226 RAISE;
1227 END;
1228 COMMIT;
1229
1230 ELSE
1231 p_message_name := v_message_name;
1232 RETURN FALSE;
1233 END IF;
1234 END IF;
1235 -- update the enrolment statistics snapshot date time
1236 -- for the government snapshot
1237 BEGIN
1238 FOR v_gsc_upd_rec IN c_gsc_upd LOOP
1239
1240 v_gsc_upd_rec.ess_snapshot_dt_time := v_ess_snapshot_dt_time;
1241 IGS_ST_GVT_SPSHT_CTL_PKG.UPDATE_ROW(
1242 X_ROWID => v_gsc_upd_rec.rowid,
1243 X_SUBMISSION_YR => v_gsc_upd_rec.submission_yr,
1244 X_SUBMISSION_NUMBER => v_gsc_upd_rec.submission_number,
1245 X_ESS_SNAPSHOT_DT_TIME => v_gsc_upd_rec.ess_snapshot_dt_time,
1246 X_COMPLETION_DT => v_gsc_upd_rec.completion_dt,
1247 X_MODE => 'R');
1248 END LOOP;
1249 EXCEPTION
1250 WHEN e_resource_busy THEN
1251 p_message_name := 'IGS_ST_ENR_STAT_DT_TIM_NOT_UP';
1252 RETURN FALSE;
1253 WHEN OTHERS THEN
1254 RAISE;
1255 END;
1256 COMMIT;
1257
1258 ELSIF p_submission_number = 3 THEN
1259 OPEN c_gsc;
1260 FETCH c_gsc INTO v_ess_snapshot_dt_time;
1261 CLOSE c_gsc;
1262 BEGIN
1263 FOR v_gsc_upd_rec IN c_gsc_upd LOOP
1264
1265 v_gsc_upd_rec.ess_snapshot_dt_time := v_ess_snapshot_dt_time;
1266 IGS_ST_GVT_SPSHT_CTL_PKG.UPDATE_ROW(
1267 X_ROWID => v_gsc_upd_rec.rowid,
1268 X_SUBMISSION_YR => v_gsc_upd_rec.submission_yr,
1269 X_SUBMISSION_NUMBER => v_gsc_upd_rec.submission_number,
1270 X_ESS_SNAPSHOT_DT_TIME => v_gsc_upd_rec.ess_snapshot_dt_time,
1271 X_COMPLETION_DT => v_gsc_upd_rec.completion_dt,
1272 X_MODE => 'R');
1273
1274 END LOOP;
1275 EXCEPTION
1276 WHEN e_resource_busy THEN
1277 p_message_name := 'IGS_ST_ENR_STAT_DT_TIM_NOT_UP';
1278 RETURN FALSE;
1279 WHEN OTHERS THEN
1280 RAISE;
1281 END;
1282 COMMIT;
1283
1284 END IF;
1285 -- remove all existing government snapshot detail
1286 -- records
1287 BEGIN
1288 FOR v_gsli_upd_rec IN c_gsli_upd LOOP
1289
1290 IGS_ST_GVT_STDNT_LBL_PKG.DELETE_ROW(
1291 X_ROWID => v_gsli_upd_rec.rowid);
1292
1293 END LOOP;
1294 EXCEPTION
1295 WHEN e_resource_busy THEN
1296 p_message_name := 'IGS_ST_GOV_STUD_REC_CANT_DEL';
1297 RETURN FALSE;
1298 WHEN OTHERS THEN
1299 RAISE;
1300 END;
1301 BEGIN
1302 FOR v_gslo_upd_rec IN c_gslo_upd LOOP
1303
1304 IGS_ST_GVT_STDNTLOAD_PKG.DELETE_ROW(
1305 X_ROWID => v_gslo_upd_rec.rowid);
1306
1307 END LOOP;
1308 EXCEPTION
1309 WHEN e_resource_busy THEN
1310 p_message_name := 'IGS_ST_GOV_STUD_LOAD_CANT_DEL';
1311 RETURN FALSE;
1312 WHEN OTHERS THEN
1313 RAISE;
1314 END;
1315 BEGIN
1316 FOR v_gse_upd_rec IN c_gse_upd LOOP
1317
1318 IGS_ST_GOVT_STDNT_EN_PKG.DELETE_ROW(
1319 X_ROWID => v_gse_upd_rec.rowid);
1320 END LOOP;
1321 EXCEPTION
1322 WHEN e_resource_busy THEN
1323 p_message_name := 'IGS_ST_GOV_ENRL_REC_CANT_DEL';
1324 RETURN FALSE;
1325 WHEN OTHERS THEN
1326 RAISE;
1327 END;
1328 COMMIT;
1329
1330 -- Create an entry in the system log.
1331 IGS_GE_GEN_003.genp_ins_log (
1332 v_s_log_type,
1333 TO_CHAR(p_submission_yr) ||
1334 ' ' ||
1335 TO_CHAR(p_submission_number) ||
1336 ',' ||
1337 IGS_GE_DATE.igscharDT(v_ess_snapshot_dt_time), -- Key
1338 v_creation_dt); -- Output parameter. Needed for creating entries.
1339 p_log_creation_dt := v_creation_dt;
1340 -- for submissions 1 and 2, retrieve data from the
1341 -- enrolment statistics snapshot
1342 IF (p_submission_number IN (1, 2)) THEN
1343 -- determine and set the effective date
1344 IF (p_submission_number = 1) THEN
1345 v_effective_dt := v_submission_1_census_dt;
1346 ELSE -- submission 2
1347 v_effective_dt := v_submission_2_census_dt;
1348 END IF;
1349 -- set the default attendance type
1350 OPEN c_get_att_type;
1351 FETCH c_get_att_type INTO v_attendance_type,
1352 v_govt_attendance_type;
1353 IF (c_get_att_type%NOTFOUND) THEN
1354 CLOSE c_get_att_type;
1355 gv_other_detail := 'Parm:'
1356 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1357 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1358 || ', p_dt_time- ' ||
1359 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1360 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind;
1361 Fnd_Message.Set_Name('IGS',v_message_name);
1362 IGS_GE_MSG_STACK.ADD;
1363 App_Exception.Raise_Exception;
1364 END IF;
1365 CLOSE c_get_att_type;
1366 -- set the attendance mode values
1367 -- retrieve the first record only from each
1368 -- select (there are three different selects
1369 -- as different details are retrieved depending
1370 -- on the govt_attendance_mode specified)
1371 -- select the first record only
1372 -- internal attendance mode
1373 OPEN c_get_att_mode_1;
1374 FETCH c_get_att_mode_1 INTO v_attendance_mode_1,
1375 v_govt_attendance_mode_1;
1376 IF (c_get_att_mode_1%NOTFOUND) THEN
1377 CLOSE c_get_att_mode_1;
1378 gv_other_detail := 'Parm:'
1379 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1380 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1381 || ', p_dt_time- ' ||
1382 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1383 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind;
1384 Fnd_Message.Set_Name('IGS',v_message_name);
1385 IGS_GE_MSG_STACK.ADD;
1386 App_Exception.Raise_Exception;
1387 END IF;
1388 CLOSE c_get_att_mode_1;
1389 -- external attendance mode
1390 OPEN c_get_att_mode_2;
1391 FETCH c_get_att_mode_2 INTO v_attendance_mode_2,
1392 v_govt_attendance_mode_2;
1393 IF (c_get_att_mode_2%NOTFOUND) THEN
1394 CLOSE c_get_att_mode_2;
1395 gv_other_detail := 'Parm:'
1396 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1397 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1398 || ', p_dt_time- ' ||
1399 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1400 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind;
1401 Fnd_Message.Set_Name('IGS',v_message_name);
1402 IGS_GE_MSG_STACK.ADD;
1403 App_Exception.Raise_Exception;
1404 END IF;
1405 CLOSE c_get_att_mode_2;
1406 -- multi-modal attendance mode
1407 OPEN c_get_att_mode_3;
1408 FETCH c_get_att_mode_3 INTO v_attendance_mode_3,
1409 v_govt_attendance_mode_3;
1410 IF (c_get_att_mode_3%NOTFOUND) THEN
1411 CLOSE c_get_att_mode_3;
1412 gv_other_detail := 'Parm:'
1413 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1414 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1415 || ', p_dt_time- ' ||
1416 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1417 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind;
1418 Fnd_Message.Set_Name('IGS',v_message_name);
1419 IGS_GE_MSG_STACK.ADD;
1420
1421 App_Exception.Raise_Exception;
1422 END IF;
1423 CLOSE c_get_att_mode_3;
1424
1425
1426 -- retrieve the enrolment statistics snapshot data
1427
1428 FOR v_enr_snpsht_rec IN c_enr_snpsht_rec(
1429 v_ess_snapshot_dt_time) LOOP
1430 -- setting that a record has been found
1431 v_ess_rec_found := TRUE;
1432 -- checking if the values of the cursor are
1433 -- the same as the previous ones retrieved,
1434 -- as the government semester only needs to
1435 -- be re-determined if they have changed
1436 IF v_load_cal_type IS NULL OR
1437 v_load_cal_type <> v_enr_snpsht_rec.ci_cal_type OR
1438 v_load_ci_sequence_number IS NULL OR
1439 v_load_ci_sequence_number <> v_enr_snpsht_rec.ci_sequence_number OR
1440 v_teach_cal_type IS NULL OR
1441 v_teach_cal_type <> v_enr_snpsht_rec.sua_cal_type THEN
1442 -- retrieve the government semester
1443 v_govt_semester := IGS_ST_GEN_002.stap_get_govt_sem(
1444 p_submission_yr,
1445 p_submission_number,
1446 v_enr_snpsht_rec.ci_cal_type,
1447 v_enr_snpsht_rec.ci_sequence_number,
1448 v_enr_snpsht_rec.sua_cal_type);
1449 IF (v_govt_semester IS NULL) THEN
1450 OPEN c_ci(
1451 v_enr_snpsht_rec.ci_cal_type,
1452 v_enr_snpsht_rec.ci_sequence_number);
1453 FETCH c_ci INTO v_start_dt,
1454 v_end_dt;
1455 CLOSE c_ci;
1456 gv_extra_details := 'Cannot determine the Government Semester for - '
1457 || v_enr_snpsht_rec.ci_cal_type || ', '
1458 || IGS_GE_DATE.igschar(v_start_dt) || ' - '
1459 || IGS_GE_DATE.igschar(v_end_dt) || ', '
1460 || v_enr_snpsht_rec.sua_cal_type;
1461 gv_other_detail := 'Parm:'
1462 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1463 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1464 || ', p_ess_snapshot_dt_time- ' ||
1465 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1466 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind || ' '
1467 || gv_extra_details;
1468 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1469 IGS_GE_MSG_STACK.ADD;
1470
1471 App_Exception.Raise_Exception;
1472 END IF;
1473 END IF;
1474 IF (p_submission_number = 1 OR
1475 (p_submission_number = 2 AND
1476 (v_govt_semester IN(2, 4)))) THEN
1477 -- retrieve the teaching calendar census date
1478 -- select the first record only
1479 IF v_teach_cal_type IS NULL OR
1480 v_teach_cal_type <> v_enr_snpsht_rec.sua_cal_type OR
1481 v_teach_ci_sequence_number IS NULL OR
1482 v_teach_ci_sequence_number <> v_enr_snpsht_rec.sua_ci_sequence_number THEN
1483 OPEN c_alias_val(
1484 v_enr_snpsht_rec.sua_cal_type,
1485 v_enr_snpsht_rec.sua_ci_sequence_number);
1486 FETCH c_alias_val INTO v_alias_val;
1487 -- if no records found, raise a user exception
1488 IF (c_alias_val%NOTFOUND) THEN
1489 CLOSE c_alias_val;
1490 OPEN c_ci(
1491 v_enr_snpsht_rec.sua_cal_type,
1492 v_enr_snpsht_rec.sua_ci_sequence_number);
1493 FETCH c_ci INTO v_start_dt,
1494 v_end_dt;
1495 CLOSE c_ci;
1496 gv_extra_details := 'Cannot determine the Teaching Census Date for - '
1497 || v_enr_snpsht_rec.sua_cal_type || ', '
1498 || IGS_GE_DATE.igschar(v_start_dt) || ' - '
1499 || IGS_GE_DATE.igschar(v_end_dt);
1500 gv_other_detail := 'Parm:'
1501 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1502 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1503 || ', p_ess_snapshot_dt_time- ' ||
1504 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1505 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind || ' '
1506 || gv_extra_details;
1507 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1508 IGS_GE_MSG_STACK.ADD;
1509 App_Exception.Raise_Exception;
1510 END IF;
1511 CLOSE c_alias_val;
1512 END IF;
1513 -- determine if the record is government
1514 -- reportable for the submission
1515 v_govt_reportable := IGS_ST_GEN_003.stap_get_rptbl_sbmsn(
1516 p_submission_yr,
1517 p_submission_number,
1518 v_enr_snpsht_rec.person_id,
1519 v_enr_snpsht_rec.course_cd,
1520 v_enr_snpsht_rec.crv_version_number,
1521 v_enr_snpsht_rec.unit_cd,
1522 v_enr_snpsht_rec.uv_version_number,
1523 v_enr_snpsht_rec.sua_cal_type,
1524 v_enr_snpsht_rec.sua_ci_sequence_number,
1525 v_enr_snpsht_rec.tr_org_unit_cd,
1526 v_enr_snpsht_rec.tr_ou_start_dt,
1527 v_enr_snpsht_rec.eftsu,
1528 v_enr_snpsht_rec.enrolled_dt,
1529 v_enr_snpsht_rec.discontinued_dt,
1530 v_govt_semester,
1531 v_alias_val,
1532 v_enr_snpsht_rec.ci_cal_type,
1533 v_enr_snpsht_rec.ci_sequence_number,
1534 v_enr_snpsht_rec.uoo_id);
1535 -- check the value of gv_govt_reportable
1536 IF v_govt_reportable IS NULL THEN
1537 OPEN c_ci(
1538 v_enr_snpsht_rec.sua_cal_type,
1539 v_enr_snpsht_rec.sua_ci_sequence_number);
1540 FETCH c_ci INTO v_start_dt,
1541 v_end_dt;
1542 CLOSE c_ci;
1543 OPEN c_ci(
1544 v_enr_snpsht_rec.ci_cal_type,
1545 v_enr_snpsht_rec.ci_sequence_number);
1546 FETCH c_ci INTO v_start_dt_2,
1547 v_end_dt_2;
1548 CLOSE c_ci;
1549 gv_extra_details := 'Cannot determine the government '
1550 || 'submission reportable value. '
1551 || p_submission_yr || ', '
1552 || TO_CHAR(p_submission_number) || ', '
1553 || TO_CHAR(v_enr_snpsht_rec.person_id) || ', '
1554 || v_enr_snpsht_rec.course_cd || ', '
1555 || TO_CHAR(v_enr_snpsht_rec.crv_version_number) || ', '
1556 || v_enr_snpsht_rec.unit_cd || ', '
1557 || TO_CHAR(v_enr_snpsht_rec.uv_version_number) || ', '
1558 || v_enr_snpsht_rec.sua_cal_type || ', '
1559 || IGS_GE_DATE.igschar(v_start_dt) || ' - '
1560 || IGS_GE_DATE.igschar(v_end_dt) || ', '
1561 || v_enr_snpsht_rec.tr_org_unit_cd || ', '
1562 || IGS_GE_DATE.igscharDT(v_enr_snpsht_rec.tr_ou_start_dt) || ', '
1563 || TO_CHAR(v_enr_snpsht_rec.eftsu) || ', '
1564 || IGS_GE_DATE.igschar(v_enr_snpsht_rec.enrolled_dt) || ', '
1565 || IGS_GE_DATE.igschar(v_enr_snpsht_rec.discontinued_dt) || ', '
1566 || TO_CHAR(v_govt_semester) || ', '
1567 || IGS_GE_DATE.igschar(v_alias_val) || ', '
1568 || v_enr_snpsht_rec.ci_cal_type || ', '
1569 || IGS_GE_DATE.igschar(v_start_dt_2) || ' - '
1570 || IGS_GE_DATE.igschar(v_end_dt_2);
1571 gv_other_detail := 'Parm:'
1572 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1573 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1574 || ', p_ess_snapshot_dt_time- ' ||
1575 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1576 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind || ' '
1577 || gv_extra_details;
1578
1579 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1580 IGS_GE_MSG_STACK.ADD;
1581 App_Exception.Raise_Exception;
1582 END IF;
1583 IF (v_govt_reportable <> 'N') THEN
1584 -- government student enrolment records
1585 -- are created in submission 1 only.
1586 -- government student load and liability
1587 -- records are created in submissions 1 and 2
1588 IF (p_submission_number = 1) THEN
1589 -- only get the IGS_PE_PERSON data if the
1590 -- person_id changes
1591 IF v_person_id IS NULL OR
1592 v_person_id <> v_enr_snpsht_rec.person_id OR
1593 v_course_cd IS NULL OR
1594 v_course_cd <> v_enr_snpsht_rec.course_cd THEN
1595 IGS_ST_GEN_002.stap_get_person_data(
1596 v_enr_snpsht_rec.person_id,
1597 v_enr_snpsht_rec.course_cd,
1598 v_enr_snpsht_rec.crv_version_number,
1599 v_effective_dt,
1600 v_enr_snpsht_rec.commencing_student_ind,
1601 v_logged_ind,
1602 v_s_log_type,
1603 v_creation_dt,
1604 v_birth_dt, -- OUT NOCOPY
1605 v_sex, -- OUT NOCOPY
1606 v_aborig_torres_cd, -- OUT NOCOPY
1607 v_govt_aborig_torres_cd,-- OUT NOCOPY
1608 v_citizenship_cd, -- OUT NOCOPY
1609 v_govt_citizenship_cd, -- OUT NOCOPY
1610 v_perm_resident_cd, -- OUT NOCOPY
1611 v_govt_perm_resident_cd,-- OUT NOCOPY
1612 v_home_location_cd, -- OUT NOCOPY
1613 v_govt_home_location_cd,-- OUT NOCOPY
1614 v_term_location_cd, -- OUT NOCOPY
1615 v_govt_term_location_cd,-- OUT NOCOPY
1616 v_birth_country_cd, -- OUT NOCOPY
1617 v_govt_birth_country_cd,-- OUT NOCOPY
1618 v_yr_arrival, -- OUT NOCOPY
1619 v_home_language_cd, -- OUT NOCOPY
1620 v_govt_home_language_cd,-- OUT NOCOPY
1621 v_prior_ug_inst, -- OUT NOCOPY
1622 v_govt_prior_ug_inst, -- OUT NOCOPY
1623 v_prior_other_qual, -- OUT NOCOPY
1624 v_prior_post_grad, -- OUT NOCOPY
1625 v_prior_degree, -- OUT NOCOPY
1626 v_prior_subdeg_notafe, -- OUT NOCOPY
1627 v_prior_subdeg_tafe, -- OUT NOCOPY
1628 v_prior_seced_tafe, -- OUT NOCOPY
1629 v_prior_seced_school, -- OUT NOCOPY
1630 v_prior_tafe_award, -- OUT NOCOPY
1631 v_govt_disability); -- OUT NOCOPY
1632 END IF;
1633 -- get the IGS_PE_PERSON IGS_PS_COURSE details if the IGS_PE_PERSON
1634 -- and IGS_PS_COURSE has changed from the previous
1635 -- record retrieved
1636 IF v_person_id IS NULL OR
1637 v_person_id <> v_enr_snpsht_rec.person_id OR
1638 v_course_cd IS NULL OR
1639 v_course_cd <> v_enr_snpsht_rec.course_cd OR
1640 v_old_govt_semester IS NULL OR
1641 v_old_govt_semester <> v_govt_semester THEN
1642 IGS_ST_GEN_003.stap_get_sca_data(
1643 p_submission_yr,
1644 p_submission_number,
1645 v_enr_snpsht_rec.person_id,
1646 v_enr_snpsht_rec.course_cd,
1647 v_effective_dt,
1648 v_enr_snpsht_rec.crv_version_number,
1649 v_enr_snpsht_rec.commencing_student_ind,
1650 v_enr_snpsht_rec.ci_cal_type,
1651 v_enr_snpsht_rec.ci_sequence_number,
1652 v_logged_ind,
1653 v_s_log_type,
1654 v_creation_dt,
1655 v_govt_semester,
1656 v_enr_snpsht_rec.award_course_ind,
1657 v_govt_citizenship_cd,
1658 v_prior_seced_tafe,
1659 v_prior_seced_school,
1660 v_commencement_dt, -- OUT NOCOPY
1661 v_prior_studies_exemption, -- OUT NOCOPY
1662 v_exempt_institution_cd, -- OUT NOCOPY
1663 v_govt_exempt_institution_cd, -- OUT NOCOPY
1664 v_tertiary_entrance_score, -- OUT NOCOPY
1665 v_basis_for_admission_type, -- OUT NOCOPY
1666 v_govt_basis_for_adm_type, -- OUT NOCOPY
1667 v_hecs_amount_pd, -- OUT NOCOPY
1668 v_hecs_payment_option, -- OUT NOCOPY
1669 v_govt_hecs_payment_option, -- OUT NOCOPY
1670 v_tuition_fee, -- OUT NOCOPY
1671 v_hecs_fee, -- OUT NOCOPY
1672 v_differential_hecs_ind); -- OUT NOCOPY
1673 END IF;
1674 -- get the industrial indicator from IGS_PS_UNIT_VER
1675 IF v_unit_cd IS NULL OR
1676 v_unit_cd <> v_enr_snpsht_rec.unit_cd OR
1677 v_uv_version_number IS NULL OR
1678 v_uv_version_number <> v_enr_snpsht_rec.uv_version_number THEN
1679 OPEN c_get_indus_ind(
1680 v_enr_snpsht_rec.unit_cd,
1681 v_enr_snpsht_rec.uv_version_number);
1682 FETCH c_get_indus_ind INTO v_industrial_ind;
1683 IF (c_get_indus_ind%NOTFOUND) THEN
1684 CLOSE c_get_indus_ind;
1685 gv_extra_details := ' Cannot find IGS_PS_UNIT data:'
1686 || v_enr_snpsht_rec.unit_cd || ', '
1687 || TO_CHAR(v_enr_snpsht_rec.uv_version_number);
1688 gv_other_detail := 'Parm:'
1689 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1690 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1691 || ', p_ess_snapshot_dt_time- ' ||
1692 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1693 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind || ' '
1694 || gv_extra_details;
1695 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1696 IGS_GE_MSG_STACK.ADD;
1697 App_Exception.Raise_Exception;
1698 END IF;
1699 CLOSE c_get_indus_ind;
1700 END IF;
1701 -- determine the IGS_PS_UNIT completion status
1702 stapl_val_unit_compltn_status(
1703 v_enr_snpsht_rec.person_id,
1704 v_enr_snpsht_rec.course_cd,
1705 v_enr_snpsht_rec.unit_cd,
1706 v_enr_snpsht_rec.sua_cal_type,
1707 v_enr_snpsht_rec.sua_ci_sequence_number,
1708 v_unit_completion_status,
1709 v_enr_snpsht_rec.uoo_id);
1710 -- create the government student enrolment record,
1711 -- which only needs to be created if the IGS_PE_PERSON or
1712 -- IGS_PS_COURSE values are different from the previous record
1713 IF v_person_id IS NULL OR
1714 v_person_id <> v_enr_snpsht_rec.person_id OR
1715 v_course_cd IS NULL OR
1716 v_course_cd <> v_enr_snpsht_rec.course_cd THEN
1717 -- IGS_GE_NOTE : below some values are entered with a NVL
1718 -- statement. Even though they are set to all
1719 -- spaces, it seems to interpret them as
1720 -- as being NULLs (not null values on table).
1721 DECLARE
1722 v_rowid VARCHAR2(25);
1723 BEGIN
1724
1725 -- to insert row using insertrow of the respective TBH package
1726
1727 IGS_ST_GOVT_STDNT_EN_PKG.INSERT_ROW(
1728 X_ROWID => v_rowid,
1729 X_SUBMISSION_YR => p_submission_yr,
1730 X_SUBMISSION_NUMBER => p_submission_number,
1731 X_PERSON_ID => v_enr_snpsht_rec.person_id,
1732 X_COURSE_CD => v_enr_snpsht_rec.course_cd,
1733 X_VERSION_NUMBER => v_enr_snpsht_rec.crv_version_number,
1734 X_BIRTH_DT => v_birth_dt,
1735 X_SEX => v_sex,
1736 X_ABORIG_TORRES_CD => v_aborig_torres_cd,
1737 X_GOVT_ABORIG_TORRES_CD => v_govt_aborig_torres_cd,
1738 X_CITIZENSHIP_CD => v_citizenship_cd,
1739 X_GOVT_CITIZENSHIP_CD => v_govt_citizenship_cd,
1740 X_PERM_RESIDENT_CD => v_perm_resident_cd,
1741 X_GOVT_PERM_RESIDENT_CD => v_govt_perm_resident_cd,
1742 X_HOME_LOCATION => v_home_location_cd,
1743 X_GOVT_HOME_LOCATION => v_govt_home_location_cd,
1744 X_TERM_LOCATION => v_term_location_cd,
1745 X_GOVT_TERM_LOCATION => v_govt_term_location_cd,
1746 X_BIRTH_COUNTRY_CD => v_birth_country_cd,
1747 X_GOVT_BIRTH_COUNTRY_CD => v_govt_birth_country_cd,
1748 X_YR_ARRIVAL => v_yr_arrival,
1749 X_HOME_LANGUAGE_CD => v_home_language_cd,
1750 X_GOVT_HOME_LANGUAGE_CD => v_govt_home_language_cd,
1751 X_PRIOR_UG_INST => v_prior_ug_inst,
1752 X_GOVT_PRIOR_UG_INST => v_govt_prior_ug_inst,
1753 X_PRIOR_OTHER_QUAL => v_prior_other_qual,
1754 X_PRIOR_POST_GRAD => v_prior_post_grad,
1755 X_PRIOR_DEGREE => v_prior_degree,
1756 X_PRIOR_SUBDEG_NOTAFE => v_prior_subdeg_notafe,
1757 X_PRIOR_SUBDEG_TAFE => v_prior_subdeg_tafe,
1758 X_PRIOR_SECED_TAFE => v_prior_seced_tafe,
1759 X_PRIOR_SECED_SCHOOL => v_prior_seced_school,
1760 X_PRIOR_TAFE_AWARD => v_prior_tafe_award,
1761 X_PRIOR_STUDIES_EXEMPTION => v_prior_studies_exemption,
1762 X_EXEMPTION_INSTITUTION_CD => v_exempt_institution_cd,
1763 X_GOVT_EXEMPT_INSTITU_CD => v_govt_exempt_institution_cd,
1764 X_ATTENDANCE_MODE => v_attendance_mode_3,
1765 X_GOVT_ATTENDANCE_MODE => v_govt_attendance_mode_3,
1766 X_ATTENDANCE_TYPE => v_attendance_type,
1767 X_GOVT_ATTENDANCE_TYPE => v_govt_attendance_type,
1768 X_COMMENCEMENT_DT => v_commencement_dt,
1769 X_MAJOR_COURSE => 1,
1770 X_TERTIARY_ENTRANCE_SCORE => v_tertiary_entrance_score,
1771 X_BASIS_FOR_ADMISSION_TYPE => v_basis_for_admission_type,
1772 X_GOVT_BASIS_FOR_ADM_TYPE => v_govt_basis_for_adm_type,
1773 X_GOVT_DISABILITY => NVL(v_govt_disability, ' '),
1774 X_MODE => 'R');
1775
1776 EXCEPTION
1777 WHEN OTHERS THEN
1778 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1779 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004. 1');
1780 IGS_GE_MSG_STACK.ADD;
1781 App_Exception.Raise_Exception;
1782 END;
1783 END IF;
1784 -- create the government student load record
1785 stapl_ins_govt_sdnt_load_rec(
1786 p_submission_yr,
1787 p_submission_number,
1788 v_enr_snpsht_rec.person_id,
1789 v_enr_snpsht_rec.course_cd,
1790 v_enr_snpsht_rec.crv_version_number,
1791 v_govt_semester,
1792 v_enr_snpsht_rec.unit_cd,
1793 v_enr_snpsht_rec.uv_version_number,
1794 v_enr_snpsht_rec.sua_cal_type,
1795 v_enr_snpsht_rec.sua_ci_sequence_number,
1796 v_enr_snpsht_rec.tr_org_unit_cd,
1797 v_enr_snpsht_rec.tr_ou_start_dt,
1798 v_enr_snpsht_rec.discipline_group_cd,
1799 v_enr_snpsht_rec.govt_discipline_group_cd,
1800 v_industrial_ind,
1801 v_enr_snpsht_rec.eftsu,
1802 v_unit_completion_stat,
1803 v_logged_ind,
1804 v_s_log_type,
1805 v_creation_dt,
1806 v_enr_snpsht_rec.sua_location_cd,
1807 v_enr_snpsht_rec.unit_class);
1808 -- create the government student liability record
1809 IF v_person_id IS NULL OR
1810 v_person_id <> v_enr_snpsht_rec.person_id OR
1811 v_course_cd IS NULL OR
1812 v_course_cd <> v_enr_snpsht_rec.course_cd OR
1813 v_old_govt_semester IS NULL OR
1814 v_old_govt_semester <> v_govt_semester THEN
1815 stapl_ins_govt_sdnt_liab_rec(
1816 p_submission_yr,
1817 p_submission_number,
1818 v_enr_snpsht_rec.person_id,
1819 v_enr_snpsht_rec.course_cd,
1820 v_enr_snpsht_rec.crv_version_number,
1821 v_govt_semester,
1822 v_hecs_payment_option,
1823 v_govt_hecs_payment_option,
1824 v_hecs_amount_pd,
1825 v_tuition_fee,
1826 v_differential_hecs_ind,
1827 v_birth_dt,
1828 v_sex,
1829 v_citizenship_cd,
1830 v_govt_citizenship_cd,
1831 v_perm_resident_cd,
1832 v_govt_perm_resident_cd,
1833 v_commencement_dt,
1834 v_hecs_fee);
1835 END IF;
1836 ELSE -- p_submission_number = 2
1837 -- get the IGS_PE_PERSON IGS_PS_COURSE details if the IGS_PE_PERSON
1838 -- and IGS_PS_COURSE has changed from the previous
1839 -- record retrieved
1840 IF v_person_id IS NULL OR
1841 v_person_id <> v_enr_snpsht_rec.person_id OR
1842 v_course_cd IS NULL OR
1843 v_course_cd <> v_enr_snpsht_rec.course_cd OR
1844 v_old_govt_semester IS NULL OR
1845 v_old_govt_semester <> v_govt_semester THEN
1846 IGS_ST_GEN_003.stap_get_sca_data(
1847 p_submission_yr,
1848 p_submission_number,
1849 v_enr_snpsht_rec.person_id,
1850 v_enr_snpsht_rec.course_cd,
1851 v_effective_dt,
1852 v_enr_snpsht_rec.crv_version_number,
1853 v_enr_snpsht_rec.commencing_student_ind,
1854 v_enr_snpsht_rec.ci_cal_type,
1855 v_enr_snpsht_rec.ci_sequence_number,
1856 v_logged_ind,
1857 v_s_log_type,
1858 v_creation_dt,
1859 v_govt_semester,
1860 v_enr_snpsht_rec.award_course_ind,
1861 v_govt_citizenship_cd,
1862 v_prior_seced_tafe,
1863 v_prior_seced_school,
1864 v_commencement_dt, -- OUT NOCOPY
1865 v_prior_studies_exemption, -- OUT NOCOPY
1866 v_exempt_institution_cd, -- OUT NOCOPY
1867 v_govt_exempt_institution_cd, -- OUT NOCOPY
1868 v_tertiary_entrance_score, -- OUT NOCOPY
1869 v_basis_for_admission_type, -- OUT NOCOPY
1870 v_govt_basis_for_adm_type, -- OUT NOCOPY
1871 v_hecs_amount_pd, -- OUT NOCOPY
1872 v_hecs_payment_option, -- OUT NOCOPY
1873 v_govt_hecs_payment_option, -- OUT NOCOPY
1874 v_tuition_fee, -- OUT NOCOPY
1875 v_hecs_fee, -- OUT NOCOPY
1876 v_differential_hecs_ind); -- OUT NOCOPY
1877 END IF;
1878 -- get the industrial indicator from IGS_PS_UNIT_VER
1879 IF v_unit_cd IS NULL OR
1880 v_unit_cd <> v_enr_snpsht_rec.unit_cd OR
1881 v_uv_version_number IS NULL OR
1882 v_uv_version_number <> v_enr_snpsht_rec.uv_version_number THEN
1883 OPEN c_get_indus_ind(
1884 v_enr_snpsht_rec.unit_cd,
1885 v_enr_snpsht_rec.uv_version_number);
1886 FETCH c_get_indus_ind INTO v_industrial_ind;
1887 IF (c_get_indus_ind%NOTFOUND) THEN
1888 CLOSE c_get_indus_ind;
1889 gv_extra_details := ' Cannot find IGS_PS_UNIT data:'
1890 || v_enr_snpsht_rec.unit_cd || ', '
1891 || TO_CHAR(v_enr_snpsht_rec.uv_version_number);
1892 gv_other_detail := 'Parm:'
1893 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
1894 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
1895 || ', p_ess_snapshot_dt_time- ' ||
1896 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
1897 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind || ' '
1898 || gv_extra_details;
1899 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1900 IGS_GE_MSG_STACK.ADD;
1901 App_Exception.Raise_Exception;
1902 END IF;
1903 CLOSE c_get_indus_ind;
1904 END IF;
1905 -- determine the IGS_PS_UNIT completion status
1906 stapl_val_unit_compltn_status(
1907 v_enr_snpsht_rec.person_id,
1908 v_enr_snpsht_rec.course_cd,
1909 v_enr_snpsht_rec.unit_cd,
1910 v_enr_snpsht_rec.sua_cal_type,
1911 v_enr_snpsht_rec.sua_ci_sequence_number,
1912 v_unit_completion_status,
1913 v_enr_snpsht_rec.uoo_id);
1914 -- create the government student load record
1915 stapl_ins_govt_sdnt_load_rec(
1916 p_submission_yr,
1917 p_submission_number,
1918 v_enr_snpsht_rec.person_id,
1919 v_enr_snpsht_rec.course_cd,
1920 v_enr_snpsht_rec.crv_version_number,
1921 v_govt_semester,
1922 v_enr_snpsht_rec.unit_cd,
1923 v_enr_snpsht_rec.uv_version_number,
1924 v_enr_snpsht_rec.sua_cal_type,
1925 v_enr_snpsht_rec.sua_ci_sequence_number,
1926 v_enr_snpsht_rec.tr_org_unit_cd,
1927 v_enr_snpsht_rec.tr_ou_start_dt,
1928 v_enr_snpsht_rec.discipline_group_cd,
1929 v_enr_snpsht_rec.govt_discipline_group_cd,
1930 v_industrial_ind,
1931 v_enr_snpsht_rec.eftsu,
1932 v_unit_completion_status,
1933 v_logged_ind,
1934 v_s_log_type,
1935 v_creation_dt,
1936 v_enr_snpsht_rec.sua_location_cd,
1937 v_enr_snpsht_rec.unit_class);
1938 -- retrieve IGS_PE_PERSON and IGS_PE_PERSON IGS_PS_COURSE data for the government
1939 -- student liability record
1940 stapl_get_sca_data_for_liab(
1941 v_enr_snpsht_rec.person_id,
1942 v_person_id, -- old person_id
1943 v_enr_snpsht_rec.course_cd,
1944 v_course_cd, -- old course_cd
1945 v_effective_dt,
1946 v_logged_ind, -- IN OUT NOCOPY
1947 v_s_log_type,
1948 v_creation_dt,
1949 v_birth_dt, -- IN OUT NOCOPY
1950 v_sex, -- IN OUT NOCOPY
1951 v_citizenship_cd, -- IN OUT NOCOPY
1952 v_govt_citizenship_cd, -- IN OUT NOCOPY
1953 v_perm_resident_cd, -- IN OUT NOCOPY
1954 v_govt_perm_resident_cd); -- IN OUT NOCOPY
1955 -- create the government student liability record
1956 IF v_person_id IS NULL OR
1957 v_person_id <> v_enr_snpsht_rec.person_id OR
1958 v_course_cd IS NULL OR
1959 v_course_cd <> v_enr_snpsht_rec.course_cd OR
1960 v_old_govt_semester IS NULL OR
1961 v_old_govt_semester <> v_govt_semester THEN
1962 stapl_ins_govt_sdnt_liab_rec(
1963 p_submission_yr,
1964 p_submission_number,
1965 v_enr_snpsht_rec.person_id,
1966 v_enr_snpsht_rec.course_cd,
1967 v_enr_snpsht_rec.crv_version_number,
1968 v_govt_semester,
1969 v_hecs_payment_option,
1970 v_govt_hecs_payment_option,
1971 v_hecs_amount_pd,
1972 v_tuition_fee,
1973 v_differential_hecs_ind,
1974 v_birth_dt,
1975 v_sex,
1976 v_citizenship_cd,
1977 v_govt_citizenship_cd,
1978 v_perm_resident_cd,
1979 v_govt_perm_resident_cd,
1980 v_commencement_dt,
1981 v_hecs_fee);
1982 END IF;
1983 END IF;
1984 -- set the values before looping again
1985 v_person_id := v_enr_snpsht_rec.person_id;
1986 v_course_cd := v_enr_snpsht_rec.course_cd;
1987 v_unit_cd := v_enr_snpsht_rec.unit_cd;
1988 v_uv_version_number := v_enr_snpsht_rec.uv_version_number;
1989 END IF; --end if for reportable = Y
1990 END IF; -- end if p_submission_number = 1 OR (p_submission_number = 2 AND
1991 -- (v_govt_semester IN(2, 4))))
1992 -- setting the values again before going around
1993 -- the loop again, as retrieval of government
1994 -- semester only needs to be done if these
1995 -- values change
1996 v_load_cal_type := v_enr_snpsht_rec.ci_cal_type;
1997 v_load_ci_sequence_number := v_enr_snpsht_rec.ci_sequence_number;
1998 v_teach_cal_type := v_enr_snpsht_rec.sua_cal_type;
1999 v_teach_ci_sequence_number := v_enr_snpsht_rec.sua_ci_sequence_number;
2000 v_old_govt_semester := v_govt_semester;
2001 END LOOP;
2002 -- raise an error if no enrolment statistics
2003 -- snapshot records were found
2004 IF (v_ess_rec_found = FALSE) THEN
2005 p_message_name := 'IGS_ST_GOVT_SNAPSHOT_NO_DATA';
2006 RETURN FALSE;
2007 END IF;
2008 COMMIT;
2009
2010 END IF;
2011 -- process data from previous submissions
2012 -- For submission 2 retrieve data for Government Semesters
2013 -- 1, 3 and 5 frm the previous submisson.
2014 -- For Submission 3, retrieve all data from the previous submission
2015 IF (p_submission_number IN (2, 3)) THEN
2016 -- Populate the government student load temporary table.
2017 -- set that no records have yet been found
2018 -- from the previous submission
2019 v_prev_sub := FALSE;
2020 -- retrieve the Government Snapshot data from the previous submission
2021 FOR v_gslot_rec IN Cur_si_st_govtstdldtmp LOOP
2022 -- a previous submission exists
2023 v_prev_sub := TRUE;
2024 v_unit_completion_status := stap_get_un_comp_sts(
2025 v_gslot_rec.person_id,
2026 v_gslot_rec.course_cd,
2027 v_gslot_rec.unit_cd,
2028 v_gslot_rec.sua_cal_type,
2029 v_gslot_rec.sua_ci_sequence_number,
2030 v_gslot_rec.uoo_id);
2031
2032 IF ((p_submission_number = 2 AND
2033 v_gslot_rec.govt_semester IN (1, 3, 5)) OR
2034 p_submission_number = 3) THEN
2035 IF (v_gslot_rec.govt_semester IN (1, 3, 5)) THEN
2036 v_unit_effective_dt := v_submission_1_census_dt;
2037 ELSE
2038 v_unit_effective_dt := v_submission_2_census_dt;
2039 END IF;
2040 -- select the alternate person_id
2041 -- select the first record only, as this
2042 -- will be the end dated record if one exists
2043 OPEN c_get_api(
2044 v_gslot_rec.person_id,
2045 v_unit_effective_dt);
2046 FETCH c_get_api INTO v_temp_person_id;
2047 IF (c_get_api%FOUND) THEN
2048 v_person_id := v_temp_person_id;
2049 ELSE
2050 v_person_id := v_gslot_rec.person_id;
2051 END IF;
2052 CLOSE c_get_api;
2053 -- determine the IGS_PS_UNIT completion status
2054 -- This is done in the stapl_populate_gslo_tmp routine
2055 -- Create the Government Student Load record
2056 stapl_ins_govt_sdnt_load_rec(
2057 p_submission_yr,
2058 p_submission_number,
2059 v_person_id,
2060 v_gslot_rec.course_cd,
2061 v_gslot_rec.crv_version_number,
2062 v_gslot_rec.govt_semester,
2063 v_gslot_rec.unit_cd,
2064 v_gslot_rec.uv_version_number,
2065 v_gslot_rec.sua_cal_type,
2066 v_gslot_rec.sua_ci_sequence_number,
2067 v_gslot_rec.tr_org_unit_cd,
2068 v_gslot_rec.tr_ou_start_dt,
2069 v_gslot_rec.discipline_group_cd,
2070 v_gslot_rec.govt_discipline_group_cd,
2071 v_gslot_rec.industrial_ind,
2072 v_gslot_rec.eftsu,
2073 v_unit_completion_status, --changed
2074 v_logged_ind,
2075 v_s_log_type,
2076 v_creation_dt,
2077 v_gslot_rec.sua_location_cd,
2078 v_gslot_rec.unit_class);
2079 END IF;
2080 END LOOP;
2081
2082 -- return an error message if no previous submission
2083 -- records were found
2084 IF (v_prev_sub = FALSE) THEN
2085 p_message_name := 'IGS_ST_PREV_GOVT_SNAP_NO_DATA';
2086 RETURN FALSE;
2087 END IF;
2088
2089 END IF;
2090
2091 IF (p_submission_number = 1) THEN
2092 -- Update the Attendance Mode value for inserted records.
2093 FOR v_gse_att_mode IN c_gse_att_mode LOOP
2094 -- get IGS_ST_GVT_STDNTLOAD details
2095 FOR v_gslo IN c_gslo(
2096 v_gse_att_mode.person_id,
2097 v_gse_att_mode.course_cd) LOOP
2098 -- determine the system IGS_PS_UNIT mode of the IGS_PS_UNIT
2099 OPEN c_sua_ucl_um(
2100 v_gse_att_mode.person_id,
2101 v_gse_att_mode.course_cd,
2102 v_gslo.unit_cd,
2103 v_gslo.sua_cal_type,
2104 v_gslo.sua_ci_sequence_number,
2105 v_gslo.sua_location_cd,
2106 v_gslo.unit_class);
2107 FETCH c_sua_ucl_um INTO v_s_unit_mode;
2108 CLOSE c_sua_ucl_um;
2109 -- checking the value of the system IGS_PS_UNIT mode
2110 IF (v_s_unit_mode = 'ON') THEN
2111 v_on := TRUE;
2112 ELSIF (v_s_unit_mode = 'OFF') THEN
2113 v_off := TRUE;
2114 ELSIF (v_s_unit_mode = 'COMPOSITE') THEN
2115 v_composite := TRUE;
2116 ELSE
2117 v_person_id := v_gse_att_mode.person_id;
2118 v_course_cd := v_gse_att_mode.course_cd;
2119 gv_extra_details := 'Cannot determine the attendance mode for- '
2120 || TO_CHAR(v_person_id) || ', '
2121 || v_course_cd;
2122 gv_other_detail := 'Parm:'
2123 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
2124 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
2125 || ', p_dt_time- ' ||
2126 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
2127 || ', p_ind- ' || p_use_most_recent_ess_ind
2128 || ', ' || gv_extra_details;
2129 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
2130 IGS_GE_MSG_STACK.ADD;
2131 App_Exception.Raise_Exception;
2132 END IF;
2133 IF (v_on = TRUE AND
2134 v_off = TRUE) THEN
2135 v_composite := TRUE;
2136 END IF;
2137 -- exit as soon as the IGS_PE_PERSON's mode is composite
2138 -- don't need to check any other records
2139 IF (v_composite = TRUE) THEN
2140 exit;
2141 END IF;
2142 END LOOP;
2143 -- set the appropriate attendance mode values
2144 IF (v_composite = TRUE) THEN
2145 v_attendance_mode := v_attendance_mode_3;
2146 v_govt_attendance_mode := v_govt_attendance_mode_3;
2147 ELSIF (v_on = TRUE) THEN
2148 v_attendance_mode := v_attendance_mode_1;
2149 v_govt_attendance_mode := v_govt_attendance_mode_1;
2150 ELSIF (v_off = TRUE) THEN
2151 v_attendance_mode := v_attendance_mode_2;
2152 v_govt_attendance_mode := v_govt_attendance_mode_2;
2153 ELSE
2154 v_person_id := v_gse_att_mode.person_id;
2155 v_course_cd := v_gse_att_mode.course_cd;
2156 gv_extra_details := 'Cannot determine the attendance mode for- '
2157 || TO_CHAR(v_person_id) || ', '
2158 || v_course_cd;
2159 gv_other_detail := 'Parm:'
2160 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
2161 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
2162 || ', p_dt_time- ' ||
2163 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
2164 || ', p_ind- ' || p_use_most_recent_ess_ind
2165 || ', ' || gv_extra_details;
2166 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
2167 IGS_GE_MSG_STACK.ADD;
2168 App_Exception.Raise_Exception;
2169 END IF;
2170 -- update IGS_ST_GOVT_STDNT_EN table
2171 BEGIN
2172 FOR v_gse_upd3_rec IN c_gse_upd3(
2173 v_gse_att_mode.person_id,
2174 v_gse_att_mode.course_cd) LOOP
2175
2176 v_gse_upd3_rec.attendance_mode := v_attendance_mode;
2177 v_gse_upd3_rec.govt_attendance_mode := v_govt_attendance_mode;
2178 IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
2179 X_ROWID => v_gse_upd3_rec.rowid,
2180 X_SUBMISSION_YR => v_gse_upd3_rec.submission_yr,
2181 X_SUBMISSION_NUMBER => v_gse_upd3_rec.submission_number,
2182 X_PERSON_ID => v_gse_upd3_rec.person_id,
2183 X_COURSE_CD => v_gse_upd3_rec.course_cd,
2184 X_VERSION_NUMBER => v_gse_upd3_rec.version_number,
2185 X_BIRTH_DT => v_gse_upd3_rec.birth_dt,
2186 X_SEX => v_gse_upd3_rec.sex,
2187 X_ABORIG_TORRES_CD => v_gse_upd3_rec.aborig_torres_cd,
2188 X_GOVT_ABORIG_TORRES_CD => v_gse_upd3_rec.govt_aborig_torres_cd,
2189 X_CITIZENSHIP_CD => v_gse_upd3_rec.citizenship_cd,
2190 X_GOVT_CITIZENSHIP_CD => v_gse_upd3_rec.govt_citizenship_cd,
2191 X_PERM_RESIDENT_CD => v_gse_upd3_rec.perm_resident_cd,
2192 X_GOVT_PERM_RESIDENT_CD => v_gse_upd3_rec.govt_perm_resident_cd,
2193 X_HOME_LOCATION => v_gse_upd3_rec.home_location,
2194 X_GOVT_HOME_LOCATION => v_gse_upd3_rec.govt_home_location,
2195 X_TERM_LOCATION => v_gse_upd3_rec.term_location,
2196 X_GOVT_TERM_LOCATION => v_gse_upd3_rec.govt_term_location,
2197 X_BIRTH_COUNTRY_CD => v_gse_upd3_rec.birth_country_cd,
2198 X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd3_rec.govt_birth_country_cd,
2199 X_YR_ARRIVAL => v_gse_upd3_rec.yr_arrival,
2200 X_HOME_LANGUAGE_CD => v_gse_upd3_rec.home_language_cd,
2201 X_GOVT_HOME_LANGUAGE_CD => v_gse_upd3_rec.govt_home_language_cd,
2202 X_PRIOR_UG_INST => v_gse_upd3_rec.prior_ug_inst,
2203 X_GOVT_PRIOR_UG_INST => v_gse_upd3_rec.govt_prior_ug_inst,
2204 X_PRIOR_OTHER_QUAL => v_gse_upd3_rec.prior_other_qual,
2205 X_PRIOR_POST_GRAD => v_gse_upd3_rec.prior_post_grad,
2206 X_PRIOR_DEGREE => v_gse_upd3_rec.prior_degree,
2207 X_PRIOR_SUBDEG_NOTAFE => v_gse_upd3_rec.prior_subdeg_notafe,
2208 X_PRIOR_SUBDEG_TAFE => v_gse_upd3_rec.prior_subdeg_tafe,
2209 X_PRIOR_SECED_TAFE => v_gse_upd3_rec.prior_seced_tafe,
2210 X_PRIOR_SECED_SCHOOL => v_gse_upd3_rec.prior_seced_school,
2211 X_PRIOR_TAFE_AWARD => v_gse_upd3_rec.prior_tafe_award,
2212 X_PRIOR_STUDIES_EXEMPTION => v_gse_upd3_rec.prior_studies_exemption,
2213 X_EXEMPTION_INSTITUTION_CD => v_gse_upd3_rec.exemption_institution_cd,
2214 X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd3_rec.govt_exemption_institution_cd,
2215 X_ATTENDANCE_MODE => v_gse_upd3_rec.attendance_mode,
2216 X_GOVT_ATTENDANCE_MODE => v_gse_upd3_rec.govt_attendance_mode,
2217 X_ATTENDANCE_TYPE => v_gse_upd3_rec.attendance_type,
2218 X_GOVT_ATTENDANCE_TYPE => v_gse_upd3_rec.govt_attendance_type,
2219 X_COMMENCEMENT_DT => v_gse_upd3_rec.commencement_dt,
2220 X_MAJOR_COURSE => v_gse_upd3_rec.major_course,
2221 X_TERTIARY_ENTRANCE_SCORE => v_gse_upd3_rec.tertiary_entrance_score,
2222 X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd3_rec.basis_for_admission_type,
2223 X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd3_rec.govt_basis_for_admission_type,
2224 X_GOVT_DISABILITY => v_gse_upd3_rec.govt_disability,
2225 X_MODE => 'R');
2226
2227 END LOOP;
2228 EXCEPTION
2229 WHEN e_resource_busy THEN
2230 p_message_name := 'IGS_ST_GOV_ENRL_REC_CANT_UPD';
2231 RETURN FALSE;
2232 WHEN OTHERS THEN
2233 RAISE;
2234 END;
2235 -- reset the system IGS_PS_UNIT mode variables
2236 v_on := FALSE;
2237 v_off := FALSE;
2238 v_composite := FALSE;
2239 END LOOP;
2240 COMMIT;
2241
2242 -- Update the Attendance Type value for inserted records.
2243 OPEN c_att;
2244 FETCH c_att INTO v_attendance_type,
2245 v_govt_attendance_type,
2246 v_lower_enr_load_range;
2247 IF (c_att%NOTFOUND) THEN
2248 CLOSE c_att;
2249 gv_other_detail := 'Parm:'
2250 || ' p_submission_yr- ' || TO_CHAR(p_submission_yr)
2251 || ', p_submission_number- ' || TO_CHAR(p_submission_number)
2252 || ', p_dt_time- ' ||
2253 IGS_GE_DATE.igscharDT(p_ess_snapshot_dt_time)
2254 || ', p_use_most_recent_ess_ind- ' || p_use_most_recent_ess_ind;
2255 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
2256 IGS_GE_MSG_STACK.ADD;
2257 App_Exception.Raise_Exception;
2258 END IF;
2259 CLOSE c_att;
2260 -- select records from IGS_ST_GOVT_STDNT_EN which
2261 -- the SUM(eftsu) is greater than the lower_enr_load_range
2262 FOR v_gse IN c_gse(v_lower_enr_load_range) LOOP
2263 -- update the IGS_ST_GOVT_STDNT_EN record
2264 BEGIN
2265 FOR v_gse_upd2_rec IN c_gse_upd2(v_gse.person_id) LOOP
2266
2267 v_gse_upd2_rec.attendance_type := v_attendance_type;
2268 v_gse_upd2_rec.govt_attendance_type := v_govt_attendance_type;
2269 IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
2270 X_ROWID => v_gse_upd2_rec.rowid,
2271 X_SUBMISSION_YR => v_gse_upd2_rec.submission_yr,
2272 X_SUBMISSION_NUMBER => v_gse_upd2_rec.submission_number,
2273 X_PERSON_ID => v_gse_upd2_rec.person_id,
2274 X_COURSE_CD => v_gse_upd2_rec.course_cd,
2275 X_VERSION_NUMBER => v_gse_upd2_rec.version_number,
2276 X_BIRTH_DT => v_gse_upd2_rec.birth_dt,
2277 X_SEX => v_gse_upd2_rec.sex,
2278 X_ABORIG_TORRES_CD => v_gse_upd2_rec.aborig_torres_cd,
2279 X_GOVT_ABORIG_TORRES_CD => v_gse_upd2_rec.govt_aborig_torres_cd,
2280 X_CITIZENSHIP_CD => v_gse_upd2_rec.citizenship_cd,
2281 X_GOVT_CITIZENSHIP_CD => v_gse_upd2_rec.govt_citizenship_cd,
2282 X_PERM_RESIDENT_CD => v_gse_upd2_rec.perm_resident_cd,
2283 X_GOVT_PERM_RESIDENT_CD => v_gse_upd2_rec.govt_perm_resident_cd,
2284 X_HOME_LOCATION => v_gse_upd2_rec.home_location,
2285 X_GOVT_HOME_LOCATION => v_gse_upd2_rec.govt_home_location,
2286 X_TERM_LOCATION => v_gse_upd2_rec.term_location,
2287 X_GOVT_TERM_LOCATION => v_gse_upd2_rec.govt_term_location,
2288 X_BIRTH_COUNTRY_CD => v_gse_upd2_rec.birth_country_cd,
2289 X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd2_rec.govt_birth_country_cd,
2290 X_YR_ARRIVAL => v_gse_upd2_rec.yr_arrival,
2291 X_HOME_LANGUAGE_CD => v_gse_upd2_rec.home_language_cd,
2292 X_GOVT_HOME_LANGUAGE_CD => v_gse_upd2_rec.govt_home_language_cd,
2293 X_PRIOR_UG_INST => v_gse_upd2_rec.prior_ug_inst,
2294 X_GOVT_PRIOR_UG_INST => v_gse_upd2_rec.govt_prior_ug_inst,
2295 X_PRIOR_OTHER_QUAL => v_gse_upd2_rec.prior_other_qual,
2296 X_PRIOR_POST_GRAD => v_gse_upd2_rec.prior_post_grad,
2297 X_PRIOR_DEGREE => v_gse_upd2_rec.prior_degree,
2298 X_PRIOR_SUBDEG_NOTAFE => v_gse_upd2_rec.prior_subdeg_notafe,
2299 X_PRIOR_SUBDEG_TAFE => v_gse_upd2_rec.prior_subdeg_tafe,
2300 X_PRIOR_SECED_TAFE => v_gse_upd2_rec.prior_seced_tafe,
2301 X_PRIOR_SECED_SCHOOL => v_gse_upd2_rec.prior_seced_school,
2302 X_PRIOR_TAFE_AWARD => v_gse_upd2_rec.prior_tafe_award,
2303 X_PRIOR_STUDIES_EXEMPTION => v_gse_upd2_rec.prior_studies_exemption,
2304 X_EXEMPTION_INSTITUTION_CD => v_gse_upd2_rec.exemption_institution_cd,
2305 X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd2_rec.govt_exemption_institution_cd,
2306 X_ATTENDANCE_MODE => v_gse_upd2_rec.attendance_mode,
2307 X_GOVT_ATTENDANCE_MODE => v_gse_upd2_rec.govt_attendance_mode,
2308 X_ATTENDANCE_TYPE => v_gse_upd2_rec.attendance_type,
2309 X_GOVT_ATTENDANCE_TYPE => v_gse_upd2_rec.govt_attendance_type,
2310 X_COMMENCEMENT_DT => v_gse_upd2_rec.commencement_dt,
2311 X_MAJOR_COURSE => v_gse_upd2_rec.major_course,
2312 X_TERTIARY_ENTRANCE_SCORE => v_gse_upd2_rec.tertiary_entrance_score,
2313 X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd2_rec.basis_for_admission_type,
2314 X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd2_rec.govt_basis_for_admission_type,
2315 X_GOVT_DISABILITY => v_gse_upd2_rec.govt_disability,
2316 X_MODE => 'R');
2317
2318 END LOOP;
2319 EXCEPTION
2320 WHEN e_resource_busy THEN
2321 p_message_name := 'IGS_ST_GOV_ENRL_ATT_CANT_UPD';
2322 RETURN FALSE;
2323 WHEN OTHERS THEN
2324 RAISE;
2325 END;
2326 IF v_total_eftsu > 1 THEN
2327 --Check if an entry has been written to the error log
2328 --Create an entry in the system log entry
2329 IGS_GE_GEN_003.genp_ins_log_entry (
2330 v_s_log_type,
2331 IGS_GE_DATE.igscharDT(v_creation_dt),
2332 'IGS_PE_PERSON IGS_PS_COURSE ' || ',' || TO_CHAR(v_person_id) || ',' || v_course_cd,
2333 4222,
2334 NULL);
2335 -- set that an error has been logged
2336 v_logged_ind := TRUE;
2337 END IF;
2338 END LOOP;
2339 COMMIT;
2340
2341 -- Update the Major IGS_PS_COURSE value for inserted records
2342 v_tmp_person_id := 0;
2343 v_first_flag := TRUE;
2344 FOR v_gse2 IN c_gse2 LOOP
2345 IF (v_first_flag = TRUE) THEN
2346 v_person_id := v_gse2.person_id;
2347 v_course_cd := v_gse2.course_cd;
2348 v_first_flag := FALSE;
2349 ELSE
2350 -- If the IGS_PE_PERSON is same as the previous IGS_PE_PERSON and the IGS_PS_COURSE is
2351 -- different, but we haven't already changed the major IGS_PS_COURSE for
2352 -- the student.
2353 IF (v_gse2.person_id = v_person_id AND
2354 v_gse2.course_cd <> v_course_cd AND
2355 v_gse2.person_id <> v_tmp_person_id) THEN
2356 -- Not the first record and IGS_PE_PERSON enrolled in more than 1 IGS_PS_COURSE.
2357 -- Now using the flag for another purpose. Set the flag to true
2358 v_flag := TRUE;
2359 FOR v_gse_sca IN c_gse_sca(v_gse2.person_id) LOOP
2360 IF (v_flag = TRUE) THEN
2361 v_major_course := 2; -- major IGS_PS_COURSE
2362 ELSE
2363 v_major_course := 3; -- minor IGS_PS_COURSE
2364 END IF;
2365 v_flag := FALSE;
2366 -- update the IGS_ST_GOVT_STDNT_EN record
2367 BEGIN
2368 FOR v_gse_upd3_rec IN c_gse_upd3(
2369 v_person_id,
2370 v_gse_sca.course_cd) LOOP
2371
2372 v_gse_upd3_rec.major_course := v_major_course;
2373
2374 IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
2375 X_ROWID => v_gse_upd3_rec.rowid,
2376 X_SUBMISSION_YR => v_gse_upd3_rec.submission_yr,
2377 X_SUBMISSION_NUMBER => v_gse_upd3_rec.submission_number,
2378 X_PERSON_ID => v_gse_upd3_rec.person_id,
2379 X_COURSE_CD => v_gse_upd3_rec.course_cd,
2380 X_VERSION_NUMBER => v_gse_upd3_rec.version_number,
2381 X_BIRTH_DT => v_gse_upd3_rec.birth_dt,
2382 X_SEX => v_gse_upd3_rec.sex,
2383 X_ABORIG_TORRES_CD => v_gse_upd3_rec.aborig_torres_cd,
2384 X_GOVT_ABORIG_TORRES_CD => v_gse_upd3_rec.govt_aborig_torres_cd,
2385 X_CITIZENSHIP_CD => v_gse_upd3_rec.citizenship_cd,
2386 X_GOVT_CITIZENSHIP_CD => v_gse_upd3_rec.govt_citizenship_cd,
2387 X_PERM_RESIDENT_CD => v_gse_upd3_rec.perm_resident_cd,
2388 X_GOVT_PERM_RESIDENT_CD => v_gse_upd3_rec.govt_perm_resident_cd,
2389 X_HOME_LOCATION => v_gse_upd3_rec.home_location,
2390 X_GOVT_HOME_LOCATION => v_gse_upd3_rec.govt_home_location,
2391 X_TERM_LOCATION => v_gse_upd3_rec.term_location,
2392 X_GOVT_TERM_LOCATION => v_gse_upd3_rec.govt_term_location,
2393 X_BIRTH_COUNTRY_CD => v_gse_upd3_rec.birth_country_cd,
2394 X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd3_rec.govt_birth_country_cd,
2395 X_YR_ARRIVAL => v_gse_upd3_rec.yr_arrival,
2396 X_HOME_LANGUAGE_CD => v_gse_upd3_rec.home_language_cd,
2397 X_GOVT_HOME_LANGUAGE_CD => v_gse_upd3_rec.govt_home_language_cd,
2398 X_PRIOR_UG_INST => v_gse_upd3_rec.prior_ug_inst,
2399 X_GOVT_PRIOR_UG_INST => v_gse_upd3_rec.govt_prior_ug_inst,
2400 X_PRIOR_OTHER_QUAL => v_gse_upd3_rec.prior_other_qual,
2401 X_PRIOR_POST_GRAD => v_gse_upd3_rec.prior_post_grad,
2402 X_PRIOR_DEGREE => v_gse_upd3_rec.prior_degree,
2403 X_PRIOR_SUBDEG_NOTAFE => v_gse_upd3_rec.prior_subdeg_notafe,
2404 X_PRIOR_SUBDEG_TAFE => v_gse_upd3_rec.prior_subdeg_tafe,
2405 X_PRIOR_SECED_TAFE => v_gse_upd3_rec.prior_seced_tafe,
2406 X_PRIOR_SECED_SCHOOL => v_gse_upd3_rec.prior_seced_school,
2407 X_PRIOR_TAFE_AWARD => v_gse_upd3_rec.prior_tafe_award,
2408 X_PRIOR_STUDIES_EXEMPTION => v_gse_upd3_rec.prior_studies_exemption,
2409 X_EXEMPTION_INSTITUTION_CD => v_gse_upd3_rec.exemption_institution_cd,
2410 X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd3_rec.govt_exemption_institution_cd,
2411 X_ATTENDANCE_MODE => v_gse_upd3_rec.attendance_mode,
2412 X_GOVT_ATTENDANCE_MODE => v_gse_upd3_rec.govt_attendance_mode,
2413 X_ATTENDANCE_TYPE => v_gse_upd3_rec.attendance_type,
2414 X_GOVT_ATTENDANCE_TYPE => v_gse_upd3_rec.govt_attendance_type,
2415 X_COMMENCEMENT_DT => v_gse_upd3_rec.commencement_dt,
2416 X_MAJOR_COURSE => v_gse_upd3_rec.major_course,
2417 X_TERTIARY_ENTRANCE_SCORE => v_gse_upd3_rec.tertiary_entrance_score,
2418 X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd3_rec.basis_for_admission_type,
2419 X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd3_rec.govt_basis_for_admission_type,
2420 X_GOVT_DISABILITY => v_gse_upd3_rec.govt_disability,
2421 X_MODE => 'R');
2422
2423 END LOOP;
2424 EXCEPTION
2425 WHEN e_resource_busy THEN
2426 p_message_name := 'IGS_ST_GOV_ENR_MAJ_CANT_UPD';
2427 RETURN FALSE;
2428 WHEN OTHERS THEN
2429 RAISE;
2430 END;
2431 END LOOP;
2432 v_tmp_person_id := v_gse2.person_id;
2433 END IF;
2434 v_person_id := v_gse2.person_id;
2435 v_course_cd := v_gse2.course_cd;
2436 END IF;
2437 END LOOP;
2438 COMMIT;
2439
2440 END IF;
2441 IF (p_submission_number IN (1, 2)) THEN
2442 -- Determine the correct commencement dates for Government Student
2443 -- Liability and Government Student Enrolment records.
2444 FOR v_gsli_rec IN c_gsli LOOP
2445 -- Set default of derived commencement date to the government
2446 -- student liability (student IGS_PS_COURSE attempt) commencement date.
2447 v_derived_commencement_dt := v_gsli_rec.commencement_dt;
2448 -- Derive reportable commencement date
2449 v_dummy := IGS_ST_GEN_001.stap_get_comm_stdnt(
2450 v_gsli_rec.person_id,
2451 v_gsli_rec.course_cd,
2452 v_gsli_rec.version_number,
2453 v_derived_commencement_dt, -- IN OUT NOCOPY
2454 p_submission_yr);
2455 -- If the derived commencement date is different to the government
2456 -- student liability commencement date then we want to update the
2457 -- government student liability and government student enrolment
2458 -- commencement dates.
2459 IF v_derived_commencement_dt <> v_gsli_rec.commencement_dt THEN
2460 FOR v_gsli_upd_comm_rec IN c_gsli_upd_commencement(
2461 v_gsli_rec.person_id,
2462 v_gsli_rec.course_cd) LOOP
2463 BEGIN
2464
2465 v_gsli_upd_comm_rec.commencement_dt := v_derived_commencement_dt;
2466 IGS_ST_GVT_STDNT_LBL_PKG.UPDATE_ROW(
2467 X_ROWID => v_gsli_upd_comm_rec.rowid,
2468 X_SUBMISSION_YR => v_gsli_upd_comm_rec.submission_yr,
2469 X_SUBMISSION_NUMBER => v_gsli_upd_comm_rec.submission_number,
2470 X_PERSON_ID => v_gsli_upd_comm_rec.person_id,
2471 X_COURSE_CD => v_gsli_upd_comm_rec.course_cd,
2472 X_GOVT_SEMESTER => v_gsli_upd_comm_rec.govt_semester,
2473 X_VERSION_NUMBER => v_gsli_upd_comm_rec.version_number,
2474 X_HECS_PAYMENT_OPTION => v_gsli_upd_comm_rec.hecs_payment_option,
2475 X_GOVT_HECS_PAYMENT_OPTION => v_gsli_upd_comm_rec.govt_hecs_payment_option,
2476 X_TOTAL_EFTSU => v_gsli_upd_comm_rec.total_eftsu,
2477 X_INDUSTRIAL_EFTSU => v_gsli_upd_comm_rec.industrial_eftsu,
2478 X_HECS_PREXMT_EXIE => v_gsli_upd_comm_rec.hecs_prexmt_exie,
2479 X_HECS_AMOUNT_PAID => v_gsli_upd_comm_rec.hecs_amount_paid,
2480 X_TUITION_FEE => v_gsli_upd_comm_rec.tuition_fee,
2481 X_DIFFERENTIAL_HECS_IND => v_gsli_upd_comm_rec.differential_hecs_ind,
2482 X_BIRTH_DT => v_gsli_upd_comm_rec.birth_dt,
2483 X_SEX => v_gsli_upd_comm_rec.sex,
2484 X_CITIZENSHIP_CD => v_gsli_upd_comm_rec.citizenship_cd,
2485 X_GOVT_CITIZENSHIP_CD => v_gsli_upd_comm_rec.govt_citizenship_cd,
2486 X_PERM_RESIDENT_CD => v_gsli_upd_comm_rec.perm_resident_cd,
2487 X_GOVT_PERM_RESIDENT_CD => v_gsli_upd_comm_rec.govt_perm_resident_cd,
2488 X_COMMENCEMENT_DT => v_gsli_upd_comm_rec.commencement_dt,
2489 X_MODE => 'R');
2490
2491 EXCEPTION
2492 WHEN e_resource_busy THEN
2493 p_message_name := 'IGS_ST_GOV_LIAB_CANT_UPD';
2494 RETURN FALSE;
2495 WHEN OTHERS THEN
2496 RAISE;
2497 END;
2498 END LOOP;
2499 END IF;
2500 END LOOP;
2501 IF p_submission_number = 1 THEN
2502 -- Determine the correct commencement dates for Government Enrolment records
2503 FOR v_gse_enrolment_rec IN c_gse_enrolment LOOP
2504 -- Set default of derived commencement date to the government
2505 -- student enrolment (student IGS_PS_COURSE attempt) commencement date.
2506 v_derived_commencement_dt := v_gse_enrolment_rec.commencement_dt;
2507 -- Derive reportable commencement date
2508 v_dummy := IGS_ST_GEN_001.stap_get_comm_stdnt(
2509 v_gse_enrolment_rec.person_id,
2510 v_gse_enrolment_rec.course_cd,
2511 v_gse_enrolment_rec.version_number,
2512 v_derived_commencement_dt, -- IN OUT NOCOPY
2513 p_submission_yr);
2514 -- If the derived commencement date is different to the government
2515 -- student liability commencement date then we want to update the
2516 -- government student enrolment commencement dates.
2517 IF v_derived_commencement_dt <> v_gse_enrolment_rec.commencement_dt THEN
2518 FOR v_gse_upd_comm_rec IN c_gse_upd_commencement(
2519 v_gse_enrolment_rec.person_id,
2520 v_gse_enrolment_rec.course_cd) LOOP
2521 BEGIN
2522
2523 v_gse_upd_comm_rec.commencement_dt := v_derived_commencement_dt;
2524
2525 IGS_ST_GOVT_STDNT_EN_PKG.UPDATE_ROW(
2526 X_ROWID => v_gse_upd_comm_rec.rowid,
2527 X_SUBMISSION_YR => v_gse_upd_comm_rec.submission_yr,
2528 X_SUBMISSION_NUMBER => v_gse_upd_comm_rec.submission_number,
2529 X_PERSON_ID => v_gse_upd_comm_rec.person_id,
2530 X_COURSE_CD => v_gse_upd_comm_rec.course_cd,
2531 X_VERSION_NUMBER => v_gse_upd_comm_rec.version_number,
2532 X_BIRTH_DT => v_gse_upd_comm_rec.birth_dt,
2533 X_SEX => v_gse_upd_comm_rec.sex,
2534 X_ABORIG_TORRES_CD => v_gse_upd_comm_rec.aborig_torres_cd,
2535 X_GOVT_ABORIG_TORRES_CD => v_gse_upd_comm_rec.govt_aborig_torres_cd,
2536 X_CITIZENSHIP_CD => v_gse_upd_comm_rec.citizenship_cd,
2537 X_GOVT_CITIZENSHIP_CD => v_gse_upd_comm_rec.govt_citizenship_cd,
2538 X_PERM_RESIDENT_CD => v_gse_upd_comm_rec.perm_resident_cd,
2539 X_GOVT_PERM_RESIDENT_CD => v_gse_upd_comm_rec.govt_perm_resident_cd,
2540 X_HOME_LOCATION => v_gse_upd_comm_rec.home_location,
2541 X_GOVT_HOME_LOCATION => v_gse_upd_comm_rec.govt_home_location,
2542 X_TERM_LOCATION => v_gse_upd_comm_rec.term_location,
2543 X_GOVT_TERM_LOCATION => v_gse_upd_comm_rec.govt_term_location,
2544 X_BIRTH_COUNTRY_CD => v_gse_upd_comm_rec.birth_country_cd,
2545 X_GOVT_BIRTH_COUNTRY_CD => v_gse_upd_comm_rec.govt_birth_country_cd,
2546 X_YR_ARRIVAL => v_gse_upd_comm_rec.yr_arrival,
2547 X_HOME_LANGUAGE_CD => v_gse_upd_comm_rec.home_language_cd,
2548 X_GOVT_HOME_LANGUAGE_CD => v_gse_upd_comm_rec.govt_home_language_cd,
2549 X_PRIOR_UG_INST => v_gse_upd_comm_rec.prior_ug_inst,
2550 X_GOVT_PRIOR_UG_INST => v_gse_upd_comm_rec.govt_prior_ug_inst,
2551 X_PRIOR_OTHER_QUAL => v_gse_upd_comm_rec.prior_other_qual,
2552 X_PRIOR_POST_GRAD => v_gse_upd_comm_rec.prior_post_grad,
2553 X_PRIOR_DEGREE => v_gse_upd_comm_rec.prior_degree,
2554 X_PRIOR_SUBDEG_NOTAFE => v_gse_upd_comm_rec.prior_subdeg_notafe,
2555 X_PRIOR_SUBDEG_TAFE => v_gse_upd_comm_rec.prior_subdeg_tafe,
2556 X_PRIOR_SECED_TAFE => v_gse_upd_comm_rec.prior_seced_tafe,
2557 X_PRIOR_SECED_SCHOOL => v_gse_upd_comm_rec.prior_seced_school,
2558 X_PRIOR_TAFE_AWARD => v_gse_upd_comm_rec.prior_tafe_award,
2559 X_PRIOR_STUDIES_EXEMPTION => v_gse_upd_comm_rec.prior_studies_exemption,
2560 X_EXEMPTION_INSTITUTION_CD => v_gse_upd_comm_rec.exemption_institution_cd,
2561 X_GOVT_EXEMPT_INSTITU_CD => v_gse_upd_comm_rec.govt_exemption_institution_cd,
2562 X_ATTENDANCE_MODE => v_gse_upd_comm_rec.attendance_mode,
2563 X_GOVT_ATTENDANCE_MODE => v_gse_upd_comm_rec.govt_attendance_mode,
2564 X_ATTENDANCE_TYPE => v_gse_upd_comm_rec.attendance_type,
2565 X_GOVT_ATTENDANCE_TYPE => v_gse_upd_comm_rec.govt_attendance_type,
2566 X_COMMENCEMENT_DT => v_gse_upd_comm_rec.commencement_dt,
2567 X_MAJOR_COURSE => v_gse_upd_comm_rec.major_course,
2568 X_TERTIARY_ENTRANCE_SCORE => v_gse_upd_comm_rec.tertiary_entrance_score,
2569 X_BASIS_FOR_ADMISSION_TYPE => v_gse_upd_comm_rec.basis_for_admission_type,
2570 X_GOVT_BASIS_FOR_ADM_TYPE => v_gse_upd_comm_rec.govt_basis_for_admission_type,
2571 X_GOVT_DISABILITY => v_gse_upd_comm_rec.govt_disability,
2572 X_MODE => 'R');
2573
2574 EXCEPTION
2575 WHEN e_resource_busy THEN
2576 p_message_name := 'IGS_ST_GOV_ENRL_DT_CANT_UPD';
2577 RETURN FALSE;
2578 WHEN OTHERS THEN
2579 RAISE;
2580 END;
2581 END LOOP;
2582 END IF;
2583 END LOOP;
2584 END IF;
2585 COMMIT;
2586
2587 -- Update the Government Student Liability values for the inserted records
2588 FOR v_update_total_eftsu IN c_update_total_eftsu LOOP
2589 BEGIN
2590 FOR v_gsli_upd2_rec IN c_gsli_upd2(
2591 v_update_total_eftsu.person_id,
2592 v_update_total_eftsu.course_cd,
2593 v_update_total_eftsu.govt_semester) LOOP
2594
2595 v_gsli_upd2_rec.total_eftsu := v_update_total_eftsu.v_upd_total_eftsu;
2596
2597 IGS_ST_GVT_STDNT_LBL_PKG.UPDATE_ROW(
2598 X_ROWID => v_gsli_upd2_rec.rowid,
2599 X_SUBMISSION_YR => v_gsli_upd2_rec.submission_yr,
2600 X_SUBMISSION_NUMBER => v_gsli_upd2_rec.submission_number,
2601 X_PERSON_ID => v_gsli_upd2_rec.person_id,
2602 X_COURSE_CD => v_gsli_upd2_rec.course_cd,
2603 X_GOVT_SEMESTER => v_gsli_upd2_rec.govt_semester,
2604 X_VERSION_NUMBER => v_gsli_upd2_rec.version_number,
2605 X_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.hecs_payment_option,
2606 X_GOVT_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.govt_hecs_payment_option,
2607 X_TOTAL_EFTSU => v_gsli_upd2_rec.total_eftsu,
2608 X_INDUSTRIAL_EFTSU => v_gsli_upd2_rec.industrial_eftsu,
2609 X_HECS_PREXMT_EXIE => v_gsli_upd2_rec.hecs_prexmt_exie,
2610 X_HECS_AMOUNT_PAID => v_gsli_upd2_rec.hecs_amount_paid,
2611 X_TUITION_FEE => v_gsli_upd2_rec.tuition_fee,
2612 X_DIFFERENTIAL_HECS_IND => v_gsli_upd2_rec.differential_hecs_ind,
2613 X_BIRTH_DT => v_gsli_upd2_rec.birth_dt,
2614 X_SEX => v_gsli_upd2_rec.sex,
2615 X_CITIZENSHIP_CD => v_gsli_upd2_rec.citizenship_cd,
2616 X_GOVT_CITIZENSHIP_CD => v_gsli_upd2_rec.govt_citizenship_cd,
2617 X_PERM_RESIDENT_CD => v_gsli_upd2_rec.perm_resident_cd,
2618 X_GOVT_PERM_RESIDENT_CD => v_gsli_upd2_rec.govt_perm_resident_cd,
2619 X_COMMENCEMENT_DT => v_gsli_upd2_rec.commencement_dt,
2620 X_MODE => 'R');
2621
2622
2623
2624
2625
2626 IF v_update_total_eftsu.v_upd_total_eftsu > 1 THEN
2627 IF v_logged_ind = FALSE THEN
2628
2629 v_logged_ind := TRUE;
2630 END IF;
2631 IGS_GE_GEN_003.genp_ins_log_entry(
2632 v_s_log_type,
2633 v_creation_dt,
2634 'IGS_PE_PERSON IGS_PS_COURSE,' ||
2635 TO_CHAR(v_update_total_eftsu.person_id) || ',' ||
2636 v_update_total_eftsu.course_cd,
2637 4222,
2638 NULL);
2639 END IF;
2640 END LOOP;
2641 EXCEPTION
2642 WHEN e_resource_busy THEN
2643 p_message_name := 'IGS_ST_GOV_LIABILITY_CANT_UPD';
2644 RETURN FALSE;
2645 WHEN OTHERS THEN
2646 RAISE;
2647 END;
2648 END LOOP;
2649 COMMIT;
2650
2651 FOR v_update_indus_eftsu IN c_update_indus_eftsu LOOP
2652 BEGIN
2653 FOR v_gsli_upd2_rec IN c_gsli_upd2(
2654 v_update_indus_eftsu.person_id,
2655 v_update_indus_eftsu.course_cd,
2656 v_update_indus_eftsu.govt_semester) LOOP
2657
2658 v_gsli_upd2_rec.industrial_eftsu := v_update_indus_eftsu.v_upd_indus_eftsu;
2659
2660 IGS_ST_GVT_STDNT_LBL_PKG.UPDATE_ROW(
2661 X_ROWID => v_gsli_upd2_rec.rowid,
2662 X_SUBMISSION_YR => v_gsli_upd2_rec.submission_yr,
2663 X_SUBMISSION_NUMBER => v_gsli_upd2_rec.submission_number,
2664 X_PERSON_ID => v_gsli_upd2_rec.person_id,
2665 X_COURSE_CD => v_gsli_upd2_rec.course_cd,
2666 X_GOVT_SEMESTER => v_gsli_upd2_rec.govt_semester,
2667 X_VERSION_NUMBER => v_gsli_upd2_rec.version_number,
2668 X_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.hecs_payment_option,
2669 X_GOVT_HECS_PAYMENT_OPTION => v_gsli_upd2_rec.govt_hecs_payment_option,
2670 X_TOTAL_EFTSU => v_gsli_upd2_rec.total_eftsu,
2671 X_INDUSTRIAL_EFTSU => v_gsli_upd2_rec.industrial_eftsu,
2672 X_HECS_PREXMT_EXIE => v_gsli_upd2_rec.hecs_prexmt_exie,
2673 X_HECS_AMOUNT_PAID => v_gsli_upd2_rec.hecs_amount_paid,
2674 X_TUITION_FEE => v_gsli_upd2_rec.tuition_fee,
2675 X_DIFFERENTIAL_HECS_IND => v_gsli_upd2_rec.differential_hecs_ind,
2676 X_BIRTH_DT => v_gsli_upd2_rec.birth_dt,
2677 X_SEX => v_gsli_upd2_rec.sex,
2678 X_CITIZENSHIP_CD => v_gsli_upd2_rec.citizenship_cd,
2679 X_GOVT_CITIZENSHIP_CD => v_gsli_upd2_rec.govt_citizenship_cd,
2680 X_PERM_RESIDENT_CD => v_gsli_upd2_rec.perm_resident_cd,
2681 X_GOVT_PERM_RESIDENT_CD => v_gsli_upd2_rec.govt_perm_resident_cd,
2682 X_COMMENCEMENT_DT => v_gsli_upd2_rec.commencement_dt,
2683 X_MODE => 'R');
2684
2685
2686 IF v_update_indus_eftsu.v_upd_indus_eftsu > 1 THEN
2687 IF v_logged_ind = FALSE THEN
2688 v_logged_ind := TRUE;
2689 END IF;
2690 IGS_GE_GEN_003.genp_ins_log_entry(
2691 v_s_log_type,
2692 v_creation_dt,
2693 'IGS_PE_PERSON IGS_PS_COURSE,' ||
2694 TO_CHAR(v_update_indus_eftsu.person_id) || ',' ||
2695 v_update_indus_eftsu.course_cd,
2696 4223,
2697 NULL);
2698 END IF;
2699 END LOOP;
2700 EXCEPTION
2701 WHEN e_resource_busy THEN
2702 p_message_name := 'IGS_ST_GOV_LIA_INDUS_CANT_UPD';
2703 RETURN FALSE;
2704 WHEN OTHERS THEN
2705 RAISE;
2706 END;
2707 END LOOP;
2708 COMMIT;
2709 END IF;
2710 -- commit all the changes made
2711 COMMIT;
2712 -- set the default message number and return type
2713 p_message_name := NULL;
2714 RETURN TRUE;
2715 EXCEPTION
2716 WHEN OTHERS THEN
2717 IF c_govt_snpsht_ctl%ISOPEN THEN
2718 CLOSE c_govt_snpsht_ctl;
2719 END IF;
2720 IF c_gsc%ISOPEN THEN
2721 CLOSE c_gsc;
2722 END IF;
2723 IF c_essc%ISOPEN THEN
2724 CLOSE c_essc;
2725 END IF;
2726 IF c_get_att_type%ISOPEN THEN
2727 CLOSE c_get_att_type;
2728 END IF;
2729 IF c_get_att_mode_1%ISOPEN THEN
2730 CLOSE c_get_att_mode_1;
2731 END IF;
2732 IF c_get_att_mode_2%ISOPEN THEN
2733 CLOSE c_get_att_mode_2;
2734 END IF;
2735 IF c_get_att_mode_3%ISOPEN THEN
2736 CLOSE c_get_att_mode_3;
2737 END IF;
2738 IF c_enr_snpsht_rec%ISOPEN THEN
2739 CLOSE c_enr_snpsht_rec;
2740 END IF;
2741 IF c_alias_val%ISOPEN THEN
2742 CLOSE c_alias_val;
2743 END IF;
2744 IF c_get_api%ISOPEN THEN
2745 CLOSE c_get_api;
2746 END IF;
2747 IF c_gse_att_mode%ISOPEN THEN
2748 CLOSE c_gse_att_mode;
2749 END IF;
2750 IF c_gslo%ISOPEN THEN
2751 CLOSE c_gslo;
2752 END IF;
2753 IF c_sua_ucl_um%ISOPEN THEN
2754 CLOSE c_sua_ucl_um;
2755 END IF;
2756 IF c_att%ISOPEN THEN
2757 CLOSE c_att;
2758 END IF;
2759 IF c_gse%ISOPEN THEN
2760 CLOSE c_gse;
2761 END IF;
2762 IF c_gse2%ISOPEN THEN
2763 CLOSE c_gse2;
2764 END IF;
2765 IF c_gse_sca%ISOPEN THEN
2766 CLOSE c_gse_sca;
2767 END IF;
2768 IF c_get_indus_ind%ISOPEN THEN
2769 CLOSE c_get_indus_ind;
2770 END IF;
2771 IF c_update_total_eftsu%ISOPEN THEN
2772 CLOSE c_update_total_eftsu;
2773 END IF;
2774 IF c_update_indus_eftsu%ISOPEN THEN
2775 CLOSE c_update_indus_eftsu;
2776 END IF;
2777 IF c_gsc_upd%ISOPEN THEN
2778 CLOSE c_gsc_upd;
2779 END IF;
2780 IF c_essc_upd%ISOPEN THEN
2781 CLOSE c_essc_upd;
2782 END IF;
2783 IF c_gsli_upd%ISOPEN THEN
2784 CLOSE c_gsli_upd;
2785 END IF;
2786 IF c_gsli_upd2%ISOPEN THEN
2787 CLOSE c_gsli_upd2;
2788 END IF;
2789 IF c_gslo_upd%ISOPEN THEN
2790 CLOSE c_gslo_upd;
2791 END IF;
2792 IF c_gse_upd%ISOPEN THEN
2793 CLOSE c_gse_upd;
2794 END IF;
2795 IF c_gse_upd2%ISOPEN THEN
2796 CLOSE c_gse_upd2;
2797 END IF;
2798 IF c_gse_upd3%ISOPEN THEN
2799 CLOSE c_gse_upd3;
2800 END IF;
2801 IF c_gsli%ISOPEN THEN
2802 CLOSE c_gsli;
2803 END IF;
2804 IF c_gsli_upd_commencement%ISOPEN THEN
2805 CLOSE c_gsli_upd_commencement;
2806 END IF;
2807 IF c_gse_enrolment%ISOPEN THEN
2808 CLOSE c_gse_enrolment;
2809 END IF;
2810 IF c_gse_upd_commencement%ISOPEN THEN
2811 CLOSE c_gse_upd_commencement;
2812 END IF;
2813 RAISE;
2814 END;
2815 EXCEPTION
2816 WHEN OTHERS THEN
2817 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2818 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stap_ins_govt_snpsht');
2819 IGS_GE_MSG_STACK.ADD;
2820 App_Exception.Raise_Exception;
2821 END stap_ins_govt_snpsht;
2822
2823 Procedure Stap_Ins_Gsch(
2824 p_submission_yr IN NUMBER ,
2825 p_submission_number IN NUMBER )
2826 AS
2827 gv_other_detail VARCHAR2(255);
2828 BEGIN -- stap_ins_gsch
2829 -- comment.
2830 DECLARE
2831 -- table to hold old units
2832 TYPE r_units_typ IS RECORD (
2833 unit_cd IGS_ST_GVT_STDNTLOAD.unit_cd%TYPE,
2834 sua_cal_type IGS_ST_GVT_STDNTLOAD.sua_cal_type%TYPE,
2835 sua_ci_sequence_number IGS_ST_GVT_STDNTLOAD.sua_ci_sequence_number%TYPE,
2836 tr_org_unit_cd IGS_ST_GVT_STDNTLOAD.tr_org_unit_cd%TYPE,
2837 tr_ou_start_dt IGS_ST_GVT_STDNTLOAD.tr_ou_start_dt%TYPE,
2838 govt_discipline_group_cd IGS_ST_GVT_STDNTLOAD.govt_discipline_group_cd%TYPE,
2839 eftsu IGS_ST_GVT_STDNTLOAD.eftsu%TYPE,
2840 industrial_ind IGS_ST_GVT_STDNTLOAD.industrial_ind%TYPE,
2841 uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE);
2842 r_units r_units_typ;
2843 TYPE t_units_typ IS TABLE OF r_units%TYPE
2844 INDEX BY BINARY_INTEGER;
2845 t_old_units t_units_typ;
2846 t_new_units t_units_typ;
2847 t_blank t_units_typ;
2848 cst_exempt CONSTANT VARCHAR2(6) := 'EXEMPT';
2849 CURSOR c_ess IS
2850 SELECT UNIQUE ess.snapshot_dt_time
2851 FROM IGS_EN_ST_SNAPSHOT ess
2852 ORDER BY ess.snapshot_dt_time DESC;
2853 CURSOR c_gsli_gslc IS
2854 SELECT gsli.person_id,
2855 gsli.course_cd,
2856 gsli.version_number,
2857 gsli.govt_semester,
2858 gsli.hecs_payment_option,
2859 gsli.differential_hecs_ind,
2860 gsli.hecs_prexmt_exie,
2861 gsli.hecs_amount_paid,
2862 gsli.citizenship_cd,
2863 gsli.perm_resident_cd,
2864 gslc.cal_type,
2865 gslc.ci_sequence_number,
2866 ghpo.s_hecs_payment_type
2867 FROM IGS_ST_GVT_STDNT_LBL gsli,
2868 IGS_ST_GVTSEMLOAD_CA gslc,
2869 IGS_FI_HECS_PAY_OPTN hpo,
2870 IGS_FI_GOV_HEC_PA_OP ghpo
2871 WHERE gsli.submission_yr = p_submission_yr AND
2872 gsli.submission_number = p_submission_number AND
2873 gslc.submission_yr = gsli.submission_yr AND
2874 gslc.submission_number = gsli.submission_number AND
2875 gslc.govt_semester = gsli.govt_semester AND
2876 hpo.hecs_payment_option(+) = gsli.hecs_payment_option AND
2877 ghpo.govt_hecs_payment_option(+) = hpo.hecs_payment_option
2878 ORDER BY gsli.person_id,
2879 gsli.course_cd,
2880 gsli.govt_semester;
2881 CURSOR c_scho_hpo_ghpo (
2882 cp_person_id IGS_EN_STDNTPSHECSOP.person_id%TYPE,
2883 cp_course_cd IGS_EN_STDNTPSHECSOP.course_cd%TYPE,
2884 cp_effective_dt IGS_EN_STDNTPSHECSOP.start_dt%TYPE) IS
2885 SELECT scho.differential_hecs_ind,
2886 scho.hecs_payment_option,
2887 hpo.govt_hecs_payment_option,
2888 ghpo.s_hecs_payment_type
2889 FROM IGS_EN_STDNTPSHECSOP scho,
2890 IGS_FI_HECS_PAY_OPTN hpo,
2891 IGS_FI_GOV_HEC_PA_OP ghpo
2892 WHERE scho.person_id = cp_person_id AND
2893 scho.course_cd = cp_course_cd AND
2894 scho.start_dt <= cp_effective_dt AND
2895 (scho.end_dt IS NULL OR
2896 scho.end_dt >= cp_effective_dt) AND
2897 hpo.hecs_payment_option = scho.hecs_payment_option AND
2898 ghpo.govt_hecs_payment_option = hpo.hecs_payment_option
2899 ORDER BY scho.end_dt;
2900 CURSOR c_daiv_sgcc (
2901 cp_sua_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
2902 cp_sua_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE) IS
2903 SELECT daiv.alias_val
2904 FROM IGS_CA_DA_INST_V daiv,
2905 IGS_GE_S_GEN_CAL_CON sgcc
2906 WHERE daiv.cal_type = cp_sua_cal_type AND
2907 daiv.ci_sequence_number = cp_sua_ci_sequence_number AND
2908 daiv.dt_alias = sgcc.census_dt_alias
2909 ORDER BY daiv.alias_val;
2910 CURSOR c_gsc1 (
2911 cp_person_id IGS_ST_GVT_SPSHT_CHG.person_id%TYPE,
2912 cp_course_cd IGS_ST_GVT_SPSHT_CHG.course_cd%TYPE,
2913 cp_crv_version_number IGS_ST_GVT_SPSHT_CHG.version_number%TYPE,
2914 cp_govt_semester IGS_ST_GVT_SPSHT_CHG.govt_semester%TYPE,
2915 cp_old_hecs_prexmt_exie IGS_ST_GVT_SPSHT_CHG.old_hecs_prexmt_exie%TYPE,
2916 cp_old_hecs_amount_paid IGS_ST_GVT_SPSHT_CHG.old_hecs_amount_paid%TYPE,
2917 cp_old_hecs_payment_option IGS_ST_GVT_SPSHT_CHG.old_hecs_payment_option%TYPE,
2918 cp_old_differential_hecs_ind
2919 IGS_ST_GVT_SPSHT_CHG.old_differential_hecs_ind%TYPE,
2920 cp_hecs_amount_paid IGS_ST_GVT_SPSHT_CHG.hecs_amount_paid%TYPE,
2921 cp_hecs_prexmt_exie IGS_ST_GVT_SPSHT_CHG.hecs_prexmt_exie%TYPE,
2922 cp_hecs_payment_option IGS_ST_GVT_SPSHT_CHG.hecs_payment_option%TYPE,
2923 cp_differential_hecs_ind IGS_ST_GVT_SPSHT_CHG.differential_hecs_ind%TYPE) IS
2924 SELECT 'x'
2925 FROM IGS_ST_GVT_SPSHT_CHG gsc
2926 WHERE gsc.submission_yr = p_submission_yr AND
2927 gsc.submission_number = p_submission_number AND
2928 gsc.person_id = cp_person_id AND
2929 gsc.course_cd = cp_course_cd AND
2930 gsc.version_number = cp_crv_version_number AND
2931 gsc.govt_semester = cp_govt_semester AND
2932 gsc.old_hecs_prexmt_exie = cp_old_hecs_prexmt_exie AND
2933 gsc.old_hecs_amount_paid = cp_old_hecs_amount_paid AND
2934 gsc.old_hecs_payment_option = cp_old_hecs_payment_option AND
2935 gsc.old_differential_hecs_ind = cp_old_differential_hecs_ind AND
2936 gsc.hecs_amount_paid = cp_hecs_amount_paid AND
2937 gsc.hecs_prexmt_exie = cp_hecs_prexmt_exie AND
2938 gsc.hecs_payment_option = cp_hecs_payment_option AND
2939 gsc.differential_hecs_ind = cp_differential_hecs_ind;
2940 CURSOR c_scho1 (
2941 cp_person_id IGS_EN_STDNTPSHECSOP.person_id%TYPE,
2942 cp_course_cd IGS_EN_STDNTPSHECSOP.course_cd%TYPE,
2943 cp_old_hecs_payment_option
2944 IGS_EN_STDNTPSHECSOP.hecs_payment_option%TYPE,
2945 cp_old_differential_hecs_ind
2946 IGS_EN_STDNTPSHECSOP.differential_hecs_ind%TYPE) IS
2947 SELECT scho.last_updated_by,
2948 scho.last_update_date
2949 FROM IGS_EN_STDNTPSHECSOP scho
2950 WHERE scho.person_id = cp_person_id AND
2951 scho.course_cd = cp_course_cd AND
2952 scho.end_dt IS NOT NULL AND
2953 scho.hecs_payment_option = cp_old_hecs_payment_option AND
2954 scho.differential_hecs_ind = cp_old_differential_hecs_ind
2955 ORDER BY scho.end_dt DESC;
2956 CURSOR c_scho2 (
2957 cp_person_id IGS_EN_STDNTPSHECSOP.person_id%TYPE,
2958 cp_course_cd IGS_EN_STDNTPSHECSOP.course_cd%TYPE,
2959 cp_effective_dt IGS_EN_STDNTPSHECSOP.end_dt%TYPE) IS
2960 SELECT scho.last_updated_by,
2961 scho.last_update_date
2962 FROM IGS_EN_STDNTPSHECSOP scho
2963 WHERE scho.person_id = cp_person_id AND
2964 scho.course_cd = cp_course_cd AND
2965 scho.end_dt IS NOT NULL -- AND
2966 -- scho.end_dt > cp_effective_dt
2967 ORDER BY scho.end_dt DESC;
2968 CURSOR c_gslo (
2969 cp_govt_semester IGS_ST_GVT_STDNTLOAD.govt_semester%TYPE,
2970 cp_person_id IGS_ST_GVT_STDNTLOAD.person_id%TYPE,
2971 cp_course_cd IGS_ST_GVT_STDNTLOAD.course_cd%TYPE) IS
2972 SELECT gslo.unit_cd,
2973 gslo.sua_cal_type,
2974 gslo.sua_ci_sequence_number,
2975 gslo.tr_org_unit_cd,
2976 gslo.tr_ou_start_dt,
2977 gslo.govt_discipline_group_cd,
2978 gslo.eftsu,
2979 gslo.industrial_ind,
2980 uoo.uoo_id
2981 FROM IGS_ST_GVT_STDNTLOAD gslo,
2982 igs_ps_unit_ofr_opt uoo
2983 WHERE gslo.submission_yr = p_submission_yr AND
2984 gslo.submission_number = p_submission_number AND
2985 gslo.govt_semester = cp_govt_semester AND
2986 gslo.person_id = cp_person_id AND
2987 gslo.course_cd = cp_course_cd AND
2988 gslo.unit_cd = uoo.unit_cd AND
2989 gslo.uv_version_number = uoo.version_number AND
2990 gslo.sua_cal_type = uoo.cal_type AND
2991 gslo.sua_ci_sequence_number = uoo.ci_sequence_number AND
2992 gslo.sua_location_cd = uoo.location_cd AND
2993 gslo.unit_class = uoo.unit_class;
2994 CURSOR c_ess_uv_gslc (
2995 cp_snapshot_dt_time IGS_EN_ST_SNAPSHOT.snapshot_dt_time%TYPE,
2996 cp_govt_semester IGS_ST_GVTSEMLOAD_CA.govt_semester%TYPE,
2997 cp_person_id IGS_EN_ST_SNAPSHOT.person_id%TYPE,
2998 cp_course_cd IGS_EN_ST_SNAPSHOT.course_cd%TYPE) IS
2999 SELECT ess.unit_cd,
3000 ess.sua_cal_type,
3001 ess.sua_ci_sequence_number,
3002 ess.tr_org_unit_cd,
3003 ess.tr_ou_start_dt,
3004 ess.govt_discipline_group_cd,
3005 ess.eftsu,
3006 uv.industrial_ind,
3007 ess.crv_version_number,
3008 ess.uv_version_number,
3009 ess.enrolled_dt,
3010 ess.discontinued_dt,
3011 ess.ci_cal_type,
3012 ess.ci_sequence_number,
3013 uoo.uoo_id
3014 FROM IGS_EN_ST_SNAPSHOT ess,
3015 IGS_PS_UNIT_VER uv,
3016 IGS_ST_GVTSEMLOAD_CA gslc,
3017 igs_ps_unit_ofr_opt uoo
3018 WHERE ess.snapshot_dt_time = cp_snapshot_dt_time AND
3019 ess.ci_cal_type = gslc.cal_type AND
3020 ess.ci_sequence_number = gslc.ci_sequence_number AND
3021 gslc.submission_yr = p_submission_yr AND
3022 gslc.submission_number = p_submission_number AND
3023 gslc.govt_semester = cp_govt_semester AND
3024 ess.person_id = cp_person_id AND
3025 ess.course_cd = cp_course_cd AND
3026 ess.govt_reportable_ind <> 'X' AND
3027 uv.unit_cd = ess.unit_cd AND
3028 uv.version_number = ess.uv_version_number AND
3029 ess.unit_cd = uoo.unit_cd AND
3030 ess.uv_version_number = uoo.version_number AND
3031 ess.sua_cal_type = uoo.cal_type AND
3032 ess.sua_ci_sequence_number = uoo.ci_sequence_number AND
3033 ess.sua_location_cd = uoo.location_cd AND
3034 ess.unit_class = uoo.unit_class ;
3035 CURSOR c_suah1 (
3036 cp_person_id IGS_EN_SU_ATTEMPT_H.person_id%TYPE,
3037 cp_course_cd IGS_EN_SU_ATTEMPT_H.course_cd%TYPE,
3038 cp_uoo_id IGS_EN_SU_ATTEMPT_H.uoo_id%TYPE) IS
3039 SELECT suah.last_updated_by,
3040 suah.last_update_date
3041 FROM IGS_EN_SU_ATTEMPT_H suah
3042 WHERE suah.person_id = cp_person_id AND
3043 suah.course_cd = cp_course_cd AND
3044 suah.uoo_id = cp_uoo_id AND
3045 (suah.enrolled_dt IS NOT NULL OR
3046 suah.discontinued_dt IS NOT NULL)
3047 ORDER BY suah.hist_end_dt DESC;
3048 CURSOR c_suah2 (
3049 cp_person_id IGS_EN_SU_ATTEMPT_H.person_id%TYPE,
3050 cp_course_cd IGS_EN_SU_ATTEMPT_H.course_cd%TYPE,
3051 cp_uoo_id IGS_EN_SU_ATTEMPT_H.uoo_id%TYPE) IS
3052 SELECT suah.last_updated_by,
3053 suah.last_update_date
3054 FROM IGS_EN_SU_ATTEMPT_H suah
3055 WHERE suah.person_id = cp_person_id AND
3056 suah.course_cd = cp_course_cd AND
3057 suah.uoo_id = cp_uoo_id
3058 ORDER BY suah.hist_end_dt DESC;
3059 CURSOR c_suah3 (
3060 cp_person_id IGS_EN_SU_ATTEMPT_H.person_id%TYPE,
3061 cp_course_cd IGS_EN_SU_ATTEMPT_H.course_cd%TYPE,
3062 cp_uoo_id IGS_EN_SU_ATTEMPT_H.uoo_id%TYPE) IS
3063 SELECT suah.last_updated_by,
3064 suah.last_update_date
3065 FROM IGS_EN_SU_ATTEMPT_H suah
3066 WHERE suah.person_id = cp_person_id AND
3067 suah.course_cd = cp_course_cd AND
3068 suah.uoo_id = cp_uoo_id AND
3069 (suah.enrolled_dt IS NOT NULL OR
3070 suah.discontinued_dt IS NOT NULL)
3071 ORDER BY suah.hist_end_dt DESC;
3072 CURSOR c_sua (
3073 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
3074 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
3075 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
3076 SELECT sua.last_updated_by,
3077 sua.last_update_date
3078 FROM IGS_EN_SU_ATTEMPT sua
3079 WHERE sua.person_id = cp_person_id AND
3080 sua.course_cd = cp_course_cd AND
3081 sua.uoo_id = cp_uoo_id
3082 ORDER BY sua.last_update_date DESC;
3083 CURSOR c_gse (
3084 cp_person_id IGS_ST_GOVT_STDNT_EN.person_id%TYPE,
3085 cp_course_cd IGS_ST_GOVT_STDNT_EN.course_cd%TYPE) IS
3086 SELECT gse.person_id,
3087 gse.course_cd,
3088 gse.version_number,
3089 gse.citizenship_cd,
3090 gse.perm_resident_cd,
3091 gse.prior_degree,
3092 gse.prior_post_grad
3093 FROM IGS_ST_GOVT_STDNT_EN gse
3094 WHERE gse.submission_yr = p_submission_yr AND
3095 gse.submission_number = 1 AND -- Government Enrolment only sub 1
3096 gse.person_id = cp_person_id AND
3097 gse.course_cd = cp_course_cd;
3098 CURSOR c_ps (
3099 cp_person_id IGS_PE_STATISTICS.person_id%TYPE,
3100 cp_effective_dt IGS_PE_STATISTICS.start_dt%TYPE) IS
3101 SELECT NVL(ps.citizenship_cd, '9'),
3102 NVL(ps.perm_resident_cd, '9'),
3103 ps.prior_degree,
3104 ps.prior_post_grad
3105 FROM IGS_PE_STATISTICS ps
3106 WHERE ps.person_id = cp_person_id AND
3107 ps.start_dt <= cp_effective_dt AND
3108 (ps.end_dt IS NULL OR
3109 ps.end_dt >= cp_effective_dt)
3110 ORDER BY ps.end_dt;
3111 CURSOR c_gsc2 (
3112 cp_person_id IGS_ST_GVT_SPSHT_CHG.person_id%TYPE,
3113 cp_course_cd IGS_ST_GVT_SPSHT_CHG.course_cd%TYPE,
3114 cp_crv_version_number IGS_ST_GVT_SPSHT_CHG.version_number%TYPE,
3115 cp_old_citizenship_cd IGS_ST_GVT_SPSHT_CHG.old_citizenship_cd%TYPE,
3116 cp_old_perm_resident_cd IGS_ST_GVT_SPSHT_CHG.old_perm_resident_cd%TYPE,
3117 cp_old_prior_degree IGS_ST_GVT_SPSHT_CHG.old_prior_degree%TYPE,
3118 cp_old_prior_post_grad IGS_ST_GVT_SPSHT_CHG.old_prior_post_grad%TYPE,
3119 cp_perm_resident_cd IGS_ST_GVT_SPSHT_CHG.perm_resident_cd%TYPE,
3120 cp_citizenship_cd IGS_ST_GVT_SPSHT_CHG.citizenship_cd%TYPE,
3121 cp_prior_degree IGS_ST_GVT_SPSHT_CHG.prior_degree%TYPE,
3122 cp_prior_post_grad IGS_ST_GVT_SPSHT_CHG.prior_post_grad%TYPE) IS
3123 SELECT 'x'
3124 FROM IGS_ST_GVT_SPSHT_CHG gsc
3125 WHERE gsc.submission_yr = p_submission_yr AND
3126 gsc.submission_number = p_submission_number AND
3127 gsc.person_id = cp_person_id AND
3128 gsc.course_cd = cp_course_cd AND
3129 gsc.version_number = cp_crv_version_number AND
3130 NVL(gsc.old_citizenship_cd, -1) = NVL(cp_old_citizenship_cd, -1) AND
3131 NVL(gsc.old_perm_resident_cd, -1) = NVL(cp_old_perm_resident_cd, -1) AND
3132 NVL(gsc.perm_resident_cd, -1) = NVL(cp_perm_resident_cd, -1) AND
3133 NVL(gsc.citizenship_cd, -1) = NVL(cp_citizenship_cd, -1);
3134 CURSOR c_ps2 (
3135 cp_person_id IGS_PE_STATISTICS.person_id%TYPE,
3136 cp_old_perm_resident_cd IGS_PE_STATISTICS.perm_resident_cd%TYPE,
3137 cp_old_citizenship_cd IGS_PE_STATISTICS.citizenship_cd%TYPE,
3138 cp_old_prior_degree IGS_PE_STATISTICS.prior_degree%TYPE) IS
3139 SELECT ps.last_updated_by,
3140 ps.last_update_date
3141 FROM IGS_PE_STATISTICS ps
3142 WHERE ps.person_id = cp_person_id AND
3143 ps.end_dt IS NOT NULL AND
3144 ps.perm_resident_cd = cp_old_perm_resident_cd AND
3145 ps.citizenship_cd = cp_old_citizenship_cd
3146 ORDER BY ps.end_dt DESC;
3147 CURSOR c_ps3 (
3148 cp_person_id IGS_PE_STATISTICS.person_id%TYPE,
3149 cp_effective_dt IGS_PE_STATISTICS.end_dt%TYPE) IS
3150 SELECT ps.last_updated_by,
3151 ps.last_update_date
3152 FROM IGS_PE_STATISTICS ps
3153 WHERE ps.person_id = cp_person_id AND
3154 ps.end_dt IS NOT NULL -- AND
3155 -- ps.end_dt > cp_effective_dt
3156 ORDER BY ps.end_dt DESC;
3157 cst_none CONSTANT VARCHAR2(4) := 'NONE';
3158 v_snapshot_dt_time IGS_EN_ST_SPSHT_CTL.snapshot_dt_time%TYPE;
3159 v_person_id IGS_ST_GVT_STDNT_LBL.person_id%TYPE;
3160 v_course_cd IGS_ST_GVT_STDNT_LBL.course_cd%TYPE;
3161 v_govt_semester IGS_ST_GVT_STDNT_LBL.govt_semester%TYPE;
3162 v_old_hecs_payment_option IGS_ST_GVT_STDNT_LBL.hecs_payment_option%TYPE;
3163 v_old_differential_hecs_ind IGS_ST_GVT_STDNT_LBL.differential_hecs_ind%TYPE;
3164 v_old_hecs_prexmt_exie IGS_ST_GVT_STDNT_LBL.hecs_prexmt_exie%TYPE;
3165 v_old_hecs_amount_paid IGS_ST_GVT_STDNT_LBL.hecs_amount_paid%TYPE;
3166 v_old_s_hecs_payment_type IGS_FI_GOV_HEC_PA_OP.s_hecs_payment_type%TYPE;
3167 v_load_cal_type IGS_ST_GVTSEMLOAD_CA.cal_type%TYPE;
3168 v_load_ci_sequence_number IGS_ST_GVTSEMLOAD_CA.ci_sequence_number%TYPE;
3169 v_differential_hecs_ind IGS_EN_STDNTPSHECSOP.differential_hecs_ind%TYPE;
3170 v_hecs_payment_option IGS_EN_STDNTPSHECSOP.hecs_payment_option%TYPE;
3171 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
3172 v_s_hecs_payment_type IGS_FI_GOV_HEC_PA_OP.s_hecs_payment_type%TYPE;
3173 v_hecs_prexmt_exie NUMBER;
3174 v_fee_cat IGS_AS_SCAH_EFFECTIVE_H_V.fee_cat%TYPE;
3175 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
3176 v_message_name VARCHAR2(30);
3177 v_hecs_amount_paid NUMBER;
3178 v_dummy VARCHAR2(1);
3179 v_unit_cd IGS_EN_ST_SNAPSHOT.unit_cd%TYPE;
3180 v_uv_version_number IGS_EN_ST_SNAPSHOT.uv_version_number%TYPE;
3181 v_sua_cal_type IGS_EN_ST_SNAPSHOT.sua_cal_type%TYPE;
3182 v_sua_ci_sequence_number IGS_EN_ST_SNAPSHOT.sua_ci_sequence_number%TYPE;
3183 v_tr_org_unit_cd IGS_EN_ST_SNAPSHOT.tr_org_unit_cd%TYPE;
3184 v_tr_ou_start_dt IGS_EN_ST_SNAPSHOT.tr_ou_start_dt%TYPE;
3185 v_eftsu IGS_EN_ST_SNAPSHOT.eftsu%TYPE;
3186 v_enrolled_dt IGS_EN_ST_SNAPSHOT.enrolled_dt%TYPE;
3187 v_discontinued_dt IGS_EN_ST_SNAPSHOT.discontinued_dt%TYPE;
3188 v_old_unit_cd IGS_EN_ST_SNAPSHOT.unit_cd%TYPE;
3189 v_old_eftsu IGS_EN_ST_SNAPSHOT.eftsu%TYPE;
3190 v_new_unit_cd IGS_EN_ST_SNAPSHOT.unit_cd%TYPE;
3191 v_new_eftsu IGS_EN_ST_SNAPSHOT.eftsu%TYPE;
3192 v_last_updated_by IGS_EN_STDNTPSHECSOP.last_updated_by%TYPE;
3193 v_last_update_date IGS_EN_STDNTPSHECSOP.last_update_date%TYPE;
3194 v_old_units_rows NUMBER;
3195 v_new_units_rows NUMBER;
3196 v_record_found BOOLEAN DEFAULT FALSE;
3197 v_old_citizenship_cd IGS_ST_GVT_STDNT_LBL.citizenship_cd%TYPE;
3198 v_old_perm_resident_cd IGS_ST_GVT_STDNT_LBL.perm_resident_cd%TYPE;
3199 v_old_prior_degree IGS_ST_GOVT_STDNT_EN.prior_degree%TYPE;
3200 v_effective_dt DATE;
3201 v_char_date VARCHAR2(10);
3202 v_crv_version_number IGS_ST_GVT_STDNT_LBL.version_number%TYPE;
3203 v_govt_reportable_ind VARCHAR2(1);
3204 v_govt_discipline_group_cd
3205 IGS_EN_ST_SNAPSHOT.govt_discipline_group_cd%TYPE;
3206 v_industrial_ind IGS_PS_UNIT_VER.industrial_ind%TYPE;
3207 v_old_cntr NUMBER;
3208 v_new_cntr NUMBER;
3209 v_citizenship_cd IGS_PE_STATISTICS.citizenship_cd%TYPE;
3210 v_perm_resident_cd IGS_PE_STATISTICS.perm_resident_cd%TYPE;
3211 v_prior_degree IGS_PE_STATISTICS.prior_degree%TYPE;
3212 v_prior_post_grad IGS_ST_GOVT_STDNT_EN.prior_post_grad%TYPE;
3213 v_old_prior_post_grad IGS_ST_GOVT_STDNT_EN.prior_post_grad%TYPE;
3214 v_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
3215 -- declaration for rowid and the sequence number variable
3216 v_rowid VARCHAR2(25);
3217 v_seqnum IGS_ST_GVT_SPSHT_CHG.sequence_number%type;
3218 BEGIN
3219 -- find the most recent run of the enrolment statistics snapshot
3220 OPEN c_ess;
3221 FETCH c_ess INTO v_snapshot_dt_time;
3222 CLOSE c_ess;
3223 -- Determine the Effective Date.
3224 IF p_submission_number = 1 THEN
3225 v_char_date := TO_CHAR(p_submission_yr)||'03/31/';
3226 ELSE -- Submission 2
3227 v_char_date := TO_CHAR(p_submission_yr)||'08/31/';
3228 END IF;
3229 v_effective_dt := IGS_GE_DATE.igsdate(v_char_date);
3230 -- select all govt_student_liability_records for previous submission
3231 FOR v_gsli_gslc IN c_gsli_gslc LOOP
3232 v_person_id := v_gsli_gslc.person_id;
3233 v_course_cd := v_gsli_gslc.course_cd;
3234 v_crv_version_number := v_gsli_gslc.version_number;
3235 v_govt_semester := v_gsli_gslc.govt_semester;
3236 v_old_hecs_payment_option := v_gsli_gslc.hecs_payment_option;
3237 v_old_differential_hecs_ind := v_gsli_gslc.differential_hecs_ind;
3238 v_old_hecs_prexmt_exie := v_gsli_gslc.hecs_prexmt_exie;
3239 v_old_hecs_amount_paid := v_gsli_gslc.hecs_amount_paid;
3240 v_load_cal_type := v_gsli_gslc.cal_type;
3241 v_load_ci_sequence_number := v_gsli_gslc.ci_sequence_number;
3242 v_old_s_hecs_payment_type := v_gsli_gslc.s_hecs_payment_type;
3243 v_old_citizenship_cd := v_gsli_gslc.citizenship_cd;
3244 v_old_perm_resident_cd := v_gsli_gslc.perm_resident_cd;
3245 -- retrieve the new HECS payment option
3246 OPEN c_scho_hpo_ghpo (
3247 v_person_id,
3248 v_course_cd,
3249 v_effective_dt);
3250 FETCH c_scho_hpo_ghpo INTO
3251 v_differential_hecs_ind,
3252 v_hecs_payment_option,
3253 v_govt_hecs_payment_option,
3254 v_s_hecs_payment_type;
3255 IF c_scho_hpo_ghpo%NOTFOUND THEN
3256 v_differential_hecs_ind := 'Y';
3257 v_hecs_payment_option := '00';
3258 v_govt_hecs_payment_option := NULL;
3259 v_s_hecs_payment_type := NULL;
3260 END IF;
3261 CLOSE c_scho_hpo_ghpo;
3262 -- retrieve the HECS fee
3263 -- Only process students that are not HECS exempt
3264 IF v_s_hecs_payment_type <> cst_exempt OR
3265 v_old_s_hecs_payment_type <> cst_exempt THEN
3266 v_hecs_prexmt_exie := ROUND(IGS_FI_GEN_001.finp_get_hecs_fee(
3267 v_load_cal_type,
3268 v_load_ci_sequence_number,
3269 v_person_id,
3270 v_course_cd));
3271 -- Retrieve the HECS Amount Paid
3272 v_hecs_amount_paid := ROUND(IGS_FI_GEN_001.finp_get_hecs_amt_pd (
3273 v_load_cal_type,
3274 v_load_ci_sequence_number,
3275 v_person_id,
3276 v_course_cd));
3277 IF v_hecs_amount_paid <> v_old_hecs_amount_paid OR
3278 v_hecs_prexmt_exie <> v_old_hecs_prexmt_exie OR
3279 v_old_differential_hecs_ind <> v_differential_hecs_ind OR
3280 v_old_hecs_payment_option <> v_hecs_payment_option THEN
3281 -- Check if the change has already been recorded.
3282 OPEN c_gsc1 (
3283 v_person_id,
3284 v_course_cd,
3285 v_crv_version_number,
3286 v_govt_semester,
3287 v_old_hecs_prexmt_exie,
3288 v_old_hecs_amount_paid,
3289 v_old_hecs_payment_option,
3290 v_old_differential_hecs_ind,
3291 v_hecs_amount_paid,
3292 v_hecs_prexmt_exie,
3293 v_hecs_payment_option,
3294 v_differential_hecs_ind);
3295 FETCH c_gsc1 INTO v_dummy;
3296 IF c_gsc1%NOTFOUND THEN
3297 CLOSE c_gsc1;
3298 v_unit_cd := NULL;
3299 v_old_unit_cd := NULL;
3300 v_eftsu := NULL;
3301 v_old_eftsu := NULL;
3302 -- Find the extra details we need
3303 IF v_old_differential_hecs_ind <> v_differential_hecs_ind OR
3304 v_old_hecs_payment_option <> v_hecs_payment_option THEN
3305 --Attempt to find when the HECS was changed
3306 OPEN c_scho1 (
3307 v_person_id,
3308 v_course_cd,
3309 v_old_hecs_payment_option,
3310 v_old_differential_hecs_ind);
3311 FETCH c_scho1 INTO v_last_updated_by,
3312 v_last_update_date;
3313 IF (c_scho1%NOTFOUND) THEN
3314 CLOSE c_scho1;
3315 OPEN c_scho2 (
3316 v_person_id,
3317 v_course_cd,
3318 v_effective_dt);
3319 FETCH c_scho2 INTO v_last_updated_by,
3320 v_last_update_date;
3321 CLOSE c_scho2;
3322 ELSE
3323 CLOSE c_scho1;
3324 END IF; -- c_scho1%NOTFOUND
3325 END IF; -- v_old_differential_hecs_ind <> v_differential_hecs_ind...
3326 -- Find any IGS_PS_UNIT that has changed
3327 -- We need to create 2 PL/SQL tables to store the old units
3328 -- submitted and the new units so they can be compared
3329 t_old_units := t_blank;
3330 t_new_units := t_blank;
3331 v_new_units_rows := 0;
3332 v_old_units_rows := 0;
3333 FOR v_gslo IN c_gslo(
3334 v_govt_semester,
3335 v_person_id,
3336 v_course_cd) LOOP
3337 v_unit_cd := v_gslo.unit_cd;
3338 v_eftsu := v_gslo.eftsu;
3339 v_old_units_rows := v_old_units_rows + 1;
3340 t_old_units(v_old_units_rows).unit_cd := v_unit_cd;
3341 t_old_units(v_old_units_rows).sua_cal_type := v_gslo.sua_cal_type;
3342 t_old_units(v_old_units_rows).sua_ci_sequence_number :=
3343 v_gslo.sua_ci_sequence_number;
3344 t_old_units(v_old_units_rows).tr_org_unit_cd := v_gslo.tr_org_unit_cd;
3345 t_old_units(v_old_units_rows).tr_ou_start_dt := v_gslo.tr_ou_start_dt;
3346 t_old_units(v_old_units_rows).govt_discipline_group_cd :=
3347 v_gslo.govt_discipline_group_cd;
3348 t_old_units(v_old_units_rows).eftsu := v_eftsu;
3349 t_old_units(v_old_units_rows).industrial_ind := v_gslo.industrial_ind;
3350 t_old_units(v_old_units_rows).uoo_id := v_gslo.uoo_id;
3351 END LOOP;
3352 -- now find the new units
3353 FOR v_ess_uv_gslc IN c_ess_uv_gslc (
3354 v_snapshot_dt_time,
3355 v_govt_semester,
3356 v_person_id,
3357 v_course_cd) LOOP
3358 v_unit_cd := v_ess_uv_gslc.unit_cd;
3359 v_sua_cal_type := v_ess_uv_gslc.sua_cal_type;
3360 v_sua_ci_sequence_number := v_ess_uv_gslc.sua_ci_sequence_number;
3361 v_tr_org_unit_cd := v_ess_uv_gslc.tr_org_unit_cd;
3362 v_tr_ou_start_dt := v_ess_uv_gslc.tr_ou_start_dt;
3363 v_govt_discipline_group_cd := v_ess_uv_gslc.govt_discipline_group_cd;
3364 v_eftsu := v_ess_uv_gslc.eftsu;
3365 v_industrial_ind := v_ess_uv_gslc.industrial_ind;
3366 v_crv_version_number := v_ess_uv_gslc.crv_version_number;
3367 v_uv_version_number := v_ess_uv_gslc.uv_version_number;
3368 v_enrolled_dt := v_ess_uv_gslc.enrolled_dt;
3369 v_discontinued_dt := v_ess_uv_gslc.discontinued_dt;
3370 v_load_cal_type := v_ess_uv_gslc.ci_cal_type;
3371 v_load_ci_sequence_number := v_ess_uv_gslc.ci_sequence_number;
3372 v_uoo_id := v_ess_uv_gslc.uoo_id;
3373 OPEN c_daiv_sgcc(
3374 v_sua_cal_type,
3375 v_sua_ci_sequence_number);
3376 FETCH c_daiv_sgcc INTO v_alias_val;
3377 CLOSE c_daiv_sgcc;
3378 -- check if IGS_PS_UNIT is reportable
3379 v_govt_reportable_ind := IGS_ST_GEN_003.stap_get_rptbl_sbmsn (
3380 p_submission_yr,
3381 p_submission_number,
3382 v_person_id,
3383 v_course_cd,
3384 v_crv_version_number,
3385 v_unit_cd,
3386 v_uv_version_number,
3387 v_sua_cal_type,
3388 v_sua_ci_sequence_number,
3389 v_tr_org_unit_cd,
3390 v_tr_ou_start_dt,
3391 v_eftsu,
3392 v_enrolled_dt,
3393 v_discontinued_dt,
3394 v_govt_semester,
3395 v_alias_val,
3396 v_load_cal_type,
3397 v_load_ci_sequence_number,
3398 v_uoo_id);
3399 IF v_govt_reportable_ind <> 'N' THEN
3400 v_new_units_rows := v_new_units_rows + 1;
3401 t_new_units(v_new_units_rows).unit_cd := v_unit_cd;
3402 t_new_units(v_new_units_rows).sua_cal_type := v_sua_cal_type;
3403 t_new_units(v_new_units_rows).sua_ci_sequence_number :=
3404 v_sua_ci_sequence_number;
3405 t_new_units(v_new_units_rows).tr_org_unit_cd := v_tr_org_unit_cd;
3406 t_new_units(v_new_units_rows).tr_ou_start_dt := v_tr_ou_start_dt;
3407 t_new_units(v_new_units_rows).govt_discipline_group_cd :=
3408 v_govt_discipline_group_cd;
3409 t_new_units(v_new_units_rows).eftsu := v_eftsu;
3410 t_new_units(v_new_units_rows).industrial_ind := v_industrial_ind;
3411 t_new_units(v_new_units_rows).uoo_id := v_uoo_id;
3412 END IF;
3413 END LOOP; -- v_ess_uv_gslc
3414 -- now we need to compare the two tables t_old_units
3415 -- and t_new_units to see if there are differences
3416 -- loop through t_old units
3417 v_old_cntr := 1;
3418 WHILE v_old_cntr <= v_old_units_rows LOOP
3419 v_record_found := FALSE;
3420 -- loop through t_new units
3421 v_new_cntr := 1;
3422 WHILE v_new_cntr <= v_new_units_rows LOOP
3423 IF (t_old_units(v_old_cntr).unit_cd
3424 = t_new_units(v_new_cntr).unit_cd AND
3425 t_old_units(v_old_cntr).sua_cal_type
3426 = t_new_units(v_new_cntr).sua_cal_type AND
3427 t_old_units(v_old_cntr).sua_ci_sequence_number
3428 = t_new_units(v_new_cntr).sua_ci_sequence_number AND
3429 t_old_units(v_old_cntr).tr_org_unit_cd
3430 = t_new_units(v_new_cntr).tr_org_unit_cd AND
3431 t_old_units(v_old_cntr).tr_ou_start_dt
3432 = t_new_units(v_new_cntr).tr_ou_start_dt AND
3433 t_old_units(v_old_cntr).govt_discipline_group_cd
3434 = t_new_units(v_new_cntr).govt_discipline_group_cd AND
3435 t_old_units(v_old_cntr).eftsu
3436 = t_new_units(v_new_cntr).eftsu AND
3437 t_old_units(v_old_cntr).industrial_ind
3438 = t_new_units(v_new_cntr).industrial_ind AND
3439 t_old_units(v_old_cntr).uoo_id
3440 = t_new_units(v_new_cntr).uoo_id) THEN
3441 v_record_found := TRUE;
3442 exit;
3443 END IF;
3444 v_new_cntr := v_new_cntr + 1;
3445 END LOOP;
3446 IF v_record_found = FALSE THEN
3447 v_old_unit_cd := t_old_units(v_old_cntr).unit_cd;
3448 v_old_eftsu := t_old_units(v_old_cntr).eftsu;
3449 OPEN c_suah1(
3450 v_person_id,
3451 v_course_cd,
3452 t_old_units(v_old_cntr).uoo_id);
3453 FETCH c_suah1 INTO v_last_updated_by,
3454 v_last_update_date;
3455 IF c_suah1%NOTFOUND THEN
3456 CLOSE c_suah1;
3457 OPEN c_suah2(
3458 v_person_id,
3459 v_course_cd,
3460 t_old_units(v_old_cntr).uoo_id);
3461 FETCH c_suah2 INTO v_last_updated_by,
3462 v_last_update_date;
3463 CLOSE c_suah2;
3464 ELSE
3465 CLOSE c_suah1;
3466 END IF;
3467 exit;
3468 END IF; -- v_record_found = FALSE
3469 v_old_cntr := v_old_cntr +1;
3470 END LOOP; -- WHILE v_old_cntr <= old_units_rows
3471 -- we need to compare the two tables t_old_units
3472 -- and t_new_units to see if there are differences in
3473 -- reverse to last time
3474 -- loop through t_new units
3475 v_new_cntr := 1;
3476 WHILE v_new_cntr <= v_new_units_rows LOOP
3477 v_record_found := FALSE;
3478 -- loop through t_old _units
3479 v_old_cntr := 1;
3480 WHILE v_old_cntr <= v_old_units_rows LOOP
3481 IF (t_new_units(v_new_cntr).unit_cd
3482 = t_old_units(v_old_cntr).unit_cd AND
3483 t_new_units(v_new_cntr).sua_cal_type
3484 = t_old_units(v_old_cntr).sua_cal_type AND
3485 t_new_units(v_new_cntr).sua_ci_sequence_number
3486 = t_old_units(v_old_cntr).sua_ci_sequence_number AND
3487 t_new_units(v_new_cntr).tr_org_unit_cd
3488 = t_old_units(v_old_cntr).tr_org_unit_cd AND
3489 t_new_units(v_new_cntr).tr_ou_start_dt
3490 = t_old_units(v_old_cntr).tr_ou_start_dt AND
3491 t_new_units(v_new_cntr).govt_discipline_group_cd
3492 = t_old_units(v_old_cntr).govt_discipline_group_cd AND
3493 t_new_units(v_new_cntr).eftsu
3494 = t_old_units(v_old_cntr).eftsu AND
3495 t_new_units(v_new_cntr).industrial_ind
3496 = t_old_units(v_old_cntr).industrial_ind AND
3497 t_new_units(v_new_cntr).uoo_id
3498 = t_old_units(v_old_cntr).uoo_id) THEN
3499 v_record_found := TRUE;
3500 exit; -- WHILE v_old_cntr <= old_units_rows LOOP
3501 END IF;
3502 v_old_cntr := v_old_cntr + 1;
3503 END LOOP;
3504 IF v_record_found = FALSE THEN
3505 v_new_unit_cd := t_new_units(v_new_cntr).unit_cd;
3506 v_new_eftsu := t_new_units(v_new_cntr).eftsu;
3507 OPEN c_suah3 (
3508 v_person_id,
3509 v_course_cd,
3510 t_new_units(v_new_cntr).uoo_id);
3511 FETCH c_suah3 INTO v_last_updated_by,
3512 v_last_update_date;
3513 IF (c_suah3%NOTFOUND) THEN
3514 CLOSE c_suah3;
3515 OPEN c_sua (
3516 v_person_id,
3517 v_course_cd,
3518 t_new_units(v_new_cntr).uoo_id);
3519 FETCH c_sua INTO v_last_updated_by,
3520 v_last_update_date;
3521 CLOSE c_sua;
3522 ELSE
3523 CLOSE c_suah3;
3524 END IF;
3525 exit; -- WHILE v_new_cntr <= new_units_rows LOOP
3526 END IF; -- v_record_found = FALSE
3527 v_new_cntr := v_new_cntr + 1;
3528 END LOOP; -- WHILE v_new_cntr <= new_units_rows
3529 -- Insert changed details in table
3530
3531 -- to insert row using the insertrow of respective TBH package
3532
3533 IGS_ST_GVT_SPSHT_CHG_PKG.INSERT_ROW(
3534 X_ROWID => v_rowid,
3535 X_SUBMISSION_YR => p_submission_yr,
3536 X_SUBMISSION_NUMBER => p_submission_number,
3537 X_PERSON_ID => v_person_id,
3538 X_COURSE_CD => v_course_cd,
3539 X_VERSION_NUMBER => v_crv_version_number,
3540 X_SEQUENCE_NUMBER => v_seqnum,
3541 X_CHANGED_UPDATE_WHO => v_last_updated_by,
3542 X_CHANGED_UPDATE_ON => v_last_update_date,
3543 X_GOVT_SEMESTER => v_govt_semester,
3544 X_UNIT_CD => v_new_unit_cd,
3545 X_EFTSU => v_new_eftsu,
3546 X_HECS_PREXMT_EXIE => v_hecs_prexmt_exie,
3547 X_HECS_AMOUNT_PAID => v_hecs_amount_paid,
3548 X_HECS_PAYMENT_OPTION => v_hecs_payment_option,
3549 X_DIFFERENTIAL_HECS_IND => v_differential_hecs_ind,
3550 X_CITIZENSHIP_CD => NULL,
3551 X_PERM_RESIDENT_CD => NULL,
3552 X_PRIOR_DEGREE => NULL,
3553 X_PRIOR_POST_GRAD => NULL,
3554 X_OLD_UNIT_CD => NULL,
3555 X_OLD_EFTSU => NULL,
3556 X_OLD_HECS_PREXMT_EXIE => v_old_hecs_prexmt_exie,
3557 X_OLD_HECS_AMOUNT_PAID => v_old_hecs_amount_paid,
3558 X_OLD_HECS_PAYMENT_OPTION => v_old_hecs_payment_option,
3559 X_OLD_DIFFERENTIAL_HECS_IND => v_old_differential_hecs_ind,
3560 X_OLD_CITIZENSHIP_CD => NULL,
3561 X_OLD_PERM_RESIDENT_CD => NULL,
3562 X_OLD_PRIOR_DEGREE => NULL,
3563 X_OLD_PRIOR_POST_GRAD => NULL,
3564 X_REPORTED_IND => 'N',
3565 X_MODE => 'R');
3566
3567 -- reset the changed update who and on fields
3568 v_last_updated_by := NULL;
3569 v_last_update_date := NULL;
3570 v_new_unit_cd := NULL;
3571 v_new_eftsu := NULL;
3572 v_old_unit_cd := NULL;
3573 v_old_eftsu := NULL;
3574 ELSE
3575 CLOSE c_gsc1;
3576 END IF; --c_gsc1%NOTFOUND
3577 END IF; -- v_hecs_amount_paid <> v_old_hecs_amount_paid...
3578 /**********************************************************************/
3579 /* IGS_GE_NOTE: It has been decided that the check for prior degree and
3580 prior post grad is no longer useful. The relevant code has been commented
3581 out NOCOPY so that if they change their mind again it can be put back easily */
3582 -- Find changes to IGS_PE_PERSON statistics
3583 /*
3584 OPEN c_gse( v_person_id,
3585 v_course_cd);
3586 FETCH c_gse INTO v_person_id,
3587 v_course_cd,
3588 v_crv_version_number,
3589 v_old_citizenship_cd,
3590 v_old_perm_resident_cd,
3591 v_old_prior_degree,
3592 v_old_prior_post_grad;
3593 CLOSE c_gse;*/
3594 /**********************************************************************/
3595 v_old_prior_degree := NULL;
3596 v_old_prior_post_grad := NULL;
3597 OPEN c_ps (
3598 v_person_id,
3599 v_effective_dt);
3600 FETCH c_ps INTO v_citizenship_cd,
3601 v_perm_resident_cd,
3602 v_prior_degree,
3603 v_prior_post_grad;
3604 IF c_ps%NOTFOUND THEN
3605 v_citizenship_cd := '9';
3606 v_perm_resident_cd := '9';
3607 END IF;
3608 IF v_citizenship_cd NOT IN (2, 3, 9) THEN
3609 v_perm_resident_cd := '0';
3610 END IF;
3611 CLOSE c_ps;
3612 /**********************************************************************/
3613 /* IGS_GE_NOTE: It has been decided that the check for prior degree and
3614 prior post grad is no longer useful. The relevant code has been commented
3615 out NOCOPY so that if they change their mind again it can be put back easily */
3616 /* IF v_old_citizenship_cd <> v_citizenship_cd OR
3617 v_old_perm_resident_cd <> v_perm_resident_cd OR
3618 v_old_prior_degree <> v_prior_degree OR
3619 v_old_prior_post_grad <> v_prior_post_grad THEN */
3620 /***********************************************************************/
3621 IF v_old_citizenship_cd <> v_citizenship_cd OR
3622 v_old_perm_resident_cd <> v_perm_resident_cd THEN
3623 --Check if the change has already been recorded.
3624 OPEN c_gsc2 (
3625 v_person_id,
3626 v_course_cd,
3627 v_crv_version_number,
3628 v_old_citizenship_cd,
3629 v_old_perm_resident_cd,
3630 v_old_prior_degree,
3631 v_old_prior_post_grad,
3632 v_perm_resident_cd,
3633 v_citizenship_cd,
3634 v_prior_degree,
3635 v_prior_post_grad);
3636 FETCH c_gsc2 INTO v_dummy;
3637 IF c_gsc2%NOTFOUND THEN
3638 CLOSE c_gsc2;
3639 -- Find the extra details we need
3640 -- Attempt to find when it was changed
3641 OPEN c_ps2(
3642 v_person_id,
3643 v_old_perm_resident_cd,
3644 v_old_citizenship_cd,
3645 v_old_prior_degree);
3646 FETCH c_ps2 INTO v_last_updated_by,
3647 v_last_update_date;
3648 IF (c_ps2%NOTFOUND) THEN
3649 CLOSE c_ps2;
3650 OPEN c_ps3 (
3651 v_person_id,
3652 v_effective_dt);
3653 FETCH c_ps3 INTO v_last_updated_by,
3654 v_last_update_date;
3655 CLOSE c_ps3;
3656 ELSE
3657 CLOSE c_ps2;
3658 END IF;
3659 -- Insert changed details in table
3660
3661 -- to insert row using the insertrow of respective TBH package
3662
3663 IGS_ST_GVT_SPSHT_CHG_PKG.INSERT_ROW(
3664 X_ROWID => v_rowid,
3665 X_SUBMISSION_YR => p_submission_yr,
3666 X_SUBMISSION_NUMBER => p_submission_number,
3667 X_PERSON_ID => v_person_id,
3668 X_COURSE_CD => v_course_cd,
3669 X_SEQUENCE_NUMBER => v_seqnum,
3670 X_VERSION_NUMBER => v_crv_version_number,
3671 X_CHANGED_UPDATE_WHO => v_last_updated_by,
3672 X_CHANGED_UPDATE_ON => v_last_update_date,
3673 X_GOVT_SEMESTER => NULL,
3674 X_UNIT_CD => NULL,
3675 X_EFTSU => NULL,
3676 X_HECS_PREXMT_EXIE => NULL,
3677 X_HECS_AMOUNT_PAID => NULL,
3678 X_HECS_PAYMENT_OPTION => NULL,
3679 X_DIFFERENTIAL_HECS_IND => NULL,
3680 X_CITIZENSHIP_CD => v_citizenship_cd,
3681 X_PERM_RESIDENT_CD => v_perm_resident_cd,
3682 X_PRIOR_DEGREE => NULL,
3683 X_PRIOR_POST_GRAD => NULL,
3684 X_OLD_UNIT_CD => NULL,
3685 X_OLD_EFTSU => NULL,
3686 X_OLD_HECS_PREXMT_EXIE => NULL,
3687 X_OLD_HECS_AMOUNT_PAID => NULL,
3688 X_OLD_HECS_PAYMENT_OPTION => NULL,
3689 X_OLD_DIFFERENTIAL_HECS_IND => NULL,
3690 X_OLD_CITIZENSHIP_CD => v_old_citizenship_cd,
3691 X_OLD_PERM_RESIDENT_CD => v_old_perm_resident_cd,
3692 X_OLD_PRIOR_DEGREE => NULL,
3693 X_OLD_PRIOR_POST_GRAD => NULL,
3694 X_REPORTED_IND => 'N',
3695 X_MODE => 'R');
3696
3697 -- reset the changed update who and on fields
3698 v_last_updated_by := NULL;
3699 v_last_update_date := NULL;
3700 ELSE
3701 CLOSE c_gsc2;
3702 END IF; -- c_gsc2%NOTFOUND
3703 END IF; -- v_old_citizenship_cd <> v_citizenship_cd...
3704 END IF; -- v_s_hecs_payment_type <> cst_exempt OR ...
3705 END LOOP; -- v_gsli_gslc IN c_gsli_gslc LOOP
3706 COMMIT;
3707 EXCEPTION
3708 WHEN OTHERS THEN
3709 IF (c_ess%ISOPEN) THEN
3710 CLOSE c_ess;
3711 END IF;
3712 IF (c_gsli_gslc%ISOPEN) THEN
3713 CLOSE c_gsli_gslc;
3714 END IF;
3715 IF (c_scho_hpo_ghpo%ISOPEN) THEN
3716 CLOSE c_scho_hpo_ghpo;
3717 END IF;
3718 IF (c_daiv_sgcc%ISOPEN) THEN
3719 CLOSE c_daiv_sgcc;
3720 END IF;
3721 IF (c_gsc1%ISOPEN) THEN
3722 CLOSE c_gsc1;
3723 END IF;
3724 IF (c_scho1%ISOPEN) THEN
3725 CLOSE c_scho1;
3726 END IF;
3727 IF (c_scho2%ISOPEN) THEN
3728 CLOSE c_scho2;
3729 END IF;
3730 IF (c_gslo%ISOPEN) THEN
3731 CLOSE c_gslo;
3732 END IF;
3733 IF (c_ess_uv_gslc%ISOPEN) THEN
3734 CLOSE c_ess_uv_gslc;
3735 END IF;
3736 IF (c_suah1%ISOPEN) THEN
3737 CLOSE c_suah1;
3738 END IF;
3739 IF (c_suah2%ISOPEN) THEN
3740 CLOSE c_suah2;
3741 END IF;
3742 IF (c_suah3%ISOPEN) THEN
3743 CLOSE c_suah3;
3744 END IF;
3745 IF (c_sua%ISOPEN) THEN
3746 CLOSE c_sua;
3747 END IF;
3748 IF (c_gse%ISOPEN) THEN
3749 CLOSE c_gse;
3750 END IF;
3751 IF (c_ps%ISOPEN) THEN
3752 CLOSE c_ps;
3753 END IF;
3754 IF (c_gsc2%ISOPEN) THEN
3755 CLOSE c_gsc2;
3756 END IF;
3757 IF (c_ps2%ISOPEN) THEN
3758 CLOSE c_ps2;
3759 END IF;
3760 IF (c_ps3%ISOPEN) THEN
3761 CLOSE c_ps3;
3762 END IF;
3763 RAISE;
3764 END;
3765 EXCEPTION
3766 WHEN OTHERS THEN
3767 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3768 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stap_ins_gsch');
3769 IGS_GE_MSG_STACK.ADD;
3770 App_Exception.Raise_Exception;
3771 END stap_ins_gsch;
3772
3773
3774 Procedure Stas_Ins_Govt_Snpsht(
3775 p_submission_yr IN NUMBER ,
3776 p_submission_number IN NUMBER ,
3777 p_ess_snapshot_dt_time IN DATE ,
3778 p_use_most_recent_ess_ind IN VARCHAR2 DEFAULT 'N',
3779 p_log_creation_dt OUT NOCOPY DATE )
3780 AS
3781 gv_other_detail VARCHAR2(255);
3782 gv_message_name VARCHAR2(30);
3783 BEGIN -- stas_ins_govt_snpsht
3784 -- This routine is a stored database procedure that calls the stored database
3785 -- function STAP_INS_GOVT_SNPSHT. This routine is needed because Job Schedular
3786 -- can only call stored database procedures, not functions. If the called
3787 -- function returns false, this routine will log an entry in the system log
3788 -- and an error in the system error log.
3789 DECLARE
3790 cst_s_log_type CONSTANT IGS_GE_S_LOG.s_log_type%TYPE := 'GOVT-SBMSN';
3791 v_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
3792 v_log_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
3793 BEGIN
3794 v_creation_dt := SYSDATE;
3795 IF (stap_ins_govt_snpsht(
3796 p_submission_yr,
3797 p_submission_number,
3798 p_ess_snapshot_dt_time,
3799 p_use_most_recent_ess_ind,
3800 gv_message_name,
3801 v_log_creation_dt) = FALSE) THEN
3802 ROLLBACK;
3803 p_log_creation_dt := v_log_creation_dt;
3804 -- Output the exception to the job run log.
3805 Fnd_Message.Set_Name('IGS',gv_message_name);
3806 IGS_GE_MSG_STACK.ADD;
3807 App_Exception.Raise_Exception;
3808 END IF;
3809 p_log_creation_dt := v_log_creation_dt;
3810 END;
3811 EXCEPTION
3812 WHEN OTHERS THEN
3813 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3814 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_004.stas_ins_govt_snpsht');
3815 IGS_GE_MSG_STACK.ADD;
3816 App_Exception.Raise_Exception;
3817 END stas_ins_govt_snpsht;
3818
3819 END IGS_ST_GEN_004;