BRF+ db lookup

[Former Member]

In order to facilitate workflow, we have to determine at initiation if a role has an assignment approver or not. There are 2 tables, GRACROLE and GRACROLEAPPRVR that give this info.  we have to get the ROLEID from  GRACROLE based on the ROLE_NAME (available in line_item) and then do an exists using that ROLEID from GRACROLEAPPRVR.  My question is, how does one go about this type of query in BRF+?  I found that in the decision table, you can insert an expression of any time, including DB Lookup.  If anyone has a clue on how to structure this type of lookup/rule, we would be eternally grateful. 

[Former Member · Accepted solution]

Hi Jack,

Role id is already available as standard attribute in BRF context. So you just have to check role approver table . Following blog will provide more details .

Best Regards,

Aman

http://scn.sap.com/community/grc/blog/2013/03/15/using-brf-db-lookup-to-create-complex-msmp-rules 

[Former Member · Accepted solution]

Ended up using both your suggestion and one from Collen Lee.  The blog is almost correct...using the built-in ROLE_GUID did not work, however by creating a second DB Lookup that gets the ROLEID and then uses that for input for the original DB Lookup, it works great.

[nguyen_huynh · Accepted solution]

Hello experts,

you may help me as I am quite desperate.

I have posted a blog ( http://scn.sap.com/thread/3600381 )but you may help me how I can setup the right codition as my condition does not look like you have entered. Please help me  ..

I have done follwoing settign but I don't get the same result as in your screenshot:

1. Create BRF+ routing rule via SPRO

2. Create DB_LOOKUP expression.

3. Enter in field "from" the value GRACROLE and select in "Into"  "Create result from table"...

Then click on "Add condition with".

4.

5. Now, I got this message:

No context assigned to DB Lookup 'Z_GET_ROLEID' yet. Query result includes all data objects !

6. This is my result

7. Your result!!!!

Do you have a hint for me? Thanks for any help.

Regards Nguyen

[Former Member]

This message was moderated.

[Colleen]

Hi Jack

Here's my attempt... hopefully the logic is sound

Created Objects

Data Element – Z_DB_BOOLEAN for BOOLEAN

1 Expression Table

3 DB Look ups

Expression Table

The expression table is created to return the values MSMP needs for RULE_RESULT

• ZDB_GET_ROLE_NAME – this is taking the DB Lookup 1 results
• LINE_ITEM_KEY – asterisk
• RULE_RESULT – these are the Initiator Path Routes to store in MSMP
  • ZPATH_TRUE – The role has at least one assignment approver
  • ZPATH_FALSE – The role has no assignment approvers

DB Look up 1

This is completing an existence check – TRUE OR FALSE – must be Boolean as the return result

• Table Source: GRACROLE
• ROLE_NAME is checked against the Role from the Context Parameter (GRAC_S_R…-ROLE_NAME). This is coming through from the workflow request/role name
• ROLEID is then checked in the table against Z_ROLEID (refer DB Lookup 2)
• Z_DB_BOOLEAN is a data element to return a value of true or false. This is used in the Decision table. Ultimately – there exists at least one role assignment approver then the return is TRUE.

DB Lookup 2

This is completing a single entry data retrieval (links back to is there an approver or not). It is called by the DB Lookup 1 Condition statement.

• Table Source: GRACROLEAPPRVR
• APPRVR – this is the field in the table to identify the user is an approver for role assignment or not
• ROLEID – now need to check if the Role Id is the value we are matching. Therefore, need to check if the Role Id is the correct Role Id for the Role Name (otherwise the first hit in the table will return and the result will always end up TRUE)…. Because we cannot use a join or Select In, we need to check again Z_ROLEID_DERIVE (hence DB Lookup 3)
• Return Result ROLEID – Choose Select to choose it. This will return the single value for ROLEID attribute from the table. The value is returned back to DB Lookup 2

DB Lookup 3

This is now checking back against the GRACROLE table to ensure the ROLE_NAME is correct. It is returning a single entry

• Table Source: GRACROLE
• ROLE_NAME is equal to ROLE_NAME is to ensure to get the Role Id to match
• Return result ROLEID again. It goes back to DB RULE 2 to finish evaluating it

It's a pity you can't do this against structure or joined tables, etc.

[Former Member]

The logic looks right! 

1. created a data object ('Z_ROLEOWNER_BOOLEAN') at the application level (Data Object->Element)
2. created the 3 db lookups...
3. When I attempted to activate the function, I get Z_REQ_INITIATOR (Function) :

Assigned expression uses Element 'Z_ROLEOWNER_BOOLEAN' which is not in the

context.

Everything else activated fine....

Context of the element is "Application"

function mode is "Functional Mode"

Any idea what I did wrong?

[Former Member]

Hi ,

Try adding 'Z_ROLEOWNER_BOOLEAN' to your Signature of yur function in which you Rule set is defined .

[former_member541582]

Hi Jack,

Ar you are looking for a solution to catch those cases when a role owner is not defined? Then there is a much easier way...

Say you have a two stage workflow with a line manager stage and a role owner stage.

Use the standard detour rule GRAC_MSMP_ROUTE_NO_ROLEOWNER at the first stage. In combination with routing level set to line item, it will push the part of the request without a role owner to a by you defined path.

Example: If you have a request with Role A, with a role owner. And a Role B, w/o role owner. Then Role A will go to the subsequent role owner stage in your main path while Role B will go to a stage in path of your choice.

Hope this helps,

Vit

[Former Member]

That only works if you have a stage before role owner stage, that is not what I'm after, I need to determine up-front role owner status in order to select the correct path.  We have roles that are auto-approved (not even managers)...that's the issue.  And no, the escape route of no approver found does not work in this case..workflow errors out.

[Colleen]

I'm unsure without seeing you BRF+ build

the data element for Boolean for me has the following properties

Application level - Application

Data Binding - No Binding

Element Type - Boolean

Allowed Comparison - Single Value

Element Attributes - "no attributes are configurable for this type)

Possibly on the DB Look up instead of using the Z* Data element you could just choose "BOOLEAN"

"Then Boolean is True, Otherwise it is false"

[santosh_krishnan2]

Hey Jack,

I'm aware of this config and I have it working.  Customer insists that they want only one stage - role owner - and if there is no role owner, then the role should auto approve.

This works for my two stage path, but with the one stage role owner path, it fails - and I don't really have a work around.

Any ideas, please?

Thank you.

Santosh