This is a custom solution that will auto-delegate a review once a Certification round is activated using the Core Compliance product. Reviewers do not need to manually login to the Certification portal each time a review cycle is initiated in order to assign a delegate for their review.
This solution caters to executive management who have a designated delegate who performs some of their routine tasks. Delegating helps the whole review process move along quickly without delays.
For compliance audit, there should be documentation and sign-offs of all the approved delegates for Managers or Reviewers who will be profiled for auto-delegation
Supported AAS version
This solution is tested and adapted for AAS 8.4
A reference table (Reference_Table_Certification_AutoDelegation)
Holding the names and employeeID of Reviewer and assigned delegate (Table will be manually managed by Compliance Administrator)
A custom stored procedure (Certification_AutoDelegation)
Will perform a lookup of the reference table above in order to create the auto-delegation setting
Standard stored procedure (sp_ActivateReviewCycle)
Will be modified to process the auto-delegation task by executing the stored procedure “Certification_AutoDelegation” just before the “Commit Transaction” statement.
A custom function (GetProfileUIDByEmployeeNo)
Function retrieves the ProfileUID of both delegate and delegator using their employeeID.
CREATE TABLE [dbo].[Reference_Table_Certification_AutoDelegation]( [ReferenceTableId] [bigint] IDENTITY(1,1) NOT NULL, [CertificationTypeID] [int] NOT NULL, [DelegatorEmployeeNumber] [varchar](50) NOT NULL, [DelegateEmployeeNumber] [varchar](50) NOT NULL, [Description] [varchar](255) NOT NULL, [Active] [bit] NOT NULL, [StartDateTime] [datetime] NOT NULL, [EndDateTime] [datetime] NOT NULL, [_changed_on] [datetime] NOT NULL, [_changed_by] [nvarchar](150) NOT NULL, CONSTRAINT [PK_Reference_Table_Certification_AutoDelegation] PRIMARY KEY CLUSTERED ( [ReferenceTableId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE FUNCTION [dbo].[GetProfileUIDByEmployeeNo] ( @VariableInput nvarchar(200) ) RETURNS nvarchar(50) AS BEGIN RETURN (SELECT Top 1 ProfileUID FROM dbo.[Profile] WHERE EmployeeNo=@VariableInput) END CREATE PROCEDURE [dbo].[Certification_AutoDelegation] @CertificationReviewCycleID int AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON DECLARE @localMsg NVARCHAR(2000); DECLARE @today DATETIME = GETUTCDATE(); DECLARE @ProcessName NVARCHAR(256) = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID); DECLARE @ErrorMessage NVARCHAR(2000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; DECLARE @TaskAction NVARCHAR(50); DECLARE @CRCDelegationCount INT DECLARE @ItemCounter INT BEGIN TRY BEGIN TRAN DECLARE @delegations dbo.CertificationDelegationTbl INSERT INTO @delegations( DateOfAction ,ReviewCycleID ,ReviewCycleItemID ,UserPerformingAction ,UserReceivingAction ,TaskOwner ,[Action] ,DelegationStartDate ,DelegationEndDate ,_changed_by ) SELECT GETUTCDATE() ,@CertificationReviewCycleID ,NULL ,dbo.GetProfileUIDByEmployeeNo(R.DelegatorEmployeeNumber) ,dbo.GetProfileUIDByEmployeeNo(R.DelegateEmployeeNumber) ,TL.[Task Owner] ,'1' ,R.StartDateTime ,R.EndDateTime ,'Auto' from [dbo].[Reference_Table_Certification_AutoDelegation] R INNER JOIN CertificationReviewCycles C on R.CertificationTypeID = C.CertificationTypeID INNER JOIN CertificationTaskList TL on TL.CertificationReviewCycleID = C.CertificationReviewCycleID WHERE GETUTCDATE() >=R.StartDateTime AND GETUTCDATE() < R.EndDateTime AND C.CertificationReviewCycleID = @CertificationReviewCycleID AND TL.[Task Owner] = dbo.pse_GetProfileUIDByEmployeeNo(R.DelegatorEmployeeNumber) AND R.Active=1 EXEC dbo.SetCertificationReviewCycleDelegation @CRCDelegation=@delegations COMMIT END TRY BEGIN CATCH SET @localMsg = ' Error occurred at line number: '+ CAST(ERROR_LINE() as nvarchar(10)) + ' while executing '+@ProcessName+': '+ ERROR_MESSAGE() BEGIN TRY IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRANSACTION; END; IF (XACT_STATE()) = 1 BEGIN COMMIT TRANSACTION; END; END TRY BEGIN CATCH END CATCH SELECT @ErrorMessage = @localMsg, @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END