Hello,
I have written below sql query in entitlement rule to get missing app roles on request form. But I am getting duplicate entitlements from all business sources instead of one approle which is there in T_AV_ROLE_MISSINGENTS table.
select DISTINCT r.name from T_AV_ROLE_MISSINGENTS me,t_entitlement_groups r,PV_APPLICATION a
where r.id=me.ENT_ID and a.id=r.APPLICATION_ID and me.MEMBER_ID=43987;
Kindly suggest , How can I achieve this solution.
Not sure I understand the requirement or what you are trying to do but make sure you also include the ent_type in your filter. Otherwise you would be mixing IDs of different objects. Something like this:
select DISTINCT r.name from T_AV_ROLE_MISSINGENTS me,t_entitlement_groups r,PV_APPLICATION a
where r.id=me.ENT_ID and a.id=r.APPLICATION_ID and me.ENT_TYPE='app-role' and me.MEMBER_ID=43987;
Thanks for the response.
I am having same app role name for different applications and in this T_AV_ROLE_MISSINGENTS table only one application approle is there for that user. when I use the above query in SQL developer, I am getting one record but when use it in entitlement rule on the form, I am getting all the matching entitlements with name.
I want to get unique entitlements based on entitlement id .
Can't you select on the r.ID instead of r.name? Then change your Request form filter to check ID instead of name?
I can select the r.id but, on the request form I can find only below Where clauses
x."AppRole Type ID"
x."Group Type ID"
x."Entitlement Type ID"
Can we map the id of the entitlements to above values based on entitlement type?
I have tried below query in entitlement rule but no records displayed
x."AppRole Type ID" in (select DISTINCT r.id from T_AV_ROLE_MISSINGENTS me,t_entitlement_groups r,PV_APPLICATION a
where r.id=me.ENT_ID and a.id=r.APPLICATION_ID and me.MEMBER_ID=43987)
Or is there any other way to get entitlements based on Id?
Not sure if possible, but can you try something like:
x."Entitlement Type"='app-role'
and x.ENTITLEMENT_ID in (select DISTINCT r.id from T_AV_ROLE_MISSINGENTS me,t_entitlement_groups r,PV_APPLICATION a
where r.id=me.ENT_ID and a.id=r.APPLICATION_ID and me.MEMBER_ID=43987)
Thank you Mostafa !!
The above query is working fine. However when I use Union to display app roles and Entitlements in single entitlement table I am getting below warning message.
Can we use union and joins in entitlement rules ?
Thanks you Mostafa !!
OR operator is working fine. But it's taking long time to fetch the data.
I have created below SQL query in entitlement rule in 7.1 version by selecting "Allow table grouping" and "Hide table if empty" options and I am getting two errors when we ran the form in 7.1 and same functionality is working fine in 6.9.1.
Entitlement rule query :
(x."Entitlement Type"='app-role'
and (x.ENTITLEMENT_ID in (select DISTINCT ent_id from T_AV_ROLE_MISSINGENTS where ent_type='app-role' and MEMBER_ID='${avform.user.Id}')))
Error one when I select "Allow table grouping" :
Error two when I select "Hide table if empty" :
If I use simple condition in entitlement rule, we are not getting the above issues in 7.1 version.
Have you tried removing the extra brackets around your whole filter?
I am getting same errors even when I removed the extra brackets in the filter query.
Ignore my previous comment ... ERROR 500 means you will find the actual error in the aveksaServer.log
Here is the error snippet from server log:
Maybe remove the single quotes you have around the user.id variable. I think it may be causing the query to use it as a literal text instead of a variable?
I have already tried that and causing the below error :
I reproduced this in-lab. Basically that error shows up every time regardless it works or not. The problem happens when you have a combination of "Hide table if empty” and using '${avform.user.Id}' in your filter. Please open a support case for this.
The slowness though is really due to the custom filter … you would either need to adjust your logic of somehow find a more optimal query to give you the same requirements
Not sure if possible, but can you try something like:
x."Entitlement Type"='app-role'
and x.ENTITLEMENT_ID in (select DISTINCT r.id from T_AV_ROLE_MISSINGENTS me,t_entitlement_groups r,PV_APPLICATION a
where r.id=me.ENT_ID and a.id=r.APPLICATION_ID and me.MEMBER_ID=43987)