[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_GEN_010
Source
1 PACKAGE BODY igs_ad_gen_010 AS
2 /* $Header: IGSAD10B.pls 115.10 2003/12/03 20:48:59 knag ship $ */
3
4 /* who when what
5 sarakshi 6-May-2003 Bug#2858431,modified procedure admp_get_tac_ceprc,admp_get_tac_return to
6 change system reference codes to OTHER from TAC-FEE,TAC-HECS
7 */
8 FUNCTION admp_get_tac_api(
9 p_person_id IN NUMBER )
10 RETURN VARCHAR2 IS
11 gv_other_detail VARCHAR2(255);
12 BEGIN -- admp_get_tac_api
13 -- This function gets the TAC alternate person ID
14 DECLARE
15 cst_tac CONSTANT IGS_PE_PERSON_ID_TYP.s_person_id_type%TYPE := 'TAC';
16 v_tac_id IGS_PE_ALT_PERS_ID.api_person_id%TYPE;
17 CURSOR c_api_pit IS
18 SELECT api.api_person_id
19 FROM IGS_PE_ALT_PERS_ID api,
20 IGS_PE_PERSON_ID_TYP pit
21 WHERE api.pe_person_id = p_person_id AND
22 pit.person_id_type = api.person_id_type AND
23 pit.s_person_id_type = cst_tac;
24 BEGIN
25 OPEN c_api_pit;
26 FETCH c_api_pit INTO v_tac_id;
27 IF (c_api_pit%NOTFOUND) THEN
28 CLOSE c_api_pit;
29 RETURN NULL;
30 END IF;
31 CLOSE c_api_pit;
32 RETURN v_tac_id; -- This is a VARCHAR2
33 EXCEPTION
34 WHEN OTHERS THEN
35 IF (c_api_pit%ISOPEN) THEN
36 CLOSE c_api_pit;
37 END IF;
38 RAISE;
39 END;
40 EXCEPTION
41 WHEN OTHERS THEN
42 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
43 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_get_tac_api');
44 IGS_GE_MSG_STACK.ADD;
45 App_Exception.Raise_Exception;
46 END admp_get_tac_api;
47
48 FUNCTION Admp_Get_Tac_Ceprc(
49 p_adm_cal_type IN VARCHAR2 ,
50 p_adm_ci_sequence_number IN NUMBER ,
51 p_acad_cal_type IN VARCHAR2 ,
52 p_reference_cd IN VARCHAR2 ,
53 p_admission_cat IN OUT NOCOPY VARCHAR2 ,
54 p_course_cd OUT NOCOPY VARCHAR2 ,
55 p_version_number OUT NOCOPY NUMBER ,
56 p_cal_type OUT NOCOPY VARCHAR2 ,
57 p_location_cd OUT NOCOPY VARCHAR2 ,
58 p_attendance_mode OUT NOCOPY VARCHAR2 ,
59 p_attendance_type OUT NOCOPY VARCHAR2 ,
60 p_unit_set_cd OUT NOCOPY VARCHAR2 ,
61 p_us_version_number OUT NOCOPY NUMBER ,
62 p_coo_id OUT NOCOPY NUMBER ,
63 p_ref_cd_type OUT NOCOPY VARCHAR2 ,
64 p_message_name OUT NOCOPY VARCHAR2)
65 RETURN BOOLEAN IS
66 gv_other_detail VARCHAR2(255);
67 BEGIN -- admp_get_tac_ceprc
68 -- This module gets course offering option details from the
69 -- course entry point reference code table.
70 DECLARE
71 v_course_cd IGS_PS_ENT_PT_REF_CD.course_cd%TYPE;
72 v_version_number IGS_PS_ENT_PT_REF_CD.version_number%TYPE;
73 v_cal_type IGS_PS_ENT_PT_REF_CD.cal_type%TYPE;
74 v_location_cd IGS_PS_ENT_PT_REF_CD.location_cd%TYPE;
75 v_attendance_mode IGS_PS_ENT_PT_REF_CD.attendance_mode%TYPE;
76 v_attendance_type IGS_PS_ENT_PT_REF_CD.attendance_type%TYPE;
77 v_coo_id IGS_PS_ENT_PT_REF_CD.coo_id%TYPE;
78 v_ref_cd_type IGS_GE_REF_CD_TYPE.reference_cd_type%TYPE;
79 v_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE;
80 v_us_version_number IGS_EN_UNIT_SET.version_number%TYPE;
81 v_message_name varchar2(30);
82 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
83 CURSOR c_ceprc IS
84 SELECT ceprc.course_cd,
85 ceprc.version_number,
86 ceprc.cal_type,
87 ceprc.location_cd,
88 ceprc.attendance_mode,
89 ceprc.attendance_type,
90 ceprc.coo_id,
91 rct.s_reference_cd_type,
92 ceprc.unit_set_cd,
93 ceprc.us_version_number
94 FROM IGS_PS_ENT_PT_REF_CD ceprc,
95 IGS_GE_REF_CD_TYPE rct,
96 IGS_PS_VER crv,
97 IGS_PS_STAT cs
98 WHERE rct.s_reference_cd_type = 'OTHER' AND
99 ceprc.reference_cd_type = rct.reference_cd_type AND
100 ceprc.reference_cd = p_reference_cd AND
101 ceprc.cal_type = p_acad_cal_type AND
102 crv.course_cd = ceprc.course_cd AND
103 crv.version_number = ceprc.version_number AND
104 crv.expiry_dt IS NULL AND
105 cs.course_status = crv.course_status AND
106 cs.s_course_status = cst_active AND
107 (ceprc.unit_set_cd IS NULL OR
108 EXISTS (
109 SELECT 'x'
110 FROM IGS_EN_UNIT_SET us,
111 IGS_EN_UNIT_SET_STAT uss
112 WHERE us.unit_set_cd = ceprc.unit_set_cd AND
113 us.version_number = ceprc.us_version_number AND
114 expiry_dt IS NULL AND
115 uss.unit_set_status = us.unit_set_status AND
116 uss.s_unit_set_status = cst_active));
117 BEGIN
118 p_message_name := null;
119 OPEN c_ceprc;
120 LOOP
121 FETCH c_ceprc INTO v_course_cd,
122 v_version_number,
123 v_cal_type,
124 v_location_cd,
125 v_attendance_mode,
126 v_attendance_type,
127 v_coo_id,
128 v_ref_cd_type,
129 v_unit_set_cd,
130 v_us_version_number;
131 EXIT WHEN (c_ceprc%NOTFOUND);
132 IF (c_ceprc%ROWCOUNT > 1) THEN
133 exit;
134 END IF;
135 END LOOP;
136 IF (c_ceprc%ROWCOUNT = 0) THEN
137 p_message_name := 'IGS_GE_INVALID_VALUE';
138 CLOSE c_ceprc;
139 RETURN FALSE;
140 ELSIF (c_ceprc%ROWCOUNT > 1) THEN
141 p_message_name := 'IGS_AD_MULTIPLE_PRG_GOUND';
142 CLOSE c_ceprc;
143 RETURN FALSE;
144 END IF;
145 CLOSE c_ceprc;
146 IF IGS_AD_PRC_TAC_OFFER.admp_get_ac_cooac(
147 v_coo_id,
148 p_admission_cat,
149 v_message_name) = FALSE THEN
150 p_message_name := v_message_name;
151
152 RETURN FALSE;
153 END IF;
154 IF IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_admperd(
155 p_adm_cal_type,
156 p_adm_ci_sequence_number,
157 p_admission_cat,
158 'COURSE',
159 v_course_cd,
160 v_version_number,
161 v_cal_type,
162 v_location_cd,
163 v_attendance_mode,
164 v_attendance_type,
165 v_message_name) = FALSE THEN
166 p_message_name := v_message_name;
167 RETURN FALSE;
168 END IF;
169 p_course_cd := v_course_cd;
170 p_version_number := v_version_number;
171 p_cal_type := v_cal_type;
172 p_location_cd := v_location_cd;
173 p_attendance_mode := v_attendance_mode;
174 p_attendance_type := v_attendance_type;
175 p_coo_id := v_coo_id;
176 p_ref_cd_type := v_ref_cd_type;
177 p_unit_set_cd := v_unit_set_cd;
178 p_us_version_number := v_us_version_number;
179 RETURN TRUE;
180 EXCEPTION
181 WHEN OTHERS THEN
182 IF (c_ceprc%ISOPEN) THEN
183 CLOSE c_ceprc;
184 END IF;
185 RAISE;
186 END;
187 EXCEPTION
188 WHEN OTHERS THEN
189 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
190 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_get_tac_ceprc');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END admp_get_tac_ceprc;
194
195 FUNCTION Admp_Get_Tac_Return(
196 p_tac_person_id IN VARCHAR2 ,
197 p_surname IN VARCHAR2 ,
198 p_given_name1 IN VARCHAR2 ,
199 p_given_name2 IN VARCHAR2 ,
200 p_tac_course_cd IN OUT NOCOPY VARCHAR2 ,
201 p_acad_cal_type IN VARCHAR2 ,
202 p_acad_ci_sequence_number IN NUMBER ,
203 p_offer_response OUT NOCOPY VARCHAR2 ,
204 p_enrol_status OUT NOCOPY VARCHAR2 ,
205 p_attendance_type OUT NOCOPY VARCHAR2 ,
206 p_attendance_mode OUT NOCOPY VARCHAR2 ,
207 p_message_name OUT NOCOPY VARCHAR2 )
208 RETURN BOOLEAN IS
209 gv_other_detail VARCHAR2(255);
210 BEGIN
211 -- admp_get_tac_return
212 -- This module takes information about a student from a TAC, attempts to find
213 -- the student and reports details of their admission and enrolment. If the
214 -- TAC IGS_PS_COURSE code is NULL and no adm_crs_appl_ins record can be found then
215 -- the process will return FALSE but the message name will be 0. This is
216 -- to show we didn't find the required information but this is not an error
217 -- in this case..
218 DECLARE
219 cst_intermit CONSTANT VARCHAR2(8) := 'INTERMIT';
220 cst_accept CONSTANT VARCHAR2(10) := 'ACCEPT';
221 cst_not_enrol CONSTANT VARCHAR2(10) := 'NOT-ENROL';
222 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
223 cst_dfr_grant CONSTANT VARCHAR2(10) := 'DFR-GRANT';
224 cst_dfr_reject CONSTANT VARCHAR2(10) := 'DFR-REJECT';
225 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
226 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
227 v_message_name varchar2(30);
228 v_matched_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE;
229 v_given_names IGS_PE_PERSON.given_names%TYPE;
230 v_course_cd IGS_PS_ENT_PT_REF_CD.course_cd%TYPE;
231 v_version_number IGS_PS_ENT_PT_REF_CD.version_number%TYPE;
232 v_location_cd IGS_PS_ENT_PT_REF_CD.location_cd%TYPE;
233 v_attendance_mode IGS_PS_ENT_PT_REF_CD.attendance_mode%TYPE;
234 v_attendance_type IGS_PS_ENT_PT_REF_CD.attendance_type%TYPE;
235 v_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE;
236 v_adm_offer_dfrmnt_status
237 IGS_AD_OFRDFRMT_STAT.s_adm_offer_dfrmnt_status%TYPE;
238 v_enrol_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
239 v_tac_match BOOLEAN;
240 v_course_off_found BOOLEAN;
241 v_course_ins_found BOOLEAN;
242 CURSOR c_tac_id_api IS
243 SELECT api.pe_person_id
244 FROM IGS_PE_ALT_PERS_ID api,
245 IGS_PE_PERSON_ID_TYP pit
246 WHERE pit.s_person_id_type = 'TAC' AND
247 api.person_id_type = pit.person_id_type AND
248 api.api_person_id = p_tac_person_id;
249 CURSOR c_tac_id_pe(
250 cp_given_names VARCHAR2) IS
251 SELECT party_id person_id
252 FROM hz_parties
253 WHERE person_last_name = p_surname AND
254 person_first_name = cp_given_names;
255 CURSOR c_course_offer IS
256 SELECT ceprc.course_cd,
257 ceprc.version_number,
258 ceprc.location_cd,
259 ceprc.attendance_mode,
260 ceprc.attendance_type
261 FROM IGS_PS_ENT_PT_REF_CD ceprc,
262 IGS_GE_REF_CD_TYPE rct,
263 IGS_PS_VER crv,
264 IGS_PS_STAT cs
265 WHERE rct.s_reference_cd_type = 'OTHER' AND
266 ceprc.reference_cd_type = rct.reference_cd_type AND
267 ceprc.reference_cd = p_tac_course_cd AND
268 crv.course_cd = ceprc.course_cd AND
269 crv.version_number = ceprc.version_number AND
270 crv.expiry_dt IS NULL AND
271 cs.course_status = crv.course_status AND
272 cs.s_course_status = cst_active AND
273 (ceprc.unit_set_cd IS NULL OR
274 EXISTS (
275 SELECT 'x'
276 FROM IGS_EN_UNIT_SET us,
277 IGS_EN_UNIT_SET_STAT uss
278 WHERE us.unit_set_cd = ceprc.unit_set_cd AND
279 us.version_number = ceprc.us_version_number AND
280 expiry_dt IS NULL AND
281 uss.unit_set_status = us.unit_set_status AND
282 uss.s_unit_set_status = cst_active))
283 ORDER BY rct.s_reference_cd_type DESC;
284 CURSOR c_course_ins_1(
285 cp_matched_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE,
286 cp_course_cd IGS_PS_ENT_PT_REF_CD.course_cd%TYPE,
287 cp_version_number IGS_PS_ENT_PT_REF_CD.version_number%TYPE,
288 cp_location_cd IGS_PS_ENT_PT_REF_CD.location_cd%TYPE,
289 cp_attendance_mode IGS_PS_ENT_PT_REF_CD.attendance_mode%TYPE,
290 cp_attendance_type IGS_PS_ENT_PT_REF_CD.attendance_type%TYPE) IS
291 SELECT aors.s_adm_offer_resp_status,
292 aods.s_adm_offer_dfrmnt_status
293 FROM igs_ad_appl aa,
294 igs_ad_ps_appl_inst acaiv, /* Replaced IGS_AD_PS_APPL_INST_APLINST_V with IGS_AD_PS_APPL_INST. Bug 3150054 */
295 igs_ad_ofr_resp_stat aors,
296 igs_ad_ofrdfrmt_stat aods
297 WHERE acaiv.person_id = cp_matched_id AND
298 aa.acad_cal_type = p_acad_cal_type AND
299 aa.acad_ci_sequence_number = p_acad_ci_sequence_number AND
300 acaiv.course_cd = cp_course_cd AND
301 acaiv.crv_version_number = cp_version_number AND
302 acaiv.location_cd = cp_location_cd AND
303 acaiv.attendance_mode = cp_attendance_mode AND
304 acaiv.attendance_type = cp_attendance_type AND
305 aors.adm_offer_resp_status = acaiv.adm_offer_resp_status AND
306 aods.adm_offer_dfrmnt_status = acaiv.adm_offer_dfrmnt_status AND
307 aa.person_id = acaiv.person_id AND
308 aa.admission_appl_number = acaiv.admission_appl_number;
309
310 CURSOR c_course_ins_2(
311 cp_matched_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE) IS
312 SELECT aors.s_adm_offer_resp_status,
313 aods.s_adm_offer_dfrmnt_status,
314 acaiv.course_cd,
315 ceprc.reference_cd
316 FROM igs_ad_appl aa,
317 IGS_AD_PS_APPL_INST acaiv, /* Replaced IGS_AD_PS_APPL_INST_APLINST_V with IGS_AD_PS_APPL_INST. Bug 3150054 */
318 IGS_AD_OFR_RESP_STAT aors,
319 IGS_AD_OFRDFRMT_STAT aods,
320 IGS_PS_ENT_PT_REF_CD ceprc,
321 IGS_GE_REF_CD_TYPE rct
322 WHERE acaiv.person_id = cp_matched_id AND
323 aa.acad_cal_type = p_acad_cal_type AND
324 aa.acad_ci_sequence_number = p_acad_ci_sequence_number AND
325 aods.adm_offer_dfrmnt_status = acaiv.adm_offer_dfrmnt_status AND
326 aors.adm_offer_resp_status = acaiv.adm_offer_resp_status AND
327 ceprc.course_cd = acaiv.course_cd AND
328 ceprc.version_number = acaiv.crv_version_number AND
329 ceprc.location_cd = acaiv.location_cd AND
330 ceprc.attendance_mode = acaiv.attendance_mode AND
331 ceprc.attendance_type = acaiv.attendance_type AND
332 ceprc.reference_cd_type = rct.reference_cd_type AND
333 rct.s_reference_cd_type = 'OTHER' AND
334 aa.person_id = acaiv.person_id AND
335 aa.admission_appl_number = acaiv.admission_appl_number
336 ORDER BY rct.s_reference_cd_type DESC;
337 v_course_ins_rec_2 c_course_ins_2%ROWTYPE;
338 CURSOR c_govt_value(
339 cp_attendance_type IGS_PS_ENT_PT_REF_CD.attendance_type%TYPE) IS
340 SELECT govt_attendance_type
341 FROM IGS_EN_ATD_TYPE
342 WHERE attendance_type = v_attendance_type;
343 CURSOR c_attn_mode(
344 cp_course_cd IGS_PS_ENT_PT_REF_CD.course_cd%TYPE,
345 cp_person_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE) IS
346 SELECT am.govt_attendance_mode
347 FROM IGS_EN_STDNT_PS_ATT sca,
348 IGS_EN_ATD_MODE am
349 WHERE sca.course_cd = cp_course_cd AND
350 sca.person_id = cp_person_id AND
351 am.attendance_mode = sca.attendance_mode;
352 BEGIN
353 -- Set values for the output parameters. These are the default values
354 -- we want to pass back.
355 p_offer_response := 'REJECTED';
356 p_enrol_status := 'NOT-ENROL';
357 p_attendance_type := NULL;
358 p_attendance_mode := NULL;
359 p_message_name := null;
360 v_message_name := null;
361
362 -- Find student by finding stored TAC ID number.
363 v_tac_match := FALSE;
364 FOR v_tac_id_rec IN c_tac_id_api LOOP
365 v_tac_match := TRUE;
366 IF (c_tac_id_api%ROWCOUNT >1) THEN
367 v_message_name := 'IGS_AD_CANNOT_MATCH_PERSONID';
368 v_tac_match := FALSE;
369 exit;
370 END IF;
371 v_matched_id := v_tac_id_rec.pe_person_id;
372 END LOOP;
373 IF (v_tac_match = FALSE) THEN
374 IF (v_message_name IS NULL) THEN
375 -- No api records find. Try to match on other details
376 v_given_names := RTRIM(p_given_name1 || ' ' || p_given_name2);
377 FOR v_tac_id_rec IN c_tac_id_pe(
378 v_given_names) LOOP
379 v_tac_match := TRUE;
380 IF (c_tac_id_pe%ROWCOUNT > 1) THEN
381 v_tac_match := FALSE;
382 exit;
383 END IF;
384 v_matched_id := v_tac_id_rec.person_id;
385 END LOOP;
386 IF (v_tac_match = FALSE) THEN
387 -- No match or more than one record found in IGS_PE_PERSON
388 p_message_name := 'IGS_AD_CANNOT_MATCH_PERSONID';
389 RETURN FALSE;
390 END IF;
391 ELSE
392 -- More than one api records found
393 p_message_name := v_message_name;
394 RETURN FALSE;
395 END IF;
396 END IF;
397 IF (p_tac_course_cd IS NOT NULL) THEN
398 -- Find the course offering option from the TAC course code.
399 v_course_off_found := FALSE;
400 FOR v_course_off_rec IN c_course_offer LOOP
401 v_course_off_found := TRUE;
402 IF (c_course_offer%ROWCOUNT > 1) THEN
403 v_course_off_found := FALSE;
404 v_message_name := 'IGS_AD_FOUNDMULTIPLE_PRGREFCD';
405 exit;
406 END IF;
407 v_course_cd := v_course_off_rec.course_cd;
408 v_version_number := v_course_off_rec.version_number;
409 v_location_cd := v_course_off_rec.location_cd;
410 v_attendance_mode := v_course_off_rec.attendance_mode;
411 v_attendance_type := v_course_off_rec.attendance_type;
412 END LOOP;
413 IF (v_course_off_found = FALSE) THEN
414 IF (v_message_name IS NULL) THEN
415 -- No IGS_PS_COURSE offering option records found.
416 p_message_name := 'IGS_AD_CANNOT_MATCH_TACPRGCD';
417 RETURN FALSE;
418 ELSE
419 -- More than one IGS_PS_COURSE offering option recores found.
420 p_message_name := v_message_name;
421 RETURN FALSE;
422 END IF;
423 END IF;
424 -- Find the IGS_AD_PS_APPL_INST.
425 v_course_ins_found := FALSE;
426 FOR v_course_ins_rec_1 IN c_course_ins_1(
427 v_matched_id,
428 v_course_cd,
429 v_version_number,
430 v_location_cd,
431 v_attendance_mode,
432 v_attendance_type) LOOP
433 v_course_ins_found := TRUE;
434 IF (c_course_ins_1%ROWCOUNT > 1) THEN
435 v_course_ins_found := FALSE;
436 v_message_name := 'IGS_AD_FOUND_MULTIPLE_ADMPRG';
437 exit;
438 END IF;
439 v_adm_offer_resp_status := v_course_ins_rec_1.s_adm_offer_resp_status;
440 v_adm_offer_dfrmnt_status := v_course_ins_rec_1.s_adm_offer_dfrmnt_status;
441 END LOOP;
442 IF (v_course_ins_found = FALSE) THEN
443 IF (v_message_name IS NULL) THEN
444 -- No IGS_AD_PS_APPL_INST records found.
445 p_message_name := 'IGS_AD_CANNOT_FIND_ADMPRGAPPL';
446 RETURN FALSE;
447 ELSE
448 -- More than one adm_course_appl_instanc recores found.
449 p_message_name := v_message_name;
450 RETURN FALSE;
451 END IF;
452 END IF;
453 ELSE -- No TAC IGS_PS_COURSE code supplied
454 -- Find the IGS_AD_PS_APPL_INST
455 OPEN c_course_ins_2(
456 v_matched_id);
457 FETCH c_course_ins_2 INTO v_course_ins_rec_2;
458 IF (c_course_ins_2%NOTFOUND) THEN
459 -- This is not an error but will still want to return FALSE.
460 CLOSE c_course_ins_2;
461 p_message_name := null;
462 RETURN FALSE;
463 END IF;
464 CLOSE c_course_ins_2;
465 -- If more than one record is found, just use the first record.
466 p_tac_course_cd := v_course_ins_rec_2.reference_cd;
467 END IF; -- IF (p_tac_course_cd IS NOT NULL)
468 IF (v_adm_offer_resp_status IN ('ACCEPTED', 'COND-ACC', 'PEND-ACC')) THEN
469 v_enrol_status := IGS_EN_GEN_006.enrp_get_sca_status(
470 v_matched_id,
471 v_course_cd,
472 NULL,
473 NULL,
474 NULL,
475 NULL,
476 NULL,
477 NULL);
478 IF (v_enrol_status IN ('DELETED','UNCONFIRM','LAPSED','INACTIVE',NULL)) THEN
479 p_offer_response := cst_accept;
480 p_enrol_status := cst_not_enrol;
481 RETURN TRUE;
482 ELSIF (v_enrol_status IN ('DISCONTIN', cst_intermit)) THEN
483 p_offer_response := cst_accept;
484 p_enrol_status := v_enrol_status;
485 -- Find Attendance Type as may be late withdrawl
486 v_attendance_type := IGS_EN_GEN_006.enrp_get_sca_att(
487 v_matched_id,
488 v_course_cd,
489 SYSDATE);
490 IF (v_attendance_type IS NULL) THEN
491 p_attendance_type := 0;
492 ELSE
493 -- Get the Govt. value
494 OPEN c_govt_value(
495 v_attendance_type);
496 FETCH c_govt_value INTO p_attendance_type;
497 CLOSE c_govt_value;
498 -- Find Attendance mode
499 OPEN c_attn_mode(
500 v_course_cd,
501 v_matched_id);
502 FETCH c_attn_mode INTO p_attendance_mode;
503 CLOSE c_attn_mode;
504 END IF;
505 RETURN TRUE;
506 ELSE
507 -- Find Attendance Type
508 v_attendance_type := IGS_EN_GEN_006.enrp_get_sca_att(
509 v_matched_id,
510 v_course_cd,
511 SYSDATE);
512 IF (v_attendance_type IS NULL) THEN
513 p_message_name := 'IGS_AD_NO_ATTTYPE_STUDPRG';
514 RETURN FALSE;
515 END IF;
516 -- Get the Govt. value
517 OPEN c_govt_value(
518 v_attendance_type);
519 FETCH c_govt_value INTO p_attendance_type;
520 CLOSE c_govt_value;
521 -- Find Attendance mode
522 OPEN c_attn_mode(
523 v_course_cd,
524 v_matched_id);
525 FETCH c_attn_mode INTO p_attendance_mode;
526 CLOSE c_attn_mode;
527 p_offer_response := cst_accept;
528 p_enrol_status := cst_enrolled;
529 RETURN TRUE;
530 END IF; -- if (v_enrol_status = cst_intermit)
531 ELSIF (v_adm_offer_resp_status = 'DEFERRAL') THEN
532 IF (v_adm_offer_dfrmnt_status = 'APPROVED') THEN
533 p_offer_response := cst_dfr_grant;
534 ELSE
535 p_offer_response := cst_dfr_reject;
536 END IF;
537 ELSE
538 p_offer_response := cst_rejected;
539 p_enrol_status := cst_not_enrol;
540 END IF; -- if v_adm_offer_resp_status IN ('ACCEPTED', 'COND-ACC', 'PEND-ACC')
541 RETURN TRUE;
542 END;
543 EXCEPTION
544 WHEN OTHERS THEN
545 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
546 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_get_tac_return');
547 IGS_GE_MSG_STACK.ADD;
548 App_Exception.Raise_Exception;
549 END admp_get_tac_return;
550
551 FUNCTION Admp_Get_Unit_Det(
552 p_person_id IN NUMBER ,
553 p_admission_appl_number IN NUMBER ,
554 p_nominated_course_cd IN VARCHAR2 ,
555 p_acai_sequence_number IN NUMBER ,
556 p_record_number IN NUMBER )
557 RETURN VARCHAR2 IS
558 gv_other_detail VARCHAR2(255);
559 BEGIN -- admp_get_unit_det
560 -- This module retrieves IGS_PS_UNIT details from IGS_AD_PS_APPL_INST
561 -- for use on letters.
562 DECLARE
563 v_out_string VARCHAR2(200) DEFAULT NULL;
564 v_comm_period VARCHAR2(21) DEFAULT NULL;
565 v_acad_alternate_code IGS_AD_APPL_ADMAPPL_V.acad_alternate_code%TYPE;
566 CURSOR c_uv_acaiuv IS
567 SELECT acaiuv.unit_cd,
568 acaiuv.uv_version_number,
569 acaiuv.location_cd,
570 acaiuv.unit_class,
571 acaiuv.unit_mode,
572 acaiuv.teach_alternate_code,
573 uv.short_title,
574 acaiuv.adm_unit_outcome_status
575 FROM IGS_AD_PS_APLINSTUNT_APLUNIT_V acaiuv,
576 IGS_PS_UNIT_VER uv
577 WHERE acaiuv.uv_version_number = uv.version_number AND
578 acaiuv.unit_cd = uv.unit_cd AND
579 acaiuv.person_id = p_person_id AND
580 acaiuv.admission_appl_number = p_admission_appl_number AND
581 acaiuv.nominated_course_cd = p_nominated_course_cd AND
582 acaiuv.acai_sequence_number = p_acai_sequence_number
583 ORDER BY acaiuv.unit_cd,
584 acaiuv.uv_version_number,
585 acaiuv.cal_type,
586 acaiuv.ci_sequence_number,
587 acaiuv.location_cd,
588 acaiuv.unit_class,
589 acaiuv.unit_mode;
590 CURSOR c_aav IS
591 SELECT ca.alternate_code acad_alternate_code /* Replace IGS_AD_APPL_ADMAPPL_V with IGS_AD_APPL and IGS_CA_INST tables Bug: 3150054 */
592 FROM igs_ad_appl aav,
593 igs_ca_inst ca
594 WHERE aav.person_id = p_person_id AND
595 aav.admission_appl_number = p_admission_appl_number AND
596 aav.acad_cal_type = ca.cal_type AND
597 aav.acad_ci_sequence_number = ca.sequence_number;
598 BEGIN
599 FOR v_uv_acaiuv_rec IN c_uv_acaiuv LOOP
600 IF c_uv_acaiuv%ROWCOUNT = p_record_number THEN
601 -- create output return string and create p_extra_context string
602 OPEN c_aav;
603 FETCH c_aav INTO v_acad_alternate_code;
604 IF c_aav%NOTFOUND THEN
605 v_comm_period := '-';
606 ELSE
607 v_comm_period := NVL(v_acad_alternate_code, '-') || '/' ||
608 NVL(v_uv_acaiuv_rec.teach_alternate_code, '-');
609 END IF;
610 CLOSE c_aav;
611 v_out_string := RPAD(NVL(v_uv_acaiuv_rec.unit_cd, '-'), 10) || ' ' ||
612 RPAD(NVL(v_uv_acaiuv_rec.short_title, '-'), 40) || ' ' ||
613 RPAD(NVL(v_uv_acaiuv_rec.location_cd, '-'), 10) || ' ' ||
614 RPAD(NVL(v_uv_acaiuv_rec.unit_mode, '-'), 10) || ' ' ||
615 RPAD(NVL(v_uv_acaiuv_rec.unit_class, '-'), 10) || ' ' ||
616 v_comm_period ||' '||
617 RPAD(NVL(v_uv_acaiuv_rec.adm_unit_outcome_status, '-'),10);
618 END IF;
619 END LOOP;
620 RETURN v_out_string;
621 EXCEPTION
622 WHEN OTHERS THEN
623 IF c_uv_acaiuv%ISOPEN THEN
624 CLOSE c_uv_acaiuv;
625 END IF;
626 IF c_aav%ISOPEN THEN
627 CLOSE c_aav;
628 END IF;
629 RAISE;
630 END;
631 EXCEPTION
632 WHEN OTHERS THEN
633 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
634 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_get_unit_det');
635 IGS_GE_MSG_STACK.ADD;
636 App_Exception.Raise_Exception;
637 END admp_get_unit_det;
638
639 FUNCTION Admp_Ins_Aal(
640 p_person_id IN NUMBER ,
641 p_admission_appl_number IN NUMBER ,
642 p_correspondence_type IN VARCHAR2 ,
643 p_admission_cat IN VARCHAR2 ,
644 p_s_admission_process_type IN VARCHAR2 ,
645 p_message_name OUT NOCOPY VARCHAR2 )
646 RETURN BOOLEAN IS
647 gv_other_detail VARCHAR2(255);
648 BEGIN -- admp_ins_aal
649 -- This module validates and inserts a record into the IGS_AD_APPL_LTR
650 -- table
651 DECLARE
652 v_sequence_num IGS_AD_APPL_LTR.sequence_number%TYPE;
653 v_message_name varchar2(30);
654 lv_rowid VARCHAR2(25);
655
656
657
658 -- Derive the next sequence number
659 CURSOR c_aal IS
660 SELECT NVL(MAX(sequence_number),0) + 1
661 FROM IGS_AD_APPL_LTR
662 WHERE person_id = p_person_id AND
663 admission_appl_number = p_admission_appl_number AND
664 correspondence_type = p_correspondence_type;
665 BEGIN
666 p_message_name := null;
667 -- Validate correspondence type for admission process category
668 IF IGS_AD_VAL_AAL.admp_val_aal_cort(
669 p_correspondence_type,
670 p_admission_cat,
671 p_s_admission_process_type,
672 v_message_name) = FALSE THEN
673 p_message_name := v_message_name;
674 RETURN FALSE;
675 END IF;
676 -- Validate correspondence type not closed
677 IF IGS_AD_VAL_AAL.corp_val_cort_closed(
678 p_correspondence_type,
679 v_message_name) = FALSE THEN
680 p_message_name := v_message_name;
681 RETURN FALSE;
682 END IF;
683 -- Validate that an unsent letter with this correspondence type does not
684 -- already exist.
685 IF IGS_AD_VAL_AAL.admp_val_aal_exists(
686 p_person_id,
687 p_admission_appl_number,
688 p_correspondence_type,
689 v_message_name) = FALSE THEN
690 p_message_name := v_message_name;
691 RETURN FALSE;
692 END IF;
693 OPEN c_aal;
694 FETCH c_aal INTO v_sequence_num;
695 CLOSE c_aal;
696 -- Insert new record
697 IGS_AD_APPL_Ltr_Pkg.Insert_Row (
698 X_Mode => 'R',
699 X_RowId => lv_rowid,
700 X_Person_Id => p_person_id,
701 X_Admission_Appl_Number => p_admission_appl_number,
702 X_Correspondence_Type => p_correspondence_type,
703 X_Sequence_Number => v_sequence_num,
704 X_Composed_Ind => 'Y',
705 X_Letter_Reference_Number => Null,
706 X_Spl_Sequence_Number => Null
707 );
708
709
710 RETURN TRUE;
711 END;
712 EXCEPTION
713 WHEN OTHERS THEN
714 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
715 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_ins_aal');
716 IGS_GE_MSG_STACK.ADD;
717 App_Exception.Raise_Exception;
718 END admp_ins_aal;
719
720 FUNCTION Admp_Ins_Aal_Commit(
721 p_person_id IN NUMBER ,
722 p_admission_appl_number IN NUMBER ,
723 p_correspondence_type IN VARCHAR2 ,
724 p_admission_cat IN VARCHAR2 ,
725 p_s_admission_process_type IN VARCHAR2 ,
726 p_message_name OUT NOCOPY VARCHAR2 )
727 RETURN BOOLEAN IS
728 gv_other_detail VARCHAR2(255);
729 BEGIN -- admp_ins_aal_commit
730 -- This module calls admp_ins_aal and performs a commit.
731 DECLARE
732 v_message_name varchar2(30);
733 BEGIN
734 p_message_name := null;
735 IF admp_ins_aal (
736 p_person_id,
737 p_admission_appl_number,
738 p_correspondence_type,
739 p_admission_cat,
740 p_s_admission_process_type,
741 v_message_name) = FALSE THEN
742 p_message_name := v_message_name;
743 RETURN FALSE;
744 END IF;
745 COMMIT;
746 RETURN TRUE;
747 END;
748 EXCEPTION
749 WHEN OTHERS THEN
750 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
751 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_ins_aal_commit');
752 IGS_GE_MSG_STACK.ADD;
753 App_Exception.Raise_Exception;
754 END admp_ins_aal_commit;
755
756 PROCEDURE Admp_Ins_Aa_Hist(
757 p_person_id IN IGS_AD_APPL_ALL.person_id%TYPE ,
758 p_admission_appl_number IN NUMBER ,
759 p_new_appl_dt IN IGS_AD_APPL_ALL.appl_dt%TYPE ,
760 p_old_appl_dt IN IGS_AD_APPL_ALL.appl_dt%TYPE ,
761 p_new_acad_cal_type IN IGS_AD_APPL_ALL.acad_cal_type%TYPE ,
762 p_old_acad_cal_type IN IGS_AD_APPL_ALL.acad_cal_type%TYPE ,
763 p_new_acad_ci_sequence_number IN NUMBER ,
764 p_old_acad_ci_sequence_number IN NUMBER ,
765 p_new_adm_cal_type IN IGS_AD_APPL_ALL.adm_cal_type%TYPE ,
766 p_old_adm_cal_type IN IGS_AD_APPL_ALL.adm_cal_type%TYPE ,
767 p_new_adm_ci_sequence_number IN NUMBER ,
768 p_old_adm_ci_sequence_number IN NUMBER ,
769 p_new_admission_cat IN IGS_AD_APPL_ALL.admission_cat%TYPE ,
770 p_old_admission_cat IN IGS_AD_APPL_ALL.admission_cat%TYPE ,
771 p_new_s_admission_process_type IN VARCHAR2 ,
772 p_old_s_admission_process_type IN VARCHAR2 ,
773 p_new_adm_appl_status IN IGS_AD_APPL_ALL.adm_appl_status%TYPE ,
774 p_old_adm_appl_status IN IGS_AD_APPL_ALL.adm_appl_status%TYPE ,
775 p_new_adm_fee_status IN IGS_AD_APPL_ALL.adm_fee_status%TYPE ,
776 p_old_adm_fee_status IN IGS_AD_APPL_ALL.adm_fee_status%TYPE ,
777 p_new_tac_appl_ind IN IGS_AD_APPL_ALL.tac_appl_ind%TYPE ,
778 p_old_tac_appl_ind IN IGS_AD_APPL_ALL.tac_appl_ind%TYPE ,
779 p_new_update_who IN IGS_AD_APPL_ALL.last_updated_by%TYPE ,
780 p_old_update_who IN IGS_AD_APPL_ALL.last_updated_by%TYPE ,
781 p_new_update_on IN IGS_AD_APPL_ALL.last_update_date%TYPE ,
782 p_old_update_on IN IGS_AD_APPL_ALL.last_update_date%TYPE )
783 IS
784 gv_other_detail VARCHAR2(255);
785 BEGIN -- admp_ins_aa_hist
786 -- Create a history for an IGS_AD_APPL record
787 DECLARE
788 v_aah_rec IGS_AD_APPL_HIST%ROWTYPE;
789 v_create_history BOOLEAN := FALSE;
790 lv_rowid VARCHAR2(25);
791 l_org_id NUMBER(15);
792
793 BEGIN
794 -- Check if any of the non-primary key fields have been changed
795 -- and set the flag v_create_history to indicate so.
796 IF p_new_appl_dt <> p_old_appl_dt THEN
797 v_aah_rec.appl_dt := p_old_appl_dt;
798 v_create_history := TRUE;
799 END IF;
800 IF p_new_acad_cal_type <> p_old_acad_cal_type THEN
801 v_aah_rec.acad_cal_type := p_old_acad_cal_type;
802 v_create_history := TRUE;
803 END IF;
804 IF p_new_acad_ci_sequence_number <> p_old_acad_ci_sequence_number THEN
805 v_aah_rec.acad_ci_sequence_number := p_old_acad_ci_sequence_number;
806 v_create_history := TRUE;
807 END IF;
808 IF p_new_adm_cal_type <> p_old_adm_cal_type THEN
809 v_aah_rec.adm_cal_type := p_old_adm_cal_type;
810 v_create_history := TRUE;
811 END IF;
812 IF p_new_adm_ci_sequence_number <> p_old_adm_ci_sequence_number THEN
813 v_aah_rec.adm_ci_sequence_number := p_old_adm_ci_sequence_number;
814 v_create_history := TRUE;
815 END IF;
816 IF p_new_admission_cat <> p_old_admission_cat THEN
817 v_aah_rec.admission_cat := p_old_admission_cat;
818 v_create_history := TRUE;
819 END IF;
820 IF p_new_s_admission_process_type <> p_old_s_admission_process_type THEN
821 v_aah_rec.s_admission_process_type := p_old_s_admission_process_type;
822 v_create_history := TRUE;
823 END IF;
824 IF p_new_adm_appl_status <> p_old_adm_appl_status THEN
825 v_aah_rec.adm_appl_status := p_old_adm_appl_status;
826 v_create_history := TRUE;
827 END IF;
828 IF p_new_adm_fee_status <> p_old_adm_fee_status THEN
829 v_aah_rec.adm_fee_status := p_old_adm_fee_status;
830 v_create_history := TRUE;
831 END IF;
832 IF p_new_tac_appl_ind <> p_old_tac_appl_ind THEN
833 v_aah_rec.tac_appl_ind := p_old_tac_appl_ind;
834 v_create_history := TRUE;
835 END IF;
836 -- Create a history record if a column has changed value
837 IF v_create_history = TRUE THEN
838 v_aah_rec.person_id := p_person_id;
839 v_aah_rec.admission_appl_number := p_admission_appl_number;
840 v_aah_rec.hist_start_dt := p_old_update_on;
841 v_aah_rec.hist_end_dt := p_new_update_on;
842 v_aah_rec.hist_who := p_old_update_who;
843 l_org_id := igs_ge_gen_003.get_org_id;
844
845 IGS_AD_APPL_Hist_Pkg.Insert_Row (
846 X_Mode => 'R',
847 X_RowId => lv_rowid,
848 X_Person_Id => v_aah_rec.person_id,
849 X_Admission_Appl_Number => v_aah_rec.admission_appl_number,
850 X_Hist_Start_Dt => v_aah_rec.hist_start_dt,
851 X_Hist_End_Dt => v_aah_rec.hist_end_dt,
852 X_Hist_Who => v_aah_rec.hist_who,
853 X_Appl_Dt => v_aah_rec.appl_dt,
854 X_Acad_Cal_Type => v_aah_rec.acad_cal_type,
855 X_Acad_Ci_Sequence_Number => v_aah_rec.acad_ci_sequence_number,
856 X_Adm_Cal_Type => v_aah_rec.acad_cal_type,
857 X_Adm_Ci_Sequence_Number => v_aah_rec.adm_ci_sequence_number,
858 X_Admission_Cat => v_aah_rec.admission_cat,
859 X_S_Admission_Process_Type => v_aah_rec.s_admission_process_type,
860 X_Adm_Appl_Status => v_aah_rec.adm_appl_status,
861 X_Adm_Fee_Status => v_aah_rec.adm_fee_status,
862 X_Tac_Appl_Ind => v_aah_rec.tac_appl_ind,
863 X_Org_Id => l_org_id
864 );
865
866
867 END IF;
868 END;
869 EXCEPTION
870 WHEN OTHERS THEN
871 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
872 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_ins_aa_hist');
873 IGS_GE_MSG_STACK.ADD;
874 App_Exception.Raise_Exception;
875 END admp_ins_aa_hist;
876
877 PROCEDURE Admp_Ins_Acaiu_Hist(
878 p_person_id IN NUMBER ,
879 p_admission_appl_number IN NUMBER ,
880 p_nominated_course_cd IN VARCHAR2 ,
881 p_acai_sequence_number IN NUMBER ,
882 p_unit_cd IN VARCHAR2 ,
883 p_adm_ps_appl_inst_unit_id IN NUMBER ,
884 p_new_uv_version_number IN NUMBER ,
885 p_old_uv_version_number IN NUMBER ,
886 p_new_cal_type IN VARCHAR2 ,
887 p_old_cal_type IN VARCHAR2 ,
888 p_new_ci_sequence_number IN NUMBER ,
889 p_old_ci_sequence_number IN NUMBER ,
890 p_new_location_cd IN VARCHAR2 ,
891 p_old_location_cd IN VARCHAR2 ,
892 p_new_unit_class IN VARCHAR2 ,
893 p_old_unit_class IN VARCHAR2 ,
894 p_new_unit_mode IN VARCHAR2 ,
895 p_old_unit_mode IN VARCHAR2 ,
896 p_new_adm_unit_outcome_status IN VARCHAR2 ,
897 p_old_adm_unit_outcome_status IN VARCHAR2 ,
898 p_new_ass_tracking_id IN NUMBER ,
899 p_old_ass_tracking_id IN NUMBER ,
900 p_new_rule_waived_dt IN DATE ,
901 p_old_rule_waived_dt IN DATE ,
902 p_new_rule_waived_person_id IN NUMBER ,
903 p_old_rule_waived_person_id IN NUMBER ,
904 p_new_sup_unit_cd IN VARCHAR2 ,
905 p_old_sup_unit_cd IN VARCHAR2 ,
906 p_new_sup_uv_version_number IN NUMBER ,
907 p_old_sup_uv_version_number IN NUMBER ,
908 p_new_update_who IN VARCHAR2 ,
909 p_old_update_who IN VARCHAR2 ,
910 p_new_update_on IN DATE ,
911 p_old_update_on IN DATE )
912 IS
913 gv_other_detail VARCHAR2(255);
914 BEGIN -- admp_ins_acaiu_hist
915 -- Routine to create a history for the IGS_AD_PS_APLINSTUNT table.
916 DECLARE
917 v_changed_flag BOOLEAN DEFAULT FALSE;
918 v_acaiuh_rec IGS_AD_PS_APINTUNTHS%ROWTYPE;
919 lv_rowid VARCHAR2(25);
920 l_org_id NUMBER(15);
921
922 BEGIN
923 -- Check if any of the old IGS_AD_PS_APLINSTUNT values are different
924 -- from the associated new IGS_AD_PS_APLINSTUNT values.
925
926 -- Unit code and and version number are non-updateable fields
927 /*
928 IF NVL(p_new_uv_version_number, -1) <> NVL(p_old_uv_version_number, -1) THEN
929 v_changed_flag := TRUE;
930 v_acaiuh_rec.uv_version_number := p_old_uv_version_number;
931 END IF;
932 */
933 IF NVL(p_new_cal_type, 'NULL') <> NVL(p_old_cal_type, 'NULL') THEN
934 v_changed_flag := TRUE;
935 v_acaiuh_rec.cal_type := p_old_cal_type;
936 END IF;
937 IF NVL(p_new_ci_sequence_number, -1) <> NVL(p_old_ci_sequence_number, -1) THEN
938 v_changed_flag := TRUE;
939 v_acaiuh_rec.ci_sequence_number := p_old_ci_sequence_number;
940 END IF;
941 IF NVL(p_new_location_cd, 'NULL') <> NVL(p_old_location_cd, 'NULL') THEN
942 v_changed_flag := TRUE;
943 v_acaiuh_rec.location_cd := p_old_location_cd;
944 END IF;
945 IF NVL(p_new_unit_class, 'NULL') <> NVL(p_old_unit_class, 'NULL') THEN
946 v_changed_flag := TRUE;
947 v_acaiuh_rec.unit_class := p_old_unit_class;
948 END IF;
949 IF NVL(p_new_unit_mode, 'NULL') <> NVL(p_old_unit_mode, 'NULL') THEN
950 v_changed_flag := TRUE;
951 v_acaiuh_rec.unit_mode := p_old_unit_mode;
952 END IF;
953 IF NVL(p_new_adm_unit_outcome_status, 'NULL') <>
954 NVL(p_old_adm_unit_outcome_status, 'NULL') THEN
955 v_changed_flag := TRUE;
956 v_acaiuh_rec.adm_unit_outcome_status := p_old_adm_unit_outcome_status;
957 END IF;
958 IF NVL(p_new_ass_tracking_id, -1 ) <> NVL(p_old_ass_tracking_id, -1) THEN
959 v_changed_flag := TRUE;
960 v_acaiuh_rec.ass_tracking_id := p_old_ass_tracking_id;
961 END IF;
962 IF p_new_rule_waived_dt <> p_old_rule_waived_dt OR
963 (p_new_rule_waived_dt IS NULL AND
964 p_old_rule_waived_dt IS NOT NULL) OR
965 (p_new_rule_waived_dt IS NOT NULL AND
966 p_old_rule_waived_dt IS NULL) THEN
967 v_changed_flag := TRUE;
968 v_acaiuh_rec.rule_waived_dt := p_old_rule_waived_dt;
969 END IF;
970 IF NVL(p_new_rule_waived_person_id, -1 ) <>
971 NVL(p_old_rule_waived_person_id, -1) THEN
972 v_changed_flag := TRUE;
973 v_acaiuh_rec.rule_waived_person_id := p_old_rule_waived_person_id;
974 END IF;
975 IF NVL(p_new_sup_unit_cd, 'NULL' ) <> NVL(p_old_sup_unit_cd, 'NULL') THEN
976 v_changed_flag := TRUE;
977 v_acaiuh_rec.sup_unit_cd := p_old_sup_unit_cd;
978 END IF;
979 IF NVL(p_new_sup_uv_version_number, -1 ) <>
980 NVL(p_old_sup_uv_version_number, -1) THEN
981 v_changed_flag := TRUE;
982 v_acaiuh_rec.sup_uv_version_number := p_old_sup_uv_version_number;
983 END IF;
984 IF v_changed_flag = TRUE THEN
985 -- insert into history table
986 -- set the mandatory columns
987 v_acaiuh_rec.person_id := p_person_id;
988 v_acaiuh_rec.admission_appl_number := p_admission_appl_number;
989 v_acaiuh_rec.nominated_course_cd := p_nominated_course_cd;
990 v_acaiuh_rec.acai_sequence_number := p_acai_sequence_number;
991 v_acaiuh_rec.unit_cd := p_unit_cd;
992 -- Unit code and and version number are non-updateable fields
993 v_acaiuh_rec.uv_version_number := p_old_uv_version_number;
994 v_acaiuh_rec.adm_ps_appl_inst_unit_id := p_adm_ps_appl_inst_unit_id;
995 v_acaiuh_rec.hist_start_dt := p_old_update_on;
996 v_acaiuh_rec.hist_end_dt := p_new_update_on;
997 v_acaiuh_rec.hist_who := p_old_update_who;
998 l_org_id := igs_ge_gen_003.get_org_id;
999
1000 IGS_AD_PS_APINTUNTHS_Pkg.Insert_Row (
1001 X_Mode => 'R',
1002 X_RowId => lv_rowid,
1003 X_Person_Id => v_acaiuh_rec.person_id,
1004 X_Admission_Appl_Number => v_acaiuh_rec.admission_appl_number,
1005 X_Nominated_Course_Cd => v_acaiuh_rec.nominated_course_cd,
1006 X_Acai_Sequence_Number => v_acaiuh_rec.acai_sequence_number,
1007 X_Unit_Cd => v_acaiuh_rec.unit_cd,
1008 X_Hist_Start_Dt => v_acaiuh_rec.hist_start_dt,
1009 X_Hist_End_Dt => v_acaiuh_rec.hist_end_dt,
1010 X_Hist_Who => v_acaiuh_rec.hist_who,
1011 X_Uv_Version_Number => v_acaiuh_rec.uv_version_number,
1012 X_Cal_Type => v_acaiuh_rec.cal_type,
1013 X_Ci_Sequence_Number => v_acaiuh_rec.ci_sequence_number,
1014 X_Location_Cd => v_acaiuh_rec.location_cd,
1015 X_Unit_Class => v_acaiuh_rec.unit_class,
1016 X_Unit_Mode => v_acaiuh_rec.unit_mode,
1017 X_Adm_Unit_Outcome_Status => v_acaiuh_rec.adm_unit_outcome_status,
1018 X_Ass_Tracking_Id => v_acaiuh_rec.ass_tracking_id,
1019 X_Rule_Waived_Dt => v_acaiuh_rec.rule_waived_dt,
1020 X_Rule_Waived_Person_Id => v_acaiuh_rec.rule_waived_person_id,
1021 X_Sup_Unit_Cd => v_acaiuh_rec.sup_unit_cd,
1022 X_Sup_Uv_Version_Number => v_acaiuh_rec.sup_uv_version_number,
1023 X_Org_Id => l_org_id,
1024 X_adm_ps_appl_inst_unit_id => v_acaiuh_rec.adm_ps_appl_inst_unit_id,
1025 X_adm_ps_appl_inst_unithist_id => v_acaiuh_rec.adm_ps_appl_inst_unit_hist_id
1026 );
1027
1028 END IF;
1029 END;
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1033 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_010.admp_ins_acaiu_hist');
1034 IGS_GE_MSG_STACK.ADD;
1035 App_Exception.Raise_Exception;
1036 END admp_ins_acaiu_hist;
1037
1038 END igs_ad_gen_010;