[Home] [Help]
PACKAGE BODY: APPS.AZW_HIER
Source
1 PACKAGE BODY AZW_HIER AS
2 /* $Header: AZWHIERB.pls 115.51 1999/11/09 12:52:57 pkm ship $: */
3
4 TYPE message_tbl_t IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER;
5
6 /* CAREFUL !!!! Do not chage the sizes of hierarchy_rec_t fields */
7
8 TYPE hierarchy_rec_t IS RECORD (
9 node_id VARCHAR2(300),
10 display_name VARCHAR2(415),
11 parent_node_id VARCHAR2(300),
12 node_type VARCHAR2(1),
13 status VARCHAR2(30),
14 context_name az_processes.context_name%TYPE,
15 context_type az_processes.context_type%TYPE,
16 display_order az_processes.display_order%TYPE );
17
18 TYPE hierarchy_tbl_t IS TABLE OF hierarchy_rec_t INDEX BY BINARY_INTEGER;
19
20 hierarchy_table hierarchy_tbl_t;
21 t_index BINARY_INTEGER DEFAULT 0;
22
23 -- Start: added by swarup for context sort
24
25 ctx_table AZW_PROC.context_tbl_t;
26
27 PROCEDURE insert_context( ctx_type IN VARCHAR2,
28 meaning IN VARCHAR2,
29 msg IN OUT message_tbl_t,
30 i IN OUT INTEGER,
31 p_disp_order IN INTEGER);
32
33 PROCEDURE insert_groups_for_context( ctx_type IN VARCHAR2,
34 msg IN OUT message_tbl_t,
35 i IN OUT INTEGER);
36
37 PROCEDURE insert_proc_task_for_context( msg IN OUT message_tbl_t,
38 i IN OUT INTEGER );
39
40 PROCEDURE get_context( ctx_type IN VARCHAR2,
41 ctx_table OUT AZW_PROC.context_tbl_t);
42
43 -- End: added by swarup for context sort
44
45 g_current_mode az_processes.process_type%TYPE ;
46
47 msg_delimiter VARCHAR2(1) := '^';
48 v_language_code fnd_languages.language_code%TYPE DEFAULT NULL;
49 v_language fnd_languages.nls_language%TYPE DEFAULT NULL;
50 v_days varchar2(8) DEFAULT NULL;
51 v_skip varchar2(8) DEFAULT NULL;
52 v_done varchar2(8) DEFAULT NULL;
53 v_priority_display varchar2(16) DEFAULT NULL;
54
55 -- group_hierarchy_tree_not_found
56 --
57 -- Private function. Called by get_group_hierarchy.
58 -- Given a group, return TRUE if there is no hierarchy under it.
59 --
60
61 FUNCTION group_hierarchy_tree_not_found(p_group_id VARCHAR2) RETURN BOOLEAN
62 IS
63 ret BOOLEAN DEFAULT FALSE;
64 v_cnt INTEGER DEFAULT 0;
65
66 BEGIN
67
68 SELECT COUNT(*)
69 INTO v_cnt
70 FROM az_groups
71 WHERE hierarchy_parent_id = p_group_id
72 AND process_type = g_current_mode;
73
74 IF (v_cnt > 0) THEN
75 ret := FALSE;
76 ELSE
77 ret := TRUE;
78 END IF;
79
80 RETURN ret;
81
82 EXCEPTION
83 WHEN app_exception.application_exception THEN
84 RAISE ;
85 WHEN OTHERS THEN
86 --DBMS_OUTPUT.PUT_LINE('error: group_hierarchy_tree_not_found: ' || SQLERRM);
87 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
88 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
89 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
90 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.group_hierarchy_tree_not_found');
91 fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_groups');
92 APP_EXCEPTION.RAISE_EXCEPTION;
93 END group_hierarchy_tree_not_found;
94
95
96 --
97 -- get_leaf_nodes
98 --
99 -- Private procedure. Called by get_group_hierarchy.
100 -- Retrieves all the leaf group nodes for a root process group
101 --
102
103 PROCEDURE get_leaf_nodes(p_process_group IN VARCHAR2) IS
104
105 v_group_id az_groups.group_id%TYPE DEFAULT NULL;
106
107 CURSOR leaf_node_cursor IS
108 SELECT group_id
109 FROM az_groups
110 WHERE hierarchy_parent_id is not null
111 START WITH group_id = p_process_group
112 AND process_type = g_current_mode
113 CONNECT BY PRIOR group_id = hierarchy_parent_id
114 AND process_type = g_current_mode
115 ORDER BY group_id;
116
117 BEGIN
118
119 -- DBMS_OUTPUT.PUT_LINE('leaf nodes: get_leaf_nodes: ' );
120 OPEN leaf_node_cursor;
121 FETCH leaf_node_cursor INTO v_group_id;
122
123 WHILE leaf_node_cursor%FOUND LOOP
124
125 BEGIN
126 IF group_hierarchy_tree_not_found(v_group_id) THEN
127 INSERT INTO az_webform_messages (mesg)
128 VALUES (v_group_id);
129
130 END IF;
131
132 EXCEPTION
133 WHEN app_exception.application_exception THEN
134 RAISE ;
135 WHEN OTHERS THEN
136 --DBMS_OUTPUT.PUT_LINE('error: get_leaf_nodes: ' || SQLERRM);
137 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
138 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
139 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
140 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_leaf_nodes');
141 fnd_message.set_token('AZW_ERROR_STMT','insert into az_webform_messages');
142 APP_EXCEPTION.RAISE_EXCEPTION;
143 END;
144
145 FETCH leaf_node_cursor INTO v_group_id;
146
147 END LOOP;
148 CLOSE leaf_node_cursor;
149
150 EXCEPTION
151 WHEN app_exception.application_exception THEN
152 RAISE ;
153 WHEN OTHERS THEN
154 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
155 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
156 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
157 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_leaf_nodes');
158 fnd_message.set_token('AZW_ERROR_STMT','CURSOR leaf_node_cursor');
159 APP_EXCEPTION.RAISE_EXCEPTION;
160 END get_leaf_nodes;
161
162 --
163 -- get_process
164 --
165 -- Private procedure. Called by get_group_hierarchy.
166 -- Retrieve all the processes for a given group satisfying the search
167 -- criteria.
168 --
169
170 PROCEDURE get_process(rollup_flag OUT VARCHAR2,
171 p_process_parent_id IN VARCHAR2,
172 process_status IN VARCHAR2,
173 process_phase IN NUMBER,
174 assigned_user IN VARCHAR2,
175 task_status IN VARCHAR2,
176 task_total_days IN NUMBER,
177 task_total_days_op IN VARCHAR2,
178 sort_by IN VARCHAR2)
179 IS
180 display_id NUMBER(5) DEFAULT 0;
181 v_group_rollup VARCHAR2(1);
182 v_process_rollup VARCHAR2(1);
183 p_assigned_user VARCHAR2(30);
184 p_task_status VARCHAR2(10);
185 p_sort_by VARCHAR2(30);
186 label VARCHAR2(400);
187 -- Should be >= hierarchy_table.display_name
188 p_process_status VARCHAR2(30);
189 p_process_phase NUMBER(3);
190
191 CURSOR process_hierarchy_ph IS
192 SELECT DISTINCT azp.item_type,
193 azp.process_name,
194 azp.context_id,
195 azp.display_order,
196 azp.status_code,
197 azp.context_type,
198 azp.context_name,
199 azp.comments,
200 azp.parent_id,
201 azfpv.phase,
202 wav.display_name
203 FROM az_processes azp,
204 az_flow_phases_v azfpv,
205 wf_activities_vl wav
206 WHERE azfpv.item_type = azp.item_type
207 AND azfpv.process_name = azp.process_name
208 AND azp.parent_id = p_process_parent_id
209 AND azp.process_type = g_current_mode
210 AND wav.item_type = azp.item_type
211 AND wav.name = azp.process_name
212 AND wav.end_date is NULL
213 ORDER BY 4, 6, 7;
214
215 CURSOR process_hierarchy_pn_all IS
216 SELECT DISTINCT azp.item_type,
217 azp.process_name,
218 azp.context_id,
219 azp.display_order,
220 azp.status_code,
221 azp.context_type,
222 azp.context_name,
223 azp.comments,
224 azp.parent_id,
225 azfpv.phase,
226 wav.display_name
227 FROM az_processes azp,
228 az_flow_phases_v azfpv,
229 wf_activities_vl wav
230 WHERE azfpv.item_type = azp.item_type
231 AND azfpv.process_name = azp.process_name
232 AND azp.process_type = g_current_mode
233 AND wav.item_type = azp.item_type
234 AND wav.name = azp.process_name
235 AND wav.end_date is NULL
236 ORDER BY wav.display_name, azp.context_type, azp.context_name;
237
238 CURSOR process_hierarchy_pn IS
239 SELECT DISTINCT azp.item_type,
240 azp.process_name,
241 azp.context_id,
242 azp.display_order,
243 azp.status_code,
244 azp.context_type,
245 azp.context_name,
246 azp.comments,
247 azp.parent_id,
248 azfpv.phase,
249 wav.display_name
250 FROM az_processes azp,
251 az_flow_phases_v azfpv,
252 wf_activities_vl wav,
253 az_webform_messages azm
254 WHERE azfpv.item_type = azp.item_type
255 AND azfpv.process_name = azp.process_name
256 AND azp.process_type = g_current_mode
257 AND wav.item_type = azp.item_type
258 AND wav.name = azp.process_name
259 AND wav.end_date is NULL
260 AND azp.parent_id = azm.mesg
261 ORDER BY wav.display_name, azp.context_type, azp.context_name;
262
263 BEGIN
264 -- dbms_output.put_line('get process hierarchy' || process_status);
265
266 -- assign the local variables
267 p_assigned_user := assigned_user;
268 p_task_status := task_status;
269 p_sort_by := sort_by;
270 p_process_status := process_status;
271 p_process_phase := process_phase;
272 rollup_flag := 'Y';
273
274
275 IF (p_sort_by = 'PH' OR p_sort_by = 'C') THEN
276 BEGIN
277 FOR data IN process_hierarchy_ph LOOP
278
279 IF (data.context_name is null) THEN
280 label := data.display_name || data.context_name || ' [' ||
281 TO_CHAR(data.phase) || ']';
282 ELSE
283 label := data.display_name || ': ' || data.context_name ||
284 ' [' || TO_CHAR(data.phase) || ']';
285 END IF; /* data.context_name is null */
286
287 IF (((process_status <> 'ALL') AND (data.status_code = process_status))
288 OR
289 (process_status = 'ALL')
290 OR
291 ((process_status = 'I') AND
292 ((data.status_code = 'A') OR (data.status_code = 'N'))))
293 THEN
294 IF ((data.phase = process_phase) OR (process_phase = 0)) THEN
295 display_id := display_id + 1;
296
297 rollup_flag := 'N';
298 t_index := t_index + 1;
299 hierarchy_table(t_index).node_id := data.item_type || '.'
300 || data.process_name || '.' || data.context_id;
301 hierarchy_table(t_index).display_name := label;
302 hierarchy_table(t_index).parent_node_id := data.parent_id;
303 hierarchy_table(t_index).node_type := 'P';
304 hierarchy_table(t_index).context_name := data.context_name;
308 hierarchy_table(t_index).status := 'COMPLETED';
305 hierarchy_table(t_index).context_type := data.context_type;
306
307 IF UPPER(data.status_code) = 'C' THEN
309 ELSE
310 hierarchy_table(t_index).status := 'INCOMPLETE';
311 END IF; /* UPPER(data.status_code = 'C' */
312
313 hierarchy_table(t_index).display_order := display_id;
314
315 END IF; /* data.phase = process_phase OR process_phase = 0 */
316 END IF; /* process_status <> 'A' AND data.complete_flag = process_status */
317 END LOOP;
318
319 EXCEPTION
320 WHEN app_exception.application_exception THEN
321 RAISE;
322 WHEN OTHERS THEN
323 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
324 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
325 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
326 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_process');
327 fnd_message.set_token('AZW_ERROR_STMT','CURSOR process_hierarchy_ph');
328 APP_EXCEPTION.RAISE_EXCEPTION;
329 END;
330 ELSE /* p_sort_by = 'PN' */
331 BEGIN
332 FOR data IN process_hierarchy_pn LOOP
333 -- dbms_output.put_line('data found for PN sort ny and not all groups');
334 IF (data.context_name is null) THEN
335 label := data.display_name || data.context_name ||
336 ' [' || TO_CHAR(data.phase) || ']';
337 ELSE
338 label := data.display_name || ': ' || data.context_name ||
339 ' [' || TO_CHAR(data.phase) || ']';
340 END IF; /* data.context_name is null */
341
342 IF (((process_status <> 'ALL') AND (data.status_code = process_status))
343 OR
344 (process_status = 'ALL')
345 OR
346 ((process_status = 'I') AND
347 ((data.status_code = 'A') OR (data.status_code = 'N'))))
348 THEN
349 IF ((data.phase = process_phase) OR (process_phase = 0)) THEN
350 display_id := display_id + 1;
351 rollup_flag := 'N';
352 t_index := t_index + 1;
353
354 hierarchy_table(t_index).node_id := data.item_type || '.' ||
355 data.process_name || '.' || data.context_id;
356 hierarchy_table(t_index).display_name := label;
357 hierarchy_table(t_index).parent_node_id := 'root';
358 hierarchy_table(t_index).node_type := 'P';
359
360 IF UPPER(data.status_code) = 'C' THEN
361 hierarchy_table(t_index).status := 'COMPLETED';
362 ELSE
363 hierarchy_table(t_index).status := 'INCOMPLETE';
364 END IF; /* UPPER(data.status_code) = 'Y' */
365
366 hierarchy_table(t_index).display_order := display_id;
367 -- dbms_output.put_line('process' || label);
368
369 END IF; /* data.phase = process_phase OR process_phase = 0 */
370 END IF;
371
372 END LOOP;
373 EXCEPTION
374 WHEN OTHERS THEN
375 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
376 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
377 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
378 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_process');
379 fnd_message.set_token('AZW_ERROR_STMT','CURSOR process_hierarchy_pn');
380 APP_EXCEPTION.RAISE_EXCEPTION;
381 END;
382 END IF; /* p_sort_by = 'PH' */
383
384 EXCEPTION
385 WHEN app_exception.application_exception THEN
386 RAISE;
387 WHEN OTHERS THEN
388 --DBMS_OUTPUT.PUT_LINE('error: get_process: ' || SQLERRM);
389 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
390 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
391 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
392 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_process');
393 fnd_message.set_token('AZW_ERROR_STMT','CURSOR process_hierarchy_pn/ph');
394 APP_EXCEPTION.RAISE_EXCEPTION;
395
396 END get_process;
397
398
399 --
400 -- get_group_hierarchy_tree
401 --
402 -- Private procedure. Called by get_group_hierarchy.
403 --
404
405 PROCEDURE get_group_hierarchy_tree (group_rollup_flag OUT VARCHAR2,
406 process_group IN VARCHAR2,
407 process_status IN VARCHAR2,
408 process_phase IN NUMBER,
409 assigned_user IN VARCHAR2,
410 task_status IN VARCHAR2,
411 task_total_days IN NUMBER,
412 task_total_days_op IN VARCHAR2,
413 sort_by IN VARCHAR2) IS
414
415 p_process_group VARCHAR2(20);
416 p_process_status VARCHAR2(20);
417 p_process_phase NUMBER(3);
418 p_assigned_user VARCHAR2(30);
419 p_task_status VARCHAR2(10);
420 p_task_total_days NUMBER(3);
421 p_task_total_days_op VARCHAR2(5);
422 p_sort_by VARCHAR2(40);
423
424 /*
425 Reduced the size of the following ... in the data base they are 240
426 v_node_id az_groups.group_id%TYPE;
427 v_hierarchy_parent_id az_groups.hierarchy_parent_id%TYPE;
428 */
429 v_node_id VARCHAR2(60);
430 v_hierarchy_parent_id VARCHAR2(60);
431
432 v_display_order az_groups.display_order%TYPE;
433 v_lookup_code az_groups.lookup_code%TYPE;
434 v_rollup_flag VARCHAR2(1);
435 v_group_rollup_flag VARCHAR2(1);
436 v_application_id az_groups.application_id%TYPE;
440 SUBSTR(hierarchy_parent_id, 1, 60),
437
438 CURSOR group_hierarchy_tree_cursor IS
439 SELECT SUBSTR(group_id, 1, 60),
441 lookup_code, display_order, application_id
442 FROM az_groups
443 WHERE hierarchy_parent_id = process_group
444 AND process_type = g_current_mode
445 ORDER BY 1;
446
447 BEGIN
448
449 --dbms_output.put_line('get group hierarchy tree: '|| process_group);
450
451 p_process_group := process_group;
452 p_process_status := process_status;
453 p_process_phase := process_phase;
454 p_assigned_user := assigned_user;
455 p_task_status := task_status;
456 p_task_total_days := task_total_days;
457 p_task_total_days_op := task_total_days_op;
458 p_sort_by := sort_by;
459
460 group_rollup_flag := 'Y';
461
462 OPEN group_hierarchy_tree_cursor;
463 FETCH group_hierarchy_tree_cursor
464 INTO v_node_id, v_hierarchy_parent_id, v_lookup_code, v_display_order, v_application_id;
465
466 WHILE group_hierarchy_tree_cursor%FOUND LOOP
467
468 IF group_hierarchy_tree_not_found(v_node_id) THEN
469 -- this group is a LEAF group
470 --dbms_output.put_line('Getting processes for group : ' || v_node_id);
471
472 get_process(v_rollup_flag, v_node_id, p_process_status,
473 p_process_phase,
474 p_assigned_user, p_task_status,
475 p_task_total_days, p_task_total_days_op,
476 p_sort_by);
477
478 -- dbms_output.put_line('Completed get_process for group : ' || v_node_id);
479
480 IF v_rollup_flag = 'N' THEN
481
482 -- insert this group as a node
483 group_rollup_flag := 'N';
484
485 IF p_sort_by = 'PH' OR p_sort_by = 'C' THEN
486
487 t_index := t_index + 1;
488 IF (v_application_id is not null) THEN
489 hierarchy_table(t_index).display_name :=
490 AZW_PROC.get_application_name(v_application_id);
491 ELSE
492 hierarchy_table(t_index).display_name :=
493 AZW_PROC.get_lookup_meaning(v_lookup_code);
494 END IF; /* v_application_id is not null */
495
496 hierarchy_table(t_index).node_id := v_node_id;
497 hierarchy_table(t_index).parent_node_id := v_hierarchy_parent_id;
498 hierarchy_table(t_index).node_type := 'G';
499 hierarchy_table(t_index).status := 'INCOMPLETE';
500 hierarchy_table(t_index).display_order := v_display_order;
501
502 -- dbms_output.put_line('Inserted group : ' || v_node_id);
503 END IF;
504 END IF;
505 ELSE
506 -- this group has children groups
507 --dbms_output.put_line('Getting group_hierarchy_tree for group : ' || v_node_id);
508
509 get_group_hierarchy_tree(v_group_rollup_flag, v_node_id,
510 p_process_status, p_process_phase,
511 p_assigned_user,
512 p_task_status, p_task_total_days,
513 p_task_total_days_op, p_sort_by);
514
515 --dbms_output.put_line('Completed get_group_hierarchy_tree for group: ' || v_node_id);
516
517 IF v_group_rollup_flag = 'N' THEN
518 -- insert this group as a node
519 group_rollup_flag := 'N';
520
521 IF p_sort_by = 'PH' OR p_sort_by = 'C'THEN
522 t_index := t_index + 1;
523 IF (v_application_id is not null) THEN
524 hierarchy_table(t_index).display_name :=
525 AZW_PROC.get_application_name(v_application_id);
526 ELSE
527 hierarchy_table(t_index).display_name :=
528 AZW_PROC.get_lookup_meaning(v_lookup_code);
529 END IF; /* v_application_id is not null */
530
531 hierarchy_table(t_index).node_id := v_node_id;
532 hierarchy_table(t_index).parent_node_id := v_hierarchy_parent_id;
533 hierarchy_table(t_index).node_type := 'G';
534 hierarchy_table(t_index).status := 'INCOMPLETE';
535 hierarchy_table(t_index).display_order := v_display_order;
536
537 -- dbms_output.put_line('Inserted group : ' || v_node_id);
538 END IF;
539 END IF;
540 END IF;
541
542 FETCH group_hierarchy_tree_cursor
543 INTO v_node_id, v_hierarchy_parent_id,
544 v_lookup_code, v_display_order, v_application_id;
545
546 END LOOP;
547
548 CLOSE group_hierarchy_tree_cursor;
549
550 EXCEPTION
551 WHEN app_exception.application_exception THEN
552 RAISE;
553 WHEN OTHERS THEN
554 --dbms_output.put_line('*** Error: get_group_hierarchy_tree: ' || SQLERRM);
555 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
556 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
557 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
558 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_group_hierarchy_tree');
559 fnd_message.set_token('AZW_ERROR_STMT','CURSOR group_hierarchy_tree_cursor');
560 APP_EXCEPTION.RAISE_EXCEPTION;
561
562 END get_group_hierarchy_tree;
563
564
565 --
566 -- get_group_hierarchy
567 --
568 -- Private procedure. Called by get_hierarchy.
569 -- Retrieve all the process groups satisfying the selection criteria
570 --
571
572 PROCEDURE get_group_hierarchy(root_rollup_flag OUT VARCHAR2,
576 assigned_user IN VARCHAR2,
573 process_group IN VARCHAR2,
574 process_status IN VARCHAR2,
575 process_phase IN NUMBER,
577 task_status IN VARCHAR2,
578 task_total_days IN NUMBER,
579 task_total_days_op IN VARCHAR2,
580 sort_by IN VARCHAR2) IS
581
582 p_process_group VARCHAR2(20);
583 p_process_status VARCHAR2(20);
584 p_process_phase NUMBER(3);
585 p_assigned_user VARCHAR2(30);
586 p_task_status VARCHAR2(10);
587 p_task_total_days NUMBER(3);
588 p_task_total_days_op VARCHAR2(5);
589 p_sort_by VARCHAR2(40);
590 /*
591 Reduced the size of the following to 60 ... in the data base they are 240 ... too large
592 v_node_id az_groups.group_id%TYPE;
593 */
594 v_node_id VARCHAR2(60);
595
596 v_display_order az_groups.display_order%TYPE;
597 v_application_id az_groups.application_id%TYPE;
598 v_lookup_code az_groups.lookup_code%TYPE;
599 v_rollup_flag VARCHAR2(1);
600 v_group_rollup_flag VARCHAR2(1);
601
602 BEGIN
603
604 -- dbms_output.enable(1000000);
605 -- dbms_output.put_line('get grouping hierarchy');
606
607 p_process_group := process_group;
608 p_process_status := process_status;
609 p_process_phase := process_phase;
610 p_assigned_user := assigned_user;
611 p_task_status := task_status;
612 p_task_total_days := task_total_days;
613 p_task_total_days_op := task_total_days_op;
614 p_sort_by := sort_by;
615
616 root_rollup_flag := 'Y';
617
618 v_node_id := p_process_group;
619
620 BEGIN
621 SELECT lookup_code, application_id, display_order
622 INTO v_lookup_code, v_application_id, v_display_order
623 FROM az_groups
624 WHERE group_id = v_node_id
625 AND process_type = g_current_mode;
626
627 EXCEPTION
628 WHEN app_exception.application_exception THEN
629 RAISE;
630 WHEN OTHERS THEN
631 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
632 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
633 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
634 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_group_hierarchy');
635 fnd_message.set_token('AZW_ERROR_STMT','select lookup_code ... from az_groups');
636 APP_EXCEPTION.RAISE_EXCEPTION;
637 END;
638
639 IF group_hierarchy_tree_not_found(v_node_id) THEN
640
641 -- this node is a LEAF node
642
643 IF (p_sort_by = 'PH' OR p_sort_by = 'C') THEN
644
645 get_process(v_rollup_flag, v_node_id, p_process_status,
646 p_process_phase,
647 p_assigned_user, p_task_status,
648 p_task_total_days, p_task_total_days_op,
649 p_sort_by);
650
651 --dbms_output.put_line('Completed get_process for group : ' || v_node_id);
652
653 IF v_rollup_flag = 'N' THEN
654 root_rollup_flag := 'N';
655
656 IF (p_sort_by = 'PH' OR p_sort_by = 'C') THEN
657 t_index := t_index + 1;
658
659 IF (v_application_id is not null) THEN
660 hierarchy_table(t_index).display_name :=
661 AZW_PROC.get_application_name(v_application_id);
662 ELSE
663 hierarchy_table(t_index).display_name :=
664 AZW_PROC.get_lookup_meaning(v_lookup_code);
665 END IF; /* v_application_id is not null */
666
667 hierarchy_table(t_index).node_id := v_node_id;
668 hierarchy_table(t_index).parent_node_id := 'root';
669 hierarchy_table(t_index).node_type := 'G';
670 hierarchy_table(t_index).status := 'INCOMPLETE';
671 hierarchy_table(t_index).display_order := v_display_order;
672 END IF; /* p_sort_by = 'PH'or 'C' */
673 END IF; /* v_rollup_flag = 'N' */
674 ELSE
675 BEGIN
676 INSERT INTO az_webform_messages (mesg)
677 VALUES (v_node_id);
678 EXCEPTION
679 WHEN app_exception.application_exception THEN
680 RAISE;
681 WHEN OTHERS THEN
682 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
683 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
684 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
685 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_group_hierarchy');
686 fnd_message.set_token('AZW_ERROR_STMT','insert into az_webform_messages');
687 APP_EXCEPTION.RAISE_EXCEPTION;
688 END;
689 get_process(v_rollup_flag, v_node_id, p_process_status,p_process_phase,
690 p_assigned_user, p_task_status,
691 p_task_total_days, p_task_total_days_op, p_sort_by);
692
693 END IF; /* p_sort_by = 'PN' */
694
695 ELSE
696
697 IF (p_sort_by = 'PH' OR p_sort_by = 'C') THEN
698 --dbms_output.put_line('Getting group_hierarchy_tree for group : ' || v_node_id);
699 get_group_hierarchy_tree(v_group_rollup_flag, v_node_id,
700 p_process_status, p_process_phase,
701 p_assigned_user,
702 p_task_status, p_task_total_days,
703 p_task_total_days_op, p_sort_by);
704
705 --dbms_output.put_line('Completed get_group_hierarchy_tree for group: '||v_node_id);
706
710
707 IF v_group_rollup_flag = 'N' THEN
708 root_rollup_flag := 'N';
709 t_index := t_index + 1;
711 IF (v_application_id is not null) THEN
712 hierarchy_table(t_index).display_name :=
713 AZW_PROC.get_application_name(v_application_id);
714 ELSE
715 hierarchy_table(t_index).display_name :=
716 AZW_PROC.get_lookup_meaning(v_lookup_code);
717 END IF; /* v_application_id is not null */
718
719 hierarchy_table(t_index).node_id := v_node_id;
720 hierarchy_table(t_index).parent_node_id := 'root';
721 hierarchy_table(t_index).node_type := 'G';
722 hierarchy_table(t_index).status := 'INCOMPLETE';
723 hierarchy_table(t_index).display_order := v_display_order;
724 END IF;
725 ELSE
726 get_leaf_nodes(v_node_id);
727
728 get_process(v_rollup_flag, v_node_id, p_process_status,
729 p_process_phase,
730 p_assigned_user, p_task_status,
731 p_task_total_days, p_task_total_days_op,
732 p_sort_by);
733
734 END IF; /* p_sort_by = 'PN' */
735
736 -- END IF; /* p_sort_by = 'PH' */
737
738 END IF; /* group_hierarchy_tree_not_found(v_node_id) */
739
740 EXCEPTION
741 WHEN app_exception.application_exception THEN
742 RAISE;
743 WHEN OTHERS THEN
744 --dbms_output.put_line('*** Error: get_group_hierarchy: ' || SQLERRM);
745 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
746 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
747 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
748 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_group_hierarchy');
749 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
750 APP_EXCEPTION.RAISE_EXCEPTION;
751 END get_group_hierarchy;
752
753
754 --
755 -- task_init
756 --
757 -- Private procedure. Called by get_hierarchy each time the form for
758 -- hierarchy is shown.
759 -- Gets the display names of days, done, skip
760 -- which are part of task label for the current language
761 --
762
763 PROCEDURE task_init IS
764 BEGIN
765 -- Set the session language, if necessary
766 IF (v_language_code is null) THEN
767 BEGIN
768 select distinct language_code, nls_language
769 into v_language_code, v_language
770 from fnd_languages
771 where NLS_LANGUAGE =
772 SUBSTR(USERENV('LANGUAGE'), 1, INSTR(USERENV('LANGUAGE'), '_')-1);
773 EXCEPTION
774 WHEN app_exception.application_exception THEN
775 RAISE;
776 WHEN OTHERS THEN
777 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
778 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
779 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
780 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_hierarchy');
781 fnd_message.set_token('AZW_ERROR_STMT','select language_code ... from fnd_languages');
782 APP_EXCEPTION.RAISE_EXCEPTION;
783 END;
784 END IF;
785
786 BEGIN
787 SELECT SUBSTRB(text, 0, 8)
788 INTO v_days
789 FROM wf_resources
790 WHERE language = v_language_code
791 AND type = 'WFTKN'
792 AND name = 'DAYS';
793 EXCEPTION
794 WHEN app_exception.application_exception THEN
795 RAISE;
796 WHEN OTHERS THEN
797 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
798 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
799 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
800 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.tast_init');
801 fnd_message.set_token('AZW_ERROR_STMT','select text into v_days from wf_resources');
802 APP_EXCEPTION.RAISE_EXCEPTION;
803 END;
804
805 BEGIN
806 SELECT SUBSTRB(text, 0, 8)
807 INTO v_done
808 FROM wf_resources
809 WHERE language = v_language_code
810 AND type = 'WFTKN'
811 AND name = 'WFMON_DONE';
812 EXCEPTION
813 WHEN app_exception.application_exception THEN
814 RAISE;
815 WHEN OTHERS THEN
816 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
817 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
818 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
819 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.tast_init');
820 fnd_message.set_token('AZW_ERROR_STMT','select text into v_done from wf_resources');
821 APP_EXCEPTION.RAISE_EXCEPTION;
822 END;
823
824 BEGIN
825 SELECT SUBSTRB(text, 0, 8)
826 INTO v_skip
827 FROM wf_resources
828 WHERE language = v_language_code
829 AND type = 'WFTKN'
830 AND name = 'WFMON_SKIP';
831 EXCEPTION
832 WHEN app_exception.application_exception THEN
833 RAISE;
834 WHEN OTHERS THEN
835 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
836 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
837 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
838 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.tast_init');
839 fnd_message.set_token('AZW_ERROR_STMT','select text into v_skip from wf_resources');
840 APP_EXCEPTION.RAISE_EXCEPTION;
841 END;
842
843 BEGIN
844 SELECT SUBSTRB(text, 0, 16)
845 INTO v_priority_display
849 AND name = 'PRIORITY';
846 FROM wf_resources
847 WHERE language = v_language_code
848 AND type = 'WFTKN'
850 EXCEPTION
851 WHEN app_exception.application_exception THEN
852 RAISE;
853 WHEN OTHERS THEN
854 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
855 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
856 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
857 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.tast_init');
858 fnd_message.set_token('AZW_ERROR_STMT','select text into v_priority_display from wf_resources');
859 APP_EXCEPTION.RAISE_EXCEPTION;
860 END;
861 END task_init;
862
863
864 --
865 -- get_all_group_hierarchy
866 --
867 -- Private procedure. Called by get_hierarchy.
868 -- Retrieve all the groups
869 --
870
871 PROCEDURE get_all_group_hierarchy
872 IS
873
874 /*
875 v_node_id az_groups.group_id%TYPE;
876 v_dependency_parent_id az_groups.dependency_parent_id%TYPE;
877 v_hierarchy_parent_id az_groups.hierarchy_parent_id%TYPE;
878 */
879 v_node_id VARCHAR2(60);
880 v_dependency_parent_id VARCHAR2(60);
881 v_hierarchy_parent_id VARCHAR2(60);
882
883 v_display_order az_groups.display_order%TYPE;
884 v_application_id az_groups.application_id%TYPE;
885 v_lookup_code az_groups.lookup_code%TYPE;
886
887 CURSOR root_group_hierarchy_cursor IS
888 SELECT SUBSTR(group_id, 1, 60),
889 SUBSTR(dependency_parent_id, 1, 60),
890 display_order, application_id, lookup_code
891 FROM az_groups
892 WHERE hierarchy_parent_id is null
893 AND process_type = g_current_mode
894 ORDER BY 1;
895
896 CURSOR group_hierarchy_cursor IS
897 SELECT SUBSTR(group_id, 1, 60),
898 SUBSTR(hierarchy_parent_id, 1, 60),
899 display_order, application_id, lookup_code
900 FROM az_groups
901 WHERE hierarchy_parent_id is not null
902 AND process_type = g_current_mode
903 ORDER BY 1;
904
905 BEGIN
906
907 -- dbms_output.enable(1000000);
908 -- dbms_output.put_line('get grouping hierarchy');
909 -- get all the root groups and create root for them
910
911 BEGIN
912 OPEN root_group_hierarchy_cursor;
913 FETCH root_group_hierarchy_cursor
914 INTO v_node_id, v_dependency_parent_id, v_display_order,
915 v_application_id, v_lookup_code;
916
917 WHILE root_group_hierarchy_cursor%FOUND LOOP
918 t_index := t_index + 1;
919
920 IF (v_application_id is not null) THEN
921 hierarchy_table(t_index).display_name :=
922 AZW_PROC.get_application_name(v_application_id);
923 ELSE
924 hierarchy_table(t_index).display_name :=
925 AZW_PROC.get_lookup_meaning(v_lookup_code);
926 END IF;
927
928 hierarchy_table(t_index).node_id := v_node_id;
929 hierarchy_table(t_index).parent_node_id := 'root';
930 hierarchy_table(t_index).node_type := 'G';
931 hierarchy_table(t_index).status := 'INCOMPLETE';
932 hierarchy_table(t_index).display_order := v_display_order;
933
934 FETCH root_group_hierarchy_cursor
935 INTO v_node_id, v_dependency_parent_id, v_display_order,
936 v_application_id, v_lookup_code;
937 END LOOP;
938 EXCEPTION
939 WHEN app_exception.application_exception THEN
940 RAISE;
941 WHEN OTHERS THEN
942 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
943 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
944 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
945 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_group_hierarchy');
946 fnd_message.set_token('AZW_ERROR_STMT','CURSOR root_group_hierarchy_cursor');
947 APP_EXCEPTION.RAISE_EXCEPTION;
948 END;
949
950 -- get all the remaining hierarchy leaf groups and insert into hierarchy table
951 BEGIN
952 OPEN group_hierarchy_cursor;
953 FETCH group_hierarchy_cursor
954 INTO v_node_id, v_hierarchy_parent_id, v_display_order,
955 v_application_id, v_lookup_code;
956
957 WHILE group_hierarchy_cursor%FOUND LOOP
958 t_index := t_index + 1;
959
960 IF (v_application_id is not null) THEN
961 hierarchy_table(t_index).display_name :=
962 AZW_PROC.get_application_name(v_application_id);
963 ELSE
964 hierarchy_table(t_index).display_name :=
965 AZW_PROC.get_lookup_meaning(v_lookup_code);
966 END IF;
967
968 hierarchy_table(t_index).node_id := v_node_id;
969 hierarchy_table(t_index).parent_node_id := v_hierarchy_parent_id;
970 hierarchy_table(t_index).node_type := 'G';
971 hierarchy_table(t_index).status := 'INCOMPLETE';
972 hierarchy_table(t_index).display_order := v_display_order;
973
974 FETCH group_hierarchy_cursor
975 INTO v_node_id, v_hierarchy_parent_id, v_display_order,
976 v_application_id, v_lookup_code;
977 END LOOP;
978
979 EXCEPTION
980 WHEN app_exception.application_exception THEN
981 RAISE;
982 WHEN OTHERS THEN
983 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
984 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
985 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
989 END;
986 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_group_hierarchy');
987 fnd_message.set_token('AZW_ERROR_STMT','CURSOR group_hierarchy_cursor');
988 APP_EXCEPTION.RAISE_EXCEPTION;
990
991 EXCEPTION
992 WHEN app_exception.application_exception THEN
993 RAISE;
994 WHEN OTHERS THEN
995 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
996 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
997 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
998 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_group_hierarchy');
999 fnd_message.set_token('AZW_ERROR_STMT','CURSOR group_hierarchy_cursor');
1000 APP_EXCEPTION.RAISE_EXCEPTION;
1001
1002 END get_all_group_hierarchy;
1003
1004
1005 --
1006 -- get_all_process
1007 --
1008 -- Private procedure. Called by get_hierarchy.
1009 -- Retrieve all the processes satisfying the selection criteria
1010 -- without getting tasks belonging to the processes
1011 --
1012
1013 PROCEDURE get_all_process(process_status IN VARCHAR2,
1014 process_phase IN NUMBER,
1015 sort_by IN VARCHAR2)
1016 IS
1017 display_id NUMBER(5) DEFAULT 0;
1018 p_sort_by VARCHAR2(30);
1019 label VARCHAR2(400); -- Should be the same as hierarchy_table.display_name
1020
1021 CURSOR process_hierarchy_ph IS
1022 SELECT DISTINCT azp.item_type,
1023 azp.process_name,
1024 azp.context_id,
1025 azp.display_order,
1026 azp.status_code,
1027 azp.context_type,
1028 azp.context_name,
1029 azp.comments,
1030 azp.parent_id,
1031 azfpv.phase,
1032 wav.display_name
1033 FROM wf_activities_vl wav,
1034 az_processes azp,
1035 az_flow_phases_v azfpv
1036 WHERE azfpv.item_type = azp.item_type
1037 AND azfpv.process_name = azp.process_name
1038 AND azp.process_type = g_current_mode
1039 AND wav.item_type = azp.item_type
1040 AND wav.name = azp.process_name
1041 AND wav.end_date is NULL
1042 ORDER BY 4, 6, 7;
1043
1044 CURSOR process_hierarchy_pn_all IS
1045 SELECT DISTINCT azp.item_type,
1046 azp.process_name,
1047 azp.context_id,
1048 azp.display_order,
1049 azp.status_code,
1053 azp.parent_id,
1050 azp.context_type,
1051 azp.context_name,
1052 azp.comments,
1054 azfpv.phase,
1055 wav.display_name
1056 FROM wf_activities_vl wav,
1057 az_processes azp,
1058 az_flow_phases_v azfpv
1059 WHERE azfpv.item_type = azp.item_type
1060 AND azfpv.process_name = azp.process_name
1061 AND azp.process_type = g_current_mode
1062 AND wav.item_type = azp.item_type
1063 AND wav.name = azp.process_name
1064 AND wav.end_date is NULL
1065 ORDER BY wav.display_name, azp.context_type, azp.context_name;
1066
1067 BEGIN
1068
1069 -- when sort by is Process Hierarchy
1070 IF (sort_by = 'PH' OR sort_by = 'C') THEN
1071 BEGIN
1072 FOR data IN process_hierarchy_ph LOOP
1073 IF (data.context_name is null) THEN
1074 label := data.display_name || data.context_name
1075 || ' [' || TO_CHAR(data.phase) || ']';
1076 ELSE
1077 label := data.display_name || ': ' || data.context_name
1078 || ' [' || TO_CHAR(data.phase) || ']';
1079 END IF;
1080 --dbms_output.put_line('getting data for process 2' || data.display_name);
1081
1082 IF (((process_status <> 'ALL') AND (data.status_code = process_status))
1083 OR
1084 (process_status = 'ALL')
1085 OR
1086 ((process_status = 'I') AND
1087 ((data.status_code = 'A') OR (data.status_code = 'N'))))
1088 THEN
1089 IF ((data.phase = process_phase) OR (process_phase = 0)) THEN
1090 display_id := display_id + 1;
1091 t_index := t_index + 1;
1092
1093 hierarchy_table(t_index).node_id := data.item_type || '.' ||
1094 data.process_name || '.' || data.context_id;
1095 hierarchy_table(t_index).display_name := label;
1096 hierarchy_table(t_index).parent_node_id := data.parent_id;
1097 hierarchy_table(t_index).node_type := 'P';
1098 hierarchy_table(t_index).context_name := data.context_name;
1099 hierarchy_table(t_index).context_type := data.context_type;
1100
1101 IF UPPER(data.status_code) = 'C' THEN
1102 hierarchy_table(t_index).status := 'COMPLETED';
1103 ELSE
1104 hierarchy_table(t_index).status := 'INCOMPLETE';
1108 --dbms_output.put_line('inserted process ' || label);
1105 END IF;
1106 hierarchy_table(t_index).display_order := display_id;
1107
1109 END IF;
1110 END IF;
1111 END LOOP;
1112 EXCEPTION
1113 WHEN app_exception.application_exception THEN
1114 RAISE;
1115 WHEN OTHERS THEN
1116 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1117 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1118 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1119 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_process');
1120 fnd_message.set_token('AZW_ERROR_STMT','CURSOR process_hierarchy_ph');
1121 APP_EXCEPTION.RAISE_EXCEPTION;
1122 END;
1123
1124 ELSE
1125 -- When sort by is by Process Name
1126 BEGIN
1127 FOR data IN process_hierarchy_pn_all LOOP
1128 IF (data.context_name is null) THEN
1129 label := data.display_name || data.context_name || ' [' ||
1130 TO_CHAR(data.phase) || ']';
1131 ELSE
1132 label := data.display_name || ': ' || data.context_name || ' [' ||
1133 TO_CHAR(data.phase) || ']';
1134 END IF;
1135
1136 IF (((process_status <> 'ALL') AND (data.status_code = process_status))
1137 OR
1138 (process_status = 'ALL')
1139 OR
1140 ((process_status = 'I') AND
1141 ((data.status_code = 'A') OR (data.status_code = 'N'))))
1142 THEN
1143 IF ((data.phase = process_phase) OR (process_phase = 0)) THEN
1144 display_id := display_id + 1;
1145 t_index := t_index + 1;
1146 hierarchy_table(t_index).node_id := data.item_type || '.' ||
1147 data.process_name || '.' || data.context_id;
1148 hierarchy_table(t_index).display_name := label;
1149 hierarchy_table(t_index).parent_node_id := 'root';
1150 hierarchy_table(t_index).node_type := 'P';
1151
1152 IF UPPER(data.status_code) = 'C' THEN
1153 hierarchy_table(t_index).status := 'COMPLETED';
1154 ELSE
1155 hierarchy_table(t_index).status := 'INCOMPLETE';
1156 END IF;
1157
1158 hierarchy_table(t_index).display_order := display_id;
1159 END IF;
1160 END IF;
1161 END LOOP;
1162 EXCEPTION
1166 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1163 WHEN app_exception.application_exception THEN
1164 RAISE;
1165 WHEN OTHERS THEN
1167 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1168 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1169 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_process');
1170 fnd_message.set_token('AZW_ERROR_STMT','CURSOR process_hierarchy_pn_all');
1171 APP_EXCEPTION.RAISE_EXCEPTION;
1172 END;
1173 END IF;
1174
1175 EXCEPTION
1176 WHEN app_exception.application_exception THEN
1177 RAISE;
1178 WHEN OTHERS THEN
1179 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1180 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1181 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1182 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_process');
1183 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1184 APP_EXCEPTION.RAISE_EXCEPTION;
1185
1186 END get_all_process;
1187
1188
1189 --
1190 -- get_all_task
1191 --
1192 -- Private procedure. Called by get_hierarchy.
1193 -- Gets all the tasks satisfying the search criteria.
1194 --
1195
1196 PROCEDURE get_all_task(p_process_status IN VARCHAR2,
1197 p_process_phase IN NUMBER,
1198 p_assigned_user IN VARCHAR2,
1199 p_task_status IN VARCHAR2,
1200 p_task_tot_days IN NUMBER,
1201 p_task_tot_days_op IN VARCHAR2,
1202 p_sort_by IN VARCHAR2)
1203 IS
1204 v_item_type az_processes.item_type%TYPE;
1205 v_item_type_p az_processes.item_type%TYPE;
1206 v_process_name az_processes.process_name%TYPE;
1207 v_parent_id az_processes.parent_id%TYPE;
1208 v_comments az_processes.comments%TYPE;
1209 v_context_id az_processes.context_id%TYPE;
1210 v_status_code az_processes.status_code%TYPE;
1211 v_context_type az_processes.context_type%TYPE;
1212 v_context_name az_processes.context_name%TYPE;
1213 v_display_order az_processes.display_order%TYPE;
1214
1215 v_status wf_item_activity_statuses.activity_status%TYPE;
1219 v_item_key wf_items.item_key%TYPE;
1216 v_task_status wf_item_activity_statuses.activity_status%TYPE;
1217 v_dummy_begin_date wf_item_activity_statuses.begin_date%TYPE;
1218 v_root_activity wf_items.root_activity%TYPE;
1220 v_count NUMBER(4);
1221 v_skip_count NUMBER(4);
1222 v_display_string wf_activities_vl.display_name%TYPE;
1223 v_days_no NUMBER(4);
1224 v_role_assd wf_item_attribute_values.text_value%TYPE;
1225 display_id NUMBER(5) DEFAULT 0;
1226 v_task_begin_date wf_items.begin_date%TYPE;
1227 v_task_tot_days NUMBER(5) DEFAULT 0;
1228 v_days_since NUMBER(5) DEFAULT 0;
1229 v_process_phase az_product_phases.phase%TYPE;
1230 v_display_status wf_resources.text%TYPE;
1231
1232 CURSOR az_item_types IS
1233 SELECT distinct item_type
1234 FROM az_processes;
1235
1236 CURSOR az_tasks_cursor IS
1237 SELECT DISTINCT wfi.item_key,
1238 wfi.root_activity,
1239 wfi.begin_date,
1240 round( months_between(sysdate, wfi.begin_date)* 31),
1241 wias.activity_status,
1242 wiav2.text_value,
1243 azpfv.phase,
1244 azp.status_code,
1245 azp.item_type,
1246 azp.context_id
1247 FROM wf_item_attribute_values wiav1,
1248 wf_item_attribute_values wiav2,
1249 wf_item_activity_statuses wias,
1250 wf_process_activities wpa,
1251 az_processes azp,
1252 az_flow_phases_v azpfv,
1253 wf_items wfi
1254 WHERE wfi.item_type = azp.item_type
1255 AND wfi.root_activity = azp.process_name
1256 AND azpfv.item_type = azp.item_type
1257 AND azpfv.process_name = azp.process_name
1258 AND wiav1.item_type = azp.item_type
1259 AND wiav1.item_key = wfi.item_key
1260 AND wiav1.name = 'AZW_IA_CTXT_ID'
1261 AND wiav1.text_value = to_char(azp.context_id)
1262 AND wiav2.item_type = wfi.item_type
1263 AND wiav2.item_key = wfi.item_key
1264 AND wiav2.name = 'AZW_IA_ROLE'
1265 AND wias.item_type = wfi.item_type
1266 AND wias.item_key = wfi.item_key
1267 AND wpa.instance_id = wias.process_activity
1268 AND wpa.activity_name = wfi.root_activity
1269 AND wpa.process_item_type = azp.item_type
1270 AND wpa.process_name = 'ROOT'
1271 AND azp.item_type = v_item_type_p
1272 ORDER BY wfi.begin_date;
1273
1274 CURSOR az_steps_count_cursor IS
1275 SELECT COUNT(*)
1279 AND wias.item_key = v_item_key
1276 FROM wf_item_activity_statuses wias, wf_notification_attributes wna,
1277 wf_notifications wn
1278 WHERE wias.item_type = v_item_type
1280 AND wias.notification_id IS NOT NULL
1281 AND wna.notification_id = wias.notification_id
1282 AND wn.notification_id = wna.notification_id
1283 AND wn.status = 'CLOSED'
1284 AND wna.name = 'RESULT'
1285 AND wna.text_value LIKE '%DONE%';
1286
1287 CURSOR az_skip_count_cursor IS
1288 SELECT COUNT(*)
1289 FROM wf_item_activity_statuses wias, wf_notification_attributes wna,
1290 wf_notifications wn
1291 WHERE wias.item_type = v_item_type
1292 AND wias.item_key = v_item_key
1293 AND wias.notification_id is not NULL
1294 AND wna.notification_id = wias.notification_id
1295 AND wn.notification_id = wna.notification_id
1296 AND wn.status = 'CLOSED'
1297 AND wna.name = 'RESULT'
1298 AND wna.text_value like '%SKIP%';
1299
1300 CURSOR az_task_display_cursor IS
1301 SELECT wav.display_name,
1302 round( months_between(sysdate, wias.begin_date)* 31),
1303 wias.begin_date
1304 FROM wf_process_activities wpa, wf_item_activity_statuses wias,
1305 wf_activities_vl wav
1306 WHERE wias.item_type = v_item_type
1307 AND wias.item_key = v_item_key
1308 AND wias.process_activity = wpa.instance_id
1309 AND wpa.activity_name = wav.name
1310 AND wpa.activity_item_type = wav.item_type
1311 AND wpa.process_name <> 'ROOT'
1312 AND wpa.activity_name <> 'START'
1313 AND wav.begin_date is not NULL
1314 AND wav.end_date is NULL
1318 BEGIN
1315 AND wav.type = 'NOTICE'
1316 ORDER BY wias.begin_date desc;
1317
1319 -- dbms_output.put_line('get tasks all hierarchy');
1320 -- dbms_output.put_line('Done with lookups');
1321 -- Get iterations for processes
1322 -- dbms_output.put_line('Task status is :' || p_task_status);
1323 -- dbms_output.put_line('getting all statuses tasks');
1324 -- Get all tasks one at a time
1325
1326 OPEN az_item_types;
1327 FETCH az_item_types
1328 INTO v_item_type_p;
1329
1330 WHILE az_item_types%FOUND LOOP
1331
1332 OPEN az_tasks_cursor;
1333 FETCH az_tasks_cursor
1334 INTO v_item_key, v_root_activity, v_task_begin_date, v_task_tot_days,
1335 v_task_status, v_role_assd, v_process_phase, v_status_code,
1336 v_item_type, v_context_id;
1337
1338 -- IMPORTANT ... trim v_role_assd to 60 characters and v_item_key to 100
1339 v_role_assd := SUBSTR(v_role_assd, 1, 100);
1340 v_item_key := SUBSTR(v_item_key, 1, 15);
1341
1342 BEGIN
1343
1344 WHILE az_tasks_cursor%FOUND LOOP
1345 --dbms_output.put_line('Found all statuses tasks: ' || v_item_key);
1346 display_id := display_id + 1;
1347
1348 OPEN az_task_display_cursor;
1349 FETCH az_task_display_cursor
1350 INTO v_display_string, v_days_since,
1351 v_dummy_begin_date;
1352
1353 --dbms_output.put_line('Opening count cursor');
1354
1355 OPEN az_steps_count_cursor;
1356 FETCH az_steps_count_cursor INTO v_count;
1357
1358 OPEN az_skip_count_cursor;
1359 FETCH az_skip_count_cursor INTO v_skip_count;
1360
1361 --dbms_output.put_line('count: ' || v_count);
1362
1363 IF(((p_task_tot_days_op = '=') AND (v_task_tot_days = p_task_tot_days))
1364 OR
1368 OR
1365 ((p_task_tot_days_op = '>') AND (v_task_tot_days > p_task_tot_days))
1366 OR
1367 ((p_task_tot_days_op = '<') AND (v_task_tot_days < p_task_tot_days))
1369 ((p_task_tot_days_op = '<=') AND (v_task_tot_days <= p_task_tot_days))
1370 OR
1371 ((p_task_tot_days_op = '>=') AND (v_task_tot_days >= p_task_tot_days))
1372 OR
1373 ((p_task_tot_days_op = '!=') AND (v_task_tot_days <> p_task_tot_days)))
1374 THEN
1375 IF((v_role_assd = p_assigned_user) OR (p_assigned_user = 'ALL'))
1376 AND (((p_task_status = 'A') AND (v_task_status = 'ACTIVE')) OR (p_task_status = 'ALL')
1377 OR ((p_task_status = 'C') AND (v_task_status <> 'ACTIVE')))
1378 AND ((v_process_phase = p_process_phase) OR (p_process_phase = 0))
1379 AND (((v_status_code = p_process_status) OR (p_process_status = 'ALL'))
1380 OR (( p_process_status = 'I') AND ((v_status_code = 'A') OR (v_status_code = 'N'))))
1381 THEN
1382 -- dbms_output.put_line('inserting task');
1383 BEGIN
1384 t_index := t_index + 1;
1385 hierarchy_table(t_index).node_id := v_item_type || '.' ||
1386 v_root_activity || '.'|| v_context_id || '.' || v_item_key;
1387 hierarchy_table(t_index).parent_node_id := v_item_type || '.' ||
1388 v_root_activity || '.' || v_context_id;
1389 hierarchy_table(t_index).node_type := 'I';
1390
1391 IF (v_task_status = 'ACTIVE') THEN
1392 hierarchy_table(t_index).status := 'INCOMPLETE';
1393 hierarchy_table(t_index).display_name := v_display_string ||
1394 ': ' || v_role_assd || ' - ' ||to_char(v_task_tot_days)
1395 || ' '|| v_days || ': ' || to_char(v_count) ||' '||
1396 v_done || ' , '|| to_char(v_skip_count) || ' ' || v_skip;
1397 ELSE
1398 hierarchy_table(t_index).status := 'COMPLETED';
1399 hierarchy_table(t_index).display_name := v_role_assd ||
1400 ' - ' ||to_char(v_task_tot_days) || ' '|| v_days ||
1401 ': ' || to_char(v_count) ||' '|| v_done || ' , '||
1402 to_char(v_skip_count) || ' ' || v_skip;
1403 END IF;
1404
1405 hierarchy_table(t_index).display_order := display_id;
1409 RAISE;
1406
1407 EXCEPTION
1408 WHEN app_exception.application_exception THEN
1410 WHEN OTHERS THEN
1411 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1412 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1413 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1414 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_task');
1415 fnd_message.set_token('AZW_ERROR_STMT','while inserting task to hierarchy table');
1416 APP_EXCEPTION.RAISE_EXCEPTION;
1417 END;
1418 END IF;
1419 END IF;
1420
1421 CLOSE az_task_display_cursor;
1422 CLOSE az_steps_count_cursor;
1423 CLOSE az_skip_count_cursor;
1424
1425 FETCH az_tasks_cursor
1426 INTO v_item_key, v_root_activity, v_task_begin_date, v_task_tot_days,
1427 v_task_status, v_role_assd, v_process_phase, v_status_code,
1428 v_item_type, v_context_id;
1429 END LOOP;
1430
1431 CLOSE az_tasks_cursor;
1432
1433 EXCEPTION
1434 WHEN app_exception.application_exception THEN
1435 RAISE;
1436 WHEN OTHERS THEN
1437 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1438 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1439 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1440 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_task');
1441 fnd_message.set_token('AZW_ERROR_STMT','While looping in CURSOR az_tasks_cursor');
1442 APP_EXCEPTION.RAISE_EXCEPTION;
1443 END;
1444
1448 CLOSE az_item_types;
1445 FETCH az_item_types
1446 INTO v_item_type_p;
1447 END LOOP;
1449
1450 EXCEPTION
1451 WHEN app_exception.application_exception THEN
1452 RAISE;
1453 WHEN OTHERS THEN
1454 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1455 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1456 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1457 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_all_task');
1458 fnd_message.set_token('AZW_ERROR_STMT','While looping in CURSOR az_item_types');
1459 APP_EXCEPTION.RAISE_EXCEPTION;
1460
1461 END get_all_task;
1462
1463
1464 --
1465 -- GET_HIERARCHY
1466 --
1467 -- Public procedure. Called by Process Overview window.
1468 -- The message format which has been agreed upon between front end
1469 -- and back end is as follows:
1470 --
1471 -- display_name^node_id^parent_node_id^node_type^status^display_order^^
1472 --
1473
1474 PROCEDURE get_hierarchy (process_group IN VARCHAR2,
1475 process_status IN VARCHAR2,
1476 process_phase IN NUMBER,
1477 assigned_user IN VARCHAR2,
1478 task_status IN VARCHAR2,
1482 IS
1479 task_total_days IN NUMBER,
1480 task_total_days_op IN VARCHAR2,
1481 sort_by IN VARCHAR2)
1483
1484 msg message_tbl_t;
1485 i BINARY_INTEGER DEFAULT 0;
1486 p_process_group az_groups.group_id%TYPE;
1487 p_process_status VARCHAR2(20);
1488 p_process_phase NUMBER(3);
1489 p_assigned_user VARCHAR2(30);
1490 p_task_status VARCHAR2(10);
1491 p_task_total_days NUMBER(3);
1492 p_task_total_days_op VARCHAR2(5);
1493 p_sort_by VARCHAR2(40);
1494 v_root_rollup_flag VARCHAR2(1);
1495
1496 -- Start: added by swarup for context sort
1497 disp_order INTEGER ;
1498 v_context_type fnd_lookups.lookup_code%TYPE;
1499 v_meaning fnd_lookups.meaning%TYPE;
1500
1501 CURSOR get_context_types_c IS
1502
1503 SELECT lookup_code,
1504 meaning,
1505 DECODE(lookup_code, 'NONE', 1, 'BG', 2, 'SOB', 3, 'OU', 4, 5)
1506 display_order
1507 FROM fnd_lookups
1508 WHERE lookup_type = 'AZ_CONTEXT_TYPE'
1509 ORDER BY display_order;
1510
1511 -- End: added by swarup for context sort
1512
1513 BEGIN
1514 dbms_output.enable(1000000);
1515 -- DBMS_OUTPUT.PUT_LINE('get_hierarchy: ');
1516 -- assign the variables
1517 p_process_status := process_status;
1518 p_process_group := process_group;
1519 p_process_phase := process_phase;
1520 p_assigned_user := assigned_user;
1521 p_task_status := task_status;
1522 p_task_total_days := task_total_days;
1523 p_task_total_days_op := task_total_days_op;
1524 p_sort_by := sort_by;
1525
1526 -- get the current mode
1527 g_current_mode := fnd_profile.value('AZ_CURRENT_MODE');
1528
1529 -- get the current session language
1530 BEGIN
1531 select distinct language_code, nls_language
1532 into v_language_code, v_language
1533 from fnd_languages
1534 where NLS_LANGUAGE =
1535 SUBSTR(USERENV('LANGUAGE'), 1, INSTR(USERENV('LANGUAGE'), '_')-1);
1536
1537 EXCEPTION
1538 WHEN app_exception.application_exception THEN
1539 RAISE;
1540 WHEN OTHERS THEN
1541 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1542 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1543 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1544 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_hierarchy');
1545 fnd_message.set_token('AZW_ERROR_STMT','select language_code .. from fnd_languages');
1546 APP_EXCEPTION.RAISE_EXCEPTION;
1547 END;
1548
1549 -- initialize the language specific display labels for task's label
1550 task_init;
1551 hierarchy_table.delete;
1552 msg.delete;
1553 t_index := 0;
1554
1555 -- AZW_PROC.populate_az_processes is now called from AZWIZARD.fmb within the
1556 -- WHEN_NEW_FORM_INSTANCE trigger instead.
1557 -- AZW_PROC.populate_az_processes; -- DONT REMOVE THIS !!!
1558
1559 -- get groups, then get processes and then tasks
1560 IF (p_process_group = 'ALL') THEN
1561 IF (p_sort_by = 'PH' OR p_sort_by = 'C') THEN
1562 get_all_group_hierarchy;
1563 END IF;
1564
1565 get_all_process(p_process_status, p_process_phase, p_sort_by);
1566
1567 get_all_task(p_process_status, p_process_phase, p_assigned_user,
1568 p_task_status, p_task_total_days, p_task_total_days_op, p_sort_by);
1569 ELSE
1570 -- get groups which then get processes and tasks recursively
1571 --dbms_output.put_line('Calling');
1572 get_group_hierarchy(v_root_rollup_flag, p_process_group,p_process_status,
1573 p_process_phase, p_assigned_user,
1574 p_task_status, p_task_total_days,
1575 p_task_total_days_op, p_sort_by);
1576 get_all_task(p_process_status, p_process_phase, p_assigned_user,
1577 p_task_status, p_task_total_days, p_task_total_days_op,
1578 p_sort_by);
1579 END IF;
1580
1581 -- The following condition is added by swarup for context sort
1582 IF (p_sort_by = 'C') THEN
1583
1584 BEGIN
1585 OPEN get_context_types_c ;
1586 FETCH get_context_types_c INTO v_context_type, v_meaning, disp_order;
1587 WHILE get_context_types_c%FOUND LOOP
1588 -- Make nodes with Context Names with each context type
1589 -- This will add all groups and their sub groups under
1590 -- each node
1591 -- dbms_output.put_line('TYPE: ' || v_context_type ||' meaning: ' || v_meaning );
1592 ctx_table.delete ;
1596 ctx_table(1).context_id := 0; -- Shouldn't be used, careful.
1593
1594 IF UPPER(v_context_type) = 'NONE' THEN
1595 ctx_table(1).context_name := v_context_type;
1597 ELSE
1598 get_context(v_context_type, ctx_table);
1599
1600 END IF;
1601 insert_context( v_context_type, v_meaning, msg, i, disp_order);
1602
1603 -- Now insert groups for each context
1604 -- unnecessary groups will be rolled up by java
1605 -- from hierarchy table
1606 insert_groups_for_context(v_context_type, msg, i);
1607 FETCH get_context_types_c INTO v_context_type, v_meaning, disp_order;
1608 END LOOP;
1609 CLOSE get_context_types_c ;
1610
1611 EXCEPTION
1612 WHEN app_exception.application_exception THEN
1613 RAISE;
1614 WHEN OTHERS THEN
1615 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1616 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1617 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1618 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_hierarchy');
1619 fnd_message.set_token('AZW_ERROR_STMT','CURSOR get_context_types_c');
1620 APP_EXCEPTION.RAISE_EXCEPTION;
1621 END;
1622
1623 -- Now insert processes/tasks for each their respective context
1624 insert_proc_task_for_context(msg, i);
1625
1626 ELSE -- Either 'PH' or 'PN'
1627 FOR j IN 1..hierarchy_table.COUNT LOOP
1628 i := i + 1;
1629 msg(i) := hierarchy_table(j).display_name || msg_delimiter ||
1630 hierarchy_table(j).node_id || msg_delimiter ||
1631 hierarchy_table(j).parent_node_id || msg_delimiter ||
1632 hierarchy_table(j).node_type || msg_delimiter ||
1633 hierarchy_table(j).status || msg_delimiter ||
1634 hierarchy_table(j).display_order || msg_delimiter || msg_delimiter;
1635 END LOOP;
1636 END IF;
1637
1638 BEGIN
1639 -- Somehow table.COUNT doesn't work.. so use i, which is the count of rows
1640 IF i > 0 THEN
1644 END IF;
1641 FORALL k IN 1..i
1642 INSERT INTO az_webform_messages (mesg)
1643 VALUES (msg(k));
1645
1646 EXCEPTION
1647 WHEN app_exception.application_exception THEN
1648 RAISE;
1649 WHEN OTHERS THEN
1650 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1651 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1652 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1653 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_hierarchy');
1654 fnd_message.set_token('AZW_ERROR_STMT','insert into az_webform_messages');
1655 APP_EXCEPTION.RAISE_EXCEPTION;
1656 END;
1657
1658 EXCEPTION
1659 WHEN app_exception.application_exception THEN
1660 RAISE;
1661 WHEN OTHERS THEN
1662 --DBMS_OUTPUT.PUT_LINE('error: get_hierarchy: ' || SQLERRM);
1663 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1664 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1665 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1666 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_hierarchy');
1667 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1668 APP_EXCEPTION.RAISE_EXCEPTION;
1669
1670 END get_hierarchy;
1671
1672 /*---------------------------------------------------------------
1673 INSERT_PROC_TASK_FOR_CONTEXT
1674 private procedure inserts all processes and tasks
1675 ---------------------------------------------------------------*/
1676
1677 PROCEDURE insert_proc_task_for_context(
1678 msg IN OUT message_tbl_t,
1679 i IN OUT INTEGER ) IS
1680 p_context_name VARCHAR2(200);
1684 BEGIN
1681 p_context_type VARCHAR2(20);
1682 parent_id VARCHAR2(220);
1683 node_id VARCHAR2(220);
1685 FOR j IN 1..hierarchy_table.COUNT LOOP
1686 IF ( hierarchy_table(j).node_type = 'G') THEN
1687 NULL;
1688 ELSE
1689 parent_id := hierarchy_table(j).parent_node_id;
1690 --dbms_output.put_line('Here: '|| parent_id );
1691 IF ( hierarchy_table(j).node_type = 'P') THEN
1692
1693 p_context_type := hierarchy_table(j).context_type;
1694 p_context_name := hierarchy_table(j).context_name;
1695
1696 IF ( UPPER(p_context_type) = 'NONE' ) THEN
1697 parent_id := parent_id ||
1698 'NONE' || 'NONE';
1699 ELSE
1700 parent_id := parent_id ||
1701 p_context_type || p_context_name ;
1702 END IF;
1703 END IF;
1704 node_id := hierarchy_table(j).node_id ;
1705 i := i + 1;
1706 msg(i) := hierarchy_table(j).display_name || msg_delimiter ||
1707 node_id || msg_delimiter ||
1708 parent_id || msg_delimiter ||
1709 hierarchy_table(j).node_type || msg_delimiter ||
1710 hierarchy_table(j).status || msg_delimiter ||
1711 hierarchy_table(j).display_order ||
1712 msg_delimiter || msg_delimiter;
1713 END IF;
1714 END LOOP;
1715 EXCEPTION
1716 WHEN OTHERS THEN
1720 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1717 --DBMS_OUTPUT.PUT_LINE('error: insert_proc_task_for_context: ' || SQLERRM);
1718 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1719 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1721 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.insert_proc_task_for_context');
1722 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1723 APP_EXCEPTION.RAISE_EXCEPTION;
1724 END insert_proc_task_for_context;
1725
1726 /*---------------------------------------------------------------
1727 * INSERT_CONTEXT
1728 Private procedure : added by swarup for context sort
1729 This procedure adds five node at the root level
1730 1. NONE
1731 2. BG
1732 3. SOB
1733 4. OU
1734 5. IO
1735 Then all contexts are added below respective types
1736 EXCEPT 'NONE', which doesn't have context
1737
1738 -------------------------------------------------------------- */
1739 PROCEDURE insert_context( ctx_type IN VARCHAR2,
1740 meaning IN VARCHAR2,
1741 msg IN OUT message_tbl_t,
1742 i IN OUT INTEGER,
1743 p_disp_order IN INTEGER
1744 ) IS
1745 l_disp_order INTEGER DEFAULT 0;
1746 l_meaning fnd_lookups.meaning%TYPE;
1747
1748 BEGIN
1749
1750 l_meaning := meaning ;
1751
1752 --IF meaning = 'NULL' OR meaning IS NULL OR meaning = '' THEN
1753 IF ctx_type = 'NONE' THEN
1754 -- note that context type is concatenated twice
1758 ctx_type || ctx_type || msg_delimiter ||
1755 -- this will be used for attaching groups below it
1756 i := i + 1;
1757 msg(i) := NVL(meaning,'NONE') || msg_delimiter ||
1759 'root'|| msg_delimiter ||
1760 'G'|| msg_delimiter ||
1761 'INCOMPLETE'|| msg_delimiter ||
1762 p_disp_order || msg_delimiter || msg_delimiter;
1763 ELSE
1764
1765 -- First add the context type
1766
1767 i := i + 1;
1768 msg(i) := meaning || msg_delimiter ||
1769 ctx_type || msg_delimiter ||
1770 'root'|| msg_delimiter ||
1771 'G'|| msg_delimiter ||
1772 'INCOMPLETE'|| msg_delimiter ||
1773 p_disp_order || msg_delimiter || msg_delimiter;
1774
1775 -- Now add all context under type
1776
1777 FOR l in 1..ctx_table.COUNT LOOP
1778 l_disp_order := l_disp_order + 1;
1779 i := i + 1;
1780 msg(i) := ctx_table(l).context_name || msg_delimiter ||
1781 ctx_type||ctx_table(l).context_name|| msg_delimiter ||
1782 ctx_type || msg_delimiter ||
1783 'G'|| msg_delimiter ||
1784 'INCOMPLETE'|| msg_delimiter ||
1785 l_disp_order || msg_delimiter || msg_delimiter;
1786 END LOOP;
1787 END IF;
1788 EXCEPTION
1789 WHEN OTHERS THEN
1790 --DBMS_OUTPUT.PUT_LINE('error: insert_context: ' || SQLERRM);
1791 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1792 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1793 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1794 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.insert_context');
1795 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1796 APP_EXCEPTION.RAISE_EXCEPTION;
1797 END insert_context;
1798
1799
1800 FUNCTION validContextType( ctx_type IN VARCHAR2,p_node_id VARCHAR2) RETURN BOOLEAN IS
1801 v_count PLS_INTEGER := 0;
1802 v_application_id az_groups.application_id%TYPE;
1803
1804 CURSOR get_subgroup_c IS
1805 select group_id from az_groups
1806 where
1807 hierarchy_parent_id = p_node_id
1808 and process_type = g_current_mode;
1809
1810 BEGIN
1811 SELECT count(distinct context_type)
1812 INTO v_count
1816 AND parent_id = p_node_id ;
1813 FROM az_processes
1814 WHERE context_type = ctx_type
1815 AND process_type = g_current_mode
1817
1818 IF v_count > 0 THEN
1819 return TRUE;
1820 ELSE
1821 FOR data IN get_subgroup_c LOOP
1822 IF validContextType( ctx_type,data.group_id ) = TRUE THEN
1823 return TRUE;
1824 END IF;
1825 END LOOP;
1826 END IF;
1827
1828 RETURN FALSE;
1829
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 return TRUE;
1833 -- Do not put exception handler here
1834 END validContextType;
1835
1836
1837 /*---------------------------------------------------------------
1838 * INSERT_NODES_FOR_CONTEXT_TYPE
1839 Private procedure : added by swarup for context sort
1840 This procedure loops thru all the nodes of hierarchy table
1841 ( as retrieved as a hieararchy ), adds all groups for each
1842 context type : name pair , and processes/tasks for their
1843 own context type : name pair only
1847 ctx_type IN VARCHAR2,
1844 -------------------------------------------------------------- */
1845
1846 PROCEDURE insert_groups_for_context(
1848 msg IN OUT message_tbl_t,
1849 i IN OUT INTEGER
1850 ) IS
1851 parent_id VARCHAR2(220); -- id := context_name || context_type
1852 node_id VARCHAR2(220);
1853
1854 BEGIN
1855
1856 FOR j IN 1..hierarchy_table.COUNT LOOP
1857 IF ( hierarchy_table(j).node_type = 'G' AND
1858 validContextType( ctx_type, hierarchy_table(j).node_id ) = TRUE ) THEN
1859
1860 FOR k IN 1..ctx_table.COUNT LOOP
1861 IF ( hierarchy_table(j).parent_node_id = 'root') THEN
1862 parent_id := ctx_type || ctx_table(k).context_name;
1863 ELSE
1864 parent_id := hierarchy_table(j).parent_node_id ||
1865 ctx_type || ctx_table(k).context_name ;
1866 END IF;
1867
1868 node_id := hierarchy_table(j).node_id ||
1869 ctx_type || ctx_table(k).context_name ;
1870 i := i + 1;
1871 msg(i) := hierarchy_table(j).display_name || msg_delimiter ||
1872 node_id || msg_delimiter ||
1873 parent_id || msg_delimiter ||
1874 hierarchy_table(j).node_type || msg_delimiter ||
1875 hierarchy_table(j).status || msg_delimiter ||
1876 hierarchy_table(j).display_order ||
1877 msg_delimiter || msg_delimiter;
1878 END LOOP; -- Loop for context table
1879 END IF;
1880 END LOOP;
1881 EXCEPTION
1882 WHEN OTHERS THEN
1883 --DBMS_OUTPUT.PUT_LINE('AZW_HIER.insert_groups_for_context: ' || SQLERRM);
1884 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1885 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1886 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1887 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.insert_groups_for_context');
1888 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1889 APP_EXCEPTION.RAISE_EXCEPTION;
1890 END insert_groups_for_context;
1891
1892 /*---------------------------------------------------------------
1893 * get_context : private procedure
1894 * retrieves all distinct context_id, context_name
1895 * from az_processes table.
1896 * AZW_PROC.get_context fetches all the context_id, context_name
1897 * but this one fetches only those for which some processes exists.
1898 *--------------------------------------------------------------*/
1899
1900 PROCEDURE get_context( ctx_type IN VARCHAR2,
1901 ctx_table OUT AZW_PROC.context_tbl_t) IS
1902
1903 CURSOR ctx_cursor IS
1907 context_type = ctx_type
1904 SELECT DISTINCT context_id, context_name
1905 FROM az_processes
1906 WHERE
1908 ORDER BY context_name;
1909 i BINARY_INTEGER DEFAULT 0;
1910 BEGIN
1911
1912 ctx_table.delete;
1913 FOR data IN ctx_cursor LOOP
1914 i := i + 1;
1915 ctx_table(i).context_id := data.context_id;
1916 ctx_table(i).context_name := data.context_name;
1917 END LOOP;
1918
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1922 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1923 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1924 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_context');
1925 fnd_message.set_token('AZW_ERROR_STMT','cursor select from az_processes ');
1926 END get_context;
1927
1928 END AZW_HIER;