REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \ REM dbdrv: checkfile:~PROD:~PATH:~FILE /*===========================================================================+ | Copyright (c) 1997 Oracle Corporation | | Redwood Shores, California, USA | | All rights reserved. | +===========================================================================*/ SET ARRAY 1; SET SCAN OFF; SET VERIFY OFF; WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; CREATE OR REPLACE PACKAGE BODY wf_item_types_vl_pub AS /* $Header: wfdefb.pls 26.11 2004/06/29 19:39:59 ctilley ship $ */ /*=========================================================================== PACKAGE NAME: wf_item_types_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: MODIFICATION LOG: 06 JUN 2001 JWSMITH BUG 1819232 ADA Enhancement - Added ID attr for TD tags - Added summary for table tags - Added labels for input tags 01 JAN 2002 JWSMITH BUG 2001012 - Increase l_username,l_admin_role to varchar2(320) ============================================================================*/ /*=========================================================================== PROCEDURE NAME: fetch_item_type DESCRIPTION: Fetches all the properties of a given item type into a wf_item_types_vl_tbl_type table based on the item type internal eight character name. ============================================================================*/ PROCEDURE fetch_item_type (p_name IN VARCHAR2, p_wf_item_types_vl_tbl OUT NOCOPY wf_item_types_vl_pub.wf_item_types_vl_tbl_type) IS l_record_num NUMBER := 0; BEGIN /* ** Make sure all the required parameters are set */ IF (p_name IS NULL) THEN return; END IF; /* ** Get the item type definition */ SELECT rowid, name, protect_level, custom_level, wf_selector, read_role, write_role, execute_role, display_name, description INTO p_wf_item_types_vl_tbl(1) FROM wf_item_types_vl WHERE name = p_name; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_types_vl_pub', 'fetch_item_type', p_name); wf_item_definition.Error; END fetch_item_type; /*=========================================================================== PROCEDURE NAME: draw_item_type_list DESCRIPTION: Shows the display name of an item type as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. ============================================================================*/ PROCEDURE draw_item_type_list (p_wf_item_types_vl_tbl IN wf_item_types_vl_pub.wf_item_types_vl_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Print out all item type names in the pl*sql table */ FOR l_record_num IN 1..p_wf_item_types_vl_tbl.count LOOP /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. */ wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_item_types_vl_tbl(l_record_num).name, p_effective_date, 'ITEM_TYPE', p_wf_item_types_vl_tbl(l_record_num).name, p_wf_item_types_vl_tbl(l_record_num).display_name, NULL, p_indent_level); END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_types_vl_pub', 'draw_item_type_list'); wf_item_definition.Error; END draw_item_type_list; /*=========================================================================== PROCEDURE NAME: draw_item_type_details DESCRIPTION: Shows all the details of an item type as a html view. This function uses the htp to generate its html output. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_item_type_details (p_wf_item_types_vl_tbl IN wf_item_types_vl_pub.wf_item_types_vl_tbl_type, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Draw the section title for the item type detail section */ wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_ITEM_TYPE_DETAILS'), 0); /* ** Print out all item attribute display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_item_types_vl_tbl.count LOOP /* ** Open a new table for each item_type so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary="' || wf_core.translate('WFITD_ITEM_TYPE_DETAILS') || '"'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( 'ITEM_TYPE', p_wf_item_types_vl_tbl(l_record_num).name, p_wf_item_types_vl_tbl(l_record_num).display_name, wf_core.translate('WFITD_ITEM_TYPE_NAME'), 0); /* ** Create the internal name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_item_types_vl_tbl(l_record_num).name); /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_item_types_vl_tbl(l_record_num).description); /* ** Create the selector row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_SELECTOR'), p_wf_item_types_vl_tbl(l_record_num).wf_selector); /* ** Call function to print the read/write/execute roles */ wf_item_definition_util_pub.draw_read_write_exe_details( p_wf_item_types_vl_tbl(l_record_num).read_role, p_wf_item_types_vl_tbl(l_record_num).write_role, p_wf_item_types_vl_tbl(l_record_num).execute_role, TRUE); /* ** Call function to print the customization/protection levels */ wf_item_definition_util_pub.draw_custom_protect_details( p_wf_item_types_vl_tbl(l_record_num).custom_level, p_wf_item_types_vl_tbl(l_record_num).protect_level); /* ** Table is created so close it out */ htp.tableClose; /* ** Draw a line between each attribute definition ** if this is not the last item in the list */ IF (l_record_num <> p_wf_item_types_vl_tbl.count) THEN htp.p ('
'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_types_vl_pub', 'draw_item_type_details'); wf_item_definition.Error; END draw_item_type_details; END wf_item_types_vl_pub; / -- SHOW ERRORS PACKAGE BODY wf_item_types_vl_pub; CREATE OR REPLACE PACKAGE BODY wf_item_attributes_vl_pub AS /* $Header: wfdefb.pls 26.11 2004/06/29 19:39:59 ctilley ship $ */ /*=========================================================================== PACKAGE NAME: wf_item_attributes_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PROCEDURE NAME: fetch_item_attributes DESCRIPTION: Fetches all the attributes for a given item type into a p_wf_item_attributes_vl_tbl table based on the item type internal eight character name. This function can also retrieve a single item attribute definition if the internal name along with the item type name is provided. This is especially useful if you wish to display the details for a single attribute when it is referenced from some drilldown mechanism. ============================================================================*/ PROCEDURE fetch_item_attributes (p_item_type IN VARCHAR2, p_name IN VARCHAR2, p_wf_item_attributes_vl_tbl OUT NOCOPY wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type) IS /*=========================================================================== CURSOR NAME: c_fetch_item_attributes DESCRIPTION: Fetches all attributes for the given item_type You'll notice that we are selecting the attribute display name twice. The second occurrence is simply 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. PARAMETERS: c_item_type IN Internal name of the item type ============================================================================*/ CURSOR c_fetch_item_attributes (c_item_type IN VARCHAR2) IS SELECT row_id, item_type, name, sequence, type, protect_level, custom_level, subtype, format, display_name lookup_type_display_name, display_name lookup_code_display_name, text_default, number_default, date_default, display_name, description FROM wf_item_attributes_vl WHERE item_type = c_item_type ORDER BY sequence; l_record_num NUMBER := 0; BEGIN /* ** Make sure all the required parameters are set */ IF (p_item_type IS NULL) THEN return; END IF; /* ** Check if the caller has passed a specific attribute_name to search for. ** If so then just get the row corresponding to that item_type and ** attribute_name. If not then get all rows for that item_type. */ IF (p_name IS NOT NULL) THEN BEGIN SELECT row_id, item_type, name, sequence, type, protect_level, custom_level, subtype, format, display_name lookup_type_display_name, display_name lookup_code_display_name, text_default, number_default, date_default, display_name, description INTO p_wf_item_attributes_vl_tbl(1) FROM wf_item_attributes_vl WHERE item_type = p_item_type AND name = p_name; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN RAISE; END; ELSE OPEN c_fetch_item_attributes (p_item_type); /* ** Loop through all the lookup_code rows for the given lookup_type ** filling in the p_wf_lookups_tbl */ LOOP l_record_num := l_record_num + 1; FETCH c_fetch_item_attributes INTO p_wf_item_attributes_vl_tbl(l_record_num); EXIT WHEN c_fetch_item_attributes%NOTFOUND; /* ** If the validation for this attribute is a lookup then go get the ** display name for that lookup and put it in the ** lookup_type_display_name record element */ IF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_lookup_types_pub.fetch_lookup_display( p_wf_item_attributes_vl_tbl(l_record_num).format, p_wf_item_attributes_vl_tbl(l_record_num).text_default, p_wf_item_attributes_vl_tbl(l_record_num).lookup_type_display_name, p_wf_item_attributes_vl_tbl(l_record_num).lookup_code_display_name); END IF; END LOOP; CLOSE c_fetch_item_attributes; END IF; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_attributes_vl_pub', 'fetch_item_attributes', p_item_type, p_name); wf_item_definition.Error; END fetch_item_attributes; /*=========================================================================== PROCEDURE NAME: fetch_item_attribute_display DESCRIPTION: fetch the item attribute display name based on a item type name and an internal item attribute name ============================================================================*/ PROCEDURE fetch_item_attribute_display (p_item_type IN VARCHAR2, p_internal_name IN VARCHAR2, p_display_name OUT NOCOPY VARCHAR2) IS l_display_name VARCHAR2(80); l_wf_item_attributes_vl_tbl wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type; BEGIN /* ** Fetch the item attribute record associated with this internal name */ fetch_item_attributes (p_item_type, p_internal_name, l_wf_item_attributes_vl_tbl); /* ** See if you found a row. If not, proide the user with feedback */ IF (l_wf_item_attributes_vl_tbl.count < 1) THEN l_display_name := p_internal_name||' '|| ' -- '||wf_core.translate ('WFITD_UNDEFINED')||''; ELSE l_display_name := l_wf_item_attributes_vl_tbl(1).display_name; END IF; p_display_name := l_display_name; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_attributes_pub', 'fetch_item_attribute_display', p_internal_name); END fetch_item_attribute_display; /*=========================================================================== PROCEDURE NAME: draw_item_attribute_list DESCRIPTION: Shows the display name of an item attribute as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. ============================================================================*/ PROCEDURE draw_item_attribute_list (p_wf_item_attributes_vl_tbl IN wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Create the the attributes title. Indent it to the level specified */ wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_ATTRIBUTES'), p_indent_level); /* ** Print out all item attribute display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_item_attributes_vl_tbl.count LOOP /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. */ wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_item_attributes_vl_tbl(l_record_num).item_type, p_effective_date, 'ATTRIBUTE', p_wf_item_attributes_vl_tbl(l_record_num).name, p_wf_item_attributes_vl_tbl(l_record_num).display_name, NULL, p_indent_level+1); END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_attributes_vl_pub', 'draw_item_attribute_list'); wf_item_definition.Error; END draw_item_attribute_list; /*=========================================================================== PROCEDURE NAME: draw_item_attribute_details DESCRIPTION: Shows all the details of an item attrribute as a html view. This function uses the htp to generate its html output. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_item_attribute_details (p_wf_item_attributes_vl_tbl IN wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Draw the section title for the item type detail section */ wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_ATTRIBUTE_DETAILS'), 0); /* ** Print out all item attribute display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_item_attributes_vl_tbl.count LOOP /* ** Open a new table for each attribute so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary= "' || wf_core.translate('WFITD_ATTRIBUTE_DETAILS') || '"'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( 'ATTRIBUTE', p_wf_item_attributes_vl_tbl(l_record_num).name, p_wf_item_attributes_vl_tbl(l_record_num).display_name, wf_core.translate('WFITD_ATTRIBUTE_NAME'), 0); /* ** Create the internal name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_item_attributes_vl_tbl(l_record_num).name); /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_item_attributes_vl_tbl(l_record_num).description); /* ** Create the attribute type row in the table. I've named the ** translated resource so that all I have to do is add ** WFITD_ATTR_TYPE_ to the type of resource and I get the ** translated string. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_ATTRIBUTE_TYPE'), wf_core.translate('WFITD_ATTR_TYPE_'|| p_wf_item_attributes_vl_tbl(l_record_num).type)); /* ** Create the length/format/lookup type row in the table. ** If the type is VARCHAR2 then show a length prompt ** If the type is NUMBER/DATE then show format prompt ** If the type is LOOKUP then show lookup type prompt ** If it is any other type then don't show the row at all */ IF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'VARCHAR2') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('LENGTH'), p_wf_item_attributes_vl_tbl(l_record_num).format); ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).type IN ('NUMBER', 'DATE')) THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('FORMAT'), p_wf_item_attributes_vl_tbl(l_record_num).format); ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).type IN ('URL','DOCUMENT')) THEN /* ** If it is URL or DOCUMENT, indicate where the resulting page should be displayed */ IF (NVL(p_wf_item_attributes_vl_tbl(l_record_num).format, '_top') = '_top') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_TOP')); ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).format = '_blank') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_BLANK')); ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).format = '_self') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_SELF')); ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).format = '_parent') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_PARENT')); END IF; ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('LOOKUP'), p_wf_item_attributes_vl_tbl(l_record_num).lookup_type_display_name); END IF; /* ** Create the default value row in the table. If the attribute type is based on ** a lookup then the default value must be one of the lookup codes. If so print ** the lookup code that was fetch, If this is any other attribute type then ** nvl on text value. If there is no text value then try the number ** default. If there is no number default then try the date. */ IF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), p_wf_item_attributes_vl_tbl(l_record_num).lookup_code_display_name); ELSE wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), NVL(p_wf_item_attributes_vl_tbl(l_record_num).text_default, NVL(TO_CHAR(p_wf_item_attributes_vl_tbl(l_record_num).number_default), TO_CHAR(p_wf_item_attributes_vl_tbl(l_record_num).date_default)))); END IF; htp.tableRowClose; /* ** Call function to print the customization/protection levels */ wf_item_definition_util_pub.draw_custom_protect_details( p_wf_item_attributes_vl_tbl(l_record_num).custom_level, p_wf_item_attributes_vl_tbl(l_record_num).protect_level); /* ** Table is created so close it out */ htp.tableClose; /* ** Draw a line between each attribute definition ** if this is not the last item in the list */ IF (l_record_num <> p_wf_item_attributes_vl_tbl.count) THEN htp.p ('
'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_attributes_vl_pub', 'draw_item_attribute_details'); wf_item_definition.Error; END draw_item_attribute_details; END wf_item_attributes_vl_pub; / -- SHOW ERRORS PACKAGE BODY wf_item_attributes_vl_pub; CREATE OR REPLACE PACKAGE BODY wf_activities_vl_pub AS /* $Header: wfdefb.pls 26.11 2004/06/29 19:39:59 ctilley ship $ */ /*=========================================================================== PACKAGE NAME: wf_activities_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PROCEDURE NAME: fetch_activities DESCRIPTION: Fetches all the activities and each activities associate attributes for a given item type into a p_wf_activities_vl_tbl table and a p_wf_activity_attr_vl_tbl table based on the item type internal eight character name and the effective_date for the activities. This function can retrieve just one type of activity list like only the processes or notification or it can retrieve all the activity types for a given item type. This function can also retrieve a single activity definition if the internal name along with the item type name is provided. This is especially useful if you wish to display the details for a single activity when it is referenced from some drilldown mechanism. The p_wf_activities_vl_tbl table and the p_wf_activity_attr_vl_tbl table are synchronized by 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; /*=========================================================================== CURSOR NAME: fetch_typed_activities DESCRIPTION: Fetches all activities 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_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; /*=========================================================================== CURSOR NAME: fetch_activity_attributes DESCRIPTION: Fetches all activity attributes for the given item_type and effective date for the activity. 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; l_record_num NUMBER := 0; l_throwaway VARCHAR2(1); BEGIN /* ** Make sure all the required parameters are set */ IF (p_item_type IS NULL) THEN return; END IF; /* ** Check if the caller has passed a specific activity_name to search for. ** If so then just get the row corresponding to that item_type and ** activity_name. If not then get all rows for that item_type. You ** 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); /* ** Get the display name for the result type for this activity and ** put it in the result_type_display_name field */ IF (NVL(p_wf_activities_vl_tbl(1).result_type, '*') <> '*') THEN wf_lookup_types_pub.fetch_lookup_display( p_wf_activities_vl_tbl(1).result_type, null, p_wf_activities_vl_tbl(1).result_type_display_name, l_throwaway); END IF; /* ** If this is a notification activity and the message is populated ** then go get the display name for the message and put it in ** message_display_name */ IF (p_wf_activities_vl_tbl(1).message IS NOT NULL) THEN wf_messages_vl_pub.fetch_message_display ( p_wf_activities_vl_tbl(1).item_type, p_wf_activities_vl_tbl(1).message, p_wf_activities_vl_tbl(1).message_display_name); END IF; /* ** If you pass in an item_type and an activity type then get all ** activities relating to the given */ ELSIF (p_name IS NULL AND p_activity_type IS NOT NULL) THEN OPEN fetch_typed_activities (p_item_type, p_activity_type, p_effective_date); /* ** Loop through the specific type of activity row ** for the given item_type filling in the p_wf_activities_vl_tbl */ LOOP l_record_num := l_record_num + 1; FETCH fetch_typed_activities INTO p_wf_activities_vl_tbl(l_record_num); EXIT WHEN fetch_typed_activities%NOTFOUND; /* ** Get the display name for the result type for this activity and ** put it in the result_type_display_name field */ IF (NVL(p_wf_activities_vl_tbl(l_record_num).result_type, '*') <> '*') THEN wf_lookup_types_pub.fetch_lookup_display( p_wf_activities_vl_tbl(l_record_num).result_type, null, p_wf_activities_vl_tbl(l_record_num).result_type_display_name, l_throwaway); END IF; /* ** If this is a notification activity and the message is populated ** then go get the display name for the message and put it in ** message_display_name */ IF (p_wf_activities_vl_tbl(l_record_num).message IS NOT NULL) THEN wf_messages_vl_pub.fetch_message_display ( p_wf_activities_vl_tbl(l_record_num).item_type, p_wf_activities_vl_tbl(l_record_num).message, p_wf_activities_vl_tbl(l_record_num).message_display_name); END IF; END LOOP; CLOSE fetch_typed_activities; ELSIF (p_name IS NULL AND p_activity_type IS NULL) THEN OPEN fetch_all_activities (p_item_type, p_effective_date); /* ** Loop through all the activitiy rows for the given item_type ** filling in the p_wf_activities_vl_tbl */ LOOP l_record_num := l_record_num + 1; FETCH fetch_all_activities INTO p_wf_activities_vl_tbl(l_record_num); EXIT WHEN fetch_all_activities%NOTFOUND; /* ** Get the display name for the result type for this activity and ** put it in the result_type_display_name field */ IF (NVL(p_wf_activities_vl_tbl(l_record_num).result_type, '*') <> '*') THEN wf_lookup_types_pub.fetch_lookup_display( p_wf_activities_vl_tbl(l_record_num).result_type, null, p_wf_activities_vl_tbl(l_record_num).result_type_display_name, l_throwaway); END IF; /* ** If this is a notification activity and the message is populated ** then go get the display name for the message and put it in ** message_display_name */ IF (p_wf_activities_vl_tbl(l_record_num).message IS NOT NULL) THEN wf_messages_vl_pub.fetch_message_display ( p_wf_activities_vl_tbl(l_record_num).item_type, p_wf_activities_vl_tbl(l_record_num).message, p_wf_activities_vl_tbl(l_record_num).message_display_name); END IF; END LOOP; CLOSE fetch_all_activities; OPEN fetch_activity_attributes (p_item_type, p_effective_date); l_record_num := 0; /* ** Loop through all the activitiy rows for the given item_type ** filling in the p_wf_activities_vl_tbl */ LOOP l_record_num := l_record_num + 1; FETCH fetch_activity_attributes INTO p_wf_activity_attr_vl_tbl(l_record_num); EXIT WHEN fetch_activity_attributes%NOTFOUND; /* ** If the validation for this attribute is a lookup then go get the ** display name for that lookup and put it in the ** lookup_type_display_name record element */ IF (p_wf_activity_attr_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_lookup_types_pub.fetch_lookup_display( p_wf_activity_attr_vl_tbl(l_record_num).format, p_wf_activity_attr_vl_tbl(l_record_num).text_default, p_wf_activity_attr_vl_tbl(l_record_num).lookup_type_display_name, p_wf_activity_attr_vl_tbl(l_record_num).lookup_code_display_name); END IF; /* ** If the default value for this attribute is an item attribute then ** populate the attr_default_display_name with the item attribute display ** name */ IF (p_wf_activity_attr_vl_tbl(l_record_num).value_type = 'ITEMATTR') THEN wf_item_attributes_vl_pub.fetch_item_attribute_display( p_wf_activity_attr_vl_tbl(l_record_num).activity_item_type, p_wf_activity_attr_vl_tbl(l_record_num).text_default, p_wf_activity_attr_vl_tbl(l_record_num).attr_default_display_name); END IF; END LOOP; CLOSE fetch_activity_attributes; END IF; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_activities_vl_pub', 'fetch_activities', p_item_type, p_activity_type, TO_CHAR(p_effective_date), p_name); wf_item_definition.Error; END fetch_activities; /*=========================================================================== PROCEDURE NAME: fetch_draw_activity_details DESCRIPTION: Fetches and draws a single activity for a given item type. This function is basically a cover for the fetch_activities and draw_activity_details routines. ============================================================================*/ PROCEDURE fetch_draw_activity_details (p_item_type IN VARCHAR2, p_activity_type IN VARCHAR2, p_effective_date IN VARCHAR2, p_name IN VARCHAR2) IS l_username varchar2(320); -- Username to query l_wf_activities_vl_tbl wf_activities_vl_pub.wf_activities_vl_tbl_type; l_wf_activity_attr_vl_tbl wf_activities_vl_pub.wf_activity_attr_vl_tbl_type; l_effective_date DATE; l_date_date DATE; l_valid_date BOOLEAN; l_print_date VARCHAR2(80); l_expected_format VARCHAR2(80); BEGIN -- Check session and current user wfa_sec.GetSession(l_username); /* ** Get the NLS Date format that is currently set. All to_char of ** date values should use the l_expected_format */ wf_item_definition_util_pub.validate_date ( TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'), l_date_date, l_valid_date, l_expected_format); l_effective_date := TO_DATE(p_effective_date, 'YYYY/MM/DD HH24:MI:SS'); l_print_date := TO_CHAR(l_effective_date, l_expected_format); /* ** Create a standard title page with the item_type display name as the title */ wf_item_definition.draw_header( p_item_type, l_print_date, 'DISPLAY'); /* ** Give me a blank line if this is a process activity ** Any other type of activity is handled correctly by ** the draw skipped activity headers function */ IF (p_activity_type = 'PROCESS') THEN htp.p('

'); END IF; /* ** Get the activity definition */ wf_activities_vl_pub.fetch_activities (p_item_type, p_activity_type, l_effective_date, p_name, l_wf_activities_vl_tbl, l_wf_activity_attr_vl_tbl); /* ** Draw the activity definition details */ wf_activities_vl_pub.draw_activity_details (l_wf_activities_vl_tbl, l_wf_activity_attr_vl_tbl, l_effective_date, 0, FALSE, FALSE); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_activities_vl_pub', 'fetch_draw_activity_details', p_item_type, p_activity_type, p_effective_date, p_name); wf_item_definition.Error; END fetch_draw_activity_details; /*=========================================================================== PROCEDURE NAME: draw_activity_list DESCRIPTION: Shows the display name of an activity along with any activity attributes for that activity that have been passed in as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. When we find an attribute that matches the current activity, we copy that attribute and all that follow for that activity to a temp list until we find a new activity in the attribute list. When this happens we write out the attributes using the draw_activity_attr_list. ============================================================================*/ PROCEDURE draw_activity_list (p_wf_activities_vl_tbl IN wf_activities_vl_pub.wf_activities_vl_tbl_type, p_wf_activity_attr_vl_tbl IN wf_activities_vl_pub.wf_activity_attr_vl_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_activity_record_num NUMBER := 1; l_attr_record_num NUMBER := 1; l_cur_attr_record_num NUMBER := 1; l_highest_activity NUMBER := 1; ii NUMBER := 0; l_activity_type VARCHAR2(8); l_summary_section_title VARCHAR2(240); l_wf_activity_attr_vl_tbl wf_activities_vl_pub.wf_activity_attr_vl_tbl_type; BEGIN l_activity_type := 'UNSET'; /* ** Print out all item attribute display names in the pl*sql table */ FOR l_activity_record_num IN 1..p_wf_activities_vl_tbl.count LOOP /* ** Check to see if the activity type has changed since the last ** that you printed. If so then create the title. If this is a \ ** special type of activity like a folder then ** don't reset your context */ IF (l_activity_type <> p_wf_activities_vl_tbl(l_activity_record_num).type AND p_wf_activities_vl_tbl(l_activity_record_num).type <> 'FOLDER') THEN /* ** Reset the activity type to the local name */ l_activity_type := p_wf_activities_vl_tbl(l_activity_record_num).type; /* ** The type has changed so print the proper title */ IF (l_activity_type = 'PROCESS') THEN /* ** Set the the processes title. */ l_summary_section_title := wf_core.translate('PROCESSES'); /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 2; ELSIF (l_activity_type = 'NOTICE') THEN /* ** Set the the notifications title. */ l_summary_section_title := wf_core.translate('WFITD_NOTIFICATIONS'); /* ** Check to see if you skipped the processes section in case ** there weren't any. If so print out the header for the processes ** here. */ wf_item_definition_util_pub.activity_titles_list ( l_highest_activity, 2, p_indent_level); /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 3; ELSIF (l_activity_type = 'FUNCTION') THEN /* ** Set the functions title. */ l_summary_section_title := wf_core.translate('WFITD_FUNCTIONS'); /* ** Check to see if you skipped the processes and or Notifications ** section in case there weren't any. If so print out the ** header for the processes and/or notificaitons here. */ wf_item_definition_util_pub.activity_titles_list ( l_highest_activity, 3, p_indent_level); /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 4; ELSIF (l_activity_type = 'EVENT') THEN /* ** Set the functions title. */ l_summary_section_title := wf_core.translate('WFITD_EVENTS'); /* ** Check to see if you skipped the processes and or Notifications ** section in case there weren't any. If so print out the ** header for the processes and/or notificaitons here. */ wf_item_definition_util_pub.activity_titles_list ( l_highest_activity, 4, p_indent_level); /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 5; END IF; /* ** Create the the activity type summary title. ** Indent it to the level specified */ wf_item_definition_util_pub.draw_summary_section_title( l_summary_section_title, p_indent_level); END IF; /* ** If this is a special type of activity like a folder then ** don't show it in the list */ IF (p_wf_activities_vl_tbl(l_activity_record_num).type <> 'FOLDER') THEN /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. */ wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_activities_vl_tbl(l_activity_record_num).item_type, p_effective_date, l_activity_type, p_wf_activities_vl_tbl(l_activity_record_num).name, p_wf_activities_vl_tbl(l_activity_record_num).display_name, NULL, p_indent_level+1); /* ** Here we look for all the activity attributes that are related ** to the current activity. The p_wf_activity_attr_vl_tbl is ** ordered by activity type, activity display name and then ** by activity attribute display name. As long as we stay ** in sync we should be able to correctly create the temp ** attribute list for the current activity. ** We could create a cursor here for the child ** attributes but that would break the rule of separating the UI layer ** and the data layer */ l_wf_activity_attr_vl_tbl.delete; l_cur_attr_record_num := 1; /* ** Make sure there the l_attr_record_num is less than or equal to ** p_wf_activity_attr_vl_tbl.count. If there is not then the ** l_attr_record_num index of 1 ** will cause a 6502-PL*SQL numeric or value error exception. */ WHILE ( l_attr_record_num <= p_wf_activity_attr_vl_tbl.count AND p_wf_activities_vl_tbl(l_activity_record_num).type = p_wf_activity_attr_vl_tbl(l_attr_record_num).activity_type AND p_wf_activities_vl_tbl(l_activity_record_num).display_name = p_wf_activity_attr_vl_tbl(l_attr_record_num).activity_display_name ) LOOP /* ** We have found an attribute for the current activity. Copy the ** contents of that list to a temp attr list and then pass the ** temp list to the activity attribute display function to display ** the results. */ l_wf_activity_attr_vl_tbl(l_cur_attr_record_num) := p_wf_activity_attr_vl_tbl(l_attr_record_num); l_attr_record_num := l_attr_record_num + 1; l_cur_attr_record_num := l_cur_attr_record_num + 1; END LOOP; /* ** If the l_cur_attr_record_num is greater than 1 then you ** must have found an attribute for this activity. Copy that ** set of attributes to a temporary pl*sql table and then ** print it out. */ IF (l_cur_attr_record_num > 1) THEN /* ** List all the activity attribute details for this message that ** we found above. Add two to the current indent level so it ** is pushed in past the start of the message list. */ wf_activities_vl_pub.draw_activity_attr_list ( l_wf_activity_attr_vl_tbl, p_effective_date, p_indent_level + 2); END IF; END IF; END LOOP; /* ** Check to see if you skipped the processes section in case ** there weren't any. If so print out the header for the processes ** here. */ wf_item_definition_util_pub.activity_titles_list ( l_highest_activity, 4, p_indent_level); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_activities_vl_pub', 'draw_activity_list'); wf_item_definition.Error; END draw_activity_list; /*=========================================================================== PROCEDURE NAME: draw_activity_attr_list DESCRIPTION: Shows the display names of activity attributes for a given activity as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. ============================================================================*/ PROCEDURE draw_activity_attr_list (p_wf_activity_attr_vl_tbl IN wf_activities_vl_pub.wf_activity_attr_vl_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Create the the activity attributes title. ** I'm using the first record to determine the type since all ** attributes in this list are for the same activity of a specfic ** type */ IF (p_wf_activity_attr_vl_tbl(1).activity_type = 'PROCESS') THEN wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_PROCESS_ATTRS'), p_indent_level); ELSIF (p_wf_activity_attr_vl_tbl(1).activity_type = 'NOTICE') THEN wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_NOTIFICATION_ATTRS'), p_indent_level); ELSE wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_FUNCTION_ATTRS'), p_indent_level); END IF; /* ** Print out all activity attribute display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_activity_attr_vl_tbl.count LOOP /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. The target name is especially complex. It is the ** combination of the activity_type, the activity_name, and the ** activity_attribute_name. This will ensure uniqueness. ie ** #ACTIVITY_ATTR:FUNCTION:CHECK_FUNDS:FUNDS_AVAILABLE */ wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_activity_attr_vl_tbl(l_record_num).activity_item_type, p_effective_date, 'ACTIVITY_ATTR', p_wf_activity_attr_vl_tbl(l_record_num).activity_type||':'|| p_wf_activity_attr_vl_tbl(l_record_num).activity_name||':'|| p_wf_activity_attr_vl_tbl(l_record_num).name, p_wf_activity_attr_vl_tbl(l_record_num).display_name, NULL, p_indent_level+1); END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_activities_vl_pub', 'draw_activity_attr_list'); wf_item_definition.Error; END draw_activity_attr_list; /*=========================================================================== PROCEDURE NAME: draw_activity_details DESCRIPTION: Shows all of the details for a list of activities along with any activity attribute details for that activity that have been passed in. The listing is shown as activity detail and then corresponding attributes and then another activity and then its When we find an attribute that matches the current activity, we copy that attribute and all that follow for that activity to a temp list until we find a new activity in the attribute list. When this happens we write out the attributes using the draw_activity_attr_details function. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_activity_details (p_wf_activities_vl_tbl IN wf_activities_vl_pub.wf_activities_vl_tbl_type, p_wf_activity_attr_vl_tbl IN wf_activities_vl_pub.wf_activity_attr_vl_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER, p_create_child_links IN BOOLEAN, p_print_skipped_titles IN BOOLEAN) IS l_username varchar2(320); -- Username to query l_activity_record_num NUMBER := 1; l_attr_record_num NUMBER := 1; l_cur_attr_record_num NUMBER := 1; l_highest_activity NUMBER := 1; ii NUMBER := 0; l_timeout_minutes NUMBER := 0; l_timeout_hours NUMBER := 0; l_timeout_days NUMBER := 0; l_runnable_process NUMBER := 0; l_date_date DATE; l_valid_date BOOLEAN; l_expected_format VARCHAR2(80); l_activity_type VARCHAR2(8); l_end_date VARCHAR2(80); l_activity_name_prompt VARCHAR2(80); l_activity_section_title VARCHAR2(240); l_wf_activity_attr_vl_tbl wf_activities_vl_pub.wf_activity_attr_vl_tbl_type; BEGIN -- Check session and current user wfa_sec.GetSession(l_username); l_activity_type := 'UNSET'; /* ** Print out all item attribute display names in the pl*sql table */ FOR l_activity_record_num IN 1..p_wf_activities_vl_tbl.count LOOP /* ** Check to see if the activity type has changed since the last ** that you printed. If so then create the title. If this is a ** special type of activity like a folder then ** don't reset your context */ IF (l_activity_type <> p_wf_activities_vl_tbl(l_activity_record_num).type AND p_wf_activities_vl_tbl(l_activity_record_num).type <> 'FOLDER') THEN /* ** Reset the activity type to the local name */ l_activity_type := p_wf_activities_vl_tbl(l_activity_record_num).type; /* ** The type has changed so print the proper title for the region ** ** Also set the appropriate prompt for the internal name for the ** activity prompt */ IF (l_activity_type = 'PROCESS') THEN /* ** Put on the the processes title. */ l_activity_section_title := wf_core.translate('WFITD_PROCESS_DETAILS'); l_activity_name_prompt := wf_core.translate('WFITD_PROCESS_NAME'); /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 2; ELSIF (l_activity_type = 'NOTICE') THEN /* ** Put on the the processes title. */ l_activity_section_title := wf_core.translate('WFITD_NOTIFICATION_DETAILS'); l_activity_name_prompt := wf_core.translate('WFITD_NOTIFICATION_NAME'); /* ** Check to see if you skipped the processes section in case ** there weren't any. If so print out the header for the processes ** here. */ IF (p_print_skipped_titles = TRUE) THEN wf_item_definition_util_pub.activity_titles_details ( l_highest_activity, 2); END IF; /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 3; ELSIF (l_activity_type = 'FUNCTION') THEN /* ** Put on the the processes title. */ l_activity_section_title := wf_core.translate('WFITD_FUNCTION_DETAILS'); l_activity_name_prompt := wf_core.translate('WFITD_FUNCTION_NAME'); /* ** Check to see if you skipped the processes and or Notifications ** section in case there weren't any. If so print out the ** header for the processes and/or notificaitons here. */ IF (p_print_skipped_titles = TRUE) THEN wf_item_definition_util_pub.activity_titles_details ( l_highest_activity, 3); END IF; /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 4; ELSIF (l_activity_type = 'EVENT') THEN /* ** Put on the the event title. */ l_activity_section_title := wf_core.translate('WFITD_EVENT_DETAILS'); l_activity_name_prompt := wf_core.translate('WFITD_EVENT_NAME'); /* ** Check to see if you skipped the events, processes ** and or Notifications section in case there weren't ** any. If so print out the header for the processes ** and/or notificaitons here. */ IF (p_print_skipped_titles = TRUE) THEN wf_item_definition_util_pub.activity_titles_details ( l_highest_activity, 4); END IF; /* ** Set the indicator for the highest activity found. This is ** used later to print any missing headers. */ l_highest_activity := 5; END IF; /* ** If you are creating the Notification of Function detail ** list then skip a couple of rows since you don't have an ** interrupt from the parent to perform this function */ IF (l_activity_type IN ('NOTICE', 'FUNCTION', 'EVENT')) THEN htp.p ('

'); END IF; /* ** Draw the section title for the activity detail section */ wf_item_definition_util_pub.draw_detail_section_title ( l_activity_section_title, 0); END IF; /* ** If this is a special type of activity like a folder then ** don't print it */ IF (p_wf_activities_vl_tbl(l_activity_record_num).type <> 'FOLDER') THEN /* ** Open a new table for each attribute so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary=""'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( p_wf_activities_vl_tbl(l_activity_record_num).type, p_wf_activities_vl_tbl(l_activity_record_num).name, p_wf_activities_vl_tbl(l_activity_record_num).display_name, l_activity_name_prompt, 0); /* ** Create the internal name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_activities_vl_tbl(l_activity_record_num).name); /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_activities_vl_tbl(l_activity_record_num).description); /* ** If this is a process or notification activity, only show the function row ** if the function field is populate. If this is a function activity then ** always create the function row */ IF ((p_wf_activities_vl_tbl(l_activity_record_num).type = 'PROCESS' AND p_wf_activities_vl_tbl(l_activity_record_num).function IS NOT NULL) OR p_wf_activities_vl_tbl(l_activity_record_num).type IN ('NOTICE', 'FUNCTION')) THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_FUNCTION'), p_wf_activities_vl_tbl(l_activity_record_num).function); wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_FUNCTION_TYPE'), NVL(p_wf_activities_vl_tbl(l_activity_record_num).function_type, 'PL/SQL')); END IF; /* ** Create the result type row in the table ** Do not show the result type field if it is equal to * which ** occurs when a notification is FYI and doesn't expect a response */ IF (NVL(p_wf_activities_vl_tbl(l_activity_record_num).result_type, '*') <> '*') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_RESULT_TYPE'), p_wf_activities_vl_tbl(l_activity_record_num).result_type_display_name); END IF; /* ** There are a number of activity attributes that are tied to the ** type of activity you are printing. Encapsulated here are most ** of those differences since they are usually at the bottom of the ** main dialog in the Builder. */ IF (l_activity_type = 'PROCESS') THEN /* ** 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; IF (l_runnable_process > 0) THEN /* ** Create the runnable row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_RUNNABLE'), wf_core.translate('WFITD_YES')); ELSE /* ** Create the runnable row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_RUNNABLE'), wf_core.translate('WFITD_NO')); END IF; ELSIF (l_activity_type = 'NOTICE') THEN /* ** Create the message name row in the table ** Only create a link to the message details if you are ** drawing the message details. IN ( 'm not going to use ** the create_hotlink_to_details here. IN ( could */ IF (p_create_child_links = TRUE) THEN wf_item_definition_util_pub.create_hotlink_to_details( p_wf_activities_vl_tbl(l_activity_record_num).item_type, p_effective_date, 'MESSAGE', p_wf_activities_vl_tbl(l_activity_record_num).message, p_wf_activities_vl_tbl(l_activity_record_num).message_display_name, wf_core.translate('MESSAGE_NAME'), 0); ELSE wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('MESSAGE_NAME'), p_wf_activities_vl_tbl(l_activity_record_num).message_display_name); END IF; /* ** Create the expand roles in the table */ IF (p_wf_activities_vl_tbl(l_activity_record_num).expand_role = 'Y') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_EXPAND_ROLES'), wf_core.translate('WFITD_YES')); ELSE wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_EXPAND_ROLES'), wf_core.translate('WFITD_NO')); END IF; ELSIF (l_activity_type = 'FUNCTION') THEN /* ** Create the cost row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFMON_COST'), TO_CHAR((p_wf_activities_vl_tbl(l_activity_record_num).cost/100))); ELSIF (l_activity_type = 'EVENT') THEN /* ** Create the event name and direction rows in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_EVENT'), p_wf_activities_vl_tbl(l_activity_record_num).event_name); wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DIRECTION'), p_wf_activities_vl_tbl(l_activity_record_num).direction); END IF; /* ** Create the icon name in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_ICON'), p_wf_activities_vl_tbl(l_activity_record_num).icon_name); /* ** Create the error item type name in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFMON_ERROR_TYPE'), p_wf_activities_vl_tbl(l_activity_record_num).error_item_type); /* ** Create the error process name in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFMON_ERROR_PROCESS'), p_wf_activities_vl_tbl(l_activity_record_num).error_process); /* ** Get the expected format for the date. You'll notice that I've ** added a time element to sysdate. That ensures the time format ** will be included in l_expected_format. You don't care about the ** validation stuff */ wf_item_definition_util_pub.validate_date ( TO_CHAR(sysdate, 'DD-MON-YY')||' 00:00:00', l_date_date, l_valid_date, l_expected_format); /* ** Only populate the l_end_date for the continuation of the effective ** date if there is an end date otherwise leave it null. */ IF (p_wf_activities_vl_tbl(l_activity_record_num).end_date IS NOT NULL) THEN l_end_date := ' - ' || TO_CHAR(p_wf_activities_vl_tbl(l_activity_record_num).end_date, l_expected_format); ELSE l_end_date := ''; END IF; /* ** Create the effective date range in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_EFFECTIVE'), TO_CHAR(p_wf_activities_vl_tbl(l_activity_record_num).begin_date, l_expected_format)|| l_end_date); /* ** Create the loop reset description */ IF (p_wf_activities_vl_tbl(l_activity_record_num).rerun = 'RESET') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_LOOP_RESET'), wf_core.translate('WFITD_LOOP_RESET_VALUE')); ELSIF (p_wf_activities_vl_tbl(l_activity_record_num).rerun = 'LOOP') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_LOOP_RESET'), wf_core.translate('WFITD_LOOP_RESET_LOOP')); ELSE wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_LOOP_RESET'), wf_core.translate('WFITD_LOOP_RESET_IGNORE')); END IF; /* ** Create the version row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_VERSION'), p_wf_activities_vl_tbl(l_activity_record_num).version); /* ** Call function to print the read/write/execute roles */ wf_item_definition_util_pub.draw_read_write_exe_details( p_wf_activities_vl_tbl(l_activity_record_num).read_role, p_wf_activities_vl_tbl(l_activity_record_num).write_role, p_wf_activities_vl_tbl(l_activity_record_num).execute_role, TRUE); /* ** Call function to print the customization/protection levels */ wf_item_definition_util_pub.draw_custom_protect_details( p_wf_activities_vl_tbl(l_activity_record_num).custom_level, p_wf_activities_vl_tbl(l_activity_record_num).protect_level); /* ** Table is created so close it out */ htp.tableClose; /* ** Here we look for all the activity attributes that are related to ** the current activity. The p_wf_activity_attr_vl_tbl is ** ordered by activty type (PROCESS, NOTICE, FUNCTION) then by ** display name and then by activity attribute ** display name. As long as we stay in sync we should be ** able to correctly create the temp attribute list ** for the current activity. We could create a cursor ** here for the child attributes but that would break ** the rule of separating the UI layer and the data layer */ l_wf_activity_attr_vl_tbl.delete; l_cur_attr_record_num := 1; /* ** Make sure there is at least on record in the activity attribute ** list. If there is not then the l_attr_record_num index of 1 ** will cause a 6502-PL*SQL numeric or value error exception. */ WHILE ( l_attr_record_num <= p_wf_activity_attr_vl_tbl.count AND p_wf_activities_vl_tbl(l_activity_record_num).type = p_wf_activity_attr_vl_tbl(l_attr_record_num).activity_type AND p_wf_activities_vl_tbl(l_activity_record_num).display_name = p_wf_activity_attr_vl_tbl(l_attr_record_num).activity_display_name ) LOOP /* ** We have found an attribute for the current activity. Copy the ** contents of that list to a temp attr list and then pass the ** temp list to the activity_attribute display function to display ** the results. */ l_wf_activity_attr_vl_tbl(l_cur_attr_record_num) := p_wf_activity_attr_vl_tbl(l_attr_record_num); l_attr_record_num := l_attr_record_num + 1; l_cur_attr_record_num := l_cur_attr_record_num + 1; END LOOP; /* ** If the l_cur_attr_record_num is greater than 1 then you ** must have found an attribute for this activity. Copy that ** set of attributes to a temporary pl*sql table and then ** print it out. */ IF (l_cur_attr_record_num > 1) THEN /* ** Put in a couple of blank lines between the current activity ** and its attributes */ htp.p('

'); /* ** List all the activity attribute details for this activity that ** we found above. */ wf_activities_vl_pub.draw_activity_attr_details ( l_wf_activity_attr_vl_tbl, 1); /* ** If you still have more activities to process and the next activity is ** the same type as the current one then put in a ** few blank lines and put in another Activity Details Header */ IF (l_activity_record_num < p_wf_activities_vl_tbl.count AND l_activity_type = p_wf_activities_vl_tbl(l_activity_record_num + 1).type) THEN /* ** Put in a couple of blank lines between the current activity ** attributes and the next activity */ htp.p('

'); /* ** Draw the section title for the Activity detail section */ wf_item_definition_util_pub.draw_detail_section_title ( l_activity_section_title, 0); END IF; END IF; /* ** Draw a line between each activity definition ** if this is not the last item in the list and if there ** are no attributes in the attribute list for this activity and ** there are more activities of the same type */ IF (l_activity_record_num < p_wf_activities_vl_tbl.count AND l_cur_attr_record_num = 1 AND l_activity_type = p_wf_activities_vl_tbl(l_activity_record_num + 1).type) THEN htp.p ('
'); END IF; END IF; END LOOP; /* ** Check to see if you skipped the processes section in case ** there weren't any. If so print out the header for the processes ** here. */ IF (p_print_skipped_titles = TRUE) THEN wf_item_definition_util_pub.activity_titles_details ( l_highest_activity, 4); END IF; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_activities_vl_pub', 'draw_activity_details'); wf_item_definition.Error; END draw_activity_details; /*=========================================================================== PROCEDURE NAME: draw_activity_attr_details DESCRIPTION: Shows all of the details for a list of activity attributes for that have been passed in. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_activity_attr_details (p_wf_activity_attr_vl_tbl IN wf_activities_vl_pub.wf_activity_attr_vl_tbl_type, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; l_activity_name_prompt VARCHAR2(80); l_activity_attr_name_prompt VARCHAR2(80); l_activity_section_title VARCHAR2(240); BEGIN /* ** Create the the activity attributes title. ** I'm using the first record to determine the type since all ** attributes in this list are for the same activity of a specfic ** type */ IF (p_wf_activity_attr_vl_tbl(1).activity_type = 'PROCESS') THEN /* ** Put on the the processes title. */ l_activity_section_title := wf_core.translate('WFITD_PROCESS_ATTR_DETAILS'); l_activity_name_prompt := wf_core.translate('WFITD_PROCESS_NAME'); l_activity_attr_name_prompt := wf_core.translate('WFITD_PROCESS_ATTR_NAME'); ELSIF (p_wf_activity_attr_vl_tbl(1).activity_type = 'NOTICE') THEN /* ** Put on the the notification title. */ l_activity_section_title := wf_core.translate('WFITD_NOTIFICATION_ATTR_DETAIL'); l_activity_name_prompt := wf_core.translate('WFITD_NOTIFICATION_NAME'); l_activity_attr_name_prompt := wf_core.translate('WFITD_NOTIFICATION_ATTR_NAME'); ELSIF (p_wf_activity_attr_vl_tbl(1).activity_type = 'FUNCTION') THEN /* ** Put on the the function title. */ l_activity_section_title := wf_core.translate('WFITD_FUNCTION_ATTR_DETAILS'); l_activity_name_prompt := wf_core.translate('WFITD_FUNCTION_NAME'); l_activity_attr_name_prompt := wf_core.translate('WFITD_FUNCTION_ATTR_NAME'); END IF; /* ** Draw the section title for the activity attribute detail section */ wf_item_definition_util_pub.draw_detail_section_title ( l_activity_section_title, 0); /* ** Print out all meesage attribute display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_activity_attr_vl_tbl.count LOOP /* ** Open a new table for each attribute so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary=""'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( 'ACTIVITY_ATTR', p_wf_activity_attr_vl_tbl(l_record_num).activity_type||':'|| p_wf_activity_attr_vl_tbl(l_record_num).activity_name||':'|| p_wf_activity_attr_vl_tbl(l_record_num).name, p_wf_activity_attr_vl_tbl(l_record_num).display_name, l_activity_attr_name_prompt, 0); /* ** Create the internal name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_activity_attr_vl_tbl(l_record_num).name); /* ** Create the activity display name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( l_activity_name_prompt, p_wf_activity_attr_vl_tbl(l_record_num).activity_display_name); /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_activity_attr_vl_tbl(l_record_num).description); /* ** Create the attribute type row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_ATTRIBUTE_TYPE'), wf_core.translate('WFITD_ATTR_TYPE_'|| p_wf_activity_attr_vl_tbl(l_record_num).type)); /* ** Create the length/format/lookup type row in the table. ** If the type is VARCHAR2 then show a length prompt ** If the type is NUMBER/DATE then show format prompt ** If the type is LOOKUP then show lookup type prompt ** If it is any other type then don't show the row at all */ IF (p_wf_activity_attr_vl_tbl(l_record_num).type = 'VARCHAR2') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('LENGTH'), p_wf_activity_attr_vl_tbl(l_record_num).format); ELSIF (p_wf_activity_attr_vl_tbl(l_record_num).type IN ('DATE', 'NUMBER')) THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('FORMAT'), p_wf_activity_attr_vl_tbl(l_record_num).format); ELSIF (p_wf_activity_attr_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('LOOKUP'), p_wf_activity_attr_vl_tbl(l_record_num).lookup_type_display_name); ELSIF (p_wf_activity_attr_vl_tbl(l_record_num).type IN ('URL','DOCUMENT')) THEN /* ** If it is URL or DOCUMENT, indicate where the resulting page should be displayed */ IF (NVL(p_wf_activity_attr_vl_tbl(l_record_num).format, '_top') = '_top') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_TOP')); ELSIF (p_wf_activity_attr_vl_tbl(l_record_num).format = '_blank') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_BLANK')); ELSIF (p_wf_activity_attr_vl_tbl(l_record_num).format = '_self') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_SELF')); ELSIF (p_wf_activity_attr_vl_tbl(l_record_num).format = '_parent') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_PARENT')); END IF; END IF; /* ** Create the default type row */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_TYPE'), wf_core.translate('WFITD_DEFAULT_TYPE_'|| p_wf_activity_attr_vl_tbl(l_record_num).value_type)); /* ** If the default value is a constant then show the default value type ** that is not null. If the default value is based on an item attribute ** then show the attr_default_display_name. */ IF (p_wf_activity_attr_vl_tbl(l_record_num).value_type = 'ITEMATTR') THEN /* ** Create the default item attribute row in the table. This is based on the ** p_wf_activity_attr_vl_tbl(l_record_num).attr_default_display_name */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), p_wf_activity_attr_vl_tbl(l_record_num).attr_default_display_name); /* ** Create the default value row in the table. If the attribute type is based on ** a lookup then the default value must be one of the lookup codes. If so print ** the lookup code that was fetch. */ ELSIF (p_wf_activity_attr_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), p_wf_activity_attr_vl_tbl(l_record_num).lookup_code_display_name); /* ** If this is any other attribute type then ** nvl on text value. If there is no text value then try the number ** default. If there is no number default then try the date. */ ELSE wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), NVL(p_wf_activity_attr_vl_tbl(l_record_num).text_default, NVL(TO_CHAR(p_wf_activity_attr_vl_tbl(l_record_num).number_default), TO_CHAR(p_wf_activity_attr_vl_tbl(l_record_num).date_default)))); END IF; /* ** Table is created so close it out */ htp.tableClose; /* ** Draw a line between each activity attribute definition ** if this is not the last item in the list */ IF (l_record_num <> p_wf_activity_attr_vl_tbl.count) THEN htp.p ('
'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_activities_vl_pub', 'draw_activity_attr_details'); wf_item_definition.Error; END draw_activity_attr_details; END wf_activities_vl_pub; / -- SHOW ERRORS PACKAGE BODY wf_activities_vl_pub; CREATE OR REPLACE PACKAGE BODY wf_messages_vl_pub AS /* $Header: wfdefb.pls 26.11 2004/06/29 19:39:59 ctilley ship $ */ /*=========================================================================== PACKAGE NAME: wf_messages_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PROCEDURE NAME: fetch_messages DESCRIPTION: Fetches all the messages and each message associate attributes for a given item type into a p_wf_messages_vl_tbl table and a p_wf_message_attr_vl_tbl table based on the item type internal eight character name. This function can retrieve a single message definition if the internal name along with the item type name is provided. This is especially useful if you wish to display the details for a single message when it is referenced from some drilldown mechanism. The p_wf_messages_vl_tbl table and the p_wf_message_attr_vl_tbl table are synchronized by 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; /*=========================================================================== CURSOR NAME: fetch_message_attributes DESCRIPTION: Fetches all message attributes for the given item_type. 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; l_record_num NUMBER := 0; BEGIN /* ** Make sure all the required parameters are set */ IF (p_item_type IS NULL) THEN return; END IF; /* ** Check if the caller has passed a specific attribute_name to search for. ** If so then just get the row corresponding to that item_type and ** attribute_name. If not then get all rows for that item_type. */ IF (p_name IS NOT NULL) THEN 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; ELSE OPEN fetch_messages (p_item_type); /* ** Loop through all the lookup_code rows for the given message ** filling in the p_wf_messages_vl_tbl */ LOOP l_record_num := l_record_num + 1; FETCH fetch_messages INTO p_wf_messages_vl_tbl(l_record_num); EXIT WHEN fetch_messages%NOTFOUND; END LOOP; CLOSE fetch_messages; l_record_num := 0; OPEN fetch_message_attributes (p_item_type); LOOP l_record_num := l_record_num + 1; FETCH fetch_message_attributes INTO p_wf_message_attr_vl_tbl( l_record_num); EXIT WHEN fetch_message_attributes%NOTFOUND; /* ** If the validation for this attribute is a lookup then go get the ** display name for that lookup and put it in the ** lookup_type_display_name record element */ IF (p_wf_message_attr_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_lookup_types_pub.fetch_lookup_display( p_wf_message_attr_vl_tbl(l_record_num).format, p_wf_message_attr_vl_tbl(l_record_num).text_default, p_wf_message_attr_vl_tbl(l_record_num).lookup_type_display_name, p_wf_message_attr_vl_tbl(l_record_num).lookup_code_display_name); END IF; /* ** If the default value for this attribute is an item attribute then ** populate the attr_default_display_name with the item attribute display ** name */ IF (p_wf_message_attr_vl_tbl(l_record_num).value_type = 'ITEMATTR') THEN wf_item_attributes_vl_pub.fetch_item_attribute_display( p_wf_message_attr_vl_tbl(l_record_num).message_type, p_wf_message_attr_vl_tbl(l_record_num).text_default, p_wf_message_attr_vl_tbl(l_record_num).attr_default_display_name); END IF; END LOOP; CLOSE fetch_message_attributes; END IF; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_messages_vl_pub', 'fetch_messages', p_item_type, p_name); wf_item_definition.Error; END fetch_messages; /*=========================================================================== PROCEDURE NAME: fetch_message_display DESCRIPTION: fetch the messagedisplay name based on a item type name and an internal item message name ============================================================================*/ PROCEDURE fetch_message_display (p_item_type IN VARCHAR2, p_internal_name IN VARCHAR2, p_display_name OUT NOCOPY VARCHAR2) IS l_display_name VARCHAR2(80); l_wf_messages_vl_tbl wf_messages_vl_pub.wf_messages_vl_tbl_type; l_wf_message_attr_vl_tbl wf_messages_vl_pub.wf_message_attr_vl_tbl_type; BEGIN /* ** Fetch the message record associated with this internal name */ fetch_messages (p_item_type, p_internal_name, l_wf_messages_vl_tbl, l_wf_message_attr_vl_tbl); /* ** See if you found a row. If not, proide the user with feedback */ IF (l_wf_messages_vl_tbl.count < 1) THEN l_display_name := p_internal_name||' '|| ' -- '||UPPER(wf_core.translate ('WFITD_UNDEFINED'))||''; ELSE l_display_name := l_wf_messages_vl_tbl(1).display_name; END IF; p_display_name := l_display_name; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_messages_pub', 'fetch_message_display', p_internal_name); END fetch_message_display; /*=========================================================================== PROCEDURE NAME: draw_message_list DESCRIPTION: Shows the display name of a message along with any message attributes for that message that have been passed in as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. When we find an attribute that matches the message activity, we copy that attribute and all that follow for that message to a temp list until we find a new activity in the attribute list. When this happens we write out the attributes using the draw_message_attr_list. ============================================================================*/ PROCEDURE draw_message_list (p_wf_messages_vl_tbl IN wf_messages_vl_pub.wf_messages_vl_tbl_type, p_wf_message_attr_vl_tbl IN wf_messages_vl_pub.wf_message_attr_vl_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_message_record_num NUMBER; l_attr_record_num NUMBER := 1; ii NUMBER := 0; l_cur_attr_record_num NUMBER := 1; l_wf_message_attr_vl_tbl wf_messages_vl_pub.wf_message_attr_vl_tbl_type; BEGIN /* ** Create the the messages title. Indent it to the level specified */ wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_MESSAGES'), p_indent_level); /* ** Print out all message display names in the pl*sql table */ FOR l_message_record_num IN 1..p_wf_messages_vl_tbl.count LOOP /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. */ wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_messages_vl_tbl(l_message_record_num).type, p_effective_date, 'MESSAGE', p_wf_messages_vl_tbl(l_message_record_num).name, p_wf_messages_vl_tbl(l_message_record_num).display_name, NULL, p_indent_level+1); /* ** Here we look for all the message attributes that are related to the ** current message. The p_wf_message_attr_vl_tbl is ordered by display ** name and then by message attribute display name. As long as we stay ** in sync we should be able to correctly create the temp attribute list ** for the current message. We could create a cursor here for the child ** attributes but that would break the rule of separating the UI layer ** and the data layer */ l_wf_message_attr_vl_tbl.delete; l_cur_attr_record_num := 1; /* ** Make sure there is at least on record in the message attribute ** list. If there is not then the l_attr_record_num index of 1 ** will cause a 6502-PL*SQL numeric or value error exception. */ WHILE ( l_attr_record_num <= p_wf_message_attr_vl_tbl.count AND p_wf_messages_vl_tbl(l_message_record_num).display_name = p_wf_message_attr_vl_tbl(l_attr_record_num).message_display_name ) LOOP /* ** We have found an attribute for the current message. Copy the ** contents of that list to a temp attr list and then pass the ** temp list to the message_attribute display function to display ** the results. */ l_wf_message_attr_vl_tbl(l_cur_attr_record_num) := p_wf_message_attr_vl_tbl(l_attr_record_num); l_attr_record_num := l_attr_record_num + 1; l_cur_attr_record_num := l_cur_attr_record_num + 1; END LOOP; /* ** If the l_cur_attr_record_num is greater than 1 then you ** must have found an attribute for this message. Copy that ** set of attributes to a temporary pl*sql table and then ** print it out. */ IF (l_cur_attr_record_num > 1) THEN /* ** List all the message attribute details for this message that ** we found above. Add two to the current indent level so it ** is pushed in past the start of the message list. */ wf_messages_vl_pub.draw_message_attr_list ( l_wf_message_attr_vl_tbl, p_effective_date, p_indent_level + 2); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_messages_vl_pub', 'draw_message_list'); wf_item_definition.Error; END draw_message_list; /*=========================================================================== PROCEDURE NAME: draw_message_attr_list DESCRIPTION: Shows the display names of message attributes for a given message as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. ============================================================================*/ PROCEDURE draw_message_attr_list (p_wf_message_attr_vl_tbl IN wf_messages_vl_pub.wf_message_attr_vl_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Create the the messages title. Indent it to the level specified */ wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_MESSAGE_ATTRS'), p_indent_level); /* ** Print out all message display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_message_attr_vl_tbl.count LOOP /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. */ IF (p_wf_message_attr_vl_tbl(l_record_num).name <> 'RESULT') THEN wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_message_attr_vl_tbl(l_record_num).message_type, p_effective_date, 'MESSAGE_ATTR', p_wf_message_attr_vl_tbl(l_record_num).message_name||':'|| p_wf_message_attr_vl_tbl(l_record_num).name, p_wf_message_attr_vl_tbl(l_record_num).display_name, NULL, p_indent_level+1); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_messages_vl_pub', 'draw_message_attr_list'); wf_item_definition.Error; END draw_message_attr_list; /*=========================================================================== PROCEDURE NAME: draw_message_details DESCRIPTION: Shows all of the details for a list of messages along with any message attribute details for that message that have been passed in. The listing is shown as message detail and then corresponding attributes and then another message and then its detail When we find an attribute that matches the current message, we copy that attribute and all that follow for that message to a temp list until we find a new message in the attribute list. When this happens we write out the attributes using the draw_message_attr_details function. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_message_details (p_wf_messages_vl_tbl IN wf_messages_vl_pub.wf_messages_vl_tbl_type, p_wf_message_attr_vl_tbl IN wf_messages_vl_pub.wf_message_attr_vl_tbl_type, p_indent_level IN NUMBER) IS l_message_record_num NUMBER := 1; l_attr_record_num NUMBER := 1; l_attr_marker NUMBER := 1; l_cur_attr_record_num NUMBER := 1; ii NUMBER := 0; pri VARCHAR2(80) := NULL; l_wf_message_attr_vl_tbl wf_messages_vl_pub.wf_message_attr_vl_tbl_type; BEGIN /* ** Draw the section title for the Message detail section */ wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_MESSAGE_DETAILS'), 0); /* ** Print out all item attribute display names in the pl*sql table */ FOR l_message_record_num IN 1..p_wf_messages_vl_tbl.count LOOP /* ** Open a new table for each attribute so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary=""'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( 'MESSAGE', p_wf_messages_vl_tbl(l_message_record_num).name, p_wf_messages_vl_tbl(l_message_record_num).display_name, wf_core.translate('WFITD_MESSAGE_NAME'), 0); /* ** Create the internal name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_messages_vl_tbl(l_message_record_num).name); /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_messages_vl_tbl(l_message_record_num).description); IF (p_wf_messages_vl_tbl(l_message_record_num).default_priority < 34) THEN pri := wf_core.translate('HIGH'); ELSIF (p_wf_messages_vl_tbl(l_message_record_num).default_priority > 66) THEN pri := wf_core.translate('LOW'); ELSE pri := wf_core.translate('MEDIUM'); END IF; /* ** Create the priority row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('PRIORITY'), pri); /* ** Create the Subject row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('SUBJECT'), p_wf_messages_vl_tbl(l_message_record_num).subject); /* ** Create the html body row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_HTML_BODY'), p_wf_messages_vl_tbl(l_message_record_num).html_body); /* ** Create the text body row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_TEXT_BODY'), p_wf_messages_vl_tbl(l_message_record_num).body); /* ** Call function to print the read/write/execute roles */ wf_item_definition_util_pub.draw_read_write_exe_details( p_wf_messages_vl_tbl(l_message_record_num).read_role, p_wf_messages_vl_tbl(l_message_record_num).write_role, null, FALSE); /* ** Call function to print the customization/protection levels */ wf_item_definition_util_pub.draw_custom_protect_details( p_wf_messages_vl_tbl(l_message_record_num).custom_level, p_wf_messages_vl_tbl(l_message_record_num).protect_level); /* ** Go find the result attribute in the list of attributes ** for this message */ l_wf_message_attr_vl_tbl.delete; l_cur_attr_record_num := 1; l_attr_marker := l_attr_record_num; /* ** Make sure there is at least on record in the message attribute ** list. If there is not then the l_attr_record_num index of 1 ** will cause a 6502-PL*SQL numeric or value error exception. ** There is only ever 1 result attribute so once l_cur_attr_record_num ** incremented then exit the loop. */ WHILE ( l_cur_attr_record_num = 1 AND l_attr_marker <= p_wf_message_attr_vl_tbl.count AND p_wf_messages_vl_tbl(l_message_record_num).display_name = p_wf_message_attr_vl_tbl(l_attr_marker).message_display_name ) LOOP /* ** We have found an attribute for the current message. Check to ** see if this is the RESULT attribute. If it is then copy the ** contents of that list to a temp attr list and then pass the ** temp list to the message_attribute display function to display ** the result. */ IF (p_wf_message_attr_vl_tbl(l_attr_marker).name = 'RESULT') THEN l_wf_message_attr_vl_tbl(l_cur_attr_record_num) := p_wf_message_attr_vl_tbl(l_attr_marker); l_cur_attr_record_num := l_cur_attr_record_num + 1; l_attr_marker := l_attr_marker + 1; END IF; l_attr_marker := l_attr_marker + 1; END LOOP; /* ** If you've found a result attribute then display it. We pass a ** special value for the p_indent_level to tell it to not show ** certain pieces of the attribute */ IF (l_cur_attr_record_num > 1) THEN wf_messages_vl_pub.draw_message_attr_details ( l_wf_message_attr_vl_tbl, -1); END IF; /* ** Table is created so close it out */ htp.tableClose; /* ** Here we look for all the message attributes that are related to the ** current message. The p_wf_message_attr_vl_tbl is ordered by display ** name and then by message attribute display name. As long as we stay ** in sync we should be able to correctly create the temp attribute list ** for the current message. We could create a cursor here for the child ** attributes but that would break the rule of separating the UI layer ** and the data layer */ l_wf_message_attr_vl_tbl.delete; l_cur_attr_record_num := 1; /* ** Make sure there is at least on record in the message attribute ** list. If there is not then the l_attr_record_num index of 1 ** will cause a 6502-PL*SQL numeric or value error exception. */ WHILE ( l_attr_record_num <= p_wf_message_attr_vl_tbl.count AND p_wf_messages_vl_tbl(l_message_record_num).display_name = p_wf_message_attr_vl_tbl(l_attr_record_num).message_display_name ) LOOP /* ** We have found an attribute for the current message. Copy the ** contents of that list to a temp attr list and then pass the ** temp list to the message_attribute display function to display ** the results. If the message attribute is named RESULT then ** Skip it since it will be displayed as part of the message ** definition */ IF (p_wf_message_attr_vl_tbl(l_attr_record_num).name <> 'RESULT') THEN l_wf_message_attr_vl_tbl(l_cur_attr_record_num) := p_wf_message_attr_vl_tbl(l_attr_record_num); l_cur_attr_record_num := l_cur_attr_record_num + 1; END IF; l_attr_record_num := l_attr_record_num + 1; END LOOP; /* ** If the l_cur_attr_record_num is greater than 1 then you ** must have found an attribute for this message. Copy that ** set of attributes to a temporary pl*sql table and then ** print it out. */ IF (l_cur_attr_record_num > 1) THEN /* ** Put in a couple of blank lines between the current message ** and its attributes */ htp.p('

'); /* ** List all the message attribute details for this message that ** we found above. */ wf_messages_vl_pub.draw_message_attr_details ( l_wf_message_attr_vl_tbl, 1); /* ** If you still have more messages to process then put in a ** few blank lines and put in another Message Details Header */ IF (l_message_record_num < p_wf_messages_vl_tbl.count) THEN /* ** Put in a couple of blank lines between the current message ** attributes and the next message */ htp.p('

'); /* ** Draw the section title for the Message detail section */ wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_MESSAGE_DETAILS'), 0); END IF; END IF; /* ** Draw a line between each message definition ** if this is not the last item in the list and if there ** are no attributes in the attribute list for this message. */ IF (l_message_record_num < p_wf_messages_vl_tbl.count AND l_cur_attr_record_num = 1) THEN htp.p ('
'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_messages_vl_pub', 'draw_message_details'); wf_item_definition.Error; END draw_message_details; /*=========================================================================== PROCEDURE NAME: draw_message_attr_details DESCRIPTION: Shows all of the details for a list of message attributes for that have been passed in. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_message_attr_details (p_wf_message_attr_vl_tbl IN wf_messages_vl_pub.wf_message_attr_vl_tbl_type, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Draw the section title for the message attribute detail section ** If p_indent_level is = -1 then you are printing some special component ** of the parent object that is stored as an attribute but really is ** shown as part of the parent item in the builder */ IF (p_indent_level <> -1) THEN wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_MESSAGE_ATTR_DETAILS'), 0); END IF; /* ** Print out all meesage attribute display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_message_attr_vl_tbl.count LOOP IF (p_indent_level <> -1) THEN /* ** Open a new table for each attribute so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary=""'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( 'MESSAGE_ATTR', p_wf_message_attr_vl_tbl(l_record_num).message_name||':'|| p_wf_message_attr_vl_tbl(l_record_num).name, p_wf_message_attr_vl_tbl(l_record_num).display_name, wf_core.translate('WFITD_MESSAGE_ATTR_NAME'), 0); /* ** Create the internal name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_message_attr_vl_tbl(l_record_num).name); /* ** Create the message display name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_MESSAGE_NAME'), p_wf_message_attr_vl_tbl(l_record_num).message_display_name); ELSE /* ** Create the attribute display name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_RESULT_DISPLAY_NAME'), p_wf_message_attr_vl_tbl(l_record_num).display_name); END IF; /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_message_attr_vl_tbl(l_record_num).description); /* ** Create the attribute type row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_ATTRIBUTE_TYPE'), wf_core.translate('WFITD_ATTR_TYPE_'|| p_wf_message_attr_vl_tbl(l_record_num).type)); /* ** Create the length/format/lookup type row in the table. ** If the type is VARCHAR2 then show a length prompt ** If the type is NUMBER/DATE then show format prompt ** If the type is LOOKUP then show lookup type prompt ** If it is any other type then don't show the row at all */ IF (p_wf_message_attr_vl_tbl(l_record_num).type = 'VARCHAR2') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('LENGTH'), p_wf_message_attr_vl_tbl(l_record_num).format); ELSIF (p_wf_message_attr_vl_tbl(l_record_num).type IN ('NUMBER', 'DATE')) THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('FORMAT'), p_wf_message_attr_vl_tbl(l_record_num).format); ELSIF (p_wf_message_attr_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('LOOKUP'), p_wf_message_attr_vl_tbl(l_record_num).lookup_type_display_name); ELSIF (p_wf_message_attr_vl_tbl(l_record_num).type IN ('URL','DOCUMENT')) THEN /* ** If it is URL or DOCUMENT, indicate where the resulting page should be displayed */ IF (NVL(p_wf_message_attr_vl_tbl(l_record_num).format, '_top') = '_top') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_TOP')); ELSIF (p_wf_message_attr_vl_tbl(l_record_num).format = '_blank') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_BLANK')); ELSIF (p_wf_message_attr_vl_tbl(l_record_num).format = '_self') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_SELF')); ELSIF (p_wf_message_attr_vl_tbl(l_record_num).format = '_parent') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_PARENT')); END IF; /* ** If the message attribute is a send, then display the attachment ** preference. */ IF p_wf_message_attr_vl_tbl(l_record_num).subtype = 'SEND' THEN IF NVL(p_wf_message_attr_vl_tbl(l_record_num).attach, 'N') = 'Y' THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_ATTACH'), wf_core.translate('WFITD_YES')); ELSE wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_ATTACH'), wf_core.translate('WFITD_NO')); END iF; END IF; END IF; /* ** Create the source row in the table */ IF (p_indent_level <> -1) THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_SOURCE'), wf_core.translate('WFITD_MSG_SOURCE_TYPE_'|| p_wf_message_attr_vl_tbl(l_record_num).subtype)); END IF; /* ** Create the default type row */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_TYPE'), wf_core.translate('WFITD_DEFAULT_TYPE_'|| p_wf_message_attr_vl_tbl(l_record_num).value_type)); /* ** If the default value is a constant then show the default value type ** that is not null. If the default value is based on an item attribute ** then show the attr_default_display_name. */ IF (p_wf_message_attr_vl_tbl(l_record_num).value_type = 'ITEMATTR') THEN /* ** Create the default item attribute row in the table. This is based on the ** p_wf_message_attr_vl_tbl(l_record_num).attr_default_display_name */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), p_wf_message_attr_vl_tbl(l_record_num).attr_default_display_name); /* ** Create the default value row in the table. If the attribute type is based on ** a lookup then the default value must be one of the lookup codes. If so print ** the lookup code that was fetch. */ ELSIF (p_wf_message_attr_vl_tbl(l_record_num).type = 'LOOKUP') THEN wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), p_wf_message_attr_vl_tbl(l_record_num).lookup_code_display_name); /* ** If this is any other attribute type then ** nvl on text value. If there is no text value then try the number ** default. If there is no number default then try the date. */ ELSE wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_DEFAULT_VALUE'), NVL(p_wf_message_attr_vl_tbl(l_record_num).text_default, NVL(TO_CHAR(p_wf_message_attr_vl_tbl(l_record_num).number_default), TO_CHAR(p_wf_message_attr_vl_tbl(l_record_num).date_default)))); END IF; /* ** Table is created so close it out */ htp.tableClose; /* ** Draw a line between each message attribute definition ** if this is not the last item in the list */ IF (l_record_num <> p_wf_message_attr_vl_tbl.count AND p_indent_level <> -1) THEN htp.p ('
'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_messages_vl_pub', 'draw_message_attr_details'); wf_item_definition.Error; END draw_message_attr_details; END wf_messages_vl_pub; / -- SHOW ERRORS PACKAGE BODY wf_messages_vl_pub; CREATE OR REPLACE PACKAGE BODY wf_lookup_types_pub AS /* $Header: wfdefb.pls 26.11 2004/06/29 19:39:59 ctilley ship $ */ /*=========================================================================== PACKAGE NAME: wf_lookup_types_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PROCEDURE NAME: fetch_lookup_type DESCRIPTION: Fetches all the lookup types for a given item type into a p_wf_lookup_types_vl_tbl table based on the item type internal eight character name. This function can also retrieve a single lookup type definition if the internal name along with the item type name is provided. This is especially useful if you wish to display the details for a single lookup type when it is referenced from some drilldown mechanism. ============================================================================*/ PROCEDURE fetch_lookup_types (p_item_type IN VARCHAR2, p_lookup_type IN VARCHAR2, p_wf_lookup_types_tbl OUT NOCOPY wf_lookup_types_pub.wf_lookup_types_tbl_type, p_wf_lookups_tbl OUT NOCOPY wf_lookup_types_pub.wf_lookups_tbl_type) IS CURSOR fetch_lookup_types (c_item_type IN VARCHAR2) IS SELECT row_id, lookup_type, item_type, protect_level, custom_level, display_name, description FROM wf_lookup_types WHERE item_type = c_item_type ORDER BY display_name; /*=========================================================================== CURSOR NAME: fetch_lookups DESCRIPTION: Fetches all lookups for the given item_type. You'll notice that the select orders the results by lookup type display name, and then by lookup meaning. The criteria is based on the requirement to synchronize the lookup list with the lookup type list. The lookup type list is ordered by display name. When we list the lookup tyoes and their corresponding lookups we walk these lists in parallel. When we find a lookup that matches the lookup type, we copy that lookup to a temp list until we find a new lookup type in the lookup list. When this happens we write out the lookup temp list and move to the next lookup type. Thus the need for the special order criteria. PARAMETERS: c_item_type IN Internal name of the item type ============================================================================*/ CURSOR fetch_lookups (c_item_type IN VARCHAR2) IS SELECT wlt.display_name lookup_type_display_name, wlt.item_type, wl.row_id, wl.lookup_type, wl.lookup_code, wl.protect_level, wl.custom_level, wl.meaning, wl.description FROM wf_lookups wl, wf_lookup_types wlt WHERE wlt.item_type = c_item_type AND wlt.lookup_type = wl.lookup_type ORDER BY wlt.display_name, wl.meaning; /*=========================================================================== CURSOR NAME: fetch_lookups_for_type DESCRIPTION: Fetches lookups for the given item_type and lookup type. PARAMETERS: c_item_type IN Internal name of the item type c_lookup_type IN Internal name of the lookup type ============================================================================*/ CURSOR fetch_lookups_for_type (c_item_type IN VARCHAR2, c_lookup_type IN VARCHAR2) IS SELECT wlt.display_name lookup_type_display_name, wlt.item_type, wl.row_id, wl.lookup_type, wl.lookup_code, wl.protect_level, wl.custom_level, wl.meaning, wl.description FROM wf_lookups wl, wf_lookup_types wlt WHERE wlt.item_type = c_item_type AND wlt.lookup_type = c_lookup_type AND wlt.lookup_type = wl.lookup_type ORDER BY wlt.display_name, wl.meaning; l_record_num NUMBER := 0; BEGIN /* ** Make sure all the required parameters are set */ IF (p_item_type IS NULL) THEN return; END IF; /* ** Check if the caller has passed a specific attribute_name to search for. ** If so then just get the row corresponding to that item_type and ** attribute_name. If not then get all rows for that item_type. */ IF (p_lookup_type IS NOT NULL) THEN SELECT row_id, lookup_type, item_type, protect_level, custom_level, display_name, description INTO p_wf_lookup_types_tbl(1) FROM wf_lookup_types WHERE item_type = p_item_type AND lookup_type = p_lookup_type; l_record_num := 0; OPEN fetch_lookups_for_type (p_item_type, p_lookup_type); /* ** Loop through all the lookup_code rows for the given lookup_type ** filling in the p_wf_lookups_tbl */ LOOP l_record_num := l_record_num + 1; FETCH fetch_lookups_for_type INTO p_wf_lookups_tbl(l_record_num); EXIT WHEN fetch_lookups_for_type%NOTFOUND; END LOOP; CLOSE fetch_lookups_for_type; ELSE OPEN fetch_lookup_types (p_item_type); /* ** Loop through all the lookup_code rows for the given lookup_type ** filling in the p_wf_lookups_tbl */ LOOP l_record_num := l_record_num + 1; FETCH fetch_lookup_types INTO p_wf_lookup_types_tbl(l_record_num); EXIT WHEN fetch_lookup_types%NOTFOUND; END LOOP; CLOSE fetch_lookup_types; l_record_num := 0; OPEN fetch_lookups (p_item_type); /* ** Loop through all the lookup_code rows for the given lookup_type ** filling in the p_wf_lookups_tbl */ LOOP l_record_num := l_record_num + 1; FETCH fetch_lookups INTO p_wf_lookups_tbl(l_record_num); EXIT WHEN fetch_lookups%NOTFOUND; END LOOP; CLOSE fetch_lookups; END IF; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_lookup_types_pub', 'fetch_lookup_types', p_item_type, p_lookup_type); wf_item_definition.Error; END fetch_lookup_types; /*=========================================================================== PROCEDURE NAME: fetch_lookup_display DESCRIPTION: fetch the lookup type display name and the lookup code display name based on a lookup type internal name and lookup code internal name ============================================================================*/ PROCEDURE fetch_lookup_display (p_type_internal_name IN VARCHAR2, p_code_internal_name IN VARCHAR2, p_type_display_name OUT NOCOPY VARCHAR2, p_code_display_name OUT NOCOPY VARCHAR2) IS l_type_display_name VARCHAR2(80); l_code_display_name VARCHAR2(80); BEGIN /* ** Only try to fetch the lookup type if the internal name is passed in */ IF (p_type_internal_name IS NOT NULL) THEN /* ** Get the display name based on the internal name. ** Use a max() so you don't need a exception for no data found */ SELECT MAX(display_name) INTO l_type_display_name FROM wf_lookup_types WHERE lookup_type = p_type_internal_name; /* ** If no value is found then set the display name to the ** internal name + ' is undefined' message so user can see ** missing reference */ IF (l_type_display_name IS NULL) THEN l_type_display_name := p_type_internal_name||' '|| ' -- '||UPPER(wf_core.translate ('WFITD_UNDEFINED'))||''; END IF; ELSE /* ** No internal name was passed so set the display name to null */ l_type_display_name := NULL; END IF; /* ** Set the outbound lookup code display name */ p_type_display_name := l_type_display_name; /* ** Only try to fetch the lookup code if both internal names are passed in */ IF (p_type_internal_name IS NOT NULL AND p_code_internal_name IS NOT NULL) THEN /* ** Get the display name based on the internal name. ** Use a max() so you don't need a exception for no data found */ SELECT MAX(meaning) INTO l_code_display_name FROM wf_lookups WHERE lookup_type = p_type_internal_name AND lookup_code = p_code_internal_name; /* ** If no value is found then set the display name to the ** internal name + ' is undefined' message so user can see ** missing reference */ IF (l_code_display_name IS NULL) THEN l_code_display_name := p_code_internal_name||' '|| ' -- '||UPPER(wf_core.translate ('WFITD_UNDEFINED'))||''; END IF; ELSE /* ** No internal name was passed so set the display name to null */ l_code_display_name := NULL; END IF; /* ** Set the outbound lookup code display name */ p_code_display_name := l_code_display_name; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_lookup_types_pub', 'fetch_lookup_display', p_type_internal_name, p_code_internal_name); END fetch_lookup_display; /*=========================================================================== PROCEDURE NAME: draw_lookup_type_list DESCRIPTION: Shows the display name of lookup type as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. ============================================================================*/ PROCEDURE draw_lookup_type_list (p_wf_lookup_types_tbl IN wf_lookup_types_pub.wf_lookup_types_tbl_type, p_wf_lookups_tbl IN wf_lookup_types_pub.wf_lookups_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_lookup_type_record_num NUMBER; l_lookup_record_num NUMBER := 1; ii NUMBER := 0; l_cur_lookup_record_num NUMBER := 1; l_wf_lookups_tbl wf_lookup_types_pub.wf_lookups_tbl_type; BEGIN /* ** Create the the attributes title. Indent it to the level specified */ wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_LOOKUP_TYPES'), p_indent_level); /* ** Print out all lookup type display names in the pl*sql table */ FOR l_lookup_type_record_num IN 1..p_wf_lookup_types_tbl.count LOOP /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. */ wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_lookup_types_tbl(l_lookup_type_record_num).item_type, p_effective_date, 'LOOKUP_TYPE', p_wf_lookup_types_tbl(l_lookup_type_record_num).lookup_type, p_wf_lookup_types_tbl(l_lookup_type_record_num).display_name, NULL, p_indent_level+1); /* ** Here we look for all the lookup types that are related to the ** current lookup type. The p_wf_lookups_vl_tbl is ordered by display ** name and then by lookup type display name. As long as we stay ** in sync we should be able to correctly create the temp attribute list ** for the current lookup type. We could create a cursor here for the child ** attributes but that would break the rule of separating the UI layer ** and the data layer */ l_wf_lookups_tbl.delete; l_cur_lookup_record_num := 1; /* ** Make sure there is at least on record in the lookup ** list. If there is not then the l_lookup_record_num index of 1 ** will cause a 6502-PL*SQL numeric or value error exception. */ WHILE ( l_lookup_record_num <= p_wf_lookups_tbl.count AND p_wf_lookup_types_tbl(l_lookup_type_record_num).display_name = p_wf_lookups_tbl(l_lookup_record_num).lookup_type_display_name ) LOOP /* ** We have found an attribute for the current lookup type. Copy the ** contents of that list to a temp attr list and then pass the ** temp list to the lookupsibute display function to display ** the results. */ l_wf_lookups_tbl(l_cur_lookup_record_num) := p_wf_lookups_tbl(l_lookup_record_num); l_lookup_record_num := l_lookup_record_num + 1; l_cur_lookup_record_num := l_cur_lookup_record_num + 1; END LOOP; /* ** If the l_cur_attr_record_num is greater than 1 then you ** must have found an attribute for this lookup type. Copy that ** set of attributes to a temporary pl*sql table and then ** print it out. */ IF (l_cur_lookup_record_num > 1) THEN /* ** List all the lookup type details for this lookup type that ** we found above. Add two to the current indent level so it ** is pushed in past the start of the lookup type list. */ wf_lookup_types_pub.draw_lookup_list ( l_wf_lookups_tbl, p_effective_date, p_indent_level + 2); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_lookup_types_pub', 'draw_lookup_type_list'); wf_item_definition.Error; END draw_lookup_type_list; /*=========================================================================== PROCEDURE NAME: draw_lookup_list DESCRIPTION: Shows the display names of message attributes for a given message as a html view as a part of a hierical summary list of an item type. This function uses the htp to generate its html output. ============================================================================*/ PROCEDURE draw_lookup_list (p_wf_lookups_tbl IN wf_lookup_types_pub.wf_lookups_tbl_type, p_effective_date IN DATE, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Create the the lookups title. Indent it to the level specified */ wf_item_definition_util_pub.draw_summary_section_title( wf_core.translate('WFITD_LOOKUP_CODES'), p_indent_level); /* ** Print out all lookup display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_lookups_tbl.count LOOP /* ** The creation of the anchor from the summary frame to the detail ** frame was very complex so I've extracted the function into its ** own routine. */ wf_item_definition_util_pub.create_hotlink_to_details ( p_wf_lookups_tbl(l_record_num).item_type, p_effective_date, 'LOOKUP', p_wf_lookups_tbl(l_record_num).lookup_type||':'|| p_wf_lookups_tbl(l_record_num).lookup_code, p_wf_lookups_tbl(l_record_num).meaning, NULL, p_indent_level+1); END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_lookup_types_vl_pub', 'draw_lookup_list'); wf_item_definition.Error; END draw_lookup_list; /*=========================================================================== PROCEDURE NAME: draw_lookup_type_details DESCRIPTION: Shows all the details of an lookup type as a html view. This function uses the htp to generate its html output. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_lookup_type_details (p_wf_lookup_types_tbl IN wf_lookup_types_pub.wf_lookup_types_tbl_type, p_wf_lookups_tbl IN wf_lookup_types_pub.wf_lookups_tbl_type, p_indent_level IN NUMBER) IS l_lookup_type_record_num NUMBER; ii NUMBER := 0; l_lookup_record_num NUMBER := 1; l_cur_lookup_record_num NUMBER := 1; l_wf_lookups_tbl wf_lookup_types_pub.wf_lookups_tbl_type; BEGIN /* ** Draw the section title for the lookup type detail section */ wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_LOOKUP_TYPE_DETAILS'), 0); /* ** Print out all item attribute display names in the pl*sql table */ FOR l_lookup_type_record_num IN 1..p_wf_lookup_types_tbl.count LOOP /* ** Open a new table for each attribute so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary=""'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( 'LOOKUP_TYPE', p_wf_lookup_types_tbl(l_lookup_type_record_num).lookup_type, p_wf_lookup_types_tbl(l_lookup_type_record_num).display_name, wf_core.translate('WFITD_LOOKUP_TYPE_NAME'), 0); /* ** Create the internal name row in the table. Also lay down the ** destination for the anchor based on the row id. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_lookup_types_tbl(l_lookup_type_record_num).lookup_type); /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_lookup_types_tbl(l_lookup_type_record_num).description); /* ** Call function to print the customization/protection levels */ wf_item_definition_util_pub.draw_custom_protect_details( p_wf_lookup_types_tbl(l_lookup_type_record_num).custom_level, p_wf_lookup_types_tbl(l_lookup_type_record_num).protect_level); /* ** Table is created so close it out */ htp.tableClose; /* ** Here we look for all the lookups that are related to the current ** lookup type. The p_wf_lookups_tbl is ordered by display ** name and then by lookup meaning. As long as we stay ** in sync we should be able to correctly create the temp lookup list ** for the current lookup type. We could create a cursor here for ** the child lookups but that would break the rule of separating ** the UI layer and the data layer */ l_wf_lookups_tbl.delete; l_cur_lookup_record_num := 1; /* ** Make sure there is at least on record in the lookups ** list. If there is not then the l_lookup_record_num index of 1 ** will cause a 6502-PL*SQL numeric or value error exception. */ WHILE ( l_lookup_record_num <= p_wf_lookups_tbl.count AND p_wf_lookup_types_tbl(l_lookup_type_record_num).display_name = p_wf_lookups_tbl(l_lookup_record_num).lookup_type_display_name ) LOOP /* ** We have found a lookup for the lookup type. Copy the ** contents of that list to a temp lookup list and then pass the ** temp list to the lookup display function to display ** the results. */ l_wf_lookups_tbl(l_cur_lookup_record_num) := p_wf_lookups_tbl(l_lookup_record_num); l_lookup_record_num := l_lookup_record_num + 1; l_cur_lookup_record_num := l_cur_lookup_record_num + 1; END LOOP; /* ** If the l_cur_lookup_record_num is greater than 1 then you ** must have found a lookup for this lookup type. Copy that ** set of lookups to a temporary pl*sql table and then ** print it out. */ IF (l_cur_lookup_record_num > 1) THEN /* ** Put in a couple of blank lines between the current lookup type ** and its lookups */ htp.p('

'); /* ** List all the lookup details for this lookup typethat ** we found above. */ wf_lookup_types_pub.draw_lookup_details ( l_wf_lookups_tbl, 1); /* ** If you still have more lookup types to process then put in a ** few blank lines and put in another Lookup Type Details Header */ IF (l_lookup_type_record_num < p_wf_lookup_types_tbl.count) THEN /* ** Put in a couple of blank lines between the current message ** attributes and the next message */ htp.p('

'); /* ** Draw the section title for the Lookup Type detail section */ wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_LOOKUP_TYPE_DETAILS'), 0); END IF; END IF; /* ** Draw a line between each message definition ** if this is not the last item in the list and if there ** are no attributes in the attribute list for this message. */ IF (l_lookup_type_record_num < p_wf_lookup_types_tbl.count AND l_cur_lookup_record_num = 1) THEN htp.p ('
'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_lookup_types_pub', 'draw_lookup_type_details'); wf_item_definition.Error; END draw_lookup_type_details; /*=========================================================================== PROCEDURE NAME: draw_lookup_details DESCRIPTION: Shows all of the details for a list of lookups that have been passed in. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA ============================================================================*/ PROCEDURE draw_lookup_details (p_wf_lookups_tbl IN wf_lookup_types_pub.wf_lookups_tbl_type, p_indent_level IN NUMBER) IS l_record_num NUMBER; ii NUMBER := 0; BEGIN /* ** Draw the section title for the lookup detail section */ wf_item_definition_util_pub.draw_detail_section_title ( wf_core.translate('WFITD_LOOKUP_DETAILS'), 0); /* ** Print out all meesage attribute display names in the pl*sql table */ FOR l_record_num IN 1..p_wf_lookups_tbl.count LOOP /* ** Open a new table for each lookup so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0 summary=""'); /* ** Create the target for the hotlink from the summary view. Also ** create the first row in the table which is always the display ** name for the object. */ wf_item_definition_util_pub.create_details_hotlink_target ( 'LOOKUP', p_wf_lookups_tbl(l_record_num).lookup_type||':'|| p_wf_lookups_tbl(l_record_num).lookup_code, p_wf_lookups_tbl(l_record_num).meaning, wf_core.translate('WFITD_LOOKUP_CODE_NAME'), 0); /* ** Create the internal name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_INTERNAL_NAME'), p_wf_lookups_tbl(l_record_num).lookup_code); /* ** Create the lookup type display name row in the table. */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_LOOKUP_TYPE_NAME'), p_wf_lookups_tbl(l_record_num).lookup_type_display_name); /* ** Create the description row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('DESCRIPTION'), p_wf_lookups_tbl(l_record_num).description); /* ** Table is created so close it out */ htp.tableClose; /* ** Draw a line between each lookup definition ** if this is not the last item in the list */ IF (l_record_num <> p_wf_lookups_tbl.count) THEN htp.p ('
'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_lookup_types_vl_pub', 'draw_lookup_details'); wf_item_definition.Error; END draw_lookup_details; END wf_lookup_types_pub; / -- SHOW ERRORS PACKAGE BODY wf_lookup_types_pub; CREATE OR REPLACE PACKAGE BODY wf_item_definition_util_pub AS /* $Header: wfdefb.pls 26.11 2004/06/29 19:39:59 ctilley ship $ */ /*=========================================================================== PACKAGE NAME: wf_item_definition_util_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PROCEDURE NAME: draw_custom_protect_details DESCRIPTION: Writes out the custom and protect prompts and values for a detailed listing of a workflow object. ============================================================================*/ PROCEDURE draw_custom_protect_details (p_customization_level IN VARCHAR2, p_protection_level IN VARCHAR2) IS BEGIN /* ** Create the customization row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_CUSTOMIZATION_LEVEL'), p_customization_level); /* ** Create the protection level row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_PROTECTION_LEVEL'), p_protection_level); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'draw_custom_protect_details', p_customization_level, p_protection_level); wf_item_definition.Error; END draw_custom_protect_details; /*=========================================================================== PROCEDURE NAME: draw_read_write_exe_details DESCRIPTION: Writes out the read, write, execute role prompts and values for a detailed listing of a workflow object. ============================================================================*/ PROCEDURE draw_read_write_exe_details (p_read_role IN VARCHAR2, p_write_role IN VARCHAR2, p_execute_role IN VARCHAR2, p_draw_execute_role IN BOOLEAN) IS BEGIN /* ** Create the read role row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_READ_ROLE'), p_read_role); /* ** Create the write role row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_WRITE_ROLE'), p_write_role); IF (p_draw_execute_role = TRUE) THEN /* ** Create the execute role row in the table */ wf_item_definition_util_pub.draw_detail_prompt_value_pair ( wf_core.translate('WFITD_EXECUTE_ROLE'), p_execute_role); END IF; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'draw_read_write_exe_details', p_read_role, p_write_role, p_execute_role); wf_item_definition.Error; END draw_read_write_exe_details; /*=========================================================================== PROCEDURE NAME: create_hotlink_to_details DESCRIPTION: The creation of the anchor from the summary frame to the detail frame was very complex so I've extracted the function into its own routine. I used the straight tabledata call rather than the htf.anchor2 web server call because the anchor2 created an HREF string that would never seem to create the proper syntax How this call works is that the A HREF is preceeded by the indent characters. This is so the indent characters are not part of the anchor and therefore do not appear as underlined text. The second component is the link to the url for the details frame. When using frames and anchors you must provide the full url that was used to create the frame including all the parameters. Since I don't store this parameter in any of my pl*sql tables, I had to add this parameter to all the procedures so I could pass it through. The next component is the tag within the detail frame that you are going to navigate to. The tag is composed of two parts. The first part is the object_type_prefix (ATTRIBUTE, PROCESS, NOTIFICATION, MESSAGE, MESSAGE_ATTR, etc.) with a '#' in front of it to tell it that its a local link in the existing frame. The second part is the internal name of the object. This is followed by the frame target which is DETAILS, and then the name that is displayed to the user as the link name which is the display name for the object. The alignment is always left and I prevented wrapping so you don't get every line being double spaced if one attribute or some other object is a bit longer than what fits in the summary frame. This is especially effective if the user resizes the summary frame down to something small. ============================================================================*/ PROCEDURE create_hotlink_to_details ( p_item_type IN VARCHAR2, p_effective_date IN DATE, p_object_type_prefix IN VARCHAR2, p_internal_name IN VARCHAR2, p_display_name IN VARCHAR2, p_detail_prompt IN VARCHAR2, p_indent_level IN NUMBER) IS l_indent_string VARCHAR2(80) := ''; ii NUMBER := 0; BEGIN /* ** Add three blank spaces for every indent level to preceed the ** link with */ FOR ii IN 1..p_indent_level LOOP l_indent_string := l_indent_string || '      '; END LOOP; htp.tableRowOpen(calign=>'left', cvalign=>'top'); /* ** If this is a all from the detail region then you'll have a two ** column format and you need to put this prompt on. Otherwise it's ** a one column table for the summary frame. */ IF (p_detail_prompt IS NOT NULL) THEN wf_item_definition_util_pub.draw_detail_prompt( p_detail_prompt); END IF; htp.tableData( cvalue=>l_indent_string|| ''|| p_display_name|| '', calign=>'Left', cnowrap=>'NOWRAP'); htp.tableRowClose; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'create_hotlink_to_details', p_item_type, TO_CHAR(p_effective_date), p_object_type_prefix, p_internal_name, p_display_name); wf_item_definition.Error; END create_hotlink_to_details; /*=========================================================================== PROCEDURE NAME: create_details_hotlink_target DESCRIPTION: Creates the destination target in the detail frame for a hotlink. The destination target name is based on the a name comprised of two parts. The first part is the object_type_prefix (ATTRIBUTE, PROCESS, NOTIFICATION, MESSAGE, MESSAGE_ATTR, etc.) The second part is the internal name of the object. ============================================================================*/ PROCEDURE create_details_hotlink_target ( p_object_type_prefix IN VARCHAR2, p_internal_name IN VARCHAR2, p_display_name IN VARCHAR2, p_display_prompt IN VARCHAR2, p_indent_level IN NUMBER) IS l_indent_string VARCHAR2(80) := ''; ii NUMBER := 0; BEGIN /* ** Add three blank spaces for every indent level to preceed the ** link with */ FOR ii IN 1..p_indent_level LOOP l_indent_string := l_indent_string || '      '; END LOOP; /* ** Create the display name row in the table */ htp.tableRowOpen(calign=>'middle', cvalign=>'top'); /* ** The destination target name is based on the a name comprised of ** two parts. The first part is the object_type_prefix ** (ATTRIBUTE, PROCESS, NOTIFICATION, MESSAGE, MESSAGE_ATTR, etc.) ** The second part is the internal name of the object. */ htp.p(''); wf_item_definition_util_pub.draw_detail_prompt(p_display_prompt); htp.p (''); wf_item_definition_util_pub.draw_detail_value(p_display_name); htp.tableRowClose; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'create_details_hotlink_target', p_object_type_prefix, p_internal_name, p_display_name, p_display_prompt); wf_item_definition.Error; END create_details_hotlink_target; /*=========================================================================== PROCEDURE NAME: draw_summary_section_title DESCRIPTION: Draws the bold section title for an object type in the summary frame. ============================================================================*/ PROCEDURE draw_summary_section_title ( p_section_title IN VARCHAR2, p_indent_level IN NUMBER) IS l_indent_string VARCHAR2(80) := ''; ii NUMBER := 0; BEGIN /* ** Add three blank spaces for every indent level to preceed the ** link with */ FOR ii IN 1..p_indent_level LOOP l_indent_string := l_indent_string || '      '; END LOOP; /* ** Open a row in the summary table, put on the the section title. ** and then close the row. */ htp.tableRowOpen(calign=>'left', cvalign=>'top'); htp.tableData( cvalue=>l_indent_string||''||p_section_title||'', calign=>'Left', cnowrap=>'NOWRAP'); htp.tableRowClose; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'draw_summary_section_title', p_section_title, p_indent_level); wf_item_definition.Error; END draw_summary_section_title; /*=========================================================================== PROCEDURE NAME: draw_detail_section_title DESCRIPTION: Draws the bold section title and the thick line for an object type in the detail frame. ============================================================================*/ PROCEDURE draw_detail_section_title ( p_section_title IN VARCHAR2, p_indent_level IN NUMBER) IS BEGIN /* ** Draw the detail section title */ htp.bold(p_section_title); /* ** Put a line across the form */ htp.p('
'); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'draw_detail_section_title', p_section_title); wf_item_definition.Error; END draw_detail_section_title; /*=========================================================================== PROCEDURE NAME: draw_detail_prompt_value_pair DESCRIPTION: Draws the bold detail section prompt and its corresponding value in the detail frame ============================================================================*/ PROCEDURE draw_detail_prompt_value_pair (p_prompt IN VARCHAR2, p_value IN VARCHAR2) IS BEGIN htp.tableRowOpen(calign=>'middle', cvalign=>'top'); htp.tableData(cvalue=>p_prompt||' ', calign=>'Right', cnowrap=>'NOWRAP'); htp.tableData(''||p_value||'', 'Left'); htp.tableRowClose; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'draw_detail_prompt_value_pair', p_prompt, p_value); wf_item_definition.Error; END draw_detail_prompt_value_pair; /*=========================================================================== PROCEDURE NAME: draw_detail_prompt DESCRIPTION: Draws the bold detail section prompt ============================================================================*/ PROCEDURE draw_detail_prompt (p_prompt IN VARCHAR2) IS BEGIN htp.tableData(p_prompt||' ', 'Right'); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'draw_detail_prompt', p_prompt); wf_item_definition.Error; END draw_detail_prompt; /*=========================================================================== PROCEDURE NAME: draw_detail_value DESCRIPTION: Draws the value of an attribute ============================================================================*/ PROCEDURE draw_detail_value (p_value IN VARCHAR2) IS BEGIN htp.tableData(''||p_value||'', 'Left'); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'draw_detail_value', p_value); wf_item_definition.Error; END draw_detail_value; /*=========================================================================== PROCEDURE NAME: activity_titles_list DESCRIPTION: Check how many activity types got printed. If the list didn't to one of the activity types because there weren't any of that then catch it here and print it. PARAMETERS: ============================================================================*/ PROCEDURE activity_titles_list ( p_highest_level IN NUMBER, p_current_level IN NUMBER, p_indent_level IN NUMBER ) IS ii NUMBER := 0; l_summary_section_title VARCHAR2(240); BEGIN /* ** Check how many activity types got printed. If the list didn't ** to one of the activity types because there weren't any of that ** then catch it here and print it. Subtract 1 from the current ** level since the index is 1 and the current is always going to be ** one higher than the starting point in the for loop. */ FOR ii IN p_highest_level..p_current_level - 1 LOOP IF (ii = 1) THEN /* ** Set the the processes title. */ l_summary_section_title := wf_core.translate('PROCESSES'); wf_item_definition_util_pub.draw_summary_section_title( l_summary_section_title, p_indent_level); ELSIF (ii = 2) THEN /* ** Set the the notifications title. */ l_summary_section_title := wf_core.translate('WFITD_NOTIFICATIONS'); wf_item_definition_util_pub.draw_summary_section_title( l_summary_section_title, p_indent_level); ELSIF (ii = 3) THEN /* ** Set the functions title. */ l_summary_section_title := wf_core.translate('WFITD_FUNCTIONS'); wf_item_definition_util_pub.draw_summary_section_title( l_summary_section_title, p_indent_level); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'activity_titles_list'); wf_item_definition.Error; END activity_titles_list; /*=========================================================================== PROCEDURE NAME: activity_titles_details DESCRIPTION: Check how many activity types got printed. If the list didn't to one of the activity types because there weren't any of that then catch it here and print it. PARAMETERS: ============================================================================*/ PROCEDURE activity_titles_details ( p_highest_level IN NUMBER, p_current_level IN NUMBER ) IS ii NUMBER := 0; l_detail_section_title VARCHAR2(240); BEGIN /* ** Check how many activity types got printed. If the list didn't ** to one of the activity types because there weren't any of that ** then catch it here and print it. Subtract 1 from the current ** level since the index is 1 and the current is always going to be ** one higher than the starting point in the for loop. */ FOR ii IN p_highest_level..p_current_level - 1 LOOP IF (ii = 1) THEN /* ** Set the the processes title. */ l_detail_section_title := wf_core.translate('WFITD_PROCESS_DETAILS'); wf_item_definition_util_pub.draw_detail_section_title( l_detail_section_title, 0); /* ** Create some blank space around the title */ htp.p ('

'); ELSIF (ii = 2) THEN /* ** Set the the notifications title. */ l_detail_section_title := wf_core.translate('WFITD_NOTIFICATION_DETAILS'); wf_item_definition_util_pub.draw_detail_section_title( l_detail_section_title, 0); /* ** Create some blank space around the title */ htp.p ('

'); ELSIF (ii = 3) THEN /* ** Set the functions title. */ l_detail_section_title := wf_core.translate('WFITD_FUNCTION_DETAILS'); wf_item_definition_util_pub.draw_detail_section_title( l_detail_section_title, 0); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'activity_titles_details'); wf_item_definition.Error; END activity_titles_details; /*=========================================================================== PROCEDURE NAME: validate date DESCRIPTION: Validates and converts a char datatype date string to a date datatype that the user has entered is in a valid format based on the NLS_DATE_FORMAT parameter. ============================================================================*/ PROCEDURE validate_date (p_char_date IN VARCHAR2, p_date_date OUT NOCOPY DATE, p_valid_date OUT NOCOPY BOOLEAN, p_expected_format OUT NOCOPY VARCHAR2) IS l_nls_date_format VARCHAR2(80); l_date_date DATE; BEGIN /* ** Set the l_date_date to null */ l_date_date := NULL; /* ** Get the current date format from the v$nls_parameters view */ SELECT MAX(value) INTO l_nls_date_format FROM v$nls_parameters WHERE parameter = 'NLS_DATE_FORMAT'; /* ** If no parameter can be found then set it to something */ IF (l_nls_date_format IS NULL) THEN l_nls_date_format := 'DD-MON-RRRR'; END IF; /* ** Convert YY or YYYY in the l_nls_date_format to RRRR since ** this is the most flexible format */ l_nls_date_format := REPLACE (l_nls_date_format, 'YYYY', 'RRRR'); l_nls_date_format := REPLACE (l_nls_date_format, 'YY', 'RRRR'); /* ** Check to see if you need to add the time to the date format in case ** the char string has a time element on it. This check ** is based on having a ':' in the date value string and not having a ':' ** in the nls string */ IF (INSTR(p_char_date, ':') > 0 AND INSTR(l_nls_date_format, ':') = 0) THEN l_nls_date_format := l_nls_date_format || ' HH24:MI:SS'; END IF; /* ** Now try to convert the char date string to a date datatype. If any ** exception occurs then tell the caller that the */ p_valid_date := TRUE; BEGIN SELECT TO_DATE(p_char_date, l_nls_date_format) INTO l_date_date FROM dual; EXCEPTION WHEN OTHERS THEN p_valid_date := FALSE; END; p_date_date := l_date_date; p_expected_format := l_nls_date_format; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'validate_date'); wf_item_definition.Error; END validate_date; /*=========================================================================== PROCEDURE NAME: create_checkbox DESCRIPTION: Create a checkbox entry in a table ============================================================================*/ PROCEDURE create_checkbox ( p_name IN VARCHAR2, p_value IN VARCHAR2, p_checked IN VARCHAR2, p_prompt IN VARCHAR2, p_image_name IN VARCHAR2 , p_new_row IN BOOLEAN ) IS BEGIN IF (p_new_row = TRUE) THEN /* ** Open the checkboxes row */ htp.tableRowOpen; END IF; /* ** Create the checkbox for Top Level Process Only List ** add nbsp; to space out the checkboxes */ IF (p_image_name IS NOT NULL) THEN htp.tableData( cvalue=> htf.formcheckbox( cname=>p_name, cvalue=>p_value, cchecked=>p_checked, cattributes=>NULL)||' '|| htf.img( curl=>wfa_html.image_loc||p_image_name, calign=>'absmiddle', calt=>null, cismap=>null, cattributes=>'height=26')|| ' '||p_prompt||'   ', calign=>'left'); ELSE htp.tableData( cvalue=> htf.formcheckbox( cname=>p_name, cvalue=>p_value, cchecked=>p_checked, cattributes=>NULL)|| ' '||p_prompt||'   ', calign=>'left', cattributes=>'valign="TOP"'); END IF; IF (p_new_row = TRUE) THEN /* ** Close the checkboxes row */ htp.tableRowClose; END IF; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition_util_pub', 'create_checkbox', p_name, p_value, p_checked, p_prompt); wf_item_definition.Error; END create_checkbox; END wf_item_definition_util_pub; / -- SHOW ERRORS PACKAGE BODY wf_item_definition_util_pub; CREATE OR REPLACE PACKAGE BODY wf_item_definition AS /* $Header: wfdefb.pls 26.11 2004/06/29 19:39:59 ctilley ship $ */ /*=========================================================================== PACKAGE NAME: wf_item_definition DESCRIPTION: OWNER: GKELLNER ============================================================================*/ /*=========================================================================== PROCEDURE NAME: find_item_type DESCRIPTION: Main Find View drawing routine. This is the main entry point into the Item Type Definition View. This view has two attributes: The Item Type List shows all the Items Types that are currently stored in the Workflow database repository. The effective date allows you to chose which date you would like the view to be effective for. 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. Once the user clicks on the Find buttton from this view, the draw_item_type function takes over to create the Item Type Definition. MODIFICATION LOG: 06-JUN-2001 JWSMITH BUG 1819232 -Added ID attr for TD tag for ADA - Added summary attr for table tag ============================================================================*/ PROCEDURE find_item_type IS l_username VARCHAR2(320); l_admin_role VARCHAR2(320); l_admin_privilege BOOLEAN; l_char_date VARCHAR2(80); l_date_date DATE; l_valid_date BOOLEAN; l_expected_format VARCHAR2(80); /* ** List of all item types based no security or admin access */ CURSOR admin_itemtypes IS SELECT display_name, name FROM wf_item_types_vl ORDER BY 1; /* ** List of item types based on owner access for a given user. */ CURSOR user_itemtypes IS SELECT distinct wit.display_name, wit.name FROM wf_item_types_vl wit, wf_items wik WHERE wik.owner_role = l_username AND wik.item_type = wit.name ORDER BY 1; CURSOR all_users IS SELECT distinct wik.owner_role owner_role FROM wf_items wik ORDER BY 1; BEGIN /* ** Make sure the user has signed on */ wfa_sec.GetSession(l_username); /* ** Check what security controls are enabled */ l_admin_role := wf_core.Translate('WF_ADMIN_ROLE'); IF (l_admin_role <> '*') THEN IF (wf_directory.IsPerformer(l_username, l_admin_role)) THEN l_admin_privilege := TRUE; ELSE l_admin_privilege := FALSE; END IF; ELSE /* ** No security is enabled so everyone has admin privileges. */ l_admin_privilege := TRUE; END IF; /* ** Create a standard title page with the item_type display name as the title */ wf_item_definition.draw_header( NULL, NULL, 'FIND'); /* ** We use the simple GET method, when the form is submitted it ** generates a URL of the form ** ** http://...wf_item_definition.draw_item_type?x_process=&x_ident= ... ** ** which is what our instance_list procedure (defined later) is ** expecting to get passed. */ htp.formOpen(curl=>'wf_item_definition.draw_item_type', cmethod=>'GET', cattributes=>'NAME="WF_FIND"'); /* ** Create a table for the find attributes. */ htp.tableOpen(calign=>'CENTER', cattributes=>'border=0 cellpadding=2 cellspacing=0 summary=""'); /* ** Create the prompt for the item type poplist */ htp.tableRowOpen; htp.tableData(cvalue=>'', calign=>'right', cattributes=>'valign=middle id=""'); /* ** Create the item type poplist */ htp.p(''); htp.formSelectOpen(cname=>'p_item_type',cattributes=>'id="i_item_type"'); /* ** Create the item type poplist. If you have admin privs then show ** all item types */ IF (l_admin_privilege) THEN FOR it IN admin_itemtypes LOOP /* ** Take care of the case where the item type has a space in it. ** We used a + to represent the space in the list of values since you ** can escape it in a poplist and pass it through the post. */ htp.formSelectOption(cvalue=>it.display_name, cattributes=>'value='||REPLACE(it.name,' ', '+')); END LOOP; ELSE /* ** If you do not have admin privs then show only those item types ** for which you have owner access */ FOR it IN user_itemtypes LOOP htp.formSelectOption(cvalue=>it.display_name, cattributes=>'value='||REPLACE(it.name,' ', '+')); END LOOP; END IF; htp.formSelectClose; htp.p(''); htp.tableRowClose; /* ** Create the prompt for the Effective poplist */ htp.tableRowOpen; htp.tableData(cvalue=>'', calign=>'right', cattributes=>'valign=middle id=""'); /* ** Get the expected format for the date. You don't care about the ** validation stuff */ wf_item_definition_util_pub.validate_date ( TO_CHAR(sysdate), l_date_date, l_valid_date, l_expected_format); /* ** Set the default Effective Date value based on the l_expected_format */ l_char_date := TO_CHAR(SYSDATE, l_expected_format); htp.tableData(cvalue=>htf.formText(cname=>'p_effective_date', csize=>'30', cmaxlength=>'240', cvalue=>l_char_date, cattributes=>'id="i_effective_date"'), calign=>'left', cattributes=>'id=""'); htp.tableRowClose; htp.tableClose; htp.formClose; -- Add submit button htp.tableopen(calign=>'CENTER',cattributes=>'summary=""'); htp.tableRowOpen; htp.p(''); wfa_html.create_reg_button ('javascript:document.WF_FIND.submit()', wf_core.translate ('FIND'), wfa_html.image_loc, 'fndfind.gif', wf_core.translate ('FIND')); htp.p(''); htp.tableRowClose; htp.tableClose; wfa_sec.footer; htp.htmlClose; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition', 'find_item_type'); wf_item_definition.Error; END find_item_type; /*=========================================================================== PROCEDURE NAME: draw_item_type DESCRIPTION: Main routine that will create a three framed view that shows the complete definition of an item type. The top frame is the view header. It show the title of the view along with controls to return to the find window, return to the main menu or exit the system. It then displays the Item Type Summary and Item Type Details in two separate frame below the header frame. The left frame consists of the hierarchical summary of the Item Type Definition showing all display names for attributes, processes, notifications, functions, etc. The right frame consists of a complete listing of all the objects and their associated properties for the given item type. ============================================================================*/ PROCEDURE draw_item_type ( p_item_type VARCHAR2 , p_effective_date VARCHAR2 ) IS l_username VARCHAR2(320); l_valid_date BOOLEAN; l_date_date DATE; l_effective_date VARCHAR2(80); l_expected_format VARCHAR2(80); l_item_type VARCHAR2(30); BEGIN /* ** Make sure the user has signed on */ wfa_sec.GetSession(l_username); /* ** Create the three frames for the Item Definition Summary. ** The frames are constructed in the following manner: ** ______________________ **| | **| HEADER | **|---------------------| **| | | **| | | **| SUMMARY | DETAILS | **| | | **| | | **|---------------------| */ htp.title(wf_core.translate('WFITD_ITEM_TYPE_DEFINITION')); /* ** Take care of the case where the item type has a space in it. ** We used a + to represent the space in the list of values since you ** can escape it in a poplist and pass it through the post. Here we'll ** switch it back and let the convert function take care of it. */ l_item_type := REPLACE(p_item_type, '+', ' '); /* ** Check if there is a time included with the search criteria ** If not the add midnight to the time so the activities will always ** be after the given date on the same day. */ /* ** Do not replace + here, because we are to encode them later on IF (INSTR(p_effective_date, ':') = 0) THEN l_effective_date := p_effective_date || '+23:59:59'; ELSE l_effective_date := REPLACE(p_effective_date, ' ', '+'); END IF; */ IF (INSTR(p_effective_date, ':') = 0) THEN l_effective_date := p_effective_date || ' 23:59:59'; END IF; /* ** Create the top header frameset and the bottom summary/detail frameset */ htp.p (''); /* ** Create the header frame */ htp.p (''); /* ** Check the effective date that was passed in. If it is invalid ** Then just show a frame with an error. Otherwise show the ** frameset for the summary and details. */ wf_item_definition_util_pub.validate_date ( l_effective_date, l_date_date, l_valid_date, l_expected_format); /* ** The date that was passed in is good so continue to draw the ** frameset for the summary and details. */ IF (l_valid_date = TRUE) THEN /* ** Now create the summary/detail frameset */ htp.p (''); /* ** Create the summary frame */ htp.p (''); /* ** Create the details frame */ htp.p (''); /* ** Close the summary/details frameset */ htp.p (''); ELSE /* ** Create the error frame */ htp.p (''); END IF; /* ** Close the header and summary/details frameset */ htp.p (''); htp.htmlClose; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition', 'draw_item_type', p_item_type, p_effective_date); wf_item_definition.Error; END draw_item_type; /*=========================================================================== PROCEDURE NAME: draw_header DESCRIPTION: Draws the top frame of the Item Definition View. It show the title of the view along with controls to return to the find window, return to the main menu or exit the system. ============================================================================*/ PROCEDURE draw_header ( p_item_type VARCHAR2 , p_effective_date VARCHAR2 , p_caller VARCHAR2 ) IS l_username VARCHAR2(320); l_item_type_display_name VARCHAR2(240) := NULL; l_title VARCHAR2(240) := NULL; l_wf_item_types_vl_tbl wf_item_types_vl_pub.wf_item_types_vl_tbl_type; BEGIN -- Make sure user has signed on wfa_sec.GetSession(l_username); /* ** Get the display name for the item type if it was passed in */ IF (p_item_type IS NOT NULL) THEN wf_item_types_vl_pub.fetch_item_type (p_item_type, l_wf_item_types_vl_tbl); l_item_type_display_name := '('||l_wf_item_types_vl_tbl(1).display_name||')'; /* ** Add the effective date to the item type display name ** if it was passed in */ /* ** Don't like this but I can see it coming up so I'll leave it here. */ /* IF (p_effective_date IS NOT NULL) THEN l_item_type_display_name := l_item_type_display_name || ' - ' || p_effective_date || ')'; ELSE l_item_type_display_name := l_item_type_display_name || ')'; END IF; */ END IF; IF (p_caller = 'FIND') THEN l_title := wf_core.translate('WFITD_FIND_ITEM_TYPE'); ELSIF (p_caller = 'DISPLAY') THEN l_title := wf_core.translate('WFITD_ITEM_TYPE_DEFINITION'); --call get session again to set icx values. --this is important when comming from find screen because this --will be executed in a new frame, which is a new session END IF; /* ** Create the Window title */ htp.htmlOpen; htp.headOpen; htp.title(l_title); -- wfa_html.create_help_function('wfnew/wfnew49.htm'); wfa_html.create_help_function('wf/links/itt.htm?ITTDEFPG'); /* ** Open body and draw standard header */ if (p_caller = 'FIND') THEN wfa_sec.header(FALSE, '', l_title, FALSE); else wfa_sec.header(FALSE, 'wf_item_definition.find_item_type', l_title,FALSE); end if; htp.p(''); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition', 'draw_header', p_item_type, p_effective_date, p_caller); wf_item_definition.Error; END draw_header; /*=========================================================================== PROCEDURE NAME: draw_item_summary DESCRIPTION: Draws a hierarchical summary of the Item Type Definition showing all display names for attributes, processes, notifications, functions, etc. The following is an example of the output: ============================================================================*/ PROCEDURE draw_item_summary ( p_item_type VARCHAR2 , p_effective_date VARCHAR2 ) IS l_valid_date BOOLEAN; l_effective_date DATE; l_item_type VARCHAR2(30); l_username VARCHAR2(320); l_expected_format VARCHAR2(80); l_wf_item_types_vl_tbl wf_item_types_vl_pub.wf_item_types_vl_tbl_type; l_wf_item_attributes_vl_tbl wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type; l_wf_activities_vl_tbl wf_activities_vl_pub.wf_activities_vl_tbl_type; l_wf_activity_attr_vl_tbl wf_activities_vl_pub.wf_activity_attr_vl_tbl_type; l_wf_messages_vl_tbl wf_messages_vl_pub.wf_messages_vl_tbl_type; l_wf_message_attr_vl_tbl wf_messages_vl_pub.wf_message_attr_vl_tbl_type; l_wf_lookup_types_tbl wf_lookup_types_pub.wf_lookup_types_tbl_type; l_wf_lookups_tbl wf_lookup_types_pub.wf_lookups_tbl_type; BEGIN /* ** Make sure the user has signed on */ wfa_sec.GetSession(l_username); l_item_type := p_item_type; /* ** Check the effective date that was passed in. If it is invalid ** Then just show a frame with an error. Otherwise show the ** frameset for the summary and details. */ wf_item_definition_util_pub.validate_date ( p_effective_date, l_effective_date, l_valid_date, l_expected_format); /* ** Get all the information about this item type */ wf_item_types_vl_pub.fetch_item_type (l_item_type, l_wf_item_types_vl_tbl); /* ** Fetch all the item attributes associtated with this item type */ wf_item_attributes_vl_pub.fetch_item_attributes (l_item_type, null, l_wf_item_attributes_vl_tbl); /* ** Fetch all the activity information into a list. ** This function will fetch all types of ** activities order by Processes, Notfications, Functions, and then by ** their display name */ wf_activities_vl_pub.fetch_activities (l_item_type, null, l_effective_date, null, l_wf_activities_vl_tbl, l_wf_activity_attr_vl_tbl); /* ** Fetch all the messages and their associated attributes for this item type */ wf_messages_vl_pub.fetch_messages (l_item_type, null, l_wf_messages_vl_tbl, l_wf_message_attr_vl_tbl); /* ** Fetch all the lookup types associtated with this item type */ wf_lookup_types_pub.fetch_lookup_types (l_item_type, null, l_wf_lookup_types_tbl, l_wf_lookups_tbl); /* ** Open body and draw standard header */ wfa_sec.header(background_only=>TRUE); /* ** Open a new table for each attribute so you can control the spacing ** between each attribute */ htp.tableOpen(cattributes=>'border=0 cellpadding=0 cellspacing=0 summary=""'); /* ** List all the item type names */ wf_item_types_vl_pub.draw_item_type_list (l_wf_item_types_vl_tbl, l_effective_date, 0); /* ** List all the item attribute names */ wf_item_attributes_vl_pub.draw_item_attribute_list (l_wf_item_attributes_vl_tbl, l_effective_date, 1); /* ** List all the activity names. This function will list all type of ** activities order by Processes, Notfications, Functions, and then ** by their display name. This is based on how the list was created, not ** by any special processing by the draw_activity_list function */ wf_activities_vl_pub.draw_activity_list (l_wf_activities_vl_tbl, l_wf_activity_attr_vl_tbl, l_effective_date, 1); /* ** List all the message names */ wf_messages_vl_pub.draw_message_list (l_wf_messages_vl_tbl, l_wf_message_attr_vl_tbl, l_effective_date, 1); /* ** List all the lookup type names */ wf_lookup_types_pub.draw_lookup_type_list (l_wf_lookup_types_tbl, l_wf_lookups_tbl, l_effective_date, 1); /* ** Table is created so close it out */ htp.tableClose; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition', 'draw_item_summary', p_item_type, p_effective_date); wf_item_definition.Error; END draw_item_summary; /*=========================================================================== PROCEDURE NAME: draw_item_details DESCRIPTION: Draws a complete listing of all the objects and their associated properties for the given item type. The following is an example of the output: ============================================================================*/ PROCEDURE draw_item_details ( p_item_type VARCHAR2 , p_effective_date VARCHAR2 ) IS l_valid_date BOOLEAN; l_effective_date DATE; l_username VARCHAR2(320); l_item_type VARCHAR2(30); l_expected_format VARCHAR2(80); l_wf_item_types_vl_tbl wf_item_types_vl_pub.wf_item_types_vl_tbl_type; l_wf_item_attributes_vl_tbl wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type; l_wf_activities_vl_tbl wf_activities_vl_pub.wf_activities_vl_tbl_type; l_wf_activity_attr_vl_tbl wf_activities_vl_pub.wf_activity_attr_vl_tbl_type; l_wf_messages_vl_tbl wf_messages_vl_pub.wf_messages_vl_tbl_type; l_wf_message_attr_vl_tbl wf_messages_vl_pub.wf_message_attr_vl_tbl_type; l_wf_lookup_types_tbl wf_lookup_types_pub.wf_lookup_types_tbl_type; l_wf_lookups_tbl wf_lookup_types_pub.wf_lookups_tbl_type; BEGIN l_item_type := p_item_type; /* ** Make sure the user has signed on */ wfa_sec.GetSession(l_username); /* ** Check the effective date that was passed in. If it is invalid ** Then just show a frame with an error. Otherwise show the ** frameset for the summary and details. */ wf_item_definition_util_pub.validate_date ( p_effective_date, l_effective_date, l_valid_date, l_expected_format); /* ** Get all the information about this item type */ wf_item_types_vl_pub.fetch_item_type (l_item_type, l_wf_item_types_vl_tbl); /* ** Fetch all the item attributes associtated with this item type */ wf_item_attributes_vl_pub.fetch_item_attributes (l_item_type, null, l_wf_item_attributes_vl_tbl); /* ** Fetch all the activity information into a list. ** This function will fetch all types of ** activities order by Processes, Notfications, Functions, and then by ** their display name */ wf_activities_vl_pub.fetch_activities (l_item_type, null, l_effective_date, null, l_wf_activities_vl_tbl, l_wf_activity_attr_vl_tbl); /* ** Fetch all the messages and their associated attributes for this item type */ wf_messages_vl_pub.fetch_messages (l_item_type, null, l_wf_messages_vl_tbl, l_wf_message_attr_vl_tbl); /* ** Fetch all the lookup types associtated with this item type */ wf_lookup_types_pub.fetch_lookup_types (l_item_type, null, l_wf_lookup_types_tbl, l_wf_lookups_tbl); /* ** Open body and draw standard header */ wfa_sec.header(background_only=>TRUE); /* ** List all the item type details */ wf_item_types_vl_pub.draw_item_type_details (l_wf_item_types_vl_tbl, 1); /* ** Finish off the list with a couple of blank rows */ htp.p ('

