1 PACKAGE BODY IGS_FI_VAL_FCCI AS
2 /* $Header: IGSFI25B.pls 120.1 2005/07/28 07:38:00 appldev ship $ */
3 /*----------------------------------------------------------------------------
4 || Created By :
5 || Created On :
6 || Purpose :
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || (reverse chronological order - newest change first)
11 || shtatiko 04-FEB-2004 Enh# 3167098, Removed validation of Retro Date Alias from FINP_VAL_FCCI_DATES.
12 || vvutukur 23-Jul-2002 Bug#2425767.Modified FUNCTION finp_upd_fcci_status to remove references to
13 || payment_hierarchy_rank.
14 ----------------------------------------------------------------------------*/
15 --
16 -- Validate FCCI can be made ACTIVE.
17 FUNCTION finp_val_fcci_active(
18 p_fee_cat_ci_status IN VARCHAR2 ,
19 p_fee_cal_type IN VARCHAR2 ,
20 p_fee_ci_sequence_number IN NUMBER ,
21 p_message_name OUT NOCOPY VARCHAR2 )
22 RETURN BOOLEAN AS
23 gv_other_detail VARCHAR2(255);
24 BEGIN -- finp_val_fcci_active
25 -- Validates that IGS_FI_F_CAT_CA_INST has a system calendar category of
26 -- 'FEE' and that the calendar instance is active when setting the
27 -- IGS_FI_F_CAT_CA_INST status to active.
28 DECLARE
29 cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
30 cst_fee CONSTANT VARCHAR2(3) := 'FEE';
31 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
32 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
33 v_dummy VARCHAR2(1);
34 CURSOR c_fss (
35 cp_fee_cat_ci_status IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE) IS
36 SELECT 'x'
37 FROM IGS_FI_FEE_STR_STAT fss
38 WHERE fss.fee_structure_status = cp_fee_cat_ci_status AND
39 fss.s_fee_structure_status = cst_active;
40 CURSOR c_cict (
41 cp_cal_type IGS_CA_INST.cal_type%TYPE,
42 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
43 SELECT cat.s_cal_cat,
44 cs.s_cal_status
45 FROM IGS_CA_INST ci,
46 IGS_CA_STAT cs,
47 IGS_CA_TYPE cat
48 WHERE ci.cal_type = cp_cal_type AND
49 ci.sequence_number = cp_sequence_number AND
50 ci.cal_type = cat.cal_type AND
51 ci.cal_status = cs.cal_status;
52 BEGIN
53 p_message_name := NULL;
54 -- Check parameters
55 IF(p_fee_cat_ci_status IS NULL OR
56 p_fee_cal_type IS NULL OR
57 p_fee_ci_sequence_number IS NULL) THEN
58 RETURN TRUE;
59 END IF;
60 -- Check system value of status.
61 -- If not 'ACTIVE', no further processing is required.
62 OPEN c_fss(
63 p_fee_cat_ci_status);
64 FETCH c_fss INTO v_dummy;
65 IF(c_fss%NOTFOUND) THEN
66 CLOSE c_fss;
67 RETURN TRUE;
68 END IF;
69 CLOSE c_fss;
70 -- Check the calendar system category
71 OPEN c_cict(
72 p_fee_cal_type,
73 p_fee_ci_sequence_number);
74 FETCH c_cict INTO v_s_cal_cat,
75 v_s_cal_status;
76 CLOSE c_cict;
77 IF(v_s_cal_cat <> cst_fee) THEN
78 p_message_name := 'IGS_FI_CAL_MUSTBE_CAT_AS_FEE';
79 RETURN FALSE;
80 END IF;
81 IF(v_s_cal_status <> cst_active) THEN
82 p_message_name := 'IGS_FI_CALINST_ACTIVE_FEECAT';
83 RETURN FALSE;
84 END IF;
85 RETURN TRUE;
86 END;
87 END finp_val_fcci_active;
88 --
89 -- Update the status of related FCFL records.
90 FUNCTION finp_upd_fcci_status(
91 p_fee_cat IN VARCHAR2 ,
92 p_fee_cal_type IN VARCHAR2 ,
93 p_fee_ci_sequence_number IN NUMBER ,
94 p_fee_cat_ci_status IN VARCHAR2 ,
95 p_message_name OUT NOCOPY VARCHAR2 )
96 RETURN BOOLEAN AS
97 /*----------------------------------------------------------------------------
98 || Created By :
99 || Created On :
100 || Purpose :
101 || Known limitations, enhancements or remarks :
102 || Change History :
103 || Who When What
104 || (reverse chronological order - newest change first)
105 || pmarada 28-jul-2005 Enh 3392095, Added waiver_calc_flag column to the IGS_FI_F_CAT_FEE_LBL_Pkg.Update_Row
106 || vvutukur 23-Jul-2002 Bug#2425767.Removed references to payment_hierarchy_rank(from the call
107 || to IGS_FI_F_CAT_FEE_LBL_Pkg.Update_Row).
108 ----------------------------------------------------------------------------*/
109 gv_other_detail VARCHAR2(255);
110 BEGIN
111 DECLARE
112 e_resource_busy EXCEPTION;
113 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
114 v_s_fee_structure_status IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE;
115 CURSOR c_fss IS
116 SELECT fss.s_fee_structure_status
117 FROM IGS_FI_FEE_STR_STAT fss
118 WHERE fss.fee_structure_status = p_fee_cat_ci_status;
119 CURSOR c_fcfl IS
120 SELECT fcfl.*, fcfl.rowid -- kdande -> rowid was added to make a call to Update_Row TBH.
121 FROM IGS_FI_F_CAT_FEE_LBL fcfl
122 WHERE fcfl.fee_cal_type = p_fee_cal_type AND
123 fcfl.fee_ci_sequence_number = p_fee_ci_sequence_number AND
124 fcfl.fee_cat =p_fee_cat AND
125 fcfl.fee_liability_status in
126 (select fss.fee_structure_status
127 from IGS_FI_FEE_STR_STAT fss
128 where fss.fee_structure_status = fcfl.fee_liability_status
129 and fss.s_fee_structure_status = 'ACTIVE')
130 FOR UPDATE OF fcfl.fee_liability_status NOWAIT;
131 fcfl_rec c_fcfl%ROWTYPE; -- kdande -> Added while converting DMLs.
132
133 BEGIN
134 -- When the IGS_FI_F_CAT_CA_INST.fee_cat_ci_status is changed
135 -- to 'INACTIVE' update the IGS_FI_F_CAT_FEE_LBL.fee_liability_status
136 -- in related records to 'INACTIVE'.
137 p_message_name := NULL;
138 -- 1. Check if the fee_cat_ci_status relates to a system
139 -- status in IGS_FI_FEE_STR_STAT of 'INACTIVE'.
140 OPEN c_fss;
141 FETCH c_fss INTO v_s_fee_structure_status;
142 CLOSE c_fss;
143 IF (v_s_fee_structure_status <> 'INACTIVE') THEN
144 RETURN TRUE;
145 END IF;
146 -- 2. Update any related IGS_FI_F_CAT_FEE_LBL records.
147 OPEN c_fcfl;
148 LOOP
149 FETCH c_fcfl INTO fcfl_rec; -- kdande -> Added fcfl_rec for use in Update_Row DML.
150 IF (c_fcfl%NOTFOUND) THEN
151 CLOSE c_fcfl;
152 RETURN TRUE;
153 END IF;
154
155 /* Call server side TBH package procedure */
156 IGS_FI_F_CAT_FEE_LBL_Pkg.Update_Row (
157 x_rowid => fcfl_rec.rowid,
158 x_fee_cat => fcfl_rec.fee_cat,
159 x_fee_ci_sequence_number => fcfl_rec.fee_ci_sequence_number,
160 x_fee_type => fcfl_rec.fee_type,
161 x_fee_cal_type => fcfl_rec.fee_cal_type,
162 x_fee_liability_status => p_fee_cat_ci_status,
163 x_start_dt_alias => fcfl_rec.start_dt_alias,
164 x_start_dai_sequence_number => fcfl_rec.start_dai_sequence_number,
165 x_s_chg_method_type => fcfl_rec.s_chg_method_type,
166 x_rul_sequence_number => fcfl_rec.rul_sequence_number,
167 x_waiver_calc_flag => fcfl_rec.waiver_calc_flag
168 );
169
170 -- If record is locked exception will be handled
171 END LOOP;
172 -- 4. Update Successful
173 RETURN TRUE;
174 EXCEPTION
175 WHEN e_resource_busy THEN
176 p_message_name := 'IGS_FI_FEECATFEELIAB_LOCKED';
177 RETURN FALSE;
178 WHEN OTHERS THEN
179 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
180 Fnd_Message.Set_Token('NAME','IGS_FI_VAL_FCCI.finp_upd_fcci_status');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END;
184 END finp_upd_fcci_status;
185 --
186 -- Validate the IGS_FI_F_CAT_CA_INST status
187 FUNCTION finp_val_fcci_status(
188 p_fee_cat IN VARCHAR2 ,
189 p_fee_cal_type IN VARCHAR2 ,
190 p_fee_ci_sequence_number IN NUMBER ,
191 p_new_fcci_status IN VARCHAR2 ,
192 p_old_fcci_status IN VARCHAR2 ,
193 p_message_name OUT NOCOPY VARCHAR2 )
194 RETURN BOOLEAN AS
195 gv_other_detail VARCHAR2(255);
196 BEGIN
197 DECLARE
198 cst_active CONSTANT IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE := 'ACTIVE';
199 cst_planned CONSTANT
200 IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE := 'PLANNED';
201 cst_inactive CONSTANT
202 IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE := 'INACTIVE';
203 v_new_system_status IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE;
204 v_old_system_status IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE;
205 v_fee_cat IGS_FI_F_CAT_FEE_LBL.fee_cat%TYPE;
206 CURSOR c_fss (
207 cp_fcci_status IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE) IS
208 SELECT fss.s_fee_structure_status
209 FROM IGS_FI_FEE_STR_STAT fss
210 WHERE fss.FEE_STRUCTURE_STATUS = cp_fcci_status;
211 CURSOR c_fcfl IS
212 SELECT fcfl.fee_cat
213 FROM IGS_FI_F_CAT_FEE_LBL fcfl
214 WHERE fcfl.fee_cat = p_fee_cat AND
215 fcfl.fee_cal_type = p_fee_cal_type AND
216 fcfl.fee_ci_sequence_number = p_fee_ci_sequence_number;
217 CURSOR c_fcfl_fss IS
218 SELECT fcfl.fee_cat
219 FROM IGS_FI_F_CAT_FEE_LBL fcfl,
220 IGS_FI_FEE_STR_STAT fss
221 WHERE fcfl.fee_cat = p_fee_cat AND
222 fcfl.fee_cal_type = p_fee_cal_type AND
223 fcfl.fee_ci_sequence_number = p_fee_ci_sequence_number AND
224 fcfl.fee_liability_status = fss.fee_structure_status AND
225 fss.s_fee_structure_status = cst_active;
226 BEGIN
227 -- Validate the IGS_FI_F_CAT_CA_INST.fee_cat_ci_status. The checks are:
228 -- Fee_cat_ci_status can only be set back to a system status of
229 -- 'PLANNED' from 'ACTIVE' if it has no associate
230 -- IGS_FI_F_CAT_FEE_LBL records.
231 -- Fee_cat_ci_status can only be set to a system status of 'INACTIVE' from
232 -- 'ACTIVE' if it has no 'ACTIVE' associated IGS_FI_F_CAT_FEE_LBL records.
233 -- 1. If the status has been changed get the system status:
234 IF (p_new_fcci_status <> p_old_fcci_status) THEN
235 OPEN c_fss(p_new_fcci_status);
236 FETCH c_fss INTO v_new_system_status;
237 CLOSE c_fss;
238 OPEN c_fss(p_old_fcci_status);
239 FETCH c_fss INTO v_old_system_status;
240 CLOSE c_fss;
241 END IF;
242 -- 2. If the new system status is planned check that there is no related
243 -- fee_cat_fee_aliability records
244 IF (v_new_system_status <> v_old_system_status) THEN
245 IF (v_new_system_status = cst_planned) THEN
246 OPEN c_fcfl;
247 FETCH c_fcfl INTO v_fee_cat;
248 IF (c_fcfl%FOUND) THEN
249 CLOSE c_fcfl;
250 p_message_name := 'IGS_FI_FEECAT_CAL_PLANNED';
251 RETURN FALSE;
252 END IF;
253 CLOSE c_fcfl;
254 END IF;
255 -- 3. If the new system status is planned check that there is no
256 -- ACTIVE related IGS_FI_F_CAT_FEE_LBL records
257 IF (v_new_system_status = cst_inactive) THEN
258 OPEN c_fcfl_fss;
259 FETCH c_fcfl_fss INTO v_fee_cat;
260 IF (c_fcfl_fss%FOUND) THEN
261 CLOSE c_fcfl_fss;
262 p_message_name := 'IGS_FI_FEECAT_CALINST_PLANNED';
263 RETURN FALSE;
264 END IF;
265 CLOSE c_fcfl_fss;
266 END IF;
267 END IF;
268 -- 4. validation successful
269 p_message_name := NULL;
270 RETURN TRUE;
271 END;
272 END finp_val_fcci_status;
273 --
274 -- Ensure cal instance dates are consistent.
275 FUNCTION finp_val_fcci_dates(
276 p_fee_cal_type IN VARCHAR2 ,
277 p_fee_ci_sequence_number IN NUMBER ,
278 p_start_dt_alias IN VARCHAR2 ,
279 p_start_dai_sequence_number IN NUMBER ,
280 p_end_dt_alias IN VARCHAR2 ,
281 p_end_dai_sequence_number IN NUMBER ,
282 p_retro_dt_alias IN VARCHAR2 ,
283 p_retro_dai_sequence_number IN NUMBER ,
284 p_message_name OUT NOCOPY VARCHAR2 )
285 RETURN BOOLEAN AS
286 gv_other_detail VARCHAR2(255);
287 BEGIN -- finp_val_fcci_dates
288 DECLARE
289 CURSOR c_dai(
290 cp_fee_cal_type VARCHAR2,
291 cp_fee_ci_sequence_number NUMBER,
292 cp_dt_alias VARCHAR2,
293 cp_dai_sequence_number NUMBER) IS
294 SELECT alias_val
295 FROM IGS_CA_DA_INST_V
296 WHERE cal_type = cp_fee_cal_type AND
297 ci_sequence_number = cp_fee_ci_sequence_number AND
298 dt_alias = cp_dt_alias AND
299 sequence_number = cp_dai_sequence_number;
300 v_dai_start_rec c_dai%ROWTYPE;
301 v_dai_end_rec c_dai%ROWTYPE;
302 v_dai_retro_rec c_dai%ROWTYPE;
303 BEGIN
304 --- Set the default message number
305 p_message_name := NULL;
306 -- Check parameters, at least 2 pairs of date aliases must be specified,
307 -- not including the start and retro combination
308 IF (p_fee_cal_type IS NULL OR
309 p_fee_ci_sequence_number IS NULL OR
310 p_end_dt_alias IS NULL OR
311 p_end_dai_sequence_number IS NULL OR
312 ((p_start_dt_alias IS NULL OR
313 p_start_dai_sequence_number IS NULL) AND
314 (p_retro_dt_alias IS NULL OR
315 p_retro_dai_sequence_number IS NULL))) THEN
316 RETURN TRUE;
317 END IF;
318 -- Get start date alias value if parameter values are not null
319 IF (p_start_dt_alias IS NOT NULL AND
320 p_start_dai_sequence_number IS NOT NULL) THEN
321 OPEN c_dai(
322 p_fee_cal_type,
323 p_fee_ci_sequence_number,
324 p_start_dt_alias,
325 p_start_dai_sequence_number);
326 FETCH c_dai INTO v_dai_start_rec;
327 CLOSE c_dai;
328 END IF;
329 -- Get end date alias value
330 OPEN c_dai(
331 p_fee_cal_type,
332 p_fee_ci_sequence_number,
333 p_end_dt_alias,
334 p_end_dai_sequence_number);
335 FETCH c_dai INTO v_dai_end_rec;
336 CLOSE c_dai;
337 -- Get retro date alias value if parameter values are not null
338 IF (p_retro_dt_alias IS NOT NULL AND
339 p_retro_dai_sequence_number IS NOT NULL) THEN
340 OPEN c_dai(
341 p_fee_cal_type,
342 p_fee_ci_sequence_number,
343 p_retro_dt_alias,
344 p_retro_dai_sequence_number);
345 FETCH c_dai INTO v_dai_retro_rec;
346 CLOSE c_dai;
347 END IF;
348 -- Check the date values
349 IF (p_start_dt_alias IS NOT NULL AND
350 p_start_dai_sequence_number IS NOT NULL) THEN
351 IF (v_dai_start_rec.alias_val > v_dai_end_rec.alias_val) THEN
352 p_message_name := 'IGS_FI_STDT_LE_END_DT';
353 RETURN FALSE;
354 END IF;
355 END IF;
356 -- Return the default value
357 RETURN TRUE;
358 END;
359 END finp_val_fcci_dates;
360 --
361 -- Validate the fee structure status closed indicator
362 FUNCTION finp_val_fss_closed(
363 p_fee_structure_status IN VARCHAR2 ,
364 p_message_name OUT NOCOPY VARCHAR2 )
365 RETURN BOOLEAN AS
366 BEGIN
367 DECLARE
368 v_other_detail VARCHAR2(255);
369 v_closed_ind CHAR;
370 CURSOR c_fee_structure_status IS
371 SELECT closed_ind
372 FROM IGS_FI_FEE_STR_STAT
373 WHERE fee_structure_status = p_fee_structure_status;
374 BEGIN
375 -- Check if the IGS_FI_FEE_STR_STAT is closed
376 p_message_name := NULL;
377 OPEN c_fee_structure_status;
378 FETCH c_fee_structure_status INTO v_closed_ind;
379 IF (c_fee_structure_status%NOTFOUND) THEN
380 CLOSE c_fee_structure_status;
381 RETURN TRUE;
382 END IF;
383 IF (v_closed_ind = 'Y') THEN
384 p_message_name := 'IGS_FI_FEESTRUCT_STATUSCLOSED';
385 CLOSE c_fee_structure_status;
386 RETURN FALSE;
387 END IF;
388 -- record is not closed
389 CLOSE c_fee_structure_status;
390 RETURN TRUE;
391 END;
392 END finp_val_fss_closed;
393 --
394 -- Ensure calendar instance is FEE and ACTIVE.
395 FUNCTION finp_val_ci_fee(
396 p_fee_cal_type IN VARCHAR2 ,
397 p_fee_ci_sequence_number IN NUMBER ,
398 p_message_name OUT NOCOPY VARCHAR2 )
399 RETURN BOOLEAN AS
400 gv_other_detail VARCHAR2(255);
401 BEGIN
402 DECLARE
403 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
404 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
405 CURSOR c_ci_cat_cs IS
406 SELECT cat.s_cal_cat,
407 cs.s_cal_status
408 FROM IGS_CA_INST ci,
409 IGS_CA_TYPE cat,
410 IGS_CA_STAT cs
411 WHERE ci.cal_type = p_fee_cal_type AND
412 ci.sequence_number = p_fee_ci_sequence_number AND
413 ci.cal_type = cat.CAL_TYPE AND
414 ci.cal_status = cs.CAL_STATUS;
415 BEGIN
416 -- Validate the calendar instance to check it is calendar system category
417 -- FEE and has a system status of ACTIVE or PLANNED.
418 OPEN c_ci_cat_cs;
419 FETCH c_ci_cat_cs INTO v_s_cal_cat,
420 v_s_cal_status;
421 CLOSE c_ci_cat_cs;
422 -- Check the calendar system category.
423 IF (v_s_cal_cat <> 'FEE') THEN
424 p_message_name := 'IGS_FI_CAL_MUSTBE_CAT_AS_FEE';
425 RETURN FALSE;
426 END IF;
427 -- Check the calendar system status.
428 IF (v_s_cal_status not in ('ACTIVE','PLANNED')) THEN
429 p_message_name := 'IGS_CA_CAL_INST_MUST_BE_ACTIV';
430 RETURN FALSE;
431 END IF;
432 p_message_name := NULL;
433 RETURN TRUE;
434 END;
435 END finp_val_ci_fee;
436 END IGS_FI_VAL_FCCI;
[Home] [Help]