1 PACKAGE BODY IGS_AD_GRD_DATA_LOAD AS
2 /* $Header: IGSAD77B.pls 120.0 2005/06/01 20:44:33 appldev noship $ */
3
4 PROCEDURE admp_vtac_load_address (
5 p_vtac_addr1 IN VARCHAR2,
6 p_vtac_addr2 IN VARCHAR2,
7 p_vtac_addr3 IN VARCHAR2,
8 p_vtac_addr4 IN VARCHAR2,
9 p_vtac_postcode IN NUMBER,
10 p_vtac_home_ph IN VARCHAR2,
11 p_vtac_bus_ph IN VARCHAR2,
12 p_aus_addr_type IN VARCHAR2,
13 p_os_addr_type IN VARCHAR2 );
14
15 FUNCTION admp_vtac_load_stu_off_crs
16 (p_offer_round IN NUMBER,
17 p_override_adm_cat IN VARCHAR2,
18 p_fee_paying_appl_ind IN VARCHAR2,
19 p_fee_paying_hpo IN VARCHAR2,
20 p_offer_letter_req_ind IN VARCHAR2,
21 p_pre_enrol_ind IN VARCHAR2)
22 RETURN BOOLEAN;
23
24 PROCEDURE admp_vtac_load_sec_edu;
25
26 PROCEDURE admp_vtac_load_tert_edu;
27
28
29
30
31
32 -- Inserts a person and alternate person ID record with data from TAC
33 PROCEDURE admp_ins_vtac_offer (
34 errbuf out NOCOPY varchar2,
35 retcode out NOCOPY number,
36 p_file_name IN VARCHAR2 ,
37 p_offer_round IN NUMBER,
38 p_acad_perd IN VARCHAR2,
39 p_adm_perd IN VARCHAR2,
40 p_aus_addr_type IN VARCHAR2,
41 p_os_addr_type IN VARCHAR2,
42 p_alt_person_id_type IN VARCHAR2,
43 p_override_adm_cat IN VARCHAR2,
44 p_fee_payment IN VARCHAR2,
45 p_fee_paying_hpo IN VARCHAR2,
46 p_pre_enrol_ind IN VARCHAR2,
47 p_offer_letter_req_ind IN VARCHAR2,
48 p_org_id IN NUMBER) IS
49
50
51 --Personal Details Section
52 vtac_id_num VARCHAR2(9);
53 vtac_surname VARCHAR2(24);
54 vtac_gname1 VARCHAR2(17);
55 vtac_gname2 VARCHAR2(17);
56 vtac_DateOfBirth VARCHAR2(8);
57 vtac_addr1 VARCHAR2(25);
58 vtac_addr2 VARCHAR2(25);
59 vtac_addr3 VARCHAR2(3);
60 vtac_postcode VARCHAR2(4);
61 vtac_addr4 VARCHAR2(14);
62 vtac_home_ph VARCHAR2(12);
63 vtac_bus_ph VARCHAR2(12);
64 vtac_sex VARCHAR2(1);
65 vtac_category VARCHAR2(3);
66 v_message_name VARCHAR2(30);
67
68 v_initial VARCHAR2(1);
69 message_str VARCHAR2(2000);
70
71 vtac_filehandle UTL_FILE.FILE_TYPE;
72 vtac_filedir VARCHAR2(255);
73 v_last_char VARCHAR2(1);
74
75
76 CURSOR c_api ( cp_api_person_id
77 IGS_PE_ALT_PERS_ID.api_person_id%TYPE, cp_alt_person_id_type
78 IGS_PE_ALT_PERS_ID.person_id_type%TYPE) IS
79 SELECT pe_person_id
80 FROM IGS_PE_ALT_PERS_ID
81 WHERE api_person_id = cp_api_person_id
82 AND person_id_type = cp_alt_person_id_type
83 AND start_dt < SYSDATE
84 AND NVL(end_dt,SYSDATE) >= SYSDATE;
85
86 BEGIN
87 igs_ge_gen_003.set_org_id(p_org_id);
88 retcode := 0;
89
90 -- extract academic calendar
91 p_acad_cal_type := RTRIM(SUBSTR(p_acad_perd, 101, 10));
92 p_acad_seq_num := IGS_GE_NUMBER.TO_NUM(RTRIM(SUBSTR(p_acad_perd, 112, 6)));
93
94 -- extract admission calendar
95 p_adm_cal_type := RTRIM(SUBSTR(p_adm_perd, 101, 10));
96 p_adm_seq_num := IGS_GE_NUMBER.TO_NUM(RTRIM(SUBSTR(p_adm_perd, 112, 6)));
97
98
99 IF (IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
100 p_acad_cal_type,
101 p_acad_seq_num,
102 p_adm_cal_type,
103 p_adm_seq_num,
104 'N') = 'N') THEN
105
106 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_AD_CAL_DOES_NOT_EXIST');
107 retcode := 2;
108 Return;
109
110 END IF;
111
112 --decode p_fee_payment to required value
113 IF (p_fee_payment = 'FEE-PAYING') THEN
114 p_fee_paying_appl_ind := 'Y';
115 ELSIF (p_fee_payment = 'HECS') THEN
116 p_fee_paying_appl_ind := 'N';
117 ELSE
118 p_fee_paying_appl_ind := 'U';
119 END IF;
120
121 --Open the file p_file_name for reading, the directory must be specified in the --(INIT.ORA) using the UTL_FILE_DIR --parameter
122
123 vtac_filedir := nvl(RTRIM(FND_PROFILE.VALUE('IGS_IN_FILE_PATH')),' ');
124 v_last_char := SUBSTR(vtac_filedir,LENGTH(vtac_filedir),1);
125
126 IF v_last_char IN ('/','\') THEN -- '/' To match UNIX & '\' for NT
127 vtac_filedir := SUBSTR(vtac_filedir,1,LENGTH(vtac_filedir)-1);
128 END IF;
129
130
131 vtac_filehandle := UTL_FILE.FOPEN(vtac_filedir, p_file_name, 'r');
132
133
134 --Process Offers
135
136 LOOP -- Main Loop
137 BEGIN
138 UTL_FILE.GET_LINE(vtac_filehandle, G_vtac_output_buffer);
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 EXIT;
142 END;
143
144 -- Commit the changes for the previous student
145
146 IF G_test_only = 'TRUE' THEN
147 ROLLBACK;
148 ELSE
149 COMMIT;
150 END IF;
151
152
153 G_read_number := G_read_number + 1;
154
155 -- Transfer the data to the host variables
156
157 vtac_id_num := RTRIM(SUBSTR(G_vtac_output_buffer,1,9));
158 vtac_surname := RTRIM(SUBSTR(G_vtac_output_buffer,10,24));
159 vtac_gname1 := RTRIM(SUBSTR(G_vtac_output_buffer,34,17));
160 vtac_gname2 := RTRIM(SUBSTR(G_vtac_output_buffer,51,17));
161 vtac_DateOfBirth := RTRIM(SUBSTR(G_vtac_output_buffer,68,8));
162 vtac_addr1 := RTRIM(SUBSTR(G_vtac_output_buffer,76,25));
163 vtac_addr2 := RTRIM(SUBSTR(G_vtac_output_buffer,101,25));
164 vtac_addr3 := RTRIM(SUBSTR(G_vtac_output_buffer,126,3));
165 vtac_addr4 := RTRIM(SUBSTR(G_vtac_output_buffer,129,14));
166 vtac_postcode := RTRIM(SUBSTR(G_vtac_output_buffer,143,4));
167 vtac_home_ph := RTRIM(SUBSTR(G_vtac_output_buffer,147,12));
168 vtac_bus_ph := RTRIM(SUBSTR(G_vtac_output_buffer,159,12));
169 vtac_sex := RTRIM(SUBSTR(G_vtac_output_buffer,171,1));
170
171
172 -- Log the student loaded
173 FND_FILE.PUT_LINE( FND_FILE.LOG,
174 G_read_number||'-'||vtac_id_num||' '||vtac_surname||' '||vtac_DateOfBirth);
175
176
177 -- Check to see if the person already exists on Callista.
178 -- Match on surname, birth date, sex and first initial of first name.
179
180
181 v_initial := SUBSTR(vtac_gname1,1,1);
182
183 -- admp_get_match_prsn module attempts to find a person based on surname,
184 -- birth date, sex and first initial.
185 G_match_person_id := IGS_AD_GEN_007.ADMP_GET_MATCH_PRSN(
186 vtac_surname,
187 vtac_DateOfBirth, -- format 'DDMMYYYY'
188 vtac_sex,
189 v_initial,
190 v_message_name);
191
192 IF (v_message_name IS NOT NULL) THEN
193 -- vtac_DateOfBirth has wrong date format
194 G_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
195 FND_FILE.PUT_LINE( FND_FILE.LOG, vtac_id_num||'.'||RTRIM(G_message_str));
196 END IF;
197
198
199 IF G_match_person_id = 0 THEN
200 OPEN c_api ( vtac_id_num,
201 p_alt_person_id_type);
202 FETCH c_api INTO G_match_person_id;
203 IF (c_api%NOTFOUND) THEN
204 G_match_person_id := 0;
205 END IF;
206
207 CLOSE c_api;
208 END IF;
209
210
211 -- Concatenate First Given Name with Second Given Name
212
213 IF vtac_gname2 IS NOT NULL THEN
214 G_vtac_all_given_names := RTRIM(vtac_gname1||' '||vtac_gname2);
215 END IF;
216
217
218 -- This module uses information from the TAC offer load process
219 -- to create person and alternate person ID records if they
220 -- don't already exist.
221 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_TAC_PRSN(
222 G_match_person_id,
223 vtac_id_num,
224 vtac_surname,
225 G_vtac_all_given_names,
226 vtac_sex,
227 TO_DATE(vtac_DateOfBirth, 'DDMMYYYY'),
228 p_alt_person_id_type,
229 G_new_person_id, -- OUT NOCOPY
230 G_message_str) = FALSE
231
232 THEN
233 ROLLBACK;
234 FND_FILE.PUT_LINE( FND_FILE.LOG,RTRIM(G_message_str));
235 UTL_FILE.FCLOSE(vtac_filehandle);
236 retcode := 2;
237 return;
238 END IF;
239
240
241 --Log the creating record message
242
243 FND_FILE.PUT_LINE( FND_FILE.LOG, RTRIM(G_message_str));
244
245 --Save the ID number we have used so far into a new variable. We can then use this
246 --variable from now on regardless of if the student was new or matched.
247 IF G_match_person_id <> 0 THEN
248 G_current_person_id := G_match_person_id;
249 ELSE
250 G_current_person_id := G_new_person_id;
251 END IF;
252
253
254 -- Load the address
255 admp_vtac_load_address(
256 vtac_addr1,
257 vtac_addr2,
258 vtac_addr3,
259 vtac_addr4,
260 vtac_postcode,
261 vtac_home_ph,
262 vtac_bus_ph,
263 p_aus_addr_type,
264 p_os_addr_type);
265
266 -- Only continue if admission application is created
267
268 IF (admp_vtac_load_stu_off_crs( p_offer_round,
269 p_override_adm_cat,
270 p_fee_paying_appl_ind,
271 p_fee_paying_hpo,
272 p_offer_letter_req_ind,
273 p_pre_enrol_ind) = TRUE) THEN
274
275 -- Load sec edu details and Load ter edu details
276
277 admp_vtac_load_sec_edu;
278 admp_vtac_load_tert_edu;
279
280 ELSE
281
282 IF G_match_person_id = 0 THEN
283 FND_FILE.PUT_LINE(FND_FILE.LOG,G_current_person_id||' '||IGS_GE_GEN_004.GENP_GET_LOOKUP('REPORT','REJTD'));
284 END IF;
285
286 END IF;
287
288 END LOOP; -- End of enormous main Loop.
289
290 -- Commit any changes made for the last student
291 IF G_test_only = 'TRUE' THEN
292 ROLLBACK;
293 ELSE
294 COMMIT;
295 END IF;
296
297 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NUM_OF_RECORD_READ')||'-'||G_read_number);
298
299 UTL_FILE.FCLOSE(vtac_filehandle);
300
301 EXCEPTION
302 WHEN UTL_FILE.INVALID_PATH THEN
303 UTL_FILE.FCLOSE(vtac_filehandle);
304 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INVALID_PATH');
305 retcode := 2;
306
307 WHEN UTL_FILE.INVALID_MODE THEN
308 UTL_FILE.FCLOSE(vtac_filehandle);
309 ERRBUF:= FND_MESSAGE.GET_STRING('IGS', 'IGS_GE_INVALID_MODE');
310 retcode := 2;
311
312 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
313 UTL_FILE.FCLOSE(vtac_filehandle);
314 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INVALID_FILE_HANDLE');
315 retcode := 2;
316
317 WHEN UTL_FILE.INVALID_OPERATION THEN
318 UTL_FILE.FCLOSE(vtac_filehandle);
319 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INVALID_OPER');
320 retcode := 2;
321
322 WHEN UTL_FILE.READ_ERROR THEN
323 UTL_FILE.FCLOSE(vtac_filehandle);
324 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_READ_ERR');
325 retcode := 2;
326
327 WHEN UTL_FILE.WRITE_ERROR THEN
328 UTL_FILE.FCLOSE(vtac_filehandle);
329 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_WRITE_ERR');
330 retcode := 2;
331
332 WHEN UTL_FILE.INTERNAL_ERROR THEN
333 UTL_FILE.FCLOSE(vtac_filehandle);
334 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INTERNAL_ERR');
335 retcode := 2;
336
337 WHEN NO_DATA_FOUND THEN
338 UTL_FILE.FCLOSE(vtac_filehandle);
339 ERRBUF:= IGS_GE_GEN_004.GENP_GET_LOOKUP('REPORT','NO_DATA');
340 retcode := 2;
341
342 WHEN OTHERS THEN
343 UTL_FILE.FCLOSE(vtac_filehandle);
344 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
345 retcode := 2;
346 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
347 END admp_ins_vtac_offer;
348
349
350
351 --Load Address
352
353 PROCEDURE admp_vtac_load_address (
354 p_vtac_addr1 IN VARCHAR2,
355 p_vtac_addr2 IN VARCHAR2,
356 p_vtac_addr3 IN VARCHAR2,
357 p_vtac_addr4 IN VARCHAR2,
358 p_vtac_postcode IN NUMBER,
359 p_vtac_home_ph IN VARCHAR2,
360 p_vtac_bus_ph IN VARCHAR2,
361 p_aus_addr_type IN VARCHAR2,
362 p_os_addr_type IN VARCHAR2 ) IS
363
364 v_message_name VARCHAR2(30);
365 aust_address VARCHAR2(5);
366 message_str VARCHAR2(2000);
367
368
369 BEGIN
370 IF p_vtac_addr3 IS NOT NULL OR p_vtac_addr4 IS NULL THEN
371 aust_address := 'TRUE';
372 ELSE
373 aust_address := 'FALSE';
374 END IF;
375
376 IF aust_address = 'TRUE' THEN
377 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_PERSON_ADDR(
378 G_current_person_id,
379 p_aus_addr_type,
380 SYSDATE,
381 p_vtac_addr1,
382 p_vtac_addr2,
383 p_vtac_addr3,
384 NULL,
385 p_vtac_postcode,
386 NULL,
387 p_vtac_home_ph,
388 p_vtac_bus_ph,
389 v_message_name) = FALSE THEN
390
391 G_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
392 FND_FILE.PUT_LINE( FND_FILE.LOG,G_current_person_id||' '||RTRIM(G_message_str));
393 END IF;
394
395 ELSE
396 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_PERSON_ADDR(
397 G_current_person_id,
398 p_os_addr_type,
399 SYSDATE,
400 p_vtac_addr1,
401 p_vtac_addr2,
402 NULL,
403 p_vtac_addr4,
404 NULL,
405 p_vtac_postcode,
406 p_vtac_home_ph,
407 p_vtac_bus_ph,
408 v_message_name) = FALSE THEN
409
410 G_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
411 FND_FILE.PUT_LINE( FND_FILE.LOG,G_current_person_id||' '||RTRIM(G_message_str));
412 END IF;
413 END IF; --End of if aust_address
414
415 EXCEPTION
416
417 WHEN OTHERS THEN
418 RAISE;
419 END admp_vtac_load_address; --end of load address
420
421
422
423 --Load the students offered courses
424 FUNCTION admp_vtac_load_stu_off_crs
425 (p_offer_round IN NUMBER,
426 p_override_adm_cat IN VARCHAR2,
427 p_fee_paying_appl_ind IN VARCHAR2,
428 p_fee_paying_hpo IN VARCHAR2,
429 p_offer_letter_req_ind IN VARCHAR2,
430 p_pre_enrol_ind IN VARCHAR2)
431 RETURN BOOLEAN IS
432
433 vtac_category VARCHAR2(3);
434
435 --Course preference section (13)
436 vtac_inco VARCHAR2(5);
437 vtac_offer_stream VARCHAR2(1);
438 vtac_rank VARCHAR2(5);
439 vtac_orig_rank VARCHAR2(4);
440 vtac_offer_status VARCHAR2(1);
441 vtac_offer_round VARCHAR2(1);
442 vtac_special_admissions VARCHAR2(1);
443
444 valid_pref VARCHAR2(5) := 'FALSE';
445 course_cd VARCHAR2(80);
446 attendence_mode VARCHAR2(80);
447 admission_cat VARCHAR2(80);
448 admission_cd VARCHAR2(80);
449 basis_for_admission_type VARCHAR2(80);
450 return_type VARCHAR2(80);
451 tac_course_match_ind VARCHAR2(80);
452 message_str VARCHAR2(2000);
453 v_message_name VARCHAR2(30);
454
455 TYPE pref_str_table IS TABLE OF VARCHAR2( 25)
456 INDEX BY BINARY_INTEGER;
457 pref_str pref_str_table;
458 j BINARY_INTEGER := 1;
459 i NUMBER := 208;
460 k NUMBER := 1;
461
462 BEGIN
463
464 G_ret_val := 0;
465
466 vtac_category := RTRIM(SUBSTR(G_vtac_output_buffer,172,3));
467
468 -- Get the data we need for inserting adm_course_appl record
469
470 -- This module finds the user defined admission code and basis for
471 -- admission type from the admission code table.
472 IF (IGS_AD_GEN_003.ADMP_GET_AC_BFA(
473 vtac_category,
474 admission_cd,
475 basis_for_admission_type,
476 v_message_name) = FALSE) THEN
477
478 G_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
479 FND_FILE.PUT_LINE( FND_FILE.LOG,
480 RTRIM (G_message_str)||' '||vtac_category);
481 RETURN FALSE;
482 ROLLBACK;
483 END IF;
484
485 admission_cd := RTRIM(admission_cd);
486 basis_for_admission_type := RTRIM(basis_for_admission_type);
487
488 --Transfer the course preference section data from the VTAC file to the
489 --PL/SQL table of strings
490
491 WHILE i < 442
492 LOOP
493 pref_str(j) := RTRIM(SUBSTR(G_vtac_output_buffer,i,18));
494 i := i + 18;
495 j := j + 1;
496 END LOOP;
497
498
499 -- Loop through the preferences looking for Deakin Courses(s). Validate that
500 -- this is an actual course.
501
502
503 -- << pref_loop >>
504 WHILE k <=13
505 LOOP
506 vtac_inco := RTRIM(SUBSTR(pref_str(k),1,5));
507 vtac_offer_status := RTRIM(SUBSTR(pref_str(k),16,1));
508 vtac_offer_round := RTRIM(SUBSTR(pref_str(k),17,1));
509
510 IF vtac_inco IS NULL THEN
511 GOTO pref_loop; --Do not process this record, go to the end of the while loop
512 END IF;
513
514
515 -- We only want to process for offered courses for the correct offer round
516 IF vtac_offer_status = 'O' AND vtac_offer_round =SUBSTR(p_offer_round,1,1)
517 THEN
518
519
520 -- Get the data we need for inserting an admission_appl
521
522 IF p_override_adm_cat IS NOT NULL THEN
523 admission_cat := p_override_adm_cat;
524 END IF;
525
526 G_ret_val := 0;
527 -- Inserts TAC details to form an admission course
528 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_TAC_COURSE(
529 p_acad_cal_type,
530 p_acad_seq_num,
531 p_adm_cal_type,
532 p_adm_seq_num,
533 admission_cat,
534 NULL, -- fee category
535 NULL, -- enrolment cat
536 NULL, -- correspondence cat
537 G_current_person_id,
538 vtac_inco, -- match_course,
539 1, -- preference number
540 SYSDATE, -- application date
541 SYSDATE, -- offer date
542 basis_for_admission_type,
543 admission_cd,
544 p_fee_paying_appl_ind,
545 p_fee_paying_hpo,
546 p_offer_letter_req_ind,
547 -- VTAC produces offer letter
548 -- on Deakin's behalf
549 p_pre_enrol_ind,
550 course_cd, -- OUT NOCOPY
551 tac_course_match_ind, -- OUT NOCOPY
552 return_type, -- OUT NOCOPY
553 v_message_name) = FALSE
554 THEN
555 G_message_str :=
556 FND_MESSAGE.GET_STRING('IGS',
557 v_message_name);
558 G_ret_val := 1;
559 END IF;
560
561
562
563 course_cd := RTRIM(course_cd);
564 tac_course_match_ind := RTRIM(tac_course_match_ind);
565 return_type := RTRIM(return_type);
566 IF G_ret_val = 1 THEN
567 G_message_str := RTRIM(G_message_str);
568
569 IF return_type = 'W' THEN
570
571 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_WARNING_APPLICANT')||' '||
572 G_current_person_id||'-'||course_cd||'-'||vtac_inco||'.'||
573 G_message_str||'-'||
574 FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADPS_APPL_INST_CREATIN'));
575 valid_pref := 'TRUE';
576
577 ELSE
578
579 -- Process next offer if no TAC match
580
581 IF tac_course_match_ind = 'N' THEN
582 --Do not process this record, go to the end of the while loop
583 GOTO pref_loop;
584 END IF;
585
586 --There must have been an error inserting
587
588 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_ERR_APPLICANT')||' '||
589 G_current_person_id||'-'||course_cd||'-'||vtac_inco||'.'||
590 G_message_str||'-'||
591 FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADPS_APPL_INST_CREATIN'));
592
593 RETURN FALSE;
594 ROLLBACK;
595
596 END IF; --End of if return_type
597
598 ELSE
599 valid_pref := 'TRUE';
600
601 END IF; -- End of if G_ret_val -- Course offered and correct round
602
603 END IF; -- End of if vtac_offer_status
604
605 << pref_loop >>
606 k := k+1;
607 END LOOP; --End of while loop
608
609
610 IF valid_pref = 'FALSE' THEN
611 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_VALID_PREFERENCES')||' '||
612 G_current_person_id);
613 END IF;
614
615
616 RETURN TRUE;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 RAISE;
621
622 END admp_vtac_load_stu_off_crs; -- End of Load the students offered courses.
623
624 -- Load secondary education details
625 PROCEDURE admp_vtac_load_sec_edu IS
626
627 --Secondary level qualifications (4)
628
629
630 vtac_qual_year VARCHAR2(4);
631 vtac_qual_type VARCHAR2(1);
632 vtac_qual_school VARCHAR2(3);
633 vtac_state_name VARCHAR2(24);
634
635
636 --Results (24)
637
638 vtac_result_year VARCHAR2(4);
639 vtac_s_result_year VARCHAR2(4);
640 vtac_subject_code VARCHAR2(2);
641 vtac_subject_type VARCHAR2(1);
642 vtac_gl_score NUMBER(2);
643 vtac_est_gl_score VARCHAR2(2);
644 vtac_fact_gl_score VARCHAR2(2);
645 vtac_old_mark VARCHAR2(3);
646 vtac_old_grade VARCHAR2(1);
647 vtac_unit_res_1 VARCHAR2(1);
648 vtac_unit_res_2 VARCHAR2(1);
649 vtac_unit_res_3 VARCHAR2(1);
650 vtac_unit_res_4 VARCHAR2(1);
651 vtac_average_cat_grade VARCHAR2(2);
652 vtac_s_ter VARCHAR2(5);
653 vtac_s_est_ter VARCHAR2(5);
654 vtac_s_factor_ter VARCHAR2(5);
655
656 vtac_study_count NUMBER(2);
657
658 result_yr NUMBER(4);
659 study_cnt NUMBER(2);
660 sub_result_yr NUMBER(4);
661 score NUMBER(5,2);
662 sub_result VARCHAR2(3);
663 ase_sequence_number NUMBER(10);
664
665 TYPE sec_edu_str_table IS TABLE OF VARCHAR2(80)
666 INDEX BY BINARY_INTEGER;
667 sec_edu_str sec_edu_str_table;
668 j BINARY_INTEGER := 1;
669 i NUMBER := 1050;
670 k NUMBER := 1;
671
672 message_str VARCHAR2(2000);
673 v_message_name VARCHAR2(30);
674 v_dummy NUMBER;
675
676 CURSOR c_dup_chk is
677 SELECT 1
678 FROM IGS_AD_AUS_SEC_ED_SU ases
679 WHERE ases.person_id = G_current_person_id and
680 ases.ase_sequence_number = ase_sequence_number and
681 ases.subject_result_yr = sub_result_yr and
682 ases.subject_cd = vtac_subject_code;
683
684 BEGIN
685
686 -- Take state_cd, ass_type and secondary school code data from the
687 --first record of secondary level qualifications (sec_qual)
688 --provided in the input file.
689
690 vtac_state_name := RTRIM(SUBSTR(G_vtac_output_buffer,608,24));
691
692 -- Check if the qualification is from a state of Australia.
693
694 IF SUBSTR(vtac_state_name,1,3) IN ('VIC','NSW','ACT','QLD','TAS') OR
695 SUBSTR(vtac_state_name,1,2) IN ('NT','WA','SA') THEN
696 G_aus_edu := 'TRUE';
697
698 ELSE
699 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_SEC_QUALIFICATION')
700 ||'-'||G_current_person_id);
701 G_aus_edu := 'FALSE';
702 END IF;
703
704 IF G_aus_edu = 'TRUE' THEN
705
706 vtac_qual_type := RTRIM(SUBSTR(G_vtac_output_buffer,604,1));
707 vtac_qual_school := RTRIM(SUBSTR(G_vtac_output_buffer,605,3));
708
709
710 --Take year and TER score data from the first record of result
711 --summary (result) provided in the input file. See below.
712
713 vtac_s_result_year := RTRIM(SUBSTR(G_vtac_output_buffer,1050,4));
714
715 -- Only insert aus scn edu if the result year is known
716
717 IF vtac_s_result_year IS NOT NULL THEN
718
719 result_yr := IGS_GE_NUMBER.TO_NUM(vtac_s_result_year);
720 vtac_s_ter := RTRIM(SUBSTR(G_vtac_output_buffer,497,5));
721 score := IGS_GE_NUMBER.TO_NUM(vtac_s_ter);
722
723 IF score = 0 THEN
724 vtac_s_est_ter := RTRIM(SUBSTR(G_vtac_output_buffer,502,5));
725 score := IGS_GE_NUMBER.TO_NUM(vtac_s_est_ter);
726 END IF;
727
728 IF score = 0 THEN
729 vtac_s_factor_ter := RTRIM(SUBSTR(G_vtac_output_buffer,507,5));
730 score := IGS_GE_NUMBER.TO_NUM(vtac_s_factor_ter);
731 END IF;
732
733 score := score/100;
734
735 G_ret_val := 0;
736 -- This procedure inserts a new aus_scndry_education record
737 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_AUS_SCN_EDU(
738 G_current_person_id,
739 result_yr,
740 score,
741 vtac_state_name,
742 NULL,
743 vtac_qual_type,
744 vtac_qual_school,
745 ase_sequence_number, -- OUT NOCOPY
746 v_message_name) = FALSE
747 THEN
748 G_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
749 FND_FILE.PUT_LINE( FND_FILE.LOG,
750 G_message_str||' '||G_current_person_id||
751 ' '||vtac_state_name);
752 END IF;
753
754 --Load the secondary education subjects if any exist.
755
756 vtac_study_count := RTRIM(SUBSTR(G_vtac_output_buffer,1048,2));
757 study_cnt := IGS_GE_NUMBER.TO_NUM(vtac_study_count);
758
759 IF study_cnt > 0 THEN
760
761 --Transfer the secondary education subjects data form the VTAC file to the
762 -- PL/SQL table of strings
763
764 WHILE i <= 2658
765 LOOP
766 sec_edu_str(j) := RTRIM(SUBSTR(G_vtac_output_buffer,i,67));
767 i := i + 67;
768 j := j + 1;
769 END LOOP;
770
771
772 WHILE k <= study_cnt
773
774 LOOP
775 vtac_result_year := RTRIM(SUBSTR(sec_edu_str(k),1,4));
776 IF vtac_result_year IS NULL THEN
777 -- Do not process this record, go to end of the while loop
778 GOTO sec_edu_loop;
779 END IF;
780
781 sub_result_yr := IGS_GE_NUMBER.TO_NUM(vtac_result_year);
782
783 -- Work out NOCOPY mark from possible mark fields
784
785 vtac_gl_score := RTRIM(SUBSTR(sec_edu_str(k),8,2));
786 vtac_subject_code := RTRIM(SUBSTR(sec_edu_str(k),5,2));
787 vtac_subject_type := RTRIM(SUBSTR(sec_edu_str(k),7,1));
788
789 sub_result := RTRIM(vtac_gl_score);
790
791 IF sub_result IS NULL THEN
792 vtac_est_gl_score := RTRIM(SUBSTR(sec_edu_str(k),10 ,2));
793 sub_result := vtac_est_gl_score;
794 END IF;
795
796 IF sub_result IS NULL THEN
797 vtac_fact_gl_score := RTRIM(SUBSTR(sec_edu_str(k),12,2));
798 sub_result := vtac_fact_gl_score;
799 END IF;
800
801
802 G_ret_val := 0;
803 --check for duplicate record
804 OPEN c_dup_chk;
805 FETCH c_dup_chk into v_dummy;
806 IF (c_dup_chk%NOTFOUND) THEN
807 -- Insert an Australian secondary education subject record
808 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_AUS_SCN_SUB(
809 G_current_person_id,
810 ase_sequence_number,
811 sub_result_yr,
812 vtac_subject_code,
813 NULL,
814 sub_result,
815 NULL,
816 NULL,
817 vtac_subject_type,
818 NULL,
819 v_message_name) = FALSE
820 THEN
821 G_message_str :=FND_MESSAGE.GET_STRING('IGS',v_message_name);
822 FND_FILE.PUT_LINE( FND_FILE.LOG,RTRIM(G_message_str) ||' '||G_current_person_id
823 ||' '||vtac_state_name||' '||vtac_subject_code);
824 END IF;
825 ELSE
826 G_message_str := FND_MESSAGE.GET_STRING('IGS','IGS_GE_DUPLICATE_VALUE')||'-'
827 ||IGS_GE_NUMBER.TO_CANN(sub_result_yr) ;
828 FND_FILE.PUT_LINE( FND_FILE.LOG,RTRIM(G_message_str) ||'-'||G_current_person_id
829 ||'-'||vtac_state_name||'-'||vtac_subject_code);
830
831 END IF; -- End of if c_dup_chk%NOTFOUND
832
833 CLOSE c_dup_chk;
834
835 <<sec_edu_loop>>
836 k := k + 1;
837 END LOOP; -- End sec_edu_loop while loop
838
839 END IF; -- End of if study_cnt
840
841 END IF; --End of vtac result year not null
842
843 END IF; -- End of if G_aus_edu
844
845 EXCEPTION
846
847 WHEN OTHERS THEN
848 RAISE;
849
850 END admp_vtac_load_sec_edu; --End of Load sec edu details
851
852 --Load tert edu details
853
854 PROCEDURE admp_vtac_load_tert_edu IS
855
856 --Post Secondary levels qualifications Australia
857
858 vtac_post_sec_started VARCHAR2(4);
859 vtac_post_sec_completed VARCHAR2(4);
860 vtac_post_sec_level VARCHAR2(2);
861 vtac_post_sec_inst_code VARCHAR2(4);
862 vtac_post_sec_inst_name VARCHAR2(12);
863 vtac_post_sec_student_id VARCHAR2(10);
864 vtac_post_sec_compl VARCHAR2(1);
865 vtac_post_sec_gpa VARCHAR2(3);
866
867
868 --Post Secondary levels qualifications Overseas
869
870 vtac_post_sec_syear_ov VARCHAR2(4);
871 vtac_post_sec_cyear_ov VARCHAR2(4);
872 vtac_post_sec_inst_ov VARCHAR2(17);
873 vtac_post_sec_cntry_ov VARCHAR2(12);
874 vtac_post_sec_level_ov VARCHAR2(2);
875 vtac_post_sec_ccode_ov VARCHAR2(1);
876
877
878 institution_cd VARCHAR2(80);
879 notes VARCHAR2(80);
880
881 start_yr NUMBER(4);
882 end_yr NUMBER(4);
883 gpa NUMBER(3,2);
884
885 v_message_name VARCHAR2(30);
886 v_inserted_ind VARCHAR2(1);
887 message_str VARCHAR2(2000);
888
889 TYPE post_sec_str_table IS TABLE OF VARCHAR2(50)
890 INDEX BY BINARY_INTEGER;
891 po_sec_str post_sec_str_table;
892
893 TYPE po_sec_ov_str_table IS TABLE OF VARCHAR2(50)
894 INDEX BY BINARY_INTEGER;
895 po_sec_ov_str po_sec_ov_str_table;
896
897 j BINARY_INTEGER := 1;
898 i NUMBER := 728;
899 k NUMBER := 1;
900
901
902 BEGIN
903
904 --Transfer the course post secondary level qualification data from the VTAC file to
905 --the PL/SQL table of strings
906
907 WHILE i < 888
908 LOOP
909 po_sec_str(j) := RTRIM(SUBSTR(G_vtac_output_buffer,i,40));
910 i := i + 40;
911 j := j + 1;
912 END LOOP;
913
914
915 --Loop through the Post secondary level qualification in Australia
916
917
918 WHILE k <= 4
919 LOOP
920 vtac_post_sec_compl := RTRIM(SUBSTR(po_sec_str(k),37,1));
921
922 IF nvl(vtac_post_sec_compl,' ') = ' ' THEN
923 --Do not process the record, got to end of the loop
924 GOTO po_sec_loop;
925 END IF;
926
927
928 --Do Y2K stuff for start and end years
929
930 vtac_post_sec_started := RTRIM(SUBSTR(po_sec_str(k),1,4));
931 vtac_post_sec_completed := RTRIM(SUBSTR(po_sec_str(k),5,4));
932
933 start_yr := IGS_GE_NUMBER.TO_NUM(vtac_post_sec_started);
934 end_yr := IGS_GE_NUMBER.TO_NUM(vtac_post_sec_completed);
935
936 IF start_yr > 50 THEN
937 start_yr := start_yr + 1900;
938 ELSE
939 start_yr := start_yr + 2000;
940 END IF;
941
942 IF end_yr > 50 THEN
943 end_yr := end_yr + 1900;
944 ELSE
945 end_yr := end_yr + 2000;
946 END IF;
947
948 -- Get the GPA in a format we can use. It needs 2 decimal places
949
950 vtac_post_sec_gpa := RTRIM(SUBSTR(po_sec_str(k),38,3));
951 gpa := IGS_GE_NUMBER.TO_NUM(vtac_post_sec_gpa)/100;
952
953 vtac_post_sec_student_id := RTRIM(SUBSTR(po_sec_str(k),27,10));
954 vtac_post_sec_inst_code := RTRIM(SUBSTR(po_sec_str(k),11,4));
955 vtac_post_sec_inst_name := RTRIM(SUBSTR(po_sec_str(k),15,12));
956
957 G_ret_val := 0;
958 -- Insert a tertiary education record
959 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_TERT_EDU(
960 G_current_person_id,
961 'N',
962 vtac_post_sec_compl,
963 start_yr,
964 vtac_post_sec_inst_code,
965 end_yr,
966 gpa,
967 NULL,
968 NULL,
969 vtac_post_sec_inst_name,
970 NULL,
971 vtac_post_sec_student_id,
972 NULL,
973 NULL,
974 NULL,
975 NULL,
976 NULL,
977 NULL,
978 NULL,
979 NULL,
980 NULL,
981 NULL,
982 v_message_name,
983 v_inserted_ind) = FALSE THEN
984
985 G_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
986 FND_FILE.PUT_LINE( FND_FILE.LOG,G_current_person_id||' '||RTRIM(G_message_str));
987 END IF;
988
989 << po_sec_loop >>
990 k := k + 1;
991 END LOOP; --End po_sec_loop while loop
992
993
994
995 --Transfer the course post secondary level qualification Overseas data from the
996 --VTAC file to the PL/SQL table of strings
997
998 i := 888;
999 j := 1;
1000 k := 1;
1001 WHILE i < 1048
1002 LOOP
1003 po_sec_ov_str(j) := RTRIM(SUBSTR(G_vtac_output_buffer,i,40));
1004 i := i + 40;
1005 j := j + 1;
1006 END LOOP;
1007
1008
1009
1010 -- Loop through the Post Secondary level qualification Overseas
1011
1012
1013
1014 WHILE k <= 4
1015 LOOP
1016 -- Find the level of completion
1017 vtac_post_sec_ccode_ov := RTRIM(SUBSTR(po_sec_ov_str(k),40,1));
1018
1019 IF nvl(vtac_post_sec_ccode_ov,' ') = ' ' THEN
1020 --Do not process the current record, go to end of the loop
1021 GOTO po_sec_ov_loop;
1022 END IF;
1023
1024
1025 --Do Y2K stuff for start and end years
1026
1027 vtac_post_sec_syear_ov := RTRIM(SUBSTR(po_sec_ov_str(k),1,4));
1028 vtac_post_sec_cyear_ov := RTRIM(SUBSTR(po_sec_ov_str(k),5,4));
1029
1030 start_yr := IGS_GE_NUMBER.TO_NUM(vtac_post_sec_syear_ov);
1031 end_yr := IGS_GE_NUMBER.TO_NUM(vtac_post_sec_cyear_ov);
1032
1033 IF start_yr > 50 THEN
1034 start_yr := start_yr + 1900;
1035 ELSE
1036 start_yr := start_yr + 2000;
1037 END IF;
1038
1039 IF end_yr > 50 THEN
1040 end_yr := end_yr + 1900;
1041 ELSE
1042 end_yr := end_yr + 2000;
1043 END IF;
1044
1045 --We can't insert the overseas institution codes so we will
1046 --store the institution and the country in the notes field.
1047 --This should provide some extra info for the users.USES_ASE_FK
1048
1049 vtac_post_sec_inst_ov := RTRIM(SUBSTR(po_sec_ov_str(k),9,17));
1050 vtac_post_sec_cntry_ov := RTRIM(SUBSTR(po_sec_ov_str(k),26,12));
1051 notes := vtac_post_sec_inst_ov||' , '||vtac_post_sec_cntry_ov;
1052
1053
1054 G_ret_val := 0;
1055 -- Insert a tertiary education record
1056 IF (IGS_AD_PRC_TAC_OFFER.ADMP_INS_TERT_EDU(
1057 G_current_person_id,
1058 'N',
1059 vtac_post_sec_ccode_ov,
1060 start_yr,
1061 NULL,
1062 end_yr,
1063 NULL,
1064 NULL,
1065 NULL,
1066 notes,
1067 NULL,
1068 NULL,
1069 NULL,
1070 NULL,
1071 NULL,
1072 NULL,
1073 NULL,
1074 NULL,
1075 NULL,
1076 NULL,
1077 NULL,
1078 NULL,
1079 v_message_name,
1080 v_inserted_ind) = FALSE) THEN
1081
1082 G_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
1083 FND_FILE.PUT_LINE( FND_FILE.LOG,G_current_person_id||' '||RTRIM(G_message_str));
1084
1085 END IF;
1086 <<po_sec_ov_loop>>
1087 k := k + 1;
1088 END LOOP; --End of po_sec_ov_loop while loop
1089
1090 EXCEPTION
1091
1092 WHEN OTHERS THEN
1093 RAISE;
1094 END admp_vtac_load_tert_edu; -- End of Load ter edu details
1095
1096
1097 END IGS_AD_GRD_DATA_LOAD;
[Home] [Help]