[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_UPD_INITIALISE
Source
1 PACKAGE BODY IGS_AD_UPD_INITIALISE AS
2 /* $Header: IGSAD16B.pls 120.6 2006/05/02 05:33:20 apadegal noship $ */
3
4 --
5 -- Update admission IGS_PS_COURSE application instance IGS_PS_UNIT initialisation.
6 PROCEDURE admp_upd_acaiu_init(
7 p_person_id IN NUMBER ,
8 p_admission_appl_number IN NUMBER ,
9 p_nominated_course_cd IN VARCHAR2 ,
10 p_acai_sequence_number IN NUMBER ,
11 p_acad_cal_type IN VARCHAR2 ,
12 p_acad_ci_sequence_number IN NUMBER ,
13 p_adm_cal_type IN VARCHAR2 ,
14 p_adm_ci_sequence_number IN NUMBER ,
15 p_s_admission_process_type IN VARCHAR2 ,
16 p_offered_ind IN VARCHAR2 DEFAULT 'N',
17 p_s_log_type IN VARCHAR2 ,
18 p_creation_dt IN DATE )
19 IS
20 BEGIN -- admp_upd_acaiu_init
21 -- This module updates IGS_AD_PS_APLINSTUNT when
22 -- IGS_AD_PS_APPL_INST are updated when
23 -- initialising an admission period as a result of reconsideration or deferment
24 -- This module is called from IGS_AD_GEN_012.ADMP_UPD_ACAI_RECON and IGS_AD_GEN_012.ADMP_UPD_ACAI_DEFER.
25 DECLARE
26 e_resource_busy EXCEPTION;
27 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
28 v_init_process VARCHAR2(125);
29 v_adm_unit_outcome_status VARCHAR2(125);
30 v_log_message_name Varchar2(30);
31 v_message_name VARCHAR2(30);
32 CURSOR c_acaiu_auos IS
33 SELECT acaiu.ROWID, acaiu.*
34 FROM IGS_AD_PS_APLINSTUNT acaiu,
35 IGS_AD_UNIT_OU_STAT auos
36 WHERE
37 acaiu.person_id = p_person_id AND
38 acaiu.admission_appl_number = p_admission_appl_number AND
39 acaiu.nominated_course_cd = p_nominated_course_cd AND
40 acaiu.acai_sequence_number = p_acai_sequence_number AND
41 acaiu.adm_unit_outcome_status = auos.adm_unit_outcome_status AND
42 auos.s_adm_outcome_status IN ('PENDING', 'OFFER')
43 FOR UPDATE OF person_id NOWAIT;
44 BEGIN
45 IF (p_s_log_type = 'ADM-RECON') THEN
46 v_init_process := 'reconsideration';
47 ELSE
48 -- p_s_log_type must be 'ADM-DEFER'
49 v_init_process := 'deferment';
50 END IF;
51 -- Get admission UNIT outcome status pending default
52 v_adm_unit_outcome_status := IGS_AD_GEN_009.ADMP_GET_SYS_AUOS(
53 'PENDING');
54 FOR v_acaiu_rec IN c_acaiu_auos LOOP
55 v_log_message_name := NULL;
56 -- Validate the UNIT offering option
57 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_uv(
58 v_acaiu_rec.unit_cd,
59 v_acaiu_rec.uv_version_number,
60 p_s_admission_process_type,
61 p_offered_ind,
62 v_message_name) = FALSE THEN
63 -- UNIT cannot remain, it is no longer valid
64 v_log_message_name := v_message_name;
65
66 IGS_AD_PS_APLINSTUNT_Pkg.Delete_Row (
67 v_acaiu_rec.RowId
68 );
69
70 ELSE
71 -- Validate that the UNIT offering options
72 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_opt(
73 v_acaiu_rec.unit_cd,
74 v_acaiu_rec.uv_version_number,
75 v_acaiu_rec.cal_type,
76 v_acaiu_rec.ci_sequence_number,
77 v_acaiu_rec.location_cd,
78 v_acaiu_rec.unit_class,
79 v_acaiu_rec.unit_mode,
80 p_adm_cal_type,
81 p_adm_ci_sequence_number,
82 p_acad_cal_type,
83 p_acad_ci_sequence_number,
84 p_offered_ind,
85 v_message_name) = FALSE THEN
86 -- UNIT options must be cleared
87 v_log_message_name := v_message_name;
88 -- Update this record (IGS_AD_PS_APLINSTUNT)
89
90 IGS_AD_PS_APLINSTUNT_Pkg.Update_Row (
91 X_Mode => 'R',
92 X_RowId => v_acaiu_rec.RowId,
93 X_Person_Id => v_acaiu_rec.Person_Id,
94 X_Admission_Appl_Number => v_acaiu_rec.Admission_Appl_Number,
95 X_Nominated_Course_Cd => v_acaiu_rec.Nominated_Course_Cd,
96 X_Acai_Sequence_Number => v_acaiu_rec.Acai_Sequence_Number,
97 X_Unit_Cd => v_acaiu_rec.Unit_Cd,
98 X_Uv_Version_Number => v_acaiu_rec.Uv_Version_Number,
99 X_Cal_Type => NULL,
100 X_Ci_Sequence_Number => NULL,
101 X_Location_Cd => NULL,
102 X_Unit_Class => NULL,
103 X_Unit_Mode => NULL,
104 X_Adm_Unit_Outcome_Status => v_adm_unit_outcome_status,
105 X_Ass_Tracking_Id => v_acaiu_rec.Ass_Tracking_Id,
106 X_Rule_Waived_Dt => v_acaiu_rec.Rule_Waived_Dt,
107 X_Rule_Waived_Person_Id => v_acaiu_rec.Rule_Waived_Person_Id,
108 X_Sup_Unit_Cd => v_acaiu_rec.Sup_Unit_Cd,
109 X_Sup_Uv_Version_Number => v_acaiu_rec.Sup_Uv_Version_Number,
110 X_adm_ps_appl_inst_unit_id => v_acaiu_rec.adm_ps_appl_inst_unit_id
111 );
112
113 END IF;
114 END IF;
115 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
116 p_s_log_type,
117 p_creation_dt,
118 (v_acaiu_rec.person_id ||','|| v_acaiu_rec.admission_appl_number ||','||
119 v_acaiu_rec.nominated_course_cd ||','|| v_acaiu_rec.acai_sequence_number ||
120 ','|| v_acaiu_rec.unit_cd),
121 v_log_message_name,
122 '');
123 END LOOP;
124 EXCEPTION
125 WHEN e_resource_busy THEN
126 Fnd_Message.Set_Name('IGS','IGS_GE_NO_ENOUGH_SYS_RESOURCE');
127 App_Exception.Raise_Exception;
128 WHEN OTHERS THEN
129 App_Exception.Raise_Exception;
130 END;
131 EXCEPTION
132 WHEN OTHERS THEN
133 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
134 Fnd_Message.Set_Token('NAME','IGS_AD_UPD_INITIALISE.admp_upd_acaiu_init');
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END admp_upd_acaiu_init;
138
139
140 FUNCTION perform_pre_enrol (
141 p_person_id IN igs_ad_ps_appl_inst. person_id%TYPE,
142 p_admission_appl_number IN igs_ad_ps_appl_inst. admission_appl_number%TYPE,
143 p_nominated_course_cd IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
144 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
145 p_confirm_ind IN VARCHAR2,
146 p_check_eligibility_ind IN VARCHAR2,
147 p_message_name OUT NOCOPY VARCHAR2
148 )
149 RETURN BOOLEAN IS
150 ----------------------------------------------------------------
151 --Created by : rghosh
152 --Date created: 07-Apr-2003
153 --
154 --Purpose: To run the pre-enrolment job from admissions.(bug#2860860-UCAS Conditional Offer build)
155 --
156 --
157 --Known limitations/enhancements and/or remarks:
158 --
159 --Change History:
160 --Who When What
161 --amuthu 10-JUN-2003 modified as per the UK Streaming and Repeat TD (bug 2829265)
162 --svanukur 17-oct-2003 modified the declaration of v_message_name as part of placements build 3052438
163 --ptandon 13-Feb-2004 Added Exception Handling section. Bug# 3360336.
164 ----------------------------------------------------------------
165 v_message_name VARCHAR2(2000) ;
166 v_warn_level VARCHAR2(10);
167
168 CURSOR c_pre_enrol (cp_person_id IN igs_ad_ps_appl_inst. person_id%TYPE,
169 cp_admission_appl_number IN igs_ad_ps_appl_inst. admission_appl_number%TYPE,
170 cp_nominated_course_cd IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
171 cp_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE) IS
172 SELECT a.enrolment_cat,b.acad_cal_type,b.acad_ci_sequence_number
173 FROM igs_ad_ps_appl_inst a, igs_ad_appl b
174 WHERE a.person_id = cp_person_id
175 AND a.admission_appl_number = cp_admission_appl_number
176 AND a.nominated_course_cd = cp_nominated_course_cd
177 AND a.sequence_number=cp_sequence_number
178 AND a.person_id = b.person_id
179 AND a.admission_appl_number = b.admission_appl_number;
180
181 l_pre_enrol_rec c_pre_enrol%ROWTYPE;
182 l_encoded_message VARCHAR2(2000);
183 l_app_short_name VARCHAR2(10);
184 l_message_name VARCHAR2(100);
185
186 CURSOR c_check_units (cp_person_id IGS_EN_SU_ATTEMPT_ALL.person_id%TYPE,
187 cp_course_cd IGS_EN_SU_ATTEMPT_ALL.course_cd%TYPE) IS
188 SELECT 'X'
189 FROM IGS_EN_SU_ATTEMPT_ALL
190 WHERE person_id = cp_person_id
191 AND course_cd = cp_course_cd;
192
193 l_check_units VARCHAR2(1);
194
195 l_units_indicator igs_ad_prcs_cat_step_all.s_admission_step_type%TYPE;
196
197 BEGIN
198
199 OPEN c_pre_enrol(p_person_id,p_admission_appl_number,p_nominated_course_cd,p_sequence_number);
200 FETCH c_pre_enrol INTO l_pre_enrol_rec;
201 CLOSE c_pre_enrol;
202
203 OPEN c_check_units(p_person_id,p_nominated_course_cd);
204 FETCH c_check_units INTO l_check_units;
205 CLOSE c_check_units;
206
207 IF l_check_units IS NOT NULL THEN
208 l_units_indicator := 'N';
209 ELSE
210 l_units_indicator := igs_ad_gen_003.get_core_or_optional_unit (p_person_id, p_admission_appl_number);
211 END IF;
212
213 IF igs_en_gen_010.enrp_ins_snew_prenrl (
214 p_person_id,
215 p_nominated_course_cd,
216 l_pre_enrol_rec.enrolment_cat,
217 l_pre_enrol_rec.acad_cal_type,
218 l_pre_enrol_rec.acad_ci_sequence_number,
219 l_units_indicator,
220 p_confirm_ind,
221 NULL, -- Input: Override Enrolment Form Due Date.
222 NULL, -- Input: Override Enrolment Package Production Date.
223 p_check_eligibility_ind,
224 p_admission_appl_number,
225 p_nominated_course_cd,
226 p_sequence_number,
227 NULL, -- Input: 1 - Unit Code
228 NULL, -- Input: 1 - Unit Teaching Calendar
229 NULL, -- Input: 1 - Unit Location Code
230 NULL, -- Input: 1 - Unit Class
231 NULL, -- Input: 2 - Unit Code
232 NULL, -- Input: 2 - Unit Teaching Calendar
233 NULL, -- Input: 2 - Unit Location Code
234 NULL, -- Input: 2 - Unit Class
235 NULL, -- Input: 3 - Unit Code
236 NULL, -- Input: 3 - Unit Teaching Calendar
237 NULL, -- Input: 3 - Unit Location Code
238 NULL, -- Input: 3 - Unit Class
239 NULL, -- Input: 4 - Unit Code
240 NULL, -- Input: 4 - Unit Teaching Calendar
241 NULL, -- Input: 4 - Unit Location Code
242 NULL, -- Input: 4 - Unit Class
243 NULL, -- Input: 5 - Unit Code
244 NULL, -- Input: 5 - Unit Teaching Calendar
245 NULL, -- Input: 5 - Unit Location Code
246 NULL, -- Input: 5 - Unit Class
247 NULL, -- Input: 6 - Unit Code
248 NULL, -- Input: 6 - Unit Teaching Calendar
249 NULL, -- Input: 6 - Unit Location Code
250 NULL, -- Input: 6 - Unit Class
251 NULL, -- Input: 7 - Unit Code
252 NULL, -- Input: 7 - Unit Teaching Calendar
253 NULL, -- Input: 7 - Unit Location Code
254 NULL, -- Input: 7 - Unit Class
255 NULL, -- Input: 8 - Unit Code
256 NULL, -- Input: 8 - Unit Teaching Calendar
257 NULL, -- Input: 8 - Unit Location Code
258 NULL, -- Input: 8 - Unit Class
259 NULL, -- Input: Batch Log Creation Date
260 v_warn_level, -- Output: Warning Level.
261 v_message_name,
262 NULL, -- Input: 9 - Unit Code
263 NULL, -- Input: 9 - Unit Teaching Calendar
264 NULL, -- Input: 9 - Unit Location Code
265 NULL, -- Input: 9 - Unit Class
266 NULL, -- Input: 10 - Unit Code
267 NULL, -- Input: 10 - Unit Teaching Calendar
268 NULL, -- Input: 10 - Unit Location Code
269 NULL, -- Input: 10 - Unit Class
270 NULL, -- Input: 11 - Unit Code
271 NULL, -- Input: 11 - Unit Teaching Calendar
272 NULL, -- Input: 11 - Unit Location Code
273 NULL, -- Input: 11 - Unit Class
274 NULL, -- Input: 12 - Unit Code
275 NULL, -- Input: 12 - Unit Teaching Calendar
276 NULL, -- Input: 12 - Unit Location Code
277 NULL, -- Input: 12 - Unit Class
278 NULL, -- Input: Unit Set Cd1
279 NULL, -- Input: Unit Set Cd2
280 NULL, -- Input: p_progress_stat
281 NULL, -- Input: Enrollment Method
282 NULL, -- Input: Load Cal Type
283 NULL -- Input: Load Seq Number
284 ) =TRUE THEN
285 RETURN TRUE;
286 END IF;
287 p_message_name := v_message_name;
288 RETURN FALSE;
289
290 EXCEPTION
291 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
292 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
293 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_ad_upd_initialise.perform_pre_enrol.APP_EXP','Application Exception raised with code '||SQLCODE||' and error '||SQLERRM);
294 END IF;
295 l_encoded_message := FND_MESSAGE.GET_ENCODED;
296 IF l_encoded_message IS NOT NULL THEN
297 FND_MESSAGE.SET_ENCODED(l_encoded_message);
298 FND_MESSAGE.PARSE_ENCODED(l_encoded_message,l_app_short_name,l_message_name);
299 p_message_name := l_message_name;
300 ELSE
301 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNEXPECTED_ERR');
302 p_message_name := 'IGS_GE_UNEXPECTED_ERR';
303 END IF;
304 RETURN FALSE;
305 WHEN OTHERS THEN
306 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
307 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_ad_upd_initialise.perform_pre_enrol.UNH_EXP','Unhandled Exception raised with code '||SQLCODE||' and error '||SQLERRM);
308 END IF;
309 l_encoded_message := FND_MESSAGE.GET_ENCODED;
310 IF l_encoded_message IS NOT NULL THEN
311 FND_MESSAGE.SET_ENCODED(l_encoded_message);
312 FND_MESSAGE.PARSE_ENCODED(l_encoded_message,l_app_short_name,l_message_name);
313 p_message_name := l_message_name;
314 ELSE
315 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNEXPECTED_ERR');
316 p_message_name := 'IGS_GE_UNEXPECTED_ERR';
317 END IF;
318 RETURN FALSE;
319 END perform_pre_enrol;
320
321 PROCEDURE perform_pre_enrol (
322 p_person_id IN igs_ad_ps_appl_inst. person_id%TYPE,
323 p_admission_appl_number IN igs_ad_ps_appl_inst. admission_appl_number%TYPE,
324 p_nominated_course_cd IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
325 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
326 p_confirm_ind IN VARCHAR2,
327 p_check_eligibility_ind IN VARCHAR2
328 )
329 IS
330 ----------------------------------------------------------------
331 --Created by : tray
332 --Date created: 01-dec-2003
333 --
334 --Purpose: To run the pre-enrolment job from admissions, from Self Service
335 -- This internally calls the function defined above.
336 -- proc created since fucntion boolean return value raises error in jdbc call.
337 --Known limitations/enhancements and/or remarks:
338 --
339 --Change History:
340 --Who When What
341 ----------------------------------------------------------------
342 l_message_name VARCHAR2(2000);
343
344 BEGIN
345
346 IF igs_ad_upd_initialise.perform_pre_enrol(
347 P_PERSON_ID => p_person_id ,
348 P_ADMISSION_APPL_NUMBER => p_admission_appl_number,
349 P_NOMINATED_COURSE_CD => p_nominated_course_cd,
350 P_SEQUENCE_NUMBER => p_sequence_number,
351 P_CONFIRM_IND => p_confirm_ind,
352 P_CHECK_ELIGIBILITY_IND => p_check_eligibility_ind,
353 P_MESSAGE_NAME => l_message_name) = FALSE THEN
354
355 FND_MESSAGE.SET_NAME('IGS',l_message_name);
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358
359 END IF;
360 END perform_pre_enrol;
361
362
363 FUNCTION get_msg_name_mapping (
364 p_msg_name IN VARCHAR2)
365 RETURN VARCHAR2 IS
366
367 BEGIN
368
369 IF p_msg_name = 'IGS_EN_UNABLE_TO_FND_ADM' THEN
370 RETURN 'E457';
371 ELSIF p_msg_name = 'IGS_EN_NOT_DETERMINE_CONDID' THEN
372 RETURN 'E460';
373 ELSIF p_msg_name = 'IGS_EN_CANDID_KEY_DETAIL' THEN
374 RETURN 'E464';
375 ELSIF p_msg_name = 'IGS_EN_STUD_PRG_REC_LOCKED' THEN
376 RETURN 'E459';
377 ELSIF p_msg_name = 'IGS_EN_DISCONT_NOTLIFT' THEN
378 RETURN 'E463';
379 ELSIF p_msg_name = 'IGS_EN_UNABLE_DETM_ENR_PERIOD' THEN
380 RETURN 'E454';
381 ELSIF p_msg_name = 'IGS_EN_UNABLE_DETM_ENRCAT' THEN
382 RETURN 'E455';
383 ELSIF p_msg_name = 'IGS_EN_STUD_PRG_LAPSE_USER' THEN
384 RETURN 'E458';
385 ELSIF p_msg_name = 'IGS_EN_UNABLE_LOCATE_UOO_MATC' THEN
386 RETURN 'E456';
387 ELSIF p_msg_name = 'IGS_EN_FEE_CONTRACT_NOT_CREAT' THEN
388 RETURN 'E461';
389 ELSIF p_msg_name ='IGS_EN_FEE_CONTRACT_NOTCREATE' THEN
390 RETURN 'E462';
391 ELSIF p_msg_name = 'IGS_AD_ADM_OUCOME_ST_CLOSED' THEN
392 RETURN 'E529';
393 ELSIF p_msg_name = 'IGS_AD_OFRST_VALUE_PENDING' THEN
394 RETURN 'E530';
395 ELSIF p_msg_name = 'IGS_AD_CONDOFR_NOT_MADE' THEN
396 RETURN 'E531';
397 ELSIF p_msg_name = 'IGS_AD_OUTCOME_STATUS_CHG' THEN
398 RETURN 'E532';
399 ELSIF p_msg_name = 'IGS_AD_OUTCOME_CANNOT_SETTO' THEN
400 RETURN 'E533';
401 ELSIF p_msg_name = 'IGS_AD_OUTCOME_FUTURE_TERM' THEN
402 RETURN 'E534';
403 ELSIF p_msg_name = 'IGS_AD_APPLFEES_OUTSTANDING' THEN
404 RETURN 'E535';
405 ELSIF p_msg_name = 'IGS_AD_INVALID_COND_OFFER' THEN
406 RETURN 'E536';
407 ELSIF p_msg_name = 'IGS_AD_ADMAPL_FEES_OUTSTANDIN' THEN
408 RETURN 'E537';
409 ELSIF p_msg_name = 'IGS_AD_NOTBE_PENDING_OFR_MADE' THEN
410 RETURN 'E538';
411 ELSIF p_msg_name = 'IGS_AD_NOTBE_NOTQUALIF_OFRMAD' THEN
412 RETURN 'E539';
413 ELSIF p_msg_name = 'IGS_AD_NOTBE_PENDNG_OFR_MADE' THEN
414 RETURN 'E540';
415 ELSIF p_msg_name = 'IGS_AD_NOTBE_INCOMPL_OFR_MADE' THEN
416 RETURN 'E541';
417 ELSIF p_msg_name = 'IGS_AD_ADMDOC_NOTBE_IMCOMPL' THEN
418 RETURN 'E541';
419 ELSIF p_msg_name = 'IGS_AD_NOTBE_INCOMP_OFR_MADE' THEN
420 RETURN 'E541';
421 ELSIF p_msg_name = 'IGS_AD_LATE_ADMFEE_NOTPENDING'THEN
422 RETURN 'E542';
423 ELSIF p_msg_name = 'IGS_AD_OFFER_CANNOT_MADE' THEN
424 RETURN 'E543';
425 ELSIF p_msg_name = 'IGS_AD_LATEFEE_CANNOT_ASSESS' THEN
426 RETURN 'E544';
427 ELSIF p_msg_name = 'IGS_AD_FEE_CANNOT_BE_OUTSTAND' THEN
428 RETURN 'E545';
429 ELSIF p_msg_name = 'IGS_AD_OFRST_NOTPENDING' THEN
430 RETURN 'E546';
431 ELSIF p_msg_name = 'IGS_AD_OFRST_NOTACCEPTED' THEN
432 RETURN 'E547';
433 ELSIF p_msg_name = 'IGS_AD_OFRST_NOTCHANGED' THEN
434 RETURN 'E548';
435 ELSIF p_msg_name = 'IGS_AD_OFR_RESP_NOTLAPSED' THEN
436 RETURN 'E549';
437 ELSIF p_msg_name = 'IGS_GE_UNHANDLED_EXP' THEN
438 RETURN 'E518';
439 ELSIF p_msg_name = 'IGS_GE_UNHANDLED_EXCEPTION' THEN
440 RETURN 'E518';
441 ELSIF p_msg_name = 'IGS_AD_ADMDOC_STATUS_CLOSED' THEN
442 RETURN 'E507';
443 ELSIF p_msg_name = 'IGS_AD_ADMDOC_STATUS' THEN
444 RETURN 'E507';
445 ELSIF p_msg_name = 'IGS_AD_ADM_DOC_STATUS' THEN
446 RETURN 'E507';
447 ELSIF p_msg_name = 'IGS_AD_NOTBE_PENDNG_OFR_MADE' THEN
448 RETURN 'E507';
449 ELSIF p_msg_name = 'IGS_AD_NOTBE_INCOMPL_OFR_MADE' THEN
450 RETURN 'E507';
451 ELSIF p_msg_name = 'IGS_AD_ADMDOC_NOTBE_IMCOMPL' THEN
452 RETURN 'E507';
453 ELSIF p_msg_name = 'IGS_AD_NOTBE_INCOMP_OFR_MADE' THEN
454 RETURN 'E507';
455 ELSIF p_msg_name = 'IGS_AD_ADM_ENTRY_CLS_ST_CLOSE' THEN
456 RETURN 'E508';
457 ELSIF p_msg_name = 'IGS_AD_ADMENTRY_QUALIFY_ST' THEN
458 RETURN 'E508';
459 ELSIF p_msg_name = 'IGS_AD_ADM_ENTRY_QUALIFYST' THEN
460 RETURN 'E508';
461 ELSIF p_msg_name = 'IGS_AD_NOTBE_PENDING_OFR_MADE' THEN
462 RETURN 'E508';
463 ELSIF p_msg_name = 'IGS_AD_NOTBE_NOTQUALIF_OFRMAD' THEN
464 RETURN 'E508';
465 ELSIF p_msg_name = 'IGS_AD_OFRST_NOTPENDING' THEN
466 RETURN 'E511';
467 ELSIF p_msg_name = 'IGS_AD_OFRST_NOTACCEPTED' THEN
468 RETURN 'E511';
469 ELSIF p_msg_name = 'IGS_AD_OFRST_NOTCHANGED' THEN
470 RETURN 'E511';
471 ELSIF p_msg_name = 'IGS_AD_OFR_RESP_NOTLAPSED' THEN
472 RETURN 'E511';
473 ELSIF p_msg_name = 'IGS_AD_OFR_RESP_NOTAPPLICABLE' THEN
474 RETURN 'E511';
475 ELSIF p_msg_name = 'IGS_AD_MULTIPLE_OFRS_NOTALLOW' THEN
476 RETURN 'E511';
477 ELSIF p_msg_name = 'IGS_AD_MULTIPLE_OFFER_LIMIT' THEN
478 RETURN 'E511';
479 ELSIF p_msg_name = 'IGS_AD_PRSN_CANNOTOFR_SAMEPRG' THEN
480 RETURN 'E511';
481 ELSIF p_msg_name = 'IGS_PS_LOC_CODE_CLOSED' THEN
482 RETURN 'E589';
483 ELSIF p_msg_name = 'IGS_PS_ATTEND_MODE_CLOSED' THEN
484 RETURN 'E586';
485 ELSIF p_msg_name = 'IGS_PS_ATTEND_TYPE_CLOSED' THEN
486 RETURN 'E587';
487 ELSIF p_msg_name = 'IGS_AD_OFR_RESP_NOTAPPLICABLE' THEN
488 RETURN 'E550';
489 ELSE
490 RETURN NULL;
491 END IF;
492
493 END get_msg_name_mapping;
494
495 -- procedure to update the person statistics (moved the code from IGSAI18 )
496 PROCEDURE update_per_stats (
497 p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
498 p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE DEFAULT NULL,
499 p_acptd_or_reopnd_ind IN VARCHAR2 DEFAULT NULL
500 )
501 IS
502
503 --Query to check if the current admission calendar is associated with exactly 1 load calendar
504 CURSOR check_adm_load_rel
505 IS
506 SELECT count(*)
507 FROM
508 igs_ca_inst_rel car , -- calendar relations table
509 igs_ca_inst_all ca , -- calendar instances table
510 igs_ca_type cat, -- calendar types table
511 igs_ad_appl_all apl
512 WHERE
513 apl.person_id = p_person_id -- current person id
514 AND apl.admission_appl_number = p_admission_appl_number -- current adm appl number
515 AND car.sup_cal_type = apl.adm_cal_type
516 AND car.sup_ci_sequence_number = apl.adm_ci_sequence_number
517 AND ca.cal_type = car.sub_cal_type
518 AND ca.sequence_number = car.sub_ci_sequence_number
519 AND ca.cal_type = cat.cal_type
520 AND cat.s_cal_cat = 'LOAD' ;
521
522 l_adm_load_cnt NUMBER default 0;
523
524 --Cursor to fetch load calendar associated with tthe
525 -- given admission calendar
526 CURSOR load_cal_cur (cp_adm_cal_type igs_ca_inst.cal_type%TYPE, cp_adm_ci_sequence_number igs_ca_inst.sequence_number%TYPE )
527 IS
528 SELECT ca.cal_type load_cal_type ,
529 ca.sequence_number load_cal_seq_num
530 FROM
531 igs_ca_inst_rel car , -- calendar relations table
532 igs_ca_inst_all ca , -- calendar instances table
533 igs_ca_type cat -- calendar types table
534 WHERE
535 ca.cal_type = car.sub_cal_type
536 AND ca.sequence_number = car.sub_ci_sequence_number
537 AND ca.cal_type = cat.cal_type
538 AND cat.s_cal_cat = 'LOAD'
539 AND car.sup_cal_type = cp_adm_cal_type
540 AND car.sup_ci_sequence_number = cp_adm_ci_sequence_number;
541
542 CURSOR adm_cal_cur -- query to fetch all admission calendars in which the applicant accepted offer
543 IS
544 SELECT apl.adm_cal_type,
545 apl.adm_ci_sequence_number ,
546 ca.start_dt
547 FROM igs_ad_appl_all apl ,
548 igs_ad_ps_appl_inst_all inst,
549 igs_ca_inst_all ca
550 WHERE apl.person_id = P_PERSON_ID
551 AND apl.person_id = inst.person_id
552 AND apl.admission_appl_number = inst.admission_appl_number
553 AND igs_ad_gen_008.admp_get_saors(inst.adm_offer_resp_status) = 'ACCEPTED'
554 AND ca.cal_type =apl.adm_cal_type
555 AND ca.sequence_number = apl.adm_ci_sequence_number
556 ORDER BY ca.start_dt asc,inst.actual_response_dt asc;
557
558 --curor to check if User Hook package is in valid state.
559 CURSOR chk_uhook_status_cur IS
560 SELECT
561 status
562 FROM
563 user_objects
564 WHERE
565 object_name = 'IGS_AD_UHK_PSTATS_PKG' AND
566 object_type = 'PACKAGE BODY';
567
568 CURSOR c_pe_stat IS
569 SELECT psv.*
570 FROM igs_pe_stat_v psv
571 WHERE psv.person_id = p_person_id;
572
573 l_user_hook_status VARCHAR2(20);
574
575
576 l_init_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL;
577 l_init_cal_ci_seq_num igs_ca_inst.sequence_number%TYPE DEFAULT NULL;
578 l_recent_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL;
579 l_recent_cal_ci_seq_num igs_ca_inst.sequence_number%TYPE DEFAULT NULL;
580 l_adm_pe_stats_der_profile VARCHAR2(200) := FND_PROFILE.VALUE('IGS_AD_PER_STATS_DER_TYPE');
581 cv_pe_stat c_pe_stat%ROWTYPE;
582 l_most_recent_profile VARCHAR2(200) := FND_PROFILE.VALUE('IGS_PE_RECENT_TERM');
583 l_catalog_profile VARCHAR2(200) := FND_PROFILE.VALUE('IGS_PE_CATALOG');
584
585
586 v_party_last_update_date hz_person_profiles.last_update_date%TYPE;
587 lv_perosn_profile_id hz_person_profiles.person_profile_id%TYPE;
588 v_return_status VARCHAR2(5);
589 v_msg_count NUMBER;
590 v_msg_data VARCHAR2(2000);
591 l_acpt_appl_exists BOOLEAN := FALSE;
592 l_load_cal_exists BOOLEAN := FALSE;
593
594 BEGIN
595
596
597 OPEN c_pe_stat;
598 FETCH c_pe_stat INTO cv_pe_stat;
599 CLOSE c_pe_stat;
600
601 IF NVL(l_adm_pe_stats_der_profile,'OFF')='OFF' -- profile set to OFF
602 THEN
603 RETURN;
604 ELSIF NVL(l_adm_pe_stats_der_profile,'OFF') ='USER_HOOK' -- profile set to User hook
605 THEN
606 OPEN chk_uhook_status_cur;
607 FETCH chk_uhook_status_cur INTO l_user_hook_status;
608 CLOSE chk_uhook_status_cur;
609
610 -- If the status is INVALID then raise appropriate message
611 -- If the status is VALID then call the user hook procedure
612 --
613
614 IF l_user_hook_status = 'INVALID' THEN
615 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UH_INVALID');
616 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_UHK_PSTATS_PKG');
617 igs_ge_msg_stack.add;
618 APP_EXCEPTION.RAISE_EXCEPTION;
619 ELSE
620 IGS_AD_UHK_PSTATS_PKG.Derive_Person_Stats(p_person_id,
621 l_init_cal_type,
622 l_init_cal_ci_seq_num,
623 l_recent_cal_type,
624 l_recent_cal_ci_seq_num);
625 END IF;
626 ELSIF NVL(l_adm_pe_stats_der_profile,'OFF') ='SYSTEM_DERIVE' -- profile set to System derive
627 THEN
628
629 IF p_admission_appl_number IS NOT NULL -- when procedure is invoked from TBH
630 AND NVL(p_acptd_or_reopnd_ind,'X') ='A' -- and only when offer is accepted
631 THEN
632
633 OPEN check_adm_load_rel;
634 FETCH check_adm_load_rel INTO l_adm_load_cnt;
635 CLOSE check_adm_load_rel;
636
637 IF l_adm_load_cnt <> 1
638 THEN
639
640 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PE_STATS_WARN');
641 IGS_GE_MSG_STACK.ADD;
642 app_exception.raise_exception;
643
644 END IF;
645
646 END IF;
647
648
649 FOR adm_cal_cur_rec IN adm_cal_cur
650 LOOP
651 l_acpt_appl_exists := TRUE;
652 FOR load_cal_cur_rec IN load_cal_cur(adm_cal_cur_rec.adm_cal_type,adm_cal_cur_rec.adm_ci_sequence_number)
653 LOOP
654 l_load_cal_exists := TRUE;
655 IF l_init_cal_type IS NULL AND l_init_cal_ci_seq_num IS NULL
656 THEN
657
658 l_init_cal_type := load_cal_cur_rec.load_cal_type;
659 l_init_cal_ci_seq_num := load_cal_cur_rec.load_cal_seq_num;
660 END IF;
661 l_recent_cal_type := load_cal_cur_rec.load_cal_type;
662 l_recent_cal_ci_seq_num := load_cal_cur_rec.load_cal_seq_num;
663 END LOOP;
664
665 END LOOP;
666
667
668 IF l_acpt_appl_exists AND (NOT l_load_cal_exists) -- accepted applicatin exists but not even one admission calendar has got subordinate load calendar
669 THEN
670 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NO_LOAD_CAL');
671 IGS_GE_MSG_STACK.ADD;
672 app_exception.raise_exception;
673
674 END IF;
675
676 END IF;
677
678 cv_pe_stat.init_cal_type := l_init_cal_type;
679 cv_pe_stat.init_sequence_number := l_init_cal_ci_seq_num;
680
681 IF l_catalog_profile = 'INITIAL_ADM_TERM'
682 THEN
683 cv_pe_stat.catalog_cal_type := l_init_cal_type;
684 cv_pe_stat.catalog_sequence_number := l_init_cal_ci_seq_num;
685 END IF;
686
687 -- If the IGS:Person Most Recent Admittance Term is set to
688 -- Accepted Application Offer Only, then set the MRT
689 IF l_most_recent_profile = 'ACCPT_OFFER_ONLY'
690 THEN
691
692 cv_pe_stat.recent_cal_type := l_recent_cal_type;
693 cv_pe_stat.recent_sequence_number := l_recent_cal_ci_seq_num;
694
695 IF l_catalog_profile = 'MR_ADM_TERM'
696 THEN
697
698 cv_pe_stat.catalog_cal_type := l_recent_cal_type;
699 cv_pe_stat.catalog_sequence_number := l_recent_cal_ci_seq_num;
700 END IF;
701 END IF;
702
703
704 IF cv_pe_stat.PERSON_ID IS NOT NULL THEN
705 -- Call the IGS_PE_STAT_PKG.UPDATE_ROW
706 BEGIN
707
708
709 igs_pe_stat_pkg.update_row(
710 x_action => 'UPDATE',
711 x_rowid => cv_pe_stat.row_id,
712 x_person_id => cv_pe_stat.person_id,
713 x_ethnic_origin_id => cv_pe_stat.ethnic_origin_id,
714 x_marital_status => cv_pe_stat.marital_status,
715 x_marital_stat_effect_dt => cv_pe_stat.marital_status_effective_date,
716 x_ann_family_income => cv_pe_stat.ann_family_income,
717 x_number_in_family => cv_pe_stat.number_in_family,
718 x_content_source_type => cv_pe_stat.content_source_type,
719 x_internal_flag => cv_pe_stat.internal_flag,
720 x_person_number => cv_pe_stat.person_number,
721 x_effective_start_date => cv_pe_stat.effective_start_date,
722 x_effective_end_date => cv_pe_stat.effective_end_date,
723 x_ethnic_origin => cv_pe_stat.ethnic_origin,
724 x_religion => cv_pe_stat.religion,
725 x_next_to_kin => cv_pe_stat.next_to_kin,
726 x_next_to_kin_meaning => cv_pe_stat.next_to_kin_meaning,
727 x_place_of_birth => cv_pe_stat.place_of_birth,
728 x_socio_eco_status => cv_pe_stat.socio_eco_status,
729 x_socio_eco_status_desc => cv_pe_stat.socio_eco_status_desc,
730 x_further_education => cv_pe_stat.further_education,
731 x_further_education_desc => cv_pe_stat.further_education_desc,
732 x_in_state_tuition => cv_pe_stat.in_state_tuition,
733 x_tuition_st_date => cv_pe_stat.tuition_st_date,
734 x_tuition_end_date => cv_pe_stat.tuition_end_date,
735 x_person_initials => cv_pe_stat.person_initials,
736 x_primary_contact_id => cv_pe_stat.primary_contact_id,
737 x_personal_income => cv_pe_stat.personal_income,
738 x_head_of_household_flag => cv_pe_stat.head_of_household_flag,
739 x_content_source_number => cv_pe_stat.content_source_number,
740 x_hz_parties_ovn => cv_pe_stat.object_version_number,
741 x_attribute_category => cv_pe_stat.attribute_category,
742 x_attribute1 => cv_pe_stat.attribute1,
743 x_attribute2 => cv_pe_stat.attribute2,
744 x_attribute3 => cv_pe_stat.attribute3,
745 x_attribute4 => cv_pe_stat.attribute4,
746 x_attribute5 => cv_pe_stat.attribute5,
747 x_attribute6 => cv_pe_stat.attribute6,
748 x_attribute7 => cv_pe_stat.attribute7,
749 x_attribute8 => cv_pe_stat.attribute8,
750 x_attribute9 => cv_pe_stat.attribute9,
751 x_attribute10 => cv_pe_stat.attribute10,
752 x_attribute11 => cv_pe_stat.attribute11,
753 x_attribute12 => cv_pe_stat.attribute12,
754 x_attribute13 => cv_pe_stat.attribute13,
755 x_attribute14 => cv_pe_stat.attribute14,
756 x_attribute15 => cv_pe_stat.attribute15,
757 x_attribute16 => cv_pe_stat.attribute16,
758 x_attribute17 => cv_pe_stat.attribute17,
759 x_attribute18 => cv_pe_stat.attribute18,
760 x_attribute19 => cv_pe_stat.attribute19,
761 x_attribute20 => cv_pe_stat.attribute20,
762 x_global_attribute_category => cv_pe_stat.global_attribute_category,
763 x_global_attribute1 => cv_pe_stat.global_attribute1,
764 x_global_attribute2 => cv_pe_stat.global_attribute2,
765 x_global_attribute3 => cv_pe_stat.global_attribute3,
766 x_global_attribute4 => cv_pe_stat.global_attribute4,
767 x_global_attribute5 => cv_pe_stat.global_attribute5,
768 x_global_attribute6 => cv_pe_stat.global_attribute6,
769 x_global_attribute7 => cv_pe_stat.global_attribute7,
770 x_global_attribute8 => cv_pe_stat.global_attribute8,
771 x_global_attribute9 => cv_pe_stat.global_attribute9,
772 x_global_attribute10=> cv_pe_stat.global_attribute10,
773 x_global_attribute11 => cv_pe_stat.global_attribute11,
774 x_global_attribute12 => cv_pe_stat.global_attribute12,
775 x_global_attribute13 => cv_pe_stat.global_attribute13,
776 x_global_attribute14 => cv_pe_stat.global_attribute14,
777 x_global_attribute15 => cv_pe_stat.global_attribute15,
778 x_global_attribute16 => cv_pe_stat.global_attribute16,
779 x_global_attribute17 => cv_pe_stat.global_attribute17,
780 x_global_attribute18 => cv_pe_stat.global_attribute18,
781 x_global_attribute19 => cv_pe_stat.global_attribute19,
782 x_global_attribute20 => cv_pe_stat.global_attribute20,
783 x_party_last_update_date => v_party_last_update_date,
784 x_person_profile_id => lv_perosn_profile_id,
785 x_matr_cal_type => cv_pe_stat.matr_cal_type,
786 x_matr_sequence_number => cv_pe_stat.matr_sequence_number,
787 x_init_cal_type => cv_pe_stat.init_cal_type,
788 x_init_sequence_number => cv_pe_stat.init_sequence_number,
789 x_recent_cal_type => cv_pe_stat.recent_cal_type,
790 x_recent_sequence_number => cv_pe_stat.recent_sequence_number,
791 x_catalog_cal_type => cv_pe_stat.catalog_cal_type,
792 x_catalog_sequence_number => cv_pe_stat.catalog_sequence_number,
793 z_return_status => v_return_status,
794 z_msg_count => v_msg_count,
795 z_msg_data => v_msg_data
796 );
797 EXCEPTION
798 WHEN OTHERS THEN
799
800 FND_MESSAGE.SET_NAME('IGS', SQLERRM);
801 app_exception.raise_exception;
802 END;
803 END IF;
804
805 END update_per_stats;
806
807 END igs_ad_upd_initialise;