1 PACKAGE BODY igs_ad_gen_004 AS
2 /* $Header: IGSAD04B.pls 115.21 2003/12/05 11:11:53 rboddu ship $ */
3
4 /*
5 who when what
6 sarakshi 06-May-2003 Enh#2858431,modified admp_get_cricos_cd replaced system reference code of CRICOS to OTHER
7 */
8 PROCEDURE Admp_Get_Apcs_Val(
9 p_admission_cat IN VARCHAR2 ,
10 p_s_admission_process_type IN VARCHAR2 ,
11 p_apcs_pref_limit_ind OUT NOCOPY VARCHAR2 ,
12 p_apcs_app_fee_ind OUT NOCOPY VARCHAR2 ,
13 p_apcs_late_app_ind OUT NOCOPY VARCHAR2 ,
14 p_apcs_late_fee_ind OUT NOCOPY VARCHAR2 ,
15 p_apcs_chkpencumb_ind OUT NOCOPY VARCHAR2 ,
16 p_apcs_fee_assess_ind OUT NOCOPY VARCHAR2 ,
17 p_apcs_corcategry_ind OUT NOCOPY VARCHAR2 ,
18 p_apcs_enrcategry_ind OUT NOCOPY VARCHAR2 ,
19 p_apcs_chkcencumb_ind OUT NOCOPY VARCHAR2 ,
20 p_apcs_unit_set_ind OUT NOCOPY VARCHAR2 ,
21 p_apcs_un_crs_us_ind OUT NOCOPY VARCHAR2 ,
22 p_apcs_chkuencumb_ind OUT NOCOPY VARCHAR2 ,
23 p_apcs_unit_restr_ind OUT NOCOPY VARCHAR2 ,
24 p_apcs_unit_restriction_num OUT NOCOPY NUMBER ,
25 p_apcs_un_dob_ind OUT NOCOPY VARCHAR2 ,
26 p_apcs_un_title_ind OUT NOCOPY VARCHAR2 ,
27 p_apcs_asses_cond_ind OUT NOCOPY VARCHAR2 ,
28 p_apcs_fee_cond_ind OUT NOCOPY VARCHAR2 ,
29 p_apcs_doc_cond_ind OUT NOCOPY VARCHAR2 ,
30 p_apcs_multi_off_ind OUT NOCOPY VARCHAR2 ,
31 p_apcs_multi_off_restrict_num OUT NOCOPY NUMBER ,
32 p_apcs_set_otcome_ind OUT NOCOPY VARCHAR2 ,
33 p_apcs_override_o_ind OUT NOCOPY VARCHAR2 ,
34 p_apcs_defer_ind OUT NOCOPY VARCHAR2 ,
35 p_apcs_ack_app_ind OUT NOCOPY VARCHAR2 ,
36 p_apcs_outcome_lt_ind OUT NOCOPY VARCHAR2 ,
37 p_apcs_pre_enrol_ind OUT NOCOPY VARCHAR2 )
38 IS
39 ----------------------------------------------------------------
40 --Created by :
41 --Date created:
42 --
43 --Purpose: BUG NO :
44 --
45 --
46 --Known limitations/enhancements and/or remarks:
47 --
48 --Change History:
49 --Who When What
50 ----------------------------------------------------------------
51
52 BEGIN -- admp_get_apcs_val
53 -- Return the steps for an admission process category validation.
54 DECLARE
55 -- Retrieve the admission process category steps.
56 CURSOR c_apcs IS
57 SELECT s_admission_step_type,
58 step_type_restriction_num
59 FROM IGS_AD_PRCS_CAT_STEP
60 WHERE admission_cat = p_admission_cat AND
61 s_admission_process_type = p_s_admission_process_type AND
62 step_group_type <> 'TRACK'; --2402377
63 BEGIN
64 p_apcs_pref_limit_ind :='N';
65 p_apcs_app_fee_ind :='N';
66 p_apcs_late_app_ind :='N';
67 p_apcs_late_fee_ind :='N';
68 p_apcs_chkpencumb_ind :='N';
69 p_apcs_fee_assess_ind :='N';
70 p_apcs_corcategry_ind :='N';
71 p_apcs_enrcategry_ind :='N';
72 p_apcs_chkcencumb_ind :='N';
73 p_apcs_unit_set_ind :='N';
74 p_apcs_un_crs_us_ind :='N';
75 p_apcs_chkuencumb_ind :='N';
76 p_apcs_unit_restr_ind :='N';
77 p_apcs_unit_restriction_num := 0;
78 p_apcs_asses_cond_ind :='N';
79 p_apcs_fee_cond_ind :='N';
80 p_apcs_doc_cond_ind :='N';
81 p_apcs_multi_off_ind := 'N';
82 p_apcs_multi_off_restrict_num := 0;
83 p_apcs_set_otcome_ind :='N';
84 p_apcs_override_o_ind :='N';
85 p_apcs_defer_ind :='N';
86 p_apcs_ack_app_ind :='N';
87 p_apcs_outcome_lt_ind :='N';
88 p_apcs_pre_enrol_ind :='N';
89 p_apcs_un_dob_ind :='N';
90 p_apcs_un_title_ind :='N';
91 --Loop through each IGS_AD_PRCS_CAT_STEP record
92 FOR v_apcs_rec IN c_apcs LOOP
93 IF (v_apcs_rec.s_admission_step_type = 'PREF-LIMIT') THEN
94 p_apcs_pref_limit_ind := 'Y';
95 ELSIF (v_apcs_rec.s_admission_step_type = 'APP-FEE') THEN
96 p_apcs_app_fee_ind :='Y';
97 ELSIF (v_apcs_rec.s_admission_step_type = 'LATE-APP') THEN
98 p_apcs_late_app_ind :='Y';
99 ELSIF (v_apcs_rec.s_admission_step_type = 'LATE-FEE') THEN
100 p_apcs_late_fee_ind := 'Y';
101 ELSIF (v_apcs_rec.s_admission_step_type = 'CHKPENCUMB') THEN
102 p_apcs_chkpencumb_ind := 'Y';
103 ELSIF (v_apcs_rec.s_admission_step_type = 'FEE-ASSESS') THEN
104 p_apcs_fee_assess_ind := 'Y';
105 ELSIF (v_apcs_rec.s_admission_step_type = 'CORCATEGRY') THEN
106 p_apcs_corcategry_ind := 'Y';
107 ELSIF (v_apcs_rec.s_admission_step_type = 'ENRCATEGRY') THEN
108 p_apcs_enrcategry_ind :='Y';
109 ELSIF (v_apcs_rec.s_admission_step_type = 'CHKCENCUMB') THEN
110 p_apcs_chkcencumb_ind := 'Y';
111 ELSIF (v_apcs_rec.s_admission_step_type = 'UNIT-SET') THEN
112 p_apcs_unit_set_ind := 'Y';
113 ELSIF (v_apcs_rec.s_admission_step_type = 'UN-CRS-US') THEN
114 p_apcs_un_crs_us_ind := 'Y';
115 ELSIF (v_apcs_rec.s_admission_step_type = 'CHKUENCUMB') THEN
116 p_apcs_chkuencumb_ind := 'Y';
117 ELSIF (v_apcs_rec.s_admission_step_type = 'UNIT-RESTR') THEN
118 p_apcs_unit_restr_ind := 'Y';
119 p_apcs_unit_restriction_num := v_apcs_rec.step_type_restriction_num;
120 ELSIF (v_apcs_rec.s_admission_step_type = 'ASSES-COND') THEN
121 p_apcs_asses_cond_ind := 'Y';
122 ELSIF (v_apcs_rec.s_admission_step_type = 'FEE-COND') THEN
123 p_apcs_fee_cond_ind :='Y';
124 ELSIF (v_apcs_rec.s_admission_step_type = 'DOC-COND') THEN
125 p_apcs_doc_cond_ind :='Y';
126 ELSIF (v_apcs_rec.s_admission_step_type = 'MULTI-OFF') THEN
127 p_apcs_multi_off_ind := 'Y';
128 p_apcs_multi_off_restrict_num := v_apcs_rec.step_type_restriction_num;
129 ELSIF (v_apcs_rec.s_admission_step_type = 'SET-OTCOME') THEN
130 p_apcs_set_otcome_ind :='Y';
131 ELSIF (v_apcs_rec.s_admission_step_type = 'OVERRIDE-O') THEN
132 p_apcs_override_o_ind := 'Y';
133 ELSIF (v_apcs_rec.s_admission_step_type = 'DEFER') THEN
134 p_apcs_defer_ind := 'Y';
135 ELSIF (v_apcs_rec.s_admission_step_type = 'ACK-APP') THEN
136 p_apcs_ack_app_ind := 'Y';
137 ELSIF (v_apcs_rec.s_admission_step_type = 'OUTCOME-LT') THEN
138 p_apcs_outcome_lt_ind := 'Y';
139 ELSIF (v_apcs_rec.s_admission_step_type = 'PRE-ENROL') THEN
140 p_apcs_pre_enrol_ind := 'Y';
141 ELSIF (v_apcs_rec.s_admission_step_type = 'UN-DOB') THEN
142 p_apcs_un_dob_ind := 'Y';
143 ELSIF (v_apcs_rec.s_admission_step_type = 'UN-TITLE') THEN
144 p_apcs_un_title_ind :='Y';
145 END IF;
146 END LOOP;
147 END;
148 EXCEPTION
149 WHEN OTHERS THEN
150 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
151 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_apcs_val');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception;
154 END admp_get_apcs_val;
155
156 FUNCTION Admp_Get_Archive_Ind(
157 p_person_id IN NUMBER )
158 RETURN VARCHAR2 IS
159 BEGIN -- admp_get_archive_ind
160 -- Determine whether or not a person is archived
161 DECLARE
162 CURSOR c_pe(
163 cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
164 SELECT pd.archive_dt
165 FROM igs_pe_hz_parties pd
166 WHERE pd.party_id = cp_person_id ;
167
168 cst_yes CONSTANT CHAR := 'Y';
169 cst_no CONSTANT CHAR := 'N';
170 v_pe_rec c_pe%ROWTYPE;
171 BEGIN
172 OPEN c_pe(
173 p_person_id);
174 FETCH c_pe INTO v_pe_rec;
175 IF c_pe%NOTFOUND THEN
176 CLOSE c_pe;
177 RETURN cst_no;
178 END IF;
179 CLOSE c_pe;
180 IF v_pe_rec.archive_dt IS NOT NULL THEN
181 RETURN cst_yes;
182 END IF;
183 RETURN cst_no;
184 END;
185 EXCEPTION
186 WHEN OTHERS THEN
187 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
188 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_archive_ind');
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END admp_get_archive_ind;
192
193 FUNCTION Admp_Get_Chg_Pref_Dt(
194 p_person_id IN NUMBER ,
195 p_admission_appl_number IN NUMBER ,
196 p_nominated_course_cd IN VARCHAR2 ,
197 p_acai_sequence_number IN NUMBER )
198 RETURN VARCHAR2 IS
199 BEGIN -- admp_get_chg_pref_dt
200 -- This module retrieves the date instance for change of preferences.
201 DECLARE
202 v_out_date DATE;
203 v_dt_alias IGS_AD_CAL_CONF.adm_appl_chng_of_pref_dt_alias%TYPE;
204 CURSOR c_sacc_dt_alias IS
205 SELECT sacc.adm_appl_chng_of_pref_dt_alias
206 FROM IGS_AD_CAL_CONF sacc
207 WHERE sacc.s_control_num = 1;
208 CURSOR c_aa_acaiv IS
209 SELECT acaiv.adm_cal_type,
210 acaiv.adm_ci_sequence_number,
211 aa.admission_cat,
212 aa.s_admission_process_type,
213 acaiv.course_cd,
214 acaiv.crv_version_number,
215 aa.acad_cal_type,
216 acaiv.location_cd,
217 acaiv.attendance_mode,
218 acaiv.attendance_type
219 FROM igs_ad_ps_appl_inst acaiv, -- Replaced IGS_AD_PS_APPL_INST_APLINST_V with IGS_AD_PS_APPL Bug: 3150054
220 igs_ad_appl aa
221 WHERE acaiv.admission_appl_number = aa.admission_appl_number AND
222 acaiv.person_id = aa.person_id AND
223 acaiv.person_id = p_person_id AND
224 acaiv.admission_appl_number = p_admission_appl_number AND
225 acaiv.nominated_course_cd = p_nominated_course_cd AND
226 acaiv.sequence_number = p_acai_sequence_number;
227 v_aa_acaiv_rec c_aa_acaiv%ROWTYPE;
228 BEGIN
229 OPEN c_sacc_dt_alias;
230 FETCH c_sacc_dt_alias INTO v_dt_alias;
231 CLOSE c_sacc_dt_alias;
232 OPEN c_aa_acaiv;
233 FETCH c_aa_acaiv INTO v_aa_acaiv_rec;
234 CLOSE c_aa_acaiv;
235 v_out_date := IGS_AD_GEN_003.admp_get_adm_perd_dt(
236 v_dt_alias,
237 v_aa_acaiv_rec.adm_cal_type,
238 v_aa_acaiv_rec.adm_ci_sequence_number,
239 v_aa_acaiv_rec.admission_cat,
240 v_aa_acaiv_rec.s_admission_process_type,
241 v_aa_acaiv_rec.course_cd,
242 v_aa_acaiv_rec.crv_version_number,
243 v_aa_acaiv_rec.acad_cal_type,
244 v_aa_acaiv_rec.location_cd,
245 v_aa_acaiv_rec.attendance_mode,
246 v_aa_acaiv_rec.attendance_type);
247 IF v_out_date IS NOT NULL THEN
248 RETURN IGS_GE_DATE.IGSCHAR(v_out_date); -- IGS_GE_DATE.IGSCHAR(v_out_date, 'DD/MM/YYYY');
249 ELSE
250 RETURN NULL;
251 END IF;
252 EXCEPTION
253 WHEN OTHERS THEN
254 IF c_sacc_dt_alias%ISOPEN THEN
255 CLOSE c_sacc_dt_alias;
256 END IF;
257 IF c_aa_acaiv%ISOPEN THEN
258 CLOSE c_aa_acaiv;
259 END IF;
260 App_Exception.Raise_Exception;
261 END;
262 EXCEPTION
263 WHEN OTHERS THEN
264 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
265 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_chg_pref_dt');
266 IGS_GE_MSG_STACK.ADD;
267 App_Exception.Raise_Exception;
268 END admp_get_chg_pref_dt;
269
270 FUNCTION Admp_Get_Comm_Perd(
271 p_person_id IN NUMBER ,
272 p_admission_appl_number IN NUMBER ,
273 p_nominated_course_cd IN VARCHAR2 ,
274 p_acai_sequence_number IN NUMBER )
275 RETURN VARCHAR2 IS
276 BEGIN -- admp_get_comm_perd
277 -- This module retrieves the commencement period
278 DECLARE
279 v_commencement_period IGS_AD_PS_APPL_INST_APLSUMR_V.commencement_period%TYPE;
280 CURSOR c_aasv IS --modified the cursor to use base tables instead of IGS_AD_PS_APPL_INST_APLSUMR_V. Bug: 3150054
281 SELECT SUBSTR(IGS_AD_GEN_002.admp_get_acai_acadcd(
282 aav.person_id,
283 aav.admission_appl_number,
284 aav.acad_cal_type,
285 NVL(acai.adm_cal_type, aav.adm_cal_type),
286 NVL(acai.adm_ci_sequence_number, aav.adm_ci_sequence_number)),1,10) || '/' || ci.alternate_code commencement_period
287 FROM igs_ad_ps_appl_inst acai,
288 igs_ad_appl aav,
289 igs_ca_inst ci
290 WHERE aav.person_id = acai.person_id AND
291 aav.admission_appl_number = acai.admission_appl_number AND
292 ci.cal_type = NVL(acai.adm_cal_type, aav.adm_cal_type) AND
293 ci.sequence_number = NVL(acai.adm_ci_sequence_number,aav.adm_ci_sequence_number) AND
294 acai.person_id = p_person_id AND
295 acai.admission_appl_number = p_admission_appl_number AND
296 acai.nominated_course_cd = p_nominated_course_cd AND
297 acai.sequence_number = p_acai_sequence_number;
298 BEGIN
299 OPEN c_aasv;
300 FETCH c_aasv INTO v_commencement_period;
301 IF (c_aasv%NOTFOUND) THEN
302 CLOSE c_aasv;
303 RETURN NULL;
304 END IF;
305 CLOSE c_aasv;
306 RETURN v_commencement_period;
307 EXCEPTION
308 WHEN OTHERS THEN
309 IF (c_aasv%ISOPEN) THEN
310 CLOSE c_aasv;
311 END IF;
312 App_Exception.Raise_Exception;
313 END;
314 EXCEPTION
315 WHEN OTHERS THEN
316 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
317 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_comm_perd');
318 IGS_GE_MSG_STACK.ADD;
319 App_Exception.Raise_Exception;
320 END admp_get_comm_perd;
321
322 FUNCTION Admp_Get_Course_Det(
323 p_person_id IN NUMBER ,
324 p_admission_appl_number IN NUMBER ,
325 p_s_letter_parameter_type IN VARCHAR2 ,
326 p_record_number IN NUMBER ,
327 p_extra_context OUT NOCOPY VARCHAR2 )
328 RETURN VARCHAR2 IS
329 BEGIN -- admp_get_course_det
330 -- This module retrieves course details from IGS_AD_PS_APPL_INST
331 -- for use on letters.
332 DECLARE
333 v_out_string VARCHAR2(106) DEFAULT NULL;
334 v_alt_acad_cal IGS_CA_INST.alternate_code%TYPE;
335 CURSOR c_cv_acaiv IS
336 SELECT acaiv.preference_number,
337 acaiv.course_cd,
338 cv.short_title,
339 cv.title,
340 acaiv.location_cd,
341 acaiv.attendance_mode,
342 acaiv.attendance_type,
343 aav.acad_cal_type,
344 aav.acad_ci_sequence_number,
345 acaiv.sequence_number,
346 ci1.alternate_code || '/' || ci2.alternate_code commencement_period
347 FROM igs_ad_ps_appl_inst acaiv, -- Replaced IGS_AD_APPL_ADMAPPL_V with igs_ad_appl_inst Bug: 3150054
348 igs_ad_appl aav,
349 igs_ca_inst ci1,
350 igs_ca_inst ci2,
351 igs_ps_ver cv
352 WHERE acaiv.crv_version_number = cv.version_number AND
353 acaiv.course_cd = cv.course_cd AND
354 acaiv.person_id = p_person_id AND
355 acaiv.admission_appl_number = p_admission_appl_number AND
356 aav.person_id = acaiv.person_id AND
357 aav.admission_appl_number = acaiv.admission_appl_number AND
358 ci1.cal_type = aav.acad_cal_type AND
359 ci1.sequence_number = aav.acad_ci_sequence_number AND
360 ci2.cal_type = NVL(acaiv.adm_cal_type, aav.adm_cal_type) AND
361 ci2.sequence_number = NVL(acaiv.adm_cal_type, aav.adm_ci_sequence_number)
362 ORDER BY acaiv.preference_number,
363 acaiv.course_cd;
364 BEGIN
365 -- Set default value
366 p_extra_context := NULL;
367 FOR v_cv_acaiv_rec IN c_cv_acaiv LOOP
368 IF c_cv_acaiv%ROWCOUNT = p_record_number THEN
369 -- create output return string and create p_extra_context string
370 IF p_s_letter_parameter_type = 'ADM_COURSE' THEN
371 IF v_cv_acaiv_rec.preference_number IS NULL THEN
372 v_out_string :=
373 RPAD(NVL(v_cv_acaiv_rec.course_cd, '-'),10) || ' ' ||
374 RPAD(NVL(v_cv_acaiv_rec.short_title, '-'),40) || ' ' ||
375 RPAD(NVL(v_cv_acaiv_rec.location_cd, '-'),10) || ' ' ||
376 RPAD(NVL(v_cv_acaiv_rec.attendance_mode, '-'),2) || ' ' ||
377 RPAD(NVL(v_cv_acaiv_rec.attendance_type, '-'),2) || ' ' ||
378 NVL(v_cv_acaiv_rec.commencement_period, '-');
379 ELSE
380 v_out_string :=
381 RPAD(NVL(IGS_GE_NUMBER.TO_CANN(v_cv_acaiv_rec.preference_number),'-'),2) || ' ' ||
382 RPAD(NVL(v_cv_acaiv_rec.course_cd, '-'),10) || ' ' ||
383 RPAD(NVL(v_cv_acaiv_rec.short_title, '-'),40) || ' ' ||
384 RPAD(NVL(v_cv_acaiv_rec.location_cd, '-'),10) || ' ' ||
385 RPAD(NVL(v_cv_acaiv_rec.attendance_mode, '-'),2) || ' ' ||
386 RPAD(NVL(v_cv_acaiv_rec.attendance_type, '-'),2) || ' ' ||
387 NVL(v_cv_acaiv_rec.commencement_period, '-');
388 END IF;
389 ELSIF p_s_letter_parameter_type = 'ADM_CRS_CD' THEN
390 v_out_string := NVL(v_cv_acaiv_rec.course_cd, '-');
391 ELSE
392 v_out_string := INITCAP(NVL(v_cv_acaiv_rec.title, '-'));
393 END IF;
394 p_extra_context := v_cv_acaiv_rec.course_cd || '|' ||
395 v_cv_acaiv_rec.sequence_number;
396 END IF;
397 END LOOP;
398 RETURN v_out_string;
399 EXCEPTION
400 WHEN OTHERS THEN
401 IF c_cv_acaiv%ISOPEN THEN
402 CLOSE c_cv_acaiv;
403 END IF;
404 App_Exception.Raise_Exception;
405 END;
406 EXCEPTION
407 WHEN OTHERS THEN
408 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
409 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_course_det');
410 IGS_GE_MSG_STACK.ADD;
411 App_Exception.Raise_Exception;
412 END admp_get_course_det;
413
414 FUNCTION Admp_Get_Cricos_Cd(
415 p_person_id IN NUMBER ,
416 p_admission_appl_number IN NUMBER ,
417 p_nominated_course_cd IN VARCHAR2 ,
418 p_acai_sequence_number IN NUMBER )
419 RETURN VARCHAR2 IS
420 BEGIN
421 DECLARE
422 v_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE;
423 v_crv_version_number IGS_AD_PS_APPL_INST.crv_version_number%TYPE;
424 v_reference_cd IGS_PS_REF_CD.reference_cd%TYPE;
425 CURSOR c_acai IS
426 SELECT acai.course_cd,
427 acai.crv_version_number
428 FROM IGS_AD_PS_APPL_INST acai
429 WHERE acai.person_id = p_person_id AND
430 acai.admission_appl_number = p_admission_appl_number AND
431 acai.nominated_course_cd = p_nominated_course_cd AND
432 acai.sequence_number = p_acai_sequence_number;
433 CURSOR c_crc_rct (
434 cp_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE,
435 cp_crv_version_number IGS_AD_PS_APPL_INST.crv_version_number%TYPE) IS
436 SELECT crc.reference_cd
437 FROM IGS_PS_REF_CD crc,
438 IGS_GE_REF_CD_TYPE rct
439 WHERE rct.s_reference_cd_type = 'OTHER' AND
440 crc.reference_cd_type = rct.reference_cd_type AND
441 crc.course_cd = cp_course_cd AND
442 crc.version_number = cp_crv_version_number;
443 BEGIN
444 IF (p_person_id IS NULL OR
445 p_admission_appl_number IS NULL OR
446 p_nominated_course_cd IS NULL OR
447 p_acai_sequence_number IS NULL) THEN
448 RETURN NULL;
449 END IF;
450 OPEN c_acai;
451 FETCH c_acai INTO v_course_cd,
452 v_crv_version_number;
453 IF (c_acai%NOTFOUND) THEN
454 CLOSE c_acai;
455 RETURN NULL;
456 ELSE
457 CLOSE c_acai;
458 OPEN c_crc_rct (
459 v_course_cd,
460 v_crv_version_number);
461 FETCH c_crc_rct INTO v_reference_cd;
462 CLOSE c_crc_rct;
463 RETURN v_reference_cd;
464 END IF;
465 EXCEPTION
466 WHEN OTHERS THEN
467 IF (c_acai%ISOPEN) THEN
468 CLOSE c_acai;
469 END IF;
470 IF (c_crc_rct%ISOPEN) THEN
471 CLOSE c_crc_rct;
472 END IF;
473 App_Exception.Raise_Exception;
474 END;
475 EXCEPTION
476 WHEN OTHERS THEN
477 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
478 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_cricos_cd');
479 IGS_GE_MSG_STACK.ADD;
480 App_Exception.Raise_Exception;
481 END admp_get_cricos_cd;
482
483 PROCEDURE Admp_Get_Crs_Exists(
484 p_person_id IN NUMBER ,
485 p_admission_appl_number IN NUMBER ,
486 p_nominated_course_cd IN VARCHAR2 ,
487 p_acai_sequence_number IN NUMBER ,
488 p_course_cd IN VARCHAR2 ,
489 p_effective_dt IN DATE ,
490 p_s_admission_process_type IN VARCHAR2 ,
491 p_check_referee IN BOOLEAN ,
492 p_check_scholarship IN BOOLEAN ,
493 p_check_lang_prof IN BOOLEAN ,
494 p_check_interview IN BOOLEAN ,
495 p_check_exchange IN BOOLEAN ,
496 p_check_adm_test IN BOOLEAN ,
497 p_check_research IN BOOLEAN ,
498 p_referee_exists OUT NOCOPY BOOLEAN ,
499 p_scholarship_exists OUT NOCOPY BOOLEAN ,
500 p_lang_prof_exists OUT NOCOPY BOOLEAN ,
501 p_interview_exists OUT NOCOPY BOOLEAN ,
502 p_exchange_exists OUT NOCOPY BOOLEAN ,
503 p_adm_test_exists OUT NOCOPY BOOLEAN ,
504 p_research_exists OUT NOCOPY BOOLEAN ,
505 p_error_message_research OUT NOCOPY VARCHAR2)
506
507 IS
508 /******************************************************************
509 Created By:
510 Date Created By:
511 Purpose:
512 Known limitations,enhancements,remarks:
513 Change History
514 Who When What
515 rrengara 2-APR-2002 Added parameter P_error_message_research in procedure for the bug 2285677
516 hreddych 6-jan-2003 Remove cursor check for ADM TEST and
517 return FALSE for it # 2737932
518 *****************************************************************************************************/
519
520 BEGIN -- admp_get_crs_exists
521 -- Return output parameters indicating whether or not data exists
522 -- on course level detail tables for the specified person id.
523 DECLARE
524 cst_readmit CONSTANT VARCHAR2(10) := 'RE-ADMIT';
525 v_check VARCHAR2(1);
526 v_message_name VARCHAR2(30) default null;
527 v_out_admission_appl_number IGS_AD_PS_APPL.admission_appl_number%TYPE;
528 v_out_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE;
529 v_out_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE;
530 v_admission_appl_number IGS_AD_PS_APPL.admission_appl_number%TYPE;
531 v_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE;
532 v_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE;
533 v_parent VARCHAR2(5);
534 BEGIN -- Initialise output parameters
535 p_referee_exists := FALSE;
536 p_scholarship_exists := FALSE;
537 p_lang_prof_exists := FALSE;
538 p_interview_exists := FALSE;
539 p_exchange_exists := FALSE;
540 p_adm_test_exists := FALSE;
541 p_research_exists := FALSE;
542
543 IF p_check_research = TRUE THEN
544 IF p_s_admission_process_type = cst_readmit THEN
545 --Determine the admission course application instance.
546 IGS_RE_GEN_002.resp_get_sca_ca_acai (
547 p_person_id,
548 p_course_cd,
549 p_admission_appl_number,
550 p_nominated_course_cd,
551 p_acai_sequence_number,
552 v_out_admission_appl_number,
553 v_out_nominated_course_cd,
554 v_out_acai_sequence_number);
555 IF v_out_admission_appl_number IS NULL THEN
556 v_parent := 'SCA';
557 ELSE
558 v_parent := 'ACAI';
559 END IF;
560 v_admission_appl_number := v_out_admission_appl_number;
561 v_nominated_course_cd := v_out_nominated_course_cd;
562 v_acai_sequence_number := v_out_acai_sequence_number;
563 ELSE
564 v_parent := 'ACAI';
565 v_admission_appl_number := p_admission_appl_number;
566 v_nominated_course_cd := p_nominated_course_cd;
567 v_acai_sequence_number := p_acai_sequence_number;
568 END IF;
569 -- Check for existence of research details.
570 /*
571 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
572 || Changed the reference of "IGS_AD_VAL_ACAI.RESP_VAL_CA_DTL_COMP" to program unit "IGS_EN_VAL_SCA.RESP_VAL_CA_DTL_COMP". -- kdande
573 */
574 IF IGS_EN_VAL_SCA.resp_val_ca_dtl_comp (
575 p_person_id,
576 p_course_cd,
577 v_admission_appl_number,
578 v_nominated_course_cd,
579 v_acai_sequence_number,
580 v_parent,
581 v_message_name) = TRUE THEN
582 p_research_exists := TRUE;
583 -- Added else part for the bug 2285677 by rrengara on 2-APR-2002
584 -- Previously the message was not propagating to the calling procedure
585 -- if the api fails.
586 ELSE
587 p_error_message_research := v_message_name;
588 END IF;
589 END IF;
590 EXCEPTION
591 WHEN OTHERS THEN
592 App_Exception.Raise_Exception;
593 END;
594 EXCEPTION
595 WHEN OTHERS THEN
596 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
597 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_crs_exists');
598 IGS_GE_MSG_STACK.ADD;
599 App_Exception.Raise_Exception;
600 END admp_get_crs_exists;
601
602 PROCEDURE Admp_Get_Crv_Comp_Dt(
603 p_course_cd IN VARCHAR2 ,
604 p_crv_version_number IN NUMBER ,
605 p_cal_type IN VARCHAR2 ,
606 p_attendance_type IN VARCHAR2 ,
607 p_start_dt IN DATE ,
608 p_expected_completion_yr IN OUT NOCOPY NUMBER ,
609 p_expected_completion_perd IN OUT NOCOPY VARCHAR2 ,
610 p_completion_dt OUT NOCOPY DATE,
611 p_attendance_mode IN VARCHAR2 ,
612 p_location_cd IN VARCHAR2)
613 ----------------------------------------------------------------
614 --Created by :
615 --Date created:
616 --
617 --Purpose: BUG NO :
618 --
619 --
620 --Known limitations/enhancements and/or remarks:
621 --
622 --Change History:
623 --Who When What
624 --rboddu 29-OCT-2002 Calculating the Program Completion details based on
625 -- the Program Length and Program Length Measurement present
626 -- in the Program Offering Option record. Removed the code
627 -- related to the Standard Full time or Standard Part time
628 -- Completion. Enh bug : 2647482
629 ----------------------------------------------------------------
630 IS
631 BEGIN --admp_get_crv_comp_dt
632 --Routine to return the course version expected completion date.
633 --This can be determined from a start date or expected completeion details
634 DECLARE
635 v_cal_type IGS_PS_OFR.cal_type%TYPE;
636 v_att_gat IGS_EN_ATD_TYPE.govt_attendance_type%TYPE;
637 v_comp_perd_dt_alias IGS_AD_CAL_CONF.adm_appl_e_comp_perd_dt_alias%TYPE;
638 v_completion_time IGS_PS_VER.std_ft_completion_time%TYPE;
639 v_completion_months IGS_PS_VER.std_ft_completion_time%TYPE;
640 v_completion_dt DATE;
641 v_start_dt_yyyy VARCHAR2(4);
642 v_completion_dt_yyyy VARCHAR2(4);
643 v_context_completion_dt DATE;
644 v_dai2_alias_val_dd_mon VARCHAR2(7);
645 v_dai2_alias_val_yyyy VARCHAR2(4);
646 v_dai2_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
647 v_dai2_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE;
648 v_dai IGS_CA_DA_INST_V.alias_val%TYPE;
649 v_adm_e_perd_dt_alias IGS_AD_CAL_CONF.adm_appl_e_comp_perd_dt_alias%TYPE;
650 v_adm_m_perd_dt_alias IGS_AD_CAL_CONF.adm_appl_m_comp_perd_dt_alias%TYPE;
651 v_adm_s_perd_dt_alias IGS_AD_CAL_CONF.adm_appl_s_comp_perd_dt_alias%TYPE;
652 v_rec_cnt NUMBER;
653 v_completion_days NUMBER;
654 CURSOR c_co IS
655 SELECT co.cal_type
656 FROM IGS_PS_OFR co
657 WHERE co.course_cd = p_course_cd AND
658 co.version_number = p_crv_version_number;
659 CURSOR c_sacco IS
660 SELECT adm_appl_e_comp_perd_dt_alias,
661 adm_appl_m_comp_perd_dt_alias,
662 adm_appl_s_comp_perd_dt_alias
663 FROM IGS_AD_CAL_CONF sacco
664 WHERE s_control_num = 1;
665
666 --Following cursor is modified as part of bug 2715535, to pick up alias values which are greater than
667 --the Context Completion Date such that the Year part of it is greater than or equal to Expected Completion Year.
668 CURSOR c_dai IS
669 SELECT dai.alias_val
670 FROM IGS_CA_DA_INST_V dai
671 WHERE dt_alias = v_comp_perd_dt_alias AND
672 cal_type = v_cal_type AND
673 NVL(alias_val, IGS_GE_DATE.IGSDATE('1900/01/01')) >= v_context_completion_dt AND
674 SUBSTR(IGS_GE_DATE.IGSCHAR(alias_val),1,4) >= p_expected_completion_yr
675 ORDER BY alias_val; -- Order is changed to Ascending. Bug :2647482
676
677 CURSOR c_dai2 (
678 cp_adm_e_perd_dt_alias IGS_AD_CAL_CONF.adm_appl_e_comp_perd_dt_alias%TYPE,
679 cp_adm_m_perd_dt_alias IGS_AD_CAL_CONF.adm_appl_m_comp_perd_dt_alias%TYPE,
680 cp_adm_s_perd_dt_alias IGS_AD_CAL_CONF.adm_appl_s_comp_perd_dt_alias%TYPE) IS
681 SELECT alias_val,
682 dt_alias
683 FROM IGS_CA_DA_INST_V dai
684 WHERE dt_alias IN(
685 NVL(cp_adm_e_perd_dt_alias, 'NONE'),
686 NVL(cp_adm_m_perd_dt_alias, 'NONE'),
687 NVL(cp_adm_s_perd_dt_alias, 'NONE')
688 ) AND
689 cal_type = v_cal_type AND
690 NVL(alias_val, IGS_GE_DATE.IGSDATE('1900/01/01')) >= v_context_completion_dt --Replaced '<' with '>'. Bug: 2647482
691 ORDER BY alias_val; -- Order is changed to Ascending. Bug :2647482
692
693 --Cursor to get the Program Length and Program Length Measurement for the Program Offering Option record. Bug:2647482
694 CURSOR c_get_prg_mesr_dtls(
695 cp_course_cd igs_ps_ofr_opt_all.course_cd%TYPE,
696 cp_version_number igs_ps_ofr_opt_all.version_number%TYPE,
697 cp_cal_type igs_ps_ofr_opt_all.cal_type%TYPE,
698 cp_attendance_type igs_ps_ofr_opt_all.attendance_type%TYPE,
699 cp_attendance_mode igs_ps_ofr_opt_all.attendance_mode%TYPE,
700 cp_location_cd igs_ps_ofr_opt_all.location_cd%TYPE
701 ) IS
702 SELECT program_length,
703 program_length_measurement
704 FROM igs_ps_ofr_opt_all
705 WHERE course_cd = p_course_cd AND
706 version_number = p_crv_version_number AND
707 cal_type = p_cal_type AND
708 location_cd = p_location_cd AND
709 attendance_mode = p_attendance_mode AND
710 attendance_type = p_attendance_type;
711
712 c_get_prg_mesr_dtls_rec c_get_prg_mesr_dtls%ROWTYPE;
713
714 BEGIN
715
716 --Check if there is adequate information to identify the Program Offering Option record. Enh Bug: 2647482
717 IF p_attendance_type IS NULL OR p_attendance_mode IS NULL OR p_location_cd IS NULL THEN
718 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
719 RETURN;
720 END IF;
721 --Check for adequate information to continue processing
722 IF (p_start_dt IS NULL AND
723 p_expected_completion_yr IS NULL AND
724 p_expected_completion_perd IS NULL) THEN
725 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
726 RETURN;
727 END IF;
728
729 --Check that course start date is given, calculations cannot be
730 --determined without it
731 IF (p_start_dt IS NULL) THEN
732 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
733 RETURN;
734 ELSE
735 -- Determine course start date year
736 v_start_dt_yyyy := SUBSTR(IGS_GE_DATE.IGSCHAR(p_start_dt),1,4);
737 END IF;
738
739
740 --Get academic calendar type if not specified or validate calendar type
741 -- specified
742 OPEN c_co;
743 FETCH c_co INTO v_cal_type;
744 IF (c_co%NOTFOUND) THEN
745 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
746 CLOSE c_co;
747 RETURN;
748 END IF;
749 CLOSE c_co;
750 v_rec_cnt := 0;
751
752 FOR v_co_rec IN c_co LOOP
753 IF (p_cal_type IS NULL) THEN
754 IF (v_rec_cnt = 0) THEN
755 v_cal_type := v_co_rec.cal_type;
756 ELSE
757 v_cal_type := NULL;
758 EXIT;
759 END IF;
760 ELSE
761 IF (v_co_rec.cal_type = p_cal_type) THEN
762 v_cal_type := v_co_rec.cal_type;
763 END IF;
764 END IF;
765 v_rec_cnt := v_rec_cnt + 1;
766 END LOOP;
767
768 IF ( v_cal_type IS NULL) THEN
769 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
770 RETURN;
771 END IF;
772
773 --Check for existence of completion period date alias
774 --in calendar configuration table
775 OPEN c_sacco;
776 FETCH c_sacco INTO v_adm_e_perd_dt_alias,
777 v_adm_m_perd_dt_alias,
778 v_adm_s_perd_dt_alias;
779 IF (c_sacco%NOTFOUND) THEN
780 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
781 CLOSE c_sacco;
782 RETURN;
783 END IF;
784 CLOSE c_sacco;
785
786 -- Removed the code related to the Government Attendance Type, which gives the course whether
787 -- it's FULL / PART time. Now the completion details calculation is based on Program Length and
788 -- Program Length Measurement details present at the Program Offering Option record.
789 -- The following cursor c_get_prg_mesr_dtls gets the Program length details of the Offering Option record.
790 -- Enh Bug : 2647482
791
792 OPEN c_get_prg_mesr_dtls(p_course_cd,
793 p_crv_version_number,
794 p_cal_type,
795 p_attendance_type,
796 p_attendance_mode,
797 p_location_cd
798 );
799
800 FETCH c_get_prg_mesr_dtls INTO c_get_prg_mesr_dtls_rec;
801 IF c_get_prg_mesr_dtls%NOTFOUND THEN
802 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
803 CLOSE c_get_prg_mesr_dtls;
804 RETURN;
805 END IF;
806 CLOSE c_get_prg_mesr_dtls;
807
808 -- Convert the Program Length into Days, depending on the Program Length Measurement
809 -- record for the Program Offering Option. Enh Bug: 2647482
810 IF c_get_prg_mesr_dtls_rec.program_length_measurement = 'YEAR' THEN
811 v_completion_days := c_get_prg_mesr_dtls_rec.program_length*365;
812 ELSIF c_get_prg_mesr_dtls_rec.program_length_measurement = '10TH OF A YEAR' THEN
813 v_completion_days := c_get_prg_mesr_dtls_rec.program_length*36.5;
814 ELSIF c_get_prg_mesr_dtls_rec.program_length_measurement = 'MONTHS' THEN
815 v_completion_days := c_get_prg_mesr_dtls_rec.program_length*30.4;
816 ELSIF c_get_prg_mesr_dtls_rec.program_length_measurement = 'WEEKS' THEN
817 v_completion_days := c_get_prg_mesr_dtls_rec.program_length*7;
818 ELSIF c_get_prg_mesr_dtls_rec.program_length_measurement = 'DAYS' THEN
819 v_completion_days := c_get_prg_mesr_dtls_rec.program_length;
820 ELSIF c_get_prg_mesr_dtls_rec.program_length_measurement = 'HOURS' THEN
821 v_completion_days := c_get_prg_mesr_dtls_rec.program_length/24;
822 ELSIF c_get_prg_mesr_dtls_rec.program_length_measurement = 'MINUTES' THEN
823 v_completion_days := c_get_prg_mesr_dtls_rec.program_length/1440;
824 END IF;
825
826 IF v_completion_days IS NULL THEN
827 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
828 RETURN;
829 END IF;
830
831 --Add the Program Length (in Days) to the Program Start Date to get the Completion Date. Bug: 2647482
832 v_context_completion_dt := p_start_dt + ROUND(v_completion_days) -1;
833
834 -- Calculate the Completion Details for the given Expected Completion Period only if the Expected Completion Period and Completion Period Date alias are NOT NULL.
835 IF (p_expected_completion_yr IS NOT NULL AND --replaced the OR condition here with the AND and removed the following redundant validation. Bug: 2647482
836 p_expected_completion_perd IS NOT NULL) THEN
837
838 -- Determine end date, expected completion details given
839 IF (p_expected_completion_perd = 'E' AND
840 v_adm_e_perd_dt_alias IS NOT NULL) THEN
841 v_comp_perd_dt_alias := v_adm_e_perd_dt_alias;
842 ELSIF (p_expected_completion_perd = 'M' AND
843 v_adm_m_perd_dt_alias IS NOT NULL) THEN
844 v_comp_perd_dt_alias := v_adm_m_perd_dt_alias;
845 ELSIF (p_expected_completion_perd = 'S' AND
846 v_adm_s_perd_dt_alias IS NOT NULL) THEN
847 v_comp_perd_dt_alias := v_adm_s_perd_dt_alias;
848 END IF;
849
850 IF (v_comp_perd_dt_alias IS NULL) THEN
851 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
852 RETURN;
853 END IF;
854
855 -- get completion date, which is the closest date greater than the context completion date and year part is greater than
856 -- the Expected Completion Year. Bug : 2715535
857 OPEN c_dai;
858 FETCH c_dai INTO v_dai;
859 IF (c_dai%NOTFOUND) THEN
860 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
861 CLOSE c_dai;
862 RETURN;
863 END IF;
864 CLOSE c_dai;
865
866 -- Only process the first record found as it's the closest date greater than the Context Completion Date.
867 -- And also the returned alias value has the Year which is greater than or equal to the given Expected Completion Year. Bug: 2715535
868 p_completion_dt := v_dai;
869 RETURN;
870 END IF;
871
872 -- Move completion date into context of course start date
873 v_completion_dt_yyyy := SUBSTR(IGS_GE_DATE.IGSCHAR(v_completion_dt),1,4); --IGSCHAR returns date in 'YYYY/MM/DD' format
874
875 --Determine expected completion details
876 OPEN c_dai2 ( v_adm_e_perd_dt_alias,
877 v_adm_m_perd_dt_alias,
878 v_adm_s_perd_dt_alias );
879 FETCH c_dai2 INTO v_dai2_alias_val,
880 v_dai2_dt_alias;
881
882 IF (c_dai2%NOTFOUND) THEN
883 p_completion_dt := IGS_GE_DATE.IGSDATE(NULL);
884 CLOSE c_dai2;
885 RETURN;
886 END IF;
887 CLOSE c_dai2;
888
889 -- Only process the first record found because it's the nearest date greater than the Context Completion Date;
890 v_dai2_alias_val_dd_mon := SUBSTR(IGS_GE_DATE.IGSCHAR(v_dai2_alias_val),5,10); --IGSCHAR returns date in 'YYYY/MM/DD' format
891
892 v_dai2_alias_val_yyyy := SUBSTR(IGS_GE_DATE.IGSCHAR(v_dai2_alias_val),1,4); --IGSCHAR returns date in 'YYYY/MM/DD' format
893
894 p_expected_completion_yr := IGS_GE_NUMBER.TO_NUM(v_dai2_alias_val_yyyy);
895
896 p_completion_dt := IGS_GE_DATE.IGSDATE(IGS_GE_NUMBER.TO_CANN(p_expected_completion_yr)||v_dai2_alias_val_dd_mon);
897
898 --Return the Completion Period associated with the calculated Completion date alias instance.
899 IF (v_dai2_dt_alias = NVL(v_adm_e_perd_dt_alias, 'NONE')) THEN
900 p_expected_completion_perd := 'E';
901 ELSIF (v_dai2_dt_alias = NVL(v_adm_m_perd_dt_alias, 'NONE')) THEN
902 p_expected_completion_perd := 'M';
903 ELSIF (v_dai2_dt_alias = NVL(v_adm_s_perd_dt_alias, 'NONE')) THEN
904 p_expected_completion_perd := 'S';
905 END IF;
906 RETURN;
907 END;
908 EXCEPTION
909 WHEN OTHERS THEN
910 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
911 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_004.admp_get_crv_comp_dt');
912 IGS_GE_MSG_STACK.ADD;
913 App_Exception.Raise_Exception;
914 END admp_get_crv_comp_dt;
915
916 END igs_ad_gen_004;
[Home] [Help]