1 PACKAGE BODY IGS_FI_VAL_CFT AS
2 /* $Header: IGSFI13B.pls 115.6 2002/11/29 00:17:27 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_cty_closed"
7 -------------------------------------------------------------------------------------------
8 --
9 --
10 -- bug id : 1956374
11 -- sjadhav , 28-aug-2001
12 -- removed enrp_val_att_closed
13 --
14
15 -- Ensure IGS_PS_COURSE fee triggers can be created.
16 FUNCTION finp_val_cft_ins(
17 p_fee_type IN VARCHAR2 ,
18 p_message_name OUT NOCOPY VARCHAR2 )
19 RETURN BOOLEAN AS
20 gv_other_detail VARCHAR2(255);
21 BEGIN -- finp_val_cft_ins
22 -- Validate IGS_PS_FEE_TRG.IGS_FI_FEE_TYPE.s_fee_trigger_cat = IGS_PS_COURSE or COMPOSITE
23 -- otherwise IGS_PS_COURSE fee triggers cannot be defined.
24 DECLARE
25 CURSOR c_ft(
26 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
27 SELECT s_fee_trigger_cat
28 FROM IGS_FI_FEE_TYPE
29 WHERE fee_type= cp_fee_type;
30 v_ft_rec c_ft%ROWTYPE;
31 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
32 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
33 BEGIN
34 -- Set the default message number
35 p_message_name := Null;
36 -- Check parameters
37 IF p_fee_type IS NULL THEN
38 RETURN TRUE;
39 END IF;
40 -- Get the system fee trigger category of the IGS_FI_FEE_TYPE.
41 OPEN c_ft (p_fee_type);
42 FETCH c_ft INTO v_ft_rec;
43 IF c_ft%NOTFOUND THEN
44 CLOSE c_ft;
45 RETURN TRUE;
46 END IF;
47 CLOSE c_ft;
48 IF v_ft_rec.s_fee_trigger_cat <> cst_course AND
49 v_ft_rec.s_fee_trigger_cat <> cst_composite THEN
50 p_message_name := 'IGS_FI_PRGFEETRG_COURSE_COMPO';
51 RETURN FALSE;
52 END IF;
53 -- Return the default value
54 RETURN TRUE;
55 END;
56 END finp_val_cft_ins;
57
58 -- Ensure only one open IGS_PS_FEE_TRG record exists.
59 FUNCTION finp_val_cft_open(
60 p_fee_cat IN VARCHAR2 ,
61 p_fee_cal_type IN VARCHAR2 ,
62 p_fee_ci_sequence_number IN NUMBER ,
63 p_fee_type IN VARCHAR2 ,
64 p_course_cd IN VARCHAR2 ,
65 p_sequence_number IN NUMBER ,
66 p_version_number IN NUMBER ,
67 p_cal_type IN VARCHAR2 ,
68 p_location_cd IN VARCHAR2 ,
69 p_attendance_mode IN VARCHAR2 ,
70 p_attendance_type IN VARCHAR2 ,
71 p_create_dt IN DATE ,
72 p_fee_trigger_group_number IN NUMBER ,
73 p_message_name OUT NOCOPY VARCHAR2 )
74 RETURN BOOLEAN AS
75 gv_other_detail VARCHAR2(255);
76 BEGIN -- finp_val_cft_open
77 -- Validate that there are no other "open-ended" IGS_PS_FEE_TRG records for
78 -- the nominated course_cd details and the same parent IGS_FI_F_CAT_FEE_LBL.
79 DECLARE
80 CURSOR c_cft IS
81 SELECT 'x'
82 FROM IGS_PS_FEE_TRG cft
83 WHERE cft.fee_cat = p_fee_cat AND
84 cft.fee_cal_type = p_fee_cal_type AND
85 cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
86 cft.fee_type = p_fee_type AND
87 cft.course_cd = p_course_cd AND
88 cft.sequence_number <> NVL(p_sequence_number,0) AND
89 NVL(cft.version_number,0) = NVL(p_version_number,0) AND
90 NVL(cft.cal_type,'NULL') = NVL(p_cal_type,'NULL') AND
91 NVL(cft.location_cd,'NULL') = NVL(p_location_cd,'NULL') AND
92 NVL(cft.attendance_mode,'NULL') = NVL(p_attendance_mode,'NULL') AND
93 NVL(cft.attendance_type,'NULL') = NVL(p_attendance_type,'NULL') AND
94 cft.create_dt <> p_create_dt AND
95 NVL(cft.fee_trigger_group_number,0) = NVL(p_fee_trigger_group_number,0) AND
96 cft.logical_delete_dt IS NULL;
97 v_check CHAR;
98 BEGIN
99 --- Set the default message number
100 p_message_name := Null;
101 IF p_fee_cat IS NULL OR
102 p_fee_cal_type IS NULL OR
103 p_fee_ci_sequence_number IS NULL OR
104 p_fee_type IS NULL OR
105 p_course_cd IS NULL OR
106 p_create_dt IS NULL THEN
107 RETURN TRUE;
108 END IF;
109 OPEN c_cft;
110 FETCH c_cft INTO v_check;
111 IF (c_cft%FOUND) THEN
112 CLOSE c_cft;
113 p_message_name := 'IGS_GE_DUPLICATE_VALUE';
114 RETURN FALSE;
115 END IF;
116 CLOSE c_cft;
117 RETURN TRUE;
118 END;
119 END finp_val_cft_open;
120 --
121 --
122 -- Validate IGS_PS_COURSE code has an 'ACTIVE' or 'PLANNED' version.
123 FUNCTION finp_val_cft_crs(
124 p_course_cd IN VARCHAR2 ,
125 p_message_name OUT NOCOPY VARCHAR2 )
126 RETURN BOOLEAN AS
127 gv_other_detail VARCHAR2(255);
128 BEGIN -- finp_val_cft_crs
129 -- Validate IGS_PS_FEE_TRG.course_cd. Course code must have at least one
130 -- version which has a status of 'ACTIVE' or 'PLANNED'
131 DECLARE
132 cst_planned CONSTANT VARCHAR2(10) := 'PLANNED';
133 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
134 v_dummy VARCHAR2(1);
135 CURSOR c_course_version_status (
136 cp_course_cd IGS_PS_FEE_TRG.course_cd%TYPE) IS
137 SELECT 'x'
138 FROM IGS_PS_VER crv,
139 IGS_PS_STAT cs
140 WHERE crv.course_cd = cp_course_cd AND
141 crv.course_status = cs.course_status AND
142 cs.s_course_status IN (cst_active, cst_planned);
143 BEGIN
144 p_message_name := Null;
145 IF(p_course_cd IS NULL) THEN
146 RETURN TRUE;
147 END IF;
148 OPEN c_course_version_status(
149 p_course_cd);
150 FETCH c_course_version_status INTO v_dummy;
151 IF(c_course_version_status%NOTFOUND) THEN
152 CLOSE c_course_version_status;
153 p_message_name := 'IGS_FI_PRGCD_PLANNED_ACTIVE';
154 RETURN FALSE;
155 END IF;
156 CLOSE c_course_version_status;
157 RETURN TRUE;
158 END;
159 END finp_val_cft_crs;
160 --
161 -- Validate calendar type has a system category of 'ACADEMIC'.
162 FUNCTION finp_val_ct_academic(
163 p_cal_type IN VARCHAR2 ,
164 p_message_name OUT NOCOPY VARCHAR2 )
165 RETURN BOOLEAN AS
166 gv_other_detail VARCHAR2(255);
167 BEGIN -- finp_val_ct_academic
168 -- Validates that the IGS_PS_FEE_TRG.IGS_CA_TYPE has a system calendar
169 -- category of 'ACADEMIC'
170 DECLARE
171 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
172 v_dummy VARCHAR2(1);
173 CURSOR c_cal_type (
174 cp_cal_type IGS_CA_TYPE.cal_type%TYPE) IS
175 SELECT 'x'
176 FROM IGS_CA_TYPE cat
177 WHERE cat.cal_type = cp_cal_type AND
178 cat.s_cal_cat = cst_academic;
179 BEGIN
180 p_message_name := Null;
181 IF(p_cal_type IS NULL) THEN
182 RETURN TRUE;
183 END IF;
184 OPEN c_cal_type(
185 p_cal_type);
186 FETCH c_cal_type INTO v_dummy;
187 IF(c_cal_type%NOTFOUND) THEN
188 CLOSE c_cal_type;
189 p_message_name := 'IGS_FI_CALTYPE_CAT_ACADEMIC';
190 RETURN FALSE;
191 END IF;
192 CLOSE c_cal_type;
193 RETURN TRUE;
194 END;
195 END finp_val_ct_academic;
196
197 --
198 -- Validate the Calendar Type closed ind
199 FUNCTION calp_val_cat_closed(
200 p_cal_type IN VARCHAR2 ,
201 p_message_name OUT NOCOPY VARCHAR2 )
202 RETURN BOOLEAN AS
203 gv_other_detail VARCHAR2(255);
204 BEGIN
205 DECLARE
206 CURSOR c_cat IS
207 SELECT closed_ind
208 FROM IGS_CA_TYPE
209 WHERE cal_type = p_cal_type;
210 v_cat_rec c_cat%ROWTYPE;
211 BEGIN
212 -- Check if the IGS_CA_TYPE is closed
213 -- Set the default message number
214 p_message_name := Null;
215 -- Cursor handling
216 OPEN c_cat ;
217 FETCH c_cat INTO v_cat_rec;
218 IF c_cat%NOTFOUND THEN
219 CLOSE c_cat;
220 RETURN TRUE;
221 END IF;
222 CLOSE c_cat;
223 IF (v_cat_rec.closed_ind = 'Y') THEN
224 p_message_name := 'IGS_CA_CALTYPE_CLOSED';
225 RETURN FALSE;
226 END IF;
227 -- Return the default value
228 RETURN TRUE;
229 END;
230 END calp_val_cat_closed;
231 --
232 -- bug id : 1956374
233 -- sjadhav , 28-aug-2001
234 -- removed enrp_val_att_closed
235 --
236 -- Validate IGS_PS_COURSE fee trigger can belong to a fee trigger group.
237 FUNCTION finp_val_cft_ftg(
238 p_fee_cat IN IGS_FI_FEE_CAT_ALL.fee_cat%TYPE ,
239 p_fee_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
240 p_fee_ci_sequence_num IN NUMBER ,
241 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
242 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE,
243 p_fee_trigger_group_num IN NUMBER ,
244 p_message_name OUT NOCOPY VARCHAR2 )
245 RETURN BOOLEAN AS
246 gv_other_detail VARCHAR2(255);
247 BEGIN -- finp_val_cft_ftg
248 -- Validate IGS_PS_FEE_TRG can belong to a IGS_FI_FEE_TRG_GRP
249 DECLARE
250 v_dummy VARCHAR2(1);
251 CURSOR c_ft(
252 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE)IS
253 SELECT s_fee_trigger_cat
254 FROM IGS_FI_FEE_TYPE
255 WHERE fee_type= cp_fee_type;
256 v_ft_rec c_ft%ROWTYPE;
257 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
258 CURSOR c_cft( cp_fee_cat IGS_PS_FEE_TRG.fee_cat%TYPE,
259 cp_fee_cal_type IGS_PS_FEE_TRG.fee_cal_type%TYPE,
260 cp_fee_ci_sequence_num IGS_PS_FEE_TRG.fee_ci_sequence_number%TYPE,
261 cp_fee_type IGS_PS_FEE_TRG.fee_type%TYPE,
262 cp_course_cd IGS_PS_FEE_TRG.course_cd%TYPE,
263 cp_fee_trigger_group_num
264 IGS_PS_FEE_TRG.fee_trigger_group_number%TYPE) IS
265 SELECT 'x'
266 FROM IGS_PS_FEE_TRG cft
267 WHERE cft.fee_cat= cp_fee_cat AND
268 cft.fee_cal_type = cp_fee_cal_type AND
269 cft.fee_ci_sequence_number = cp_fee_ci_sequence_num AND
270 cft.fee_type = cp_fee_type AND
271 cft.course_cd <> cp_course_cd AND
272 cft.fee_trigger_group_number = cp_fee_trigger_group_num AND
273 cft.logical_delete_dt IS NULL;
274 BEGIN
275 p_message_name := Null;
276 IF(p_fee_cat IS NULL OR
277 p_fee_cal_type IS NULL OR
278 p_fee_ci_sequence_num IS NULL OR
279 p_fee_type IS NULL OR
280 p_course_cd IS NULL OR
281 p_fee_trigger_group_num IS NULL) THEN
282 RETURN TRUE;
283 END IF;
284 -- Get the system fee trigger category of the IGS_FI_FEE_TYPE.
285 OPEN c_ft (p_fee_type);
286 FETCH c_ft INTO v_ft_rec;
287 IF c_ft%NOTFOUND THEN
288 CLOSE c_ft;
289 RETURN TRUE;
290 END IF;
291 CLOSE c_ft;
292 IF v_ft_rec.s_fee_trigger_cat <> cst_composite THEN
293 p_message_name := 'IGS_FI_PRG_FEETRG_COMPOSITE';
294 RETURN FALSE;
295 END IF;
296 OPEN c_cft( p_fee_cat,
297 p_fee_cal_type,
298 p_fee_ci_sequence_num,
299 p_fee_type,
300 p_course_cd,
301 p_fee_trigger_group_num);
302 FETCH c_cft INTO v_dummy;
303 IF(c_cft%FOUND) THEN
304 CLOSE c_cft;
305 p_message_name := 'IGS_FI_PRGFEE_TRG_FEE_TRG_GRP';
306 RETURN FALSE;
307 END IF;
308 CLOSE c_cft;
309 RETURN TRUE;
310 END;
311 end FINP_VAL_CFT_FTG;
312 END IGS_FI_VAL_CFT;
[Home] [Help]