1 PACKAGE BODY IGS_FI_VAL_ER AS
2 /* $Header: IGSFI20B.pls 115.7 2002/11/29 00:18:43 nsidana ship $ */
3 /* Who When What
4 jbegum 05-Mar-2002 Modified the logic of function finp_val_er_ovrlp
5 The logic was changed as part of bug fix for
6 bug #2117296.
7 --sbaliga 20-feb-2002 Modified the finp_val_er_ovrlp procedure
8 -- bug no-2231567 */
9 -- Validate elements ranges can be created for the relation type.
10 FUNCTION finp_val_er_defn(
11 p_fee_type IN VARCHAR2 ,
12 p_fee_cal_type IN VARCHAR2 ,
13 p_fee_ci_sequence_number IN NUMBER ,
14 p_s_relation_type IN VARCHAR2 ,
15 p_message_name OUT NOCOPY VARCHAR2 )
16 RETURN BOOLEAN AS
17 gv_other_detail VARCHAR2(255);
18 BEGIN -- finp_val_er_defn
19 -- Validate if IGS_FI_ELM_RANGE records can be created.
20 -- When defined at FTCI level, they cannot also be
21 -- defined at FCFL level and vice-versa.
22 DECLARE
23 CURSOR c_er (
24 cp_s_relation_type IGS_FI_ELM_RANGE.s_relation_type%TYPE) IS
25 SELECT 'x'
26 FROM IGS_FI_ELM_RANGE
27 WHERE fee_type = p_fee_type AND
28 fee_cal_type = p_fee_cal_type AND
29 fee_ci_sequence_number = p_fee_ci_sequence_number AND
30 s_relation_type = cp_s_relation_type AND
31 logical_delete_dt IS NULL;
32 v_fcfl_exists VARCHAR2(1);
33 v_ftci_exists VARCHAR2(1);
34 BEGIN
35 -- Set the default message number
36 p_message_name := Null;
37 -- 1. Check Parameters
38 IF p_fee_type IS NULL OR
39 p_fee_cal_type IS NULL OR
40 p_fee_ci_sequence_number IS NULL OR
41 p_s_relation_type IS NULL THEN
42 RETURN TRUE;
43 END IF;
44 -- 2. If p_s_relation_type = ?FCFL?, check if any IGS_FI_ELM_RANGE records
45 -- have been defined at the FTCI level. If so, return error.
46 IF p_s_relation_type = 'FCFL' THEN
47 OPEN c_er(
48 'FTCI');
49 FETCH c_er INTO v_ftci_exists;
50 IF c_er%FOUND THEN
51 CLOSE c_er;
52 p_message_name := 'IGS_FI_ELERNG_ND_FEECATFEELIA';
53 RETURN FALSE;
54 END IF;
55 CLOSE c_er;
56 END IF;
57 -- 3. If p_s_relation_type = ?FTCI?, check if any IGS_FI_ELM_RANGE records
58 -- have been defined at the FCFL level. If so, return error.
59 IF p_s_relation_type = 'FTCI' THEN
60 OPEN c_er(
61 'FCFL');
62 FETCH c_er INTO v_fcfl_exists;
63 IF c_er%FOUND THEN
64 CLOSE c_er;
65 p_message_name := 'IGS_FI_ELERNG_ND_FEETYPECALIN';
66 RETURN FALSE;
67 END IF;
68 CLOSE c_er;
69 END IF;
70 RETURN TRUE;
71 END;
72 END finp_val_er_defn;
73 --
74 -- Ensure elements range values do not overlap.
75 FUNCTION finp_val_er_ovrlp(
76 p_fee_type IN VARCHAR2 ,
77 p_fee_cal_type IN VARCHAR2 ,
78 p_fee_ci_sequence_number IN NUMBER ,
79 p_s_relation_type IN VARCHAR2 ,
80 p_fee_cat IN VARCHAR2 ,
81 p_range_number IN NUMBER ,
82 p_lower_range IN NUMBER ,
83 p_upper_range IN NUMBER ,
84 p_message_name OUT NOCOPY VARCHAR2 )
85 RETURN BOOLEAN AS
86 gv_other_detail VARCHAR2(255);
87 /***********************************************************************
88 Change History
89 Who When What
90 jbegum 05-Mar-2002 Modified the logic of function finp_val_er_ovrlp
91 The logic was changed as part of bug fix for
92 bug #2117296.
93 The existing logic was assuming the lower_range field
94 to be mandatory and only checking for the cases where
95 upper_range field was Null or Not Null.
96 The logic did not handle the cases where the lower_range
97 field is Null.
98 sbaliga 20-feb-2002 Added check for case when both ranges are open
99 ranges-bug no 2231567
100 *************************************************************************/
101 BEGIN
102 DECLARE
103 CURSOR c_er IS
104 SELECT er.lower_range,
105 er.upper_range
106 FROM IGS_FI_ELM_RANGE er
107 WHERE er.fee_type = p_fee_type AND
108 er.fee_cal_type = p_fee_cal_type AND
109 er.fee_ci_sequence_number = p_fee_ci_sequence_number AND
110 er.s_relation_type = p_s_relation_type AND
111 nvl(er.fee_cat,'NULL') = nvl(p_fee_cat,'NULL') AND
112 er.range_number <> nvl(p_range_number,0) AND
113 er.logical_delete_dt IS NULL;
114 v_message_name VARCHAR2(30) := Null;
115 BEGIN
116 -- Validate that IGS_FI_ELM_RANGE.lower_range and upper_range do not overlap with
117 -- existing records with the same fee_type, fee_cal_type,
118 -- fee_ci_sequence_number, s_relation_type (FTCI and FCFL) and
119 -- fee_cat (FCFL only)
120 --- Set the default message number
121
122 p_message_name := NULL;
123
124 FOR v_er_rec IN c_er LOOP
125
126 -- Existing records with closed ranges
127 IF v_er_rec.upper_range IS NOT NULL AND v_er_rec.lower_range IS NOT NULL THEN
128
129 -- If the lower range is between an existing lower to upper range
130 IF (p_lower_range >= v_er_rec.lower_range AND
131 p_lower_range <= v_er_rec.upper_range) THEN
132 v_message_name := 'IGS_FI_RO_LOWRNG_LOWER';
133 EXIT;
134 END IF;
135 -- If the upper range is between an existing lower to upper range
136 IF (p_upper_range >= v_er_rec.lower_range AND
137 p_upper_range <= v_er_rec.upper_range) THEN
138 v_message_name := 'IGS_FI_RO_UPPERRNG_LOWER';
139 EXIT;
140 END IF;
141 -- If lower and upper ranges encompass an existing lower to upper range
142 IF (p_lower_range <= v_er_rec.lower_range AND
143 p_upper_range >= v_er_rec.upper_range) THEN
144 v_message_name := 'IGS_FI_RO_LOWRNG_ENCOMPASS';
145 EXIT;
146 END IF;
147 -- If open range overlaps with an existing elements range
148 IF p_upper_range IS NULL THEN
149 IF (p_lower_range <= v_er_rec.lower_range OR
150 p_lower_range <= v_er_rec.upper_range) THEN
151 v_message_name := 'IGS_FI_OPEN_RO_LOWRNG_LOWER';
152 EXIT;
153 END IF;
154 ELSIF p_lower_range IS NULL THEN
155 IF (p_upper_range >= v_er_rec.lower_range OR
156 p_upper_range >= v_er_rec.upper_range) THEN
157 v_message_name := 'IGS_FI_OPEN_RO_LOWRNG_LOWER';
158 EXIT;
159 END IF;
160 END IF;
161 -- Existing records with open ranges (ie. the lower_range is not set )
162 ELSIF v_er_rec.upper_range IS NOT NULL THEN
163 IF (p_lower_range <= v_er_rec.upper_range OR
164 p_upper_range <= v_er_rec.upper_range) THEN
165 v_message_name := 'IGS_FI_RO_RNG_OVERLAPS';
166 EXIT;
167 END IF;
168 -- Existing records with open ranges (ie. the upper_range is not set )
169 ELSIF v_er_rec.lower_range IS NOT NULL THEN
170 IF (p_lower_range >= v_er_rec.lower_range OR
171 p_upper_range >= v_er_rec.lower_range) THEN
172 v_message_name := 'IGS_FI_RO_RNG_OVERLAPS';
173 EXIT;
174 END IF;
175 END IF;
176
177 END LOOP;
178 IF v_message_name IS NOT NULL THEN
179 p_message_name := v_message_name;
180 RETURN FALSE;
181 END IF;
182 RETURN TRUE;
183 END;
184 END finp_val_er_ovrlp;
185 --
186 -- Ensure elements range rate can be created.
187 -- Duplicate code removal, msrinivi Removed proc finp_val_err_ins
188 -- Ensure elements range can be created.
189 FUNCTION finp_val_er_create(
190 p_fee_cal_type IN VARCHAR2 ,
191 p_fee_ci_sequence_number IN NUMBER ,
192 p_fee_type IN VARCHAR2 ,
193 p_fee_cat IN VARCHAR2 ,
194 p_message_name OUT NOCOPY VARCHAR2 )
195 RETURN BOOLEAN AS
196 gv_other_detail VARCHAR2(255);
197 BEGIN -- finp_val_er_create
198 -- Validate IGS_FI_F_TYP_CA_INST.s_chg_method_type, fee_type and
199 -- IGS_FI_F_CAT_FEE_LBL.s_chg_method_type, fee_type.
200 -- If s_chg_method_type = 'FLATRATE' or IGS_FI_FEE_TYPE.s_fee_type = 'HECS', then
201 -- element ranges cannot be defined
202 DECLARE
203 CURSOR c_ftci IS
204 SELECT ftci.s_chg_method_type
205 FROM IGS_FI_F_TYP_CA_INST ftci
206 WHERE ftci.fee_type = p_fee_type AND
207 ftci.fee_cal_type = p_fee_cal_type AND
208 ftci.fee_ci_sequence_number = p_fee_ci_sequence_number;
209 CURSOR c_ftfl IS
210 SELECT ftfl.s_chg_method_type
211 FROM IGS_FI_F_CAT_FEE_LBL ftfl
212 WHERE ftfl.fee_type = p_fee_type AND
213 ftfl.fee_cal_type = p_fee_cal_type AND
214 ftfl.fee_ci_sequence_number = p_fee_ci_sequence_number AND
215 ftfl.fee_cat = p_fee_cat;
216 CURSOR c_ft IS
217 SELECT ft.s_fee_type
218 FROM IGS_FI_FEE_TYPE ft
219 WHERE ft.fee_type = p_fee_type;
220 cst_flatrate CONSTANT VARCHAR2(10) := 'FLATRATE';
221 cst_hecs CONSTANT VARCHAR2(10) := 'HECS';
222 v_s_fee_type IGS_FI_FEE_TYPE.s_fee_type%TYPE;
223 v_ftci_scmt IGS_FI_F_CAT_FEE_LBL.s_chg_method_type%TYPE;
224 v_ftfl_scmt IGS_FI_F_CAT_FEE_LBL.s_chg_method_type%TYPE;
225 BEGIN
226 --- Set the default message number
227 p_message_name := Null;
228 -- 1. check parameters
229 IF ((p_fee_cal_type IS NULL) OR
230 (p_fee_ci_sequence_number IS NULL) OR
231 (p_fee_type IS NULL)) THEN
232 p_message_name := Null;
233 RETURN TRUE;
234 END IF;
235 -- 2. Determine the s_chg_method_type, if p_fee_cat is null, master record must
236 -- be a IGS_FI_F_TYP_CA_INST record
237 IF p_fee_cat IS NULL THEN
238 OPEN c_ftci;
239 FETCH c_ftci INTO v_ftci_scmt;
240 IF (c_ftci%NOTFOUND) THEN
241 CLOSE c_ftci;
242 /* Changed by lpriyadh to close the bug 1488301 */
243 -- RAISE NO_DATA_FOUND;
244 RETURN FALSE;
245 ELSE
246 IF v_ftci_scmt = cst_flatrate THEN
247 p_message_name := 'IGS_FI_ELERNG_NOTDEFN_CHGMTH';
248 CLOSE c_ftci;
249 RETURN FALSE;
250 END IF;
251 END IF;
252 CLOSE c_ftci;
253 END IF;
254 -- 3.if p_fee_cat is not null, master record must be a IGS_FI_F_CAT_FEE_LBL
255 -- record
256 IF p_fee_cat IS NOT NULL THEN
257 OPEN c_ftfl;
258 FETCH c_ftfl INTO v_ftfl_scmt;
259 IF (c_ftfl%NOTFOUND) THEN
260 CLOSE c_ftfl;
261 /* changed by lpriyadh to close the bug 1488301 */
262 -- RAISE NO_DATA_FOUND;
263 RETURN FALSE;
264 ELSE
265 IF v_ftfl_scmt = cst_flatrate THEN
266 p_message_name := 'IGS_FI_ELERNG_NOTDEFN_CHGMTH';
267 CLOSE c_ftfl;
268 RETURN FALSE;
269 END IF;
270 END IF;
271 CLOSE c_ftfl;
272 END IF;
273 -- 4.Check the fee_type
274 OPEN c_ft;
275 FETCH c_ft INTO v_s_fee_type;
276 IF v_s_fee_type = cst_hecs THEN
277 p_message_name := 'IGS_FI_ELERNG_NOTDEFN_FEETYPE';
278 CLOSE c_ft;
279 RETURN FALSE;
280 END IF;
281 CLOSE c_ft;
282 RETURN TRUE;
283 END;
284 END finp_val_er_create;
285 --
286 -- Ensure elements range values are valid.
287 FUNCTION finp_val_er_ranges(
288 p_lower_range IN NUMBER ,
289 p_upper_range IN NUMBER ,
290 p_message_name OUT NOCOPY VARCHAR2 )
291 RETURN BOOLEAN AS
292 gv_other_detail VARCHAR2(255);
293 BEGIN -- finp_val_er_ranges
294 -- validate IGS_FI_ELM_RANGE.lower_range and IGS_FI_ELM_RANGE.upper_range.
295 -- If both are specififed, then lower_range must be less than or equal to
296 -- upper range
297 DECLARE
298 BEGIN
299 --- Set the default message number
300 p_message_name := Null;
301 -- validate parameters (one or both must exist)
302 IF p_lower_range IS NULL
303 AND p_upper_range IS NULL THEN
304 p_message_name := 'IGS_FI_ONE_LOW_AND_UP_RANGE';
305 RETURN FALSE;
306 END IF;
307 -- validate ranges if both are specified
308 IF (p_lower_range IS NOT NULL
309 AND p_upper_range IS NOT NULL) THEN
310 IF p_lower_range > p_upper_range THEN
311 p_message_name := 'IGS_FI_UPRANGE_GE_LOWRANGE';
312 RETURN FALSE;
313 END IF;
314 END IF;
315 RETURN TRUE;
316 END;
317 END finp_val_er_ranges;
318 --
319 -- Ensure elements range relations are valid.
320 FUNCTION finp_val_er_rltn(
321 p_s_relation_type IN VARCHAR2 ,
322 p_fee_cat IN VARCHAR2 ,
323 p_message_name OUT NOCOPY VARCHAR2 )
324 RETURN BOOLEAN AS
325 gv_other_detail VARCHAR2(255);
326 BEGIN -- finp_val_er_relation
327 -- Validate IGS_FI_ELM_RANGE.fee_cat is only specified for the appropriate
328 -- IGS_FI_ELM_RANGE.s_relation_type
329 DECLARE
330 BEGIN
331 --- Set the default message number
332 p_message_name := Null;
333 -- Validate parameter values
334 IF p_s_relation_type IS NULL THEN
335 RETURN TRUE;
336 ELSIF p_s_relation_type NOT IN('FTCI','FCFL') THEN
337 p_message_name := 'IGS_FI_FINP_VAL_ER_RLTN_CALL';
338 RETURN FALSE;
339 END IF;
340 -- Validate that for relation type FTCI, fee_cat is NULL
341 IF p_s_relation_type = 'FTCI' THEN
342 IF p_fee_cat IS NULL THEN
343 RETURN TRUE;
344 ELSE
345 p_message_name := 'IGS_FI_FEECAT_NULL_ELERNG';
346 RETURN FALSE;
347 END IF;
348 END IF;
349 -- Validate that for relation type 'FCFL', fee_cat is NOT NULL
350 IF p_s_relation_type = 'FCFL' THEN
351 IF p_fee_cat IS NOT NULL THEN
352 RETURN TRUE;
353 ELSE
354 p_message_name := 'IGS_FI_FEECAT_SPEC_ELERNG';
355 RETURN FALSE;
356 END IF;
357 END IF;
358 RETURN TRUE;
359 END;
360 END finp_val_er_rltn;
361 --
362 -- Ensure elements range can be created.
363 FUNCTION finp_val_er_ins(
364 p_fee_type IN VARCHAR2 ,
365 p_message_name OUT NOCOPY VARCHAR2 )
366 RETURN BOOLEAN AS
367 gv_other_detail VARCHAR2(255);
368 BEGIN -- finp_val_er_ins
369 -- validate IGS_FI_ELM_RANGE.fee_type. If IGS_FI_FEE_TYPE.s_fee_trigger_cat equals
370 -- 'INSTITUTN', then apportionments can only be defined against
371 -- IGS_FI_F_TYP_CA_INSTs
372 DECLARE
373 CURSOR c_ft IS
374 SELECT s_fee_trigger_cat,
375 s_fee_type
376 FROM IGS_FI_FEE_TYPE
377 WHERE fee_type = p_fee_type;
378 v_s_fee_trigger_cat IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE;
379 v_s_fee_type IGS_FI_FEE_TYPE.s_fee_type%TYPE;
380 cst_institutn CONSTANT IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE := 'INSTITUTN';
381 cst_hecs CONSTANT IGS_FI_FEE_TYPE.s_fee_type%TYPE := 'HECS';
382 BEGIN
383 --- Set the default message number
384 p_message_name := Null;
385 IF p_fee_type IS NULL THEN
386 p_message_name := Null;
387 RETURN TRUE;
388 END IF;
389 OPEN c_ft;
390 FETCH c_ft INTO v_s_fee_trigger_cat, v_s_fee_type;
391 IF (c_ft%FOUND) THEN
392 IF v_s_fee_trigger_cat = cst_institutn THEN
393 p_message_name := 'IGS_FI_ELERNG_NOTDEFN_FEECAT';
394 CLOSE c_ft;
395 RETURN FALSE;
396 END IF;
397 IF v_s_fee_type = cst_hecs THEN
398 p_message_name := 'IGS_FI_ELERNG_NOTDEFN_FEECAT';
399 CLOSE c_ft;
400 RETURN FALSE;
401 END IF;
402 END IF;
403 CLOSE c_ft;
404 RETURN TRUE;
405 END;
406 END finp_val_er_ins;
407 END IGS_FI_VAL_ER;
[Home] [Help]