1 PACKAGE BODY IGS_EN_VAL_PEN AS
2 /* $Header: IGSEN54B.pls 115.6 2002/11/29 00:03:19 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
7
8 -------------------------------------------------------------------------------------------
9 --Bug 1956374 msrinivi Removed func genp_val_prsn_id 27 aug,2001
10 --
11 -- bug id : 1956374
12 -- sjadhav,28-aug-2001
13 -- removed FUNCTION enrp_val_encmb_dt
14 -- removed FUNCTION enrp_val_et_closed
15 --
16 --
17 -- Validate the person does not have an active enrolment.
18 FUNCTION finp_val_encmb_eff(
19 p_person_id IN NUMBER ,
20 p_encumbrance_type IN VARCHAR2 ,
21 p_fee_encumbrance_dt IN DATE ,
22 p_course_cd IN VARCHAR2 ,
23 p_message_name OUT NOCOPY VARCHAR2)
24 RETURN BOOLEAN AS
25
26 BEGIN --finp_val_encmb_eff
27 --Validate that the person does not currently have an active enrolment
28 --if the encumbrance to be applied has an encumbrance_effect which
29 --requires current enrolments to be discontinued before the encumbrance
30 --can be applied.
31 DECLARE
32 v_message_name VARCHAR2(30) DEFAULT NULL;
33 v_return_type VARCHAR2(1);
34 v_start_dt DATE;
35 CURSOR c_etde IS
36 SELECT etde.s_encmb_effect_type
37 FROM IGS_FI_ENC_DFLT_EFT etde
38 WHERE encumbrance_type = p_encumbrance_type;
39 BEGIN
40 --- Set the default message number
41 p_message_name := null;
42 --validate parameters
43 IF (p_person_id IS NULL OR
44 p_encumbrance_type IS NULL OR
45 p_fee_encumbrance_dt IS NULL) THEN
46 RETURN TRUE;
47 END IF;
48 --The start date for encumbrances must not be prior to the current date
49 IF (p_fee_encumbrance_dt < SYSDATE) THEN
50 v_start_dt := SYSDATE;
51 ELSE
52 v_start_dt := p_fee_encumbrance_dt;
53 END IF;
54 --retrieve each encumbrance effect associated with the encumbrance type
55 FOR v_etde_rec IN c_etde LOOP
56 --Depending on the effect type, check for breached enrolment criteria
57 IF (v_etde_rec.s_encmb_effect_type = 'RVK_SRVC') THEN
58 IF (IGS_EN_VAL_PEE.enrp_val_pee_sca(
59 p_person_id,
60 p_message_name) = FALSE) THEN
61 v_message_name := 'IGS_FI_ENCUMB_NOTAPPLIED_RVK';
62 EXIT;
63 END IF;
64 END IF;
65 IF (v_etde_rec.s_encmb_effect_type IN('EXC_COURSE', 'SUS_COURSE')) THEN
66 IF (p_course_cd IS NOT NULL) THEN
67 IF IGS_EN_VAL_PCE.enrp_val_pce_crs(
68 p_person_id,
69 p_course_cd,
70 v_start_dt,
71 v_message_name,
72 v_return_type) = FALSE THEN
73 IF (v_return_type = 'E') THEN
74 v_message_name := 'IGS_FI_ENCUMB_NOTAPPLIED_EXC';
75 EXIT;
76 ELSE
77 v_message_name := null;
78 END IF;
79 ELSE
80 v_message_name := null;
81 END IF;
82 END IF;
83 END IF;
84 END LOOP;
85 IF (v_message_name is not null ) THEN
86 p_message_name := v_message_name;
87 RETURN FALSE;
88 END IF;
89 RETURN TRUE;
90 EXCEPTION
91 WHEN OTHERS THEN
92 IF (c_etde%ISOPEN) THEN
93 CLOSE c_etde;
94 END IF;
95 RAISE;
96 END;
97 EXCEPTION
98 WHEN OTHERS THEN
99 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
100 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PEN.finp_val_encmb_eff');
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103
104 END finp_val_encmb_eff;
105 --
106 --
107 -- Validate that person doesn't already have an open encumbrance.
108 FUNCTION enrp_val_pen_open(
109 p_person_id IN NUMBER ,
110 p_encumbrance_type IN VARCHAR2 ,
111 p_start_dt IN DATE ,
112 p_message_name OUT NOCOPY VARCHAR2)
113 RETURN BOOLEAN AS
114 BEGIN
115 DECLARE
116 v_psn_encmb_rec IGS_PE_PERS_ENCUMB%ROWTYPE;
117 v_encmb_type_rec IGS_FI_ENCMB_TYPE%ROWTYPE;
118 CURSOR c_psn_encmb_rec (
119 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
120 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
121 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE) IS
122 SELECT *
123 FROM IGS_PE_PERS_ENCUMB
124 WHERE person_id = cp_person_id AND
125 encumbrance_type = cp_encumbrance_type AND
126 start_dt <> cp_start_dt AND
127 expiry_dt IS NULL;
128 CURSOR c_encmb_type_rec (
129 cp_encumbrance_type IGS_FI_ENCMB_TYPE.encumbrance_type%TYPE) IS
130 SELECT *
131 FROM IGS_FI_ENCMB_TYPE
132 WHERE encumbrance_type = cp_encumbrance_type AND
133 s_encumbrance_cat = 'ADMIN';
134 BEGIN
135 -- This module checks if there are no other
136 -- 'open ended' IGS_PE_PERS_ENCUMB records for
137 -- the nominated encumbrance type.
138 p_message_name := null;
139 OPEN c_encmb_type_rec(p_encumbrance_type);
140 FETCH c_encmb_type_rec INTO v_encmb_type_rec;
141 IF (c_encmb_type_rec%NOTFOUND) THEN
142 CLOSE c_encmb_type_rec;
143 RETURN TRUE;
144 ELSE
145 CLOSE c_encmb_type_rec;
146 END IF;
147 OPEN c_psn_encmb_rec(p_person_id,
148 p_encumbrance_type,
149 p_start_dt);
150 FETCH c_psn_encmb_rec INTO v_psn_encmb_rec;
151 IF (c_psn_encmb_rec%NOTFOUND) THEN
152 CLOSE c_psn_encmb_rec;
153 ELSE -- data is found
154 CLOSE c_psn_encmb_rec;
155 p_message_name := 'IGS_EN_ENCMBR_HAS_SPECIFIED';
156 RETURN FALSE;
157 END IF;
158 RETURN TRUE;
159 EXCEPTION
160 WHEN OTHERS THEN
161 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
162 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PEN.enrp_val_pen_open');
163 IGS_GE_MSG_STACK.ADD;
164 App_Exception.Raise_Exception;
165
166 END;
167 END enrp_val_pen_open;
168 --
169 -- Validate the application of an encumbrance type to a person.
170 FUNCTION enrp_val_prsn_encmb(
171 p_person_id IN NUMBER ,
172 p_encumbrance_type IN VARCHAR2 ,
173 p_start_dt IN DATE ,
174 p_expiry_dt IN DATE ,
175 p_message_name OUT NOCOPY VARCHAR2)
176 RETURN BOOLEAN AS
177 gv_encmb_eff_rec_found BOOLEAN;
178 gv_count NUMBER;
179 gv_level NUMBER;
180 gv_mess_num NUMBER;
181 gv_mess_name VARCHAR2(30);
182 gv_rstr_at_ty BOOLEAN;
183 gv_rstr_ge_cp BOOLEAN;
184 gv_rstr_le_cp BOOLEAN;
185 CURSOR gc_encmb_eff (
186 cp_enc_type IGS_FI_ENC_DFLT_EFT.encumbrance_type%TYPE)IS
187 SELECT etde.s_encmb_effect_type,
188 seet.encumbrance_level
189 FROM IGS_FI_ENC_DFLT_EFT etde,
190 -- s_encmb_effect_type seet
191 IGS_EN_ENCMB_EFCTTYP_V SEET
192 WHERE etde.encumbrance_type = cp_enc_type AND
193 seet.s_encmb_effect_type = etde.s_encmb_effect_type
194 ORDER BY etde.s_encmb_effect_type ASC;
195 BEGIN
196 DECLARE
197 FUNCTION enrpl_val_rest_effects(
198 p_validation_level VARCHAR2,
199 p_encmb_effect_type IGS_FI_ENC_DFLT_EFT.s_encmb_effect_type%TYPE,
200 p_message_name OUT NOCOPY varchar2)
201 RETURN BOOLEAN IS
202 BEGIN
203 DECLARE
204 BEGIN
205 -- set the default message number
206 -- to 0
207 p_message_name := null;
208 -- set encumbrance effect type flags
209 IF (p_encmb_effect_type = 'RSTR_AT_TY') THEN
210 gv_rstr_at_ty := TRUE;
211 END IF;
212 IF (p_encmb_effect_type = 'RSTR_GE_CP') THEN
213 gv_rstr_ge_cp := TRUE;
214 END IF;
215 IF (p_encmb_effect_type = 'RSTR_LE_CP') THEN
216 gv_rstr_le_cp := TRUE;
217 END IF;
218 -- validate that encumbrance effects are a
219 -- valid combination
220 -- RSTR_AT_TY not valid with RSTR_GE_CP or RSTR_LE_CP
221 -- RSTR_GE_CP not valid with RSTR_AT_TY or RSTR_LE_CP
222 -- RSTR_LE_CP not valid with RSTR_AT_TY or RSTR_GE_CP
223 IF ((gv_rstr_at_ty = TRUE AND (gv_rstr_ge_cp = TRUE OR
224 gv_rstr_le_cp = TRUE)) OR
225 (gv_rstr_ge_cp = TRUE AND (gv_rstr_at_ty = TRUE OR
226 gv_rstr_le_cp = TRUE)) OR
227 (gv_rstr_le_cp = TRUE AND (gv_rstr_at_ty = TRUE OR
228 gv_rstr_ge_cp = TRUE))) THEN
229 IF (p_validation_level = 'ENCUMBRANCE LEVEL') THEN
230 p_message_name := 'IGS_EN_ENCUMBTYPE_INV_COMBI';
231 ELSE
232 p_message_name := 'IGS_EN_ENCUMBTYPE_PRG_INVALID';
233 END IF;
234 RETURN FALSE;
235 END IF;
236 -- set the default return type
237 RETURN TRUE;
238 EXCEPTION
239 WHEN OTHERS THEN
240 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
241 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PEN.enrpl_val_rest_effects');
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244
245 END;
246 END enrpl_val_rest_effects;
247 BEGIN
248 -- This routine validates the combination of encumbrance
249 -- effect toyes appplied to a person.
250 -- The encumbrance type being applied must :
251 -- 1. have effect defined
252 -- 2. have effects at the same level
253 -- 3. have a valid combination of effects
254 -- 4. not have effects at a different level than
255 -- other effects defined for the person for the
256 -- effective period of time
257 -- 5. not have effects that are an invalid combination
258 -- with other effects defined for the person for the
259 -- effective period of time
260 -- set the default message number
261 p_message_name := null;
262 -- set that no encumbrance records have
263 -- yet been found
264 gv_encmb_eff_rec_found := FALSE;
265 -- initialise the count for encumbrance records
266 gv_count := 0;
267 -- retriveing the encumbrance effects for the goven
268 -- encumbrance type
269 FOR gv_encmb_eff_rec IN gc_encmb_eff(p_encumbrance_type) LOOP
270 -- set that a record was found
271 gv_encmb_eff_rec_found := TRUE;
272 -- counting the number of records found
273 gv_count := gv_count + 1;
274 -- validate that encumbrance effects are
275 -- the same level
276 IF (gv_count = 1) THEN
277 -- the first record selected
278 gv_level := gv_encmb_eff_rec.encumbrance_level;
279 ELSE
280 -- not at the first record
281 IF (gv_encmb_eff_rec.encumbrance_level <> gv_level) THEN
282 p_message_name := 'IGS_EN_ENCUMBTYPE_DIFF_LVLS';
283 RETURN FALSE;
284 END IF;
285 END IF;
286 -- validate that encumbrance effects are
287 -- a valid combination
288 IF (enrpl_val_rest_effects(
289 'ENCUMBRANCE LEVEL',
290 gv_encmb_eff_rec.s_encmb_effect_type,
291 gv_mess_name) = FALSE) THEN
292 p_message_name := gv_mess_name;
293 RETURN FALSE;
294 END IF;
295 END LOOP;
296 -- check if encumbrance record were found
297 -- (ie. whether encumbrance effects have been
298 -- defined for the encumbrance type)
299 IF (gv_encmb_eff_rec_found = FALSE) THEN
300 p_message_name := 'IGS_EN_ENCUMB_TYPE_NOTAPPLIED';
301 RETURN FALSE;
302 END IF;
303 -- set the default return type
304 RETURN TRUE;
305 END;
306 EXCEPTION
307 WHEN OTHERS THEN
308 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
309 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PEN.enrp_val_prsn_encmb');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312
313 END enrp_val_prsn_encmb;
314
315 END IGS_EN_VAL_PEN;
[Home] [Help]