Certification Campaign - Certifier Manual Query

[JasBel]

Hi.  I'm trying to create a manual query for the Certifier on a Certification Campaign but I seem to have troubles with Saviynt to accept the query.

When I use the query in Query Analyzer this is working fine:
select m.username from users u inner join users m on u.manager=m.userkey inner join user_accounts ua on ua.userkey = u.userkey inner join accounts a on ua.accountkey=a.accountkey inner join account_entitlements1 ae on a.ACCOUNTKEY = ae.ACCOUNTKEY inner join entitlement_values ev on ae.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY where ev.ENTITLEMENT_VALUE = 'entitlement1'

But under the manual query I'm getting an Invalid SQL syntax error.  This is what I'm trying to use:
u.username in (select m.username from users u inner join users m on u.manager=m.userkey inner join user_accounts ua on ua.userkey = u.userkey inner join accounts a on ua.accountkey=a.accountkey inner join account_entitlements1 ae on a.ACCOUNTKEY = ae.ACCOUNTKEY inner join entitlement_values ev on ae.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY where ev.ENTITLEMENT_VALUE = 'entitlement1')

I want to only display managers that have users with the entitlement so all managers don't get a campaign if they don't have any employees listed with this entitlement.

Any suggestions please?

Thank you in advance.

[VenkataElluru]

Hi @JasBel 

From your question: I want to only display managers that have users with the entitlement so all managers don't get a campaign if they don't have any employees listed with this entitlement.

Try to use the below process to achieve the above case:

1.Select "Manager" under "Attribute To Identify Manager"

2.Select "Yes" under Ignore Accounts Without Entitlements

So that "campaign will skip the accounts that do not have entitlements from access verification step-2"

3.Select All Certifier under "Select Certifier"

4.Under Advanced Campaign Config, use the Entitlements query to filter the entitlement value

Thanks & Regards,
Elluru Venkata Peddi Raju,
If you find this response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

[JasBel]

Thank you @VenkataElluru  but when we put the 2.Select "Yes" under Ignore Accounts Without Entitlements this certification goes directly in Completed.  If we leave this as No then all the managers are displayed in Step1 but don't have anyone to certified at the exception of Managers having employees to certified.

I also tried the option Account Entitlements1 Query as: 
ae1.entitlement_valuekey.entitlement_value = 'entitlement1'

Any other suggestion?   Thank you for your help.

[JasBel]

Thanks we found a solution for this one still by using the manager certification.  We are running a query in the users query field for users having the entitlement value required and also under the Entitlements query.  This seem to fix the issue.

[rushikeshvartak]

Please share query and close thread by accepting answer

[JasBel · Accepted solution]

For the users query I used this: 

select u.username from users u inner join user_accounts ua on ua.userkey = u.userkey inner join accounts a on ua.accountkey=a.accountkey inner join account_entitlements1 ae on a.ACCOUNTKEY = ae.ACCOUNTKEY inner join entitlement_values ev on ae.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY where ev.ENTITLEMENT_VALUE = 'entitlement1'

And under Entitlements Query I used this:

ENTITLEMENT_VALUE = 'entitlement1'

This fixed this issue where all certifiers were appearing in the certification.