Release 2.6.4
Part Number B15852-05
Contents
Previous
Next
| Oracle Workflow Administrator's Guide Release 2.6.4 Part Number B15852-05 | Contents | Previous | Next |
This chapter describes the SQL scripts that workflow administrators can run against an Oracle Workflow server installation.
This chapter covers the following topics:
You can use any of the following administrative scripts to help set up and maintain various features in Oracle Workflow. For the standalone version of Oracle Workflow, the scripts are located on your server in the Oracle Workflow admin/sql subdirectory. For the version of Oracle Workflow embedded in Oracle Applications, the scripts are located in the sql subdirectory under $FND_TOP.
Update translation tables - WFNLADD.sql.
Enable/disable a language - wfnlena.sql
Run a workflow process - wfrun.sql.
Start a background engine - wfbkg.sql.
Show activities deferred for the next background engine execution - wfbkgchk.sql.
Display a status report for an item
Show a notification's status - wfntfsh.sql.
Display debugging information for a notification - wfmlrdbg.sql.
Reset the protection level for objects - wfprot.sql.
Handle errored activities - wfretry.sql.
Check for version and process definition errors
Check for invalid hanging foreign keys - wfrefchk.sql.
Check the directory service data model - wfdirchk.sql.
Clean up workflow queues in the system tables - wfqclean.sql.
Change the internal name of a workflow object
Note: Generally, you cannot update the internal name of a workflow object in Oracle Workflow Builder. However, if you load your process definition to a database, you can use one of these scripts to update a workflow object's internal name if no runtime data exists for the object. You should only use these scripts to correct errors in an object's internal name during design time. Do not use these scripts to rename objects that are involved in running instances of processes.
Remove data from Oracle Workflow tables
Note: In Oracle Applications, a standard concurrent program called "Purge Obsolete Workflow Runtime Data" is also available. See: FNDWFPR.
If you are using the standalone version of Oracle Workflow, you can use the standalone Oracle Workflow Manager component available through Oracle Enterprise Manager to submit and manage Workflow purge database jobs. For more information, please refer to the Oracle Workflow Manager online help.
Display the version of the Oracle Workflow server - wfver.sql.
Stop the Java Function Activity Agent - wfjvstop.sql.
Enqueue an event message on a queue using an override agent - wfevtenq.sql.
Run a listener to monitor an agent for inbound event messages - wfagtlst.sql.
Display debugging information for an event - wfbesdbg.sql.
For Oracle Workflow embedded in Oracle Applications, use the standard concurrent program FNDWFPR "Purge Obsolete Workflow Runtime Data" to purge old data from the Oracle Workflow tables regularly.
This program purges obsolete runtime information associated with work items, including status information and any associated notifications and Oracle XML Gateway transactions. By default, it also purges obsolete design information, such as activities that are no longer in use and expired ad hoc users and roles, and obsolete runtime information not associated with work items, such as notifications or Oracle XML Gateway transactions that were not handled through a workflow process. You can optionally choose to purge only core runtime information associated with work items for performance gain during periods of high activity, and purge all obsolete information as part of your routine maintenance during periods of low activity.
Navigate to the Submit Requests form in Oracle Applications to submit the Purge Obsolete Workflow Runtime Data concurrent program. When you install and set up Oracle Applications and Oracle Workflow, your system administrator needs to add this concurrent program to a request security group for the responsibility that you want to run this program from. See: Overview of Concurrent Programs and Requests, Oracle Applications System Administrator's Guide and Running Reports and Programs, Oracle Applications User's Guide.
You can supply the following parameters for the Purge Obsolete Workflow Runtime Data concurrent program:
Item Type - The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
Item Key - The item key to purge. Leaving this field blank defaults to purging the runtime data for all item keys.
Age - Minimum age of data to purge, in days. The default is0.
Persistence Type - The persistence type to be purged, either Temporary or Permanent. The default is Temporary
Core Workflow Only - Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The default is 'N'.
Commit Frequency - The number of records to purge before the program commits data. To reduce rollback size and improve performance, set this parameter to commit data after a smaller number of records. The default is 500 records.
Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit, the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.
Note: If you are using the version of Oracle Workflow embedded in Oracle Applications and you have implemented Oracle Applications Manager, you can use Oracle Workflow Manager to submit and manage the Purge Obsolete Workflow Runtime Data concurrent program. For more information, please refer to the Oracle Applications Manager online help.
Additionally, in Oracle Applications you can use the Purge Obsolete ECX Data concurrent program to purge Oracle XML Gateway transactions according to Oracle XML Gateway-specific parameters. See: Purge Obsolete ECX Data Concurrent Program, Oracle XML Gateway User's Guide.
If you enable a new language in your Oracle installation, use WFNLADD.sql to add the missing rows for that language to the Oracle Workflow translation tables. See: Setting Up Additional Languages and wfnlena.sql.
Use the script as follows:
sqlplus <user/pwd> @WFNLADDNote: The wfnlena.sql and WFNLADD.sql scripts prepare the database for loading workflow definitions in another language. However, these scripts by themselves do not enable displaying Oracle Workflow Web pages in other languages. See: Setting Up Additional Languages.
Use wfagtlst.sql to run a PL/SQL agent listener to monitor an agent for inbound event messages. When a message is received, the Event Manager searches for and executes any enabled subscriptions by the local system to the event with a source type of External, and also any enabled subscriptions by the local system to the Any event with a source type of External.
Use the script as follows:
sqlplus <user/pwd> @wfagtlst <agent_name>Replace <agent_name> with the internal name of the agent that you want to monitor for inbound event messages.
Note: You should use this script primarily for debugging purposes.
Related Topics
Listen, Oracle Workflow API Reference
Use wfbesdbg.sql to display debugging information for an event that you can use to check the status of the event or investigate errors. This script creates an output file named wfevtdbg<event_key>.html, where <event_key> is the event key for a particular instance of the event. The output file displays details about the Business Event System, including the statuses of the local system and agent listeners, details about the definitions of the specified event and any subscriptions to that event, and details about the specified instance of the event if it appears on one of these queues:
WF_DEFERRED
WF_JAVA_DEFERRED
WF_ERROR
WF_JAVA_ERROR
Use the script as follows:
sqlplus <user/pwd> @wfbesdbg <event_name> <event_key>Replace <event_name> with the name of the event and <event_key> with the event key that identifies the particular instance of the event. The event name and event key parameters are case-sensitive.
Note: In Oracle Applications, you can also obtain this information by running the Event Diagnostic Test in Oracle Diagnostics. See: Event Diagnostic Test.
If you are using the standalone version of Oracle Workflow, you can use wfbkg.sql to start a background engine. This script calls the WF_ENGINE.Background API to run a background engine for the indicated number of minutes. On completing its current set of eligible activities to process, the background process waits for the specified number of seconds before launching another background engine. This cycle continues until the indicated number of minutes have elapsed.
Use the script as follows:
sqlplus <user/pwd> @wfbkg <minutes> <seconds>Replace <minutes> with the number of minutes you want the background engine to run, and replace <seconds> with the number of seconds you want the background engine to wait between queries.
Related Topics
Background, Oracle Workflow API Reference
Setting up Background Workflow Engines
Use wfbkgchk.sql to get a list of all activities waiting to be processed by the background engine the next time it runs.
Use the script as follows:
sqlplus <user/pwd> @wfbkgchk <WF_schema>Replace <WF_schema> with the Oracle Workflow schema, usually applsys in Oracle Applications or owf_mgr in standalone Oracle Workflow.
Related Topics
Background, Oracle Workflow API Reference
Setting up Background Workflow Engines
Use wfchact.sql to change the internal name of an activity and update all references to the activity. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchact <act_type> <old_act> <new_act>Replace <act_type> with the item type that the activity you wish to update is associated with, replace <old_act> with the current internal name of the activity, and replace <new_act> with the new internal name of the activity.
Use wfchacta.sql to change the internal name of an activity attribute and update all references to the activity attribute. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchacta <act_type> <old_acta> <new_acta>Replace <act_type> with the item type that the activity attribute you wish to update is associated with, replace <old_acta> with the current internal name of the activity attribute, and replace <new_acta> with the new internal name of the activity attribute.
Use wfchita.sql to change the internal name of an item attribute and update all references to the item attribute. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchita <item_type> <old_attr> <new_attr>Replace <item_type> with the item type that the item attribute you wish to update is associated with, replace <old_attr> with the current internal name of the item attribute, and replace <new_attr> with the new internal name of the item attribute.
Use wfchitt.sql to change the internal name of an item type and update all references to the item type. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchitt <old_type> <new_type>Replace <old_type> with the current internal name of the item type, and replace <new_type> with the new internal name of the item type.
Use wfchluc.sql to change the internal name of a lookup code and update all references to the lookup code. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchluc <lookup_type> <old_luc> <new_luc>Replace <lookup_type> with the lookup type of the lookup code you wish to update, replace <old_luc> with the current internal name of the lookup code, and replace <new_luc> with the new internal name of the lookup code.
Use wfchlut.sql to change the internal name of a lookup type and update all references to the lookup type. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchlut <old_lut> <new_lut>Replace <old_lut> with the current internal name of the lookup type, and replace <new_lut> with the new internal name of the lookup type.
Use wfchmsg.sql to change the internal name of a message and update all references to the message. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchmsg <msg_type> <old_msg> <new_msg>Replace <msg_type> with the item type of the message you wish to update, replace <old_msg> with the current internal name of the message, and replace <new_msg> with the new internal name of the message.
Use wfchmsga.sql to change the internal name of a message attribute. This script does not update the message subject or message body references to the message attribute, however. You must manually update the message attribute references. See: Change the internal name of a workflow object.
Use the script as follows:
sqlplus <user/pwd> @wfchmsga <msg_type> <msg_name> <old_attr> <new_attr>Replace <msg_type> with the item type of the message attribute you wish to update, replace <msg_name> with the internal name of the message that the message attribute belongs to, replace <old_attr> with the current internal name of the message attribute, and replace <new_attr> with the new internal name of the message attribute.
Use wfdirchk.sql to check for the following conditions in your directory service data model:
Invalid internal names that contain the characters '#', ':', or '/' in WF_USERS.
Invalid compound names in WF_USERS or WF_ROLES.
Duplicate names in WF_USERS or WF_ROLES.
Multiple names in WF_USERS or WF_ROLES linked to the same row in the original repository.
Missing display names in WF_USERS or WF_ROLES.
Invalid notification preference or null e-mail address if the notification preference is set in WF_USERS or WF_ROLES to receive e-mail.
Invalid status in WF_USERS.
Rows in WF_USERS that do not have a corresponding row in WF_ROLES.
Invalid internal names in WF_ROLES that contain the characters '#' or '/' or have a length greater than 30 characters.
Invalid user/role foreign key in WF_USER_ROLES.
Missing user/role in WF_USER_ROLES. Every user must participate in its own role.
Duplicate rows in WF_USER_ROLES.
Wfdirchk.sql should return no rows to ensure that your directory service data model is correct.
Use the script as follows:
sqlplus <user/pwd> @wfdirchkUse wfevtenq.sql to enqueue an event message on a local queue using an override agent. This script constructs an event message using the event name, event key, event data, From Agent, and To Agent you specify. Then the event message is enqueued on the queue associated with the override agent you specify, which can be different than the From Agent listed inside the event message. If no override agent is specified, the event message is enqueued on the message's From Agent by default.
Note: This script can only enqueue an event message onto a queue for an agent on the local system.
Use the script as follows:
sqlplus <user/pwd> @wfevtenq <overrideagent> <overridesystem>
<fromagent> <fromsystem> <toagent> <tosystem> <eventname>
<eventkey> <message>Replace the variables with your parameters as follows:
<overrideagent> - The agent on whose queue you want to enqueue the event message.
<overridesystem> - The system where the override agent is located.
<fromagent> - The From Agent that you want to list in the event message.
<fromsystem> - The system where the From Agent is located.
<toagent> - The To Agent that receives the event message.
<tosystem> - The system where the To Agent is located.
<eventname> - The internal name of the event.
<eventkey> - The event key that uniquely identifies the instance of the event.
<message> - The event data.
Use wfjvstop.sql to stop the Java Function Activity Agent by placing a stop message on the 'Outbound' queue.
Use the script as follows:
sqlplus <user/pwd> @wfjvstopUse wfmlrdbg.sql to display debugging information for a notification that you can use to check the status of the notification or investigate errors. This script creates an output file named wfmlrdbg<nid>.html, where <nid> is the notification ID (NID).
For Oracle Applications, use the script as follows:
sqlplus <user/pwd> @wfmlrdbg <nid>For standalone Oracle Workflow, use the script as follows:
sqlplus <user/pwd> @wfmlrdbg <nid> <FND_schema>Replace <nid> with the notification ID for the notification. For standalone Oracle Workflow, also replace <FND_schema> with the ORACLE username for Oracle Workflow, usually owf_mgr.
Note: In Oracle Applications, you can also obtain this information by running the Mailer Diagnostic Test in Oracle Diagnostics. See: Mailer Diagnostic Test.
If you define a new language in your Oracle installation, use wfnlena.sql to enable or disable that language in Oracle Workflow. See: WFNLADD.sql.
Use the script as follows:
sqlplus <user/pwd> @wfnlena <language_code> <enable_flag>Replace <language_code> with a valid language code, and replace <enable_flag> with Y to enable and N to disable the specified language.
Note: The wfnlena.sql and WFNLADD.sql scripts prepare the database for loading workflow definitions in another language. However, these scripts by themselves do not enable displaying Oracle Workflow Web pages in other languages. See: Setting Up Additional Languages.
Use wfntfsh.sql to display status information about a particular notification, given its notification ID.
Use the script as follows:
sqlplus <user/pwd> @wfntfsh <notification_id>Use wfprot.sql to reset the protection level of all objects associated with a specified item type.
Important: If you reset the protection level for all objects in an item type, then none of those objects in the item type will be customizable by users operating at an access level higher than the new protection level.
Use the script as follows:
sqlplus <user/pwd> @wfprot <item_type> <protection_level>Replace <item_type> with the item type that you want to reset the protection level for, and replace <protection_level> with the new protection level.
Use wfqclean.sql to clean up Workflow queues in the system tables.
Important: This script is only necessary if you are using a version of the Oracle8 Database prior to 8.1.5 and you drop your user or tablespace without previously dropping the Workflow queues using wfqued.sql. The wfqued.sql script is located in the Oracle Workflow sql subdirectory. The DROP USER CASCADE and DROP TABLESPACE INCLUDING CONTENTS commands in these prior versions of Oracle8 leave queue data in your system tables that result in an ORA-600 error when you recreate the queues. To avoid this case, you should always run wfqued.sql to drop the queues prior to dropping the user or tablespace.
Use the wfqclean.sql script as follows:
sqlplus system/manager @wfqclean <un>Replace <un> with the username of the schema that experiences the ORA-600 error.
Use wfrefchk.sql to check for invalid workflow data that is missing primary key data for a foreign key.
sqlplus <user/pwd> @wfrefchkUse wfretry.sql to display a list of activities that have encountered an error for a given process instance and then specify whether to skip, retry, or reset any one of those errored activities.
Use the script as follows:
sqlplus <user/pwd> @wfretry <item_type> <item_key>Provide an item type and item key to uniquely identify an item or process instance. The script first returns the list of errored activities by label name. The script then prompts you for the label name of an activity that you wish to skip, retry, or reset. If you choose skip, then you must also specify the result that you want the skipped activity to have.
Note: This script calls the WF_ENGINE HandleError API, so you can actually specify the label name of any activity associated with the specified item type and item key to perform a rollback. See: HandleError, Oracle Workflow API Reference.
Use wfrmall.sql to delete all data in all Oracle Workflow runtime and design time tables.
Use the script as follows:
sqlplus <user/pwd> @wfrmallWarning: This script deletes ALL workflow definitions. Do not use this script unless you are absolutely sure you want to remove all workflow data from your runtime and design time tables.
After you run this script, you should also reload the workflow definitions for the Standard, System: Mailer, and System: Error item types stored in the files wfstd.wft, wfmail.wft, and wferror.wft, respectively.
Use wfrmita.sql to delete all workflow data for a specified item type attribute. This script prompts you for the item type and the name of the attribute to delete. Alternatively, you can use Oracle Workflow Builder to delete an item type attribute from a workflow definition stored in a file or a database.
Use the script as follows:
sqlplus <user/pwd> @wfrmitaUse wfrmitms.sql to delete status information in Oracle Workflow runtime tables for a particular item. This script prompts you to choose between deleting all data associated with a specified item type and item key or deleting only data for the completed activities of the specified item type and item key.
Use the script as follows:
sqlplus <user/pwd> @wfrmitms <item_type> <item_key>Use wfrmitt.sql to delete all data in all Oracle Workflow design time and runtime tables for a particular item type. This script prompts you for an item type from a list of valid item types.
Use the script as follows:
sqlplus <user/pwd> @wfrmittWarning: This script deletes ALL workflow data for the specified item type.
Use wfrmtype.sql to delete runtime data associated with a given item type. This script prompts you for an item type to purge from a list of valid item types, then asks you to choose between deleting all runtime data associated with the specified item type or deleting only runtime data for the completed activities and items of the specified item type.
Use the script as follows:
sqlplus <user/pwd> @wfrmtypeUse wfrun.sql to create and start a specified process.
Use the script as follows:
sqlplus <user/pwd> @wfrun <item_type> <item_key> <process_name>Note: The item key for a process instance can only contain single-byte characters. It cannot contain a multibyte value.
Use wfstat.sql to display a developer status report for a specified item. The output is 132 characters per line.
Use the script as follows:
sqlplus <user/pwd> @wfstat <item_type> <item_key>Use wfstatus.sql to display an end user status report for a specified item. The output is 132 characters per line.
Use the script as follows:
sqlplus <user/pwd> @wfstatus <item_type> <item_key>Use wfstdchk.sql to check and report any problems found in the Oracle Workflow data model. For example, this script will report any function activities that reference invalid functions and scan the tables of each workflow process definition object to verify that each row has a valid internal name and display name.
Use the script as follows:
sqlplus <user/pwd> @wfstdchkUse wfver.sql to display the version of the Oracle Workflow server, the status and version of the Oracle Workflow PL/SQL packages, and the version of the Oracle Workflow views installed.
Use the script as follows:
sqlplus <user/pwd> @wfverUse wfverchk.sql if you suspect that problems arising in your workflow process are due to multiple versions of an activity being active simultaneously. This script identifies errors in versions of activities that cause multiple versions to appear to be active at once.
Use the script as follows:
sqlplus <user/pwd> @wfverchkUse wfverupd.sql to correct problems arising in your workflow process that are due to multiple versions of an activity being active simultaneously. This script identifies and corrects errors in versions of activities that cause multiple versions to appear to be active at once.
Use the script as follows:
sqlplus <user/pwd> @wfverupd