1 PACKAGE BODY IGS_EN_VAL_SCT AS
2 /* $Header: IGSEN66B.pls 120.1 2005/12/05 07:13:31 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 25-AUG-2001 Bug No. 1956374 .Modified function GENP_VAL_SDTT_SESS
7 --ckasu 20-Nov-2004 modified enrp_val_sua_trnsfr and enrp_val_sct_to procedure
8 -- as aprt of program transfer build bug#4000939
9 --ckasu 06-Dec-2004 modified enrp_val_sua_trnsfr procedure as a part of bug#4048248
10 -- inorder to transfer discontinue unit attempt with result other
11 -- than fail from source prgm to dest prgm.
12 -- smaddali 21-dec-04 Modified wrong message name in proc enrp_val_sct_to for bug#4080736
13 -- smaddali 21-dec-04 modified parameter in procedure enrp_val_sua_acad for bug#4083358
14 -- amuthu 23-Dec-2004 The source program for a transfer could be unconfirmed.
15 -- bdeviset 07-JAN-2005 Bug# 4103437.Modified enrp_val_sct_to,enrp_val_sct_from.To avoid having
16 -- when a source/destination is already invloved in a stored transfer.
17 -------------------------------------------------------------------------------------------
18
19 --
20 -- Validate the enrolment period for a transferred course_attempt.
21 FUNCTION enrp_val_scae_acad(
22 p_person_id IN NUMBER ,
23 p_course_cd IN VARCHAR2 ,
24 p_cal_type IN VARCHAR2 ,
25 p_message_name OUT NOCOPY VARCHAR2)
26 RETURN BOOLEAN AS
27
28 BEGIN -- enrp_val_scae_acad
29 -- When transferring a IGS_EN_STDNT_PS_ATT and the calendar
30 -- type of the IGS_PS_OFR_OPT varies from that of the
31 -- original course, validate that the enrolment period of the
32 -- current IGS_AS_SC_ATMPT_ENR is also a sub-ordinate of the
33 -- new calendar type.
34 DECLARE
35 v_ci_sequence_number IGS_AS_SC_ATMPT_ENR.ci_sequence_number%TYPE;
36 v_cal_type IGS_AS_SC_ATMPT_ENR.cal_type%TYPE;
37 v_enrolment_cat IGS_AS_SC_ATMPT_ENR.enrolment_cat%TYPE;
38 v_ret BOOLEAN;
39 CURSOR c_scae_ci IS
40 SELECT scae.cal_type,
41 scae.ci_sequence_number,
42 scae.enrolment_cat
43 FROM IGS_AS_SC_ATMPT_ENR scae,
44 IGS_CA_INST ci
45 WHERE scae.person_id = p_person_id AND
46 scae.course_cd = p_course_cd AND
47 scae.cal_type = ci.cal_type AND
48 scae.ci_sequence_number = ci.sequence_number
49 ORDER BY ci.start_dt DESC;
50 BEGIN
51 p_message_name := null;
52 -- Check parameters.
53 IF p_person_id IS NULL OR
54 p_course_cd IS NULL OR
55 p_cal_type IS NULL THEN
56 RETURN TRUE;
57 END IF;
58 -- Get the current enrolment period from the latest
59 -- student course attempt enrolment record.
60 OPEN c_scae_ci;
61 FETCH c_scae_ci INTO v_cal_type,
62 v_ci_sequence_number,
63 v_enrolment_cat;
64 IF (c_scae_ci%NOTFOUND) THEN
65 CLOSE c_scae_ci;
66 p_message_name := 'IGS_EN_NO_SPA_ENR_EXISTS';
67 RETURN FALSE;
68 END IF;
69 CLOSE c_scae_ci;
70 v_ret := IGS_EN_GEN_008.ENRP_GET_WITHIN_CI (
71 p_cal_type,
72 NULL,
73 v_cal_type,
74 v_ci_sequence_number,
75 FALSE);
76 IF v_ret = FALSE THEN
77 p_message_name := 'IGS_EN_CURENR_PRD_NOT_SUBORD';
78 RETURN FALSE;
79 END IF;
80 RETURN TRUE;
81 EXCEPTION
82 WHEN OTHERS THEN
83 IF (c_scae_ci%ISOPEN) THEN
84 CLOSE c_scae_ci;
85 END IF;
86 RAISE;
87 END;
88 EXCEPTION
89 WHEN OTHERS THEN
90 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
91 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCT.enrp_val_scae_acad');
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END enrp_val_scae_acad;
95 --
96 -- Validate the IGS_PS_OFR_OPT for a transferred unit_attempt
97 -- smaddali modified parameter for bug#4083358
98 FUNCTION enrp_val_sua_acad(
99 p_person_id IN NUMBER ,
100 p_course_cd IN VARCHAR2 ,
101 p_uoo_id IN NUMBER,
102 p_cal_type IN VARCHAR2 ,
103 p_message_name OUT NOCOPY VARCHAR2)
104 RETURN BOOLEAN AS
105
106 BEGIN -- enrp_val_sua_acad
107 -- Validate whether the ENROLLED unit attempt has a link to an instance of
108 -- their enrolled course academic calendar type.
109 -- This is the result of a change of course offering option calendar type
110 -- during a course transfer.
111 DECLARE
112 v_acad_cal_type IGS_CA_INST.cal_type%TYPE;
113 v_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
114 v_acad_ci_start_dt IGS_CA_INST.start_dt%TYPE;
115 v_acad_ci_end_dt IGS_CA_INST.end_dt%TYPE;
116 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
117 v_message_name varchar2(30);
118 CURSOR c_sua IS
119 SELECT sua.cal_type,
120 sua.ci_sequence_number
121 FROM IGS_EN_SU_ATTEMPT sua
122 WHERE person_id = p_person_id AND
123 course_cd = p_course_cd AND
124 uoo_id = p_uoo_id ;
125 v_sua_rec c_sua%ROWTYPE;
126 BEGIN
127 -- Set the default message number
128 p_message_name := null;
129 -- 1. Check parameters.
130 IF p_person_id IS NULL OR
131 p_course_cd IS NULL OR
132 p_uoo_id IS NULL OR
133 p_cal_type IS NULL THEN
134 RETURN TRUE;
135 END IF;
136 -- 2. Fetch the unit attempt record.
137 OPEN c_sua;
138 FETCH c_sua INTO v_sua_rec;
139 IF c_sua%NOTFOUND THEN
140 -- this should not happen
141 CLOSE c_sua;
142 RETURN TRUE;
143 END IF;
144 CLOSE c_sua;
145 -- 3. Check if a link exists.
146 v_alternate_code := IGS_EN_GEN_002.ENRP_GET_ACAD_ALT_CD(
147 v_sua_rec.cal_type,
148 v_sua_rec.ci_sequence_number,
149 v_acad_cal_type,
150 v_acad_ci_sequence_number,
151 v_acad_ci_start_dt,
152 v_acad_ci_end_dt,
153 v_message_name);
154 IF v_acad_cal_type IS NULL OR v_acad_cal_type <> p_cal_type THEN
155 p_message_name := 'IGS_EN_UA_TEACHPRD_NOTLINKED';
156 RETURN FALSE;
157 END IF;
158 -- 4. Set p_message_name to 0
159 RETURN TRUE;
160 EXCEPTION
161 WHEN OTHERS THEN
162 IF c_sua%ISOPEN THEN
163 CLOSE c_sua;
164 END IF;
165 RAISE;
166 END;
167 EXCEPTION
168 WHEN OTHERS THEN
169 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
170 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCT.enrp_val_sua_acad');
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END enrp_val_sua_acad;
174 --
175 -- To validate student course transfer insert
176 FUNCTION enrp_val_sct_insert(
177 p_person_id IN NUMBER ,
178 p_course_cd IN VARCHAR2 ,
179 p_transfer_course_cd IN VARCHAR2 ,
180 p_transfer_dt IN DATE ,
181 p_message_name OUT NOCOPY VARCHAR2)
182 RETURN BOOLEAN AS
183
184 BEGIN -- enrp_val_sct_insert
185 -- This module validates IGS_PS_STDNT_TRN courses.
186 -- Course_cd and transfer_course_cd must not be the same.
187 -- Course_cd and transfer_course_cd must be in a system defined course transfer
188 -- group type, and must be members of the same group.
189 -- Validate course_cd.
190 -- Validate transfer_course_cd.
191 DECLARE
192 cst_unconfirm CONSTANT VARCHAR(9) := 'UNCONFIRM';
193 cst_transfer CONSTANT VARCHAR(8) := 'TRANSFER';
194 cst_admtransfr CONSTANT VARCHAR(10) := 'ADMTRANSFR';
195 v_dummy VARCHAR2(1);
196 v_message_name varchar2(30);
197 v_to_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
198 v_to_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
199 v_to_acai_sequence_number IGS_EN_STDNT_PS_ATT.adm_sequence_number%TYPE;
200 v_s_course_group_type IGS_PS_GRP_TYPE.s_course_group_type%TYPE;
201 v_from_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
202 v_from_course_attempt_status
203 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
204 v_to_admission_appl_number
205 IGS_EN_STDNT_PS_ATT.adm_admission_appl_number%TYPE;
206 v_to_nominated_course_cd
207 IGS_EN_STDNT_PS_ATT.adm_nominated_course_cd%TYPE;
208 CURSOR c_sca_to IS
209 SELECT sca.version_number,
210 sca.course_attempt_status,
211 sca.adm_admission_appl_number,
212 sca.adm_nominated_course_cd ,
213 sca.adm_sequence_number
214 FROM IGS_EN_STDNT_PS_ATT sca
215 WHERE sca.person_id = p_person_id AND
216 sca.course_cd = p_course_cd;
217 CURSOR c_sca_from IS
218 SELECT sca.version_number,
219 sca.course_attempt_status
220 FROM IGS_EN_STDNT_PS_ATT sca
221 WHERE sca.person_id = p_person_id AND
222 sca.course_cd = p_transfer_course_cd;
223 CURSOR c_cgm_cg_cgt(
224 cp_s_course_group_type IGS_PS_GRP_TYPE.s_course_group_type%TYPE,
225 cp_to_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE,
226 cp_from_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE)
227 IS
228 SELECT 'x'
229 FROM IGS_PS_GRP_MBR cgm,
230 IGS_PS_GRP cg,
231 IGS_PS_GRP_TYPE cgt
232 WHERE cgm.course_cd = p_course_cd AND
233 cgm.version_number = cp_to_version_number AND
234 cgt.s_course_group_type = cp_s_course_group_type AND
235 cg.course_group_cd = cgm.course_group_cd AND
236 cg.course_group_type = cgt.course_group_type AND
237 EXISTS (SELECT 'x'
238 FROM IGS_PS_GRP_MBR cgm2
239 WHERE cgm2.course_cd = p_transfer_course_cd AND
240 cgm2.version_number = cp_from_version_number AND
241 cgm2.course_group_cd = cgm.course_group_cd);
242 BEGIN
243 p_message_name := null;
244 -- Validate that transfer 'from' and 'to' course codes are not the same.
245 IF p_course_cd = p_transfer_course_cd THEN
246 p_message_name := 'IGS_EN_UA_FAILS_ST_INVALID';
247 RETURN FALSE;
248 END IF;
249 -- Get student course attempt details for transfer 'to' IGS_PS_COURSE
250 OPEN c_sca_to;
251 FETCH c_sca_to INTO v_to_version_number,
252 v_to_course_attempt_status,
253 v_to_admission_appl_number,
254 v_to_nominated_course_cd,
255 v_to_acai_sequence_number;
256 IF (c_sca_to%NOTFOUND) THEN
257 -- Return, This error will be resolved elsewhere.
258 CLOSE c_sca_to;
259 p_message_name := null;
260 RETURN FALSE;
261 ELSE
262 CLOSE c_sca_to;
263 -- Set system course group type depending on status of transfer 'to'
264 -- course attempt
265 IF v_to_course_attempt_status = cst_unconfirm THEN
266 v_s_course_group_type := cst_admtransfr;
267 ELSE
268 v_s_course_group_type := cst_transfer;
269 END IF;
270 END IF;
271 -- Get student course attempt details for transfer 'from' course
272 OPEN c_sca_from;
273 FETCH c_sca_from INTO v_from_version_number,
274 v_from_course_attempt_status;
275 IF (c_sca_from%NOTFOUND) THEN
276 -- Return, This error will be resolved elsewhere.
277 CLOSE c_sca_from;
278 p_message_name := null;
279 RETURN TRUE;
280 END IF;
281 CLOSE c_sca_from;
282 -- Validate that transfer 'from' and transfer 'to' course attempts are
283 -- members of the same course group, and are mapped to system course group
284 -- type 'TRANSFER' or 'ADMTRANSFR'.
285 OPEN c_cgm_cg_cgt(
286 v_s_course_group_type,
287 v_to_version_number,
288 v_from_version_number);
289 FETCH c_cgm_cg_cgt INTO v_dummy;
290 IF (c_cgm_cg_cgt%NOTFOUND) THEN
291 CLOSE c_cgm_cg_cgt;
292 p_message_name := 'IGS_EN_TOPRG_TRNS_TO_PRG';
293 RETURN FALSE;
294 END IF;
295 CLOSE c_cgm_cg_cgt;
296 -- Validate transfer 'to' course code
297 IF IGS_EN_VAL_SCT.enrp_val_sct_to(
298 p_person_id,
299 p_course_cd,
300 p_transfer_dt,
301 v_to_version_number,
302 v_to_course_attempt_status,
303 v_to_admission_appl_number,
304 v_to_nominated_course_cd,
305 v_to_acai_sequence_number,
306 v_message_name) = FALSE THEN
307 p_message_name := v_message_name;
308 RETURN FALSE;
309 END IF;
310 -- Validate transfer 'from' course code
311 IF IGS_EN_VAL_SCT.enrp_val_sct_from(
312 p_person_id,
313 p_course_cd,
314 p_transfer_course_cd,
315 p_transfer_dt,
316 v_from_course_attempt_status,
317 v_message_name) = FALSE THEN
318 p_message_name := v_message_name;
319 RETURN FALSE;
320 END IF;
321 RETURN TRUE;
322 EXCEPTION
323 WHEN OTHERS THEN
324 IF (c_sca_to%ISOPEN) THEN
325 CLOSE c_sca_to;
326 END IF;
327 IF (c_sca_from%ISOPEN) THEN
328 CLOSE c_sca_from;
329 END IF;
330 IF (c_cgm_cg_cgt%ISOPEN) THEN
331 CLOSE c_cgm_cg_cgt;
332 END IF;
333 RAISE;
334 END;
335 EXCEPTION
336 WHEN OTHERS THEN
337 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
338 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCT.enrp_val_sct_insert');
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 END enrp_val_sct_insert;
342 --
343 -- To validate student course transfer 'to' course code
344 FUNCTION enrp_val_sct_to(
345 p_person_id IN NUMBER ,
346 p_course_cd IN VARCHAR2 ,
347 p_transfer_dt IN DATE ,
348 p_crv_version_number IN NUMBER ,
349 p_course_attempt_status IN VARCHAR2 ,
350 p_admission_appl_number IN NUMBER ,
351 p_nominated_course_cd IN VARCHAR2 ,
352 p_acai_sequence_number IN NUMBER ,
353 p_message_name OUT NOCOPY VARCHAR2)
354 RETURN BOOLEAN AS
355
356
357 -------------------------------------------------------------------------------------------
358 --Change History:
359 --Who When What
360 --ckasu 20-Nov-2004 modified procedure by removing message IGS_EN_TRANSDT_NOTBE_FUTUREDT
361 -- and corresponding if condition as aprt of program transfer
362 -- build bug#4000939
363 --bdeviset 07-JAN-2005 Bug# 4103437.Modified cursor c_sct. To avoid having another transfer
364 -- when a destination program is already invloved in a stored transfer.
365 -------------------------------------------------------------------------------------------
366 BEGIN -- enrp_val_sct_to
367 -- This module validates IGS_PS_STDNT_TRN.course_cd details.
368 -- Course_cd must not be course version defined as generic.
369 -- Course_cd must map to IGS_EN_STDNT_PS_ATT whose course_attempt_status
370 -- is 'ENROLLED', 'INTERMIT', 'INACTIVE' or ('UNCONFIRM' and
371 -- admission_course_appl matching transfer details exist).
372 -- NOTE: also allow 'DISCONTIN' to prevent invalid lifting of discontinuation
373 -- through ENRF3000.
374 -- If IGS_EN_STDNT_PS_ATT is created through Admissions, the related
375 -- IGS_AD_PS_APPL_INST.IGS_AD_OU_STAT must map to
376 -- s_adm_outcome_status 'OFFER', 'COND-OFFER'.
377 -- If IGS_EN_STDNT_PS_ATT is created through Admissions, the related
378 -- IGS_AD_PS_APPL_INST.IGS_AD_OFR_RESP_STAT must map to
379 -- s_adm_offer_resp_status 'REJECTED', 'LAPSED'.
380 -- Course_cd must not have existing IGS_PS_STDNT_TRN mapping whose
381 -- transfer_dt is >= this transfer_dt.
382 -- Transfer_dt >= IGS_EN_STDNT_PS_ATT.commencement_dt and <= today's date
383 DECLARE
384 cst_lapsed CONSTANT VARCHAR2(6) := 'LAPSED';
385 cst_discontin CONSTANT VARCHAR2(9) := 'DISCONTIN';
386 cst_completed CONSTANT VARCHAR2(9) := 'COMPLETED';
387 cst_transfer CONSTANT VARCHAR2(8) := 'TRANSFER';
388 cst_unconfirm CONSTANT VARCHAR2(9) := 'UNCONFIRM';
389 v_dummy VARCHAR2(1);
390 v_generic_course_ind IGS_PS_VER.generic_course_ind%TYPE;
391 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
392 v_adm_outcome_status IGS_AD_PS_APPL_INST.adm_outcome_status%TYPE;
393 v_adm_offer_resp_status IGS_AD_PS_APPL_INST.adm_offer_resp_status%TYPE;
394 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
395 CURSOR c_cv IS
396 SELECT generic_course_ind
397 FROM IGS_PS_VER cv
398 WHERE cv.course_cd = p_course_cd AND
399 cv.version_number = p_crv_version_number;
400 CURSOR c_aa IS
401 SELECT s_admission_process_type
402 FROM IGS_AD_APPL aa
403 WHERE aa.person_id = p_person_id AND
404 aa.admission_appl_number = p_admission_appl_number;
405 CURSOR c_acai IS
406 SELECT adm_outcome_status,
407 adm_offer_resp_status
408 FROM IGS_AD_PS_APPL_INST acai
409 WHERE acai.person_id = p_person_id AND
410 acai.admission_appl_number = p_admission_appl_number AND
411 acai.nominated_course_cd = p_nominated_course_cd AND
412 acai.sequence_number = p_acai_sequence_number;
413
414 -- cursor to check if a destination program is already invloved in a stored transfer
415 CURSOR c_sct IS
416 SELECT 'x'
417 FROM IGS_PS_STDNT_TRN sct
418 WHERE sct.person_id = p_person_id AND
419 (course_cd = p_course_cd OR
420 transfer_course_cd = p_course_cd) AND
421 status_flag = 'U';
422 CURSOR c_sca IS
423 SELECT sca.commencement_dt
424 FROM IGS_EN_STDNT_PS_ATT sca
425 WHERE sca.person_id = p_person_id AND
426 sca.course_cd = p_course_cd;
427 BEGIN
428 p_message_name := null;
429 -- Validate parameters
430 IF p_person_id IS NULL OR
431 p_course_cd IS NULL OR
432 p_transfer_dt IS NULL OR
433 p_crv_version_number IS NULL OR
434 p_course_attempt_status IS NULL THEN
435 p_message_name := 'IGS_GE_INSUFFICIENT_PARAMETER';
436 RETURN FALSE;
437 END IF;
438 -- Validate that course attempt is not mapped to a course version that
439 -- is defined as generic
440 OPEN c_cv;
441 FETCH c_cv INTO v_generic_course_ind;
442 IF (c_cv%NOTFOUND) THEN
443 -- return, this error will be resolved elsewhere
444 CLOSE c_cv;
445 p_message_name := null;
446 RETURN TRUE;
447 ELSE
448 IF (v_generic_course_ind = 'Y') THEN
449 CLOSE c_cv;
450 p_message_name := 'IGS_EN_TOPRG_NOT_MAP';
451 RETURN FALSE;
452 END IF;
453 END IF;
454 -- Validate course attempt status and related admission status
455 -- smaddali modified message name from 3942 to IGS_EN_TOPRG_NOT_LAPSED, bug#4080736
456 IF p_course_attempt_status IN (
457 cst_lapsed,
458 cst_completed) THEN
459 p_message_name := 'IGS_EN_TOPRG_NOT_LAPSED';
460 RETURN FALSE;
461 ELSIF
462 p_course_attempt_status = 'UNCONFIRM' THEN
463 OPEN c_aa;
464 FETCH c_aa INTO v_s_admission_process_type;
465 IF (c_aa%NOTFOUND) OR
466 v_s_admission_process_type <> cst_transfer THEN
467 CLOSE c_aa;
468 p_message_name := 'IGS_EN_TOPRG_NOT_UNCONFIRMED';
469 RETURN FALSE;
470 END IF;
471 CLOSE c_aa;
472 -- Cannot transfer if admission course application was not offered or
473 -- The applicant has already responded, and rejected, or the application
474 -- has lapsed
475 OPEN c_acai;
476 FETCH c_acai INTO v_adm_outcome_status,
477 v_adm_offer_resp_status;
478 IF (c_acai%NOTFOUND) THEN
479 CLOSE c_acai;
480 p_message_name := 'IGS_EN_TOPRG_NOT_ADMPRG_APPL';
481 RETURN FALSE;
482 ELSE
483 CLOSE c_acai;
484 -- Determine the admission course application is valid
485 -- for course Transfer
486 IF IGS_EN_GEN_002.ENRP_GET_ACAI_OFFER(
487 v_adm_outcome_status,
488 v_adm_offer_resp_status) = 'N' THEN
489 p_message_name := 'IGS_EN_TOPRG_NOTLINK_ADMPRG';
490 RETURN FALSE;
491 END IF;
492 END IF;
493 END IF;
494 -- Validate that student course transfer details do not already exist for
495 -- this course attempt Where the transfer to date is >= this transfer date
496 OPEN c_sct;
497 FETCH c_sct INTO v_dummy;
498 IF (c_sct%FOUND) THEN
499 CLOSE c_sct;
500 p_message_name := 'IGS_EN_TOPRG_TRNS_RELATIONSHI';
501 RETURN FALSE;
502 END IF;
503 CLOSE c_sct;
504 -- Validate that transfer date is on or after the course commencement date
505 -- and on or before today's date
506 -- IF p_transfer_dt > TRUNC(SYSDATE) THEN
507 OPEN c_sca;
508 FETCH c_sca INTO v_commencement_dt;
509 IF (c_sca%NOTFOUND) THEN
510 CLOSE c_sca;
511 p_message_name := null;
512 RETURN TRUE; -- this should never occur
513 ELSE
514 CLOSE c_sca;
515 IF v_commencement_dt IS NOT NULL AND
516 p_transfer_dt < v_commencement_dt THEN
517 p_message_name := 'IGS_EN_TRANSDT_NOTBE_PRIOR';
518 RETURN FALSE;
519 END IF;
520 END IF;
521
522 RETURN TRUE;
523 EXCEPTION
524 WHEN OTHERS THEN
525 IF (c_cv%ISOPEN) THEN
526 CLOSE c_cv;
527 END IF;
528 IF (c_aa%ISOPEN) THEN
529 CLOSE c_aa;
530 END IF;
531 IF (c_acai%ISOPEN) THEN
532 CLOSE c_acai;
533 END IF;
534 IF (c_sct%ISOPEN) THEN
535 CLOSE c_sct;
536 END IF;
537 IF (c_sca%ISOPEN) THEN
538 CLOSE c_sca;
539 END IF;
540 RAISE;
541 END;
542 EXCEPTION
543 WHEN OTHERS THEN
544 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
545 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCT.enrp_val_sct_to');
546 IGS_GE_MSG_STACK.ADD;
547 App_Exception.Raise_Exception;
548 END enrp_val_sct_to;
549 --
550 -- To validate student course transfer 'from' course code
551 FUNCTION enrp_val_sct_from(
552 p_person_id IN NUMBER ,
553 p_course_cd IN VARCHAR2 ,
554 p_transfer_course_cd IN VARCHAR2 ,
555 p_transfer_dt IN DATE ,
556 p_course_attempt_status IN VARCHAR2 ,
557 p_message_name OUT NOCOPY VARCHAR2)
558 RETURN BOOLEAN AS
559
560 --Change History :
561 --Who When What
562 --bdeviset 07-JAN-2005 Bug# 4103437.Modified cursor c_sct_sca. To avoid having another transfer
563 -- when a source program is already invloved in a stored transfer.
564
565 BEGIN -- enrp_val_sct_from
566 -- This module validates IGS_PS_STDNT_TRN.transfer_course_cd details.
567 -- Transfer_course_cd must map to IGS_EN_STDNT_PS_ATT whose
568 -- course_attempt_status is 'ENROLLED', 'DISCONTIN', 'INACTIVE',
569 -- 'INTERMIT', 'COMPLETED', 'LAPSED'.
570 -- Transfer_course_cd must not have existing IGS_PS_STDNT_TRN mapping
571 -- whose transfer_dt is >= this transfer_dt.
572 -- Transfer_course_cd is not currently transferred to IGS_EN_STDNT_PS_ATT
573 -- with course_attempt_status 'ENROLLED', 'INACTIVE', 'INTERMIT','LAPSED'.
574 DECLARE
575 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
576 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
577 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
578 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
579 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
580 v_dummy VARCHAR2(1);
581
582 --cursor to check if a source program is invloved in a stored transfer
583
584 CURSOR c_sct_sca IS
585 SELECT 'x'
586 FROM IGS_PS_STDNT_TRN sct
587 WHERE sct.person_id = p_person_id AND
588 (course_cd = p_transfer_course_cd OR
589 transfer_course_cd = p_transfer_course_cd) AND
590 status_flag = 'U';
591 BEGIN
592 p_message_name := null;
593 -- Validate parameters
594 IF (p_person_id IS NULL OR
595 p_course_cd IS NULL OR
596 p_transfer_course_cd IS NULL OR
597 p_transfer_dt IS NULL OR
598 p_course_attempt_status IS NULL) THEN
599 p_message_name := 'IGS_GE_INSUFFICIENT_PARAMETER';
600 RETURN FALSE;
601 END IF;
602 /*
603 -- validate course_attempt_status
604 IF p_course_attempt_status = cst_unconfirm THEN
605 p_message_name := 'IGS_EN_FROMPRG_NOT_UNCONFIRM';
606 RETURN FALSE;
607 END IF;
608 */
609
610 -- Validate that transfer 'from' course attempt is not currently transferred
611 -- to course attempt with enrolled, inactive or intermission course attempt
612 -- status.
613 OPEN c_sct_sca;
614 FETCH c_sct_sca INTO v_dummy;
615 IF (c_sct_sca%FOUND) THEN
616 CLOSE c_sct_sca;
617 p_message_name := 'IGS_EN_FROMPRG_TRNS_RELATION';
618 RETURN FALSE;
619 END IF;
620 CLOSE c_sct_sca;
621 RETURN TRUE;
622 EXCEPTION
623 WHEN OTHERS THEN
624 IF (c_sct_sca%ISOPEN) THEN
625 CLOSE c_sct_sca;
626 END IF;
627 RAISE;
628 END;
629 EXCEPTION
630 WHEN OTHERS THEN
631 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
632 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCT.enrp_val_sct_from');
633 IGS_GE_MSG_STACK.ADD;
634 App_Exception.Raise_Exception;
635 END enrp_val_sct_from;
636 --
637 -- To validate transfer of SUA.
638 FUNCTION enrp_val_sua_trnsfr(
639 p_person_id IN NUMBER ,
640 p_course_cd IN VARCHAR2 ,
641 p_unit_cd IN VARCHAR2 ,
642 p_cal_type IN VARCHAR2 ,
643 p_ci_sequence_number IN NUMBER ,
644 p_unit_attempt_status IN VARCHAR2 ,
645 p_message_name OUT NOCOPY VARCHAR2,
646 p_uoo_id IN NUMBER,
647 p_unit_outcome OUT NOCOPY VARCHAR2)
648 RETURN BOOLEAN AS
649 /*
650 || Created By : pkpatel
651 || Created On : 27-SEP-2002
652 || Purpose :
653 || Known limitations, enhancements or remarks :
654 || Change History :
655 || Who When What
656 || kkillams 21-03-2003 Modified validation, to bypass the IGS_EN_SUA_ENR_COMPL_DISCN_DU error message
657 || for the wait list units w.r.t bug 2863707
658 || kkillams 28-04-2003 Added new parameter p_uoo_id to the enrp_val_sua_trnsfr function
659 || w.r.t. bug number 2829262
660 || ckasu 20-Nov-2004 added new parameter p_unit_outcome as a part of Program Transfer build bug#4000939
661 || ckasu 06-Dec-2004 removed IF p_outcome <> 'FAIL' THEN as a part of bug#4048248 inorder to transfer
662 || discontinue unit attempt with result other than fail from source prgm to dest prgm.
663 */
664 BEGIN -- enrp_val_sua_trnsfr
665 -- This module validates that only student_unit_attempts with
666 -- unit_attempt_status values of 'ENROLLED', 'COMPLETED',
667 -- 'DISCONTIN' and 'DUPLICATE', can transferred as a result of
668 -- a course transfer.
669
670 DECLARE
671 cst_completed CONSTANT VARCHAR(9) := 'COMPLETED';
672 cst_enrolled CONSTANT VARCHAR(9) := 'ENROLLED';
673 cst_discontin CONSTANT VARCHAR(9) := 'DISCONTIN';
674 cst_duplicate CONSTANT VARCHAR(9) := 'DUPLICATE';
675 cst_fail CONSTANT VARCHAR(9) := 'FAIL';
676 cst_waitlist CONSTANT VARCHAR2(10) := 'WAITLISTED';
677 cst_invalid CONSTANT VARCHAR2(10) := 'INVALID';
678 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
679 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
680 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
681 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
682 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
683 v_origin_course_cd IGS_AS_SU_STMPTOUT.course_cd%TYPE;
684 v_s_result_type IGS_LOOKUPS_VIEW.lookup_code%TYPE;
685 BEGIN
686
687 p_message_name := null;
688 IF p_unit_attempt_status NOT IN (
689 cst_completed,
690 cst_enrolled,
691 cst_discontin,
692 cst_duplicate,
693 cst_waitlist,
694 cst_invalid) THEN
695 p_message_name := 'IGS_EN_SUA_ENR_COMPL_DISCN_DU';
696 RETURN FALSE;
697 END IF;
698 IF p_unit_attempt_status = cst_discontin THEN
699 p_unit_outcome := IGS_AS_GEN_003.ASSP_GET_SUA_OUTCOME(
700 p_person_id,
701 p_course_cd,
702 p_unit_cd,
703 p_cal_type,
704 p_ci_sequence_number,
705 p_unit_attempt_status,
706 'Y',
707 v_outcome_dt,
708 v_grading_schema_cd,
709 v_gs_version_number,
710 v_grade,
711 v_mark,
712 v_origin_course_cd,
713 p_uoo_id,
714 --added by LKAKI---
715 'N');
716
717 END IF;
718 RETURN TRUE;
719 END;
720 EXCEPTION
721 WHEN OTHERS THEN
722 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
723 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCT.enrp_val_sua_trnsfr');
724 IGS_GE_MSG_STACK.ADD;
725 App_Exception.Raise_Exception;
726 END enrp_val_sua_trnsfr;
727 --
728 -- To validate a student unit set attempt exists.
729 FUNCTION enrp_val_susa_exists(
730 p_person_id IN NUMBER ,
731 p_course_cd IN VARCHAR2 ,
732 p_unit_set_cd IN VARCHAR2 ,
733 p_us_version_number IN NUMBER ,
734 p_message_name OUT NOCOPY VARCHAR2)
735 RETURN BOOLEAN AS
736
737 BEGIN -- enrp_val_susa_exist
738 -- Check that a student unit set attempt which
739 -- is to be created as a result of a course transfer.
740 DECLARE
741 CURSOR c_susa IS
742 SELECT 'x'
743 FROM IGS_AS_SU_SETATMPT
744 WHERE person_id = p_person_id AND
745 course_cd = p_course_cd AND
746 unit_set_cd = p_unit_set_cd AND
747 us_version_number = p_us_version_number;
748 v_susa_found VARCHAR2(1) DEFAULT NULL;
749 BEGIN
750 p_message_name := null;
751 -- Check parameters
752 IF p_person_id IS NULL OR
753 p_course_cd IS NULL OR
754 p_unit_set_cd IS NULL OR
755 p_us_version_number IS NULL THEN
756 RETURN TRUE;
757 END IF;
758 -- Check whether student unit attempt already exist.
759 -- This could be as a result of a proir transfer or admissions pre-enrolment.
760 OPEN c_susa;
761 FETCH c_susa INTO v_susa_found;
762 IF (c_susa%FOUND) THEN
763 CLOSE c_susa;
764 p_message_name := 'IGS_EN_SUA_SET_ATT_TRNS_EXIST';
765 RETURN FALSE;
766 END IF;
767 CLOSE c_susa;
768 RETURN TRUE;
769 EXCEPTION
770 WHEN OTHERS THEN
771 IF (c_susa%ISOPEN) THEN
772 CLOSE c_susa;
773 END IF;
774 RAISE;
775 END;
776 EXCEPTION
777 WHEN OTHERS THEN
778 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
779 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCT.enrp_val_susa_exists');
780 IGS_GE_MSG_STACK.ADD;
781 App_Exception.Raise_Exception;
782 END enrp_val_susa_exists;
783
784 END ;
[Home] [Help]