ALTER PROCEDURE dbo.CM_REQUEST_TEMPLATE_App
@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),
			@AUTO_APP BIT = 0,
			@DONE VARCHAR(1) = 'U'
	--SET CURRENT APPROVE
	UPDATE CM_APPROVE_GROUP
	SET 
	DONE = 1,
	PROCESS_STATUS = 0,
	CHECKER_ID = @P_CHECKER_ID,
	NOTES = @P_NOTE,
	APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
	WHERE APPROVE_GROUP_ID=@l_APPROVE_GROUP_ID
	IF @@Error <> 0 GOTO ABORT

	--CHECK CURRENT APPROVE
	IF(NOT EXISTS(SELECT * FROM CM_APPROVE_GROUP WHERE STEP_LEVEL = @l_STEP_LEVEL 
													AND DONE = 0
													AND REQ_ID = @P_REQUEST_TEMPLATE_ID
													AND TYPE = 'APP'))
	BEGIN
		IF(EXISTS(SELECT 1 FROM CM_REQUEST_TEMPLATE A
							LEFT JOIN dbo.TL_USER B ON B.TLNANME = A.MAKER_ID
							LEFT JOIN dbo.CM_DEPARTMENT C ON C.DEP_ID = B.DEP_ID
							WHERE A.REQUEST_TEMPLATE_ID = @P_REQUEST_TEMPLATE_ID
							AND	@l_STEP_LEVEL = 3 -- Khi hoàn tất nhóm 3
							AND B.BRANCH_TYPE = 'HS'
							AND C.DEP_CODE = '79900001'))-- 7990000:Trung tâm QL&THN -- 79900001:Phòng Quản lý nợ
		BEGIN	
			SET @l_STEP_LEVEL = @l_STEP_LEVEL + 1
			-- Tự động duyệt nhóm 4
			UPDATE CM_APPROVE_GROUP
			SET 
			DONE = 1,
			PROCESS_STATUS = 0,
			-- CHECKER_ID = @P_CHECKER_ID,
			NOTES = N'Hệ thống tự động duyệt',
			APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
			WHERE REQ_ID = @P_REQUEST_TEMPLATE_ID
			AND STEP_LEVEL = @l_STEP_LEVEL
			AND TYPE = 'APP'

			SET @AUTO_APP = 1
		END

		--SET UP NEXT APPROVE
		UPDATE CM_APPROVE_GROUP
		SET
		PROCESS_STATUS = 1,
		NEED_SEND_EMAIL = 1
		WHERE 
		REQ_ID = @P_REQUEST_TEMPLATE_ID
		AND DONE = 0
		AND STEP_LEVEL = @l_STEP_LEVEL + 1

		--SET CURRENT_STEP OF CM_REQUEST_TEMPLATE
		UPDATE CM_REQUEST_TEMPLATE
		SET CURRENT_STEP = @l_STEP_LEVEL + 1
		WHERE REQUEST_TEMPLATE_ID = @P_REQUEST_TEMPLATE_ID
	END
	--SET UP AUTH_STATUS
	IF(NOT EXISTS(SELECT * FROM CM_APPROVE_GROUP WHERE REQ_ID = @P_REQUEST_TEMPLATE_ID 
													AND DONE <> 1 
													AND TYPE = 'APP'))
	BEGIN
		UPDATE CM_REQUEST_TEMPLATE SET AUTH_STATUS = 'O' WHERE REQUEST_TEMPLATE_ID = @P_REQUEST_TEMPLATE_ID
		SET @DONE = 'A'
	END
	--ADD LOG
	DECLARE @DONE_INS_APPROVE_GROUP BIT
	EXEC CM_TEMPLATE_LOG_Ins @P_REQUEST_TEMPLATE_ID, 'APP', @P_NOTE, @P_CHECKER_ID, @l_APPROVE_GROUP_ID, @DONE_INS_APPROVE_GROUP OUT
	
	IF(@AUTO_APP = 1)
	BEGIN
		EXEC CM_TEMPLATE_LOG_Ins @P_REQUEST_TEMPLATE_ID, 'APP_AUTO', N'Nhóm 3 đã được duyệt hoàn tất, cập nhật duyệt nhóm 4', NULL, NULL, @DONE_INS_APPROVE_GROUP OUT
	END
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc, @DONE AS Attr1
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc, '' AS Attr1
		RETURN '-1'
End