1 PACKAGE BODY IGS_FI_VAL_FTCI AS
2 /* $Header: IGSFI34B.pls 120.1 2005/07/28 07:41:33 appldev ship $ */
3 /*
4 Who When What
5 pmarada 28-jul-2005 Enh 3392095, Added waiver_calc_flag cloumn to the IGS_FI_F_CAT_FEE_LBL_Pkg.Update_Row
6 shtatiko 04-FEB-2004 Enh# 3167098, Removed validation of Retro Date Alias from finp_val_ftci_dates.
7 vvutukur 29-Jul-2002 Bug#2425767. Removed payment_hierarchy_rank column references as this
8 is obsoleted(from call to IGS_FI_F_CAT_FEE_LBL_Pkg.Update_Row in
9 FUNCTION finp_upd_fcfl_status.Removed function finp_val_ftci_rank
10 as this function validates payment_hierarchy_rank,an obsoleted column.)
11 vchappid 25-Apr-2002 Bug# 2329407, Removed the parameters account_cd, fin_cal_type
12 and fin_ci_sequence_number from the function call finp_val_ftci_rqrd
13
14 vchappid 04-Feb-2002 As per Enh#2187247, procedure finp_val_ftci_rqrd is modofied to include new validations
15 */
16
17 /* Bug 1966961
18 Who schodava
19 When 5 Sept,2001
20 What Obsolete the account code link with Financial Calendar
21 */
22 /* Bug 1956374
23 Who msrinivi
24 When 25 Aug,2001
25 What Duplicate code removal finp_val_ft_closed
26 */
27
28 /* Who When What
29 vivuyyur 10-sep-2001 Bug No :1966961
30 PROCEDURE finp_val_ftci_ac is changed */
31 -- Validate the IGS_FI_ACC has the correct calendar relations.
32 FUNCTION finp_val_ftci_ac(
33 p_fee_cal_type IN VARCHAR2 ,
34 p_fee_ci_sequence_number IN NUMBER ,
35 p_message_name OUT NOCOPY VARCHAR2 )
36 RETURN BOOLEAN AS
37 gv_other_detail VARCHAR2(255);
38 BEGIN -- finp_val_ftci_account
39 -- Validate the IGS_FI_F_TYP_CA_INST calendar instance
40 -- is a subordinate of the IGS_FI_ACC finace calendar instance.
41 DECLARE
42 v_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE;
43 CURSOR c_cir IS
44 SELECT sub_cal_type
45 FROM IGS_CA_INST_REL
46 WHERE sub_cal_type = p_fee_cal_type AND
47 sub_ci_sequence_number = p_fee_ci_sequence_number ;
48 BEGIN
49 -- Set the default message number
50 p_message_name := NULL;
51 -- Check parameters
52 IF (
53 p_fee_cal_type IS NULL OR
54 p_fee_ci_sequence_number IS NULL) THEN
55 RETURN TRUE;
56 END IF;
57 -- Check if the IGS_FI_F_TYP_CA_INST fee calendar
58 -- is a subordinate of the IGS_FI_ACC finace calendar.
59 OPEN c_cir;
60 FETCH c_cir INTO v_sub_cal_type;
61 IF (c_cir%FOUND) THEN
62 CLOSE c_cir;
63 RETURN TRUE;
64 END IF;
65 CLOSE c_cir;
66 -- Return error
67 p_message_name := 'IGS_FI_FEECAL_TYPE_SUBORD';
68 RETURN FALSE;
69 END;
70 END finp_val_ftci_ac;
71 --
72 -- Ensure Fee calendar has relationship to Teaching Calendar
73 FUNCTION finp_chk_tchng_prds(
74 p_fee_cal_type IN VARCHAR2 ,
75 p_fee_ci_sequence_number IN NUMBER ,
76 p_message_name OUT NOCOPY VARCHAR2 )
77 RETURN BOOLEAN AS
78 gv_other_detail VARCHAR2(255);
79 BEGIN
80 DECLARE
81 v_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE;
82 cst_cal_cat CONSTANT VARCHAR2(8):= 'TEACHING';
83 CURSOR c_cir IS
84 SELECT cir.sub_cal_type
85 FROM IGS_CA_INST_REL cir
86 WHERE cir.sub_cal_type = p_fee_cal_type AND
87 cir.sub_ci_sequence_number = p_fee_ci_sequence_number AND
88 cir.sup_cal_type IN ( SELECT ct.CAL_TYPE
89 FROM IGS_CA_TYPE ct
90 WHERE ct.CAL_TYPE = cir.sup_cal_type AND
91 ct.S_CAL_CAT = cst_cal_cat AND
92 ct.closed_ind = 'N');
93 BEGIN
94 -- Validate IGS_FI_F_CAT_FEE_LBL calender instance has
95 --subordinate relationships to 'TEACHING PERIOD' calender
96 --instances. IGS_GE_NOTE: IGS_FI_F_CAT_FEE_LBL calender instances
97 --must be of category 'FEE'.
98 --1. Check parameters
99 IF (p_fee_cal_type IS NULL OR
100 p_fee_ci_sequence_number IS NULL) THEN
101 p_message_name := NULL;
102 RETURN TRUE;
103 END IF;
104 --2. Check if superior relationships exist with calender
105 --instances which have a system calender category of 'TEACHING'.
106 OPEN c_cir;
107 FETCH c_cir INTO v_sub_cal_type;
108 IF (c_cir%FOUND) THEN
109 CLOSE c_cir;
110 p_message_name := NULL;
111 RETURN TRUE;
112 END IF;
113 CLOSE c_cir;
114 --3. Return Error
115 p_message_name := 'IGS_FI_REQ_RELATION_DONOT_EXS';
116 RETURN FALSE;
117 END;
118 END finp_chk_tchng_prds;
119 --
120 -- Update the status of related FCFL records.
121 FUNCTION finp_upd_fcfl_status(
122 p_fee_type IN VARCHAR2 ,
123 p_fee_cal_type IN VARCHAR2 ,
124 p_fee_ci_sequence_number IN NUMBER ,
125 p_fee_type_ci_status IN VARCHAR2 ,
126 p_message_name OUT NOCOPY VARCHAR2 )
127 RETURN BOOLEAN AS
128 gv_other_detail VARCHAR2(255);
129 BEGIN
130 DECLARE
131 e_resource_busy EXCEPTION;
132 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
133 v_s_fee_structure_status IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE;
134 v_fee_liability_status IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE;
135 CURSOR c_fss IS
136 SELECT fss.s_fee_structure_status
137 FROM IGS_FI_FEE_STR_STAT fss
138 WHERE fss.fee_structure_status = p_fee_type_ci_status;
139 CURSOR c_fcfl IS
140 SELECT fcfl.*, fcfl.rowid -- kdande -> rowid was added to make a call to Update_Row TBH.
141 FROM IGS_FI_F_CAT_FEE_LBL fcfl
142 WHERE fcfl.fee_cal_type = p_fee_cal_type AND
143 fcfl.fee_ci_sequence_number = p_fee_ci_sequence_number AND
144 fcfl.fee_type =p_fee_type
145 FOR UPDATE OF fcfl.fee_liability_status NOWAIT;
146 fcfl_rec c_fcfl%ROWTYPE; -- kdande -> Added while converting DMLs.
147 BEGIN
148 -- When the fee_cal_instance.fee_type_ci_status is changed
149 -- to 'INACTIVE' update the IGS_FI_F_CAT_FEE_LBL.fee_liability_status
150 -- in related records to 'INACTIVE'.
151 p_message_name := NULL;
152 -- 1. Check if the fee_type_ci_status relates to a system
153 -- status in IGS_FI_FEE_STR_STAT of 'INACTIVE'.
154 OPEN c_fss;
155 FETCH c_fss INTO v_s_fee_structure_status;
156 CLOSE c_fss;
157 IF (v_s_fee_structure_status <> 'INACTIVE') THEN
158 RETURN TRUE;
159 END IF;
160 -- 2. Update any related IGS_FI_F_CAT_FEE_LBL records.
161 OPEN c_fcfl;
162 LOOP
163 FETCH c_fcfl INTO fcfl_rec; -- kdande -> Added fcfl_rec for use in Update_Row DML.
164 IF (c_fcfl%NOTFOUND) THEN
165 CLOSE c_fcfl;
166 RETURN TRUE;
167 END IF;
168
169 /* Call server side TBH package procedure */
170 IGS_FI_F_CAT_FEE_LBL_Pkg.Update_Row (
171 x_rowid => fcfl_rec.rowid,
172 x_fee_cat => fcfl_rec.fee_cat,
173 x_fee_ci_sequence_number => fcfl_rec.fee_ci_sequence_number,
174 x_fee_type => fcfl_rec.fee_type,
175 x_fee_cal_type => fcfl_rec.fee_cal_type,
176 x_fee_liability_status => p_fee_type_ci_status,
177 x_start_dt_alias => fcfl_rec.start_dt_alias,
178 x_start_dai_sequence_number => fcfl_rec.start_dai_sequence_number,
179 x_s_chg_method_type => fcfl_rec.s_chg_method_type,
180 x_rul_sequence_number => fcfl_rec.rul_sequence_number,
181 x_waiver_calc_flag => fcfl_rec.waiver_calc_flag
182 );
183
184 -- If record is locked exception will be handled
185 END LOOP;
186 -- 4. Update Successful
187 RETURN TRUE;
188 EXCEPTION
189 WHEN e_resource_busy THEN
190 p_message_name := 'IGS_FI_FEECATFEELIAB_LOCKED';
191 RETURN FALSE;
192 WHEN OTHERS THEN
193 APP_EXCEPTION.RAISE_EXCEPTION;
194 END;
195 END finp_upd_fcfl_status;
196 --
197 -- Ensure calendar instance is FEE and ACTIVE.
198 -- Duplicate Code Removal, msrinivi Removed proc finp_val_ci_fee
199 -- Validate the fee structure status closed indicator
200 -- Duplicate Code Removal, msrinivi Removed func finp_val_fss_closed
201 --
202 -- Validate the IGS_FI_F_TYP_CA_INST s_chg_method_type.
203 FUNCTION finp_val_ftci_c_mthd(
204 p_fee_type IN VARCHAR ,
205 p_chg_method IN VARCHAR2 ,
206 p_message_name OUT NOCOPY VARCHAR2 )
207 RETURN BOOLEAN AS
208 gv_other_detail VARCHAR2(255);
209 BEGIN
210 DECLARE
211 cst_hecs CONSTANT IGS_FI_FEE_TYPE.s_fee_type%TYPE := 'HECS';
212 cst_eftsu CONSTANT VARCHAR2(10) := 'EFTSU';
213 v_s_fee_type IGS_FI_FEE_TYPE.s_fee_type%TYPE;
214 CURSOR c_ft IS
215 SELECT ft.s_fee_type
216 FROM IGS_FI_FEE_TYPE ft
217 WHERE ft.fee_type = p_fee_type;
218 BEGIN
219 -- Validate if IGS_FI_F_TYP_CA_INST.s_chg_method_type
220 -- is correct for the IGS_FI_FEE_TYPE.s_fee_type
221 p_message_name := NULL;
222 -- 1. Check parameters
223 IF (p_fee_type IS NULL) THEN
224 RETURN TRUE;
225 END IF;
226 -- 2. The p_chg_method must be 'EFTSU' if the
227 -- s_fee_type is 'HECS' or 'TUITION'.
228 OPEN c_ft;
229 FETCH c_ft INTO v_s_fee_type;
230 CLOSE c_ft;
231 IF (v_s_fee_type = cst_hecs AND p_chg_method <> cst_eftsu) THEN
232 p_message_name := 'IGS_FI_CHGMTH_SETTO_EFTSU';
233 RETURN FALSE;
234 END IF;
235 RETURN TRUE;
236 END;
237 END finp_val_ftci_c_mthd;
238 --
239 -- Validate the IGS_FI_F_TYP_CA_INST date aliases
240 FUNCTION finp_val_ftci_dates(
241 p_fee_cal_type IN VARCHAR2 ,
242 p_fee_ci_sequence_number IN NUMBER ,
243 p_start_dt_alias IN VARCHAR2 ,
244 p_start_dai_sequence_number IN NUMBER ,
245 p_end_dt_alias IN VARCHAR2 ,
246 p_end_dai_sequence_number IN NUMBER ,
247 p_retro_dt_alias IN VARCHAR2 ,
248 p_retro_dai_sequence_number IN NUMBER ,
249 p_message_name OUT NOCOPY VARCHAR2 )
250 RETURN BOOLEAN AS
251 gv_other_detail VARCHAR2(255);
252 BEGIN
253 DECLARE
254 v_start_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
255 v_end_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
256 v_retro_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
257 CURSOR c_daiv (
258 cp_dt_alias IGS_CA_DA_INST_V.DT_ALIAS%TYPE,
259 cp_dai_sequence_number IGS_CA_DA_INST_V.sequence_number%TYPE) IS
260 SELECT daiv.alias_val
261 FROM IGS_CA_DA_INST_V daiv
262 WHERE daiv.CAL_TYPE = p_fee_cal_type AND
263 daiv.ci_sequence_number = p_fee_ci_sequence_number AND
264 daiv.DT_ALIAS = cp_dt_alias AND
265 daiv.sequence_number = cp_dai_sequence_number;
266 BEGIN
267 -- Validate IGS_FI_F_TYP_CA_INST dates.
268 -- Validate that start_dt is less then or equal to end_dt and
269 -- that end_dt is less than or equal to retro_dt.
270 p_message_name := NULL;
271 -- 1. Check parameters (function must be called with at least
272 -- 2 pairs of date alias specified - start and end or end and
273 -- retro or all three (start and retro is not valid):
274 IF (p_fee_cal_type IS NULL OR
275 p_fee_ci_sequence_number IS NULL OR
276 p_end_dt_alias IS NULL OR
277 p_end_dai_sequence_number IS NULL OR
278 ((p_start_dt_alias IS NULL OR p_start_dai_sequence_number IS NULL) AND
279 (p_retro_dt_alias IS NULL OR p_retro_dai_sequence_number IS NULL))) THEN
280 RETURN TRUE;
281 END IF;
282 -- 2. Obtain the actual value for the aliases (Steps 3 thru 5)
283 -- 3. Get start date alias value (if parameter value not null)
284 IF (p_start_dt_alias IS NOT NULL AND
285 p_start_dai_sequence_number IS NOT NULL) THEN
286 OPEN c_daiv(
287 p_start_dt_alias,
288 p_start_dai_sequence_number);
289 FETCH c_daiv INTO v_start_alias_val;
290 CLOSE c_daiv;
291 END IF;
292 -- 4. Get end date alias value. (this one will always be specified)
293 OPEN c_daiv(
294 p_end_dt_alias,
295 p_end_dai_sequence_number);
296 FETCH c_daiv INTO v_end_alias_val;
297 CLOSE c_daiv;
298 -- 5. Get retro date alias value (if parameter value not null)
299 IF (p_retro_dt_alias IS NOT NULL AND
300 p_retro_dai_sequence_number IS NOT NULL) THEN
301 OPEN c_daiv(
302 p_retro_dt_alias,
303 p_retro_dai_sequence_number);
304 FETCH c_daiv INTO v_retro_alias_val;
305 CLOSE c_daiv;
306 END IF;
307 -- 6. Check the dates values:
308 IF (p_start_dt_alias IS NOT NULL) THEN
309 IF (v_start_alias_val > v_end_alias_val) THEN
310 p_message_name := 'IGS_FI_STDT_LE_END_DT';
311 RETURN FALSE;
312 END IF;
313 END IF;
314 RETURN TRUE;
315 END;
316 END finp_val_ftci_dates;
317 --
318 -- Validate the IGS_FI_F_TYP_CA_INST required data
319 FUNCTION finp_val_ftci_rqrd(
320 p_fee_cal_type IN VARCHAR2 ,
321 p_fee_ci_sequence_number IN NUMBER ,
322 p_fee_type IN VARCHAR2 ,
323 p_old_chg_method IN VARCHAR2 ,
324 p_old_rule_sequence IN NUMBER ,
325 p_chg_method IN VARCHAR2 ,
326 p_rule_sequence IN NUMBER ,
327 p_fee_type_ci_status IN VARCHAR2 ,
328 p_message_name OUT NOCOPY VARCHAR2 )
329 RETURN BOOLEAN AS
330 /*
331 WHO WHEN WHAT
332 vchappid 25-Apr-2002 Bug# 2329407, Removed the parameters account_cd, fin_cal_type
333 and fin_ci_sequence_number from the function call finp_val_ftci_rqrd
334 vchappid 04-Feb-2002 As per Enh#2187247, procedure finp_val_ftci_rqrd is modofied to include new validations
335 */
336
337 gv_other_detail VARCHAR2(255);
338 BEGIN -- finp_val_ftci_rqrd
339 -- When the system status is ACTIVE validate if
340 -- IGS_FI_F_TYP_CA_INST.s_chg_method_type and
341 -- IGS_FI_F_TYP_CA_INST.rul_sequence_number are required or not, depending
342 -- on related values.
343 -- Ensure that the FINANCE calendar_instance is
344 -- ACTIVE.
345 DECLARE
346 cst_hecs CONSTANT IGS_FI_FEE_TYPE.s_fee_type%TYPE := 'HECS';
347 cst_institutn CONSTANT IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE := 'INSTITUTN';
348 cst_active CONSTANT IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE := 'ACTIVE';
349 v_s_fee_type IGS_FI_FEE_TYPE.s_fee_type%TYPE;
350 v_s_fee_trigger_cat IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE;
351 v_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE;
352 v_s_fee_structure_status IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE;
353 v_acc_closed_ind IGS_FI_ACC.closed_ind%TYPE;
354 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
355 v_s_cal_cat IGS_CA_TYPE.S_CAL_CAT%TYPE;
356 CURSOR c_fss (
357 cp_fee_type_ci_status IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE) IS
358 SELECT fss.s_fee_structure_status
359 FROM IGS_FI_FEE_STR_STAT fss
360 WHERE fss.fee_structure_status = cp_fee_type_ci_status;
361 CURSOR c_ft IS
362 SELECT ft.s_fee_type,
363 ft.s_fee_trigger_cat
364 FROM IGS_FI_FEE_TYPE ft
365 WHERE ft.fee_type = p_fee_type;
366 CURSOR c_fcfl IS
367 SELECT fcfl.fee_type
368 FROM IGS_FI_F_CAT_FEE_LBL fcfl
369 WHERE fcfl.fee_type = p_fee_type AND
370 fcfl.fee_cal_type = p_fee_cal_type AND
371 fcfl.fee_ci_sequence_number = p_fee_ci_sequence_number;
372 CURSOR c_fee_ci IS
373 SELECT cs.s_cal_status,
374 ct.S_CAL_CAT
375 FROM IGS_CA_INST ci,
376 IGS_CA_TYPE ct,
377 IGS_CA_STAT cs
378 WHERE ci.CAL_TYPE = p_fee_cal_type AND
379 ci.sequence_number = p_fee_ci_sequence_number AND
380 ci.CAL_TYPE = ct.CAL_TYPE AND
381 ci.CAL_STATUS = cs.CAL_STATUS;
382
383 -- Start of Modifications Enh# 2187247
384 -- Parameter to the function igs_fi_gen_001.finp_get_lfci_reln,
385 -- suggesting the input parameters are of FEE Calendar Category
386 -- OUT NOCOPY parameters are of LOAD Calendar Category
387 cst_fee CONSTANT igs_ca_type.s_cal_cat%TYPE :='FEE';
388
389 -- Variables to storing Load Calendar Instance
390 l_c_load_cal_type igs_ca_inst_all.cal_type%TYPE;
391 l_n_load_seq_num igs_ca_inst_all.sequence_number%TYPE;
392
393 l_c_message_name fnd_new_messages.message_name%TYPE;
394
395 -- Check the system status of the load calendar instance, should be ACTIVE before a FTCI is created
396 CURSOR cur_load_cal_status (cp_load_cal_type igs_ca_inst_all.cal_type%TYPE ,
397 cp_load_cal_seq_num igs_ca_inst_all.sequence_number%TYPE)
398 IS
399 SELECT s.s_cal_status
400 FROM igs_ca_inst i,
401 igs_ca_stat s
402 WHERE s.cal_status = i.cal_status
403 AND i.cal_type = cp_load_cal_type
404 AND i.sequence_number = cp_load_cal_seq_num;
405
406 l_cur_load_cal_status cur_load_cal_status%ROWTYPE;
407 -- End of Modifications Enh# 2187247
408
409 BEGIN
410 -- Validate if IGS_FI_F_TYP_CA_INST.s_chg_method_type and
411 -- IGS_FI_F_TYP_CA_INST.rul_sequence_number are required
412 -- or not, depending on related values.
413 p_message_name := NULL;
414 -- 1. Check parameters
415 IF (p_fee_cal_type IS NULL OR
416 p_fee_ci_sequence_number IS NULL OR
417 p_fee_type IS NULL) THEN
418 RETURN TRUE;
419 END IF;
420 -- 2.1 Check if the system status is ACTIVE before testing for required data
421 OPEN c_fss(p_fee_type_ci_status);
422 FETCH c_fss INTO v_s_fee_structure_status;
423 CLOSE c_fss;
424 IF (v_s_fee_structure_status <> cst_active) THEN
425 RETURN TRUE;
426 END IF;
427 -- 2.2 If p_chg_method is not null pr p_rul_sequence is not null, then
428 -- validate the IGS_FI_FEE_TYPE to see if it is permissible for these values to be
429 -- specified. Required when IGS_FI_FEE_TYPE.s_fee_trigger_cat = 'INSTITUTN' or
430 -- s_fee_type = 'HECS'.
431 OPEN c_ft;
432 FETCH c_ft INTO v_s_fee_type,
433 v_s_fee_trigger_cat;
434 CLOSE c_ft;
435 IF (v_s_fee_type = cst_hecs AND
436 p_chg_method IS NULL) THEN
437 p_message_name := 'IGS_FI_CHGMTH_SPECIFY_FEETYPE';
438 RETURN FALSE;
439 END IF;
440 IF (v_s_fee_type = cst_hecs AND
441 p_rule_sequence IS NULL) THEN
442 p_message_name := 'IGS_FI_RULSEQ_FEETYPE_HECS';
443 RETURN FALSE;
444 END IF;
445 IF (v_s_fee_trigger_cat = cst_institutn AND
446 p_chg_method IS NULL) THEN
447 p_message_name := 'IGS_FI_CHGMTH_FEETYPE_INSTITU';
448 RETURN FALSE;
449 END IF;
450 IF (v_s_fee_trigger_cat = cst_institutn AND
451 p_rule_sequence IS NULL) THEN
452 p_message_name := 'IGS_FI_RULSEQ_FEETYPE_INSTITU';
453 RETURN FALSE;
454 END IF;
455 -- 2.3 Check if the account_cd, fin_cal_type and fin_ci_sequence_number
456 -- have been set.
457 -- If they are set check that the account_cd is linked to an active Finance
458 -- calendar instance.
459 -- This part f the code is removed as a part of Enh # 1966961 : Obsolete Items CCR
460
461 -- 2.4 Check if the fee_cal_type and fee_ci_sequence_number
462 -- are linked to an active Fee calendar instance.
463 OPEN c_fee_ci;
464 FETCH c_fee_ci INTO
465 v_s_cal_status,
466 v_s_cal_cat;
467 IF (c_fee_ci%NOTFOUND) THEN
468 CLOSE c_fee_ci;
469 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
470 RAISE NO_DATA_FOUND;
471 END IF;
472 CLOSE c_fee_ci;
473 IF v_s_cal_status <> cst_active THEN
474 p_message_name := 'IGS_FI_CALINST_FEETYPE_CALINS';
475 RETURN FALSE;
476 END IF;
477 IF v_s_cal_cat <> 'FEE' THEN
478 p_message_name := 'IGS_FI_CALINST_LINKED_FEETYPE';
479 RETURN FALSE;
480 END IF;
481
482 -- Start of Modifications Enh# 2187247
483 -- Get the Related Load Calendar Instance for the Fee Calendar Instance
484 IF (igs_fi_gen_001.finp_get_lfci_reln( p_fee_cal_type,
485 p_fee_ci_sequence_number,
486 cst_fee,
487 l_c_load_cal_type,
488 l_n_load_seq_num,
489 l_c_message_name)) THEN
490 -- For the Load Calendar Instance identified, check the System Load Calendar Status
491 -- Status should be Active
492 OPEN cur_load_cal_status (l_c_load_cal_type, l_n_load_seq_num);
493 FETCH cur_load_cal_status INTO l_cur_load_cal_status;
494 IF cur_load_cal_status%NOTFOUND THEN
495 CLOSE cur_load_cal_status;
496 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
497 RAISE NO_DATA_FOUND;
498 ELSE
499 CLOSE cur_load_cal_status;
500 IF (l_cur_load_cal_status.s_cal_status <> cst_active) THEN
501 p_message_name := 'IGS_FI_LOAD_CAL_NOT_ACTIVE';
502 RETURN FALSE;
503 END IF;
504 END IF;
505 ELSE
506 p_message_name := l_c_message_name;
507 RETURN FALSE;
508 END IF;
509 -- End of Modifications Enh# 2187247
510
511 -- 3. If there are related IGS_FI_F_CAT_FEE_LBL records the s_chg_method_type
512 -- or rul_sequence_number cannot be added or removed (can however be changed)
513 IF (p_chg_method IS NULL AND p_old_chg_method IS NOT NULL) OR
514 (p_chg_method IS NOT NULL AND p_old_chg_method IS NULL) OR
515 (p_rule_sequence IS NULL AND p_old_rule_sequence IS NOT NULL) OR
516 (p_rule_sequence IS NOT NULL AND p_old_rule_sequence IS NULL) THEN
517 OPEN c_fcfl;
518 FETCH c_fcfl INTO v_fee_type;
519 IF (c_fcfl%FOUND) THEN
520 CLOSE c_fcfl;
521 p_message_name := 'IGS_FI_CHGMTH_OR_RULSEQ';
522 RETURN FALSE;
523 END IF;
524 CLOSE c_fcfl;
525 END IF;
526 RETURN TRUE;
527 END;
528 END finp_val_ftci_rqrd;
529 --
530 -- Validate the IGS_FI_F_TYP_CA_INST status
531 FUNCTION finp_val_ftci_status(
532 p_fee_type IN VARCHAR2 ,
533 p_fee_cal_type IN VARCHAR2 ,
534 p_fee_ci_sequence_number IN NUMBER ,
535 p_new_ftci_status IN VARCHAR2 ,
536 p_old_ftci_status IN VARCHAR2 ,
537 p_message_name OUT NOCOPY VARCHAR2 )
538 RETURN BOOLEAN AS
539 gv_other_detail VARCHAR2(255);
540 BEGIN
541 DECLARE
542 cst_active CONSTANT IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE := 'ACTIVE';
543 cst_planned CONSTANT
544 IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE := 'PLANNED';
545 cst_inactive CONSTANT
546 IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE := 'INACTIVE';
547 v_new_system_status IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE;
548 v_old_system_status IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE;
549 v_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE;
550 CURSOR c_fss (
551 cp_ftci_status IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE) IS
552 SELECT fss.s_fee_structure_status
553 FROM IGS_FI_FEE_STR_STAT fss
554 WHERE fss.fee_structure_status = cp_ftci_status;
555 CURSOR c_fcfl IS
556 SELECT fcfl.fee_type
557 FROM IGS_FI_F_CAT_FEE_LBL fcfl
558 WHERE fcfl.fee_type = p_fee_type AND
559 fcfl.fee_cal_type = p_fee_cal_type AND
560 fcfl.fee_ci_sequence_number = p_fee_ci_sequence_number;
561 CURSOR c_fcfl_fss IS
562 SELECT fcfl.fee_type
563 FROM IGS_FI_F_CAT_FEE_LBL fcfl,
564 IGS_FI_FEE_STR_STAT fss
565 WHERE fcfl.fee_type = p_fee_type AND
566 fcfl.fee_cal_type = p_fee_cal_type AND
567 fcfl.fee_ci_sequence_number = p_fee_ci_sequence_number AND
568 fcfl.fee_liability_status = fss.fee_structure_status AND
569 fss.s_fee_structure_status = cst_active;
570 BEGIN
571 -- Validate the IGS_FI_F_TYP_CA_INST.fee_type_ci_status. The checks are:
572 -- Fee_type_ci_status can only be set back to a system status of
573 -- 'PLANNED' from 'ACTIVE' if it has no associated
574 -- IGS_FI_F_CAT_FEE_LBL records.
575 -- Fee_type_ci_status can only be set to a system status of 'INACTIVE' from
576 -- 'ACTIVE' if it has no 'ACTIVE' associated IGS_FI_F_CAT_FEE_LBL records.
577 -- 1. If the status has been changed get the system status:
578 IF (p_new_ftci_status <> p_old_ftci_status) THEN
579 OPEN c_fss(p_new_ftci_status);
580 FETCH c_fss INTO v_new_system_status;
581 CLOSE c_fss;
582 OPEN c_fss(p_old_ftci_status);
583 FETCH c_fss INTO v_old_system_status;
584 CLOSE c_fss;
585 END IF;
586 -- 2. If the new system status is planned check that there is no related
587 -- fee_cat_fee_aliability records
588 IF (v_new_system_status <> v_old_system_status) THEN
589 IF (v_new_system_status = cst_planned) THEN
590 OPEN c_fcfl;
591 FETCH c_fcfl INTO v_fee_type;
592 IF (c_fcfl%FOUND) THEN
593 CLOSE c_fcfl;
594 p_message_name := 'IGS_FI_FEETYPECAL_NOTBE_PLANN';
595 RETURN FALSE;
596 END IF;
597 CLOSE c_fcfl;
598 END IF;
599 -- 3. If the new system status is inactive check that there is no
600 -- ACTIVE related IGS_FI_F_CAT_FEE_LBL records
601 IF (v_new_system_status = cst_inactive) THEN
602 OPEN c_fcfl_fss;
603 FETCH c_fcfl_fss INTO v_fee_type;
604 IF (c_fcfl_fss%FOUND) THEN
605 CLOSE c_fcfl_fss;
606 p_message_name := 'IGS_FI_FEETYPECAL_NOTBE_INACT';
607 RETURN FALSE;
608 END IF;
609 CLOSE c_fcfl_fss;
610 END IF;
611 END IF;
612 -- 4. validation successful
613 p_message_name := NULL;
614 RETURN TRUE;
615 END;
616 END finp_val_ftci_status;
617 --
618 -- Validate the IGS_FI_FEE_TYPE in the fee_type_account is not closed.
619 -- Bug 1956374 Removed duplicate code finp_val_ft_closed
620 -- Validate PAYMENT HIERARCHY RAN
621 --As part of bugfix#2425767, removed function finp_val_ftci_rank,as this validates obsoleted column,
622 --payment_hierarchy_rank.
623 END IGS_FI_VAL_FTCI;
[Home] [Help]