1 PACKAGE BODY IGS_AD_PG_DATA_LOAD AS
2 /* $Header: IGSAD78B.pls 115.9 2003/09/04 06:46:28 akadam ship $ */
3 PROCEDURE admp_vtac_pg_load_address (
4 p_vtac_street_name IN VARCHAR2,
5 p_vtac_suburb IN VARCHAR2,
6 p_vtac_state IN VARCHAR2,
7 p_vtac_country IN VARCHAR2,
8 p_vtac_postcode IN NUMBER,
9 p_vtac_home_ph IN VARCHAR2,
10 p_vtac_bus_ph IN VARCHAR2,
11 p_aus_addr_type IN VARCHAR2,
12 p_os_addr_type IN VARCHAR2);
13
14 FUNCTION admp_vtac_pg_load_stu_off_crs (p_offer_round IN NUMBER,
15 p_override_adm_cat IN VARCHAR2,
16 p_offer_letter_req_ind IN VARCHAR2,
17 p_pre_enrol_ind IN VARCHAR2,
18 basis_for_admission_type IN VARCHAR2)
19
20
21 RETURN BOOLEAN;
22
23 PROCEDURE admp_vtac_pg_load_tert_edu;
24
25 PROCEDURE admp_ins_vtac_pg_off (
26 errbuf out NOCOPY varchar2,
27 retcode out NOCOPY number,
28 p_file_name IN VARCHAR2 ,
29 p_offer_round IN NUMBER,
30 p_acad_perd IN VARCHAR2,
31 p_adm_perd IN VARCHAR2,
32 p_aus_addr_type IN VARCHAR2,
33 p_os_addr_type IN VARCHAR2,
34 p_alt_person_id_type IN VARCHAR2,
35 p_override_adm_cat IN VARCHAR2,
36 p_pre_enrol_ind IN VARCHAR2,
37 p_offer_letter_req_ind IN VARCHAR2,
38 p_org_id IN NUMBER) IS
39
40 --Personal Details Section
41
42 vtac_id_num VARCHAR2(9);
43 vtac_surname VARCHAR2(24);
44 vtac_gname1 VARCHAR2(17);
45 vtac_gname2 VARCHAR2(17);
46 vtac_DateOfBirth VARCHAR2(8);
47 vtac_street_name VARCHAR2(25);
48 vtac_suburb VARCHAR2(25);
49 vtac_state VARCHAR2(3);
50 vtac_postcode VARCHAR2(4);
51 vtac_country VARCHAR2(14);
52 vtac_home_ph VARCHAR2(12);
53 vtac_bus_ph VARCHAR2(12);
54 vtac_sex VARCHAR2(1);
55 vtac_prev_surname VARCHAR2(24);
56 vtac_prev_gname1 VARCHAR2(17);
57 vtac_prev_gname2 VARCHAR2(17);
58 vtac_category VARCHAR2(3);
59 vtac_residency VARCHAR2(1);
60 v_message_name VARCHAR2(30);
61 message_str VARCHAR2(2000);
62 v_initial VARCHAR2(1);
63 admission_cd VARCHAR2(80);
64 basis_for_admission_type VARCHAR2(80);
65 v_last_char VARCHAR2(1);
66
67 vtac_pg_filehandle UTL_FILE.FILE_TYPE;
68 vtac_filedir VARCHAR2(100);
69 CURSOR c_api (cp_api_person_id IGS_PE_ALT_PERS_ID.api_person_id%TYPE,
70 cp_alt_person_id_type IGS_PE_ALT_PERS_ID.person_id_type%TYPE) IS
71 SELECT pe_person_id
72 FROM IGS_PE_ALT_PERS_ID
73 WHERE api_person_id = cp_api_person_id
74 AND person_id_type = cp_alt_person_id_type
75 AND start_dt < SYSDATE
76 AND NVL(end_dt,SYSDATE) >= SYSDATE;
77
78 BEGIN
79
80 retcode := 0;
81 igs_ge_gen_003.set_org_id(p_org_id);
82
83 -- extract academic calendar
84 p_acad_cal_type := RTRIM(SUBSTR(p_acad_perd, 101, 10));
85 p_acad_seq_num := IGS_GE_NUMBER.TO_NUM(RTRIM(SUBSTR(p_acad_perd, 112, 6)));
86
87 -- extract admission calendar
88 p_adm_cal_type := RTRIM(SUBSTR(p_adm_perd, 101, 10));
89 p_adm_seq_num := IGS_GE_NUMBER.TO_NUM(RTRIM(SUBSTR(p_adm_perd, 112, 6)));
90
91
92
93 IF (IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
94 p_acad_cal_type,
95 p_acad_seq_num,
96 p_adm_cal_type,
97 p_adm_seq_num,
98 'N') = 'N') THEN
99
100 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_AD_CAL_DOES_NOT_EXIST');
101 retcode := 2;
102 Return;
103 END IF;
104
105 -- The VTAC admission code E41 is assumed for all students. There is
106 -- no value given in the file.
107
108 G_pg_ret_val := 0;
109 --Get the data we need for inserting adm_course_appl record
110
111 IF (IGS_AD_GEN_003.ADMP_GET_AC_BFA(
112 'E41',
113 admission_cd,
114 basis_for_admission_type,
115 v_message_name) = FALSE) THEN
116
117 G_pg_message_str := FND_MESSAGE.GET_STRING('IGS',v_message_name);
118 FND_FILE.PUT_LINE( FND_FILE.LOG,vtac_id_num||'.'||RTRIM(G_pg_message_str));
119 ROLLBACK;
120 RETCODE := 2;
121 RETURN;
122 END IF;
123
124 admission_cd := RTRIM(admission_cd);
125 basis_for_admission_type := RTRIM(basis_for_admission_type);
126
127
128
129 -- Open the file p_file_name for reading, the directory must be specified in the -- instance parameter initialization file(INIT.ORA) using the UTL_FILE_DIR
130 -- parameter
131
132 vtac_filedir := nvl(RTRIM(FND_PROFILE.VALUE('IGS_IN_FILE_PATH')),' ');
133 v_last_char := SUBSTR(vtac_filedir,LENGTH(vtac_filedir),1);
134
135 IF v_last_char IN ('/','\') THEN -- '/' To match UNIX & '\' for NT
136 vtac_filedir := SUBSTR(vtac_filedir,1,LENGTH(vtac_filedir)-1);
137 END IF;
138
139 vtac_pg_filehandle := UTL_FILE.FOPEN(vtac_filedir, p_file_name, 'r');
140
141
142
143 --Process Offers
144
145 LOOP -- Main Loop
146 BEGIN
147 UTL_FILE.GET_LINE(vtac_pg_filehandle,G_vtac_pg_output_buffer);
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 EXIT;
151 END;
152
153 --Commit the changes for the previous student
154
155 IF G_pg_test_only = 'TRUE' THEN
156 ROLLBACK;
157 ELSE
158 COMMIT;
159 END IF;
160
161
162 G_pg_read_number := G_pg_read_number + 1;
163
164 -- Transfer the data to the host variables
165
166 vtac_id_num := RTRIM(SUBSTR(G_vtac_pg_output_buffer,1,9));
167 vtac_surname := RTRIM(SUBSTR(G_vtac_pg_output_buffer,10,24));
168 vtac_gname1 := RTRIM(SUBSTR(G_vtac_pg_output_buffer,34,17));
169 vtac_gname2 := RTRIM(SUBSTR(G_vtac_pg_output_buffer,51,17));
170 vtac_DateOfBirth := RTRIM(SUBSTR(G_vtac_pg_output_buffer,68,8));
171 vtac_street_name := RTRIM(SUBSTR(G_vtac_pg_output_buffer,76,25));
172 vtac_suburb := RTRIM(SUBSTR(G_vtac_pg_output_buffer,101,25));
173 vtac_state := RTRIM(SUBSTR(G_vtac_pg_output_buffer,126,3));
174 vtac_postcode := RTRIM(SUBSTR(G_vtac_pg_output_buffer,129,4));
175 vtac_country := RTRIM(SUBSTR(G_vtac_pg_output_buffer,133,14));
176 vtac_home_ph := RTRIM(SUBSTR(G_vtac_pg_output_buffer,147,12));
177 vtac_bus_ph := RTRIM(SUBSTR(G_vtac_pg_output_buffer,159,12));
178 vtac_sex := RTRIM(SUBSTR(G_vtac_pg_output_buffer,171,1));
179 vtac_prev_surname := RTRIM(SUBSTR(G_vtac_pg_output_buffer,172,24));
180 vtac_prev_gname1 := RTRIM(SUBSTR(G_vtac_pg_output_buffer,196,17));
181 vtac_prev_gname2 := RTRIM(SUBSTR(G_vtac_pg_output_buffer,213,17));
182 vtac_residency := RTRIM(SUBSTR(G_vtac_pg_output_buffer,274,1));
183
184 -- Log the student loaded
185 FND_FILE.PUT_LINE( FND_FILE.LOG,
186 G_pg_read_number||'-'||vtac_id_num||' '||vtac_surname||' '||vtac_DateOfBirth);
187
188 -- Check to see if the person already exists on Callista.
189 -- Match on surname, birth date, sex and first initial of first name.
190
191 v_initial := SUBSTR(vtac_gname1,1,1);
192
193 -- This module attempts to find a person based on surname, birth date,
194 -- sex and first initial
195 G_pg_match_person_id := IGS_AD_GEN_007.ADMP_GET_MATCH_PRSN(
196 vtac_surname,
197 vtac_DateOfBirth, -- format 'DDMMYYYY'
198 vtac_sex,
199 v_initial,
200 v_message_name);
201
202 IF (v_message_name IS NOT NULL) THEN
203
204 -- vtac_DateOfBirth has wrong date format
205 G_pg_message_str := FND_MESSAGE.GET_STRING('IGS', v_message_name);
206 FND_FILE.PUT_LINE( FND_FILE.LOG,vtac_id_num||'.'||RTRIM(G_pg_message_str));
207 END IF;
208
209 IF G_pg_match_person_id = 0 THEN
210 OPEN c_api (vtac_id_num,
211 p_alt_person_id_type);
212 FETCH c_api INTO G_pg_match_person_id;
213 IF (c_api%NOTFOUND) THEN
214 G_pg_match_person_id := 0;
215 END IF;
216
217 CLOSE c_api;
218 END IF;
219
220
221 -- Concatenate First Given Name with Second Given Name
222
223 IF vtac_gname2 IS NOT NULL THEN
224 G_vtac_pg_all_given_names := RTRIM(vtac_gname1||' '||vtac_gname2);
225 END IF;
226
227 G_pg_ret_val := 0;
228
229
230 -- This module uses information from the TAC offer load process
231 -- to create person and alternate person ID records if they
232 -- don't already exist
233 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_TAC_PRSN(
234 G_pg_match_person_id,
235 vtac_id_num,
236 vtac_surname,
237 G_vtac_pg_all_given_names,
238 vtac_sex,
239 TO_DATE(vtac_DateOfBirth,'DDMMYYYY'),
240 p_alt_person_id_type,
241 G_pg_new_person_id, -- OUT NOCOPY
242 G_pg_message_str) = FALSE
243 THEN
244 ROLLBACK;
245 FND_FILE.PUT_LINE( FND_FILE.LOG, RTRIM(G_pg_message_str));
246 UTL_FILE.FCLOSE(vtac_pg_filehandle);
247 retcode := 2;
248 return;
249 END IF;
250
251
252
253 --Log the creating record message
254 FND_FILE.PUT_LINE( FND_FILE.LOG, RTRIM(G_pg_message_str));
255
256
257
258 --Save the ID number we have used so far into a new variable. We can then use this
259 --variable from now on regardless of if the student was new or matched.
260
261 IF G_pg_match_person_id <> 0 THEN
262 G_pg_current_person_id := G_pg_match_person_id;
263 ELSE
264 G_pg_current_person_id := G_pg_new_person_id;
265 END IF;
266
267
268 --Load the address
269
270
271 admp_vtac_pg_load_address(
272 vtac_street_name,
273 vtac_suburb,
274 vtac_state,
275 vtac_country,
276 vtac_postcode,
277 vtac_home_ph,
278 vtac_bus_ph,
279 p_aus_addr_type,
280 p_os_addr_type);
281
282
283 -- Only continue if admission application is created
284
285
286 IF (admp_vtac_pg_load_stu_off_crs( p_offer_round,
287 p_override_adm_cat,
288 p_offer_letter_req_ind,
289 p_pre_enrol_ind,
290 basis_for_admission_type) = TRUE) THEN
291
292 --Load ter edu details
293 admp_vtac_pg_load_tert_edu;
294
295 ELSE
296 IF G_pg_match_person_id = 0 THEN
297 FND_FILE.PUT_LINE( FND_FILE.LOG,G_pg_current_person_id||
298 ' '||IGS_GE_GEN_004.GENP_GET_LOOKUP('REPORT','REJTD'));
299 END IF;
300
301 END IF;
302
303 END LOOP; -- End of enormous main Loop.
304
305 -- Commit any changes made for the last student
306
307 IF G_pg_test_only = 'TRUE' THEN
308 ROLLBACK;
309 ELSE
310 COMMIT;
311 END IF;
312
313 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NUM_OF_RECORD_READ')||' - '||G_pg_read_number);
314
315 UTL_FILE.FCLOSE(vtac_pg_filehandle);
316
317 EXCEPTION
318 WHEN UTL_FILE.INVALID_PATH THEN
319 UTL_FILE.FCLOSE(vtac_pg_filehandle);
320 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INVALID_PATH');
321 retcode := 2;
322
323 WHEN UTL_FILE.INVALID_MODE THEN
324 UTL_FILE.FCLOSE(vtac_pg_filehandle);
325 ERRBUF:= FND_MESSAGE.GET_STRING('IGS', 'IGS_GE_INVALID_MODE');
326 retcode := 2;
327
328 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
329 UTL_FILE.FCLOSE(vtac_pg_filehandle);
330 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INVALID_FILE_HANDLE');
331 retcode := 2;
332
333 WHEN UTL_FILE.INVALID_OPERATION THEN
334 UTL_FILE.FCLOSE(vtac_pg_filehandle);
335 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INVALID_OPER');
336 retcode := 2;
337
338 WHEN UTL_FILE.READ_ERROR THEN
339 UTL_FILE.FCLOSE(vtac_pg_filehandle);
340 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_READ_ERR');
341 retcode := 2;
342
343 WHEN UTL_FILE.WRITE_ERROR THEN
344 UTL_FILE.FCLOSE(vtac_pg_filehandle);
345 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_WRITE_ERR');
346 retcode := 2;
347
348 WHEN UTL_FILE.INTERNAL_ERROR THEN
349 UTL_FILE.FCLOSE(vtac_pg_filehandle);
350 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_INTERNAL_ERR');
351 retcode := 2;
352
353 WHEN NO_DATA_FOUND THEN
354 UTL_FILE.FCLOSE(vtac_pg_filehandle);
355 ERRBUF:= IGS_GE_GEN_004.GENP_GET_LOOKUP('REPORT','NO_DATA');
356 retcode := 2;
357
358 WHEN OTHERS THEN
359 UTL_FILE.FCLOSE(vtac_pg_filehandle);
360 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
361 retcode := 2;
362 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
363 END admp_ins_vtac_pg_off;
364
365
366 --Load Address
367
368 PROCEDURE admp_vtac_pg_load_address (
369 p_vtac_street_name IN VARCHAR2 ,
370 p_vtac_suburb IN VARCHAR2 ,
371 p_vtac_state IN VARCHAR2 ,
372 p_vtac_country IN VARCHAR2 ,
373 p_vtac_postcode IN NUMBER ,
374 p_vtac_home_ph IN VARCHAR2 ,
375 p_vtac_bus_ph IN VARCHAR2,
376 p_aus_addr_type IN VARCHAR2,
377 p_os_addr_type IN VARCHAR2) IS
378
379 v_message_name VARCHAR2(30);
380 aust_address VARCHAR2(5);
381 message_str VARCHAR2(2000);
382
383
384 BEGIN
385
386 -- Have to work out NOCOPY if the address is Australian or overseas.
387 -- If an Australian address then STATE always has the state.
388
389
390 IF SUBSTR(p_vtac_state,1,3) IN ('VIC','NSW','ACT','QLD','TAS') OR
391 SUBSTR(p_vtac_state,1,2) IN ('NT','WA','SA') THEN
392
393 aust_address := 'TRUE';
394
395 ELSE
396
397 aust_address := 'FALSE';
398
399 END IF;
400
401
402 IF aust_address = 'TRUE' THEN
403
404 G_pg_ret_val := 0;
405
406 --Procedure inserts a new person address record
407 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_PERSON_ADDR(
408 G_pg_current_person_id,
409 p_aus_addr_type,
410 SYSDATE,
411 p_vtac_street_name,
412 p_vtac_suburb,
413 p_vtac_state,
414 NULL,
415 p_vtac_postcode,
416 NULL,
417 p_vtac_home_ph,
418 p_vtac_bus_ph,
419 v_message_name) = FALSE THEN
420
421
422 G_pg_message_str := FND_MESSAGE.GET_STRING('IGS', v_message_name);
423 FND_FILE.PUT_LINE(FND_FILE.LOG,
424 FND_MESSAGE.GET_STRING('IGS','IGS_GE_INVALID_VALUE')||'-'||p_vtac_postcode);
425 FND_FILE.PUT_LINE( FND_FILE.LOG, G_pg_current_person_id||' '||RTRIM(G_pg_message_str));
426
427 END IF;
428
429 ELSE
430
431 G_pg_ret_val := 0;
432 --Procedure inserts a new person address record
433 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_PERSON_ADDR(
434 G_pg_current_person_id,
435 p_os_addr_type,
436 SYSDATE,
437 p_vtac_street_name,
438 p_vtac_suburb,
439 NULL,
440 p_vtac_country,
441 NULL,
442 p_vtac_postcode,
443 p_vtac_home_ph,
444 p_vtac_bus_ph,
445 v_message_name) = FALSE THEN
446 G_pg_message_str := FND_MESSAGE.GET_STRING('IGS', v_message_name);
447 FND_FILE.PUT_LINE( FND_FILE.LOG,G_pg_current_person_id||' '||RTRIM(G_pg_message_str));
448
449 END IF;
450
451
452 END IF; --End of if aust_address
453
454
455 EXCEPTION
456
457 WHEN OTHERS THEN
458 RAISE;
459 END admp_vtac_pg_load_address; --end of load address
460
461
462
463 --Load the students offered courses
464
465
466 FUNCTION admp_vtac_pg_load_stu_off_crs (
467 p_offer_round IN NUMBER,
468 p_override_adm_cat IN VARCHAR2,
469 p_offer_letter_req_ind IN VARCHAR2,
470 p_pre_enrol_ind IN VARCHAR2,
471 basis_for_admission_type IN VARCHAR2)
472 RETURN BOOLEAN IS
473
474
475 --Course preference section (13)
476
477 vtac_inco VARCHAR2(5);
478 vtac_offer_status VARCHAR2(1);
479 vtac_offer_round VARCHAR2(1);
480
481 valid_pref VARCHAR2(5) := 'FALSE';
482 course_cd VARCHAR2(80);
483 attendence_mode VARCHAR2(80);
484 admission_cat VARCHAR2(80);
485 admission_cd VARCHAR2(80);
486 basis_for_admision_type VARCHAR2(80);
487 return_type VARCHAR2(80);
488 tac_course_match_ind VARCHAR2(80);
489 v_message_name VARCHAR2(30);
490 message_str VARCHAR2(2000);
491
492 TYPE pref_str_table IS TABLE OF VARCHAR2( 40)
493 INDEX BY BINARY_INTEGER;
494 pref_str pref_str_table;
495 j BINARY_INTEGER := 1;
496 i NUMBER := 310;
497 k NUMBER := 1;
498
499 BEGIN
500
501 --Transfer the course preference section data from the VTAC PG offer file to the
502 -- PL/SQL table of strings
503
504 WHILE i < 470
505 LOOP
506 pref_str(j) := RTRIM(SUBSTR(G_vtac_pg_output_buffer,i,32));
507 i := i + 32;
508 j := j + 1;
509 END LOOP;
510
511
512 -- Loop through the preferences looking for Deakin Courses(s). Validate that
513 -- this is an actual course.
514
515
516
517 WHILE k <=5
518 LOOP
519 vtac_inco := RTRIM(SUBSTR(pref_str(k),1,5));
520 vtac_offer_round := RTRIM(SUBSTR(pref_str(k),17,1));
521 vtac_offer_status := RTRIM(SUBSTR(pref_str(k),18,1));
522
523
524 IF vtac_inco IS NULL THEN
525 GOTO pref_loop; --Do not process this record, go to the end of the while loop
526 END IF;
527
528
529 -- We only want to process for offered courses for the correct offer round
530 IF vtac_offer_status = 'O' AND vtac_offer_round = SUBSTR(p_offer_round,1,1)
531 THEN
532 -- Get the data we need for inserting an admission_appl
533 IF p_override_adm_cat IS NOT NULL THEN
534 admission_cat := p_override_adm_cat;
535 END IF;
536
537 G_pg_ret_val := 0;
538
539 -- Inserts TAC details to form an admission course
540 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_TAC_COURSE(
541 p_acad_cal_type,
542 p_acad_seq_num,
543 p_adm_cal_type,
544 p_adm_seq_num,
545 admission_cat,
546 NULL, -- fee category
547 NULL, -- enrolment cat
548 NULL, -- correspondence cat
549 G_pg_current_person_id,
550 vtac_inco, -- match_course,
551 NULL, -- preference number
552 SYSDATE, -- application date
553 SYSDATE, -- offer date
554 basis_for_admission_type,
555 admission_cd,
556 'N',
557 NULL,
558 p_offer_letter_req_ind,
559 -- VTAC produces offer letter
560 -- on Deakin's behalf
561 p_pre_enrol_ind,
562 course_cd, -- OUT NOCOPY
563 tac_course_match_ind, -- OUT NOCOPY
564 return_type, -- OUT NOCOPY
565 v_message_name) = FALSE
566 THEN
567 G_pg_message_str := FND_MESSAGE.GET_STRING('IGS', v_message_name);
568 G_pg_ret_val := 1;
569 END IF;
570
571 course_cd := RTRIM(course_cd);
572 tac_course_match_ind := RTRIM(tac_course_match_ind);
573 return_type := RTRIM(return_type);
574
575
576
577 IF G_pg_ret_val = 1 THEN
578 G_pg_message_str := RTRIM(G_pg_message_str);
579
580 IF return_type = 'W' THEN
581
582 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_WARNING_APPLICANT')||' '|| G_pg_current_person_id||'-'||course_cd||'-'||G_pg_message_str||'-'||
583 FND_MESSAGE.GET_STRING('IGS','IGS_AD_CREATE_PENDING_OC_STAT'));
584
585 valid_pref := 'TRUE';
586
587 ELSE
588
589 -- Process next offer if no TAC match
590
591
592 IF tac_course_match_ind = 'N' THEN
593 GOTO pref_loop;
594 END IF;
595
596
597 --There must have been an error inserting
598 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_ERR_APPLICANT')||' '|| G_pg_current_person_id||' '||course_cd||'-'||G_pg_message_str);
599
600 RETURN FALSE;
601
602 ROLLBACK;
603
604 END IF; --End of if return_type
605
606 ELSE
607 valid_pref := 'TRUE';
608
609 END IF; -- End of if G_pg_ret_val
610
611 END IF; --End of if vtac_offer_status -- Course offered and correct round ******/
612
613 <<pref_loop>>
614 k := k + 1;
615 END LOOP; -- End of while loop
616
617
618 IF valid_pref = 'FALSE' THEN
619 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_VALID_PREFERENCES')||' '||
620 G_pg_current_person_id);
621 END IF;
622
623
624 RETURN TRUE;
625
626 EXCEPTION
627
628 WHEN OTHERS THEN
629 RAISE;
630
631 END admp_vtac_pg_load_stu_off_crs; -- End of Load the students offered courses.
632
633
634 --Load tert edu details
635
636 PROCEDURE admp_vtac_pg_load_tert_edu IS
637
638 --Tertiary study detail.
639
640 vtac_tert_year_commence VARCHAR2(4);
641 vtac_tert_year_finish VARCHAR2(4);
642 vtac_tert_institute VARCHAR2(14);
643 vtac_tert_qual VARCHAR2(10);
644 vtac_tert_status VARCHAR2(1);
645 vtac_tert_student_idnum VARCHAR2(9);
646 vtac_tert_inst_code VARCHAR2(4);
647
648 start_yr NUMBER(4);
649 end_yr NUMBER(4);
650
651 v_message_name VARCHAR2(30);
652 v_inserted_ind VARCHAR2(1);
653 message_str VARCHAR2(2000);
654
655
656 TYPE po_sec_str_table IS TABLE OF VARCHAR2(50)
657 INDEX BY BINARY_INTEGER;
658 po_sec_str po_sec_str_table;
659
660 j BINARY_INTEGER;
661 i NUMBER;
662 k NUMBER;
663
664 BEGIN
665
666 --Transfer the course post secondary level qualification data from the VTAC PG
667 --offer file to the PL/SQL table of strings
668
669 i := 609;
670 j := 1;
671 k := 1;
672 WHILE i < 793
673 LOOP
674 po_sec_str(j) := RTRIM(SUBSTR(G_vtac_pg_output_buffer,i,46));
675 i := i + 46;
676 j := j + 1;
677 END LOOP;
678
679
680 --Loop through the Tertiary study details
681
682 WHILE k <= 4
683 LOOP
684 vtac_tert_status := RTRIM(SUBSTR(po_sec_str(k),33,1));
685
686 -- If the record is blank then get the next one
687 IF nvl(vtac_tert_status,' ') = ' ' THEN
688 GOTO tert_edu_loop;
689 END IF;
690
691 vtac_tert_year_commence := RTRIM(SUBSTR(po_sec_str(k),1,4));
692 vtac_tert_year_finish := RTRIM(SUBSTR(po_sec_str(k),5,4));
693
694 start_yr := IGS_GE_NUMBER.TO_NUM(vtac_tert_year_commence);
695 end_yr := IGS_GE_NUMBER.TO_NUM(vtac_tert_year_finish);
696
697
698 vtac_tert_student_idnum := RTRIM(SUBSTR(po_sec_str(k),34,9));
699 vtac_tert_inst_code := RTRIM(SUBSTR(po_sec_str(k),43,4));
700 vtac_tert_institute := RTRIM(SUBSTR(po_sec_str(k),9,14));
701 vtac_tert_qual := RTRIM(SUBSTR(po_sec_str(k),23,10));
702
703 G_pg_ret_val := 0;
704 -- Insert a tertiary education record
705 IF IGS_AD_PRC_TAC_OFFER.ADMP_INS_TERT_EDU(
706 G_pg_current_person_id,
707 'N', --exclusion indiacator
708 vtac_tert_status,
709 start_yr,
710 vtac_tert_inst_code,
711 end_yr,
712 NULL, --grade point average
713 NULL, --language of tuition
714 NULL, --qualification
715 UPPER(vtac_tert_institute),
716 -- vtac may send inst names in mixed case
717 NULL, --equv full time yrs enr
718 vtac_tert_student_idnum,
719 NULL, --course cd
720 NULL, --course title
721 NULL, --state cd
722 NULL, --level of achievement type
723 NULL, --field of study
724 NULL, --language component
725 NULL, --country cd
726 vtac_tert_qual,
727 NULL, -- honours level
728 NULL, --notes
729 v_message_name,
730 v_inserted_ind) = FALSE THEN
731
732 G_pg_message_str := FND_MESSAGE.GET_STRING('IGS', v_message_name);
733 FND_FILE.PUT_LINE( FND_FILE.LOG,G_pg_current_person_id||'-'||RTRIM(G_pg_message_str));
734
735 END IF;
736
737
738 << tert_edu_loop >>
739 k := k + 1;
740 END LOOP; --End tert_edu_loop while loop
741
742
743 EXCEPTION
744
745 WHEN OTHERS THEN
746 RAISE;
747
748 END admp_vtac_pg_load_tert_edu; -- End of Load ter edu details
749
750 END IGS_AD_PG_DATA_LOAD; --End of Package Specification
[Home] [Help]