1 PACKAGE BODY IGS_EN_VAL_SCHO AS
2 /* $Header: IGSEN63B.pls 115.5 2002/11/29 00:06:19 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The function GENP_VAL_STRT_END_DT removed.
7 -- IGS_EN_VAL_SCHO.GENP_VAL_STRT_END_DT replaced by IGS_AD_VAL_EDTL.GENP_VAL_STRT_END_DT
8 --smadathi 25-AUG-2001 Bug No. 1956374 .The function GENP_VAL_SDTT_SESS removed.
9 -------------------------------------------------------------------------------------------
10 -- Validate the delete of a student course HECS option record.
11 FUNCTION enrp_val_scho_trgdel(
12 p_person_id IN NUMBER ,
13 p_course_cd IN VARCHAR2 ,
14 p_start_dt IN DATE ,
15 p_message_name OUT NOCOPY VARCHAR2)
16 RETURN BOOLEAN AS
17 BEGIN -- enrp_val_scho_trgdel
18 -- This module validates deletion of IGS_EN_STDNTPSHECSOP in
19 -- the database trigger. This varies from the form validation in
20 -- that it allows deletion of when the student course attempt is
21 -- unconfirmed.
22 DECLARE
23 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
24 CURSOR c_sca IS
25 SELECT sca.course_attempt_status
26 FROM IGS_EN_STDNT_PS_ATT sca
27 WHERE sca.person_id = p_person_id AND
28 sca.course_cd = p_course_cd;
29 v_sca_rec c_sca%ROWTYPE;
30 v_message_name varchar2(30) ;
31 BEGIN
32 -- Set the default message number
33 p_message_name := null;
34 -- Cursor handling
35 OPEN c_sca;
36 FETCH c_sca INTO v_sca_rec;
37 IF c_sca%NOTFOUND THEN
38 CLOSE c_sca;
39 -- This should not occur, resolve elsewhere
40 RETURN TRUE;
41 END IF;
42 CLOSE c_sca;
43 IF v_sca_rec.course_attempt_status = cst_unconfirm THEN
44 RETURN TRUE;
45 END IF;
46 -- Validate against start date
47 IF NOT IGS_EN_VAL_SCHO.enrp_val_scho_delete(
48 p_start_dt,
49 v_message_name) THEN
50 p_message_name := v_message_name;
51 RETURN FALSE;
52 END IF;
53 -- Return the default value
54 RETURN TRUE;
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF c_sca%ISOPEN THEN
58 CLOSE c_sca;
59 END IF;
60 RAISE;
61 END;
62 EXCEPTION
63 WHEN OTHERS THEN
64 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
65 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_trgdel');
66 IGS_GE_MSG_STACK.ADD;
67 App_Exception.Raise_Exception;
68 END enrp_val_scho_trgdel;
69 --
70 -- To perform all validations on a scho record
71 FUNCTION ENRP_VAL_SCHO_ALL(
72 p_person_id IN NUMBER ,
73 p_course_cd IN VARCHAR2 ,
74 p_start_dt IN DATE ,
75 p_end_dt IN DATE ,
76 p_hecs_payment_option IN VARCHAR2 ,
77 p_differential_hecs_ind IN VARCHAR2 ,
78 p_diff_hecs_ind_update_who IN VARCHAR2 ,
79 p_diff_hecs_ind_update_on IN DATE ,
80 p_diff_hecs_ind_update_comment IN VARCHAR2 ,
81 p_outside_aus_res_ind IN VARCHAR2 ,
82 p_nz_citizen_ind IN VARCHAR2 ,
83 p_nz_citizen_less2yr_ind IN VARCHAR2 ,
84 p_nz_citizen_not_res_ind IN VARCHAR2 ,
85 p_safety_net_ind IN VARCHAR2 ,
86 p_tax_file_number IN NUMBER ,
87 p_tax_file_number_collected_dt IN DATE ,
88 p_tax_file_invalid_dt IN DATE ,
89 p_tax_file_certificate_number IN NUMBER ,
90 p_message_name OUT NOCOPY VARCHAR2)
91 RETURN boolean AS
92 BEGIN -- enrp_val_scho_all
93 -- Perform all validations associated with the addition of a
94 -- IGS_EN_STDNTPSHECSOP record. This routine is typically called by
95 -- processes which are defaulting or HECS records and need all validations
96 -- to be performed.
97 -- The routine will return a single error, being the first one encountered.
98 DECLARE
99 v_message_name varchar2(30);
100 v_return_type VARCHAR2(1);
101 cst_error CONSTANT VARCHAR2(1) DEFAULT 'E';
102 BEGIN
103 -- Set the default message number
104 p_message_name := null;
105 -- Validate START DATE AND END DATE.
106 IF p_end_dt IS NOT NULL THEN
107 IF IGS_AD_VAL_EDTL.GENP_VAL_STRT_END_DT(
108 p_start_dt,
109 p_end_dt,
110 v_message_name) = FALSE THEN
111 p_message_name := v_message_name;
112 RETURN FALSE;
113 END IF;
114 END IF;
115 -- Validate the start/end dates against other hecs option records.
116 IF IGS_EN_VAL_SCHO.enrp_val_scho_st_end(
117 p_person_id,
118 p_course_cd,
119 p_start_dt,
120 p_end_dt,
121 v_message_name) = FALSE THEN
122 p_message_name := v_message_name;
123 RETURN FALSE;
124 END IF;
125 -- Validate for overlapping records.
126 IF IGS_EN_VAL_SCHO.enrp_val_scho_ovrlp(
127 p_person_id,
128 p_course_cd,
129 p_start_dt,
130 p_end_dt,
131 v_message_name) = FALSE THEN
132 p_message_name := v_message_name;
133 RETURN FALSE;
134 END IF;
135 -- Validate VISA indicators
136 IF IGS_EN_VAL_SCHO.enrp_val_scho_visa(
137 p_outside_aus_res_ind,
138 p_nz_citizen_ind,
139 p_nz_citizen_less2yr_ind,
140 p_nz_citizen_not_res_ind,
141 v_message_name) = FALSE THEN
142 p_message_name := v_message_name;
143 RETURN FALSE;
144 END IF;
145 -- Validate the tax file number
146 IF IGS_EN_VAL_SCHO.enrp_val_scho_tfn(
147 p_person_id,
148 p_course_cd,
149 p_start_dt,
150 p_tax_file_number,
151 v_message_name,
152 v_return_type) = FALSE THEN
153 IF v_return_type = cst_error THEN
154 p_message_name := v_message_name;
155 RETURN FALSE;
156 END IF;
157 END IF;
158 -- Validate the HECS payment option
159 IF IGS_EN_VAL_SCHO.enrp_val_scho_hpo(
160 p_hecs_payment_option,
161 p_outside_aus_res_ind,
162 p_nz_citizen_ind,
163 p_nz_citizen_less2yr_ind,
164 p_nz_citizen_not_res_ind,
165 p_safety_net_ind,
166 p_tax_file_number,
167 p_tax_file_number_collected_dt,
168 p_tax_file_certificate_number,
169 p_differential_hecs_ind,
170 v_message_name,
171 v_return_type) = FALSE THEN
172 IF v_return_type = cst_error THEN
173 p_message_name := v_message_name;
174 RETURN FALSE;
175 END IF;
176 END IF;
177 -- Validate the tax file number
178 IF IGS_EN_VAL_SCHO.enrp_val_tfn_invalid(
179 p_tax_file_number,
180 p_tax_file_invalid_dt,
181 v_message_name) = FALSE THEN
182 p_message_name := v_message_name;
183 RETURN FALSE;
184 END IF;
185 -- Validate the tax file certificate number
186 IF IGS_EN_VAL_SCHO.enrp_val_tfn_crtfct(
187 p_tax_file_number,
188 p_tax_file_invalid_dt,
189 p_tax_file_certificate_number,
190 v_message_name) = FALSE THEN
191 p_message_name := v_message_name;
192 RETURN FALSE;
193 END IF;
194 -- Validate END DATE.
195 IF (p_hecs_payment_option IS NOT NULL OR
196 p_end_dt IS NOT NULL) THEN
197 IF IGS_EN_VAL_SCHO.enrp_val_scho_expire (
198 p_person_id,
199 p_course_cd,
200 p_start_dt,
201 p_end_dt,
202 p_hecs_payment_option,
203 v_message_name) = FALSE THEN
204 p_message_name := v_message_name;
205 RETURN FALSE;
206 END IF;
207 END IF;
208 -- Validate the student course attempt HECS option HECS payment option,
209 -- and the course type of the course version for the student course attempt.
210 IF (p_hecs_payment_option IS NOT NULL) THEN
211 IF IGS_EN_VAL_SCHO.enrp_val_hpo_crs_typ (
212 p_person_id,
213 p_course_cd,
214 p_hecs_payment_option,
215 v_message_name,
216 v_return_type) = FALSE THEN
217 IF v_return_type = cst_error THEN
218 p_message_name := v_message_name;
219 RETURN FALSE;
220 END IF;
221 END IF;
222 END IF;
223 -- Validate the student course attempt HECS option HECS payment option,
224 -- and the special course type of the course version for the student
225 -- course attempt.
226 IF p_hecs_payment_option IS NOT NULL THEN
227 IF IGS_EN_VAL_SCHO.enrp_val_hpo_spc_crs (
228 p_person_id,
229 p_course_cd,
230 p_hecs_payment_option,
231 v_message_name,
232 v_return_type) = FALSE THEN
233 IF v_return_type = cst_error THEN
234 p_message_name := v_message_name;
235 RETURN FALSE;
236 END IF;
237 END IF;
238 END IF;
239 -- Validate the student course attempt HECS option HECS payment option,
240 -- the course type of the course version for the student course attempt,
241 -- and the person statistics citizenship code.
242 IF (p_hecs_payment_option IS NOT NULL OR
243 p_end_dt IS NOT NULL) THEN
244 IF IGS_EN_VAL_SCHO.enrp_val_hpo_crs_cic (
245 p_person_id,
246 p_course_cd,
247 p_start_dt,
248 p_end_dt,
249 p_hecs_payment_option,
250 NULL,
251 NULL,
252 NULL,
253 v_message_name,
254 v_return_type) = FALSE THEN
255 IF v_return_type = cst_error THEN
256 p_message_name := v_message_name;
257 RETURN FALSE;
258 END IF;
259 END IF;
260 END IF;
261 -- Validate the student course attempt HECS option HECS payment option,
262 -- and the person statistics citizenship code.
263 IF (p_hecs_payment_option IS NOT NULL OR
264 p_end_dt IS NOT NULL) THEN
265 IF IGS_EN_VAL_SCHO.enrp_val_hpo_cic (
266 p_person_id,
267 p_course_cd,
268 p_start_dt,
269 p_end_dt,
270 p_hecs_payment_option,
271 NULL,
272 NULL,
273 NULL,
274 v_message_name,
275 v_return_type) = FALSE THEN
276 IF v_return_type = cst_error THEN
277 p_message_name := v_message_name;
278 RETURN FALSE;
279 END IF;
280 END IF;
281 END IF;
282 -- Validate the student course attempt HECS option HECS payment option,
283 -- and the person statistics citizenship code and permanent resident code.
284 IF (p_hecs_payment_option IS NOT NULL OR
285 p_end_dt IS NOT NULL) THEN
286 IF IGS_EN_VAL_SCHO.enrp_val_hpo_cic_prc (
287 p_person_id,
288 p_course_cd,
289 p_start_dt,
290 p_end_dt,
291 p_hecs_payment_option,
292 NULL,
293 NULL,
294 NULL,
295 NULL,
296 v_message_name,
297 v_return_type) = FALSE THEN
298 IF v_return_type = cst_error THEN
299 p_message_name := v_message_name;
300 RETURN FALSE;
301 END IF;
302 END IF;
303 END IF;
304 -- Validate the student course attempt HECS option visa indicators,
305 -- and the person statistics citizenship code and permanent resident code.
306 IF (p_outside_aus_res_ind IS NOT NULL OR
307 p_nz_citizen_ind IS NOT NULL OR
308 p_nz_citizen_less2yr_ind IS NOT NULL OR
309 p_nz_citizen_not_res_ind IS NOT NULL OR
310 p_end_dt is NOT NULL) THEN
311 IF IGS_EN_VAL_SCHO.enrp_val_vis_cic_prc (
312 p_person_id,
313 p_course_cd,
314 p_start_dt,
315 p_end_dt,
316 p_outside_aus_res_ind,
317 p_nz_citizen_ind,
318 p_nz_citizen_less2yr_ind,
319 p_nz_citizen_not_res_ind,
320 NULL,
321 NULL,
322 NULL,
323 NULL,
324 v_message_name,
325 v_return_type) = FALSE THEN
326 IF v_return_type = cst_error THEN
327 p_message_name := v_message_name;
328 RETURN FALSE;
329 END IF;
330 END IF;
331 END IF;
332 -- Validate the student course attempt HECS option HECS payment option,
333 -- the student course attempt HECS option visa indicators, and the person
334 -- statistics citizenship code and permanent resident code.
335 IF (p_hecs_payment_option IS NOT NULL OR
336 p_outside_aus_res_ind IS NOT NULL OR
337 p_end_dt IS NOT NULL) THEN
338 IF IGS_EN_VAL_SCHO.enrp_val_ho_cic_prc (
339 p_person_id,
340 p_course_cd,
341 p_start_dt,
342 p_end_dt,
343 p_hecs_payment_option,
344 p_outside_aus_res_ind,
345 NULL,
346 NULL,
347 NULL,
348 NULL,
349 v_message_name,
350 v_return_type) = FALSE THEN
351 IF v_return_type = cst_error THEN
352 p_message_name := v_message_name;
353 RETURN FALSE;
354 END IF;
355 END IF;
356 END IF;
357 -- Validate the student course attempt HECS option HECS payment option,
358 -- the student course attempt HECS option visa indicators, and the person
359 -- statistics citizenship code.
360 IF (p_outside_aus_res_ind IS NOT NULL OR
361 p_nz_citizen_ind IS NOT NULL OR
362 p_nz_citizen_less2yr_ind IS NOT NULL OR
363 p_nz_citizen_not_res_ind is NOT NULL OR
364 p_end_dt is NOT NULL) THEN
365 IF IGS_EN_VAL_SCHO.enrp_val_hpo_vis_cic (
366 p_person_id,
367 p_course_cd,
368 p_start_dt,
369 p_end_dt,
370 p_hecs_payment_option,
371 p_outside_aus_res_ind,
372 p_nz_citizen_ind,
373 p_nz_citizen_less2yr_ind,
374 p_nz_citizen_not_res_ind,
375 NULL,
376 NULL,
377 NULL,
378 NULL,
379 v_message_name,
380 v_return_type) = FALSE THEN
381 IF v_return_type = cst_error THEN
382 p_message_name := v_message_name;
383 RETURN FALSE;
384 END IF;
385 END IF;
386 END IF;
387 -- Validate the student course attempt HECS option HECS payment option,
388 -- and the person statistics citizenship code and other person statistics
389 -- values including year of arrival and term LOCATION country and postcode.
390 IF (p_hecs_payment_option IS NOT NULL OR
391 p_end_dt IS NOT NULL) THEN
392 IF IGS_EN_VAL_SCHO.enrp_val_hpo_cic_ps (
393 p_person_id,
394 p_course_cd,
395 p_start_dt,
396 p_end_dt,
400 NULL,
397 p_hecs_payment_option,
398 NULL,
399 NULL,
401 NULL,
402 NULL,
403 NULL,
404 NULL,
405 v_message_name,
406 v_return_type) = FALSE THEN
407 IF v_return_type = cst_error THEN
408 p_message_name := v_message_name;
409 RETURN FALSE;
410 END IF;
411 END IF;
412 END IF;
413 RETURN TRUE;
414 END;
415 EXCEPTION
416 WHEN OTHERS THEN
417 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
418 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_all');
419 IGS_GE_MSG_STACK.ADD;
420 App_Exception.Raise_Exception;
421 END enrp_val_scho_all;
422 --
423
424
425 -- Validate that there are no other open ended student course hecs option
426 FUNCTION ENRP_VAL_SCHO_OPEN(
427 p_person_id IN NUMBER ,
428 p_course_cd IN VARCHAR2 ,
429 p_start_dt IN DATE ,
430 p_message_name OUT NOCOPY VARCHAR2)
431 RETURN BOOLEAN AS
432 BEGIN
433 DECLARE
434 CURSOR gc_scho(
435 cp_person_id IGS_EN_STDNTPSHECSOP.person_id%TYPE,
436 cp_course_cd IGS_EN_STDNTPSHECSOP.course_cd%TYPE,
437 cp_start_dt IGS_EN_STDNTPSHECSOP.start_dt%TYPE) IS
438 SELECT IGS_EN_STDNTPSHECSOP.end_dt
439 FROM IGS_EN_STDNTPSHECSOP
440 WHERE IGS_EN_STDNTPSHECSOP.person_id = cp_person_id AND
441 IGS_EN_STDNTPSHECSOP.course_cd = cp_course_cd AND
442 IGS_EN_STDNTPSHECSOP.start_dt <> cp_start_dt;
443 BEGIN
444 -- this module validates that there are no other "open ended" scho
445 -- records for the nominated student course attempt
446 p_message_name := null;
447 FOR gc_scho_rec IN gc_scho(
448 p_person_id,
449 p_course_cd,
450 p_start_dt) LOOP
451 IF gc_scho_rec.end_dt IS NULL THEN
452 p_message_name := 'IGS_EN_OPEN_END_ALREADY_EXIST';
453 RETURN FALSE;
454 END IF;
455 END LOOP;
456 RETURN TRUE;
457 EXCEPTION
458 WHEN OTHERS THEN
459 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
460 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_open');
461 IGS_GE_MSG_STACK.ADD;
462 App_Exception.Raise_Exception;
463 END;
464 END enrp_val_scho_open;
465 --
466 -- Check for overlap in a students course hecs option records
467 FUNCTION enrp_val_scho_ovrlp(
468 p_person_id IN NUMBER ,
469 p_course_cd IN VARCHAR2 ,
470 p_start_dt IN DATE ,
471 p_end_dt IN DATE ,
472 p_message_name OUT NOCOPY VARCHAR2)
473 RETURN BOOLEAN AS
474 BEGIN
475 DECLARE
476 v_exit_loop BOOLEAN DEFAULT FALSE;
477 CURSOR c_scho IS
478 SELECT scho.start_dt,
479 scho.end_dt
480 FROM IGS_EN_STDNTPSHECSOP scho
481 WHERE scho.person_id = p_person_id AND
482 scho.course_cd = p_course_cd AND
483 scho.start_dt <> p_start_dt;
484 BEGIN
485 -- this module validates that the IGS_EN_STDNTPSHECSOP record
486 -- being created or updated does not overlap with an existing record
487 -- for the nominated person
488 p_message_name := null;
489 FOR v_scho_rec IN c_scho LOOP
490 IF v_scho_rec.end_dt IS NOT NULL THEN
491 -- Validate the start date is not between an existing
492 -- date range.
493 IF p_start_dt BETWEEN v_scho_rec.start_dt AND v_scho_rec.end_dt THEN
494 p_message_name := 'IGS_EN_HECS_STDT_BTWN_DTRNG';
495 v_exit_loop := TRUE;
496 EXIT;
497 -- Validate the end date is not between an
498 -- existing date range.
499 ELSIF p_end_dt BETWEEN v_scho_rec.start_dt AND v_scho_rec.end_dt THEN
500 p_message_name := 'IGS_EN_HECS_ENDDT_BTWN_DTRNG';
501 v_exit_loop := TRUE;
502 EXIT;
503 -- Validate the current dates do not overlap
504 -- and entire exisitng date range.
505 ELSIF p_start_dt <= v_scho_rec.start_dt AND
506 (p_end_dt IS NULL OR
507 p_end_dt >= v_scho_rec.end_dt) THEN
508 p_message_name := 'IGS_EN_HECS_DT_OVERLAP_DTRNG';
509 v_exit_loop := TRUE;
510 EXIT;
511 END IF;
512 ELSE -- c_scho.end_dt is null
513 -- Validate the new date range does not overlap an
514 -- existing start date.
515 IF p_end_dt IS NULL OR
516 v_scho_rec.start_dt <= p_end_dt THEN
517 p_message_name := 'IGS_EN_HECS_DT_OVERLAP_STDT';
518 v_exit_loop := TRUE;
519 EXIT;
520 END IF;
521 END IF;
522 END LOOP;
523 IF v_exit_loop THEN
524 RETURN FALSE;
525 END IF;
526 RETURN TRUE;
527 END;
528 EXCEPTION
529 WHEN OTHERS THEN
530 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
531 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_ovrlp');
532 IGS_GE_MSG_STACK.ADD;
533 App_Exception.Raise_Exception;
534 END enrp_val_scho_ovrlp;
535 --
536 -- Validate student course HECS option start and end date.
537 FUNCTION enrp_val_scho_st_end(
538 p_person_id IN NUMBER ,
539 p_course_cd IN VARCHAR2 ,
540 p_start_dt IN DATE ,
541 p_end_dt IN DATE ,
542 p_message_name OUT NOCOPY VARCHAR2)
543 RETURN BOOLEAN AS
544 BEGIN
545 DECLARE
549 -- this module validates the start and end date for the
546 v_other_detail VARCHAR(255);
547 v_message_name varchar2(30);
548 BEGIN
550 -- nominated student course hecs option
551 p_message_name := null;
552 IF (p_end_dt IS NOT NULL) THEN
553 IF (p_end_dt < p_start_dt) THEN
554 p_message_name := 'IGS_EN_HECS_EN_DT_GE_ST_DT';
555 RETURN FALSE;
556 END IF;
557 ELSE
558 IF (IGS_EN_VAL_SCHO.enrp_val_scho_open(
559 p_person_id,
560 p_course_cd,
561 p_start_dt,
562 v_message_name) = FALSE) THEN
563 p_message_name := v_message_name;
564 RETURN FALSE;
565 END IF;
566 END IF;
567 RETURN TRUE;
568 EXCEPTION
569 WHEN OTHERS THEN
570 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
571 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_st_end');
572 IGS_GE_MSG_STACK.ADD;
573 App_Exception.Raise_Exception;
574
575 END;
576 END enrp_val_scho_st_end;
577 --
578 -- Validate student course attempt HECS payment option visa indicators.
579 FUNCTION enrp_val_scho_visa(
580 p_outside_aus_res_ind IN VARCHAR2 DEFAULT 'N',
581 p_nz_citizen_ind IN VARCHAR2 DEFAULT 'N',
582 p_nz_citizen_less2yrind IN VARCHAR2 DEFAULT 'N',
583 p_nz_citizen_not_res_ind IN VARCHAR2 DEFAULT 'N',
584 p_message_name OUT NOCOPY VARCHAR2)
585 RETURN BOOLEAN AS
586 BEGIN
587 DECLARE
588
589 BEGIN
590 -- This module validates the student course
591 -- attempt visa indicators, in which either
592 -- all must not be set or only one
593 -- may be set
594 p_message_name := 'IGS_EN_ONE_RES_IND';
595 IF (p_outside_aus_res_ind = 'Y' AND
596 p_nz_citizen_ind = 'N' AND
597 p_nz_citizen_less2yrind = 'N' AND
598 p_nz_citizen_not_res_ind = 'N') THEN
599 p_message_name := null;
600 return TRUE;
601 ELSIF(p_nz_citizen_ind = 'Y' AND
602 p_outside_aus_res_ind = 'N' AND
603 p_nz_citizen_less2yrind = 'N' AND
604 p_nz_citizen_not_res_ind = 'N') THEN
605 p_message_name := null;
606 return TRUE;
607 ELSIF (p_nz_citizen_less2yrind = 'Y' AND
608 p_nz_citizen_not_res_ind = 'N' AND
609 p_outside_aus_res_ind = 'N' AND
610 p_nz_citizen_ind = 'N') THEN
611 p_message_name := null;
612 return TRUE;
613 ELSIF (p_nz_citizen_not_res_ind = 'Y' AND
614 p_outside_aus_res_ind = 'N' AND
615 p_nz_citizen_ind = 'N' AND
616 p_nz_citizen_less2yrind = 'N') THEN
617 p_message_name := null;
618 return TRUE;
619 ELSIF (p_nz_citizen_not_res_ind = 'N' AND
620 p_outside_aus_res_ind = 'N' AND
621 p_nz_citizen_ind = 'N' AND
622 p_nz_citizen_less2yrind = 'N') THEN
623 p_message_name := null;
624 return TRUE;
625 END IF;
626 RETURN FALSE;
627 EXCEPTION
628 WHEN OTHERS THEN
629 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
630 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_visa');
631 IGS_GE_MSG_STACK.ADD;
632 App_Exception.Raise_Exception;
633 END;
634 END enrp_val_scho_visa;
635 --
636 -- Validate student course attempt HECS payment option tax file number.
637 FUNCTION enrp_val_scho_tfn(
638 p_person_id IN NUMBER ,
639 p_course_cd IN VARCHAR2 ,
640 p_start_dt IN DATE ,
641 p_tax_file_number IN NUMBER ,
642 p_message_name OUT NOCOPY VARCHAR2,
643 p_return_type OUT NOCOPY VARCHAR2 )
644 RETURN BOOLEAN AS
645 BEGIN
646 DECLARE
647 v_other_detail VARCHAR2(255);
648 v_scho_unique_rec IGS_EN_STDNTPSHECSOP%ROWTYPE;
649 v_scho_same_rec IGS_EN_STDNTPSHECSOP%ROWTYPE;
650 v_count NUMBER;
651 v_count1 NUMBER;
652 v_cnt NUMBER;
653 v_tfn_digit NUMBER;
654 v_algorithm_total NUMBER;
655 cst_factor CONSTANT NUMBER := 11;
656 CURSOR c_scho_unique_rec IS
657 SELECT *
658 FROM IGS_EN_STDNTPSHECSOP
659 WHERE person_id <> p_person_id AND
660 tax_file_number = p_tax_file_number;
661 CURSOR c_scho_same_rec IS
662 SELECT *
663 FROM IGS_EN_STDNTPSHECSOP
664 WHERE person_id = p_person_id AND
665 tax_file_number is NOT NULL AND
666 tax_file_number <> p_tax_file_number;
667 TYPE t_algorithm IS TABLE OF NUMBER NOT NULL
668 INDEX BY BINARY_INTEGER;
669 tax_file_alg_tab t_algorithm;
670 BEGIN
671 -- This module validates the student course attempt
672 -- HECS payment option tax file number.
673 -- The tax file number must be unique for a person_id
674 -- and the same for one person, of length 9 and must
675 -- be valid according to the tax file number
676 -- algorithm
677 p_message_name := null;
678 p_return_type := ' ';
682 IF (p_tax_file_number IS NULL) THEN
679 -- initialising the total
680 v_algorithm_total := 0;
681 -- this checks that the p_tax_file_number is entered
683 RETURN TRUE;
684 END IF;
685 -- this checks that the tax file number
686 -- is of the correct length (ie. 9)
687 IF (LENGTH(p_tax_file_number) <> 9) THEN
688 p_message_name := 'IGS_EN_TFN_LENGTH_BE_9';
689 p_return_type := cst_error;
690 RETURN FALSE;
691 END IF;
692 -- this validates that the tax file number is unique
693 -- across a person's id
694 v_count := 0;
695 OPEN c_scho_unique_rec;
696 LOOP
697 FETCH c_scho_unique_rec INTO v_scho_unique_rec;
698 EXIT WHEN c_scho_unique_rec%NOTFOUND;
699 v_count := v_count + 1;
700 END LOOP;
701 CLOSE c_scho_unique_rec;
702 IF (v_count > 0) THEN
703 p_message_name := 'IGS_GE_DUPLICATE_VALUE';
704 p_return_type := cst_error;
705 RETURN FALSE;
706 END IF;
707 -- setting the required values for the tax file
708 -- number algorithm
709 tax_file_alg_tab(1) := 10;
710 tax_file_alg_tab(2) := 7;
711 tax_file_alg_tab(3) := 8;
712 tax_file_alg_tab(4) := 4;
713 tax_file_alg_tab(5) := 6;
714 tax_file_alg_tab(6) := 3;
715 tax_file_alg_tab(7) := 5;
716 tax_file_alg_tab(8) := 2;
717 tax_file_alg_tab(9) := 1;
718 -- for each digit of the p_tax_file_number, extract it and
719 -- apply the tax file number algorithm to the digit
720 FOR v_cnt IN 1..9 LOOP
721 v_tfn_digit :=
722 SUBSTR(to_char(p_tax_file_number), v_cnt, 1);
723 v_algorithm_total :=
724 v_algorithm_total + (v_tfn_digit * tax_file_alg_tab(v_cnt));
725 END LOOP;
726 -- if the algorithm returns a number other than zero,
727 -- then an invalid p_tax_file_number was entered
728 IF (MOD(v_algorithm_total, cst_factor) <> 0) THEN
729 p_message_name := 'IGS_EN_TFN_HAS_FAILED';
730 p_return_type := cst_error;
731 RETURN FALSE;
732 END IF;
733 -- this validates that the tax file number is the
734 -- same for a person's id
735 v_count1 := 0;
736 OPEN c_scho_same_rec;
737 LOOP
738 FETCH c_scho_same_rec INTO v_scho_same_rec;
739 EXIT WHEN c_scho_same_rec%NOTFOUND;
740 -- Eliminate the record being updated/inserted.
741 IF (v_scho_same_rec.course_cd = p_course_cd AND
742 v_scho_same_rec.start_dt <> p_start_dt) OR
743 (v_scho_same_rec.course_cd <> p_course_cd) THEN
744 v_count1 := v_count1 + 1;
745 END IF;
746 END LOOP;
747 CLOSE c_scho_same_rec;
748 IF (v_count1 > 0) THEN
749 p_message_name := 'IGS_EN_STUD_HAS_DIFF_TFN';
750 p_return_type := cst_warn;
751 RETURN FALSE;
752 END IF;
753 RETURN TRUE;
754 EXCEPTION
755 WHEN OTHERS THEN
756 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
757 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_tfn');
758 IGS_GE_MSG_STACK.ADD;
759 App_Exception.Raise_Exception;
760 END;
761 END enrp_val_scho_tfn;
762 --
763 -- To validate the HECS Payment Option for a Student course HECS option
764 FUNCTION enrp_val_scho_hpo(
765 p_hecs_payment_option IN VARCHAR2 ,
766 p_outside_aus_res_ind IN VARCHAR2 DEFAULT 'N',
767 p_nz_citizen_ind IN VARCHAR2 DEFAULT 'N',
768 p_nz_citizen_less2yr_ind IN VARCHAR2 DEFAULT 'N',
769 p_nz_citizen_not_res_ind IN VARCHAR2 DEFAULT 'N',
770 p_safety_net_ind IN VARCHAR2 DEFAULT 'N',
771 p_tax_file_number IN NUMBER ,
772 p_tax_file_number_collected_dt IN DATE ,
773 p_tax_file_certificate_number IN NUMBER ,
774 p_differential_hecs_ind IN VARCHAR2 DEFAULT 'N',
775 p_message_name OUT NOCOPY VARCHAR2,
776 p_return_type OUT NOCOPY VARCHAR2 )
777 RETURN BOOLEAN AS
778 BEGIN --enrp_val_scho_hpo
779 -- This module validates the student course attempt
780 -- HECS payment_option and associated fields.
781 DECLARE
782 CURSOR c_ghpo_details IS
783 SELECT ghpo.s_hecs_payment_type
784 FROM IGS_FI_HECS_PAY_OPTN hpo,
785 IGS_FI_GOV_HEC_PA_OP ghpo
786 WHERE hpo.hecs_payment_option = p_hecs_payment_option AND
787 ghpo.govt_hecs_payment_option = hpo.govt_hecs_payment_option;
788 v_ghpo_rec c_ghpo_details%ROWTYPE;
789 BEGIN
790 p_message_name := null;
791 OPEN c_ghpo_details;
792 FETCH c_ghpo_details INTO v_ghpo_rec;
793 -- exit if no HECS payment option is found
794 IF c_ghpo_details%NOTFOUND THEN
795 CLOSE c_ghpo_details;
796 RETURN TRUE;
800 -- deferred
797 END IF;
798 CLOSE c_ghpo_details;
799 -- perform validation when the HECS payment option is
801 IF v_ghpo_rec.s_hecs_payment_type = cst_hecs_type_deferred THEN
802 -- validating the visa indicators, which
803 -- none must be set
804 IF p_outside_aus_res_ind = 'Y' OR
805 p_nz_citizen_ind = 'Y' OR
806 p_nz_citizen_less2yr_ind = 'Y' OR
807 p_nz_citizen_not_res_ind = 'Y' THEN
808 p_message_name := 'IGS_EN_VISA_IND_MUST_NOT_SET';
809 p_return_type := cst_error;
810 RETURN FALSE;
811 END IF;
812 -- validating the safety net indicator, which
813 -- must not be set
814 IF p_safety_net_ind = 'Y' THEN
815 p_message_name := 'IGS_EN_SAFET_IND_MUST_NOT_SET';
816 p_return_type := cst_error;
817 RETURN FALSE;
818 END IF;
819 -- validate the tax file number/tax file certificate
820 -- number, of which either must be set. If the tax
821 -- file number is set, then the tax fiel collected
822 -- date must also be set
823 IF ((p_tax_file_number IS NULL OR
824 (p_tax_file_number IS NOT NULL AND
825 p_tax_file_number_collected_dt IS NULL)) AND
826 p_tax_file_certificate_number IS NULL) THEN
827 p_message_name := 'IGS_EN_TAX_FILE_NO_BE_SET';
828 p_return_type := cst_warn;
829 RETURN FALSE;
830 END IF;
831 END IF;
832 -- perform validation when the HECS payment option is
833 -- up-front with discount
834 IF v_ghpo_rec.s_hecs_payment_type = cst_hecs_type_upfront_discount THEN
835 -- validating the visa indicators, which
836 -- none must be set
837 IF p_outside_aus_res_ind = 'Y' OR
838 p_nz_citizen_ind = 'Y' OR
839 p_nz_citizen_less2yr_ind = 'Y' OR
840 p_nz_citizen_not_res_ind = 'Y' THEN
841 p_message_name := 'IGS_EN_VISA_IND_NOT_BE_SET';
842 p_return_type := cst_error;
843 RETURN FALSE;
844 END IF;
845 -- validating the safety net indicator
846 IF p_safety_net_ind = 'Y' THEN
847 -- validate the tax file number/tax file certificate
848 -- number, of which either must be set. If the tax
849 -- file number is set, then the tax fiel collected
850 -- date must also be set
851 IF ((p_tax_file_number IS NULL OR
852 (p_tax_file_number IS NOT NULL AND
853 p_tax_file_number_collected_dt IS NULL)) AND
854 p_tax_file_certificate_number IS NULL) THEN
855 p_message_name := 'IGS_EN_CHK_TFN_RECORDED_DT';
856 p_return_type := cst_warn;
857 RETURN FALSE;
858 END IF;
859 ELSE -- p_safety_net_ind = 'N'
860 IF p_tax_file_number IS NOT NULL OR
861 p_tax_file_number_collected_dt IS NOT NULL OR
862 p_tax_file_certificate_number IS NOT NULL THEN
863 p_message_name := 'IGS_EN_CHK_HECS_PAY_OPTION';
864 p_return_type := cst_error;
865 RETURN FALSE;
866 END IF;
867 END IF;
868 END IF;
869 -- validate when HECS payment option is up-front with
870 -- no discount
871 IF v_ghpo_rec.s_hecs_payment_type = cst_hecs_type_upfront THEN
872 IF p_outside_aus_res_ind ='Y' OR
873 p_nz_citizen_ind = 'Y' OR
874 p_nz_citizen_less2yr_ind = 'Y' OR
875 p_nz_citizen_not_res_ind = 'Y' THEN
876 -- a visa indicator is set
877 -- validate the safety net indicator, which must
878 -- not be set
879 IF p_safety_net_ind = 'Y' THEN
880 p_message_name := 'IGS_EN_SAFETY_NET_IND_BE_SET';
881 p_return_type := cst_error;
882 RETURN FALSE;
883 END IF;
884 -- validate the tax file number/tax file
885 -- certificate number, which none must be
886 -- set
887 IF p_tax_file_number IS NOT NULL OR
888 p_tax_file_number_collected_dt IS NOT NULL OR
889 p_tax_file_certificate_number IS NOT NULL THEN
890 p_message_name := 'IGS_EN_CHK_TFN_TFC';
891 p_return_type := cst_error;
892 RETURN FALSE;
893 END IF;
894 END IF;
895 END IF;
896 -- validate when HECS payment option is exempt
897 IF v_ghpo_rec.s_hecs_payment_type = cst_hecs_type_exempt THEN
898 -- validate the visa indicators, safety net indicator
899 -- and tax file number/tax file certificate number,
900 -- which none must be set
901 IF p_outside_aus_res_ind = 'Y' OR
902 p_nz_citizen_ind = 'Y' OR
903 p_nz_citizen_less2yr_ind = 'Y' OR
904 p_nz_citizen_not_res_ind = 'Y' OR
905 p_safety_net_ind = 'Y' OR
906 p_tax_file_number IS NOT NULL OR
907 p_tax_file_number_collected_dt IS NOT NULL OR
908 p_tax_file_certificate_number IS NOT NULL OR
909 p_differential_hecs_ind = 'Y' THEN
910 p_message_name := 'IGS_EN_CHK_DIFF_INDICATORS';
911 p_return_type := cst_error;
912 RETURN FALSE;
913 END IF;
914 END IF;
915 RETURN TRUE;
916 END;
917 EXCEPTION
918 WHEN OTHERS THEN
919 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
920 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_hpo');
921 IGS_GE_MSG_STACK.ADD;
922 App_Exception.Raise_Exception;
923 END enrp_val_scho_hpo;
924 --
925 -- Validate the insert of a student course HECS option record.
929 p_message_name OUT NOCOPY VARCHAR2)
926 FUNCTION enrp_val_scho_insert(
927 p_person_id IN NUMBER ,
928 p_course_cd IN VARCHAR2 ,
930 RETURN BOOLEAN AS
931 BEGIN
932 DECLARE
933 v_other_detail VARCHAR2(255);
934 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
935 CURSOR c_attempt_status IS
936 SELECT course_attempt_status
937 FROM IGS_EN_STDNT_PS_ATT
938 WHERE person_id = p_person_id AND
939 course_cd = p_course_cd;
940 BEGIN
941 -- This module validates the insertion of a
942 -- IGS_EN_STDNT_PS_HECS_OPTION_V record, in which
943 -- it can't be inserted if it has a status of
944 -- completed or deleted.
945 p_message_name := null;
946 OPEN c_attempt_status;
947 FETCH c_attempt_status INTO v_course_attempt_status;
948 -- exit successfully if a record isn't found
949 -- therefore, no validation was required
950 IF (c_attempt_status%NOTFOUND) THEN
951 CLOSE c_attempt_status;
952 p_message_name := null;
953 RETURN TRUE;
954 END IF;
955 CLOSE c_attempt_status;
956 -- checking if the attempt_status is set
957 -- to deleted or completed
958 IF (v_course_attempt_status = cst_deleted OR
959 v_course_attempt_status = cst_completed) THEN
960 p_message_name := 'IGS_EN_CANT_CREATE_HECS';
961 RETURN FALSE;
962 END IF;
963 RETURN TRUE;
964 EXCEPTION
965 WHEN OTHERS THEN
966 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
967 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_insert');
968 IGS_GE_MSG_STACK.ADD;
969 App_Exception.Raise_Exception;
970 END;
971 END enrp_val_scho_insert;
972 --
973 -- Validate the update of a student course HECS option record.
974 FUNCTION enrp_val_scho_update(
975 p_start_dt IN DATE ,
976 p_message_name OUT NOCOPY VARCHAR2)
977 RETURN BOOLEAN AS
978 BEGIN
979 DECLARE
980
981 v_diff NUMBER;
982 BEGIN
983 -- This module validates whether a IGS_EN_STDNT_PS_HECS_OPTION_V
984 -- record may be updated. It may only be updated if
985 -- the IGS_EN_STDNTPSHECSOP.start_dt is equal or greater
986 -- than the current date.
987 v_diff := MONTHS_BETWEEN(SYSDATE, p_start_dt);
988 -- start_dt is less than the current date
989 IF (v_diff > 0) THEN
990 p_message_name := 'IGS_EN_CANT_UPD_HECS_PAY_OPT';
991 RETURN FALSE;
992 END IF;
993 -- start_dt is greater than or equal to the
994 -- current date
995 p_message_name := null;
996 RETURN TRUE;
997 EXCEPTION
998 WHEN OTHERS THEN
999 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1000 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_update');
1001 IGS_GE_MSG_STACK.ADD;
1002 App_Exception.Raise_Exception;
1003 END;
1004 END enrp_val_scho_update;
1005 --
1006 -- Validate the delete of a student course HECS option record.
1007 FUNCTION enrp_val_scho_delete(
1008 p_start_dt IN DATE ,
1009 p_message_name OUT NOCOPY VARCHAR2)
1010 RETURN BOOLEAN AS
1011 BEGIN
1012 DECLARE
1013
1014 v_diff NUMBER;
1015 BEGIN
1016 -- This module validates whether a IGS_EN_STDNT_PS_HECS_OPTION_V
1017 -- record may be deleted. It may only be deleted if
1018 -- the IGS_EN_STDNTPSHECSOP.start_dt is equal or greater
1019 -- than the current date.
1020 v_diff := MONTHS_BETWEEN(SYSDATE, p_start_dt);
1021 -- start_dt is less than the current date
1022 IF (v_diff > 0) THEN
1023 p_message_name := 'IGS_EN_CANT_DEL_HECS_PAY_OPT';
1024 RETURN FALSE;
1025 END IF;
1026 -- start_dt is greater than or equal to the
1027 -- current date
1028 p_message_name := null;
1029 RETURN TRUE;
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1033 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_delete');
1034 IGS_GE_MSG_STACK.ADD;
1035 App_Exception.Raise_Exception;
1036 END;
1037 END enrp_val_scho_delete;
1038 --
1039 -- Validate HECS option, citizenship code and permanent resident.
1040 FUNCTION enrp_val_ho_cic_prc(
1041 p_person_id IN NUMBER ,
1042 p_course_cd IN VARCHAR2 ,
1043 p_scho_start_dt IN DATE ,
1044 p_scho_end_dt IN DATE ,
1045 p_hecs_payment_option IN VARCHAR2 ,
1046 p_outside_aus_res_ind IN VARCHAR2 DEFAULT 'N',
1047 p_ps_start_dt IN DATE ,
1048 p_ps_end_dt IN DATE ,
1049 p_citizenship_cd IN VARCHAR2 ,
1050 p_perm_resident_cd IN VARCHAR2 ,
1051 p_message_name OUT NOCOPY VARCHAR2,
1052 p_return_type OUT NOCOPY VARCHAR2 )
1053 RETURN BOOLEAN AS
1054 BEGIN
1055 DECLARE
1056 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1057 v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
1058 v_govt_perm_resident_cd
1059 IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE DEFAULT NULL;
1060 v_outside_aus_res_ind IGS_EN_STDNT_PS_HECS_OPTION_V.outside_aus_res_ind%TYPE;
1061 v_exit BOOLEAN DEFAULT FALSE;
1062 CURSOR c_ghpo IS
1063 SELECT govt_hecs_payment_option
1064 FROM IGS_FI_HECS_PAY_OPTN
1065 WHERE hecs_payment_option = p_hecs_payment_option AND
1066 govt_hecs_payment_option IS NOT NULL;
1070 CIT.govt_citizenship_cd,
1067 CURSOR c_gcitiz IS
1068 SELECT PS.start_dt,
1069 PS.end_dt,
1071 PRCD.govt_perm_resident_cd
1072 FROM IGS_PE_STATISTICS PS,
1073 IGS_ST_CITIZENSHP_CD CIT,
1074 IGS_PE_PERM_RES_CD PRCD
1075 WHERE PS.person_id = p_person_id AND
1076 PS.citizenship_cd = CIT.citizenship_cd AND
1077 PS.perm_resident_cd = PRCD.perm_resident_cd (+)
1078 ORDER BY PS.start_dt,
1079 PS.end_dt;
1080 CURSOR c_gccd IS
1081 SELECT govt_citizenship_cd
1082 FROM IGS_ST_CITIZENSHP_CD
1083 WHERE citizenship_cd = p_citizenship_cd;
1084 CURSOR c_gprcd IS
1085 SELECT govt_perm_resident_cd
1086 FROM IGS_PE_PERM_RES_CD
1087 WHERE perm_resident_cd = p_perm_resident_cd;
1088 CURSOR c_hpo IS
1089 SELECT SCHOV.start_dt,
1090 SCHOV.end_dt,
1091 SCHOV.outside_aus_res_ind,
1092 HPO.govt_hecs_payment_option
1093 FROM IGS_EN_STDNT_PS_HECS_OPTION_V SCHOV,
1094 IGS_FI_HECS_PAY_OPTN HPO
1095 WHERE SCHOV.person_id = p_person_id AND
1096 SCHOV.hecs_payment_option =
1097 HPO.hecs_payment_option AND
1098 HPO.govt_hecs_payment_option IS NOT NULL
1099 ORDER BY SCHOV.start_dt,
1100 SCHOV.end_dt;
1101 FUNCTION enrpl_chk_whether_to_validate (
1102 p_p_start_dt DATE,
1103 p_p_end_dt DATE,
1104 p_db_start_dt DATE,
1105 p_db_end_dt DATE)
1106 RETURN BOOLEAN AS
1107 BEGIN
1108 DECLARE
1109
1110 BEGIN
1111 -- this module checks whether further validation
1112 -- should be performed, depending on the values
1113 -- of certain system and parameter dates
1114 -- determining if a record should be validated
1115 -- which will occur when the DB effective date(s)
1116 -- overlap or match the parameter date(s)
1117 IF (p_p_end_dt IS NULL OR
1118 p_p_end_dt >= p_db_start_dt) AND
1119 (p_db_end_dt IS NULL OR
1120 p_p_start_dt <= p_db_end_dt) THEN
1121 RETURN TRUE;
1122 END IF;
1123 RETURN FALSE;
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1127 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_chk_whether_to_validate');
1128 IGS_GE_MSG_STACK.ADD;
1129 App_Exception.Raise_Exception;
1130 END;
1131 END enrpl_chk_whether_to_validate;
1132 FUNCTION enrpl_do_validations (
1133 p_outside_aus_res_ind IGS_EN_STDNT_PS_HECS_OPTION_V.outside_aus_res_ind%TYPE,
1134 p_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
1135 p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
1136 p_govt_perm_resident_cd IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE)
1137 RETURN BOOLEAN AS
1138 BEGIN
1139 DECLARE
1140 v_other_detail VARCHAR2(255);
1141 BEGIN
1142 -- this module performs the validation that is
1143 -- required, in that if the IGS_FI_GOV_HEC_PA_OP = 12
1144 -- then the govt. value for the IGS_ST_CITIZENSHP_CD must be
1145 -- 3 and the govt. value for the IGS_PE_PERM_RES_CD must
1146 -- be 2
1147 -- checking whether the IGS_FI_GOV_HEC_PA_OP = 12
1148 IF p_govt_hecs_payment_option = cst_hecs_upfront AND
1149 -- checking if the indicator is set
1150 p_outside_aus_res_ind = 'Y' THEN
1151 -- checking if the IGS_PE_GOVCITIZEN_CD = '3'
1152 IF p_govt_citizenship_cd = cst_citizen_perm THEN
1153 -- checking if the perm_resdient_cd = 2
1154 IF NVL(p_govt_perm_resident_cd, 9) <> cst_perm_out_aust_not_crs THEN
1155 RETURN FALSE;
1156 END IF;
1157 ELSE
1158 RETURN FALSE;
1159 END IF;
1160 END IF;
1161 RETURN TRUE;
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1165 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_do_validations');
1166 IGS_GE_MSG_STACK.ADD;
1167 App_Exception.Raise_Exception;
1168 END;
1169 END enrpl_do_validations;
1170 BEGIN
1171 -- This is the main module which validates the student_course_hecs_
1172 -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
1173 -- IGS_ST_CITIZENSHP_CD and the IGS_PE_STATISTICS.IGS_PE_PERM_RES_CD.
1174 p_message_name := null;
1175 -- validating the input parameters
1176 IF p_person_id IS NULL THEN
1177 RETURN TRUE;
1178 END IF;
1179 -- validating the input parameters
1180 IF p_course_cd IS NOT NULL THEN
1181 IF p_scho_start_dt IS NULL THEN
1182 RETURN TRUE;
1183 END IF;
1184 -- retrieving the govt. value for the student_course_
1185 -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
1186 OPEN c_ghpo;
1187 FETCH c_ghpo INTO v_govt_hecs_payment_option;
1188 -- exit successfully if a record isn't found
1189 -- therefore, no validation is required
1190 IF c_ghpo%NOTFOUND THEN
1191 CLOSE c_ghpo;
1192 RETURN TRUE;
1193 END IF;
1194 CLOSE c_ghpo;
1195 -- set the below variable to the input parameter
1196 v_outside_aus_res_ind := p_outside_aus_res_ind;
1197 -- retrieving the govt. value for the IGS_PE_STATISTICS.
1198 -- IGS_ST_CITIZENSHP_CD and the IGS_PE_STATISTICS.IGS_PE_PERM_RES_CD,
1199 -- in which more than one may be found
1200 FOR v_citz_record IN c_gcitiz LOOP
1204 IF enrpl_chk_whether_to_validate (
1201 -- execute the rountine which checks whether
1202 -- further validation is required.
1203 -- if it returns true, perform the validation
1205 p_scho_start_dt,
1206 p_scho_end_dt,
1207 v_citz_record.start_dt,
1208 v_citz_record.end_dt) THEN
1209 -- execute the validation routine.
1210 -- if it returns false, set the message number
1211 -- and the return type
1212 IF NOT enrpl_do_validations (
1213 v_outside_aus_res_ind,
1214 v_govt_hecs_payment_option,
1215 v_citz_record.govt_citizenship_cd,
1216 v_citz_record.govt_perm_resident_cd) THEN
1217 v_exit := TRUE;
1218 EXIT;
1219 END IF;
1220 END IF;
1221 END LOOP;
1222 ELSE -- p_course_cd IS NULL
1223 -- checking the input parameters
1224 IF p_ps_start_dt IS NULL THEN
1225 RETURN TRUE;
1226 END IF;
1227 -- retrieving the govt. value for the IGS_PE_STATISTICS.
1228 -- IGS_ST_CITIZENSHP_CD
1229 OPEN c_gccd;
1230 FETCH c_gccd INTO v_govt_citizenship_cd;
1231 -- exit successfully if a record isn't found
1232 -- therefore, no validation is required
1233 IF c_gccd%NOTFOUND THEN
1234 CLOSE c_gccd;
1235 RETURN TRUE;
1236 END IF;
1237 CLOSE c_gccd;
1238 -- retrieving the govt. value for the IGS_PE_STATISTICS.
1239 -- IGS_PE_PERM_RES_CD
1240 OPEN c_gprcd;
1241 FETCH c_gprcd INTO v_govt_perm_resident_cd;
1242 CLOSE c_gprcd;
1243 -- retrieving the visa indicators for the student_course_
1244 -- hecs_option_v and the govt. value for the
1245 -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
1246 -- in which more than one record may be found
1247 FOR v_scho_record IN c_hpo LOOP
1248 -- execute the rountine which checks whether
1249 -- furhter validation is required.
1250 -- if it returns true, perform the validation
1251 IF enrpl_chk_whether_to_validate (
1252 p_ps_start_dt,
1253 p_ps_end_dt,
1254 v_scho_record.start_dt,
1255 v_scho_record.end_dt) THEN
1256 -- execute the validation routine.
1257 -- if it returns false, set the message number
1258 -- and the return type
1259 IF NOT enrpl_do_validations (
1260 v_scho_record.outside_aus_res_ind,
1261 v_scho_record.govt_hecs_payment_option,
1262 v_govt_citizenship_cd,
1263 v_govt_perm_resident_cd) THEN
1264 v_exit := TRUE;
1265 EXIT;
1266 END IF;
1267 END IF;
1268 END LOOP;
1269 END IF;
1270 IF v_exit THEN
1271 p_message_name := 'IGS_GE_INVALID_VALUE';
1272 p_return_type := cst_error;
1273 RETURN FALSE;
1274 END IF;
1275 RETURN TRUE;
1276 END;
1277 EXCEPTION
1278 WHEN OTHERS THEN
1279 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1280 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_ho_cic_prc');
1281 IGS_GE_MSG_STACK.ADD;
1282 App_Exception.Raise_Exception;
1283 END enrp_val_ho_cic_prc;
1284 --
1285 -- Validate HECS visa indicators, citizenship cd and permanent resident.
1286 FUNCTION enrp_val_vis_cic_prc(
1287 p_person_id IN NUMBER ,
1288 p_course_cd IN VARCHAR2 ,
1289 p_scho_start_dt IN DATE ,
1290 p_scho_end_dt IN DATE ,
1291 p_outside_aus_res_ind IN VARCHAR2 DEFAULT 'N',
1292 p_nz_citizen_ind IN VARCHAR2 DEFAULT 'N',
1293 p_nz_citizen_less2yr_ind IN VARCHAR2 DEFAULT 'N',
1294 p_nz_citizen_not_res_ind IN VARCHAR2 DEFAULT 'N',
1295 p_ps_start_dt IN DATE ,
1296 p_ps_end_dt IN DATE ,
1300 p_return_type OUT NOCOPY VARCHAR2 )
1297 p_citizenship_cd IN VARCHAR2 ,
1298 p_perm_resident_cd IN VARCHAR2 ,
1299 p_message_name OUT NOCOPY VARCHAR2,
1301 RETURN BOOLEAN AS
1302 BEGIN
1303 DECLARE
1304 v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE
1305 DEFAULT NULL;
1306 v_govt_perm_resident_cd IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE
1307 DEFAULT NULL;
1308 v_return_false BOOLEAN DEFAULT FALSE;
1309 CURSOR c_prc IS
1310 SELECT prc.govt_perm_resident_cd
1311 FROM IGS_PE_PERM_RES_CD prc
1312 WHERE prc.perm_resident_cd = p_perm_resident_cd;
1313 CURSOR c_ps IS
1314 SELECT ps.start_dt,
1315 ps.end_dt,
1316 cic.govt_citizenship_cd,
1317 prcd.govt_perm_resident_cd
1318 FROM IGS_PE_STATISTICS ps,
1319 IGS_ST_CITIZENSHP_CD cic,
1320 IGS_PE_PERM_RES_CD prcd
1321 WHERE ps.person_id = p_person_id AND
1322 ps.citizenship_cd = cic.citizenship_cd AND
1323 ps.perm_resident_cd = prcd.perm_resident_cd (+)
1324 ORDER BY ps.start_dt,
1325 ps.end_dt;
1326 CURSOR c_cic IS
1327 SELECT cic.govt_citizenship_cd
1328 FROM IGS_ST_CITIZENSHP_CD cic
1329 WHERE cic.citizenship_cd = p_citizenship_cd;
1330 CURSOR c_schov IS
1331 SELECT schov.start_dt,
1332 schov.end_dt,
1333 schov.outside_aus_res_ind,
1334 schov.nz_citizen_ind,
1335 schov.nz_citizen_less2yr_ind,
1336 schov.nz_citizen_not_res_ind
1337 FROM IGS_EN_STDNT_PS_HECS_OPTION_V schov
1338 WHERE schov.person_id = p_person_id
1339 ORDER BY
1340 schov.start_dt,
1341 schov.end_dt;
1342 FUNCTION enrpl_chk_whether_to_validate (
1343 p_p_start_dt DATE,
1344 p_p_end_dt DATE,
1345 p_db_start_dt DATE,
1346 p_db_end_dt DATE)
1347 RETURN BOOLEAN AS
1348 BEGIN
1349 DECLARE
1350
1351 BEGIN
1352 -- determining if a record should be validated
1353 -- which will occur when the DB effective date(s)
1354 -- overlap or match the parameter date(s)
1355 IF (p_p_end_dt IS NULL OR
1356 p_p_end_dt >= p_db_start_dt) AND
1357 (p_db_end_dt IS NULL OR
1358 p_p_start_dt <= p_db_end_dt) THEN
1359 RETURN TRUE;
1360 END IF;
1361 -- none of the conditions were true
1362 RETURN FALSE;
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1366 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_chk_whether_to_validate');
1367 IGS_GE_MSG_STACK.ADD;
1368 App_Exception.Raise_Exception;
1369 END;
1370 END enrpl_chk_whether_to_validate;
1371 FUNCTION enrpl_do_validations (
1372 p_outside_aus_res_ind IGS_EN_STDNT_PS_HECS_OPTION_V.outside_aus_res_ind%TYPE,
1373 p_nz_citizen_ind IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_ind%TYPE,
1374 p_nz_citizen_less2yr_ind
1375 IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_less2yr_ind%TYPE,
1376 p_nz_citizen_not_res_ind
1377 IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_not_res_ind%TYPE,
1378 p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
1379 p_govt_perm_resident_cd IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE)
1380 RETURN BOOLEAN AS
1381 BEGIN
1382 DECLARE
1383 v_other_detail VARCHAR2(255);
1384 BEGIN
1385 -- checking that if the govt. value for the IGS_ST_CITIZENSHP_CD
1386 -- is 1, 4, or 5, or it is 3 and the govt. value for the
1387 -- IGS_PE_PERM_RES_CD is 1 or 3, then the visa indicators
1388 -- must not be set
1389 -- checking the IGS_PE_GOVCITIZEN_CD and IGS_PE_GOV_PER_RESCD
1390 IF p_govt_citizenship_cd IN (
1391 cst_citizen_aust,
1392 cst_citizen_temp_dip,
1393 cst_citizen_other) OR
1394 (p_govt_citizenship_cd = cst_citizen_perm AND
1395 NVL(p_govt_perm_resident_cd, 9) IN (
1396 cst_perm_in_out_aust_crs,
1397 cst_perm)) THEN
1398 -- checking the visa indicators
1399 IF p_outside_aus_res_ind = 'Y' OR
1400 p_nz_citizen_ind = 'Y' OR
1401 p_nz_citizen_less2yr_ind = 'Y' OR
1402 p_nz_citizen_not_res_ind = 'Y' THEN
1403 RETURN FALSE;
1404 END IF;
1405 END IF;
1406 -- return when one of the above conditions
1407 -- didn't hold
1408 RETURN TRUE;
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1412 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_do_validations');
1413 IGS_GE_MSG_STACK.ADD;
1414 App_Exception.Raise_Exception;
1415 END;
1416 END enrpl_do_validations;
1417 BEGIN
1418 -- This is the main module which validates the student_course_hecs_
1419 -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
1420 -- IGS_ST_CITIZENSHP_CD.
1421 p_message_name := null;
1422 -- validating the input parameters
1423 IF p_person_id IS NULL THEN
1424 RETURN TRUE;
1425 END IF;
1426 -- validating the input parameters
1427 IF p_course_cd IS NOT NULL THEN
1428 IF p_scho_start_dt IS NULL THEN
1429 RETURN TRUE;
1430 END IF;
1431 -- retrieving the govt. value for the IGS_PE_STATISTICS.
1432 -- IGS_ST_CITIZENSHP_CD, and the government value for the
1436 -- execute the rountine which checks whether
1433 -- IGS_PE_STATISTICS.IGS_PE_PERM_RES_CD, in which more
1434 -- than one may be found
1435 FOR v_ps_rec IN c_ps LOOP
1437 -- further validation is required.
1438 -- if it returns true, perform the validation
1439 IF enrpl_chk_whether_to_validate (
1440 p_scho_start_dt,
1441 p_scho_end_dt,
1442 v_ps_rec.start_dt,
1443 v_ps_rec.end_dt) THEN
1444 -- execute the validation routine.
1445 -- if it returns false, set the message number
1446 -- and the return type
1447 IF NOT enrpl_do_validations (
1448 p_outside_aus_res_ind,
1449 p_nz_citizen_ind,
1450 p_nz_citizen_less2yr_ind,
1451 p_nz_citizen_not_res_ind,
1452 v_ps_rec.govt_citizenship_cd,
1453 v_ps_rec.govt_perm_resident_cd) THEN
1454 p_message_name := 'IGS_EN_VISA_IND_BE_SET';
1455 p_return_type := cst_error;
1456 v_return_false := TRUE;
1457 EXIT;
1458 END IF;
1459 END IF;
1460 END LOOP;
1461 ELSE -- p_course_cd IS NULL
1462 IF p_ps_start_dt IS NULL THEN
1463 RETURN TRUE;
1464 END IF;
1465 -- retrieving the govt. value for the IGS_PE_STATISTICS.
1466 -- IGS_ST_CITIZENSHP_CD
1467 OPEN c_cic;
1468 FETCH c_cic INTO v_govt_citizenship_cd;
1469 -- exit successfully if a record isn't found
1470 -- therefore, no validation is required
1471 IF c_cic%NOTFOUND THEN
1472 CLOSE c_cic;
1473 RETURN TRUE;
1474 END IF;
1475 CLOSE c_cic;
1476 -- retrieving the govt. value for the IGS_PE_STATISTICS.
1477 -- IGS_PE_PERM_RES_CD
1478 OPEN c_prc;
1479 FETCH c_prc INTO v_govt_perm_resident_cd;
1480 CLOSE c_prc;
1481 -- retrieving the visa indicators for the student_course_
1482 -- hecs_option_v and the govt. value for the
1483 -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
1484 -- in which more than one record may be found
1485 FOR v_schov_rec IN c_schov LOOP
1486 -- execute the rountine which checks whether
1487 -- further validation is required.
1488 -- if it returns true, perform the validation
1489 IF enrpl_chk_whether_to_validate (
1490 p_ps_start_dt,
1491 p_ps_end_dt,
1492 v_schov_rec.start_dt,
1493 v_schov_rec.end_dt) THEN
1494 -- execute the validation routine.
1495 -- if it returns false, set the message number
1496 -- and the return type
1497 IF NOT enrpl_do_validations (
1498 v_schov_rec.outside_aus_res_ind,
1499 v_schov_rec.nz_citizen_ind,
1500 v_schov_rec.nz_citizen_less2yr_ind,
1501 v_schov_rec.nz_citizen_not_res_ind,
1502 v_govt_citizenship_cd,
1503 v_govt_perm_resident_cd) THEN
1504 p_message_name := 'IGS_EN_VISA_IND_BE_SET';
1505 p_return_type := cst_error;
1506 v_return_false := TRUE;
1507 EXIT;
1508 END IF;
1509 END IF;
1510 END LOOP;
1511 END IF;
1512 IF v_return_false THEN
1513 RETURN FALSE;
1514 END IF;
1515 RETURN TRUE;
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 IF c_prc%ISOPEN THEN
1519 CLOSE c_prc;
1520 END IF;
1521 IF c_ps%ISOPEN THEN
1522 CLOSE c_ps;
1523 END IF;
1524 IF c_cic%ISOPEN THEN
1525 CLOSE c_cic;
1526 END IF;
1527 IF c_schov%ISOPEN THEN
1528 CLOSE c_schov;
1529 END IF;
1530 RAISE;
1531 END;
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1535 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_vis_cic_prc');
1536 IGS_GE_MSG_STACK.ADD;
1537 App_Exception.Raise_Exception;
1538 END enrp_val_vis_cic_prc;
1539 --
1540 -- Validate the HECS Payment Option against the course type.
1541 FUNCTION enrp_val_hpo_crs_typ(
1542 p_person_id IN NUMBER ,
1543 p_course_cd IN VARCHAR2 ,
1544 p_hecs_payment_option IN VARCHAR2 ,
1545 p_message_name OUT NOCOPY VARCHAR2,
1546 p_return_type OUT NOCOPY VARCHAR2 )
1547 RETURN BOOLEAN AS
1548 BEGIN
1549 DECLARE
1550 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1551 v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
1552 CURSOR c_ghpo IS
1553 SELECT hpo.govt_hecs_payment_option
1554 FROM IGS_FI_HECS_PAY_OPTN hpo
1555 WHERE hpo.hecs_payment_option = p_hecs_payment_option;
1556 CURSOR c_gct IS
1557 SELECT cty.govt_course_type
1558 FROM IGS_EN_STDNT_PS_ATT sca,
1559 IGS_PS_VER crv,
1560 IGS_PS_TYPE cty
1561 WHERE sca.person_id = p_person_id AND
1562 sca.course_cd = p_course_cd AND
1563 crv.course_cd = sca.course_cd AND
1564 crv.version_number = sca.version_number AND
1565 crv.course_type = cty.course_type;
1566 BEGIN
1567 -- This module validates the student_course_hecs_
1568 -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PS_VER.
1569 -- IGS_PS_TYPE.
1570 -- Validations are :
1571 -- 2.If the government vaue for the
1572 -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN is 26,
1573 -- then the government value for the IGS_PS_VER.IGS_PS_TYPE for the
1577 -- the IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN then must be 22,
1574 -- IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd must be 50 (DEETYA validation 1294).
1575 -- 3.If the government value for the IGS_PS_VER.IGS_PS_TYPE for the
1576 -- IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd is 50, then the government value for
1578 -- 23, 24, 26, 27, 30, 33, 70or 99 (DEETYA validation 1655).
1579 -- set the default message number
1580 p_message_name := null;
1581 -- retrieving the govt. value for the student_course_
1582 -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
1583 OPEN c_ghpo;
1584 FETCH c_ghpo INTO v_govt_hecs_payment_option;
1585 -- exit successfully if a record isn't found
1586 -- therefore, no validation is required
1587 IF c_ghpo%NOTFOUND THEN
1588 CLOSE c_ghpo;
1589 RETURN TRUE;
1590 END IF;
1591 CLOSE c_ghpo;
1592 -- retrieving the govt. value for the IGS_PS_VER.
1593 -- IGS_PS_GOVT_SPL_TYPE
1594 OPEN c_gct;
1595 FETCH c_gct INTO v_govt_course_type;
1596 -- exit successfully if a record isn't found
1597 -- therefore, no validation is required
1598 IF c_gct%NOTFOUND THEN
1599 CLOSE c_gct;
1600 RETURN TRUE;
1601 END IF;
1602 CLOSE c_gct;
1603 -- validating non-IGS_PS_AWD course tpye
1604 -- DEETYA validation 1294
1605 IF v_govt_hecs_payment_option = cst_hecs_non_award_crs AND
1606 v_govt_course_type <> cst_crs_non_award THEN
1607 p_message_name := 'IGS_EN_CHK_GOV_VAL_26';
1608 p_return_type := cst_error;
1609 RETURN FALSE;
1610 END IF;
1611 -- DEETYA validation 1655
1612 IF v_govt_course_type = cst_crs_non_award AND
1613 v_govt_hecs_payment_option NOT IN (
1614 cst_hecs_fee_paying_os,
1615 cst_hecs_os_student_charge,
1616 cst_hecs_fee_paying_os_spnsr,
1617 cst_hecs_non_award_crs,
1618 cst_hecs_employer_funded_crs,
1619 cst_hecs_os_exchange_student,
1620 cst_hecs_work_experience,
1621 cst_hecs_non_os_spec_crs,
1622 cst_hecs_os_spec_crs,
1623 cst_hecs_avondale_special)THEN
1624 p_message_name := 'IGS_EN_CHK_GOV_VAL_50';
1625 p_return_type := cst_error;
1626 RETURN FALSE;
1627 END IF;
1628 -- DETYA validation 1729
1629 IF v_govt_course_type IN (
1630 cst_crs_cross_inst_ug,
1631 cst_crs_cross_inst_pg) AND
1632 v_govt_hecs_payment_option IN (
1633 cst_hecs_fee_paying_os_spnsr,
1634 cst_hecs_enabling_crs,
1635 cst_hecs_non_award_crs,
1636 cst_hecs_employer_funded_crs,
1637 cst_hecs_non_os_comm_ug_dis,
1638 cst_hecs_comm_industry,
1639 cst_hecs_pg_award,
1640 cst_hecs_non_os_spec_crs,
1641 cst_hecs_os_spec_crs,
1642 cst_hecs_avondale_special) THEN
1643 p_message_name := 'IGS_EN_GOVT_PRGTYPE_SPA';
1644 p_return_type := cst_error;
1645 RETURN FALSE;
1646 END IF;
1647 IF v_govt_hecs_payment_option = cst_hecs_fee_paying_pg AND
1648 v_govt_course_type NOT IN (
1649 cst_crs_higher_doctorate,
1650 cst_crs_doctorate_research,
1651 cst_crs_masters_research,
1652 cst_crs_masters_crs_work,
1653 cst_crs_postgrad,
1654 cst_crs_grad_dip_pg_dip_new,
1655 cst_crs_grad_dip_pg_dip_extend,
1656 cst_crs_graduate,
1657 cst_crs_doctorate_crs_work,
1658 cst_crs_cross_inst_pg) THEN
1659 p_message_name := 'IGS_EN_GOVT_HECS_PAYMENT';
1660 p_return_type := cst_error;
1661 RETURN FALSE;
1662 END IF;
1663 IF v_govt_hecs_payment_option = cst_hecs_non_os_fee_paying_ug AND
1664 v_govt_course_type NOT IN (
1665 cst_crs_bachelor_graduate,
1666 cst_crs_bachelor_honours,
1667 cst_crs_bachelor_pass,
1668 cst_crs_assoc_degree,
1669 cst_crs_adv_diploma,
1670 cst_crs_diploma,
1671 cst_other_award,
1672 cst_crs_cross_inst_ug) THEN
1673 p_message_name := 'IGS_EN_GOVTVAL_HECS_PYMNT';
1674 p_return_type := cst_error;
1675 RETURN FALSE;
1676 END IF;
1677 -- set the default return type
1678 RETURN TRUE;
1679 EXCEPTION
1680 WHEN OTHERS THEN
1681 IF c_ghpo%ISOPEN THEN
1682 CLOSE c_ghpo;
1683 END IF;
1684 IF c_gct%ISOPEN THEN
1685 CLOSE c_gct;
1686 END IF;
1687 RAISE;
1688 END;
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1692 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_hpo_crs_typ');
1693 IGS_GE_MSG_STACK.ADD;
1694 App_Exception.Raise_Exception;
1695 END enrp_val_hpo_crs_typ;
1696 --
1697 -- Validate the HECS Payment Option against the special course type.
1698 FUNCTION enrp_val_hpo_spc_crs(
1699 p_person_id IN NUMBER ,
1700 p_course_cd IN VARCHAR2 ,
1701 p_hecs_payment_option IN VARCHAR2 ,
1702 p_message_name OUT NOCOPY VARCHAR2,
1703 p_return_type OUT NOCOPY VARCHAR2 )
1704 RETURN BOOLEAN AS
1705 BEGIN
1706 DECLARE
1707 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1708 v_govt_special_course_type IGS_PS_VER.govt_special_course_type%TYPE;
1709 v_institution_2239 BOOLEAN DEFAULT FALSE;
1710 v_dummy VARCHAR2(1);
1711 CURSOR c_hpo IS
1715 CURSOR c_crv IS
1712 SELECT hpo.govt_hecs_payment_option
1713 FROM IGS_FI_HECS_PAY_OPTN hpo
1714 WHERE hpo.hecs_payment_option = p_hecs_payment_option;
1716 SELECT crv.govt_special_course_type
1717 FROM IGS_EN_STDNT_PS_ATT sca,
1718 IGS_PS_VER crv
1719 WHERE sca.person_id = p_person_id AND
1720 sca.course_cd = p_course_cd AND
1721 crv.course_cd = sca.course_cd AND
1722 crv.version_number = sca.version_number;
1723 CURSOR c_ins IS
1724 SELECT 'x'
1725 FROM IGS_OR_INSTITUTION ins,
1726 IGS_OR_INST_STAT ist
1727 WHERE ins.local_institution_ind = 'Y' AND
1728 ins.govt_institution_cd = 2239 AND
1729 ist.institution_status = ins.institution_status AND
1730 ist.s_institution_status = 'ACTIVE';
1731 BEGIN
1732 -- This module validates the student_course_hecs_
1733 -- option_v.hecs_payment_option against the IGS_PS_VER.
1734 -- IGS_PS_GOVT_SPL_TYPE.
1735 -- Validations are :
1736 -- 1. If the government value for the
1737 -- IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option
1738 -- is 70, then the IGS_PS_VER.govt_special_course_type for the
1739 -- IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd must be 15, and vice-versa, and the
1740 -- IGS_OR_INSTITUTION code must be 2239 (Australian Maritime College)
1741 -- (DEETYA validation 1670, 1671).
1742 -- set the default message number
1743 p_message_name := null;
1744 -- retrieving the govt. value for the student_course_
1745 -- hecs_option_v.hecs_payment_option
1746 OPEN c_hpo;
1747 FETCH c_hpo INTO v_govt_hecs_payment_option;
1748 -- exit successfully if a record isn't found
1749 -- therefore, no validation is required
1750 IF c_hpo%NOTFOUND THEN
1751 CLOSE c_hpo;
1752 RETURN TRUE;
1753 END IF;
1754 CLOSE c_hpo;
1755 -- retrieving the govt. value for the IGS_PS_VER.
1756 -- IGS_PS_GOVT_SPL_TYPE
1757 OPEN c_crv;
1758 FETCH c_crv INTO v_govt_special_course_type;
1759 -- exit successfully if a record isn't found
1760 -- therefore, no validation is required
1761 IF c_crv%NOTFOUND THEN
1762 CLOSE c_crv;
1763 RETURN TRUE;
1764 END IF;
1765 CLOSE c_crv;
1766 -- retrieving the government INSTITUTION code
1767 OPEN c_ins;
1768 FETCH c_ins INTO v_dummy;
1769 IF c_ins%FOUND THEN
1770 v_institution_2239 := TRUE;
1771 END IF;
1772 CLOSE c_ins;
1773 -- checking that if the student_course_hecs
1774 -- option_v.hecs_payment_option is set to 70,
1775 -- then the course_version_govt_special_course
1776 -- type for the IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd
1777 -- must be 15, and vice-versa.
1778 IF (v_govt_hecs_payment_option IN (
1779 cst_hecs_non_os_spec_crs,
1780 cst_hecs_os_spec_crs) AND
1781 (v_govt_special_course_type <> cst_spcl_crs_amc OR
1782 NOT v_institution_2239 )) OR
1783 (v_govt_special_course_type = cst_spcl_crs_amc AND
1784 (v_govt_hecs_payment_option NOT IN (
1785 cst_hecs_non_os_spec_crs,
1786 cst_hecs_os_spec_crs) OR
1787 NOT v_institution_2239)) THEN
1788 p_message_name := 'IGS_EN_CHK_GOV_VAL_71_72';
1789 p_return_type := cst_error;
1790 RETURN FALSE;
1791 END IF;
1792 -- set the default return type
1793 RETURN TRUE;
1794 END;
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1798 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_hpo_spc_crs');
1799 IGS_GE_MSG_STACK.ADD;
1800 App_Exception.Raise_Exception;
1801 END enrp_val_hpo_spc_crs;
1802 --
1803 -- Validate HECS payment option, visa indicators and citizenship code.
1804 FUNCTION enrp_val_hpo_vis_cic(
1805 p_person_id IN NUMBER ,
1806 p_course_cd IN VARCHAR2 ,
1807 p_scho_start_dt IN DATE ,
1808 p_scho_end_dt IN DATE ,
1809 p_hecs_payment_option IN VARCHAR2 ,
1810 p_outside_aus_res_ind IN VARCHAR2 ,
1811 p_nz_citizen_ind IN VARCHAR2 DEFAULT 'N',
1812 p_nz_citizen_less2yr_ind IN VARCHAR2 DEFAULT 'N',
1813 p_nz_citizen_not_res_ind IN VARCHAR2 DEFAULT 'N',
1814 p_ps_start_dt IN DATE ,
1815 p_ps_end_dt IN DATE ,
1816 p_yr_arrival IN VARCHAR2 ,
1817 p_citizenship_cd IN VARCHAR2 ,
1818 p_message_name OUT NOCOPY VARCHAR2,
1819 p_return_type OUT NOCOPY VARCHAR2 )
1820 RETURN BOOLEAN AS
1821 BEGIN
1822 DECLARE
1823 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1824 v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
1825 v_return_false BOOLEAN DEFAULT FALSE;
1826 CURSOR c_hpo IS
1827 SELECT hpo.govt_hecs_payment_option
1828 FROM IGS_FI_HECS_PAY_OPTN hpo
1829 WHERE hpo.hecs_payment_option = p_hecs_payment_option AND
1830 hpo.govt_hecs_payment_option IS NOT NULL;
1831 CURSOR c_ps_cic IS
1832 SELECT ps.start_dt,
1833 ps.end_dt,
1834 cic.govt_citizenship_cd,
1835 ps.yr_arrival
1836 FROM IGS_PE_STATISTICS ps,
1837 IGS_ST_CITIZENSHP_CD cic
1838 WHERE ps.person_id = p_person_id AND
1839 ps.citizenship_cd = cic.citizenship_cd(+)
1840 ORDER BY ps.start_dt,
1841 ps.end_dt;
1842 CURSOR c_gcc IS
1843 SELECT gcc.govt_citizenship_cd
1844 FROM IGS_ST_CITIZENSHP_CD gcc
1848 scho.end_dt,
1845 WHERE gcc.citizenship_cd = p_citizenship_cd;
1846 CURSOR c_scho_hpo IS
1847 SELECT scho.start_dt,
1849 scho.outside_aus_res_ind,
1850 scho.nz_citizen_ind,
1851 scho.nz_citizen_less2yr_ind,
1852 scho.nz_citizen_not_res_ind,
1853 hpo.govt_hecs_payment_option
1854 FROM IGS_EN_STDNT_PS_HECS_OPTION_V scho,
1855 IGS_FI_HECS_PAY_OPTN hpo
1856 WHERE scho.person_id = p_person_id AND
1857 scho.hecs_payment_option = hpo.hecs_payment_option AND
1858 hpo.govt_hecs_payment_option IS NOT NULL
1859 ORDER BY scho.start_dt,
1860 scho.end_dt;
1861 FUNCTION enrpl_chk_whether_to_validate (
1862 p_p_start_dt DATE,
1863 p_p_end_dt DATE,
1864 p_db_start_dt DATE,
1865 p_db_end_dt DATE)
1866 RETURN BOOLEAN
1867 AS
1868 BEGIN
1869 DECLARE
1870 BEGIN
1871 -- this module checks whether further validation
1872 -- should be performed, depending on the values
1873 -- of certain system and parameter dates
1874 -- determining if a record should be validated
1875 -- which will occur when the DB effective date(s)
1876 -- overlap or match the parameter date(s)
1877 IF (p_p_end_dt IS NULL OR
1878 p_p_end_dt >= p_db_start_dt) AND
1879 (p_db_end_dt IS NULL OR
1880 p_p_start_dt <= p_db_end_dt) THEN
1881 RETURN TRUE;
1882 END IF;
1883 -- none of the conditions were true
1884 RETURN FALSE;
1885 END;
1886 EXCEPTION
1887 WHEN OTHERS THEN
1888 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1889 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_chk_whether_to_validate');
1890 IGS_GE_MSG_STACK.ADD;
1891 App_Exception.Raise_Exception;
1892 END enrpl_chk_whether_to_validate;
1893 FUNCTION enrpl_do_validations (
1894 p_outside_aus_res_ind IGS_EN_STDNT_PS_HECS_OPTION_V.outside_aus_res_ind%TYPE,
1895 p_nz_citizen_ind IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_ind%TYPE,
1896 p_nz_citizen_less2yr_ind
1897 IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_less2yr_ind%TYPE,
1898 p_nz_citizen_not_res_ind
1899 IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_not_res_ind%TYPE,
1900 p_govt_hecs_payment_option
1901 IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
1902 p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
1903 p_yr_arrival IGS_PE_STATISTICS.yr_arrival%TYPE)
1904 RETURN BOOLEAN
1905 AS
1906 BEGIN
1907 DECLARE
1908 v_test_year NUMBER(6);
1909 BEGIN
1910 -- this module performs the validation that is
1911 -- required, in that if the IGS_FI_GOV_HEC_PA_OP = 12
1912 -- and if a New Zealand indicator is set, then the
1913 -- government value must be '2'
1914 -- checking whether the IGS_FI_GOV_HEC_PA_OP = 12
1915 IF p_govt_hecs_payment_option = cst_hecs_upfront AND
1916 -- checking if any New Zealand indicators are set
1917 (p_nz_citizen_ind = 'Y' OR
1918 p_nz_citizen_less2yr_ind = 'Y' OR
1919 p_nz_citizen_not_res_ind = 'Y') AND
1920 -- checking if the IGS_PE_GOVCITIZEN_CD = '2'
1921 p_govt_citizenship_cd <> cst_citizen_nz THEN
1922 p_message_name := 'IGS_EN_CTZ/RES_STAT_VAL_BE_2';
1923 p_return_type := cst_error;
1924 RETURN FALSE;
1925 END IF;
1926 IF p_govt_hecs_payment_option = cst_hecs_upfront AND-- cst_hecs_upfront = 12
1927 p_outside_aus_res_ind = 'N' AND
1928 p_nz_citizen_ind = 'N' AND
1929 p_nz_citizen_less2yr_ind = 'N' AND
1930 p_nz_citizen_not_res_ind = 'N' THEN
1931 IF p_govt_citizenship_cd IS NULL THEN
1932 p_message_name := 'IGS_EN_STUD_PAY_UPFRONT_HECS';
1933 p_return_type := cst_warn;
1934 RETURN FALSE;
1935 ELSIF p_govt_citizenship_cd <> 3 THEN
1936 p_message_name := 'IGS_EN_STUD_PAY_UPFRONT_HECS';
1937 p_return_type := cst_error;
1938 RETURN FALSE;
1939 ELSE
1940 IF p_yr_arrival IS NOT NULL AND
1941 p_yr_arrival NOT IN (
1942 cst_off_shore, -- '00'
1943 cst_no_arrival, -- '01'
1944 cst_arrival_prior_1903, -- '02'
1945 cst_arrival_no_info, -- 'A8'
1946 cst_no_info_aust) THEN -- 'A9'
1947 IF TO_NUMBER(substr(IGS_GE_DATE.IGSCHAR(SYSDATE),1,4)) -
1948 TO_NUMBER(p_yr_arrival) < 3 THEN
1949 p_message_name := 'IGS_EN_STUD_GOVT_VAL3';
1950 p_return_type := cst_warn;
1951 RETURN FALSE;
1952 END IF;
1953 IF TO_NUMBER(p_yr_arrival) < 1996 THEN
1954 p_message_name := 'IGS_EN_STUD_GOVT_VALUE';
1955 p_return_type := cst_warn;
1956 RETURN FALSE;
1957 END IF;
1958 END IF;
1959 END IF;
1960 END IF;
1961 RETURN TRUE;
1962 END;
1963 EXCEPTION
1964 WHEN OTHERS THEN
1965 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1966 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_do_validations');
1967 IGS_GE_MSG_STACK.ADD;
1968 App_Exception.Raise_Exception;
1969 END enrpl_do_validations;
1970 BEGIN
1971 -- This is the main module which validates the student_course_hecs_
1972 -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
1973 -- IGS_ST_CITIZENSHP_CD.
1974 p_message_name := null;
1978 END IF;
1975 -- validating the input parameters
1976 IF p_person_id IS NULL THEN
1977 RETURN TRUE;
1979 -- validating the input parameters
1980 IF p_course_cd IS NOT NULL THEN
1981 IF p_scho_start_dt IS NULL THEN
1982 RETURN TRUE;
1983 END IF;
1984 -- retrieving the govt. value for the student_course_
1985 -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
1986 OPEN c_hpo;
1987 FETCH c_hpo INTO v_govt_hecs_payment_option;
1988 -- exit successfully if a record isn't found
1989 -- therefore, no validation is required
1990 IF c_hpo%NOTFOUND THEN
1991 CLOSE c_hpo;
1992 RETURN TRUE;
1993 END IF;
1994 CLOSE c_hpo;
1995 -- retrieving the govt. value for the IGS_PE_STATISTICS.
1996 -- IGS_ST_CITIZENSHP_CD, in which more than one may be found
1997 FOR v_ps_cic_rec IN c_ps_cic LOOP
1998 -- execute the rountine which checks whether
1999 -- further validation is required
2000 -- if it returns true, perform the validation
2001 IF enrpl_chk_whether_to_validate (
2002 p_scho_start_dt,
2003 p_scho_end_dt,
2004 v_ps_cic_rec.start_dt,
2005 v_ps_cic_rec.end_dt) THEN
2006 -- execute the validation routine
2007 -- if it returns false, set the message number
2008 -- and the return type
2009 IF NOT enrpl_do_validations (
2010 p_outside_aus_res_ind,
2011 p_nz_citizen_ind,
2012 p_nz_citizen_less2yr_ind,
2013 p_nz_citizen_not_res_ind,
2014 v_govt_hecs_payment_option,
2015 v_ps_cic_rec.govt_citizenship_cd,
2016 v_ps_cic_rec.yr_arrival) THEN
2017 v_return_false := TRUE;
2018 EXIT;
2019 END IF;
2020 END IF;
2021 END LOOP;
2022 ELSE -- p_course_cd IS NULL
2023 -- checking the input parameters
2024 IF p_ps_start_dt IS NULL THEN
2025 RETURN TRUE;
2026 END IF;
2027 -- retrieving the govt. value for the IGS_PE_STATISTICS.
2028 -- IGS_ST_CITIZENSHP_CD
2029 IF p_citizenship_cd IS NOT NULL THEN
2030 OPEN c_gcc;
2031 FETCH c_gcc INTO v_govt_citizenship_cd;
2032 -- exit successfully if a record isn't found
2033 -- therefore, no validation is required
2034 IF c_gcc%NOTFOUND THEN
2035 CLOSE c_gcc;
2036 RETURN TRUE;
2037 END IF;
2038 CLOSE c_gcc;
2039 ELSE
2040 v_govt_citizenship_cd := NULL;
2041 END IF;
2042 -- retrieving the visa indicators for the student_course_
2043 -- hecs_option_v and the govt. value for the
2044 -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
2045 -- in which more than one record may be found
2046 FOR v_scho_rec IN c_scho_hpo LOOP
2047 -- execute the rountine which checks whether
2048 -- furhter validation is required
2049 -- if it returns true, perform the validation
2050 IF enrpl_chk_whether_to_validate (
2051 p_ps_start_dt,
2052 p_ps_end_dt,
2053 v_scho_rec.start_dt,
2054 v_scho_rec.end_dt) THEN
2055 -- execute the validation routine
2056 -- if it returns false, set the message number
2057 -- and the return type
2058 IF NOT enrpl_do_validations (
2059 v_scho_rec.outside_aus_res_ind,
2060 v_scho_rec.nz_citizen_ind,
2061 v_scho_rec.nz_citizen_less2yr_ind,
2062 v_scho_rec.nz_citizen_not_res_ind,
2063 v_scho_rec.govt_hecs_payment_option,
2064 v_govt_citizenship_cd,
2065 p_yr_arrival) THEN
2066 v_return_false := TRUE;
2067 EXIT;
2068 END IF;
2069 END IF;
2070 END LOOP;
2071 END IF;
2072 IF v_return_false THEN
2073 RETURN FALSE;
2074 END IF;
2075 RETURN TRUE;
2076 EXCEPTION
2077 WHEN OTHERS THEN
2078 IF c_hpo%ISOPEN THEN
2079 CLOSE c_hpo;
2080 END IF;
2081 IF c_ps_cic%ISOPEN THEN
2082 CLOSE c_ps_cic;
2083 END IF;
2084 IF c_gcc%ISOPEN THEN
2085 CLOSE c_gcc;
2086 END IF;
2087 IF c_scho_hpo%ISOPEN THEN
2088 CLOSE c_scho_hpo;
2089 END IF;
2090 RAISE;
2091 END;
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2095 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_hpo_vis_cic');
2096 IGS_GE_MSG_STACK.ADD;
2097 App_Exception.Raise_Exception;
2098 END enrp_val_hpo_vis_cic;
2099 --
2100 -- Validate HECS payment option, citizenship code and permanent resident.
2101 FUNCTION enrp_val_hpo_cic_prc(
2102 p_person_id IN NUMBER ,
2103 p_course_cd IN VARCHAR2 ,
2104 p_scho_start_dt IN DATE ,
2105 p_scho_end_dt IN DATE ,
2106 p_hecs_payment_option IN VARCHAR2 ,
2107 p_ps_start_dt IN DATE ,
2108 p_ps_end_dt IN DATE ,
2109 p_citizenship_cd IN VARCHAR2 ,
2110 p_perm_resident_cd IN VARCHAR2 ,
2111 p_message_name OUT NOCOPY VARCHAR2,
2112 p_return_type OUT NOCOPY VARCHAR2 )
2113 RETURN BOOLEAN AS
2114 BEGIN
2115 DECLARE
2116 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2117 v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2118 v_govt_perm_resident_cd
2122 v_scho_rec_found BOOLEAN DEFAULT FALSE;
2119 IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE DEFAULT NULL;
2120 v_rec_found BOOLEAN DEFAULT FALSE;
2121 v_no_validation_req BOOLEAN DEFAULT FALSE;
2123 v_false_val BOOLEAN DEFAULT FALSE;
2124 CURSOR c_ghpo IS
2125 SELECT govt_hecs_payment_option
2126 FROM IGS_FI_HECS_PAY_OPTN
2127 WHERE hecs_payment_option = p_hecs_payment_option AND
2128 govt_hecs_payment_option IS NOT NULL;
2129 CURSOR c_gcitiz_rec IS
2130 SELECT PS.start_dt,
2131 PS.end_dt,
2132 CIT.govt_citizenship_cd,
2133 PRCD.govt_perm_resident_cd
2134 FROM IGS_PE_STATISTICS PS,
2135 IGS_ST_CITIZENSHP_CD CIT,
2136 IGS_PE_PERM_RES_CD PRCD
2137 WHERE PS.person_id = p_person_id AND
2138 PS.citizenship_cd = CIT.citizenship_cd AND
2139 PS.perm_resident_cd = PRCD.perm_resident_cd (+)
2140 ORDER BY PS.start_dt,
2141 PS.end_dt;
2142 CURSOR c_gccd IS
2143 SELECT govt_citizenship_cd
2144 FROM IGS_ST_CITIZENSHP_CD
2145 WHERE citizenship_cd = p_citizenship_cd;
2146 CURSOR c_gprcd IS
2147 SELECT govt_perm_resident_cd
2148 FROM IGS_PE_PERM_RES_CD
2149 WHERE perm_resident_cd = p_perm_resident_cd;
2150 CURSOR c_hpo_rec IS
2151 SELECT SCHOV.start_dt,
2152 SCHOV.end_dt,
2153 HPO.govt_hecs_payment_option
2154 FROM IGS_EN_STDNT_PS_HECS_OPTION_V SCHOV,
2155 IGS_FI_HECS_PAY_OPTN HPO
2156 WHERE SCHOV.person_id = p_person_id AND
2157 SCHOV.hecs_payment_option = HPO.hecs_payment_option AND
2158 HPO.govt_hecs_payment_option IS NOT NULL
2159 ORDER BY SCHOV.start_dt,
2160 SCHOV.end_dt;
2161 FUNCTION enrpl_chk_whether_to_validate (
2162 p_p_start_dt DATE,
2163 p_p_end_dt DATE,
2164 p_db_start_dt DATE,
2165 p_db_end_dt DATE)
2166 RETURN BOOLEAN AS
2167 BEGIN
2168 DECLARE
2169
2170 BEGIN
2171 -- this module checks whether further validation
2172 -- should be performed, depending on the values
2173 -- of certain system and parameter dates
2174 -- determining if a record should be validated
2175 -- which will occur when the DB effective date(s)
2176 -- overlap or match the parameter date(s)
2177 IF (p_p_end_dt IS NULL OR
2178 p_p_end_dt >= p_db_start_dt) AND
2179 (p_db_end_dt IS NULL OR
2180 p_p_start_dt <= p_db_end_dt) THEN
2181 RETURN TRUE;
2182 END IF;
2183 -- none of the conditions were true
2184 RETURN FALSE;
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2188 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_chk_whether_to_validate');
2189 IGS_GE_MSG_STACK.ADD;
2190 App_Exception.Raise_Exception;
2191 END;
2192 END enrpl_chk_whether_to_validate;
2193 FUNCTION enrpl_do_validations (
2194 p_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
2195 p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
2196 p_govt_perm_resident_cd IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE,
2197 p_message_name OUT NOCOPY VARCHAR2,
2198 p_return_type OUT NOCOPY VARCHAR)
2199 RETURN BOOLEAN AS
2200 BEGIN
2201 DECLARE
2202 v_other_detail VARCHAR2(255);
2203 BEGIN
2204 -- this module performs the validation that is
2205 -- required, in that if the govt. value for
2206 -- the IGS_FI_HECS_PAY_OPTN = 10 or 11, then
2207 -- the govt. value for the IGS_ST_CITIZENSHP_CD must be
2208 -- 1 OR the govt. value for the IGS_PE_PERM_RES_CD must
2209 -- be 1
2210 -- OR
2211 -- if the govt value for the IGS_FI_HECS_PAY_OPTN is 10,
2212 -- 11, 19, 20, 25, 26, 27, 32, or 40, then the govt. value for
2213 -- the IGS_ST_CITIZENSHP_CD must be 1, 2, or 3, and if it is
2214 -- 3, then the govt. value for the IGS_PE_PERM_RES_CD must
2215 -- be 1 or 3
2216 -- checking whether the IGS_FI_GOV_HEC_PA_OP = 10 or 11
2217 -- DETYA validation 1772. Allow for values being NULL
2218 IF p_govt_perm_resident_cd IN (
2219 cst_perm_out_aust_not_crs,
2220 cst_perm) AND
2221 (p_govt_hecs_payment_option IS NULL OR
2222 p_govt_hecs_payment_option <> cst_hecs_upfront) THEN
2223 p_message_name := 'IGS_EN_PERM_RES_STAT_VAL_2_3';
2224 p_return_type := cst_error;
2225 RETURN FALSE;
2226 END IF;
2227 -- checking whether the IGS_FI_GOV_HEC_PA_OP = 10, 11, 19, 20, 25, 26,
2228 -- 27, 32 or 40
2229 -- DETYA validation 1774. Allow for values being NULL
2230 IF (p_govt_hecs_payment_option IS NOT NULL AND
2231 p_govt_hecs_payment_option NOT IN (
2232 cst_hecs_deferred,
2233 cst_hecs_upfront_discount,
2234 cst_hecs_upfront)) AND
2235 (p_govt_perm_resident_cd IS NOT NULL AND
2236 p_govt_perm_resident_cd <> cst_perm_no) THEN
2237 p_message_name := 'IGS_EN_CHK_IF_GOV_VAL_GT_12';
2238 p_return_type := cst_error;
2239 RETURN FALSE;
2240 END IF;
2241 RETURN TRUE;
2242 EXCEPTION
2243 WHEN OTHERS THEN
2244 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2245 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_do_validations');
2246 IGS_GE_MSG_STACK.ADD;
2247 App_Exception.Raise_Exception;
2248 END;
2249 END enrpl_do_validations;
2250 BEGIN
2254 p_message_name := null;
2251 -- This is the main module which validates the student_course_hecs_
2252 -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
2253 -- IGS_ST_CITIZENSHP_CD and the IGS_PE_STATISTICS.IGS_PE_PERM_RES_CD.
2255 p_return_type := NULL;
2256 -- validating the input parameters
2257 IF p_person_id IS NULL THEN
2258 RETURN TRUE;
2259 END IF;
2260 -- validating the input parameters
2261 IF p_course_cd IS NOT NULL THEN
2262 IF p_scho_start_dt IS NULL THEN
2263 RETURN TRUE;
2264 END IF;
2265 -- retrieving the govt. value for the student_course_
2266 -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
2267 OPEN c_ghpo;
2268 FETCH c_ghpo INTO v_govt_hecs_payment_option;
2269 -- exit successfully if a record isn't found
2270 -- therefore, no validation is required
2271 IF c_ghpo%NOTFOUND THEN
2272 CLOSE c_ghpo;
2273 RETURN TRUE;
2274 END IF;
2275 CLOSE c_ghpo;
2276 -- retrieving the govt. value for the IGS_PE_STATISTICS.
2277 -- IGS_ST_CITIZENSHP_CD and the IGS_PE_STATISTICS.IGS_PE_PERM_RES_CD,
2278 -- in which more than one may be found
2279 FOR v_citz IN c_gcitiz_rec LOOP
2280 -- set that a record was found
2281 v_rec_found := TRUE;
2282 -- execute the rountine which checks whether
2283 -- further validation is required.
2284 -- if it returns true, perform the validation
2285 IF enrpl_chk_whether_to_validate (
2286 p_scho_start_dt,
2287 p_scho_end_dt,
2288 v_citz.start_dt,
2289 v_citz.end_dt) THEN
2290 -- execute the validation routine.
2291 -- if it returns false, set the message number
2292 -- and the return type
2293 IF NOT enrpl_do_validations (
2294 v_govt_hecs_payment_option,
2295 v_citz.govt_citizenship_cd,
2296 v_citz.govt_perm_resident_cd,
2297 p_message_name,
2298 p_return_type) THEN
2299 v_false_val := TRUE;
2300 EXIT;
2301 END IF;
2302 END IF;
2303 END LOOP;
2304 ELSE -- p_course_cd IS NULL
2305 -- checking the input parameters
2306 IF p_ps_start_dt IS NULL THEN
2307 RETURN TRUE;
2308 END IF;
2309 -- retrieving the govt. value for the IGS_PE_STATISTICS.
2310 -- IGS_ST_CITIZENSHP_CD
2311 OPEN c_gccd;
2312 FETCH c_gccd INTO v_govt_citizenship_cd;
2313 -- exit successfully if a record isn't found
2314 -- therefore, no validation is required
2315 IF c_gccd%NOTFOUND THEN
2316 CLOSE c_gccd;
2317 RETURN TRUE;
2318 END IF;
2319 CLOSE c_gccd;
2320 -- retrieving the govt. value for the IGS_PE_STATISTICS.
2321 -- IGS_PE_PERM_RES_CD
2322 OPEN c_gprcd;
2323 FETCH c_gprcd INTO v_govt_perm_resident_cd;
2324 CLOSE c_gprcd;
2325 -- retrieving govt. value for the
2326 -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
2327 -- in which more than one record may be found
2328 FOR v_scho IN c_hpo_rec LOOP
2329 -- set that a record was found
2330 v_scho_rec_found := TRUE;
2331 -- execute the rountine which checks whether
2332 -- further validation is required.
2333 -- if it returns true, perform the validation
2334 IF enrpl_chk_whether_to_validate (
2335 p_ps_start_dt,
2336 p_ps_end_dt,
2337 v_scho.start_dt,
2338 v_scho.end_dt) THEN
2339 -- execute the validation routine.
2340 -- if it returns false, set the message number
2341 -- and the return type
2342 IF NOT enrpl_do_validations (
2343 v_scho.govt_hecs_payment_option,
2344 v_govt_citizenship_cd,
2345 v_govt_perm_resident_cd,
2346 p_message_name,
2347 p_return_type) THEN
2351 END IF;
2348 v_false_val := TRUE;
2349 EXIT;
2350 END IF;
2352 END LOOP;
2353 END IF;
2354 -- the validation didn't return TRUE
2355 IF v_false_val THEN
2356 RETURN FALSE;
2357 END IF;
2358 RETURN TRUE;
2359 EXCEPTION
2360 WHEN OTHERS THEN
2361 IF c_ghpo%ISOPEN THEN
2362 CLOSE c_ghpo;
2363 END IF;
2364 IF c_gcitiz_rec%ISOPEN THEN
2365 CLOSE c_gcitiz_rec;
2366 END IF;
2367 IF c_gccd%ISOPEN THEN
2368 CLOSE c_gccd;
2369 END IF;
2370 IF c_gprcd%ISOPEN THEN
2371 CLOSE c_gprcd;
2372 END IF;
2373 IF c_hpo_rec%ISOPEN THEN
2374 CLOSE c_hpo_rec;
2375 END IF;
2376 RAISE;
2377 END;
2378 EXCEPTION
2379 WHEN OTHERS THEN
2380 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2381 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.');
2382 IGS_GE_MSG_STACK.ADD;
2383 App_Exception.Raise_Exception;
2384 END enrp_val_hpo_cic_prc;
2385 --
2386 -- Validate the HECS pay option, the course type and the citizenship cd.
2387 FUNCTION enrp_val_hpo_crs_cic(
2388 p_person_id IN NUMBER ,
2389 p_course_cd IN VARCHAR2 ,
2390 p_scho_start_dt IN DATE ,
2391 p_scho_end_dt IN DATE ,
2392 p_hecs_payment_option IN VARCHAR2 ,
2393 p_ps_start_dt IN DATE ,
2394 p_ps_end_dt IN DATE ,
2395 p_citizenship_cd IN VARCHAR2 ,
2396 p_message_name OUT NOCOPY VARCHAR2,
2397 p_return_type OUT NOCOPY VARCHAR2 )
2398 RETURN BOOLEAN AS
2399 BEGIN
2400 -- validate the IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN against
2401 -- the IGS_PS_VER.IGS_PS_TYPE and the IGS_PE_STATISTICS.citizenship_cd
2402 DECLARE
2403 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2404 v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
2405 v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2406 v_validation_failed BOOLEAN DEFAULT FALSE;
2407 CURSOR c_hpo IS
2408 SELECT hpo.govt_hecs_payment_option
2409 FROM IGS_FI_HECS_PAY_OPTN hpo
2410 WHERE hpo.hecs_payment_option = p_hecs_payment_option;
2411 CURSOR c_ct IS
2412 SELECT ct.govt_course_type
2413 FROM IGS_EN_STDNT_PS_ATT sca,
2414 IGS_PS_VER cv,
2415 IGS_PS_TYPE ct
2416 WHERE sca.person_id = p_person_id AND
2417 sca.course_cd = p_course_cd AND
2418 cv.course_cd = sca.course_cd AND
2419 cv.version_number = sca.version_number AND
2420 cv.course_type = ct.course_type;
2421 CURSOR c_ps IS
2422 SELECT ps.start_dt,
2423 ps.end_dt,
2424 cc.govt_citizenship_cd
2425 FROM IGS_PE_STATISTICS ps,
2426 IGS_ST_CITIZENSHP_CD cc
2427 WHERE ps.person_id = p_person_id AND
2428 ps.citizenship_cd = cc.citizenship_cd
2429 ORDER BY
2430 start_dt,
2431 end_dt;
2432 CURSOR c_scho IS
2433 SELECT scho.start_dt,
2434 scho.end_dt,
2435 hpo.govt_hecs_payment_option,
2436 ct.govt_course_type
2437 FROM IGS_EN_STDNT_PS_HECS_OPTION_V scho,
2438 IGS_FI_HECS_PAY_OPTN hpo,
2439 IGS_EN_STDNT_PS_ATT sca,
2440 IGS_PS_VER cv,
2441 IGS_PS_TYPE ct
2442 WHERE scho.person_id = p_person_id AND
2443 scho.hecs_payment_option = hpo.hecs_payment_option AND
2444 sca.person_id = scho.person_id AND
2445 sca.course_cd = scho.course_cd AND
2446 cv.course_cd = sca.course_cd AND
2447 cv.version_number = sca.version_number AND
2448 cv.course_type = ct.course_type;
2449 CURSOR c_cic IS
2450 SELECT cic.govt_citizenship_cd
2451 FROM IGS_ST_CITIZENSHP_CD cic
2452 WHERE cic.citizenship_cd = p_citizenship_cd;
2453 FUNCTION enrpl_get_govt_values
2454 RETURN BOOLEAN
2455 AS
2456 BEGIN
2457 -- Get the govt value for the student hecs paymt option
2458 OPEN c_hpo;
2459 FETCH c_hpo INTO v_govt_hecs_payment_option;
2460 IF c_hpo%NOTFOUND THEN
2461 CLOSE c_hpo;
2462 RETURN FALSE;
2463 END IF;
2464 CLOSE c_hpo;
2465 -- Get the govt value for IGS_PS_TYPE
2466 OPEN c_ct;
2467 FETCH c_ct INTO v_govt_course_type;
2468 IF c_ct%NOTFOUND THEN
2469 CLOSE c_ct;
2470 RETURN FALSE;
2471 END IF;
2472 CLOSE c_ct;
2473 RETURN TRUE;
2474 END enrpl_get_govt_values;
2475 FUNCTION enrpl_chk_dates_overlap (
2476 p_para_start_dt DATE,
2477 p_para_end_dt DATE,
2478 p_dbase_start_dt DATE,
2479 p_dbase_end_dt DATE)
2480 RETURN BOOLEAN
2481 AS
2482 BEGIN -- enrpl_chk_dates_overlap
2483 -- Check if database effective date(s) overlap or match the parameter date(s)
2484 DECLARE
2485
2486 BEGIN
2487 IF (p_para_end_dt IS NULL OR
2488 p_para_end_dt >= p_dbase_start_dt) AND
2489 (p_dbase_end_dt IS NULL OR
2490 p_para_start_dt <= p_dbase_end_dt) THEN
2491 RETURN TRUE;
2492 END IF;
2493 RETURN FALSE;
2494 EXCEPTION
2495 WHEN OTHERS THEN
2496 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2497 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_chk_dates_overlap');
2498 IGS_GE_MSG_STACK.ADD;
2502 FUNCTION enrpl_perform_validation (
2499 App_Exception.Raise_Exception;
2500 END;
2501 END enrpl_chk_dates_overlap;
2503 p_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
2504 p_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE,
2505 p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
2506 p_message_name OUT NOCOPY VARCHAR2,
2507 p_return_type OUT NOCOPY VARCHAR2)
2508 RETURN BOOLEAN
2509 AS
2510 BEGIN
2511 DECLARE
2512
2513 BEGIN
2514 p_message_name := null;
2515 -- DEETYA validation 1293
2516 IF p_govt_hecs_payment_option = cst_hecs_enabling_crs AND
2517 (p_govt_course_type <> cst_crs_enabling OR
2518 p_govt_citizenship_cd NOT IN (
2519 cst_citizen_aust,
2520 cst_citizen_nz,
2521 cst_citizen_perm)) THEN
2522 p_message_name := 'IGS_EN_CHK_GOV_VAL_25';
2523 p_return_type := cst_error;
2524 RETURN FALSE;
2525 END IF;
2526 -- DEETYA validation 1654.
2527 IF p_govt_course_type = cst_crs_enabling AND
2528 (p_govt_hecs_payment_option <> cst_hecs_enabling_crs OR
2529 p_govt_citizenship_cd NOT IN (
2530 cst_citizen_aust,
2531 cst_citizen_nz,
2532 cst_citizen_perm)) THEN
2533 p_message_name := 'IGS_EN_PRGTYPE_STUD_PRGATT';
2534 p_return_type := cst_error;
2535 RETURN FALSE;
2536 END IF;
2537 -- DEETYA validation 1646.
2538 IF p_govt_hecs_payment_option = cst_hecs_pg_award THEN
2539 IF p_govt_citizenship_cd IN(
2540 cst_citizen_aust,
2541 cst_citizen_nz,
2542 cst_citizen_perm) AND
2543 p_govt_course_type NOT IN (
2544 cst_crs_higher_doctorate,
2545 cst_crs_doctorate_research,
2546 cst_crs_masters_research,
2547 cst_crs_masters_crs_work,
2548 cst_crs_postgrad,
2549 cst_crs_grad_dip_pg_dip_new,
2550 cst_crs_grad_dip_pg_dip_extend,
2551 cst_crs_bachelor_graduate,
2552 cst_crs_bachelor_honours,
2553 cst_crs_graduate,
2554 cst_crs_doctorate_crs_work) THEN
2555 p_message_name := 'IGS_EN_CHK_GOV_CTZ_1_2_3';
2556 p_return_type := cst_error;
2557 RETURN FALSE;
2558 END IF;
2559 IF p_govt_citizenship_cd IN(
2560 cst_citizen_temp_dip,
2561 cst_citizen_other) AND
2562 p_govt_course_type NOT IN (
2563 cst_crs_higher_doctorate,
2564 cst_crs_doctorate_research,
2565 cst_crs_masters_research) THEN
2566 p_message_name := 'IGS_EN_CHK_GOV_CTZ_1_2_3';
2567 p_return_type := cst_error;
2568 RETURN FALSE;
2569 END IF;
2570 END IF;
2571 -- DEETYA validation 1645.
2572 IF p_govt_hecs_payment_option = cst_hecs_fee_paying_pg AND
2573 (p_govt_course_type NOT IN(
2574 cst_crs_higher_doctorate,
2575 cst_crs_doctorate_research,
2576 cst_crs_masters_research,
2577 cst_crs_masters_crs_work,
2578 cst_crs_postgrad,
2579 cst_crs_grad_dip_pg_dip_new,
2580 cst_crs_grad_dip_pg_dip_extend,
2581 cst_crs_graduate,
2582 cst_crs_doctorate_crs_work,
2583 cst_crs_cross_inst_pg) OR
2584 p_govt_citizenship_cd NOT IN (
2585 cst_citizen_aust,
2586 cst_citizen_nz,
2587 cst_citizen_perm)) THEN
2588 p_message_name := 'IGS_EN_HECS_OPTION_GOVTVAK_20';
2589 p_return_type := cst_error;
2590 RETURN FALSE;
2591 END IF;
2592 IF p_govt_hecs_payment_option = cst_hecs_non_os_fee_paying_ug THEN
2593 IF p_govt_course_type NOT IN (
2594 cst_crs_bachelor_graduate,
2595 cst_crs_bachelor_honours,
2596 cst_crs_bachelor_pass,
2597 cst_crs_assoc_degree,
2601 cst_crs_cross_inst_ug) THEN
2598 cst_crs_adv_diploma,
2599 cst_crs_diploma,
2600 cst_other_award,
2602 p_message_name := 'IGS_EN_HECS_OPTION_GOVT_19';
2603 p_return_type := cst_error;
2604 RETURN FALSE;
2605 ELSE -- course type is a valid value
2606 IF p_govt_citizenship_cd NOT IN (
2607 cst_citizen_aust,
2608 cst_citizen_nz,
2609 cst_citizen_perm) THEN
2610 p_message_name := 'IGS_EN_HECS_OPTION_GOVT_19';
2611 p_return_type := cst_error;
2612 RETURN FALSE;
2613 END IF;
2614 END IF;
2615 END IF;
2616 RETURN TRUE;
2617 EXCEPTION
2618 WHEN OTHERS THEN
2619 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2620 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_perform_validation');
2621 IGS_GE_MSG_STACK.ADD;
2622 App_Exception.Raise_Exception;
2623 END;
2624 END enrpl_perform_validation;
2625 BEGIN
2626 p_message_name := null;
2627 -- Validate input parameters
2628 IF p_person_id IS NULL THEN
2629 RETURN TRUE;
2630 END IF;
2631 IF p_course_cd IS NOT NULL THEN
2632 -- This module has been called when validating
2633 -- a IGS_EN_STDNT_PS_HECS_OPTION_V record
2634 IF p_scho_start_dt IS NULL THEN
2635 RETURN TRUE;
2636 END IF;
2637 -- get govt values for student hecs paymnt option, IGS_PS_TYPE
2638 IF NOT enrpl_get_govt_values THEN
2639 RETURN TRUE;
2640 END IF;
2641 -- Get the govt value for the IGS_ST_CITIZENSHP_CD
2642 FOR v_rec IN c_ps LOOP
2643 -- For each record that is retrieved, only validate if the
2644 -- database effective date(s) overlap or match the parameter date(s)
2645 IF enrpl_chk_dates_overlap (
2646 p_scho_start_dt,
2647 p_scho_end_dt,
2648 v_rec.start_dt,
2649 v_rec.end_dt) THEN
2650 -- premature exit loop if validation fails
2651 IF NOT enrpl_perform_validation (
2652 v_govt_hecs_payment_option,
2653 v_govt_course_type,
2654 v_rec.govt_citizenship_cd,
2655 p_message_name,
2656 p_return_type) THEN
2657 v_validation_failed := TRUE;
2658 EXIT;
2659 END IF;
2660 END IF;
2661 END LOOP;
2662 ELSE -- p_course_cd IS NOT NULL
2663 IF p_ps_start_dt IS NULL THEN
2664 RETURN TRUE;
2665 END IF;
2666 OPEN c_cic;
2667 FETCH c_cic INTO v_govt_citizenship_cd;
2668 IF c_cic%NOTFOUND THEN
2669 CLOSE c_cic;
2670 RETURN TRUE;
2671 END IF;
2672 CLOSE c_cic;
2673 FOR v_govt_rec IN c_scho LOOP
2674 -- For each record that is retrieved, only validate if the
2675 -- database effective date(s) overlap or match the parameter date(s)
2676 IF enrpl_chk_dates_overlap (
2677 p_ps_start_dt,
2678 p_ps_end_dt,
2679 v_govt_rec.start_dt,
2680 v_govt_rec.end_dt) THEN
2681 -- premature exit loop if validation fails
2682 IF NOT enrpl_perform_validation (
2683 v_govt_rec.govt_hecs_payment_option,
2684 v_govt_rec.govt_course_type,
2685 v_govt_citizenship_cd,
2686 p_message_name,
2687 p_return_type) THEN
2688 v_validation_failed := TRUE;
2689 EXIT;
2690 END IF;
2691 END IF;
2692 END LOOP;
2693 END IF; -- p_course_cd IS NULL
2694 -- Validation fails
2695 IF v_validation_failed THEN
2696 RETURN FALSE;
2697 END IF;
2698 RETURN TRUE;
2699 END;
2700 EXCEPTION
2701 WHEN OTHERS THEN
2702 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2703 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_hpo_crs_cic');
2704 IGS_GE_MSG_STACK.ADD;
2705 App_Exception.Raise_Exception;
2706 END enrp_val_hpo_crs_cic;
2707 --
2708 -- Validate HECS payment option and the citizenship code.
2709 FUNCTION enrp_val_hpo_cic(
2710 p_person_id IN NUMBER ,
2711 p_course_cd IN VARCHAR2 ,
2712 p_scho_start_dt IN DATE ,
2713 p_scho_end_dt IN DATE ,
2714 p_hecs_payment_option IN VARCHAR2 ,
2715 p_ps_start_dt IN DATE ,
2716 p_ps_end_dt IN DATE ,
2717 p_citizenship_cd IN VARCHAR2 ,
2718 p_message_name OUT NOCOPY VARCHAR2,
2719 p_return_type OUT NOCOPY VARCHAR2 )
2723 pl_govt_hecs_payment_option IN
2720 RETURN BOOLEAN AS
2721 -- local function
2722 FUNCTION enrpl_val_hpo_cit_cd (
2724 IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option%TYPE,
2725 pl_govt_citizenship_cd IN IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
2726 p1_v_inst_3037 IN BOOLEAN,
2727 pl_message_name OUT NOCOPY VARCHAR2,
2728 pl_return_type OUT NOCOPY VARCHAR2 )
2729 RETURN
2730 BOOLEAN
2731 AS
2732 BEGIN
2733 pl_message_name := null;
2734 pl_return_type := NULL;
2735 -- DEETYA validation 1666
2736 IF pl_govt_citizenship_cd IN (
2737 cst_citizen_aust,
2738 cst_citizen_nz,
2739 cst_citizen_perm) AND
2740 pl_govt_hecs_payment_option NOT IN (
2741 cst_hecs_deferred,
2742 cst_hecs_upfront_discount,
2743 cst_hecs_upfront,
2744 cst_hecs_non_os_fee_paying_ug,
2745 cst_hecs_fee_paying_pg,
2746 cst_hecs_enabling_crs,
2747 cst_hecs_non_award_crs,
2748 cst_hecs_employer_funded_crs,
2749 cst_hecs_non_os_comm_ug_dis,
2750 cst_hecs_comm_industry,
2751 cst_hecs_work_experience,
2752 cst_hecs_pg_award,
2753 cst_hecs_non_os_spec_crs,
2754 cst_hecs_avondale_special) THEN
2755 pl_message_name := 'IGS_EN_CHK_CTZ/RES_STAT_1_2_3';
2756 pl_return_type := cst_error;
2757 RETURN FALSE;
2758 END IF;
2759 -- DEETYA validation 1574
2760 IF pl_govt_citizenship_cd IN (
2761 cst_citizen_temp_dip,
2762 cst_citizen_other) AND
2763 pl_govt_hecs_payment_option NOT IN (
2764 cst_hecs_fee_paying_os,
2765 cst_hecs_os_student_charge,
2766 cst_hecs_fee_paying_os_spnsr,
2767 cst_hecs_os_exchange_student,
2768 cst_hecs_pg_award,
2769 cst_hecs_os_spec_crs,
2770 cst_hecs_avondale_special) THEN
2771 pl_message_name := 'IGS_EN_CHK_CTZ/RES_STAT_4_5';
2772 pl_return_type := cst_error;
2773 RETURN FALSE;
2774 END IF;
2775 -- DEETYA validation 1675
2776 IF NOT p1_v_inst_3037 AND
2777 pl_govt_citizenship_cd = cst_citizen_aust AND
2778 pl_govt_hecs_payment_option NOT IN (
2779 cst_hecs_deferred,
2780 cst_hecs_upfront_discount,
2781 cst_hecs_non_os_fee_paying_ug,
2782 cst_hecs_fee_paying_pg,
2783 cst_hecs_enabling_crs,
2784 cst_hecs_non_award_crs,
2785 cst_hecs_employer_funded_crs,
2786 cst_hecs_non_os_comm_ug_dis,
2787 cst_hecs_comm_industry,
2788 cst_hecs_work_experience,
2789 cst_hecs_pg_award,
2790 cst_hecs_non_os_spec_crs,
2791 cst_hecs_avondale_special) THEN
2792 pl_message_name := 'IGS_EN_INST_NOT_3037';
2793 pl_return_type := cst_error;
2794 RETURN FALSE;
2795 END IF;
2796 -- DETYA Validation 1759
2797 IF pl_govt_hecs_payment_option = cst_hecs_employer_funded_crs AND
2798 pl_govt_citizenship_cd NOT IN (
2799 cst_citizen_aust,
2800 cst_citizen_nz,
2801 cst_citizen_perm) THEN
2802 pl_message_name := 'IGS_EN_HECS_PYMNT_OPTION_27';
2803 pl_return_type := cst_error;
2804 RETURN FALSE;
2805 END IF;
2806 RETURN TRUE;
2807 END enrpl_val_hpo_cit_cd;
2808 BEGIN
2809 DECLARE
2810 TYPE t_govt_cit_cd_details IS RECORD (
2811 start_dt IGS_PE_STATISTICS.start_dt%TYPE,
2812 end_dt IGS_PE_STATISTICS.end_dt%TYPE,
2813 IGS_PE_GOVCITIZEN_CD IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE );
2814 TYPE t_hpo_details IS RECORD (
2815 start_dt IGS_PE_STATISTICS.start_dt%TYPE,
2816 end_dt IGS_PE_STATISTICS.end_dt%TYPE,
2817 govt_hpo IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE );
2818 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2819 v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2820 v_cit_cd_details_rec t_govt_cit_cd_details;
2821 v_hpo_details_rec t_hpo_details;
2822 v_institution_3037 BOOLEAN DEFAULT FALSE;
2823 v_exit BOOLEAN DEFAULT FALSE;
2824 CURSOR c_ins IS
2825 SELECT ins.govt_institution_cd
2826 FROM IGS_OR_INSTITUTION ins,
2827 IGS_OR_INST_STAT ist
2828 WHERE ins.local_institution_ind = 'Y' AND
2829 ist.institution_status = ins.institution_status AND
2830 ist.s_institution_status = 'ACTIVE';
2831 CURSOR c_govt_hpo IS
2832 SELECT hpo.govt_hecs_payment_option
2833 FROM IGS_FI_HECS_PAY_OPTN hpo
2834 WHERE hpo.hecs_payment_option = p_hecs_payment_option AND
2835 hpo.govt_hecs_payment_option IS NOT NULL;
2836 CURSOR c_ps IS
2837 SELECT ps.start_dt,
2838 ps.end_dt,
2839 cic.govt_citizenship_cd
2840 FROM IGS_PE_STATISTICS ps,
2841 IGS_ST_CITIZENSHP_CD cic
2842 WHERE ps.person_id = p_person_id AND
2843 cic.citizenship_cd = ps.citizenship_cd
2844 ORDER BY
2845 ps.start_dt,
2846 ps.end_dt;
2847 CURSOR c_schov IS
2848 SELECT schov.start_dt,
2849 schov.end_dt,
2850 hpo.govt_hecs_payment_option
2851 FROM IGS_EN_STDNT_PS_HECS_OPTION_V schov,
2852 IGS_FI_HECS_PAY_OPTN hpo
2853 WHERE schov.person_id = p_person_id AND
2857 schov.start_dt,
2854 schov.hecs_payment_option = hpo.hecs_payment_option AND
2855 hpo.govt_hecs_payment_option IS NOT NULL
2856 ORDER BY
2858 schov.end_dt;
2859 CURSOR c_gov_details IS
2860 SELECT govt_citizenship_cd
2861 FROM IGS_ST_CITIZENSHP_CD
2862 WHERE citizenship_cd = p_citizenship_cd;
2863 BEGIN
2864 -- This module may be called when validating a IGS_EN_STDNT_PS_HECS_OPTION_V
2865 -- record or a IGS_PE_STATISTICS record. person Id will be the only
2866 -- input parameter that will be set when called from either place. The
2867 -- other input parameters will be set depending on where the module is
2868 -- called from. If course code is set, we can assume the module have been
2869 -- called when validating a IGS_EN_STDNT_PS_HECS_OPTION_V record, otherwise
2870 -- if it is not set, we can assume the module has been called when validating
2871 -- a IGS_PE_STATISTICS record. Because both records are effective dated,
2872 -- validation from both places will need to include looping logic to match
2873 -- on all records that overlap with the one being validated. When validating
2874 -- a IGS_PE_STATISTICS record, logic is required to loop through all of the
2875 -- IGS_EN_STDNT_PS_HECS_OPTION_V records that may exist for the person.
2876 -- Looping is required for the different course codes that may exist.
2877 -- Therefor, this module validates the IGS_EN_STDNT_PS_HECS_OPTION_V.
2878 -- IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.citizenship_cd.
2879 -- Validations are :
2880 -- 1. If the government value for the IGS_PE_STATISTICS.citizenship_cd is
2881 -- 1, 2 or 3, then the government value for the
2882 -- IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option must be
2883 -- 10, 11, 12, 19, 20, 25, 26, 27, 31, 32, 33, 40, 70 or 99
2884 -- (DEETYA validation1666).
2885 -- 2. If the government value for the IGS_PE_STATISTICS.citizenship_cd is 4 or
2886 --5, then the government value for the
2887 -- IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option must be 22, 23,
2888 -- 24, 26, 27, 30, 70 or 99 (DEETYA validation 1574).
2889 -- 3. If the local INSTITUTION is not 3037 (Open Learning Agency of Australia)
2890 -- and the government value for the IGS_PE_STATISTICS.citizenship_cd is 1,
2891 -- then the government value for the IGS_EN_STDNT_PS_HECS_OPTION_V.
2892 -- IGS_FI_HECS_PAY_OPTN must be 10, 11, 19, 20, 25, 26, 27, 31, 32, 33, 40,
2893 -- 70
2894 -- or 99 (DEETYA validation 1675).
2895 --- Set the default message number
2896 p_message_name := null;
2897 --- Validate input parameters
2898 IF p_person_id IS NULL THEN
2899 RETURN TRUE;
2900 END IF;
2901 -- retrieving the government INSTITUTION code
2902 FOR v_govt_ins_cd IN c_ins LOOP
2903 IF v_govt_ins_cd.govt_institution_cd = 3037 THEN
2904 v_institution_3037 := TRUE;
2905 EXIT;
2906 END IF;
2907 END LOOP;
2908 --- If the course code is set, then we assume this module has been called to
2909 --- validate a IGS_EN_STDNT_PS_HECS_OPTION_V record. Otherwise we assume we are
2910 --- validating a IGS_PE_STATISTICS record.
2911 IF p_course_cd IS NOT NULL THEN
2912 IF p_scho_start_dt IS NULL THEN
2913 RETURN TRUE;
2914 END IF;
2915 --- Retrieve the government value for
2916 --- the IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option.
2917 OPEN c_govt_hpo;
2918 FETCH c_govt_hpo INTO v_govt_hecs_payment_option;
2919 IF c_govt_hpo%NOTFOUND THEN
2920 CLOSE c_govt_hpo;
2921 RETURN TRUE;
2922 END IF;
2923 CLOSE c_govt_hpo;
2924 --- Retrieve the government value for the IGS_PE_STATISTICS.citizenship_cd.
2925 --- Many records may be retrieved.
2926 --- For each record that is retrieved determine if it should be validated.
2927 --- Validation is required if the database effective date(s) overlap or match
2928 --- the parameter date(s). Looping logic is required here.
2929 FOR v_ps_rec IN c_ps LOOP
2930 IF (p_scho_end_dt IS NULL OR
2931 p_scho_end_dt >= v_ps_rec.start_dt) AND
2932 (v_ps_rec.end_dt IS NULL OR
2933 p_scho_start_dt <= v_ps_rec.end_dt) AND
2934 NOT enrpl_val_hpo_cit_cd(
2935 v_govt_hecs_payment_option,
2936 v_ps_rec.govt_citizenship_cd,
2937 v_institution_3037,
2938 p_message_name,
2939 p_return_type ) THEN
2940 v_exit := TRUE;
2941 EXIT;
2942 END IF;
2943 END LOOP;
2947 END IF;
2944 ELSE -- p_course_cd IS NULL
2945 IF p_ps_start_dt IS NULL THEN
2946 RETURN TRUE;
2948 --- Retrieve the government value for the IGS_PE_STATISTICS.citizenship_cd
2949 OPEN c_gov_details;
2950 FETCH c_gov_details INTO v_govt_citizenship_cd;
2951 IF c_gov_details%NOTFOUND THEN
2952 CLOSE c_gov_details;
2953 RETURN TRUE;
2954 END IF;
2955 CLOSE c_gov_details;
2956 --- Retrieve the government value for the
2957 --- IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option.
2958 --- Many records may be retrieved.
2959 --- For each record that is retrieved determine IF it should be validated.
2960 --- Validation is required IF the database effective date(s) overlap or
2961 --- match the parameter date(s).
2962 FOR v_schov_rec IN c_schov LOOP
2963 IF (p_scho_end_dt IS NULL OR
2964 p_scho_end_dt >= v_schov_rec.start_dt) AND
2965 (v_schov_rec.end_dt IS NULL OR
2966 p_scho_start_dt <= v_schov_rec.end_dt) AND
2967 NOT enrpl_val_hpo_cit_cd(
2968 v_schov_rec.govt_hecs_payment_option,
2969 v_govt_citizenship_cd,
2970 v_institution_3037,
2971 p_message_name,
2972 p_return_type ) THEN
2973 v_exit := TRUE;
2974 EXIT;
2975 END IF;
2976 END LOOP;
2977 END IF;
2978 IF v_exit THEN
2979 RETURN FALSE;
2980 END IF;
2981 --- Return the default values
2982 RETURN TRUE;
2983 END;
2984 EXCEPTION
2985 WHEN OTHERS THEN
2986 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2987 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_hpo_cic');
2988 IGS_GE_MSG_STACK.ADD;
2989 App_Exception.Raise_Exception;
2990 END enrp_val_hpo_cic;
2991 --
2992 -- Validate the HECS payment option closed indicator.
2993 FUNCTION enrp_val_hpo_closed(
2994 p_hecs_payment_option IN VARCHAR2 ,
2995 p_message_name OUT NOCOPY VARCHAR2)
2996 RETURN BOOLEAN AS
2997 BEGIN
2998 DECLARE
2999 gv_closed_ind IGS_FI_HECS_PAY_OPTN.closed_ind%TYPE;
3000 CURSOR gc_hecs_payment_option(
3001 cp_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE) IS
3002 SELECT IGS_FI_HECS_PAY_OPTN.closed_ind
3003 FROM IGS_FI_HECS_PAY_OPTN
3004 WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = cp_hecs_payment_option;
3005 BEGIN
3006 -- This module validates if IGS_FI_HECS_PAY_OPTN.hecs_payment_option
3007 -- is closed
3008 p_message_name := null;
3009 OPEN gc_hecs_payment_option(
3010 p_hecs_payment_option);
3011 FETCH gc_hecs_payment_option INTO gv_closed_ind;
3012 IF (gc_hecs_payment_option%FOUND) THEN
3013 IF (gv_closed_ind = 'Y' ) THEN
3014 CLOSE gc_hecs_payment_option;
3015 p_message_name := 'IGS_EN_HECS_PAY_OPT_CLOSED';
3016 RETURN FALSE;
3017 END IF;
3018 END IF;
3019 CLOSE gc_hecs_payment_option;
3020 RETURN TRUE;
3021 EXCEPTION
3022 WHEN OTHERS THEN
3023 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3024 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_hpo_closed');
3025 IGS_GE_MSG_STACK.ADD;
3026 App_Exception.Raise_Exception;
3027 END;
3028 END enrp_val_hpo_closed;
3029 --
3030 -- Validate HECS payment option, citizenship code and other statistics.
3031 FUNCTION enrp_val_hpo_cic_ps(
3032 p_person_id IN NUMBER ,
3033 p_course_cd IN VARCHAR2 ,
3034 p_scho_start_dt IN DATE ,
3035 p_scho_end_dt IN DATE ,
3036 p_hecs_payment_option IN VARCHAR2 ,
3037 p_ps_start_dt IN DATE ,
3038 p_ps_end_dt IN DATE ,
3039 p_citizenship_cd IN VARCHAR2 ,
3040 p_yr_arrival IN VARCHAR2 ,
3041 p_term_location_country IN VARCHAR2 ,
3042 p_term_location_postcode IN NUMBER ,
3043 p_collection_yr IN DATE ,
3044 p_message_name OUT NOCOPY VARCHAR2,
3045 p_return_type OUT NOCOPY VARCHAR2 )
3046 RETURN BOOLEAN AS
3047 BEGIN -- ernp_val_hpo_cic_ps
3048 -- validate the IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option
3049 -- against the IGS_PE_STATISTICS.citizenship_cd and other IGS_PE_STATISTICS
3050 -- values, including IGS_PE_STATISTICS.yr_arrival and
3051 -- IGS_PE_STATISTICS.term_location_[country|postcode]
3052 -- The IGS_EN_STDNT_PS_ATT.commencement_dt is also used in validations.
3053 DECLARE
3054 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
3055 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
3056 v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
3057 v_validation_failed BOOLEAN DEFAULT FALSE;
3058 CURSOR c_hpo IS
3059 SELECT hpo.govt_hecs_payment_option
3060 FROM IGS_FI_HECS_PAY_OPTN hpo
3061 WHERE hpo.hecs_payment_option = p_hecs_payment_option;
3062 CURSOR c_sca IS
3063 SELECT sca.commencement_dt
3064 FROM IGS_EN_STDNT_PS_ATT sca
3065 WHERE sca.person_id = p_person_id AND
3066 sca.course_cd = p_course_cd;
3067 CURSOR c_ps IS
3068 SELECT ps.start_dt,
3069 ps.end_dt,
3070 ps.yr_arrival,
3071 ps.term_location_country,
3072 ps.term_location_postcode,
3073 cc.govt_citizenship_cd
3074 FROM IGS_PE_STATISTICS ps,
3075 IGS_ST_CITIZENSHP_CD cc
3076 WHERE ps.person_id = p_person_id AND
3077 ps.citizenship_cd = cc.citizenship_cd
3078 ORDER BY
3079 ps.start_dt,
3080 ps.end_dt;
3081 CURSOR c_cic IS
3082 SELECT cic.govt_citizenship_cd
3083 FROM IGS_ST_CITIZENSHP_CD cic
3084 WHERE cic.citizenship_cd = p_citizenship_cd;
3085 CURSOR c_scho IS
3086 SELECT scho.start_dt,
3087 scho.end_dt,
3088 hpo.govt_hecs_payment_option,
3089 sca.commencement_dt
3090 FROM IGS_EN_STDNT_PS_HECS_OPTION_V scho,
3091 IGS_FI_HECS_PAY_OPTN hpo,
3092 IGS_EN_STDNT_PS_ATT sca
3096 sca.course_cd = scho.course_cd
3093 WHERE scho.person_id = p_person_id AND
3094 scho.hecs_payment_option = hpo.hecs_payment_option AND
3095 sca.person_id = scho.person_id AND
3097 ORDER BY
3098 scho.start_dt,
3099 scho.end_dt;
3100 FUNCTION enrpl_chk_dates_overlap (
3101 p_para_start_dt DATE,
3102 p_para_end_dt DATE,
3103 p_dbase_start_dt DATE,
3104 p_dbase_end_dt DATE)
3105 RETURN BOOLEAN
3106 AS
3107 BEGIN -- enrpl_chk_dates_overlap
3108 -- Check if database effective date(s) overlap or match the
3109 -- parameter date(s)
3110 DECLARE
3111 v_other_detail VARCHAR2(255);
3112 BEGIN
3113 IF (p_para_end_dt IS NULL OR
3114 p_para_end_dt >= p_dbase_start_dt) AND
3115 (p_dbase_end_dt IS NULL OR
3116 p_para_start_dt <= p_dbase_end_dt) THEN
3117 RETURN TRUE;
3118 END IF;
3119 RETURN FALSE;
3120 EXCEPTION
3121 WHEN OTHERS THEN
3122 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3123 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_chk_dates_overlap');
3124 IGS_GE_MSG_STACK.ADD;
3125 App_Exception.Raise_Exception;
3126 END;
3127 END enrpl_chk_dates_overlap;
3128 FUNCTION enrpl_perform_validation (
3129 lp_govt_hecs_payment_option
3130 IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option%TYPE,
3131 lp_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE,
3132 lp_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
3133 lp_yr_arrival IGS_PE_STATISTICS.yr_arrival%TYPE,
3134 lp_term_location_country IGS_PE_STATISTICS.term_location_country%TYPE,
3135 lp_term_location_postcode IGS_PE_STATISTICS.term_location_postcode%TYPE)
3136 RETURN BOOLEAN
3137 AS
3138 lv_other_detail VARCHAR2(255);
3139 BEGIN
3140 DECLARE
3141 lv_check VARCHAR2(1);
3142 lv_collection_yr NUMBER(4);
3143 lv_collection_yr_less1_str VARCHAR2(12);
3144 CURSOR c_cnc IS
3145 SELECT 'x'
3146 FROM IGS_PE_COUNTRY_CD cnc
3147 WHERE cnc.country_cd = lp_term_location_country;
3148 BEGIN
3149 lv_collection_yr := TO_NUMBER(substr(IGS_GE_DATE.IGSCHAR(p_collection_yr),1,4));
3150 IF lp_govt_citizenship_cd = cst_citizen_nz THEN
3151 IF lp_commencement_dt IS NOT NULL THEN
3152 -- perform validations that are dependent on the course commencement date
3153 IF lp_commencement_dt >= IGS_GE_DATE.IGSDATE('1996/01/01') THEN
3154 -- DEET validation 1676
3155 -- HECS payment option must not be deferred or upfront with discount
3156 IF lp_govt_hecs_payment_option IN (
3157 cst_hecs_deferred,
3158 cst_hecs_upfront_discount) THEN
3159 p_message_name := 'IGS_EN_HECS_GOV_VAL_10_11';
3160 p_return_type := cst_warn;
3161 RETURN FALSE;
3162 END IF;
3163 ELSE -- commencement date before 1/1/96
3164 IF lv_collection_yr IS NOT NULL AND
3165 lp_yr_arrival IS NOT NULL AND
3166 lp_yr_arrival NOT IN (
3167 cst_off_shore,
3168 cst_no_arrival,
3169 cst_arrival_no_info,
3170 cst_no_info_aust) AND
3171 lv_collection_yr - lp_yr_arrival >= 3 THEN
3172 IF lp_term_location_country = cst_born_os_no_info THEN
3173 --Term LOCATION is S999.
3174 -- DEET validation 1701
3175 -- HECS payment must not be upfront(12).
3176 IF lp_govt_hecs_payment_option= cst_hecs_upfront THEN
3177 p_message_name := 'IGS_EN_CHK_CTZ/RES_STATUS';
3178 p_return_type := cst_warn;
3179 RETURN FALSE;
3180 END IF;
3181 ELSE
3182 -- Check if term LOCATION is a country code.
3183 OPEN c_cnc;
3184 FETCH c_cnc INTO lv_check;
3185 IF c_cnc%FOUND AND
3186 -- Term LOCATION is an overseas country.
3187 -- DEET validation 1701
3188 -- HECS payment must not be upfront.
3189 lp_govt_hecs_payment_option = cst_hecs_upfront THEN
3190 CLOSE c_cnc;
3191 p_message_name := 'IGS_EN_CHK_CTZ/RES_STATUS';
3192 p_return_type := cst_warn;
3193 RETURN FALSE;
3194 END IF;
3195 CLOSE c_cnc;
3196 END IF;
3197 END IF;
3198 END IF;
3199 IF p_collection_yr IS NOT NULL AND
3200 lp_commencement_dt <
3201 IGS_GE_DATE.IGSDATE(lv_collection_yr_less1_str) THEN
3202 -- Perform validations that are dependent on the year of arrival
3203 IF lp_yr_arrival IS NOT NULL AND
3204 lp_yr_arrival NOT IN (
3205 cst_off_shore,
3206 cst_no_arrival,
3207 cst_arrival_no_info,
3208 cst_no_info_aust) THEN
3209 IF TO_NUMBER(lp_yr_arrival) IN (
3210 lv_collection_yr,
3211 lv_collection_yr - 1) AND
3212 -- DEET validation 1681
3213 -- HECS payment option must not be deferred or
3214 -- Upfront with discount
3215 lp_govt_hecs_payment_option IN (
3216 cst_hecs_deferred,
3217 cst_hecs_upfront_discount) THEN
3218 p_message_name := 'IGS_EN_CHK_GOV_VAL_CITIZEN';
3219 p_return_type := cst_warn;
3220 RETURN FALSE;
3221 END IF;
3222 IF TO_NUMBER(lp_yr_arrival) = lv_collection_yr - 2 AND
3223 -- DEET validation 1682
3224 -- HECS payment option would not nornally be deferred
3225 -- or upfront with discount
3226 lp_govt_hecs_payment_option IN (
3227 cst_hecs_deferred,
3228 cst_hecs_upfront_discount) THEN
3229 p_message_name := 'IGS_EN_CHK_GOV_CTZ_STATUS';
3230 p_return_type := cst_warn;
3231 RETURN FALSE;
3232 END IF;
3233 END IF; -- lp_yr_arrival IS NOT NULL
3234 END IF; -- p_collection_yr IS NOT NULL ...
3238 -- Deet validation 1683
3235 END IF; -- v_commencement_dt IS NOT NULL
3236 IF lp_term_location_country = cst_born_os_no_info THEN
3237 -- Term LOCATION is S999
3239 -- HECS payment must not be deferred or upfront with discount
3240 IF lp_govt_hecs_payment_option IN (
3241 cst_hecs_deferred,
3242 cst_hecs_upfront_discount) THEN
3243 p_message_name := 'IGS_EN_GOVT_VAL_STATUS_2';
3244 p_return_type := cst_error;
3245 RETURN FALSE;
3246 END IF;
3247 ELSE
3248 -- Check if term LOCATION is a country code
3249 OPEN c_cnc;
3250 FETCH c_cnc INTO lv_check;
3251 IF c_cnc%FOUND THEN
3252 -- Term LOCATION is an overseas country
3253 -- DEET validation 1683
3254 -- HECS payment option must not be deferred or upfront with discount
3255 IF lp_govt_hecs_payment_option IN (
3256 cst_hecs_deferred,
3257 cst_hecs_upfront_discount) THEN
3258 CLOSE c_cnc;
3259 p_message_name := 'IGS_EN_GOVT_VAL_STATUS_2';
3260 p_return_type := cst_error;
3261 RETURN FALSE;
3262 END IF;
3263 END IF;
3264 CLOSE c_cnc;
3265 END IF;
3266 END IF; -- lp_govt_citizenship_cd = cst_citizen_nz
3267 IF lp_govt_citizenship_cd = cst_citizen_perm THEN
3268 IF lp_term_location_country = cst_born_os_no_info THEN
3269
3270 -- Warning.Payment would not normally be 10 or 11
3271 IF lp_govt_hecs_payment_option IN (
3272 cst_hecs_upfront_discount,
3273 cst_hecs_deferred) THEN
3274 p_message_name := 'IGS_EN_CHK_GOV_VAL_CTZN/RES';
3275 p_return_type := cst_warn;
3276 RETURN FALSE;
3277 END IF;
3278 ELSE
3279 -- Check if term LOCATION is a country code.
3280 OPEN c_cnc;
3281 FETCH c_cnc INTO lv_check;
3282 IF c_cnc%FOUND THEN
3283
3284 -- Warning.Payment would not normally be 10 or 11
3285 IF lp_govt_hecs_payment_option IN (
3286 cst_hecs_upfront_discount,
3287 cst_hecs_deferred) THEN
3288 CLOSE c_cnc;
3289 p_message_name := 'IGS_EN_CHK_GOV_VAL_CTZN/RES';
3290 p_return_type := cst_warn;
3291 RETURN FALSE;
3292 END IF;
3293 END IF;
3294 CLOSE c_cnc;
3295 END IF;
3296 END IF;
3297 RETURN TRUE;
3298 EXCEPTION
3299 WHEN OTHERS THEN
3300 IF c_cnc%ISOPEN THEN
3301 CLOSE c_cnc;
3302 END IF;
3303 RAISE;
3304 END;
3305 EXCEPTION
3306 WHEN OTHERS THEN
3307 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3308 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrpl_perform_validation');
3309 IGS_GE_MSG_STACK.ADD;
3310 App_Exception.Raise_Exception;
3311 END enrpl_perform_validation;
3312 BEGIN
3313 p_message_name := null;
3314 IF p_person_id IS NULL THEN
3315 RETURN TRUE;
3316 END IF;
3317 IF p_course_cd IS NOT NULL THEN
3318 -- This module has been called when validating
3319 -- a IGS_EN_STDNT_PS_HECS_OPTION_V record
3320 IF p_scho_start_dt IS NULL THEN
3321 RETURN TRUE;
3322 END IF;
3323 OPEN c_hpo;
3324 FETCH c_hpo INTO v_govt_hecs_payment_option;
3325 CLOSE c_hpo;
3326 IF v_govt_hecs_payment_option IS NULL THEN
3327 RETURN TRUE;
3328 END IF;
3329 OPEN c_sca;
3330 FETCH c_sca INTO v_commencement_dt;
3331 IF c_sca%NOTFOUND THEN
3332 CLOSE c_sca;
3333 RETURN TRUE;
3334 END IF;
3335 CLOSE c_sca;
3336 FOR v_ps_rec IN c_ps LOOP
3337 -- Only perform validation if dbase date overlap or match parameter dates
3338 IF enrpl_chk_dates_overlap (
3339 p_scho_start_dt,
3340 p_scho_end_dt,
3341 v_ps_rec.start_dt,
3342 v_ps_rec.end_dt) THEN
3343 IF NOT enrpl_perform_validation (
3344 v_govt_hecs_payment_option,
3345 v_commencement_dt,
3346 v_ps_rec.govt_citizenship_cd,
3347 v_ps_rec.yr_arrival,
3348 NVL(v_ps_rec.term_location_country, 'NULL-VALUE'),
3349 NVL(v_ps_rec.term_location_postcode, -1)) THEN
3350 v_validation_failed := TRUE;
3351 EXIT;
3352 END IF;
3353 END IF;
3354 END LOOP;
3355 ELSE -- (p_course_cd IS NULL)
3356 -- This module has been called when validating
3357 -- a person_statistic record
3358 IF p_ps_start_dt IS NULL THEN
3359 RETURN TRUE;
3360 END IF;
3361 OPEN c_cic;
3362 FETCH c_cic INTO v_govt_citizenship_cd;
3363 IF c_cic%NOTFOUND THEN
3364 CLOSE c_cic;
3365 RETURN TRUE;
3366 END IF;
3367 CLOSE c_cic;
3368 FOR v_scho_rec IN c_scho LOOP
3369 -- Only perform validation if dbase date overlap or match parameter dates
3370 IF enrpl_chk_dates_overlap (
3371 p_ps_start_dt,
3372 p_ps_end_dt,
3373 v_scho_rec.start_dt,
3374 v_scho_rec.end_dt) THEN
3375 IF NOT enrpl_perform_validation (
3376 v_scho_rec.govt_hecs_payment_option,
3377 v_scho_rec.commencement_dt,
3378 v_govt_citizenship_cd,
3379 p_yr_arrival,
3380 NVL(p_term_location_country, 'NULL-VALUE'),
3381 NVL(p_term_location_postcode, -1)) THEN
3382 v_validation_failed := TRUE;
3383 EXIT;
3384 END IF;
3385 END IF;
3386 END LOOP;
3387 END IF;
3388 IF v_validation_failed THEN
3389 RETURN FALSE;
3390 END IF;
3391 RETURN TRUE;
3392 EXCEPTION
3393 WHEN OTHERS THEN
3394 IF c_hpo%ISOPEN THEN
3395 CLOSE c_hpo;
3396 END IF;
3397 IF c_sca%ISOPEN THEN
3398 CLOSE c_sca;
3402 END IF;
3399 END IF;
3400 IF c_ps%ISOPEN THEN
3401 CLOSE c_ps;
3403 IF c_cic%ISOPEN THEN
3404 CLOSE c_cic;
3405 END IF;
3406 IF c_scho%ISOPEN THEN
3407 CLOSE c_scho;
3408 END IF;
3409 RAISE;
3410 END;
3411 EXCEPTION
3412 WHEN OTHERS THEN
3413 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3414 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_hpo_cic_ps');
3415 IGS_GE_MSG_STACK.ADD;
3416 App_Exception.Raise_Exception;
3417 END enrp_val_hpo_cic_ps;
3418 --
3419 -- Validate that scho end date is in accordance with expiry restriction.
3420 FUNCTION enrp_val_scho_expire(
3421 p_person_id IN NUMBER ,
3422 p_course_cd IN VARCHAR2 ,
3423 p_start_dt IN DATE ,
3424 p_end_dt IN DATE ,
3425 p_hecs_payment_option IN VARCHAR2 ,
3426 p_message_name OUT NOCOPY VARCHAR2)
3427 RETURN BOOLEAN AS
3428 BEGIN
3429 DECLARE
3430 v_expire_aftr_acdmc_perd_ind
3431 IGS_FI_HECS_PAY_OPTN.expire_aftr_acdmc_perd_ind%TYPE;
3432 v_end_dt IGS_CA_INST.end_dt%TYPE;
3433 v_cal_type IGS_CA_INST.cal_type%TYPE;
3434 v_sequence_number IGS_CA_INST.sequence_number%TYPE;
3435 CURSOR c_hpo(
3436 cp_hpo IGS_EN_STDNTPSHECSOP.hecs_payment_option%TYPE) IS
3437 SELECT expire_aftr_acdmc_perd_ind
3438 FROM IGS_FI_HECS_PAY_OPTN
3439 WHERE hecs_payment_option= cp_hpo;
3440 CURSOR c_cal_instance (
3441 cp_cal_type IGS_CA_INST.cal_type%TYPE,
3442 cp_cal_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
3443 SELECT end_dt
3444 FROM IGS_CA_INST
3445 WHERE cal_type = cp_cal_type AND
3446 sequence_number = cp_cal_sequence_number ;
3447 v_other_detail VARCHAR(255);
3448 v_message_name varchar2(30);
3449 BEGIN
3450 -- Validate the the end date is allowable according to whether the
3451 -- nominated HECS payment option is an "expire before end of academic period"
3452 -- option.
3453 p_message_name := null;
3454 OPEN c_hpo(
3455 p_hecs_payment_option);
3456 FETCH c_hpo INTO v_expire_aftr_acdmc_perd_ind;
3457 IF(c_hpo%NOTFOUND) THEN
3458 CLOSE c_hpo;
3459 RETURN TRUE;
3460 END IF;
3461 CLOSE c_hpo;
3462 IF(v_expire_aftr_acdmc_perd_ind = 'N') THEN
3463 RETURN TRUE;
3464 END IF;
3465 IF p_end_dt IS NULL THEN
3466 p_message_name := 'IGS_EN_HECS_PYMTOP_ENDDT';
3467 RETURN FALSE;
3468 END IF;
3469 IGS_EN_GEN_001.ENRP_CLC_SCA_ACAD(p_person_id,
3470 p_course_cd,
3471 SYSDATE,
3472 v_cal_type,
3473 v_sequence_number);
3474 IF(v_cal_type = NULL) THEN
3475 RETURN TRUE;
3476 END IF;
3477 OPEN c_cal_instance(
3478 v_cal_type,
3479 v_sequence_number);
3480 FETCH c_cal_instance INTO v_end_dt;
3481 CLOSE c_cal_instance;
3482 IF (p_end_dt > v_end_dt) THEN
3483 p_message_name := 'IGS_EN_HECS_PAY_OPT_CANT_APPL';
3484 RETURN FALSE;
3485 END IF;
3486 RETURN TRUE;
3487 EXCEPTION
3488 WHEN OTHERS THEN
3489 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3490 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_expire');
3491 IGS_GE_MSG_STACK.ADD;
3492 App_Exception.Raise_Exception;
3493 END;
3494 END enrp_val_scho_expire;
3495 --
3496 --
3497 -- Validate the HECS option tax file number certificate number.
3498 FUNCTION enrp_val_tfn_crtfct(
3499 p_tax_file_number IN NUMBER ,
3500 p_tax_file_invalid_dt IN DATE ,
3501 p_tax_file_certificate_number IN NUMBER ,
3502 p_message_name OUT NOCOPY VARCHAR2)
3503 RETURN BOOLEAN AS
3504 BEGIN
3505 BEGIN
3506 -- Validate the IGS_EN_STDNTPSHECSOP.tax_file_certificate_number.
3507 -- Set the default message number
3508 p_message_name := null;
3509 -- Validate the input parameter
3510 IF (p_tax_file_certificate_number IS NULL) THEN
3511 RETURN TRUE;
3512 END IF;
3513 -- Validate the tax file certificate number.
3514 -- Certificate number can only be set when there is no tax file number
3515 -- recorded or an invalid tax file number is recorded.
3516 IF (p_tax_file_number IS NOT NULL) AND
3517 (p_tax_file_invalid_dt IS NULL) THEN
3518 p_message_name := 'IGS_EN_CERTNUM_NO_TAX_FILE';
3519 RETURN FALSE;
3520 END IF;
3521 -- Return the default value
3522 RETURN TRUE;
3523 END;
3524 EXCEPTION
3525 WHEN OTHERS THEN
3526 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3527 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_tfn_crtfct');
3528 IGS_GE_MSG_STACK.ADD;
3529 App_Exception.Raise_Exception;
3530 END enrp_val_tfn_crtfct;
3531 --
3532 -- Validate the HECS option tax file number invalid date.
3533 FUNCTION enrp_val_tfn_invalid(
3534 p_tax_file_number IN NUMBER ,
3535 p_tax_file_invalid_dt IN DATE ,
3536 p_message_name OUT NOCOPY VARCHAR2)
3537 RETURN BOOLEAN AS
3538 BEGIN
3539 BEGIN
3540 -- This module validates the IGS_EN_STDNTPSHECSOP.tax_file_invalid_dt.
3541 -- Set the default message number
3542 p_message_name := null;
3543 -- Validate the input parameter
3544 IF (p_tax_file_invalid_dt IS NULL) THEN
3545 RETURN TRUE;
3546 END IF;
3547 -- Validate the tax file number invalid date.
3548 -- The tax file number must be set when the invalid date is set.
3549 IF (p_tax_file_number IS NULL) THEN
3550 p_message_name := 'IGS_GE_INVALID_VALUE';
3551 RETURN FALSE;
3552 END IF;
3553 -- Invalid date must not be future dated.
3554 IF (p_tax_file_invalid_dt > SYSDATE) THEN
3555 p_message_name := 'IGS_EN_TAX_FILE_NUM_INVALID_D';
3556 RETURN FALSE;
3557 END IF;
3558 -- Return the default value
3562 WHEN OTHERS THEN
3559 RETURN TRUE;
3560 END;
3561 EXCEPTION
3563 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3564 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_tfn_invalid');
3565 IGS_GE_MSG_STACK.ADD;
3566 App_Exception.Raise_Exception;
3567 END enrp_val_tfn_invalid;
3568 END IGS_EN_VAL_SCHO;
[Home] [Help]