[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
the select order of both queries. The
draw_message_list and draw_message_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 message, we copy that attribute to a temp
list until we find a new message in the attribute
list. When this happens we write out the attribute
temp list and move to the next activity.
============================================================================*/
PROCEDURE fetch_messages
(p_item_type IN VARCHAR2,
p_name IN VARCHAR2,
p_wf_messages_vl_tbl OUT NOCOPY wf_messages_vl_pub.wf_messages_vl_tbl_type,
p_wf_message_attr_vl_tbl OUT NOCOPY wf_messages_vl_pub.wf_message_attr_vl_tbl_type) IS
CURSOR fetch_messages (c_item_type IN VARCHAR2) IS
SELECT row_id,
type,
name,
protect_level,
custom_level,
default_priority,
read_role,
write_role,
display_name,
description,
subject,
html_body,
body
FROM wf_messages_vl
WHERE type = c_item_type
ORDER BY display_name;
You'll notice that the select orders the
results by message display name, and then
by attribute sequence. The criteria is based
on the requirement to synchronize the
attribute list with the message list. The
message list is ordered by display name.
When we list the messages and their
corresponding attributes we walk these lists
in parallel. When we find an attribute that matches
the current message, we copy that attribute to a temp
list until we find a new message in the attribute
list. When this happens we write out the attribute
temp list and move to the next message. Thus the need
for the special order criteria.
You might also notice that we are selecting the message
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
============================================================================*/
CURSOR fetch_message_attributes (c_item_type IN VARCHAR2) IS
SELECT
wm.display_name message_display_name,
wm.display_name attr_default_display_name,
wm.display_name lookup_type_display_name,
wm.display_name lookup_code_display_name,
wma.row_id,
wma.message_type,
wma.message_name,
wma.name,
wma.sequence,
wma.type,
wma.subtype,
wma.attach,
wma.value_type,
wma.protect_level,
wma.custom_level,
wma.format,
wma.text_default,
wma.number_default,
wma.date_default,
wma.display_name,
wma.description
FROM wf_message_attributes_vl wma, wf_messages_vl wm
WHERE wma.message_type = c_item_type
AND wm.type = c_item_type
AND wm.name = wma.message_name
ORDER BY wm.display_name, wma.sequence;
SELECT row_id,
type,
name,
protect_level,
custom_level,
default_priority,
read_role,
write_role,
display_name,
description,
subject,
html_body,
body
INTO p_wf_messages_vl_tbl(1)
FROM wf_messages_vl
WHERE type = p_item_type
AND name = p_name;
l_wf_message_attr_vl_tbl.delete;
l_wf_message_attr_vl_tbl.delete;
l_wf_message_attr_vl_tbl.delete;