c.CERTKEY AS 'CERTIFICATION_ID',
c.CERT_NAME AS 'CERTIFICATION NAME',
CASE
WHEN c.STATUS = 0 THEN 'New'
WHEN c.STATUS = 1 THEN 'In Progress'
WHEN c.STATUS = 2 THEN 'Ready to Submit'
WHEN c.STATUS = 3 THEN 'Submitted'
WHEN c.STATUS = 4 THEN 'Expired'
WHEN c.STATUS = 6 THEN 'Preview'
WHEN c.STATUS = 7 THEN 'Discontinued'
WHEN c.STATUS = 8 THEN 'Locked and Task Created'
WHEN c.STATUS = 9 THEN 'Cancelled'
WHEN c.STATUS = 10 THEN 'Fully Executed'
END 'CERTIFICATION STATUS',
'ROLE OWNER' AS 'CERTIFICATION TYPE',
DATE(c.startdate) AS 'CERTIFICATION LAUNCH DATE',
u.USERNAME AS 'CERTIFIER',
u.DISPLAYNAME AS 'CERTIFIERNAME',
u.EMAIL AS 'CERTIFIEREMAIL',
ar.ROLE_NAME AS 'ROLE NAME',
CASE
WHEN ar.roletype = 1 THEN 'Enabler'
WHEN ar.roletype = 2 THEN 'Transactional'
WHEN ar.roletype = 3 THEN 'Fire Figther'
WHEN ar.roletype = 4 THEN 'Enterprise'
WHEN ar.roletype = 5 THEN 'Application'
WHEN ar.roletype = 6 THEN 'Entitlement'
END 'ROLE TYPE',
CASE
WHEN ars.certified = 1 THEN 'Belongs To Me'
WHEN ars.certified = 3 THEN 'Does Not Belongs To Me'
ELSE 'No Response'
END 'CERTIFY ACTION',
DATE(ars.UPDATEDATE) AS 'CERTIFY DATE',
k.USERNAME AS 'USER ID',
CASE
WHEN aruas.certified = 1 THEN 'Yes'
WHEN aruas.certified = 2 THEN 'No'
ELSE 'No Response'
END 'ACCOUNT CERTIFIED',
CASE
WHEN
u.statuskey = 0
AND (ars.CERTIFIED IS NULL
OR ars.CERTIFIED NOT IN (1 , 3))
THEN
'Inactive/Missing Certifier'
WHEN
(u.email IS NULL
OR LTRIM(RTRIM(u.email)) = '')
AND (ars.CERTIFIED IS NULL
OR ars.CERTIFIED NOT IN (1 , 3))
THEN
'Pending with Certifier with no email address'
ELSE 'N/A'
END AS 'FAILURE REASON',
CASE
WHEN (c.status IN (3 , 8)) THEN DATE(c.updatedate)
END AS 'CERTIFICATION COMPLETE DATE',
(SELECT
GROUP_CONCAT(t.TASKKEY)
FROM
arstasks t
WHERE
t.accountkey = acc.accountkey
AND t.ENTITLEMENT_VALUEKEY = are.ENTITLEMENT_VALUEKEY
AND t.TASKTYPE = 2
#AND t.STATUS IN (1 , 2)
AND t.SOURCE = 'CERTIFICATION'
AND t.SOURCEID = c.CERTKEY) AS 'REVOKE TASK ID',
(SELECT
GROUP_CONCAT(t.STATUS)
FROM
arstasks t
WHERE
t.accountkey = acc.accountkey
AND t.ENTITLEMENT_VALUEKEY = are.ENTITLEMENT_VALUEKEY
AND t.TASKTYPE = 2
#AND t.STATUS IN (1 , 2)
AND t.SOURCE = 'CERTIFICATION'
AND t.SOURCEID = c.CERTKEY) AS 'TASK_STATUS'
FROM
Certification c,
users u,
users k,
certification_role ar,
cert_roles_status ars,
cert_role_entitlements are,
cert_role_entitlements_status ares,
entitlement_values ev,
entitlement_types et,
certification_role_user_account_status aruas,
cert_role_user_account arua,
accounts acc,
securitysystems ss
WHERE
c.type = 5 AND c.CERTKEY = ars.CERTKEY
AND ars.CERT_ROLEKEY = ar.CERT_ROLEKEY
AND c.certifier = u.userkey
AND ev.ENTITLEMENT_VALUEKEY = are.ENTITLEMENT_VALUEKEY
AND aruas.CERTKEY = c.CERTKEY
AND aruas.CERT_role_user_account = arua.CERT_role_user_accountkey
AND arua.CERT_ROLEKEY = ars.CERT_ROLEKEY
AND arua.accountkey = acc.accountkey
AND arua.userkey = k.userkey
AND ars.CERT_ROLEKEY = are.CERT_ROLEKEY
AND are.CERT_ROLE_ENTITLEMENTKEY = ares.CERT_ROLE_ENTITLEMENTKEY
AND et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
AND ares.CERTKEY = ars.CERTKEY
AND acc.systemid = ss.SYSTEMKEY
AND et.systemkey = ss.SYSTEMKEY
AND c.CAMPAIGNKEY=${campaignkey};
