Solution
Please configure the below report for entitlement owner campaign by editing the campaign.id attribute:
SELECT DISTINCT
c.CERT_NAME AS 'Certification Name',
ca.campaign_name AS 'Campaign Name',
users.username AS Certifier,
users.Firstname AS Certifier_FName,
users.Lastname AS Certifier_LName,
users.email AS Certifier_Email,
users.DEPARTMENTNAME AS Certifier_Departmentname,
users.COUNTRY AS Certifier_Country,
users.Siteid AS Certifier_Sitecode,
u2.username AS User,
u2.Firstname AS User_Firstname,
u2.lastname AS User_Lastname,
u2.DEPARTMENTNAME AS User_Departmentname,
u2.COUNTRY AS User_Country,
u2.Siteid AS User_Sitecode,
CASE u2.STATUSKEY
WHEN '1' THEN 'ACTIVE'
WHEN '0' THEN 'INACTIVE'
END AS USER_STATUS,
c.PROGRESS AS 'Percentage',
CASE
WHEN c.STATUS = 0 THEN 'New'
WHEN c.STATUS = 1 THEN 'In Progress'
WHEN c.STATUS = 2 THEN 'Completed'
WHEN c.STATUS = 3 THEN 'Locked'
WHEN c.STATUS = 4 THEN 'Expired'
WHEN c.STATUS = 7 THEN 'Discontinued'
WHEN c.STATUS = 8 THEN 'Locked and Task Created'
WHEN c.STATUS = 10 THEN 'FULLY EXECUTED'
ELSE c.STATUS
END AS 'Status of Certification',
c.CREATEDATE AS 'Date_Created',
c.ENDDATE AS 'Due_Date_of_Review ',
caa.name AS AccountName,
CASE caa.status
WHEN '1' THEN 'Active'
WHEN '2' THEN 'Inactive'
WHEN 'Manually Provisioned' THEN 'Manually Provisioned'
WHEN 'Manually Suspended' THEN 'Manually Suspended'
WHEN 'SUSPENDED FROM IMPORT SERVICE' THEN 'SUSPENDED FROM IMPORT SERVICE'
WHEN 'Active' THEN 'Active'
WHEN 'Inactive' THEN 'Inactive'
ELSE caa.STATUS
END AS 'ACCOUNT_STATUS',
endpoints.displayname AS Application,
cev.Entitlement_Value,
et.ENTITLEMENTNAME,
cev.CUSTOMPROPERTY1,
cev.CUSTOMPROPERTY2,
cev.CUSTOMPROPERTY3,
cev.CUSTOMPROPERTY4,
cev.CUSTOMPROPERTY5,
cev.CUSTOMPROPERTY6,
cev.CUSTOMPROPERTY7,
cev.CUSTOMPROPERTY8,
cev.CUSTOMPROPERTY9,
cev.CUSTOMPROPERTY10,
cev.CUSTOMPROPERTY11,
cev.CUSTOMPROPERTY12,
cev.CUSTOMPROPERTY13,
cev.CUSTOMPROPERTY14,
cev.CUSTOMPROPERTY15,
cev.CUSTOMPROPERTY16,
cev.CUSTOMPROPERTY17,
cev.CUSTOMPROPERTY18,
cev.CUSTOMPROPERTY19,
cev.CUSTOMPROPERTY20,
cev.CUSTOMPROPERTY21,
CASE
WHEN cs.CERTIFIED = 0 THEN 'REVOKED'
WHEN cs.CERTIFIED = 1 THEN 'CERTIFIED'
WHEN cs.CERTIFIED = 2 THEN 'CERTIFIED'
WHEN cs.CERTIFIED = 3 THEN 'REVOKED'
WHEN cs.CERTIFIED = 5 THEN 'CONDITIONALLY CERTIFIED'
WHEN cs.CERTIFIED = - 1 THEN 'TERMINATED'
ELSE 'NO RESPONSE'
END AS FinalAction,
'' as 'USER_Audit_Log',
'' AS 'ACCOUNT_AUDIT_LOG',
cs.AUDIT_TRAIL AS 'ACCOUNT_ENTITLEMENT_AUDIT_LOG',
'' as 'ENTITLEMENT_AUDIT_LOG',
'' AS USER_Comments,
'' AS ACCOUNT_Comments,
cs.Comments AS ACCOUNT_ENTITLEMENT_Comments,
'' AS ENTITLEMENT_Comments
FROM
certification c
INNER JOIN campaign ca ON c.CAMPAIGNKEY = ca.id
INNER JOIN certification_account_entitlement1_status cs ON c.certkey = cs.certkey
INNER JOIN certification_account caa ON caa.cert_accountkey = cs.cert_accountkey
INNER JOIN certification_entitlement_value cev ON cev.cert_entitlement_valuekey = cs.cert_entitlement_valuekey
INNER JOIN users ON users.userkey = c.certifier
INNER JOIN user_accounts ua ON ua.accountkey = caa.accountkey
INNER JOIN users u2 ON u2.userkey = ua.userkey
INNER JOIN endpoints ON endpoints.ENDPOINTKEY = caa.endpointkey
INNER JOIN entitlement_types et ON cev.ENTITLEMENTTYPEKEY= et.ENTITLEMENTTYPEKEY
WHERE
ca.id IN (423)
union all SELECT DISTINCT
c.CERT_NAME AS 'Certification Name',
ca.campaign_name AS 'Campaign Name',
users.username AS Certifier,
users.Firstname AS Certifier_FName,
users.Lastname AS Certifier_LName,
users.email AS Certifier_Email,
users.DEPARTMENTNAME AS Certifier_Departmentname,
users.COUNTRY AS Certifier_Country,
users.Siteid AS Certifier_Sitecode,
'' AS User,
'' AS User_Firstname,
'' AS User_Lastname,
'' AS User_Departmentname,
'' AS User_Country,
'' AS User_Sitecode,
'' USER_STATUS,
c.PROGRESS AS 'Percentage',
CASE
WHEN c.STATUS = 0 THEN 'New'
WHEN c.STATUS = 1 THEN 'In Progress'
WHEN c.STATUS = 2 THEN 'Completed'
WHEN c.STATUS = 3 THEN 'Locked'
WHEN c.STATUS = 4 THEN 'Expired'
WHEN c.STATUS = 7 THEN 'Discontinued'
WHEN c.STATUS = 8 THEN 'Locked and Task Created'
WHEN c.STATUS = 10 THEN 'FULLY EXECUTED'
ELSE c.STATUS
END AS 'Status of Certification',
c.CREATEDATE AS 'Date_Created',
c.ENDDATE AS 'Due_Date_of_Review ',
'' AS AccountName,
'' as 'ACCOUNT_STATUS',
endpoints.displayname AS Application,
cev.ENTITLEMENT_VALUE,
entitlement_types.ENTITLEMENTNAME,
cev.CUSTOMPROPERTY1,
cev.CUSTOMPROPERTY2,
cev.CUSTOMPROPERTY3,
cev.CUSTOMPROPERTY4,
cev.CUSTOMPROPERTY5,
cev.CUSTOMPROPERTY6,
cev.CUSTOMPROPERTY7,
cev.CUSTOMPROPERTY8,
cev.CUSTOMPROPERTY9,
cev.CUSTOMPROPERTY10,
cev.CUSTOMPROPERTY11,
cev.CUSTOMPROPERTY12,
cev.CUSTOMPROPERTY13,
cev.CUSTOMPROPERTY14,
cev.CUSTOMPROPERTY15,
cev.CUSTOMPROPERTY16,
cev.CUSTOMPROPERTY17,
cev.CUSTOMPROPERTY18,
cev.CUSTOMPROPERTY19,
cev.CUSTOMPROPERTY20,
cev.CUSTOMPROPERTY21,
CASE
WHEN cvv.CERTIFIED = 1 THEN 'CERTIFIED'
WHEN cvv.CERTIFIED is null then 'NO RESPONSE'
ELSE 'DOES NOT BELONG TO ME'
END AS FinalAction,
'' as 'USER_Audit_Log',
'' AS 'ACCOUNT_AUDIT_LOG',
'' AS 'ACCOUNT_ENTITLEMENT_AUDIT_LOG',
cvv.AUDIT_TRAIL as 'ENTITLEMENT_AUDIT_LOG',
'' AS USER_Comments,
'' AS ACCOUNT_Comments,
'' AS ACCOUNT_ENTITLEMENT_Comments,
cvv.Comments as ENTITLEMENT_Comments
FROM
certification c
INNER JOIN campaign ca ON c.CAMPAIGNKEY = ca.id
INNER JOIN users on users.userkey= c.CERTIFIER
INNER JOIN cert_ent_values_status cvv on cvv.CERTKEY= c.CERTKEY
INNER JOIN certification_entitlement_value cev on cvv.CERT_ENTITLEMENT_VALUEKEY= cev.CERT_ENTITLEMENT_VALUEKEY
INNER JOIN entitlement_types ON cev.ENTITLEMENTTYPEKEY= entitlement_types.ENTITLEMENTTYPEKEY
INNER JOIN endpoints ON endpoints.ENDPOINTKEY = entitlement_types.endpointkey
WHERE
cvv.CERT_ENTITLEMENT_VALUEKEY= cev.CERT_ENTITLEMENT_VALUEKEY and
ca.id IN (<<campaign.id>>);
References
