Query that reports user having specific account name along with their roles and entitlements

[sharu]

Hi Team,

Need a query that displays user's having account name starting with "A-"  along with their corresponding roles and entitlements pertaining to that account .

Thanks,

Sharanya V

[rushikeshvartak]

SELECT u.username          AS UserName,
       a.NAME              AS AccountName,
       r.role_name         AS RoleName,
       e.entitlement_value AS EntitlementName
FROM   users u
       JOIN user_accounts ua
         ON u.userkey = ua.userkey
       JOIN accounts a
         ON ua.accountkey = a.accountkey
       JOIN role_user_account rua
         ON ua.accountkey = rua.accountkey
       JOIN roles r
         ON rua.rolekey = r.rolekey
       JOIN account_entitlements1 ae
         ON a.accountkey = ae.accountkey
       JOIN entitlement_values e
         ON ae.entitlement_valuekey = e.entitlement_valuekey
WHERE  a.NAME LIKE 'A-%';