[Home] [Help]
PACKAGE BODY: APPS.AZW_GROUP
Source
1 PACKAGE BODY AZW_GROUP AS
2 /* $Header: AZWPGRPB.pls 115.13 1999/11/01 17:00:17 pkm ship $: */
3
4
5 TYPE message_tbl_t IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER;
6
7 msg_delimiter VARCHAR2(1) := '^';
8
9 TYPE dependency_rec_t IS RECORD (
10 node_id VARCHAR2(60),
11 display_name VARCHAR2(240),
12 parent_node_id VARCHAR2(60),
13 node_type VARCHAR2(1),
14 status VARCHAR2(20),
15 installed_flag VARCHAR2(1),
16 group_color VARCHAR2(15),
17 display_order NUMBER(5));
18
19 TYPE dependency_tbl_t IS TABLE OF dependency_rec_t INDEX BY BINARY_INTEGER;
20
21 g_current_mode az_processes.process_type%TYPE DEFAULT 'IMP';
22 --
23 -- get_installed_flag
24 --
25 -- Private function. Called by get_group_dependency.
26 -- Given an application id, find the corresponding installed flag.
27 --
28
29 FUNCTION contains_processes ( node_id IN VARCHAR2 ) RETURN BOOLEAN IS
30 CURSOR sub_group_c IS
31 SELECT group_id
32 FROM az_groups
33 WHERE
34 hierarchy_parent_id = node_id
35 AND process_type = g_current_mode ;
36
37 ret_val BOOLEAN DEFAULT TRUE;
38 v_node_id az_groups.group_id%TYPE;
39 child_count INTEGER DEFAULT 0;
40 BEGIN
41
42 BEGIN
43 SELECT count(*)
44 INTO child_count
45 FROM az_processes azp
46 WHERE azp.parent_id = node_id
47 AND azp.process_type = g_current_mode ;
48 EXCEPTION
49 WHEN app_exception.application_exception THEN
50 RAISE;
51 WHEN OTHERS THEN
52 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
53 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
54 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
55 fnd_message.set_token('AZW_ERROR_PROC','azw_group.contains_processes');
56 fnd_message.set_token('AZW_ERROR_STMT','select count(*) into child_count from az_processes');
57 APP_EXCEPTION.RAISE_EXCEPTION;
58 END;
59
60 --dbms_output.put_line('Here: '||child_count|| ' MODE: ' ||g_current_mode || ' P:'|| node_id);
61 IF child_count > 0 THEN
62 ret_val := TRUE;
63 ELSE
64 ret_val := FALSE; -- this takes care of 'if cursor not found '
65 BEGIN
66 OPEN sub_group_c ;
67 FETCH sub_group_c INTO v_node_id ;
68 WHILE sub_group_c%FOUND LOOP
69 ret_val := contains_processes( v_node_id );
70 IF ret_val THEN
71 GOTO endloop;
72 END IF;
73 FETCH sub_group_c INTO v_node_id ;
74 END LOOP;
75 <<endloop>>
76 CLOSE sub_group_c;
77 EXCEPTION
78 WHEN app_exception.application_exception THEN
79 RAISE;
80 WHEN OTHERS THEN
81 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
82 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
83 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
84 fnd_message.set_token('AZW_ERROR_PROC','azw_group.contains_processes');
85 fnd_message.set_token('AZW_ERROR_STMT','CURSOR sub_group_c');
86 APP_EXCEPTION.RAISE_EXCEPTION;
87 END;
88 END IF;
89
90 RETURN ret_val;
91
92 EXCEPTION
93 WHEN app_exception.application_exception THEN
94 RAISE;
95 WHEN OTHERS THEN
96 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
97 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
98 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
99 fnd_message.set_token('AZW_ERROR_PROC','azw_group.contains_processes');
100 fnd_message.set_token('AZW_ERROR_STMT','CURSOR sub_group_c');
101 APP_EXCEPTION.RAISE_EXCEPTION;
102 END contains_processes;
103
104 FUNCTION get_installed_flag(appl_id NUMBER) RETURN VARCHAR2 IS
105 v_installed_flag VARCHAR2(1) DEFAULT 'A';
106 v_count_installed NUMBER(5) DEFAULT 0;
107 v_appl_id NUMBER;
108 BEGIN
109
110 -- dbms_output.put_line('*** get installed flag');
111 -- dbms_output.put_line('appl_id = ' || appl_id);
112
113 -- code put to handle CST as CST has no product installation
114 -- should be taken out for R12
115
116 IF (appl_id = 707) THEN
117 v_appl_id := 702;
118 ELSE
119 v_appl_id := appl_id;
120 END IF;
121
122 --- end of hard coding for CST
123
124 SELECT count(*)
125 INTO v_count_installed
126 FROM fnd_product_installations fpi, fnd_application_vl fav
127 WHERE fpi.application_id = fav.application_id
128 AND fav.application_id = v_appl_id
129 AND fpi.status = 'I';
130 IF (v_count_installed = 0) THEN
131 v_installed_flag := 'N';
132 ELSE
133 v_installed_flag := 'I';
134 END IF;
135
136 RETURN v_installed_flag;
137
138 EXCEPTION
139 WHEN app_exception.application_exception THEN
140 RAISE;
141 WHEN NO_DATA_FOUND THEN
142 v_installed_flag := 'A';
143 RETURN v_installed_flag;
144 WHEN OTHERS THEN
145 -- DBMS_OUTPUT.PUT_LINE('error: get_installed_flag: ' || SQLERRM);
146 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
147 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
148 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
149 fnd_message.set_token('AZW_ERROR_PROC','azw_group.get_installed_flag');
150 fnd_message.set_token('AZW_ERROR_STMT','select status from fnd_product_installations,fnd_application_vl');
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END get_installed_flag;
153
154
155 --
156 -- get_installed_flag_group
157 --
158 -- Private function. Called by get_group_dependency.
159 -- Given a group id, find the corresponding installed flag.
160 --
161
162 FUNCTION get_installed_flag_group(p_group_id VARCHAR2) RETURN VARCHAR2 IS
163
164 v_installed_flag VARCHAR2(1) DEFAULT 'I';
165 v_count_installed NUMBER(5) DEFAULT 0;
166 BEGIN
167
168 SELECT count(*)
169 INTO v_count_installed
170 FROM az_groups azg, fnd_product_installations fpi
171 WHERE fpi.application_id = azg.application_id
172 AND fpi.status = 'I'
173 AND azg.dependency_parent_id = p_group_id
174 AND process_type = g_current_mode;
175
176 IF (v_count_installed = 0) THEN
177 v_installed_flag := 'N';
178 ELSE
179 v_installed_flag := 'I';
180 END IF;
181
182 RETURN v_installed_flag;
183
184 EXCEPTION
185 WHEN app_exception.application_exception THEN
186 RAISE;
187 WHEN NO_DATA_FOUND THEN
188 v_installed_flag := 'A';
189 RETURN v_installed_flag;
190 WHEN OTHERS THEN
191 --DBMS_OUTPUT.PUT_LINE('error: get_installed_flag: ' || SQLERRM);
192 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
193 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
194 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
195 fnd_message.set_token('AZW_ERROR_PROC','azw_group.get_process');
196 fnd_message.set_token('AZW_ERROR_STMT','CURSOR process_hierarchy_pn');
197 APP_EXCEPTION.RAISE_EXCEPTION;
198 END get_installed_flag_group;
199
200
201 --
202 -- get_group_dependency
203 --
204 -- Private procedure. Called by get_dependency.
205 --
206
207 PROCEDURE get_group_dependency (gd OUT dependency_tbl_t) IS
208
209 v_node_id az_groups.group_id%TYPE;
210 v_parent_node_id az_groups.dependency_parent_id%TYPE;
211 v_display_order az_groups.display_order%TYPE;
212 v_application_id az_groups.application_id%TYPE;
213 v_lookup_code az_groups.lookup_code%TYPE;
214 v_color_code az_groups.color_code%TYPE;
215 v_complete_flag az_groups.complete_flag%TYPE;
216 i BINARY_INTEGER DEFAULT 0;
217
218
219 CURSOR dependency_cursor IS
220 SELECT group_id, dependency_parent_id, display_order,
221 application_id, lookup_code, color_code, complete_flag
222 FROM az_groups
223 WHERE hierarchy_parent_id is null
224 AND process_type = g_current_mode
225 ORDER BY 1;
226
227 BEGIN
228
229 OPEN dependency_cursor;
230 FETCH dependency_cursor INTO v_node_id, v_parent_node_id, v_display_order,
231 v_application_id, v_lookup_code, v_color_code,
232 v_complete_flag;
233
234 WHILE dependency_cursor%FOUND LOOP
235
236 i := i + 1;
237
238 IF (v_application_id is not null) THEN
239 gd(i).display_name := AZW_PROC.get_application_name(v_application_id);
240 gd(i).installed_flag := get_installed_flag(v_application_id);
241 ELSE
242 gd(i).display_name := AZW_PROC.get_lookup_meaning(v_lookup_code);
243
244 IF (v_parent_node_id is not null) THEN
245 gd(i).installed_flag := get_installed_flag_group(v_node_id);
246 ELSE
247 gd(i).installed_flag := 'I';
248 END IF;
249 END IF;
250
251 -- Start : Swarup added
252 IF contains_processes( v_node_id ) THEN
253 NULL;
254 ELSE
255 gd(i).installed_flag := 'U';
256 END IF ;
257 -- End : Swarup added
258
259 gd(i).node_id := v_node_id;
260 IF v_node_id = '1010' THEN
261 gd(i).parent_node_id := 'root';
262 ELSE
263 gd(i).parent_node_id := v_parent_node_id;
264 END IF;
265 gd(i).node_type := 'G';
266
267 IF v_complete_flag = 'Y' THEN
268 gd(i).status := 'COMPLETED';
269 ELSE
270 gd(i).status := 'INCOMPLETE';
271 END IF;
272
273 gd(i).group_color := v_color_code;
274 IF((gd(i).installed_flag <> 'I') AND (v_application_id is not null)) THEN
275 gd(i).display_order := v_display_order + 10000;
276 ELSE
277 gd(i).display_order := v_display_order;
278 END IF;
279
280 FETCH dependency_cursor INTO v_node_id, v_parent_node_id, v_display_order,
281 v_application_id, v_lookup_code,v_color_code,
282 v_complete_flag;
283 END LOOP;
284 CLOSE dependency_cursor;
285
286 EXCEPTION
287 WHEN app_exception.application_exception THEN
288 RAISE;
289 WHEN OTHERS THEN
290 -- DBMS_OUTPUT.PUT_LINE('error: get_group_dependency: ' || SQLERRM);
291 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
292 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
293 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
294 fnd_message.set_token('AZW_ERROR_PROC','azw_group.get_group_dependency');
295 fnd_message.set_token('AZW_ERROR_STMT','CURSOR dependency_cursor');
296 APP_EXCEPTION.RAISE_EXCEPTION;
297 END get_group_dependency;
298
299
300 --
301 -- GET_DEPENDENCY
302 --
303 -- Public procedure. Called by Process Groups window.
304 -- The message format which has been agreed upon between front end
305 -- and back end is as follows:
306 --
307 -- display_name^node_id^parent_node_id^node_type^installed_flag^
308 -- status^display_order^^
309 --
310
311 PROCEDURE get_dependency IS
312
313 gd dependency_tbl_t;
314 mesg az_webform_messages.mesg%TYPE;
315
316 BEGIN
317
318 -- dbms_output.put_line('get dependency');
319
320 g_current_mode := fnd_profile.value('AZ_CURRENT_MODE');
321
322 get_group_dependency(gd);
323
324 FOR j IN 1..gd.COUNT LOOP
325
326 IF gd(j).display_name <> 'NONE' THEN
327
328 BEGIN
329 mesg := gd(j).display_name || msg_delimiter ||
330 gd(j).node_id || msg_delimiter ||
331 gd(j).parent_node_id || msg_delimiter ||
332 gd(j).node_type || msg_delimiter ||
333 gd(j).installed_flag || msg_delimiter ||
334 gd(j).status || msg_delimiter ||
335 gd(j).display_order || msg_delimiter ||
336 gd(j).group_color || msg_delimiter || msg_delimiter;
337
338 INSERT INTO az_webform_messages (mesg)
339 VALUES (mesg);
340 EXCEPTION
341 WHEN app_exception.application_exception THEN
342 RAISE;
343 WHEN OTHERS THEN
344 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
345 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
346 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
347 fnd_message.set_token('AZW_ERROR_PROC','azw_group.get_dependency');
348 fnd_message.set_token('AZW_ERROR_STMT','insert into az_webform_messages');
349 APP_EXCEPTION.RAISE_EXCEPTION;
350 END;
351
352 END IF;
353 END LOOP;
354
355 EXCEPTION
356 WHEN app_exception.application_exception THEN
357 APP_EXCEPTION.RAISE_EXCEPTION;
358 WHEN OTHERS THEN
359 --DBMS_OUTPUT.PUT_LINE('error: get_dependency: ' || SQLERRM);
360 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
361 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
362 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
363 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_dependency');
364 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
365 APP_EXCEPTION.RAISE_EXCEPTION;
366 END get_dependency;
367
368 END AZW_GROUP;