Automatic Delegation for Certification using Core Compliance Portal

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

Solution Components
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.

Database Schema

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