Use Case
Locking a campaign after access-review completion is essential for revocation tasks to be created as part of the review.
However, there are certain scenarios in which managers may have completed their certifications and their progress is at 100% but they not have locked their certifications. This results in system admins having to manually check the certification and follow up with certifiers. In order to avoid this, the below analytic report can be configured to get a comprehensive overview of those campaigns and certifications that have a 100% progress but have not been locked.
Pre-requisites
Campaign management and review
Applicable Version(s)
3.x and EIC
Solution
Please configure an analytical report with the query given below. The report gives you a list of all certifiers who have a progress of 100% but have not locked their campaigns. Note - please replace the campaign.id with the corresponding values from your campaign.
SELECT DISTINCT
campaign_name as 'Campaign Name',
certification.CERT_NAME as 'Certification Name',
concat(users.firstname, ' ', users.lastname) as 'Certifier Name',
users.email AS 'Certifier Email',
certification.progress as 'Certification Progress',
certification.STATUS as 'Certification Status'
FROM
certification,
campaign,
users
WHERE
certification.CAMPAIGNKEY = campaign.id
AND certification.certifier = users.userkey
AND certification.progress = 100
AND certification.status NOT IN (3 , 8, 10)
AND campaign.id IN ('405' , '415', '421')
Refer below table for various Certification Status :
| Status | Details |
0
| Locked |
| 1 | Locked and Task Created |
| 2 | Fully Executed |
| 3 | Locked |
| 4 | Expired |
| 7 | Discontinued |
| 8 | Locked and Task Created |
| 10 | Fully Executed |
