1 PACKAGE BODY IGS_EN_VAL_SUT AS
2 /* $Header: IGSEN70B.pls 120.0 2005/06/01 14:43:00 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 25-AUG-2001 Bug No. 1956374 .The function GENP_VAL_SDTT_SESS removed
7 --amuthu 14-JUL-2004 Allowing the drop of duplicate unit attempt as
8 -- part of IGS.M bug 3765628/ IGS.L.#R bug 3703889
9 -- Modified the logic in enrp_val_sut_delete to correctly
10 -- check if unit has been transfered to another program
11 -- before deleting it.
12 --ckasu 06-Dec-2004 modified enrp_val_sut_insert procedure as a part of bug#4048248
13 -- inorder to transfer discontinue unit attempt with result other
14 -- than fail from source prgm to dest prgm.
15 -- smaddali 16-dec-04 Modified enrp_val_sut_insert for bug#4063726.
16 -------------------------------------------------------------------------------------------
17 -- To validate for student unit transfer on delete.
18 FUNCTION enrp_val_sut_delete(
19 p_person_id IN NUMBER ,
20 p_course_cd IN VARCHAR2 ,
21 p_unit_cd IN VARCHAR2 ,
22 p_cal_type IN VARCHAR2 ,
23 p_ci_sequence_number IN NUMBER ,
24 p_message_name OUT NOCOPY VARCHAR2,
25 p_uoo_id IN NUMBER)
26 RETURN BOOLEAN AS
27 -------------------------------------------------------------------------------------------
28 --Change History:
29 --Who When What
30 --kkillams 28-04-2003 New parameter p_uoo_id is added to the function and modified c_sua
31 -- cursor where clause w.r.t. bug number 2829262
32 -------------------------------------------------------------------------------------------
33 BEGIN -- enrp_val_sut_delete
34 -- This module validates the delete of IGS_PS_STDNT_UNT_TRN details.
35 DECLARE
36 CURSOR c_sua IS
37 SELECT 'X'
38 FROM IGS_PS_STDNT_UNT_TRN sut, igs_en_su_attempt sua
39 WHERE sut.person_id = p_person_id AND
40 sut.transfer_course_cd = p_course_cd AND
41 sut.uoo_id = p_uoo_id AND
42 sua.person_id = sut.person_id AND
43 sua.course_cd = sut.transfer_course_cd AND
44 sua.uoo_id = sut.uoo_id;
45 v_dummy VARCHAR2(1);
46 BEGIN
47 -- Set the default message number
48 p_message_name := null;
49 -- Validate that related DUPLICATE student unit attempt
50 -- does not exist.
51 OPEN c_sua;
52 FETCH c_sua INTO v_dummy;
53 IF c_sua%FOUND THEN
54 -- Prevent deletion of transfer link
55 CLOSE c_sua;
56 p_message_name := 'IGS_EN_STUDUNIT_TRNS_NOTDEL';
57 RETURN FALSE;
58 END IF;
59 CLOSE c_sua;
60 RETURN TRUE;
61 EXCEPTION
62 WHEN OTHERS THEN
63 IF c_sua%ISOPEN THEN
64 CLOSE c_sua;
65 END IF;
66 RAISE;
67 END;
68 EXCEPTION
69 WHEN OTHERS THEN
70 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
71 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUT.enrp_val_sut_delete');
72 IGS_GE_MSG_STACK.ADD;
73 App_Exception.Raise_Exception;
74 END enrp_val_sut_delete;
75 --
76 -- To validate for student unit transfer on insert.
77 FUNCTION enrp_val_sut_insert(
78 p_person_id IN NUMBER ,
79 p_course_cd IN VARCHAR2 ,
80 p_transfer_course_cd IN VARCHAR2 ,
81 p_unit_cd IN VARCHAR2 ,
82 p_cal_type IN VARCHAR2 ,
83 p_ci_sequence_number IN NUMBER ,
84 p_message_name OUT NOCOPY VARCHAR2,
85 p_uoo_id IN NUMBER)
86 RETURN BOOLEAN AS
87 -------------------------------------------------------------------------------------------
88 -- enrp_val_sut_insert
89 -- This module validates the insert of IGS_PS_STDNT_UNT_TRN details
90 -- * Transferred 'from' IGS_PS_UNIT must have unit_attempt_status 'COMPLETED',
91 -- 'DUPLICATE', ('DISCONTIN' and IGS_EN_SU_ATTEMPT has a result of
92 -- 'FAIL').
93 -- * Cannot insert if transferred ?to? IGS_PS_UNIT maps to IGS_EN_STDNT_PS_ATT
94 -- with course_attempt_status 'UNCONFIRM', 'DISCONTIN', 'LAPSED',
95 -- 'COMPLETED'.
96 -- IGS_GE_NOTE: all statuses with the exception of unconfirmed are handled in
97 -- ENRP_VAL_SCT_TO validation.
98 --Change History:
99 --Who When What
100 --kkillams 28-04-2003 New parameter p_uoo_id is added to the function and modified c_sua
101 -- cursor where clause w.r.t. bug number 2829262
102 --ckasu 06-Dec-2004 removed ELSIF condition as aprt of bug#4048248 inorder to transfer
103 -- discontinue unit attempt with result other than fail from source prgm
104 -- to dest prgm.
105 -- smaddali 16-dec-04 Modified the validation to allow creation of transfer records for
106 -- ENROLLED and INVALID unit attempts. bug#4063726
107 -------------------------------------------------------------------------------------------
108
109 BEGIN
110 DECLARE
111 cst_dropped CONSTANT VARCHAR2(10) := 'DROPPED';
112 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
113 CURSOR c_sua_from IS
114 SELECT sua.unit_attempt_status
115 FROM IGS_EN_SU_ATTEMPT sua
116 WHERE sua.person_id = p_person_id AND
117 sua.course_cd = p_transfer_course_cd AND
118 sua.uoo_id = p_uoo_id ;
119 CURSOR c_sca IS
120 SELECT sca.course_attempt_status
121 FROM IGS_EN_STDNT_PS_ATT sca
122 WHERE sca.person_id = p_person_id AND
123 sca.course_cd = p_course_cd;
124 v_from_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
125 v_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
126 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
127 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
128 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
129 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
130 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
131 v_origin_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
132 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
133 BEGIN
134 p_message_name := null;
135 -- Get student unit attempt of transfer 'from' IGS_PS_UNIT
136 OPEN c_sua_from;
137 FETCH c_sua_from INTO v_from_unit_attempt_status;
138 IF (c_sua_from%NOTFOUND) THEN
139 -- This is a foreign key constraint and will be handled elsewhere
140 CLOSE c_sua_from;
141 p_message_name := null;
142 RETURN TRUE;
143 ELSE
144 CLOSE c_sua_from;
145 -- Can only transfer duplicate, completed and discontinued units
146 -- smaddali modified this validation to enable transfer of enrolled and invalid unit attempts also. bug#4063726
147 IF v_from_unit_attempt_status IN (
148 cst_unconfirm,
149 cst_dropped ) THEN
150 p_message_name := 'IGS_EN_TRNS_FROMUNIT_NOTCONFI';
151 RETURN FALSE;
152 END IF;
153 END IF;
154 OPEN c_sca;
155 FETCH c_sca INTO v_course_attempt_status;
156 IF (c_sca%NOTFOUND) THEN
157 -- This could not happen without above student unit attempt select failing
158 CLOSE c_sca;
159 p_message_name := null;
160 RETURN TRUE;
161 ELSE
162 CLOSE c_sca;
163 IF v_course_attempt_status = cst_unconfirm THEN
164 p_message_name := 'IGS_EN_NOTTRNS_UA_UNCONFIRM';
165 RETURN FALSE;
166 END IF;
167 END IF;
168 RETURN TRUE;
169 EXCEPTION
170 WHEN OTHERS THEN
171 IF (c_sua_from%ISOPEN) THEN
172 CLOSE c_sua_from;
173 END IF;
174 IF (c_sca%ISOPEN) THEN
175 CLOSE c_sca;
176 END IF;
177 RAISE;
178 END;
179 EXCEPTION
180 WHEN OTHERS THEN
181 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
182 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUT.enrp_val_sut_insert');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END enrp_val_sut_insert;
186 END IGS_EN_VAL_SUT;
[Home] [Help]