1 PACKAGE BODY IGS_AD_GEN_003 AS
2 /* $Header: IGSAD03B.pls 120.2 2005/08/18 07:46:55 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --knag 29-Oct-02 Bug 2647482 : Created the function get_core_or_optional_unit
7 -- to return CORE_ONLY/Y/N for CORE/CORE_OPTIONAL/no
8 -- as unit enroll indicator to preenroll process
9 --anwest 22-Jul-05 IGS.M (ADTD003): Created the function to act as
10 -- a wrapper for the
11 -- Admp_Get_Adm_Perd_Dt function
12 -- when requiring a Submission
13 -- Deadline or Offer Response date
14 -------------------------------------------------------------------------------------------
15
16 Function Admp_Get_Acai_Aos_Id(
17 p_person_id IN NUMBER ,
18 p_admission_appl_number IN NUMBER ,
19 p_nominated_course_cd IN VARCHAR2 ,
20 p_acai_sequence_number IN NUMBER ,
21 p_date OUT NOCOPY DATE )
22 RETURN NUMBER IS
23 BEGIN -- admp_get_acai_aos_id
24 -- This module gets the person id and date of the current
25 -- IGS_AD_PS_APPL_INST. adm_outcome_status was set.
26 DECLARE
27 v_person_id IGS_AD_PS_APPL_INST.person_id%TYPE;
28
29 CURSOR c_acai IS
30 SELECT acai.decision_date,
31 acai.decision_make_id
32 FROM IGS_AD_PS_APPL_INST acai
33 WHERE acai.person_id = p_person_id AND
34 acai.admission_appl_number = p_admission_appl_number AND
35 acai.nominated_course_cd = p_nominated_course_cd AND
36 acai.sequence_number = p_acai_sequence_number AND
37 acai.adm_outcome_status IS NOT NULL
38 ORDER BY decision_date DESC;
39 BEGIN
40 v_person_id := NULL;
41 p_date := NULL;
42 OPEN c_acai;
43 FETCH c_acai INTO p_date,
44 v_person_id;
45 CLOSE c_acai;
46 RETURN v_person_id;
47 EXCEPTION
48 WHEN OTHERS THEN
49 IF (c_acai%ISOPEN) THEN
50 CLOSE c_acai;
51 END IF;
52 App_Exception.Raise_Exception;
53 END;
54 EXCEPTION
55 WHEN OTHERS THEN
56 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
57 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_003.admp_get_acai_aos_id');
58 IGS_GE_MSG_STACK.ADD;
59 App_Exception.Raise_Exception;
60 END admp_get_acai_aos_id;
61
62 Function Admp_Get_Acai_Crv(
63 p_person_id IN NUMBER ,
64 p_admission_appl_number IN NUMBER ,
65 p_nominated_course_cd IN VARCHAR2 ,
66 p_sequence_number IN NUMBER ,
67 p_course_cd OUT NOCOPY VARCHAR2 ,
68 p_crv_version_number OUT NOCOPY NUMBER )
69 RETURN VARCHAR2 IS
70 BEGIN -- admp_get_acai_crv
71 -- Description: This module gets
72 -- IGS_AD_PS_APPL_INST.course_cd/crv_version_number
73 DECLARE
74 CURSOR c_acai IS
75 SELECT acai.course_cd,
76 acai.crv_version_number
77 FROM IGS_AD_PS_APPL_INST acai
78 WHERE acai.person_id = p_person_id AND
79 acai.admission_appl_number = p_admission_appl_number AND
80 acai.nominated_course_cd = p_nominated_course_cd AND
81 acai.sequence_number = p_sequence_number;
82 v_acai_recs c_acai%ROWTYPE;
83 BEGIN
84 OPEN c_acai;
85 FETCH c_acai INTO v_acai_recs;
86 IF (c_acai%NOTFOUND) THEN
87 CLOSE c_acai;
88 p_course_cd := NULL;
89 p_crv_version_number := NULL;
90 ELSE
91 CLOSE c_acai;
92 p_course_cd := v_acai_recs.course_cd;
93 p_crv_version_number := v_acai_recs.crv_version_number;
94 END IF;
95 END;
96 EXCEPTION
97 WHEN OTHERS THEN
98 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
99 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_003.admp_get_acai_crv');
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END admp_get_acai_crv;
103
104 Function Admp_Get_Acai_Status(
105 p_person_id IN NUMBER ,
106 p_admission_appl_number IN NUMBER ,
107 p_nominated_course_cd IN VARCHAR2 ,
108 p_sequence_number IN NUMBER ,
109 p_adm_outcome_status OUT NOCOPY VARCHAR2 ,
110 p_adm_offer_resp_status OUT NOCOPY VARCHAR2 )
111 RETURN VARCHAR2 IS
112 BEGIN -- admp_get_acai_status
113 -- This module gets the adm_outcome_status and adm_offer_resp_status
114 -- for the specified IGS_AD_PS_APPL_INST. It returns the statuses
115 -- formatted to be used by admission course application context blocks.
116 DECLARE
117 CURSOR c_acai IS
118 SELECT acai.adm_outcome_status,
119 acai.adm_offer_resp_status,
120 aors.s_adm_offer_resp_status
121 FROM IGS_AD_PS_APPL_INST acai,
122 IGS_AD_OFR_RESP_STAT aors
123 WHERE aors.adm_offer_resp_status = acai.adm_offer_resp_status AND
124 acai.person_id = p_person_id AND
125 acai.admission_appl_number = p_admission_appl_number AND
126 acai.nominated_course_cd = p_nominated_course_cd AND
127 acai.sequence_number = p_sequence_number;
128 v_acai_recs c_acai%ROWTYPE;
129 BEGIN
130 OPEN c_acai;
131 FETCH c_acai INTO v_acai_recs;
132 IF (c_acai%NOTFOUND) THEN
133 CLOSE c_acai;
134 p_adm_offer_resp_status := NULL;
135 p_adm_outcome_status := NULL;
136 RETURN NULL;
137 ELSE
138 p_adm_offer_resp_status := v_acai_recs.adm_offer_resp_status;
139 p_adm_outcome_status := v_acai_recs.adm_outcome_status;
140 END IF;
141 CLOSE c_acai;
142 IF (v_acai_recs.s_adm_offer_resp_status = 'NOT-APPLIC') THEN
143 RETURN(v_acai_recs.adm_outcome_status);
144 ELSE
145 RETURN(v_acai_recs.adm_outcome_status|| '/' ||
146 v_acai_recs.adm_offer_resp_status);
147 END IF;
148 CLOSE c_acai;
149 END;
150 EXCEPTION
151 WHEN OTHERS THEN
152 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
153 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_003.admp_get_acai_status');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END admp_get_acai_status;
157
158 Function Admp_Get_Ac_Bfa(
159 p_tac_admission_cd IN VARCHAR2 ,
160 p_admission_cd OUT NOCOPY VARCHAR2 ,
161 p_basis_for_admission_type OUT NOCOPY VARCHAR2 ,
162 p_message_name OUT NOCOPY VARCHAR2 )
163 RETURN BOOLEAN IS
164 BEGIN -- admp_get_ac_bfa
165 -- This module finds the user defined admission code and basis for
166 -- admission type from the admission code table.
167 DECLARE
168 v_adm_cd IGS_AD_CD.admission_cd%TYPE;
169 v_basis_for_adm_type IGS_AD_BASIS_FOR_AD.basis_for_admission_type%TYPE;
170 CURSOR c_aco IS
171 SELECT admission_cd,
172 basis_for_admission_type
173 FROM IGS_AD_CD
174 WHERE tac_admission_cd = p_tac_admission_cd AND
175 closed_ind = 'N'
176 ORDER BY basis_for_admission_type ASC;
177 BEGIN
178 p_message_name := null;
179 OPEN c_aco;
180 FETCH c_aco INTO v_adm_cd,
181 v_basis_for_adm_type;
182 IF (c_aco%NOTFOUND) THEN
183 CLOSE c_aco;
184 p_message_name := 'IGS_AD_CANNOT_DERIVE_ADMCD';
185 RETURN FALSE;
186 ELSIF v_basis_for_adm_type IS NULL THEN
187 CLOSE c_aco;
188 p_message_name := 'IGS_AD_CANNOT_DERIVE_ADMTYPE';
189 RETURN FALSE;
190 END IF;
191 p_admission_cd := v_adm_cd;
192 p_basis_for_admission_type := v_basis_for_adm_type;
193 CLOSE c_aco;
194 RETURN TRUE;
195 END;
196 EXCEPTION
197 WHEN OTHERS THEN
198 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
199 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_003.admp_get_ac_bfa');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END admp_get_ac_bfa;
203
204 Function Admp_Get_Adm_Perd_Dt(
205 p_dt_alias IN VARCHAR2 ,
206 p_adm_cal_type IN VARCHAR2 ,
207 p_adm_ci_sequence_number IN NUMBER ,
208 p_admission_cat IN VARCHAR2 ,
209 p_s_admission_process_type IN VARCHAR2 ,
210 p_course_cd IN VARCHAR2 ,
211 p_version_number IN NUMBER ,
212 p_acad_cal_type IN VARCHAR2 ,
213 p_location_cd IN VARCHAR2 ,
214 p_attendance_mode IN VARCHAR2 ,
215 p_attendance_type IN VARCHAR2 )
216 RETURN DATE IS
217 BEGIN --admp_get_adm_perd_dt
218 --This module returns the date value for an admission period date alias.
219 DECLARE
220 v_sacc_due_dt_alias IGS_AD_CAL_CONF.adm_appl_due_dt_alias%TYPE;
221 v_sacc_final_dt_alias IGS_AD_CAL_CONF.adm_appl_final_dt_alias%TYPE;
222 v_daiv_alias_val DATE;
223 v_dai_sequence_number IGS_AD_PECRS_OFOP_DT.dai_sequence_number%TYPE;
224 v_apcood_found BOOLEAN;
225 v_curr_component_ctr NUMBER;
226 v_high_component_ctr NUMBER;
227 CURSOR c_sacc IS
228 SELECT adm_appl_due_dt_alias,
229 adm_appl_final_dt_alias
230 FROM IGS_AD_CAL_CONF sacc
231 WHERE s_control_num = 1;
232 CURSOR c_daiv IS
233 SELECT IGS_CA_GEN_001.calp_set_alias_value(absolute_val, IGS_CA_GEN_002.cals_clc_dt_from_dai(ci_sequence_number, CAL_TYPE, DT_ALIAS, sequence_number) ) alias_val
234 FROM IGS_CA_DA_INST daiv,
235 IGS_AD_PERD_AD_CAT apac
236 WHERE daiv.dt_alias = p_dt_alias AND
237 apac.adm_cal_type = p_adm_cal_type AND
238 apac.adm_ci_sequence_number = p_adm_ci_sequence_number AND
239 apac.admission_cat = p_admission_cat AND
240 apac.adm_cal_type = daiv.cal_type AND
241 apac.adm_ci_sequence_number = daiv.ci_sequence_number AND
242 NOT EXISTS(
243 SELECT 'x'
244 FROM IGS_AD_PECRS_OFOP_DT apcood
245 WHERE apcood.adm_cal_type = apac.adm_cal_type AND
246 apcood.adm_ci_sequence_number = apac.adm_ci_sequence_number AND
247 apcood.dt_alias = daiv.dt_alias AND
248 apcood.dai_sequence_number = daiv.sequence_number)
249 ORDER BY 1 desc;
250 CURSOR c_daiv2 IS
251 SELECT IGS_CA_GEN_001.calp_set_alias_value(absolute_val, IGS_CA_GEN_002.cals_clc_dt_from_dai(ci_sequence_number, CAL_TYPE, DT_ALIAS, sequence_number) ) alias_val
252 FROM IGS_CA_DA_INST daiv
253 WHERE daiv.cal_type = p_adm_cal_type AND
254 daiv.ci_sequence_number = p_adm_ci_sequence_number AND
255 daiv.dt_alias = p_dt_alias AND
256 daiv.sequence_number = v_dai_sequence_number;
257 CURSOR c_apcood IS
258 SELECT apcood.s_admission_process_type,
259 apcood.course_cd,
260 apcood.version_number,
261 apcood.acad_cal_type,
262 apcood.location_cd,
263 apcood.attendance_mode,
264 apcood.attendance_type,
265 apcood.dai_sequence_number
266 FROM IGS_AD_PECRS_OFOP_DT apcood
267 WHERE apcood.adm_cal_type = p_adm_cal_type AND
268 apcood.adm_ci_sequence_number = p_adm_ci_sequence_number AND
269 apcood.admission_cat = p_admission_cat AND
270 apcood.dt_alias = p_dt_alias;
271 BEGIN
272 --Initialise variables
273 v_apcood_found := FALSE;
274 v_curr_component_ctr := 0;
275 v_high_component_ctr := 0;
276 v_daiv_alias_val := NULL;
277 --Validate parameters, none can be null
278 IF p_dt_alias IS NULL OR
279 p_adm_cal_type IS NULL OR
280 p_adm_ci_sequence_number IS NULL OR
281 p_admission_cat IS NULL THEN
282 RETURN NULL;
283 END IF;
284 --Determine if date alias is allowed admission period date override
285 OPEN c_sacc;
286 FETCH c_sacc INTO v_sacc_due_dt_alias,
287 v_sacc_final_dt_alias;
288 IF (c_sacc%NOTFOUND) THEN
289 CLOSE c_sacc;
290 RETURN NULL;
291 END IF;
292 CLOSE c_sacc;
293 IF ( ((v_sacc_due_dt_alias IS NULL) OR
294 (v_sacc_due_dt_alias <> p_dt_alias)) AND
295 ((v_sacc_final_dt_alias IS NULL) OR
296 (v_sacc_final_dt_alias<> p_dt_alias))) THEN
297 --Get the date value straight from the dt_alias_instance linked to the
298 -- admission
299 --Period, the date alias passed cannot have admission period date overrides
300 --applied
301 OPEN c_daiv;
302 FETCH c_daiv INTO v_daiv_alias_val;
303 IF (c_daiv%NOTFOUND) THEN
304 CLOSE c_daiv;
305 RETURN NULL;
306 END IF;
307 CLOSE c_daiv;
308 RETURN v_daiv_alias_val;
309 END IF;
310 IF p_s_admission_process_type IS NULL AND
311 p_course_cd IS NULL AND
312 p_version_number IS NULL AND
313 p_acad_cal_type IS NULL AND
314 p_location_cd IS NULL AND
315 p_attendance_mode IS NULL AND
316 p_attendance_type IS NULL THEN
317 RETURN NULL;
318 END IF;
319 --Determine if date overrides exist for the date alias in the admission period
320 FOR v_apcood_rec IN c_apcood LOOP
321 v_apcood_found := TRUE;
322 --Check that the record firstly is valid for the parameters
323 IF ((v_apcood_rec.s_admission_process_type IS NULL OR
324 v_apcood_rec.s_admission_process_type = p_s_admission_process_type) AND
328 v_apcood_rec.acad_cal_type = p_acad_cal_type)) AND
325 (v_apcood_rec.course_cd IS NULL OR
326 (v_apcood_rec.course_cd = p_course_cd AND
327 v_apcood_rec.version_number = p_version_number AND
329 (v_apcood_rec.location_cd IS NULL OR
330 v_apcood_rec.location_cd = p_location_cd) AND
331 (v_apcood_rec.attendance_mode IS NULL OR
332 v_apcood_rec.attendance_mode = p_attendance_mode) AND
333 (v_apcood_rec.attendance_type IS NULL OR
334 v_apcood_rec.attendance_type = p_attendance_type)) THEN
335 --Match on the components and save the IGS_CA_DA_INST_V key for the
336 --Record that matches with the highest number of components
337 IF (v_apcood_rec.s_admission_process_type IS NOT NULL) THEN
338 IF (v_apcood_rec.s_admission_process_type = p_s_admission_process_type) THEN
339 v_curr_component_ctr := v_curr_component_ctr + 1;
340 END IF;
341 END IF;
342 IF (v_apcood_rec.course_cd IS NOT NULL) THEN
343 IF (v_apcood_rec.course_cd = p_course_cd AND
344 v_apcood_rec.version_number = p_version_number AND
345 v_apcood_rec.acad_cal_type = p_acad_cal_type) THEN
346 v_curr_component_ctr := v_curr_component_ctr + 1;
347 END IF;
348 END IF;
349 IF (v_apcood_rec.location_cd IS NOT NULL) THEN
350 IF (v_apcood_rec.location_cd = p_location_cd) THEN
351 v_curr_component_ctr := v_curr_component_ctr + 1;
352 END IF;
353 END IF;
354 IF (v_apcood_rec.attendance_mode IS NOT NULL) THEN
355 IF (v_apcood_rec.attendance_mode = p_attendance_mode) THEN
356 v_curr_component_ctr := v_curr_component_ctr + 1;
357 END IF;
358 END IF;
359 IF (v_apcood_rec.attendance_type IS NOT NULL) THEN
360 IF (v_apcood_rec.attendance_type = p_attendance_type) THEN
361 v_curr_component_ctr := v_curr_component_ctr + 1;
362 END IF;
363 END IF;
364 --If this record has the most number of matches then we want to use
365 --its dai_sequence_number
366 IF (v_curr_component_ctr > v_high_component_ctr) THEN
367 v_high_component_ctr := v_curr_component_ctr;
368 v_dai_sequence_number := v_apcood_rec.dai_sequence_number;
369 END IF;
370 v_curr_component_ctr := 0;
371 END IF;
372 END LOOP;
373 IF (NOT v_apcood_found) THEN
374 --Get the date value straight from the dt_alias_instance
375 --linked to the admission period
376 OPEN c_daiv;
377 FETCH c_daiv INTO v_daiv_alias_val;
378 CLOSE c_daiv;
379 RETURN v_daiv_alias_val;
380 END IF;
381 IF (v_dai_sequence_number IS NULL) THEN
382 --Get the date value straight from the dt_alias_instance
383 --linked to the admission Period, no override was found
384 OPEN c_daiv;
385 FETCH c_daiv INTO v_daiv_alias_val;
386 CLOSE c_daiv;
387 RETURN v_daiv_alias_val;
388 END IF;
389 OPEN c_daiv2;
390 FETCH c_daiv2 INTO v_daiv_alias_val;
391 IF (c_daiv2%NOTFOUND) THEN
392 CLOSE c_daiv2;
393 RETURN NULL;
394 END IF;
395 CLOSE c_daiv2;
396 RETURN v_daiv_alias_val;
397 END;
398 END admp_get_adm_perd_dt;
399
400 Procedure Admp_Get_Adm_Pp(
401 p_oracle_username IN VARCHAR2 ,
402 p_adm_acad_cal_type OUT NOCOPY VARCHAR2 ,
403 p_adm_acad_ci_sequence_number OUT NOCOPY NUMBER ,
404 p_adm_acad_alternate_code OUT NOCOPY VARCHAR2 ,
405 p_adm_acad_abbreviation OUT NOCOPY VARCHAR2 ,
406 p_adm_adm_cal_type OUT NOCOPY VARCHAR2 ,
407 p_adm_adm_ci_sequence_number OUT NOCOPY NUMBER ,
408 p_adm_adm_alternate_code OUT NOCOPY VARCHAR2 ,
409 p_adm_adm_abbreviation OUT NOCOPY VARCHAR2 ,
410 p_adm_admission_cat OUT NOCOPY VARCHAR2 ,
411 p_adm_s_admission_process_type OUT NOCOPY VARCHAR2 ,
412 p_adm_ac_description OUT NOCOPY VARCHAR2 )
413 IS
414 BEGIN
415 NULL;
416 END admp_get_adm_pp;
417
418 Procedure Admp_Get_Apcs_Mndtry(
419 p_admission_cat IN VARCHAR2 ,
420 p_s_admission_process_type IN VARCHAR2 ,
421 p_mandatory_athletics OUT NOCOPY BOOLEAN,
422 p_mandatory_alternate OUT NOCOPY BOOLEAN ,
423 p_mandatory_address OUT NOCOPY BOOLEAN ,
424 p_mandatory_disability OUT NOCOPY BOOLEAN ,
425 p_mandatory_visa OUT NOCOPY BOOLEAN ,
426 p_mandatory_finance OUT NOCOPY BOOLEAN ,
427 p_mandatory_notes OUT NOCOPY BOOLEAN ,
428 p_mandatory_statistics OUT NOCOPY BOOLEAN ,
429 p_mandatory_alias OUT NOCOPY BOOLEAN ,
430 p_mandatory_tertiary OUT NOCOPY BOOLEAN ,
431 p_mandatory_aus_sec_ed OUT NOCOPY BOOLEAN ,
432 p_mandatory_os_sec_ed OUT NOCOPY BOOLEAN ,
433 p_mandatory_employment OUT NOCOPY BOOLEAN ,
434 p_mandatory_membership OUT NOCOPY BOOLEAN ,
435 p_mandatory_dob OUT NOCOPY BOOLEAN ,
436 p_mandatory_title OUT NOCOPY BOOLEAN ,
437 p_mandatory_referee OUT NOCOPY BOOLEAN ,
438 p_mandatory_scholarship OUT NOCOPY BOOLEAN ,
439 p_mandatory_lang_prof OUT NOCOPY BOOLEAN ,
443 p_mandatory_fee_assess OUT NOCOPY BOOLEAN ,
440 p_mandatory_interview OUT NOCOPY BOOLEAN ,
441 p_mandatory_exchange OUT NOCOPY BOOLEAN ,
442 p_mandatory_adm_test OUT NOCOPY BOOLEAN ,
444 p_mandatory_cor_category OUT NOCOPY BOOLEAN ,
445 p_mandatory_enr_category OUT NOCOPY BOOLEAN ,
446 p_mandatory_research OUT NOCOPY BOOLEAN ,
447 p_mandatory_rank_app OUT NOCOPY BOOLEAN ,
448 p_mandatory_completion OUT NOCOPY BOOLEAN ,
449 p_mandatory_rank_set OUT NOCOPY BOOLEAN ,
450 p_mandatory_basis_adm OUT NOCOPY BOOLEAN ,
451 p_mandatory_crs_international OUT NOCOPY BOOLEAN ,
452 p_mandatory_ass_tracking OUT NOCOPY BOOLEAN ,
453 p_mandatory_adm_code OUT NOCOPY BOOLEAN ,
454 p_mandatory_fund_source OUT NOCOPY BOOLEAN ,
455 p_mandatory_location OUT NOCOPY BOOLEAN ,
456 p_mandatory_att_mode OUT NOCOPY BOOLEAN ,
457 p_mandatory_att_type OUT NOCOPY BOOLEAN ,
458 p_mandatory_unit_set OUT NOCOPY BOOLEAN ,
459 p_mandatory_evaluation_tab OUT NOCOPY BOOLEAN ,
460 p_mandatory_prog_approval OUT NOCOPY BOOLEAN ,
461 p_mandatory_indices OUT NOCOPY BOOLEAN ,
462 p_mandatory_tst_scores OUT NOCOPY BOOLEAN ,
463 p_mandatory_outcome OUT NOCOPY BOOLEAN ,
464 p_mandatory_override OUT NOCOPY BOOLEAN ,
465 p_mandatory_spl_consider OUT NOCOPY BOOLEAN ,
466 p_mandatory_cond_offer OUT NOCOPY BOOLEAN ,
467 p_mandatory_offer_dead OUT NOCOPY BOOLEAN ,
468 p_mandatory_offer_resp OUT NOCOPY BOOLEAN ,
469 p_mandatory_offer_defer OUT NOCOPY BOOLEAN ,
470 p_mandatory_offer_compl OUT NOCOPY BOOLEAN ,
471 p_mandatory_transfer OUT NOCOPY BOOLEAN ,
472 p_mandatory_other_inst OUT NOCOPY BOOLEAN ,
473 p_mandatory_edu_goals OUT NOCOPY BOOLEAN ,
474 p_mandatory_acad_interest OUT NOCOPY BOOLEAN ,
475 p_mandatory_app_intent OUT NOCOPY BOOLEAN ,
476 p_mandatory_spl_interest OUT NOCOPY BOOLEAN ,
477 p_mandatory_spl_talents OUT NOCOPY BOOLEAN ,
478 p_mandatory_miscell OUT NOCOPY BOOLEAN ,
479 p_mandatory_fees OUT NOCOPY BOOLEAN ,
480 p_mandatory_program OUT NOCOPY BOOLEAN ,
481 p_mandatory_completness OUT NOCOPY BOOLEAN ,
482 p_mandatory_creden OUT NOCOPY BOOLEAN ,
483 p_mandatory_review_det OUT NOCOPY BOOLEAN ,
484 p_mandatory_recomm_det OUT NOCOPY BOOLEAN ,
485 p_mandatory_fin_aid OUT NOCOPY BOOLEAN ,
486 p_mandatory_acad_honors OUT NOCOPY BOOLEAN ,
487 p_mandatory_des_unitsets OUT NOCOPY BOOLEAN,
488 p_mandatory_extrcurr OUT NOCOPY BOOLEAN )
489 IS
490 BEGIN -- admp_get_apcs_mndtry
491 -- Return the steps for an admission process category
492 DECLARE
493 v_unit_set_step BOOLEAN DEFAULT FALSE;
494 v_un_crs_us BOOLEAN DEFAULT FALSE;
495 CURSOR c_apcs IS
496 SELECT apcs.s_admission_step_type
497 FROM IGS_AD_PRCS_CAT_STEP apcs
498 WHERE admission_cat = p_admission_cat AND
499 s_admission_process_type = p_s_admission_process_type AND
500 mandatory_step_ind = 'Y' AND
501 step_group_type <> 'TRACK'; -- 2402377
502 BEGIN
503 -- Initialise the output parameters
504 p_mandatory_athletics := FALSE;
505 p_mandatory_alternate := FALSE;
506 p_mandatory_address := FALSE;
507 p_mandatory_disability := FALSE;
508 p_mandatory_visa := FALSE;
509 p_mandatory_finance := FALSE;
510 p_mandatory_notes := FALSE;
511 p_mandatory_statistics := FALSE;
512 p_mandatory_alias := FALSE;
513 p_mandatory_tertiary := FALSE;
514 p_mandatory_aus_sec_ed := FALSE;
515 p_mandatory_os_sec_ed := FALSE;
516 p_mandatory_employment := FALSE;
517 p_mandatory_membership := FALSE;
518 p_mandatory_referee := FALSE;
519 p_mandatory_scholarship := FALSE;
520 p_mandatory_lang_prof := FALSE;
521 p_mandatory_interview := FALSE;
522 p_mandatory_exchange := FALSE;
523 p_mandatory_adm_test := FALSE;
524 p_mandatory_fee_assess := FALSE;
525 p_mandatory_cor_category := FALSE;
526 p_mandatory_enr_category := FALSE;
527 p_mandatory_research := FALSE;
528 p_mandatory_rank_app := FALSE;
529 p_mandatory_completion := FALSE;
530 p_mandatory_rank_set := FALSE;
531 p_mandatory_basis_adm := FALSE;
532 p_mandatory_crs_international := FALSE;
533 p_mandatory_ass_tracking := FALSE;
534 p_mandatory_adm_code := FALSE;
535 p_mandatory_fund_source := FALSE;
536 p_mandatory_unit_set := FALSE;
537 p_mandatory_dob := TRUE;
538 p_mandatory_title := TRUE;
539 p_mandatory_location := TRUE;
540 p_mandatory_att_mode := TRUE;
541 p_mandatory_att_type := TRUE;
542
543 p_mandatory_evaluation_tab := FALSE;
544 p_mandatory_prog_approval := FALSE;
545 p_mandatory_indices := FALSE;
546 p_mandatory_tst_scores := FALSE;
547 p_mandatory_outcome := FALSE;
548 p_mandatory_override := FALSE;
549 p_mandatory_spl_consider := FALSE;
550 p_mandatory_cond_offer := FALSE;
551 p_mandatory_offer_dead := FALSE;
552 p_mandatory_offer_resp := FALSE;
553 p_mandatory_offer_defer := FALSE;
554 p_mandatory_offer_compl := FALSE;
555 p_mandatory_transfer := FALSE;
559 p_mandatory_app_intent := FALSE;
556 p_mandatory_other_inst := FALSE;
557 p_mandatory_edu_goals := FALSE;
558 p_mandatory_acad_interest := FALSE;
560 p_mandatory_spl_interest := FALSE;
561 p_mandatory_spl_talents := FALSE;
562 p_mandatory_miscell := FALSE;
563 p_mandatory_fees := FALSE;
564 p_mandatory_program := FALSE;
565 p_mandatory_completness := FALSE;
566 p_mandatory_creden := FALSE;
567 p_mandatory_review_det := FALSE;
568 p_mandatory_recomm_det := FALSE;
569 p_mandatory_fin_aid := FALSE;
570 p_mandatory_acad_honors := FALSE;
571 p_mandatory_des_unitsets := FALSE; -- added for 2382599
572 p_mandatory_extrcurr := FALSE;
573
574 FOR v_apcs_rec IN c_apcs LOOP
575 IF (v_apcs_rec.s_admission_step_type = 'ATHLETICS') THEN
576 p_mandatory_athletics := TRUE;
577 END IF;
578 IF (v_apcs_rec.s_admission_step_type = 'PER-ALTERNATE') THEN
579 p_mandatory_alternate := TRUE;
580 END IF;
581 IF (v_apcs_rec.s_admission_step_type = 'PER-ADDR') THEN
582 p_mandatory_address := TRUE;
583 END IF;
584 IF (v_apcs_rec.s_admission_step_type = 'DISABILITY') THEN
585 p_mandatory_disability := TRUE;
586 END IF;
587 IF (v_apcs_rec.s_admission_step_type = 'PER-INTL') THEN
588 p_mandatory_visa := TRUE;
589 END IF;
590 IF (v_apcs_rec.s_admission_step_type = 'FINANCE') THEN
591 p_mandatory_finance := TRUE;
592 END IF;
593 IF (v_apcs_rec.s_admission_step_type = 'PER-NOTES') THEN
594 p_mandatory_notes := TRUE;
595 END IF;
596 IF (v_apcs_rec.s_admission_step_type = 'PER-STAT') THEN
597 p_mandatory_statistics := TRUE;
598 END IF;
599 IF (v_apcs_rec.s_admission_step_type = 'PER-ALIASES') THEN
600 p_mandatory_alias := TRUE;
601 END IF;
602 IF (v_apcs_rec.s_admission_step_type = 'TERT-EDN') THEN
603 p_mandatory_tertiary := TRUE;
604 END IF;
605 IF (v_apcs_rec.s_admission_step_type = 'AUS-SEC-ED') THEN
606 p_mandatory_aus_sec_ed := TRUE;
607 END IF;
608 IF (v_apcs_rec.s_admission_step_type = 'OS-SEC-ED') THEN
609 p_mandatory_os_sec_ed := TRUE;
610 END IF;
611 IF (v_apcs_rec.s_admission_step_type = 'EMPLOYMENT') THEN
612 p_mandatory_employment := TRUE;
613 END IF;
614 IF (v_apcs_rec.s_admission_step_type = 'PROFMEMBER') THEN
615 p_mandatory_membership := TRUE;
616 END IF;
617 IF (v_apcs_rec.s_admission_step_type = 'REFEREE') THEN
618 p_mandatory_referee := TRUE;
619 END IF;
620 IF (v_apcs_rec.s_admission_step_type = 'SCHOLRSHIP') THEN
621 p_mandatory_scholarship := TRUE;
622 END IF;
623 IF (v_apcs_rec.s_admission_step_type = 'LANG-PROF') THEN
624 p_mandatory_lang_prof := TRUE;
625 END IF;
626 IF (v_apcs_rec.s_admission_step_type = 'INTERVIEW') THEN
627 p_mandatory_interview := TRUE;
628 END IF;
629 IF (v_apcs_rec.s_admission_step_type = 'EXCHANGE') THEN
630 p_mandatory_exchange := TRUE;
631 END IF;
632 IF (v_apcs_rec.s_admission_step_type = 'ADM-TEST') THEN
633 p_mandatory_adm_test := TRUE;
634 END IF;
635 IF (v_apcs_rec.s_admission_step_type = 'FEE-ASSESS') THEN
636 p_mandatory_fee_assess := TRUE;
637 END IF;
638 IF (v_apcs_rec.s_admission_step_type = 'CORCATEGRY') THEN
639 p_mandatory_cor_category := TRUE;
640 END IF;
641 IF (v_apcs_rec.s_admission_step_type = 'ENRCATEGRY') THEN
642 p_mandatory_enr_category := TRUE;
643 END IF;
644 IF (v_apcs_rec.s_admission_step_type = 'RESEARCH') THEN
645 p_mandatory_research := TRUE;
646 END IF;
647 IF (v_apcs_rec.s_admission_step_type = 'RANK-APP') THEN
648 p_mandatory_rank_app := TRUE;
649 END IF;
650 IF (v_apcs_rec.s_admission_step_type = 'COMPLETION') THEN
651 p_mandatory_completion := TRUE;
652 END IF;
653 IF (v_apcs_rec.s_admission_step_type = 'RANK-SET') THEN
654 p_mandatory_rank_set := TRUE;
655 END IF;
656 IF (v_apcs_rec.s_admission_step_type = 'BASIS-ADM') THEN
657 p_mandatory_basis_adm := TRUE;
658 END IF;
659 IF (v_apcs_rec.s_admission_step_type = 'CRS-INTERN') THEN
660 p_mandatory_crs_international := TRUE;
661 END IF;
662 IF (v_apcs_rec.s_admission_step_type = 'ASS-TRACK') THEN
663 p_mandatory_ass_tracking := TRUE;
664 END IF;
665 IF (v_apcs_rec.s_admission_step_type = 'ADM-CODE') THEN
666 p_mandatory_adm_code := TRUE;
667 END IF;
668 IF (v_apcs_rec.s_admission_step_type = 'FUNDSOURCE') THEN
669 p_mandatory_fund_source := TRUE;
670 END IF;
671 IF (v_apcs_rec.s_admission_step_type = 'UN-DOB') THEN
672 p_mandatory_dob := FALSE;
673 END IF;
674 IF (v_apcs_rec.s_admission_step_type = 'UN-TITLE') THEN
678 p_mandatory_location := FALSE;
675 p_mandatory_title := FALSE;
676 END IF;
677 IF (v_apcs_rec.s_admission_step_type = 'UN-CRS-LOC') THEN
679 END IF;
680 IF (v_apcs_rec.s_admission_step_type = 'UN-CRS-MOD') THEN
681 p_mandatory_att_mode := FALSE;
682 END IF;
683 IF (v_apcs_rec.s_admission_step_type = 'UN-CRS-TYP') THEN
684 p_mandatory_att_type := FALSE;
685 END IF;
686 IF (v_apcs_rec.s_admission_step_type = 'UNIT-SET') THEN
687 v_unit_set_step := TRUE;
688 END IF;
689 IF (v_apcs_rec.s_admission_step_type = 'UN-CRS-US') THEN
690 v_un_crs_us := TRUE;
691 END IF;
692
693 IF (v_apcs_rec.s_admission_step_type = 'PGM-APPRV') THEN
694 p_mandatory_prog_approval := TRUE;
695 END IF;
696 IF (v_apcs_rec.s_admission_step_type = 'ACAD-HONORS') THEN
697 p_mandatory_acad_honors := TRUE;
698 END IF;
699 IF (v_apcs_rec.s_admission_step_type = 'ACAD-INTEREST') THEN
700 p_mandatory_acad_interest := TRUE;
701 END IF;
702 IF (v_apcs_rec.s_admission_step_type = 'ACAD-INTENT') THEN
703 p_mandatory_app_intent := TRUE;
704 END IF;
705 IF (v_apcs_rec.s_admission_step_type = 'EDU-GOALS') THEN
706 p_mandatory_edu_goals := TRUE;
707 END IF;
708 IF (v_apcs_rec.s_admission_step_type = 'OTH-INST-APPL') THEN
709 p_mandatory_other_inst := TRUE;
710 END IF;
711 IF (v_apcs_rec.s_admission_step_type = 'SPL-TALENT') THEN
712 p_mandatory_spl_talents := TRUE;
713 END IF;
714 IF (v_apcs_rec.s_admission_step_type = 'SPL-INTEREST') THEN
715 p_mandatory_spl_talents := TRUE;
716 END IF;
717 IF (v_apcs_rec.s_admission_step_type = 'MISC-DTL') THEN
718 p_mandatory_miscell := TRUE;
719 END IF;
720 IF (v_apcs_rec.s_admission_step_type = 'FINAID') THEN
721 p_mandatory_fin_aid := TRUE;
722 END IF;
723
724 IF (v_apcs_rec.s_admission_step_type = 'DES-UNITSETS') THEN -- added for 2382599
725 p_mandatory_des_unitsets := TRUE;
726 END IF;
727
728 IF (v_apcs_rec.s_admission_step_type = 'EXTRACURRI') THEN
729 p_mandatory_extrcurr := TRUE;
730 END IF;
731
732
733 END LOOP;
734 IF (v_unit_set_step AND
735 NOT v_un_crs_us) THEN
736 p_mandatory_unit_set := TRUE;
737 END IF;
738 EXCEPTION
739 WHEN OTHERS THEN
740 IF (c_apcs%ISOPEN) THEN
741 CLOSE c_apcs;
742 END IF;
743 App_Exception.Raise_Exception;
744 END;
745 EXCEPTION
746 WHEN OTHERS THEN
747 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
748 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_003.admp_get_apcs_mndtry');
749 IGS_GE_MSG_STACK.ADD;
750 App_Exception.Raise_Exception;
751 END admp_get_apcs_mndtry;
752
753 PROCEDURE get_entr_doc_apc (p_admission_cat IN IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
754 p_s_admission_process_type IN IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE,
755 l_adm_doc_status OUT NOCOPY IGS_AD_PS_APPL_INST_ALL.adm_doc_status%TYPE,
756 l_adm_entr_qual_status OUT NOCOPY IGS_AD_PS_APPL_INST_ALL.adm_entry_qual_status%TYPE) IS
757 /*****************************************************************************************
758 Created By: [email protected]
759 Date Created : 09-SEP-2002
760 Purpose: 1.Returns the application completion status and entry qual status based on APC
761 Known limitations,enhancements,remarks:
762 Change History
763 Who When What
764 *****************************************************************************************/
765 CURSOR c_apcs_step (
766 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
767 cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE,
768 cp_s_admission_step_type IGS_AD_PRCS_CAT_STEP.s_admission_step_type%TYPE) IS
769 SELECT apcs.s_admission_step_type
770 FROM IGS_AD_PRCS_CAT_STEP apcs
771 WHERE apcs.admission_cat = cp_admission_cat AND
772 apcs.s_admission_process_type = cp_s_admission_process_type AND
773 apcs.s_admission_step_type = cp_s_admission_step_type AND
774 apcs.step_group_type = 'OUTCOME';
775 l_s_admission_step_type IGS_AD_PRCS_CAT_STEP.s_admission_step_type%TYPE;
776 BEGIN
777 IF p_s_admission_process_type = 'NON-AWARD' THEN
778 l_adm_entr_qual_status := IGS_AD_GEN_009.admp_get_sys_aeqs('NOT-APPLIC');
779 l_adm_doc_status:= IGS_AD_GEN_009.admp_get_sys_ads('NOT-APPLIC');
780 ELSE
781 OPEN c_apcs_step(p_admission_cat,p_s_admission_process_type,'DFLT_ENTRY_QUAL');
782 FETCH c_apcs_step INTO l_s_admission_step_type;
783 IF c_apcs_step%FOUND AND l_s_admission_step_type = 'DFLT_ENTRY_QUAL' THEN
784 l_adm_entr_qual_status := IGS_AD_GEN_009.admp_get_sys_aeqs('QUALIFIED');
785 ELSE
786 l_adm_entr_qual_status := IGS_AD_GEN_009.admp_get_sys_aeqs('PENDING');
787 END IF;
788 CLOSE c_apcs_step;
789
790 OPEN c_apcs_step(p_admission_cat,p_s_admission_process_type,'DFLT_DOC_STATUS');
791 FETCH c_apcs_step INTO l_s_admission_step_type;
792 IF c_apcs_step%FOUND AND l_s_admission_step_type = 'DFLT_DOC_STATUS' THEN
793 l_adm_doc_status:= IGS_AD_GEN_009.admp_get_sys_ads('SATISFIED');
794 ELSE
795 l_adm_doc_status:= IGS_AD_GEN_009.admp_get_sys_ads('PENDING');
796 END IF;
797 CLOSE c_apcs_step;
798 END IF;
799 END get_entr_doc_apc;
800
801 FUNCTION get_core_or_optional_unit (
802 p_person_id igs_ad_appl_all.person_id%TYPE,
803 p_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE)
804 RETURN VARCHAR2 IS -- (Y, CORE_ONLY, N)
805 /*****************************************************************************************
806 Created By: knag
807 Date Created : 29-OCT-2002
808 Purpose: To return CORE_ONLY/Y/N for CORE/CORE_OPTIONAL/no as unit enrollment indicator
809 to pre-enrollment process
810 Known limitations,enhancements,remarks:
811 Change History
812 Who When What
813 *****************************************************************************************/
814 -- will fetch first CORE_OPTIONAL then CORE if both present
815 CURSOR c_get_core_or_optional_unit IS
816 SELECT apcs.s_admission_step_type
817 FROM igs_ad_prcs_cat_step_all apcs, igs_ad_appl_all appl
818 WHERE appl.person_id = p_person_id
819 AND appl.admission_appl_number = p_admission_appl_number
820 AND apcs.admission_cat = appl.admission_cat
821 AND apcs.s_admission_process_type = appl.s_admission_process_type
822 AND apcs.step_group_type = 'UNIT-VAL'
823 AND apcs.s_admission_step_type IN ('CORE','CORE_OPTIONAL')
824 ORDER BY apcs.s_admission_step_type desc;
825
826 l_s_admission_step_type igs_ad_prcs_cat_step_all.s_admission_step_type%TYPE;
827 BEGIN
828 IF p_person_id IS NOT NULL AND p_admission_appl_number IS NOT NULL THEN
829 OPEN c_get_core_or_optional_unit;
830 FETCH c_get_core_or_optional_unit INTO l_s_admission_step_type;
831 CLOSE c_get_core_or_optional_unit;
832 END IF;
833 IF l_s_admission_step_type IS NOT NULL THEN
834 IF l_s_admission_step_type = 'CORE_OPTIONAL' THEN
835 l_s_admission_step_type := 'Y';
836 ELSIF l_s_admission_step_type = 'CORE' THEN
837 l_s_admission_step_type := 'CORE_ONLY';
838 END IF;
839 ELSE
840 l_s_admission_step_type := 'N';
841 END IF;
842 RETURN l_s_admission_step_type;
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF c_get_core_or_optional_unit%ISOPEN THEN
846 CLOSE c_get_core_or_optional_unit;
847 END IF;
848 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
849 fnd_message.set_token('NAME','igs_ad_gen_003.get_core_or_optional_unit');
850 igs_ge_msg_stack.add;
851 app_exception.raise_exception;
852 END get_core_or_optional_unit;
853
854
855 Function get_apc_date(p_date_type IN VARCHAR2 ,
856 p_adm_cal_type IN VARCHAR2 ,
857 p_adm_ci_sequence_number IN NUMBER ,
858 p_admission_cat IN VARCHAR2 ,
859 p_s_admission_process_type IN VARCHAR2 ,
860 p_course_cd IN VARCHAR2 ,
861 p_version_number IN NUMBER ,
862 p_acad_cal_type IN VARCHAR2 ,
863 p_location_cd IN VARCHAR2 ,
864 p_attendance_mode IN VARCHAR2 ,
865 p_attendance_type IN VARCHAR2 )
866 RETURN DATE IS
867
868 /******************************************************************************
869 Created By: anwest
870 Date Created: 22-JUL-2005
871 Purpose: To act as a wrapper for the Admp_Get_Adm_Perd_Dt function when
872 requiring a Submission Deadline or Offer Response date
873 Known limitations,enhancements,remarks:
874 Change History
875 Who When What
876 ******************************************************************************/
877
878 BEGIN
879
880 DECLARE
881
885 l_offer_resp_dt_alias IGS_AD_CAL_CONF.adm_appl_offer_resp_dt_alias%TYPE;
882 l_apc_step_exists VARCHAR2(1);
883 l_due_dt_alias IGS_AD_CAL_CONF.adm_appl_due_dt_alias%TYPE;
884 l_final_dt_alias IGS_AD_CAL_CONF.adm_appl_final_dt_alias%TYPE;
886 l_dt_alias IGS_AD_CAL_CONF.adm_appl_offer_resp_dt_alias%TYPE;
887 l_out_dt DATE;
888
889 CURSOR c_apc_step IS
890 SELECT 'X'
891 FROM IGS_AD_PRCS_CAT_STEP
892 WHERE admission_cat = p_admission_cat AND
893 s_admission_process_type = p_s_admission_process_type AND
894 s_admission_step_type = 'LATE-APP' AND
895 step_group_type <> 'TRACK';
896
897 CURSOR c_dt_alias IS
898 SELECT adm_appl_due_dt_alias, adm_appl_final_dt_alias, adm_appl_offer_resp_dt_alias
899 FROM IGS_AD_CAL_CONF
900 WHERE s_control_num = '1';
901
902 BEGIN
903
904 OPEN c_dt_alias;
905 FETCH c_dt_alias INTO l_due_dt_alias, l_final_dt_alias, l_offer_resp_dt_alias;
906 CLOSE c_dt_alias;
907
908 IF p_date_type = 'SUBMISSION_DEADLINE' THEN
909
910 OPEN c_apc_step;
911 FETCH c_apc_step INTO l_apc_step_exists;
912 CLOSE c_apc_step;
913
914 IF l_apc_step_exists IS NOT NULL THEN
915 l_dt_alias:= l_final_dt_alias;
916 ELSE
917 l_dt_alias:= l_due_dt_alias;
918 END IF;
919
920 ELSIF p_date_type = 'OFFER_RESPONSE_DATE' THEN
921
922 l_dt_alias:= l_offer_resp_dt_alias;
923
924 ELSE
925
926 l_dt_alias:= NULL;
927
928 END IF;
929
930 RETURN Admp_Get_Adm_Perd_Dt(
931 l_dt_alias,
932 p_adm_cal_type,
933 p_adm_ci_sequence_number,
934 p_admission_cat,
935 p_s_admission_process_type,
936 p_course_cd,
937 p_version_number,
938 p_acad_cal_type,
939 p_location_cd,
940 p_attendance_mode,
941 p_attendance_type );
942
943 END;
944 EXCEPTION
945 WHEN OTHERS THEN
946 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
947 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_003.get_apc_date');
948 IGS_GE_MSG_STACK.ADD;
949 App_Exception.Raise_Exception;
950 END get_apc_date;
951
952 END IGS_AD_GEN_003;
[Home] [Help]