1 PACKAGE BODY igs_ad_gen_007 AS
2 /* $Header: IGSAD07B.pls 120.3 2006/02/01 23:46:58 pfotedar ship $ */
3 FUNCTION Admp_Get_Match_Prsn(
4 p_surname IN VARCHAR2 ,
5 p_birth_dt IN VARCHAR2 ,
6 p_sex IN VARCHAR2 ,
7 p_initial IN VARCHAR2 ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN NUMBER IS
10 BEGIN -- admp_get_match_prsn
11 -- This module attempts to find a person based on surname, birth date,
12 -- sex and first initial.
13 DECLARE
14 v_match BOOLEAN;
15 v_person_id IGS_PE_PERSON.person_id%TYPE;
16 v_dd_mm_yyyy NUMBER(8);
17 CURSOR c_person IS
18 SELECT person_id,
19 first_name given_names
20 FROM igs_pe_person_base_v
21 WHERE last_name = p_surname AND
22 birth_date = TO_DATE(p_birth_dt, 'DDMMYYYY') AND
23 gender = p_sex;
24 BEGIN
25 -- Set the default message number
26 p_message_name := null;
27 -- Check the parameter birthday in correct format.
28 BEGIN
29 v_dd_mm_yyyy := IGS_GE_NUMBER.TO_NUM(p_birth_dt);
30 IF v_dd_mm_yyyy <= 0 THEN
31 p_message_name := 'IGS_AD_BIRTHDT_INCORRECT_DATA';
32 RETURN 0;
33 END IF;
34 v_dd_mm_yyyy := IGS_GE_NUMBER.TO_NUM(SUBSTR(p_birth_dt,1,2));
35 IF (v_dd_mm_yyyy > 31) THEN
36 p_message_name := 'IGS_AD_BIRTHDT_INCORRECT_DATA';
37 RETURN 0;
38 END IF;
39 v_dd_mm_yyyy := IGS_GE_NUMBER.TO_NUM(SUBSTR(p_birth_dt,3,2));
40 IF (v_dd_mm_yyyy > 12) THEN
41 p_message_name := 'IGS_AD_BIRTHDT_INCORRECT_DATA';
42 RETURN 0;
43 END IF;
44 v_dd_mm_yyyy := IGS_GE_NUMBER.TO_NUM(SUBSTR(p_birth_dt,5,4));
45 IF (v_dd_mm_yyyy <= 1900) THEN
46 p_message_name := 'IGS_AD_BIRTHDT_INCORRECT_DATA';
47 RETURN 0;
48 END IF;
49 EXCEPTION
50 WHEN VALUE_ERROR THEN
51 p_message_name := 'IGS_AD_BIRTHDT_INCORRECT_DATA';
52 RETURN 0;
53 END;
54 v_match := FALSE;
55 FOR v_person_rec IN c_person LOOP
56 IF (SUBSTR(v_person_rec.given_names,1,1) = p_initial) THEN
57 v_person_id := v_person_rec.person_id;
58 v_match := TRUE;
59 EXIT;
60 END IF;
61 END LOOP;
62 IF (v_match = TRUE) THEN
63 RETURN v_person_id;
64 ELSE
65 RETURN 0;
66 END IF;
67 END;
68 EXCEPTION
69 WHEN OTHERS THEN
70 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
71 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_007.admp_get_match_prsn');
72 IGS_GE_MSG_STACK.ADD;
73 App_Exception.Raise_Exception;
74 END admp_get_match_prsn;
75
76 FUNCTION Admp_Get_Ovrd_Comm(
77 p_person_id IN NUMBER ,
78 p_admission_appl_number IN NUMBER ,
79 p_nominated_course_cd IN VARCHAR ,
80 p_sequence_number IN NUMBER )
81 RETURN VARCHAR2 IS
82 BEGIN -- admp_get_ovrd_comm
83 DECLARE
84 v_ovrd_comm_period VARCHAR2(40);
85 CURSOR c_aa_acai_ci IS
86 SELECT ci1.cal_type ||
87 TO_CHAR(ci1.start_dt, 'DDMMYYYY') ||
88 ci2.cal_type ||
89 TO_CHAR(ci2.start_dt, 'DDMMYYYY')
90 FROM IGS_AD_APPL aa,
91 IGS_AD_PS_APPL_INST acai,
92 IGS_CA_INST ci1,
93 IGS_CA_INST ci2
94 WHERE aa.person_id = p_person_id AND
95 aa.admission_appl_number = p_admission_appl_number AND
96 acai.person_id = aa.person_id AND
97 acai.admission_appl_number = aa.admission_appl_number AND
98 acai.nominated_course_cd = p_nominated_course_cd AND
99 acai.sequence_number = p_sequence_number AND
100 ci1.cal_type = aa.acad_cal_type AND
101 ci1.sequence_number = (
102 SELECT sup_ci_sequence_number
103 FROM IGS_CA_INST_REL
104 WHERE sup_cal_type = aa.acad_cal_type AND
105 sub_cal_type = acai.adm_cal_type AND
106 sub_ci_sequence_number = acai.adm_ci_sequence_number) AND
107 ci2.cal_type = acai.adm_cal_type AND
108 ci2.sequence_number = acai.adm_ci_sequence_number;
109 BEGIN
110 IF p_sequence_number IS NULL THEN
111 RETURN NULL;
112 END IF;
113 OPEN c_aa_acai_ci;
114 FETCH c_aa_acai_ci INTO v_ovrd_comm_period;
115 CLOSE c_aa_acai_ci;
116 RETURN v_ovrd_comm_period;
117 EXCEPTION
118 WHEN OTHERS THEN
119 IF c_aa_acai_ci%ISOPEN THEN
120 CLOSE c_aa_acai_ci;
121 END IF;
122 RAISE;
123 END;
124 END admp_get_ovrd_comm;
125
126 PROCEDURE Admp_Get_Pe_Exists(
127 p_person_id IN NUMBER ,
128 p_effective_dt IN DATE ,
129 p_check_athletics IN BOOLEAN ,
130 p_check_alternate IN BOOLEAN ,
131 p_check_address IN BOOLEAN ,
132 p_check_disability IN BOOLEAN ,
133 p_check_visa IN BOOLEAN ,
134 p_check_finance IN BOOLEAN ,
135 p_check_notes IN BOOLEAN ,
136 p_check_statistics IN BOOLEAN ,
137 p_check_alias IN BOOLEAN ,
138 p_check_tertiary IN BOOLEAN ,
139 p_check_aus_sec_ed IN BOOLEAN ,
140 p_check_os_sec_ed IN BOOLEAN ,
141 p_check_employment IN BOOLEAN ,
142 p_check_membership IN BOOLEAN ,
143 p_check_excurr IN BOOLEAN ,
144 p_athletics_exists OUT NOCOPY BOOLEAN ,
145 p_alternate_exists OUT NOCOPY BOOLEAN ,
146 p_address_exists OUT NOCOPY BOOLEAN ,
147 p_disability_exists OUT NOCOPY BOOLEAN ,
148 p_visa_exists OUT NOCOPY BOOLEAN ,
149 p_finance_exists OUT NOCOPY BOOLEAN ,
150 p_notes_exists OUT NOCOPY BOOLEAN ,
151 p_statistics_exists OUT NOCOPY BOOLEAN ,
152 p_alias_exists OUT NOCOPY BOOLEAN ,
153 p_tertiary_exists OUT NOCOPY BOOLEAN ,
154 p_aus_sec_ed_exists OUT NOCOPY BOOLEAN ,
155 p_os_sec_ed_exists OUT NOCOPY BOOLEAN ,
156 p_employment_exists OUT NOCOPY BOOLEAN ,
157 p_membership_exists OUT NOCOPY BOOLEAN,
158 p_excurr_exists OUT NOCOPY BOOLEAN)
159 IS
160 BEGIN -- admp_get_pe_exists
161 -- Return output parameters indicating whether or not data exists on person
162 -- detail
163 -- tables for the specified person ID.
164 DECLARE
165 v_person_id IGS_PE_PERSON.person_id%TYPE;
166
167 CURSOR c_at IS
168 SELECT person_id
169 FROM igs_pe_athletic_dtl
170 WHERE person_id = p_person_id;
171
172 CURSOR c_api IS
173 SELECT pe_person_id
174 FROM IGS_PE_ALT_PERS_ID
175 WHERE pe_person_id = p_person_id ;
176
177 CURSOR c_person IS
178 SELECT person_id
179 FROM IGS_PE_PERSON_ADDR pa,
180 IGS_CO_ADDR_TYPE adt
181 WHERE pa.person_id = p_person_id AND
182 adt.addr_type = pa.addr_type AND
183 pa.correspondence_ind = 'Y';
184 CURSOR c_pd IS
185 SELECT person_id
186 FROM IGS_PE_PERS_DISABLTY
187 WHERE person_id = p_person_id;
188 CURSOR c_iv IS
189 SELECT person_id
190 FROM IGS_PE_VISA
191 WHERE person_id = p_person_id;
192 CURSOR c_pn IS
193 SELECT person_id
194 FROM IGS_PE_PERS_NOTE
195 WHERE person_id = p_person_id;
196 CURSOR c_ps IS
197 SELECT coun.person_id
198 FROM IGS_PE_EIT coun,
199 HZ_CITIZENSHIP cz,
200 HZ_PERSON_LANGUAGE lang,
201 IGS_PE_EIT state,
202 IGS_PE_VOTE_INFO_ALL voter,
203 IGS_PE_INCOME_TAX_ALL itax
204 WHERE coun.INFORMATION_TYPE = 'PE_STAT_RES_COUNTRY'
205 AND coun.person_id = p_person_id
206 AND cz.PARTY_ID = coun.person_id
207 AND cz.STATUS = 'A'
208 AND lang.PARTY_ID = coun.person_id
209 AND lang.STATUS = 'A'
210 AND state.INFORMATION_TYPE = 'PE_STAT_RES_STATE'
211 AND state.person_id = coun.person_id
212 AND voter.person_id = coun.person_id
213 AND itax.person_id = coun.person_id;
214
215 CURSOR c_pa IS
216 SELECT person_id
217 FROM IGS_PE_PERSON_ALIAS
218 WHERE person_id = p_person_id ;
219 CURSOR c_te IS
220 SELECT person_id
221 FROM IGS_AD_TER_EDU
222 WHERE person_id = p_person_id;
223 CURSOR c_ase IS
224 SELECT person_id
225 FROM IGS_AD_AUS_SEC_EDU
226 WHERE person_id = p_person_id;
227 CURSOR c_ose IS
228 SELECT person_id
229 FROM IGS_AD_OS_SEC_EDU
230 WHERE person_id = p_person_id;
231 CURSOR c_ed IS
232 SELECT person_id
233 FROM IGS_AD_EMP_DTL
234 WHERE person_id = p_person_id;
235
236 CURSOR c_ex IS
237 SELECT PI.PARTY_ID
238 FROM HZ_PERSON_INTEREST PI, IGS_AD_HZ_EXTRACURR_ACT HEA
239 WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID
240 AND PI.PARTY_ID = p_person_id;
241
242
243 BEGIN
244 -- Initialise output parameters
245 p_athletics_exists := FALSE;
246 p_alternate_exists := FALSE;
247 p_address_exists := FALSE;
248 p_disability_exists := FALSE;
249 p_visa_exists := FALSE;
250 p_finance_exists := FALSE;
251 p_notes_exists := FALSE;
252 p_statistics_exists := FALSE;
253 p_alias_exists := FALSE;
254 p_tertiary_exists := FALSE;
255 p_aus_sec_ed_exists := FALSE;
256 p_os_sec_ed_exists := FALSE;
257 p_employment_exists := FALSE;
258 p_membership_exists := FALSE;
259 p_excurr_exists := FALSE;
260 IF p_check_athletics THEN
261 -- Check for the existence of an Alternate person ID record.
262 OPEN c_at;
263 FETCH c_at INTO v_person_id;
264 IF (c_at%FOUND) THEN
265 p_athletics_exists := TRUE;
266 END IF;
267 CLOSE c_at;
268 END IF;
269 IF p_check_alternate THEN
270 -- Check for the existence of an Alternate person ID record.
271 OPEN c_api;
272 FETCH c_api INTO v_person_id;
273 IF (c_api%FOUND) THEN
274 p_alternate_exists := TRUE;
275 END IF;
276 CLOSE c_api;
277 END IF;
278 IF p_check_address THEN
279 -- Check for the existence of an Address record(correspondence).
280 OPEN c_person;
281 FETCH c_person INTO v_person_id;
282 IF (c_person%FOUND) THEN
283 p_address_exists := TRUE;
284 END IF;
285 CLOSE c_person;
286 END IF;
287 IF p_check_disability THEN
288 -- Check for the existence of a person Disability record.
289 OPEN c_pd;
290 FETCH c_pd INTO v_person_id;
291 IF (c_pd%FOUND) THEN
292 p_disability_exists := TRUE;
293 END IF;
294 CLOSE c_pd;
295 END IF;
296 IF p_check_visa THEN
297 -- Check for the existence of an International Visa record.
298 OPEN c_iv;
299 FETCH c_iv INTO v_person_id;
300 IF (c_iv%FOUND) THEN
301 p_visa_exists := TRUE;
302 END IF;
303 CLOSE c_iv;
304 END IF;
305
306 IF p_check_notes THEN
307 -- Check for the existence of a person Notes record.
308 OPEN c_pn;
309 FETCH c_pn INTO v_person_id;
310 IF (c_pn%FOUND) THEN
311 p_notes_exists := TRUE;
312 END IF;
313 CLOSE c_pn;
314 END IF;
315 IF p_check_statistics THEN
316 -- Check for the existence of an person Statistics record.
317 OPEN c_ps;
318 FETCH c_ps INTO v_person_id;
319 IF (c_ps%FOUND) THEN
320 p_statistics_exists := TRUE;
321 END IF;
322 CLOSE c_ps;
323 END IF;
324 IF p_check_alias THEN
325 -- Check for the existence of an person Alias record.
326 OPEN c_pa;
327 FETCH c_pa INTO v_person_id;
328 IF (c_pa%FOUND) THEN
329 p_alias_exists := TRUE;
330 END IF;
331 CLOSE c_pa;
332 END IF;
333 IF p_check_tertiary THEN
334 -- Check for the existence of an Tertiary Education record.
335 OPEN c_te;
336 FETCH c_te INTO v_person_id;
337 IF (c_te%FOUND) THEN
338 p_tertiary_exists := TRUE;
339 END IF;
340 CLOSE c_te;
341 END IF;
342 IF p_check_aus_sec_ed THEN
343 -- Check for the existence of an Australian Secondary Education record.
344 OPEN c_ase;
345 FETCH c_ase INTO v_person_id;
346 IF (c_ase%FOUND) THEN
347 p_aus_sec_ed_exists := TRUE;
348 END IF;
349 CLOSE c_ase;
350 END IF;
351 IF p_check_os_sec_ed THEN
352 -- Check for the existence of an Overseas Secondary Education record.
353 OPEN c_ose;
354 FETCH c_ose INTO v_person_id;
355 IF (c_ose%FOUND) THEN
356 p_os_sec_ed_exists := TRUE;
357 END IF;
358 CLOSE c_ose;
359 END IF;
360 IF p_check_employment THEN
361 -- Check for the existence of a person Finance record.
362 OPEN c_ed;
363 FETCH c_ed INTO v_person_id;
364 IF (c_ed%FOUND) THEN
365 p_employment_exists := TRUE;
366 END IF;
367 CLOSE c_ed;
368 END IF;
369 IF p_check_excurr THEN
370 -- Check for the existence of a person Activities record.
371 OPEN c_ex;
372 FETCH c_ex INTO v_person_id;
373 IF (c_ex%FOUND) THEN
374 p_excurr_exists := TRUE;
375 END IF;
376 CLOSE c_ex;
377 END IF;
378
379 END;
380 EXCEPTION
381 WHEN OTHERS THEN
382 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
383 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_007.admp_get_pe_exists');
384 IGS_GE_MSG_STACK.ADD;
385 App_Exception.Raise_Exception;
386 END admp_get_pe_exists;
387
388 FUNCTION Admp_Get_Resp_Dt(
389 p_course_cd IN VARCHAR2 ,
390 p_version_number IN NUMBER ,
391 p_acad_cal_type IN VARCHAR2 ,
392 p_location_cd IN VARCHAR2 ,
393 p_attendance_mode IN VARCHAR2 ,
394 p_attendance_type IN VARCHAR2 ,
395 p_admission_cat IN VARCHAR2 ,
396 p_admission_process_type IN VARCHAR2 ,
397 p_adm_cal_type IN VARCHAR2 ,
398 p_adm_ci_sequence_number IN NUMBER ,
399 p_offer_dt IN DATE )
400 RETURN DATE IS
401 -- admp_get_resp_dt
402 -- Calculate the offer response date. If the date cannot be derived return
403 -- NULL, otherwise return the derived date.
407 v_offer_resp_dt DATE;
404 v_offer_resp_offset IGS_AD_PRCS_CAT.offer_response_offset%TYPE;
405 v_appl_offer_resp_dt_alias IGS_AD_CAL_CONF.adm_appl_offer_resp_dt_alias%TYPE;
406 v_offer_resp_dt_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
408 v_apc_found BOOLEAN;
409 v_dai_sequence_number IGS_AD_PECRS_OFOP_DT.dai_sequence_number%TYPE ;
410 v_apcood_found BOOLEAN := FALSE;
411 v_curr_component_ctr NUMBER := 0;
412 v_high_component_ctr NUMBER := 0;
413
414 CURSOR c_apc IS
415 SELECT offer_response_offset
416 FROM IGS_AD_PRCS_CAT
417 WHERE admission_cat = p_admission_cat AND
418 s_admission_process_type = p_admission_process_type;
419
420 CURSOR c_sacc IS
421 SELECT adm_appl_offer_resp_dt_alias ,
422 adm_appl_due_dt_alias,
423 adm_appl_final_dt_alias
424 FROM IGS_AD_CAL_CONF
425 WHERE s_control_num = 1;
426
427 CURSOR c_apcood(cp_appl_offer_resp_dt_alias
428 IGS_AD_CAL_CONF.adm_appl_offer_resp_dt_alias%TYPE) IS
429 SELECT apcood.s_admission_process_type,
430 apcood.course_cd,
431 apcood.version_number,
432 apcood.acad_cal_type,
433 apcood.location_cd,
434 apcood.attendance_mode,
435 apcood.attendance_type,
436 apcood.dai_sequence_number
437 FROM IGS_AD_PECRS_OFOP_DT apcood
438 WHERE apcood.adm_cal_type = p_adm_cal_type AND
439 apcood.adm_ci_sequence_number = p_adm_ci_sequence_number AND
440 apcood.admission_cat = p_admission_cat AND
441 apcood.dt_alias = cp_appl_offer_resp_dt_alias;
442
443 CURSOR c_daiv IS
444 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
445 FROM IGS_CA_DA_INST daiv,
446 IGS_AD_PERD_AD_CAT apac
447 WHERE daiv.dt_alias = v_appl_offer_resp_dt_alias AND
448 apac.adm_cal_type = p_adm_cal_type AND
449 apac.adm_ci_sequence_number = p_adm_ci_sequence_number AND
450 apac.admission_cat = p_admission_cat AND
451 apac.adm_cal_type = daiv.cal_type AND
452 apac.adm_ci_sequence_number = daiv.ci_sequence_number AND
453 NOT EXISTS(
454 SELECT 'x'
455 FROM IGS_AD_PECRS_OFOP_DT apcood
456 WHERE apcood.adm_cal_type = apac.adm_cal_type AND
457 apcood.adm_ci_sequence_number = apac.adm_ci_sequence_number AND
458 apcood.dt_alias = daiv.dt_alias AND
459 apcood.dai_sequence_number = daiv.sequence_number)
460 ORDER BY 1 desc;
461
462 CURSOR c_daiv2 IS
463 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
464 FROM IGS_CA_DA_INST daiv
465 WHERE daiv.cal_type = p_adm_cal_type AND
466 daiv.ci_sequence_number = p_adm_ci_sequence_number AND
467 daiv.dt_alias = v_appl_offer_resp_dt_alias AND
468 daiv.sequence_number = v_dai_sequence_number;
469
470 l_sacc c_sacc%ROWTYPE;
471 -- Offer Response Date to be defaulted can be derived from
472 --1. Admission period Override -> Date alias value for the
473 -- Admission period and Admission Category at Process type / Program Offering level
474 --2. Calendar setup and Date alias -> Date alias value for the Admission period
475 --3. APC -> Offset to offer date (offer date is defaulted to system date) for the APC
476
477 --1. Check (1). If available, also check to see if greater than system date. If so use (1). Else go to step 2.
478 --2. Check (3). If available, also check to see if greater than system date. If so use (2). Else go to step 3.
479 --3. Check (2). If available, also check to see if greater than system date. If so use (3). Else go to step 4.
480 --4. Default in as null.
481 BEGIN
482 --Find whether the offer response date alias set up in admission calendars set up.
483 -- Get offer response date alias
484 OPEN c_sacc;
485 FETCH c_sacc INTO l_sacc;
486 v_appl_offer_resp_dt_alias := l_sacc.adm_appl_offer_resp_dt_alias;
487 IF (c_sacc%NOTFOUND OR
488 v_appl_offer_resp_dt_alias IS NULL) THEN
489 v_offer_resp_dt_alias_val := NULL;
490 ELSE
491 IF p_admission_process_type IS NULL AND
492 p_course_cd IS NULL AND
493 p_version_number IS NULL AND
494 p_acad_cal_type IS NULL AND
495 p_location_cd IS NULL AND
496 p_attendance_mode IS NULL AND
497 p_attendance_type IS NULL THEN
498 RETURN NULL;
499 END IF;
500 --Find whether the offer response date alias is overridden in Admission overrides form.
501 FOR v_apcood_rec IN c_apcood(v_appl_offer_resp_dt_alias) LOOP
502 v_apcood_found := TRUE;
503 --Check that the record firstly is valid for the parameters
504 IF ((v_apcood_rec.s_admission_process_type IS NULL OR
505 v_apcood_rec.s_admission_process_type = p_admission_process_type) AND
506 (v_apcood_rec.course_cd IS NULL OR
507 (v_apcood_rec.course_cd = p_course_cd AND
508 v_apcood_rec.version_number = p_version_number AND
512 (v_apcood_rec.attendance_mode IS NULL OR
509 v_apcood_rec.acad_cal_type = p_acad_cal_type)) AND
510 (v_apcood_rec.location_cd IS NULL OR
511 v_apcood_rec.location_cd = p_location_cd) AND
513 v_apcood_rec.attendance_mode = p_attendance_mode) AND
514 (v_apcood_rec.attendance_type IS NULL OR
515 v_apcood_rec.attendance_type = p_attendance_type)) THEN
516 --Match on the components and save the IGS_CA_DA_INST_V key for the
517 --Record that matches with the highest number of components
518 IF (v_apcood_rec.s_admission_process_type IS NOT NULL) THEN
519 IF (v_apcood_rec.s_admission_process_type = p_admission_process_type) THEN
520 v_curr_component_ctr := v_curr_component_ctr + 1;
521 END IF;
522 END IF;
523 IF (v_apcood_rec.course_cd IS NOT NULL) THEN
524 IF (v_apcood_rec.course_cd = p_course_cd AND
525 v_apcood_rec.version_number = p_version_number AND
526 v_apcood_rec.acad_cal_type = p_acad_cal_type) THEN
527 v_curr_component_ctr := v_curr_component_ctr + 1;
528 END IF;
529 END IF;
530 IF (v_apcood_rec.location_cd IS NOT NULL) THEN
531 IF (v_apcood_rec.location_cd = p_location_cd) THEN
532 v_curr_component_ctr := v_curr_component_ctr + 1;
533 END IF;
534 END IF;
535 IF (v_apcood_rec.attendance_mode IS NOT NULL) THEN
536 IF (v_apcood_rec.attendance_mode = p_attendance_mode) THEN
537 v_curr_component_ctr := v_curr_component_ctr + 1;
538 END IF;
539 END IF;
540 IF (v_apcood_rec.attendance_type IS NOT NULL) THEN
541 IF (v_apcood_rec.attendance_type = p_attendance_type) THEN
542 v_curr_component_ctr := v_curr_component_ctr + 1;
543 END IF;
544 END IF;
545 --If this record has the most number of matches then we want to use
546 --its dai_sequence_number
547 IF (v_curr_component_ctr > v_high_component_ctr) THEN
548 v_high_component_ctr := v_curr_component_ctr;
549 v_dai_sequence_number := v_apcood_rec.dai_sequence_number;
550 END IF;
551 v_curr_component_ctr := 0;
552 END IF;
553 END LOOP;
554 IF v_apcood_found AND v_dai_sequence_number IS NOT NULL THEN
555 OPEN c_daiv2;
556 FETCH c_daiv2 INTO v_offer_resp_dt_alias_val;
557 IF (c_daiv2%NOTFOUND) THEN
558 v_offer_resp_dt_alias_val := NULL;
559 END IF;
560 CLOSE c_daiv2;
561 ELSE
562 v_offer_resp_dt_alias_val := NULL;
563 END IF;
564 END IF;
565 CLOSE c_sacc;
566
567 IF v_offer_resp_dt_alias_val IS NOT NULL AND v_offer_resp_dt_alias_val > TRUNC(SYSDATE) THEN
568 RETURN v_offer_resp_dt_alias_val;
569 ELSE
570 v_offer_resp_dt_alias_val := NULL;
571 END IF;
572
573 OPEN c_apc;
574 FETCH c_apc INTO v_offer_resp_offset;
575 IF (c_apc%NOTFOUND OR
576 v_offer_resp_offset IS NULL) THEN
577 v_apc_found := FALSE;
578 ELSE
579 v_apc_found := TRUE;
580 END IF;
581 CLOSE c_apc;
582
583 IF v_offer_resp_dt_alias_val IS NULL AND v_apc_found THEN
584 v_offer_resp_dt_alias_val := p_offer_dt + v_offer_resp_offset;
585 IF v_offer_resp_dt_alias_val > TRUNC(SYSDATE) THEN
586 RETURN v_offer_resp_dt_alias_val;
587 ELSE
588 v_offer_resp_dt_alias_val := NULL;
589 END IF;
590 END IF;
591 IF v_appl_offer_resp_dt_alias IS NOT NULL AND v_offer_resp_dt_alias_val IS NULL THEN
592 OPEN c_daiv;
593 FETCH c_daiv INTO v_offer_resp_dt_alias_val;
594 CLOSE c_daiv;
595 IF v_offer_resp_dt_alias_val > TRUNC(SYSDATE) THEN
596 RETURN v_offer_resp_dt_alias_val;
597 ELSE
598 v_offer_resp_dt_alias_val := NULL;
599 END IF;
600 END IF;
601
602 RETURN v_offer_resp_dt_alias_val;
603 EXCEPTION
604 WHEN OTHERS THEN
605 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
606 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_007.admp_get_resp_dt');
607 IGS_GE_MSG_STACK.ADD;
608 App_Exception.Raise_Exception;
609 END admp_get_resp_dt;
610
611
612 FUNCTION Admp_Get_Saas(
613 p_adm_appl_status IN VARCHAR2 )
614 RETURN VARCHAR2 IS
615 BEGIN --admp_get_saas
616 --Get the s_adm_appl_status for a specified adm_appl_status
617 DECLARE
618 v_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE;
619 CURSOR c_aas IS
620 SELECT s_adm_appl_status
621 FROM IGS_AD_APPL_STAT
622 WHERE adm_appl_status = p_adm_appl_status;
623 BEGIN
624 --initialise v_adm_appl_status
625 v_adm_appl_status := NULL;
626 OPEN c_aas;
627 FETCH c_aas INTO v_adm_appl_status;
628 CLOSE c_aas;
629 RETURN v_adm_appl_status;
630 END;
631 EXCEPTION
632 WHEN OTHERS THEN
633 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
634 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_007.admp_get_saas');
635 IGS_GE_MSG_STACK.ADD;
636 App_Exception.Raise_Exception;
637 END admp_get_saas;
638
639 FUNCTION Admp_Get_Sacos(
640 p_adm_cndtnl_offer_status IN VARCHAR2 )
641 RETURN VARCHAR2 IS
642 BEGIN --admp_get_sacos
643 --Get the s_adm_cndtnl_offer_status for a specified adm_cndtnl_offer_status
644 DECLARE
645 v_adm_offer_status IGS_AD_CNDNL_OFRSTAT.s_adm_cndtnl_offer_status%TYPE;
646 CURSOR c_acos IS
647 SELECT s_adm_cndtnl_offer_status
648 FROM IGS_AD_CNDNL_OFRSTAT
649 WHERE adm_cndtnl_offer_status = p_adm_cndtnl_offer_status;
650 BEGIN
651 --initialise v_adm_offer_status
652 v_adm_offer_status := NULL;
653 OPEN c_acos ;
654 FETCH c_acos INTO v_adm_offer_status;
655 CLOSE c_acos ;
656 RETURN v_adm_offer_status;
657 END;
658
659 END admp_get_sacos;
660
661 FUNCTION Admp_Get_Sads(
662 p_adm_doc_status IN VARCHAR2 )
663 RETURN VARCHAR2 IS
664 BEGIN --admp_get_sads
665 --Get the s_adm_doc_status for a specified adm_doc_status
666 DECLARE
667 v_s_adm_doc_status IGS_AD_DOC_STAT.s_adm_doc_status%TYPE;
668 CURSOR c_ads IS
669 SELECT s_adm_doc_status
670 FROM IGS_AD_DOC_STAT
671 WHERE adm_doc_status = p_adm_doc_status;
672 BEGIN
673 --initialise v_s_adm_doc_status
674 v_s_adm_doc_status := NULL;
675 OPEN c_ads ;
676 FETCH c_ads INTO v_s_adm_doc_status;
677 CLOSE c_ads ;
678 RETURN v_s_adm_doc_status;
679 END;
680 END admp_get_sads;
681
682 FUNCTION Admp_Get_Saeqs(
683 p_adm_entry_qual_status IN VARCHAR2 )
684 RETURN VARCHAR2 IS
685 BEGIN --admp_get_saeqs
686 --Get the s_adm_entry_qual_status for a specified adm_entry_qual_status.
687 DECLARE
688 v_qual_status IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE;
689 CURSOR c_aeqs IS
690 SELECT s_adm_entry_qual_status
691 FROM IGS_AD_ENT_QF_STAT
692 WHERE adm_entry_qual_status = p_adm_entry_qual_status;
693 BEGIN
694 --initialise v_s_adm_doc_status
695 v_qual_status := NULL;
696 OPEN c_aeqs ;
697 FETCH c_aeqs INTO v_qual_status;
698 CLOSE c_aeqs ;
699 RETURN v_qual_status;
700 END;
701 END admp_get_saeqs;
702
703 END igs_ad_gen_007;
[Home] [Help]