ERP Associates, Inc. - Using Trees to Route Workflow

Using Trees to Route Workflow

Written by Brent Martin

Monday, 07 May 2007

I’m currently working on two workflows. The first one is Journal line approval, and the other is AR Pending Item approval. Each approval needs to go to the submitter’s “supervisor” until it gets to someone who’s authorized to approve at that level.

I had planned to use the ROLEUSER_SUPR field on PS_ROLEXLATOPR. It’s an easy solution with a delivered role query called _ROLE__SUPERVISOR_BY_OPRID. Then I discovered that the receivables department has one person who approves all Journals, so a “supervisor” for GL workflow will be different than a “supervisor” for AR workflow. At that point I couldn’t use the same ROLEUSER_SUPR field for both anymore, and I needed a new solution.

The obvious choice would have been to use Route Control Profiles. And that’s not a bad solution. But at this client, they want a workflow administrator to be able to maintain this hierarchy, and the workflow administrator won’t have access to the “Roles” page in the User Profile component in order to assign route control profiles.

Then the GL functional guy came up with a good idea (yes they can be helpful on occasion). Why not build a tree to represent the approval hierarchy? I had to think for a minute, but isn’t that what trees are for? The operators would be stored in PSTREELEAF, and their supervisors would be stored in PSTREENODE. And since it’s simply a one-level approval, that’s all we needed.

In fact, the more I thought about route control profiles, the more I can see similarities between them and trees. Except route control profiles don’t have a nice graphical interface to make them intuitive.

Anyway, the GL functional guy set up the tree and I created a new role query like _ROLE__SUPERVISOR_BY_OPRID, except it used the tree tables instead.

Here’s what the tree looks like (approvers are nodes, submitters are leaves):

Tree to route workflow in Tree Manager

And here’s the SQL for my new role query:

SELECT A.ROLEUSER
FROM PS_ROLEXLATOPR A
WHERE EXISTS (SELECT 'X'
FROM PSTREELEAF B, PSTREENODE C
WHERE c.tree_node = a.roleuser and
       B.EFFDT =
       (SELECT MAX(B_ED.EFFDT) FROM PSTREELEAF B_ED
       WHERE B.SETID = B_ED.SETID
         AND B.SETCNTRLVALUE = B_ED.SETCNTRLVALUE
         AND B.TREE_NAME = B_ED.TREE_NAME
         AND B_ED.EFFDT <= SYSDATE)
    AND B.SETID = C.SETID
    AND B.SETCNTRLVALUE = C.SETCNTRLVALUE
    AND B.TREE_NAME = C.TREE_NAME
    AND B.TREE_NODE_NUM = C.TREE_NODE_NUM
    AND B.TREE_BRANCH = C.TREE_BRANCH
    AND C.EFFDT =
       (SELECT MAX(C_ED.EFFDT) FROM PSTREENODE C_ED
       WHERE C.SETID = C_ED.SETID
         AND C.SETCNTRLVALUE = C_ED.SETCNTRLVALUE
         AND C.TREE_NAME = C_ED.TREE_NAME
         AND C_ED.EFFDT <= B.EFFDT)
    AND B.SETID = 'SHARE'
    AND B.TREE_NAME = 'RULE_GL_WRKFLOW'
    AND B.RANGE_FROM = :1)

The one thing we had to consider is that approvers might enter their own journal lines, and they’re not supposed to approve their own work. So we had to set up approvers as both nodes and leaves, but that’s no big deal (See GLS1). We also liked the flexibility of being able to set up users under multiple approvers, just in case we need multiple approvers to handle the workload (see GLA3).

We considered making this approval tree a “Winter Tree” with no leaves – everyone would be a node on the tree. But since nodes must be unique, that would have meant approvers couldn’t also be submitters. It would have also meant we couldn’t assign users to more than one approver.

Has anybody else ever used trees to route workflow? How did it work out for you?

Comments (0)