Exlcuding accounts in the Entitlement Owner Campaign

[AravindK]

Hi Team,

We have requirement in the Entitlment owner campaign to include only the accounts whose accountname is same as username.

Ex: User(71682906) have two accounts like below: 
       1)71682906   2)9D71640906.

While launching the campaign it should bring only  one account which account name is same as username i.e 71682906 and exclude 9D71640906.

In the Account_entitlments1 query in Advanced configurations , we have setup like below :

ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN')

 This is bringing both two accounts  but we need to bring only account which is accountname=username.

We have prepared a query to check in data analyzer and  it is fetching the data as expected :

select a.accountkey from accounts a ,account_entitlements1 ae1,users u where ae1.accountkey=a.accountkey and u.username=a.name and ae1.ENTITLEMENT_VALUEKEY=16539404  .

But in the campaign we tried like below but it is giving Incorrect SQL syntax error:
1)
ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN') and ae1.accountkey in (select a.accountkey from accounts a ,account_entitlements1 ae1,users u where ae1.accountkey=a.accountkey and u.username=a.name)
 2)
ae1.entitlement_valuekey='16539404' and ae1.accountkey in (select a.accountkey from accounts a ,account_entitlements1 ae1,users u where ae1.accountkey=a.accountkey and u.username=a.name)

Please help us on this use case.

[dgandhi]

Try below in Accounts query placeholder

name in (select a.name from accounts a,user_accounts ua,users u where a.accountkey=ua.accountkey and ua.userkey=u.userkey and a.name=u.username)

If you want to include active accounts then include below

name in (select a.name from accounts a,user_accounts ua,users u where a.accountkey=ua.accountkey and ua.userkey=u.userkey and a.name=u.username and a.status in (1,'Active','Manually Provisioned'))

[AravindK]

Hi @dgandhi ,

Thanks for the suggestion. 
Trying like below but it is not pulling the any accounts.

Accounts query :

name in (select a.name from accounts a,user_accounts ua,users u where a.accountkey=ua.accountkey and ua.userkey=u.userkey and a.name=u.username)

Account_Entitlement Query:
ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN')

[pmahalle]

Hi @AravindK ,

You want to include only accounts whose name is same as username and having ROLE_ADMIN entitlement?

[dgandhi]

Remove below

Account_Entitlement Query:
ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN')

and add in Entitlement query

entitlement_value IN ('ROLE_ADMIN')

Above accounts query worked perfectly in my case.

[AravindK]

Hi @pmahalle ,
Correct. Need to pull the only accounts whose account name same as username and having ROLE_ADMIN entitlement?

[pmahalle]

Hi @AravindK ,

Can you use below query under Accounts Query for you campaign and check

name in (select ac.name from users us, user_accounts ua, accounts ac, account_entitlements1 ae, entitlement_values ev where us.userkey=ua.userkey and
us.username=ac.name and ua.accountkey=ae.accountkey and ae.entitlement_valuekey = ev.entitlement_valuekey and ev.entitlement_value='ROLE_ADMIN')

[AravindK]

Hi @pmahalle I have tried the above query under accounts query but it is pulling all the entitlements of the Application which is selected in the campaign configuration. It should fetch one entitlement and associated accounts.

[pmahalle]

Hi @AravindK ,

Along  with above account query use below query in entitlement query and check 

entitlement_value in ('ROLE_ADMIN')