How to identify self-certifiers in an Entitlement Owner campaign

[saikanumuri]

Use Case

In an Entitlement Owner campaign, how do I identify the entitlement owners who are also self-certifiers?

Who is a Self-Certifier?

If an entitlement owner of an entitlement (E1) has E1 himself/herself, which implies that the certifier qualifies to be a user in the list of users who have E1, then the certifier is considered as a self-certifier. 

Pre-requisites

NA

Applicable Version(s)

NA

Solution

Please configure the report given below to find all the self-certifiers in a particular campaign.

* Replace the Campaign Name as per your requirement.*

SELECT

    t.*, ENTITLEMENTNAME, ENDPOINTNAME

FROM

    entitlement_values,

    entitlement_types,

    endpoints,

    (SELECT

        campaign.CAMPAIGN_NAME,

            certification.CERT_NAME,

            u1.DISPLAYNAME AS CERTIFIER,

            u2.DISPLAYNAME AS USER,

            entitlement_values.ENTITLEMENT_VALUE,

            entitlement_values.ENTITLEMENT_VALUEKEY,

            u3.USERNAME AS MANAGERS_USERNAME,

            u3.EMAIL AS MANAGERS_EMAIL

    FROM

        campaign, certification, certification_account_entitlement1_status, certification_account, certification_entitlement_value, entitlement_values, users u1, users u2, user_accounts, users u3

    WHERE

        campaign.id = certification.CAMPAIGNKEY

            AND certification.CERTKEY = certification_account_entitlement1_status.CERTKEY

            AND certification_account_entitlement1_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY

            AND certification_account_entitlement1_status.CERT_ENTITLEMENT_VALUEKEY = certification_entitlement_value.CERT_ENTITLEMENT_VALUEKEY

            AND certification_entitlement_value.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY

            AND u1.userkey = certification.CERTIFIER

            AND certification_account.ACCOUNTKEY = user_accounts.ACCOUNTKEY

            AND user_accounts.userkey = u2.userkey

            AND campaign.campaign_name in ('FY20 Year-End Privileged User Access Review by BPO Group B','FY20 Year-End Privileged User Access Review by BPO Group A','FY20 Year-End Privileged User Access Review by BPO Group C')

            AND u2.userkey = u1.userkey

            AND u3.userkey = u2.manager) t

WHERE

    t.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY

        AND entitlement_values.entitlementtypekey = entitlement_types.entitlementtypekey

        AND entitlement_types.endpointkey = endpoints.endpointkey;