Case Study: The Manager Query Role

When an employee requests an external course, we route the request to the employee’s manager. Because we route requests to different people, depending on who submits the request, we must define the manager role as a query.

A role query returns one or more role-user IDs based on the data that is saved on the page triggering the routing. In our case, we need a query that takes an employee’s EMPLID (which is available on the course request form) and returns the role user ID of the employee’s manager.

This is the SQL statement for the [ROLE] Manager query (with the effective date logic removed for simplicity):

SELECT C.ROLEUSER

 FROM PS_JOB A, PS_DEPT_TBL B, PS_ROLEXLATOPR C

 WHERE B.DEPTID = A.DEPTID

 AND A.EMPLID = :1

 AND C.EMPLID = B.MANAGER_ID

The Query Selects Role User IDs

The system uses role queries to determine which role users receive the work items. Therefore, the role query should select only ROLEUSER, from either the ROLEXLATOPR or ROLEUSER record definition. No matter how complex the query is—how many joins or selection criteria it has—it must return ROLEUSER only.

Bind Variables Correspond to the Contextual Factors

You define a role as a query because you want to route items differently based on the context of the transaction that the user is performing. So every role query contains at least one bind variable with a value that is set at runtime. The bind variables correspond to the data on which you want to base the routing decision. In our example query, we want to route requests differently based on the EMPLID of the requester, so we should include a bind variable to set the EMPLID.

Note:

At runtime, the system sets the values of the bind variables based on data from the page that triggers the routing. When you create the query, make sure that each bind variable matches a field from the page.