[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
the select order of both queries. The
draw_activity_list and draw_activity_details functions
take advantage of this ordering for performance reasons
so they can walk these lists in parallel.
When we find an attribute that matches
the current activity, we copy that attribute to a temp
list until we find a new activity in the attribute
list. When this happens we write out the attribute
temp list and move to the next activity.
============================================================================*/
PROCEDURE fetch_activities
(p_item_type IN VARCHAR2,
p_activity_type IN VARCHAR2,
p_effective_date IN DATE,
p_name IN VARCHAR2,
p_wf_activities_vl_tbl OUT NOCOPY wf_activities_vl_pub.wf_activities_vl_tbl_type,
p_wf_activity_attr_vl_tbl OUT NOCOPY wf_activities_vl_pub.wf_activity_attr_vl_tbl_type) IS
/*===========================================================================
CURSOR NAME: fetch_typed_activities
DESCRIPTION: Fetches all activities of a certain type for a given
item_type and effective date for the activities.
You'll notice we are selecting the activity
display name three times. The second is a placeholder
used when the result type display name.
The third occurrence is a placeholder in the
record so that I can fill in that column
with the message display name if this activity is
a notification.
PARAMETERS:
c_item_type IN Internal name of the item type
c_type IN Type of activity you would like to fetch
(PROCESS, NOTICE, FUNCTION)
c_effective_date IN
The requested effective date. Since activities can
have multiple versions and have effective date ranges
for each of those version we need a specific value
to determine which of those versions is requested.
============================================================================*/
CURSOR fetch_typed_activities (c_item_type IN VARCHAR2,
c_type IN VARCHAR2,
c_effective_date IN DATE) IS
SELECT row_id,
item_type,
name,
version,
type,
rerun,
expand_role,
protect_level,
custom_level,
begin_date,
end_date,
function,
function_type,
result_type,
cost,
read_role,
write_role,
execute_role,
icon_name,
message,
error_process,
runnable_flag,
error_item_type,
event_name,
direction,
display_name,
display_name result_type_display_name,
display_name message_display_name,
description
FROM wf_activities_vl
WHERE item_type = c_item_type
AND type = c_type
AND begin_date <= c_effective_date
AND (end_date is null or
end_date > c_effective_date)
ORDER BY display_name;
You'll notice we are selecting the activity
display name three times. The second is a placeholder
used when the result type display name.
The third occurrence is a placeholder in the
record so that I can fill in that column
with the message display name if this activity is
a notification.
PARAMETERS:
c_item_type IN Internal name of the item type
c_effective_date IN
The requested effective date. Since activities can
have multiple versions and have effective date ranges
for each of those version we need a specific value
to determine which of those versions is requested.
============================================================================*/
CURSOR fetch_all_activities (c_item_type IN VARCHAR2,
c_effective_date IN DATE) IS
SELECT row_id,
item_type,
name,
version,
type,
rerun,
expand_role,
protect_level,
custom_level,
begin_date,
end_date,
function,
function_type,
result_type,
cost,
read_role,
write_role,
execute_role,
icon_name,
message,
error_process,
runnable_flag,
error_item_type,
event_name,
direction,
display_name,
display_name result_type_display_name,
display_name message_display_name,
description
FROM wf_activities_vl
WHERE item_type = c_item_type
AND begin_date <= c_effective_date
AND (end_date is null or
end_date > c_effective_date)
ORDER BY DECODE(type, 'PROCESS', 1, 'NOTICE', 2, 'FUNCTION', 3, 'EVENT', 4,
5),
display_name;
You'll notice that the select orders the
results by activity type, activity display
name, and then by attribute sequence. The first two
order criteria are based on the requirement to
synchronize the attribute list with the activity list.
The activity list is ordered by activity type and
activity display name. When we list the activities
and their corresponding attributes we walk these lists
in parallel. When we find an attribute that matches
the current activity, we copy that attribute to a temp
list until we find a new activity in the attribute
list. When this happens we write out the attribute
temp list and move to the next activity. Thus the need
for the special order criteria.
You might also notice that we are selecting the activity
display name four times. The second is a placeholder
used when the default value is based on an
item attribute. The third occurrence is a
placeholder in the record so that I can fill in that column
with the lookup type display name if this attribute is
validated based on a lookup type. The fourth occurence
is later populated with the lookup code display name
if the default value is based on a lookup type.
PARAMETERS:
c_item_type IN Internal name of the item type
c_effective_date IN
The requested effective date. Since activities can
have multiple versions and have effective date ranges
for each of those version we need a specific value
to determine which of those versions is requested.
============================================================================*/
CURSOR fetch_activity_attributes (c_item_type IN VARCHAR2,
c_effective_date IN VARCHAR2) IS
SELECT
wact.type activity_type,
wact.display_name activity_display_name,
wact.display_name attr_default_display_name,
wact.display_name lookup_type_display_name,
wact.display_name lookup_code_display_name,
waa.row_id,
waa.activity_item_type,
waa.activity_name,
waa.activity_version,
waa.name,
waa.sequence,
waa.type,
waa.value_type,
waa.protect_level,
waa.custom_level,
waa.subtype,
waa.format,
waa.text_default,
waa.number_default,
waa.date_default,
waa.display_name,
waa.description
FROM wf_activity_attributes_vl waa,
wf_activities_vl wact
WHERE waa.activity_item_type = c_item_type
AND wact.item_type = c_item_type
AND wact.name = waa.activity_name
AND wact.version = waa.activity_version
AND wact.begin_date <= c_effective_date
AND (wact.end_date is null OR
wact.end_date > c_effective_date)
ORDER BY DECODE(wact.type, 'PROCESS', 1, 'NOTICE', 2, 'FUNCTION', 3,
'EVENT', 4, 5),
wact.display_name, waa.sequence;
** also have the option of selecting activities of a certain type into
** the pl*sql table or all activities for the given item type
*/
IF (p_name IS NOT NULL AND p_activity_type IS NOT NULL) THEN
SELECT row_id,
item_type,
name,
version,
type,
rerun,
expand_role,
protect_level,
custom_level,
begin_date,
end_date,
function,
function_type,
result_type,
cost,
read_role,
write_role,
execute_role,
icon_name,
message,
error_process,
runnable_flag,
error_item_type,
event_name,
direction,
display_name,
display_name result_type_display_name,
display_name message_display_name,
description
INTO p_wf_activities_vl_tbl(1)
FROM wf_activities_vl
WHERE item_type = p_item_type
AND type = p_activity_type
AND name = p_name
AND begin_date <= p_effective_date
AND (end_date is null or
end_date > p_effective_date);
l_wf_activity_attr_vl_tbl.delete;
** Select whether this process is runnable or not. This is
** the most optimal method of getting this info rather than
** doing this in the view since you would have to do an
** outer join on the WF_RUNNABLE_PROCESSES_V view and that
** will cause a full table scan on activities.
*/
SELECT count(*)
INTO l_runnable_process
FROM WF_RUNNABLE_PROCESSES_V
WHERE item_type = p_wf_activities_vl_tbl(l_activity_record_num).item_type
AND process_name = p_wf_activities_vl_tbl(l_activity_record_num).name;
l_wf_activity_attr_vl_tbl.delete;