'); /* ** List all the item attribute details */ wf_item_attributes_vl_pub.draw_item_attribute_details (l_wf_item_attributes_vl_tbl, 1); /* ** Finish off the list with a couple of blank rows */ htp.p ('

'); /* ** List all the activity details */ wf_activities_vl_pub.draw_activity_details (l_wf_activities_vl_tbl, l_wf_activity_attr_vl_tbl, l_effective_date, 1, TRUE, TRUE); /* ** Finish off the list with a couple of blank rows */ htp.p ('

'); /* ** List all the message details */ wf_messages_vl_pub.draw_message_details (l_wf_messages_vl_tbl, l_wf_message_attr_vl_tbl, 1); /* ** Finish off the list with a couple of blank rows */ htp.p ('

'); /* ** List all the lookup type details */ wf_lookup_types_pub.draw_lookup_type_details (l_wf_lookup_types_tbl, l_wf_lookups_tbl, 1); EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition', 'draw_item_details', p_item_type, p_effective_date); wf_item_definition.Error; END draw_item_details; /*=========================================================================== PROCEDURE NAME: error DESCRIPTION: Print a page with an error message. Errors are retrieved from these sources in order: 1. wf_core errors 2. Oracle errors 3. Unspecified INTERNAL error ============================================================================*/ PROCEDURE error IS error_name varchar2(30); error_message varchar2(2000); error_stack varchar2(32000); BEGIN /* ** Open an html page */ htp.htmlOpen; htp.headOpen; htp.title(wf_core.translate('ERROR')); htp.headClose; /* ** Create the ICX standard header */ BEGIN wfa_sec.Header(background_only=>TRUE); EXCEPTION WHEN OTHERS THEN htp.bodyOpen; END; htp.header(nsize=>1, cheader=>wf_core.translate('ERROR')); /* ** Get the error message */ wf_core.get_error(error_name, error_message, error_stack); IF (error_name IS NOT NULL) THEN htp.p(error_message); ELSE htp.p(sqlerrm); END IF; htp.hr; htp.p(wf_core.translate('WFENG_ERRNAME')||': '||error_name); htp.br; htp.p(wf_core.translate('WFENG_ERRSTACK')||': '|| replace(error_stack,wf_core.newline,'
')); wfa_sec.Footer; htp.htmlClose; end Error; /*=========================================================================== PROCEDURE NAME: draw_error DESCRIPTION: Draws the bottom frame for the error message if an invalid date has been entered ============================================================================*/ PROCEDURE draw_error (p_effective_date VARCHAR2 , p_expected_format VARCHAR2 ) IS BEGIN wfa_sec.header(background_only=>TRUE); /* ** skip a line */ htp.p('
'); /* ** Write the error message in bold */ htp.bold(wf_core.translate('WFITD_INVALID_EFFECTIVE')); /* ** Write the value the user entered normally */ htp.p(' '||p_effective_date); /* ** skip a line */ htp.p('

'); /* ** Show the expected format */ htp.p(wf_core.translate('WFITD_USE_FORMAT')||' '|| TO_CHAR(sysdate, p_expected_format)); wfa_sec.footer; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition', 'draw_error'); wf_item_definition.Error; END draw_error; /*=========================================================================== PROCEDURE NAME: fetch_item_definition_url DESCRIPTION: Fetches the url address to initiate the Item Definition View ============================================================================*/ PROCEDURE fetch_item_definition_url (p_item_definition_url OUT NOCOPY VARCHAR2) IS BEGIN p_item_definition_url := owa_util.get_owa_service_path|| 'wf_item_definition.find_item_type'; EXCEPTION WHEN OTHERS THEN Wf_Core.Context('wf_item_definition', 'fetch_item_definition_url'); wf_item_definition.Error; END fetch_item_definition_url; END wf_item_definition; / -- SHOW ERRORS PACKAGE BODY wf_item_definition; COMMIT; EXIT;