1 PACKAGE BODY IGS_EN_CAREER_MODEL AS
2 /* $Header: IGSEN86B.pls 120.0 2005/06/02 00:47:32 appldev noship $ */
3 -- sarakshi 16-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG in the procedure SCA_TBH_AFTER_DML
4 -- svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added
5 -- to table handler procedure calls as part of CCR - ENCR022.
6 -- smaddali 25-feb-2002 Bug# 2233348 ENCR018 ccr. modified procedure
7 -- before_tbh because a utp case failed in IGSEN022
8 -- ptandon 15-Dec-2003 Modified procedure SCA_TBH_BEFORE_DML to error out if no
9 -- program is set as primary program for the given career.
10 -- amuthu 24-Dec-2004 commented the logic related to the automatic determination of primary program.
11
12 FUNCTION ENRP_GET_SEC_SCA_STATUS (
13 p_person_id IN NUMBER ,
14 p_course_cd IN VARCHAR2 ,
15 p_course_attempt_status IN VARCHAR2 ,
16 p_primary_program_type IN VARCHAR2,
17 p_primary_prog_type_source IN VARCHAR2,
18 p_course_type IN VARCHAR2 ,
19 p_new_primary_course_cd IN VARCHAR2 DEFAULT NULL
20 )RETURN VARCHAR2 AS
21
22
23 CURSOR c_sca IS
24 SELECT sca.course_attempt_status,
25 sca.primary_program_type,
26 sca.primary_prog_type_source
27 FROM IGS_EN_STDNT_PS_ATT sca
28 WHERE sca.person_id = p_person_id
29 AND sca.course_cd = p_course_cd;
30
31
32 CURSOR c_pri_course_cd is
33 SELECT sca.course_attempt_status
34 FROM IGS_EN_STDNT_PS_ATT sca,
35 IGS_PS_VER crv
36 WHERE sca.person_id = p_person_id
37 AND crv.course_type = p_course_type
38 AND sca.course_cd = crv.course_Cd
39 AND sca.version_number = crv.version_number
40 AND sca.primary_program_type = 'PRIMARY'
41 AND sca.course_cd = NVL(p_new_primary_course_cd,sca.course_cd);
42
43
44
45 v_pri_course_cd_rec c_pri_course_cd%ROWTYPE;
46
47 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
48 v_primary_program_type IGS_EN_STDNT_PS_ATT.primary_program_type%TYPE;
49 v_primary_prog_type_source IGS_EN_STDNT_PS_ATT.primary_prog_type_source%TYPE;
50
51 cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
52 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
53 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
54 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
55 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
56 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
57 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
58 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
59
60 cst_primary CONSTANT VARCHAR2(10) := 'PRIMARY';
61 cst_secondary CONSTANT VARCHAR2(10) := 'SECONDARY';
62
63
64 BEGIN
65 /*
66 if this function returns null then it could for one of two reason
67 the career model is not enabled or the primary program does not exit for
68 the context career (course_type)
69 */
70
71
72 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') <> 'Y' THEN
73 return null;
74 END IF;
75
76 -- If the values have not been passed in, load them.
77 IF p_course_attempt_status IS NULL
78 OR p_primary_program_type IS NULL THEN
79 OPEN c_sca;
80 FETCH c_sca INTO v_course_attempt_status,
81 v_primary_program_type,
82 v_primary_prog_type_source;
83 IF (c_sca%NOTFOUND) THEN
84 CLOSE c_sca;
85 RETURN NULL;
86 END IF;
87 CLOSE c_sca;
88 ELSE
89 -- Use parameters instead of selected student IGS_PS_COURSE attempt
90 -- information to set v_ values.
91 v_course_attempt_status := p_course_attempt_status;
92 v_primary_program_type := p_primary_program_type;
93 v_primary_prog_type_source := p_primary_prog_type_source;
94 END IF;
95
96
97 IF v_primary_program_type = cst_secondary THEN
98
99 OPEN c_pri_course_cd;
100 FETCH c_pri_course_cd INTO v_pri_course_cd_rec;
101
102 IF c_pri_course_cd%FOUND THEN
103
104 IF v_pri_course_cd_rec.course_attempt_status = cst_inactive THEN
105 CLOSE c_pri_course_cd;
106 RETURN cst_inactive;
107 END IF;
108
109 IF v_pri_course_cd_rec.course_attempt_status = cst_enrolled THEN
110 CLOSE c_pri_course_cd;
111 RETURN cst_enrolled;
112 END IF;
113
114 IF v_pri_course_cd_rec.course_attempt_status = cst_intermit THEN
115 CLOSE c_pri_course_cd;
116 RETURN cst_intermit;
117 END IF;
118
119 IF v_pri_course_cd_rec.course_attempt_status = cst_lapsed THEN
120 CLOSE c_pri_course_cd;
121 RETURN cst_lapsed;
122 END IF;
123
124 IF v_pri_course_cd_rec.course_attempt_status = cst_discontin
125 OR v_pri_course_cd_rec.course_attempt_status = cst_completed THEN
126 CLOSE c_pri_course_cd;
127 RETURN null; -- status cannot be derived since the primary program is
128 -- is completed or discontinued. The user has to select
129 -- a new primary progarm.
130 END IF;
131 END IF;
132 CLOSE c_pri_course_cd;
133 END IF;
134
135 RETURN null; -- could not find the primary program for this career
136 /*
137 if this function returns null then it could for one of two reason
138 the career model is not enabled or the primary program does not exit for
139 the context career (course_type)
140 */
141 END ENRP_GET_SEC_SCA_STATUS;
142
143
144
145 PROCEDURE SCA_TBH_BEFORE_DML(
146 p_person_id IN NUMBER,
147 p_course_cd IN VARCHAR2,
148 p_version_number IN NUMBER,
149 p_old_course_attempt_status IN VARCHAR2 ,
150 p_new_course_attempt_status IN OUT NOCOPY VARCHAR2 ,
151 p_primary_program_type IN OUT NOCOPY VARCHAR2,
152 p_primary_prog_type_source IN OUT NOCOPY VARCHAR2,
153 p_new_key_program IN OUT NOCOPY VARCHAR2
154 ) AS
155 -------------------------------------------------------------------------------------------
156 --Change History:
157 --Who When What
158 --kkillams 08-01-03 Added new validation before changing the primary program type
159 -- to SECONDARY if new program attempt status is DISCONTINU/COMPLETE/UNCONFIRM.
160 -- Which checks whether any active program is exist in the same career other
161 -- than current program , if exist then only set the context program as SECONDARY.
162 -- w.r.t. the bug 2710998.
163 -- ptandon 15-12-03 Added validation to error out if no program is set as primary program for the given career.
164 -------------------------------------------------------------------------------------------
165
166
167 v_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE;
168 v_primary_prog_type_source IGS_EN_STDNT_PS_ATT_ALL.primary_prog_type_source%TYPE;
169 v_course_cd_rank IGS_PS_VER.PRIMARY_PROGRAM_RANK%TYPE;
170 v_exst_primary_rank IGS_PS_VER.PRIMARY_PROGRAM_RANK%TYPE;
171 v_exst_key_program IGS_EN_STDNT_PS_ATT_ALL.KEY_PROGRAM%TYPE ;
172 v_min_sec_rank IGS_PS_VER.PRIMARY_PROGRAM_RANK%TYPE;
173 cst_system CONSTANT VARCHAR2(10) := 'SYSTEM';
174 cst_primary CONSTANT VARCHAR2(10) := 'PRIMARY';
175 cst_secondary CONSTANT VARCHAR2(10) := 'SECONDARY';
176 l_count NUMBER(5);
177
178 CURSOR c_course_type IS
179 SELECT crv.course_type
180 FROM igs_ps_ver crv
181 WHERE course_cd = p_course_cd
182 and version_number = p_version_number;
183
184 /*
185 CURSOR c_auto_enabled (cp_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE) IS
186 SELECT crv.primary_program_rank
187 FROM igs_ps_type pst,
188 igs_ps_ver crv
189 WHERE pst.course_type = cp_course_type
190 AND crv.course_Cd = p_course_Cd
191 AND crv.version_number = p_version_number
192 AND crv.course_type = pst.course_type;
193 */
194 CURSOR c_exst_primary_rank (cp_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE) IS
195 SELECT PRIMARY_PROGRAM_RANK , key_program
196 FROM igs_en_stdnt_ps_att sca,
197 igs_ps_ver crv
198 WHERE crv.course_type = cp_course_type
199 AND sca.course_cd = crv.course_cd
200 AND sca.version_number = crv.version_number
201 AND sca.person_id = p_person_id
202 AND sca.course_cd <> p_course_cd
203 AND sca.primary_program_type = cst_primary;
204
205
206 CURSOR c_key_prog_exists IS
207 SELECT 'X'
208 FROM IGS_EN_STDNT_PS_ATT
209 WHERE person_id = p_person_id
210 AND key_program = 'Y';
211 /*
212 --Cursor gets the total number of courses attempted by student for a given course type.
213 CURSOR c_prg_count(cp_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE) IS
214 SELECT count(1)
215 FROM IGS_EN_STDNT_PS_ATT sca,
216 IGS_PS_VER crv
217 WHERE sca.person_id = p_person_id
218 AND crv.course_type = cp_course_type
219 AND sca.course_cd = crv.course_cd
220 AND sca.version_number = crv.version_number
221 AND course_attempt_status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT');
222 */
223
224 l_key_prog_exists VARCHAR2(1) ;
225 v_exst_key_prog igs_en_stdnt_ps_att_all.key_program%TYPE;
226 l_new_course_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
227 BEGIN
228
229 l_key_prog_exists := NULL;
230 -- Initializing the first active program attempt as the Key Program
231 OPEN c_key_prog_exists ;
232 FETCH c_key_prog_exists INTO l_key_prog_exists ;
233 IF c_key_prog_exists %NOTFOUND AND
234 p_new_course_attempt_status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT') THEN
235 p_new_key_program := 'Y' ;
236 END IF;
237 CLOSE c_key_prog_exists ;
238
239
240 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') <> 'Y' THEN
241 RETURN; -- return if the career model is not enabled
242 END IF;
243
244 OPEN c_course_type;
245 FETCH c_course_type INTO v_course_type;
246 CLOSE c_course_type;
247 /*
248 OPEN c_auto_enabled (v_course_type);
249 FETCH c_auto_enabled INTO v_course_cd_rank;
250 CLOSE c_auto_enabled;
251 */
252 p_primary_prog_type_source := cst_system ;
253
254 IF p_new_course_attempt_status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT') THEN
255 OPEN c_exst_primary_rank(v_course_type);
256 FETCH c_exst_primary_rank INTO v_exst_primary_rank ,v_exst_key_prog ;
257 IF c_exst_primary_rank%NOTFOUND THEN
258 p_primary_program_type := cst_primary;
259 /*
260 ELSE
261 IF v_course_cd_rank IS NOT NULL AND
262 v_course_cd_rank < v_exst_primary_rank THEN
263 -- if the rank of the context progarm is not null
264 -- and less than that of the existing primary program
265 -- and the course attempt status is not unconfirmed then
266 -- set the context program as the primary program
267 p_primary_program_type := cst_primary;
268 IF v_exst_key_prog = 'Y' THEN
269 p_new_key_program := 'Y' ;
270 END IF;
271 */
272 ELSE
273 p_primary_program_type := cst_secondary ;
274 -- END IF;
275 END IF;
276 CLOSE c_exst_primary_rank;
277 END IF;
278 /*
279 --smaddali modified this code to include status UNCONFIRM for bug#2233348
280 -- because when we unconfirm a primary program it should be made secondary
281 IF p_old_course_attempt_status <> p_new_course_attempt_status
282 AND p_new_course_attempt_status IN ('DISCONTIN','COMPLETED','UNCONFIRM') THEN
283 --Check is there any other program exists for this career.
284 --If more than one active program exist then only change the primary program type
285 --to SECONDARY otherwise no. Since there is no other program is active then
286 --we should keep this program as PRIMARY.
287
288 p_primary_program_type := cst_secondary;
289 p_new_key_program := 'N';
290 END IF;
291
292 */
293 IF p_primary_program_type = cst_secondary THEN
294 -- using the same cursor as that for selecting the primary programs
295 -- this indirectly tells us if there is a primary program
296 -- A secnario will arise when the primary program is manually
297 -- set to secondary. In that case there will be no primary in the
298 -- data base and get_sec_sca_status will return null
299 -- the status for the secondary program in this case will
300 -- be handled in the after dml procedure for another program
301 -- that could be saved as the primary
302 OPEN c_exst_primary_rank(v_course_type);
303 FETCH c_exst_primary_rank INTO v_exst_primary_rank ,v_exst_key_program;
304 IF c_exst_primary_rank%NOTFOUND THEN
305 -- this will over-ride the course attempt status calculated
306 -- by igs_en_gen_006.enrp_get_sca_status.
307 -- it is important to maintain the order in which this procedure
308 -- and igs_en_gen_006.enrp_get_sca_status. are called
309 -- the call to the igs_en_gen_006.enrp_get_sca_status. should be
310 -- first in the order
311 -- If the secondary program is active then recalculate its program attempt status depending on the
312 -- primary programs status
313 IF p_new_course_attempt_status IN ('INACTIVE','ENROLLED','LAPSED','INTERMIT') THEN
314 l_new_course_attempt_status := enrp_get_sec_sca_status( p_person_id ,
315 p_course_cd ,
316 p_new_course_attempt_status,
317 p_primary_program_type ,
318 p_primary_prog_type_source,
319 v_course_type );
320 IF l_new_course_attempt_status IS NULL THEN
321 l_new_course_attempt_status := IGS_EN_GEN_006.ENRP_GET_SCA_STATUS(p_person_id ,
322 p_course_cd,
323 null,null,null,null,null,null);
324 END IF;
325
326
327 -- If there is no primary program for the career error out.
328 IF l_new_course_attempt_status IS NULL THEN
329 fnd_message.set_name ('IGS', 'IGS_EN_SCA_STDNT_NO_PRIMARY');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 ELSE
333 p_new_course_attempt_status := l_new_course_attempt_status;
334 END IF;
335
336 END IF; --end if secondary program attempt status is active
337 END IF;
338 CLOSE c_exst_primary_rank;
339 END IF;
340
341 END SCA_TBH_BEFORE_DML;
342
343
344 PROCEDURE SCA_TBH_AFTER_DML(
345 p_person_id IN NUMBER,
346 p_course_cd IN VARCHAR2,
347 p_version_number IN NUMBER,
348 p_old_course_attempt_status IN VARCHAR2 ,
352 p_new_pri_prog_type IN VARCHAR2 ,
349 p_new_course_attempt_status IN VARCHAR2 ,
350 p_primary_prog_type_source IN VARCHAR2,
351 p_old_pri_prog_type IN VARCHAR2,
353 p_old_key_program IN VARCHAR2
354 ) AS
355
356 v_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE;
357 v_sec_rank IGS_PS_VER.PRIMARY_PROGRAM_RANK%TYPE;
358 v_test_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE;
359 v_primary_prog_type_source IGS_EN_STDNT_PS_ATT_ALL.primary_prog_type_source%TYPE;
360 v_course_attempt_status IGS_EN_STDNT_PS_ATT_ALL.course_attempt_status%TYPE;
361 cst_primary CONSTANT VARCHAR2(10) := 'PRIMARY';
362 cst_secondary CONSTANT VARCHAR2(10) := 'SECONDARY';
363 cst_system CONSTANT VARCHAR2(10) := 'SYSTEM';
364 v_update BOOLEAN;
365 lv_dummy VARCHAR2(1);
366 -- saving the context record
367 c_person_id IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE;
368 c_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE;
369 c_version_number IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE;
370 c_old_course_attempt_status IGS_EN_STDNT_PS_ATT_ALL.course_attempt_status%TYPE;
371 c_new_course_attempt_status IGS_EN_STDNT_PS_ATT_ALL.course_attempt_status%TYPE;
372 c_primary_prog_type_source IGS_EN_STDNT_PS_ATT_ALL.primary_prog_type_source%TYPE;
373 c_old_pri_prog_type IGS_EN_STDNT_PS_ATT_ALL.primary_program_type%TYPE;
374 c_new_pri_prog_type IGS_EN_STDNT_PS_ATT_ALL.primary_program_type%TYPE;
375 c_old_key_program IGS_EN_STDNT_PS_ATT_ALL.key_program%TYPE;
376
377 CURSOR c_course_type(cp_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE,
378 cp_version_number IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE) IS
379 SELECT crv.course_type
380 FROM igs_ps_ver crv
381 WHERE course_cd = cp_course_cd
382 and version_number = cp_version_number;
383
384 CURSOR c_sca_upd (cp_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE ,
385 cp_person_id IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
386 cp_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE) IS
387 SELECT sca.*
388 FROM igs_en_stdnt_ps_att sca,
389 igs_ps_ver crv
390 WHERE crv.course_type = cp_course_type
391 and sca.course_cd = crv.course_cd
392 and sca.version_number = crv.version_number
393 and sca.person_id = cp_person_id
394 and sca.course_cd <> cp_course_cd;
395
396 CURSOR c_min_ranked_sec_sca (cp_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE ,
397 cp_person_id IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
398 cp_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ) IS
399 SELECT crv.PRIMARY_PROGRAM_RANK, sca.course_Cd
400 FROM igs_en_stdnt_ps_att sca,
401 igs_ps_ver crv
402 WHERE crv.course_type = cp_course_type
403 and sca.course_cd = crv.course_cd
404 and sca.version_number = crv.version_number
405 and sca.person_id = cp_person_id
406 and sca.course_cd <> cp_course_cd
407 and sca.primary_program_type <> cst_primary
408 and sca.course_attempt_status IN ('INACTIVE','ENROLLED','LAPSED','INTERMIT')
409 order by crv.primary_program_rank asc ; -- ordering it to find the min ranked value first Kamal's idea
410
411 --Following cursor get's key program for other career
412 --Added by kkillams
413 CURSOR c_ext_oth_prg_key (cp_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE) IS
414 SELECT 'X'
415 FROM igs_en_stdnt_ps_att sca,
416 igs_ps_ver crv
417 WHERE crv.course_type <> cp_course_type
418 AND sca.course_cd = crv.course_cd
419 AND sca.version_number = crv.version_number
420 AND sca.person_id = p_person_id
421 AND sca.primary_program_type = cst_primary
422 AND sca.key_program = 'Y';
423
424 --Following cursor get's any primary program for this career other than current program
425 CURSOR c_ext_oth_primprg (cp_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE,cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
426 SELECT 'X'
427 FROM igs_en_stdnt_ps_att sca,
428 igs_ps_ver crv
429 WHERE crv.course_type = cp_course_type
430 AND sca.course_cd = crv.course_cd
431 AND sca.version_number = crv.version_number
432 AND sca.person_id = p_person_id
433 AND sca.course_cd <> cp_course_cd
434 AND sca.primary_program_type = cst_primary ;
435 c_ext_oth_primprg_rec c_ext_oth_primprg%ROWTYPE;
436
437 BEGIN
438
439 IF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') <> 'Y' THEN
440 RETURN; -- return if the career model is not enabled
441 END IF;
442
443 c_person_id := p_person_id ;
444 c_course_cd := p_course_cd ;
445 c_version_number := p_version_number ;
446 c_old_course_attempt_status := p_old_course_attempt_status;
447 c_new_course_attempt_status := p_new_course_attempt_status ;
448 c_primary_prog_type_source := p_primary_prog_type_source ;
449 c_old_pri_prog_type := p_old_pri_prog_type ;
450 c_new_pri_prog_type := p_new_pri_prog_type ;
451 c_old_key_program := p_old_key_program ;
452 v_primary_prog_type_source := cst_system;
453
454 OPEN c_course_type(c_course_cd, c_version_number ) ;
455 FETCH c_course_type INTO v_course_type;
456 CLOSE c_course_type;
457
461 -- update all the records other than the current record if needed
458 --donot allow calls to before dml and after dml of this package from igs_en_stdnt_ps_att_pkg
459 -- this is to prevent recursion of the update row call below
460 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := TRUE ;
462 FOR v_sca_upd_rec IN c_sca_upd(v_course_type ,c_person_id, c_course_cd)
463 LOOP
464
465 v_update := FALSE;
466 /*
467 -- if the context program is primary
468 -- then set all other programs to 'Secondary'
469 IF c_new_pri_prog_type = cst_primary
470 and ((c_new_pri_prog_type IS NOT NULL AND c_old_pri_prog_type IS NULL )
471 OR c_new_pri_prog_type <> c_old_pri_prog_type ) THEN
472
473 IF NVL(v_sca_upd_rec.primary_program_type,'NULL') <> cst_secondary THEN
474 v_update := TRUE;
475 -- if the context program is newly selected as primary program then
476 -- set the primary program type of all other program to secondary
477 v_sca_upd_rec.primary_program_type := cst_secondary;
478 v_sca_upd_rec.key_program := 'N' ;
479 END IF;
480
481 -- calculating the course attempt status for all the other records
482 -- that would be looped through
483 -- If the secondary program is active then recalculate its program attempt status depending on the
484 -- primary programs status
485 IF v_sca_upd_rec.course_attempt_status IN ('INACTIVE','ENROLLED','LAPSED','INTERMIT') THEN
486 v_course_attempt_status := enrp_get_sec_sca_status( c_person_id ,
487 v_sca_upd_rec.course_cd ,
488 v_sca_upd_rec.course_attempt_status,
489 v_sca_upd_rec.primary_program_type ,
490 v_sca_upd_rec.primary_prog_type_source,
491 v_course_type ,
492 c_course_cd );
493 IF v_course_attempt_status IS NULL THEN
494 v_course_attempt_status := IGS_EN_GEN_006.ENRP_GET_SCA_STATUS(c_person_id ,
495 v_sca_upd_rec.course_cd,
496 null,null,null,null,null,null);
497 END IF;
498
499 IF NVL(v_sca_upd_rec.course_attempt_status,'NULL') <> v_course_attempt_status THEN
500 v_update := TRUE;
501 v_sca_upd_rec.course_attempt_status := v_course_attempt_status;
502 END IF;
503 END IF; --end if secondary course_attempt_status is not active
504
505 END IF;
506 */
507 -- If the primary program attempt status has changed then recalculate the status of the
508 -- other programs also
509 IF c_new_pri_prog_type = cst_primary and
510 NVL(c_old_course_attempt_Status,'NULL') <> NVL(c_new_course_attempt_status,'NULL') THEN
511 -- If the secondary program is active then recalculate its program attempt status depending on the
512 -- primary programs status
513 IF v_sca_upd_rec.course_attempt_status IN ('INACTIVE','ENROLLED','LAPSED','INTERMIT') THEN
514 v_course_attempt_status := enrp_get_sec_sca_status( c_person_id ,
515 v_sca_upd_rec.course_cd ,
516 v_sca_upd_rec.course_attempt_status,
517 v_sca_upd_rec.primary_program_type ,
518 v_sca_upd_rec.primary_prog_type_source,
519 v_course_type ,
520 c_course_cd );
521 IF v_course_attempt_status IS NULL THEN
522 v_course_attempt_status := IGS_EN_GEN_006.ENRP_GET_SCA_STATUS(c_person_id ,
523 v_sca_upd_rec.course_cd,
524 null,null,null,null,null,null);
525 END IF;
526
527 IF NVL(v_sca_upd_rec.course_attempt_status,'NULL') <> v_course_attempt_status THEN
528 v_update := TRUE;
529 v_sca_upd_rec.course_attempt_status := v_course_attempt_status;
530 END IF;
531 END IF; --end if secondary program status is active
532 END IF;
533
534 /*
535 IF c_new_pri_prog_type = cst_secondary
536 and c_old_pri_prog_type = cst_primary THEN
537 -- set this as primary only if no other primary exists for this career
538 OPEN c_ext_oth_primprg(v_course_type,c_course_cd );
539 FETCH c_ext_oth_primprg INTO c_ext_oth_primprg_rec;
540 IF c_ext_oth_primprg%NOTFOUND THEN
541 CLOSE c_ext_oth_primprg;
545 -- once it has been completed or discontinued.
542 -- previously testing this cursor if it fetches any rows
543 -- in the sca_tbh_before_dml . Only if the cursor fetches any rows
544 -- are we changing the context program to secondary from the primary
546 OPEN c_min_ranked_sec_sca(v_course_type ,c_person_id,c_course_cd);
547 FETCH c_min_ranked_sec_sca INTO v_sec_rank, v_test_course_cd;
548 IF c_min_ranked_sec_sca%FOUND AND
549 v_sca_upd_rec.course_cd = v_test_course_cd THEN
550 v_update := TRUE;
551 v_sca_upd_rec.primary_program_type := cst_primary;
552 IF c_old_key_program = 'Y' THEN
553 --Check whether any key program is exists in other career. If exist then don't set current program as key program.
554 --Previously it's not looking whether any key program is exist in other career, so that there is chances of getting
555 --two key programs for person. Added by kkillams w.r.t. bug 2407760
556 OPEN c_ext_oth_prg_key(v_course_type);
557 FETCH c_ext_oth_prg_key INTO lv_dummy;
558 IF c_ext_oth_prg_key%NOTFOUND THEN
559 v_sca_upd_rec.key_program := 'Y' ;
560 END IF;
561 CLOSE c_ext_oth_prg_key;
562 END IF ;
563 END IF;
564 CLOSE c_min_ranked_sec_sca;
565 ELSE
566 CLOSE c_ext_oth_primprg;
567 END IF; -- end of if c_ext_oth_primprg%NOTFOUND
568
569 END IF;
570 */
571
572 IF v_update THEN -- update the record only if some thing has changed.
573
574 IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
575 X_ROWID => v_sca_upd_rec.row_id,
576 X_PERSON_ID => v_sca_upd_rec.PERSON_ID,
577 X_COURSE_CD => v_sca_upd_rec.COURSE_CD,
578 X_ADVANCED_STANDING_IND => v_sca_upd_rec.ADVANCED_STANDING_IND,
579 X_FEE_CAT => v_sca_upd_rec.fee_cat,
580 X_CORRESPONDENCE_CAT => v_sca_upd_rec.correspondence_cat,
581 X_SELF_HELP_GROUP_IND => v_sca_upd_rec.SELF_HELP_GROUP_IND,
582 X_LOGICAL_DELETE_DT => v_sca_upd_rec.logical_delete_dt,
583 X_ADM_ADMISSION_APPL_NUMBER => v_sca_upd_rec.adm_admission_appl_number,
584 X_ADM_NOMINATED_COURSE_CD => v_sca_upd_rec.adm_nominated_course_cd,
585 X_ADM_SEQUENCE_NUMBER => v_sca_upd_rec.adm_sequence_number,
586 X_VERSION_NUMBER => v_sca_upd_rec.version_number,
587 X_CAL_TYPE => v_sca_upd_rec.cal_type,
588 X_LOCATION_CD => v_sca_upd_rec.location_cd,
589 X_ATTENDANCE_MODE => v_sca_upd_rec.attendance_mode,
590 X_ATTENDANCE_TYPE => v_sca_upd_rec.attendance_type,
591 X_COO_ID => v_sca_upd_rec.coo_id,
592 X_STUDENT_CONFIRMED_IND => v_sca_upd_rec.student_confirmed_ind,
593 X_COMMENCEMENT_DT => v_sca_upd_rec.commencement_dt,
594 X_COURSE_ATTEMPT_STATUS => v_sca_upd_rec.course_attempt_status,
595 X_PROGRESSION_STATUS => v_sca_upd_rec.PROGRESSION_STATUS,
596 X_DERIVED_ATT_TYPE => v_sca_upd_rec.DERIVED_ATT_TYPE,
597 X_DERIVED_ATT_MODE => v_sca_upd_rec.DERIVED_ATT_MODE,
598 X_PROVISIONAL_IND => v_sca_upd_rec.provisional_ind,
599 X_DISCONTINUED_DT => v_sca_upd_rec.discontinued_dt,
600 X_DISCONTINUATION_REASON_CD => v_sca_upd_rec.discontinuation_reason_cd,
601 X_LAPSED_DT => v_sca_upd_rec.LAPSED_DT,
602 X_FUNDING_SOURCE => v_sca_upd_rec.funding_source,
603 X_EXAM_LOCATION_CD => v_sca_upd_rec.EXAM_LOCATION_CD,
604 X_DERIVED_COMPLETION_YR => v_sca_upd_rec.DERIVED_COMPLETION_YR,
605 X_DERIVED_COMPLETION_PERD => v_sca_upd_rec.DERIVED_COMPLETION_PERD,
606 X_NOMINATED_COMPLETION_YR => v_sca_upd_rec.nominated_completion_yr,
607 X_NOMINATED_COMPLETION_PERD => v_sca_upd_rec.NOMINATED_COMPLETION_PERD,
608 X_RULE_CHECK_IND => v_sca_upd_rec.RULE_CHECK_IND,
609 X_WAIVE_OPTION_CHECK_IND => v_sca_upd_rec.WAIVE_OPTION_CHECK_IND,
610 X_LAST_RULE_CHECK_DT => v_sca_upd_rec.LAST_RULE_CHECK_DT,
611 X_PUBLISH_OUTCOMES_IND => v_sca_upd_rec.PUBLISH_OUTCOMES_IND,
612 X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_rec.COURSE_RQRMNT_COMPLETE_IND,
613 X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_rec.COURSE_RQRMNTS_COMPLETE_DT,
614 X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_rec.S_COMPLETED_SOURCE_TYPE,
618 X_IGS_PR_CLASS_STD_ID => v_sca_upd_rec.igs_pr_class_std_id,
615 X_OVERRIDE_TIME_LIMITATION => v_sca_upd_rec.OVERRIDE_TIME_LIMITATION,
616 x_last_date_of_attendance => v_sca_upd_rec.last_date_of_attendance,
617 x_dropped_by => v_sca_upd_rec.dropped_by,
619 x_primary_program_type => v_sca_upd_rec.primary_program_type,
620 x_primary_prog_type_source => v_sca_upd_rec.primary_prog_type_source,
621 x_catalog_cal_type => v_sca_upd_rec.catalog_cal_type,
622 x_catalog_seq_num => v_sca_upd_rec.catalog_seq_num,
623 x_key_program => v_sca_upd_rec.key_program,
624 x_override_cmpl_dt => v_sca_upd_rec.override_cmpl_dt,
625 x_manual_ovr_cmpl_dt_ind => v_sca_upd_rec.manual_ovr_cmpl_dt_ind,
626 X_MODE => 'R',
627 X_ATTRIBUTE_CATEGORY => v_sca_upd_rec.attribute_category,
628 X_ATTRIBUTE1 => v_sca_upd_rec.attribute1,
629 X_ATTRIBUTE2 => v_sca_upd_rec.attribute2,
630 X_ATTRIBUTE3 => v_sca_upd_rec.attribute3,
631 X_ATTRIBUTE4 => v_sca_upd_rec.attribute4,
632 X_ATTRIBUTE5 => v_sca_upd_rec.attribute5,
633 X_ATTRIBUTE6 => v_sca_upd_rec.attribute6,
634 X_ATTRIBUTE7 => v_sca_upd_rec.attribute7,
635 X_ATTRIBUTE8 => v_sca_upd_rec.attribute8,
636 X_ATTRIBUTE9 => v_sca_upd_rec.attribute9,
637 X_ATTRIBUTE10 => v_sca_upd_rec.attribute10,
638 X_ATTRIBUTE11 => v_sca_upd_rec.attribute11,
639 X_ATTRIBUTE12 => v_sca_upd_rec.attribute12,
640 X_ATTRIBUTE13 => v_sca_upd_rec.attribute13,
641 X_ATTRIBUTE14 => v_sca_upd_rec.attribute14,
642 X_ATTRIBUTE15 => v_sca_upd_rec.attribute15,
643 X_ATTRIBUTE16 => v_sca_upd_rec.attribute16,
644 X_ATTRIBUTE17 => v_sca_upd_rec.attribute17,
645 X_ATTRIBUTE18 => v_sca_upd_rec.attribute18,
646 X_ATTRIBUTE19 => v_sca_upd_rec.attribute19,
647 X_ATTRIBUTE20 => v_sca_upd_rec.attribute20,
648 X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_rec.future_dated_trans_flag
649 );
650
651 END IF;
652
653 END LOOP;
654
655 --allow calls before dml and after dml to this package from igs_en_stdnt_ps_att_pkg to fire
656 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE ;
657
658 END SCA_TBH_AFTER_DML;
659
660
661 FUNCTION ENRP_CHECK_FOR_ONE_PRIMARY (
662 p_person_id IN NUMBER,
663 p_course_type IN VARCHAR2,
664 p_message_name OUT NOCOPY VARCHAR2
665 ) RETURN BOOLEAN AS
666
667
668 v_primary_count NUMBER;
669 v_confirmed_count NUMBER;
670
671 CURSOR c_primary_count IS
672 SELECT count(primary_program_type)
673 FROM IGS_EN_STDNT_PS_ATT sca,
674 IGS_PS_VER crv
675 WHERE crv.course_type = p_course_type AND
676 sca.course_cd = crv.course_cd AND
677 sca.version_number = crv.version_number AND
678 sca.person_id = p_person_id AND
679 sca.primary_program_type = 'PRIMARY';
680
681 CURSOR c_confirmed_sca IS
682 select count(student_confirmed_ind)
683 from IGS_EN_STDNT_PS_ATT sca, igs_ps_ver crv
684 where crv.course_type = p_course_type and
685 sca.course_cd = crv.course_cd and
686 sca.version_number = crv.version_number and
687 sca.person_id = p_person_id and
688 course_attempt_Status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT');
689
690 BEGIN
691
692 p_message_name := null;
693
694 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') <> 'Y' THEN
695 RETURN TRUE;
696 END IF;
697
698 OPEN c_primary_count;
699 FETCH c_primary_count INTO v_primary_count;
700 IF c_primary_count%FOUND THEN
701
702 CLOSE c_primary_count;
703 IF v_primary_count > 1 THEN
704
705 p_message_name := 'IGS_EN_STDNT_PS_MORE_PRIMARY';
706 RETURN FALSE;
707
708 ELSIF v_primary_count = 0 THEN
709 -- selecting the number of confirmed unit which have not been discontimued
710 -- if the count return a value greater than zero then there are program
711 -- which are confirmed but no primary has been set.
712 OPEN c_confirmed_sca;
713 FETCH c_confirmed_sca INTO v_confirmed_count;
714 IF c_confirmed_sca%FOUND AND v_confirmed_count > 0 THEN
715 CLOSE c_confirmed_sca;
716 p_message_name := 'IGS_EN_STDNT_PS_NO_PRIMARY';
717 RETURN FALSE;
718 END IF;
719 CLOSE c_confirmed_sca;
720 END IF;
721
722 ELSE
723 CLOSE c_primary_count;
724 -- selecting the number of confirmed unit which have not been discontimued
725 -- if the count return a value greater than zero then there are program
726 -- which are confirmed but no primary has been set.
727 OPEN c_confirmed_sca;
728 FETCH c_confirmed_sca INTO v_confirmed_count;
729 IF c_confirmed_sca%FOUND AND v_confirmed_count > 0 THEN
730 CLOSE c_confirmed_sca;
731 p_message_name := 'IGS_EN_STDNT_PS_NO_PRIMARY';
732 RETURN FALSE;
733 END IF;
734 CLOSE c_confirmed_sca;
735 END IF;
736
737 RETURN TRUE;
738
739 END ENRP_CHECK_FOR_ONE_PRIMARY;
740
741 END IGS_EN_CAREER_MODEL;
[Home] [Help]