[Home] [Help]
PACKAGE BODY: APPS.AZW_PROC
Source
1 PACKAGE BODY AZW_PROC AS
2 /* $Header: AZWPROCB.pls 115.46 2003/04/08 08:50:04 sbandi ship $: */
3
4
5 TYPE status_rec_t IS RECORD (
6 item_type az_processes.item_type%TYPE,
7 process_name az_processes.process_name%TYPE,
8 context_id az_processes.context_id%TYPE);
9
10 TYPE status_tbl_t IS TABLE OF status_rec_t INDEX BY BINARY_INTEGER;
11
12 group_status_tbl status_tbl_t;
13
14 group_status_index BINARY_INTEGER DEFAULT 0;
15
16 TYPE process_rec_t IS RECORD (
17 item_type az_processes.item_type%TYPE,
18 process_name az_processes.process_name%TYPE,
19 parent_id az_processes.parent_id%TYPE,
20 context_type az_processes.context_type%TYPE,
21 display_order az_processes.display_order%TYPE,
22 process_type az_processes.process_type%TYPE);
23
24 TYPE process_tbl_t IS TABLE OF process_rec_t INDEX BY BINARY_INTEGER;
25
26 msg_delimiter VARCHAR2(1) := '^';
27
28 v_language_code fnd_languages.language_code%TYPE;
29 v_language fnd_languages.nls_language%TYPE;
30 v_days VARCHAR2(8);
31 v_done VARCHAR2(8);
32 v_skip VARCHAR2(8);
33 v_new_task_key wf_items.item_key%TYPE;
34 g_current_mode az_groups.process_type%TYPE;
35
36 PROCEDURE update_hierarchy_status(p_item_type IN VARCHAR2,
37 p_process_name IN VARCHAR2,
38 p_context_id IN VARCHAR2);
39
40 --
41 -- get_parent_group
42 --
43 -- Private function. Called by get_group_color
44 -- Given a process' parent id, find the process parent group's parent
45 -- Returns 'NONE' if there is no more parent
46 --
47
48 FUNCTION get_parent_group(p_parent_id IN VARCHAR2) RETURN VARCHAR2
49 IS
50 v_parent_gr_id az_groups.group_id%TYPE;
51 BEGIN
52 -- dbms_output.put_line(' getting parent for ' || p_parent_id);
53
54 SELECT azg1.group_id
55 INTO v_parent_gr_id
56 FROM az_groups azg1, az_groups azg2
57 WHERE azg2.group_id = p_parent_id
58 AND azg1.process_type = azg2.process_type
59 AND azg1.process_type = g_current_mode
60 AND azg1.group_id = azg2.hierarchy_parent_id;
61
62 RETURN v_parent_gr_id;
63
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 v_parent_gr_id := 'NONE';
67 RETURN v_parent_gr_id;
68 WHEN app_exception.application_exception THEN
69 RAISE;
70 WHEN OTHERS THEN
71 --DBMS_OUTPUT.PUT_LINE('error: group_hierarchy_tree_not_found: ' || SQLERRM);
72 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
73 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
74 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
75 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_parent_group');
76 fnd_message.set_token('AZW_ERROR_STMT','select group_id from az_groups');
77 APP_EXCEPTION.RAISE_EXCEPTION;
78 END get_parent_group;
79
80
81 --
82 -- PARSE_CTXT_ID_TASK
83 --
84 -- Public function. Called by get_group_color.
85 -- Given a concatenated node_id for a task, retrieve context_id for that node.
86 -- Separator is '.'
87 --
88 FUNCTION parse_ctxt_id_task(node_id IN VARCHAR2) RETURN NUMBER IS
89
90 v_first_step PLS_INTEGER;
91 v_second_step PLS_INTEGER;
92 v_ctx_id az_processes.context_id%TYPE;
93
94 BEGIN
95 -- dbms_output.put_line('parse context id');
96
97 v_first_step := INSTR(node_id, '.', 1, 2);
98 v_second_step := INSTR(node_id, '.',-1, 1);
99 v_ctx_id := SUBSTR(node_id, v_first_step + 1, v_second_step - v_first_step - 1);
100
101 RETURN v_ctx_id;
102 EXCEPTION
103 WHEN app_exception.application_exception THEN
104 RAISE;
105 WHEN OTHERS THEN
106 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
107 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
108 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
109 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_ctxt_id_task');
110 fnd_message.set_token('AZW_ERROR_STMT','NODE_ID :'|| node_id);
111 APP_EXCEPTION.RAISE_EXCEPTION;
112 END parse_ctxt_id_task;
113
114
115 --
116 -- process_not_found
117 --
118 -- Private function. Called by set_process.
119 -- Given a process and a context id, check if it already exits in the
120 -- az_processes table. If the process is already in the table,
121 -- return FALSE; otherwise return TRUE.
122 --
123 FUNCTION process_not_found(proc process_rec_t, p_ctx_id NUMBER) RETURN BOOLEAN
124 IS
125 v_cnt PLS_INTEGER DEFAULT 0;
126 BEGIN
127 -- dbms_output.put_line('process not found');
128
129 SELECT count(*)
130 INTO v_cnt
131 FROM az_processes ap
132 WHERE ap.item_type = proc.item_type
133 AND ap.process_name = proc.process_name
134 AND ap.context_id = p_ctx_id;
135
136 IF v_cnt = 0 THEN
137 RETURN TRUE;
138 ELSE
139 RETURN FALSE;
140 END IF;
141
142 EXCEPTION
143 WHEN app_exception.application_exception THEN
144 RAISE;
145 WHEN OTHERS THEN
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_proc.process_not_found');
150 fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_processes');
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END process_not_found;
153
154
155 --
156 -- PARSE_APPLICATION_IDS
157 --
158 -- Public function. Called by populate_product_flows.
159 -- Parses a string of app ids, returning one appl_id at a time.
160 --
161 FUNCTION parse_application_ids(p_application_ids IN VARCHAR2,
162 id_cnt IN NUMBER)
163
164 RETURN NUMBER IS
165
166 prev_cnt PLS_INTEGER DEFAULT 0;
167 pres_cnt PLS_INTEGER DEFAULT 0;
168 id_length NUMBER DEFAULT 0;
169 v_id NUMBER DEFAULT 0;
170 BEGIN
171
172 pres_cnt := INSTR(p_application_ids, ',', 1, id_cnt);
173
174 IF id_cnt > 1 THEN
175 prev_cnt := INSTR(p_application_ids, ',', 1, id_cnt - 1);
176 IF ((pres_cnt = 0) AND (prev_cnt <> 0)) THEN
177 pres_cnt := LENGTH(p_application_ids) + 1;
178 END IF;
179 ELSE
180 IF (pres_cnt = 0) THEN
181 v_id := to_number(p_application_ids);
182 RETURN v_id;
183 END IF;
184 END IF;
185
186 id_length := pres_cnt - prev_cnt - 1;
187
188 v_id := to_number(SUBSTR(p_application_ids, prev_cnt + 1, id_length));
189
190 IF v_id IS NULL THEN
191 RETURN -1;
192 ELSE
193 RETURN v_id;
194 END IF;
195
196 EXCEPTION
197 WHEN app_exception.application_exception THEN
198 APP_EXCEPTION.RAISE_EXCEPTION;
199 WHEN OTHERS THEN
200 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
201 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
202 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
203 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_application_ids');
204 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
205 APP_EXCEPTION.RAISE_EXCEPTION;
206 END parse_application_ids;
207
208
209 --
210 -- PARSE_ITEM_TYPE
211 --
212 -- Public function. Called by update_process_xxxx.
213 -- Given a concatenated node_id, retrieve item_type for that node.
214 -- Separator is '.'
215 --
216 FUNCTION parse_item_type(node_id IN VARCHAR2) RETURN VARCHAR2 IS
217
218 v_first_sep PLS_INTEGER;
219 v_type az_processes.item_type%TYPE;
220
221 BEGIN
222
223 v_first_sep := INSTR(node_id, '.', 1, 1);
224 v_type := SUBSTR(node_id, 1, v_first_sep - 1);
225 RETURN v_type;
226
227 EXCEPTION
228 WHEN app_exception.application_exception THEN
229 RAISE;
230 WHEN OTHERS THEN
231 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
232 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
233 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
234 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_item_type');
235 fnd_message.set_token('AZW_ERROR_STMT','NODE_ID:'|| node_id);
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 END parse_item_type;
238
239
240 --
241 -- PARSE_PROCESS_NAME
242 --
243 -- Public function.
244 -- Given a cocatenated node_id, retrieve process_name for that node.
245 -- Separator is '.'
246 --
247 FUNCTION parse_process_name(node_id IN VARCHAR2) RETURN VARCHAR2 IS
248
249 v_first_sep PLS_INTEGER;
250 v_second_sep PLS_INTEGER;
251 p_name az_processes.process_name%TYPE;
252
253 BEGIN
254
255 v_first_sep := INSTR(node_id, '.', 1, 1);
256 v_second_sep := INSTR(node_id, '.', -1, 1);
257 p_name := SUBSTR(node_id, v_first_sep + 1, v_second_sep - v_first_sep - 1);
258
259 RETURN p_name;
260
261 EXCEPTION
262 WHEN app_exception.application_exception THEN
263 APP_EXCEPTION.RAISE_EXCEPTION;
264 WHEN OTHERS THEN
265 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
266 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
267 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
268 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_process_name');
269 fnd_message.set_token('AZW_ERROR_STMT','NODE_ID:'|| node_id);
270 APP_EXCEPTION.RAISE_EXCEPTION;
271 END parse_process_name;
272
273 --
274 -- PARSE_PROCESS_NAME_TASK
275 --
276 -- Public function.
277 -- Given a concatenated node_id for a task, get process_name for that node.
278 -- Separator is '.'
279 --
280 FUNCTION parse_process_name_task(node_id IN VARCHAR2) RETURN VARCHAR2 IS
281
282 v_first_sep PLS_INTEGER;
283 v_second_sep PLS_INTEGER;
284 p_name az_processes.process_name%TYPE;
285
286 BEGIN
287
288 v_first_sep := INSTR(node_id, '.', 1, 1);
289 v_second_sep := INSTR(node_id, '.', 1, 2);
290 p_name := SUBSTR(node_id, v_first_sep + 1, v_second_sep - v_first_sep - 1);
291 RETURN p_name;
292
293 EXCEPTION
294 WHEN app_exception.application_exception THEN
295 APP_EXCEPTION.RAISE_EXCEPTION;
296 WHEN OTHERS THEN
297 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
298 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
299 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
300 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_process_name_task');
301 fnd_message.set_token('AZW_ERROR_STMT','NODEID:'|| node_id);
302 APP_EXCEPTION.RAISE_EXCEPTION;
303 END parse_process_name_task;
304
305 --
306 -- PARSE_CONTEXT_ID
307 --
308 -- Public function.
309 -- Given a concatenated node_id, retrieve context_id for that node.
310 -- Separator is '.'
311 --
312 FUNCTION parse_context_id(node_id IN VARCHAR2) RETURN VARCHAR2 IS
313
314 v_last_sep PLS_INTEGER;
315 v_len PLS_INTEGER;
316 v_ctx_id az_processes.context_id%TYPE;
317
318 BEGIN
319
320 v_last_sep := INSTR(node_id, '.', -1, 1);
321 v_len := LENGTH(node_id);
322 v_ctx_id := SUBSTR(node_id, v_last_sep + 1, v_len - v_last_sep);
323 RETURN v_ctx_id;
324
325 EXCEPTION
326 WHEN app_exception.application_exception THEN
327 APP_EXCEPTION.RAISE_EXCEPTION;
328 WHEN OTHERS THEN
329 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
330 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
331 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
332 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_context_id');
333 fnd_message.set_token('AZW_ERROR_STMT','NODE_ID:'||node_id);
337
334 APP_EXCEPTION.RAISE_EXCEPTION;
335 END parse_context_id;
336
338 --
339 -- PARSE_ITEM_KEY
340 --
341 -- Public function.
342 -- Given a concatenated node_id for a task , get item key for that task.
343 -- Separator is '.'
344 --
345 FUNCTION parse_item_key(node_id IN VARCHAR2) RETURN VARCHAR2 IS
346
347 v_last_sep PLS_INTEGER;
348 v_len PLS_INTEGER;
349 itm_k wf_items.item_key%TYPE;
350
351 BEGIN
352
353 v_last_sep := INSTR(node_id, '.', -1, 1);
354 v_len := LENGTH(node_id);
355 itm_k := SUBSTR(node_id, v_last_sep + 1, v_len - v_last_sep);
356
357 RETURN itm_k;
358
359 EXCEPTION
360 WHEN app_exception.application_exception THEN
361 RAISE;
362 WHEN OTHERS THEN
363 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
364 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
365 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
366 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_item_key');
367 fnd_message.set_token('AZW_ERROR_STMT','NODE_ID:'|| node_id);
368 APP_EXCEPTION.RAISE_EXCEPTION;
369 END parse_item_key;
370
371
372 --
373 -- GET_GROUP_COLOR
374 --
375 -- Public function. Called by step detail form
376 -- Given a node id for a task, return the color of its root group
377 -- to which it belongs
378 --
379 FUNCTION get_group_color(node_id IN VARCHAR2) RETURN VARCHAR2
380 IS
381
382 v_process_parent_id az_processes.parent_id%TYPE;
383 v_process_item_type az_processes.item_type%TYPE;
384 v_process_name az_processes.process_name%TYPE;
385 v_process_ctxt_id az_processes.context_id%TYPE;
386 p_group_id az_groups.group_id%TYPE;
387 v_group_color az_groups.color_code%TYPE;
388 p_node_id VARCHAR2(300);
389
390 BEGIN
391 p_node_id := node_id;
392 v_process_item_type := parse_item_type(p_node_id);
393 v_process_name := parse_process_name_task(p_node_id);
394 v_process_ctxt_id := parse_ctxt_id_task(p_node_id);
395
396 BEGIN
397 SELECT parent_id
398 INTO v_process_parent_id
399 FROM az_processes
400 WHERE item_type = v_process_item_type
401 AND process_name = v_process_name
402 AND context_id = v_process_ctxt_id;
403 EXCEPTION
404 WHEN OTHERS THEN
405 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
406 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
407 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
408 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_group_color');
409 fnd_message.set_token('AZW_ERROR_STMT','select parent_id from az_processes');
410 APP_EXCEPTION.RAISE_EXCEPTION;
411 END;
412
413 p_group_id := v_process_parent_id;
414
415 WHILE (p_group_id <> 'NONE') LOOP
416 v_process_parent_id := p_group_id;
417 p_group_id := get_parent_group(v_process_parent_id);
418 END LOOP;
419
420 BEGIN
421 SELECT color_code
422 INTO v_group_color
423 FROM az_groups
424 WHERE group_id = v_process_parent_id
425 AND process_type = g_current_mode;
426
427 RETURN v_group_color;
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
432 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
433 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
434 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_group_color');
435 fnd_message.set_token('AZW_ERROR_STMT','select color_code from az_groups');
436 APP_EXCEPTION.RAISE_EXCEPTION;
437 END;
438 EXCEPTION
439 WHEN app_exception.application_exception THEN
440 APP_EXCEPTION.RAISE_EXCEPTION;
441 WHEN OTHERS THEN
442 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
443 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
444 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
445 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_group_color');
446 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
447 APP_EXCEPTION.RAISE_EXCEPTION;
448 END get_group_color;
449
450 --
451 -- get_context_name
452 --
453 -- Private function. Called by set_hierarchy_display_name.
454 --
455 FUNCTION get_context_name(id IN VARCHAR2, ctx_table IN context_tbl_t)
456 RETURN VARCHAR2 IS
457
458 BEGIN
459
460 FOR i IN 1..ctx_table.COUNT LOOP
461 IF ctx_table(i).context_id = TO_NUMBER(id) THEN
462 RETURN ctx_table(i).context_name;
463 END IF;
464 END LOOP;
465
466 RETURN '???';
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
471 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
472 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
473 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_name');
474 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
475 APP_EXCEPTION.RAISE_EXCEPTION;
476 END get_context_name;
477
478 --
479 -- get_context_type
480 --
481 -- Private function. Called by wrapper_azw_start.
482 -- Given a process item type, process name and context id return
483 -- it's context type.
484 --
485 FUNCTION get_context_type(p_item_type IN VARCHAR2,
486 p_process_name IN VARCHAR2,
487 p_context_id IN NUMBER)
488 RETURN VARCHAR2 IS
489
490 v_ctxt_type az_processes.context_type%TYPE;
491
492 BEGIN
493
494 IF (p_context_id > -1) THEN
495 SELECT waav.text_default
496 INTO v_ctxt_type
497 FROM wf_activity_attributes_vl waav
498 WHERE waav.activity_item_type = p_item_type
499 AND waav.activity_name = p_process_name
500 AND waav.name = 'AZW_IA_CTXTYP'
501 AND waav.activity_version =
502 (SELECT MAX(activity_version)
503 FROM wf_activity_attributes_vl
504 WHERE activity_item_type = p_item_type
505 AND activity_name = p_process_name
506 AND name = 'AZW_IA_CTXTYP');
507 END IF;
508
509 RETURN v_ctxt_type;
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
514 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
515 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
516 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_type');
517 fnd_message.set_token('AZW_ERROR_STMT','select text_default from wf_activity_attributes_vl');
518 APP_EXCEPTION.RAISE_EXCEPTION;
519
520 END get_context_type;
521
522 -- get_opm_context_name
523 -- Private function. Called by get_context_name.
524 -- Executes dynamic sql and returns the name of opmcontexts
525
526 FUNCTION get_opm_context_name(ctx_type IN VARCHAR2, ctx_id IN NUMBER)
527 RETURN VARCHAR2 IS
528
529 curs integer;
530 rows integer;
531 sqlstatement az_contexts_sql.SQL_STATEMENT%TYPE;
532 v_ctxt_name az_processes.context_name%TYPE;
533
534 BEGIN
535
536 v_ctxt_name := NULL;
537
538 SELECT sql_statement
539 INTO sqlstatement
540 FROM az_contexts_sql
541 WHERE context = ctx_type
542 AND purpose = 'GET_NAME';
543
544 curs := DBMS_SQL.OPEN_CURSOR;
545 DBMS_SQL.PARSE(curs, sqlstatement, DBMS_SQL.NATIVE);
546
547 DBMS_SQL.DEFINE_COLUMN(curs, 1, v_ctxt_name, 80);
548 DBMS_SQL.BIND_VARIABLE(curs, ':ctx_id', ctx_id);
549
550 rows := DBMS_SQL.EXECUTE(curs);
551 rows := DBMS_SQL.FETCH_ROWS(curs);
552
553 DBMS_SQL.COLUMN_VALUE(curs, 1, v_ctxt_name);
554 DBMS_SQL.CLOSE_CURSOR(curs);
555
556 RETURN v_ctxt_name;
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 IF DBMS_SQL.IS_OPEN(curs) then
561 DBMS_SQL.CLOSE_CURSOR(curs);
562 END IF;
563
564 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
565 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
566 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
567 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_opm_context_name');
568 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
569 APP_EXCEPTION.RAISE_EXCEPTION;
570
571 END get_opm_context_name;
572
573 --
574 -- get_context_name
575 --
576 -- Private function. Called by AIWStart.
577 -- Given a context type and a context id find the context name.
578 --
579 FUNCTION get_context_name(ctx_type IN VARCHAR2, ctx_id IN NUMBER)
580 RETURN VARCHAR2 IS
581
582 v_ctxt_name az_processes.context_name%TYPE;
583
584 BEGIN
585
586 IF (ctx_type = 'BG') THEN
587 BEGIN
588 SELECT name INTO v_ctxt_name
589 FROM per_business_groups
590 WHERE date_from < SYSDATE
591 AND (date_to IS NULL
592 OR date_to > SYSDATE)
593 AND organization_id = ctx_id;
594 EXCEPTION
595 WHEN OTHERS THEN
596 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
597 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
598 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
599 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_name');
600 fnd_message.set_token('AZW_ERROR_STMT','select name from per_business_groups');
601 APP_EXCEPTION.RAISE_EXCEPTION;
602 END;
603
604 ELSIF (ctx_type = 'IO') THEN
605 BEGIN
606 SELECT organization_name INTO v_ctxt_name
607 FROM org_organization_definitions
608 WHERE user_definition_enable_date < SYSDATE
609 AND (disable_date IS NULL
610 OR disable_date > SYSDATE)
611 AND organization_id = ctx_id;
612 EXCEPTION
613 WHEN OTHERS THEN
617 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_name');
614 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
615 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
616 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
618 fnd_message.set_token('AZW_ERROR_STMT','select name from org_organization_definitions');
619 APP_EXCEPTION.RAISE_EXCEPTION;
620 END;
621
622 ELSIF (ctx_type = 'OU') THEN
623 BEGIN
624 SELECT name INTO v_ctxt_name
625 FROM hr_operating_units
626 WHERE organization_id = ctx_id
627 AND date_from < SYSDATE
628 AND (date_to IS NULL
629 OR date_to > SYSDATE);
630 EXCEPTION
631 WHEN OTHERS THEN
632 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
633 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
634 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
635 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_name');
636 fnd_message.set_token('AZW_ERROR_STMT','select name from hr_operating_units');
637 APP_EXCEPTION.RAISE_EXCEPTION;
638 END;
639
640 ELSIF (ctx_type = 'SOB') THEN
641 BEGIN
642 SELECT name INTO v_ctxt_name
643 FROM gl_sets_of_books
644 WHERE set_of_books_id = ctx_id;
645 EXCEPTION
646 WHEN OTHERS THEN
647 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
648 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
649 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
650 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_name');
651 fnd_message.set_token('AZW_ERROR_STMT','select name from gl_sets_of_books');
652 APP_EXCEPTION.RAISE_EXCEPTION;
653 END;
654
655 ELSIF (ctx_type = 'OPMCOM' OR ctx_type = 'OPMORG') THEN
656 BEGIN
657 v_ctxt_name := get_opm_context_name(ctx_type, ctx_id);
658 EXCEPTION
659 WHEN OTHERS THEN
660 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
661 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
662 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
663 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_name');
664 fnd_message.set_token('AZW_ERROR_STMT','select name from sy_orgn_mst');
665 APP_EXCEPTION.RAISE_EXCEPTION;
666 END;
667
668 END IF;
669
670 IF v_ctxt_name is NULL THEN
671 v_ctxt_name := 'NONE';
672 END IF;
673
674 RETURN v_ctxt_name;
675
676 EXCEPTION
677 WHEN app_exception.application_exception THEN
678 RAISE;
679 WHEN OTHERS THEN
680 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
681 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
682 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
683 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context_name');
684 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
685 APP_EXCEPTION.RAISE_EXCEPTION;
686 END get_context_name;
687
688
689 --
690 -- get_application_name
691 --
692 -- Public function. Called by various procedures.
693 -- Given an application id, find the corresponding application name.
694 --
695 FUNCTION get_application_name(appl_id NUMBER)
696 RETURN VARCHAR2 IS
697
698 v_application_name fnd_application_vl.application_name%TYPE;
699 BEGIN
700
701 SELECT application_name
702 INTO v_application_name
703 FROM fnd_application_vl
704 WHERE application_id = appl_id;
705
706 RETURN v_application_name;
707
708
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 v_application_name := 'NONE';
712 RETURN v_application_name;
713 WHEN app_exception.application_exception THEN
714 APP_EXCEPTION.RAISE_EXCEPTION;
715 WHEN OTHERS THEN
716 -- DBMS_OUTPUT.PUT_LINE('error: get_application_name: ' || SQLERRM);
717 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
718 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
719 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
720 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_application_name');
721 fnd_message.set_token('AZW_ERROR_STMT','select application_name from fnd_application_vl');
722 APP_EXCEPTION.RAISE_EXCEPTION;
723 END get_application_name;
724
725
726 --
727 -- get_lookup_meaning
728 --
729 -- Public function. Called by various procedures.
730 -- Given a lookup code, find the corresponding meaning.
731 --
732 FUNCTION get_lookup_meaning(code VARCHAR2)
733 RETURN VARCHAR2 IS
734
735 v_meaning fnd_lookups.meaning%TYPE;
736
737 BEGIN
738
739 SELECT meaning
740 INTO v_meaning
741 FROM fnd_lookups
742 WHERE lookup_type = 'AZ_PROCESS_GROUPS'
743 AND lookup_code = code;
744
745 RETURN v_meaning;
746
747 EXCEPTION
748 WHEN app_exception.application_exception THEN
749 APP_EXCEPTION.RAISE_EXCEPTION;
750 WHEN NO_DATA_FOUND THEN
751 v_meaning := 'NONE';
752 RETURN v_meaning;
753 WHEN OTHERS THEN
754 -- DBMS_OUTPUT.PUT_LINE('error: get_lookup_meaning: ' || SQLERRM);
758 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_lookup_meaning');
755 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
756 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
757 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
759 fnd_message.set_token('AZW_ERROR_STMT','select meaning from fnd_lookups');
760 APP_EXCEPTION.RAISE_EXCEPTION;
761 END get_lookup_meaning;
762
763
764 --
765 -- task_init
766 --
767 -- Private procedure. Called by get_hierarchy each time the form for
768 -- hierarchy is shown.
769 -- Gets the display names of days,done,skip
770 -- which are part of task label for the current language
771 --
772 PROCEDURE task_init IS
773 BEGIN
774 IF (v_language_code is null) THEN
775 BEGIN
776 select distinct language_code, nls_language
777 into v_language_code, v_language
778 from fnd_languages
779 where NLS_LANGUAGE =
780 SUBSTR(USERENV('LANGUAGE'), 1, INSTR(USERENV('LANGUAGE'), '_')-1);
781 EXCEPTION
782 WHEN app_exception.application_exception THEN
783 RAISE;
784 WHEN OTHERS THEN
785 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
786 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
787 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
788 fnd_message.set_token('AZW_ERROR_PROC','azw_hier.get_hierarchy')
789 ;
790 fnd_message.set_token('AZW_ERROR_STMT','select language_code ..
791 from fnd_languages');
792 APP_EXCEPTION.RAISE_EXCEPTION;
793 END;
794 END IF;
795 BEGIN
796 SELECT substr(text, 1, 8)
797 INTO v_days
798 FROM wf_resources
799 WHERE language = v_language_code
800 AND type = 'WFTKN'
801 AND name = 'DAYS';
802 EXCEPTION
803 WHEN OTHERS THEN
804 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
805 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
806 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
807 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.task_init');
808 fnd_message.set_token('AZW_ERROR_STMT','select into v_days');
809 APP_EXCEPTION.RAISE_EXCEPTION;
810 END;
811
812 BEGIN
813 SELECT substr(text, 1, 8)
814 INTO v_done
815 FROM wf_resources
816 WHERE language = v_language_code
817 AND type = 'WFTKN'
818 AND name = 'WFMON_DONE';
819 EXCEPTION
820 WHEN OTHERS THEN
821 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
822 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
823 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
824 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.task_init');
825 fnd_message.set_token('AZW_ERROR_STMT','select into v_done');
826 APP_EXCEPTION.RAISE_EXCEPTION;
827 END;
828
829 BEGIN
830 SELECT substr(text, 1, 8)
831 INTO v_skip
832 FROM wf_resources
833 WHERE language = v_language_code
834 AND type = 'WFTKN'
835 AND name = 'WFMON_SKIP';
836 EXCEPTION
837 WHEN OTHERS THEN
838 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
839 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
840 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
841 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.task_init');
842 fnd_message.set_token('AZW_ERROR_STMT','select into v_skip');
843 APP_EXCEPTION.RAISE_EXCEPTION;
844 END;
845
846 END task_init;
847
848
849 -- GET_CONTEXT
850 --
851 -- Public procedure. Called by populate_processes and AZW_HIER.
852 -- Given a context type, retrieve all enabled/valid context ids and
853 -- store in the given PL/SQL context table.
854 --
855 PROCEDURE get_context(ctx_type IN VARCHAR2, ctx_table OUT NOCOPY context_tbl_t) IS
856
857 i BINARY_INTEGER DEFAULT 0;
858 CURSOR bg_cursor IS
859 SELECT organization_id, name
860 FROM per_business_groups
861 WHERE date_from < SYSDATE
862 AND (date_to IS NULL
863 OR date_to > SYSDATE)
864 ORDER BY organization_id;
865
866 CURSOR io_cursor IS
867 SELECT organization_id, organization_name
868 FROM org_organization_definitions
869 WHERE user_definition_enable_date < SYSDATE
870 AND (disable_date IS NULL
871 OR disable_date > SYSDATE)
872 ORDER BY organization_id;
873
874 CURSOR ou_cursor IS
875 SELECT organization_id, name
876 FROM hr_operating_units
877 WHERE date_from < SYSDATE
878 AND (date_to IS NULL
879 OR date_to > SYSDATE)
880 ORDER BY organization_id;
881
882 CURSOR sob_cursor IS
883 SELECT set_of_books_id, name
884 FROM gl_sets_of_books
885 ORDER BY set_of_books_id;
886
887 curs integer;
888 rows integer;
889 sqlstatement az_contexts_sql.SQL_STATEMENT%TYPE;
890 t1 NUMBER(15);
891 t2 varchar2(40);
895 -- dbms_output.put_line('get context type=' || ctx_type);
892
893
894 BEGIN
896
897 IF (ctx_type = 'BG') THEN
898
899 BEGIN
900 OPEN bg_cursor;
901 LOOP
902 i := i + 1;
903 FETCH bg_cursor INTO ctx_table(i);
904 EXIT WHEN bg_cursor%NOTFOUND;
905 END LOOP;
906 CLOSE bg_cursor;
907 EXCEPTION
908 WHEN OTHERS THEN
909 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
910 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
911 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
912 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context');
913 fnd_message.set_token('AZW_ERROR_STMT','CURSOR bg_cursor');
914 APP_EXCEPTION.RAISE_EXCEPTION;
915 END;
916
917 ELSIF (ctx_type = 'IO') THEN
918
919 BEGIN
920 OPEN io_cursor;
921 LOOP
922 i := i + 1;
923 FETCH io_cursor INTO ctx_table(i);
924 EXIT WHEN io_cursor%NOTFOUND;
925 END LOOP;
926 CLOSE io_cursor;
927 EXCEPTION
928 WHEN OTHERS THEN
929 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
930 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
931 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
932 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context');
933 fnd_message.set_token('AZW_ERROR_STMT','CURSOR io_cursor');
934 APP_EXCEPTION.RAISE_EXCEPTION;
935 END;
936
937 ELSIF (ctx_type = 'OU') THEN
938
939 BEGIN
940 OPEN ou_cursor;
941 LOOP
942 i := i + 1;
943 FETCH ou_cursor INTO ctx_table(i);
944 EXIT WHEN ou_cursor%NOTFOUND;
945 END LOOP;
946 CLOSE ou_cursor;
947 EXCEPTION
948 WHEN OTHERS THEN
949 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
950 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
951 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
952 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context');
953 fnd_message.set_token('AZW_ERROR_STMT','CURSOR ou_cursor');
954 APP_EXCEPTION.RAISE_EXCEPTION;
955 END;
956
957 ELSIF (ctx_type = 'SOB') THEN
958
959 BEGIN
960 OPEN sob_cursor;
961 LOOP
962 i := i + 1;
963 FETCH sob_cursor INTO ctx_table(i);
964 EXIT WHEN sob_cursor%NOTFOUND;
965 END LOOP;
966 CLOSE sob_cursor;
967 EXCEPTION
968 WHEN OTHERS THEN
969 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
970 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
971 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
972 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context');
973 fnd_message.set_token('AZW_ERROR_STMT','CURSOR sob_cursor');
974 APP_EXCEPTION.RAISE_EXCEPTION;
975 END;
976
977 ELSIF (ctx_type = 'OPMCOM' OR ctx_type = 'OPMORG') THEN
978
979 BEGIN
980 SELECT sql_statement
981 INTO sqlstatement
982 FROM az_contexts_sql
983 WHERE context = ctx_type
984 AND purpose = 'POPULATE';
985
986 curs := DBMS_SQL.OPEN_CURSOR;
987 DBMS_SQL.PARSE(curs, sqlstatement, DBMS_SQL.NATIVE);
988
989 DBMS_SQL.DEFINE_COLUMN(curs, 1, t1);
990 DBMS_SQL.DEFINE_COLUMN(curs, 2, t2, 40);
991
992 rows := DBMS_SQL.EXECUTE(curs);
993
994 LOOP
995 IF DBMS_SQL.FETCH_ROWS(curs) > 0 THEN
996
997 DBMS_SQL.COLUMN_VALUE(curs, 1, t1);
998 DBMS_SQL.COLUMN_VALUE(curs, 2, t2);
999
1000 IF (t1 IS NOT NULL) THEN
1001 i := i + 1;
1002 ctx_table(i).context_id := t1;
1003 ctx_table(i).context_name := t2;
1004 END IF;
1005
1006 ELSE
1007 EXIT;
1008
1009 END IF;
1010 END LOOP;
1011
1012 DBMS_SQL.CLOSE_CURSOR(curs);
1013
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017
1018 if DBMS_SQL.IS_OPEN(curs) then
1019 DBMS_SQL.CLOSE_CURSOR(curs);
1020 end if;
1021
1022 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1023 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1024 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1025 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context');
1026 fnd_message.set_token('AZW_ERROR_STMT','CURSOR opm_cursor');
1027 APP_EXCEPTION.RAISE_EXCEPTION;
1028 END;
1029
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN app_exception.application_exception THEN
1034 RAISE;
1035 WHEN OTHERS THEN
1036 -- DBMS_OUTPUT.PUT_LINE('error: get_context: ' || SQLERRM);
1037 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1038 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1039 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1040 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_context');
1041 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1042 APP_EXCEPTION.RAISE_EXCEPTION;
1043
1044 END get_context;
1045
1046
1047 --
1048 -- get_processes
1049 --
1050 -- Private procedure. Called by populate_az_processes.
1054 PROCEDURE get_processes(proc_table OUT NOCOPY process_tbl_t) IS
1051 -- Retrieve all workflow runnable processes for installed products
1052 -- and store them in the given PL/SQL table.
1053 --
1055
1056 i BINARY_INTEGER DEFAULT 0;
1057
1058 CURSOR valid_processes_c IS
1059 SELECT distinct azpf.item_type, azpf.process_name, azpf.parent_id,
1060 azpf.context_type, azpf.display_order, azpf.process_type
1061 FROM az_product_flows azpf, fnd_product_installations fpi
1062 WHERE azpf.application_id = fpi.application_id
1063 AND fpi.status = 'I';
1064
1065 BEGIN
1066 --dbms_output.put_line('get processes');
1067
1068 OPEN valid_processes_c;
1069 LOOP
1070 i := i + 1;
1071 FETCH valid_processes_c INTO proc_table(i);
1072 EXIT WHEN valid_processes_c%NOTFOUND;
1073 END loop;
1074 CLOSE valid_processes_c;
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 CLOSE valid_processes_c;
1079 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1080 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1081 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1082 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_processes');
1083 fnd_message.set_token('AZW_ERROR_STMT','CURSOR valid_processes_c');
1084 APP_EXCEPTION.RAISE_EXCEPTION;
1085 END get_processes;
1086
1087
1088 --
1089 -- insert_az_processes
1090 --
1091 -- Private procedure. Called by populate_processes.
1092 -- Insert the process into az_processes for each context.
1093 -- (Cartesian product)
1094 -- Check if the process with the context is already in the table; insert
1095 -- the one not already in the table.
1096 --
1097 PROCEDURE insert_az_processes(proc IN process_rec_t,ctxts IN context_tbl_t) IS
1098
1099 BEGIN
1100
1101 FOR j IN 1..ctxts.COUNT LOOP
1102
1103 IF process_not_found(proc, ctxts(j).context_id) THEN
1104 BEGIN
1105 INSERT INTO az_processes(item_type, process_name, context_id,
1106 display_order, complete_flag,
1107 context_type, context_name, comments,parent_id,
1108 status_code, process_type)
1109 VALUES (proc.item_type, proc.process_name,
1110 ctxts(j).context_id, proc.display_order, 'N', proc.context_type,
1111 ctxts(j).context_name, NULL, proc.parent_id,
1112 'N', proc.process_type);
1113
1114 group_status_index := group_status_index + 1;
1115 group_status_tbl( group_status_index ).item_type := proc.item_type;
1116 group_status_tbl( group_status_index ).process_name := proc.process_name;
1117 group_status_tbl( group_status_index ).context_id := ctxts(j).context_id;
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1121 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1122 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1123 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1, param2)');
1124 fnd_message.set_token('AZW_ERROR_STMT','insert into az_processes');
1125 APP_EXCEPTION.RAISE_EXCEPTION;
1126 END;
1127
1128 ELSE
1129 BEGIN
1130 UPDATE az_processes
1131 SET context_name = ctxts(j).context_name
1132 WHERE item_type = proc.item_type
1133 AND process_name = proc.process_name
1134 AND context_id = ctxts(j).context_id
1135 AND context_name <> ctxts(j).context_name;
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1139 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1140 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1141 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1, param2)');
1142 fnd_message.set_token('AZW_ERROR_STMT','update az_processes');
1143 APP_EXCEPTION.RAISE_EXCEPTION;
1144 END;
1145 END IF;
1146 END LOOP;
1147
1148 EXCEPTION
1149 WHEN app_exception.application_exception THEN
1150 RAISE;
1151 WHEN OTHERS THEN
1152 -- DBMS_OUTPUT.PUT_LINE('error: insert_az_processes(context): ' || SQLERRM);
1153 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1154 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1155 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1156 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1, param2)');
1157 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1158 APP_EXCEPTION.RAISE_EXCEPTION;
1159
1160 END insert_az_processes;
1161
1162
1163 --
1164 -- insert_az_processes
1165 --
1166 -- Private procedure. Called by populate_processes.
1167 -- Insert the process without context into az_processes.
1168 -- Check if the process with the context is already in the table; insert
1169 -- the one not already in the table.
1170 -- The context_id in az_processes is defaulted to -1.
1171 --
1172 PROCEDURE insert_az_processes(proc IN process_rec_t) IS
1173
1174 BEGIN
1175
1176 IF process_not_found(proc, -1) THEN
1177 BEGIN
1178 INSERT INTO az_processes (item_type, process_name, context_id,
1179 display_order, complete_flag,
1180 context_type, context_name, comments, parent_id,
1181 status_code, process_type)
1182 VALUES (proc.item_type, proc.process_name, -1, proc.display_order, 'N',
1186 WHEN OTHERS THEN
1183 'NONE', NULL, NULL, proc.parent_id,
1184 'N', proc.process_type);
1185 EXCEPTION
1187 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1188 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1189 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1190 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1)');
1191 fnd_message.set_token('AZW_ERROR_STMT','insert into az_processes');
1192 APP_EXCEPTION.RAISE_EXCEPTION;
1193 END;
1194
1195 group_status_index := group_status_index + 1;
1196 group_status_tbl( group_status_index ).item_type := proc.item_type;
1197 group_status_tbl( group_status_index ).process_name := proc.process_name;
1198 group_status_tbl( group_status_index ).context_id := '-1';
1199
1200 END IF;
1201
1202 EXCEPTION
1203 WHEN app_exception.application_exception THEN
1204 RAISE;
1205 WHEN OTHERS THEN
1206 -- DBMS_OUTPUT.PUT_LINE('error: insert_az_processes: ' || SQLERRM);
1207 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1208 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1209 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1210 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1)');
1211 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1212 APP_EXCEPTION.RAISE_EXCEPTION;
1213
1214 END insert_az_processes;
1215
1216
1217 --
1218 -- populate_processes
1219 --
1220 -- Private procedure. Called by populate_az_processes.
1221 --
1222 PROCEDURE populate_processes(bg_ctx IN context_tbl_t,
1223 io_ctx IN context_tbl_t,
1224 ou_ctx IN context_tbl_t,
1225 sob_ctx IN context_tbl_t,
1226 opmcom_ctx IN context_tbl_t,
1227 opmorg_ctx IN context_tbl_t,
1228 processes IN process_tbl_t) IS
1229
1230 BEGIN
1231 -- dbms_output.put_line('populate processes');
1232
1233 FOR i IN 1..processes.COUNT LOOP
1234 IF (UPPER(processes(i).context_type) LIKE '%BG%') THEN
1235 insert_az_processes(processes(i), bg_ctx);
1236 ELSIF (UPPER(processes(i).context_type) LIKE '%IO%') THEN
1237 insert_az_processes(processes(i), io_ctx);
1238 ELSIF (UPPER(processes(i).context_type) LIKE '%OU%') THEN
1239 insert_az_processes(processes(i), ou_ctx);
1240 ELSIF (UPPER(processes(i).context_type) LIKE '%SOB%') THEN
1241 insert_az_processes(processes(i), sob_ctx);
1242 ELSIF (UPPER(processes(i).context_type) LIKE '%OPMCOM%') THEN
1243 insert_az_processes(processes(i), opmcom_ctx);
1244 ELSIF (UPPER(processes(i).context_type) LIKE '%OPMORG%') THEN
1245 insert_az_processes(processes(i), opmorg_ctx);
1246 ELSE
1247 insert_az_processes(processes(i));
1248 END IF;
1249 END LOOP;
1250 COMMIT;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 --DBMS_OUTPUT.PUT_LINE('error: populate_processes: ' || SQLERRM);
1255 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1256 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1257 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1258 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.populate_processes');
1259 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1260 APP_EXCEPTION.RAISE_EXCEPTION;
1261 END populate_processes;
1262
1263 --
1264 -- disable_invalid_opm_processes
1265 --
1266 -- Private procedure. Called by disable_invalid_processes.
1267 -- Delete processes that are not valid in OPMCOM, OPMORG
1268 -- by executing dynamic sql.
1269
1270 PROCEDURE disable_invalid_opm_processes(ctx_type IN VARCHAR2) IS
1271
1272 curs integer;
1273 rows integer;
1274 sqlstatement az_contexts_sql.SQL_STATEMENT%TYPE;
1275
1276 BEGIN
1277
1278 SELECT sql_statement
1279 INTO sqlstatement
1280 FROM az_contexts_sql
1281 WHERE context = ctx_type
1282 AND purpose = 'DELETE';
1283
1284 curs := DBMS_SQL.OPEN_CURSOR;
1285 DBMS_SQL.PARSE(curs, sqlstatement, DBMS_SQL.NATIVE);
1286 DBMS_SQL.BIND_VARIABLE(curs, ':ctx_type', ctx_type);
1287
1288 rows := DBMS_SQL.EXECUTE(curs);
1289
1290 DBMS_SQL.CLOSE_CURSOR(curs);
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 IF DBMS_SQL.IS_OPEN(curs) then
1295 DBMS_SQL.CLOSE_CURSOR(curs);
1296 END IF;
1297
1298 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1299 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1300 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1301 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.disable_invalid_opm_processes');
1302 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1303 APP_EXCEPTION.RAISE_EXCEPTION;
1304
1305 END disable_invalid_opm_processes;
1306
1307 --
1308 -- disable_invalid_processes
1309 --
1310 -- Private procedure. Called by populate_az_processes.
1311 -- Delete processes that are not valid as of the date this procedure is
1312 -- executed. These processes include those in BG, IO, OU types. SOB type
1313 -- doesn't define from/to dates.
1314 --
1315 PROCEDURE disable_invalid_processes IS
1316
1320 v_process_name az_processes.process_name%TYPE;
1317 cnt PLS_INTEGER DEFAULT 0;
1318 v_installed_prods PLS_INTEGER DEFAULT 0;
1319 v_item_type az_processes.item_type%TYPE;
1321
1322 CURSOR invalid_bg_processes_cursor IS
1323 SELECT ap.item_type, ap.process_name, ap.context_id
1324 FROM az_processes ap,
1325 per_business_groups pbg
1326 WHERE ap.context_type = 'BG'
1327 AND ap.process_type = g_current_mode
1328 AND ap.context_id > -1
1329 AND ap.context_id = pbg.organization_id
1330 AND (pbg.date_to IS NOT NULL
1331 AND pbg.date_from IS NOT NULL
1332 AND (pbg.date_from > SYSDATE
1333 OR pbg.date_to < SYSDATE));
1334
1335 CURSOR invalid_io_processes_cursor IS
1336 SELECT ap.item_type, ap.process_name, ap.context_id
1337 FROM az_processes ap,
1338 org_organization_definitions ood
1339 WHERE ap.context_type = 'IO'
1340 AND ap.process_type = g_current_mode
1341 AND ap.context_id > -1
1342 AND ap.context_id = ood.organization_id
1343 AND (ood.user_definition_enable_date IS NOT NULL
1344 AND ood.disable_date IS NOT NULL
1345 AND (ood.user_definition_enable_date > SYSDATE
1346 OR ood.disable_date < SYSDATE));
1347
1348 CURSOR invalid_ou_processes_cursor IS
1349 SELECT ap.item_type, ap.process_name, ap.context_id
1350 FROM az_processes ap,
1351 hr_operating_units hou
1352 WHERE ap.context_type = 'OU'
1353 AND ap.process_type = g_current_mode
1354 AND ap.context_id > -1
1355 AND ap.context_id = hou.organization_id
1356 AND (hou.date_from IS NOT NULL
1357 AND hou.date_to IS NOT NULL
1358 AND (hou.date_from > SYSDATE
1359 OR hou.date_to < SYSDATE));
1360
1361 BEGIN
1362 -- dbms_output.put_line('disable invalid process');
1363 -- get rid of non-existent BG processes from az_processes
1364 DELETE from az_processes
1365 WHERE context_type = 'BG'
1366 AND context_id not in
1367 ( select distinct organization_id
1368 from per_business_groups);
1369 -- get rid of non-existent IO processes from az_processes
1370 DELETE from az_processes
1371 WHERE context_type = 'IO'
1372 AND context_id not in
1373 ( select distinct organization_id
1374 from org_organization_definitions);
1375 -- get rid of non-existent OU processes from az_processes
1376 DELETE from az_processes
1377 WHERE context_type = 'OU'
1378 AND context_id not in
1379 ( select distinct organization_id
1380 from hr_operating_units);
1381
1382 disable_invalid_opm_processes('OPMCOM');
1383 -- disable_invalid_opm_processes('OPMORG');
1384
1385
1386 BEGIN
1387 SELECT COUNT(*)
1388 INTO cnt
1389 FROM az_processes;
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1393 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1394 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1395 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.disable_invalid_processes');
1396 fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_processes');
1397 APP_EXCEPTION.RAISE_EXCEPTION;
1398 END ;
1399
1400 IF (cnt <> 0) THEN
1401 BEGIN
1402 FOR invalid_process IN invalid_bg_processes_cursor LOOP
1403 DELETE from az_processes ap
1404 WHERE ap.item_type = invalid_process.item_type
1405 AND ap.process_name = invalid_process.process_name
1406 AND ap.context_id = invalid_process.context_id;
1407
1408 group_status_index := group_status_index + 1;
1409 group_status_tbl( group_status_index ).item_type := invalid_process.item_type;
1410 group_status_tbl( group_status_index ).process_name:=invalid_process.process_name;
1411 group_status_tbl( group_status_index ).context_id := invalid_process.context_id;
1412
1413 END LOOP;
1414 COMMIT;
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 ROLLBACK;
1418 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1419 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1420 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1421 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.disable_invalid_processes');
1422 fnd_message.set_token('AZW_ERROR_STMT','CURSOR invalid_bg_processes_cursor');
1423 APP_EXCEPTION.RAISE_EXCEPTION;
1424 END ;
1425
1426 BEGIN
1427 FOR invalid_process IN invalid_io_processes_cursor LOOP
1428 DELETE from az_processes ap
1429 WHERE ap.item_type = invalid_process.item_type
1430 AND ap.process_name = invalid_process.process_name
1431 AND ap.context_id = invalid_process.context_id;
1432
1433 group_status_index := group_status_index + 1;
1434 group_status_tbl( group_status_index ).item_type := invalid_process.item_type;
1435 group_status_tbl( group_status_index ).process_name := invalid_process.process_name;
1436 group_status_tbl( group_status_index ).context_id := invalid_process.context_id;
1437
1438 END LOOP;
1439 COMMIT;
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 ROLLBACK;
1446 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.disable_invalid_processes');
1443 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1444 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1445 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1447 fnd_message.set_token('AZW_ERROR_STMT','CURSOR invalid_io_processes_cursor');
1448 APP_EXCEPTION.RAISE_EXCEPTION;
1449 END ;
1450
1451 BEGIN
1452 FOR invalid_process IN invalid_ou_processes_cursor LOOP
1453 DELETE from az_processes ap
1454 WHERE ap.item_type = invalid_process.item_type
1455 AND ap.process_name = invalid_process.process_name
1456 AND ap.context_id = invalid_process.context_id;
1457
1458 group_status_index := group_status_index + 1;
1459 group_status_tbl( group_status_index ).item_type := invalid_process.item_type;
1460 group_status_tbl( group_status_index ).process_name := invalid_process.process_name;
1461 group_status_tbl( group_status_index ).context_id := invalid_process.context_id;
1462
1463 END LOOP;
1464 COMMIT;
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 ROLLBACK;
1468 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1469 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1470 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1471 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.disable_invalid_processes');
1472 fnd_message.set_token('AZW_ERROR_STMT','CURSOR invalid_ou_processes_cursor');
1473 APP_EXCEPTION.RAISE_EXCEPTION;
1474 END ;
1475
1476 END IF;
1477
1478 EXCEPTION
1479 WHEN app_exception.application_exception THEN
1480 RAISE;
1481 WHEN OTHERS THEN
1482 -- DBMS_OUTPUT.PUT_LINE('error: disable_invalid_processes: ' || SQLERRM);
1483 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1484 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1485 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1486 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.disable_invalid_processes');
1487 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1488 APP_EXCEPTION.RAISE_EXCEPTION;
1489
1490
1491
1492 END disable_invalid_processes;
1493
1494
1495 --
1496 -- POPULATE_AZ_PROCESSES
1497 --
1498 -- Public procedure. Called by AZW_HIER.get_hierarchy each time
1499 -- hierarchy is shown.
1500 --
1501 PROCEDURE populate_az_processes IS
1502
1503 bg_ctx context_tbl_t;
1504 io_ctx context_tbl_t;
1505 ou_ctx context_tbl_t;
1506 sob_ctx context_tbl_t;
1507 processes process_tbl_t;
1508
1509 opmcom_ctx context_tbl_t;
1510 opmorg_ctx context_tbl_t;
1511
1512
1513 BEGIN
1514 -- dbms_output.put_line('populate az_processes table');
1515
1516 g_current_mode := fnd_profile.value('AZ_CURRENT_MODE');
1517
1518 -- get the current session language for task_init
1519 BEGIN
1520 select distinct language_code, nls_language
1521 into v_language_code, v_language
1522 from fnd_languages
1523 where NLS_LANGUAGE =
1524 SUBSTR(USERENV('LANGUAGE'), 1, INSTR(USERENV('LANGUAGE'), '_')-1);
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1528 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1529 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1530 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.populate_az_processes');
1531 fnd_message.set_token('AZW_ERROR_STMT','select language_code .. from fnd_languages');
1532 APP_EXCEPTION.RAISE_EXCEPTION;
1533 END;
1534 get_context('BG', bg_ctx);
1535 get_context('IO', io_ctx);
1536 get_context('OU', ou_ctx);
1537 get_context('SOB', sob_ctx);
1538
1539 get_context('OPMCOM', opmcom_ctx);
1540 get_context('OPMORG', opmorg_ctx);
1541
1542 -- dbms_output.put_line('populate contexts');
1543
1544 get_processes(processes);
1545 -- dbms_output.put_line('done get_processes');
1546
1547 group_status_index := 0;
1548 group_status_tbl.delete;
1549
1550 populate_processes(bg_ctx, io_ctx, ou_ctx, sob_ctx, opmcom_ctx, opmorg_ctx, processes);
1551 -- dbms_output.put_line('done populate_processes ');
1552
1553 -- check for group status
1554 FOR i IN 1..group_status_index LOOP
1555 update_hierarchy_status(group_status_tbl(i).item_type,
1556 group_status_tbl(i).process_name,
1557 group_status_tbl(i).context_id);
1558 END LOOP;
1559 group_status_index := 0;
1560 group_status_tbl.delete;
1561
1562 disable_invalid_processes;
1563 -- dbms_output.put_line('Done disable_invalid_processes');
1564
1565 -- check for group status
1566 FOR i IN 1..group_status_index LOOP
1567 update_hierarchy_status(group_status_tbl(i).item_type,
1568 group_status_tbl(i).process_name,
1569 group_status_tbl(i).context_id);
1570 END LOOP;
1571
1572 EXCEPTION
1573 WHEN app_exception.application_exception THEN
1574 RAISE;
1575 WHEN OTHERS THEN
1576 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1577 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1578 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1579 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.populate_az_processes');
1580 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1581 APP_EXCEPTION.RAISE_EXCEPTION;
1585
1582
1583 END populate_az_processes;
1584
1586 --
1587 -- UPDATE_PROCESS_PHASE
1588 --
1589 -- Public procedure. Called from the trigger in response to an event in
1590 -- a form.
1591 -- Updates phase information for a given process
1592 --
1593 PROCEDURE update_process_phase(node_id IN VARCHAR2, value IN NUMBER) IS
1594
1595 p_type az_processes.item_type%TYPE;
1596 p_name az_processes.process_name%TYPE;
1597 ctx_id az_processes.context_id%TYPE;
1598
1599 BEGIN
1600 p_type := parse_item_type(node_id);
1601 p_name := parse_process_name(node_id);
1602 ctx_id := parse_context_id(node_id);
1603
1604 /*
1605 UPDATE az_processes ap
1606 SET ap.phase = value
1607 WHERE ap.item_type = p_type
1608 AND ap.process_name = p_name
1609 AND ap.context_id = TO_NUMBER(ctx_id);
1610 COMMIT;
1611
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 -- DBMS_OUTPUT.PUT_LINE('error: update_process_phase: ' || SQLERRM);
1615 RAISE;
1616 */
1617 END update_process_phase;
1618
1619
1620 --
1621 -- UPDATE_PROCESS_COMMENTS
1622 --
1623 -- Public procedure. Called from the trigger in response to an event in
1624 -- a form.
1625 -- Updates phase information for a given process
1626 --
1627 PROCEDURE update_process_comments(node_id IN VARCHAR2, value IN VARCHAR2) IS
1628
1629 p_type az_processes.item_type%TYPE;
1630 p_name az_processes.process_name%TYPE;
1631 ctx_id az_processes.context_id%TYPE;
1632
1633 BEGIN
1634 p_type := parse_item_type(node_id);
1635 p_name := parse_process_name(node_id);
1636 ctx_id := parse_context_id(node_id);
1637
1638 BEGIN
1639 UPDATE az_processes ap
1640 SET ap.comments = value
1641 WHERE ap.item_type = p_type
1642 AND ap.process_name = p_name
1643 AND ap.context_id = TO_NUMBER(ctx_id);
1644 COMMIT;
1645 EXCEPTION
1646 WHEN OTHERS THEN
1647 ROLLBACK;
1648 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1649 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1650 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1651 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_comments');
1652 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1653 APP_EXCEPTION.RAISE_EXCEPTION;
1654 END;
1655
1656 EXCEPTION
1657 WHEN app_exception.application_exception THEN
1658 APP_EXCEPTION.RAISE_EXCEPTION;
1659 WHEN OTHERS THEN
1660 -- DBMS_OUTPUT.PUT_LINE('error: update_process_comments: ' || SQLERRM);
1661 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1662 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1663 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1664 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_comments');
1665 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1666 APP_EXCEPTION.RAISE_EXCEPTION;
1667 END update_process_comments;
1668
1669
1670 --
1671 -- update_groups_status
1672 --
1673 -- Private procedure. Called by update_hierarchy_status.
1674 -- Given a group id, finds if all siblings are completed. If completed,
1675 -- sets the parent complete status to 'Y' and continues rolling up the
1676 -- hierarchy. When first 'N' is found, stop checking status and sets
1677 -- all ancestors' complete status to 'N'.
1678 --
1679 PROCEDURE update_groups_status(p_group_id IN VARCHAR2) IS
1680
1681 v_parent_id az_groups.group_id%TYPE;
1682 v_cnt NUMBER DEFAULT 0;
1683 v_total_kids NUMBER DEFAULT 0;
1684
1685 BEGIN
1686 BEGIN
1687 SELECT ag.hierarchy_parent_id
1688 INTO v_parent_id
1689 FROM az_groups ag
1690 WHERE ag.group_id = p_group_id
1691 AND ag.process_type = g_current_mode;
1692 EXCEPTION
1693 WHEN NO_DATA_FOUND THEN
1694 v_parent_id := NULL;
1695 WHEN OTHERS THEN
1696 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1697 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1698 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1699 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1700 fnd_message.set_token('AZW_ERROR_STMT','select hierarchy_parent_id from az_groups');
1701 APP_EXCEPTION.RAISE_EXCEPTION;
1702 END;
1703
1704 WHILE (v_parent_id IS NOT NULL) LOOP
1705
1706 BEGIN
1707 SELECT COUNT(*)
1708 INTO v_total_kids
1709 FROM az_groups ag
1710 WHERE ag.hierarchy_parent_id = v_parent_id
1711 AND ag.process_type = g_current_mode;
1712 EXCEPTION
1713 WHEN OTHERS THEN
1714 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1715 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1716 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1717 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1718 fnd_message.set_token('AZW_ERROR_STMT','select count(*) into v_total_kids from az_groups');
1719 APP_EXCEPTION.RAISE_EXCEPTION;
1720 END;
1721
1722 BEGIN
1723 SELECT COUNT(*)
1724 INTO v_cnt
1725 FROM az_groups ag
1726 WHERE ag.hierarchy_parent_id = v_parent_id
1730 WHEN OTHERS THEN
1727 AND ag.process_type = g_current_mode
1728 AND ag.complete_flag <> 'Y';
1729 EXCEPTION
1731 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1732 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1733 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1734 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1735 fnd_message.set_token('AZW_ERROR_STMT','select count(*) into v_cnt from az_groups');
1736 APP_EXCEPTION.RAISE_EXCEPTION;
1737 END;
1738
1739 IF (v_total_kids <> 0 AND v_cnt = 0) THEN
1740 BEGIN
1741 UPDATE az_groups
1742 SET complete_flag = 'Y'
1743 WHERE group_id = v_parent_id
1744 AND process_type = g_current_mode;
1745 COMMIT;
1746 EXCEPTION
1747 WHEN OTHERS THEN
1748 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1749 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1750 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1751 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1752 fnd_message.set_token('AZW_ERROR_STMT','update az_groups set complete_flag = Y');
1753 APP_EXCEPTION.RAISE_EXCEPTION;
1754 END;
1755
1756 BEGIN
1757 SELECT ag.hierarchy_parent_id
1758 INTO v_parent_id
1759 FROM az_groups ag
1760 WHERE ag.group_id = v_parent_id
1761 AND ag.process_type = g_current_mode;
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1765 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1766 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1767 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1768 fnd_message.set_token('AZW_ERROR_STMT','select hierarchy_parent_id from az_groups');
1769 APP_EXCEPTION.RAISE_EXCEPTION;
1770 END;
1771
1772 ELSIF (v_total_kids <> 0 AND v_cnt <> 0) THEN
1773 WHILE (v_parent_id IS NOT NULL) LOOP
1774
1775 BEGIN
1776 UPDATE az_groups
1777 SET complete_flag = 'N'
1778 WHERE group_id = v_parent_id
1779 AND process_type = g_current_mode;
1780 COMMIT;
1781 EXCEPTION
1782 WHEN OTHERS THEN
1783 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1784 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1785 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1786 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1787 fnd_message.set_token('AZW_ERROR_STMT','update az_groups set complete_flag = N');
1788 APP_EXCEPTION.RAISE_EXCEPTION;
1789 END;
1790
1791 BEGIN
1792 SELECT ag.hierarchy_parent_id
1793 INTO v_parent_id
1794 FROM az_groups ag
1795 WHERE ag.group_id = v_parent_id
1796 AND ag.process_type = g_current_mode;
1797 EXCEPTION
1798 WHEN OTHERS THEN
1799 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1800 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1801 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1802 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1803 fnd_message.set_token('AZW_ERROR_STMT','select hierarchy_parent_id from az_groups');
1804 APP_EXCEPTION.RAISE_EXCEPTION;
1805 END;
1806 END LOOP;
1807
1808 END IF;
1809
1810 END LOOP;
1811
1812 EXCEPTION
1813 WHEN app_exception.application_exception THEN
1814 RAISE ;
1815 WHEN OTHERS THEN
1816 -- DBMS_OUTPUT.PUT_LINE('error: update_groups_status ' || SQLERRM);
1817 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1818 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1819 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1820 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
1821 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1822 APP_EXCEPTION.RAISE_EXCEPTION;
1823
1824 END update_groups_status;
1825
1826
1827 --
1828 -- update_hierarchy_status
1829 --
1830 -- Private procedure. Called by update_process_status.
1831 -- Given an process id (item_type, process_name, context_id),
1832 -- updates all the ancestors' complete status.
1833 --
1834 PROCEDURE update_hierarchy_status(p_item_type IN VARCHAR2,
1835 p_process_name IN VARCHAR2,
1836 p_context_id IN VARCHAR2) IS
1837
1838 v_group_id az_groups.group_id%TYPE;
1839 v_cnt NUMBER DEFAULT 0;
1840 v_status VARCHAR2(1);
1841
1842 BEGIN
1843 BEGIN
1844 SELECT ap.parent_id
1845 INTO v_group_id
1846 FROM az_processes ap
1847 WHERE ap.item_type = p_item_type
1848 AND ap.process_name = p_process_name
1849 AND ap.process_type = g_current_mode
1850 AND ap.context_id = p_context_id;
1851 EXCEPTION
1852 WHEN NO_DATA_FOUND THEN
1853 v_group_id := NULL;
1854 WHEN OTHERS THEN
1855 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1856 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1857 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1858 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
1862
1859 fnd_message.set_token('AZW_ERROR_STMT','select parent_id from az_processes');
1860 APP_EXCEPTION.RAISE_EXCEPTION;
1861 END;
1863 IF v_group_id is null THEN
1864 NULL;
1865 ELSE
1866 BEGIN
1867 SELECT COUNT(*)
1868 INTO v_cnt
1869 FROM az_processes ap
1870 WHERE ap.status_code <> 'C'
1871 AND ap.process_type = g_current_mode
1872 AND ap.parent_id = v_group_id;
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1876 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1877 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1878 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
1879 fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_processes');
1880 APP_EXCEPTION.RAISE_EXCEPTION;
1881 END;
1882
1883 IF (v_cnt = 0) THEN
1884 v_status := 'Y';
1885 ELSE
1886 v_status := 'N';
1887 END IF;
1888
1889 BEGIN
1890 UPDATE az_groups ag
1891 SET ag.complete_flag = v_status
1892 WHERE ag.group_id = v_group_id
1893 AND ag.process_type = g_current_mode;
1894 COMMIT;
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1898 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1899 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1900 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
1901 fnd_message.set_token('AZW_ERROR_STMT','update az_groups set complete_flag');
1902 APP_EXCEPTION.RAISE_EXCEPTION;
1903 END;
1904
1905 -- update groups complete status, starting with the leaf group id
1906 update_groups_status(v_group_id);
1907
1908 END IF;
1909
1910 EXCEPTION
1911 WHEN app_exception.application_exception THEN
1912 RAISE;
1913 WHEN OTHERS THEN
1914 -- DBMS_OUTPUT.PUT_LINE('error: update_hierarchy_status ' || SQLERRM);
1915 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1916 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1917 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1918 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
1919 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
1920 APP_EXCEPTION.RAISE_EXCEPTION;
1921 END update_hierarchy_status;
1922
1923
1924 --
1925 -- UPDATE_PROCESS_STATUS
1926 --
1927 -- Public procedure.
1928 -- Updates phase information for a given process
1929 --
1930 PROCEDURE update_process_status(node_id IN VARCHAR2, value IN VARCHAR2) IS
1931
1932 p_type az_processes.item_type%TYPE;
1933 p_name az_processes.process_name%TYPE;
1934 ctx_id az_processes.context_id%TYPE;
1935 v_count NUMBER(30);
1936 BEGIN
1937 p_type := parse_item_type(node_id);
1938 p_name := parse_process_name(node_id);
1939 ctx_id := parse_context_id(node_id);
1940 -- get count of tasks for this process
1941 BEGIN
1942 SELECT count(*)
1943 INTO v_count
1944 FROM wf_items wfi, wf_item_attribute_values wiav
1945 WHERE wfi.item_type = p_type
1946 AND wfi.root_activity = p_name
1947 AND wiav.item_type = p_type
1948 AND wiav.item_key = wfi.item_key
1949 AND wiav.name = 'AZW_IA_CTXT_ID'
1950 AND wiav.text_value = ctx_id;
1951 EXCEPTION
1952 WHEN OTHERS THEN
1953 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1954 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1955 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1956 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
1957 fnd_message.set_token('AZW_ERROR_STMT','select count(*) into v_count from wf_items,wf_item_attribute_values');
1958 APP_EXCEPTION.RAISE_EXCEPTION;
1959 END;
1960
1961 IF (value <> 'N') THEN
1962 BEGIN
1963 UPDATE az_processes ap
1964 SET ap.status_code = value
1965 WHERE ap.item_type = p_type
1966 AND ap.process_name = p_name
1967 AND ap.context_id = TO_NUMBER(ctx_id);
1968 COMMIT;
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1972 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1973 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1974 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
1975 fnd_message.set_token('AZW_ERROR_STMT','update az_processes set status_code = value');
1976 APP_EXCEPTION.RAISE_EXCEPTION;
1977 END;
1978 ELSE
1979 IF (v_count <> 0) THEN
1980 BEGIN
1981 UPDATE az_processes ap
1982 SET ap.status_code = 'A'
1983 WHERE ap.item_type = p_type
1984 AND ap.process_name = p_name
1985 AND ap.context_id = TO_NUMBER(ctx_id);
1986 COMMIT;
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
1990 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
1991 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
1995 END;
1992 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
1993 fnd_message.set_token('AZW_ERROR_STMT','update az_processes set status_code = A');
1994 APP_EXCEPTION.RAISE_EXCEPTION;
1996 ELSE
1997 BEGIN
1998 UPDATE az_processes ap
1999 SET ap.status_code = 'N'
2000 WHERE ap.item_type = p_type
2001 AND ap.process_name = p_name
2002 AND ap.context_id = TO_NUMBER(ctx_id);
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2006 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2007 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2008 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
2009 fnd_message.set_token('AZW_ERROR_STMT','update az_processes set status_code = N');
2010 APP_EXCEPTION.RAISE_EXCEPTION;
2011 END;
2012 END IF; /* for process complete unchecked */
2013 END IF;
2014 -- update status for all ancestors
2015 update_hierarchy_status(p_type, p_name, ctx_id);
2016
2017 EXCEPTION
2018 WHEN app_exception.application_exception THEN
2019 APP_EXCEPTION.RAISE_EXCEPTION;
2020 WHEN OTHERS THEN
2021 -- DBMS_OUTPUT.PUT_LINE('error: complete_process_status: ' || SQLERRM);
2022 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2023 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2024 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2025 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
2026 fnd_message.set_token('AZW_ERROR_STMT','select into v_ctx_id');
2027 APP_EXCEPTION.RAISE_EXCEPTION;
2028 END update_process_status;
2029
2030
2031 --
2032 -- AIWStart
2033 --
2034 -- Private procedure. Called by az_start_task.
2035 -- Starts a task.
2036 --
2037 PROCEDURE AIWStart(
2038 p_itemtype in varchar2,
2039 p_workflow in varchar2,
2040 p_context in varchar2,
2041 p_role in varchar2,
2042 p_context_level in varchar2,
2043 p_org_id in number,
2044 p_org_code in varchar2,
2045 p_coa_id in number,
2046 p_context_id in number) IS
2047
2048 itemkey varchar2(240);
2049 murl varchar2(2000);
2050 prev_step varchar2(2000);
2051
2052 BEGIN
2053
2054 select AZ_WF_ITEMKEY_S.nextval into itemkey from dual;
2055
2056 if (wf_item.Item_Exist(p_itemtype, itemkey)) then
2057 --wf_engine.handleerror(p_itemtype, itemkey, 'START', 'RETRY', null);
2058 null;
2059 else
2060
2061 BEGIN
2062 wf_engine.CreateProcess(p_itemtype, itemkey, p_workflow);
2063 --Set the context, role, comments and priority here
2064 wf_engine.SetItemAttrText(p_itemtype, itemkey,
2065 'AZW_IA_CTXTNAME', p_context);
2066
2067 if(p_context_level = 'IO') then
2068 wf_engine.SetItemAttrText(p_itemtype, itemkey,
2069 'AZW_IA_ORG_CODE', p_org_code);
2070 wf_engine.SetItemAttrNumber(p_itemtype, itemkey,
2071 'AZW_IA_ORG_ID', p_org_id);
2072 wf_engine.SetItemAttrNumber(p_itemtype, itemkey,
2073 'AZW_IA_COA_ID', p_coa_id);
2074 end if;
2075 EXCEPTION
2076 WHEN app_exception.application_exception THEN
2077 APP_EXCEPTION.RAISE_EXCEPTION;
2078 WHEN OTHERS THEN
2079 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2080 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2081 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2082 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.AIWStart');
2083 fnd_message.set_token('AZW_ERROR_STMT','BLOCK 1');
2084 APP_EXCEPTION.RAISE_EXCEPTION;
2085 END;
2086
2087 BEGIN
2088 if(p_context is null) then
2089 wf_engine.SetItemAttrText(p_itemtype, itemkey,
2090 'AZW_IA_CTXTNAME', 'NONE');
2091 end if;
2092 wf_engine.SetItemAttrText(p_itemtype, itemkey, 'AZW_IA_ROLE', p_role);
2093
2094 wf_engine.AddItemAttr(p_itemtype, itemkey, 'AZW_IA_CTXT_ID');
2095 wf_engine.SetItemAttrText(p_itemtype, itemkey, 'AZW_IA_CTXT_ID',
2096 p_context_id);
2097 wf_engine.AddItemAttr(p_itemtype, itemkey, 'AZW_IA_NEW_CTXT_TYPE');
2098 wf_engine.SetItemAttrText(p_itemtype, itemkey,
2099 'AZW_IA_NEW_CTXT_TYPE', p_context_level);
2100
2101 AZW_UTIL.UpdateDocUrl(p_itemtype, p_workflow);
2102
2103 wf_engine.StartProcess(p_itemtype, itemkey);
2104 EXCEPTION
2105 WHEN app_exception.application_exception THEN
2106 APP_EXCEPTION.RAISE_EXCEPTION;
2107 WHEN OTHERS THEN
2108 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2109 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2110 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2111 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.AIWStart');
2112 fnd_message.set_token('AZW_ERROR_STMT','BLOCK 2');
2113 APP_EXCEPTION.RAISE_EXCEPTION;
2114 END;
2115 end if;
2116
2117 commit;
2118 v_new_task_key := itemkey;
2119 EXCEPTION
2120 WHEN app_exception.application_exception THEN
2121 APP_EXCEPTION.RAISE_EXCEPTION;
2122 WHEN OTHERS THEN
2126 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.AIWStart');
2123 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2124 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2125 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2127 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
2128 APP_EXCEPTION.RAISE_EXCEPTION;
2129
2130 END AIWStart;
2131
2132
2133 --
2134 -- get_old_task_label
2135 --
2136 -- Private procedure. Returns old task's label.
2137 -- Called by get_task_label.
2138 --
2139 FUNCTION get_old_task_label(p_type VARCHAR2, p_key VARCHAR2, p_name VARCHAR2,
2140 p_role VARCHAR2) RETURN VARCHAR2
2141 IS
2142 v_begin_date wf_item_activity_statuses.begin_date%TYPE;
2143 v_display_status wf_resources.text%TYPE;
2144 v_label az_webform_messages.mesg%TYPE;
2145 v_task_tot_days NUMBER(5) := 0;
2146 v_count NUMBER(5) := 0;
2147 v_skip_count NUMBER(4) := 0;
2148 v_task_status wf_item_activity_statuses.activity_status%TYPE;
2149
2150 CURSOR az_tasks_days_cursor IS
2151 SELECT round( months_between(sysdate, wfi.begin_date)* 31),
2152 wias.activity_status
2153 FROM wf_items wfi, wf_process_activities wpa,
2154 wf_item_activity_statuses wias
2155 WHERE wfi.item_type = p_type
2156 AND wfi.root_activity = p_name
2157 AND wfi.item_key = p_key
2158 AND wias.item_type = wfi.item_type
2159 AND wias.item_key = wfi.item_key
2160 AND wpa.instance_id = wias.process_activity
2161 AND wpa.activity_name = wfi.root_activity
2162 AND wpa.process_item_type =p_type
2163 AND wpa.process_name = 'ROOT';
2164
2165 CURSOR az_task_display_cursor IS
2166 SELECT wav.display_name,
2167 wias.begin_date
2168 FROM wf_process_activities wpa, wf_item_activity_statuses wias,
2169 wf_activities_vl wav
2170 WHERE wias.item_type = p_type
2171 AND wias.item_key = p_key
2172 AND wias.process_activity = wpa.instance_id
2173 AND wpa.activity_name = wav.name
2174 AND wpa.activity_item_type = wav.item_type
2175 AND wpa.process_name <> 'ROOT'
2176 AND wpa.activity_name <> 'START'
2177 AND wav.begin_date is not NULL
2178 AND wav.end_date is NULL
2179 AND wav.type = 'NOTICE'
2180 ORDER BY wias.begin_date desc;
2181
2182 CURSOR az_steps_count_cursor IS
2183 SELECT COUNT(*)
2184 FROM wf_item_activity_statuses wias, wf_notification_attributes wna,
2185 wf_notifications wn
2186 WHERE wias.item_type = p_type
2187 AND wias.item_key = p_key
2188 AND wias.notification_id IS NOT NULL
2189 AND wna.notification_id = wias.notification_id
2190 AND wn.notification_id = wna.notification_id
2191 AND wn.status = 'CLOSED'
2192 AND wna.name = 'RESULT'
2193 AND wna.text_value LIKE '%DONE%';
2194
2195 CURSOR az_skip_count_cursor IS
2196 SELECT COUNT(*)
2197 FROM wf_item_activity_statuses wias, wf_notification_attributes wna,
2198 wf_notifications wn
2199 WHERE wias.item_type = p_type
2200 AND wias.item_key = p_key
2201 AND wias.notification_id is not NULL
2202 AND wna.notification_id = wias.notification_id
2203 AND wn.notification_id = wna.notification_id
2204 AND wn.status = 'CLOSED'
2205 AND wna.name = 'RESULT'
2206 AND wna.text_value like '%SKIP%';
2207
2208 BEGIN
2209 task_init;
2210
2211 OPEN az_task_display_cursor;
2212 FETCH az_task_display_cursor
2213 INTO v_label, v_begin_date;
2214
2215 OPEN az_tasks_days_cursor;
2216 FETCH az_tasks_days_cursor
2217 INTO v_task_tot_days, v_task_status;
2218
2219 OPEN az_steps_count_cursor;
2220 FETCH az_steps_count_cursor
2221 INTO v_count;
2222
2223 OPEN az_skip_count_cursor;
2224 FETCH az_skip_count_cursor
2225 INTO v_skip_count;
2226
2227 IF (v_task_status = 'COMPLETE') THEN
2228 v_label := p_role || ' - '|| to_char(v_task_tot_days) || ' ' ||
2229 v_days || ': ' || to_char(v_count) || ' ' || v_done ||
2230 ' , '|| to_char(v_skip_count) || ' ' || v_skip;
2231 ELSE
2232 v_label := v_label || ': ' || p_role || ' - '||
2233 to_char(v_task_tot_days) || ' ' || v_days || ': ' ||
2234 to_char(v_count) || ' ' || v_done || ' , '||
2235 to_char(v_skip_count) || ' ' || v_skip;
2236 END IF;
2237
2238 CLOSE az_task_display_cursor;
2239 CLOSE az_tasks_days_cursor;
2240 CLOSE az_steps_count_cursor;
2241 CLOSE az_skip_count_cursor;
2242
2243 RETURN v_label;
2244
2245 EXCEPTION
2246 WHEN app_exception.application_exception THEN
2247 RAISE;
2248 WHEN OTHERS THEN
2249 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2250 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2254 APP_EXCEPTION.RAISE_EXCEPTION;
2251 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2252 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_old_task_label');
2253 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
2255 END get_old_task_label;
2256
2257
2258 --
2259 -- get_new_task_mesg
2260 --
2261 -- Private procedure. Returns new task's message.
2262 -- Called by az_start_task.
2263 --
2264 FUNCTION get_new_task_mesg(p_type VARCHAR2, p_name VARCHAR2,
2265 p_role VARCHAR2, p_ctxt_id VARCHAR2)
2266 RETURN VARCHAR2
2267 IS
2268 label az_webform_messages.mesg%TYPE;
2269 v_begin_date wf_item_activity_statuses.begin_date%TYPE;
2270 v_display_status wf_resources.text%TYPE;
2271 v_mesg az_webform_messages.mesg%TYPE;
2272 v_node_id VARCHAR2(300);
2273 v_parent_node_id VARCHAR2(300);
2274
2275 CURSOR az_task_display_cursor IS
2276 SELECT wav.display_name,
2277 wias.begin_date
2278 FROM wf_process_activities wpa, wf_item_activity_statuses wias,
2279 wf_activities_vl wav
2280 WHERE wias.item_type = p_type
2281 AND wias.item_key = v_new_task_key
2282 AND wias.process_activity = wpa.instance_id
2283 AND wpa.activity_name = wav.name
2284 AND wpa.activity_item_type = wav.item_type
2285 AND wpa.process_name <> 'ROOT'
2286 AND wpa.activity_name <> 'START'
2287 AND wav.type = 'NOTICE'
2288 AND wav.end_date is NULL
2289 AND wav.begin_date is not NULL
2290 ORDER BY wias.begin_date desc;
2291
2292 BEGIN
2293 task_init;
2294
2295 OPEN az_task_display_cursor;
2296 FETCH az_task_display_cursor
2297 INTO label, v_begin_date;
2298
2299 v_node_id := p_type || '.' || p_name || '.'|| p_ctxt_id ||
2300 '.' || v_new_task_key;
2301
2302 v_parent_node_id := p_type || '.' || p_name || '.'|| p_ctxt_id;
2303
2304 label := label || ': ' || p_role || ' - '|| '0' || ' '
2305 || v_days || ': ' || '0 ' || v_done || ' , '||
2306 '0' || ' ' || v_skip;
2307
2308 v_mesg := label || msg_delimiter || v_node_id || msg_delimiter ||
2309 v_parent_node_id || msg_delimiter || 'I' || msg_delimiter
2310 || 'INCOMPLETE' || msg_delimiter || '100000'
2311 || msg_delimiter || msg_delimiter;
2312
2313 CLOSE az_task_display_cursor;
2314 RETURN v_mesg;
2315
2316 EXCEPTION
2317 WHEN app_exception.application_exception THEN
2318 RAISE;
2319 WHEN OTHERS THEN
2320 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2321 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2322 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2323 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_new_task_mesg');
2324 fnd_message.set_token('AZW_ERROR_STMT','CURSOR az_task_display_cursor');
2325 APP_EXCEPTION.RAISE_EXCEPTION;
2326 END get_new_task_mesg;
2327
2328
2329 --
2330 -- AZ_START_TASK
2331 --
2332 -- Public procedure. Starts a new task and passes its display string.
2333 --
2334 FUNCTION az_start_task(node_id IN VARCHAR2, role IN VARCHAR2) RETURN VARCHAR2
2335 IS
2336
2337 p_type az_processes.item_type%TYPE;
2338 p_name az_processes.process_name%TYPE;
2339 ctx_id az_processes.context_id%TYPE;
2340 ctx_type az_processes.context_type%TYPE;
2341 ctx_name az_processes.context_name%TYPE;
2342 org_code org_access_view.organization_code%TYPE DEFAULT NULL;
2343 coa_id org_access_view.chart_of_accounts_id%TYPE DEFAULT NULL;
2344 p_role wf_roles.name%TYPE;
2345 msg az_webform_messages.mesg%TYPE;
2346
2347 BEGIN
2348
2349 p_type := parse_item_type(node_id);
2350 p_name := parse_process_name(node_id);
2351 ctx_id := parse_context_id(node_id);
2352 ctx_type := get_context_type(p_type, p_name, to_number(ctx_id));
2353 ctx_name := get_context_name(ctx_type, to_number(ctx_id));
2354
2355 p_role := role;
2356
2357 BEGIN
2358 IF(ctx_type = 'IO') THEN
2359 SELECT distinct organization_code, chart_of_accounts_id
2360 INTO org_code, coa_id
2361 FROM org_access_view
2362 WHERE organization_id = ctx_id;
2363 END IF;
2364 EXCEPTION
2365 WHEN OTHERS THEN
2366 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2367 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2368 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2369 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.az_start_task');
2370 fnd_message.set_token('AZW_ERROR_STMT','select organization_code from org_access_view');
2371 APP_EXCEPTION.RAISE_EXCEPTION;
2372 END;
2373
2374 AIWStart(p_type, p_name, ctx_name, p_role ,ctx_type, ctx_id, org_code,
2375 coa_id, ctx_id);
2376 msg := get_new_task_mesg(p_type, p_name, p_role, ctx_id);
2377 /* Now update the status */
2378 update_process_status(node_id, 'A');
2379 COMMIT;
2380 RETURN msg;
2384 WHEN OTHERS THEN
2381 EXCEPTION
2382 WHEN app_exception.application_exception THEN
2383 APP_EXCEPTION.RAISE_EXCEPTION;
2385 ROLLBACK;
2386 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2387 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2388 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2389 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.az_start_task');
2390 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
2391 APP_EXCEPTION.RAISE_EXCEPTION;
2392 END az_start_task;
2393
2394
2395 --
2396 -- AZ_REASSIGN_TASK
2397 --
2398 -- Public function. Reassigns a task and returns the new label.
2399 --
2400 FUNCTION az_reassign_task(node_id IN VARCHAR2, p_role IN VARCHAR2)
2401 RETURN VARCHAR2
2402 IS
2403 v_type az_processes.item_type%TYPE;
2404 v_key wf_items.item_key%TYPE;
2405 p_name az_processes.process_name%TYPE;
2406 label az_webform_messages.mesg%TYPE;
2407
2408 BEGIN
2409
2410 v_type := parse_item_type(node_id);
2411 v_key := parse_item_key(node_id);
2412 p_name := parse_process_name_task(node_id);
2413
2414 wf_engine.SetItemAttrText(v_type, v_key, 'AZW_IA_ROLE', p_role);
2415 label := get_old_task_label(v_type, v_key, p_name, p_role);
2416 return label;
2417
2418 EXCEPTION
2419 WHEN app_exception.application_exception THEN
2420 APP_EXCEPTION.RAISE_EXCEPTION;
2421 WHEN OTHERS THEN
2422 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2423 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2424 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2425 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.az_reassign_task');
2426 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
2427 APP_EXCEPTION.RAISE_EXCEPTION;
2428 END az_reassign_task;
2429
2430
2431 --
2432 -- AZ_ABORT_TASK
2433 --
2434 -- Public function. Aborts a task and returns its label
2435 --
2436 FUNCTION az_abort_task(node_id IN VARCHAR2) RETURN VARCHAR2
2437 IS
2438 v_type az_processes.item_type%TYPE;
2439 v_key wf_items.item_key%TYPE;
2440 p_name az_processes.process_name%TYPE;
2441 label az_webform_messages.mesg%TYPE;
2442 p_role wf_roles.name%TYPE;
2443
2444 BEGIN
2445
2446 v_type := parse_item_type(node_id);
2447 v_key := parse_item_key(node_id);
2448 p_name := parse_process_name_task(node_id);
2449 p_role := wf_engine.GetItemAttrText(v_type, v_key, 'AZW_IA_ROLE');
2450 label := NULL;
2451
2452 wf_engine.AbortProcess(v_type, v_key, NULL, 'eng_force');
2453
2454 label := get_old_task_label(v_type, v_key, p_name, p_role);
2455 commit;
2456 return label;
2457
2458 EXCEPTION
2459 WHEN app_exception.application_exception THEN
2460 APP_EXCEPTION.RAISE_EXCEPTION;
2461 WHEN OTHERS THEN
2462 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2463 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2464 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2465 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.az_abort_task');
2466 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
2467 APP_EXCEPTION.RAISE_EXCEPTION;
2468 END az_abort_task;
2469
2470
2471 --
2472 -- GET_TASK_LABEL
2473 --
2474 -- Public function.
2475 -- Given a task node id for a task return it's display label
2476 --
2477 FUNCTION get_task_label(node_id IN VARCHAR2) RETURN VARCHAR2
2478 IS
2479 v_type az_processes.item_type%TYPE;
2480 v_key wf_items.item_key%TYPE;
2481 p_name az_processes.process_name%TYPE;
2482 label az_webform_messages.mesg%TYPE;
2483 p_role wf_roles.name%TYPE;
2484
2485 BEGIN
2486
2487 v_type := parse_item_type(node_id);
2488 v_key := parse_item_key(node_id);
2489 p_name := parse_process_name_task(node_id);
2490 p_role := wf_engine.GetItemAttrText(v_type, v_key, 'AZW_IA_ROLE');
2491 label := get_old_task_label(v_type, v_key, p_name, p_role);
2492
2493 RETURN label;
2494 EXCEPTION
2495 WHEN app_exception.application_exception THEN
2496 APP_EXCEPTION.RAISE_EXCEPTION;
2497 WHEN OTHERS THEN
2498 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2499 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2500 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2501 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_task_label');
2502 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
2503 APP_EXCEPTION.RAISE_EXCEPTION;
2504 END get_task_label;
2505
2506
2507 --
2508 -- GET_TASK_STATUS
2509 --
2510 -- Public function.
2511 -- Given a task node id for a task return it's current status
2512 --
2513 FUNCTION get_task_status(node_id IN VARCHAR2) RETURN VARCHAR2
2514 IS
2515 v_type az_processes.item_type%TYPE;
2516 v_key wf_items.item_key%TYPE;
2517 p_name az_processes.process_name%TYPE;
2518 label az_webform_messages.mesg%TYPE;
2519 v_task_status wf_item_activity_statuses.activity_status%TYPE;
2520
2521 CURSOR az_task_status_cursor IS
2522 SELECT wias.activity_status
2523 FROM wf_items wfi, wf_process_activities wpa,
2524 wf_item_activity_statuses wias
2525 WHERE wfi.item_type = v_type
2529 AND wias.item_key = wfi.item_key
2526 AND wfi.root_activity = p_name
2527 AND wfi.item_key = v_key
2528 AND wias.item_type = wfi.item_type
2530 AND wpa.instance_id = wias.process_activity
2531 AND wpa.activity_name = wfi.root_activity
2532 AND wpa.process_item_type =v_type
2533 AND wpa.process_name = 'ROOT';
2534
2535 BEGIN
2536
2537 v_type := parse_item_type(node_id);
2538 v_key := parse_item_key(node_id);
2539 p_name := parse_process_name_task(node_id);
2540
2541 OPEN az_task_status_cursor;
2542 FETCH az_task_status_cursor
2543 INTO v_task_status;
2544
2545 IF (v_task_status = 'ACTIVE') THEN
2546 RETURN 'N';
2547 ELSE
2548 RETURN 'Y';
2549 END IF;
2550 CLOSE az_task_status_cursor;
2551 EXCEPTION
2552 WHEN app_exception.application_exception THEN
2553 APP_EXCEPTION.RAISE_EXCEPTION;
2554 WHEN OTHERS THEN
2555 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2556 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2557 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2558 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.get_task_status');
2559 fnd_message.set_token('AZW_ERROR_STMT','CURSOR az_task_status_cursor');
2560 APP_EXCEPTION.RAISE_EXCEPTION;
2561 END get_task_status;
2562
2563
2564 --
2565 -- POPULATE_PROCESS_STATUS
2566 --
2567 -- Public procedure. Called during upgrade from 11.0 to 11.5 only.
2568 -- Populates the STATUS_CODE column in AZ_PROCESSES to be either
2569 -- 'A', 'C', or 'N'.
2570 --
2571
2572 PROCEDURE populate_process_status IS
2573
2574 CURSOR az_process_active_cursor IS
2575 SELECT ap.item_type, ap.process_name, ap.context_id
2576 FROM az_processes ap,wf_items wfi, wf_item_attribute_values wiav
2577 WHERE wfi.item_type = ap.item_type
2578 AND wfi.root_activity = ap.process_name
2579 AND wiav.item_type = wfi.item_type
2580 AND wiav.item_key = wfi.item_key
2581 AND wiav.name = 'AZW_IA_CTXT_ID'
2582 AND wiav.text_value = ap.context_id
2583 AND ap.complete_flag = 'N';
2584 p_item_type az_processes.item_type%TYPE;
2585 p_process_name az_processes.process_name%TYPE;
2586 p_context_id az_processes.context_id%TYPE;
2587
2588 BEGIN
2589 -- dbms_output.put_line('populate process status');
2590
2591 -- Update all 'Active' processes
2592
2593 BEGIN
2594 OPEN az_process_active_cursor;
2595 FETCH az_process_active_cursor
2596 INTO p_item_type, p_process_name, p_context_id;
2597
2598 WHILE az_process_active_cursor%FOUND LOOP
2599 UPDATE az_processes
2600 SET status_code = 'A'
2601 WHERE item_type = p_item_type
2602 AND process_name = p_process_name
2603 AND context_id = p_context_id;
2604 FETCH az_process_active_cursor
2605 INTO p_item_type, p_process_name, p_context_id;
2606 END LOOP;
2607
2608 CLOSE az_process_active_cursor;
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2612 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2613 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2614 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.populate_process_status');
2615 fnd_message.set_token('AZW_ERROR_STMT','CURSOR az_process_active_cursor');
2616 APP_EXCEPTION.RAISE_EXCEPTION;
2617 END;
2618
2619 BEGIN
2620 -- Update all 'Complete' processes
2621
2622 UPDATE az_processes
2623 SET status_code = 'C'
2624 WHERE complete_flag = 'Y';
2625 EXCEPTION
2626 WHEN OTHERS THEN
2627 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2628 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2629 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2630 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_ctxt_id_task');
2631 fnd_message.set_token('AZW_ERROR_STMT','update az_processes status code C');
2632 APP_EXCEPTION.RAISE_EXCEPTION;
2633 END;
2634
2635 BEGIN
2636 -- Update all 'Not Started' Processes
2637
2638 UPDATE az_processes
2639 SET status_code = 'N'
2640 WHERE status_code NOT IN ('A', 'C');
2641 EXCEPTION
2642 WHEN OTHERS THEN
2643 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2644 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2645 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2646 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.parse_ctxt_id_task');
2647 fnd_message.set_token('AZW_ERROR_STMT','update az_processes status code N');
2648 APP_EXCEPTION.RAISE_EXCEPTION;
2649 END;
2650
2651 COMMIT;
2652
2653 EXCEPTION
2654 WHEN app_exception.application_exception THEN
2655 RAISE;
2656 WHEN OTHERS THEN
2657 -- DBMS_OUTPUT.PUT_LINE('error: populate_process_status: ' || SQLERRM);
2658 ROLLBACK;
2659 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2660 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2661 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2665 END populate_process_status;
2662 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.populate_process_status');
2663 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
2664 APP_EXCEPTION.RAISE_EXCEPTION;
2666
2667
2668 --
2669 -- ABORT_RUNNING_TASKS
2670 --
2671 -- Public procedure. Called during upgrade from 11.0 to 11.5 only.
2672 -- Aborts all tasks without an end date.
2673 --
2674
2675 PROCEDURE abort_running_tasks IS
2676
2677 CURSOR running_tasks IS
2678 SELECT distinct wi.item_type, wi.item_key
2679 FROM wf_items wi
2680 WHERE wi.item_type like 'AZW%'
2681 AND wi.end_date is NULL;
2682
2683 v_item_type wf_items.item_type%TYPE;
2684 v_item_key wf_items.item_key%TYPE;
2685
2686 BEGIN
2687
2688 OPEN running_tasks;
2689 FETCH running_tasks INTO v_item_type, v_item_key;
2690 WHILE running_tasks%FOUND LOOP
2691 BEGIN
2692 wf_engine.AbortProcess(v_item_type, v_item_key);
2693 EXCEPTION
2694 WHEN OTHERS THEN
2695 NULL;
2696 END;
2697 FETCH running_tasks INTO v_item_type, v_item_key;
2698 END LOOP;
2699 CLOSE running_tasks;
2700 EXCEPTION
2701 WHEN OTHERS THEN
2702 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2703 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2704 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2705 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.abort_running_tasks');
2706 fnd_message.set_token('AZW_ERROR_STMT','CURSOR running_tasks');
2707 APP_EXCEPTION.RAISE_EXCEPTION;
2708
2709 END abort_running_tasks;
2710
2711 --
2712 -- PROCESS_HAS_ACTIVE_TASKS
2713 --
2714 -- Public function. Called by AZWIZARD form from
2715 -- process_overview.process_complete program unit.
2716 -- Checks if the specified process has any active tasks.
2717 -- Returns TRUE if there is any active tasks for the specified process
2718 -- otherwise returns FALSE.
2719 --
2720 FUNCTION process_has_active_tasks(node_id IN VARCHAR2) RETURN BOOLEAN
2721 IS
2722
2723 v_count NUMBER;
2724 v_return BOOLEAN;
2725 v_type wf_items.item_type%TYPE;
2726 v_name wf_items.root_activity%TYPE;
2727
2728 BEGIN
2729
2730 --
2731 -- parse the node id to get the item type and the process name
2732 --
2733 v_type := parse_item_type(node_id);
2734 v_name := parse_process_name_task(node_id);
2735
2736 --
2737 -- Check if there is any active tasks for the specified process.
2738 --
2739 SELECT COUNT(*) INTO v_count
2740 FROM az_tasks_v
2741 WHERE item_type = v_type
2742 AND root_activity = v_name
2743 AND status = 'A';
2744
2745 IF v_count > 0 THEN
2746 RETURN TRUE;
2747 ELSE
2748 RETURN FALSE;
2749 END IF;
2750
2751 EXCEPTION
2752 WHEN OTHERS THEN
2753 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
2754 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
2755 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
2756 fnd_message.set_token('AZW_ERROR_PROC','azw_proc.process_has_active_tasks');
2757 fnd_message.set_token('AZW_ERROR_STMT','SELECT COUNT(*) FROM AZ_TASKS_V');
2758 APP_EXCEPTION.RAISE_EXCEPTION;
2759 END process_has_active_tasks;
2760
2761
2762 END AZW_PROC;