1 PACKAGE BODY cz_pb_usg AS
2 /* $Header: czpbusgb.pls 120.2 2006/04/25 03:45:18 kdande ship $ */
3
4 FUNCTION INVERT_MAP(usage_map IN VARCHAR2)
5 RETURN VARCHAR2
6 IS LANGUAGE JAVA
7 NAME 'oracle.apps.cz.utilities.EffectivityUtilities.invertMap (java.lang.String)
8 return java.lang.String';
9
10 FUNCTION MAP_LESS_USAGE_ID(usage_id IN NUMBER, usage_map IN VARCHAR2 )
11 RETURN VARCHAR2
12 AS LANGUAGE JAVA NAME 'oracle.apps.cz.utilities.EffectivityUtilities.mapLessUsageId(int, java.lang.String)
13 return java.lang.String';
14
15 PROCEDURE REMOVE_USAGE_ID(usage_id IN NUMBER, usage_map IN OUT NOCOPY VARCHAR2) AS
16 BEGIN
17 usage_map := MAP_LESS_USAGE_ID(usage_id, usage_map);
18 END REMOVE_USAGE_ID;
19
20 FUNCTION MAP_PLUS_USAGE_ID(usage_id IN NUMBER, usage_map IN VARCHAR2 )
21 RETURN VARCHAR2
22 AS LANGUAGE JAVA NAME 'oracle.apps.cz.utilities.EffectivityUtilities.mapPlusUsageId(int, java.lang.String)
23 return java.lang.String';
24
25 PROCEDURE ADD_USAGE_ID(usage_id IN NUMBER, usage_map IN OUT NOCOPY VARCHAR2)
26 AS
27 BEGIN
28 usage_map := MAP_PLUS_USAGE_ID(usage_id, usage_map);
29 END ADD_USAGE_ID;
30
31 FUNCTION MAP_HAS_USAGE_ID(usage_id IN NUMBER, usage_map IN VARCHAR2)
32 RETURN NUMBER
33 AS LANGUAGE JAVA NAME 'oracle.apps.cz.utilities.EffectivityUtilities.mapHasUsageId(int, java.lang.String)
34 return int';
35
36 FUNCTION MAP_HAS_USAGE_NAME(usage_name IN VARCHAR2,usage_map IN VARCHAR2)
37 RETURN NUMBER
38 IS
39 usageId NUMBER;
40 answer NUMBER;
41 BEGIN
42 SELECT model_usage_id INTO usageId
43 FROM cz_model_usages
44 WHERE name = usage_name;
45 answer := MAP_HAS_USAGE_ID(usageId, usage_map);
46 RETURN answer;
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 RETURN -1; -- The named usage dosen't exist
50 END MAP_HAS_USAGE_NAME;
51
52 FUNCTION MAP_LESS_USAGE_NAME(usage_name IN VARCHAR2,usage_map IN VARCHAR2)
53 RETURN VARCHAR2
54 AS
55 usageId NUMBER;
56 outMap VARCHAR2(16);
57 BEGIN
58 SELECT model_usage_id INTO usageId
59 FROM cz_model_usages
60 WHERE name = usage_name;
61 outMap := MAP_LESS_USAGE_ID(usageId, usage_map);
62 RETURN outMap;
63 EXCEPTION
64 -- Return '-1' if the named usage dosen't exist
65 WHEN NO_DATA_FOUND THEN RETURN '-1';
66 END MAP_LESS_USAGE_NAME;
67
68
69 PROCEDURE REMOVE_USAGE_NAME(usage_name IN VARCHAR2,usage_map IN OUT NOCOPY VARCHAR2)
70 AS
71 tmp_map VARCHAR2(16);
72 BEGIN
73 tmp_map := MAP_LESS_USAGE_NAME(usage_name, usage_map);
74 IF tmp_map <> '-1' THEN
75 usage_map := tmp_map;
76 ELSE
77 RAISE_APPLICATION_ERROR (-20001,
78 'Usage does not exist');
79 END IF;
80 END REMOVE_USAGE_NAME;
81
82
83 FUNCTION MAP_PLUS_USAGE_NAME(usage_name IN VARCHAR2,usage_map IN VARCHAR2)
84 RETURN VARCHAR2
85 AS
86 usageId NUMBER;
87 outMap VARCHAR2(16);
88 BEGIN
89 SELECT model_usage_id INTO usageId
90 FROM cz_model_usages
91 WHERE name = usage_name;
92 outMap := MAP_PLUS_USAGE_ID(usageId, usage_map);
93 RETURN outMap;
94 EXCEPTION
95 -- Return '-1' if the named usage dosen't exist
96 WHEN NO_DATA_FOUND THEN RETURN '-1';
97 END MAP_PLUS_USAGE_NAME;
98
99
100 PROCEDURE ADD_USAGE_BY_NAME(usage_name IN VARCHAR2,
101 usage_map IN OUT NOCOPY VARCHAR2
102 ) AS
103 tmp_map VARCHAR2(16);
104 BEGIN
105 tmp_map := MAP_PLUS_USAGE_NAME(usage_name, usage_map);
106 IF tmp_map <> '-1' THEN
107 usage_map := tmp_map;
108 ELSE
109 RAISE_APPLICATION_ERROR (-20001,
110 'Usage does not exist');
111 END IF;
112 END ADD_USAGE_BY_NAME;
113
114
115 FUNCTION LIST_USAGES_IN_MAP_STRING(usage_map IN VARCHAR2)
116 RETURN VARCHAR2
117 AS LANGUAGE JAVA
118 NAME 'oracle.apps.cz.utilities.EffectivityUtilities.listUsagesInMap (java.lang.String)
119 return java.lang.String';
120
121
122 FUNCTION LIST_USAGES_IN_MAP(usage_map IN VARCHAR2)
123 RETURN USAGE_NAME_LIST
124 AS
125 usages_list USAGE_NAME_LIST;
126 usages_string VARCHAR2(16400);
127 v_index NUMBER :=1;
128 BEGIN
129 usages_string := LIST_USAGES_IN_MAP_STRING(usage_map);
130 WHILE length(usages_string) >0 LOOP
131 usages_list(v_index) := SUBSTR(usages_string, 1, INSTR(usages_string, '|') - 1);
132 usages_string := SUBSTR(usages_string, INSTR(usages_string, '|') + 1);
133 v_index := v_index+1;
134 END LOOP;
135 RETURN usages_list;
136 END LIST_USAGES_IN_MAP;
137 -----------
138 PROCEDURE DELETE_USAGE(usageId IN NUMBER,
139 delete_status IN OUT NOCOPY VARCHAR2)
140 AS
141
142
143 TYPE t_indexes IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
144 TYPE t_mask IS TABLE OF cz_ps_nodes.effective_usage_mask%TYPE INDEX BY BINARY_INTEGER;
145
146 v_nodes t_indexes;
147 v_rules t_indexes;
148 v_masks_nodes t_mask;
149 v_masks_rules t_mask;
150
151
152 v_new_mask VARCHAR2(16);
153 v_last_old_mask VARCHAR2(16);
154 v_first_index BINARY_INTEGER;
155
156 BEGIN
157 v_nodes.DELETE;
158 v_rules.DELETE;
159 v_masks_nodes.DELETE;
160 v_masks_rules.DELETE;
161
162
163 BEGIN
164 SELECT ps_node_id,
165 effective_usage_mask
166 BULK
167 COLLECT
168 INTO v_nodes,
169 v_masks_nodes
170 FROM cz_ps_nodes
171 WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
172 ORDER BY effective_usage_mask;
173 EXCEPTION
174 WHEN OTHERS THEN
175 NULL;
176 END;
177
178 BEGIN
179 SELECT rule_id,
180 effective_usage_mask
181 BULK
182 COLLECT
183 INTO v_rules,
184 v_masks_rules
185 FROM cz_rules
186 WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
187 ORDER BY effective_usage_mask;
188 EXCEPTION
189 WHEN OTHERS THEN
190 NULL;
191 END;
192
193
194 BEGIN
195 UPDATE cz_model_usages
196 SET in_use = 'X'
197 WHERE model_usage_id = usageId;
198
199 DELETE FROM cz_publication_usages
200 WHERE usage_id = usageId;
201
202 DELETE FROM cz_rp_entries
203 WHERE object_type ='USG' and object_id = usageId;
204
205 IF (v_nodes.COUNT > 0) THEN
206 v_first_index := v_masks_nodes.FIRST;
207 v_last_old_mask := v_masks_nodes(v_first_index);
208 v_new_mask := MAP_LESS_USAGE_ID(usageId, v_masks_nodes(v_first_index));
209 v_masks_nodes(v_first_index) := v_new_mask;
210
211 FOR i IN v_masks_nodes.NEXT(v_first_index)..v_masks_nodes.LAST
212 LOOP
213 IF v_masks_nodes(i) = v_last_old_mask THEN
214 v_masks_nodes(i) := v_masks_nodes(i-1);
215 ELSE
216 v_last_old_mask := v_masks_nodes(i);
217
218 v_new_mask := MAP_LESS_USAGE_ID(usageId, v_masks_nodes(i));
219 v_masks_nodes(i) := v_new_mask;
220 END IF;
221 END LOOP;
222
223 FORALL i IN v_nodes.FIRST..v_nodes.LAST
224 UPDATE cz_ps_nodes
225 SET effective_usage_mask = v_masks_nodes(i)
226 WHERE ps_node_id = v_nodes(i);
227
228 END IF;
229
230 IF (v_rules.COUNT > 0) THEN
231 v_first_index := v_masks_rules.FIRST;
232 v_last_old_mask := v_masks_rules(v_first_index);
233 v_new_mask := MAP_LESS_USAGE_ID(usageId, v_masks_rules(v_first_index));
234 v_masks_rules(v_first_index) := v_new_mask;
235
236 FOR i IN v_masks_rules.NEXT(v_first_index)..v_masks_rules.LAST
237 LOOP
238 IF v_masks_rules(i) = v_last_old_mask THEN
239 v_masks_rules(i) := v_masks_rules(i-1);
240 ELSE
241 v_last_old_mask := v_masks_rules(i);
242
243 v_new_mask := MAP_LESS_USAGE_ID(usageId, v_masks_rules(i));
244 v_masks_rules(i) := v_new_mask;
245 END IF;
246 END LOOP;
247
248 FORALL i IN v_rules.FIRST..v_rules.LAST
249 UPDATE cz_rules
250 SET effective_usage_mask = v_masks_rules(i)
251 WHERE rule_id = v_rules(i);
252 END IF;
253 EXCEPTION
254 WHEN OTHERS THEN
255 delete_status := '-1';
256 END;
257
258 IF SQLCODE = 0 THEN
259 delete_status := '0';
260 END IF;
261
262
263 END DELETE_USAGE;
264
265 END;
[Home] [Help]