Rules for SQL statements for custom views of pending tasks

  • You must create a new symbol, ACTIONTASK, to enable UI management of Lookup/Views.
  • Do not use “V\” in the query name.
  • The query must be joined to the FSACTIONTASKVIEW1 view using the table name alias of “V”.
  • The query must have the [%%FILTERS] token as part of its WHERE clause. This token is replaced at run-time with the SQL criteria fields from the Optiva View Filter.
  • The query must have the ActionWIP_ID, LINE_ID, ACTION_EVENTS, ACTION_EVENTSCOMMENT, ACTION_EVENTSRSN, ACTION_EVENTSSGN, ACTION_EVENTSCNF, and SOURCE_USER_CODE columns as returned values from the query. These columns can be located anywhere in the SELECT statement. These columns can be hidden through the Column Widths entry when the width is 0 or blank.
  • To display the Wizard icons in the Events column, update this portion of the select statement for the ACTION_EVENTS: 
    SELECT V.ACTIONWIP_ID
, CASE WHEN V.WIZARD_IND = 1 THEN 512 ELSE V.ACTION_EVENTS END AS ACTION_EVENTS
, V.ACTIONWIP_ID, V.DESCRIPTION, etc.
  • To display the (optional) Visual Workflow icon and column, add the column STAGE_GATE_IND to the select statement, and Column Label (e.g. “Visual Workflow”) and Column Width to the Lookup Language tab of the Lookup Set.
  • For general information about configuring queries, see Configuring queries in the Query form.
Caution: 
Exercise caution when you write Pending Task queries for filters that use the Current User, Group, or Role selections. Suppose your query includes a join to a table that has the User_Code, Group_Code, or Role_Code columns. Even if those columns are not called in your custom query, the query may fail altogether; or give results that are not the tasks you would expect. For example, both the FsFormula and FsAction tables include a GROUP_CODE column; and are typical tables to include data from in a custom pending task query.