/*===========================================================================+ | Copyright (c) 1997 Oracle Corporation | | Redwood Shores, California, USA | | All rights reserved. | +===========================================================================*/ REM Added driver autogenrate instructions REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=pls \ REM dbdrv: checkfile:~PROD:~PATH:~FILE SET ARRAY 1; SET SCAN OFF; SET VERIFY OFF; WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; CREATE OR REPLACE PACKAGE wf_item_types_vl_pub AS /* $Header: wfdefs.pls 26.8 2002/12/03 01:12:18 dlam ship $ */ /*=========================================================================== PACKAGE NAME: wf_item_types_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: MODIFICATION LOG: 01/2002 JWSMITH BUG 2001012 - Increase read_role, write_role, execute_role to varchar2(320) ============================================================================*/ /*=========================================================================== PL*SQL TABLE NAME: wf_item_types_vl_tbl_type DESCRIPTION: Stores a list of item types or workflow definitions. Typically you will only ever have one row in this table as it is the master for all the objects within this workflow definition. ============================================================================*/ TYPE wf_item_types_vl_rec_type IS RECORD ( ROW_ID ROWID, NAME VARCHAR2(8), PROTECT_LEVEL NUMBER, CUSTOM_LEVEL NUMBER, WF_SELECTOR VARCHAR2(240), READ_ROLE VARCHAR2(320), WRITE_ROLE VARCHAR2(320), EXECUTE_ROLE VARCHAR2(320), DISPLAY_NAME VARCHAR2(80), DESCRIPTION VARCHAR2(240) ); TYPE wf_item_types_vl_tbl_type IS TABLE OF wf_item_types_vl_pub.wf_item_types_vl_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== 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. PARAMETERS: p_name IN Internal name of the item type p_wf_item_types_vl_tbl OUT The pl*sql table with the detailed definition of the item type ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_item_types_vl_tbl IN The pl*sql table with the detailed definition of the item type p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_item_types_vl_tbl IN The pl*sql table with the detailed definition of the item type p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); END wf_item_types_vl_pub; / -- SHOW ERRORS PACKAGE wf_item_types_vl_pub; CREATE OR REPLACE PACKAGE wf_item_attributes_vl_pub AS /* $Header: wfdefs.pls 26.8 2002/12/03 01:12:18 dlam ship $ */ /*=========================================================================== PACKAGE NAME: wf_item_attributes_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PL*SQL TABLE NAME: wf_item_attributes_vl_tbl_type DESCRIPTION: Stores a list of item attribute definitions for the selected item type. ============================================================================*/ TYPE wf_item_attributes_vl_rec_type IS RECORD ( row_id ROWID, item_type VARCHAR2(8), name VARCHAR2(30), sequence NUMBER, type VARCHAR2(8), protect_level NUMBER, custom_level NUMBER, subtype VARCHAR2(8), format VARCHAR2(240), lookup_type_display_name VARCHAR2(80), lookup_code_display_name VARCHAR2(80), text_default VARCHAR2(4000), number_default NUMBER, date_default DATE, display_name VARCHAR2(80), description VARCHAR2(240) ); TYPE wf_item_attributes_vl_tbl_type IS TABLE OF wf_item_attributes_vl_pub.wf_item_attributes_vl_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== 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. PARAMETERS: p_item_type IN Internal name of the item type p_name IN (optional) Internal name of the item attribute p_wf_item_attributes_vl_tbl OUT The pl*sql table with the detailed definition of the item attributes ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: fetch_item_attribute_display DESCRIPTION: fetch the item attribute display name based on an item type and an item attribute internal name PARAMETERS: p_item_type IN Internal name of the item type p_internal_name IN Internal name of the item attribute p_display_name IN Display name of the item attribute ============================================================================*/ PROCEDURE fetch_item_attribute_display (p_item_type IN VARCHAR2, p_internal_name IN VARCHAR2, p_display_name OUT NOCOPY VARCHAR2); /*=========================================================================== 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. PARAMETERS: p_wf_item_attributes_vl_tbl IN The pl*sql table with the detailed definition of the item attributes p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_item_attributes_vl_tbl IN The pl*sql table with the detailed definition of the item attributes p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); END wf_item_attributes_vl_pub; / -- SHOW ERRORS PACKAGE wf_item_attributes_vl_pub; CREATE OR REPLACE PACKAGE wf_activities_vl_pub AS /* $Header: wfdefs.pls 26.8 2002/12/03 01:12:18 dlam ship $ */ /*=========================================================================== PACKAGE NAME: wf_activities_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PL*SQL TABLE NAME: wf_activities_vl_tbl_type DESCRIPTION: Stores a list of activity definitions for the selected item type. ============================================================================*/ TYPE wf_activities_vl_rec_type IS RECORD ( row_id ROWID, item_type VARCHAR2(8), name VARCHAR2(30), version NUMBER, type VARCHAR2(8), rerun VARCHAR2(8), expand_role VARCHAR2(1), protect_level NUMBER, custom_level NUMBER, begin_date DATE, end_date DATE, function VARCHAR2(240), function_type VARCHAR2(30), result_type VARCHAR2(30), cost NUMBER, read_role VARCHAR2(320), write_role VARCHAR2(320), execute_role VARCHAR2(320), icon_name VARCHAR2(30), message VARCHAR2(30), error_process VARCHAR2(30), runnable_flag VARCHAR2(1), error_item_type VARCHAR2(8), event_name VARCHAR2(240), direction VARCHAR2(30), display_name VARCHAR2(80), result_type_display_name VARCHAR2(80), message_display_name VARCHAR2(80), description VARCHAR2(240) ); TYPE wf_activities_vl_tbl_type IS TABLE OF wf_activities_vl_pub.wf_activities_vl_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== PL*SQL TABLE NAME: wf_activity_attr_vl_tbl_type DESCRIPTION: Stores a list of activity attributes based on the fetch_activity_attributes cursor shown above. ============================================================================*/ TYPE wf_activity_attr_vl_rec_type IS RECORD ( activity_type VARCHAR2(8), activity_display_name VARCHAR2(80), attr_default_display_name VARCHAR2(80), lookup_type_display_name VARCHAR2(80), lookup_code_display_name VARCHAR2(80), row_id ROWID, activity_item_type VARCHAR2(8), activity_name VARCHAR2(30), activity_version NUMBER, name VARCHAR2(30), sequence NUMBER, type VARCHAR2(8), value_type VARCHAR2(8), protect_level NUMBER, custom_level NUMBER, subtype VARCHAR2(8), format VARCHAR2(240), text_default VARCHAR2(4000), number_default NUMBER, date_default DATE, display_name VARCHAR2(80), description VARCHAR2(240) ); TYPE wf_activity_attr_vl_tbl_type IS TABLE OF wf_activities_vl_pub.wf_activity_attr_vl_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== 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. PARAMETERS: p_item_type IN Internal name of the item type p_activity_type IN (optional) The type of activity you would like to retrieve. Values: PROCESS, NOTICE, FUNCTION p_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. p_name IN (optional) Internal name of the activity p_wf_activities_vl_tbl OUT The pl*sql table with the detailed definition of the activities for this item type p_wf_activity_attr_vl_tbl OUT The pl*sql table with the detailed definition of the activity attributes ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_item_type IN Internal name of the item type p_activity_type IN The type of activity you would like to retrieve. Values: PROCESS, NOTICE, FUNCTION p_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. p_name IN Internal name of the activity ============================================================================*/ PROCEDURE fetch_draw_activity_details (p_item_type IN VARCHAR2, p_activity_type IN VARCHAR2, p_effective_date IN VARCHAR2, p_name IN VARCHAR2); /*=========================================================================== 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. PARAMETERS: p_wf_activities_vl_tbl IN The pl*sql table with the detailed definition of the activities for this item type p_wf_activity_attr_vl_tbl IN The pl*sql table with the detailed definition of the activity attributes p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_activity_attr_vl_tbl IN The pl*sql table with the detailed definition of the activity attributes p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_activities_vl_tbl IN The pl*sql table with the detailed definition of the activities for this item type p_wf_activity_attr_vl_tbl IN The pl*sql table with the detailed definition of the activity attributes p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between the notification details and the corresponding message details. I'm investigating whenther the full parameter list is required for a link within the same frame since the notification and message details would be in the same frame. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. p_create_child_links IN Tells the function whether to create the hot link between an activity and any child object related to that activity. For example you may wish to link a notification message name with its corresponding details. If you have no intention of listing those message details then you want to pass FALSE for the p_create_child_links parameter. Otherwise you would pass TRUE. p_print_skipped_titles IN Tells the function whether to print titles for activity types that have been skipped. This is useful when you're calling this function to draw a single activity in which case you would pass FALSE. If you're listing all activities for a given item type then you probably do want to list all activity type titles so you probably want to pass TRUE ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: draw_activity_attr_details DESCRIPTION: Shows all of the details for a list of activity attributes for that have been passed in. PARAMETERS: p_wf_activity_attr_vl_tbl IN The pl*sql table with the detailed definition of the activity attributes p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); END wf_activities_vl_pub; / -- SHOW ERRORS PACKAGE wf_activities_vl_pub; CREATE OR REPLACE PACKAGE wf_messages_vl_pub AS /* $Header: wfdefs.pls 26.8 2002/12/03 01:12:18 dlam ship $ */ /*=========================================================================== PACKAGE NAME: wf_messages_vl_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PL*SQL TABLE NAME: wf_messages_vl_tbl_type DESCRIPTION: Stores a list of message definitions for the selected item type. ============================================================================*/ TYPE wf_messages_vl_rec_type IS RECORD ( ROW_ID ROWID, TYPE VARCHAR2(8), NAME VARCHAR2(30), PROTECT_LEVEL NUMBER, CUSTOM_LEVEL NUMBER, DEFAULT_PRIORITY NUMBER, READ_ROLE VARCHAR2(320), WRITE_ROLE VARCHAR2(320), DISPLAY_NAME VARCHAR2(80), DESCRIPTION VARCHAR2(240), SUBJECT VARCHAR2(240), HTML_BODY VARCHAR2(4000), BODY VARCHAR2(4000)); TYPE wf_messages_vl_tbl_type IS TABLE OF wf_messages_vl_pub.wf_messages_vl_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== PL*SQL TABLE NAME: wf_message_attr_vl_tbl_type DESCRIPTION: Stores a list of message attributes based on the fetch_message_attributes cursor shown above. ============================================================================*/ TYPE wf_message_attr_vl_rec_type IS RECORD ( message_display_name VARCHAR2(80), attr_default_display_name VARCHAR2(80), lookup_type_display_name VARCHAR2(80), lookup_code_display_name VARCHAR2(80), row_id ROWID, message_type VARCHAR2(8), message_name VARCHAR2(30), name VARCHAR2(30), sequence NUMBER, type VARCHAR2(8), subtype VARCHAR2(8), attach VARCHAR2(1), value_type VARCHAR2(8), protect_level NUMBER, custom_level NUMBER, format VARCHAR2(240), text_default VARCHAR2(4000), number_default NUMBER, date_default DATE, display_name VARCHAR2(80), description VARCHAR2(240) ); TYPE wf_message_attr_vl_tbl_type IS TABLE OF wf_messages_vl_pub.wf_message_attr_vl_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== 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. PARAMETERS: p_item_type IN Internal name of the item type p_name IN (optional) Internal name of the message p_wf_messages_vl_tbl OUT The pl*sql table with the detailed definition of the messages for this item type p_wf_message_attr_vl_tbl OUT The pl*sql table with the detailed definition of the message attributes ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_messages_vl_tbl IN The pl*sql table with the detailed definition of the messages for this item type p_wf_message_attr_vl_tbl IN The pl*sql table with the detailed definition of the message attributes p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: fetch_message_display DESCRIPTION: fetch the messagedisplay name based on a item type name and an internal item message name PARAMETERS: p_item_type IN Internal name of the item type p_internal_name IN Internal name of the message p_display_name IN Display name of the message ============================================================================*/ PROCEDURE fetch_message_display (p_item_type IN VARCHAR2, p_internal_name IN VARCHAR2, p_display_name OUT NOCOPY VARCHAR2); /*=========================================================================== 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. PARAMETERS: p_wf_message_attr_vl_tbl IN The pl*sql table with the detailed definition of the message attributes p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_messages_vl_tbl IN The pl*sql table with the detailed definition of the messages for this item type p_wf_message_attr_vl_tbl IN The pl*sql table with the detailed definition of the message attributes p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: draw_message_attr_details DESCRIPTION: Shows all of the details for a list of message attributes for that have been passed in. PARAMETERS: p_wf_message_attr_vl_tbl IN The pl*sql table with the detailed definition of the message attributes p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); END wf_messages_vl_pub; / -- SHOW ERRORS PACKAGE wf_messages_vl_pub; CREATE OR REPLACE PACKAGE wf_lookup_types_pub AS /* $Header: wfdefs.pls 26.8 2002/12/03 01:12:18 dlam ship $ */ /*=========================================================================== PACKAGE NAME: wf_lookup_types_pub DESCRIPTION: OWNER: GKELLNER TABLES/RECORDS: PROCEDURES/FUNCTIONS: ============================================================================*/ /*=========================================================================== PL*SQL TABLE NAME: wf_lookup_types_tbl_type DESCRIPTION: Stores a list of lookup type definitions for the selected item type. ============================================================================*/ TYPE wf_lookup_types_rec_type IS RECORD ( ROW_ID ROWID, LOOKUP_TYPE VARCHAR2(30), ITEM_TYPE VARCHAR2(8), PROTECT_LEVEL NUMBER, CUSTOM_LEVEL NUMBER, DISPLAY_NAME VARCHAR2(80), DESCRIPTION VARCHAR2(240) ); TYPE wf_lookup_types_tbl_type IS TABLE OF wf_lookup_types_pub.wf_lookup_types_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== PL*SQL TABLE NAME: wf_lookups_tbl_type DESCRIPTION: Stores a list of lookups based on the fetch_lookups cursor shown above. ============================================================================*/ TYPE wf_lookups_rec_type IS RECORD ( lookup_type_display_name VARCHAR2(80), item_type VARCHAR2(8), row_id ROWID, lookup_type VARCHAR2(30), lookup_code VARCHAR2(30), protect_level NUMBER, custom_level NUMBER, meaning VARCHAR2(80), description VARCHAR2(240) ); TYPE wf_lookups_tbl_type IS TABLE OF wf_lookup_types_pub.wf_lookups_rec_type INDEX BY BINARY_INTEGER; /*=========================================================================== PROCEDURE NAME: fetch_lookup_types 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. PARAMETERS: p_item_type IN Internal name of the item type p_name IN (optional) Internal name of the lookup type p_wf_lookup_types_tbl OUT The pl*sql table with the detailed definition of the lookup types p_wf_lookups_tbl OUT The pl*sql table with the detailed definition of the lookups ============================================================================*/ 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); /*=========================================================================== 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 PARAMETERS: p_type_internal_name IN Internal name of the lookup type p_type_internal_code IN Internal name of the lookup code p_type_display_name IN Display name of the lookup type p_code_display_name IN Display name of the lookup code ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_lookup_types_tbl IN The pl*sql table with the detailed definition of the lookup types p_wf_lookups_tbl IN The pl*sql table with the detailed definition of the lookups p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: draw_lookup_list DESCRIPTION: Shows the display name of lookups 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. PARAMETERS: p_wf_lookups_tbl IN The pl*sql table with the detailed definition of the lookups p_effective_date IN The effective date that was requested. This is required if you would like to create hotlinks between a summary frame view and your detail frame view. Since the listing are usually implemented as frames the links need to include all the attributes that were used to generate those frames. p_indent_level IN How many spaces would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_wf_lookup_type_tbl IN The pl*sql table with the detailed definition of the lookup type p_wf_lookups_tbl IN The pl*sql table with the detailed definition of the lookups p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: draw_lookup_details DESCRIPTION: Shows all the details of a lookup as a html view. This function uses the htp to generate its html output. PARAMETERS: p_wf_lookups_tbl IN The pl*sql table with the detailed definition of the lookups p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ PROCEDURE draw_lookup_details (p_wf_lookups_tbl IN wf_lookup_types_pub.wf_lookups_tbl_type, p_indent_level IN NUMBER); END wf_lookup_types_pub; / -- SHOW ERRORS PACKAGE wf_lookup_types_pub; CREATE OR REPLACE PACKAGE wf_item_definition_util_pub AS /* $Header: wfdefs.pls 26.8 2002/12/03 01:12:18 dlam 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. PARAMETERS: p_customization_level IN Customization Level value p_protection_level IN Protection Level value ============================================================================*/ PROCEDURE draw_custom_protect_details (p_customization_level IN VARCHAR2, p_protection_level IN VARCHAR2); /*=========================================================================== PROCEDURE NAME: draw_read_write_execute_details DESCRIPTION: Writes out the read, write, execute role prompts and values for a detailed listing of a workflow object. PARAMETERS: p_read_role IN Read role value p_write_role IN Write role value p_execute_role IN Execute role value p_draw_execute_role IN Not all activity objects (Notifications) have an execute role. This parameters prevents that prompt from being listed. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_item_type IN Frame parameter for the item type p_effective_date IN Frame parameter for the effective date p_object_type_prefix IN Type of workflow object that you are going to create p_internal_name IN Internal name of that object p_display_name IN Display name of the object p_detail_prompt IN If this is a link between a detail object and other detail object then what prompt would you like to preceed the value with p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== 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. PARAMETERS: p_object_type_prefix IN Type of workflow object that you are going to create p_internal_name IN Internal name of that object p_display_name IN Display name of the object p_display_prompt IN The prompt would you like to preceed the value with p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: draw_summary_section_title DESCRIPTION: Draws the bold section title for an object type in the summary frame. PARAMETERS: p_section_title IN Section title p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ PROCEDURE draw_summary_section_title ( p_section_title IN VARCHAR2, p_indent_level IN NUMBER); /*=========================================================================== PROCEDURE NAME: draw_detail_section_title DESCRIPTION: Draws the bold section title and the thick line for an object type in the detail frame. PARAMETERS: p_section_title IN Section title p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ PROCEDURE draw_detail_section_title ( p_section_title IN VARCHAR2, p_indent_level IN NUMBER); /*=========================================================================== PROCEDURE NAME: draw_detail_prompt_value_pair DESCRIPTION: Draws the bold detail section prompt and its corresponding value in the detail frame PARAMETERS: p_prompt IN Prompt to be shown in bold p_value IN Value corresponding to the prompt ============================================================================*/ PROCEDURE draw_detail_prompt_value_pair (p_prompt IN VARCHAR2, p_value IN VARCHAR2); /*=========================================================================== PROCEDURE NAME: draw_detail_prompt DESCRIPTION: Draws the bold detail section prompt PARAMETERS: p_prompt IN Prompt to be shown in bold ============================================================================*/ PROCEDURE draw_detail_prompt (p_prompt IN VARCHAR2); /*=========================================================================== PROCEDURE NAME: draw_detail_value DESCRIPTION: Draws the value of an attribute PARAMETERS: p_value IN Value to be drawn ============================================================================*/ PROCEDURE draw_detail_value (p_value IN VARCHAR2); /*=========================================================================== 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: p_highest_level IN What was the highest activity type reached p_current_level IN What is the current activity level p_indent_level IN How many space would you like to indent this listing from the left border of the screen. ============================================================================*/ PROCEDURE activity_titles_list ( p_highest_level IN NUMBER, p_current_level IN NUMBER, p_indent_level IN NUMBER); /*=========================================================================== 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: p_highest_level IN What was the highest activity type reached p_current_level IN What is the current activity level ============================================================================*/ PROCEDURE activity_titles_details ( p_highest_level IN NUMBER, p_current_level IN NUMBER); /*=========================================================================== 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 teh NLS_DATE_FORMAT parameter. PARAMETERS: p_char_date IN Char datatype date string p_date_date OUT Return the date as a date datatype p_valid_date OUT Tells the caller whether the string could be converted to a date or not. p_expected_format OUT Tells the caller what is the expected format is for the date ============================================================================*/ 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); /*=========================================================================== PROCEDURE NAME: create_checkbox DESCRIPTION: Create a checkbox entry in a table PARAMETERS: p_name IN Name of the checkbox p_value IN Value that is returned as a parameter when this checkbox is checked p_checked IN Is the default value checked or unchecked (Values = 'Y' or 'N') p_prompt IN Checkbox prompt show to user p_image_name IN Icon name for the checkbox ============================================================================*/ PROCEDURE create_checkbox ( p_name IN VARCHAR2, p_value IN VARCHAR2, p_checked IN VARCHAR2, p_prompt IN VARCHAR2, p_image_name IN VARCHAR2 DEFAULT NULL, p_new_row IN BOOLEAN DEFAULT FALSE ); END wf_item_definition_util_pub; / -- SHOW ERRORS PACKAGE wf_item_definition_util_pub; CREATE OR REPLACE PACKAGE wf_item_definition AS /* $Header: wfdefs.pls 26.8 2002/12/03 01:12:18 dlam 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. PARAMETERS: ============================================================================*/ PROCEDURE 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. The frames are constructed in the following manner: ______________________ | | | HEADER | |---------------------| | | | | | | | SUMMARY | DETAILS | | | | | | | |---------------------| PARAMETERS: p_item_type IN Internal name of the item type that was selected in the find window. p_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. ============================================================================*/ PROCEDURE draw_item_type( p_item_type VARCHAR2 DEFAULT NULL, p_effective_date VARCHAR2 DEFAULT NULL); /*=========================================================================== 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: Workflow Requisition Approval Demonstration Attributes Forward From Display Name Forward From Username Forward To Display Name Forward To Username Processes Notify Approver Requisition Approval Notifications Notifify Requestor of Approval Notify Requestor No Approver Available Reminder-Approval Needed Functions Approve Requisition Verify Authority Messages Requisition Approval Required Message Attributes Requisition Amount Action etc... PARAMETERS: p_item_type IN Internal name of the item type that was selected in the find window. p_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. ============================================================================*/ PROCEDURE draw_item_summary( p_item_type VARCHAR2 DEFAULT NULL, p_effective_date VARCHAR2 DEFAULT NULL); /*=========================================================================== 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: Item Type Details ---------------------------------------------------------------------------- Item Type Name Workflow Requisition Approval Demonstration Internal Name WFREQAPP Description Selector WF_REQDEMO.SELECTOR Read Role Write Role Execute Role Customization Level 0 Protection Level 100 Attribute Details ---------------------------------------------------------------------------- Attribute Name Forward From Display Name Internal Name FORWARD_FROM_DISPLAY_NAME Description Name of the person that the requisition is forwarded from Attribute Type VARCHAR2 Format Default Customization Level 0 Protection Level 100 etc... PARAMETERS: p_item_type IN Internal name of the item type that was selected in the find window. p_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. ============================================================================*/ PROCEDURE draw_item_details( p_item_type VARCHAR2 DEFAULT NULL, p_effective_date VARCHAR2 DEFAULT NULL); /*=========================================================================== 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. PARAMETERS: p_item_type IN Internal name of the item type that was selected in the find window. p_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. p_caller IN Tells the procedure which title to display (FIND, DISPLAY) ============================================================================*/ PROCEDURE draw_header ( p_item_type VARCHAR2 DEFAULT NULL, p_effective_date VARCHAR2 DEFAULT NULL, p_caller VARCHAR2 DEFAULT NULL); /*=========================================================================== 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 PARAMETERS: ============================================================================*/ PROCEDURE error; /*=========================================================================== PROCEDURE NAME: draw_error DESCRIPTION: Draws the bottom frame for the error message if an invalid date has been entered PARAMETERS: p_effective_date IN Invalid date user entered pexpected_format IN Format that the window expects ============================================================================*/ PROCEDURE draw_error (p_effective_date IN VARCHAR2 DEFAULT NULL, p_expected_format IN VARCHAR2 DEFAULT NULL); /*=========================================================================== PROCEDURE NAME: fetch_item_definition_url DESCRIPTION: Fetches the url address to initiate the Item Definition View PARAMETERS: p_item_definition_url OUT Returns the name of the url to initiate the Item Definition View ============================================================================*/ PROCEDURE fetch_item_definition_url (p_item_definition_url OUT NOCOPY VARCHAR2); END wf_item_definition; / -- SHOW ERRORS PACKAGE wf_item_definition; COMMIT; EXIT;