[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_GEN_002
Source
1 PACKAGE BODY igs_ad_gen_002 AS
2 /* $Header: IGSAD02B.pls 120.11 2006/01/16 20:23:16 rghosh ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --nshee 20-Mar-02 Bug# 2128153
7 -- Description: After rollover Admission Calendar Code not getting
8 -- displayed in the Admission Calendar LOV of deferement Tab in Admission Offer Response
9 -- Change: Changed the cursor c_cir. Selected additional column ci.start_dt to enable
10 -- order by start_dt clause.
11 -- Reason: This cursor may return more than one row because when we rollover
12 -- Admission Calendar , it is not ensured that the alternate code will be unique
13 -- for the particular Calendar Type and the sequence number. Rather, the calendar type and
14 -- sequence number remains the same and also the alternate code.However, the start date of
15 -- the last rollover should be taken into consideration and hence the
16 -- results should be sorted by the descending order of start date. This will ensure
17 -- that the first record is picked up which is the latest rollover.
18 --prchandr 08-Jan-01 Enh Bug No: 2174101, As the Part of Change in IGSEN18B
19 -- Passing NULL as parameters to ENRP_CLC_SUA_EFTSU
20 -- ENRP_CLC_EFTSU_TOTAL for Key course cd and version number
21 --knag 04-Oct-02 Bug 2602096 : Created the function Admp_Get_Appl_ID to return Application ID
22 -- and the function Admp_Get_Fee_Status to return Appl Fee Status
23 --hreddych 22-oct-2002 Bug:2602077 : SF Integration modified the function Admp_Get_Aa_Aas
24 -- to include the new offer deferment status of confirm
25 -- pradhakr 15-Jan-2003 Added one more paramter no_assessment_ind to the
26 -- call enrp_get_load_apply as an impact, following
27 -- the modification of the package Igs_En_Prc_Load.
28 -- Changes wrt ENCR026. Bug# 2743459
29 -- anwest 20-Jul-2004 IGS.M ADTD003
30 -- Added the res_pending_fee_status function to enable
31 -- finer derivation of the PENDING fee status by
32 -- the Submitted Applications Reusable Component
33 -- anwest 03-Nov-05 IGS.M ADTD002:Created function Is_EntQualCode_Allowed
34 -------------------------------------------------------------------------------------------
35 Procedure Admp_Ext_Tac_Arts(
36 p_input_file IN VARCHAR2 ,
37 p_output_file IN VARCHAR2 ,
38 p_directory IN VARCHAR2 )
39 IS
40 BEGIN -- admp_ext_tac_arts
41 -- This program reads a decrypted request file from the TAC (Tertiary
42 -- Admissions Centre) ARTS (Automated Results Transfer System). It
43 -- then validates and interprets each student header record and
44 -- endeavours to match the input file record to a IGS_PE_PERSON record on
45 -- the database. If successful it writes academic details for the
46 -- matching people to the result file, which will then be encrypted
47 -- and retured to the TAC.
48 DECLARE
49 fp_input UTL_FILE.FILE_TYPE;
50 fp_output UTL_FILE.FILE_TYPE;
51 cst_enrolled CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE :=
52 'ENROLLED';
53 cst_sus_srvc CONSTANT IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE :=
54 'SUS_SRVC';
55 cst_rvk_srvc CONSTANT IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE :=
56 'RVK_SRVC';
57 v_record_type VARCHAR2(1);
58 v_input_string VARCHAR2(158);
59 v_previous_name_records_cnt NUMBER(2);
60 v_number_of_matches NUMBER(4);
61 TYPE r_record_type_h IS RECORD (
62 record_type VARCHAR2(1),
63 institution_code NUMBER(4),
64 return_ip_addr VARCHAR2(40),
65 request_dt VARCHAR2(6),
66 request_time VARCHAR2(6),
67 request_cd VARCHAR2(10),
68 matching_level VARCHAR2(10),
69 applicant_id VARCHAR2(10),
70 campus_cd NUMBER(4),
71 student_id VARCHAR2(10),
72 family_name VARCHAR2(20),
73 first_name VARCHAR2(15),
74 second_name VARCHAR2(15),
75 sex VARCHAR2(1),
76 dob VARCHAR2(6));
77 r_header_rec r_record_type_h;
78 -- Table of previous names
79 TYPE r_record_type_p IS RECORD (
80 record_type VARCHAR2(1),
81 family_name VARCHAR2(20),
82 first_name VARCHAR2(15),
83 second_name VARCHAR2(15));
84 r_previous_name_rec r_record_type_p;
85 TYPE t_previous_name_type IS TABLE OF r_previous_name_rec%TYPE
86 INDEX BY BINARY_INTEGER;
87 t_previous_name t_previous_name_type;
88 t_previous_name_clear t_previous_name_type;
89 -- Table of Matched ID's
90 TYPE r_matched_id_rec_type IS RECORD (
91 person_id IGS_PE_PERSON.person_id%TYPE,
92 basis_of_match VARCHAR2(3),
93 reason_code VARCHAR2(1));
94 r_matched_id_rec r_matched_id_rec_type;
95 TYPE t_matched_id_type IS TABLE OF r_matched_id_rec%TYPE
96 INDEX BY BINARY_INTEGER;
97 t_matched_ids t_matched_id_type;
98 t_matched_ids_clear t_matched_id_type;
99 ---------------
100 ---------------
101 FUNCTION admpl_chk_non_num (
102 p_input_string IN VARCHAR2)
103 RETURN BOOLEAN
104 IS
105 v_string_length NUMBER(3);
106 v_character VARCHAR2(1);
107 BEGIN -- admpl_chk_non_num
108 -- Check if the input string contains any non-numeric characters
109 v_string_length := LENGTH(p_input_string);
110 IF (v_string_length > 0) THEN
111 FOR i IN 1..v_string_length LOOP
112 v_character := SUBSTR(p_input_string, i, 1);
113 IF (v_character NOT IN ('0', '1', '2', '3',
114 '4', '5', '6', '7',
115 '8', '9')) THEN
116 RETURN TRUE;
117 END IF;
118 END LOOP;
119 END IF;
120 RETURN FALSE;
121 EXCEPTION
122 WHEN OTHERS THEN
123 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
124 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_chk_non_num');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END admpl_chk_non_num;
128 ---------------
129 ---------------
130 FUNCTION admpl_strip_non_alpha (
131 p_input_string IN VARCHAR2)
132 RETURN VARCHAR2
133 IS
134 v_string_length NUMBER(3);
135 v_output_string VARCHAR2(255);
136 v_character VARCHAR2(1);
137 BEGIN -- admpl_strip_non_alpha
138 -- Remove non-alpha characters from input string
139 v_string_length := LENGTH(p_input_string);
140 IF (v_string_length > 0) THEN
141 FOR i IN 1..v_string_length LOOP
142 v_character := SUBSTR(p_input_string, i, 1);
143 IF (UPPER(v_character) IN ('A', 'B', 'C', 'D', 'E',
144 'F', 'G', 'H', 'I', 'J', 'K',
145 'L', 'M', 'N', 'O', 'P', 'Q',
146 'R', 'S', 'T', 'U', 'V', 'W',
147 'X', 'Y', 'Z')) THEN
148 v_output_string := v_output_string || v_character;
149 END IF;
150 END LOOP;
151 END IF;
152 RETURN v_output_string;
153 EXCEPTION
154 WHEN OTHERS THEN
155 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
156 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_strip_non_alpha');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END admpl_strip_non_alpha;
160 ---------------
161 ---------------
162 FUNCTION admpl_strip_spaces (
163 p_input_string IN VARCHAR2)
164 RETURN VARCHAR2
165 IS
166 v_string_length NUMBER(3);
167 v_output_string VARCHAR2(255);
168 v_character VARCHAR2(1);
169 BEGIN -- admpl_strip_spaces
170 -- Remove spaces from input string
171 v_string_length := LENGTH(p_input_string);
172 IF (v_string_length > 0) THEN
173 FOR i IN 1..v_string_length LOOP
174 v_character := SUBSTR(p_input_string, i, 1);
175 IF (v_character <> ' ') THEN
176 v_output_string := v_output_string || v_character;
177 END IF;
178 END LOOP;
179 END IF;
180 RETURN v_output_string;
181 EXCEPTION
182 WHEN OTHERS THEN
183 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
184 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_strip_spaces');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END admpl_strip_spaces;
188 ---------------
189 ---------------
190 PROCEDURE admpl_store_matched_table (
191 p_target_person_id IN IGS_PE_PERSON.person_id%TYPE,
192 p_basis_of_match IN VARCHAR2,
193 p_number_of_matches IN OUT NOCOPY NUMBER)
194 IS
195 cst_result_blk CONSTANT IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE :=
196 'RESULT_BLK';
197 v_number_of_matches NUMBER(4);
198 v_person_id IGS_PE_PERSENC_EFFCT.person_id%TYPE;
199 v_reason_code VARCHAR2(1);
200 CURSOR c_pee IS
201 SELECT pee.person_id
202 FROM IGS_PE_PERSENC_EFFCT pee
203 WHERE pee.person_id = p_target_person_id AND
204 pee.s_encmb_effect_type IN (
205 cst_result_blk,
206 cst_sus_srvc,
207 cst_rvk_srvc) AND
208 SYSDATE BETWEEN pee.pee_start_dt AND
209 DECODE (pee.expiry_dt,
210 NULL,
211 IGS_GE_DATE.IGSDATE('9999/12/31'),--TO_DATE('31/12/9999', 'DD/MM/YYYY'),
212 pee.expiry_dt);
213 CURSOR c_sua IS
214 SELECT sua.person_id
215 FROM IGS_EN_SU_ATTEMPT sua
216 WHERE sua.person_id = p_target_person_id AND
217 sua.unit_attempt_status = cst_enrolled;
218 BEGIN -- admpl_store_matched_table (10)
219 -- This will add the matched ID number along with the basis
220 -- of match and reason code to the PLSQL table of matched ID's.
221 -- The matching criteria number that the match was successful
222 -- against should be passed as a parameter to this procedure
223 -- along with the target person ID.
224 -- We don't want to store the same person ID more than once.
225 FOR i IN 1..p_number_of_matches LOOP
226 r_matched_id_rec := t_matched_ids(i);
227 IF (r_matched_id_rec.person_id = p_target_person_id) THEN
228 RETURN;
229 END IF;
230 END LOOP;
231 -- Increment the number of matches
232 p_number_of_matches := p_number_of_matches + 1;
233 -- If the students results are not released due to an encumbrance
234 -- then reason_code = 0
235 OPEN c_pee;
236 FETCH c_pee INTO v_person_id;
237 IF (c_pee%FOUND) THEN
238 CLOSE c_pee;
239 v_reason_code := 'O';
240 r_matched_id_rec.person_id := p_target_person_id;
241 r_matched_id_rec.basis_of_match := p_basis_of_match;
242 r_matched_id_rec.reason_code := v_reason_code;
243 t_matched_ids(p_number_of_matches) := r_matched_id_rec;
244 RETURN;
245 END IF;
246 CLOSE c_pee;
247 -- If student is currently enrolled then reason_code = C
248 OPEN c_sua;
249 FETCH c_sua INTO v_person_id;
250 IF (c_sua%FOUND) THEN
251 CLOSE c_sua;
252 v_reason_code := 'C';
253 r_matched_id_rec.person_id := p_target_person_id;
254 r_matched_id_rec.basis_of_match := p_basis_of_match;
255 r_matched_id_rec.reason_code := v_reason_code;
256 t_matched_ids(p_number_of_matches) := r_matched_id_rec;
257 RETURN;
258 END IF;
259 CLOSE c_sua;
260 -- If we have reached this far then there are no special circumstances.
261 v_reason_code := ' ';
262 r_matched_id_rec.person_id := p_target_person_id;
263 r_matched_id_rec.basis_of_match := p_basis_of_match;
264 r_matched_id_rec.reason_code := v_reason_code;
265 t_matched_ids(p_number_of_matches) := r_matched_id_rec;
266 EXCEPTION
267 WHEN OTHERS THEN
268 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
269 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_store_matched_table');
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END admpl_store_matched_table;
273 ---------------
274 ---------------
275 PROCEDURE admpl_match_criteria11 (
276 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
277 p_number_of_matches IN OUT NOCOPY NUMBER)
278 IS
279 cst_match11 CONSTANT VARCHAR2(3) := 'PFB';
280 v_number_of_matches NUMBER(4);
281 CURSOR c_pe (
282 cp_previous_family_name IGS_PE_PERSON.surname%TYPE) IS
283 SELECT pe.person_id
284 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
285 WHERE pe.last_name = cp_previous_family_name AND
286 pe.birth_date = p_convert_birth_dt;
287 BEGIN -- admpl_match_criteria11
288 -- Find match on previous family name and date of birth
289 FOR i IN 1..v_previous_name_records_cnt LOOP
290 r_previous_name_rec := t_previous_name(i);
291 FOR v_pe_rec IN c_pe(r_previous_name_rec.family_name) LOOP
292 -- We have a match
293 admpl_store_matched_table(
294 v_pe_rec.person_id,
295 cst_match11,
296 p_number_of_matches);
297 END LOOP;
298 END LOOP;
299 EXCEPTION
300 WHEN OTHERS THEN
301 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
302 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria11');
303 IGS_GE_MSG_STACK.ADD;
304 App_Exception.Raise_Exception;
305 END admpl_match_criteria11;
306 ---------------
307 ---------------
308 PROCEDURE admpl_match_criteria10 (
309 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
310 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
311 p_number_of_matches IN OUT NOCOPY NUMBER)
312 IS
313 cst_match10 CONSTANT VARCHAR2(3) := 'CFB';
314 v_number_of_matches NUMBER(4);
315 CURSOR c_pe IS
316 SELECT pe.person_id
317 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
318 WHERE pe.last_name = p_convert_family_name AND
319 pe.birth_date = p_convert_birth_dt;
320 BEGIN -- admpl_match_criteria10
321 -- Find match on current family name and date of birth
322 FOR v_pe_rec IN c_pe LOOP
323 -- We have a match
324 admpl_store_matched_table(
325 v_pe_rec.person_id,
326 cst_match10,
327 p_number_of_matches);
328 END LOOP;
329 EXCEPTION
330 WHEN OTHERS THEN
331 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
332 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria10');
333 IGS_GE_MSG_STACK.ADD;
334 App_Exception.Raise_Exception;
335 END admpl_match_criteria10;
336 ---------------
337 ---------------
338 PROCEDURE admpl_match_criteria9 (
339 p_convert_first_name IN VARCHAR2,
340 p_convert_second_name IN VARCHAR2,
341 p_number_of_matches IN OUT NOCOPY NUMBER)
342 IS
343 cst_match9 CONSTANT VARCHAR2(3) := 'P';
344 v_number_of_matches NUMBER(4);
345 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
346 v_other_names IGS_PE_PERSON.given_names%TYPE;
347 CURSOR c_pe (
348 cp_previous_family_name IGS_PE_PERSON.surname%TYPE) IS
349 SELECT hz.party_id person_id /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
350 FROM hz_parties hz
351 WHERE hz.person_last_name = cp_previous_family_name;
352 BEGIN -- admpl_match_criteria9
353 -- Find match on previous family name and a given name
354 FOR i IN 1..v_previous_name_records_cnt LOOP
355 r_previous_name_rec := t_previous_name(i);
356 FOR v_pe_rec IN c_pe(r_previous_name_rec.family_name) LOOP
357 -- Truncate name
358 IGS_ST_GEN_003.stap_get_prsn_names(
359 v_pe_rec.person_id,
360 v_db_given_name, -- out NOCOPY
361 v_other_names); -- out NOCOPY
362 IF (r_previous_name_rec.first_name IS NOT NULL OR
363 r_previous_name_rec.second_name IS NOT NULL) THEN
364 -- We want to match the first given
365 -- name from the database against either
366 -- previous given name provided in the file
367 IF (v_db_given_name = r_previous_name_rec.first_name OR
368 v_db_given_name = r_previous_name_rec.second_name) THEN
369 -- We have a match
370 admpl_store_matched_table(
371 v_pe_rec.person_id,
372 cst_match9,
373 p_number_of_matches);
374 END IF;
375 ELSE
376 -- Previous given names are blank
377 -- so use current given names
378 IF (v_db_given_name = p_convert_first_name OR
379 v_db_given_name = p_convert_second_name) THEN
380 -- We have a match
381 admpl_store_matched_table(
382 v_pe_rec.person_id,
383 cst_match9,
384 p_number_of_matches);
385 END IF;
386 END IF;
387 END LOOP;
388 END LOOP;
389 EXCEPTION
390 WHEN OTHERS THEN
391 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
392 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria9');
393 IGS_GE_MSG_STACK.ADD;
394 App_Exception.Raise_Exception;
395 END admpl_match_criteria9;
396 ---------------
397 ---------------
398 PROCEDURE admpl_match_criteria8 (
399 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
400 p_convert_first_name IN VARCHAR2,
401 p_convert_second_name IN VARCHAR2,
402 p_number_of_matches IN OUT NOCOPY NUMBER)
403 IS
404 cst_match8 CONSTANT VARCHAR2(3) := 'C';
405 v_number_of_matches NUMBER(4);
406 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
407 v_other_names IGS_PE_PERSON.given_names%TYPE;
408 CURSOR c_pe IS
409 SELECT hz.party_id person_id
410 FROM hz_parties hz
411 WHERE hz.person_last_name = p_convert_family_name;
412 BEGIN -- p_convert_family_name
413 -- Find match on current family name and a given name
414 FOR v_pe_rec IN c_pe LOOP
415 -- We want to match the first given name from the database
416 -- against either given name provided in the file
417 -- Truncate name
418 IGS_ST_GEN_003.stap_get_prsn_names(
419 v_pe_rec.person_id,
420 v_db_given_name, -- out NOCOPY
421 v_other_names); -- out NOCOPY
422 IF (v_db_given_name = p_convert_first_name OR
423 v_db_given_name = p_convert_second_name) THEN
424 -- We have a match
425 admpl_store_matched_table(
426 v_pe_rec.person_id,
427 cst_match8,
428 p_number_of_matches);
429 END IF;
430 END LOOP;
431 EXCEPTION
432 WHEN OTHERS THEN
433 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
434 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria8');
435 IGS_GE_MSG_STACK.ADD;
436 App_Exception.Raise_Exception;
437 END admpl_match_criteria8;
438 ---------------
439 ---------------
440 PROCEDURE admpl_match_criteria7 (
441 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
442 p_convert_first_name IN VARCHAR2,
443 p_convert_second_name IN VARCHAR2,
444 p_number_of_matches IN OUT NOCOPY NUMBER)
445 IS
446 cst_match7 CONSTANT VARCHAR2(3) := 'PB';
447 v_number_of_matches NUMBER(4);
448 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
449 v_other_names IGS_PE_PERSON.given_names%TYPE;
450 CURSOR c_pe (
451 cp_previous_family_name IGS_PE_PERSON.surname%TYPE) IS
452 SELECT pe.person_id
453 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
454 WHERE pe.last_name = cp_previous_family_name AND
455 pe.birth_date = p_convert_birth_dt;
456 BEGIN -- admpl_match_criteria7
457 -- Find match on previous family name, a given name and date of birth
458 FOR i IN 1..v_previous_name_records_cnt LOOP
459 r_previous_name_rec := t_previous_name(i);
460 FOR v_pe_rec IN c_pe(r_previous_name_rec.family_name) LOOP
461 -- Truncate name
462 IGS_ST_GEN_003.stap_get_prsn_names(
463 v_pe_rec.person_id,
464 v_db_given_name, -- out NOCOPY
465 v_other_names); -- out NOCOPY
466 IF (r_previous_name_rec.first_name IS NOT NULL OR
467 r_previous_name_rec.second_name IS NOT NULL) THEN
468 -- We want to match the first given name
469 -- from the database against either previous
470 -- given name provided in the file
471 IF (v_db_given_name = r_previous_name_rec.first_name OR
472 v_db_given_name = r_previous_name_rec.second_name) THEN
473 -- We have a match
474 admpl_store_matched_table(
475 v_pe_rec.person_id,
476 cst_match7,
477 p_number_of_matches);
478 END IF;
479 ELSE
480 -- Previous given names are blank so use current given names
481 IF (v_db_given_name = p_convert_first_name OR
482 v_db_given_name = p_convert_second_name) THEN
483 -- We have a match
484 admpl_store_matched_table(
485 v_pe_rec.person_id,
486 cst_match7,
487 p_number_of_matches);
488 END IF;
489 END IF;
490 END LOOP;
491 END LOOP;
492 EXCEPTION
493 WHEN OTHERS THEN
494 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
495 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria7');
496 IGS_GE_MSG_STACK.ADD;
497 App_Exception.Raise_Exception;
498 END admpl_match_criteria7;
499 ---------------
500 ---------------
501 PROCEDURE admpl_match_criteria6 (
502 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
503 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
504 p_convert_first_name IN VARCHAR2,
505 p_convert_second_name IN VARCHAR2,
506 p_number_of_matches IN OUT NOCOPY NUMBER)
507 IS
508 cst_match6 CONSTANT VARCHAR2(3) := 'CB';
509 v_number_of_matches NUMBER(4);
510 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
511 v_other_names IGS_PE_PERSON.given_names%TYPE;
512 CURSOR c_pe IS
513 SELECT pe.person_id
514 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
515 WHERE pe.last_name = p_convert_family_name AND
516 pe.birth_date = p_convert_birth_dt;
517 BEGIN -- admpl_match_criteria6
518 -- Find match on current family name, a given name and date of birth
519 FOR v_pe_rec IN c_pe LOOP
520 -- We want to match the first given name from
521 -- the database against either given name
522 -- provided in the file
523 -- Truncate name
524 IGS_ST_GEN_003.stap_get_prsn_names(
525 v_pe_rec.person_id,
526 v_db_given_name, -- out NOCOPY
527 v_other_names); -- out NOCOPY
528 IF (v_db_given_name = p_convert_first_name OR
529 v_db_given_name = p_convert_second_name) THEN
530 -- We have a match
531 admpl_store_matched_table(
532 v_pe_rec.person_id,
533 cst_match6,
534 p_number_of_matches);
535 END IF;
536 END LOOP;
537 EXCEPTION
538 WHEN OTHERS THEN
539 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
540 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria6');
541 IGS_GE_MSG_STACK.ADD;
542 App_Exception.Raise_Exception;
543 END admpl_match_criteria6;
544 ---------------
545 ---------------
546 PROCEDURE admpl_match_criteria5 (
547 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
548 p_number_of_matches IN OUT NOCOPY NUMBER)
549 IS
550 cst_match5 CONSTANT VARCHAR2(3) := 'S';
551 v_number_of_matches NUMBER(4);
552 v_person_id IGS_PE_PERSON.person_id%TYPE;
553 CURSOR c_pe IS
554 SELECT hz.party_id person_id /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
555 FROM hz_parties hz
556 WHERE hz.party_id = p_convert_id;
557 BEGIN -- admpl_match_criteria5
558 -- Find match on person ID only
559 OPEN c_pe;
560 FETCH c_pe INTO v_person_id;
561 IF (c_pe%FOUND) THEN
562 CLOSE c_pe;
563 -- We have a match
564 admpl_store_matched_table(
565 v_person_id,
566 cst_match5,
567 p_number_of_matches);
568 ELSE
569 CLOSE c_pe;
570 END IF;
571 EXCEPTION
572 WHEN OTHERS THEN
573 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
574 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria5');
575 IGS_GE_MSG_STACK.ADD;
576 App_Exception.Raise_Exception;
577 END admpl_match_criteria5;
578 ---------------
579 ---------------
580 PROCEDURE admpl_match_criteria4a (
581 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
582 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
583 p_number_of_matches IN OUT NOCOPY NUMBER)
584 IS
585 cst_match4a CONSTANT VARCHAR2(3) := 'SB';
586 v_number_of_matches NUMBER(4);
587 v_person_id IGS_PE_PERSON.person_id%TYPE;
588 CURSOR c_pe IS
589 SELECT pe.person_id
590 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
591 WHERE pe.person_id = p_convert_id AND
592 pe.birth_date = p_convert_birth_dt;
593 BEGIN -- admpl_match_criteria4a
594 -- Find match on person ID and date of birth
595 OPEN c_pe;
596 FETCH c_pe INTO v_person_id;
597 IF (c_pe%FOUND) THEN
598 CLOSE c_pe;
599 -- We have a match
600 admpl_store_matched_table(
601 v_person_id,
602 cst_match4a,
603 p_number_of_matches);
604 ELSE
605 CLOSE c_pe;
606 END IF;
607 EXCEPTION
608 WHEN OTHERS THEN
609 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
610 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria4a');
611 IGS_GE_MSG_STACK.ADD;
612 App_Exception.Raise_Exception;
613 END admpl_match_criteria4a;
614 ---------------
615 ---------------
616 PROCEDURE admpl_match_criteria4 (
617 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
618 p_convert_first_name IN VARCHAR2,
619 p_convert_second_name IN VARCHAR2,
620 p_number_of_matches IN OUT NOCOPY NUMBER)
621 IS
622 cst_match4 CONSTANT VARCHAR2(3) := 'SP';
623 v_number_of_matches NUMBER(4);
624 v_person_id IGS_PE_PERSON.person_id%TYPE;
625 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
626 v_other_names IGS_PE_PERSON.given_names%TYPE;
627 CURSOR c_pe (
628 cp_previous_family_name IGS_PE_PERSON.surname%TYPE) IS
629 SELECT hz.party_id person_id
630 FROM hz_parties hz /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
631 WHERE hz.party_id = p_convert_id AND
632 hz.person_last_name = cp_previous_family_name;
633 BEGIN -- admpl_match_criteria4
634 -- Find match on person_id, previous family name and a given name
635 FOR i IN 1..v_previous_name_records_cnt LOOP
636 r_previous_name_rec := t_previous_name(i);
637 OPEN c_pe(r_previous_name_rec.family_name);
638 FETCH c_pe INTO v_person_id;
639 IF (c_pe%FOUND) THEN
640 CLOSE c_pe;
641 -- Trancate name
642 IGS_ST_GEN_003.stap_get_prsn_names(
643 v_person_id,
644 v_db_given_name, -- out NOCOPY
645 v_other_names); -- out NOCOPY
646 IF (r_previous_name_rec.first_name IS NOT NULL OR
647 r_previous_name_rec.second_name IS NOT NULL) THEN
648 -- We want to match the first given name
649 -- from the database against either previous
650 -- given name provided in the file
651 IF (v_db_given_name = r_previous_name_rec.first_name OR
652 v_db_given_name = r_previous_name_rec.second_name) THEN
653 -- We have a match
654 admpl_store_matched_table(
655 v_person_id,
656 cst_match4,
657 p_number_of_matches);
658 END IF;
659 ELSE
660 -- Previous given names are blank
661 -- so use current given names
662 IF (v_db_given_name = p_convert_first_name OR
663 v_db_given_name = p_convert_second_name) THEN
664 -- We have a match
665 admpl_store_matched_table(
666 v_person_id,
667 cst_match4,
668 p_number_of_matches);
669 END IF;
670 END IF;
671 ELSE
672 CLOSE c_pe;
673 END IF;
674 END LOOP;
675 EXCEPTION
676 WHEN OTHERS THEN
677 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
678 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria4');
679 IGS_GE_MSG_STACK.ADD;
680 App_Exception.Raise_Exception;
681 END admpl_match_criteria4;
682 ---------------
683 ---------------
684 PROCEDURE admpl_match_criteria3 (
685 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
686 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
687 p_convert_first_name IN VARCHAR2,
688 p_convert_second_name IN VARCHAR2,
689 p_number_of_matches IN OUT NOCOPY NUMBER)
690 IS
691 cst_match3 CONSTANT VARCHAR2(3) := 'SC';
692 v_number_of_matches NUMBER(4);
693 v_person_id IGS_PE_PERSON.person_id%TYPE;
694 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
695 v_other_names IGS_PE_PERSON.given_names%TYPE;
696 CURSOR c_pe IS
697 SELECT hz.party_id person_id
698 FROM hz_parties hz /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
699 WHERE hz.party_id = p_convert_id AND
700 hz.person_last_name = p_convert_family_name;
701 BEGIN -- admpl_match_criteria3
702 -- Find match on person ID, current family name and a given name
703 OPEN c_pe;
704 FETCH c_pe INTO v_person_id;
705 IF (c_pe%FOUND) THEN
706 CLOSE c_pe;
707 -- We want to match the first given name
708 -- from the database against either given
709 -- name provided in the file
710 -- Truncate given name
711 IGS_ST_GEN_003.stap_get_prsn_names(
712 v_person_id,
713 v_db_given_name, -- out NOCOPY
714 v_other_names); -- out NOCOPY
715 IF (v_db_given_name = p_convert_first_name OR
716 v_db_given_name = p_convert_second_name) THEN
717 -- We have a match
718 admpl_store_matched_table(
719 v_person_id,
720 cst_match3,
721 p_number_of_matches);
722 END IF;
723 ELSE
724 CLOSE c_pe;
725 END IF;
726 EXCEPTION
727 WHEN OTHERS THEN
728 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
729 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria3');
730 IGS_GE_MSG_STACK.ADD;
731 App_Exception.Raise_Exception;
732 END admpl_match_criteria3;
733 ---------------
734 ---------------
735 PROCEDURE admpl_match_criteria2 (
736 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
737 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
738 p_convert_first_name IN VARCHAR2,
739 p_convert_second_name IN VARCHAR2,
740 p_number_of_matches IN OUT NOCOPY NUMBER)
741 IS
742 cst_match2 CONSTANT VARCHAR2(3) := 'SPB';
743 v_number_of_matches NUMBER(4);
744 v_person_id IGS_PE_PERSON.person_id%TYPE;
745 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
746 v_other_names IGS_PE_PERSON.given_names%TYPE;
747 CURSOR c_pe (
748 cp_previous_family_name IGS_PE_PERSON.surname%TYPE) IS
749 SELECT pe.person_id
750 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
751 WHERE pe.person_id = p_convert_id AND
752 pe.last_name = cp_previous_family_name AND
753 pe.birth_date = p_convert_birth_dt;
754 BEGIN -- admpl_match_criteria2
755 -- Find match on person ID, previous family name,
756 -- a given name and date of birth
757 FOR i IN 1..v_previous_name_records_cnt LOOP
758 r_previous_name_rec := t_previous_name(i);
759 OPEN c_pe(r_previous_name_rec.family_name);
760 FETCH c_pe INTO v_person_id;
761 IF (c_pe%FOUND) THEN
762 CLOSE c_pe;
763 -- truncate name
764 IGS_ST_GEN_003.stap_get_prsn_names(
765 v_person_id,
766 v_db_given_name, -- out NOCOPY
767 v_other_names); -- out NOCOPY
768 IF (r_previous_name_rec.first_name IS NOT NULL OR
769 r_previous_name_rec.second_name IS NOT NULL) THEN
770 -- We want to match the first given name from
771 -- the database against either previous given
772 -- name provided in the file
773 IF (v_db_given_name = r_previous_name_rec.first_name OR
774 v_db_given_name = r_previous_name_rec.second_name) THEN
775 -- We have a match
776 admpl_store_matched_table(
777 v_person_id,
778 cst_match2,
779 p_number_of_matches);
780 END IF;
781 ELSE
782 -- Previous given names are blank
783 -- so use current given names
784 IF (v_db_given_name = p_convert_first_name OR
785 v_db_given_name = p_convert_second_name) THEN
786 -- We have a match
787 admpl_store_matched_table(
788 v_person_id,
789 cst_match2,
790 p_number_of_matches);
791 END IF;
792 END IF;
793 ELSE
794 CLOSE c_pe;
795 END IF;
796 END LOOP;
797 EXCEPTION
798 WHEN OTHERS THEN
799 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
800 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria2');
801 IGS_GE_MSG_STACK.ADD;
802 App_Exception.Raise_Exception;
803 END admpl_match_criteria2;
804 ---------------
805 ---------------
806 PROCEDURE admpl_match_criteria1 (
807 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
808 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
809 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
810 p_convert_first_name IN VARCHAR2,
811 p_convert_second_name IN VARCHAR2,
812 p_number_of_matches IN OUT NOCOPY NUMBER)
813 IS
814 cst_match1 CONSTANT VARCHAR2(3) := 'SCB';
815 v_number_of_matches NUMBER(4);
816 v_person_id IGS_PE_PERSON.person_id%TYPE;
817 v_db_given_name IGS_PE_PERSON.given_names%TYPE;
818 v_other_names IGS_PE_PERSON.given_names%TYPE;
819 CURSOR c_pe IS
820 SELECT pe.person_id
821 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
822 WHERE pe.person_id = p_convert_id AND
823 pe.last_name = p_convert_family_name AND
824 pe.birth_date = p_convert_birth_dt;
825 BEGIN -- admpl_match_criteria1
826 -- Find match on person ID, current family name,
827 -- a given name and date of birth.
828 OPEN c_pe;
829 FETCH c_pe INTO v_person_id;
830 IF (c_pe%FOUND) THEN
831 CLOSE c_pe;
832 -- We want to match the first given name from the database
833 -- against either given name provided in the file
834 IGS_ST_GEN_003.stap_get_prsn_names(
835 v_person_id,
836 v_db_given_name, -- out NOCOPY
837 v_other_names); -- out NOCOPY
838 IF (v_db_given_name = p_convert_first_name OR
839 v_db_given_name = p_convert_second_name) THEN
840 -- we have a match
841 admpl_store_matched_table(
842 v_person_id,
843 cst_match1,
844 p_number_of_matches);
845 END IF;
846 ELSE
847 CLOSE c_pe;
848 END IF;
849 EXCEPTION
850 WHEN OTHERS THEN
851 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
852 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_criteria1');
853 IGS_GE_MSG_STACK.ADD;
854 App_Exception.Raise_Exception;
855 END admpl_match_criteria1;
856 ---------------
857 ---------------
858 PROCEDURE admpl_match_any_name (
859 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
860 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
861 p_convert_first_name IN VARCHAR2,
862 p_convert_second_name IN VARCHAR2,
863 p_number_of_matches IN OUT NOCOPY NUMBER)
864 IS
865 v_number_of_matches NUMBER(4);
866 BEGIN -- admpl_match_any_name (9)
867 -- Step 1 Attempt a match using criterion 6
868 admpl_match_criteria6(
869 p_convert_family_name,
870 p_convert_birth_dt,
871 p_convert_first_name,
872 p_convert_second_name,
873 p_number_of_matches);
874 -- Step 2 Attempt a match using criterion 7
875 admpl_match_criteria7(
876 p_convert_birth_dt,
877 p_convert_first_name,
878 p_convert_second_name,
879 p_number_of_matches);
880 IF (p_number_of_matches > 0) THEN
881 RETURN;
882 END IF;
883 -- Step 3 Attempt a match using criterion 8
884 admpl_match_criteria8(
885 p_convert_family_name,
886 p_convert_first_name,
887 p_convert_second_name,
888 p_number_of_matches);
889 -- Step 4 Attempt a match using criterion 9
890 admpl_match_criteria9(
891 p_convert_first_name,
892 p_convert_second_name,
893 p_number_of_matches);
894 IF (p_number_of_matches > 0) THEN
895 RETURN;
896 END IF;
897 -- Step 5 Attempt a match using criterion 10
898 admpl_match_criteria10(
899 p_convert_family_name,
900 p_convert_birth_dt,
901 p_number_of_matches);
902 -- Step 6 Attempt a match using criterion 11
903 admpl_match_criteria11(
904 p_convert_birth_dt,
905 p_number_of_matches);
906 EXCEPTION
907 WHEN OTHERS THEN
908 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
909 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_any_name');
910 IGS_GE_MSG_STACK.ADD;
911 App_Exception.Raise_Exception;
912 END admpl_match_any_name;
913 ---------------
914 ---------------
915 PROCEDURE admpl_match_loose_name (
916 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
917 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
918 p_convert_first_name IN VARCHAR2,
919 p_convert_second_name IN VARCHAR2,
920 p_number_of_matches IN OUT NOCOPY NUMBER)
921 IS
922 v_number_of_matches NUMBER(4);
923 BEGIN -- admpl_match_loose_name (8)
924 -- Step 1 Attempt a match using criterion 6
925 admpl_match_criteria6(
926 p_convert_family_name,
927 p_convert_birth_dt,
928 p_convert_first_name,
929 p_convert_second_name,
930 p_number_of_matches);
931 IF (p_number_of_matches > 0) THEN
932 RETURN;
933 END IF;
934 -- Step 2 Attempt a match using criterion 8
935 admpl_match_criteria8(
936 p_convert_family_name,
937 p_convert_first_name,
938 p_convert_second_name,
939 p_number_of_matches);
940 IF (p_number_of_matches > 0) THEN
941 RETURN;
942 END IF;
943 -- Step 3 Attempt a match using criterion 10
944 admpl_match_criteria10(
945 p_convert_family_name,
946 p_convert_birth_dt,
947 p_number_of_matches);
948 EXCEPTION
949 WHEN OTHERS THEN
950 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
951 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_loose_name');
952 IGS_GE_MSG_STACK.ADD;
953 App_Exception.Raise_Exception;
954 END admpl_match_loose_name;
955 ---------------
956 ---------------
957 PROCEDURE admpl_match_exact_name (
958 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
959 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
960 p_convert_first_name IN VARCHAR2,
961 p_convert_second_name IN VARCHAR2,
962 p_number_of_matches IN OUT NOCOPY NUMBER)
963 IS
964 v_number_of_matches NUMBER(4);
965 BEGIN -- admpl_match_exact_name (7)
966 -- Step 1 Attempt a match using criterion 6
967 admpl_match_criteria6(
968 p_convert_family_name,
969 p_convert_birth_dt,
970 p_convert_first_name,
971 p_convert_second_name,
972 p_number_of_matches);
973 EXCEPTION
974 WHEN OTHERS THEN
975 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
976 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_exact_name');
977 IGS_GE_MSG_STACK.ADD;
978 App_Exception.Raise_Exception;
979 END admpl_match_exact_name;
980 ---------------
981 ---------------
982 PROCEDURE admpl_match_any_id (
983 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
984 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
985 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
986 p_convert_first_name IN VARCHAR2,
987 p_convert_second_name IN VARCHAR2,
988 p_number_of_matches IN OUT NOCOPY NUMBER)
989 IS
990 v_number_of_matches NUMBER(4);
991 BEGIN -- admpl_match_any_id (6)
992 -- Step 1 Attempt a match using criterion 1
993 admpl_match_criteria1(
994 p_convert_id,
995 p_convert_family_name,
996 p_convert_birth_dt,
997 p_convert_first_name,
998 p_convert_second_name,
999 p_number_of_matches);
1000 -- Step 2 Attempt a match using criteria 2
1001 admpl_match_criteria2(
1002 p_convert_id,
1003 p_convert_birth_dt,
1004 p_convert_first_name,
1005 p_convert_second_name,
1006 p_number_of_matches);
1007 IF (p_number_of_matches > 0) THEN
1008 RETURN;
1009 END IF;
1010 -- Step 3 Attempt a match using criterion 3
1011 admpl_match_criteria3(
1012 p_convert_id,
1013 p_convert_family_name,
1014 p_convert_first_name,
1015 p_convert_second_name,
1016 p_number_of_matches);
1017 -- Step 4 Attempt a match using criterion 4
1018 admpl_match_criteria4(
1019 p_convert_id,
1020 p_convert_first_name,
1021 p_convert_second_name,
1022 p_number_of_matches);
1023 IF (p_number_of_matches > 0) THEN
1024 RETURN;
1025 END IF;
1026 -- Step 4a Attempt a match using criterion 4a.
1027 admpl_match_criteria4a(
1028 p_convert_id,
1029 p_convert_birth_dt,
1030 p_number_of_matches);
1031 IF (p_number_of_matches > 0) THEN
1032 RETURN;
1033 END IF;
1034 -- Step 5 Attempt a match using criterion 5
1035 admpl_match_criteria5(
1036 p_convert_id,
1037 p_number_of_matches);
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1041 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_any_id');
1042 IGS_GE_MSG_STACK.ADD;
1043 App_Exception.Raise_Exception;
1044 END admpl_match_any_id;
1045 ---------------
1046 ---------------
1047 PROCEDURE admpl_match_exact_id (
1048 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
1049 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
1050 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
1051 p_convert_first_name IN VARCHAR2,
1052 p_convert_second_name IN VARCHAR2,
1053 p_number_of_matches IN OUT NOCOPY NUMBER)
1054 IS
1055 v_number_of_matches NUMBER(4);
1056 BEGIN -- admpl_match_exact_id (5)
1057 -- Step 1 Attempt a match using criterion 1.
1058 admpl_match_criteria1(
1059 p_convert_id,
1060 p_convert_family_name,
1061 p_convert_birth_dt,
1062 p_convert_first_name,
1063 p_convert_second_name,
1064 p_number_of_matches);
1065 IF (p_number_of_matches > 0) THEN
1066 RETURN;
1067 END IF;
1068 -- Step 2 Attempt a match using criterion 3
1069 admpl_match_criteria3(
1070 p_convert_id,
1071 p_convert_family_name,
1072 p_convert_first_name,
1073 p_convert_second_name,
1074 p_number_of_matches);
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1078 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_exact_id');
1079 IGS_GE_MSG_STACK.ADD;
1080 App_Exception.Raise_Exception;
1081 END admpl_match_exact_id;
1082 ---------------
1083 ---------------
1084 PROCEDURE admpl_match_standard_6 (
1085 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
1086 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
1087 p_convert_first_name IN VARCHAR2,
1088 p_convert_second_name IN VARCHAR2,
1089 p_number_of_matches IN OUT NOCOPY NUMBER)
1090 IS
1091 v_number_of_matches NUMBER(4);
1092 BEGIN
1093 -- Step 6 Attempt a match using criterion 6. (17)
1094 admpl_match_criteria6(
1095 p_convert_family_name,
1096 p_convert_birth_dt,
1097 p_convert_first_name,
1098 p_convert_second_name,
1099 p_number_of_matches);
1100 -- Step 7 Attempt a match using criterion 7. (18)
1101 admpl_match_criteria7(
1102 p_convert_birth_dt,
1103 p_convert_first_name,
1104 p_convert_second_name,
1105 p_number_of_matches);
1106 -- If one or more new matches are found
1107 IF (p_number_of_matches > 0) THEN
1108 RETURN;
1109 END IF;
1110 -- Step 8 Attempt a match using criterion 8. (19)
1111 admpl_match_criteria8(
1112 p_convert_family_name,
1113 p_convert_first_name,
1114 p_convert_second_name,
1115 p_number_of_matches);
1116 -- Step 9 Attempt a match using criterion 9. (20)
1117 admpl_match_criteria9(
1118 p_convert_first_name,
1119 p_convert_second_name,
1120 p_number_of_matches);
1121 IF (p_number_of_matches > 0) THEN
1122 RETURN;
1123 END IF;
1124 -- Step 10 Attempt a match using criterion 10. (21)
1125 admpl_match_criteria10(
1126 p_convert_family_name,
1127 p_convert_birth_dt,
1128 p_number_of_matches);
1129 -- Step 11 Attempt a match using criterion 11. (22)
1130 admpl_match_criteria11(
1131 p_convert_birth_dt,
1132 p_number_of_matches);
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1136 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_standard_6');
1137 IGS_GE_MSG_STACK.ADD;
1138 App_Exception.Raise_Exception;
1139 END admpl_match_standard_6;
1140 PROCEDURE admpl_match_standard (
1141 p_convert_id IN IGS_PE_PERSON.person_id%TYPE,
1142 p_convert_family_name IN IGS_PE_PERSON.surname%TYPE,
1143 p_convert_birth_dt IN IGS_PE_PERSON.birth_dt%TYPE,
1144 p_convert_first_name IN VARCHAR2,
1145 p_convert_second_name IN VARCHAR2,
1146 p_number_of_matches IN OUT NOCOPY NUMBER)
1147 IS
1148 v_number_of_matches NUMBER(4);
1149 BEGIN -- admpl_match_standard (4)
1150 -- Standard matching algorithm
1151 -- Step 1 Attempt a match using criterion 1. (11)
1152 admpl_match_criteria1(
1153 p_convert_id,
1154 p_convert_family_name,
1155 p_convert_birth_dt,
1156 p_convert_first_name,
1157 p_convert_second_name,
1158 p_number_of_matches);
1159 -- Step 2 Attempt a match using criterion 2. (12)
1160 admpl_match_criteria2(
1161 p_convert_id,
1162 p_convert_birth_dt,
1163 p_convert_first_name,
1164 p_convert_second_name,
1165 p_number_of_matches);
1166 IF (p_number_of_matches > 0) THEN
1167 -- Go to Step 6
1168 admpl_match_standard_6(
1169 p_convert_family_name,
1170 p_convert_birth_dt,
1171 p_convert_first_name,
1172 p_convert_second_name,
1173 p_number_of_matches);
1174 RETURN;
1175 END IF;
1176 -- Step 3 Attempt a match using criterion 3. (13)
1177 admpl_match_criteria3(
1178 p_convert_id,
1179 p_convert_family_name,
1180 p_convert_first_name,
1181 p_convert_second_name,
1182 p_number_of_matches);
1183 -- Step 4 Attempt a match using criterion 4. (14)
1184 admpl_match_criteria4(
1185 p_convert_id,
1186 p_convert_first_name,
1187 p_convert_second_name,
1188 p_number_of_matches);
1189 IF (p_number_of_matches > 0) THEN
1190 -- Go to Step 6
1191 admpl_match_standard_6(
1192 p_convert_family_name,
1193 p_convert_birth_dt,
1194 p_convert_first_name,
1195 p_convert_second_name,
1196 p_number_of_matches);
1197 RETURN;
1198 END IF;
1199 -- Step 4a Attempt a match using criterion 4a. (15)
1200 admpl_match_criteria4a(
1201 p_convert_id,
1202 p_convert_birth_dt,
1203 p_number_of_matches);
1204 IF (p_number_of_matches > 0) THEN
1205 -- Go to Step 6
1206 admpl_match_standard_6(
1207 p_convert_family_name,
1208 p_convert_birth_dt,
1209 p_convert_first_name,
1210 p_convert_second_name,
1211 p_number_of_matches);
1212 RETURN;
1213 END IF;
1214 -- Step 5 Attempt a match using criterion 5. (16)
1215 admpl_match_criteria5(
1216 p_convert_id,
1217 p_number_of_matches);
1218 admpl_match_standard_6(
1219 p_convert_family_name,
1220 p_convert_birth_dt,
1221 p_convert_first_name,
1222 p_convert_second_name,
1223 p_number_of_matches);
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1227 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_match_standard');
1228 IGS_GE_MSG_STACK.ADD;
1229 App_Exception.Raise_Exception;
1230 END admpl_match_standard;
1231 ---------------
1232 ---------------
1233 PROCEDURE admpl_write_course_header (
1234 p_course_cd IN IGS_PS_VER.course_cd%TYPE,
1235 p_course_title IN IGS_PS_VER.title%TYPE,
1236 p_govt_field_of_study IN IGS_PS_FLD_OF_STUDY.govt_field_of_study%TYPE,
1237 p_govt_course_type IN IGS_PS_TYPE.govt_course_type%TYPE,
1238 p_honours_flag IN IGS_GR_HONOURS_LEVEL.govt_honours_level%TYPE,
1239 p_exclusion_flag IN VARCHAR2,
1240 p_all_subjects_inc_flag IN VARCHAR2,
1241 p_course_completed_flag IN VARCHAR2,
1242 p_course_year_first_enr IN NUMBER,
1243 p_course_year_last_enr IN NUMBER,
1244 p_course_sem_first_enr IN NUMBER,
1245 p_course_sem_last_enr IN NUMBER,
1246 p_equiv_full_time_enr IN NUMBER) -- sca_total_eftsu
1247 IS
1248 cst_course_rec_type CONSTANT VARCHAR2(1) := 'C';
1249 v_output_course VARCHAR2(106);
1250 BEGIN
1251 -- Write out NOCOPY the details of the course attempt record
1252 v_output_course := cst_course_rec_type ||
1253 RPAD(p_course_cd, 10) ||
1254 RPAD(p_course_title, 72) ||
1255 LTRIM(TO_CHAR(IGS_GE_NUMBER.TO_NUM(p_govt_field_of_study), '000000')) ||
1256 LTRIM(TO_CHAR(p_govt_course_type, '00')) ||
1257 NVL(p_honours_flag, '0') ||
1258 RPAD(NVL(p_exclusion_flag, ' '), 1) ||
1259 RPAD(NVL(p_all_subjects_inc_flag, ' '), 1) ||
1260 RPAD(NVL(p_course_completed_flag, ' '), 1) ||
1261 TO_CHAR(TO_DATE(p_course_year_first_enr, 'YYYY'), 'YY') ||
1262 LTRIM(TO_CHAR(p_course_sem_first_enr, '0'));
1263 IF (p_course_year_last_enr = 0) THEN
1264 v_output_course := v_output_course ||
1265 '00';
1266 ELSE
1267 v_output_course := v_output_course ||
1268 TO_CHAR(TO_DATE(p_course_year_last_enr, 'YYYY'), 'YY');
1269 END IF;
1270 v_output_course := v_output_course ||
1271 LTRIM(TO_CHAR(p_course_sem_last_enr, '0')) ||
1272 LTRIM(TO_CHAR(p_equiv_full_time_enr, '00V00')) ||
1273 fnd_global.newline;
1274 UTL_FILE.PUT(fp_output, v_output_course);
1275 UTL_FILE.FFLUSH(fp_output);
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1279 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_write_course_header');
1280 IGS_GE_MSG_STACK.ADD;
1281 App_Exception.Raise_Exception;
1282 END admpl_write_course_header;
1283 PROCEDURE admpl_write_student_details (
1284 p_matched_id IN r_matched_id_rec.person_id%TYPE,
1285 p_basis_of_match IN r_matched_id_rec.basis_of_match%TYPE,
1286 p_reason_code IN r_matched_id_rec.reason_code%TYPE)
1287 IS
1288 --
1289 -- Who When What
1290 -- knaraset 29-Apr-03 passed uoo_id in call of IGS_AS_GEN_003.assp_get_sua_grade, as part of MUS build bug 2829262
1291 --
1292 cst_discontin CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE :=
1293 'DISCONTIN';
1294 cst_completed CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE :=
1295 'COMPLETED';
1296 cst_exc_course CONSTANT IGS_PE_COURSE_EXCL.s_encmb_effect_type%TYPE :=
1297 'EXC_COURSE';
1298 cst_exc_crs_gp CONSTANT IGS_PE_CRS_GRP_EXCL.s_encmb_effect_type%TYPE :=
1299 'EXC_CRS_GP';
1300 cst_sus_course CONSTANT IGS_PE_COURSE_EXCL.s_encmb_effect_type%TYPE :=
1301 'SUS_COURSE';
1302 cst_academic CONSTANT IGS_CA_TYPE.s_cal_cat%TYPE := 'ACADEMIC';
1303 cst_load CONSTANT IGS_CA_TYPE.s_cal_cat%TYPE := 'LOAD';
1304 cst_student_rec_type CONSTANT VARCHAR2(1) := 'M';
1305 cst_subject_rec_type CONSTANT VARCHAR2(1) := 'S';
1306 cst_withdrawn CONSTANT IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE :=
1307 'WITHDRAWN';
1308 cst_fail CONSTANT IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE := 'FAIL';
1309 cst_pass CONSTANT IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE := 'PASS';
1310 v_person_id IGS_PE_PERSON.person_id%TYPE;
1311 v_surname IGS_PE_PERSON.surname%TYPE;
1312 v_given_names IGS_PE_PERSON.given_names%TYPE;
1313 v_birth_dt IGS_PE_PERSON.birth_dt%TYPE;
1314 v_birth_dt_str VARCHAR2(6);
1315 v_space_pos VARCHAR2(2);
1316 v_first_name IGS_PE_PERSON.given_names%TYPE;
1317 v_second_name IGS_PE_PERSON.given_names%TYPE;
1318 v_course_year_first_enr NUMBER(4);
1319 v_course_year_last_enr NUMBER(4);
1320 v_course_sem_first_enr NUMBER(1);
1321 v_course_sem_last_enr NUMBER(1);
1322 v_sca_total_eftsu NUMBER;
1323 v_output_student VARCHAR2(71);
1324 v_output_subject VARCHAR2(69);
1325 v_last_course_cd IGS_PS_VER.course_cd%TYPE;
1326 v_course_title IGS_PS_VER.title%TYPE;
1327 v_govt_field_of_study IGS_PS_FLD_OF_STUDY.govt_field_of_study%TYPE;
1328 v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
1329 v_honours_flag IGS_GR_HONOURS_LEVEL.govt_honours_level%TYPE;
1330 v_exclusion_flag VARCHAR2(1);
1331 v_s_encmb_effect_type IGS_PE_COURSE_EXCL.s_encmb_effect_type%TYPE;
1332 v_course_completed_flag VARCHAR2(1);
1333 v_all_subjects_inc_flag VARCHAR2(1);
1334 v_subject_code IGS_PS_UNIT_VER.unit_cd%TYPE;
1335 v_subject_title IGS_PS_UNIT_VER.title%TYPE;
1336 v_year_enrolled IGS_EN_SUA_V.acad_alternate_code%TYPE;
1337 v_arts_teaching_cal_type_cd IGS_CA_TYPE.arts_teaching_cal_type_cd%TYPE;
1338 v_semester_enrolled NUMBER(1);
1339 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
1340 v_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
1341 v_subject_completion_code NUMBER(1);
1342 v_govt_discipline_group_cd IGS_PS_DSCP.govt_discipline_group_cd%TYPE;
1343 v_subject_discipline_group NUMBER(4);
1344 v_number_ind BOOLEAN;
1345 v_credit_points NUMBER(6);
1346 v_sua_eftsu NUMBER;
1347 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1348 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1349 v_subject_cnt NUMBER(3);
1350 TYPE t_subject_type IS TABLE OF v_output_subject%TYPE
1351 INDEX BY BINARY_INTEGER;
1352 t_subject t_subject_type;
1353 t_subject_clear t_subject_type;
1354 CURSOR c_pe IS
1355 SELECT pe.person_id person_id,
1356 pe.last_name surname,
1357 pe.first_name given_names,
1358 pe.birth_date birth_dt
1359 FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with igs_pe_person_base_v Bug 3150054 */
1360 WHERE pe.person_id = p_matched_id;
1361 CURSOR c_sua_sca IS
1362 SELECT sca.course_rqrmnt_complete_ind,
1363 sca.person_id,
1364 crv.course_cd,
1365 crv.version_number course_version_number,
1366 crv.course_type,
1367 crv.title course_title,
1368 uv.unit_cd,
1369 sua.version_number unit_version_number,
1370 uv.title unit_title,
1371 IGS_EN_GEN_014.enrs_get_acad_alt_cd(sua.cal_type,sua.ci_sequence_number) acad_alternate_code,
1372 sua.cal_type,
1373 sua.ci_sequence_number,
1374 sua.uoo_id,
1375 sua.discontinued_dt,
1376 sua.administrative_unit_status,
1377 sua.override_enrolled_cp,
1378 sua.override_eftsu,
1379 sua.unit_attempt_status,
1380 sua.no_assessment_ind
1381 FROM IGS_EN_SU_ATTEMPT sua,
1382 IGS_EN_STDNT_PS_ATT sca,
1383 IGS_PS_VER crv,
1384 IGS_PS_UNIT_VER uv
1385 WHERE sua.person_id = p_matched_id AND
1386 sua.unit_attempt_status IN (
1387 cst_enrolled,
1388 cst_completed,
1389 cst_discontin) AND
1390 sca.person_id = sua.person_id AND
1391 sca.course_cd = sua.course_cd AND
1392 uv.unit_cd = sua.unit_cd AND
1393 uv.version_number = sua.version_number AND
1394 crv.course_cd = sca.course_cd AND
1395 crv.version_number = sca.version_number
1396 ORDER BY sua.course_cd,
1397 sua.ci_start_dt;
1398 CURSOR c_fos_cfos (
1399 cp_course_cd IGS_PS_VER.course_cd%TYPE,
1400 cp_version_number IGS_PS_VER.version_number%TYPE) IS
1401 SELECT fos.govt_field_of_study
1402 FROM IGS_PS_FLD_OF_STUDY fos,
1403 IGS_PS_FIELD_STUDY cfos
1404 WHERE cfos.course_cd = cp_course_cd AND
1405 cfos.version_number = cp_version_number AND
1406 cfos.major_field_ind = 'Y' AND
1407 fos.field_of_study = cfos.field_of_study;
1408 CURSOR c_cty (
1409 cp_course_type IGS_PS_VER.course_type%TYPE) IS
1410 SELECT cty.govt_course_type
1411 FROM IGS_PS_TYPE cty
1412 WHERE cty.course_type = cp_course_type;
1413 CURSOR c_pee (
1414 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE) IS
1415 SELECT pee.s_encmb_effect_type
1416 FROM IGS_PE_PERSENC_EFFCT pee
1417 WHERE pee.person_id = cp_person_id AND
1418 pee.s_encmb_effect_type IN (
1419 cst_sus_srvc,
1420 cst_rvk_srvc);
1421 CURSOR c_pce (
1422 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1423 cp_course_cd IGS_PS_VER.course_cd%TYPE) IS
1424 SELECT pce.s_encmb_effect_type
1425 FROM IGS_PE_COURSE_EXCL pce
1426 WHERE pce.person_id = cp_person_id AND
1427 pce.s_encmb_effect_type IN (
1428 cst_exc_course,
1429 cst_sus_course) AND
1430 pce.course_cd = cp_course_cd;
1431 CURSOR c_pcge_cgr_cgm (
1432 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1433 cp_course_cd IGS_PS_VER.course_cd%TYPE) IS
1434 SELECT pcge.s_encmb_effect_type
1435 FROM IGS_PE_CRS_GRP_EXCL pcge,
1436 IGS_PS_GRP cgr,
1437 IGS_PS_GRP_MBR cgm
1438 WHERE pcge.person_id = cp_person_id AND
1439 pcge.s_encmb_effect_type = cst_exc_crs_gp AND
1440 pcge.course_group_cd = cgr.course_group_cd AND
1441 cgr.course_group_cd = cgm.course_group_cd AND
1442 cgm.course_cd = cp_course_cd;
1443 CURSOR c_cat (
1444 cp_cal_type IGS_CA_TYPE.cal_type%TYPE) IS
1445 SELECT cat.arts_teaching_cal_type_cd
1446 FROM IGS_CA_TYPE cat
1447 WHERE cat.cal_type = cp_cal_type;
1448 CURSOR c_cir_cat (
1449 cp_teach_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE,
1450 cp_teach_ci_sequence_number
1451 IGS_CA_INST_REL.sub_ci_sequence_number%TYPE) IS
1452 SELECT cir2.sub_cal_type,
1453 cir2.sub_ci_sequence_number
1454 FROM IGS_CA_INST_REL cir1,
1455 IGS_CA_INST_REL cir2,
1456 IGS_CA_TYPE cat1,
1457 IGS_CA_TYPE cat2
1458 WHERE cir1.sup_cal_type = cat1.cal_type AND
1459 cat1.s_cal_cat = cst_academic AND
1460 cir1.sub_cal_type = cp_teach_cal_type AND
1461 cir1.sub_ci_sequence_number = cp_teach_ci_sequence_number AND
1462 cir2.sub_cal_type = cat2.cal_type AND
1463 cat2.s_cal_cat = cst_load AND
1464 cir1.sup_cal_type = cir2.sup_cal_type AND
1465 cir1.sup_ci_sequence_number = cir2.sup_ci_sequence_number;
1466 CURSOR c_di_ud (
1467 cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
1468 cp_unit_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
1469 SELECT di.govt_discipline_group_cd
1470 FROM IGS_PS_DSCP di,
1471 IGS_PS_UNIT_DSCP ud
1472 WHERE di.discipline_group_cd = ud.discipline_group_cd AND
1473 ud.unit_cd = cp_unit_cd AND
1474 ud.version_number = cp_unit_version_number;
1475 BEGIN
1476 OPEN c_pe;
1477 FETCH c_pe INTO v_person_id,
1478 v_surname,
1479 v_given_names,
1480 v_birth_dt;
1481 CLOSE c_pe;
1482 -- Change birth_dt to DDMMYY format
1483 v_birth_dt_str := TO_CHAR(v_birth_dt, 'DDMMYY');
1484 -- If the birth date is NULL then write it as 6 spaces.
1485 IF (v_birth_dt IS NULL) THEN
1486 v_birth_dt_str := ' ';
1487 END IF;
1488 -- If the birth date is before 1/1/1900 then write it as 000000
1489 IF (v_birth_dt < IGS_GE_DATE.IGSDATE('1900/01/01')) THEN --TO_DATE('1/1/1900', 'DD/MM/YYYY')) THEN
1490 v_birth_dt_str := '000000';
1491 END IF;
1492 v_space_pos := INSTR(v_given_names, ' ');
1493 IF (v_space_pos = 0) THEN
1494 -- Only one name
1495 v_first_name := v_given_names;
1496 ELSE
1497 v_first_name := SUBSTR(v_given_names, 1, v_space_pos - 1);
1498 v_given_names := SUBSTR(v_given_names, v_space_pos + 1);
1499 v_space_pos := INSTR(v_given_names, ' ');
1500 IF (v_space_pos = 0) THEN
1501 -- Only one middle name exists
1502 v_second_name := v_given_names;
1503 ELSE
1504 v_second_name := SUBSTR(v_given_names, 1, v_space_pos - 1);
1505 END IF;
1506 END IF;
1507 v_output_student := cst_student_rec_type ||
1508 RPAD(NVL(p_basis_of_match, ' '), 3) ||
1509 RPAD(IGS_GE_NUMBER.TO_CANN(p_matched_id), 10) ||
1510 RPAD(NVL(v_surname, ' '), 20) ||
1511 RPAD(NVL(v_first_name, ' '), 15) ||
1512 RPAD(NVL(v_second_name, ' '), 15) ||
1513 RPAD(NVL(v_birth_dt_str, ' '), 6) ||
1514 fnd_global.newline;
1515 UTL_FILE.PUT(fp_output, v_output_student);
1516 UTL_FILE.FFLUSH(fp_output);
1517 v_course_year_first_enr := 9999;
1518 v_course_year_last_enr := 0;
1519 v_course_sem_first_enr := 9;
1520 v_course_sem_last_enr := 0;
1521 v_sca_total_eftsu := 0.00;
1522 -- Find any IGS_EN_STDNT_PS_ATT records that have
1523 -- IGS_EN_SU_ATTEMPT records for this student
1524 FOR v_sua_sca_rec IN c_sua_sca LOOP
1525 -- We can't write the subject record until we have
1526 -- all the details for the course attempt record.
1527 IF (v_last_course_cd <> v_sua_sca_rec.course_cd OR
1528 v_last_course_cd IS NULL) THEN
1529 IF (v_last_course_cd IS NOT NULL) THEN
1530 -- A different course was found
1531 -- so write out NOCOPY the details for
1532 -- the last one.
1533 admpl_write_course_header(
1534 v_last_course_cd,
1535 v_course_title,
1536 v_govt_field_of_study,
1537 v_govt_course_type,
1538 v_honours_flag,
1539 v_exclusion_flag,
1540 v_all_subjects_inc_flag,
1541 v_course_completed_flag,
1542 v_course_year_first_enr,
1543 v_course_year_last_enr,
1544 v_course_sem_first_enr,
1545 v_course_sem_last_enr,
1546 v_sca_total_eftsu);
1547 FOR i IN 1..v_subject_cnt LOOP
1548 UTL_FILE.PUT(fp_output, t_subject(i));
1549 UTL_FILE.FFLUSH(fp_output);
1550 END LOOP;
1551 v_course_year_first_enr := 9999;
1552 v_course_year_last_enr := 0;
1553 v_course_sem_first_enr := 9;
1554 v_course_sem_last_enr := 0;
1555 v_sca_total_eftsu := 0.00;
1556 END IF;
1557 -- reset subject table
1558 t_subject := t_subject_clear;
1559 v_subject_cnt := 0;
1560 v_last_course_cd := v_sua_sca_rec.course_cd;
1561 v_course_title := v_sua_sca_rec.course_title;
1562 -- Find Field of study code
1563 OPEN c_fos_cfos (
1564 v_sua_sca_rec.course_cd,
1565 v_sua_sca_rec.course_version_number);
1566 FETCH c_fos_cfos INTO v_govt_field_of_study;
1567 CLOSE c_fos_cfos;
1568 IF (v_govt_field_of_study IS NULL) THEN
1569 v_govt_field_of_study := ' ';
1570 END IF;
1571 -- Find course type code
1572 OPEN c_cty(v_sua_sca_rec.course_type);
1573 FETCH c_cty INTO v_govt_course_type;
1574 CLOSE c_cty;
1575 -- Find honours flag
1576 v_honours_flag := IGS_GR_GEN_001.grdp_get_gr_ghl ( v_sua_sca_rec.person_id,
1577 v_sua_sca_rec.course_cd);
1578 -- Find Exclusion Flag
1579 -- We just want to find if an exclusion has
1580 -- ever existed. Therfore we can ignore the
1581 -- effective date against encumbrances.
1582 v_exclusion_flag := 'N';
1583 -- Check if completely excluded from all services
1584 OPEN c_pee(v_sua_sca_rec.person_id);
1585 FETCH c_pee INTO v_s_encmb_effect_type;
1586 IF (c_pee%FOUND) THEN
1587 v_exclusion_flag := 'Y';
1588 END IF;
1589 CLOSE c_pee;
1590 -- Check if excluded from this course
1591 IF (v_exclusion_flag = 'N') THEN
1592 OPEN c_pce(
1593 v_sua_sca_rec.person_id,
1594 v_sua_sca_rec.course_cd);
1595 FETCH c_pce INTO v_s_encmb_effect_type;
1596 IF (c_pce%FOUND) THEN
1597 v_exclusion_flag := 'Y';
1598 END IF;
1599 CLOSE c_pce;
1600 END IF;
1601 -- Also check for an exclusion from
1602 -- a course within a course group
1603 IF (v_exclusion_flag = 'N') THEN
1604 OPEN c_pcge_cgr_cgm(
1605 v_sua_sca_rec.person_id,
1606 v_sua_sca_rec.course_cd);
1607 FETCH c_pcge_cgr_cgm INTO v_s_encmb_effect_type;
1608 IF (c_pcge_cgr_cgm%FOUND) THEN
1609 v_exclusion_flag := 'Y';
1610 END IF;
1611 CLOSE c_pcge_cgr_cgm;
1612 END IF;
1613 -- FInd the course completed flag
1614 IF (v_sua_sca_rec.course_rqrmnt_complete_ind = 'Y') THEN
1615 v_course_completed_flag := 'Y';
1616 ELSE
1617 v_course_completed_flag := 'N';
1618 END IF;
1619 -- The All subjects included flag
1620 -- will always be set to 'Y'
1621 v_all_subjects_inc_flag := 'Y';
1622 END IF;
1623 -- Find the elements that need to be stored
1624 -- in the subject record PLSQL table.
1625 v_subject_code := v_sua_sca_rec.unit_cd;
1626 v_subject_title := v_sua_sca_rec.unit_title;
1627 v_year_enrolled := v_sua_sca_rec.acad_alternate_code;
1628 OPEN c_cat(v_sua_sca_rec.cal_type);
1629 FETCH c_cat INTO v_arts_teaching_cal_type_cd;
1630 CLOSE c_cat;
1631 IF (SUBSTR(v_arts_teaching_cal_type_cd, 1, 1) IN
1632 ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')) THEN
1633 v_semester_enrolled := IGS_GE_NUMBER.TO_NUM(SUBSTR(v_arts_teaching_cal_type_cd, 1, 1));
1634 ELSE
1635 v_semester_enrolled := NULL;
1636 END IF;
1637 -- Work out NOCOPY course record fields as we loop through all the units
1638 IF (v_year_enrolled <= v_course_year_first_enr) THEN
1639 IF (v_year_enrolled <> v_course_year_first_enr) THEN
1640 v_course_year_first_enr := v_year_enrolled;
1641 v_course_sem_first_enr := v_semester_enrolled;
1642 ELSE
1643 IF (v_semester_enrolled < v_course_sem_first_enr) THEN
1644 v_course_sem_first_enr := v_semester_enrolled;
1645 END IF;
1646 END IF;
1647 END IF;
1648 IF (v_year_enrolled >= v_course_year_last_enr) THEN
1649 IF (v_year_enrolled <> v_course_year_last_enr) THEN
1650 v_course_year_last_enr := v_year_enrolled;
1651 v_course_sem_last_enr := v_semester_enrolled;
1652 ELSE
1653 IF (v_semester_enrolled > v_course_sem_last_enr) THEN
1654 v_course_sem_last_enr := v_semester_enrolled;
1655 END IF;
1656 END IF;
1657 END IF;
1658 -- Subject Weighting
1659 --------------------
1660 v_sua_eftsu := 0.000;
1661 -- We need to find the load calendars that should be
1662 -- used against the teaching calendars we have from
1663 -- the IGS_EN_SU_ATTEMPT records.
1664 FOR v_cir_cat_rec IN c_cir_cat(
1665 v_sua_sca_rec.cal_type,
1666 v_sua_sca_rec.ci_sequence_number) LOOP
1667 IF (IGS_EN_PRC_LOAD.enrp_get_load_incur(
1668 v_sua_sca_rec.cal_type,
1669 v_sua_sca_rec.ci_sequence_number,
1670 v_sua_sca_rec.discontinued_dt,
1671 v_sua_sca_rec.administrative_unit_status,
1672 v_sua_sca_rec.unit_attempt_status,
1673 v_sua_sca_rec.no_assessment_ind,
1674 v_cir_cat_rec.sub_cal_type,
1675 v_cir_cat_rec.sub_ci_sequence_number,
1676 -- anilk, Audit special fee
1677 NULL, -- for p_uoo_id
1678 'N') = 'Y') THEN
1679 v_sua_eftsu := v_sua_eftsu +
1680 IGS_EN_PRC_LOAD.enrp_clc_sua_eftsu(
1681 v_sua_sca_rec.person_id,
1682 v_sua_sca_rec.course_cd,
1683 v_sua_sca_rec.course_version_number,
1684 v_sua_sca_rec.unit_cd,
1685 v_sua_sca_rec.unit_version_number,
1686 v_sua_sca_rec.cal_type,
1687 v_sua_sca_rec.ci_sequence_number,
1688 v_sua_sca_rec.uoo_id,
1689 v_cir_cat_rec.sub_cal_type,
1690 v_cir_cat_rec.sub_ci_sequence_number,
1691 v_sua_sca_rec.override_enrolled_cp,
1692 v_sua_sca_rec.override_eftsu,
1693 'Y',
1694 NULL,
1695 NULL,
1696 NULL,
1697 v_credit_points,
1698 -- anilk, Audit special fee build
1699 'N'); -- out NOCOPY
1700 END IF;
1701 END LOOP;
1702 -- Add the EFTSU for the sua to the total for the sca.
1703 v_sca_total_eftsu := v_sca_total_eftsu + v_sua_eftsu;
1704 v_s_result_type := IGS_AS_GEN_003.assp_get_sua_grade(
1705 v_sua_sca_rec.person_id,
1706 v_sua_sca_rec.course_cd,
1707 v_sua_sca_rec.unit_cd,
1708 v_sua_sca_rec.cal_type,
1709 v_sua_sca_rec.ci_sequence_number,
1710 v_sua_sca_rec.unit_attempt_status,
1711 'Y',
1712 v_grading_schema_cd, -- out NOCOPY
1713 v_gs_version_number, -- out NOCOPY
1714 v_grade,
1715 v_sua_sca_rec.uoo_id); -- out NOCOPY
1716 IF (v_s_result_type IS NOT NULL AND
1717 v_s_result_type = cst_withdrawn) THEN
1718 v_subject_completion_code := 1;
1719 ELSIF (v_s_result_type IS NOT NULL AND
1720 v_s_result_type = cst_fail) THEN
1721 v_subject_completion_code := 2;
1722 ELSIF (v_s_result_type IS NOT NULL AND
1723 v_s_result_type = cst_pass) THEN
1724 v_subject_completion_code := 3;
1725 ELSE
1726 v_subject_completion_code := 4;
1727 END IF;
1728 -- Subject grade comes from v_grade from IGS_AS_GEN_003.assp_get_sua_grade() above.
1729 OPEN c_di_ud(
1730 v_sua_sca_rec.unit_cd,
1731 v_sua_sca_rec.unit_version_number);
1732 FETCH c_di_ud INTO v_govt_discipline_group_cd;
1733 CLOSE c_di_ud;
1734 IF (v_govt_discipline_group_cd IS NOT NULL) THEN
1735 v_number_ind := TRUE;
1736 FOR i IN 1..LENGTH(v_govt_discipline_group_cd) LOOP
1737 IF (SUBSTR(v_govt_discipline_group_cd, i, 1) NOT IN
1738 ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')) THEN
1739 v_number_ind := FALSE;
1740 END IF;
1741 END LOOP;
1742 IF (v_number_ind = TRUE) THEN
1743 -- v_govt_discipline_group_cd is a number
1744 v_subject_discipline_group := IGS_GE_NUMBER.TO_NUM(v_govt_discipline_group_cd);
1745 ELSE
1746 -- v_govt_discipline_group_cd is not a number
1747 v_subject_discipline_group := NULL;
1748 END IF;
1749 END IF;
1750 v_output_subject := cst_subject_rec_type ||
1751 RPAD(v_sua_sca_rec.unit_cd, 10) ||
1752 RPAD(v_sua_sca_rec.unit_title, 36) ||
1753 TO_CHAR(TO_DATE(v_year_enrolled, 'YYYY'), 'YY') ||
1754 ' ' || -- unused space
1755 LTRIM(TO_CHAR(v_semester_enrolled, '0')) || -- should not be null
1756 LTRIM(TO_CHAR(v_sua_eftsu, '000V000')) ||
1757 RPAD(NVL(v_grade, ' '), 6) ||
1758 IGS_GE_NUMBER.TO_CANN(v_subject_completion_code) ||
1759 LTRIM(TO_CHAR(NVL(v_subject_discipline_group, 0), '0000')) ||
1760 fnd_global.newline;
1761 v_subject_cnt := v_subject_cnt + 1;
1762 t_subject(v_subject_cnt) := v_output_subject;
1763 END LOOP;
1764 -- Write course header only if student is enrolled in a course
1765 IF (v_last_course_cd IS NOT NULL) THEN
1766 admpl_write_course_header(
1767 v_last_course_cd,
1768 v_course_title,
1769 v_govt_field_of_study,
1770 v_govt_course_type,
1771 v_honours_flag,
1772 v_exclusion_flag,
1773 v_all_subjects_inc_flag,
1774 v_course_completed_flag,
1775 v_course_year_first_enr,
1776 v_course_year_last_enr,
1777 v_course_sem_first_enr,
1778 v_course_sem_last_enr,
1779 v_sca_total_eftsu);
1780 FOR i IN 1..v_subject_cnt LOOP
1781 UTL_FILE.PUT(fp_output, t_subject(i));
1782 UTL_FILE.FFLUSH(fp_output);
1783 END LOOP;
1784 END IF;
1785 EXCEPTION
1786 WHEN OTHERS THEN
1787 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1788 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_write_student_details');
1789 IGS_GE_MSG_STACK.ADD;
1790 App_Exception.Raise_Exception;
1791 END admpl_write_student_details;
1792 FUNCTION admpl_prc_previous_header (
1793 p_previous_name_records_cnt IN NUMBER,
1794 p_number_of_matches IN OUT NOCOPY NUMBER)
1795 RETURN BOOLEAN
1796 IS
1797 cst_standard CONSTANT VARCHAR2(10) := 'STANDARD';
1798 cst_exact_id CONSTANT VARCHAR2(10) := 'EXACT_ID';
1799 cst_any_id CONSTANT VARCHAR2(10) := 'ANY_ID';
1800 cst_exact_name CONSTANT VARCHAR2(10) := 'EXACT_NAME';
1801 cst_loose_name CONSTANT VARCHAR2(10) := 'LOOSE_NAME';
1802 cst_any_name CONSTANT VARCHAR2(10) := 'ANY_NAME';
1803 v_number_of_matches NUMBER(4);
1804 v_matching_level VARCHAR2(10);
1805 v_convert_id VARCHAR2(10);
1806 v_convert_birth_dt DATE;
1807 v_tmp_birth_dt VARCHAR2(8);
1808 v_convert_family_name VARCHAR2(20);
1809 v_convert_first_name VARCHAR2(15);
1810 v_convert_second_name VARCHAR2(15);
1811 v_output_header_reason_code VARCHAR2(2);
1812 v_reason_C_exists BOOLEAN;
1813 v_reason_O_exists BOOLEAN;
1814 v_output_header VARCHAR2(83);
1815 BEGIN -- admpl_prc_previous_header
1816 -- Process previous header (3)
1817 -- Convert data to match against
1818 v_convert_id := admpl_strip_spaces(r_header_rec.student_id);
1819 -- Check if convert_id contains non-numeric characters
1820 IF (admpl_chk_non_num(v_convert_id) = TRUE) THEN
1821 v_convert_id := 9999999999;
1822 -- we still need to keep processing the student as
1823 -- it does not need to match the ID in all cases.
1824 END IF;
1825 -- Check if birth_dt contains non-numeric characters
1826 IF (admpl_chk_non_num(r_header_rec.dob) = TRUE) THEN
1827 v_tmp_birth_dt := '01/01/01';
1828 ELSE
1829 v_tmp_birth_dt := SUBSTR(r_header_rec.dob, 1, 2) || '/' ||
1830 SUBSTR(r_header_rec.dob, 3, 2) || '/' ||
1831 SUBSTR(r_header_rec.dob, 5, 2);
1832 END IF;
1833 -- At present, we can only convert this with a 2 digit year
1834 -- as the TAC has only given us 2 digits.
1835 v_convert_birth_dt := TO_DATE(v_tmp_birth_dt, 'DD/MM/YY'); --??
1836 v_convert_family_name := admpl_strip_non_alpha(r_header_rec.family_name);
1837 v_convert_first_name := admpl_strip_non_alpha(r_header_rec.first_name);
1838 v_convert_second_name := admpl_strip_non_alpha(r_header_rec.second_name);
1839 IF (p_previous_name_records_cnt > 0) THEN
1840 FOR i IN 1..p_previous_name_records_cnt LOOP
1841 r_previous_name_rec := t_previous_name(i);
1842 r_previous_name_rec.family_name := admpl_strip_non_alpha(
1843 r_previous_name_rec.family_name);
1844 r_previous_name_rec.first_name := admpl_strip_non_alpha(
1845 r_previous_name_rec.first_name);
1846 r_previous_name_rec.second_name := admpl_strip_non_alpha(
1847 r_previous_name_rec.second_name);
1848 t_previous_name(i) := r_previous_name_rec;
1849 END LOOP;
1850 END IF;
1851 -- Match student records
1852 ------------------------
1853 -- Clear PLSQL table of matched ID's.
1854 t_matched_ids := t_matched_ids_clear;
1855 p_number_of_matches := 0;
1856 v_matching_level := admpl_strip_spaces(r_header_rec.matching_level);
1857 IF (v_matching_level = cst_standard) THEN
1858 -- Call match_standard
1859 admpl_match_standard(
1860 v_convert_id,
1861 v_convert_family_name,
1862 v_convert_birth_dt,
1863 v_convert_first_name,
1864 v_convert_second_name,
1865 p_number_of_matches);
1866 ELSIF (v_matching_level = cst_exact_id) THEN
1867 -- Call match_exact_id
1868 admpl_match_exact_id(
1869 v_convert_id,
1870 v_convert_family_name,
1871 v_convert_birth_dt,
1872 v_convert_first_name,
1873 v_convert_second_name,
1874 p_number_of_matches);
1875 ELSIF (v_matching_level = cst_any_id) THEN
1876 -- Call match_any_id
1877 admpl_match_any_id(
1878 v_convert_id,
1879 v_convert_family_name,
1880 v_convert_birth_dt,
1881 v_convert_first_name,
1882 v_convert_second_name,
1883 p_number_of_matches);
1884 ELSIF (v_matching_level = cst_exact_name) THEN
1885 -- Call match_exact_name
1886 admpl_match_exact_name(
1887 v_convert_family_name,
1888 v_convert_birth_dt,
1889 v_convert_first_name,
1890 v_convert_second_name,
1891 p_number_of_matches);
1892 ELSIF (v_matching_level = cst_loose_name) THEN
1893 -- Call match_loose_name
1894 admpl_match_loose_name(
1895 v_convert_family_name,
1896 v_convert_birth_dt,
1897 v_convert_first_name,
1898 v_convert_second_name,
1899 p_number_of_matches);
1900 ELSIF (v_matching_level = cst_any_name) THEN
1901 -- Call match_any_name
1902 admpl_match_any_name(
1903 v_convert_family_name,
1904 v_convert_birth_dt,
1905 v_convert_first_name,
1906 v_convert_second_name,
1907 p_number_of_matches);
1908 ELSIF (v_matching_level = '1') THEN
1909 -- Call match_criteria1
1910 admpl_match_criteria1(
1911 v_convert_id,
1912 v_convert_family_name,
1913 v_convert_birth_dt,
1914 v_convert_first_name,
1915 v_convert_second_name,
1916 p_number_of_matches);
1917 ELSIF (v_matching_level = '2') THEN
1918 -- Call match_criteria2
1919 admpl_match_criteria2(
1920 v_convert_id,
1921 v_convert_birth_dt,
1922 v_convert_first_name,
1923 v_convert_second_name,
1924 p_number_of_matches);
1925 ELSIF (v_matching_level = '3') THEN
1926 -- Call match_criteria3
1927 admpl_match_criteria3(
1928 v_convert_id,
1929 v_convert_family_name,
1930 v_convert_first_name,
1931 v_convert_second_name,
1932 p_number_of_matches);
1933 ELSIF (v_matching_level = '4') THEN
1934 -- Call match_criteria4
1935 admpl_match_criteria4(
1936 v_convert_id,
1937 v_convert_first_name,
1938 v_convert_second_name,
1939 p_number_of_matches);
1940 ELSIF (v_matching_level = '4a') THEN
1941 -- Call match_criteria4a
1942 admpl_match_criteria4a(
1943 v_convert_id,
1944 v_convert_birth_dt,
1945 p_number_of_matches);
1946 ELSIF (v_matching_level = '5') THEN
1947 -- Call match_criteria5
1948 admpl_match_criteria5(
1949 v_convert_id,
1950 p_number_of_matches);
1951 ELSIF (v_matching_level = '6') THEN
1952 -- Call match_criteria6
1953 admpl_match_criteria6(
1954 v_convert_family_name,
1955 v_convert_birth_dt,
1956 v_convert_first_name,
1957 v_convert_second_name,
1958 p_number_of_matches);
1959 ELSIF (v_matching_level = '7') THEN
1960 -- Call match_criteria7
1961 admpl_match_criteria7(
1962 v_convert_birth_dt,
1963 v_convert_first_name,
1964 v_convert_second_name,
1965 p_number_of_matches);
1966 ELSIF (v_matching_level = '8') THEN
1967 -- Call match_criteria8
1968 admpl_match_criteria8(
1969 v_convert_family_name,
1970 v_convert_first_name,
1971 v_convert_second_name,
1972 p_number_of_matches);
1973 ELSIF (v_matching_level = '9') THEN
1974 -- Call match_criteria9
1975 admpl_match_criteria9(
1976 v_convert_first_name,
1977 v_convert_second_name,
1978 p_number_of_matches);
1979 ELSIF (v_matching_level = '10') THEN
1980 -- Call match_criteria10
1981 admpl_match_criteria10(
1982 v_convert_family_name,
1983 v_convert_birth_dt,
1984 p_number_of_matches);
1985 ELSIF (v_matching_level = '11') THEN
1986 -- Call match_criteria11
1987 admpl_match_criteria11(
1988 v_convert_birth_dt,
1989 p_number_of_matches);
1990 ELSE
1991 Fnd_Message.Set_Name('IGS','IGS_AD_UNEXPECTED_MATCH_LEVEL');
1992 IGS_GE_MSG_STACK.ADD;
1993 RETURN FALSE;
1994 END IF;
1995 -- Now we have to work out NOCOPY te number_of_matches.
1996 -- Using the matching routines above, we can write
1997 -- out NOCOPY the details for the header record in the output file
1998 -- Set the output_header.reason_code
1999 -- NB: More than one reason code may exist so we will take the
2000 -- one with the highest precedence once we find out NOCOPY which ones exist
2001 v_reason_C_exists := FALSE;
2002 v_reason_O_exists := FALSE;
2003 IF (p_number_of_matches > 0) THEN
2004 FOR i IN 1..p_number_of_matches LOOP
2005 r_matched_id_rec := t_matched_ids(i);
2006 IF (r_matched_id_rec.reason_code = 'C') THEN
2007 v_reason_C_exists := TRUE;
2008 ELSIF (r_matched_id_rec.reason_code = 'O') THEN
2009 v_reason_O_exists := TRUE;
2010 END IF;
2011 END LOOP;
2012 IF (v_reason_O_exists = TRUE) THEN
2013 v_output_header_reason_code := 'O';
2014 ELSIF (v_reason_C_exists = TRUE) THEN
2015 v_output_header_reason_code := 'C';
2016 ELSE
2017 -- note: Blank is valid and will occur often
2018 v_output_header_reason_code := ' ';
2019 END IF;
2020 ELSE
2021 v_output_header_reason_code := 'X';
2022 END IF;
2023 -- Write out NOCOPY the header record to the output file
2024 v_output_header := r_header_rec.record_type ||
2025 LTRIM(TO_CHAR(r_header_rec.institution_code, '0000')) ||
2026 RPAD(' ', 40) || -- sending IP address is currently not used
2027 r_header_rec.request_dt ||
2028 r_header_rec.request_time ||
2029 r_header_rec.request_cd ||
2030 r_header_rec.applicant_id ||
2031 LTRIM(TO_CHAR(r_header_rec.campus_cd, '0000')) ||
2032 RPAD(v_output_header_reason_code, 2) ||
2033 fnd_global.newline;
2034 UTL_FILE.PUT(fp_output, v_output_header);
2035 UTL_FILE.FFLUSH(fp_output);
2036 -- Now we need to loop through the PLSQL table of matched ID's again
2037 -- and write out NOCOPY all the details for each matched student.
2038 FOR i IN 1..p_number_of_matches LOOP
2039 r_matched_id_rec := t_matched_ids(i);
2040 admpl_write_student_details(
2041 r_matched_id_rec.person_id,
2042 r_matched_id_rec.basis_of_match,
2043 r_matched_id_rec.reason_code);
2044 END LOOP;
2045 RETURN TRUE;
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2049 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_prc_previous_header');
2050 IGS_GE_MSG_STACK.ADD;
2051 App_Exception.Raise_Exception;
2052 END admpl_prc_previous_header;
2053 ---------------
2054 ---------------
2055 PROCEDURE admpl_store_rec (
2056 p_record_type IN VARCHAR2,
2057 p_input_string IN VARCHAR2,
2058 p_previous_name_records_cnt IN OUT NOCOPY NUMBER)
2059 IS
2060 v_previous_name_records_cnt NUMBER(2);
2061 BEGIN -- admpl_store_rec
2062 -- Store records in structures
2063 IF (p_record_type = 'H') THEN
2064 -- Breack v_input_string up and store in
2065 -- structure based on input file header record.
2066 r_header_rec.record_type := p_record_type;
2067 r_header_rec.institution_code := SUBSTR(p_input_string, 2, 4);
2068 r_header_rec.return_ip_addr := SUBSTR(p_input_string, 6, 40);
2069 r_header_rec.request_dt := SUBSTR(p_input_string, 46, 6);
2070 r_header_rec.request_time := SUBSTR(p_input_string, 52, 6);
2071 r_header_rec.request_cd := SUBSTR(p_input_string, 58, 10);
2072 r_header_rec.matching_level := SUBSTR(p_input_string, 68, 10);
2073 r_header_rec.applicant_id := SUBSTR(p_input_string, 78, 9);
2074 r_header_rec.campus_cd := SUBSTR(p_input_string, 87, 4);
2075 r_header_rec.student_id := SUBSTR(p_input_string, 91, 10);
2076 r_header_rec.family_name := SUBSTR(p_input_string, 101, 20);
2077 r_header_rec.first_name := SUBSTR(p_input_string, 121, 15);
2078 r_header_rec.second_name := SUBSTR(p_input_string, 136, 15);
2079 r_header_rec.sex := SUBSTR(p_input_string, 151, 1);
2080 r_header_rec.dob := SUBSTR(p_input_string, 152, 6);
2081 ELSIF (p_record_type = 'P') THEN
2082 -- Breack v_input_string up and store in
2083 -- structure based on input file previous name record.
2084 r_previous_name_rec.record_type := p_record_type;
2085 r_previous_name_rec.family_name := SUBSTR(p_input_string, 2, 20);
2086 r_previous_name_rec.first_name := SUBSTR(p_input_string, 22, 15);
2087 r_previous_name_rec.second_name := SUBSTR(p_input_string, 37, 15);
2088 p_previous_name_records_cnt := p_previous_name_records_cnt + 1;
2089 t_previous_name(p_previous_name_records_cnt) := r_previous_name_rec;
2090 END IF;
2091 EXCEPTION
2092 WHEN OTHERS THEN
2093 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2094 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_store_rec');
2095 IGS_GE_MSG_STACK.ADD;
2096 App_Exception.Raise_Exception;
2097 END admpl_store_rec;
2098 ---------------
2099 ---------------
2100 PROCEDURE admpl_read_rec (
2101 p_record_type OUT NOCOPY VARCHAR2,
2102 p_input_string OUT NOCOPY VARCHAR2)
2103 IS
2104 v_input_string VARCHAR2(158);
2105 v_first_char VARCHAR2(1);
2106 BEGIN -- admpl_read_rec
2107 -- Read record from input file and return record type.
2108 UTL_FILE.GET_LINE(fp_input, v_input_string);
2109 p_input_string := v_input_string;
2110 -- Get the first character from v_input_string
2111 v_first_char := SUBSTR(v_input_string, 1, 1);
2112 IF (v_first_char = 'H') THEN
2113 p_record_type := 'H';
2114 IF (LENGTH(v_input_string) <> 157) THEN
2115 Fnd_Message.Set_Name('IGS','IGS_AD_HDR_REC_LEN_NOT_CORRECT');
2116 IGS_GE_MSG_STACK.ADD;
2117 p_record_type := 'X';
2118 END IF;
2119 ELSIF (v_first_char = 'P') THEN
2120 p_record_type := 'P';
2121 IF (LENGTH(v_input_string) <> 51) THEN
2122 Fnd_Message.Set_Name('IGS','IGS_AD_PRE_NM_REC_LEN_NOT_CRCT');
2123 IGS_GE_MSG_STACK.ADD;
2124 p_record_type:= 'X';
2125 END IF;
2126 ELSE
2127 Fnd_Message.Set_Name('IGS','IGS_GE_UNKNOWN_REC_TYPE');
2128 IGS_GE_MSG_STACK.ADD;
2129 p_record_type := 'X';
2130 END IF;
2131 EXCEPTION
2132 WHEN NO_DATA_FOUND THEN
2133 p_record_type := 'F';
2134 RETURN;
2135 WHEN OTHERS THEN
2136 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2137 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admpl_read_rec');
2138 IGS_GE_MSG_STACK.ADD;
2139 App_Exception.Raise_Exception;
2140 END admpl_read_rec;
2141 ---------------
2142 ---------------
2143 BEGIN
2144 -- initialise variables
2145 v_previous_name_records_cnt := 0;
2146 t_previous_name := t_previous_name_clear;
2147 v_number_of_matches := 0;
2148 t_matched_ids := t_matched_ids_clear;
2149 -- Validate parameters and open files.
2150 IF (p_input_file IS NULL) THEN
2151 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
2152 IGS_GE_MSG_STACK.ADD;
2153 RETURN;
2154 END IF;
2155 IF (p_output_file IS NULL) THEN
2156 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
2157 IGS_GE_MSG_STACK.ADD;
2158 RETURN;
2159 END IF;
2160 IF (p_directory IS NULL) THEN
2161 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
2162 IGS_GE_MSG_STACK.ADD;
2163 RETURN;
2164 END IF;
2165 -- Open input file
2166 BEGIN
2167 fp_input := UTL_FILE.FOPEN(p_directory, p_input_file, 'R');
2168 EXCEPTION
2169 WHEN UTL_FILE.INVALID_OPERATION THEN
2170 Fnd_Message.Set_Name('IGS','IGS_GE_UNABLE_READ_FILE');
2171 IGS_GE_MSG_STACK.ADD;
2172 RETURN;
2173 END;
2174 -- Open output file
2175 BEGIN
2176 fp_output := UTL_FILE.FOPEN(p_directory, p_output_file, 'W');
2177 EXCEPTION
2178 WHEN UTL_FILE.INVALID_OPERATION THEN
2179 Fnd_Message.Set_Name('IGS','IGS_GE_FAILED_TO_CREATE_FILE');
2180 IGS_GE_MSG_STACK.ADD;
2181 RETURN;
2182 END;
2183 -- Call to function to read from file and return record type.
2184 admpl_read_rec(
2185 v_record_type,
2186 v_input_string);
2187 IF (v_record_type = 'X') THEN
2188 UTL_FILE.FCLOSE(fp_input);
2189 UTL_FILE.FCLOSE(fp_output);
2190 RETURN;
2191 ELSIF (v_record_type <> 'H') THEN
2192 Fnd_Message.Set_Name('IGS','IGS_AD_FIRST_REC_MUST HDR_REC');
2193 IGS_GE_MSG_STACK.ADD;
2194 UTL_FILE.FCLOSE(fp_input);
2195 UTL_FILE.FCLOSE(fp_output);
2196 RETURN;
2197 ELSE
2198 admpl_store_rec(
2199 v_record_type,
2200 v_input_string,
2201 v_previous_name_records_cnt);
2202 END IF;
2203 LOOP
2204 -- Call to function to read from file and return record type (1)
2205 admpl_read_rec(
2206 v_record_type,
2207 v_input_string);
2208 IF (v_record_type = 'H') THEN
2209 -- Process previous header (3)
2210 IF (admpl_prc_previous_header(
2211 v_previous_name_records_cnt,
2212 v_number_of_matches) = FALSE) THEN
2213 RETURN;
2214 END IF;
2215 -- Store new header record in structure (2)
2216 admpl_store_rec(
2217 v_record_type,
2218 v_input_string,
2219 v_previous_name_records_cnt);
2220 v_previous_name_records_cnt := 0;
2221 ELSIF (v_record_type = 'P') THEN
2222 -- Store previous name record in structure (2)
2223 admpl_store_rec(
2224 v_record_type,
2225 v_input_string,
2226 v_previous_name_records_cnt);
2227 IF (v_previous_name_records_cnt >= 20) THEN
2228 Fnd_Message.Set_Name('IGS','IGS_AD_TOO_MANY_PRE_NM_REC');
2229 IGS_GE_MSG_STACK.ADD;
2230 EXIT;
2231 END IF;
2232 ELSIF (v_record_type = 'F') THEN
2233 -- Process previous header (3)
2234 IF (admpl_prc_previous_header(
2235 v_previous_name_records_cnt,
2236 v_number_of_matches) = FALSE) THEN
2237 RETURN;
2238 END IF;
2239 EXIT;
2240 ELSIF (v_record_type = 'X') THEN
2241 EXIT;
2242 ELSE
2243 Fnd_Message.Set_Name('IGS','IGS_GE_UNKNOWN_REC_TYPE');
2244 IGS_GE_MSG_STACK.ADD;
2245 EXIT;
2246 END IF;
2247 END LOOP;
2248 UTL_FILE.FCLOSE(fp_input);
2249 UTL_FILE.FCLOSE(fp_output);
2250 END;
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2254 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admp_ext_tac_arts');
2255 IGS_GE_MSG_STACK.ADD;
2256 App_Exception.Raise_Exception;
2257 END admp_ext_tac_arts;
2258 Procedure Admp_Ext_Vtac_Return(
2259 errbuf OUT NOCOPY VARCHAR2,
2260 retcode OUT NOCOPY NUMBER,
2261 p_acad_perd IN VARCHAR2,
2262 p_input_file IN VARCHAR2,
2263 p_org_id IN NUMBER)
2264 IS
2265 p_acad_cal_type igs_ca_inst.cal_type%type;
2266 p_acad_ci_sequence_number igs_ca_inst.sequence_number%type ;
2267 BEGIN -- admp_ext_vtac_return
2268 --Block for Parameter Validation/Splitting of Parameters
2269
2270 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
2271 igs_ge_gen_003.set_org_id(null);
2272
2273 retcode:=0;
2274 DECLARE
2275 invalid_parameter EXCEPTION;
2276 BEGIN
2277 p_acad_cal_type := RTRIM(SUBSTR(p_acad_perd, 101, 10));
2278 p_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM(RTRIM(SUBSTR(p_acad_perd, 112, 6)));
2279 END;
2280 --End of Block for Parameter Validation/Splitting of Parameters
2281 --This module will read the VTAC enrolment return file, retrieve enrolment
2282 --information for the student by calling IGS_AD_GEN_010.ADMP_GET_TAC_RETURN and then write
2283 --out the new details to a new file in the required TAC format
2284 DECLARE
2285 fp_input UTL_FILE.FILE_TYPE;
2286 v_tmp_dir VARCHAR2(80);
2287 v_message_name VARCHAR2(30);
2288 --Vars to hold the respective components of the input string
2289 v_vtac_number VARCHAR2(9);
2290 v_surname VARCHAR2(24);
2291 v_gname1 VARCHAR2(17);
2292 v_gname2 VARCHAR2(17);
2293 v_category VARCHAR2(3);
2294 v_vtac_course_cd VARCHAR2(5);
2295 v_street VARCHAR2(25);
2296 v_suburb VARCHAR2(25);
2297 v_state VARCHAR2(3);
2298 v_postcode VARCHAR2(4);
2299 v_country VARCHAR2(14);
2300 v_enrol_ind VARCHAR2(1);
2301 v_round_no VARCHAR2(1);
2302 v_accept_ind VARCHAR2(1);
2303 v_birth_date VARCHAR2(8);
2304 --Variables to pass to external functions OUT NOCOPY vars
2305 v_offer_response VARCHAR2(10);
2306 v_enrol_status VARCHAR2(10);
2307 v_attendance_type IGS_EN_ATD_TYPE.govt_attendance_type%TYPE;
2308 v_attendance_mode IGS_EN_ATD_MODE.govt_attendance_mode%TYPE;
2309 --Define a function for reading the input file. This needs to
2310 --handle the exception when end of file is reached
2311 FUNCTION admpl_read_input
2312 RETURN BOOLEAN
2313 IS
2314 v_input_string VARCHAR2(255);
2315 BEGIN --admpl_read_input
2316 --this sub program reads the input file and splits up the
2317 --input string into the required components
2318 UTL_FILE.GET_LINE(fp_input, v_input_string);
2319 --split up the input string into its respective components
2320 v_vtac_number := RTRIM(SUBSTR(v_input_string, 1, 9));
2321 v_surname := RTRIM(SUBSTR(v_input_string, 10, 24));
2322 v_gname1 := RTRIM(SUBSTR(v_input_string, 34, 17));
2323 v_gname2 := RTRIM(SUBSTR(v_input_string, 51, 17));
2324 v_category := RTRIM(SUBSTR(v_input_string, 68, 3));
2325 v_vtac_course_cd := RTRIM(SUBSTR(v_input_string, 71, 5));
2326 v_street := RTRIM(SUBSTR(v_input_string, 76, 25));
2327 v_suburb := RTRIM(SUBSTR(v_input_string, 101, 25));
2328 v_state := RTRIM(SUBSTR(v_input_string, 126, 3));
2329 v_postcode := RTRIM(SUBSTR(v_input_string, 129, 4));
2330 v_country := RTRIM(SUBSTR(v_input_string, 133, 14));
2331 v_enrol_ind := RTRIM(SUBSTR(v_input_string, 147, 1));
2332 v_round_no := RTRIM(SUBSTR(v_input_string, 148, 1));
2333 v_accept_ind := RTRIM(SUBSTR(v_input_string, 149, 1));
2334 v_birth_date := SUBSTR(v_input_string, 150, 8);
2335 RETURN TRUE;
2336 EXCEPTION
2337 WHEN NO_DATA_FOUND THEN
2338 UTL_FILE.FCLOSE(fp_input);
2339 RETURN FALSE;
2340 WHEN UTL_FILE.READ_ERROR THEN
2341 Fnd_Message.Set_Name('IGS','IGS_GE_UNABLE_READ_FILE');
2342 IGS_GE_MSG_STACK.ADD;
2343 App_Exception.Raise_Exception;
2344 RETURN FALSE;
2345 END admpl_read_input;
2346 -- Define a procedure for writing the output file.
2347 -- The output file format is the same
2348 --as the input file. We only need to change the value of one field,
2349 -- enrol_ind. All other fields can be copied directly from the input file.
2350 PROCEDURE admpl_write_output (v_new_enrol_ind VARCHAR2)
2351 IS
2352 v_output_string VARCHAR2(255);
2353 BEGIN --admpl_write_output
2354 --Create the output string
2355 v_output_string :=
2356 RPAD(NVL(v_vtac_number, ' '), 9) ||
2357 RPAD(NVL(v_surname, ' '), 24) ||
2358 RPAD(NVL(v_gname1, ' '), 17) ||
2359 RPAD(NVL(v_gname2, ' '), 17) ||
2360 RPAD(NVL(v_category, ' '), 3) ||
2361 RPAD(NVL(v_vtac_course_cd, ' '), 5) ||
2362 RPAD(NVL(v_street, ' '), 25) ||
2363 RPAD(NVL(v_suburb, ' '), 25) ||
2364 RPAD(NVL(v_state, ' '), 3) ||
2365 RPAD(NVL(v_postcode, ' '), 4) ||
2366 RPAD(NVL(v_country, ' '), 14) ||
2367 RPAD(NVL(v_new_enrol_ind, ' '), 1) ||
2368 RPAD(NVL(v_round_no, ' '), 1) ||
2369 RPAD(NVL(v_accept_ind, ' '), 1) ||
2370 RPAD(NVL(v_birth_date, ' '), 8);
2371 --Write the output string to file
2372 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, v_output_string);
2373 END admpl_write_output;
2374 BEGIN
2375 --Validate parameters (all must have values)
2376 IF (p_acad_cal_type IS NULL OR
2377 p_acad_ci_sequence_number IS NULL OR
2378 p_input_file IS NULL) THEN
2379 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
2380 IGS_GE_MSG_STACK.ADD;
2381 App_Exception.Raise_Exception;
2382 END IF;
2383 fnd_profile.get('IGS_IN_FILE_PATH',v_tmp_dir);
2384 --Open input file
2385 BEGIN
2386 fp_input := UTL_FILE.FOPEN(v_tmp_dir, p_input_file, 'R');
2387 EXCEPTION
2388 WHEN UTL_FILE.INVALID_OPERATION THEN
2389 Fnd_Message.Set_Name('IGS','IGS_GE_UNABLE_READ_FILE');
2390 IGS_GE_MSG_STACK.ADD;
2391 App_Exception.Raise_Exception;
2392 WHEN UTL_FILE.INVALID_PATH THEN
2393 Fnd_Message.Set_Name('IGS','IGS_GE_UNABLE_READ_FILE');
2394 IGS_GE_MSG_STACK.ADD;
2395 App_Exception.Raise_Exception;
2396 END;
2397 WHILE admpl_read_input LOOP
2398 IF (v_vtac_number IS NULL OR
2399 v_vtac_course_cd IS NULL) THEN
2400 admpl_write_output('X');
2401 ELSIF (IGS_AD_GEN_010.ADMP_GET_TAC_RETURN(
2402 v_vtac_number,
2403 v_surname,
2404 v_gname1,
2405 v_gname2,
2406 v_vtac_course_cd,
2407 p_acad_cal_type,
2408 p_acad_ci_sequence_number,
2409 v_offer_response,
2410 v_enrol_status,
2411 v_attendance_type,
2412 v_attendance_mode,
2413 v_message_name) = FALSE) THEN
2414 FND_FILE.PUT_LINE(FND_FILE.LOG, v_vtac_number ||' '|| v_vtac_course_cd ||' '||
2415 fnd_message.get_string('IGS',v_message_name));
2416 admpl_write_output('X');
2417 ELSE
2418 IF (v_enrol_status = 'ENROLLED') THEN
2419 IF (v_attendance_type = '1') THEN
2420 admpl_write_output('F');
2421 ELSE
2422 admpl_write_output('P');
2423 END IF;
2424 ELSIF (v_enrol_status = 'NOT-ENROL') THEN
2425 IF (v_offer_response = 'DFR-GRANT') THEN
2426 admpl_write_output('D');
2427 ELSE
2428 admpl_write_output('X');
2429 END IF;
2430 ELSIF (v_enrol_status = 'INTERMIT') THEN
2431 IF (v_attendance_type = '1') THEN
2432 admpl_write_output('F');
2433 ELSIF (v_attendance_type = '2') THEN
2434 admpl_write_output('P');
2435 ELSE
2436 admpl_write_output('E');
2437 END IF;
2438 ELSIF(v_enrol_status = 'DISCONTIN') THEN
2439 IF (v_attendance_type = '1') THEN
2440 admpl_write_output('F');
2441 ELSIF (v_attendance_type = '2') THEN
2442 admpl_write_output('P');
2443 ELSE
2444 admpl_write_output('E');
2445 END IF;
2446 ELSE
2447 admpl_write_output('X');
2448 END IF;
2449 END IF;
2450 END LOOP;
2451 --Close all files
2452 UTL_FILE.FCLOSE_ALL;
2453 RETURN;
2454 END;
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 Retcode := 2;
2458 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2459 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2460 END admp_ext_vtac_return;
2461 Function Admp_Get_Aal_Sent_Dt(
2462 p_person_id IN NUMBER ,
2463 p_admission_appl_number IN NUMBER ,
2464 p_correspondence_type IN VARCHAR2 ,
2465 p_sequence_number IN NUMBER )
2466 RETURN DATE IS
2467 BEGIN -- This module retrieves the issue date for an Admission Application
2468 -- Letter
2469 -- admp_get_aal_sent_dt
2470 DECLARE
2471 cst_spl_seqnum CONSTANT VARCHAR2(10) := 'SPL_SEQNUM';
2472 v_spl_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE;
2473 v_issue_dt IGS_CO_OU_CO_REF.issue_dt%TYPE;
2474 CURSOR c_aal IS
2475 SELECT spl_sequence_number
2476 FROM IGS_AD_APPL_LTR
2477 WHERE person_id = p_person_id AND
2478 admission_appl_number = p_admission_appl_number AND
2479 correspondence_type = p_correspondence_type AND
2480 sequence_number = p_sequence_number;
2481 CURSOR c_ocr (
2482 p_person_id IGS_AD_APPL_LTR.person_id%TYPE,
2483 p_correspondence_type IGS_AD_APPL_LTR.correspondence_type%TYPE,
2484 cp_spl_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE) IS
2485 SELECT issue_dt
2486 FROM IGS_CO_OU_CO_REF
2487 WHERE person_id = p_person_id AND
2488 correspondence_type = p_correspondence_type AND
2489 s_other_reference_type = cst_spl_seqnum AND
2490 other_reference = IGS_GE_NUMBER.TO_CANN(cp_spl_sequence_number);
2491 BEGIN
2492 OPEN c_aal;
2493 FETCH c_aal INTO v_spl_sequence_number;
2494 IF (c_aal%FOUND) THEN
2495 CLOSE c_aal;
2496 IF v_spl_sequence_number IS NULL THEN
2497 RETURN NULL;
2498 ELSE
2499 OPEN c_ocr(
2500 p_person_id,
2501 p_correspondence_type,
2502 v_spl_sequence_number);
2503 FETCH c_ocr INTO v_issue_dt;
2504 IF (c_ocr%NOTFOUND) THEN
2505 CLOSE c_ocr;
2506 RETURN NULL;
2507 ELSE
2508 CLOSE c_ocr;
2509 v_issue_dt := TRUNC(v_issue_dt);
2510 RETURN v_issue_dt;
2511 END IF;
2512 END IF;
2513 END IF;
2514 CLOSE c_aal;
2515 RETURN NULL;
2516 END;
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2520 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admp_get_aal_sent_dt');
2521 IGS_GE_MSG_STACK.ADD;
2522 App_Exception.Raise_Exception;
2523 END admp_get_aal_sent_dt;
2524 Function Admp_Get_Aa_Aas(
2525 p_person_id IN NUMBER ,
2526 p_admission_appl_number IN NUMBER ,
2527 p_adm_appl_status IN VARCHAR2 )
2528 RETURN VARCHAR2 IS
2529 BEGIN -- admp_get_aa_aas
2530 -- Return the derived admission application status
2531 -- for an admission application.
2532 -- Navin Sinha 18-Feb-2002, corrected the datatype of v_s_adm_outcome_status.
2533 -- Arvind S. This function is modified as a part of igs.m
2534 -- The overall derviation logic is modified. Withdrawn status is now always a derived value
2535 DECLARE
2536 CURSOR c_aca IS
2537 SELECT --aca.req_for_reconsideration_ind,
2538 acai.adm_outcome_status,
2539 acai.adm_offer_resp_status,
2540 acai.adm_offer_dfrmnt_status,
2541 acai.appl_inst_status,
2542 acai.def_term_adm_appl_num,
2543 acai.def_appl_sequence_num
2544 FROM --IGS_AD_PS_APPL aca,
2545 IGS_AD_PS_APPL_INST acai
2546 WHERE acai.person_id = p_person_id AND
2547 acai.admission_appl_number = p_admission_appl_number;
2548 --AND aca.person_id = acai.person_id AND
2549 --aca.admission_appl_number = acai.admission_appl_number AND
2550 --aca.nominated_course_cd = acai.nominated_course_cd;
2551 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
2552 cst_received CONSTANT VARCHAR2(10) := 'RECEIVED';
2553 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
2554 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
2555 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
2556 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
2557 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
2558 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
2559 cst_voided CONSTANT VARCHAR2(10) := 'VOIDED';
2560 cst_no_quota CONSTANT VARCHAR2(10) := 'NO-QUOTA';
2561 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
2562 cst_confirm CONSTANT VARCHAR2(10) := 'CONFIRM';
2563 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
2564 cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
2565 cst_notapplic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
2566 v_received_appl BOOLEAN DEFAULT FALSE;
2567 v_withdrawn_appl BOOLEAN DEFAULT FALSE;
2568 v_completed_appl BOOLEAN DEFAULT FALSE;
2569 v_acai_records_found BOOLEAN DEFAULT FALSE;
2570 v_rej_lap BOOLEAN DEFAULT FALSE;
2571 v_s_appl_inst_stat igs_ad_ps_appl_inst.appl_inst_status%TYPE;
2572 v_s_adm_appl_status igs_ad_appl_stat.s_adm_appl_status%TYPE;
2573 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
2574 v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
2575 -- v_exit_loop NUMBER DEFAULT 0;
2576 v_s_adm_offer_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
2577 BEGIN
2578 -- Determine the system admission application status.
2579 v_s_adm_appl_status := IGS_AD_GEN_007.ADMP_GET_SAAS(p_adm_appl_status);
2580 -- Derive the admission application status.
2581 IF v_s_adm_appl_status IN (
2582 cst_received,
2583 cst_withdrawn,
2584 cst_completed) THEN
2585 -- To decide if the system Applicatiom Status(APS) can be derived to 'WITHDRAWN'
2586 FOR v_acai_rec IN c_aca LOOP
2587 IF IGS_AD_GEN_008.ADMP_GET_SAOS(v_acai_rec.adm_outcome_status) IN (cst_offer, cst_cond_offer)
2588 AND
2589 IGS_AD_GEN_008.ADMP_GET_SAORS(v_acai_rec.adm_offer_resp_status) NOT IN (cst_rejected, cst_lapsed, cst_notapplic) THEN
2590 v_rej_lap := TRUE;
2591 EXIT;
2592 END IF;
2593 END LOOP;
2594 -- Retrieve admission course application instance details.
2595 FOR v_aca_rec IN c_aca LOOP
2596 v_acai_records_found := TRUE;
2597 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(
2598 v_aca_rec.adm_outcome_status);
2599 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(
2600 v_aca_rec.adm_offer_resp_status);
2601 v_s_adm_offer_dfrmnt_status := IGS_AD_GEN_008.ADMP_GET_SAODS(
2602 v_aca_rec.adm_offer_dfrmnt_status);
2603 v_s_appl_inst_stat := NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(v_aca_rec.appl_inst_status),'-1');
2604 -- Check if the outcome of the admission course
2605 -- application instance is complete or has been resolved.
2606 IF NOT( v_s_adm_outcome_status IN (cst_cancelled, cst_voided, cst_withdrawn,cst_no_quota, cst_rejected)
2607 OR
2608 ( v_s_adm_outcome_status IN (cst_offer, cst_cond_offer)
2609 AND
2610 ( v_s_adm_offer_resp_status IN (cst_accepted, cst_rejected, cst_lapsed)
2611 OR
2612 ( v_s_adm_offer_resp_status IN (cst_deferral)
2613 AND
2614 v_s_adm_offer_dfrmnt_status IN (cst_confirm)
2615 OR
2616 ( v_s_adm_offer_dfrmnt_status IN (cst_approved)
2617 AND
2618 ( v_aca_rec.def_term_adm_appl_num IS NOT NULL
2619 OR
2620 v_aca_rec.def_appl_sequence_num IS NOT NULL
2621 )
2622 )
2623
2624
2625 )
2626 )
2627 )
2628 OR
2629 ( v_s_appl_inst_stat = cst_withdrawn
2630 )
2631 ) THEN
2632 -- Set a flag indicating that at least one admission
2633 -- course application instance would make the admission
2634 -- application not complete, so exit.
2635 -- Else need to continue looping through all the remaining
2636 -- admission course application records.
2637 v_received_appl := TRUE;
2638 ELSIF (v_s_appl_inst_stat = cst_withdrawn AND v_rej_lap = FALSE) THEN
2639 v_withdrawn_appl := TRUE;
2640 ELSE
2641 v_completed_appl := TRUE;
2642 END IF;
2643 END LOOP;
2644 -- If at least one admission course application instance is in a state that
2645 -- will make the application received, then return a value of received.
2646 -- Or if no admission course application instance records exist for the
2647 -- admission application then also return a value of received.
2648 IF v_acai_records_found = TRUE THEN
2649 IF v_received_appl THEN
2650 IF v_s_adm_appl_status <> cst_received THEN
2651 RETURN IGS_AD_GEN_008.ADMP_GET_SYS_AAS(cst_received);
2652 ELSE
2653 RETURN p_adm_appl_status;
2654 END IF;
2655 ELSIF v_withdrawn_appl THEN
2656 IF v_s_adm_appl_status <> cst_withdrawn THEN
2657 RETURN IGS_AD_GEN_008.ADMP_GET_SYS_AAS(cst_withdrawn);
2658 ELSE
2659 RETURN p_adm_appl_status;
2660 END IF;
2661 ELSIF v_completed_appl THEN
2662 -- If this point is reached then all admission course application
2663 -- instances must have a resolved outcome or withdrawn and therefore the application
2664 -- is complete.
2665 IF v_s_adm_appl_status <> cst_completed THEN
2666 RETURN IGS_AD_GEN_008.ADMP_GET_SYS_AAS(cst_completed);
2667 ELSE
2668 RETURN p_adm_appl_status;
2669 END IF;
2670 END IF;
2671 ELSE
2672 RETURN p_adm_appl_status; -- no acai record found
2673 END IF;
2674 ELSE
2675 RETURN p_adm_appl_status;
2676 END IF;
2677 EXCEPTION
2678 WHEN OTHERS THEN
2679 IF c_aca%ISOPEN THEN
2680 CLOSE c_aca;
2681 END IF;
2682 App_Exception.Raise_Exception;
2683 END;
2684 EXCEPTION
2685 WHEN OTHERS THEN
2686 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2687 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admp_get_aa_aas');
2688 IGS_GE_MSG_STACK.ADD;
2689 App_Exception.Raise_Exception;
2690 END admp_get_aa_aas;
2691 Procedure Admp_Get_Aa_Created(
2692 p_person_id IN NUMBER ,
2693 p_admission_appl_number IN NUMBER ,
2694 p_create_who OUT NOCOPY VARCHAR2 ,
2695 p_create_on OUT NOCOPY DATE )
2696 IS
2697 BEGIN -- admp_get_aa_created
2698 -- Routine to return the date an admission application was created
2699 -- and the Oracle user name of the IGS_PE_PERSON that created the admission
2700 -- application.
2701 DECLARE
2702 v_create_who IGS_AD_APPL.LAST_UPDATED_BY%TYPE DEFAULT NULL;
2703 v_create_on IGS_AD_APPL.LAST_UPDATE_DATE%TYPE DEFAULT NULL;
2704 CURSOR c_aah IS
2705 SELECT aah.hist_who,
2706 aah.hist_start_dt
2707 FROM IGS_AD_APPL_HIST aah
2708 WHERE person_id = p_person_id AND
2709 admission_appl_number = p_admission_appl_number
2710 ORDER BY
2711 hist_start_dt ASC;
2712 CURSOR c_aa IS
2713 SELECT LAST_UPDATED_BY,
2714 LAST_UPDATE_DATE
2715 FROM IGS_AD_APPL
2716 WHERE person_id = p_person_id AND
2717 admission_appl_number = p_admission_appl_number;
2718 BEGIN
2719 -- Determine who the admission application was created by and on what date.
2720 OPEN c_aah;
2721 FETCH c_aah INTO v_create_who,
2722 v_create_on;
2723 IF (c_aah%NOTFOUND) THEN
2724 OPEN c_aa;
2725 FETCH c_aa INTO v_create_who,
2726 v_create_on;
2727 CLOSE c_aa;
2728 END IF;
2729 CLOSE c_aah;
2730 p_create_who := v_create_who;
2731 p_create_on := v_create_on;
2732 END;
2733 EXCEPTION
2734 WHEN OTHERS THEN
2735 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2736 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admp_get_aa_created');
2737 IGS_GE_MSG_STACK.ADD;
2738 App_Exception.Raise_Exception;
2739 END admp_get_aa_created;
2740 Procedure Admp_Get_Aa_Dtl(
2741 p_person_id IN NUMBER ,
2742 p_admission_appl_number IN NUMBER ,
2743 p_admission_cat OUT NOCOPY VARCHAR2 ,
2744 p_s_admission_process_type OUT NOCOPY VARCHAR2 ,
2745 p_acad_cal_type OUT NOCOPY VARCHAR2 ,
2746 p_acad_ci_sequence_number OUT NOCOPY NUMBER ,
2747 p_adm_cal_type OUT NOCOPY VARCHAR2 ,
2748 p_adm_ci_sequence_number OUT NOCOPY NUMBER ,
2749 p_appl_dt OUT NOCOPY DATE ,
2750 p_adm_appl_status OUT NOCOPY VARCHAR2 ,
2751 p_adm_fee_status OUT NOCOPY VARCHAR2 )
2752 IS
2753 BEGIN --admp_get_aa_dtl
2754 --Return admission application details
2755 DECLARE
2756 v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
2757 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2758 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759 v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760 v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761 v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762 v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2763 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
2764 v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
2765 CURSOR c_aa IS
2766 SELECT aa.admission_cat,
2767 aa.s_admission_process_type,
2768 aa.acad_cal_type,
2769 aa.acad_ci_sequence_number,
2770 aa.adm_cal_type,
2771 aa.adm_ci_sequence_number,
2772 aa.appl_dt,
2773 aa.adm_appl_status,
2774 aa.adm_fee_status
2775 FROM IGS_AD_APPL aa
2776 WHERE aa.person_id = p_person_id AND
2777 aa.admission_appl_number = p_admission_appl_number;
2778 BEGIN
2779 OPEN c_aa;
2780 FETCH c_aa INTO v_admission_cat,
2781 v_s_admission_process_type,
2782 v_acad_cal_type,
2783 v_acad_ci_sequence_number,
2784 v_adm_cal_type,
2785 v_adm_ci_sequence_number,
2786 v_appl_dt,
2787 v_adm_appl_status,
2788 v_adm_fee_status;
2789 IF (c_aa%NOTFOUND) THEN
2790 p_admission_cat := NULL;
2791 p_s_admission_process_type := NULL;
2792 p_acad_cal_type := NULL;
2793 p_acad_ci_sequence_number := NULL;
2794 p_adm_cal_type := NULL;
2795 p_adm_ci_sequence_number := NULL;
2796 p_appl_dt := NULL;
2797 p_adm_appl_status := NULL;
2798 p_adm_fee_status := NULL;
2799 ELSE
2800 p_admission_cat := v_admission_cat;
2801 p_s_admission_process_type := v_s_admission_process_type;
2802 p_acad_cal_type := v_acad_cal_type;
2803 p_acad_ci_sequence_number := v_acad_ci_sequence_number;
2804 p_adm_cal_type := v_adm_cal_type;
2805 p_adm_ci_sequence_number := v_adm_ci_sequence_number;
2806 p_appl_dt := v_appl_dt;
2807 p_adm_appl_status := v_adm_appl_status;
2808 p_adm_fee_status := v_adm_fee_status;
2809 END IF;
2810 CLOSE c_aa;
2811 END;
2812 EXCEPTION
2813 WHEN OTHERS THEN
2814 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2815 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admp_get_aa_dtl');
2816 IGS_GE_MSG_STACK.ADD;
2817 App_Exception.Raise_Exception;
2818 END admp_get_aa_dtl;
2819 Function Admp_Get_Acai_Acadcd(
2820 p_person_id IN NUMBER ,
2821 p_admission_appl_number IN NUMBER ,
2822 p_acad_cal_type IN VARCHAR2 ,
2823 p_adm_cal_type IN VARCHAR2 ,
2824 p_adm_ci_sequence_number IN NUMBER )
2825 RETURN VARCHAR2 IS
2826 BEGIN -- admp_get_acai_acadcd
2827 -- routine to return the academic alternate code of the Admission Application
2828 -- relating the Admission course Application Instance admission period.
2829 DECLARE
2830 CURSOR c_aa IS
2831 SELECT aa.acad_cal_type
2832 FROM IGS_AD_APPL aa
2833 WHERE person_id = p_person_id AND
2834 admission_appl_number = p_admission_appl_number;
2835 CURSOR c_cir (
2836 cp_acad_cal_type IN IGS_AD_APPL.acad_cal_type%TYPE,
2837 cp_adm_cal_type IN IGS_AD_PS_APPL_INST.adm_cal_type%TYPE,
2838 cp_adm_ci_sequence_number IN
2839 IGS_AD_PS_APPL_INST.adm_ci_sequence_number%TYPE) IS
2840 SELECT ci.alternate_code, ci.start_dt
2841 FROM IGS_CA_INST_REL cir,
2842 IGS_CA_INST ci
2843 WHERE cir.sup_cal_type = cp_acad_cal_type AND
2844 cir.sub_cal_type = cp_adm_cal_type AND
2845 cir.sub_ci_sequence_number = cp_adm_ci_sequence_number AND
2846 cir.sup_cal_type = ci.cal_type AND
2847 cir.sup_ci_sequence_number = ci.sequence_number
2848 ORDER BY ci.start_dt;
2849 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2850 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
2851 v_start_dt IGS_CA_INST.start_dt%TYPE;
2852 BEGIN
2853 IF p_acad_cal_type IS NULL THEN
2854 OPEN c_aa;
2855 FETCH c_aa INTO v_acad_cal_type;
2856 IF (c_aa%NOTFOUND) THEN
2857 CLOSE c_aa;
2858 RETURN NULL;
2859 END IF;
2860 CLOSE c_aa;
2861 ELSE
2862 v_acad_cal_type := p_acad_cal_type;
2863 END IF;
2864 OPEN c_cir ( v_acad_cal_type,
2865 p_adm_cal_type,
2866 p_adm_ci_sequence_number);
2867 FETCH c_cir INTO v_alternate_code,v_start_dt;
2868 IF (c_cir%NOTFOUND) THEN
2869 CLOSE c_cir;
2870 RETURN NULL;
2871 ELSE
2872 CLOSE c_cir;
2873 RETURN v_alternate_code;
2874 END IF;
2875 END;
2876 EXCEPTION
2877 WHEN OTHERS THEN
2878 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2879 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admp_get_acai_acadcd');
2880 END admp_get_acai_acadcd;
2881 Function Admp_Get_Acai_Aos_Dt(
2882 p_person_id IN NUMBER ,
2883 p_admission_appl_number IN NUMBER ,
2884 p_nominated_course_cd IN VARCHAR2 ,
2885 p_acai_sequence_number IN NUMBER )
2886 RETURN DATE IS
2887 BEGIN -- admp_get_acai_aos_dt
2888 -- This module gets the date that the current
2889 -- IGS_AD_PS_APPL_INST.adm_outcome_status was set.
2890 DECLARE
2891 v_decision_date IGS_AD_PS_APPL_INST.decision_date%TYPE;
2892 CURSOR c_acai IS
2893 SELECT acai.decision_date
2894 FROM IGS_AD_PS_APPL_INST acai
2895 WHERE acai.person_id = p_person_id AND
2896 acai.admission_appl_number = p_admission_appl_number AND
2897 acai.nominated_course_cd = p_nominated_course_cd AND
2898 acai.sequence_number = p_acai_sequence_number AND
2899 acai.adm_outcome_status IS NOT NULL
2900 ORDER BY acai.decision_date DESC;
2901 BEGIN
2902 OPEN c_acai;
2903 FETCH c_acai INTO v_decision_date;
2904 CLOSE c_acai;
2905 RETURN v_decision_date;
2906 END;
2907 EXCEPTION
2908 WHEN OTHERS THEN
2909 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2910 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.admp_get_acai_aos_dt');
2911 IGS_GE_MSG_STACK.ADD;
2912 App_Exception.Raise_Exception;
2913 END admp_get_acai_aos_dt;
2914 Procedure ADMS_EXT_TAC_ARTS (
2915 errbuf out NOCOPY varchar2,
2916 retcode out NOCOPY number,
2917 p_input_file IN VARCHAR2,
2918 p_org_id IN NUMBER )
2919 IS
2920 v_dir_path VARCHAR2(255);
2921 v_output_file VARCHAR2(100);
2922 v_log_message VARCHAR2(200);
2923 v_length NUMBER(2);
2924 v_input_file varchar2(100);
2925 v_last_char VARCHAR2(1);
2926 BEGIN
2927
2928 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
2929 igs_ge_gen_003.set_org_id(null);
2930
2931 retcode := 0;
2932 v_length := 0;
2933 --Get the In_file_Directory Path
2934 v_dir_path := nvl(RTRIM(FND_PROFILE.VALUE('IGS_IN_FILE_PATH')),' ');
2935 v_last_char := SUBSTR(v_dir_path,LENGTH(v_dir_path),1);
2936 IF v_last_char IN ('/','\') THEN -- '/' To match UNIX & '\' for NT
2937 v_dir_path := SUBSTR(v_dir_path,1,LENGTH(v_dir_path)-1);
2938 END IF;
2939 --Prepare the Output file name
2940 v_length := INSTR(p_input_file,'.');
2941 v_input_file := ltrim(rtrim(p_input_file));
2942 IF v_length > 0 THEN
2943 v_output_file := substr(v_input_file,1,v_length-1);
2944 ELSE
2945 v_output_file := v_input_file;
2946 END IF;
2947 v_output_file := v_output_file||'.out';
2948 --Now call ADMP_EXT_TAC_ARTS with parameters
2949 IGS_AD_GEN_002.admp_ext_tac_arts(
2950 v_input_file ,
2951 v_output_file ,
2952 v_dir_path );
2953 Exception
2954 WHEN OTHERS THEN
2955 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2956 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2957 retcode:=2;
2958 END ADMS_EXT_TAC_ARTS;
2959 Function Admp_Get_Appl_ID(
2960 p_person_id IN NUMBER ,
2961 p_admission_appl_number IN NUMBER)
2962 RETURN NUMBER IS
2963 /*******************************************************************************
2964 Created by : Kedarnath Nag
2965 Date created: 04 OCT 2002
2966 Purpose:
2967 To get application ID
2968 Known limitations/enhancements and/or remarks:
2969 Change History: (who, when, what: )
2970 Who When What
2971 *******************************************************************************/
2972 CURSOR c_application_id IS
2973 SELECT application_id
2974 FROM igs_ad_appl_all
2975 WHERE person_id = p_person_id
2976 AND admission_appl_number = p_admission_appl_number;
2977 l_application_id igs_ad_appl_all.application_id%TYPE;
2978 BEGIN
2979 IF p_person_id IS NOT NULL AND
2980 p_admission_appl_number IS NOT NULL THEN
2981 FOR c_application_id_rec IN c_application_id
2982 LOOP
2983 l_application_id := c_application_id_rec.application_id;
2984 END LOOP;
2985 END IF;
2986 RETURN l_application_id;
2987 END Admp_Get_Appl_ID;
2988 Function Admp_Get_Fee_Status(
2989 p_person_id IN NUMBER ,
2990 p_admission_appl_number IN NUMBER)
2991 RETURN VARCHAR2 IS
2992 /*******************************************************************************
2993 Created by : Kedarnath Nag
2994 Date created: 04 OCT 2002
2995 Modified completely as a part of IGS.M -- arvsrini
2996 Purpose:
2997 To get application fee status, return Pending if appl fee status not in Paid,Waived,Partial
2998 Known limitations/enhancements and/or remarks:
2999 Change History: (who, when, what: )
3000 Who When What
3001 *******************************************************************************/
3002 l_FeeAmt igs_ad_appl_all.appl_fee_amt%TYPE :=0;
3003 l_TotalRemitt igs_ad_app_req.fee_amount%TYPE :=0;
3004 l_WaivedTemp igs_ad_app_req.fee_amount%TYPE :=0;
3005 lOverallStatus igs_lookup_values.meaning%TYPE;
3006 CURSOR c_adm_fee_amt IS
3007 SELECT appl_fee_amt
3008 FROM igs_ad_appl_all
3009 WHERE person_id = p_person_id
3010 AND admission_appl_number = p_admission_appl_number;
3011 CURSOR c_appl_fee_status_meaning (p_sys_fee_status igs_ad_code_classes.class%TYPE) IS
3012 SELECT meaning
3013 FROM igs_lookup_values
3014 WHERE lookup_type = 'SYS_FEE_STATUS'
3015 AND lookup_code = p_sys_fee_status;
3016 CURSOR c_fee_amount IS
3017 SELECT req.fee_amount,
3018 cc.system_status fee_status
3019 FROM igs_ad_app_req req,
3020 igs_ad_code_classes cc
3021 WHERE req.person_id = p_person_id
3022 AND req.admission_appl_number = p_admission_appl_number
3023 AND cc.CLASS = 'SYS_FEE_STATUS'
3024 AND cc.CODE_ID = req.applicant_fee_status
3025 AND EXISTS (SELECT 'x'
3026 FROM igs_ad_code_classes
3027 WHERE class = 'SYS_FEE_TYPE'
3028 AND system_status = 'APPL_FEE'
3029 AND req.applicant_fee_type = code_id)
3030 ORDER BY req.fee_date;
3031 BEGIN
3032 /* the logic of this function was changed as a part of IGS.M -- arvsrini */
3033 /* initializing variables*/
3034 OPEN c_adm_fee_amt;
3035 FETCH c_adm_fee_amt INTO l_FeeAmt;
3036 CLOSE c_adm_fee_amt;
3037 FOR c_fee_amount_rec IN c_fee_amount LOOP
3038 l_TotalRemitt:= l_TotalRemitt+ c_fee_amount_rec.fee_amount;
3039 END LOOP;
3040 FOR c_fee_amount_rec IN c_fee_amount LOOP
3041 IF c_fee_amount_rec.fee_status <> 'WAIVED' THEN
3042 EXIT;
3043 END IF;
3044 l_WaivedTemp:= l_WaivedTemp + c_fee_amount_rec.fee_amount;
3045 END LOOP;
3046 /*determining status*/
3047 IF l_FeeAmt= 0 THEN
3048 lOverallStatus:='PENDING';
3049 ELSIF l_TotalRemitt >= l_FeeAmt THEN
3050 IF l_WaivedTemp < l_FeeAmt THEN
3051 --the sum of fee amount of all WAIVED records
3052 --before the first record with status <> WAIVED
3053 lOverallStatus:= 'PAID';
3054 --this includes cases where full payment was made or cases
3055 --where a partial payment was made before the rest was waived
3056 ELSE
3057 lOverallStatus:='WAIVED';
3058 END IF;
3059 ELSE
3060 lOverallStatus:='PENDING';
3061 END IF;
3062 /*obtaining meaning of fee status*/
3063 IF lOverallStatus IS NOT NULL THEN
3064 OPEN c_appl_fee_status_meaning (lOverallStatus);
3065 FETCH c_appl_fee_status_meaning INTO lOverallStatus;
3066 CLOSE c_appl_fee_status_meaning;
3067 END IF;
3068 RETURN lOverallStatus;
3069 END Admp_Get_Fee_Status;
3070 PROCEDURE check_adm_appl_inst_stat(
3071 p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
3072 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
3073 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE DEFAULT NULL,
3074 p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE DEFAULT NULL,
3075 p_updateable VARCHAR2 DEFAULT 'N' -- apadegal - TD001 - IGS.M.
3076 ) IS
3077 /*******************************************************************************
3078 Created by : Kedarnath Nag
3079 Date created: 11 MAR 2003
3080 Purpose:
3081 To check whether the application details can be added/modified/deleted and
3082 raise appropriate error incase of failure
3083 Known limitations/enhancements and/or remarks:
3084 Change History: (who, when, what: )
3085 Who When What
3086 *******************************************************************************/
3087 l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE;
3088 l_adm_appl_status_old igs_ad_appl.adm_appl_status%TYPE;
3089 l_adm_appl_status_new igs_ad_appl.adm_appl_status%TYPE;
3090 l_appl_inst_status igs_ad_ps_appl_inst.appl_inst_status%TYPE;
3091 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
3092 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
3093 cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
3094 CURSOR c_adm_appl_status (cp_person_id igs_ad_appl.person_id%TYPE,
3095 cp_admission_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
3096 SELECT adm_appl_status
3097 FROM igs_ad_appl
3098 WHERE person_id = cp_person_id
3099 AND admission_appl_number = cp_admission_appl_number;
3100 CURSOR c_adm_outcome_status (cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
3101 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
3102 cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
3103 cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
3104 SELECT adm_outcome_status,
3105 appl_inst_status, --arvsrini
3106 def_term_adm_appl_num,
3107 def_appl_sequence_num
3108 FROM igs_ad_ps_appl_inst
3109 WHERE person_id = cp_person_id
3110 AND admission_appl_number= cp_admission_appl_number
3111 AND nominated_course_cd = cp_nominated_course_cd
3112 AND sequence_number = cp_sequence_number;
3113
3114 l_offer_inst VARCHAR2(1) DEFAULT 'N';
3115 l_is_inst_complete BOOLEAN DEFAULT FALSE ;
3116 l_def_term_adm_appl_num igs_ad_ps_appl_inst.def_term_adm_appl_num%TYPE DEFAULT NULL;
3117 l_def_appl_sequence_num igs_ad_ps_appl_inst.def_term_adm_appl_num%TYPE DEFAULT NULL;
3118 BEGIN
3119
3120 OPEN c_adm_appl_status (p_person_id,
3121 p_admission_appl_number
3122 );
3123 FETCH c_adm_appl_status INTO l_adm_appl_status_old;
3124 CLOSE c_adm_appl_status;
3125 IF p_nominated_course_cd IS NOT NULL AND
3126 p_sequence_number IS NOT NULL THEN
3127 OPEN c_adm_outcome_status (p_person_id,
3128 p_admission_appl_number,
3129 p_nominated_course_cd,
3130 p_sequence_number
3131 );
3132 FETCH c_adm_outcome_status INTO l_adm_outcome_status,l_appl_inst_status,l_def_term_adm_appl_num,l_def_appl_sequence_num;
3133 CLOSE c_adm_outcome_status;
3134 END IF;
3135 -- The Admission Application Status may have been re-derived
3136 l_adm_appl_status_new := IGS_AD_GEN_002.ADMP_GET_AA_AAS(
3137 p_person_id,
3138 p_admission_appl_number,
3139 l_adm_appl_status_old);
3140
3141 -------begin APADEGAL - ADTD001 RE-OPEN BUILD- IGS.M
3142
3143 IF (check_any_offer_inst (p_person_id,
3144 p_admission_appl_number,
3145 p_nominated_course_cd, -- Null if invoked from Appl Instance and its Child TBHS
3146 p_sequence_number ) -- Null if invoked from Appl Instance and its Child TBHS
3147
3148 )
3149 THEN
3150 l_offer_inst := 'Y'; -- Offered/Cond offered instance exists - for data in proceed phase
3151 END IF;
3152
3153 IF (p_nominated_course_cd IS NOT NULL and p_sequence_number IS NOT NULL)
3154 THEN
3155
3156 IF NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(l_appl_inst_status),'-1') = cst_withdrawn THEN
3157 fnd_message.set_name('IGS','IGS_AD_APPL_INST_WITHD');
3158 igs_ge_msg_stack.add;
3159 app_exception.raise_exception; -- application instance is withdrawn
3160 END IF;
3161
3162 IF Is_App_Inst_Complete( p_person_id,
3163 p_admission_appl_number,
3164 p_nominated_course_cd,
3165 p_sequence_number) = 'Y' -- application instance is resolved (logically complete)
3166 THEN
3167 l_is_inst_complete := TRUE;
3168 END IF;
3169
3170 -- if instance is closed and offered, also new appl is created in Deffered term
3171 -- then none of the instance's and its child's attributes can be udpated.
3172
3173 IF ( l_offer_inst = 'Y' AND l_is_inst_complete)
3174 AND
3175 (l_def_term_adm_appl_num IS NOT NULL OR l_def_appl_sequence_num IS NOT NULL)
3176 THEN
3177 fnd_message.set_name('IGS','IGS_AD_APPL_INST_COMPL');
3178 igs_ge_msg_stack.add;
3179 app_exception.raise_exception;
3180 END IF;
3181
3182 END IF;
3183 -------end APADEGAL - ADTD001 RE-OPEN BUILD- IGS.M
3184
3185
3186
3187 -- the variable g_pkg_cst_completed_chk is getting populated to a value of 'N' from the package igs_ad_app_req_pkg. So if the package
3188 -- igs_ad_app_req_pkg calls this procedure the variable g_pkg_cst_completed_chk will be 'N' . For all other cases it will be 'Y'.
3189 --If this procedure is called from igs_ad_app_req_pkg then the check for complete application should not be performed. -- rghosh(bug#2901627)
3190
3191 IF NVL(igs_ad_app_req_pkg.g_pkg_cst_completed_chk,'Y') = 'Y' THEN -- modified igsm 5301 arvsrini
3192 IF ( IGS_AD_GEN_007.ADMP_GET_SAAS(l_adm_appl_status_new) IN (cst_completed, cst_withdrawn)
3193 OR -- added OR condition for re-open build - ADTD001 - IGS.M
3194 l_is_inst_complete -- appilcation instance is resolved ( logically completed)
3195 )
3196 AND -- added AND condition for re-open build - ADTD001 - IGS.M
3197 ( p_updateable = 'N' OR l_offer_inst <> 'Y') -- either not in proceed phase or not offered/cond offered.
3198
3199 THEN
3200 IF (p_nominated_course_cd IS NULL and p_sequence_number IS NULL)
3201 THEN
3202 fnd_message.set_name('IGS','IGS_AD_CANNOT_CHG_APPL_DTL');
3203 ELSE
3204 fnd_message.set_name('IGS','IGS_AD_APPL_INST_COMPL');
3205 END IF;
3206
3207 igs_ge_msg_stack.add;
3208 app_exception.raise_exception;
3209 END IF;
3210 ELSE
3211 IF IGS_AD_GEN_007.ADMP_GET_SAAS(l_adm_appl_status_new) = cst_withdrawn THEN
3212 fnd_message.set_name('IGS','IGS_AD_CANNOT_CHG_APPL_DTL');
3213 igs_ge_msg_stack.add;
3214 app_exception.raise_exception;
3215 END IF;
3216 END IF;
3217
3218 END check_adm_appl_inst_stat;
3219
3220
3221 FUNCTION valid_ofr_resp_status(
3222 p_person_id igs_ad_ps_appl_inst.person_id%TYPE ,
3223 p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE)
3224 RETURN BOOLEAN IS
3225 CURSOR c_ofr_resp_status (cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
3226 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE) IS
3227 /*******************************************************************************
3228 Created by : Rishi Ghosh
3229 Date created: 17-Apr-2003
3230 Purpose:
3231 This function will return true if for this application at least one application instance has an application offer response
3232 status of 'Accepted' OR the offer response status is 'Deffered' with the deferment status as 'Confirmed'. For all other
3233 cases it will return false. (bug#2901627)
3234 Known limitations/enhancements and/or remarks:
3235 Change History: (who, when, what: )
3236 Who When What
3237 *******************************************************************************/
3238 SELECT count(*)
3239 FROM igs_ad_ps_appl_inst
3240 WHERE person_id = cp_person_id AND
3241 admission_appl_number =cp_admission_appl_number AND
3242 (( adm_offer_resp_status IN ( SELECT adm_offer_resp_status
3243 FROM igs_ad_ofr_resp_stat
3244 WHERE s_adm_offer_resp_status = 'ACCEPTED')) OR
3245 ( adm_offer_resp_status IN ( SELECT adm_offer_resp_status
3246 FROM igs_ad_ofr_resp_stat
3247 WHERE s_adm_offer_resp_status = 'DEFERRAL' ) AND
3248 adm_offer_dfrmnt_status IN (SELECT aods.adm_offer_dfrmnt_status
3249 FROM igs_ad_ofrdfrmt_stat aods
3250 WHERE aods.s_adm_offer_dfrmnt_status ='CONFIRM')));
3251 l_count NUMBER;
3252 BEGIN
3253 OPEN c_ofr_resp_status(p_person_id,p_admission_appl_number);
3254 FETCH c_ofr_resp_status INTO l_count;
3255 CLOSE c_ofr_resp_status;
3256 IF l_count >0 THEN
3257 RETURN TRUE;
3258 ELSE
3259 RETURN FALSE;
3260 END IF;
3261 END valid_ofr_resp_status;
3262 FUNCTION res_pending_fee_status
3263 (
3264 p_application_id IN NUMBER
3265 )
3266 RETURN VARCHAR2
3267 IS
3268 /*******************************************************************************
3269 Created by : ANWEST
3270 Date created: 20-Jul-2005
3271 Purpose:
3272 This function has been created as part of the ADTD003 in the IGS.M build. It is
3273 used by the Submitted Applications Reusable Component to derive the exact nature
3274 of the PENDING fee status, thus allowing a decision to made on navigation. The
3275 function can return 4 values:
3276 NULL
3277 NOFEEDUE
3278 PENDING
3279 PARTIAL
3280 Known limitations/enhancements and/or remarks:
3281 Change History: (who, when, what: )
3282 Who When What
3283 *******************************************************************************/
3284 BEGIN
3285 DECLARE
3286 l_fee_exists VARCHAR2(1);
3287 CURSOR c_igs_ad_appl_all (cp_application_id igs_ad_appl_all.application_id%TYPE) IS
3288 SELECT person_id, admission_appl_number, appl_fee_amt
3289 FROM igs_ad_appl_all
3290 WHERE application_id = cp_application_id;
3291 rec_igs_ad_appl_all c_igs_ad_appl_all%ROWTYPE;
3292 CURSOR c_igs_ad_app_req (cp_person_id igs_ad_app_req.person_id%TYPE,
3293 cp_admission_appl_number igs_ad_app_req.admission_appl_number%TYPE) IS
3294 SELECT 'X'
3295 FROM igs_ad_app_req
3296 WHERE person_id = cp_person_id
3297 AND admission_appl_number = cp_admission_appl_number;
3298 BEGIN
3299 IF p_application_id IS NULL THEN
3300 RETURN NULL;
3301 ELSE
3302 OPEN c_igs_ad_appl_all(p_application_id);
3303 FETCH c_igs_ad_appl_all INTO rec_igs_ad_appl_all;
3304 CLOSE c_igs_ad_appl_all;
3305 IF (rec_igs_ad_appl_all.appl_fee_amt is NULL OR
3306 rec_igs_ad_appl_all.appl_fee_amt = 0) THEN
3307 RETURN 'NOFEEDUE';
3308 ELSE
3309 OPEN c_igs_ad_app_req(rec_igs_ad_appl_all.person_id, rec_igs_ad_appl_all.admission_appl_number);
3310 FETCH c_igs_ad_app_req INTO l_fee_exists;
3311 CLOSE c_igs_ad_app_req;
3312 IF l_fee_exists is NULL THEN
3313 RETURN 'PENDING';
3314 ELSE
3315 RETURN 'PARTIAL';
3316 END IF;
3317 END IF;
3318 END IF;
3319 END;
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3323 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_002.res_pending_fee_status');
3324 IGS_GE_MSG_STACK.ADD;
3325 App_Exception.Raise_Exception;
3326 END res_pending_fee_status;
3327
3328 PROCEDURE Admp_resub_inst(
3329 p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
3330 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
3331 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
3332 p_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
3333 p_do_commit VARCHAR2 DEFAULT NULL,
3334 x_return_status OUT NOCOPY VARCHAR2,
3335 x_msg_count OUT NOCOPY NUMBER,
3336 x_msg_data OUT NOCOPY VARCHAR2
3337 ) IS
3338 /*******************************************************************************
3339 Created by : Arvind Srinivasamoorthy
3340 Date created: 19 Jul 2005
3341 Purpose:
3342 To resubmit a withdrawn application instance
3343 Known limitations/enhancements and/or remarks:
3344 Change History: (who, when, what: )
3345 Who When What
3346 *******************************************************************************/
3347 CURSOR c_acai IS
3348 SELECT ROWID, acai.*
3349 FROM IGS_AD_PS_APPL_INST acai
3350 WHERE acai.person_id = p_person_id AND
3351 acai.admission_appl_number = p_admission_appl_number AND
3352 acai.nominated_course_cd= p_nominated_course_cd AND
3353 acai.sequence_number = p_acai_sequence_number;
3354 acai_rec c_acai%ROWTYPE;
3355 l_msg_at_index NUMBER;
3356 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
3357 BEGIN
3358 l_msg_at_index := igs_ge_msg_stack.count_msg;
3359 OPEN c_acai;
3360 FETCH c_acai INTO acai_rec;
3361 IF (c_acai%FOUND) THEN
3362 -- Call to tbh to insert null values for Application instance status. This will resubmit the application instance.
3363 IGS_AD_PS_APPL_Inst_Pkg.update_row(
3364 X_ROWID => acai_rec.ROWID,
3365 x_PERSON_ID => acai_rec.PERSON_ID,
3366 x_ADMISSION_APPL_NUMBER => acai_rec.ADMISSION_APPL_NUMBER,
3367 x_NOMINATED_COURSE_CD => acai_rec.NOMINATED_COURSE_CD,
3368 x_SEQUENCE_NUMBER => acai_rec.SEQUENCE_NUMBER,
3369 x_PREDICTED_GPA => acai_rec.PREDICTED_GPA,
3370 x_ACADEMIC_INDEX => acai_rec.ACADEMIC_INDEX,
3371 x_ADM_CAL_TYPE => acai_rec.ADM_CAL_TYPE,
3372 x_APP_FILE_LOCATION => acai_rec.APP_FILE_LOCATION,
3373 x_ADM_CI_SEQUENCE_NUMBER => acai_rec.ADM_CI_SEQUENCE_NUMBER,
3374 x_COURSE_CD => acai_rec.COURSE_CD,
3375 x_APP_SOURCE_ID => acai_rec.APP_SOURCE_ID,
3376 x_CRV_VERSION_NUMBER => acai_rec.CRV_VERSION_NUMBER,
3377 x_WAITLIST_RANK => acai_rec.WAITLIST_RANK,
3378 x_LOCATION_CD => acai_rec.LOCATION_CD,
3379 x_ATTENT_OTHER_INST_CD => acai_rec.ATTENT_OTHER_INST_CD,
3380 x_ATTENDANCE_MODE => acai_rec.ATTENDANCE_MODE,
3381 x_EDU_GOAL_PRIOR_ENROLL_ID => acai_rec.EDU_GOAL_PRIOR_ENROLL_ID,
3382 x_ATTENDANCE_TYPE => acai_rec.ATTENDANCE_TYPE,
3383 x_DECISION_MAKE_ID => acai_rec.DECISION_MAKE_ID,
3384 x_UNIT_SET_CD => acai_rec.UNIT_SET_CD,
3385 x_DECISION_DATE => acai_rec.DECISION_DATE,
3386 x_ATTRIBUTE_CATEGORY => acai_rec.ATTRIBUTE_CATEGORY,
3387 x_ATTRIBUTE1 => acai_rec.ATTRIBUTE1,
3388 x_ATTRIBUTE2 => acai_rec.ATTRIBUTE2,
3389 x_ATTRIBUTE3 => acai_rec.ATTRIBUTE3,
3390 x_ATTRIBUTE4 => acai_rec.ATTRIBUTE4,
3391 x_ATTRIBUTE5 => acai_rec.ATTRIBUTE5,
3392 x_ATTRIBUTE6 => acai_rec.ATTRIBUTE6,
3393 x_ATTRIBUTE7 => acai_rec.ATTRIBUTE7,
3394 x_ATTRIBUTE8 => acai_rec.ATTRIBUTE8,
3395 x_ATTRIBUTE9 => acai_rec.ATTRIBUTE9,
3396 x_ATTRIBUTE10 => acai_rec.ATTRIBUTE10,
3397 x_ATTRIBUTE11 => acai_rec.ATTRIBUTE11,
3398 x_ATTRIBUTE12 => acai_rec.ATTRIBUTE12,
3399 x_ATTRIBUTE13 => acai_rec.ATTRIBUTE13,
3400 x_ATTRIBUTE14 => acai_rec.ATTRIBUTE14,
3401 x_ATTRIBUTE15 => acai_rec.ATTRIBUTE15,
3402 x_ATTRIBUTE16 => acai_rec.ATTRIBUTE16,
3403 x_ATTRIBUTE17 => acai_rec.ATTRIBUTE17,
3404 x_ATTRIBUTE18 => acai_rec.ATTRIBUTE18,
3405 x_ATTRIBUTE19 => acai_rec.ATTRIBUTE19,
3406 x_ATTRIBUTE20 => acai_rec.ATTRIBUTE20,
3407 x_DECISION_REASON_ID => acai_rec.DECISION_REASON_ID,
3408 x_US_VERSION_NUMBER => acai_rec.US_VERSION_NUMBER,
3409 x_DECISION_NOTES => acai_rec.DECISION_NOTES,
3410 x_PENDING_REASON_ID => acai_rec.PENDING_REASON_ID,
3411 x_PREFERENCE_NUMBER => acai_rec.PREFERENCE_NUMBER,
3412 x_ADM_DOC_STATUS => acai_rec.ADM_DOC_STATUS,
3413 x_ADM_ENTRY_QUAL_STATUS => acai_rec.ADM_ENTRY_QUAL_STATUS,
3414 x_DEFICIENCY_IN_PREP => acai_rec.DEFICIENCY_IN_PREP,
3415 x_LATE_ADM_FEE_STATUS => acai_rec.LATE_ADM_FEE_STATUS,
3416 x_SPL_CONSIDER_COMMENTS => acai_rec.SPL_CONSIDER_COMMENTS,
3417 x_APPLY_FOR_FINAID => acai_rec.APPLY_FOR_FINAID,
3418 x_FINAID_APPLY_DATE => acai_rec.FINAID_APPLY_DATE,
3419 x_ADM_OUTCOME_STATUS => acai_rec.ADM_OUTCOME_STATUS,
3420 x_adm_otcm_stat_auth_per_id => acai_rec.adm_otcm_status_auth_person_id,
3421 x_ADM_OUTCOME_STATUS_AUTH_DT => acai_rec.ADM_OUTCOME_STATUS_AUTH_DT,
3422 x_ADM_OUTCOME_STATUS_REASON => acai_rec.ADM_OUTCOME_STATUS_REASON,
3423 x_OFFER_DT => acai_rec.OFFER_DT,
3424 x_OFFER_RESPONSE_DT => acai_rec.OFFER_RESPONSE_DT,
3425 x_PRPSD_COMMENCEMENT_DT => acai_rec.PRPSD_COMMENCEMENT_DT,
3426 x_ADM_CNDTNL_OFFER_STATUS => acai_rec.ADM_CNDTNL_OFFER_STATUS,
3427 x_CNDTNL_OFFER_SATISFIED_DT => acai_rec.CNDTNL_OFFER_SATISFIED_DT,
3428 x_cndnl_ofr_must_be_stsfd_ind => acai_rec.cndtnl_offer_must_be_stsfd_ind,
3429 x_ADM_OFFER_RESP_STATUS => acai_rec.ADM_OFFER_RESP_STATUS,
3430 x_ACTUAL_RESPONSE_DT => acai_rec.ACTUAL_RESPONSE_DT,
3431 x_ADM_OFFER_DFRMNT_STATUS => acai_rec.ADM_OFFER_DFRMNT_STATUS,
3432 x_DEFERRED_ADM_CAL_TYPE => acai_rec.DEFERRED_ADM_CAL_TYPE,
3433 x_DEFERRED_ADM_CI_SEQUENCE_NUM => acai_rec.DEFERRED_ADM_CI_SEQUENCE_NUM,
3434 x_DEFERRED_TRACKING_ID => acai_rec.DEFERRED_TRACKING_ID,
3435 x_ASS_RANK => acai_rec.ASS_RANK,
3436 x_SECONDARY_ASS_RANK => acai_rec.SECONDARY_ASS_RANK,
3437 x_intr_accept_advice_num => acai_rec.intrntnl_acceptance_advice_num,
3438 x_ASS_TRACKING_ID => acai_rec.ASS_TRACKING_ID,
3439 x_FEE_CAT => acai_rec.FEE_CAT,
3440 x_HECS_PAYMENT_OPTION => acai_rec.HECS_PAYMENT_OPTION,
3441 x_EXPECTED_COMPLETION_YR => acai_rec.EXPECTED_COMPLETION_YR,
3442 x_EXPECTED_COMPLETION_PERD => acai_rec.EXPECTED_COMPLETION_PERD,
3443 x_CORRESPONDENCE_CAT => acai_rec.CORRESPONDENCE_CAT,
3444 x_ENROLMENT_CAT => acai_rec.ENROLMENT_CAT,
3445 x_FUNDING_SOURCE => acai_rec.FUNDING_SOURCE,
3446 x_APPLICANT_ACPTNCE_CNDTN => acai_rec.APPLICANT_ACPTNCE_CNDTN,
3447 x_CNDTNL_OFFER_CNDTN => acai_rec.CNDTNL_OFFER_CNDTN,
3448 X_MODE => 'R',
3449 X_SS_APPLICATION_ID => acai_rec.SS_APPLICATION_ID,
3450 X_SS_PWD => acai_rec.SS_PWD,
3451 X_AUTHORIZED_DT => acai_rec.AUTHORIZED_DT,
3452 X_AUTHORIZING_PERS_ID => acai_rec.AUTHORIZING_PERS_ID,
3453 x_entry_status => acai_rec.entry_status,
3454 x_entry_level => acai_rec.entry_level,
3455 x_sch_apl_to_id => acai_rec.sch_apl_to_id,
3456 x_idx_calc_date => acai_rec.idx_calc_date,
3457 x_waitlist_status => acai_rec.waitlist_status,
3458 x_ATTRIBUTE21 => acai_rec.ATTRIBUTE21,
3459 x_ATTRIBUTE22 => acai_rec.ATTRIBUTE22,
3460 x_ATTRIBUTE23 => acai_rec.ATTRIBUTE23,
3461 x_ATTRIBUTE24 => acai_rec.ATTRIBUTE24,
3462 x_ATTRIBUTE25 => acai_rec.ATTRIBUTE25,
3463 x_ATTRIBUTE26 => acai_rec.ATTRIBUTE26,
3464 x_ATTRIBUTE27 => acai_rec.ATTRIBUTE27,
3465 x_ATTRIBUTE28 => acai_rec.ATTRIBUTE28,
3466 x_ATTRIBUTE29 => acai_rec.ATTRIBUTE29,
3467 x_ATTRIBUTE30 => acai_rec.ATTRIBUTE30,
3468 x_ATTRIBUTE31 => acai_rec.ATTRIBUTE31,
3469 x_ATTRIBUTE32 => acai_rec.ATTRIBUTE32,
3470 x_ATTRIBUTE33 => acai_rec.ATTRIBUTE33,
3471 x_ATTRIBUTE34 => acai_rec.ATTRIBUTE34,
3472 x_ATTRIBUTE35 => acai_rec.ATTRIBUTE35,
3473 x_ATTRIBUTE36 => acai_rec.ATTRIBUTE36,
3474 x_ATTRIBUTE37 => acai_rec.ATTRIBUTE37,
3475 x_ATTRIBUTE38 => acai_rec.ATTRIBUTE38,
3476 x_ATTRIBUTE39 => acai_rec.ATTRIBUTE39,
3477 x_ATTRIBUTE40 => acai_rec.ATTRIBUTE40,
3478 x_fut_acad_cal_type => acai_rec.future_acad_cal_type,
3479 x_fut_acad_ci_sequence_number => acai_rec.future_acad_ci_sequence_number,
3480 x_fut_adm_cal_type => acai_rec.future_adm_cal_type,
3481 x_fut_adm_ci_sequence_number => acai_rec.future_adm_ci_sequence_number,
3482 x_prev_term_adm_appl_number => acai_rec.previous_term_adm_appl_number,
3483 x_prev_term_sequence_number => acai_rec.previous_term_sequence_number,
3484 x_fut_term_adm_appl_number => acai_rec.future_term_adm_appl_number,
3485 x_fut_term_sequence_number => acai_rec.future_term_sequence_number,
3486 x_def_acad_cal_type => acai_rec.def_acad_cal_type,
3487 x_def_acad_ci_sequence_num => acai_rec.def_acad_ci_sequence_num,
3488 x_def_prev_term_adm_appl_num => acai_rec.def_prev_term_adm_appl_num,
3489 x_def_prev_appl_sequence_num => acai_rec.def_prev_appl_sequence_num,
3490 x_def_term_adm_appl_num => acai_rec.def_term_adm_appl_num,
3491 x_def_appl_sequence_num => acai_rec.def_appl_sequence_num,
3492 x_appl_inst_status => NULL,
3493 x_ais_reason => NULL,
3494 x_decline_ofr_reason => acai_rec.decline_ofr_reason
3495 );
3496 END IF;
3497 CLOSE c_acai;
3498 x_return_status := FND_API.G_RET_STS_SUCCESS;
3499 IF p_do_commit = 'Y' THEN
3500 COMMIT;
3501 END IF;
3502 EXCEPTION
3503 WHEN OTHERS THEN
3504 igs_ad_gen_016.extract_msg_from_stack (
3505 p_msg_at_index => l_msg_at_index,
3506 p_return_status => x_return_status,
3507 p_msg_count => x_msg_count,
3508 p_msg_data => x_msg_data,
3509 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
3510 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
3511 x_return_status := FND_API.G_RET_STS_ERROR ;
3512 ELSE
3513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3514 END IF;
3515 IF c_acai%ISOPEN THEN
3516 CLOSE c_acai;
3517 END IF;
3518 END Admp_resub_inst;
3519
3520
3521
3522 -- begin arvsrini - ADTD001 RE-OPEN BUILD- IGS.M
3523 FUNCTION check_any_offer_inst
3524 (p_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
3525 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
3526 p_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE DEFAULT NULL,
3527 p_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE DEFAULT NULL
3528 )
3529 RETURN BOOLEAN IS
3530 -- created for IGS.M arvsrini
3531 --Cursor to fetch the desired records.
3532 Cursor c_aca ( cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
3533 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
3534 cp_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
3535 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE
3536 ) IS
3537 SELECT acai.adm_outcome_status outcome_status
3538 FROM IGS_AD_PS_APPL_INST acai
3539 WHERE acai.person_id = cp_person_id AND
3540 acai.admission_appl_number = cp_admission_appl_number AND
3541 acai.nominated_course_cd = NVL(cp_nominated_course_cd,acai.nominated_course_cd) AND
3542 acai.sequence_number= NVL(cp_sequence_number,acai.sequence_number) ;
3543 l_instance_found BOOLEAN DEFAULT FALSE;
3544 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
3545 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
3546 BEGIN
3547 --Loop through the record set; if an instance with outcome 'Offer' or 'Conditional Offer' is found set the flag to TRUE and exit.
3548 FOR c_aca_rec IN c_aca(p_person_id, p_admission_appl_number, p_nominated_course_cd, p_sequence_number) LOOP
3549 IF IGS_AD_GEN_008.ADMP_GET_SAOS(c_aca_rec.outcome_status) IN (cst_offer, cst_cond_offer) THEN
3550 l_instance_found := TRUE;
3551 EXIT;
3552 END IF;
3553 END LOOP;
3554 RETURN l_instance_found;
3555 END check_any_offer_inst;
3556
3557
3558
3559
3560
3561 FUNCTION Is_App_Inst_Complete (
3562 p_person_id IN NUMBER ,
3563 p_admission_appl_number IN NUMBER ,
3564 p_nominated_course_cd IN VARCHAR2,
3565 p_sequence_number IN NUMBER)
3566 RETURN VARCHAR2 IS
3567 -- created for IGS.M arvsrini
3568 CURSOR c_acai IS
3569 SELECT acai.adm_outcome_status,
3570 acai.adm_offer_resp_status,
3571 acai.adm_offer_dfrmnt_status,
3572 acai.def_term_adm_appl_num,
3573 acai.def_appl_sequence_num
3574 FROM IGS_AD_PS_APPL_INST acai
3575 WHERE acai.person_id = p_person_id AND
3576 acai.admission_appl_number = p_admission_appl_number AND
3577 acai.nominated_course_cd = p_nominated_course_cd AND
3578 acai.sequence_number = p_sequence_number;
3579 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
3580 cst_received CONSTANT VARCHAR2(10) := 'RECEIVED';
3581 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
3582 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
3583 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
3584 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
3585 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
3586 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
3587 cst_voided CONSTANT VARCHAR2(10) := 'VOIDED';
3588 cst_no_quota CONSTANT VARCHAR2(10) := 'NO-QUOTA';
3589 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
3590 cst_confirm CONSTANT VARCHAR2(10) := 'CONFIRM';
3591 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
3592 cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
3593 v_completed_appl VARCHAR2(1) DEFAULT 'N';
3594 v_aca_rec c_acai%ROWTYPE;
3595 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
3596 v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
3597 v_s_adm_offer_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
3598 BEGIN
3599 OPEN c_acai;
3600 FETCH c_acai INTO v_aca_rec;
3601 IF (c_acai%FOUND) THEN
3602 v_s_adm_outcome_status := Igs_Ad_Gen_008.Admp_Get_Saos(v_aca_rec.adm_outcome_status);
3603 v_s_adm_offer_resp_status := Igs_Ad_Gen_008.Admp_Get_Saors(v_aca_rec.adm_offer_resp_status);
3604 v_s_adm_offer_dfrmnt_status := Igs_Ad_Gen_008.Admp_Get_Saods(v_aca_rec.adm_offer_dfrmnt_status);
3605 -- Check if the outcome of the admission course
3606 -- application instance is complete or has been resolved.
3607 IF ( v_s_adm_outcome_status IN (cst_cancelled, cst_voided, cst_withdrawn,cst_no_quota,cst_rejected)
3608 OR
3609 ( v_s_adm_outcome_status IN (cst_offer, cst_cond_offer)
3610 AND
3611 ( v_s_adm_offer_resp_status IN (cst_accepted, cst_rejected, cst_lapsed)
3612 OR
3613 ( v_s_adm_offer_resp_status IN (cst_deferral)
3614 AND
3615 ( v_s_adm_offer_dfrmnt_status IN (cst_confirm)
3616 OR
3617 ( v_s_adm_offer_dfrmnt_status IN (cst_approved)
3618 AND
3619 ( v_aca_rec.def_term_adm_appl_num IS NOT NULL
3620 OR
3621 v_aca_rec.def_appl_sequence_num IS NOT NULL
3622 )
3623 )
3624 )
3625 )
3626 )
3627 )
3628 )
3629 THEN
3630 v_completed_appl:= 'Y';
3631 END IF;
3632 END IF;
3633 CLOSE c_acai;
3634 RETURN v_completed_appl;
3635 END Is_App_Inst_Complete;
3636 -- END arvsrini - ADTD001 RE-OPEN BUILD- IGS.M
3637
3638 -- begin APADEGAL - ADTD001 RE-OPEN BUILD- IGS.M
3639 ---------------------------***************Is_inst_recon_allowed (PROCEDURE)**************-------------------------
3640 PROCEDURE Is_inst_recon_allowed ( p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
3641 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
3642 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
3643 p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
3644 p_success out nocopy varchar2,
3645 p_message_name out nocopy varchar2
3646 ) IS
3647 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
3648 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
3649 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
3650
3651 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
3652 cst_confirm CONSTANT VARCHAR2(10) := 'CONFIRM';
3653
3654 CURSOR c_aa_acaiv IS
3655 SELECT acaiv.adm_cal_type,
3656 acaiv.adm_ci_sequence_number,
3657 acaiv.course_cd,
3658 acaiv.crv_version_number,
3659 acaiv.location_cd,
3660 acaiv.attendance_mode,
3661 acaiv.attendance_type ,
3662 acaiv.future_term_adm_appl_number,
3663 acaiv.future_term_sequence_number,
3664 acaiv.def_term_adm_appl_num,
3665 acaiv.def_appl_sequence_num
3666 FROM igs_ad_ps_appl_inst acaiv
3667 WHERE acaiv.person_id = p_person_id AND
3668 acaiv.admission_appl_number = p_admission_appl_number AND
3669 acaiv.nominated_course_cd = p_nominated_course_cd AND
3670 acaiv.sequence_number = p_sequence_number;
3671 v_aa_acaiv_rec c_aa_acaiv%ROWTYPE;
3672 CURSOR c_other_offer_inst IS
3673 SELECT 1 CNT
3674 FROM igs_ad_ps_appl_inst acaiv
3675 WHERE acaiv.person_id = p_person_id AND
3676 acaiv.admission_appl_number = p_admission_appl_number AND
3677 acaiv.nominated_course_cd = p_nominated_course_cd AND
3678 acaiv.sequence_number <> p_sequence_number AND
3679 IGS_AD_GEN_008.ADMP_GET_SAOS(acaiv.adm_outcome_status) IN ('OFFER','COND-OFFER')
3680 AND ( IGS_AD_GEN_008.ADMP_GET_SAORS(acaiv.adm_offer_resp_status) IN (cst_accepted,cst_rejected,cst_lapsed)
3681 OR
3682 (IGS_AD_GEN_008.ADMP_GET_SAORS(acaiv.adm_offer_resp_status) IN (cst_deferral)
3683 AND
3684 IGS_AD_GEN_008.ADMP_GET_SAODS(acaiv.adm_offer_dfrmnt_status) IN (cst_confirm)
3685 )
3686 )
3687 AND rownum <= 1 ;
3688 CURSOR c_aa IS
3689 SELECT aa.admission_cat,
3690 aa.s_admission_process_type
3691 FROM IGS_AD_APPL aa
3692 WHERE aa.person_id = p_person_id AND
3693 aa.admission_appl_number = p_admission_appl_number;
3694 CURSOR c_apcs ( cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
3695 cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE
3696 ) IS
3697 SELECT '1'
3698 FROM IGS_AD_PRCS_CAT_STEP
3699 WHERE admission_cat = cp_admission_cat AND
3700 s_admission_process_type = cp_s_admission_process_type AND
3701 s_admission_step_type = 'RECONSIDER' AND
3702 step_group_type <> 'TRACK' ;
3703 l_other_offer_inst_cnt NUMBER DEFAULT 0;
3704 l_admission_cat IGS_AD_APPL.admission_cat%TYPE;
3705 l_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
3706 l_is_reconisder_allowed BOOLEAN DEFAULT FALSE;
3707 l_dummy varchar2(30) DEFAULT NULL;
3708 BEGIN
3709 OPEN c_other_offer_inst ;
3710 FETCH c_other_offer_inst INTO l_other_offer_inst_cnt;
3711 CLOSE c_other_offer_inst;
3712 OPEN c_aa;
3713 FETCH c_aa INTO l_admission_cat, l_s_admission_process_type;
3714 CLOSE c_aa;
3715 OPEN c_apcs(l_admission_cat, l_s_admission_process_type);
3716 FETCH c_apcs INTO l_dummy;
3717 IF ( NVL(l_dummy,'X') = '1' )
3718 THEN
3719 l_is_reconisder_allowed := TRUE;
3720 END IF;
3721 CLOSE c_apcs;
3722 OPEN c_aa_acaiv;
3723 FETCH c_aa_acaiv INTO v_aa_acaiv_rec;
3724 CLOSE c_aa_acaiv;
3725
3726 IF igs_ad_gen_002.is_app_inst_complete(p_person_id,p_admission_appl_number,p_nominated_course_cd,p_sequence_number) = 'Y'
3727 THEN
3728 IF (NOT l_is_reconisder_allowed ) -- APC step not allowed
3729 THEN
3730 p_message_name := 'IGS_AD_NO_RECONSIDERATION';
3731 p_success := 'N';
3732 RETURN;
3733 ELSE
3734 IF ( v_aa_acaiv_rec.future_term_adm_appl_number IS NOT NULL OR
3735 v_aa_acaiv_rec.future_term_sequence_number IS NOT NULL OR
3736 v_aa_acaiv_rec.def_term_adm_appl_num IS NOT NULL OR
3737 v_aa_acaiv_rec.def_appl_sequence_num IS NOT NULL
3738 )
3739 THEN
3740 p_message_name := 'IGS_AD_NO_RECONSIDERATION'; -- cannot be reconsidered as a future/deferred application exists.
3741 p_success := 'N';
3742 RETURN;
3743 END IF;
3744 IF (igs_ad_gen_002.check_any_offer_inst (p_person_id, p_admission_appl_number, p_nominated_course_cd, p_sequence_number ) )
3745 THEN
3746 p_success := 'Y';
3747 RETURN; -- can be reconsiderd, as this is an offered instance
3748 ELSE
3749 IF (l_other_offer_inst_cnt <> 0) -- TO Check if there exists other instance(s) with offer/cond offer
3750 THEN
3751 p_message_name := 'IGS_AD_INST_NO_RECON'; --cant be reconsidered, as there exits another intance with offer
3752 p_success := 'N';
3753 RETURN;
3754 ELSE
3755 p_success := 'Y'; -- can be reconsidered, as no other offered instance exists
3756 RETURN;
3757 END IF;
3758 END IF;
3759 END IF;
3760 ELSE -- Instance not yet resolved, cannot be reconsidered
3761 p_message_name := 'IGS_AD_NO_RECONSIDERATION';
3762 p_success := 'N'; RETURN;
3763 END IF;
3764 EXCEPTION
3765 WHEN OTHERS THEN
3766 p_message_name := 'IGS_GE_UNHANDLED_EXP';
3767 p_success := 'N';
3768 RETURN;
3769 END Is_inst_recon_allowed;
3770 ---------------------------***************Is_inst_recon_allowed (overloaded function)**************-------------------------
3771 FUNCTION Is_inst_recon_allowed ( p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
3772 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
3773 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
3774 p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
3775 )
3776 RETURN VARCHAR2 IS
3777 p_message_name VARCHAR2(40) DEFAULT NULL;
3778 p_success VARCHAR2(1) DEFAULT NULL;
3779 BEGIN
3780 igs_ad_gen_002.Is_inst_recon_allowed ( p_person_id,p_admission_appl_number,p_nominated_course_cd, p_sequence_number,p_success,p_message_name);
3781 RETURN p_success;
3782 END Is_inst_recon_allowed;
3783
3784 ---------------------------**************Reconsider_Appl_Inst***************-------------------------
3785
3786 PROCEDURE Reconsider_Appl_Inst (
3787 p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
3788 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
3789 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
3790 p_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
3791 p_interface VARCHAR2 -- Interface which has raised reconsideration ( Forms,Self service, Import process)
3792 )
3793 IS
3794
3795 /*******************************************************************************
3796 Created by : apadegal, Oracle IDC
3797 Date created: 18-August-2005
3798
3799 Usage: (e.g. restricted, unrestricted, where to call from)
3800 1. Thie procedure would reset the outcome status to pending for all the applciation insatnces of the given program ,
3801 except the pending instances or instances which have have reference to futuer term or deferred term applications.
3802 2. It would set the other fields (like offer reponse, decision maker id, decision date...etc) accordingly.
3803 3. Once the update is done, corresponding business event (for outcome/decision change) will be raised.
3804 4. This procedure is invoked from the
3805 a) IGSAD092.pld ( Outcome Form) and
3806 b) IGSPAPPB.pls ( Public API - used in Decision import process) and
3807 c) EnterDecisionDetailsEOImpl.java ( Enter deicsions page - Self Service)
3808
3809 Known limitations/enhancements/remarks:
3810 -
3811
3812 Change History: (who, when, what: NO CREATION RECORDS HERE!)
3813 Who When What
3814 *******************************************************************************/
3815
3816 CURSOR c_aca IS
3817 SELECT rowid,acai.*
3818 FROM IGS_AD_PS_APPL_INST_ALL acai
3819 WHERE acai.person_id = p_person_id AND
3820 acai.admission_appl_number = p_admission_appl_number AND
3821 acai.nominated_course_cd = p_nominated_course_cd AND
3822 acai.future_term_adm_appl_number IS NULL AND
3823 acai.future_term_sequence_number IS NULL AND
3824 acai.def_term_adm_appl_num IS NULL AND
3825 acai.def_appl_sequence_num IS NULL AND
3826 IGS_AD_GEN_008.ADMP_GET_SAOS(acai.ADM_OUTCOME_STATUS) <> 'PENDING'
3827 FOR UPDATE NOWAIT;
3828
3829
3830 CURSOR cur_ad_ps_appl ( cur_person_id IGS_AD_PS_APPL.person_id%TYPE ,
3831 cur_admission_appl_number IGS_AD_PS_APPL.admission_appl_number%TYPE ,
3832 cur_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE ) IS
3833 SELECT rowid , IGS_AD_PS_APPL.*
3834 FROM IGS_AD_PS_APPL
3835 WHERE person_id = CUR_person_id AND
3836 admission_appl_number = CUR_admission_appl_number AND
3837 nominated_course_cd = CUR_nominated_course_cd;
3838 cur_ad_ps_appl_rec cur_ad_ps_appl%ROWTYPE ;
3839
3840
3841
3842 CURSOR c_get_applicant(cp_person_id igs_pe_typ_instances_all.person_id%TYPE,
3843 cp_admission_appl_number igs_pe_typ_instances_all.admission_appl_number%TYPE,
3844 cp_nominated_course_cd igs_pe_typ_instances_all.nominated_course_cd%TYPE) IS
3845 SELECT pti.rowid,pti.*
3846 FROM igs_pe_typ_instances_all pti
3847 WHERE pti.person_type_code IN (SELECT pt.person_type_code FROM Igs_pe_person_types pt WHERE pt.system_type = 'APPLICANT')
3848 AND pti.person_id = cp_person_id
3849 AND pti.admission_appl_number = cp_admission_appl_number
3850 AND pti.nominated_course_cd = cp_nominated_course_cd
3851 AND pti.end_date IS NOT NULL
3852 AND pti.end_method = 'CREATE_STUDENT'
3853 FOR UPDATE NOWAIT;
3854
3855
3856
3857
3858 x_msg_data varchar2(2000);
3859
3860 BEGIN
3861
3862
3863 -- call the below procedure to generate dummy history records for currently PENDING application isntances of the program
3864
3865 ins_dummy_pend_hist_rec ( p_person_id,
3866 p_admission_appl_number,
3867 p_nominated_course_cd
3868 );
3869
3870 FOR c_aca_rec IN c_aca
3871 LOOP
3872
3873
3874 igs_ad_wf_001.APP_RECONSIDER_REQUEST_EVENT
3875 (
3876 P_PERSON_ID => c_aca_rec.person_id,
3877 P_ADMISSION_APPL_NUMBER => c_aca_rec.admission_appl_number,
3878 P_NOMINATED_COURSE_CD => c_aca_rec.nominated_course_cd,
3879 P_SEQUENCE_NUMBER => c_aca_rec.sequence_number,
3880 P_ADM_OUTCOME_STATUS => c_aca_rec.adm_outcome_status,
3881 P_ADM_OFFER_RESP_STATUS => c_aca_rec.adm_offer_resp_status
3882 );
3883
3884
3885 IF ( NVL(IGS_AD_GEN_008.ADMP_GET_SAORS(c_aca_rec.Adm_Offer_Resp_Status), 'NULL') = 'ACCEPTED' )
3886 THEN
3887 -- UNCONFIRM the Student PROGRAM ATTEMPTS. (api would be provided by enrolments team)
3888
3889 IF NOT IGS_EN_VAL_SCA.handle_rederive_prog_att (p_person_id => c_aca_rec.PERSON_ID ,
3890 p_admission_appl_number => c_aca_rec.ADMISSION_APPL_NUMBER ,
3891 p_nominated_course_cd => c_aca_rec.NOMINATED_COURSE_CD ,
3892 p_sequence_number => c_aca_rec.SEQUENCE_NUMBER ,
3893 p_message => x_msg_data
3894 )
3895 THEN
3896
3897
3898 App_Exception.Raise_Exception;
3899 ELSE
3900
3901 -- Re-open the related Application Person Types - nullify the 'End'fields.
3902
3903 FOR c_appl_rec IN c_get_applicant(p_person_id,p_admission_appl_number,p_nominated_course_cd)
3904 LOOP
3905
3906 igs_pe_typ_instances_pkg.update_row(
3907 X_ROWID => c_appl_rec.rowid,
3908 X_PERSON_ID => c_appl_rec.PERSON_ID,
3909 X_COURSE_CD => c_appl_rec.COURSE_CD,
3910 X_TYPE_INSTANCE_ID => c_appl_rec.TYPE_INSTANCE_ID,
3911 X_PERSON_TYPE_CODE => c_appl_rec.PERSON_TYPE_CODE,
3912 X_CC_VERSION_NUMBER => c_appl_rec.CC_VERSION_NUMBER,
3913 X_FUNNEL_STATUS => c_appl_rec.FUNNEL_STATUS,
3914 X_ADMISSION_APPL_NUMBER => c_appl_rec.ADMISSION_APPL_NUMBER,
3915 X_NOMINATED_COURSE_CD => c_appl_rec.NOMINATED_COURSE_CD,
3916 X_NCC_VERSION_NUMBER => c_appl_rec.NCC_VERSION_NUMBER,
3917 X_SEQUENCE_NUMBER => c_appl_rec.SEQUENCE_NUMBER,
3918 X_START_DATE => c_appl_rec.START_DATE,
3919 X_END_DATE => NULL, -- nullified this field
3920 X_CREATE_METHOD => c_appl_rec.CREATE_METHOD,
3921 X_ENDED_BY => NULL, -- nullified this field
3922 X_END_METHOD => NULL, -- nullified this field
3923 X_MODE => 'R',
3924 X_EMPLMNT_CATEGORY_CODE => c_appl_rec.EMPLMNT_CATEGORY_CODE);
3925
3926
3927
3928 END LOOP;
3929 END IF;
3930
3931 END IF;
3932
3933 IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW (X_ROWID => c_aca_rec.ROWID ,
3934 x_PERSON_ID => c_aca_rec.PERSON_ID ,
3935 x_ADMISSION_APPL_NUMBER => c_aca_rec.ADMISSION_APPL_NUMBER ,
3936 x_NOMINATED_COURSE_CD => c_aca_rec.NOMINATED_COURSE_CD ,
3937 x_SEQUENCE_NUMBER => c_aca_rec.SEQUENCE_NUMBER ,
3938 x_PREDICTED_GPA => c_aca_rec.PREDICTED_GPA ,
3939 x_ACADEMIC_INDEX => c_aca_rec.ACADEMIC_INDEX,
3940 x_ADM_CAL_TYPE => c_aca_rec.ADM_CAL_TYPE,
3941 x_APP_FILE_LOCATION => c_aca_rec.APP_FILE_LOCATION,
3942 x_ADM_CI_SEQUENCE_NUMBER => c_aca_rec.ADM_CI_SEQUENCE_NUMBER,
3943 x_COURSE_CD => c_aca_rec.COURSE_CD,
3944 x_APP_SOURCE_ID => c_aca_rec.APP_SOURCE_ID ,
3945 x_CRV_VERSION_NUMBER => c_aca_rec.CRV_VERSION_NUMBER ,
3946 x_WAITLIST_RANK => NULL,
3947 x_LOCATION_CD => c_aca_rec.LOCATION_CD,
3948 x_ATTENT_OTHER_INST_CD => NULL,
3949 x_ATTENDANCE_MODE => c_aca_rec.ATTENDANCE_MODE,
3950 x_EDU_GOAL_PRIOR_ENROLL_ID => c_aca_rec.EDU_GOAL_PRIOR_ENROLL_ID,
3951 x_ATTENDANCE_TYPE => c_aca_rec.ATTENDANCE_TYPE,
3952 x_DECISION_MAKE_ID => NULL,
3953 x_UNIT_SET_CD => c_aca_rec.UNIT_SET_CD,
3954 x_DECISION_DATE => NULL,
3955 x_ATTRIBUTE_CATEGORY => c_aca_rec.ATTRIBUTE_CATEGORY,
3956 x_ATTRIBUTE1 => c_aca_rec.ATTRIBUTE1,
3957 x_ATTRIBUTE2 => c_aca_rec.ATTRIBUTE2,
3958 x_ATTRIBUTE3 => c_aca_rec.ATTRIBUTE3,
3959 x_ATTRIBUTE4 => c_aca_rec.ATTRIBUTE4,
3960 x_ATTRIBUTE5 => c_aca_rec.ATTRIBUTE5,
3961 x_ATTRIBUTE6 => c_aca_rec.ATTRIBUTE6,
3962 x_ATTRIBUTE7 => c_aca_rec.ATTRIBUTE7,
3963 x_ATTRIBUTE8 => c_aca_rec.ATTRIBUTE8,
3964 x_ATTRIBUTE9 => c_aca_rec.ATTRIBUTE9,
3965 x_ATTRIBUTE10 => c_aca_rec.ATTRIBUTE10,
3966 x_ATTRIBUTE11 => c_aca_rec.ATTRIBUTE11,
3967 x_ATTRIBUTE12 => c_aca_rec.ATTRIBUTE12,
3968 x_ATTRIBUTE13 => c_aca_rec.ATTRIBUTE13,
3969 x_ATTRIBUTE14 => c_aca_rec.ATTRIBUTE14,
3970 x_ATTRIBUTE15 => c_aca_rec.ATTRIBUTE15,
3971 x_ATTRIBUTE16 => c_aca_rec.ATTRIBUTE16,
3972 x_ATTRIBUTE17 => c_aca_rec.ATTRIBUTE17,
3973 x_ATTRIBUTE18 => c_aca_rec.ATTRIBUTE18,
3974 x_ATTRIBUTE19 => c_aca_rec.ATTRIBUTE19,
3975 x_ATTRIBUTE20 => c_aca_rec.ATTRIBUTE20,
3976 x_DECISION_REASON_ID => NULL,
3977 x_US_VERSION_NUMBER => c_aca_rec.US_VERSION_NUMBER,
3978 x_DECISION_NOTES => c_aca_rec.DECISION_NOTES,
3979 x_PENDING_REASON_ID => c_aca_rec.PENDING_REASON_ID,
3980 x_PREFERENCE_NUMBER => c_aca_rec.PREFERENCE_NUMBER,
3981 x_ADM_DOC_STATUS => c_aca_rec.ADM_DOC_STATUS,
3982 x_ADM_ENTRY_QUAL_STATUS => c_aca_rec.ADM_ENTRY_QUAL_STATUS,
3983 x_DEFICIENCY_IN_PREP => c_aca_rec.DEFICIENCY_IN_PREP,
3984 x_LATE_ADM_FEE_STATUS => c_aca_rec.LATE_ADM_FEE_STATUS,
3985 x_SPL_CONSIDER_COMMENTS => c_aca_rec.SPL_CONSIDER_COMMENTS,
3986 x_APPLY_FOR_FINAID => c_aca_rec.APPLY_FOR_FINAID,
3987 x_FINAID_APPLY_DATE => c_aca_rec.FINAID_APPLY_DATE,
3988 x_ADM_OUTCOME_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('PENDING'),
3989 x_adm_otcm_stat_auth_per_id => c_aca_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
3990 x_ADM_OUTCOME_STATUS_AUTH_DT => c_aca_rec.ADM_OUTCOME_STATUS_AUTH_DT,
3991 x_ADM_OUTCOME_STATUS_REASON => c_aca_rec.ADM_OUTCOME_STATUS_REASON ,
3992 x_OFFER_DT => NULL,
3993 x_OFFER_RESPONSE_DT => NULL,
3994 x_PRPSD_COMMENCEMENT_DT => NULL,
3995 x_ADM_CNDTNL_OFFER_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_ACOS('NOT-APPLIC'),
3996 x_CNDTNL_OFFER_SATISFIED_DT => NULL,
3997 x_cndnl_ofr_must_be_stsfd_ind => NULL,
3998 x_ADM_OFFER_RESP_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AORS('NOT-APPLIC'),
3999 x_ACTUAL_RESPONSE_DT => NULL,
4000 x_ADM_OFFER_DFRMNT_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AODS('NOT-APPLIC'),
4001 x_DEFERRED_ADM_CAL_TYPE => NULL,
4002 x_DEFERRED_ADM_CI_SEQUENCE_NUM => NULL,
4003 x_DEFERRED_TRACKING_ID => c_aca_rec.DEFERRED_TRACKING_ID,
4004 x_ASS_RANK => c_aca_rec.ASS_RANK,
4005 x_SECONDARY_ASS_RANK => c_aca_rec.SECONDARY_ASS_RANK,
4006 x_intr_accept_advice_num => c_aca_rec.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
4007 x_ASS_TRACKING_ID => c_aca_rec.ASS_TRACKING_ID ,
4008 x_FEE_CAT => c_aca_rec.FEE_CAT,
4009 x_HECS_PAYMENT_OPTION => c_aca_rec.HECS_PAYMENT_OPTION,
4010 x_EXPECTED_COMPLETION_YR => c_aca_rec.EXPECTED_COMPLETION_YR,
4011 x_EXPECTED_COMPLETION_PERD => c_aca_rec.EXPECTED_COMPLETION_PERD,
4012 x_CORRESPONDENCE_CAT => c_aca_rec.CORRESPONDENCE_CAT,
4013 x_ENROLMENT_CAT => c_aca_rec.ENROLMENT_CAT,
4014 x_FUNDING_SOURCE => c_aca_rec.FUNDING_SOURCE,
4015 x_APPLICANT_ACPTNCE_CNDTN => NULL ,
4016 x_CNDTNL_OFFER_CNDTN => NULL,
4017 X_MODE => 'S',
4018 X_SS_APPLICATION_ID => c_aca_rec.SS_APPLICATION_ID,
4019 X_SS_PWD => c_aca_rec.SS_PWD,
4020 X_AUTHORIZED_DT => c_aca_rec.AUTHORIZED_DT,
4021 X_AUTHORIZING_PERS_ID => c_aca_rec.AUTHORIZING_PERS_ID,
4022 x_entry_status => c_aca_rec.entry_status,
4023 x_entry_level => c_aca_rec.entry_level,
4024 x_sch_apl_to_id => c_aca_rec.sch_apl_to_id,
4025 x_idx_calc_date => c_aca_rec.IDX_CALC_DATE,
4026 x_waitlist_status => NULL,
4027 x_ATTRIBUTE21 => c_aca_rec.ATTRIBUTE21,
4028 x_ATTRIBUTE22 => c_aca_rec.ATTRIBUTE22,
4029 x_ATTRIBUTE23 => c_aca_rec.ATTRIBUTE23,
4030 x_ATTRIBUTE24 => c_aca_rec.ATTRIBUTE24,
4031 x_ATTRIBUTE25 => c_aca_rec.ATTRIBUTE25,
4032 x_ATTRIBUTE26 => c_aca_rec.ATTRIBUTE26,
4033 x_ATTRIBUTE27 => c_aca_rec.ATTRIBUTE27,
4034 x_ATTRIBUTE28 => c_aca_rec.ATTRIBUTE28,
4035 x_ATTRIBUTE29 => c_aca_rec.ATTRIBUTE29,
4036 x_ATTRIBUTE30 => c_aca_rec.ATTRIBUTE30,
4037 x_ATTRIBUTE31 => c_aca_rec.ATTRIBUTE31,
4038 x_ATTRIBUTE32 => c_aca_rec.ATTRIBUTE32,
4039 x_ATTRIBUTE33 => c_aca_rec.ATTRIBUTE33,
4040 x_ATTRIBUTE34 => c_aca_rec.ATTRIBUTE34,
4041 x_ATTRIBUTE35 => c_aca_rec.ATTRIBUTE35,
4042 x_ATTRIBUTE36 => c_aca_rec.ATTRIBUTE36,
4043 x_ATTRIBUTE37 => c_aca_rec.ATTRIBUTE37,
4044 x_ATTRIBUTE38 => c_aca_rec.ATTRIBUTE38,
4045 x_ATTRIBUTE39 => c_aca_rec.ATTRIBUTE39,
4046 x_ATTRIBUTE40 => c_aca_rec.ATTRIBUTE40,
4047 x_fut_acad_cal_type => NULL,
4048 x_fut_acad_ci_sequence_number => NULL,
4049 x_fut_adm_cal_type => NULL,
4050 x_fut_adm_ci_sequence_number => NULL,
4051 x_prev_term_adm_appl_number => c_aca_rec.previous_term_adm_appl_number,
4052 x_prev_term_sequence_number => c_aca_rec.previous_term_sequence_number,
4053 x_fut_term_adm_appl_number => c_aca_rec.future_term_adm_appl_number,
4054 x_fut_term_sequence_number => c_aca_rec.future_term_sequence_number,
4055 x_def_acad_cal_type => NULL,
4056 x_def_acad_ci_sequence_num => NULL,
4057 x_def_prev_term_adm_appl_num => c_aca_rec.def_prev_term_adm_appl_num,
4058 x_def_prev_appl_sequence_num => c_aca_rec.def_prev_appl_sequence_num,
4059 x_def_term_adm_appl_num => c_aca_rec.def_term_adm_appl_num,
4060 x_def_appl_sequence_num => c_aca_rec.def_appl_sequence_num,
4061 x_appl_inst_status => c_aca_rec.appl_inst_status,
4062 x_ais_reason => c_aca_rec.ais_reason,
4063 x_decline_ofr_reason => NULL
4064 );
4065
4066
4067
4068 IF p_interface = 'FORM' OR p_interface = 'SS' THEN
4069 -- Raises oracle.apps.igs.pe.rescal.os Business event for Self service and forms
4070 igs_ad_wf_001.wf_raise_event ( p_person_id => c_aca_rec.Person_Id,
4071 p_raised_for => 'AOD',
4072 p_admission_appl_number => c_aca_rec.Admission_Appl_Number,
4073 p_nominated_course_cd => c_aca_rec.Nominated_Course_cd,
4074 p_sequence_number => c_aca_rec.Sequence_Number,
4075 p_old_outcome_status => c_aca_rec.adm_outcome_status,
4076 p_new_outcome_status => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('PENDING')
4077 );
4078
4079
4080 ELSIF p_interface = 'IMPORT' THEN
4081 --Raise oracle.apps.igs.pe.rescal.io Business event for Decision Import process
4082 igs_ad_wf_001.wf_raise_event ( p_person_id => c_aca_rec.Person_Id,
4083 p_raised_for => 'IOD',
4084 p_admission_appl_number => c_aca_rec.Admission_Appl_Number,
4085 p_nominated_course_cd => c_aca_rec.Nominated_Course_cd,
4086 p_sequence_number => c_aca_rec.Sequence_Number,
4087 p_old_outcome_status => c_aca_rec.adm_outcome_status,
4088 p_new_outcome_status => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('PENDING')
4089 );
4090 END IF;
4091
4092 END LOOP;
4093
4094 END Reconsider_Appl_Inst ;
4095
4096
4097 -- following function would be invoked only from Admin Dashboard for reconsideration
4098 PROCEDURE Recon_Appl_inst (
4099 p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
4100 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
4101 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
4102 p_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
4103 p_interface VARCHAR2 -- Interface which has raised reconsideration ( Forms,Self service, Import process)
4104 )
4105 IS
4106
4107 CURSOR cur_ad_ps_appl (cur_person_id IGS_AD_PS_APPL.person_id%TYPE ,
4108 cur_admission_appl_number IGS_AD_PS_APPL.admission_appl_number%TYPE ,
4109 cur_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE ) IS
4110 SELECT rowid , IGS_AD_PS_APPL.*
4111 FROM IGS_AD_PS_APPL
4112 WHERE person_id = CUR_person_id AND
4113 admission_appl_number = CUR_admission_appl_number AND
4114 nominated_course_cd = CUR_nominated_course_cd;
4115
4116 cur_ad_ps_appl_rec cur_ad_ps_appl%ROWTYPE ;
4117
4118
4119
4120 BEGIN
4121
4122
4123 OPEN cur_ad_ps_appl(p_person_id ,p_ADMISSION_APPL_NUMBER,p_NOMINATED_COURSE_CD);
4124 FETCH cur_ad_ps_appl INTO cur_ad_ps_appl_rec;
4125 IGS_AD_PS_APPL_PKG.UPDATE_ROW (
4126 X_ROWID => cur_ad_ps_appl_rec.ROWID ,
4127 X_PERSON_ID => cur_ad_ps_appl_rec.PERSON_ID ,
4128 X_ADMISSION_APPL_NUMBER => cur_ad_ps_appl_rec.ADMISSION_APPL_NUMBER ,
4129 X_NOMINATED_COURSE_CD => cur_ad_ps_appl_rec.NOMINATED_COURSE_CD ,
4130 X_TRANSFER_COURSE_CD => cur_ad_ps_appl_rec.transfer_course_cd,
4131 X_BASIS_FOR_ADMISSION_TYPE => cur_ad_ps_appl_rec.basis_for_admission_type,
4132 X_ADMISSION_CD => cur_ad_ps_appl_rec.admission_cd,
4133 X_COURSE_RANK_SET => cur_ad_ps_appl_rec.course_rank_set,
4134 X_COURSE_RANK_SCHEDULE => cur_ad_ps_appl_rec.course_rank_schedule,
4135 X_REQ_FOR_RECONSIDERATION_IND => 'Y',
4136 X_REQ_FOR_ADV_STANDING_IND => cur_ad_ps_appl_rec.req_for_adv_standing_ind ,
4137 X_MODE => 'S' ) ;
4138
4139 CLOSE cur_ad_ps_appl;
4140
4141 Reconsider_Appl_Inst ( p_person_id,
4142 p_admission_appl_number,
4143 p_nominated_course_cd,
4144 p_acai_sequence_number,
4145 p_interface
4146 );
4147
4148 END Recon_Appl_inst;
4149
4150 -----------------***************** check_adm_appl_inst_stat overloaded function to invoke in Selfservice ***------------------------------------
4151 FUNCTION check_adm_appl_inst_stat(
4152 p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
4153 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
4154 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE DEFAULT NULL,
4155 p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE DEFAULT NULL,
4156 p_updateable VARCHAR2 DEFAULT 'N' -- apadegal - TD001 - IGS.M.
4157 )
4158 RETURN VARCHAR2
4159 IS
4160
4161 l_status VARCHAR2(1):= 'N';
4162
4163 BEGIN
4164
4165 check_adm_appl_inst_stat( p_person_id,
4166 p_admission_appl_number,
4167 p_nominated_course_cd,
4168 p_sequence_number,
4169 p_updateable);
4170 RETURN 'Y';
4171
4172 EXCEPTION
4173 WHEN OTHERS THEN
4174 RETURN 'N';
4175 END check_adm_appl_inst_stat;
4176
4177 PROCEDURE ins_dummy_pend_hist_rec ( p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
4178 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
4179 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE
4180 )
4181 IS
4182
4183 CURSOR c_inst IS
4184 SELECT rowid,acai.*
4185 FROM igs_ad_ps_appl_inst_all acai
4186 WHERE acai.person_id = p_person_id AND
4187 acai.admission_appl_number = p_admission_appl_number AND
4188 acai.nominated_course_cd = p_nominated_course_cd AND
4189 acai.future_term_adm_appl_number IS NULL AND
4190 acai.future_term_sequence_number IS NULL AND
4191 acai.def_term_adm_appl_num IS NULL AND
4192 acai.def_appl_sequence_num IS NULL AND
4193 NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(acai.APPL_INST_STATUS),'NULL') <> 'WITHDRAWN' AND
4194 IGS_AD_GEN_008.ADMP_GET_SAOS(acai.ADM_OUTCOME_STATUS) = 'PENDING'
4195 FOR UPDATE NOWAIT;
4196
4197 c_inst_rec c_inst%ROWTYPE;
4198
4199 CURSOR c_inst_last_who ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
4200 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
4201 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
4202 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
4203 )
4204 IS
4205 SELECT last_update_date
4206 FROM igs_ad_ps_appl_inst_all acinst
4207 WHERE acinst.person_id = cp_person_id AND
4208 acinst.admission_appl_number = cp_admission_appl_number AND
4209 acinst.nominated_course_cd = cp_nominated_course_cd AND
4210 acinst.sequence_number = cp_sequence_number;
4211
4212 c_inst_last_who_rec c_inst_last_who%ROWTYPE;
4213
4214
4215 CURSOR c_old_hist_dt ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
4216 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
4217 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
4218 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
4219 cp_old_update_on DATE)
4220 IS
4221 SELECT 'x'
4222 FROM IGS_AD_PS_APLINSTHST ahist
4223 WHERE person_id = cp_person_id
4224 AND admission_appl_number = cp_admission_appl_number
4225 AND nominated_course_cd = cp_nominated_course_cd
4226 AND sequence_number = cp_sequence_number
4227 AND hist_start_dt = cp_old_update_on;
4228
4229 CURSOR cur_ad_ps_appl ( cp_person_id igs_ad_ps_appl.person_id%type ,
4230 cp_admission_appl_number igs_ad_ps_appl.admission_appl_number%type ,
4231 cp_nominated_course_cd igs_ad_ps_appl.nominated_course_cd%type )
4232 IS
4233 SELECT req_for_reconsideration_ind
4234 FROM igs_ad_ps_appl
4235 WHERE person_id = cp_person_id and
4236 admission_appl_number = cp_admission_appl_number and
4237 nominated_course_cd = cp_nominated_course_cd;
4238
4239
4240 hist_rec IGS_AD_PS_APLINSTHST%ROWTYPE;
4241
4242 l_dummy VARCHAR2(1);
4243 lv_rowid VARCHAR2(25);
4244
4245
4246 BEGIN
4247
4248 IF ( p_person_id IS NOT NULL AND
4249 p_admission_appl_number IS NOT NULL AND
4250 p_nominated_course_cd IS NOT NULL
4251 )
4252 THEN
4253 FOR c_inst_rec IN c_inst --
4254 LOOP
4255
4256 --calling INSTANCE TBH, but not updating anything.. this is just to create a dummy history record.
4257
4258 IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW
4259 (
4260 X_Mode => 'S',
4261 X_RowId => c_inst_rec.rowid,
4262 X_Person_Id => c_inst_rec.Person_Id,
4263 X_Admission_Appl_Number => c_inst_rec.Admission_Appl_Number,
4264 X_Nominated_Course_Cd => c_inst_rec.Nominated_Course_Cd,
4265 X_Sequence_Number => c_inst_rec.Sequence_Number,
4266 X_Predicted_Gpa => c_inst_rec.Predicted_Gpa,
4267 X_Academic_Index => c_inst_rec.Academic_Index,
4268 X_Adm_Cal_Type => c_inst_rec.Adm_Cal_Type,
4269 X_App_File_Location => c_inst_rec.App_File_Location,
4270 X_Adm_Ci_Sequence_Number => c_inst_rec.Adm_Ci_Sequence_Number,
4271 X_Course_Cd => c_inst_rec.Course_Cd,
4272 X_App_Source_Id => c_inst_rec.App_Source_Id,
4273 X_Crv_Version_Number => c_inst_rec.Crv_Version_Number,
4274 X_Waitlist_Rank => c_inst_rec.Waitlist_Rank,
4275 X_Location_Cd => c_inst_rec.Location_Cd,
4276 X_Attent_Other_Inst_Cd => c_inst_rec.Attent_Other_Inst_Cd,
4277 X_Attendance_Mode => c_inst_rec.Attendance_Mode,
4278 X_Edu_Goal_Prior_Enroll_Id => c_inst_rec.Edu_Goal_Prior_Enroll_Id,
4279 X_Attendance_Type => c_inst_rec.Attendance_Type,
4280 X_Decision_Make_Id => c_inst_rec.Decision_Make_Id,
4281 X_Unit_Set_Cd => c_inst_rec.Unit_Set_Cd,
4282 X_Decision_Date => c_inst_rec.Decision_Date,
4283 X_Attribute_Category => c_inst_rec.Attribute_Category,
4284 X_Attribute1 => c_inst_rec.Attribute1,
4285 X_Attribute2 => c_inst_rec.Attribute2,
4286 X_Attribute3 => c_inst_rec.Attribute3,
4287 X_Attribute4 => c_inst_rec.Attribute4,
4288 X_Attribute5 => c_inst_rec.Attribute5,
4289 X_Attribute6 => c_inst_rec.Attribute6,
4290 X_Attribute7 => c_inst_rec.Attribute7,
4291 X_Attribute8 => c_inst_rec.Attribute8,
4292 X_Attribute9 => c_inst_rec.Attribute9,
4293 X_Attribute10 => c_inst_rec.Attribute10,
4294 X_Attribute11 => c_inst_rec.Attribute11,
4295 X_Attribute12 => c_inst_rec.Attribute12,
4296 X_Attribute13 => c_inst_rec.Attribute13,
4297 X_Attribute14 => c_inst_rec.Attribute14,
4298 X_Attribute15 => c_inst_rec.Attribute15,
4299 X_Attribute16 => c_inst_rec.Attribute16,
4300 X_Attribute17 => c_inst_rec.Attribute17,
4301 X_Attribute18 => c_inst_rec.Attribute18,
4302 X_Attribute19 => c_inst_rec.Attribute19,
4303 X_Attribute20 => c_inst_rec.Attribute20,
4304 X_Decision_Reason_Id => c_inst_rec.Decision_Reason_Id,
4305 X_Us_Version_Number => c_inst_rec.Us_Version_Number,
4306 X_Decision_Notes => c_inst_rec.Decision_Notes,
4307 X_Pending_Reason_Id => c_inst_rec.Pending_Reason_Id,
4308 X_Preference_Number => c_inst_rec.Preference_Number,
4309 X_Adm_Doc_Status => c_inst_rec.Adm_Doc_Status,
4310 X_Adm_Entry_Qual_Status => c_inst_rec.Adm_Entry_Qual_Status,
4311 X_Deficiency_In_Prep => c_inst_rec.Deficiency_In_Prep,
4312 X_Late_Adm_Fee_Status => c_inst_rec.Late_Adm_Fee_Status,
4313 X_Spl_Consider_Comments => c_inst_rec.Spl_Consider_Comments,
4314 X_Apply_For_Finaid => c_inst_rec.Apply_For_Finaid,
4315 X_Finaid_Apply_Date => c_inst_rec.Finaid_Apply_Date,
4316 X_Adm_Outcome_Status => c_inst_rec.Adm_Outcome_Status,
4317 X_Adm_Otcm_Stat_Auth_Per_Id => c_inst_rec.Adm_Otcm_Status_Auth_Person_Id,
4318 X_Adm_Outcome_Status_Auth_Dt => c_inst_rec.Adm_Outcome_Status_Auth_Dt,
4319 X_Adm_Outcome_Status_Reason => c_inst_rec.Adm_Outcome_Status_Reason,
4320 X_Offer_Dt => c_inst_rec.Offer_Dt,
4321 X_Offer_Response_Dt => c_inst_rec.Offer_Response_Dt,
4322 X_Prpsd_Commencement_Dt => c_inst_rec.Prpsd_Commencement_Dt,
4323 X_Adm_Cndtnl_Offer_Status => c_inst_rec.Adm_Cndtnl_Offer_Status,
4324 X_Cndtnl_Offer_Satisfied_Dt => c_inst_rec.Cndtnl_Offer_Satisfied_Dt,
4325 X_Cndnl_Ofr_Must_Be_Stsfd_Ind => c_inst_rec.Cndtnl_Offer_Must_Be_Stsfd_Ind,
4326 X_Adm_Offer_Resp_Status => c_inst_rec.Adm_Offer_Resp_Status,
4327 X_Actual_Response_Dt => c_inst_rec.Actual_Response_Dt,
4328 X_Adm_Offer_Dfrmnt_Status => c_inst_rec.Adm_Offer_Dfrmnt_Status,
4329 X_Deferred_Adm_Cal_Type => c_inst_rec.Deferred_Adm_Cal_Type,
4330 X_Deferred_Adm_Ci_Sequence_Num => c_inst_rec.Deferred_Adm_Ci_Sequence_Num,
4331 X_Deferred_Tracking_Id => c_inst_rec.Deferred_Tracking_Id,
4332 X_Ass_Rank => c_inst_rec.Ass_Rank,
4333 X_Secondary_Ass_Rank => c_inst_rec.Secondary_Ass_Rank,
4334 X_Intr_Accept_Advice_Num => c_inst_rec.Intrntnl_Acceptance_Advice_Num,
4335 X_Ass_Tracking_Id => c_inst_rec.Ass_Tracking_Id,
4336 X_Fee_Cat => c_inst_rec.Fee_Cat,
4337 X_Hecs_Payment_Option => c_inst_rec.Hecs_Payment_Option,
4338 X_Expected_Completion_Yr => c_inst_rec.Expected_Completion_Yr,
4339 X_Expected_Completion_Perd => c_inst_rec.Expected_Completion_Perd,
4340 X_Correspondence_Cat => c_inst_rec.Correspondence_Cat,
4341 X_Enrolment_Cat => c_inst_rec.Enrolment_Cat,
4342 X_Funding_Source => c_inst_rec.Funding_Source,
4343 X_Applicant_Acptnce_Cndtn => c_inst_rec.Applicant_Acptnce_Cndtn,
4344 X_Cndtnl_Offer_Cndtn => c_inst_rec.Cndtnl_Offer_Cndtn,
4345 X_SS_APPLICATION_ID => c_inst_rec.SS_APPLICATION_ID,
4346 X_SS_PWD => c_inst_rec.SS_PWD,
4347 X_AUTHORIZED_DT => c_inst_rec.Authorized_dt,
4348 X_AUTHORIZING_PERS_ID => c_inst_rec.authorizing_pers_id,
4349 X_ENTRY_STATUS => c_inst_rec.entry_status,
4350 X_ENTRY_LEVEL => c_inst_rec.entry_level,
4351 X_SCH_APL_TO_ID => c_inst_rec.sch_apl_to_id,
4352 X_IDX_CALC_DATE => c_inst_rec.IDX_CALC_DATE,
4353 X_WAITLIST_STATUS => c_inst_rec.Waitlist_Status,
4354 X_Attribute21 => c_inst_rec.Attribute21,
4355 X_Attribute22 => c_inst_rec.Attribute22,
4356 X_Attribute23 => c_inst_rec.Attribute23,
4357 X_Attribute24 => c_inst_rec.Attribute24,
4358 X_Attribute25 => c_inst_rec.Attribute25,
4359 X_Attribute26 => c_inst_rec.Attribute26,
4360 X_Attribute27 => c_inst_rec.Attribute27,
4361 X_Attribute28 => c_inst_rec.Attribute28,
4362 X_Attribute29 => c_inst_rec.Attribute29,
4363 X_Attribute30 => c_inst_rec.Attribute30,
4364 X_Attribute31 => c_inst_rec.Attribute31,
4365 X_Attribute32 => c_inst_rec.Attribute32,
4366 X_Attribute33 => c_inst_rec.Attribute33,
4367 X_Attribute34 => c_inst_rec.Attribute34,
4368 X_Attribute35 => c_inst_rec.Attribute35,
4369 X_Attribute36 => c_inst_rec.Attribute36,
4370 X_Attribute37 => c_inst_rec.Attribute37,
4371 X_Attribute38 => c_inst_rec.Attribute38,
4372 X_Attribute39 => c_inst_rec.Attribute39,
4373 X_Attribute40 => c_inst_rec.Attribute40,
4374 x_fut_acad_cal_type => c_inst_rec.future_acad_cal_type,
4375 x_fut_acad_ci_sequence_number => c_inst_rec.future_acad_ci_sequence_number,
4376 x_fut_adm_cal_type => c_inst_rec.future_adm_cal_type,
4377 x_fut_adm_ci_sequence_number => c_inst_rec.future_adm_ci_sequence_number,
4378 x_prev_term_adm_appl_number => c_inst_rec.previous_term_adm_appl_number,
4379 x_prev_term_sequence_number => c_inst_rec.previous_term_sequence_number,
4380 x_fut_term_adm_appl_number => c_inst_rec.future_term_adm_appl_number,
4381 x_fut_term_sequence_number => c_inst_rec.future_term_sequence_number,
4382 x_def_acad_cal_type =>c_inst_rec.def_acad_cal_type,
4383 x_def_acad_ci_sequence_num =>c_inst_rec.def_acad_ci_sequence_num,
4384 x_def_prev_term_adm_appl_num =>c_inst_rec.def_prev_term_adm_appl_num,
4385 x_def_prev_appl_sequence_num =>c_inst_rec.def_prev_appl_sequence_num,
4386 x_def_term_adm_appl_num =>c_inst_rec.def_term_adm_appl_num,
4387 x_def_appl_sequence_num =>c_inst_rec.def_appl_sequence_num,
4388 x_appl_inst_status =>c_inst_rec.APPL_INST_STATUS, --apadegal adtd001 IGS.m
4389 x_ais_reason =>c_inst_rec.AIS_REASON, --apadegal adtd001 IGS.m
4390 x_decline_ofr_reason =>c_inst_rec.DECLINE_OFR_REASON --apadegal adtd001 IGS.m
4391 );
4392
4393
4394 -- Fetch New Who column values
4395 hist_rec.person_id := c_inst_rec.person_id;
4396 hist_rec.admission_appl_number := c_inst_rec.admission_appl_number;
4397 hist_rec.nominated_course_cd := c_inst_rec.nominated_course_cd;
4398 hist_rec.sequence_number := c_inst_rec.sequence_number;
4399 hist_rec.hist_start_dt := c_inst_rec.last_update_date;
4400 hist_rec.hist_who := c_inst_rec.last_updated_by;
4401
4402
4403 OPEN cur_ad_ps_appl (c_inst_rec.person_id,
4404 c_inst_rec.admission_appl_number,
4405 c_inst_rec.nominated_course_cd);
4406 FETCH cur_ad_ps_appl INTO hist_rec.RECONSIDER_FLAG ;
4407 CLOSE cur_ad_ps_appl;
4408
4409 -- to get the end date for history record
4410 OPEN c_inst_last_who (c_inst_rec.person_id,
4411 c_inst_rec.admission_appl_number,
4412 c_inst_rec.nominated_course_cd,
4413 c_inst_rec.sequence_number
4414 );
4415 FETCH c_inst_last_who INTO c_inst_last_who_rec;
4416 CLOSE c_inst_last_who;
4417
4418 hist_rec.hist_end_dt := c_inst_last_who_rec.last_update_date ;
4419
4420 l_dummy := NULL;
4421 OPEN c_old_hist_dt (c_inst_rec.person_id,
4422 c_inst_rec.admission_appl_number,
4423 c_inst_rec.nominated_course_cd,
4424 c_inst_rec.sequence_number,
4425 hist_rec.hist_start_dt);
4426 FETCH c_old_hist_dt INTO l_dummy;
4427 CLOSE c_old_hist_dt;
4428 IF l_dummy IS NOT NULL THEN
4429 -- add one second from the hist_start_dt value
4430 -- to avoid a primary key constraint from occurring
4431 -- when saving the record. Modified as part of Bug:2315674
4432 hist_rec.hist_start_dt := hist_rec.hist_start_dt +1 / (60*24*60);
4433 hist_rec.hist_end_dt := hist_rec.hist_end_dt +1 / (60*24*60);
4434 END IF;
4435
4436 -- call History TBH
4437 lv_rowid := NULL;
4438 IGS_AD_PS_APLINSTHST_Pkg.Insert_Row (
4439 X_Mode => 'R',
4440 X_RowId => lv_rowid,
4441 X_Person_Id => hist_rec.person_id,
4442 X_Admission_Appl_Number => hist_rec.admission_appl_number,
4443 X_Nominated_Course_Cd => hist_rec.nominated_course_cd,
4444 X_Sequence_Number => hist_rec.sequence_number,
4445 X_Hist_Start_Dt => hist_rec.hist_start_dt,
4446 X_Hist_End_Dt => hist_rec.hist_end_dt,
4447 X_Hist_Who => hist_rec.hist_who,
4448 X_Hist_Offer_Round_Number => Null,
4449 X_Adm_Cal_Type => hist_rec.adm_cal_type,
4450 X_Adm_Ci_Sequence_Number => hist_rec.adm_ci_sequence_number,
4451 X_Course_Cd => hist_rec.course_cd,
4452 X_Crv_Version_Number => hist_rec.crv_version_number,
4453 X_Location_Cd => hist_rec.location_cd,
4454 X_Attendance_Mode => hist_rec.attendance_mode,
4455 X_Attendance_Type => hist_rec.attendance_type,
4456 X_Unit_Set_Cd => hist_rec.unit_set_cd,
4457 X_Us_Version_Number => hist_rec.us_version_number,
4458 X_Preference_Number => hist_rec.preference_number,
4459 X_Adm_Doc_Status => hist_rec.adm_doc_status,
4460 X_Adm_Entry_Qual_Status => hist_rec.adm_entry_qual_status,
4461 X_Late_Adm_Fee_Status => hist_rec.late_adm_fee_status,
4462 X_Adm_Outcome_Status => hist_rec.adm_outcome_status,
4463 X_ADM_OTCM_STATUS_AUTH_PER_ID => hist_rec.adm_otcm_status_auth_person_id,
4464 X_Adm_Outcome_Status_Auth_Dt => hist_rec.adm_outcome_status_auth_dt,
4465 X_Adm_Outcome_Status_Reason => hist_rec.adm_outcome_status_reason,
4466 X_Offer_Dt => hist_rec.offer_dt,
4467 X_Offer_Response_Dt => hist_rec.offer_response_dt,
4468 X_Prpsd_Commencement_Dt => hist_rec.prpsd_commencement_dt,
4469 X_Adm_Cndtnl_Offer_Status => hist_rec.adm_cndtnl_offer_status,
4470 X_Cndtnl_Offer_Satisfied_Dt => hist_rec.cndtnl_offer_satisfied_dt,
4471 X_CNDTNL_OFR_MUST_BE_STSFD_IND => hist_rec.cndtnl_offer_must_be_stsfd_ind,
4472 X_Adm_Offer_Resp_Status => hist_rec.adm_offer_resp_status,
4473 X_Actual_Response_Dt => hist_rec.actual_response_dt,
4474 X_Adm_Offer_Dfrmnt_Status => hist_rec.adm_offer_dfrmnt_status,
4475 X_Deferred_Adm_Cal_Type => hist_rec.deferred_adm_cal_type,
4476 X_Deferred_Adm_Ci_Sequence_Num => hist_rec.deferred_adm_ci_sequence_num,
4477 X_Deferred_Tracking_Id => hist_rec.deferred_tracking_id,
4478 X_Ass_Rank => hist_rec.ass_rank,
4479 X_Secondary_Ass_Rank => hist_rec.secondary_ass_rank,
4480 X_INTRNTNL_ACCEPT_ADVICE_NUM => hist_rec.intrntnl_acceptance_advice_num,
4481 X_Ass_Tracking_Id => hist_rec.ass_tracking_id,
4482 X_Fee_Cat => hist_rec.fee_cat,
4483 X_Hecs_Payment_Option => hist_rec.hecs_payment_option,
4484 X_Expected_Completion_Yr => hist_rec.expected_completion_yr,
4485 X_Expected_Completion_Perd => hist_rec.expected_completion_perd,
4486 X_Correspondence_Cat => hist_rec.correspondence_cat,
4487 X_Enrolment_Cat => hist_rec.enrolment_cat,
4488 X_Funding_Source => hist_rec.funding_source,
4489 X_Applicant_Acptnce_Cndtn => hist_rec.applicant_acptnce_cndtn,
4490 X_Cndtnl_Offer_Cndtn => hist_rec.cndtnl_offer_cndtn,
4491 X_Org_Id => igs_ge_gen_003.get_org_id,
4492 X_Appl_inst_status => hist_rec.appl_inst_status,
4493 X_DECISION_DATE => hist_rec.DECISION_DATE,
4494 X_DECISION_MAKE_ID => hist_rec.DECISION_MAKE_ID,
4495 X_DECISION_REASON_ID => hist_rec.DECISION_REASON_ID,
4496 X_PENDING_REASON_ID => hist_rec.PENDING_REASON_ID,
4497 X_WAITLIST_STATUS => hist_rec.WAITLIST_STATUS,
4498 X_WAITLIST_RANK => hist_rec.WAITLIST_RANK,
4499 X_FUTURE_ACAD_CAL_TYPE => hist_rec.FUTURE_ACAD_CAL_TYPE,
4500 X_FUTURE_ACAD_CI_SEQUENCE_NUM => hist_rec.FUTURE_ACAD_CI_SEQUENCE_NUM,
4501 X_FUTURE_ADM_CAL_TYPE => hist_rec.FUTURE_ADM_CAL_TYPE,
4502 X_FUTURE_ADM_CI_SEQUENCE_NUM => hist_rec.FUTURE_ADM_CI_SEQUENCE_NUM,
4503 X_DEF_ACAD_CAL_TYPE => hist_rec.DEF_ACAD_CAL_TYPE,
4504 X_DEF_ACAD_CI_SEQUENCE_NUM => hist_rec.DEF_ACAD_CI_SEQUENCE_NUM,
4505 X_RECONSIDER_FLAG => hist_rec.RECONSIDER_FLAG,
4506 X_DECLINE_OFR_REASON => hist_rec.DECLINE_OFR_REASON
4507
4508 );
4509
4510 END LOOP;
4511 END IF;
4512 END ins_dummy_pend_hist_rec;
4513
4514
4515 -- End apadegal TD001 build.
4516
4517
4518 -- 02-NOV-05 ANWEST Created for IGS.M ADTD002 AT Testing Issue #327
4519 FUNCTION Is_EntQualCode_Allowed (p_person_id IN NUMBER ,
4520 p_admission_appl_number IN NUMBER ,
4521 p_nominated_course_cd IN VARCHAR2,
4522 p_sequence_number IN NUMBER)
4523 RETURN VARCHAR2 IS
4524
4525 CURSOR c_aa_acaiv IS
4526 SELECT acaiv.future_term_adm_appl_number,
4527 acaiv.future_term_sequence_number,
4528 acaiv.def_term_adm_appl_num,
4529 acaiv.def_appl_sequence_num
4530 FROM igs_ad_ps_appl_inst acaiv
4531 WHERE acaiv.person_id = p_person_id AND
4532 acaiv.admission_appl_number = p_admission_appl_number AND
4533 acaiv.nominated_course_cd = p_nominated_course_cd AND
4534 acaiv.sequence_number = p_sequence_number;
4535
4536 v_aa_acaiv_rec c_aa_acaiv%ROWTYPE;
4537 v_ent_qual_codes VARCHAR2(1) DEFAULT 'N';
4538
4539 BEGIN
4540
4541 OPEN c_aa_acaiv;
4542 FETCH c_aa_acaiv INTO v_aa_acaiv_rec;
4543 IF c_aa_acaiv%FOUND THEN
4544
4545 IF ( v_aa_acaiv_rec.future_term_adm_appl_number IS NULL AND
4546 v_aa_acaiv_rec.future_term_sequence_number IS NULL AND
4547 v_aa_acaiv_rec.def_term_adm_appl_num IS NULL AND
4548 v_aa_acaiv_rec.def_appl_sequence_num IS NULL) THEN
4549
4550 v_ent_qual_codes := 'Y';
4551
4552 END IF;
4553 CLOSE c_aa_acaiv;
4554
4555 ELSE
4556
4557 CLOSE c_aa_acaiv;
4558
4559 END IF;
4560
4561 RETURN v_ent_qual_codes;
4562
4563 EXCEPTION
4564 WHEN OTHERS THEN
4565 IF c_aa_acaiv%ISOPEN THEN
4566 CLOSE c_aa_acaiv;
4567 END IF;
4568 v_ent_qual_codes := 'N';
4569 RETURN v_ent_qual_codes;
4570 END Is_EntQualCode_Allowed;
4571
4572 END igs_ad_gen_002;
4573
4574
4575