[Home] [Help]
PACKAGE BODY: APPS.IGS_RU_GEN_003
Source
1 PACKAGE BODY IGS_RU_GEN_003 AS
2 /* $Header: IGSRU03B.pls 120.2 2005/07/19 07:03:02 appldev ship $ */
3
4 Function Rulp_Clc_Student_Fee(
5 p_rule_number IN NUMBER ,
6 p_charge_elements IN NUMBER ,
7 p_charge_rate IN NUMBER )
8 RETURN NUMBER IS
9 v_message VARCHAR2(1000);
10 BEGIN
11 RETURN IGS_RU_GEN_001.RULP_VAL_SENNA(
12 p_message => v_message,
13 p_rule_number => p_rule_number,
14 p_param_1 => p_charge_elements,
15 p_param_2 => p_charge_rate );
16 END;
17
18 Function Rulp_Del_Rlov(
19 p_sequence_number IN NUMBER ,
20 p_message_name OUT NOCOPY VARCHAR2 )
21 RETURN BOOLEAN IS
22 gv_other_detail VARCHAR2(255);
23 BEGIN
24 /*
25 rulp_del_rlov
26 Delete from IGS_RU_LOV table
27 */
28 DECLARE
29 e_resource_busy EXCEPTION;
30 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
31 /*
32 Cursor to lock records for delete
33 */
34
35 CURSOR c_rlov_delete IS
36 SELECT rlov.rowid
37 FROM IGS_RU_LOV rlov
38 WHERE rlov.sequence_number = p_sequence_number
39 FOR UPDATE OF rlov.sequence_number NOWAIT;
40 BEGIN
41 p_message_name := null;
42 FOR v_rlov_delete_rec IN c_rlov_delete LOOP
43 /*
44 Delete current record
45 */
46 IGS_RU_LOV_PKG.Delete_Row(
47 X_ROWID => v_rlov_delete_rec.rowid
48 );
49 END LOOP;
50 /*
51 ecord successfully deleted.
52 */
53 RETURN TRUE;
54 EXCEPTION
55 WHEN e_resource_busy THEN
56 IF (c_rlov_delete%ISOPEN) THEN
57 CLOSE c_rlov_delete;
58 END IF;
59 p_message_name := 'IGS_GE_REC_NOT_LOCKED';
60 RETURN FALSE;
61 WHEN OTHERS THEN
62 IF (c_rlov_delete%ISOPEN) THEN
63 CLOSE c_rlov_delete;
64 END IF;
65 RAISE;
66 END;
67 EXCEPTION
68 WHEN OTHERS THEN
69 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
70 Fnd_Message.Set_Token('NAME','IGS_RU_GEN_003.rulp_del_rlov');
71 IGS_GE_MSG_STACK.ADD;
72 App_Exception.Raise_Exception;
73 END rulp_del_rlov;
74
75 Procedure Rulp_Del_Rule(
76 p_rule_number IN NUMBER )
77 IS
78 /*
79 cascade delete IGS_RU_RULE items
80 */
81 PROCEDURE delete_rule_items(
82 p_rule_number IN IGS_RU_RULE.sequence_number%TYPE,
83 p_item IN IGS_RU_ITEM.item%TYPE)
84 IS
85 CURSOR Cur_Item_Del( r_item IGS_RU_ITEM.item%TYPE) IS
86 SELECT rowid
87 FROM IGS_RU_ITEM
88 WHERE rul_sequence_number = p_rule_number
89 AND item = r_item;
90
91 CURSOR Cur_Rule_Del(r_rule_number IGS_RU_ITEM.rule_number%TYPE) IS
92 SELECT rowid
93 FROM IGS_RU_RULE
94 WHERE sequence_number = r_rule_number;
95
96 CURSOR Cur_Set_Mem_Del(r_set_number IGS_RU_ITEM.set_number%TYPE) IS
97 SELECT rowid
98 FROM IGS_RU_SET_MEMBER
99 WHERE rs_sequence_number = r_set_number;
100
101 CURSOR Cur_Set_Del(r_set_number IGS_RU_ITEM.set_number%TYPE) IS
102 SELECT rowid
103 FROM IGS_RU_SET
104 WHERE sequence_number = r_set_number;
105
106
107 BEGIN
108 FOR rule_items IN (
109 SELECT item,
110 rule_number,
111 set_number
112 FROM IGS_RU_ITEM
113 WHERE rul_sequence_number = p_rule_number
114 AND item >= p_item )
115 LOOP
116
117 for item_rec in Cur_Item_Del(rule_items.item) loop
118 IGS_RU_ITEM_PKG.DELETE_ROW(
119 X_ROWID => item_rec.rowid
120 );
121 end loop;
122
123
124 IF rule_items.rule_number IS NOT NULL
125 THEN
126 /*
127 remove all items of this IGS_RU_RULE
128 */
129 delete_rule_items(rule_items.rule_number,0);
130 /*
131 remove IGS_RU_RULE
132 */
133
134 for rule_rec in Cur_Rule_Del( rule_items.rule_number) loop
135 IGS_RU_RULE_PKG.DELETE_ROW(
136 X_ROWID => rule_rec.rowid
137 );
138 end loop;
139
140 ELSIF rule_items.set_number IS NOT NULL
141 THEN
142 /*
143 remove set members
144 */
145 for set_mem_rec in Cur_Set_Mem_Del( rule_items.set_number) loop
146 IGS_RU_SET_MEMBER_PKG.DELETE_ROW(
147 X_ROWID => set_mem_rec.rowid
148 );
149 end loop;
150 /*
151 remove set
152 */
153 for set_rec in Cur_Set_Del( rule_items.set_number) loop
154 IGS_RU_SET_PKG.DELETE_ROW(
155 X_ROWID => set_rec.rowid
156 );
157 end loop;
158 END IF;
159 END LOOP;
160 END delete_rule_items;
161
162 /*
163 rulp_del_rule
164 */
165 BEGIN
166 delete_rule_items(p_rule_number,0);
167 END;
168
169 Function Rulp_Del_Ur_Rule(
170 p_unit_cd IN VARCHAR2 ,
171 p_s_rule_call_cd IN VARCHAR2 ,
172 p_message_name OUT NOCOPY VARCHAR2 )
173 RETURN BOOLEAN IS
174 BEGIN
175 DECLARE
176 v_other_detail VARCHAR2(255);
177 v_ur_rec IGS_PS_UNIT_RU%ROWTYPE;
178 CURSOR c_unit_rule
179 (cp_unit_cd IGS_PS_UNIT_RU.unit_cd%TYPE,
180 cp_s_rule_call_cd IGS_PS_UNIT_RU.s_rule_call_cd%TYPE) IS
181 SELECT *
182 FROM IGS_PS_UNIT_RU ur
183 WHERE ur.unit_cd = cp_unit_cd AND
184 ur.s_rule_call_cd = cp_s_rule_call_cd;
185
186 CURSOR Cur_Unit_Ru_Del(r_unit_cd IGS_PS_UNIT_RU.unit_cd%TYPE,
187 r_s_rule_call_cd IGS_PS_UNIT_RU.s_rule_call_cd%TYPE
188 ) IS
189 SELECT rowid
190 FROM IGS_PS_UNIT_RU
191 WHERE unit_cd = r_unit_cd AND
192 s_rule_call_cd = r_s_rule_call_cd;
193
194 CURSOR Cur_Rule_Del(r_sequence_number IGS_PS_UNIT_RU.rul_sequence_number%TYPE) IS
195 SELECT rowid
196 FROM IGS_RU_RULE
197 WHERE sequence_number = r_sequence_number;
198
199 BEGIN
200 p_message_name := Null;
201 /*
202 Validate input parameters
203 */
204 IF p_unit_cd IS NULL OR
205 p_s_rule_call_cd IS NULL THEN
206 p_message_name := 'IGS_GE_INVALID_VALUE';
207 RETURN FALSE;
208 END IF;
209 /*
210 - This module deletes a IGS_PS_UNIT_RU and associated IGS_RU_RULE.
211 */
212 OPEN c_unit_rule(p_unit_cd,
213 p_s_rule_call_cd);
214
215
216 /*
217 check if a record has been found
218 if so, the record can be deleted.
219 */
220 IF (c_unit_rule%FOUND) THEN
221 /*
222 Delete IGS_PS_UNIT_RU
223 */
224
225 for unit_ru_rec in Cur_Unit_Ru_Del(v_ur_rec.unit_cd, v_ur_rec.s_rule_call_cd) loop
226 IGS_PS_UNIT_RU_PKG.DELETE_ROW(
227 X_ROWID => unit_ru_rec.rowid
228 );
229 end loop;
230
231 /*
232 Delete associated IGS_RU_RULE
233 */
234
235 for rule_rec in Cur_Rule_Del( v_ur_rec.rul_sequence_number) loop
236 IGS_RU_RULE_PKG.DELETE_ROW(
237 X_ROWID => rule_rec.rowid
238 );
239 end loop;
240
241 CLOSE c_unit_rule;
242 ELSE
243 CLOSE c_unit_rule;
244 END IF;
245 EXCEPTION
246 WHEN OTHERS THEN
247 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
248 Fnd_Message.Set_Token('Name','IGS_RU_GEN_003.rulp_del_ur_rule');
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END;
252 END rulp_del_ur_rule;
253
254 Function Rulp_Get_Ret_Type(
255 p_rud_sequence_number NUMBER )
256 RETURN VARCHAR2 IS
257 v_return_type IGS_RU_RET_TYPE.s_return_type%TYPE;
258 BEGIN
259 SELECT s_return_type
260 INTO v_return_type
261 FROM IGS_RU_DESCRIPTION
262 WHERE sequence_number = p_rud_sequence_number;
263 RETURN v_return_type;
264 END rulp_get_ret_type;
265
266 Function Rulp_Get_Rgi(
267 p_description_number NUMBER ,
268 p_description_type VARCHAR2 )
269 RETURN VARCHAR2 IS
270 /*
271 Expand description_number to IGS_RU_DESCRIPTION or group_name
272 if invalid return NULL
273 */
274 v_message_name VARCHAR2(30);
275 BEGIN
276 RETURN IGS_RU_GEN_004.rulp_val_desc_rgi(p_description_number,
277 p_description_type,
278 v_message_name);
279 END rulp_get_rgi;
280
281 FUNCTION Rulp_Get_Rule(
282 p_rule_number IN NUMBER )
283 RETURN VARCHAR2 IS
284 ------------------------------------------------------------------
285 --Created by : nsinha, Oracle India
286 --Date created: 12-Mar-2001
287 --
288 --Purpose: Get text version of IGS_RU_RULE
289 --
290 --Known limitations/enhancements and/or remarks:
291 --
292 --Change History:
293 --Who When What
294 --nsinha 12-Mar-2002 Bug# 2233951: Moved the content of this
295 -- function in Igs_ru_gen_006.rulp_get_rule
296 -- and called it from there.
297 --
298 -------------------------------------------------------------------
299 v_rule VARCHAR2(2000);
300 BEGIN
301 v_rule := Igs_ru_gen_006.rulp_get_rule(p_rule_number);
302 RETURN v_rule;
303 END rulp_get_rule;
304
305 Function Rulp_Ins_Copy_Rule(
306 p_rule_call_cd VARCHAR2 ,
307 p_rule_number IN NUMBER )
308 RETURN NUMBER IS
309 /*
310 Copy a derived IGS_RU_RULE to a new IGS_RU_RULE
311 RETURN IGS_RU_RULE number
312 on error raise exception
313 */
314 CURSOR c_get_rule_dtls (
315 cp_rule_call_cd IGS_RU_CALL.s_rule_call_cd%TYPE ) IS
316 SELECT select_group,
317 s_return_type
318 FROM IGS_RU_CALL,
319 IGS_RU_DESCRIPTION
320 WHERE s_rule_call_cd = p_rule_call_cd
321 AND sequence_number = rud_sequence_number;
322 v_rule VARCHAR2(2000);
323 v_select_group IGS_RU_CALL.select_group%TYPE;
324 v_return_type IGS_RU_RET_TYPE.s_return_type%TYPE;
325 v_unprocessed VARCHAR2(2000);
326 v_rule_number IGS_RU_RULE.sequence_number%TYPE;
327 v_lov_number IGS_RU_LOV.sequence_number%TYPE;
328 v_other_details VARCHAR2(255);
329 e_parser_error EXCEPTION;
330 BEGIN
331 /*
332 get text version of IGS_RU_RULE
333 */
334 v_rule := rulp_get_rule(p_rule_number);
335 /*
336 get select group and return type
337 */
338 OPEN c_get_rule_dtls( p_rule_call_cd);
339 FETCH c_get_rule_dtls INTO v_select_group, v_return_type;
340 CLOSE c_get_rule_dtls;
341 IF IGS_RU_GEN_002.rulp_ins_parser(v_select_group,
342 v_return_type,
343 '',
344 v_rule,
345 v_unprocessed,
346 TRUE,
347 v_rule_number,
348 v_lov_number) THEN
349 RETURN v_rule_number;
350 ELSE
351 RAISE e_parser_error;
352 END IF;
353 EXCEPTION
354 WHEN e_parser_error THEN
355 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 WHEN OTHERS THEN
359 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
360 FND_MESSAGE.SET_TOKEN('NAME','IGS_RU_GEN_003.rulp_ins_copy_rule');
361 IGS_GE_MSG_STACK.ADD;
362 App_Exception.Raise_Exception;
363 END rulp_ins_copy_rule;
364
365 FUNCTION rulp_clc_student_scope(
366 p_rule_number IN NUMBER,
367 p_unit_loc_cd IN VARCHAR2,
368 p_prg_type_level IN VARCHAR2,
369 p_org_code IN VARCHAR2,
370 p_unit_mode IN VARCHAR2,
371 p_unit_class IN VARCHAR2,
372 p_message OUT NOCOPY VARCHAR2 )
373 RETURN BOOLEAN IS
374 ------------------------------------------------------------------
375 --Created by : Bhaskar Annamalai, Oracle India
376 --Date created: 12-Jul-2005
377 --
378 --Purpose: To Evaluate the Selection Criteria Rule
379 --
380 --Known limitations/enhancements and/or remarks:
381 --
382 --Change History:
383 --Who When What
384 -------------------------------------------------------------------
385
386 l_v_ret_val VARCHAR2(100);
387
388 BEGIN
389 l_v_ret_val := igs_ru_gen_001.rulp_val_senna(
390 p_rule_number => p_rule_number,
391 p_param_1 => p_unit_loc_cd,
392 p_param_2 => p_prg_type_level,
393 p_param_3 => p_org_code,
394 p_param_4 => p_unit_mode,
395 p_param_5 => p_unit_class,
396 p_message => p_message );
397 IF (l_v_ret_val = 'true') THEN
398 return TRUE;
399 ELSE
400 return FALSE;
401 END IF;
402 END rulp_clc_student_scope;
403
404
405 END IGS_RU_GEN_003;