ALTER PROCEDURE dbo.CM_REQUEST_TEMPLATE_Reject
@P_REQUEST_TEMPLATE_ID VARCHAR(100),
@P_AUTH_STATUS VARCHAR(1),
@P_CHECKER_ID VARCHAR(12),
@P_APPROVE_DT VARCHAR(20) = NULL,
@P_NOTE NVARCHAR(500)=NULL

AS
BEGIN TRANSACTION
DECLARE @l_APPROVE_GROUP_ID VARCHAR(50)=(
						SELECT APPROVE_GROUP_ID FROM CM_APPROVE_GROUP WHERE REQ_ID=@P_REQUEST_TEMPLATE_ID
						AND (APPROVE_USERNAME=@P_CHECKER_ID OR AUTHORITY_NAME=@P_CHECKER_ID)
						AND PROCESS_STATUS=1)
DECLARE @l_STEP_LEVEL INT =(SELECT STEP_LEVEL FROM CM_APPROVE_GROUP WHERE APPROVE_GROUP_ID=@l_APPROVE_GROUP_ID)
	--SET CURRENT reject
	UPDATE CM_APPROVE_GROUP
	SET 
	DONE=0
	,PROCESS_STATUS=0
	,CHECKER_ID=@P_CHECKER_ID
	,NOTES=@P_NOTE
	,IS_REJECT=1
	,APPROVE_DT= CONVERT(DATETIME,@P_APPROVE_DT,103)
	WHERE APPROVE_GROUP_ID=@l_APPROVE_GROUP_ID
	IF @@Error <> 0 GOTO ABORT
	UPDATE CM_APPROVE_GROUP SET PROCESS_STATUS=0 WHERE REQ_ID=@P_REQUEST_TEMPLATE_ID AND STEP_LEVEL= @l_STEP_LEVEL
	UPDATE CM_REQUEST_TEMPLATE SET AUTH_STATUS ='R',IS_SENT_APPROVE=1 WHERE REQUEST_TEMPLATE_ID=@P_REQUEST_TEMPLATE_ID
	IF @@Error <> 0 GOTO ABORT
	--ADD LOG
	declare @DONE_INS_APPROVE_GROUP bit
	EXEC CM_TEMPLATE_LOG_Ins @P_REQUEST_TEMPLATE_ID,N'REJ',@P_NOTE,@P_CHECKER_ID,@l_APPROVE_GROUP_ID, @DONE_INS_APPROVE_GROUP OUT
	IF @DONE_INS_APPROVE_GROUP <> 1 GOTO ABORT
	--

COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End
GO