1 PACKAGE BODY IGS_EN_GEN_003 AS
2 /* $Header: IGSEN03B.pls 120.5 2006/01/23 01:58:08 smaddali ship $ */
3 /* change history
4 rvangala 27-SEP-2005 Bug 4335046, modified UPD_MAT_MRADM_CAT_TERMS
5 pkpatel 26-MAR-2003 Bug 2261717
6 Tuned functions Enrp_Get_Encmbrd_Ind,
7 npalanis 11-SEP-2002 Bug - 2608360
8 the pe code classes for religion , soci eco status and further education
9 has been transferred to lookups so new columns are added in igs_pe_stat_details table
10 to store codes therefore the tbh calls is modified.
11 ssawhney 30-APR V2API OVN change, igs_pe_stat_pkg signature modified.
12
13 */
14
15 /*-------------------------------------------------------------------------------------------------------------
16 Following package variable keeps the enrp_get_enr_cat function parameters values.
17 -------------------------------------------------------------------------------------------------------------*/
18 pkg_person_id igs_pe_person.person_id%TYPE;
19 pkg_course_cd igs_en_stdnt_ps_att.course_cd%TYPE;
20 pkg_cal_type igs_ca_inst.cal_type%TYPE;
21 pkg_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
22 pkg_session_enrolment_cat igs_as_sc_atmpt_enr.enrolment_cat%TYPE;
23 pkg_enrol_cal_type igs_ca_inst.cal_type%TYPE;
24 pkg_enrol_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
25 pkg_commencement_type VARCHAR2(20);
26 pkg_enroll_catg VARCHAR2(200);
27 pkg_enr_categories VARCHAR2(200);
28 pkg_course_att_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
29 -------------------------------------------------------------------------------------------------------------
30
31 Function Enrp_Get_Dflt_Dr(
32 p_description OUT NOCOPY VARCHAR2 )
33 RETURN VARCHAR2 AS
34 BEGIN
35 DECLARE
36 v_dis_reason_cd IGS_EN_DCNT_REASONCD.discontinuation_reason_cd%TYPE;
37 v_description IGS_EN_DCNT_REASONCD.description%TYPE;
38
39 CURSOR c_drcd IS
40 SELECT discontinuation_reason_cd,
41 description
42 FROM IGS_EN_DCNT_REASONCD
43 WHERE dflt_ind = 'Y' AND
44 closed_ind = 'N';
45 BEGIN
46 -- This module retrieves the default discontinuation
47 -- reason code for a student IGS_PS_COURSE attempt if the
48 -- discontinuation date has been specified
49 OPEN c_drcd;
50 FETCH c_drcd INTO v_dis_reason_cd, v_description;
51 IF (c_drcd%FOUND) THEN
52 CLOSE c_drcd;
53 p_description := v_description;
54 RETURN v_dis_reason_cd;
55 ELSE
56 CLOSE c_drcd;
57 p_description := NULL;
58 RETURN NULL;
59 END IF;
60 EXCEPTION
61 WHEN OTHERS THEN
62 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
63 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_003.enrp_get_dflt_dr');
64 IGS_GE_MSG_STACK.ADD;
65 App_Exception.Raise_Exception;
66 END;
67 END enrp_get_dflt_dr;
68
69
70 Function Enrp_Get_Dflt_Fs(
71 p_course_cd IN VARCHAR2 ,
72 p_version_number IN NUMBER )
73 RETURN VARCHAR2 AS
74 BEGIN
75 DECLARE
76 CURSOR c_fund_source_rest(
77 cp_course_cd IGS_PS_VER.course_cd%TYPE,
78 cp_version_number IGS_PS_VER.version_number%TYPE) IS
79 SELECT funding_source
80 FROM IGS_FI_FND_SRC_RSTN
81 WHERE course_cd = cp_course_cd AND
82 version_number = cp_version_number AND
83 dflt_ind = 'Y';
84 BEGIN
85 -- gets the default funding source for a IGS_PS_COURSE version if one
86 -- has been specified
87 FOR v_fund_source_rest_rec IN c_fund_source_rest(
88 p_course_cd,
89 p_version_number)
90 LOOP
91 RETURN v_fund_source_rest_rec.funding_source;
92 END LOOP;
93 RETURN NULL;
94 EXCEPTION
95 WHEN OTHERS THEN
96 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
97 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_003.enrp_get_dflt_fs');
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END;
101 END enrp_get_dflt_fs;
102
103
104 Function Enrp_Get_Ecps_Group(
105 p_s_enrolment_step_type IN VARCHAR2 )
106 RETURN VARCHAR2 AS
107 v_step_group_type IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE;
108 CURSOR c_sest (cp_s_enrolment_step_type
109 IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE) IS
110 SELECT step_group_type
111 FROM IGS_LOOKUPS_VIEW
112 WHERE LOOKUP_CODE = cp_s_enrolment_step_type AND
113 LOOKUP_TYPE = 'ENROLMENT_STEP_TYPE';
114 BEGIN
115 v_step_group_type := NULL;
116 OPEN c_sest(p_s_enrolment_step_type);
117 FETCH c_sest INTO v_step_group_type;
118 CLOSE c_sest;
119 RETURN v_step_group_type;
120 END;
121
122
123 FUNCTION Enrp_Get_Encmbrd_Ind(
124 p_person_id IN NUMBER )
125 RETURN VARCHAR2 AS
126 /* change history
127 WHO WHEN WHAT
128 pkpatel 26-MAR-2003 Bug 2261717
129 Filter the query for efficiency. Removed the COUNT(*) and
130 replaced TRUNC(SYSDATE) with l_sysdate.
131 */
132
133 cst_yes CONSTANT VARCHAR2(1) := 'Y';
134 cst_no CONSTANT VARCHAR2(1) := 'N';
135 v_output VARCHAR2(1);
136 v_count NUMBER;
137 l_sysdate DATE := TRUNC(SYSDATE);
138 --(pathipat) Modified cursor for performance issues. Bug No: 2432563
139 -- Removed variables not used.
140
141 CURSOR c_prsn_encumb
142 (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
143 SELECT 1
144 FROM IGS_PE_PERS_ENCUMB
145 WHERE person_id = cp_person_id AND
146 (l_sysdate BETWEEN start_dt AND (expiry_dt - 1) OR
147 (expiry_dt IS NULL AND start_dt <= l_sysdate)) ;
148
149
150 BEGIN
151
152 -- This module determines whether or not a IGS_PE_PERSON is
153 -- an encumbranced student and returns the appropriate
154 -- indicator
155
156 -- (pathipat) 'For' loop replaced with the following code, Bug: 2432563
157 -- Conditions checked for in the loop have been done in the cursor itself
158
159 OPEN c_prsn_encumb (p_person_id);
160 FETCH c_prsn_encumb INTO v_count;
161 IF c_prsn_encumb%FOUND THEN
162 -- return Y - the student has encumbrances
163 CLOSE c_prsn_encumb;
164 v_output := cst_yes;
165 return v_output;
166 END IF;
167 CLOSE c_prsn_encumb;
168
169 -- return N - the student has no encumbrances
170 v_output := cst_no;
171 return v_output;
172
173 END enrp_get_encmbrd_ind;
174
175
176 Function Enrp_Get_Enr_Cat(
177 p_person_id IN NUMBER ,
178 p_course_cd IN VARCHAR2 ,
179 p_cal_type IN VARCHAR2 ,
180 p_ci_sequence_number IN NUMBER ,
181 p_session_enrolment_cat IN VARCHAR2 ,
182 p_enrol_cal_type OUT NOCOPY VARCHAR2 ,
183 p_enrol_ci_sequence_number OUT NOCOPY NUMBER ,
184 p_commencement_type OUT NOCOPY VARCHAR2,
185 p_enr_categories OUT NOCOPY VARCHAR2)
186 -------------------------------------------------------------------------------------------
187 -- This routine will determine the most appropriate enrolment category(ies) for
188 -- a nominated student (and optionally IGS_PS_COURSE attempt) for a nominated
189 -- academic period.
190 -- If the routine is being called for a specific IGS_PS_COURSE, it will also return
191 -- the enrolment period calendar instance in which the match was found
192 -- (this cannot be done when a IGS_PS_COURSE isn?t specified as there may be many
193 -- periods).
194 -- If a IGS_PS_COURSE attempt is not specified the routine may return multiple
195 -- enrolment categories,
196 -- where the student may be eligible in more than one IGS_PS_COURSE. These will be
197 -- returned in the form CATEGORY1,CATEGORY2,ETC?
198 -- The routine will determine the enrolment category(ies) by looking firstly
199 -- for one or more eligible IGS_PS_COURSE attempts (matching the specified
200 -- parameters); then pre-enrolment detail (IGS_AS_SC_ATMPT_ENR records)
201 -- will be searched for matching the specified academic period (i.e. the
202 -- enrolment calendar is a subordinate of the academic calendar).
203 -- If this is not found and the passed session enrolment category is set then
204 -- it will be used.
205 -- If not set, the fallback is to take the enrolment category from the latest
206 -- pre-enrolment detail for each eligible IGS_PS_COURSE.
207 -- It is possible that this routine will not find a match ,in that case find the enrolment category
208 -- at the program level as a part of bug#2043044 .IF stillnot found then return NULL
209 -- enrolment category.
210 -- The calling routine will be responsible for handling this.
211 --Change History:
212 --Who When What
213 --kkillams 06-06-2003 Added new validation, check the input parameter values with
214 -- newly created package level variables, if values are same then
215 -- return the vaules stored in the package variable else do the
216 -- validations. W.r.t. bug no.2829270
217 --ptandon 14-08-2003 Corrected logic to return concatenated string of Enrolment Categories
218 -- as OUT parameter(p_enr_categories) if the student is attempting
219 -- program under more than one Enrolment Category. Bug# 2968590
220 -------------------------------------------------------------------------------------------
221 RETURN VARCHAR2 AS
222 BEGIN
223 DECLARE
224 v_applicable_enrolment_cat VARCHAR2(255);
225 v_crs_applicable_enrolment_cat IGS_AS_SC_ATMPT_ENR.enrolment_cat%TYPE;
226 v_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE;
227 v_first_record BOOLEAN;
228 v_first_time BOOLEAN;
229 v_set_cal BOOLEAN;
230 v_crs_commencement_type VARCHAR2(10);
231 v_commencement_type VARCHAR2(10);
232 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
233 v_enrolment_cat IGS_PS_TYPE.enrolment_cat%TYPE;
234 l_position NUMBER;
235 v_course_att_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
236
237 CURSOR c_sca(
238 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
239 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
240 SELECT person_id,
241 course_cd,
242 version_number,
243 course_attempt_status,
244 student_confirmed_ind
245 FROM IGS_EN_STDNT_PS_ATT
246 WHERE ((cp_course_cd IS NULL AND person_id = cp_person_id) OR
247 (cp_course_cd IS NOT NULL AND course_cd = cp_course_cd AND
248 person_id = cp_person_id));
249 CURSOR c_scae_ci(
250 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
251 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
252 SELECT IGS_AS_SC_ATMPT_ENR.enrolment_cat,
253 IGS_AS_SC_ATMPT_ENR.cal_type,
254 IGS_AS_SC_ATMPT_ENR.ci_sequence_number,
255 IGS_CA_INST.start_dt,
256 IGS_CA_INST.end_dt
257 FROM IGS_AS_SC_ATMPT_ENR,
258 IGS_CA_INST
259 WHERE IGS_AS_SC_ATMPT_ENR.person_id = cp_person_id AND
260 IGS_AS_SC_ATMPT_ENR.course_cd = cp_course_cd AND
261 IGS_AS_SC_ATMPT_ENR.cal_type = IGS_CA_INST.cal_type AND
262 IGS_AS_SC_ATMPT_ENR.ci_sequence_number = IGS_CA_INST.sequence_number
263 ORDER BY IGS_CA_INST.start_dt desc;
264 CURSOR c_cir(
265 cp_sup_cal_type IGS_CA_INST.cal_type%TYPE,
266 cp_sup_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
267 cp_sub_cal_type IGS_CA_INST.cal_type%TYPE,
268 cp_sub_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
269 SELECT sub_cal_type
270 FROM IGS_CA_INST_REL
271 WHERE sup_cal_type = cp_sup_cal_type AND
272 sup_ci_sequence_number = cp_sup_ci_sequence_number AND
273 sub_cal_type = cp_sub_cal_type AND
274 sub_ci_sequence_number = cp_sub_ci_sequence_number;
275
276 --To get the enrollment category at the program level if the enrollment category is NULL
277 -- as a part of self service setup DLD build enh bug#2043044
278 CURSOR c_pst(
279 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
280 cp_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
281 SELECT ENROLMENT_CAT
282 FROM IGS_PS_TYPE pt,
283 IGS_PS_VER pv
284 WHERE pv.course_cd = p_course_cd AND
285 pv.course_type = pt.course_type AND
286 pv.version_number = cp_version_number ;
287
288 CURSOR c_scas( cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
289 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
290 SELECT course_attempt_status
291 FROM IGS_EN_STDNT_PS_ATT
295 BEGIN
292 WHERE PERSON_ID = cp_person_id
293 AND COURSE_CD = cp_course_cd;
294
296 OPEN c_scas(p_person_id, p_course_cd);
297 FETCH c_scas INTO v_course_att_status;
298 CLOSE c_scas;
299
300 IF ((pkg_person_id = p_person_id) AND
301 (pkg_course_cd = p_course_cd) AND
302 (pkg_cal_type = p_cal_type ) AND
303 (pkg_course_att_status = v_course_att_status ) AND
304 (pkg_ci_sequence_number = p_ci_sequence_number) AND
305 ((pkg_session_enrolment_cat = p_session_enrolment_cat) OR
306 (p_session_enrolment_cat IS NULL AND pkg_session_enrolment_cat IS NULL))) THEN
307 p_enrol_cal_type := pkg_enrol_cal_type;
308 p_enrol_ci_sequence_number := pkg_enrol_ci_sequence_number;
309 p_commencement_type := pkg_commencement_type;
310 p_enr_categories := pkg_enr_categories;
311 RETURN pkg_enroll_catg;
312 ELSE
313 pkg_enrol_cal_type := NULL;
314 pkg_enrol_ci_sequence_number := NULL;
315 pkg_commencement_type := NULL;
316 pkg_enroll_catg := NULL;
317 pkg_enr_categories := NULL;
318 pkg_person_id := p_person_id;
319 pkg_course_cd := p_course_cd;
320 pkg_cal_type := p_cal_type;
321 pkg_ci_sequence_number := p_ci_sequence_number;
322 pkg_session_enrolment_cat := p_session_enrolment_cat;
323 pkg_course_att_status := v_course_att_status;
324 END IF;
325
326 p_enrol_cal_type := NULL;
327 p_enrol_ci_sequence_number := NULL;
328 p_commencement_type := NULL;
329 v_commencement_type := NULL;
330 v_applicable_enrolment_cat := NULL;
331 v_first_time := TRUE;
332 FOR v_sca_rec IN c_sca(
333 p_person_id,
334 p_course_cd)
335 LOOP
336 -- Call routine to obtain IGS_PS_COURSE commencement_type
337 IF IGS_EN_GEN_006.ENRP_GET_SCA_COMM(v_sca_rec.person_id,
338 v_sca_rec.course_cd,
339 v_sca_rec.student_confirmed_ind,
340 SYSDATE) THEN
341 IF v_commencement_type IS NULL THEN
342 v_commencement_type := 'NEW';
343 ELSIF v_commencement_type = 'RETURN' THEN
344 v_commencement_type := 'BOTH';
345 END IF;
346 ELSE
347 IF v_commencement_type IS NULL THEN
348 v_commencement_type := 'RETURN';
349 ELSIF v_commencement_type = 'NEW' THEN
350 v_commencement_type := 'BOTH';
351 END IF;
352 END IF;
353 --To get the version number as a part of bug 2043044
354 v_version_number := v_sca_rec.version_number;
355
356 v_crs_applicable_enrolment_cat := NULL;
357 v_first_record := TRUE;
358 v_set_cal := TRUE;
359 FOR v_scae_ci_rec IN c_scae_ci(
360 v_sca_rec.person_id,
361 v_sca_rec.course_cd)
362 LOOP
363 IF(v_first_record AND p_session_enrolment_cat IS NULL) THEN
364 v_crs_applicable_enrolment_cat := v_scae_ci_rec.enrolment_cat;
365 IF(p_course_cd IS NOT NULL) THEN
366 p_enrol_cal_type := v_scae_ci_rec.cal_type;
367 p_enrol_ci_sequence_number := v_scae_ci_rec.ci_sequence_number;
368 END IF;
369 END IF;
370 v_first_record := FALSE;
371 OPEN c_cir(
372 p_cal_type,
373 p_ci_sequence_number,
374 v_scae_ci_rec.cal_type,
375 v_scae_ci_rec.ci_sequence_number);
376 FETCH c_cir INTO v_sub_cal_type;
377 IF (c_cir%FOUND) THEN
378 v_crs_applicable_enrolment_cat := v_scae_ci_rec.enrolment_cat;
379 IF(p_course_cd IS NOT NULL and v_set_cal) THEN
380 p_enrol_cal_type := v_scae_ci_rec.cal_type;
381 p_enrol_ci_sequence_number := v_scae_ci_rec.ci_sequence_number;
382 END IF;
383 CLOSE c_cir;
384 v_set_cal := FALSE;
385 END IF;
386 IF(c_cir%ISOPEN) THEN
387 CLOSE c_cir;
388 END IF;
389 IF(v_crs_applicable_enrolment_cat IS NULL AND
390 p_session_enrolment_cat IS NOT NULL) THEN
391 v_crs_applicable_enrolment_cat := p_session_enrolment_cat;
392 END IF;
393 IF(v_crs_applicable_enrolment_cat IS NOT NULL AND v_first_time) THEN
394 v_first_time := FALSE;
398 (INSTR(v_applicable_enrolment_cat, v_crs_applicable_enrolment_cat) = 0))
395 v_applicable_enrolment_cat := v_applicable_enrolment_cat||
396 v_crs_applicable_enrolment_cat;
397 ELSIF((v_crs_applicable_enrolment_cat IS NOT NULL) AND (NOT v_first_time) AND
399 THEN
400 v_applicable_enrolment_cat := v_applicable_enrolment_cat||','||
401 v_crs_applicable_enrolment_cat;
402 END IF;
403 END LOOP;
404 END LOOP;
405 IF v_applicable_enrolment_cat IS NULL AND
406 p_session_enrolment_cat IS NOT NULL THEN
407 v_applicable_enrolment_cat := p_session_enrolment_cat;
408 END IF;
409 -- If commencement type cannot be determined the default to BOTH.
410 IF v_commencement_type IS NOT NULL THEN
411 p_commencement_type := v_commencement_type;
412 ELSE
413 p_commencement_type := 'BOTH';
414 END IF;
415 --if value of v_applicable_enrolment_cat is NULL then fetch enrolment category at the program level as a part of self service setup dld
416 -- enh number #2043044
417
418 IF v_applicable_enrolment_cat IS NULL THEN
419 OPEN c_pst(p_course_cd, v_version_number );
420 FETCH c_pst into v_applicable_enrolment_cat;
421 CLOSE c_pst;
422 END IF;
423
424 pkg_enrol_cal_type := p_enrol_cal_type;
425 pkg_enrol_ci_sequence_number := p_enrol_ci_sequence_number;
426 pkg_commencement_type := p_commencement_type;
427 pkg_enroll_catg := v_applicable_enrolment_cat;
428 pkg_enr_categories := v_applicable_enrolment_cat;
429
430 p_enr_categories := v_applicable_enrolment_cat;
431 l_position := INSTR(v_applicable_enrolment_cat,',');
432 IF l_position <> 0 THEN
433 pkg_enroll_catg := SUBSTR(v_applicable_enrolment_cat,1,l_position-1);
434 RETURN pkg_enroll_catg;
435 END IF;
436 return v_applicable_enrolment_cat;
437 EXCEPTION
438 WHEN OTHERS THEN
439 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
440 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_003.Enrp_Get_Enr_Cat');
441 IGS_GE_MSG_STACK.ADD;
442 App_Exception.Raise_Exception;
443 END;
444 END Enrp_Get_Enr_Cat;
445
446
447 Function Enrp_Get_Enr_Ci(
448 p_adm_cal_type IN VARCHAR2 ,
449 p_adm_sequence_number IN NUMBER ,
450 p_enr_cal_type OUT NOCOPY VARCHAR2 ,
451 p_enr_sequence_number OUT NOCOPY NUMBER )
452 RETURN boolean AS
453
454 BEGIN -- enrp_get_enr_ci
455 -- Get the enrolment period which applies to an admission period.
456 -- This is picked up through the relationship between the admission
457 -- and enrolment period, with the enrolment period being the
458 -- subordinate in the relationship.
459 -- If no relationship can be found, the routine will return FALSE.
460 -- If multiple records found, the first period is used (this isn?t
461 -- really a valid scenario)
462 DECLARE
463 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
464 cst_enrolment CONSTANT VARCHAR2(10) := 'ENROLMENT';
465 CURSOR c_cir IS
466 SELECT cir.sub_cal_type,
467 cir.sub_ci_sequence_number
468 FROM IGS_CA_INST_REL cir,
469 IGS_CA_INST ci,
470 IGS_CA_TYPE cat,
471 IGS_CA_STAT cs
472 WHERE cir.sup_cal_type = p_adm_cal_type AND
473 cir.sup_ci_sequence_number = p_adm_sequence_number AND
474 ci.cal_type = cir.sub_cal_type AND
475 ci.sequence_number = cir.sub_ci_sequence_number AND
476 cat.cal_type = ci.cal_type AND
477 cat.S_CAL_CAT = cst_enrolment AND
478 cs.cal_status = ci.cal_status AND
479 cs.s_cal_status = cst_active
480 ORDER BY ci.start_dt;
481 v_cir_rec c_cir%ROWTYPE;
482 BEGIN
483 OPEN c_cir;
484 FETCH c_cir INTO v_cir_rec;
485 IF c_cir%NOTFOUND THEN
486 CLOSE c_cir;
487 p_enr_cal_type := NULL;
488 p_enr_sequence_number := NULL;
489 RETURN FALSE;
490 END IF;
491 CLOSE c_cir;
492 p_enr_cal_type := v_cir_rec.sub_cal_type;
493 p_enr_sequence_number := v_cir_rec.sub_ci_sequence_number;
494 RETURN TRUE;
495 EXCEPTION
496 WHEN OTHERS THEN
497 IF c_cir%ISOPEN THEN
498 CLOSE c_cir;
499 END IF;
500 RAISE;
501 END;
502 EXCEPTION
503 WHEN OTHERS THEN
504 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
505 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_003.enrp_get_enr_ci');
506 IGS_GE_MSG_STACK.ADD;
507 App_Exception.Raise_Exception;
511 Procedure Enrp_Get_Enr_Pp(
508 END enrp_get_enr_ci;
509
510
512 p_username IN VARCHAR2 ,
513 p_cal_type OUT NOCOPY VARCHAR2 ,
514 p_sequence_number OUT NOCOPY NUMBER ,
515 p_enrolment_cat OUT NOCOPY VARCHAR2 ,
516 p_enr_method_type OUT NOCOPY VARCHAR2 )
517 AS
518 BEGIN
519 DECLARE
520 v_person_id IGS_PE_PERSON.person_id%TYPE;
521 v_person_prefs_rec IGS_PE_PERS_PREFS%ROWTYPE;
522 CURSOR c_person(
523 cp_username IGS_PE_PERSON.oracle_username%TYPE) IS
524 SELECT person_id
525 FROM IGS_PE_PERSON
526 WHERE oracle_username = cp_username;
527 CURSOR c_person_prefs(
528 cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
529 SELECT *
530 FROM IGS_PE_PERS_PREFS
531 WHERE person_id = cp_person_id;
532 BEGIN
533 -- this module gets the enrolment values for a IGS_PE_PERSON's preference
534 -- table
535
536 -- commented after ORACLE_USERNAME issue...
537 -- added after ORACLE_USERNAME issue...
538 v_person_id := FND_GLOBAL.USER_ID;
539
540 OPEN c_person_prefs(
541 v_person_id);
542 FETCH c_person_prefs INTO v_person_prefs_rec;
543
544 IF (c_person_prefs%NOTFOUND) THEN
545 CLOSE c_person_prefs;
546 p_cal_type := NULL;
547 p_sequence_number := NULL;
548 p_enrolment_cat := NULL;
549 p_enr_method_type := NULL;
550 ELSE
551 CLOSE c_person_prefs;
552 p_cal_type := v_person_prefs_rec.enr_acad_cal_type;
553 p_sequence_number := v_person_prefs_rec.enr_acad_sequence_number;
554 p_enrolment_cat := v_person_prefs_rec.enr_enrolment_cat;
555 p_enr_method_type := v_person_prefs_rec.enr_enr_method_type;
556 END IF;
557 RETURN;
558 EXCEPTION
559 WHEN OTHERS THEN
560 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
561 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_003.enrp_get_enr_pp');
562 IGS_GE_MSG_STACK.ADD;
563 App_Exception.Raise_Exception;
564 END;
565 END enrp_get_enr_pp;
566
567
568 Function Enrp_Get_Excld_Unit(
569 p_person_id IN NUMBER ,
570 p_course_cd IN VARCHAR2 ,
571 p_effective_dt IN DATE )
572 RETURN VARCHAR2 AS
573
574 BEGIN
575 DECLARE
576 v_pue_start_dt IGS_PE_PERS_UNT_EXCL.pue_start_dt%TYPE;
577 v_expiry_dt IGS_PE_PERS_UNT_EXCL.expiry_dt%TYPE;
578 v_message_name varchar2(30);
579 CURSOR c_psd_ed IS
580 SELECT pue.pue_start_dt,
581 pue.expiry_dt
582 FROM IGS_PE_PERSENC_EFFCT pee,
583 IGS_PE_PERS_UNT_EXCL pue
584 WHERE pee.person_id = p_person_id AND
585 pee.s_encmb_effect_type = 'EXC_CRS_U' AND
586 pee.course_cd = p_course_cd AND
587 pue.person_id = pee.person_id AND
588 pue.encumbrance_type = pee.encumbrance_type AND
589 pue.pen_start_dt = pee.pen_start_dt AND
590 pue.s_encmb_effect_type = pee.s_encmb_effect_type AND
591 pue.pee_start_dt = pee.pee_start_dt AND
592 pue.pee_sequence_number = pee.sequence_number;
593 BEGIN
594 -- This function validates whether or not a IGS_PE_PERSON is
595 -- excluded from admission or enrolment in a specific IGS_PS_UNIT.
596 -- Validate the input parameters
597 IF p_person_id IS NULL OR
598 p_course_cd IS NULL OR
599 p_effective_dt IS NULL THEN
600 RETURN 'N';
601 END IF;
602 --Validate for an exclusion from the university
603 IF IGS_EN_VAL_ENCMB.enrp_val_excld_prsn(
604 p_person_id,
605 p_course_cd,
606 p_effective_dt,
607 v_message_name) = FALSE THEN
608 RETURN 'Y';
609 END IF;
610 --Validate for an exclusion from a specific IGS_PS_UNIT.
611 OPEN c_psd_ed;
612 LOOP
613 FETCH c_psd_ed INTO v_pue_start_dt,
614 v_expiry_dt;
615 EXIT WHEN c_psd_ed%NOTFOUND;
616 --Validate if the dates of a returned record overlap with the effective date.
617 IF v_expiry_dt IS NULL THEN
618 IF v_pue_start_dt <= p_effective_dt THEN
619 CLOSE c_psd_ed;
620 RETURN 'Y';
621 END IF;
622 ELSE
623 IF p_effective_dt BETWEEN v_pue_start_dt AND (v_expiry_dt - 1) THEN
624 CLOSE c_psd_ed;
625 RETURN 'Y';
626 END IF;
627 END IF;
628 END LOOP;
629 CLOSE c_psd_ed;
630 --- Return the default value
631 RETURN 'N';
635 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
632 END;
633 EXCEPTION
634 WHEN OTHERS THEN
636 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_003.enrp_get_excld_unit');
637 IGS_GE_MSG_STACK.ADD;
638 App_Exception.Raise_Exception;
639 END enrp_get_excld_unit;
640
641 Function Get_Student_Ind(
642 p_person_id IN NUMBER )
643 RETURN VARCHAR2 AS
644
645 -- Cursor modified for the bug# 1956253
646 CURSOR pe_typ_cd IS
647 SELECT DISTINCT pti.person_type_code
648 FROM igs_pe_typ_instances_all pti,
649 igs_pe_person_types ppt
650 WHERE person_id = p_person_id AND
651 ppt.system_type = 'STUDENT' AND
652 ppt.person_type_code = pti.person_type_code AND
653 SYSDATE BETWEEN start_date and NVL(end_date,igs_ge_date.igsdate('9999/01/01'));
654
655 lv_pe_typ_cd pe_typ_cd%RowType;
656
657 BEGIN
658 open pe_typ_cd;
659 FETCH pe_typ_cd INTO lv_pe_typ_cd;
660 IF (pe_typ_cd%FOUND) THEN
661 CLOSE pe_typ_cd;
662 RETURN('Y');
663 ELSE
664 CLOSE pe_typ_cd;
665 RETURN('N');
666 END IF;
667
668 END Get_Student_Ind;
669
670
671 Function Get_Staff_Ind(
672 p_person_id IN NUMBER )
673 RETURN VARCHAR2 AS
674
675 -- Removed select from igs_pe_person_v (pathipat) Bug:2432563
676
677 CURSOR pe_typ_cd IS
678 SELECT pti.person_type_code
679 FROM igs_pe_typ_instances pti
680 WHERE pti.person_id = p_person_id
681 AND pti.system_type = 'STAFF'
682 AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
683
684 lv_pe_typ_cd pe_typ_cd%RowType;
685
686 BEGIN
687
688 open pe_typ_cd;
689 FETCH pe_typ_cd INTO lv_pe_typ_cd;
690 IF (pe_typ_cd%FOUND) THEN
691 CLOSE pe_typ_cd;
692 RETURN('Y');
693 ELSE
694 CLOSE pe_typ_cd;
695 RETURN('N');
696 END IF;
697 END Get_Staff_Ind;
698
699 FUNCTION Stdnt_Crs_Atmpt_Stat (perid NUMBER)
700 RETURN VARCHAR2 AS
701
702 CURSOR stu_period_c IS
703 SELECT COURSE_ATTEMPT_STATUS
704 FROM IGS_EN_STDNT_PS_ATT
705 WHERE PERSON_ID=perid;
706
707 BEGIN
708 FOR person_cursor IN stu_period_c LOOP
709 IF person_cursor.COURSE_ATTEMPT_STATUS='ENROLLED' THEN
710 RETURN ('Y');
711 ELSE
712 RETURN ('N');
713 END IF;
714 END LOOP;
715 return('N');
716 EXCEPTION WHEN OTHERS THEN
717 Return('N');
718 END Stdnt_Crs_Atmpt_Stat;
719 --Procedure added as a part of self service setup DLD to set values of matriculation term,
720 --recent admittance term and catalog terms based on the profile values setup for the passed person
721 --as a part of enh bug 2043044
722
723 --Removed columns from igs_pe_stat_details_pkg call as a part of bug number 2203778
724
725 PROCEDURE UPD_MAT_MRADM_CAT_TERMS(
726 p_person_id IN NUMBER,
727 p_program_cd IN VARCHAR2,
728 p_unit_attempt_status IN VARCHAR2,
729 p_teach_cal_type IN VARCHAR2,
730 p_teach_ci_seq_num IN NUMBER)
731
732 /*
733 || change history
734 || WHO WHEN WHAT
735 || ssawhney 30-APR V2API OVN change, igs_pe_stat_pkg signature modified.
736 */
737 IS
738 --Added attribute columns in IGS_PE_DETAILS TBH call as a part of descritpive flexfield added as a part of bug nu:2203778
739 lv_profile_matr_cd VARCHAR2(300) := FND_PROFILE.VALUE('IGS_PE_MATR_TERM');
740 lv_profile_cat_cd VARCHAR2(300) := FND_PROFILE.VALUE('IGS_PE_CATALOG');
741 lv_profile_mr_admit_cd VARCHAR2(300) := FND_PROFILE.VALUE('IGS_PE_RECENT_TERM');
742 lv_cal_term VARCHAR2(10) := 'FALSE';
743 XXX_ROWID VARCHAR2(25);
744 lv_rowid VARCHAR2(25);
745
746 v_return_status Varchar2(1) :='S';
747 v_msg_count NUMBER;
748 v_msg_Data VARCHAR2(2000);
749 v_party_last_update_date DATE;
750 lv_perosn_profile_id hz_person_profiles.person_profile_id%TYPE ;
751 l_ovn hz_parties.object_version_number%TYPE;
752
753
754
755
756 CURSOR c_espa(cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
757 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE) IS
758 Select version_number
759 FROM IGS_EN_STDNT_PS_ATT
760 WHERE course_cd = cp_course_cd AND
761 person_id = cp_person_id;
762 lv_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
763 CURSOR c_pst(cp_course_cd IGS_PS_VER.course_cd%TYPE,
764 cp_version_number IGS_PS_VER.version_number%TYPE) IS
765 Select award_course_ind
766 FROM IGS_PS_TYPE pt,
767 IGS_PS_VER pv
768 WHERE pv.course_type = pt.course_type AND
769 pv.course_cd = cp_course_cd AND
770 pv.version_number = cp_version_number ;
771 lv_award_course_ind IGS_PS_TYPE.award_course_ind%TYPE;
772 CURSOR c_cttl(cp_teach_cal_type IGS_CA_TEACH_TO_LOAD_V.teach_cal_type%TYPE,
773 cp_teach_ci_seq_num IGS_CA_TEACH_TO_LOAD_V.teach_ci_sequence_number%TYPE) IS
774 Select load_cal_type,
775 load_ci_sequence_number
776 FROM IGS_CA_TEACH_TO_LOAD_V
780 lv_cttl_rec c_cttl%ROWTYPE;
777 WHERE teach_cal_type = cp_teach_cal_type AND
778 teach_ci_sequence_number = cp_teach_ci_seq_num
779 ORDER BY load_start_dt desc ;
781 CURSOR c_apai(cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
782 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE) IS
783 SELECT count(ai.person_id) cnt
784 FROM IGS_AD_PS_APPL_INST ai,
785 IGS_AD_OFR_RESP_STAT ar
786 WHERE ai.adm_offer_resp_status = ar.adm_offer_resp_status AND
787 ar.s_adm_offer_resp_status = 'ACCEPTED' AND
788 ai.person_id = cp_person_id AND
789 ai.course_cd = cp_course_cd ;
790 lv_apai_rec c_apai%ROWTYPE;
791 --Cursor to get the row values for updating roe values
792 CURSOR c_psd(cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE) IS
793 SELECT rowid,
794 person_id,
795 effective_start_date,
796 effective_end_date,
797 religion_cd,
798 socio_eco_cd,
799 next_to_kin,
800 in_state_tuition,
801 tuition_st_date,
802 tuition_end_date,
803 further_education_cd,
804 MATR_CAL_TYPE,
805 MATR_SEQUENCE_NUMBER ,
806 INIT_CAL_TYPE ,
807 INIT_SEQUENCE_NUMBER ,
808 RECENT_CAL_TYPE ,
809 RECENT_SEQUENCE_NUMBER ,
810 CATALOG_CAL_TYPE ,
811 CATALOG_SEQUENCE_NUMBER ,
812 ATTRIBUTE_CATEGORY,
813 ATTRIBUTE1,
814 ATTRIBUTE2,
815 ATTRIBUTE3,
816 ATTRIBUTE4,
817 ATTRIBUTE5,
818 ATTRIBUTE6,
819 ATTRIBUTE7,
820 ATTRIBUTE8,
821 ATTRIBUTE9,
822 ATTRIBUTE10,
823 ATTRIBUTE11,
824 ATTRIBUTE12,
825 ATTRIBUTE13,
826 ATTRIBUTE14,
827 ATTRIBUTE15,
828 ATTRIBUTE16,
829 ATTRIBUTE17,
830 ATTRIBUTE18,
831 ATTRIBUTE19,
832 ATTRIBUTE20
833 FROM IGS_PE_STAT_DETAILS
834 WHERE person_id = cp_person_id;
835 lv_psd_rec c_psd%ROWTYPE;
836
837
838
839
840 BEGIN
841 --If for the person corresponding record does not exist in IGS_PE_STAT_DETAILS table then create a record in IGS_PE_STAT_DETAILS table
842 OPEN c_psd(p_person_id);
843 Fetch c_psd INTO lv_psd_rec;
844 IF c_psd%FOUND then
845 CLOSE c_psd;
846 ELSE
847 Igs_Pe_Stat_Details_Pkg.Insert_Row (
848 x_rowid => lv_rowid,
849 x_person_id => p_person_id,
850 x_effective_start_date => SYSDATE,
851 x_effective_end_date => NULL,
852 x_religion_cd => NULL,
853 x_socio_eco_cd => NULL,
854 x_next_to_kin => NULL,
855 x_in_state_tuition => 'N',
856 x_tuition_st_date => NULL,
857 x_tuition_end_date => NULL,
858 x_further_education_cd => NULL,
859 X_MATR_CAL_TYPE => NULL,
860 X_MATR_SEQUENCE_NUMBER => NULL,
861 X_INIT_CAL_TYPE => NULL,
862 X_INIT_SEQUENCE_NUMBER => NULL,
863 X_RECENT_CAL_TYPE => NULL,
864 X_RECENT_SEQUENCE_NUMBER => NULL,
865 X_CATALOG_CAL_TYPE => NULL,
866 X_CATALOG_SEQUENCE_NUMBER => NULL,
867 X_MODE => 'R',
868 X_ATTRIBUTE_CATEGORY => NULL,
869 X_ATTRIBUTE1 => NULL,
870 X_ATTRIBUTE2 => NULL,
871 X_ATTRIBUTE3 => NULL,
872 X_ATTRIBUTE4 => NULL,
873 X_ATTRIBUTE5 => NULL,
874 X_ATTRIBUTE6 => NULL,
875 X_ATTRIBUTE7 => NULL,
876 x_ATTRIBUTE8 => NULL,
877 X_ATTRIBUTE9 => NULL,
878 X_ATTRIBUTE10 => NULL,
879 X_ATTRIBUTE11 => NULL,
880 X_ATTRIBUTE12 => NULL,
881 X_ATTRIBUTE13 => NULL,
882 X_ATTRIBUTE14 => NULL,
883 X_ATTRIBUTE15 => NULL,
884 X_ATTRIBUTE16 => NULL,
885 X_ATTRIBUTE17 => NULL,
889
886 X_ATTRIBUTE18 => NULL,
887 X_ATTRIBUTE19 => NULL,
888 X_ATTRIBUTE20 => NULL );
890
891
892 CLOSE c_psd;
893 END IF;
894
895
896 lv_cal_term := 'FALSE';
897 OPEN c_psd(p_person_id);
898 FETCH c_psd into lv_psd_rec;
899 --Check the value of matriculation fields,if nULL then proceed to populate them by the given logic
900 IF lv_psd_rec.MATR_CAL_TYPE IS NULL OR lv_psd_rec.MATR_SEQUENCE_NUMBER IS NULL THEN
901 CLOSE c_psd;
902 --derive the matriculation term based on the following logic if unit_attempt_status is ENROLLED
903 IF p_unit_attempt_status = 'ENROLLED' then
904
905 --derive the matriculation term based on the following logic if matriculation term profile value is NON GENERIC
906 IF lv_profile_matr_cd = 'NON_GENERIC' THEN
907
908 OPEN c_espa(p_program_cd,p_person_id);
909 FETCH c_espa INTO lv_version_number ;
910
911 --Check if the program code passed is a non-generic program by checking value of award_course_ind
912 OPEN c_pst(p_program_cd,lv_version_number);
913 FETCH c_pst INTO lv_award_course_ind;
914
915 IF lv_award_course_ind = 'Y' THEN
916 lv_cal_term := 'TRUE' ;
917 ELSE
918 lv_cal_term := 'FALSE' ;
919 END IF;
920 CLOSE c_pst;
921 CLOSE c_espa;
922 --derive the matriculation term based on the following logic if matriculation term profile value is Non Generic and Generic
923
924 ELSIF lv_profile_matr_cd = 'ALL' THEN
925 lv_cal_term := 'TRUE' ;
926 END IF;
927
928 IF lv_cal_term = 'TRUE' THEN
929 OPEN c_cttl(p_teach_cal_type,p_teach_ci_seq_num);
930 FETCH c_cttl into lv_cttl_rec ;
931 OPEN c_psd(p_person_id);
932 FETCH c_psd into lv_psd_rec;
933 IF lv_profile_cat_cd = 'MATRICULATION' THEN
934 --If matriculation term is present and catalog profile option is MATRICULATION TERM then
935 --update the catalog fields of the IGS_PE_STAT_DETAILS with the values fetched above beside updating the matricualtion fields also
936
937
938 Igs_Pe_Stat_Details_Pkg.update_row (
939 x_rowid => lv_psd_rec.rowid,
940 x_person_id => lv_psd_rec.person_id ,
941 x_effective_start_date => lv_psd_rec.effective_start_date,
942 x_effective_end_date => lv_psd_rec.effective_end_date,
943 x_religion_cd => lv_psd_rec.religion_cd,
944 x_socio_eco_cd => lv_psd_rec.socio_eco_cd,
945 x_next_to_kin => lv_psd_rec.next_to_kin,
946 x_in_state_tuition => lv_psd_rec.in_state_tuition,
947 x_tuition_st_date => lv_psd_rec.tuition_st_date,
948 x_tuition_end_date => lv_psd_rec.tuition_end_date,
949 x_further_education_cd => lv_psd_rec.further_education_cd,
950 X_MATR_CAL_TYPE => lv_cttl_rec.load_cal_type,
951 X_MATR_SEQUENCE_NUMBER => lv_cttl_rec.load_ci_sequence_number,
952 X_INIT_CAL_TYPE => lv_psd_rec.INIT_CAL_TYPE,
953 X_INIT_SEQUENCE_NUMBER => lv_psd_rec.INIT_SEQUENCE_NUMBER,
954 X_RECENT_CAL_TYPE => lv_psd_rec.RECENT_CAL_TYPE,
955 X_RECENT_SEQUENCE_NUMBER => lv_psd_rec.RECENT_SEQUENCE_NUMBER ,
956 X_CATALOG_CAL_TYPE => lv_cttl_rec.load_cal_type ,
957 X_CATALOG_SEQUENCE_NUMBER => lv_cttl_rec.load_ci_sequence_number,
958 X_MODE => 'R' ,
959 X_ATTRIBUTE_CATEGORY => lv_psd_rec.ATTRIBUTE_CATEGORY,
960 X_ATTRIBUTE1 => lv_psd_rec.ATTRIBUTE1,
961 X_ATTRIBUTE2 => lv_psd_rec.ATTRIBUTE2,
962 X_ATTRIBUTE3 => lv_psd_rec.ATTRIBUTE3,
963 X_ATTRIBUTE4 => lv_psd_rec.ATTRIBUTE4,
964 X_ATTRIBUTE5 => lv_psd_rec.ATTRIBUTE5,
965 X_ATTRIBUTE6 => lv_psd_rec.ATTRIBUTE6,
966 X_ATTRIBUTE7 => lv_psd_rec.ATTRIBUTE7,
967 x_ATTRIBUTE8 => lv_psd_rec.ATTRIBUTE8,
968 X_ATTRIBUTE9 => lv_psd_rec.ATTRIBUTE9,
969 X_ATTRIBUTE10 => lv_psd_rec.ATTRIBUTE10,
970 X_ATTRIBUTE11 => lv_psd_rec.ATTRIBUTE11,
971 X_ATTRIBUTE12 => lv_psd_rec.ATTRIBUTE12,
972 X_ATTRIBUTE13 => lv_psd_rec.ATTRIBUTE13,
973 X_ATTRIBUTE14 => lv_psd_rec.ATTRIBUTE14,
977 X_ATTRIBUTE18 => lv_psd_rec.ATTRIBUTE18,
974 X_ATTRIBUTE15 => lv_psd_rec.ATTRIBUTE15,
975 X_ATTRIBUTE16 => lv_psd_rec.ATTRIBUTE16,
976 X_ATTRIBUTE17 => lv_psd_rec.ATTRIBUTE17,
978 X_ATTRIBUTE19 => lv_psd_rec.ATTRIBUTE19,
979 X_ATTRIBUTE20 => lv_psd_rec.ATTRIBUTE20);
980
981 ELSE
982 --Just updating the matriculation fields
983 Igs_Pe_Stat_Details_Pkg.update_row (
984 x_rowid => lv_psd_rec.rowid,
985 x_person_id => lv_psd_rec.person_id ,
986 x_effective_start_date => lv_psd_rec.effective_start_date,
987 x_effective_end_date => lv_psd_rec.effective_end_date,
988 x_religion_cd => lv_psd_rec.religion_cd,
989 x_socio_eco_cd => lv_psd_rec.socio_eco_cd,
990 x_next_to_kin => lv_psd_rec.next_to_kin,
991 x_in_state_tuition => lv_psd_rec.in_state_tuition,
992 x_tuition_st_date => lv_psd_rec.tuition_st_date,
993 x_tuition_end_date => lv_psd_rec.tuition_end_date,
994 x_further_education_cd => lv_psd_rec.further_education_cd,
995 X_MATR_CAL_TYPE => lv_cttl_rec.load_cal_type,
996 X_MATR_SEQUENCE_NUMBER => lv_cttl_rec.load_ci_sequence_number,
997 X_INIT_CAL_TYPE => lv_psd_rec.INIT_CAL_TYPE,
998 X_INIT_SEQUENCE_NUMBER => lv_psd_rec.INIT_SEQUENCE_NUMBER,
999 X_RECENT_CAL_TYPE => lv_psd_rec.RECENT_CAL_TYPE,
1000 X_RECENT_SEQUENCE_NUMBER => lv_psd_rec.RECENT_SEQUENCE_NUMBER ,
1001 X_CATALOG_CAL_TYPE => lv_psd_rec.CATALOG_CAL_TYPE ,
1002 X_CATALOG_SEQUENCE_NUMBER => lv_psd_rec.CATALOG_SEQUENCE_NUMBER,
1003 X_MODE => 'R' ,
1004 X_ATTRIBUTE_CATEGORY => lv_psd_rec.ATTRIBUTE_CATEGORY,
1005 X_ATTRIBUTE1 => lv_psd_rec.ATTRIBUTE1,
1006 X_ATTRIBUTE2 => lv_psd_rec.ATTRIBUTE2,
1007 X_ATTRIBUTE3 => lv_psd_rec.ATTRIBUTE3,
1008 X_ATTRIBUTE4 => lv_psd_rec.ATTRIBUTE4,
1009 X_ATTRIBUTE5 => lv_psd_rec.ATTRIBUTE5,
1010 X_ATTRIBUTE6 => lv_psd_rec.ATTRIBUTE6,
1011 X_ATTRIBUTE7 => lv_psd_rec.ATTRIBUTE7,
1012 x_ATTRIBUTE8 => lv_psd_rec.ATTRIBUTE8,
1013 X_ATTRIBUTE9 => lv_psd_rec.ATTRIBUTE9,
1014 X_ATTRIBUTE10 => lv_psd_rec.ATTRIBUTE10,
1015 X_ATTRIBUTE11 => lv_psd_rec.ATTRIBUTE11,
1016 X_ATTRIBUTE12 => lv_psd_rec.ATTRIBUTE12,
1017 X_ATTRIBUTE13 => lv_psd_rec.ATTRIBUTE13,
1018 X_ATTRIBUTE14 => lv_psd_rec.ATTRIBUTE14,
1019 X_ATTRIBUTE15 => lv_psd_rec.ATTRIBUTE15,
1020 X_ATTRIBUTE16 => lv_psd_rec.ATTRIBUTE16,
1021 X_ATTRIBUTE17 => lv_psd_rec.ATTRIBUTE17,
1022 X_ATTRIBUTE18 => lv_psd_rec.ATTRIBUTE18,
1023 X_ATTRIBUTE19 => lv_psd_rec.ATTRIBUTE19,
1024 X_ATTRIBUTE20 => lv_psd_rec.ATTRIBUTE20);
1025
1026 END IF;
1027 CLOSE c_psd;
1028 CLOSE c_cttl;
1029 END IF;
1030 END IF;
1031 END IF;
1032 IF(c_psd%ISOPEN) THEN
1033 CLOSE c_psd;
1034 END IF;
1035
1036 --To set the value of recent admittance term based on profile option value
1037 IF lv_profile_mr_admit_cd = 'ACCPT_OFFER_UNIT_ATTEMPT' THEN
1038 OPEN c_apai(p_program_cd,p_person_id);
1039 FETCH c_apai INTO lv_apai_rec;
1040
1041 --if an accepted admission application exists
1042 IF lv_apai_rec.cnt > 0 THEN
1043 CLOSE c_apai;
1044 OPEN c_cttl(p_teach_cal_type,p_teach_ci_seq_num);
1045 FETCH c_cttl into lv_cttl_rec ;
1046 OPEN c_psd(p_person_id);
1047 FETCH c_psd into lv_psd_rec ;
1048
1049 IF lv_profile_cat_cd = 'MR_ADM_TERM' THEN
1050 --Updating the catalog fields also alongwith recent admittacne term fields
1051 --if recent admittance term is present and catalog prfile is set to Recent admittance term
1052 Igs_Pe_Stat_Details_Pkg.update_row (
1053 x_rowid => lv_psd_rec.rowid,
1054 x_person_id => lv_psd_rec.person_id ,
1058 x_socio_eco_cd => lv_psd_rec.socio_eco_cd,
1055 x_effective_start_date => lv_psd_rec.effective_start_date,
1056 x_effective_end_date => lv_psd_rec.effective_end_date,
1057 x_religion_cd => lv_psd_rec.religion_cd,
1059 x_next_to_kin => lv_psd_rec.next_to_kin,
1060 x_in_state_tuition => lv_psd_rec.in_state_tuition,
1061 x_tuition_st_date => lv_psd_rec.tuition_st_date,
1062 x_tuition_end_date => lv_psd_rec.tuition_end_date,
1063 x_further_education_cd => lv_psd_rec.further_education_cd,
1064 X_MATR_CAL_TYPE => lv_psd_rec.MATR_CAL_TYPE,
1065 X_MATR_SEQUENCE_NUMBER => lv_psd_rec.MATR_SEQUENCE_NUMBER,
1066 X_INIT_CAL_TYPE => lv_psd_rec.INIT_CAL_TYPE,
1067 X_INIT_SEQUENCE_NUMBER => lv_psd_rec.INIT_SEQUENCE_NUMBER,
1068 X_RECENT_CAL_TYPE => lv_cttl_rec.load_cal_type,
1069 X_RECENT_SEQUENCE_NUMBER => lv_cttl_rec.load_ci_sequence_number,
1070 X_CATALOG_CAL_TYPE => lv_cttl_rec.load_cal_type ,
1071 X_CATALOG_SEQUENCE_NUMBER => lv_cttl_rec.load_ci_sequence_number,
1072 X_MODE => 'R',
1073 X_ATTRIBUTE_CATEGORY => lv_psd_rec.ATTRIBUTE_CATEGORY,
1074 X_ATTRIBUTE1 => lv_psd_rec.ATTRIBUTE1,
1075 X_ATTRIBUTE2 => lv_psd_rec.ATTRIBUTE2,
1076 X_ATTRIBUTE3 => lv_psd_rec.ATTRIBUTE3,
1077 X_ATTRIBUTE4 => lv_psd_rec.ATTRIBUTE4,
1078 X_ATTRIBUTE5 => lv_psd_rec.ATTRIBUTE5,
1079 X_ATTRIBUTE6 => lv_psd_rec.ATTRIBUTE6,
1080 X_ATTRIBUTE7 => lv_psd_rec.ATTRIBUTE7,
1081 x_ATTRIBUTE8 => lv_psd_rec.ATTRIBUTE8,
1082 X_ATTRIBUTE9 => lv_psd_rec.ATTRIBUTE9,
1083 X_ATTRIBUTE10 => lv_psd_rec.ATTRIBUTE10,
1084 X_ATTRIBUTE11 => lv_psd_rec.ATTRIBUTE11,
1085 X_ATTRIBUTE12 => lv_psd_rec.ATTRIBUTE12,
1086 X_ATTRIBUTE13 => lv_psd_rec.ATTRIBUTE13,
1087 X_ATTRIBUTE14 => lv_psd_rec.ATTRIBUTE14,
1088 X_ATTRIBUTE15 => lv_psd_rec.ATTRIBUTE15,
1089 X_ATTRIBUTE16 => lv_psd_rec.ATTRIBUTE16,
1090 X_ATTRIBUTE17 => lv_psd_rec.ATTRIBUTE17,
1091 X_ATTRIBUTE18 => lv_psd_rec.ATTRIBUTE18,
1092 X_ATTRIBUTE19 => lv_psd_rec.ATTRIBUTE19,
1093 X_ATTRIBUTE20 => lv_psd_rec.ATTRIBUTE20);
1094
1095 ELSE
1096 --only update the recent admittance fields
1097 Igs_Pe_Stat_Details_Pkg.update_row (
1098 x_rowid => lv_psd_rec.rowid,
1099 x_person_id => lv_psd_rec.person_id ,
1100 x_effective_start_date => lv_psd_rec.effective_start_date,
1101 x_effective_end_date => lv_psd_rec.effective_end_date,
1102 x_religion_cd => lv_psd_rec.religion_cd,
1103 x_socio_eco_cd => lv_psd_rec.socio_eco_cd,
1104 x_next_to_kin => lv_psd_rec.next_to_kin,
1105 x_in_state_tuition => lv_psd_rec.in_state_tuition,
1106 x_tuition_st_date => lv_psd_rec.tuition_st_date,
1107 x_tuition_end_date => lv_psd_rec.tuition_end_date,
1108 x_further_education_cd => lv_psd_rec.further_education_cd,
1109 X_MATR_CAL_TYPE => lv_psd_rec.MATR_CAL_TYPE,
1110 X_MATR_SEQUENCE_NUMBER => lv_psd_rec.MATR_SEQUENCE_NUMBER,
1111 X_INIT_CAL_TYPE => lv_psd_rec.INIT_CAL_TYPE,
1112 X_INIT_SEQUENCE_NUMBER => lv_psd_rec.INIT_SEQUENCE_NUMBER,
1113 X_RECENT_CAL_TYPE => lv_cttl_rec.load_cal_type,
1114 X_RECENT_SEQUENCE_NUMBER => lv_cttl_rec.load_ci_sequence_number,
1115 X_CATALOG_CAL_TYPE => lv_psd_rec.CATALOG_CAL_TYPE ,
1116 X_CATALOG_SEQUENCE_NUMBER => lv_psd_rec.CATALOG_SEQUENCE_NUMBER,
1117 X_MODE => 'R' ,
1118 X_ATTRIBUTE_CATEGORY => lv_psd_rec.ATTRIBUTE_CATEGORY,
1119 X_ATTRIBUTE1 => lv_psd_rec.ATTRIBUTE1,
1120 X_ATTRIBUTE2 => lv_psd_rec.ATTRIBUTE2,
1121 X_ATTRIBUTE3 => lv_psd_rec.ATTRIBUTE3,
1122 X_ATTRIBUTE4 => lv_psd_rec.ATTRIBUTE4,
1123 X_ATTRIBUTE5 => lv_psd_rec.ATTRIBUTE5,
1124 X_ATTRIBUTE6 => lv_psd_rec.ATTRIBUTE6,
1125 X_ATTRIBUTE7 => lv_psd_rec.ATTRIBUTE7,
1126 x_ATTRIBUTE8 => lv_psd_rec.ATTRIBUTE8,
1127 X_ATTRIBUTE9 => lv_psd_rec.ATTRIBUTE9,
1128 X_ATTRIBUTE10 => lv_psd_rec.ATTRIBUTE10,
1129 X_ATTRIBUTE11 => lv_psd_rec.ATTRIBUTE11,
1130 X_ATTRIBUTE12 => lv_psd_rec.ATTRIBUTE12,
1131 X_ATTRIBUTE13 => lv_psd_rec.ATTRIBUTE13,
1132 X_ATTRIBUTE14 => lv_psd_rec.ATTRIBUTE14,
1133 X_ATTRIBUTE15 => lv_psd_rec.ATTRIBUTE15,
1134 X_ATTRIBUTE16 => lv_psd_rec.ATTRIBUTE16,
1135 X_ATTRIBUTE17 => lv_psd_rec.ATTRIBUTE17,
1136 X_ATTRIBUTE18 => lv_psd_rec.ATTRIBUTE18,
1137 X_ATTRIBUTE19 => lv_psd_rec.ATTRIBUTE19,
1138 X_ATTRIBUTE20 => lv_psd_rec.ATTRIBUTE20);
1139 NULL;
1140
1141 END IF;
1142
1143 Close c_psd;
1144 CLOSE c_cttl;
1145 END IF;
1146 IF(c_apai%ISOPEN) THEN
1147 CLOSE c_apai;
1148 END IF;
1149 END IF;
1150 --to close any cursor if open
1151
1152
1153 IF(c_psd%ISOPEN) THEN
1154 CLOSE c_psd;
1155 END IF;
1156 IF(c_cttl%ISOPEN) THEN
1157 CLOSE c_cttl;
1158 END IF;
1159 IF(c_apai%ISOPEN) THEN
1160 CLOSE c_apai;
1161 END IF;
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1165 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_003.upd_mat_mradm_cat_terms');
1166 IGS_GE_MSG_STACK.ADD;
1167 App_Exception.Raise_Exception;
1168
1169 END UPD_MAT_MRADM_CAT_TERMS;
1170
1171
1172
1173 END IGS_EN_GEN_003 ;
[Home] [Help]