×

IDMWORKS Blog

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

 

Questions, comments or concerns? Feel free to reach out to us below, or email us at IDMWORKS to learn more about how you can protect your organization and customers.

Leave a Reply

Your email address will not be published. Required fields are marked *