[Home] [Help]
PACKAGE BODY: APPS.WF_TASKFLOW
Source
1 PACKAGE BODY WF_TASKFLOW AS
2 /* $Header: wfflob.pls 120.3 2007/08/14 09:45:07 dsardar ship $ */
3
4 -- Global Variables Declatration
5 gClob clob;
6 gUseClob boolean := false;
7 gClobInit boolean := false;
8 l_data varchar2(32000);
9 NL_CHAR varchar2 (1) := '
10 ';
11
12 --------------------- Private Procedure Declarations ----------------------
13
14 PROCEDURE get_base_definition(x_parent_type IN VARCHAR2,
15 x_parent_name IN VARCHAR2,
16 x_base_type IN VARCHAR2,
17 x_base_name IN VARCHAR2,
18 x_start_date IN DATE);
19
20 PROCEDURE get_activity_definitions(x_base_type IN VARCHAR2,
21 x_base_name IN VARCHAR2,
22 x_base_version IN NUMBER,
23 x_start_date IN DATE);
24
25 PROCEDURE get_transition_definitions(x_base_type IN VARCHAR2,
26 x_base_name IN VARCHAR2,
27 x_base_version IN NUMBER,
28 x_start_date IN DATE);
29
30 PROCEDURE get_base_instance(x_item_type IN VARCHAR2,
31 x_item_key IN VARCHAR2,
32 x_parent_name IN VARCHAR2,
33 x_base_type IN VARCHAR2,
34 x_base_name IN VARCHAR2,
35 x_start_date IN DATE);
36
37 PROCEDURE get_activity_instances(x_item_type IN VARCHAR2,
38 x_item_key IN VARCHAR2,
39 x_base_type IN VARCHAR2,
40 x_base_name IN VARCHAR2,
41 x_start_date IN DATE);
42
43 PROCEDURE get_transition_instances(x_item_type IN VARCHAR2,
44 x_item_key IN VARCHAR2,
45 x_base_type IN VARCHAR2,
46 x_base_name IN VARCHAR2,
47 x_start_date IN DATE);
48
49 /*--------------------------------------------------------------------------
50 * APPEND_CLOB_DATA
51 *
52 * Description:
53 * Called by APPEND_DATA
54 * Appends data to the Clob object
55 *
56 * Parameters:
57 * new_Data : Data to be appended
58 *
59 * Notes:
60 * To remove htp.p calls we have appended the data to a clob there by
61 * returning clob object
62 *-------------------------------------------------------------------------*/
63
64 procedure append_clob_data(newData in varchar2)
65 is
66 begin
67 if (gUseClob) then
68 if NOT (gClobInit) then
69 dbms_lob.createTemporary(gClob, true, dbms_lob.session);
70 dbms_lob.open(gClob, dbms_lob.lob_readwrite);
71 gClobInit := true;
72 end if;
73 if (newData is not null) then
74 dbms_lob.writeAppend(gClob, length(newData), newData);
75 end if;
76 end if;
77 end;
78
79
80 /*--------------------------------------------------------------------------
81 * APPEND_DATA
82 *
83 * Description:
84 * Called by GET_FLOW_DEFINITION, GET_BASE_DEFINITION, GET_ACTIVITY_DEFINITION
85 * GET_TRANSITION_DEFINITION, GET_FLOW_INSTANCE, GET_BASE_INSTANCE,
86 * GET_ACTIVITY_INSTANCE, GET_TRANSITION_INSTANCE procedures.
87
88 * Appends data to the Clob object
89 *
90 * Parameters:
91 * new_Data : Data to be appended
92 *
93 * Notes:
94 * To remove htp.p calls we have appended the data to a clob there by
95 * returning clob object
96 *-------------------------------------------------------------------------*/
97
98 procedure append_data(newData in varchar2)
99 is
100 begin
101 if (gUseClob) then
102 append_clob_data(newData|| NL_CHAR);
103 else
104 if(newData is not null) then
105 l_data := l_data || NL_CHAR || newData;
106 end if;
107 end if;
108 end;
109
110 /*--------------------------------------------------------------------------
111 * GET_FLOW_DEFINITION
112 *
113 * Description:
114 * Public procedure. Called by Java front-end (FlowDiagram.java).
115 * Get definitions of all process activities and transitions in the base
116 * process.
117 *
118 * Parameters:
119 * x_base_type - Base item type, first part of id.
120 * x_base_name - Base process name, second part of id.
121 * x_parent_type - Parent item type. Default to null.
122 * x_parent_name - Parent process_name. Default to null.
123 *
124 * Notes:
125 * x_parent_type and x_parent_name are used when the base process has
126 * a parent process of different item_type. Used when zooming-in a
127 * subprocess.
128 *-------------------------------------------------------------------------*/
129 FUNCTION get_flow_definition(
130 x_base_type VARCHAR2,
131 x_base_name VARCHAR2,
132 x_parent_type VARCHAR2,
133 x_parent_name VARCHAR2) RETURN CLOB IS
134
135 temp_name VARCHAR2(30) := '';
136 parent_type VARCHAR2(8) := '';
137 parent_name VARCHAR2(30) := '';
138 parent_version NUMBER;
139 start_date DATE;
140 base_version NUMBER;
141 res BOOLEAN;
142
143 BEGIN
144 --dbms_output.enable(1000000);
145 --dbms_output.put_line('get_flow_definition');
146 start_date := SYSDATE;
147
148 l_data := '';
149 gUseClob := false; --for error messages only
150
151 /*res := ICX_SEC.ValidateSession( c_validate_only => 'Y',
152 c_update => FALSE);
153
154 if (res = FALSE ) then
155 wf_core.raise('WFSEC_NO_SESSION');
156 end if; */
157
158 IF (x_parent_type IS NULL OR x_parent_name IS NULL) THEN
159 temp_name := 'ROOT';
160 ELSE
161 temp_name := x_parent_name;
162 END IF;
163
164 -- check whether parent information exists
165 BEGIN
166 SELECT DISTINCT wav.item_type, wav.name, wav.version
167 INTO parent_type, parent_name, parent_version
168 FROM wf_process_activities wpa,
169 wf_activities wav
170 WHERE wpa.activity_item_type = x_base_type
171 AND wpa.activity_name = x_base_name
172 AND wav.item_type = wpa.process_item_type
173 AND wav.name = wpa.process_name
174 AND wav.name = temp_name
175 AND wav.begin_date <= start_date
176 AND (wav.end_date IS NULL
177 OR wav.end_date > start_date);
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 append_data('ERROR:' || 'WFMON_PROCESS_NOT_FOUND');
181 RETURN l_data;
182 WHEN OTHERS THEN
183 append_data('ERROR:' || 'WFMON_OTHER_ERROR');
184 RETURN l_data;
185 END;
186
187 SELECT version
188 INTO base_version
189 FROM wf_activities
190 WHERE item_type = x_base_type
191 AND name = x_base_name
192 AND begin_date <= start_date
193 AND (end_date IS NULL
194 OR end_date > start_date);
195
196 /* Set useClob here */
197 gUseClob := true;
198
199 IF (x_parent_type IS NULL OR x_parent_name IS NULL) THEN
200 get_base_definition(x_base_type, 'ROOT',
201 x_base_type, x_base_name, start_date);
202 ELSE
203 get_base_definition(x_parent_type, x_parent_name,
204 x_base_type, x_base_name, start_date);
205 END IF;
206
207 get_activity_definitions(x_base_type, x_base_name, base_version,
208 start_date);
209
210 get_transition_definitions(x_base_type, x_base_name, base_version,
211 start_date);
212
213 --Resetting global variables here.
214 gUseClob := false;
215 gClobInit := false;
216 return gClob;
217
218 END get_flow_definition;
219
220 /*--------------------------------------------------------------------------
221 * GET_BASE_DEFINITION
222 *
223 * Description:
224 * Private procedure. Called by get_flow_definition.
225 * Get definition of the base process activity.
226 *
227 * Parameters:
228 * x_parent_type - Item type of the parent process.
229 * x_parent_name - Name of the parent process.
230 * x_base_type - Item type of the base process.
231 * x_base_name - Name of the base process.
232 * x_start_date - Begin date of the parent process.
233 *
234 * Notes:
235 * DECODE are used to replace null values so that each value is properly
236 * separated and the front-end knows how to interpret.
237 *-------------------------------------------------------------------------*/
238 PROCEDURE get_base_definition(x_parent_type IN VARCHAR2,
239 x_parent_name IN VARCHAR2,
240 x_base_type IN VARCHAR2,
241 x_base_name IN VARCHAR2,
242 x_start_date IN DATE) IS
243
244 CURSOR base_definition_cursor IS
245 SELECT wav.item_type,
246 wav.name,
247 wav.display_name,
248 wav.version,
249 wav.type,
250 wav.icon_name,
251 wpa.instance_id,
252 NVL(wpa.start_end,' ') start_end,
253 DECODE(wpa.icon_geometry, '', '0,0', wpa.icon_geometry) geometry
254 FROM wf_process_activities wpa,
255 wf_activities_vl wav,
256 wf_activities wav2
257 WHERE wpa.process_item_type = x_parent_type /* check parent activity */
258 AND wpa.process_name = x_parent_name
259 AND wav2.item_type = wpa.process_item_type
260 AND wav2.name = wpa.process_name
261 AND wav2.version = wpa.process_version
262 AND wav2.begin_date <= x_start_date
263 AND (wav2.end_date IS NULL
264 OR wav2.end_date > x_start_date)
265 AND wpa.activity_item_type = x_base_type /* find base activity */
266 AND wpa.activity_name = x_base_name
267 AND wav.item_type = wpa.activity_item_type
268 AND wav.name = wpa.activity_name
269 AND wav.begin_date <= x_start_date
270 AND (wav.end_date IS NULL
271 OR wav.end_date > x_start_date);
272
273 base base_definition_cursor%ROWTYPE;
274
275 l_tab varchar2(30) := wf_core.local_chr(9);
276
277 BEGIN
278 /*dbms_output.put_line('get_base_definition:' ||
279 x_parent_type || ','||
280 x_parent_name || ',' ||
281 x_base_type || ',' ||
282 x_base_name || ',' ||
283 x_start_date); */
284
285
286 OPEN base_definition_cursor;
287 FETCH base_definition_cursor INTO base;
288 IF (base_definition_cursor%NOTFOUND) THEN
289 append_data('ERROR:' || 'WFMON_INVALID_PROCESS_DEF');
290 RETURN;
291 END IF;
292
293
294 /* dbms_output.put_line('BASE: ' || base.item_type || ', ' ||
295 base.name || ', ' ||
296 base.display_name || ', ' ||
297 base.version || ', ' ||
298 base.type || ', ' ||
299 base.icon_name || ', ' ||
300 base.instance_id || ', ' ||
301 base.start_end || ', ' ||
302 base.geometry); */
303
304
305 append_data('BASE:' ||
306 base.item_type || l_tab ||
307 base.name || l_tab ||
308 base.display_name || l_tab ||
309 base.version || l_tab ||
310 base.type || l_tab ||
311 base.icon_name || l_tab ||
312 base.instance_id || l_tab ||
313 base.start_end || l_tab ||
314 base.geometry);
315
316 END get_base_definition;
317
318 /*--------------------------------------------------------------------------
319 * GET_ACTIVITY_DEFINITIONS
320 *
321 * Description:
322 * Private procedure. Called by get_flow_definition.
323 * Get definition of the all the activities in this process.
324 *
325 * Parameters:
326 * x_base_type - Item type of the base process.
327 * x_base_name - Name of the base process.
328 * x_base_version - Version of the base process.
329 * x_start_date - Begin date of base's parent process.
330 *
331 * Notes:
332 * DECODE are used to replace null values so that each value is properly
333 * separated and the front-end knows how to interpret.
334 *-------------------------------------------------------------------------*/
335 PROCEDURE get_activity_definitions(x_base_type IN VARCHAR2,
336 x_base_name IN VARCHAR2,
337 x_base_version IN NUMBER,
338 x_start_date IN DATE) IS
339
340 CURSOR activity_definitions_cursor IS
341 SELECT wav.item_type,
342 wav.name,
343 wav.display_name,
344 wav.version,
345 wav.type,
346 wav.icon_name,
347 wpa.instance_id,
348 NVL(wpa.start_end,' ') start_end,
349 wpa.icon_geometry
350 FROM wf_process_activities wpa,
351 wf_activities_vl wav,
352 wf_activities wav2
353 WHERE wpa.process_item_type = x_base_type /* check base activity */
354 AND wpa.process_name = x_base_name
355 AND wpa.process_version = x_base_version /* must have version */
356 AND wav2.item_type = wpa.process_item_type
357 AND wav2.name = wpa.process_name
358 AND wav2.begin_date <= x_start_date
359 AND (wav2.end_date IS NULL
360 OR wav2.end_date > x_start_date)
361 AND wav.item_type = wpa.activity_item_type /* find all activities */
362 AND wav.name = wpa.activity_name
363 AND wav.begin_date <= x_start_date
364 AND (wav.end_date IS NULL
365 OR wav.end_date > x_start_date);
366
367 activity activity_definitions_cursor%ROWTYPE;
368
369 l_tab varchar2(30) := wf_core.local_chr(9);
370
371 BEGIN
372
373 OPEN activity_definitions_cursor;
374 LOOP
375 FETCH activity_definitions_cursor INTO activity;
376 EXIT WHEN activity_definitions_cursor%NOTFOUND;
377
378
379 /* dbms_output.put_line('ACTIVITY: ' || activity.item_type || ', ' ||
380 activity.name || ', ' ||
381 activity.display_name || ', ' ||
382 activity.version || ', ' ||
383 activity.type || ', ' ||
384 activity.icon_name || ', ' ||
385 activity.instance_id || ', ' ||
386 activity.start_end || ', ' ||
387 activity.icon_geometry); */
388
389
390 append_data('ACTIVITY:' ||
391 activity.item_type || l_tab ||
392 activity.name || l_tab ||
393 activity.display_name || l_tab ||
394 activity.version || l_tab ||
395 activity.type || l_tab ||
396 activity.icon_name || l_tab ||
397 activity.instance_id || l_tab ||
398 activity.start_end || l_tab ||
399 activity.icon_geometry);
400 END LOOP;
401
402 END get_activity_definitions;
403
404 /*--------------------------------------------------------------------------
405 * GET_TRANSITION_DEFINITIONS
406 *
407 * Description:
408 * Private procedure. Called by get_flow_definition.
409 * Get definition of the all the transitions between activities in the
410 * base process.
411 *
412 * Parameters:
413 * x_base_type - Item type of the base process.
414 * x_base_name - Name of the base process.
415 * x_base_version - Version of the base process.
416 * x_start_date - Begin date of base's parent process.
417 *
418 * Notes:
419 * DECODE is used to replace null values so that each value is properly
420 * separated and the front-end knows how to interpret.
421 *-------------------------------------------------------------------------*/
422 PROCEDURE get_transition_definitions(x_base_type IN VARCHAR2,
423 x_base_name IN VARCHAR2,
424 x_base_version IN NUMBER,
425 x_start_date IN DATE) IS
426
427 CURSOR transition_definitions_cursor IS
428 SELECT wpa.icon_geometry from_icon,
429 wpa.instance_id from_instance,
430 DECODE(wf_core.activity_result(wav2.result_type,wat.result_code),
431 '*', ' ',
432 wf_core.activity_result(wav2.result_type,wat.result_code))
433 label,
434 wpa2.icon_geometry to_icon,
435 wpa2.instance_id to_instance,
436 wat.arrow_geometry
437 FROM wf_process_activities wpa,
438 wf_activities_vl wav,
439 wf_activities_vl wav2,
440 wf_activity_transitions wat,
441 wf_process_activities wpa2
442 WHERE wpa.process_item_type = x_base_type /* check base activity */
443 AND wpa.process_name = x_base_name
444 AND wpa.process_version = x_base_version
445 AND wav.item_type = wpa.process_item_type
446 AND wav.name = wpa.process_name
447 AND wav.version = wpa.process_version
448 AND wav.begin_date <= x_start_date
449 AND (wav.end_date IS NULL
450 OR wav.end_date > x_start_date)
451 AND wav2.item_type = wpa.activity_item_type /* check activities */
452 AND wav2.name = wpa.activity_name /* get label */
453 AND wav2.begin_date <= x_start_date
454 AND (wav2.end_date IS NULL
455 OR wav2.end_date > x_start_date)
456 AND wat.from_process_activity = wpa.instance_id
457 AND wpa2.instance_id = wat.to_process_activity;
458
459 transition transition_definitions_cursor%ROWTYPE;
460
461 l_tab varchar2(30) := wf_core.local_chr(9);
462
463 BEGIN
464
465 OPEN transition_definitions_cursor;
466 LOOP
467 FETCH transition_definitions_cursor INTO transition;
468 EXIT WHEN transition_definitions_cursor%NOTFOUND;
469
470 /* dbms_output.put_line('TRANSITION: '|| transition.from_icon || ', '||
471 transition.from_instance|| ', '||
472 transition.label || ', '||
473 transition.to_icon || ', '||
474 transition.to_instance || ', '||
475 transition.arrow_geometry); */
476
477
478 append_data('TRANSITION:' ||
479 transition.from_icon || l_tab ||
480 transition.from_instance || l_tab ||
481 transition.label || l_tab ||
482 transition.to_icon || l_tab ||
483 transition.to_instance || l_tab ||
484 transition.arrow_geometry);
485 END LOOP;
486
487 END get_transition_definitions;
488
489 /*--------------------------------------------------------------------------
490 * GET_FLOW_INSTANCE
491 *
492 * Description:
493 * Public procedure. Called by Java front-end (FlowDiagram.java).
494 * Get defintions and state info of all process activities and transitions
495 * in the process instance.
496 *
497 * Parameters:
498 * x_item_type - Item type for the process instance, first part of
499 * instance id.
500 * x_item_key - Item key for the process instance, second part of
501 * instance id. For retrieving state info.
502 * x_parent_type - Parent item type, for retrieving base process
503 * information. (A process of one item_type can contain
504 * activities of other item_types.) Default to null.
505 * x_parent_name - Parent process_name, for retrieving base process
506 * information. (A process of one item_type can contain
507 * activities of other item_types.) Default to null.
508 * x_base_type - Item type, first id of the process defintion. Default
509 * to null.
510 * x_base_name - Process name, second id of the process definition.
511 * Default to null.
512 *
513 * Notes:
514 *
515 *-------------------------------------------------------------------------*/
516 FUNCTION get_flow_instance(x_item_type VARCHAR2,
517 x_item_key VARCHAR2,
518 x_parent_type VARCHAR2,
519 x_parent_name VARCHAR2,
520 x_base_type VARCHAR2,
521 x_base_name VARCHAR2) RETURN CLOB IS
522
523 base_name VARCHAR(30) := '';
524 base_version NUMBER;
525 start_date DATE;
526 res BOOLEAN;
527
528 BEGIN
529
530 l_data := '';
531 gUseClob := false; --for error messages only
532
533 --dbms_output.enable(1000000);
534 --dbms_output.put_line('get_flow_instance');
535
536 /*res := ICX_SEC.ValidateSession( c_validate_only => 'Y',
537 c_update => FALSE);
538
539 if (res = FALSE ) then
540 wf_core.raise('WFSEC_NO_SESSION');
541 end if; */
542
543 BEGIN
544 SELECT root_activity, root_activity_version, begin_date
545 INTO base_name, base_version, start_date
546 FROM wf_items
547 WHERE item_type = x_item_type
548 AND item_key = x_item_key;
549 EXCEPTION
550 WHEN NO_DATA_FOUND THEN
551 append_data('ERROR:' || 'WFMON_PROCESS_NOT_FOUND');
552 RETURN l_data;
553 WHEN OTHERS THEN
554 append_data('ERROR:' || 'WFMON_OTHER_ERROR');
555 RETURN l_data;
556 END;
557
558 /* Set useClob here */
559 gUseClob := true;
560
561 IF (x_parent_type IS NULL OR x_parent_name IS NULL OR
562 x_base_type IS NULL OR x_base_name IS NULL) THEN
563 get_base_instance(x_item_type, x_item_key, 'ROOT',
564 x_item_type, base_name, start_date);
565
566 get_activity_instances(x_item_type, x_item_key,
567 x_item_type, base_name, start_date);
568
569 get_transition_instances(x_item_type, x_item_key,
570 x_item_type, base_name, start_date);
571 ELSE
572 get_base_instance(x_parent_type, x_item_key, x_parent_name,
573 x_base_type, x_base_name, start_date);
574
575 get_activity_instances(x_item_type, x_item_key,
576 x_base_type, x_base_name, start_date);
577
578 get_transition_instances(x_item_type, x_item_key,
579 x_base_type, x_base_name, start_date);
580 END IF;
581
582 --Resetting global variables here.
583 gUseClob := false;
584 gClobInit := false;
585 return gClob;
586
587 END get_flow_instance;
588
589 /*--------------------------------------------------------------------------
590 * GET_BASE_INSTANCE
591 *
592 * Description:
593 * Private procedure. Called by get_flow_instance.
594 * Get defintions and state info of the base activity in the instance of
595 * this process identified by the item key.
596 *
597 * Parameters:
598 * x_item_type - Item type of the parent process.
599 * x_item_key - Item key that uniquely identifies the instance.
600 * x_parent_name - Name of the parent process.
601 * x_base_type - Item type of the base activity.
602 * x_base_name - Name of the base activity.
603 * x_start_date - Begin date of the parent process.
604 *
605 * Notes:
606 * DECODE is used to replace null values so that each value is properly
607 * separated and the front-end knows how to interpret.
608 *-------------------------------------------------------------------------*/
609 PROCEDURE get_base_instance(x_item_type IN VARCHAR2,
610 x_item_key IN VARCHAR2,
611 x_parent_name IN VARCHAR2,
612 x_base_type IN VARCHAR2,
613 x_base_name IN VARCHAR2,
614 x_start_date IN DATE) IS
615
616 CURSOR base_instance_cursor IS
617 SELECT wav.item_type,
618 wav.name,
619 wav.display_name,
620 wav.version,
621 wav.type,
622 wav.icon_name,
623 wpa.instance_id,
624 DECODE(wpa.start_end, '', ' ', wpa.start_end) start_end,
625 DECODE(wpa.icon_geometry, '', '0,0', wpa.icon_geometry) geometry,
626 DECODE(wf_core.activity_result(wav.result_type,wpa.default_result),
627 '', ' ',
628 wf_core.activity_result(wav.result_type,wpa.default_result))
629 usage_result,
630 DECODE(wias.activity_status, '', ' ', wias.activity_status)
631 activity_status
632 FROM wf_process_activities wpa,
633 wf_activities_vl wav,
634 wf_activities_vl wav2,
635 wf_item_activity_statuses wias
636 WHERE wpa.process_item_type = x_item_type -- check parent activity
637 AND wpa.process_name = x_parent_name
638 AND wav2.item_type = wpa.process_item_type
639 AND wav2.name = wpa.process_name
640 AND wav2.version = wpa.process_version
641 AND wav2.begin_date <= x_start_date
642 AND (wav2.end_date IS NULL
643 OR wav2.end_date > x_start_date)
644 AND wpa.activity_item_type = x_base_type -- find base activity
645 AND wpa.activity_name = x_base_name
646 AND wav.item_type = wpa.activity_item_type
647 AND wav.name = wpa.activity_name
648 AND wav.begin_date <= x_start_date
649 AND (wav.end_date IS NULL
650 OR wav.end_date > x_start_date)
651 AND wias.item_type (+) = x_item_type -- find base status
652 AND wias.item_key (+) = x_item_key
653 AND wias.process_activity (+) = wpa.instance_id;
654
655 base base_instance_cursor%ROWTYPE;
656
657 l_tab varchar2(30) := wf_core.local_chr(9);
658
659 BEGIN
660
661 OPEN base_instance_cursor;
662 FETCH base_instance_cursor INTO base;
663 IF (base_instance_cursor%NOTFOUND) THEN
664 append_data('ERROR:' || 'WFMON_INVALID_PROCESS_DEF');
665 RETURN;
666 END IF;
667
668 /*
669 dbms_output.put_line('BASE: ' || base.item_type || ', ' ||
670 base.name || ', ' ||
671 base.display_name || ', ' ||
672 base.version || ', ' ||
673 base.type || ', ' ||
674 base.icon_name || ', ' ||
675 base.instance_id || ', ' ||
676 base.start_end || ', ' ||
677 base.geometry || ', ' ||
678 base.usage_result || ', ' ||
679 base.activity_status);
680 */
681
682 append_data('BASE:' ||
683 base.item_type || l_tab ||
684 base.name || l_tab ||
685 base.display_name || l_tab ||
686 base.version || l_tab ||
687 base.type || l_tab ||
688 base.icon_name || l_tab ||
689 base.instance_id || l_tab ||
690 base.start_end || l_tab ||
691 base.geometry || l_tab ||
692 base.usage_result || l_tab ||
693 base.activity_status);
694
695 END get_base_instance;
696
697 /*--------------------------------------------------------------------------
698 * GET_ACTIVITY_INSTANCES
699 *
700 * Description:
701 * Private procedure. Called by get_flow_instance.
702 * Get defintions and state info of all the activities in the instance of
703 * the process identified by the item key.
704 *
705 * Parameters:
706 * x_item_type - Item type of the parent process.
707 * x_item_key - Item key that uniquely identifies the instance.
708 * x_base_type - Item type of the base activity.
709 * x_base_name - Name of the base activity.
710 * x_start_date - Begin date of the parent process.
711 *
712 * Notes:
713 * DECODE is used to replace null values so that each value is properly
714 * separated and the front-end knows how to interpret.
715 *-------------------------------------------------------------------------*/
716 PROCEDURE get_activity_instances(x_item_type IN VARCHAR2,
717 x_item_key IN VARCHAR2,
718 x_base_type IN VARCHAR2,
719 x_base_name IN VARCHAR2,
720 x_start_date IN DATE) IS
721
722 CURSOR activity_instances_cursor IS
723 SELECT wav.item_type,
724 wav.name,
725 wav.display_name,
726 wav.version,
727 wav.type,
728 wav.icon_name,
729 wpa.instance_id,
730 DECODE(wpa.start_end, '', ' ', wpa.start_end) start_end,
731 wpa.icon_geometry,
732 DECODE(wf_core.activity_result(wav.result_type, wpa.default_result),
733 '', ' ',
734 wf_core.activity_result(wav.result_type, wpa.default_result))
735 usage_result,
736 DECODE(wias.activity_status, '', ' ', wias.activity_status)
737 activity_status
738 FROM wf_process_activities wpa,
739 wf_activities_vl wav,
740 wf_activities_vl wav2,
741 wf_item_activity_statuses wias
742 WHERE wpa.process_item_type = x_base_type /* check base activity */
743 AND wpa.process_name = x_base_name
744 AND wav2.item_type = wpa.process_item_type
745 AND wav2.name = wpa.process_name
746 AND wav2.begin_date <= x_start_date
747 AND (wav2.end_date IS NULL
748 OR wav2.end_date > x_start_date)
749 AND wav2.version = wpa.process_version
750 AND wav.item_type = wpa.activity_item_type /* find all activities */
751 AND wav.name = wpa.activity_name
752 AND wav.begin_date <= x_start_date
753 AND (wav.end_date IS NULL
754 OR wav.end_date > x_start_date)
755 AND wias.item_type (+) = x_item_type /* find activity status */
756 AND wias.item_key (+) = x_item_key
757 AND wias.process_activity (+) = wpa.instance_id;
758
759 activity activity_instances_cursor%ROWTYPE;
760
761 l_tab varchar2(30) := wf_core.local_chr(9);
762
763 BEGIN
764
765 OPEN activity_instances_cursor;
766 LOOP
767 FETCH activity_instances_cursor INTO activity;
768 EXIT WHEN activity_instances_cursor%NOTFOUND;
769
770 /*
771 dbms_output.put_line('ACTIVITY: ' || activity.item_type || ', ' ||
772 activity.name || ', ' ||
773 activity.display_name || ', ' ||
774 activity.version || ', ' ||
775 activity.type || ', ' ||
776 activity.icon_name || ', ' ||
777 activity.instance_id || ', ' ||
778 activity.start_end || ', ' ||
779 activity.icon_geometry || ', ' ||
780 activity.usage_result || ', ' ||
781 activity.activity_status);
782 */
783
784 append_data('ACTIVITY:' ||
785 activity.item_type || l_tab ||
786 activity.name || l_tab ||
787 activity.display_name || l_tab ||
788 activity.version || l_tab ||
789 activity.type || l_tab ||
790 activity.icon_name || l_tab ||
791 activity.instance_id || l_tab ||
792 activity.start_end || l_tab ||
793 activity.icon_geometry || l_tab ||
794 activity.usage_result || l_tab ||
795 activity.activity_status);
796 END LOOP;
797
798 END get_activity_instances;
799
800 /*--------------------------------------------------------------------------
801 * GET_TRAINSITION_INSTANCES
802 *
803 * Description:
804 * Private procedure. Called by get_flow_instance.
805 * Get defintions and state info of all transitions in the instance of
806 * the process identified by the item key.
807 *
808 * Parameters:
809 * x_item_type - Item type of the parent process.
810 * x_item_key - Item key that uniquely identifies the instance.
811 * x_base_type - Item type of the base activity.
812 * x_base_name - Name of the base activity.
813 * x_start_date - Begin date of the parent process.
814 *
815 * Notes:
816 * We first find all the activities in the process and then use their
817 * instance id to look up the transitions associated with the process
818 * instance.
819 * DECODE is used to replace null values so that each value is properly
820 * separated and the front-end knows how to interpret.
821 *-------------------------------------------------------------------------*/
822 PROCEDURE get_transition_instances(x_item_type IN VARCHAR2,
823 x_item_key IN VARCHAR2,
824 x_base_type IN VARCHAR2,
825 x_base_name IN VARCHAR2,
826 x_start_date IN DATE) IS
827
828 CURSOR transition_instances_cursor IS
829 SELECT wpa.icon_geometry from_icon,
830 wpa.instance_id from_instance,
831 DECODE(wf_core.activity_result(wav2.result_type,wat.result_code),
832 '*',' ',
833 wf_core.activity_result(wav2.result_type,wat.result_code))
834 label,
835 wpa2.icon_geometry to_icon,
836 wpa2.instance_id to_instance,
837 wat.arrow_geometry,
838 wl.lookup_code visited /* transition status */
839 FROM wf_process_activities wpa,
840 wf_activities_vl wav,
841 wf_activities_vl wav2,
842 wf_activity_transitions wat,
843 wf_process_activities wpa2,
844 wf_item_activity_statuses wias,
845 wf_lookups wl
846 WHERE wpa.process_item_type = x_base_type /* check base activity */
847 AND wpa.process_name = x_base_name
848 AND wav.item_type = wpa.process_item_type
849 AND wav.name = wpa.process_name
850 AND wav.version = wpa.process_version
851 AND wav.begin_date <= x_start_date
852 AND (wav.end_date IS NULL
853 OR wav.end_date > x_start_date)
854 AND wav2.item_type = wpa.activity_item_type /* check activities */
855 AND wav2.name = wpa.activity_name
856 AND wav2.begin_date <= x_start_date
857 AND (wav2.end_date IS NULL
858 OR wav2.end_date > x_start_date)
859 AND wat.from_process_activity = wpa.instance_id
860 AND wpa2.instance_id = wat.to_process_activity
861 AND wias.item_type (+) = x_item_type /* find status */
862 AND wias.item_key (+) = x_item_key
863 AND wias.process_activity (+) = wpa.instance_id
864 AND wl.lookup_type = 'WFSTD_BOOLEAN'
865 AND ((wias.activity_status = 'COMPLETE'
866 AND (wat.result_code = wias.activity_result_code
867 OR wat.result_code = '*'
868 AND NOT EXISTS (SELECT NULL
869 FROM wf_activity_transitions
870 WHERE from_process_activity = wpa.instance_id
871 AND result_code = wias.activity_result_code))
872 AND wl.lookup_code = 'T')
873 OR (NOT (wias.activity_status = 'COMPLETE'
874 AND (wat.result_code = wias.activity_result_code
875 OR wat.result_code = '*'
876 AND NOT EXISTS (SELECT NULL
877 FROM wf_activity_transitions
878 WHERE from_process_activity = wpa.instance_id
879 AND result_code = wias.activity_result_code)))
880 OR wias.activity_status IS NULL) /* end of OR (NOT... */
881 AND wl.lookup_code = 'F');
882
883 transition transition_instances_cursor%ROWTYPE;
884
885 l_tab varchar2(30) := wf_core.local_chr(9);
886
887 BEGIN
888
889 OPEN transition_instances_cursor;
890 LOOP
891 FETCH transition_instances_cursor INTO transition;
892 EXIT WHEN transition_instances_cursor%NOTFOUND;
893
894 /*
895 dbms_output.put_line('TRANSITION: '|| transition.from_icon || ', '||
896 transition.from_instance || ', '||
897 transition.label || ', '||
898 transition.to_icon || ', '||
899 transition.to_instance || ', '||
900 transition.arrow_geometry|| ', '||
901 transition.visited);
902 */
903
904 append_data('TRANSITION:' ||
905 transition.from_icon || l_tab ||
906 transition.from_instance || l_tab ||
907 transition.label || l_tab ||
908 transition.to_icon || l_tab ||
909 transition.to_instance || l_tab ||
910 transition.arrow_geometry || l_tab ||
911 transition.visited);
912 END LOOP;
913
914 END get_transition_instances;
915
916 /*--------------------------------------------------------------------------
917 * GET_TRANSLATIONS
918 *
919 * Description:
920 * Public Function. Called by Java front-end (FlowDiagram.java).
921 * Given a name list concatenated with '^', parse the list and return
922 * an html page of translated list with the same order.
923 *
924 * Parameters:
925 * x_name_list - List of names to be translated, concatenated with an
926 * agreed delimiter. Currently "^" is used.
927 *
928 * Notes:
929 *
930 *-------------------------------------------------------------------------*/
931 FUNCTION get_translations(x_name_list VARCHAR2) RETURN VARCHAR2 IS
932
933 labels VARCHAR2(3000) := x_name_list;
934 translated VARCHAR2(4096) := '';
935 break_point NUMBER := 0;
936 len NUMBER := 0;
937 total NUMBER := 1;
938 token VARCHAR2(240);
939 temp VARCHAR2(30);
940 res BOOLEAN;
941
942 BEGIN
943
944 l_data := 'LABELS:';
945
946 --dbms_output.enable(1000000);
947 --dbms_output.put_line('get_translations: ' || x_name_list);
948
949 /*res := ICX_SEC.ValidateSession( c_validate_only => 'Y',
950 c_update => FALSE);
951
952 if (res = FALSE ) then
953 wf_core.raise('WFSEC_NO_SESSION');
954 end if;*/
955
956 SELECT LENGTH(x_name_list) INTO temp FROM DUAL;
957 len := TO_NUMBER(temp);
958
959 LOOP
960 SELECT INSTR(x_name_list, '^', total) INTO temp FROM DUAL;
961
962 break_point := TO_NUMBER(temp);
963 SELECT SUBSTR(x_name_list, total, break_point - total) INTO token
964 FROM DUAL;
965
966 --dbms_output.put_line('token=' || token);
967
968 translated := translated ||
969 SUBSTR(wf_core.translate(token),1, 40) || wf_core.local_chr(9);
970
971 EXIT WHEN (break_point = len);
972 total := break_point + 1;
973
974 END LOOP;
975
976 -- appending translater data to l_data
977 l_data := l_data || translated;
978
979 return l_data || NL_CHAR;
980
981 END get_translations;
982
983 END WF_TASKFLOW;