1 PACKAGE BODY IGS_FI_VAL_FC AS
2 /* $Header: IGSFI24B.pls 115.7 2002/11/29 11:13:48 vvutukur ship $ */
3 --
4 /*----------------------------------------------------------------------------
5 || Created By :
6 || Created On :
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 || vvutukur 29-Nov-2002 Enh#2584986.Obsoleted finp_val_fc_cur_upd.
13 || vvutukur 26-Aug-2002 Bug#2531390.Modified function finp_val_fc_cur_upd.Removed DEFAULT clause
14 || in this package body to avoid gscc warning.
15 ----------------------------------------------------------------------------*/
16 /* Removed reference to IGS_FI_FEE_ENCMB_TYPE_V as part of bug 2126091 - sykrishn 29112001 */
17 -- Validate update of fee category closed indicator.
18 FUNCTION finp_val_fc_clsd_upd(
19 p_fee_cat IN VARCHAR2 ,
20 p_closed_ind IN VARCHAR2,
21 p_message_name OUT NOCOPY VARCHAR2 )
22 RETURN BOOLEAN AS
23 /*----------------------------------------------------------------------------
24 || Created By :
25 || Created On :
26 || Purpose :
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 || vvutukur 02-Sep-2002 Bug#2531390.Removed DEFAULT clause to avoid gscc warning, and replaced
32 || with assignment operator for defaulting variable v_ret_val.
33 ----------------------------------------------------------------------------*/
34 gv_other_detail VARCHAR2(255);
35 BEGIN -- finp_val_fc_clsd_upd
36 -- Validate update of the IGS_FI_FEE_CAT.closed_ind.
37 DECLARE
38 v_check CHAR;
39 v_ret_val BOOLEAN := TRUE;
40 CURSOR c_fcm IS
41 SELECT 'x'
42 FROM IGS_FI_FEE_CAT_MAP
43 WHERE fee_cat = p_fee_cat AND
44 dflt_cat_ind = 'Y';
45 BEGIN
46 p_message_name := NULL;
47 IF (p_closed_ind = 'Y') THEN
48 -- Validate if the fee category is the default for an admission category.
49 OPEN c_fcm;
50 FETCH c_fcm INTO v_check;
51 IF (c_fcm%FOUND) THEN
52 p_message_name := 'IGS_FI_FEECAT_NC_DFLT_ADMCAT';
53 v_ret_val := FALSE;
54 END IF;
55 CLOSE c_fcm;
56 END IF;
57 RETURN v_ret_val;
58 END;
59 END finp_val_fc_clsd_upd;
60 --
61 -- Warn if IGS_FI_FEE_CAT.currency_cd change effects child records.
62 FUNCTION finp_chk_rates_exist(
63 p_fee_cat IN VARCHAR2 ,
64 p_message_name OUT NOCOPY VARCHAR2 )
65 RETURN BOOLEAN AS
66 gv_other_detail VARCHAR2(255);
67 BEGIN -- finp_chk_rates_exist
68 -- Check if IGS_FI_F_CAT_FEE_LBL records exist for the IGS_FI_FEE_CAT,
69 -- which have associated IGS_FI_FEE_AS_RATE records.
70 DECLARE
71 cst_fcfl CONSTANT VARCHAR2(5) := 'FCFL';
72 CURSOR c_fcfl(
73 cp_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE) IS
74 SELECT fee_cal_type,
75 fee_ci_sequence_number,
76 FEE_TYPE
77 FROM IGS_FI_F_CAT_FEE_LBL
78 WHERE fee_cat = cp_fee_cat;
79 CURSOR c_far(
80 cp_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE,
81 cp_fee_cal_type IGS_FI_F_CAT_FEE_LBL.fee_cal_type%TYPE,
82 cp_fee_ci_sequence_number IGS_FI_F_CAT_FEE_LBL.fee_ci_sequence_number%TYPE,
83 cp_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE) IS
84 SELECT rate_number
85 FROM IGS_FI_FEE_AS_RATE
86 WHERE fee_type= cp_fee_type AND
87 fee_cal_type = cp_fee_cal_type AND
88 fee_ci_sequence_number = cp_fee_ci_sequence_number AND
89 s_relation_type = cst_fcfl AND
90 fee_cat = cp_fee_cat;
91 CURSOR c_err(
92 cp_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE,
93 cp_fee_cal_type IGS_FI_F_CAT_FEE_LBL.fee_cal_type%TYPE,
94 cp_fee_ci_sequence_number IGS_FI_F_CAT_FEE_LBL.fee_ci_sequence_number%TYPE,
95 cp_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE)IS
96 SELECT range_number
97 FROM IGS_FI_ELM_RANGE_RT
98 WHERE FEE_TYPE = cp_fee_type AND
99 fee_cal_type = cp_fee_cal_type AND
100 fee_ci_sequence_number = cp_fee_ci_sequence_number AND
101 s_relation_type = cst_fcfl AND
102 fee_cat = cp_fee_cat AND
103 logical_delete_dt IS NULL;
104 v_err_rec c_err%ROWTYPE;
105 v_far_rec c_far%ROWTYPE;
106 BEGIN
107 -- Set the default message number
108 p_message_name := NULL;
109 -- Check parameter
110 IF p_fee_cat IS NULL THEN
111 RETURN FALSE;
112 END IF;
113 -- Use a loop to find any IGS_FI_F_CAT_FEE_LBL records based on the IGS_FI_FEE_CAT.
114 FOR v_fcfl_rec IN c_fcfl(p_fee_cat) LOOP
115 -- Check if any associated IGS_FI_FEE_AS_RATE records exist for the
116 -- IGS_FI_F_CAT_FEE_LBL record. If so, return a warning.
117 OPEN c_far(
118 v_fcfl_rec.fee_type,
119 v_fcfl_rec.fee_cal_type,
120 v_fcfl_rec.fee_ci_sequence_number,
121 p_fee_cat);
122 FETCH c_far INTO v_far_rec;
123 IF c_far%NOTFOUND THEN
124 CLOSE c_far;
125 ELSE
126 CLOSE c_far;
127 p_message_name := 'IGS_FI_FEECATFEELIAB_EXIST';
128 RETURN TRUE;
129 END IF;
130 -- Check if any associated IGS_FI_ELM_RANGE_RT records exist for the
131 -- IGS_FI_F_CAT_FEE_LBL record. If so, return a warning.
132 OPEN c_err(
133 v_fcfl_rec.FEE_TYPE,
134 v_fcfl_rec.fee_cal_type,
135 v_fcfl_rec.fee_ci_sequence_number,
136 p_fee_cat);
137 FETCH c_err INTO v_err_rec;
138 IF c_err%NOTFOUND THEN
139 CLOSE c_err;
140 ELSE
141 CLOSE c_err;
142 p_message_name := 'IGS_FI_FEECATFEELIAB_EXIST';
143 RETURN TRUE;
144 END IF;
145 END LOOP;
146 -- Return the default value
147 RETURN FALSE;
148 END;
149 END finp_chk_rates_exist;
150 END IGS_FI_VAL_FC;
[Home] [Help]