1 PACKAGE BODY IGS_FI_VAL_FE AS
2 /* $Header: IGSFI29B.pls 115.8 2002/11/29 12:47:26 vvutukur 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 || vvutukur 29-Nov-2002 Enh#2584986.Modified finp_val_fe_cur,finp_val_fe_cur.
12 || vvutukur 26-Aug-2002 Bug#2531390.Modifications done in FUNCTION finp_val_sched_mbrs.
13 ----------------------------------------------------------------------------*/
14 -- bug id : 1956374
15 -- sjadhav , 29-aug-2001
16 -- remove funtion enrp_val_et_closed
17 /****************************
18 Removed the functions shown below as part of bug 2126091 by sykrishn - 30112001
19 1) finp_val_fe_dai
20 2) finp_val_fe_ft
21 3) finp_val_fe_offset
22 4) finp_val_fe_ins
23 5) finp_val_fe_create
24 *****************************/
25 --
26 -- Validate fee_encumbrance dt alias.
27 --Removed this function shown below as part of bug 2126091 by sykrishn - 30112001
28 -- Validate fee_encumbrance fee type
29 --Removed this function shown below as part of bug 2126091 by sykrishn - 30112001
30 --
31 -- Validate fee_encumbrance offsets.
32 --Removed this function shown below as part of bug 2126091 by sykrishn - 30112001
33 --
34 -- Validate IGS_FI_FEE_ENCMB insert.
35 --Removed this function shown below as part of bug 2126091 by sykrishn - 30112001
36 --
37 -- Validate appropriate fields set for relation type.
38 FUNCTION finp_val_sched_mbrs(
39 p_fee_relation_type IN VARCHAR2 ,
40 p_fee_cat IN VARCHAR2 ,
41 p_fee_type IN VARCHAR2 ,
42 p_message_name OUT NOCOPY VARCHAR2 )
43 RETURN BOOLEAN AS
44 /*----------------------------------------------------------------------------
45 || Created By :
46 || Created On :
47 || Purpose :
48 || Known limitations, enhancements or remarks :
49 || Change History :
50 || Who When What
51 || (reverse chronological order - newest change first)
52 || vvutukur 26-Aug-2002 Bug#2531390.Modified the comment which mentions about the validation
53 || of fee_cat and fee_type to avoid confusion as igs_fi_fee_pay_sched is
54 || obsolete.
55 ----------------------------------------------------------------------------*/
56 gv_other_detail VARCHAR2(255);
57 BEGIN
58 DECLARE
59 cst_relation_type_FTCI CONSTANT VARCHAR2(4) := 'FTCI';
60 cst_relation_type_FCCI CONSTANT VARCHAR2(4) := 'FCCI';
61 cst_relation_type_FCFL CONSTANT VARCHAR2(4) := 'FCFL';
62 BEGIN
63 -- Validate if p_fee_cat and p_fee_type are only specified
64 -- for the appropriate p_fee_relation_type.
65 --1. Check parameters.
66 IF (p_fee_relation_type IS NULL) THEN
67 p_message_name := NULL;
68 RETURN TRUE;
69 END IF;
70 IF p_fee_relation_type NOT IN (cst_relation_type_FTCI, cst_relation_type_FCCI,
71 cst_relation_type_FCFL) THEN
72 p_message_name := 'IGS_FI_FUNC_P_RELATION_TYPE';
73 RETURN FALSE;
74 END IF;
75 --2. Validate that for relation type 'FTCI', fee_type
76 --is specified and fee_cat is NULL.
77 IF p_fee_relation_type = cst_relation_type_FTCI THEN
78 IF (p_fee_type IS NOT NULL AND
79 p_fee_cat IS NULL) THEN
80 p_message_name := NULL;
81 RETURN TRUE;
82 ELSE
83 p_message_name := 'IGS_FI_FEETYPE_MUSTBE_SPECIFI';
84 RETURN FALSE;
85 END IF;
86 END IF;
87 --3. Validate that for relation type 'FCCI', fee_cat is
88 --specified and fee_type IS NULL.
89 IF p_fee_relation_type = cst_relation_type_FCCI THEN
90 IF (p_fee_cat IS NOT NULL AND
91 p_fee_type IS NULL) THEN
92 p_message_name := NULL;
93 RETURN TRUE;
94 ELSE
95 p_message_name := 'IGS_FI_FEECAT_MUSTBE_SPECIFIE';
96 RETURN FALSE;
97 END IF;
98 END IF;
99 --4. Validate that for relation type 'FCFL' both fee_cat
100 --and fee_type are specified.
101 IF p_fee_relation_type = cst_relation_type_FCFL THEN
102 IF (p_fee_type IS NOT NULL AND
103 p_fee_cat IS NOT NULL) THEN
104 p_message_name := NULL;
105 RETURN TRUE;
106 ELSE
107 p_message_name := 'IGS_FI_BOTH_FEECAT_FEETYPE';
108 RETURN FALSE;
109 END IF;
110 END IF;
111 --5. Return no error.
112 p_message_name := NULL;
113 RETURN TRUE;
114 END;
115 END finp_val_sched_mbrs;
116 --
117 -- Validate fee encumbrance can be created for the relation type.
118 --Removed this function shown below as part of bug 2126091 by sykrishn - 30112001
119 --
120 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
121 --
122 -- Validate insert of FE does not clash currencywith FCFL definitions
123 FUNCTION finp_val_fe_cur(
124 p_fee_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
125 p_fee_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
126 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
127 p_s_relation_type IN VARCHAR2 ,
128 p_message_name OUT NOCOPY VARCHAR2 )
129 RETURN BOOLEAN AS
130 /*----------------------------------------------------------------------------
131 || Created By :
132 || Created On :
133 || Purpose :
134 || Known limitations, enhancements or remarks :
135 || Change History :
136 || Who When What
137 || (reverse chronological order - newest change first)
138 || vvutukur 29-Nov-2002 Enh#2584986. Removed the references to
139 || igs_fi_cur, instead defaulted the currency
140 || that is set up in System Options Form.
141 ----------------------------------------------------------------------------*/
142 gv_other_detail VARCHAR2(255);
143 BEGIN -- finp_val_fe_cur
144
145 -- When adding an entry at the FTCI level;
146 -- check there are no related fee category fee liabilities
147 -- with a fee category currency set to other than the local
148 -- currency.
149 DECLARE
150
151 v_fee_cat_currency_cd igs_fi_control.currency_cd%TYPE;
152 l_v_currency igs_fi_control.currency_cd%TYPE;
153
154 CURSOR cur_ctrl IS
155 SELECT currency_cd
156 FROM igs_fi_control;
157
158 CURSOR c_fc (cp_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE) IS
159 SELECT currency_cd
160 FROM IGS_FI_FEE_CAT
161 WHERE fee_cat = cp_fee_cat;
162 CURSOR c_fcfl IS
163 SELECT fee_cat
164 FROM IGS_FI_F_CAT_FEE_LBL
165 WHERE fee_cal_type = p_fee_cal_type AND
166 fee_ci_sequence_number = p_fee_ci_sequence_number AND
167 fee_type = p_fee_type;
168 BEGIN
169 p_message_name := NULL;
170 -- check if the definition is at the Fee Type Calendar Instance level
171 IF (p_s_relation_type <> 'FTCI') THEN
172 RETURN TRUE;
173 END IF;
174
175 --Capture the default currency that is set up in System Options Form.
176 OPEN cur_ctrl;
177 FETCH cur_ctrl INTO l_v_currency;
178 IF cur_ctrl%NOTFOUND THEN
179 p_message_name := 'IGS_FI_SYSTEM_OPT_SETUP';
180 CLOSE cur_ctrl;
181 RETURN FALSE;
182 END IF;
183 CLOSE cur_ctrl;
184
185 -- check there are no related fee category fee liabilities
186 -- with a fee category currencyset to other than the local
187 -- currency
188 FOR v_fcfl_rec IN c_fcfl LOOP
189 -- get the fee category currencycode
190 OPEN c_fc (v_fcfl_rec.fee_cat);
191 FETCH c_fc INTO v_fee_cat_currency_cd;
192 CLOSE c_fc;
193 IF (NVL(v_fee_cat_currency_cd, l_v_currency) <> l_v_currency)
194 THEN
195 p_message_name := 'IGS_FI_FEEENCUMB_NOT_CREATED';
196 RETURN FALSE;
197 END IF;
198 END LOOP;
199 RETURN TRUE;
200 END;
201 END finp_val_fe_cur;
202 END IGS_FI_VAL_FE;