1 PACKAGE BODY IGS_RU_VAL_RGI AS
2 /* $Header: IGSRU07B.pls 120.1 2005/09/16 06:17:58 appldev ship $ */
3
4 gv_rug_sequence_number NUMBER(6,0);
5 gv_description_number NUMBER(6,0);
6 gv_description_type VARCHAR2(10);
7
8 /*
9 Populate IGS_RU_GROUP_SET from IGS_RU_GROUP_ITEM
10 */
11 PROCEDURE rulp_ins_rgi
12 IS
13 v_rowid_gs1 VARCHAR2(25);
14 v_rowid_gs2 VARCHAR2(25);
15
16 CURSOR Cur_Gs_Del(r_rug_sequence_number gv_rug_sequence_number%TYPE) IS
17 SELECT rowid
18 FROM IGS_RU_GROUP_SET
19 WHERE rug_sequence_number = r_rug_sequence_number
20 FOR UPDATE;
21
22 CURSOR Cur_Gi(r_rug_sequence_number gv_rug_sequence_number%TYPE) IS
23 SELECT rug_sequence_number, description_number
24 FROM IGS_RU_GROUP_ITEM
25 WHERE rug_sequence_number = r_rug_sequence_number
26 AND description_type = 'RUD';
27
28 /*
29 for the marked group
30 insert into IGS_RU_GROUP_SET all RUD's and expanded RUG's
31 cause parent groups to be expanded
32 NOTE this will cascade to all IGS_RU_RULE group ancestors
33 */
34 BEGIN
35
36 /*
37 delete all members of IGS_RU_GROUP_SET
38 */
39
40 for Gs_rec in Cur_Gs_Del(gv_rug_sequence_number) loop
41 IGS_RU_GROUP_SET_PKG.DELETE_ROW(
42 X_ROWID => Gs_rec.rowid);
43 end loop;
44 /*
45 insert all items of type 'RUD'
46 */
47
48 for Gi_rec in Cur_Gi(gv_rug_sequence_number) loop
49 IGS_RU_GROUP_SET_PKG.Insert_Row(
50 x_rowid => v_rowid_gs1,
51 x_rug_sequence_number => Gi_rec.rug_sequence_number,
52 x_rud_sequence_number => Gi_rec.description_number,
53 x_mode => 'R'
54 );
55 end loop;
56
57 /*
58 get all items of type 'RUG'
59 */
60 FOR rgi IN (
61 SELECT description_number
62 FROM IGS_RU_GROUP_ITEM
63 WHERE rug_sequence_number = gv_rug_sequence_number
64 AND description_type = 'RUG' )
65 LOOP
66 /*
67 insert all members of this group
68 */
69 FOR rgs IN (
70 SELECT rud_sequence_number
71 FROM IGS_RU_GROUP_SET
72 WHERE rug_sequence_number = rgi.description_number )
73 LOOP
74 BEGIN
75 IGS_RU_GROUP_SET_PKG.Insert_Row(
76 x_rowid => v_rowid_gs2,
77 x_rug_sequence_number => gv_rug_sequence_number,
78 x_rud_sequence_number => rgs.rud_sequence_number,
79 x_mode => 'R'
80 );
81
82 EXCEPTION
83 WHEN DUP_VAL_ON_INDEX THEN
84 NULL;
85 END;
86 END LOOP;
87 END LOOP;
88
89
90
91 /*
92 force trigger on groups containing the current updated group
93 */
94 FOR rgi IN (
95 SELECT ROWID, srgi.*
96 FROM IGS_RU_GROUP_ITEM srgi
97 WHERE description_number = gv_rug_sequence_number
98 AND description_type = 'RUG' )
99 LOOP
100 /*
101 IGS_GE_NOTE: gv_rug_sequence_number is volatile, changed by this update
102 update these records one at a time
103 */
104
105 IGS_RU_GROUP_ITEM_PKG.UPDATE_ROW (
106 X_ROWID => RGI.ROWID,
107 X_RUG_SEQUENCE_NUMBER => RGI.RUG_SEQUENCE_NUMBER,
108 X_DESCRIPTION_NUMBER => RGI.DESCRIPTION_NUMBER,
109 X_DESCRIPTION_TYPE => RGI.DESCRIPTION_TYPE );
110
111 END LOOP;
112 END rulp_ins_rgi;
113
114
115 /*
116 To set gv_group_number
117 */
118 PROCEDURE rulp_set_rgi(
119 P_RUG_SEQUENCE_NUMBER NUMBER ,
120 P_DESCRIPTION_NUMBER NUMBER ,
121 P_DESCRIPTION_TYPE VARCHAR2 )
122 IS
123 BEGIN
124 gv_rug_sequence_number := p_rug_sequence_number;
125 gv_description_number := p_description_number;
126 gv_description_type := p_description_type;
127 END rulp_set_rgi;
128 /*
129 To verify if the insert group can be inserted into the current group.
130 */
131 FUNCTION rulp_val_grp_rgi
132 RETURN BOOLEAN IS
133 /*
134 validate if the insert group can be inserted into the current group
135 can not insert self
136 can not insert ancestor
137 */
138 FUNCTION validate_insert_group (
139 p_current_group NUMBER,
140 p_insert_group NUMBER,
141 p_description_type VARCHAR2 )
142 RETURN BOOLEAN IS
143 BEGIN
144 IF p_description_type = 'RUD'
145 THEN
146 RETURN TRUE;
147 END IF;
148 IF p_current_group = p_insert_group
149 THEN
150 /*
151 can not insert self
152 */
153 RETURN FALSE;
154 END IF;
155 /*
156 for all parent groups of current group
157 */
158 FOR parent IN (
159 SELECT rug_sequence_number
160 FROM IGS_RU_GROUP_ITEM
161 WHERE description_number = p_current_group
162 AND description_type = 'RUG' )
163 LOOP
164 /*
165 try ancestor
166 */
167 IF validate_insert_group(parent.rug_sequence_number,
168 p_insert_group,
169 'RUG') = FALSE
170 THEN
171 /*
172 can not insert ancestor
173 */
174 RETURN FALSE;
175 END IF;
176 END LOOP;
177 /*
178 not false therefore true
179 */
180 RETURN TRUE;
181 END validate_insert_group;
182 /*
183 rulp_val_grp_rgi
184 */
185 BEGIN
186 RETURN validate_insert_group(gv_rug_sequence_number,
187 gv_description_number,
188 gv_description_type);
189 END rulp_val_grp_rgi;
190
191 END IGS_RU_VAL_RGI;
[Home] [Help]