Skip to Content
0

BRF plus - Multiple result value from a db lookup

Sep 21, 2017 at 09:36 AM

302

avatar image
Former Member

Hello GRC experts,

I have a query regarding DB lookup in BRF+. An agents rule have been created. I have a Z table which includes departments and their respective HoDs. There can be more then one HoD for a department.

So, using DB lookup, I want to fetch all the HoDs associated to a particular department and the result value(s) would be the approver agent(s) for one of the stages in MSMP.

Using multiple DB look up, it is possible to fetch single value of HoD for a department input value. But I am not able to fetch all values for HoDs of a department entry in Z table.

Your valuable suggestion are highly appreciated.

Thank you - Naveen

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Christian Lechner
Sep 26, 2017 at 10:10 AM
0

Hi,

with the DB Lookup expression, you are able to fetch several values in one call. Selection mode is "data retrieval" and the select has to be specified as "All Entries". See also screenshot below

Best regards,

Christian


screenshot.png (7.6 kB)
Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Christian,

Thank you. I have already maintained similar setup in my DB lookup to fetch multiple entries.

I have created 3 DB lookups,

1) to get request number from request ID

2) to get department from request number

3) to get HoD from z table for the corresponding department fetched in DB lookup 2

But when I am trying to add the same DB lookup as expression in decision table, I cannot find it in available options under expressions for field 'User ID'.

Could you please advise further.

Thank you.

0

Hi

the issue is the mechanics of the decision table cells: Each cell is evaluated to check if the condition specified in the cell is met. In your case your DB Lookup would return a table of values and the decision table is not able to deal with that when evaluating the cell.

Your scenario is certainly better covered by the approach Mike is proposing in the second answer to this question.

Best regards

Christian

0
Mike Pokraka Sep 26, 2017 at 07:07 PM
0

Why not maintain it all in a decision table? No lookups needed.

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Mike,

Thank you. But adding entries directly in decision table will be a complex task, since there are more then 100 entries available for dept-hod combination. Also, going further, the maintenance of the decision table on each new entry added for HoD will be a tedious work.

Otherwise, just creating the DB lookups once with required logic is easy. In that case, we only need to update Z table, as and when we get new entries for HoD.

It would be helpful, if you can further share your views on this.

Thank you.

0

You can up- and download DTs to and from Excel, very easy to maintain, makes it feasible to email out to users.

You can group or list criteria in a single DT condition, directly in Excel.

DEPT        HOD
[100..199] JONES

BRF+ also provides automatic functions to compact matching entries into one.

It's so much easier...

0
Alessandro Banzer
Oct 27, 2017 at 09:28 PM
0

Naveen,

I came across similar requirements than the one you are describing. The "ugly" part about maintaining approver tables in BRF+ (e.g. in a decision table) is that you have to transport the change each time an approver changes.

It is definitely achievable with BRF+, but easier with a function module. Have a look at the default agent rule that is provided by SAP. It's a couple of lines that need to be adjusted and you are ready to go. You can start from SPRO > GRC > Access Control > Workflow for Access Control > Define Workflow-Related MSMP Rules. Instead of choosing BRFPlus rule pick "Function Module Based Rule". The generated rule will come with most of the coding required. Implement your own rule processing logic to read the Head of Department and you are ready to go.

If you need some assistance with the function module, let me know. There are also some blogs out there that show similar scenarios.

Cheers, Alessandro

Show 3 Share
10 |10000 characters needed characters left characters exceeded

"The "ugly" part about maintaining approver tables in BRF+ (e.g. in a decision table) is that you have to transport the change each time an approver changes."

Not at all, that's what the "Master Data" storage type is for. It's a little bit clunky to split the layers into different storage types, but once you get the hang of it it's no biggie.

Plus you get the bonus of being able to test in production before activation. If you transport and get it wrong you're stuffed until you can get another transport in.

0

Mike,

I agree on that. But many customers try to gather approvers from decision tables which then needs to be transported each time an approver changes. In large organizations that can happen daily. Therefore, I always suggest to define such approver tables more dynamically, e.g. in a table that can be maintained in SM30, or by using standard functionality provided by SAP Access Control like Role Approver field, etc.

Regards, Alessandro

0

You've either missed the point, misunderstood, or have a gap in your understanding of BRFplus features.

If you use the 'Master Data' storage type then no transport is needed. It can be maintained in production, using Excel if you wish. Authorisations can be applied. If done properly, it is safer than a transport as it can be tested in production before it is activated. You can use the full functionality of BRFplus expressions inside your table, a feature which IMHO on it's own already provides a huge advantage over two-dimensional SM30 tables. Shall I go on? :-)

As an aside, I think the whole idea of maintaining agents in any form of table is an inferior solution to start with. Going via HR OM you can maintain agents and approvers for all scenarios in one go.

1