1 PACKAGE BODY IGS_ST_GEN_003 AS
2 /* $Header: IGSST03B.pls 115.7 2003/05/13 09:02:15 kkillams ship $ */
3
4 /*
5 || Created By : [email protected]
6 || Created On : 28-AUG-2000
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || pkpatel 05-MAR-2002 Bug NO: 2224621
12 || Modified P_GOVT_EXEMPTION_INST_CD from NUMBER to VARCHAR2 in STAP_GET_SCA_DATA. Since its source
13 || IGS_OR_INSTITUTION.GOVT_INSTITUTION_CD is modified from NUMBER to VARCHAR2.
14 || (reverse chronological order - newest change first)
15 */
16
17 Function Stap_Get_Prsn_Dsblty(
18 p_person_id IN NUMBER )
19 RETURN VARCHAR2 AS
20 gv_other_detail VARCHAR2(255);
21 BEGIN -- stap_get_prsn_dsblty
22 -- Derive the government disability value for a person. Disability
23 -- is an 8 character code derived from the IGS_PE_PERSON table
24 -- DEETYA element 386
25 DECLARE
26 v_disability CHAR(8) DEFAULT ' ';
27 v_disability_exist CHAR DEFAULT ' ';
28 v_hearing CHAR DEFAULT ' ';
29 v_learning CHAR DEFAULT ' ';
30 v_mobility CHAR DEFAULT ' ';
31 v_vision CHAR DEFAULT ' ';
32 v_medical CHAR DEFAULT ' ';
33 v_other CHAR DEFAULT ' ';
34 v_contact CHAR DEFAULT ' ';
35 CURSOR c_prsn_dsblty (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
36 SELECT pd.contact_ind,
37 dt.govt_disability_type
38 FROM
39 IGS_PE_PERS_DISABLTY pd,
40 IGS_AD_DISBL_TYPE dt
41 WHERE
42 pd.person_id = cp_person_id AND
43 dt.disability_type = pd.disability_type;
44 BEGIN
45 FOR v_prsn_dsblty IN c_prsn_dsblty (p_person_id) LOOP
46 v_disability_exist := '1';
47 IF (v_prsn_dsblty.govt_disability_type = 'HEARING') THEN
48 v_hearing := '1';
49 ELSIF (v_prsn_dsblty.govt_disability_type = 'LEARNING') THEN
50 v_learning := '1';
51 ELSIF (v_prsn_dsblty.govt_disability_type = 'MOBILITY') THEN
52 v_mobility := '1';
53 ELSIF (v_prsn_dsblty.govt_disability_type = 'VISION') THEN
54 v_vision := '1';
55 ELSIF (v_prsn_dsblty.govt_disability_type = 'MEDICAL') THEN
56 v_medical := '1';
57 ELSIF (v_prsn_dsblty.govt_disability_type = 'OTHER') THEN
58 v_other := '1';
59 END IF;
60 IF (v_prsn_dsblty.contact_ind = 'Y') THEN
61 v_contact := '1';
62 END IF;
63 END LOOP;
64 IF (v_disability_exist = '1' AND
65 v_contact = ' ') THEN
66 v_contact := '2';
67 END IF;
68 v_disability := v_disability_exist || v_hearing || v_learning || v_mobility ||
69 v_vision || v_medical || v_other || v_contact;
70 RETURN v_disability;
71 END;
72 EXCEPTION
73 WHEN OTHERS THEN
74 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
75 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_prsn_dsblty');
76 IGS_GE_MSG_STACK.ADD;
77 App_Exception.Raise_Exception;
78 END stap_get_prsn_dsblty;
79
80 Procedure Stap_Get_Prsn_Names(
81 p_person_id IN NUMBER ,
82 p_given_name OUT NOCOPY VARCHAR2 ,
83 p_other_names OUT NOCOPY VARCHAR2 )
84 AS
85 gv_other_detail VARCHAR2(255);
86 BEGIN -- stap_get_prsn_names
87 -- Derive the student's first given name and other given name(s)
88 -- DEETYA element 403, 404
89 DECLARE
90 v_delim_space NUMBER(2);
91 v_all_given_name IGS_PE_PERSON.given_names%TYPE;
92 CURSOR c_get_given_name (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
93 SELECT given_names
94 FROM IGS_PE_PERSON
95 WHERE person_id = cp_person_id;
96 BEGIN
97 OPEN c_get_given_name(p_person_id);
98 FETCH c_get_given_name INTO v_all_given_name;
99 IF (c_get_given_name%FOUND) THEN
100 v_delim_space := INSTR(v_all_given_name, ' ');
101 IF (v_delim_space = 0) THEN
102 -- No space, only one given name
103 p_given_name := v_all_given_name;
104 ELSE
105 -- Found a space
106 p_given_name := SUBSTR(v_all_given_name, 1, v_delim_space -1);
107 p_other_names := LTRIM(SUBSTR(v_all_given_name, v_delim_space + 1));
108 END IF;
109 END IF;
110 CLOSE c_get_given_name;
111 END;
112 EXCEPTION
113 WHEN OTHERS THEN
114 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
115 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_prsn_names');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 END stap_get_prsn_names;
119
120 Function Stap_Get_Rptbl_Benc(
121 p_person_id IN NUMBER ,
122 p_course_cd IN VARCHAR2 ,
123 p_crv_version_number IN NUMBER ,
124 p_govt_reportable_ind IN VARCHAR2 DEFAULT 'N',
125 p_enrolled_dt IN DATE ,
126 p_submission_cutoff_dt IN DATE )
127 RETURN VARCHAR2 AS
128 gv_other_detail VARCHAR2(255);
129 BEGIN -- stap_get_rptbl_benc
130 -- Routine to determine if a unit of study should be reported to
131 -- the Benefits Control branch.
132 DECLARE
133 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
134 CURSOR c_scho_hpo IS
135 SELECT hpo.govt_hecs_payment_option
136 FROM
137 IGS_EN_STDNTPSHECSOP scho,
138 IGS_FI_HECS_PAY_OPTN hpo
139 WHERE
140 scho.person_id = p_person_id AND
141 scho.course_cd = p_course_cd AND
142 scho.start_dt <= p_submission_cutoff_dt AND
143 (scho.end_dt IS NULL OR
144 scho.end_dt >= p_submission_cutoff_dt)AND
145 hpo.hecs_payment_option = scho.hecs_payment_option
146 ORDER BY
147 scho.end_dt ASC;
148 BEGIN
149 -- Processing will stop if any of the parameters is null.
150 IF (
151 p_person_id IS NULL OR
152 p_course_cd IS NULL OR
153 p_crv_version_number IS NULL OR
154 p_govt_reportable_ind IS NULL OR
155 p_enrolled_dt IS NULL OR
156 p_submission_cutoff_dt IS NULL) THEN
157 RETURN 'N';
158 END IF;
159 -- Determine if the unit should be reported in the specified submission.
160 -- Do not report non-government reportable records.
161 IF (p_govt_reportable_ind = 'N') THEN
162 RETURN 'N';
163 END IF;
164 -- Exclude units that are not enrolled as at the cutoff date.
165 IF (p_enrolled_dt > p_submission_cutoff_dt) THEN
166 RETURN 'N';
167 END IF;
168 -- Exclude Overseas Student
169 -- Select the first record only.
170 -- This will be the end dated record if one exists.
171 OPEN c_scho_hpo;
172 FETCH c_scho_hpo INTO v_govt_hecs_payment_option;
173 CLOSE c_scho_hpo;
174 IF (v_govt_hecs_payment_option IN ('22','23','24','30')) THEN
175 RETURN 'N';
176 END IF;
177 -- Otherwise, unit of study is reportable
178 RETURN 'Y';
179 EXCEPTION
180 WHEN OTHERS THEN
181 IF c_scho_hpo%ISOPEN THEN
182 CLOSE c_scho_hpo;
183 END IF;
184 RAISE;
185 END;
186 EXCEPTION
187 WHEN OTHERS THEN
188 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
189 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_rptbl_benc');
190 IGS_GE_MSG_STACK.ADD;
191 App_Exception.Raise_Exception;
192 END stap_get_rptbl_benc;
193
194 FUNCTION Stap_Get_Rptbl_Govt(
195 p_person_id IN NUMBER ,
196 p_course_cd IN VARCHAR2 ,
197 p_crv_version_number IN NUMBER ,
198 p_unit_cd IN VARCHAR2 ,
199 p_uv_version_number IN NUMBER ,
200 p_teach_cal_type IN VARCHAR2 ,
201 p_teach_ci_sequence_number IN NUMBER ,
202 p_tr_org_unit_cd IN VARCHAR2 ,
203 p_tr_ou_start_dt IN DATE ,
204 p_eftsu IN NUMBER ,
205 p_effective_dt IN DATE ,
206 p_exclusion_level OUT NOCOPY VARCHAR2,
207 p_uoo_id IN IGS_EN_SU_ATTEMPT.UOO_ID%TYPE)
208 RETURN VARCHAR2 AS
209 -------------------------------------------------------------------------------------------
210 -- stap_get_rptbl_govt
211 -- Routine to determine if a student unit attempt, or part of a student
212 -- unit attempt should be reported to the government. This routine is
213 -- called from the process that creates the Enrolment Statistics Snapshot,
214 -- and is therefore, not government submission specific. Another routine
215 -- exists to determine if a student unit attempt, or part of a student
216 -- unit attempt should be reported to the government in a specific
217 -- submission. (STAP_GET_RPTBL_SBMSN)
218 -- There are a number of data assumptions inherent in this routine.
219 -- These data assumptions are documented in Reportable Data Assumptions.doc
220 --Change History:
221 --Who When What
222 --kkillams 28-04-2003 Added new parameter p_uoo_id to the function.
223 -- w.r.t. bug number 2829262
224 -------------------------------------------------------------------------------------------
225 gv_other_detail VARCHAR2(255);
226 BEGIN
227 DECLARE
228 cst_course CONSTANT VARCHAR2(15) := 'COURSE';
229 cst_pe_course CONSTANT VARCHAR2(15) := 'PERSON-COURSE';
230 cst_unit CONSTANT VARCHAR2(15) := 'UNIT';
231 cst_yes CONSTANT VARCHAR2(1) := 'Y';
232 cst_no CONSTANT VARCHAR2(1) := 'N';
233 cst_warning CONSTANT VARCHAR2(1) := 'W';
234 cst_govt_report CONSTANT IGS_PS_UNIT_CATEGORY.unit_cat%TYPE := 'GOVT-RPT';
235 cst_govt_noreport CONSTANT IGS_PS_UNIT_CATEGORY.unit_cat%TYPE := 'GOVT-NORPT';
236 cst_tuition CONSTANT IGS_FI_FEE_TYPE.s_fee_type%TYPE := 'TUITION';
237 cst_enrolled CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE :=
238 'ENROLLED';
239 cst_discontin CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE :=
240 'DISCONTIN';
241 v_unit_cat IGS_PS_UNIT_CATEGORY.unit_cat%TYPE;
242 v_crs_cat IGS_PS_CATEGORISE.course_cat%TYPE;
243 v_resp_ou_cd IGS_PS_VER.responsible_org_unit_cd%TYPE;
244 v_resp_ou_start_dt IGS_PS_VER.responsible_ou_start_dt%TYPE;
245 v_govt_crs_type IGS_PS_TYPE.govt_course_type%TYPE;
246 v_award_crs_ind IGS_PS_TYPE.award_course_ind%TYPE;
247 v_enr_cp IGS_PS_UNIT_VER.enrolled_credit_points%TYPE;
248 v_message_name_temp VARCHAR2(30) DEFAULT NULL;
249 v_fee_cat IGS_EN_STDNT_PS_ATT.fee_cat%TYPE DEFAULT NULL;
250 v_crs_req_cmplt_ind IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind%TYPE;
251 v_comm_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
252 v_govt_hpo IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
253 v_sua_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
254 v_charged_tuition_fees BOOLEAN DEFAULT FALSE;
255 CURSOR c_uc IS
256 SELECT uc.unit_cat
257 FROM IGS_PS_UNIT_CATEGORY uc
258 WHERE uc.unit_cd = p_unit_cd AND
259 uc.version_number = p_uv_version_number AND
260 uc.unit_cat IN (
261 cst_govt_report,
262 cst_govt_noreport );
263 CURSOR c_crc IS
264 SELECT crc.course_cat
265 FROM IGS_PS_CATEGORISE crc
266 WHERE crc.course_cd = p_course_cd AND
267 crc.version_number = p_crv_version_number AND
268 crc.course_cat IN (
269 cst_govt_report,
270 cst_govt_noreport );
271 CURSOR c_crv IS
272 SELECT crv.responsible_org_unit_cd,
273 crv.responsible_ou_start_dt,
274 cty.govt_course_type,
275 cty.award_course_ind
276 FROM IGS_PS_VER crv,
277 IGS_PS_TYPE cty
278 WHERE crv.course_cd = p_course_cd AND
279 crv.version_number = p_crv_version_number AND
280 cty.course_type = crv.course_type;
281 CURSOR c_uv IS
282 SELECT uv.enrolled_credit_points
283 FROM IGS_PS_UNIT_VER uv
284 WHERE uv.unit_cd = p_unit_cd AND
285 uv.version_number = p_uv_version_number;
286 CURSOR c_sca IS
287 SELECT sca.fee_cat,
288 sca.course_rqrmnt_complete_ind,
289 sca.commencement_dt,
290 hpo.govt_hecs_payment_option
291 FROM IGS_EN_STDNT_PS_ATT sca,
292 IGS_EN_STDNTPSHECSOP scho,
293 IGS_FI_HECS_PAY_OPTN hpo
294 WHERE sca.person_id = p_person_id AND
295 sca.course_cd = p_course_cd AND
296 sca.person_id = scho.person_id (+) AND
297 sca.course_cd = scho.course_cd (+) AND
298 (scho.start_dt <= p_effective_dt OR
299 scho.start_dt IS NULL) AND
300 ( scho.end_dt IS NULL OR
301 scho.end_dt >= p_effective_dt) AND
302 scho.hecs_payment_option = hpo.hecs_payment_option(+)
303 ORDER BY scho.end_dt;
304 CURSOR c_ft IS
305 SELECT ft.fee_type
306 FROM IGS_FI_FEE_TYPE ft
307 WHERE ft.s_fee_type = cst_tuition;
308 CURSOR c_sua IS
309 SELECT sua.unit_attempt_status
310 FROM IGS_EN_SU_ATTEMPT sua
311 WHERE sua.person_id = p_person_id AND
312 sua.course_cd = p_course_cd AND
313 sua.uoo_id = p_uoo_id;
314 BEGIN
315 --- Set the default exclusion level.
316 p_exclusion_level := NULL;
317 --- Retrieve unit categorisation data.
318 OPEN c_uc;
319 FETCH c_uc INTO v_unit_cat;
320 IF c_uc%FOUND THEN
321 CLOSE c_uc;
322 p_exclusion_level := cst_unit;
323 IF v_unit_cat = cst_govt_report THEN
324 RETURN 'Y';
325 ELSE
326 RETURN 'N';
327 END IF;
328 END IF;
329 CLOSE c_uc;
330 --- Retrieve course categorisation data.
331 OPEN c_crc;
332 FETCH c_crc INTO v_crs_cat;
333 IF c_crc%FOUND THEN
334 CLOSE c_crc;
335 p_exclusion_level := cst_course;
336 IF v_crs_cat = cst_govt_report THEN
337 RETURN 'Y';
338 ELSE
339 RETURN 'N';
340 END IF;
341 END IF;
342 CLOSE c_crc;
343 --- Check for units with no load.
344 IF p_eftsu = 0 THEN
345 RETURN 'N';
346 END IF;
347 --- Retrieve course version data.
348 OPEN c_crv;
349 FETCH c_crv INTO v_resp_ou_cd,
350 v_resp_ou_start_dt,
351 v_govt_crs_type,
352 v_award_crs_ind;
353 IF c_crv%FOUND THEN
354 CLOSE c_crv;
355 --- Check for external course.
356 --- Check for Open Learning Studies course.
357 --- Check for non-award course owned by an external organisational unit.
358 --- Check for cross-institution course owned by an external organisational
359 --- unit.
360 IF v_govt_crs_type = 60 OR
361 (v_govt_crs_type IN (
362 40,
363 41,
364 42,
365 50) AND
366 NOT IGS_ST_VAL_SNAPSHOT.stap_val_local_ou(
367 v_resp_ou_cd,
368 v_resp_ou_start_dt,
369 v_message_name_temp)) THEN
370 p_exclusion_level := cst_course;
371 RETURN 'N';
372 END IF;
373 ELSE
374 CLOSE c_crv;
375 END IF;
376 --- Retrieve unit version data.
377 OPEN c_uv;
378 FETCH c_uv INTO v_enr_cp;
379 CLOSE c_uv;
380 --- Check for units with no credit.
381 IF v_enr_cp = 0 THEN
382 p_exclusion_level := cst_unit;
383 RETURN 'N';
384 END IF;
385 --- Check for units with a teaching responsibility of an external
386 --- organisational unit
387 --- if teaching responsibility parameters are set.
388 IF p_tr_org_unit_cd IS NOT NULL AND
389 p_tr_ou_start_dt IS NOT NULL AND
390 NOT IGS_ST_VAL_SNAPSHOT.stap_val_local_ou (
391 p_tr_org_unit_cd,
392 p_tr_ou_start_dt,
393 v_message_name_temp) THEN
394 RETURN 'N';
395 END IF;
396 --- Retrieve student course attempt and HECS data.
397 OPEN c_sca;
398 FETCH c_sca INTO v_fee_cat,
399 v_crs_req_cmplt_ind,
400 v_comm_dt,
401 v_govt_hpo;
402 IF c_sca%FOUND THEN
403 CLOSE c_sca;
404 --- Check for overseas student who has come to Australia either an as
405 --- Exchange Student or Study Abroad student as part of a formal exchange
406 --- program arranged between institutions, but who is not being charged
407 --- tuition fees by the institution.
408 IF v_govt_hpo = '22' AND
409 v_fee_cat IS NOT NULL THEN
410 --- Determine whether or not the student is being charged tuition fees.
411 FOR v_ft_rec IN c_ft LOOP
412 IF IGS_FI_GEN_005.finp_val_fee_lblty (
413 p_person_id,
414 p_course_cd,
415 v_fee_cat,
416 v_ft_rec.fee_type,
417 p_effective_dt,
418 v_message_name_temp) THEN
419 v_charged_tuition_fees := TRUE;
420 exit;
421 END IF;
422 END LOOP;
423 IF NOT v_charged_tuition_fees THEN
424 p_exclusion_level := cst_pe_course;
425 RETURN 'N';
426 END IF;
427 END IF;
428 OPEN c_sua;
429 FETCH c_sua INTO v_sua_status;
430 CLOSE c_sua;
431 --- Check if the student is enrolled in a unit in a completed award course.
432 IF v_award_crs_ind = 'Y' AND
433 v_crs_req_cmplt_ind = 'Y' AND
434 (v_sua_status = cst_enrolled OR
435 v_sua_status = cst_discontin) THEN
436 RETURN cst_warning;
437 END IF;
438 ELSE
439 CLOSE c_sca;
440 END IF;
441 --- Reportable.
442 RETURN cst_yes;
443 EXCEPTION
444 WHEN OTHERS THEN
445 IF c_uc%ISOPEN THEN
446 CLOSE c_uc;
447 END IF;
448 IF c_crc%ISOPEN THEN
449 CLOSE c_crc;
450 END IF;
451 IF c_crv%ISOPEN THEN
452 CLOSE c_crv;
453 END IF;
454 IF c_uv%ISOPEN THEN
455 CLOSE c_uv;
456 END IF;
457 IF c_sca%ISOPEN THEN
458 CLOSE c_sca;
459 END IF;
460 IF c_ft%ISOPEN THEN
461 CLOSE c_ft;
462 END IF;
463 IF c_sua%ISOPEN THEN
464 CLOSE c_sua;
465 END IF;
466 RAISE;
467 END;
468 EXCEPTION
469 WHEN OTHERS THEN
470 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
471 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_rptbl_govt');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END stap_get_rptbl_govt;
475
476 FUNCTION Stap_Get_Rptbl_Sbmsn(
477 p_submission_yr IN NUMBER ,
478 p_submission_number IN NUMBER ,
479 p_person_id IN NUMBER ,
480 p_course_cd IN VARCHAR2 ,
481 p_crv_version_number IN NUMBER ,
482 p_unit_cd IN VARCHAR2 ,
483 p_uv_version_number IN NUMBER ,
484 p_teach_cal_type IN VARCHAR2 ,
485 p_teach_ci_sequence_number IN NUMBER ,
486 p_tr_org_unit_cd IN VARCHAR2 ,
487 p_tr_ou_start_dt IN DATE ,
488 p_eftsu IN NUMBER ,
489 p_enrolled_dt IN DATE ,
490 p_discontinued_dt IN DATE ,
491 p_govt_semester IN NUMBER ,
492 p_teach_census_dt IN DATE ,
493 p_load_cal_type IN VARCHAR2 ,
494 p_load_ci_sequence_number IN NUMBER,
495 p_uoo_id IN IGS_EN_SU_ATTEMPT.UOO_ID%TYPE)
496 RETURN VARCHAR2 AS
497 -------------------------------------------------------------------------------------------
498 -- This routine determines if a unit of study should be
499 -- reported to the government within a specified submission.
500 -- It can return either of :
501 -- ** 'Y' - Yes
502 -- Government Reportable for the specified submission
503 -- ** 'N' - No
504 -- Not Government Reportable for the specified submisssion
505 -- ** 'W' - Warning
506 -- To be manually checked by the Statistics Officer.
507 -- By default, Warning records will be reported to the Government
508 -- ** NULL - Not determined
509 -- validate the input parameters
510
511 --Change History:
512 --Who When What
513 --kkillams 28-04-2003 Added new parameter p_uoo_id to the function.
514 -- w.r.t. bug number 2829262
515 -------------------------------------------------------------------------------------------
516 BEGIN
517 DECLARE
518 CURSOR c_daiv (
519 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
520 cp_ci_seq_num IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE) IS
521 SELECT daiv.alias_val
522 FROM IGS_CA_DA_INST_V daiv,
523 IGS_GE_S_GEN_CAL_CON sgcc
524 WHERE daiv.cal_type = cp_cal_type AND
525 daiv.ci_sequence_number = cp_ci_seq_num AND
526 daiv.dt_alias = sgcc.census_dt_alias
527 ORDER BY daiv.alias_val ASC;
528 v_teach_census_dt IGS_CA_DA_INST_V.alias_val%TYPE;
529 v_submission_1_census_dt DATE;
530 v_submission_2_census_dt DATE;
531 v_govt_semester NUMBER;
532 v_exclusion_level VARCHAR2(15);
533 v_other_detail VARCHAR2(255);
534 v_discontinued_dt DATE;
535 BEGIN
536 IF (p_submission_yr IS NULL OR
537 p_submission_number IS NULL) THEN
538 RETURN NULL;
539 END IF;
540 IF (p_enrolled_dt IS NULL) THEN
541 RETURN 'N';
542 END IF;
543 -- define the submission census dates
544 v_submission_1_census_dt := IGS_GE_DATE.igsdate(TO_CHAR(p_SUBMISSION_YR)||'03/31');
545 v_submission_2_census_dt := IGS_GE_DATE.igsdate(TO_CHAR(p_SUBMISSION_YR)||'08/31');
546 -- get the Government Semester
547 IF (p_govt_semester IS NULL) THEN
548 v_govt_semester := IGS_ST_GEN_002.stap_get_govt_sem(
549 p_submission_yr,
550 p_submission_number,
551 p_load_cal_type,
552 p_load_ci_sequence_number,
553 p_teach_cal_type);
554 IF (v_govt_semester IS NULL) THEN
555 RETURN NULL;
556 END IF;
557 ELSE
558 v_govt_semester := p_govt_semester;
559 END IF;
560 -- get the census date for the teaching calendar
561 -- census date is only required for certain submission/
562 -- semester combinations
563 IF (p_teach_census_dt IS NULL AND
564 ((p_submission_number = 1 AND
565 (v_govt_semester = 3 OR
566 v_govt_semester = 5)) OR
567 (p_submission_number = 2 AND
568 v_govt_semester = 4))) THEN
569 -- select the alias_val from IGS_CA_DA_INST_V.
570 -- select the first record only
571 OPEN c_daiv(
572 p_teach_cal_type,
573 p_teach_ci_sequence_number);
574 FETCH c_daiv INTO v_teach_census_dt;
575 -- return NULL if a record can't be found
576 IF (c_daiv%NOTFOUND) THEN
577 CLOSE c_daiv;
578 RETURN NULL;
579 END IF;
580 CLOSE c_daiv;
581 ELSE
582 v_teach_census_dt := p_teach_census_dt;
583 END IF;
584 -- set the v_discontinued_dt to p_discontinued_dt
585 -- set the value to a late date if not set
586 v_discontinued_dt := NVL(p_discontinued_dt,
587 IGS_GE_DATE.igsdate('9999/01/01'));
588 -- determine if the unit should be reported in the
589 -- specified submission
590 -- Government semester 1
591 IF (v_govt_semester = 1) THEN
592 IF (p_enrolled_dt > v_submission_1_census_dt OR
593 v_discontinued_dt <= v_submission_1_census_dt) THEN
594 RETURN 'N';
595 ELSE
596 RETURN stap_get_rptbl_govt(
597 p_person_id,
598 p_course_cd,
599 p_crv_version_number,
600 p_unit_cd,
601 p_uv_version_number,
602 p_teach_cal_type,
603 p_teach_ci_sequence_number,
604 p_tr_org_unit_cd,
605 p_tr_ou_start_dt,
606 p_eftsu,
607 v_submission_1_census_dt,
608 v_exclusion_level,
609 p_uoo_id);
610 END IF;
611 END IF;
612 -- Government semester 3 and 5
613 IF (v_govt_semester = 3 OR
614 v_govt_semester = 5) THEN
615 IF (p_enrolled_dt > v_teach_census_dt OR
616 v_discontinued_dt <= v_teach_census_dt) THEN
617 RETURN 'N';
618 ELSE
619 RETURN stap_get_rptbl_govt(
620 p_person_id,
621 p_course_cd,
622 p_crv_version_number,
623 p_unit_cd,
624 p_uv_version_number,
625 p_teach_cal_type,
626 p_teach_ci_sequence_number,
627 p_tr_org_unit_cd,
628 p_tr_ou_start_dt,
629 p_eftsu,
630 v_teach_census_dt,
631 v_exclusion_level,
632 p_uoo_id);
633 END IF;
634 END IF;
635 -- Government semester 2 and 4
636 IF (v_govt_semester = 2 OR
637 v_govt_semester = 4) THEN
638 -- Submission 1
639 IF (p_submission_number = 1) THEN
640 IF (p_enrolled_dt > v_submission_1_census_dt OR
641 v_discontinued_dt <= v_submission_1_census_dt) THEN
642 RETURN 'N';
643 ELSE
644 RETURN stap_get_rptbl_govt(
645 p_person_id,
646 p_course_cd,
647 p_crv_version_number,
648 p_unit_cd,
649 p_uv_version_number,
650 p_teach_cal_type,
651 p_teach_ci_sequence_number,
652 p_tr_org_unit_cd,
653 p_tr_ou_start_dt,
654 p_eftsu,
655 v_submission_1_census_dt,
656 v_exclusion_level,
657 p_uoo_id);
658 END IF;
659 END IF;
660 -- Submission 2 or 3
661 IF (p_submission_number = 2 OR
662 p_submission_number = 3) THEN
663 -- Government semester 2
664 IF (v_govt_semester = 2) THEN
665 IF (p_enrolled_dt > v_submission_2_census_dt OR
666 v_discontinued_dt <= v_submission_2_census_dt) THEN
667 RETURN 'N';
668 ELSE
669 RETURN stap_get_rptbl_govt(
670 p_person_id,
671 p_course_cd,
672 p_crv_version_number,
673 p_unit_cd,
674 p_uv_version_number,
675 p_teach_cal_type,
676 p_teach_ci_sequence_number,
677 p_tr_org_unit_cd,
678 p_tr_ou_start_dt,
679 p_eftsu,
680 v_submission_2_census_dt,
681 v_exclusion_level,
682 p_uoo_id);
683 END IF;
684 END IF;
685 -- Government semester 4
686 IF (v_govt_semester = 4) THEN
687 IF (p_enrolled_dt > v_teach_census_dt OR
688 v_discontinued_dt <= v_teach_census_dt) THEN
689 RETURN 'N';
690 ELSE
691 RETURN stap_get_rptbl_govt(
692 p_person_id,
693 p_course_cd,
694 p_crv_version_number,
695 p_unit_cd,
696 p_uv_version_number,
697 p_teach_cal_type,
698 p_teach_ci_sequence_number,
699 p_tr_org_unit_cd,
700 p_tr_ou_start_dt,
701 p_eftsu,
702 v_teach_census_dt,
703 v_exclusion_level,
704 p_uoo_id);
705 END IF;
706 END IF;
707 END IF;
708 END IF;
709 -- Government reportable value for submission cannot
710 -- be determined
711 RETURN NULL;
712 EXCEPTION
713 WHEN OTHERS THEN
714 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
715 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_rptbl_sbmsn');
716 IGS_GE_MSG_STACK.ADD;
717 App_Exception.Raise_Exception;
718 END;
719 END stap_get_rptbl_sbmsn;
720
721 Procedure Stap_Get_Sca_Data(
722 p_submission_yr NUMBER ,
723 p_submission_number IN NUMBER ,
724 p_person_id IN NUMBER ,
725 p_course_cd IN VARCHAR2 ,
726 p_effective_dt IN DATE ,
727 p_crv_version_number IN NUMBER ,
728 p_commencing_student_ind IN VARCHAR2 DEFAULT 'N',
729 p_load_cal_type IN VARCHAR2 ,
730 p_load_ci_sequence_number IN NUMBER ,
731 p_logged_ind IN OUT NOCOPY BOOLEAN ,
732 p_s_log_type IN VARCHAR2 ,
733 p_creation_dt IN DATE ,
734 p_govt_semester IN NUMBER ,
735 p_award_course_ind IN VARCHAR2 DEFAULT 'N',
736 p_govt_citizenship_cd IN VARCHAR2 ,
737 p_prior_seced_tafe IN VARCHAR2 ,
738 p_prior_seced_school IN VARCHAR2 ,
739 p_sca_commencement_dt OUT NOCOPY DATE ,
740 p_prior_studies_exemption OUT NOCOPY NUMBER ,
741 p_exemption_institution_cd OUT NOCOPY VARCHAR2 ,
742 p_govt_exemption_inst_cd OUT NOCOPY VARCHAR2 ,
743 p_tertiary_entrance_score OUT NOCOPY NUMBER ,
744 p_basis_for_admission_type OUT NOCOPY VARCHAR2 ,
745 p_govt_basis_for_adm_type OUT NOCOPY VARCHAR2 ,
746 p_hecs_amount_pd OUT NOCOPY NUMBER ,
747 p_hecs_payment_option OUT NOCOPY VARCHAR2 ,
748 p_govt_hecs_payment_option OUT NOCOPY VARCHAR2 ,
749 p_tuition_fee OUT NOCOPY NUMBER ,
750 p_hecs_fee OUT NOCOPY NUMBER ,
751 p_differential_hecs_ind OUT NOCOPY VARCHAR2 )
752 AS
753 /*
754 || Created By : [email protected]
755 || Created On : 28-AUG-2000
756 || Purpose :
757 || Known limitations, enhancements or remarks :
758 || Change History :
759 || Who When What
760 || pkpatel 05-MAR-2002 Bug NO: 2224621
761 || Modified GOVT_EXEMPTION_INST_CD from NUMBER to VARCHAR2. Since its source
762 || IGS_OR_INSTITUTION.GOVT_INSTITUTION_CD is modified from NUMBER to VARCHAR2.
763 || (reverse chronological order - newest change first)
764 */
765 gv_other_detail VARCHAR2(255);
766 BEGIN -- stap_get_sca_data
767 DECLARE
768 v_adm_admission_appl_number
769 IGS_EN_STDNT_PS_ATT.adm_admission_appl_number%TYPE;
770 v_adm_nominated_course_cd IGS_EN_STDNT_PS_ATT.adm_nominated_course_cd%TYPE;
771 v_prior_studies_exemption IGS_AV_ADV_STANDING.total_exmptn_perc_grntd%TYPE;
772 v_temp_prior_studies_exemption IGS_AV_ADV_STANDING.total_exmptn_perc_grntd%TYPE;
773 v_exemption_institution_cd IGS_AV_ADV_STANDING.exemption_institution_cd%TYPE;
774 v_govt_exemption_inst_cd
775 IGS_ST_GOVT_STDNT_EN.govt_exemption_institution_cd%TYPE;
776 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
777 v_basis_for_adm_type IGS_AD_BASIS_FOR_AD.basis_for_admission_type%TYPE;
778 v_govt_basis_for_adm_type IGS_AD_BASIS_FOR_AD.govt_basis_for_adm_type%TYPE;
779 v_tertiary_entrance_score IGS_ST_GOVT_STDNT_EN.tertiary_entrance_score%TYPE;
780 v_result_obtained_yr IGS_AD_AUS_SEC_EDU.result_obtained_yr%TYPE;
781 v_aus_scndry_edu_ass_type IGS_AD_AUSE_ED_AS_TY.aus_scndry_edu_ass_type%TYPE;
782 v_override_govt_score IGS_AS_TYPGOV_SCORMP.govt_score%TYPE;
783 v_diff_hecs_ind IGS_EN_STDNTPSHECSOP.differential_hecs_ind%TYPE;
784 v_hecs_payment_option IGS_EN_STDNTPSHECSOP.hecs_payment_option%TYPE;
785 v_s_hecs_payment_type IGS_FI_GOV_HEC_PA_OP.s_hecs_payment_type%TYPE;
786 v_govt_hpo IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
787 v_acad_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE;
788 v_acad_ci_sequence_number
789 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE;
790 v_hecs_amount_pd NUMBER;
791 v_hecs_fee NUMBER;
792 v_tuition_fee NUMBER;
793 v_acad_start_dt DATE;
794 v_acad_end_dt DATE;
795 v_alt_code IGS_CA_INST.alternate_code%TYPE;
796 v_message_name VARCHAR2(30);
797 v_current_log_ind BOOLEAN;
798 v_other_detail VARCHAR2(255);
799 CURSOR c_sca IS
800 SELECT sca.commencement_dt,
801 sca.adm_admission_appl_number,
802 sca.adm_nominated_course_cd
803 FROM IGS_EN_STDNT_PS_ATT sca
804 WHERE sca.person_id = p_person_id AND
805 sca.course_cd = p_course_cd;
806 CURSOR c_aca (
807 cp_admission_appl_number
808 IGS_EN_STDNT_PS_ATT.adm_admission_appl_number%TYPE,
809 cp_nominated_course_cd
810 IGS_EN_STDNT_PS_ATT.adm_nominated_course_cd%TYPE) IS
811 SELECT aca.basis_for_admission_type
812 FROM IGS_AD_PS_APPL aca
813 WHERE aca.person_id = p_person_id AND
814 aca.admission_appl_number = cp_admission_appl_number AND
815 aca.nominated_course_cd = cp_nominated_course_cd;
816 CURSOR c_bfa (
817 cp_basis_for_adm_type IGS_AD_BASIS_FOR_AD.basis_for_admission_type%TYPE) IS
818 SELECT bfa.govt_basis_for_adm_type
819 FROM IGS_AD_BASIS_FOR_AD bfa
820 WHERE bfa.basis_for_admission_type = cp_basis_for_adm_type;
821 CURSOR c_ase IS
822 SELECT ase.score,
823 ase.result_obtained_yr,
824 aseat.aus_scndry_edu_ass_type
825 FROM IGS_AD_AUS_SEC_EDU ase,
826 IGS_AD_AUSE_ED_AS_TY aseat
827 WHERE ase.person_id = p_person_id AND
828 aseat.aus_scndry_edu_ass_type = ase.aus_scndry_edu_ass_type AND
829 aseat.govt_reported_ind = 'Y'
830 ORDER BY NVL(ase.result_obtained_yr,0) DESC,
831 ase.last_update_date DESC;
832 CURSOR c_atgsm (
833 cp_result_obtained_yr IGS_AS_TYPGOV_SCORMP.result_obtained_yr%TYPE,
834 cp_scndry_edu_ass_type IGS_AS_TYPGOV_SCORMP.scndry_edu_ass_type%TYPE,
835 cp_institution_score IGS_AS_TYPGOV_SCORMP.institution_score%TYPE) IS
836 SELECT atgsm.govt_score
837 FROM IGS_AS_TYPGOV_SCORMP atgsm
838 WHERE atgsm.result_obtained_yr = cp_result_obtained_yr AND
839 atgsm.scndry_edu_ass_type = cp_scndry_edu_ass_type AND
840 atgsm.institution_score = cp_institution_score;
841 CURSOR c_adv_stnd IS
842 SELECT TRUNC(ast.total_exmptn_perc_grntd),
843 ast.exemption_institution_cd
844 FROM IGS_AV_ADV_STANDING ast
845 WHERE ast.person_id = p_person_id AND
846 ast.course_cd = p_course_cd AND
847 ast.version_number = p_crv_version_number;
848 CURSOR c_inst_code (
849 cp_exemption_institution_cd
850 IGS_AV_ADV_STANDING.exemption_institution_cd%TYPE) IS
851 SELECT ins.govt_institution_cd
852 FROM IGS_OR_INSTITUTION ins
853 WHERE ins.institution_cd = cp_exemption_institution_cd;
854 CURSOR c_get_hpo IS
855 SELECT scho.differential_hecs_ind,
856 scho.hecs_payment_option,
857 hpo.govt_hecs_payment_option,
858 ghpo.s_hecs_payment_type
859 FROM IGS_EN_STDNTPSHECSOP scho,
860 IGS_FI_HECS_PAY_OPTN hpo,
861 IGS_FI_GOV_HEC_PA_OP ghpo
862 WHERE scho.person_id = p_person_id AND
863 scho.course_cd = p_course_cd AND
864 scho.start_dt <= p_effective_dt AND
865 (scho.end_dt IS NULL OR
866 scho.end_dt >= p_effective_dt) AND
867 hpo.hecs_payment_option = scho.hecs_payment_option AND
868 ghpo.govt_hecs_payment_option = hpo.govt_hecs_payment_option
869 ORDER BY scho.end_dt ASC;
870 PROCEDURE stapl_ins_log_message(
871 p_message_name VARCHAR2,
872 p_logged_ind IN OUT NOCOPY BOOLEAN)
873 AS
874 BEGIN
875 DECLARE
876 BEGIN
877 -- Check if an entry has been written to the error log
878 IF p_logged_ind = FALSE THEN
879 -- Log an error to the IGS_GE_S_ERROR_LOG
880 p_logged_ind := TRUE;
881 END IF;
882 -- Create an entry in the System Log Entry
883 IGS_GE_GEN_003.genp_ins_log_entry(
884 p_s_log_type,
885 p_creation_dt,
886 'IGS_PE_PERSON IGS_PS_COURSE,' ||
887 TO_CHAR(p_person_id) || ', ' ||
888 p_course_cd,
889 p_message_name,
890 NULL);
891 END;
892 EXCEPTION
893 WHEN OTHERS THEN
894 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
895 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stapl_ins_log_message');
896 IGS_GE_MSG_STACK.ADD;
897 App_Exception.Raise_Exception;
898 END stapl_ins_log_message;
899 BEGIN
900 -- get the commencement dt
901 OPEN c_sca;
902 FETCH c_sca INTO v_commencement_dt,
903 v_adm_admission_appl_number,
904 v_adm_nominated_course_cd;
905 -- raise an exception if a record not found
906 IF (c_sca%NOTFOUND) THEN
907 CLOSE c_sca;
908 Fnd_Message.Set_Name('IGS','IGS_EN_CAN_LOC_EXIS_STUD ');
909 IGS_GE_MSG_STACK.ADD;
910 App_Exception.Raise_Exception;
911 END IF;
912 CLOSE c_sca;
913 IF (p_submission_number = 1) THEN
914 -- retrieve advanced standing data
915 IF (p_commencing_student_ind = 'N') THEN
916 v_prior_studies_exemption := 00;
917 v_exemption_institution_cd := '0001';
918 v_govt_exemption_inst_cd := '0001';
919 ELSE
920 OPEN c_adv_stnd;
921 FETCH c_adv_stnd INTO v_temp_prior_studies_exemption,
922 v_exemption_institution_cd;
923 IF (c_adv_stnd%NOTFOUND) THEN
924 v_prior_studies_exemption := 00;
925 v_exemption_institution_cd := '0001';
926 v_govt_exemption_inst_cd := '0001';
927 ELSE
928 -- Ensure the prior studies excemption value is not too large
929 IF v_temp_prior_studies_exemption > 99 THEN
930 v_prior_studies_exemption := 99;
931 --Check if an entry has been written to the error log
932 IF p_logged_ind = FALSE THEN
933 v_other_detail := 'Check the system log:' ||
934 ' s_log_type-' || p_s_log_type ||
935 ', creation_dt-' || IGS_GE_DATE.igschardt(p_creation_dt);
936 -- set that an error has been logged
937 p_logged_ind := TRUE;
938 END IF;
939 --Create an entry in the system log entry
940 IGS_GE_GEN_003.genp_ins_log_entry (
941 p_s_log_type,
942 p_creation_dt,
943 'IGS_PE_PERSON IGS_PS_COURSE,' ||
944 TO_CHAR(p_person_id) || ', ' ||
945 p_course_cd,
946 4250,
947 NULL);
948 ELSE
949 v_prior_studies_exemption := v_temp_prior_studies_exemption;
950 END IF;
951 -- determine the government exemption IGS_OR_INSTITUTION code
952 OPEN c_inst_code(v_exemption_institution_cd);
953 FETCH c_inst_code INTO v_govt_exemption_inst_cd;
954 IF (c_inst_code%NOTFOUND) THEN
955 -- exemption IGS_OR_INSTITUTION is a DEETYA value
956 v_govt_exemption_inst_cd := v_exemption_institution_cd;
957 END IF;
958 CLOSE c_inst_code;
959 END IF;
960 CLOSE c_adv_stnd;
961 END IF;
962 -- retrieve the basis for admission
963 IF (p_commencing_student_ind = 'N') THEN
964 v_basis_for_adm_type := '01';
965 v_govt_basis_for_adm_type := '01';
966 ELSE
967 OPEN c_aca(v_adm_admission_appl_number,
968 v_adm_nominated_course_cd);
969 FETCH c_aca INTO v_basis_for_adm_type;
970 IF c_aca%FOUND AND
971 v_basis_for_adm_type IS NOT NULL THEN
972 CLOSE c_aca;
973 OPEN c_bfa(v_basis_for_adm_type);
974 FETCH c_bfa INTO v_govt_basis_for_adm_type;
975 CLOSE c_bfa;
976 ELSE
977 CLOSE c_aca;
978 v_basis_for_adm_type := '99';
979 v_govt_basis_for_adm_type := '99';
980 --Check if an entry has been written to the error log
981 IF p_logged_ind = FALSE THEN
982 v_other_detail := 'Check the system log:' ||
983 ' s_log_type-' || p_s_log_type ||
984 ', creation_dt-' || IGS_GE_DATE.igschardt(p_creation_dt);
985 -- set that an error has been logged
986 p_logged_ind := TRUE;
987 END IF;
988 --Create an entry in the system log entry
989 IGS_GE_GEN_003.genp_ins_log_entry (
990 p_s_log_type,
991 p_creation_dt,
992 'IGS_PE_PERSON IGS_PS_COURSE,' || TO_CHAR(p_person_id) || ', ' || p_course_cd,
993 4218,
994 NULL);
995 END IF;
996 END IF;
997 -- retrieve the tertiary entrance score
998 IF (p_commencing_student_ind = 'N') OR
999 (p_commencing_student_ind = 'Y' AND
1000 NOT(IGS_EN_GEN_008.enrp_get_ug_pg_crs(
1001 p_course_cd,
1002 p_crv_version_number) = 'UG' AND
1003 p_award_course_ind = 'Y')) THEN
1004 -- Not commencing an undergraduate award course.
1005 v_tertiary_entrance_score := 001;
1006 ELSIF p_govt_citizenship_cd NOT IN (1, 2, 3) OR
1007 (p_prior_seced_tafe <> ('2' || SUBSTR((p_submission_yr - 1), 3)) AND
1008 p_prior_seced_school <> ('2' || SUBSTR((p_submission_yr - 1), 3))) THEN
1009 -- Student is an overseas student or did not complete
1010 -- the major Year 12 examination in the prior year
1011 -- in any Australian State/Territory
1012 v_tertiary_entrance_score := 999;
1013 -- Create s system log entry
1014 stapl_ins_log_message(
1015 4534,
1016 p_logged_ind);
1017 ELSE
1018 OPEN c_ase;
1019 FETCH c_ase INTO v_tertiary_entrance_score,
1020 v_result_obtained_yr,
1021 v_aus_scndry_edu_ass_type;
1022 IF c_ase%NOTFOUND OR
1023 v_tertiary_entrance_score IS NULL OR
1024 v_tertiary_entrance_score = 0 THEN
1025 v_tertiary_entrance_score := 998;
1026 ELSIF c_ase%FOUND AND
1027 v_result_obtained_yr IS NULL THEN
1028 --Check if an entry has been written to the error log
1029 IF p_logged_ind = FALSE THEN
1030 v_other_detail := 'Check the system log:' ||
1031 ' s_log_type-' || p_s_log_type ||
1032 ', creation_dt-' || IGS_GE_DATE.igschardt(p_creation_dt);
1033 -- set that an error has been logged
1034 p_logged_ind := TRUE;
1035 END IF;
1036 --Create an entry in the system log entry
1037 IGS_GE_GEN_003.genp_ins_log_entry (
1038 p_s_log_type,
1039 p_creation_dt,
1040 'IGS_PE_PERSON IGS_PS_COURSE,' || TO_CHAR(p_person_id) || ', ' || p_course_cd,
1041 4219,
1042 NULL);
1043 ELSIF c_ase%FOUND AND
1044 v_result_obtained_yr IS NOT NULL THEN
1045 OPEN c_atgsm(
1046 v_result_obtained_yr,
1047 v_aus_scndry_edu_ass_type,
1048 v_tertiary_entrance_score);
1049 FETCH c_atgsm INTO v_override_govt_score;
1050 IF c_atgsm%FOUND THEN
1051 v_tertiary_entrance_score := v_override_govt_score;
1052 END IF;
1053 CLOSE c_atgsm;
1054 END IF;
1055 CLOSE c_ase;
1056 END IF;
1057 END IF; -- Submission 1 data
1058 -- retrieve the HECS payment option
1059 -- select the first record only, which will be
1060 -- the end dated record if one exists
1061 OPEN c_get_hpo;
1062 FETCH c_get_hpo INTO v_diff_hecs_ind,
1063 v_hecs_payment_option,
1064 v_govt_hpo,
1065 v_s_hecs_payment_type;
1066 -- when no record, log and error and continue
1067 IF (c_get_hpo%NOTFOUND) THEN
1068 CLOSE c_get_hpo;
1069 -- Only log the exception if the course will be in the liabiity file.
1070 IF (p_submission_number = 1 AND p_govt_semester IN (1, 3, 5)) OR
1071 (p_submission_number = 2 AND p_govt_semester IN (2, 4)) THEN
1072 IF (p_logged_ind = FALSE) THEN
1073 p_logged_ind := TRUE;
1074 END IF;
1075 -- create an entry in the system log entry
1076 IGS_GE_GEN_003.genp_ins_log_entry (
1077 p_s_log_type,
1078 p_creation_dt,
1079 'IGS_PE_PERSON IGS_PS_COURSE' || ',' || TO_CHAR(p_person_id) || ',' || p_course_cd,
1080 4220,
1081 NULL);
1082 END IF;
1083 -- continue processing after this error has been logged
1084 v_diff_hecs_ind := 'Y';
1085 v_hecs_payment_option := '00';
1086 v_govt_hpo := '00';
1087 ELSE
1088 CLOSE c_get_hpo;
1089 END IF;
1090 -- retrieve the HECS amount paid
1091 v_hecs_amount_pd := ROUND(IGS_FI_GEN_001.finp_get_hecs_amt_pd(
1092 p_load_cal_type,
1093 p_load_ci_sequence_number,
1094 p_person_id,
1095 p_course_cd));
1096 -- retrieve the tuition_fee
1097 -- Check if the person hasn na HECS option indicating that the course
1098 -- is fully funded by an employer
1099 IF v_govt_hpo = '27' THEN
1100 v_tuition_fee := 0;
1101 ELSE
1102 v_tuition_fee := TRUNC(IGS_FI_GEN_001.finp_get_tuition_fee(
1103 p_load_cal_type,
1104 p_load_ci_sequence_number,
1105 p_person_id,
1106 p_course_cd));
1107 END IF;
1108 -- retrieve the HECS fee
1109 IF v_s_hecs_payment_type = 'EXEMPT' THEN
1110 v_hecs_fee := 0;
1111 ELSE
1112 v_hecs_fee := ROUND(IGS_FI_GEN_001.finp_get_hecs_fee(
1113 p_load_cal_type,
1114 p_load_ci_sequence_number,
1115 p_person_id,
1116 p_course_cd));
1117 -- Cannot have a hecs fee greater than 4 digits
1118 IF v_hecs_fee > 9999 THEN
1119 IF (p_logged_ind = FALSE) THEN
1120 -- log an error to the IGS_GE_S_ERROR_LOG
1121 p_logged_ind := TRUE;
1122 END IF;
1123 -- create an entry in the system log entry
1124 IGS_GE_GEN_003.genp_ins_log_entry (
1125 p_s_log_type,
1126 p_creation_dt,
1127 'IGS_PE_PERSON IGS_PS_COURSE' || ',' || TO_CHAR(p_person_id) || ',' || p_course_cd,
1128 4901,
1129 NULL);
1130 v_hecs_fee := 9999;
1131 END IF;
1132 END IF;
1133 -- set the out NOCOPY parameters to the values set in this function
1134 p_sca_commencement_dt := v_commencement_dt;
1135 p_prior_studies_exemption := v_prior_studies_exemption;
1136 p_exemption_institution_cd := v_exemption_institution_cd;
1137 p_govt_exemption_inst_cd := v_govt_exemption_inst_cd;
1138 p_tertiary_entrance_score := v_tertiary_entrance_score;
1139 p_basis_for_admission_type := v_basis_for_adm_type;
1140 p_govt_basis_for_adm_type := v_govt_basis_for_adm_type;
1141 p_hecs_amount_pd := v_hecs_amount_pd;
1142 p_hecs_payment_option := v_hecs_payment_option;
1143 p_govt_hecs_payment_option := v_govt_hpo;
1144 p_tuition_fee := v_tuition_fee;
1145 p_hecs_fee := v_hecs_fee;
1146 p_differential_hecs_ind := v_diff_hecs_ind;
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 IF c_sca%ISOPEN THEN
1150 CLOSE c_sca;
1151 END IF;
1152 IF c_aca%ISOPEN THEN
1153 CLOSE c_aca;
1154 END IF;
1155 IF c_bfa%ISOPEN THEN
1156 CLOSE c_bfa;
1157 END IF;
1158 IF c_ase%ISOPEN THEN
1159 CLOSE c_ase;
1160 END IF;
1161 IF c_atgsm%ISOPEN THEN
1162 CLOSE c_atgsm;
1163 END IF;
1164 IF c_adv_stnd%ISOPEN THEN
1165 CLOSE c_adv_stnd;
1166 END IF;
1167 IF c_inst_code%ISOPEN THEN
1168 CLOSE c_inst_code;
1169 END IF;
1170 IF c_get_hpo%ISOPEN THEN
1171 CLOSE c_get_hpo;
1172 END IF;
1173 RAISE;
1174 END;
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1178 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_sca_data');
1179 IGS_GE_MSG_STACK.ADD;
1180 App_Exception.Raise_Exception;
1181 END stap_get_sca_data;
1182
1183 Function Stap_Get_Sch_Leaver(
1184 p_person_id IN NUMBER ,
1185 p_course_cd IN VARCHAR2 ,
1186 p_commencing_student_ind IN VARCHAR2 DEFAULT 'N',
1187 p_collection_yr IN NUMBER )
1188 RETURN NUMBER AS
1189 gv_other_detail VARCHAR2(255);
1190 BEGIN -- stap_get_sch_leaver
1191 -- Derive the school leaver value
1192 -- DEETYA element 925
1193 DECLARE
1194 v_ret_val NUMBER(2) DEFAULT 1;
1195 v_prior_post_grad IGS_PE_STATISTICS.prior_post_grad%TYPE;
1196 v_prior_degree IGS_PE_STATISTICS.prior_degree%TYPE;
1197 v_prior_subdeg_notafe IGS_PE_STATISTICS.prior_subdeg_notafe%TYPE;
1198 v_prior_subdeg_tafe IGS_PE_STATISTICS.prior_subdeg_tafe%TYPE;
1199 v_prior_tafe_award IGS_PE_STATISTICS.prior_tafe_award%TYPE;
1200 v_prior_other_qual IGS_PE_STATISTICS.prior_other_qual%TYPE;
1201 v_prior_seced_school IGS_PE_STATISTICS.prior_seced_school%TYPE;
1202 v_prior_seced_tafe IGS_PE_STATISTICS.prior_seced_tafe%TYPE;
1203 v_collection_yr VARCHAR2(4);
1204 v_collection_yr_less1 VARCHAR2(4);
1205 v_collection_yr_less2 VARCHAR2(4);
1206 CURSOR c_ps IS
1207 SELECT ps.prior_post_grad,
1208 ps.prior_degree,
1209 ps.prior_subdeg_notafe,
1210 ps.prior_subdeg_tafe,
1211 ps.prior_tafe_award,
1212 ps.prior_other_qual,
1213 ps.prior_seced_school,
1214 ps.prior_seced_tafe
1215 FROM IGS_PE_STATISTICS ps
1216 WHERE ps.person_id = p_person_id AND
1217 TRUNC(ps.start_dt) <= TRUNC(SYSDATE) AND
1218 (ps.end_dt IS NULL OR
1219 TRUNC(ps.end_dt) >= TRUNC(SYSDATE))
1220 ORDER BY ps.end_dt;
1221 BEGIN
1222 IF p_commencing_student_ind = 'N' THEN
1223 -- Not a commencing student
1224 RETURN v_ret_val;
1225 END IF;
1226 OPEN c_ps;
1227 FETCH c_ps INTO
1228 v_prior_post_grad,
1229 v_prior_degree,
1230 v_prior_subdeg_notafe,
1231 v_prior_subdeg_tafe,
1232 v_prior_tafe_award,
1233 v_prior_other_qual,
1234 v_prior_seced_school,
1235 v_prior_seced_tafe;
1236 IF c_ps%NOTFOUND THEN
1237 CLOSE c_ps;
1238 -- Other Commencing Student
1239 RETURN 8;
1240 END IF;
1241 CLOSE c_ps;
1242 v_collection_yr := TO_CHAR(p_collection_yr);
1243 v_collection_yr_less1 := TO_CHAR(TO_NUMBER(v_collection_yr) - 1);
1244 v_collection_yr_less2 := TO_CHAR(TO_NUMBER(v_collection_yr) - 2);
1245 IF SUBSTR(v_prior_post_grad, 1, 1) = '3' OR
1246 SUBSTR(v_prior_degree, 1, 1) = '3' OR
1247 SUBSTR(v_prior_subdeg_notafe, 1, 1) = '3' OR
1248 SUBSTR(v_prior_subdeg_tafe, 1, 1) = '3' OR
1249 SUBSTR(v_prior_tafe_award, 1, 1) = '3' OR
1250 SUBSTR(v_prior_other_qual, 1, 1) = '2' THEN
1251 -- student has completed a qualification higher than
1252 -- final secondary education at school or elsewhere
1253 v_ret_val := 2;
1254 ELSIF v_prior_seced_tafe = '2' || SUBSTR(v_collection_yr, 3,2) OR
1255 v_prior_seced_tafe = '2' || SUBSTR(v_collection_yr_less1,3,2) OR
1256 v_prior_seced_school = '2' || SUBSTR(v_collection_yr,3,2) OR
1257 v_prior_seced_school = '2' || SUBSTR(v_collection_yr_less1,3,2) THEN
1258 -- Student had completed final year of secondary education in
1259 -- the reference year or the year prior to the ref year
1260 v_ret_val := 3;
1261 ELSIF v_prior_seced_tafe BETWEEN
1262 '200' AND ('2' || SUBSTR(v_collection_yr_less2, 3,2)) OR
1263 v_prior_seced_school BETWEEN
1264 '200' AND ('2' || SUBSTR(v_collection_yr_less2, 3,2)) THEN
1265 -- Student had completed final year of secondary education earlier
1266 -- than the year prior to the reference year
1267 v_ret_val := 4;
1268 ELSIF SUBSTR(v_prior_seced_tafe, 1,1) = '2' OR
1269 SUBSTR(v_prior_seced_school, 1,1) = '2' THEN
1270 -- Student who had completed final year of secondary
1271 -- education but not information on the year of completion
1272 v_ret_val := 9;
1273 ELSE
1274 -- Other commencing student
1275 v_ret_val := 8;
1276 END IF;
1277 RETURN v_ret_val;
1278 END;
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1282 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_sch_leaver');
1283 IGS_GE_MSG_STACK.ADD;
1284 App_Exception.Raise_Exception;
1285 END stap_get_sch_leaver;
1286
1287 Function Stap_Get_Spclstn(
1288 p_course_cd IN VARCHAR2 ,
1289 p_version_number IN NUMBER )
1290 RETURN VARCHAR2 AS
1291 gv_other_detail VARCHAR2(255);
1292 BEGIN -- stap_get_spclstn
1293 -- This module get the student course attempt specialisation.
1294 -- Currently this can only be set to the field of study of the
1295 -- major of the course
1296 DECLARE
1297 CURSOR c_cfos_fos IS
1298 SELECT fos.govt_field_of_study
1299 FROM IGS_PS_FIELD_STUDY cfos,
1300 IGS_PS_FLD_OF_STUDY fos
1301 WHERE cfos.course_cd = p_course_cd AND
1302 cfos.version_number = p_version_number AND
1303 cfos.major_field_ind = 'Y' AND
1304 fos.field_of_study = cfos.field_of_study;
1305 v_govt_field_of_study IGS_PS_FLD_OF_STUDY.govt_field_of_study%TYPE;
1306 BEGIN
1307 -- Cursor handling
1308 OPEN c_cfos_fos;
1309 FETCH c_cfos_fos INTO v_govt_field_of_study;
1310 IF c_cfos_fos%FOUND THEN
1311 CLOSE c_cfos_fos;
1312 RETURN v_govt_field_of_study;
1313 END IF;
1314 CLOSE c_cfos_fos;
1315 -- Return the default value
1316 RETURN NULL;
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 IF c_cfos_fos%ISOPEN THEN
1320 CLOSE c_cfos_fos;
1321 END IF;
1322 RAISE;
1323 END;
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1327 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_003.stap_get_spclstn');
1328 IGS_GE_MSG_STACK.ADD;
1329 App_Exception.Raise_Exception;
1330 END stap_get_spclstn;
1331
1332 END IGS_ST_GEN_003;
[Home] [Help]