1 PACKAGE BODY IGS_ST_GEN_002 AS
2 /* $Header: IGSST02B.pls 115.7 2003/05/09 14:09:40 sarakshi ship $ */
3
4 /*
5 || Created By : [email protected]
6 || Created On : 28-AUG-2000
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || sarakshi 06-May-2003 Enh#2858431,modified procedure stap_get_crs_study replaced CRSOFSTUDY to OTHER
12 || pkpatel 05-MAR-2002 Bug NO: 2224621
13 || Modified P_GOVT_PRIOR_UG_INST from NUMBER to VARCHAR2 in STAP_GET_PERSON_DATA. Since its source
14 || IGS_OR_INSTITUTION.GOVT_INSTITUTION_CD is modified from NUMBER to VARCHAR2.
15 || (reverse chronological order - newest change first)
16 */
17
18 Function Stap_Get_Course_Lvl(
19 p_course_cd IN VARCHAR2 ,
20 p_version_number IN NUMBER ,
21 p_govt_course_type IN NUMBER )
22 RETURN NUMBER AS
23 gv_other_detail VARCHAR2(255);
24 BEGIN -- stap_get_course_lvl
25 -- Routine to return the level of a course.
26 -- If the level cannot be determined a null value is returned.
27 -- This routine will eventually be replaced with an level field
28 -- on the course_type table.
29 DECLARE
30 v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
31 CURSOR c_cty IS
32 SELECT cty.govt_course_type
33 FROM IGS_PS_VER cv,
34 IGS_PS_TYPE cty
35 WHERE cv.course_cd = p_course_cd AND
36 cv.version_number = p_version_number AND
37 cty.course_type = cv.course_type;
38 BEGIN
39 --- Determine the Government course Type.
40 IF p_govt_course_type IS NULL THEN
41 OPEN c_cty;
42 FETCH c_cty INTO v_govt_course_type;
43 IF c_cty%NOTFOUND THEN
44 CLOSE c_cty;
45 RETURN NULL;
46 END IF;
47 CLOSE c_cty;
48 ELSE
49 v_govt_course_type := p_govt_course_type;
50 END IF;
51 IF v_govt_course_type = 01 THEN
52 RETURN 130;
53 ELSIF v_govt_course_type IN (
54 02,
55 12) THEN
56 RETURN 120;
57 ELSIF v_govt_course_type = 03 THEN
58 RETURN 110;
59 ELSIF v_govt_course_type = 04 THEN
60 RETURN 100;
61 ELSIF v_govt_course_type IN (
62 05,
63 06,
64 07) THEN
65 RETURN 90;
66 ELSIF v_govt_course_type = 11 THEN
67 RETURN 80;
68 ELSIF v_govt_course_type = 08 THEN
69 RETURN 70;
70 ELSIF v_govt_course_type = 09 THEN
71 RETURN 60;
72 ELSIF v_govt_course_type = 10 THEN
73 RETURN 50;
74 ELSIF v_govt_course_type IN (
75 20,
76 13) THEN
77 RETURN 40;
78 ELSIF v_govt_course_type = 21 THEN
79 RETURN 30;
80 ELSIF v_govt_course_type = 22 THEN
81 RETURN 20;
82 ELSIF v_govt_course_type = 30 THEN
83 RETURN 10;
84 END IF;
85 RETURN NULL;
86 END;
87 END stap_get_course_lvl;
88
89 Procedure Stap_Get_Crs_Study(
90 p_course_cd IGS_PS_VER_ALL.course_cd%TYPE ,
91 p_version_number IGS_PS_VER_ALL.version_number%TYPE ,
92 p_reference_cd OUT NOCOPY IGS_PS_REF_CD.reference_cd%TYPE ,
93 p_description OUT NOCOPY IGS_PS_VER_ALL.title%TYPE )
94 AS
95 gv_other_detail VARCHAR2(255);
96 BEGIN -- stap_get_crs_study
97 -- This module returns the course of study code and
98 -- course of study name if they exist,
99 -- otherwise it returns the course code and course title
100 DECLARE
101 CURSOR c_crfc_rct IS
102 SELECT crfc.reference_cd,
103 crfc.description
104 FROM IGS_PS_REF_CD crfc,
105 IGS_GE_REF_CD_TYPE rct
106 WHERE crfc.course_cd = p_course_cd AND
107 crfc.version_number = p_version_number AND
108 rct.reference_cd_type = crfc.reference_cd_type AND
109 rct.s_reference_cd_type = 'OTHER' AND
110 rct.closed_ind = 'N';
111 CURSOR c_crv IS
112 SELECT crv.title
113 FROM IGS_PS_VER crv
114 WHERE crv.course_cd = p_course_cd AND
115 crv.version_number = p_version_number;
116 BEGIN
117 p_reference_cd := NULL;
118 p_description := NULL;
119 OPEN c_crfc_rct;
120 FETCH c_crfc_rct INTO p_reference_cd,
121 p_description;
122 IF c_crfc_rct%NOTFOUND THEN
123 OPEN c_crv;
124 FETCH c_crv INTO p_description;
125 IF c_crv%FOUND THEN
126 p_reference_cd := p_course_cd;
127 END IF;
128 CLOSE c_crv;
129 END IF;
130 CLOSE c_crfc_rct;
131 EXCEPTION
132 WHEN OTHERS THEN
133 IF c_crfc_rct%ISOPEN THEN
134 CLOSE c_crfc_rct;
135 END IF;
136 IF c_crv%ISOPEN THEN
137 CLOSE c_crv;
138 END IF;
139 RAISE;
140 END;
141 EXCEPTION
142 WHEN OTHERS THEN
143 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
144 FND_MESSAGE.SET_TOKEN('NAME','IGSST_GEN_002.stap_get_crs_study');
145 IGS_GE_MSG_STACK.ADD;
146 App_Exception.Raise_Exception;
147 END stap_get_crs_study;
148
149 Function Stap_Get_Govt_Ou_Cd(
150 p_org_unit_cd IN VARCHAR2 )
151 RETURN VARCHAR2 AS
152 gv_other_detail VARCHAR2(255);
153 v_ret_val VARCHAR2(3);
154 BEGIN -- stap_get_govt_ou_cd
155 -- Determine the 3 character code to be reported to DEETYA
156 DECLARE
157 BEGIN
158 -- Validate input parameter
159 IF (p_org_unit_cd IS NULL) THEN
160 RETURN NULL;
161 END IF;
162 -- Determine the return organisational unit code
163 IF (LENGTH(p_org_unit_cd) < 4) THEN
164 v_ret_val := p_org_unit_cd;
165 ELSE
166 -- Return the 2nd, 3rd, 4th characters
167 v_ret_val := SUBSTR(p_org_unit_cd, 2, 3);
168 END IF;
169 RETURN v_ret_val;
170 END;
171 EXCEPTION
172 WHEN OTHERS THEN
173 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
174 FND_MESSAGE.SET_TOKEN('NAME','IGSST_GEN_002.stap_get_govt_ou_cd');
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception;
177 END stap_get_govt_ou_cd;
178
179 Function Stap_Get_Govt_Sem(
180 p_submission_yr IN NUMBER ,
181 p_submission_number IN NUMBER ,
182 p_load_cal_type IN VARCHAR2 ,
183 p_load_ci_sequence_number IN NUMBER ,
184 p_teach_cal_type IN VARCHAR2 )
185 RETURN NUMBER AS
186 BEGIN
187 DECLARE
188 v_other_detail VARCHAR2(255);
189 v_govt_sem IGS_ST_GVTSEMLOAD_OV.govt_semester%TYPE;
190 CURSOR c_gslov IS
191 SELECT gslov.govt_semester
192 FROM IGS_ST_GVTSEMLOAD_OV gslov
193 WHERE gslov.submission_yr = p_submission_yr AND
194 gslov.submission_number = p_submission_number AND
195 gslov.cal_type = p_load_cal_type AND
196 gslov.ci_sequence_number = p_load_ci_sequence_number AND
197 gslov.teach_cal_type = p_teach_cal_type;
198 CURSOR c_gslc IS
199 SELECT gslc.govt_semester
200 FROM IGS_ST_GVTSEMLOAD_CA gslc
201 WHERE gslc.submission_yr = p_submission_yr AND
202 gslc.submission_number = p_submission_number AND
203 gslc.cal_type = p_load_cal_type AND
204 gslc.ci_sequence_number = p_load_ci_sequence_number;
205 BEGIN
206 -- This module gets the government semester for a load calendar
207 -- instance or a load calendar structure (ie. load calendar instance
208 -- and teaching calendar) for a specified government submission.
209 -- If the government semester cannot be determined, a null value
210 -- will be returned.
211 -- get the government value from the IGS_ST_GVTSEMLOAD_OV
212 -- table
213 OPEN c_gslov;
214 FETCH c_gslov INTO v_govt_sem;
215 -- check if a record has been found
216 IF (c_gslov%FOUND) THEN
217 CLOSE c_gslov;
218 RETURN v_govt_sem;
219 END IF;
220 CLOSE c_gslov;
221 -- get the government value from the IGS_ST_GVTSEMLOAD_CA
222 -- table
223 OPEN c_gslc;
224 FETCH c_gslc INTO v_govt_sem;
225 -- check if a record has been found
226 IF (c_gslc%FOUND) THEN
227 CLOSE c_gslc;
228 RETURN v_govt_sem;
229 END IF;
230 CLOSE c_gslc;
231 -- return null when government value can't be
232 -- determined
233 RETURN NULL;
234 END;
235 END stap_get_govt_sem;
236
237 Function Stap_Get_New_Hgh_Edu(
238 p_person_id IN NUMBER ,
239 p_course_cd IN VARCHAR2 ,
240 p_commencing_student_ind IN VARCHAR2 DEFAULT 'N')
241 RETURN NUMBER AS
242 gv_other_detail VARCHAR2(255);
243 BEGIN -- stap_get_new_hgh_edu
244 -- Derive the New To Higher Education value.
245 -- DEETYA element 924
246 DECLARE
247 v_ret_val NUMBER(1) DEFAULT 1;
248 v_prior_post_grad IGS_PE_STATISTICS.prior_post_grad%TYPE;
249 v_prior_degree IGS_PE_STATISTICS.prior_degree%TYPE;
250 v_prior_subdeg_notafe IGS_PE_STATISTICS.prior_subdeg_notafe%TYPE;
251 CURSOR c_ps IS
252 SELECT ps.prior_post_grad,
253 ps.prior_degree,
254 ps.prior_subdeg_notafe
255 FROM IGS_PE_STATISTICS ps
256 WHERE ps.person_id = p_person_id AND
257 TRUNC(ps.start_dt) <= TRUNC(SYSDATE) AND
258 (ps.end_dt IS NULL OR
259 TRUNC(ps.end_dt) >= TRUNC(SYSDATE))
260 ORDER BY end_dt;
261 BEGIN
262 IF p_commencing_student_ind = 'N' THEN
263 -- not a commencing student
264 RETURN v_ret_val;
265 END IF;
266 OPEN c_ps;
267 FETCH c_ps INTO v_prior_post_grad,
268 v_prior_degree,
269 v_prior_subdeg_notafe;
270 IF c_ps%NOTFOUND THEN
271 CLOSE c_ps;
272 -- A commencing student but no information on prior higher
273 -- education course
274 RETURN 9;
275 END IF;
276 CLOSE c_ps;
277 IF v_prior_post_grad = '100' AND
278 v_prior_degree = '100' AND
279 v_prior_subdeg_notafe = '100' THEN
280 -- A commecing student who had never commenced
281 -- a higher education course prior to the first
282 -- enrolment in the current course
283 v_ret_val := 2;
284 ELSIF SUBSTR(v_prior_post_grad, 1, 1) = '3' OR
285 SUBSTR(v_prior_degree, 1, 1) = '3' OR
286 SUBSTR(v_prior_subdeg_notafe, 1, 1) = '3' THEN
287 -- A commecing student who had commenced and complete
288 -- all of the requirements of a higer education course
289 -- prior to the first enrolment in the current course
290 v_ret_val := 3;
291 ELSIF SUBSTR(v_prior_post_grad, 1, 1) = '2' OR
292 SUBSTR(v_prior_degree, 1, 1) = '2' OR
293 SUBSTR(v_prior_subdeg_notafe, 1, 1) = '2' THEN
294 -- A commecing student who had commenced but not
295 -- completed all of the requirements of a higer
296 -- education course prior to the
297 -- first enrolment in the current course
298 v_ret_val := 4;
299 ELSE
300 -- A commencing student but no information on
301 -- prior higher education course
302 v_ret_val := 9;
303 END IF;
304 RETURN v_ret_val;
305 END;
306 EXCEPTION
307 WHEN OTHERS THEN
308 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
309 FND_MESSAGE.SET_TOKEN('NAME','IGSST_GEN_002.stap_get_new_hgh_edu');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312 END stap_get_new_hgh_edu;
313
314 Procedure Stap_Get_Pcc_Pe_Dtl(
315 p_person_id IN NUMBER ,
316 p_effective_dt IN DATE ,
317 p_birth_dt OUT NOCOPY DATE ,
318 p_sex OUT NOCOPY VARCHAR2 ,
319 p_govt_aborig_torres_cd OUT NOCOPY NUMBER ,
320 p_govt_citizenship_cd OUT NOCOPY NUMBER ,
321 p_govt_birth_country_cd OUT NOCOPY VARCHAR2 ,
322 p_yr_arrival OUT NOCOPY VARCHAR2 ,
323 p_govt_home_language_cd OUT NOCOPY NUMBER )
324 AS
325 gv_other_detail VARCHAR2(255);
326 BEGIN -- stap_get_pcc_pe_dtl
327 -- This module gets the person based details needed for the
328 -- government statistics past course completions file.
329 DECLARE
330 CURSOR c_pe IS
331 SELECT pe.birth_dt,
332 pe.sex
333 FROM IGS_PE_PERSON pe
334 WHERE pe.person_id = p_person_id;
335 CURSOR c_ps IS
336 SELECT ps.aborig_torres_cd,
337 ps.citizenship_cd,
338 ps.birth_country_cd,
339 ps.yr_arrival,
340 ps.home_language_cd
341 FROM IGS_PE_STATISTICS ps
342 WHERE ps.person_id = p_person_id AND
343 TRUNC(ps.start_dt) <= TRUNC(p_effective_dt)
344 ORDER BY ps.start_dt DESC;
345 v_aborig_torres_cd IGS_PE_STATISTICS.aborig_torres_cd%TYPE;
346 v_citizenship_cd IGS_PE_STATISTICS.citizenship_cd%TYPE;
347 v_birth_country_cd IGS_PE_STATISTICS.birth_country_cd%TYPE;
348 v_home_language_cd IGS_PE_STATISTICS.home_language_cd%TYPE;
349 CURSOR c_atc IS
350 SELECT atc.govt_aborig_torres_cd
351 FROM IGS_PE_ABORG_TORESCD atc
352 WHERE atc.aborig_torres_cd = v_aborig_torres_cd;
353 CURSOR c_cic IS
354 SELECT cic.govt_citizenship_cd
355 FROM IGS_ST_CITIZENSHP_CD cic
356 WHERE cic.citizenship_cd = v_citizenship_cd;
357 CURSOR c_cnc IS
358 SELECT cnc.govt_country_cd
359 FROM IGS_PE_COUNTRY_CD cnc
360 WHERE cnc.country_cd = v_birth_country_cd;
361 CURSOR c_lc IS
362 SELECT lc.govt_language_cd
363 FROM IGS_PE_LANGUAGE_CD lc
364 WHERE lc.language_cd = v_home_language_cd;
365 BEGIN
366 OPEN c_pe;
367 FETCH c_pe INTO p_birth_dt,
368 p_sex;
369 IF c_pe%NOTFOUND THEN
370 CLOSE c_pe;
371 RAISE NO_DATA_FOUND;
372 END IF;
373 CLOSE c_pe;
374 OPEN c_ps;
375 FETCH c_ps INTO
376 v_aborig_torres_cd,
377 v_citizenship_cd,
378 v_birth_country_cd,
379 p_yr_arrival,
380 v_home_language_cd;
381 IF c_ps%NOTFOUND THEN
382 CLOSE c_ps;
383 p_govt_aborig_torres_cd := 9;
384 p_govt_citizenship_cd := 9;
385 p_yr_arrival := 'A9';
386 p_govt_birth_country_cd := '9999';
387 p_govt_home_language_cd := 99;
388 RETURN;
389 ELSE
390 CLOSE c_ps;
391 END IF;
392 OPEN c_atc;
393 FETCH c_atc INTO p_govt_aborig_torres_cd;
394 IF c_atc%NOTFOUND THEN
395 CLOSE c_atc;
396 p_govt_aborig_torres_cd := 9;
397 ELSE
398 CLOSE c_atc;
399 END IF;
400 OPEN c_cic;
401 FETCH c_cic INTO p_govt_citizenship_cd;
402 IF c_cic%NOTFOUND THEN
403 CLOSE c_cic;
404 p_govt_citizenship_cd := 9;
405 ELSE
406 CLOSE c_cic;
407 END IF;
408 OPEN c_cnc;
409 FETCH c_cnc INTO p_govt_birth_country_cd;
410 IF c_cnc%NOTFOUND THEN
411 CLOSE c_cnc;
412 p_govt_birth_country_cd := v_birth_country_cd;
413 ELSE
414 CLOSE c_cnc;
415 END IF;
416 OPEN c_lc;
417 FETCH c_lc INTO p_govt_home_language_cd;
418 IF c_lc%NOTFOUND THEN
419 CLOSE c_lc;
420 p_govt_home_language_cd := TO_NUMBER(v_home_language_cd);
421 ELSE
422 CLOSE c_lc;
423 END IF;
424 EXCEPTION
425 WHEN OTHERS THEN
426 IF c_pe%ISOPEN THEN
427 CLOSE c_pe;
428 END IF;
429 IF c_ps%ISOPEN THEN
430 CLOSE c_ps;
431 END IF;
432 IF c_atc%ISOPEN THEN
433 CLOSE c_atc;
434 END IF;
435 IF c_cic%ISOPEN THEN
436 CLOSE c_cic;
437 END IF;
438 IF c_cnc%ISOPEN THEN
439 CLOSE c_cnc;
440 END IF;
441 IF c_lc%ISOPEN THEN
442 CLOSE c_lc;
443 END IF;
444 RAISE;
445 END;
446 EXCEPTION
447 WHEN OTHERS THEN
448 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
449 FND_MESSAGE.SET_TOKEN('NAME','IGSST_GEN_002.stap_get_pcc_pe_dtl');
450 IGS_GE_MSG_STACK.ADD;
451 App_Exception.Raise_Exception;
452 END stap_get_pcc_pe_dtl;
453
454 Procedure Stap_Get_Person_Data(
455 p_person_id IN NUMBER ,
456 p_course_cd IN VARCHAR2 ,
457 p_crv_version_number IN NUMBER ,
458 p_effective_dt IN DATE ,
459 p_commencing_student_ind IN VARCHAR2 DEFAULT 'N',
460 p_logged_ind IN OUT NOCOPY BOOLEAN ,
461 p_s_log_type IN VARCHAR2 ,
462 p_creation_dt IN DATE ,
463 p_birth_dt OUT NOCOPY DATE ,
464 p_sex OUT NOCOPY VARCHAR2 ,
465 p_aborig_torres_cd OUT NOCOPY VARCHAR2 ,
466 p_govt_aborig_torres_cd OUT NOCOPY NUMBER ,
467 p_citizenship_cd OUT NOCOPY VARCHAR2 ,
468 p_govt_citizenship_cd OUT NOCOPY NUMBER ,
469 p_perm_resident_cd OUT NOCOPY VARCHAR2 ,
470 p_govt_perm_resident_cd OUT NOCOPY NUMBER ,
471 p_home_location_cd OUT NOCOPY VARCHAR2 ,
472 p_govt_home_location_cd OUT NOCOPY VARCHAR2 ,
473 p_term_location_cd OUT NOCOPY VARCHAR2 ,
474 p_govt_term_location_cd OUT NOCOPY VARCHAR2 ,
475 p_birth_country_cd OUT NOCOPY VARCHAR2 ,
476 p_govt_birth_country_cd OUT NOCOPY VARCHAR2 ,
477 p_yr_arrival OUT NOCOPY VARCHAR2 ,
478 p_home_language_cd OUT NOCOPY VARCHAR2 ,
479 p_govt_home_language_cd OUT NOCOPY NUMBER ,
480 p_prior_ug_inst OUT NOCOPY VARCHAR2 ,
481 p_govt_prior_ug_inst OUT NOCOPY VARCHAR2 ,
482 p_prior_other_qual OUT NOCOPY VARCHAR2 ,
483 p_prior_post_grad OUT NOCOPY VARCHAR2 ,
484 p_prior_degree OUT NOCOPY VARCHAR2 ,
485 p_prior_subdeg_notafe OUT NOCOPY VARCHAR2 ,
486 p_prior_subdeg_tafe OUT NOCOPY VARCHAR2 ,
487 p_prior_seced_tafe OUT NOCOPY VARCHAR2 ,
488 p_prior_seced_school OUT NOCOPY VARCHAR2 ,
489 p_prior_tafe_award OUT NOCOPY VARCHAR2 ,
490 p_govt_disability OUT NOCOPY VARCHAR2 )
491 AS
492 /*
493 || Created By : [email protected]
494 || Created On : 28-AUG-2000
495 || Purpose :
496 || Known limitations, enhancements or remarks :
497 || Change History :
498 || Who When What
499 || pkpatel 05-MAR-2002 Bug NO: 2224621
500 || Modified GOVT_PRIOR_UG_INST from NUMBER to VARCHAR2. Since its source
501 || IGS_OR_INSTITUTION.GOVT_INSTITUTION_CD is modified from NUMBER to VARCHAR2.
502 || (reverse chronological order - newest change first)
503 */
504 gv_other_detail VARCHAR2(255);
505 BEGIN
506 DECLARE
507 v_other_detail VARCHAR2(255);
508 v_citizenship_cd IGS_ST_GOVT_STDNT_EN.citizenship_cd%TYPE;
509 v_aborig_torres_cd IGS_ST_GOVT_STDNT_EN.aborig_torres_cd%TYPE;
510 v_birth_dt IGS_PE_PERSON.birth_dt%TYPE;
511 v_sex IGS_PE_PERSON.sex%TYPE;
512 v_govt_disability CHAR(8);
513 v_govt_aborig_torres_cd IGS_ST_GOVT_STDNT_EN.govt_aborig_torres_cd%TYPE;
514 v_govt_citizenship_cd IGS_ST_GOVT_STDNT_EN.govt_citizenship_cd%TYPE;
515 v_perm_resident_cd IGS_ST_GOVT_STDNT_EN.perm_resident_cd%TYPE;
516 v_govt_perm_resident_cd IGS_ST_GOVT_STDNT_EN.govt_perm_resident_cd%TYPE;
517 v_home_location IGS_ST_GOVT_STDNT_EN.home_location%TYPE;
518 v_govt_home_location IGS_ST_GOVT_STDNT_EN.govt_home_location%TYPE;
519 v_term_location IGS_ST_GOVT_STDNT_EN.term_location%TYPE;
520 v_govt_term_location IGS_ST_GOVT_STDNT_EN.govt_term_location%TYPE;
521 v_home_location_postcode IGS_PE_STATISTICS.home_location_postcode%TYPE;
522 v_home_location_country IGS_PE_STATISTICS.home_location_country%TYPE;
523 v_term_location_postcode IGS_PE_STATISTICS.term_location_postcode%TYPE;
524 v_term_location_country IGS_PE_STATISTICS.term_location_country%TYPE;
525 v_birth_country_cd IGS_PE_STATISTICS.birth_country_cd%TYPE;
526 v_govt_birth_country_cd IGS_ST_GOVT_STDNT_EN.govt_birth_country_cd%TYPE;
527 v_yr_arrival IGS_ST_GOVT_STDNT_EN.yr_arrival%TYPE;
528 v_home_language_cd IGS_ST_GOVT_STDNT_EN.home_language_cd%TYPE;
529 v_govt_home_language_cd IGS_ST_GOVT_STDNT_EN.govt_home_language_cd%TYPE;
530 v_prior_ug_inst IGS_ST_GOVT_STDNT_EN.prior_ug_inst%TYPE;
531 v_govt_prior_ug_inst IGS_ST_GOVT_STDNT_EN.govt_prior_ug_inst%TYPE;
532 v_prior_other_qual IGS_ST_GOVT_STDNT_EN.prior_other_qual%TYPE;
533 v_prior_post_grad IGS_ST_GOVT_STDNT_EN.prior_post_grad%TYPE;
534 v_prior_degree IGS_ST_GOVT_STDNT_EN.prior_degree%TYPE;
535 v_prior_subdeg_notafe IGS_ST_GOVT_STDNT_EN.prior_subdeg_notafe%TYPE;
536 v_prior_subdeg_tafe IGS_ST_GOVT_STDNT_EN.prior_subdeg_tafe%TYPE;
537 v_prior_seced_tafe IGS_ST_GOVT_STDNT_EN.prior_seced_tafe%TYPE;
538 v_prior_seced_school IGS_ST_GOVT_STDNT_EN.prior_seced_school%TYPE;
539 v_prior_tafe_award IGS_ST_GOVT_STDNT_EN.prior_tafe_award%TYPE;
540 v_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
541 CURSOR c_get_person_dtls IS
542 SELECT pe.birth_dt,
543 pe.sex
544 FROM IGS_PE_PERSON pe
545 WHERE pe.person_id = p_person_id;
546 CURSOR c_prsn_stats IS
547 SELECT ps.aborig_torres_cd,
548 ps.citizenship_cd,
549 ps.perm_resident_cd,
550 ps.home_location_postcode,
551 ps.home_location_country,
552 ps.term_location_postcode,
553 ps.term_location_country,
554 ps.birth_country_cd,
555 ps.yr_arrival,
556 ps.home_language_cd,
557 ps.prior_ug_inst,
558 ps.prior_other_qual,
559 ps.prior_post_grad,
560 ps.prior_degree,
561 ps.prior_subdeg_notafe,
562 ps.prior_subdeg_tafe,
563 ps.prior_seced_tafe,
564 ps.prior_seced_school,
565 ps.prior_tafe_award
566 FROM IGS_PE_STATISTICS ps
567 WHERE ps.person_id = p_person_id AND
568 ps.start_dt <= p_effective_dt AND
569 (ps.end_dt IS NULL OR
570 ps.end_dt >= p_effective_dt)
571 ORDER BY ps.end_dt ASC;
572 CURSOR c_aborig_tsi IS
573 SELECT atcd.govt_aborig_torres_cd
574 FROM IGS_PE_ABORG_TORESCD atcd
575 WHERE atcd.aborig_torres_cd = v_aborig_torres_cd;
576 CURSOR c_citz IS
577 SELECT ccd.govt_citizenship_cd
578 FROM IGS_ST_CITIZENSHP_CD ccd
579 WHERE ccd.citizenship_cd = v_citizenship_cd;
580 CURSOR c_perm_res (
581 cp_perm_res_cd IGS_PE_PERM_RES_CD.perm_resident_cd%TYPE) IS
582 SELECT prcd.govt_perm_resident_cd
583 FROM IGS_PE_PERM_RES_CD prcd
584 WHERE prcd.perm_resident_cd = cp_perm_res_cd;
585 CURSOR c_country_dtls (
586 cp_country_cd IGS_PE_COUNTRY_CD.country_cd%TYPE) IS
587 SELECT ccd.govt_country_cd
588 FROM IGS_PE_COUNTRY_CD ccd
589 WHERE ccd.country_cd = cp_country_cd;
590 CURSOR c_language (
591 cp_language_cd IGS_PE_LANGUAGE_CD.language_cd%TYPE) IS
592 SELECT lcd.govt_language_cd
593 FROM IGS_PE_LANGUAGE_CD lcd
594 WHERE lcd.language_cd = cp_language_cd;
595 CURSOR c_inst (
596 cp_prior_ug_inst IGS_OR_INSTITUTION.institution_cd%TYPE) IS
597 SELECT inst.govt_institution_cd
598 FROM IGS_OR_INSTITUTION inst
599 WHERE inst.institution_cd = cp_prior_ug_inst;
600 PROCEDURE stapl_ins_log_message(
601 p_message_name OUT NOCOPY VARCHAR2,
602 p_logged_ind IN OUT NOCOPY BOOLEAN)
603 AS
604 BEGIN
605 DECLARE
606 BEGIN
607 -- Check if an entry has been written to the error log
608 IF p_logged_ind = FALSE THEN
609 -- Log an error to the IGS_GE_S_ERROR_LOG
610
611 p_logged_ind := TRUE;
612 END IF;
613 -- Create an entry in the System Log Entry
614 IGS_GE_GEN_003.genp_ins_log_entry(
615 p_s_log_type,
616 p_creation_dt,
617 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
618 p_message_name,
619 NULL);
620 END;
621 EXCEPTION
622 WHEN OTHERS THEN
623 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
624 FND_MESSAGE.SET_TOKEN('NAME','IGSST_GEN_002.stapl_ins_log_message');
625 IGS_GE_MSG_STACK.ADD;
626 App_Exception.Raise_Exception;
627 END stapl_ins_log_message;
628 BEGIN -- this routine returns all the person details
629 -- get the person data
630 OPEN c_get_person_dtls;
631 FETCH c_get_person_dtls INTO v_birth_dt,
632 v_sex;
633 -- raise an exception if no person record found
634 IF (c_get_person_dtls%NOTFOUND) THEN
635 CLOSE c_get_person_dtls;
636 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
637 IGS_GE_MSG_STACK.ADD;
638 App_Exception.Raise_Exception;
639 gv_other_detail := 'Parm:'
640 || ' p_person_id-' || TO_CHAR(p_person_id)
641 || ', p_effective_dt-' || IGS_GE_DATE.igschar(p_effective_dt)
642 || ', p_commencing_student_ind-' || p_commencing_student_ind
643 || ', p_s_log_type-' || p_s_log_type
644 || ', p_creation_dt-' || IGS_GE_DATE.igschardt(p_creation_dt)
645 || ', person Data not found for person '|| TO_CHAR(p_person_id);
646 END IF;
647 CLOSE c_get_person_dtls;
648 IF (v_birth_dt IS NULL) THEN
649 IF (p_logged_ind = FALSE) THEN
650 -- log an error to the IGS_GE_S_ERROR_LOG
651 -- using IGS_GE_GEN_003.genp_log_error
652 -- set that an error has been logged
653 p_logged_ind := TRUE;
654 END IF;
655 -- create an entry in the system log entry
656 IGS_GE_GEN_003.genp_ins_log_entry (
657 p_s_log_type,
658 p_creation_dt,
659 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
660 4194,
661 NULL);
662 END IF;
663 IF (v_sex NOT IN('M','F')) THEN
664 --Check if an entry has been written to the error log.
665 IF p_logged_ind = FALSE THEN
666 -- log an error to the IGS_GE_S_ERROR_LOG
667 -- using IGS_GE_GEN_003.genp_log_error
668
669 -- set that an error has been logged
670 p_logged_ind := TRUE;
671 END IF;
672 -- create an entry in the system log entry
673 IGS_GE_GEN_003.genp_ins_log_entry (
674 p_s_log_type,
675 p_creation_dt,
676 'IGS_PE_PERSON,' || TO_CHAR(p_person_id),
677 4195,
678 NULL);
679 --Continue processing this record after the error has been loged.
680 END IF;
681 -- retrieve the person disability
682 v_govt_disability := IGS_ST_GEN_003.stap_get_prsn_dsblty(p_person_id);
683 -- retrieve the person statistics data
684 -- get the first record only, which will
685 -- be the end dated record if one exists
686 OPEN c_prsn_stats;
687 FETCH c_prsn_stats INTO v_aborig_torres_cd,
688 v_citizenship_cd,
689 v_perm_resident_cd,
690 v_home_location_postcode,
691 v_home_location_country,
692 v_term_location_postcode,
693 v_term_location_country,
694 v_birth_country_cd,
695 v_yr_arrival,
696 v_home_language_cd,
697 v_prior_ug_inst,
698 v_prior_other_qual,
699 v_prior_post_grad,
700 v_prior_degree,
701 v_prior_subdeg_notafe,
702 v_prior_subdeg_tafe,
703 v_prior_seced_tafe,
704 v_prior_seced_school,
705 v_prior_tafe_award;
706 IF (c_prsn_stats%NOTFOUND) THEN
707 CLOSE c_prsn_stats;
708 -- Check if an entry has been written to the error log
709 IF (p_logged_ind = FALSE) THEN
710 -- log an error to the IGS_GE_S_ERROR_LOG
711 -- using IGS_GE_GEN_003.genp_log_error
712
713 -- set that an error has been logged
714 p_logged_ind := TRUE;
715 END IF;
716 -- create an entry in the system log entry
717 IGS_GE_GEN_003.genp_ins_log_entry (
718 p_s_log_type,
719 p_creation_dt,
720 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
721 4196,
722 NULL);
723 ELSE
724 CLOSE c_prsn_stats;
725 END IF;
726 -- re-set statistics values and set the government values
727 -- Aboriginal / Torres Strait Islander code
728 IF (v_aborig_torres_cd IS NOT NULL) THEN
729 -- a record will always exist because of
730 -- referential integrity
731 OPEN c_aborig_tsi;
732 FETCH c_aborig_tsi INTO v_govt_aborig_torres_cd;
733 CLOSE c_aborig_tsi;
734 IF v_govt_aborig_torres_cd = 9 THEN
735 -- Check if an entry has been written to the error log
736 IF p_logged_ind = FALSE THEN
737 -- log an error to the IGS_GE_S_ERROR_LOG
738 -- using IGS_GE_GEN_003.genp_log_error
739
740 -- set that an error has been logged
741 p_logged_ind := TRUE;
742 END IF;
743 -- create an entry in the system log entry
744 IGS_GE_GEN_003.genp_ins_log_entry (
745 p_s_log_type,
746 p_creation_dt,
747 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
748 4197,
749 NULL);
750 END IF;
751 ELSE
752 v_aborig_torres_cd := '9';
753 v_govt_aborig_torres_cd := 9;
754 -- Check if an entry has been written to the error log
755 IF p_logged_ind = FALSE THEN
756 -- log an error to the IGS_GE_S_ERROR_LOG
757 -- using IGS_GE_GEN_003.genp_log_error
758
759 -- set that an error has been logged
760 p_logged_ind := TRUE;
761 END IF;
762 -- create an entry in the system log entry
763 IGS_GE_GEN_003.genp_ins_log_entry (
764 p_s_log_type,
765 p_creation_dt,
766 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
767 4197,
768 NULL);
769 END IF;
770 -- citizenship code
771 IF (v_citizenship_cd IS NOT NULL) THEN
772 -- a record will always exist because of
773 -- referential integrity
774 OPEN c_citz;
775 FETCH c_citz INTO v_govt_citizenship_cd;
776 CLOSE c_citz;
777 IF v_govt_citizenship_cd = 9 THEN
778 -- Check if an entry has been written to the error log
779 IF p_logged_ind = FALSE THEN
780 -- log an error to the IGS_GE_S_ERROR_LOG
781 -- using IGS_GE_GEN_003.genp_log_error
782
783 -- set that an error has been logged
784 p_logged_ind := TRUE;
785 END IF;
786 -- create an entry in the system log entry
787 IGS_GE_GEN_003.genp_ins_log_entry (
788 p_s_log_type,
789 p_creation_dt,
790 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
791 4202,
792 NULL);
793 END IF;
794 ELSE
795 v_citizenship_cd := '9';
796 v_govt_citizenship_cd := 9;
797 -- Check if an entry has been written to the error log
798 IF p_logged_ind = FALSE THEN
799 -- log an error to the IGS_GE_S_ERROR_LOG
800 -- using IGS_GE_GEN_003.genp_log_error
801
802 -- set that an error has been logged
803 p_logged_ind := TRUE;
804 END IF;
805 -- create an entry in the system log entry
806 IGS_GE_GEN_003.genp_ins_log_entry (
807 p_s_log_type,
808 p_creation_dt,
809 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
810 4202,
811 NULL);
812 END IF;
813 -- permanent resident code
814 IF (v_perm_resident_cd IS NOT NULL) THEN
815 -- a record will always exist because of
816 -- referential integrity
817 OPEN c_perm_res(
818 v_perm_resident_cd);
819 FETCH c_perm_res INTO v_govt_perm_resident_cd;
820 CLOSE c_perm_res;
821 ELSE
822 IF v_govt_citizenship_cd in (2, 3, 9) THEN
823 v_perm_resident_cd := '9';
824 v_govt_perm_resident_cd := 9;
825 -- Check if an entry has been written to the error log
826 IF p_logged_ind = FALSE THEN
827 -- log an error to the IGS_GE_S_ERROR_LOG
828 -- using IGS_GE_GEN_003.genp_log_error
829
830 -- set that an error has been logged
831 p_logged_ind := TRUE;
832 END IF;
833 -- create an entry in the system log entry
834 IGS_GE_GEN_003.genp_ins_log_entry (
835 p_s_log_type,
836 p_creation_dt,
837 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
838 4203,
839 NULL);
840 ELSE
841 v_perm_resident_cd := '0';
842 v_govt_perm_resident_cd := 0;
843 -- Check if an entry has been written to the error log
844 IF p_logged_ind = FALSE THEN
845 -- log an error to the IGS_GE_S_ERROR_LOG
846 -- using IGS_GE_GEN_003.genp_log_error
847
848 -- set that an error has been logged
849 p_logged_ind := TRUE;
850 END IF;
851 -- create an entry in the system log entry
852 IGS_GE_GEN_003.genp_ins_log_entry (
853 p_s_log_type,
854 p_creation_dt,
855 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
856 4644,
857 NULL);
858 END IF;
859 END IF;
860 -- home location
861 IF (v_home_location_postcode IS NOT NULL) THEN
862 -- when a postcode is used, it must be 4 digits in
863 -- length, therefore, left pad them with zeros to
864 -- make them 4 digits long
865 -- eg. 801 becomes 0801
866 v_home_location := LPAD(TO_CHAR(v_home_location_postcode), 4, '0');
867 v_govt_home_location := LPAD(TO_CHAR(v_home_location_postcode), 4, '0');
868 ELSIF(v_home_location_country IS NOT NULL) THEN
869 v_home_location := v_home_location_country;
870 -- check if v_home_location_country exists on the
871 -- country code table
872 OPEN c_country_dtls(
873 v_home_location_country);
874 FETCH c_country_dtls INTO v_govt_home_location;
875 IF (c_country_dtls%NOTFOUND) THEN
876 -- term location country is a DEETYA value
877 v_govt_home_location := v_home_location_country;
878 END IF;
879 CLOSE c_country_dtls;
880 ELSE
881 v_home_location := '9999';
882 v_govt_home_location := '9999';
883 -- Check if an entry has been written to the error log
884 IF p_logged_ind = FALSE THEN
885 -- log an error to the IGS_GE_S_ERROR_LOG
886 -- using IGS_GE_GEN_003.genp_log_error
887
888 -- set that an error has been logged
889 p_logged_ind := TRUE;
890 END IF;
891 -- create an entry in the system log entry
892 IGS_GE_GEN_003.genp_ins_log_entry (
893 p_s_log_type,
894 p_creation_dt,
895 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
896 4204,
897 NULL);
898 END IF;
899 -- term location
900 IF (v_term_location_postcode IS NOT NULL) THEN
901 -- when a postcode is used, it must be 4 digits in
902 -- length, therefore, left pad them with zeros to
903 -- make them 4 digits long
904 -- eg. 801 becomes 0801
905 v_term_location := LPAD(TO_CHAR(v_term_location_postcode), 4, '0');
906 v_govt_term_location := LPAD(TO_CHAR(v_term_location_postcode), 4, '0');
907 ELSIF (v_term_location_country IS NOT NULL) THEN
908 v_term_location := v_term_location_country;
909 -- check if v_term_location_country exists on the
910 -- country code table
911 OPEN c_country_dtls(
912 v_term_location_country);
913 FETCH c_country_dtls INTO v_govt_term_location;
914 IF (c_country_dtls%NOTFOUND) THEN
915 -- term location country is a DEETYA value
916 v_govt_term_location := v_term_location_country;
917 END IF;
918 CLOSE c_country_dtls;
919 ELSE
920 v_term_location := '9999';
921 v_govt_term_location := '9999';
922 -- Check if an entry has been written to the error log
923 IF p_logged_ind = FALSE THEN
924 -- log an error to the IGS_GE_S_ERROR_LOG
925 -- using IGS_GE_GEN_003.genp_log_error
926
927 -- set that an error has been logged
928 p_logged_ind := TRUE;
929 END IF;
930 -- create an entry in the system log entry
931 IGS_GE_GEN_003.genp_ins_log_entry (
932 p_s_log_type,
933 p_creation_dt,
934 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
935 4205,
936 NULL);
937 END IF;
938 -- country of birth code
939 IF (v_birth_country_cd IS NOT NULL) THEN
940 -- check if v_birth_country_cd exists on the
941 -- country code table
942 OPEN c_country_dtls(v_birth_country_cd);
943 FETCH c_country_dtls INTO v_govt_birth_country_cd;
944 IF (c_country_dtls%NOTFOUND) THEN
945 -- country of birth is a DEETYA value
946 v_govt_birth_country_cd := v_birth_country_cd;
947 END IF;
948 CLOSE c_country_dtls;
949 ELSE
950 v_birth_country_cd := '9999';
951 v_govt_birth_country_cd := '9999';
952 END IF;
953 IF v_govt_birth_country_cd = '9999' THEN
954 -- Check if an entry has been written to the error log
955 IF p_logged_ind = FALSE THEN
956 -- log an error to the IGS_GE_S_ERROR_LOG
957 -- using IGS_GE_GEN_003.genp_log_error
958
959 -- set that an error has been logged
960 p_logged_ind := TRUE;
961 END IF;
962 -- create an entry in the system log entry
963 IGS_GE_GEN_003.genp_ins_log_entry (
964 p_s_log_type,
965 p_creation_dt,
966 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
967 4206,
968 NULL);
969 END IF;
970 -- year of arrival
971 IF (v_yr_arrival IS NULL) THEN
972 v_yr_arrival := 'A9';
973 -- Check if an entry has been written to the error log
974 IF p_logged_ind = FALSE THEN
975 -- log an error to the IGS_GE_S_ERROR_LOG
976 -- using IGS_GE_GEN_003.genp_log_error
977
978 -- set that an error has been logged
979 p_logged_ind := TRUE;
980 END IF;
981 -- create an entry in the system log entry
982 IGS_GE_GEN_003.genp_ins_log_entry (
983 p_s_log_type,
984 p_creation_dt,
985 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
986 4207,
987 NULL);
988 ELSIF v_yr_arrival = 'A8' OR
989 v_yr_arrival = 'A9' THEN
990 -- Check if an entry has been written to the error log
991 IF p_logged_ind = FALSE THEN
992 -- log an error to the IGS_GE_S_ERROR_LOG
993 -- using IGS_GE_GEN_003.genp_log_error
994
995 -- set that an error has been logged
996 p_logged_ind := TRUE;
997 END IF;
998 -- create an entry in the system log entry
999 IGS_GE_GEN_003.genp_ins_log_entry (
1000 p_s_log_type,
1001 p_creation_dt,
1002 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1003 4207,
1004 NULL);
1005 END IF;
1006 -- home language code
1007 IF (v_home_language_cd IS NOT NULL) THEN
1008 -- check if v_home_language_cd exists on the
1009 -- language code table
1010 OPEN c_language(
1011 v_home_language_cd);
1012 FETCH c_language INTO v_govt_home_language_cd;
1013 IF (c_language%NOTFOUND) THEN
1014 -- home langauge is a DEETYA value
1015 v_govt_home_language_cd := TO_NUMBER(v_home_language_cd);
1016 END IF;
1017 CLOSE c_language;
1018 ELSE
1019 v_home_language_cd := '99';
1020 v_govt_home_language_cd := 99;
1021 -- Check if an entry has been written to the error log
1022 IF p_logged_ind = FALSE THEN
1023 -- log an error to the IGS_GE_S_ERROR_LOG
1024 -- using IGS_GE_GEN_003.genp_log_error
1025
1026 -- set that an error has been logged
1027 p_logged_ind := TRUE;
1028 END IF;
1029 -- create an entry in the system log entry
1030 IGS_GE_GEN_003.genp_ins_log_entry (
1031 p_s_log_type,
1032 p_creation_dt,
1033 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1034 4208,
1035 NULL);
1036 END IF;
1037 -- prior undergraduate institution
1038 IF (p_commencing_student_ind = 'N') OR
1039 (p_commencing_student_ind = 'Y' AND
1040 IGS_EN_GEN_008.enrp_get_ug_pg_crs(
1041 p_course_cd,
1042 p_crv_version_number) <> 'PG') THEN
1043 -- not commencing a postgraduate course
1044 v_prior_ug_inst := '0001';
1045 v_govt_prior_ug_inst := '0001';
1046 ELSIF (v_prior_ug_inst IS NOT NULL) THEN
1047 -- check if v_prior_ug_inst exists on the
1048 -- institution code table
1049 OPEN c_inst(
1050 v_prior_ug_inst);
1051 FETCH c_inst INTO v_govt_prior_ug_inst;
1052 IF (c_inst%NOTFOUND) THEN
1053 -- prior undergraduate institution is a DEETYA value
1054 v_govt_prior_ug_inst := v_prior_ug_inst;
1055 END IF;
1056 CLOSE c_inst;
1057 IF v_govt_prior_ug_inst = '9999' THEN
1058 -- Check if an entry has been written to the error log
1059 IF p_logged_ind = FALSE THEN
1060 -- log an error to the IGS_GE_S_ERROR_LOG
1061 -- using IGS_GE_GEN_003.genp_log_error
1062
1063 -- set that an error has been logged
1064 p_logged_ind := TRUE;
1065 END IF;
1066 -- create an entry in the system log entry
1067 IGS_GE_GEN_003.genp_ins_log_entry (
1068 p_s_log_type,
1069 p_creation_dt,
1070 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1071 4209,
1072 NULL);
1073 END IF;
1074 ELSE
1075 v_prior_ug_inst := '9999';
1076 v_govt_prior_ug_inst := '9999';
1077 -- Check if an entry has been written to the error log
1078 IF p_logged_ind = FALSE THEN
1079 -- log an error to the IGS_GE_S_ERROR_LOG
1080 -- using IGS_GE_GEN_003.genp_log_error
1081
1082 -- set that an error has been logged
1083 p_logged_ind := TRUE;
1084 END IF;
1085 -- create an entry in the system log entry
1086 IGS_GE_GEN_003.genp_ins_log_entry (
1087 p_s_log_type,
1088 p_creation_dt,
1089 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1090 4209,
1091 NULL);
1092 END IF;
1093 -- prior other qualification/certificate
1094 IF (p_commencing_student_ind = 'N') THEN
1095 IF v_prior_other_qual IS NULL OR
1096 v_prior_other_qual <> '001' THEN
1097 v_prior_other_qual := '001';
1098 END IF;
1099 END IF;
1100 IF v_prior_other_qual IS NULL OR
1101 v_prior_other_qual = '999' THEN
1102 v_prior_other_qual := '999';
1103 -- Check if an entry has been written to the error log
1104 IF p_logged_ind = FALSE THEN
1105 -- log an error to the IGS_GE_S_ERROR_LOG
1106 -- using IGS_GE_GEN_003.genp_log_error
1107
1108 -- set that an error has been logged
1109 p_logged_ind := TRUE;
1110 END IF;
1111 -- create an entry in the system log entry
1112 IGS_GE_GEN_003.genp_ins_log_entry (
1113 p_s_log_type,
1114 p_creation_dt,
1115 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1116 4210,
1117 NULL);
1118 END IF;
1119 -- prior postgraduate course
1120 IF p_commencing_student_ind = 'N' THEN
1121 IF v_prior_post_grad IS NULL OR
1122 v_prior_post_grad <> '001' THEN
1123 v_prior_post_grad := '001';
1124 END IF;
1125 END IF;
1126 IF v_prior_post_grad IS NULL OR
1127 v_prior_post_grad = '999' THEN
1128 v_prior_post_grad := '999';
1129 -- Check if an entry has been written to the error log
1130 IF p_logged_ind = FALSE THEN
1131 -- log an error to the IGS_GE_S_ERROR_LOG
1132 -- using IGS_GE_GEN_003.genp_log_error
1133
1134 -- set that an error has been logged
1135 p_logged_ind := TRUE;
1136 END IF;
1137 -- create an entry in the system log entry
1138 IGS_GE_GEN_003.genp_ins_log_entry (
1139 p_s_log_type,
1140 p_creation_dt,
1141 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1142 4211,
1143 NULL);
1144 END IF;
1145 -- prior degree
1146 IF p_commencing_student_ind = 'N' THEN
1147 IF v_prior_degree IS NULL OR
1148 v_prior_degree <> '001' THEN
1149 v_prior_degree := '001';
1150 END IF;
1151 END IF;
1152 IF v_prior_degree IS NULL OR
1153 v_prior_degree = '999' THEN
1154 v_prior_degree := '999';
1155 -- Check if an entry has been written to the error log
1156 IF p_logged_ind = FALSE THEN
1157 -- log an error to the IGS_GE_S_ERROR_LOG
1158 -- using IGS_GE_GEN_003.genp_log_error
1159
1160 -- set that an error has been logged
1161 p_logged_ind := TRUE;
1162 END IF;
1163 -- create an entry in the system log entry
1164 IGS_GE_GEN_003.genp_ins_log_entry (
1165 p_s_log_type,
1166 p_creation_dt,
1167 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1168 4212,
1169 NULL);
1170 END IF;
1171 -- prior sub-degree course (not at TAFE)
1172 IF p_commencing_student_ind = 'N' THEN
1173 IF v_prior_subdeg_notafe IS NULL OR
1174 v_prior_subdeg_notafe <> '001' THEN
1175 v_prior_subdeg_notafe := '001';
1176 END IF;
1177 END IF;
1178 IF v_prior_subdeg_notafe IS NULL OR
1179 v_prior_subdeg_notafe = '999' THEN
1180 v_prior_subdeg_notafe := '999';
1181 -- Check if an entry has been written to the error log
1182 IF p_logged_ind = FALSE THEN
1183 -- log an error to the IGS_GE_S_ERROR_LOG
1184 -- using IGS_GE_GEN_003.genp_log_error
1185
1186 -- set that an error has been logged
1187 p_logged_ind := TRUE;
1188 END IF;
1189 -- create an entry in the system log entry
1190 IGS_GE_GEN_003.genp_ins_log_entry (
1191 p_s_log_type,
1192 p_creation_dt,
1193 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1194 4213,
1195 NULL);
1196 END IF;
1197 -- prior sub-degree course
1198 IF p_commencing_student_ind = 'N' THEN
1199 IF v_prior_subdeg_tafe IS NULL OR
1200 v_prior_subdeg_tafe <> '001' THEN
1201 v_prior_subdeg_tafe := '001';
1202 END IF;
1203 END IF;
1204 IF v_prior_subdeg_tafe IS NULL OR
1205 v_prior_subdeg_tafe = '999' THEN
1206 v_prior_subdeg_tafe := '999';
1207 -- Check if an entry has been written to the error log
1208 IF p_logged_ind = FALSE THEN
1209 -- log an error to the IGS_GE_S_ERROR_LOG
1210 -- using IGS_GE_GEN_003.genp_log_error
1211
1212 -- set that an error has been logged
1213 p_logged_ind := TRUE;
1214 END IF;
1215 -- create an entry in the system log entry
1216 IGS_GE_GEN_003.genp_ins_log_entry (
1217 p_s_log_type,
1218 p_creation_dt,
1219 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1220 4214,
1221 NULL);
1222 END IF;
1223 -- prior secondary education course at TAFE
1224 IF p_commencing_student_ind = 'N' THEN
1225 IF v_prior_seced_tafe IS NULL OR
1226 v_prior_seced_tafe <> '001' THEN
1227 v_prior_seced_tafe := '001';
1228 END IF;
1229 END IF;
1230 IF v_prior_seced_tafe IS NULL OR
1231 v_prior_seced_tafe = '999' THEN
1232 v_prior_seced_tafe := '999';
1233 -- Check if an entry has been written to the error log
1234 IF p_logged_ind = FALSE THEN
1235 -- log an error to the IGS_GE_S_ERROR_LOG
1236 -- using IGS_GE_GEN_003.genp_log_error
1237
1238 -- set that an error has been logged
1239 p_logged_ind := TRUE;
1240 END IF;
1241 -- create an entry in the system log entry
1242 IGS_GE_GEN_003.genp_ins_log_entry (
1243 p_s_log_type,
1244 p_creation_dt,
1245 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1246 4215,
1247 NULL);
1248 END IF;
1249 -- prior secondary course at school
1250 IF p_commencing_student_ind = 'N' THEN
1251 IF v_prior_seced_school IS NULL OR
1252 v_prior_seced_school <> '001' THEN
1253 v_prior_seced_school := '001';
1254 END IF;
1255 END IF;
1256 IF v_prior_seced_school IS NULL OR
1257 v_prior_seced_school = '999' THEN
1258 v_prior_seced_school := '999';
1259 -- Check if an entry has been written to the error log
1260 IF p_logged_ind = FALSE THEN
1261 -- log an error to the IGS_GE_S_ERROR_LOG
1262 -- using IGS_GE_GEN_003.genp_log_error
1263
1264 -- set that an error has been logged
1265 p_logged_ind := TRUE;
1266 END IF;
1267 -- create an entry in the system log entry
1268 IGS_GE_GEN_003.genp_ins_log_entry (
1269 p_s_log_type,
1270 p_creation_dt,
1271 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1272 4216,
1273 NULL);
1274 END IF;
1275 -- prior TAFE award course
1276 IF p_commencing_student_ind = 'N' THEN
1277 IF v_prior_tafe_award IS NULL OR
1278 v_prior_tafe_award <> '001' THEN
1279 v_prior_tafe_award := '001';
1280 END IF;
1281 END IF;
1282 IF v_prior_tafe_award IS NULL OR
1283 v_prior_tafe_award = '999' THEN
1284 v_prior_tafe_award := '999';
1285 -- Check if an entry has been written to the error log
1286 IF p_logged_ind = FALSE THEN
1287 -- log an error to the IGS_GE_S_ERROR_LOG
1288 -- using IGS_GE_GEN_003.genp_log_error
1289
1290 -- set that an error has been logged
1291 p_logged_ind := TRUE;
1292 END IF;
1293 -- create an entry in the system log entry
1294 IGS_GE_GEN_003.genp_ins_log_entry (
1295 p_s_log_type,
1296 p_creation_dt,
1297 'IGS_PE_PERSON' || ',' || TO_CHAR(p_person_id),
1298 4217,
1299 NULL);
1300 END IF;
1301 -- setting the output parameters to what these
1302 -- values were set to above
1303 p_birth_dt := v_birth_dt;
1304 p_sex := v_sex;
1305 p_aborig_torres_cd := v_aborig_torres_cd;
1306 p_govt_aborig_torres_cd := v_govt_aborig_torres_cd;
1307 p_citizenship_cd := v_citizenship_cd;
1308 p_govt_citizenship_cd := v_govt_citizenship_cd;
1309 p_perm_resident_cd := v_perm_resident_cd;
1310 p_govt_perm_resident_cd := v_govt_perm_resident_cd;
1311 p_home_location_cd := v_home_location;
1312 p_govt_home_location_cd := v_govt_home_location;
1313 p_term_location_cd := v_term_location;
1314 p_govt_term_location_cd := v_govt_term_location;
1315 p_birth_country_cd := v_birth_country_cd;
1316 p_govt_birth_country_cd := v_govt_birth_country_cd;
1317 p_yr_arrival := v_yr_arrival;
1318 p_home_language_cd := v_home_language_cd;
1319 p_govt_home_language_cd := v_govt_home_language_cd;
1320 p_prior_ug_inst := v_prior_ug_inst;
1321 p_govt_prior_ug_inst := v_govt_prior_ug_inst;
1322 p_prior_other_qual := v_prior_other_qual;
1323 p_prior_post_grad := v_prior_post_grad;
1324 p_prior_degree := v_prior_degree;
1325 p_prior_subdeg_notafe := v_prior_subdeg_notafe;
1326 p_prior_subdeg_tafe := v_prior_subdeg_tafe;
1327 p_prior_seced_tafe := v_prior_seced_tafe;
1328 p_prior_seced_school := v_prior_seced_school;
1329 p_prior_tafe_award := v_prior_tafe_award;
1330 p_govt_disability := v_govt_disability;
1331 END;
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1335 FND_MESSAGE.SET_TOKEN('NAME','IGSST_GEN_002.stap_get_person_data');
1336 IGS_GE_MSG_STACK.ADD;
1337 App_Exception.Raise_Exception;
1338 END stap_get_person_data;
1339
1340 END IGS_ST_GEN_002;
[Home] [Help]