[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_VAL_USFT
Source
1 PACKAGE BODY IGS_EN_VAL_USFT AS
2 /* $Header: IGSEN72B.pls 115.4 2002/11/29 00:08:55 nsidana ship $ */
3 --
4
5 -- Ensure IGS_PS_UNIT set fee triggers can be created.
6
7 FUNCTION finp_val_usft_ins(
8
9 p_fee_type IN VARCHAR2 ,
10
11 p_message_name OUT NOCOPY VARCHAR2)
12
13 RETURN BOOLEAN AS
14
15
16
17 BEGIN -- finp_val_usft_ins
18
19 -- Validate IGS_EN_UNITSETFEETRG IGS_FI_FEE_TYPE.s_fee_trigger_cat = UNITSET or COMPOSITE
20
21 -- otherwise IGS_PS_UNIT set fee triggers cannot be defined.
22
23 DECLARE
24
25 CURSOR c_ft(
26
27 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
28
29 SELECT s_fee_trigger_cat
30
31 FROM IGS_FI_FEE_TYPE
32
33 WHERE fee_type = cp_fee_type;
34
35 v_ft_rec c_ft%ROWTYPE;
36
37 cst_unitset CONSTANT VARCHAR2(10) := 'UNITSET';
38
39 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
40
41 BEGIN
42
43 -- Set the default message number
44
45 p_message_name := null;
46
47 -- Check parameters
48
49 IF p_fee_type IS NULL THEN
50
51 RETURN TRUE;
52
53 END IF;
54
55 -- Get the system fee trigger category of the IGS_FI_FEE_TYPE.
56
57 OPEN c_ft (p_fee_type);
58
59 FETCH c_ft INTO v_ft_rec;
60
61 IF c_ft%NOTFOUND THEN
62
63 CLOSE c_ft;
64
65 RETURN TRUE;
66
67 END IF;
68
69 CLOSE c_ft;
70
71 IF v_ft_rec.s_fee_trigger_cat <> cst_unitset AND
72
73 v_ft_rec.s_fee_trigger_cat <> cst_composite THEN
74
75 p_message_name := 'IGS_FI_UNIT_SET_FEETRG_UNIT';
76
77 RETURN FALSE;
78
79 END IF;
80
81 -- Return the default value
82
83 RETURN TRUE;
84
85 END;
86
87 EXCEPTION
88
89 WHEN OTHERS THEN
90
91 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
92 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_USFT.finp_val_usft_ins');
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END finp_val_usft_ins;
96
97 --
98
99 -- Validate IGS_PS_UNIT set fee trigger can belong to a fee trigger group.
100
101 FUNCTION finp_val_usft_ftg(
102
103 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
104
105 p_fee_trigger_group_num IN NUMBER ,
106
107 p_message_name OUT NOCOPY VARCHAR2)
108
109 RETURN BOOLEAN AS
110
111 gv_other_detail VARCHAR2(255);
112
113 BEGIN -- finp_val_usft_ftg
114
115 -- Validate IGS_EN_UNITSETFEETRG can belong to a IGS_FI_FEE_TRG_GRP
116
117 DECLARE
118
119 CURSOR c_ft(
120
121 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
122
123 SELECT s_fee_trigger_cat
124
125 FROM IGS_FI_FEE_TYPE
126
127 WHERE fee_type = cp_fee_type;
128
129 v_ft_rec c_ft%ROWTYPE;
130
131 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
132
133 BEGIN
134
135 p_message_name := null;
136
137 IF(p_fee_type IS NULL OR
138
139 p_fee_trigger_group_num IS NULL) THEN
140
141 RETURN TRUE;
142
143 END IF;
144
145 -- Get the system fee trigger category of the IGS_FI_FEE_TYPE.
146
147 OPEN c_ft (p_fee_type);
148
149 FETCH c_ft INTO v_ft_rec;
150
151 IF c_ft%NOTFOUND THEN
152
153 CLOSE c_ft;
154
155 RETURN TRUE;
156
157 END IF;
158
159 CLOSE c_ft;
160
161 IF v_ft_rec.s_fee_trigger_cat <> cst_composite THEN
162
163 p_message_name := 'IGS_FI_UNITSET_FEETRG_COMPOSI';
164
165 RETURN FALSE;
166
167 END IF;
168
169 RETURN TRUE;
170
171 END;
172
173 EXCEPTION
174
175 WHEN OTHERS THEN
176
177 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
178 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_USFT.finp_val_usft_ftg');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END finp_val_usft_ftg;
182
183 --
184
185 -- Ensure only one open IGS_EN_UNITSETFEETRG record exists.
186
187 FUNCTION finp_val_usft_open(
188
189 p_fee_cat IN IGS_EN_UNITSETFEETRG.fee_cat%TYPE ,
190
191 p_fee_cal_type IN IGS_EN_UNITSETFEETRG.fee_cal_type%TYPE ,
192
193 p_fee_ci_sequence_number IN NUMBER ,
194
195 p_fee_type IN IGS_EN_UNITSETFEETRG.fee_type%TYPE ,
196
197 p_unit_set_cd IN IGS_EN_UNITSETFEETRG.unit_set_cd%TYPE ,
198
199 p_version_number IN IGS_EN_UNITSETFEETRG.version_number%TYPE ,
200
201 p_create_dt IN IGS_EN_UNITSETFEETRG.create_dt%TYPE ,
202
203 p_fee_trigger_group_number IN NUMBER ,
204
205 p_message_name OUT NOCOPY VARCHAR2)
206
207 RETURN BOOLEAN AS
208
209
210
211 BEGIN -- finp_val_usft_open
212
213 -- Validate that there are no other "open" IGS_EN_UNITSETFEETRG records for
214
215 -- the nominated unit_set_cd details and the same parent IGS_FI_F_CAT_FEE_LBL.
216
217 DECLARE
218
219 CURSOR c_usft IS
220
221 SELECT 'x'
222
223 FROM IGS_EN_UNITSETFEETRG usft
224
225 WHERE usft.fee_cat = p_fee_cat AND
226
227 usft.fee_cal_type = p_fee_cal_type AND
228
229 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
230
231 usft.fee_type = p_fee_type AND
232
233 usft.unit_set_cd = p_unit_set_cd AND
234
235 NVL(usft.version_number,0) = NVL(p_version_number,0) AND
236
237 usft.create_dt <> p_create_dt AND
238
239 NVL(usft.fee_trigger_group_number,0) = NVL(p_fee_trigger_group_number,0) AND
240
241 usft.logical_delete_dt IS NULL;
242
243 v_check VARCHAR2(1);
244
245 BEGIN
246
247 --- Set the default message number
248
249 p_message_name := null;
250
251 IF p_fee_cat IS NULL OR
252
253 p_fee_cal_type IS NULL OR
254
255 p_fee_ci_sequence_number IS NULL OR
256
257 p_fee_type IS NULL OR
258
259 p_unit_set_cd IS NULL OR
260
261 p_version_number IS NULL OR
262
263 p_create_dt IS NULL THEN
264
265 RETURN TRUE;
266
267 END IF;
268
269 OPEN c_usft;
270
271 FETCH c_usft INTO v_check;
272
273 IF (c_usft%FOUND) THEN
274
275 CLOSE c_usft;
276
277 p_message_name := 'IGS_GE_DUPLICATE_VALUE';
278
279 RETURN FALSE;
280
281 END IF;
282
283 CLOSE c_usft;
284
285 RETURN TRUE;
286
287 END;
288
289 EXCEPTION
290
291 WHEN OTHERS THEN
292
293 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
294 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_USFT.finp_val_usft_open');
295 IGS_GE_MSG_STACK.ADD;
296 App_Exception.Raise_Exception;
297 END finp_val_usft_open;
298
299 --
300
301 -- To validate the calendar instance system cal status is not 'INACTIVE'
302
303 FUNCTION FINP_VAL_US_STATUS(
304
305 p_unit_set_cd IN IGS_EN_UNIT_SET_ALL.unit_set_cd%TYPE ,
306
307 p_version_number IN IGS_EN_UNIT_SET_ALL.version_number%TYPE ,
308
309 p_message_name OUT NOCOPY VARCHAR2)
310 RETURN boolean AS
311
312
313
314 BEGIN -- finp_val_us_status
315
316 -- Validate the s_unit_set_status is NOT inactive.
317
318 DECLARE
319
320 v_dummy VARCHAR2(1);
321
322 CURSOR c_us ( cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE,
323
324 cp_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
325
326 SELECT 'X'
327
328 FROM IGS_EN_UNIT_SET us,
329
330 IGS_EN_UNIT_SET_STAT uss
331
332 WHERE us.unit_set_cd = p_unit_set_cd AND
333
334 us.version_number = p_version_number AND
335
336 us.unit_set_status = uss.unit_set_status AND
337
338 uss.s_unit_set_status = 'INACTIVE';
339
340 BEGIN
341
342 p_message_name := null;
343
344 IF p_unit_set_cd IS NULL OR
345
346 p_version_number IS NULL THEN
347
348 RETURN TRUE;
349
350 END IF;
351
352 OPEN c_us ( p_unit_set_cd,
353
354 p_version_number);
355
356 FETCH c_us INTO v_dummy;
357
358 IF (c_us%FOUND) THEN
359
360 CLOSE c_us;
361
362 p_message_name := 'IGS_FI_UNITSET_INACTIVE';
363
364 RETURN FALSE;
365
366 END IF;
367
368 CLOSE c_us;
369
370 RETURN TRUE;
371
372 END;
373
374 EXCEPTION
375
376 WHEN OTHERS THEN
377
378 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
379 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_USFT.finp_val_us_status');
380 IGS_GE_MSG_STACK.ADD;
381 App_Exception.Raise_Exception;
382 END finp_val_us_status;
383
384
385
386 END IGS_EN_VAL_USFT;