DBA Data[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;