1 PACKAGE BODY IGS_FI_VAL_FRTNS AS
2 /* $Header: IGSFI32B.pls 115.7 2002/12/30 10:22:22 sykrishn ship $ */
3 --
4 /*Change History:
5 Who When What
6 sykrishn 30dec2002 Bug 2708665 - Function finp_val_frtns_amt Changed the message to be returned
7 as more menaingful instead of IGS_GE_MANDATORY_FLD.
8 vvutukur 29-Nov-2002 Enh#2584986.Modified finp_val_frtns_cur to remove references to igs_fi_cur.
9
10 /* Bug 1956374
11 What Duplicate code finp_val_sched_mbrs removed
12 who msrinivi
13 */
14 -- Validate fee retention schedules can be created for the relation type.
15 FUNCTION finp_val_frtns_creat(
16 p_fee_type IN VARCHAR2 ,
17 p_fee_cal_type IN VARCHAR2 ,
18 p_fee_ci_sequence_number IN NUMBER ,
19 p_s_relation_type IN VARCHAR2 ,
20 p_message_name OUT NOCOPY VARCHAR2 )
21 RETURN BOOLEAN AS
22 gv_other_detail VARCHAR2(255);
23 BEGIN -- finp_val_frtns_creat
24 -- Validate if IGS_FI_FEE_RET_SCHD records can be created.
25 -- When defined at FTCI level, they cannot also be
26 -- defined at FCFL level and vice-versa.
27 DECLARE
28 CURSOR c_frtns (
29 cp_s_relation_type IGS_FI_FEE_RET_SCHD.s_relation_type%TYPE) IS
30 SELECT 'x'
31 FROM IGS_FI_FEE_RET_SCHD
32 WHERE FEE_TYPE = p_fee_type AND
33 fee_cal_type = p_fee_cal_type AND
34 fee_ci_sequence_number = p_fee_ci_sequence_number AND
35 s_relation_type = cp_s_relation_type;
36 v_fcfl_exists VARCHAR2(1);
37 v_ftci_exists VARCHAR2(1);
38 BEGIN
39 -- Set the default message number
40 p_message_name := NULL;
41 -- 1. Check Parameters
42 IF p_fee_type IS NULL OR
43 p_fee_cal_type IS NULL OR
44 p_fee_ci_sequence_number IS NULL OR
45 p_s_relation_type IS NULL THEN
46 RETURN TRUE;
47 END IF;
48 -- 2. If p_s_relation_type = FCFL, check if any records
49 -- have been defined at the FTCI level. If so, return error.
50 IF p_s_relation_type = 'FCFL' THEN
51 OPEN c_frtns(
52 'FTCI');
53 FETCH c_frtns INTO v_ftci_exists;
54 IF c_frtns%FOUND THEN
55 CLOSE c_frtns;
56 p_message_name := 'IGS_FI_FEE_RETN_SCH_FEECAT';
57 RETURN FALSE;
58 END IF;
59 CLOSE c_frtns;
60 END IF;
61 -- 3. If p_s_relation_type = FTCI, check if any records
62 -- have been defined at the FCFL level. If so, return error.
63 IF p_s_relation_type = 'FTCI' THEN
64 OPEN c_frtns(
65 'FCFL');
66 FETCH c_frtns INTO v_fcfl_exists;
67 IF c_frtns%FOUND THEN
68 CLOSE c_frtns;
69 p_message_name := 'IGS_FI_FEE_RETN_SCH_FEETYPE';
70 RETURN FALSE;
71 END IF;
72 CLOSE c_frtns;
73 END IF;
74 RETURN TRUE;
75 END;
76 END finp_val_frtns_creat;
77 --
78 -- Validate IGS_FI_FEE_RET_SCHD retention_amount
79 FUNCTION finp_val_frtns_amt(
80 p_retention_amount IN NUMBER ,
81 p_retention_percentage IN NUMBER ,
82 p_message_name OUT NOCOPY VARCHAR2 )
83 RETURN BOOLEAN AS
84 gv_other_detail VARCHAR2(255);
85 BEGIN -- finp_val_frtns_amt
86 -- Validate IGS_FI_FEE_RET_SCHD.retention_amount and
87 -- retention_percentage.
88 -- Only one of either retention_amount or retention_percentage
89 -- may be specified.
90 -- Set the default message number
91 p_message_name := NULL;
92 -- 1. Check parameter values
93 IF (p_retention_amount IS NOT NULL AND
94 p_retention_percentage IS NOT NULL) THEN
95 p_message_name := 'IGS_FI_ONE_RETAMT_OR_RETPREC';
96 RETURN FALSE;
97 END IF;
98 IF (p_retention_amount IS NULL AND
99 p_retention_percentage IS NULL) THEN
100 p_message_name := 'IGS_FI_RETAMT_OR_PER_MAND';
101 RETURN FALSE;
102 END IF;
103 -- 2. Return no error
104 RETURN TRUE;
105 END finp_val_frtns_amt;
106 --
107 -- Validate IGS_FI_FEE_RET_SCHD fee type
108 FUNCTION finp_val_frtns_ft(
109 p_fee_type IN VARCHAR2 ,
110 p_s_relation_type IN VARCHAR2 ,
111 p_message_name OUT NOCOPY VARCHAR2 )
112 RETURN BOOLEAN AS
113 gv_other_detail VARCHAR2(255);
114 BEGIN -- finp_val_frtns_ft
115 -- Validate IGS_FI_FEE_RET_SCHD.FEE_TYPE.
116 -- If IGS_FI_FEE_TYPE.s_fee_trigger_cat = 'INSTITUTN', then retention schedules
117 -- can only be defined against fee_type_cal_instances. If the
118 -- IGS_FI_FEE_TYPE.optional_payment_ind
119 -- is set to 'Y' then fee retention schedules can only be set against
120 -- fee_cat_cal_instances.
121 DECLARE
122 cst_institutn CONSTANT VARCHAR(10) := 'INSTITUTN';
123 cst_hecs CONSTANT VARCHAR(4) := 'HECS';
124 cst_ftci CONSTANT VARCHAR(5) := 'FTCI';
125 cst_fcci CONSTANT VARCHAR(5) := 'FCCI';
126 cst_yes CONSTANT CHAR := 'Y';
127 CURSOR c_ft(
128 cp_fee_type IGS_FI_FEE_TYPE.FEE_TYPE%TYPE) IS
129 SELECT s_fee_trigger_cat,
130 s_fee_type,
131 optional_payment_ind
132 FROM IGS_FI_FEE_TYPE
133 WHERE FEE_TYPE = cp_fee_type;
134 v_ft_rec c_ft%ROWTYPE;
135 BEGIN
136 -- Set the default message number
137 p_message_name := NULL;
138 IF (p_fee_type IS NULL) THEN
139 RETURN TRUE;
140 END IF;
141 -- Get the system fee trigger category and the optional indicator of the
142 -- IGS_FI_FEE_TYPE.
143 OPEN c_ft (p_fee_type);
144 FETCH c_ft INTO v_ft_rec;
145 IF c_ft%NOTFOUND THEN
146 CLOSE c_ft;
147 RAISE NO_DATA_FOUND;
148 END IF;
149 CLOSE c_ft;
150 IF v_ft_rec.s_fee_trigger_cat = cst_institutn AND
151 p_s_relation_type <> cst_ftci THEN
152 p_message_name := 'IGS_FI_FEERET_SCH_FEETYPE_CAL';
153 RETURN FALSE;
154 END IF;
155 IF v_ft_rec.s_fee_type = cst_hecs AND
156 p_s_relation_type <> cst_ftci THEN
157 p_message_name := 'IGS_FI_FEERET_SCH_ONLY_DFEINE';
158 RETURN FALSE;
159 END IF;
160 IF v_ft_rec.optional_payment_ind = cst_yes AND
161 p_s_relation_type <> cst_fcci THEN
162 p_message_name := 'IGS_FI_FEERET_SCH_FEECAT_CAL';
163 RETURN FALSE;
164 END IF;
165 -- Return the default value
166 RETURN TRUE;
167 END;
168 END finp_val_frtns_ft;
169 --
170 -- Validate appropriate fields set for relation type.
171 -- Duplicate code finp_val_sched_mbrs removed
172 -- Validate insert of FRTNS does not clash currency code set up with FCFL definitions
173 FUNCTION finp_val_frtns_cur(
174 p_fee_cal_type IN IGS_CA_TYPE.CAL_TYPE%TYPE ,
175 p_fee_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
176 p_fee_type IN IGS_FI_FEE_TYPE_ALL.FEE_TYPE%TYPE ,
177 p_s_relation_type IN VARCHAR2 ,
178 p_message_name OUT NOCOPY VARCHAR2 )
179 RETURN BOOLEAN AS
180 /*----------------------------------------------------------------------------
181 || Created By :
182 || Created On :
183 || Purpose :
184 || Known limitations, enhancements or remarks :
185 || Change History :
186 || Who When What
187 || (reverse chronological order - newest change first)
188 || vvutukur 29-Nov-2002 Enh#2584986. Removed the references to
189 || igs_fi_cur, instead defaulted the currency
190 || that is set up in System Options Form.
191 ----------------------------------------------------------------------------*/
192 gv_other_detail VARCHAR2(255);
193 BEGIN -- finp_val_frtns_cur
194 -- Validate insert of the IGS_FI_FEE_RET_SCHD.
195 -- When adding an entry at the FTCI level;
196 -- check there are no related fee category fee liabilities
197 -- with a fee category currency set to other than the local
198 -- currency.
199 DECLARE
200
201 l_v_currency igs_fi_control.currency_cd%TYPE;
202 v_fee_cat_currency_cd igs_fi_control.currency_cd%TYPE;
203
204 CURSOR c_fc (cp_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE) IS
205 SELECT currency_cd
206 FROM IGS_FI_FEE_CAT
207 WHERE FEE_CAT = cp_fee_cat;
208 CURSOR c_fcfl IS
209 SELECT FEE_CAT
210 FROM IGS_FI_F_CAT_FEE_LBL
211 WHERE fee_cal_type = p_fee_cal_type AND
212 fee_ci_sequence_number = p_fee_ci_sequence_number AND
213 FEE_TYPE = p_fee_type;
214 CURSOR cur_ctrl IS
215 SELECT currency_cd
216 FROM igs_fi_control;
217 BEGIN
218 p_message_name := NULL;
219 -- check if the definition is at the Fee Type Calendar Instance level
220 IF (p_s_relation_type <> 'FTCI') THEN
221 RETURN TRUE;
222 END IF;
223
224 --Capture the default currency that is set up in System Options Form.
225 OPEN cur_ctrl;
226 FETCH cur_ctrl INTO l_v_currency;
227 IF cur_ctrl%NOTFOUND THEN
228 p_message_name := 'IGS_FI_SYSTEM_OPT_SETUP';
229 CLOSE cur_ctrl;
230 RETURN FALSE;
231 END IF;
232 CLOSE cur_ctrl;
233
234 -- check there are no related fee category fee liabilities
235 -- with a fee category currency set to other than the local
236 -- currency.
237 FOR v_fcfl_rec IN c_fcfl LOOP
238 -- get the fee category currency code
239 OPEN c_fc (v_fcfl_rec.fee_cat);
240 FETCH c_fc INTO v_fee_cat_currency_cd;
241 CLOSE c_fc;
242 IF (NVL(v_fee_cat_currency_cd, l_v_currency) <> l_v_currency)
243 THEN
244 p_message_name := 'IGS_FI_FEEPYM_CLASH_FEECAT';
245 RETURN FALSE;
246 END IF;
247 END LOOP;
248 RETURN TRUE;
249 END;
250 END finp_val_frtns_cur;
251 END IGS_FI_VAL_FRTNS;
[Home] [Help]