INSERT INTO SYS_PARAMETERS (ParaKey, ParaValue, DataType, Description, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT) VALUES
('AUTO_APPROVE', N'0', 'Chr', N'Tự động duyệt phòng Quản lý nợ (0: tắt, 1: bật)', '1', 'admin', CONVERT(DATETIME, '2023-05-26 16:51:15.000', 121), 'U', 'admin', CONVERT(DATETIME, '2023-05-26 16:51:15.000', 121))
GO

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ợ
        AND (SELECT ParaValue FROM SYS_PARAMETERS WHERE ParaKey = 'AUTO_APPROVE') = '1') -- 26-05-23 Nếu là 1 sẽ bật tự động duyệt 
		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

GO

ALTER PROCEDURE dbo.CM_REQUEST_TEMPLATE_Ins
	@p_REQUEST_TEMPLATE_NAME	nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_CODE	nvarchar(100)  = NULL,
	@p_CREATE_LOCATION NVARCHAR(100)=NULL,
	@p_HEADER NVARCHAR(max)=NULL,
	@p_CONTENT NVARCHAR(max)=NULL,
	@p_BRANCH_ID NVARCHAR(50)=NULL,
	@p_REPORT_NO NVARCHAR(50)=NULL,
	@p_TITLE NVARCHAR(500)=NULL,
	@p_REPORT_DT	VARCHAR(20) = NULL,
	@p_SCHEME_OUT VARCHAR(20),
	@p_TYPE_TEMPLATE_ID NVARCHAR(20)=NULL,
	@p_NOTES	nvarchar(max)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(12)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(12)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL,
	@p_REQUEST_TEMPLATE_XML nvarchar(MAX) = NULL,
	@p_GROUP_APPROVES NVARCHAR(MAX)=NULL

AS
--Validation is here

DECLARE @ERRORSYS NVARCHAR(15) = '' 


	IF( @ERRORSYS <> '' )
	BEGIN
		SELECT '-1' as Result, ''  ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN 0
	END
	IF EXISTS(SELECT * FROM CM_REQUEST_TEMPLATE WHERE LTRIM(RTRIM(REPORT_NO)) = LTRIM(RTRIM(@p_REPORT_NO)) AND RECORD_STATUS = 1)
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Tờ trình số: ' + @p_REPORT_NO + N' đã tồn tại trong hệ thống!' ErrorDesc 
		RETURN '-1'
	END

  IF EXISTS(SELECT * FROM CM_REQUEST_TEMPLATE WHERE LTRIM(RTRIM(REPORT_NO)) = LTRIM(RTRIM(@p_REPORT_NO)) AND RECORD_STATUS = 1)
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Tờ trình số: ' + @p_REPORT_NO + N' đã tồn tại trong hệ thống!' ErrorDesc 
		RETURN '-1'
	END

 IF (@p_CONTENT IS NULL OR @p_CONTENT = '')
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Nội dung tờ trình không được bỏ trống' ErrorDesc 
		RETURN '-1'
	END
  IF (@p_GROUP_APPROVES IS NULL OR @p_GROUP_APPROVES = '')
  BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Nhóm duyệt không được bỏ trống' ErrorDesc 
		RETURN '-1'
	END

BEGIN TRANSACTION
DECLARE @l_REQUEST_TEMPLATE_ID VARCHAR(100),

@l_SENT_TO_TEMPLATE_ID VARCHAR(20),
@string varchar(200), @hdoc int
DECLARE @DEP_BRANCH VARCHAR(20)
IF(@p_REPORT_NO IS NULL OR @p_REPORT_NO='')
BEGIN
	SET @DEP_BRANCH=(SELECT D.DEP_CODE FROM TL_USER U LEFT JOIN CM_DEPARTMENT D ON U.DEP_ID=D.DEP_ID WHERE TLNANME=@p_MAKER_ID)
	IF(@DEP_BRANCH IS NULL OR @DEP_BRANCH='')
	BEGIN
		SET @DEP_BRANCH=(SELECT B.BRANCH_CODE FROM TL_USER U LEFT JOIN CM_BRANCH B ON U.TLSUBBRID=B.BRANCH_ID WHERE TLNANME=@p_MAKER_ID)
	END
	EXEC CM_REQUEST_TEMPLATE_Get_Report_No @DEP_BRANCH,@p_REPORT_NO OUT
END
		EXEC SYS_CodeMasters_Gen 'CM_REQUEST_TEMPLATE', @l_REQUEST_TEMPLATE_ID out
		
		
INSERT INTO [dbo].[CM_REQUEST_TEMPLATE]
           ([REQUEST_TEMPLATE_ID]
           ,[REQUEST_TEMPLATE_NAME]
           ,[NOTES]
           ,[RECORD_STATUS]
           ,[MAKER_ID]
           ,[CREATE_DT]
           ,[AUTH_STATUS]
           ,[CHECKER_ID]
           ,[APPROVE_DT]
           ,[CONTENT]
           ,[CREATE_LOCATION]
           ,[HEADER]
           ,[BRANCH_ID]
           ,[REPORT_NO]
           ,[TITLE]
           ,[REPORT_DT]
           ,[SCHEME]
		   ,TYPE_TEMPLATE_ID
		   ,IS_SENT_APPROVE)
     VALUES
           (@l_REQUEST_TEMPLATE_ID
           ,@p_REQUEST_TEMPLATE_NAME
           ,@p_NOTES
           ,@p_RECORD_STATUS
           ,@p_MAKER_ID
           ,CONVERT(DATETIME, @p_CREATE_DT, 103) 
           ,@p_AUTH_STATUS
           ,@p_CHECKER_ID
           ,CONVERT(DATETIME, @p_APPROVE_DT, 103) 
           ,@p_CONTENT
           ,@p_CREATE_LOCATION
           ,@p_HEADER
           ,@p_BRANCH_ID
           ,@p_REPORT_NO
           ,@p_TITLE
           ,CONVERT(DATETIME, @p_REPORT_DT, 103) 
           ,@p_SCHEME_OUT
		   ,@p_TYPE_TEMPLATE_ID
		   ,1)
		IF @@Error <> 0 GOTO ABORT
	--insert sent to
	IF(@p_REQUEST_TEMPLATE_XML <> '' AND @p_REQUEST_TEMPLATE_XML IS NOT NULL)
	BEGIN
		declare @sentToTlb table(CONTENT nvarchar(200))
		insert into @sentToTlb
		select *
		FROM WSISPLIT(@p_REQUEST_TEMPLATE_XML,',')
	END
	DECLARE @TOT INT = (SELECT COUNT(CONTENT) from @sentToTlb)
	DECLARE @ind INT
	EXEC SYS_CodeMasters_Gen_Mult 'CM_SENT_TO_TEMPLATE',@TOT, @l_SENT_TO_TEMPLATE_ID out, @ind out
	INSERT INTO [dbo].[CM_SENT_TO_TEMPLATE]
           ([TEMPLATE_ID]
           ,[SENT_TO_TEMPLATE_ID]
           ,[CONTENT])
	SELECT @l_REQUEST_TEMPLATE_ID
		   ,('CSTM' + right('0000000000000'+convert(varchar(15),(@ind -1 + row_number() over(order by (select 1)))),10)),
		CONTENT
	FROM @sentToTlb
	IF @@Error <> 0 GOTO ABORT
	DECLARE @DONE_INS_APPROVE_GROUP BIT = 1,
			@ERROR_MESS NVARCHAR(MAX)
	EXEC CM_APPROVE_GROUP_Ins @l_REQUEST_TEMPLATE_ID,@p_GROUP_APPROVES,@DONE_INS_APPROVE_GROUP OUT, @ERROR_MESS OUT
	IF @DONE_INS_APPROVE_GROUP <> 1 GOTO ABORT_CHILD

  IF (NOT EXISTS(SELECT * FROM CM_APPROVE_GROUP WHERE REQ_ID = @l_REQUEST_TEMPLATE_ID))
  BEGIN  
    ROLLBACK TRANSACTION
  	SELECT '-1' Result, '' REQ_ID, N'Nhóm duyệt không được bỏ trống' ErrorDesc 
		RETURN '-1'
  END
	--ADD LOG
	DECLARE @p_DONE BIT = 1
	EXEC CM_TEMPLATE_LOG_Ins @l_REQUEST_TEMPLATE_ID,'ADD','',@p_MAKER_ID,NULL, @p_DONE OUT
	IF @p_DONE <> 1 GOTO ABORT
	--

COMMIT TRANSACTION
SELECT '0' as Result, @l_REQUEST_TEMPLATE_ID  ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, '' ErrorDesc
		RETURN '-1'
End
ABORT_CHILD:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, @ERROR_MESS ErrorDesc
		RETURN '-1'
End

GO

ALTER PROCEDURE dbo.CM_REQUEST_TEMPLATE_Upd 
	@p_REQUEST_TEMPLATE_ID nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_NAME	nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_CODE	nvarchar(100)  = NULL,
	@p_CREATE_LOCATION NVARCHAR(100)=NULL,
	@p_HEADER NVARCHAR(max)=NULL,
	@p_CONTENT NVARCHAR(max)=NULL,
	@p_BRANCH_ID NVARCHAR(50)=NULL,
	@p_REPORT_NO NVARCHAR(50)=NULL,
	@p_TITLE NVARCHAR(500)=NULL,
	@p_REPORT_DT	VARCHAR(20) = NULL,
	@p_SCHEME_OUT VARCHAR(20),
	@p_TYPE_TEMPLATE_ID NVARCHAR(20)=NULL,
	@p_NOTES	nvarchar(max)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(12)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(12)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL,
	@p_REQUEST_TEMPLATE_XML nvarchar(MAX) = NULL,
	@p_GROUP_APPROVES NVARCHAR(MAX)=NULL
AS
BEGIN
	--Validation is here
	DECLARE @ERRORSYS NVARCHAR(15) = '', 
		@hdoc int,
		@l_REQUEST_TEMPLATE_DETAIL_ID varchar(15),

		@l_REQUEST_TEMPLATE_DETAIL_CONTENT nvarchar(max),
		@l_REQUEST_TEMPLATE_DETAIL_CODE varchar(15),
		@l_PAGE_SIZE varchar(5),
		@l_NOTES nvarchar(max)
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END
	IF (@p_REPORT_NO IS NULL OR LTRIM(RTRIM(@p_REPORT_NO)) = '')
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Số tờ trình không được bỏ trống' ErrorDesc 
		RETURN '-1'
	END
	IF EXISTS(SELECT * FROM CM_REQUEST_TEMPLATE WHERE LTRIM(RTRIM(REPORT_NO)) = LTRIM(RTRIM(@p_REPORT_NO)) AND RECORD_STATUS = 1 AND REQUEST_TEMPLATE_ID <> @p_REQUEST_TEMPLATE_ID)
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Tờ trình số: ' + @p_REPORT_NO + N' đã tồn tại trong hệ thống!' ErrorDesc 
		RETURN '-1'
	END

  IF (@p_CONTENT IS NULL OR @p_CONTENT = '')
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Nội dung tờ trình đang tải vui lòng cập nhật lại' ErrorDesc 
		RETURN '-1'
	END
  IF (@p_GROUP_APPROVES IS NULL OR @p_GROUP_APPROVES = '')
  BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Nhóm duyệt không được bỏ trống' ErrorDesc 
		RETURN '-1'
	END


	BEGIN TRANSACTION  
	IF((SELECT AUTH_STATUS FROM CM_REQUEST_TEMPLATE WHERE REQUEST_TEMPLATE_ID=@p_REQUEST_TEMPLATE_ID ) IN ('U', 'R'))
	BEGIN
		UPDATE [dbo].[CM_REQUEST_TEMPLATE]
		SET [REQUEST_TEMPLATE_NAME] = @p_REQUEST_TEMPLATE_NAME
		  ,[REQUEST_TEMPLATE_CODE] = @p_REQUEST_TEMPLATE_CODE
		  ,[NOTES] = @p_NOTES
		  ,[MAKER_ID] = @p_MAKER_ID
		  ,[CREATE_DT] =CONVERT(DATETIME, @p_CREATE_DT, 103) 
		  ,[CONTENT] = @p_CONTENT
		  ,[CREATE_LOCATION] = @p_CREATE_LOCATION
		  ,[HEADER] = @p_HEADER
		  ,[BRANCH_ID] = @p_BRANCH_ID
		  ,[REPORT_NO] = @p_REPORT_NO
		  ,[TITLE] = @p_TITLE
		  ,[REPORT_DT] = CONVERT(DATETIME, @p_REPORT_DT, 103) 
		  ,[SCHEME] = @p_SCHEME_OUT
		  ,[TYPE_TEMPLATE_ID] = @p_TYPE_TEMPLATE_ID
		  WHERE [REQUEST_TEMPLATE_ID] = @p_REQUEST_TEMPLATE_ID
			IF @@Error <> 0 GOTO ABORT
	

		DELETE CM_SENT_TO_TEMPLATE WHERE TEMPLATE_ID=@p_REQUEST_TEMPLATE_ID
		declare @sentToTlb table(CONTENT nvarchar(200))
		IF(@p_REQUEST_TEMPLATE_XML <> '' AND @p_REQUEST_TEMPLATE_XML IS NOT NULL)
		BEGIN
			insert into @sentToTlb
			select *
			FROM WSISPLIT(@p_REQUEST_TEMPLATE_XML,',')
		END
		DECLARE @TOT INT = (SELECT COUNT(CONTENT) from @sentToTlb)
		DECLARE @ind INT,@p_SENT_TO_TEMPLATE_ID nvarchar(50)
		EXEC SYS_CodeMasters_Gen_Mult 'CM_SENT_TO_TEMPLATE',@TOT, @p_SENT_TO_TEMPLATE_ID out, @ind out
		--EXEC SYS_CodeMasters_Gen 'CM_SENT_TO_TEMPLATE', @l_SENT_TO_TEMPLATE_ID out

		INSERT INTO [dbo].[CM_SENT_TO_TEMPLATE]
			   ([TEMPLATE_ID]
			   ,[SENT_TO_TEMPLATE_ID]
			   ,[CONTENT])
		SELECT @p_REQUEST_TEMPLATE_ID
			   ,('CSTM' + right('0000000000000'+convert(varchar(15),(@ind -1 + row_number() over(order by (select 1)))),10)),
			CONTENT
		FROM @sentToTlb
		IF @@Error <> 0 GOTO ABORT
	
		--EDIT USER APPROVE
		DECLARE @CURRENT_STEP INT =(SELECT CURRENT_STEP FROM CM_REQUEST_TEMPLATE WHERE REQUEST_TEMPLATE_ID=@p_REQUEST_TEMPLATE_ID)
		DECLARE @DONE_INS_APPROVE_GROUP BIT=1,
				@ERROR_MESS NVARCHAR(MAX)
		EXEC CM_APPROVE_GROUP_Upd @p_REQUEST_TEMPLATE_ID,@p_GROUP_APPROVES,@CURRENT_STEP,@DONE_INS_APPROVE_GROUP OUT, @ERROR_MESS OUT
		IF @DONE_INS_APPROVE_GROUP <> 1 GOTO ABORT_CHILD
		--
    IF (NOT EXISTS(SELECT * FROM CM_APPROVE_GROUP WHERE REQ_ID = @p_REQUEST_TEMPLATE_ID))
    BEGIN  
      ROLLBACK TRANSACTION
    	SELECT '-1' Result, '' REQ_ID, N'Nhóm duyệt không được bỏ trống' ErrorDesc 
  		RETURN '-1'
    END

		--ADD LOG
		IF(EXISTS(SELECT 1 FROM CM_REQUEST_TEMPLATE WHERE IS_SENT_APPROVE = 0 AND REQUEST_TEMPLATE_ID = @P_REQUEST_TEMPLATE_ID))
		BEGIN
			DECLARE @p_DONE BIT = 1
			EXEC CM_TEMPLATE_LOG_Ins @p_REQUEST_TEMPLATE_ID,'UPD','',@p_MAKER_ID,NULL, @p_DONE OUT
			IF @p_DONE <> 1 GOTO ABORT
		END
	END
	ELSE
	BEGIN
		SET @ERROR_MESS = N'Trạng thái phiếu hiện không thể cập nhật'
		GOTO ABORT_CHILD
	END
	--

COMMIT TRANSACTION

SELECT '0' as Result, @p_REQUEST_TEMPLATE_ID  ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, '' ErrorDesc
		RETURN '-1'
END
ABORT_CHILD:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, @ERROR_MESS ErrorDesc
		RETURN '-1'
End
END

GO

ALTER PROCEDURE dbo.CM_REQUEST_TEMPLATE_Upd_Admin 
	@p_REQUEST_TEMPLATE_ID nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_NAME	nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_CODE	nvarchar(100)  = NULL,
	@p_CREATE_LOCATION NVARCHAR(100)=NULL,
	@p_HEADER NVARCHAR(max)=NULL,
	@p_CONTENT NVARCHAR(max)=NULL,
	@p_BRANCH_ID NVARCHAR(50)=NULL,
	@p_REPORT_NO NVARCHAR(50)=NULL,
	@p_TITLE NVARCHAR(50)=NULL,
	@p_REPORT_DT	VARCHAR(20) = NULL,
	@p_SCHEME_IN BIT,
	@p_TYPE_TEMPLATE_ID NVARCHAR(20)=NULL,
	@p_NOTES	nvarchar(max)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(12)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(12)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL,
	@p_REQUEST_TEMPLATE_XML nvarchar(MAX) = NULL
AS
BEGIN
	--Validation is here
	DECLARE @ERRORSYS NVARCHAR(15) = '', 
		@hdoc int,
		@l_REQUEST_TEMPLATE_DETAIL_ID varchar(15),

		@l_REQUEST_TEMPLATE_DETAIL_CONTENT nvarchar(max),
		@l_REQUEST_TEMPLATE_DETAIL_CODE varchar(15),
		@l_PAGE_SIZE varchar(5),
		@l_NOTES nvarchar(max)
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END
	IF (@p_REPORT_NO IS NULL OR LTRIM(RTRIM(@p_REPORT_NO)) = '')
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Số tờ trình không được bỏ trống' ErrorDesc 
		RETURN '-1'
	END
	IF EXISTS(SELECT * FROM CM_REQUEST_TEMPLATE WHERE LTRIM(RTRIM(REPORT_NO)) = LTRIM(RTRIM(@p_REPORT_NO)) AND RECORD_STATUS = 1 AND REQUEST_TEMPLATE_ID <> @p_REQUEST_TEMPLATE_ID)
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Tờ trình số: ' + @p_REPORT_NO + N' đã tồn tại trong hệ thống!' ErrorDesc 
		RETURN '-1'
	END

  IF (@p_CONTENT IS NULL OR @p_CONTENT = '')
	BEGIN
		SELECT '-1' Result, '' REQ_ID, N'Nội dung tờ trình đang tải vui lòng cập nhật lại' ErrorDesc 
		RETURN '-1'
	END

	BEGIN TRANSACTION  

	UPDATE [dbo].[CM_REQUEST_TEMPLATE]
   SET [REQUEST_TEMPLATE_NAME] = @p_REQUEST_TEMPLATE_NAME
      ,[REQUEST_TEMPLATE_CODE] = @p_REQUEST_TEMPLATE_CODE
      ,[NOTES] = @p_NOTES
      ,[CREATE_DT] =CONVERT(DATETIME, @p_CREATE_DT, 103) 
      ,[AUTH_STATUS] = @p_AUTH_STATUS
      ,[CONTENT] = @p_CONTENT
      ,[CREATE_LOCATION] = @p_CREATE_LOCATION
      ,[HEADER] = @p_HEADER
      ,[BRANCH_ID] = @p_BRANCH_ID
      ,[REPORT_NO] = @p_REPORT_NO
      ,[TITLE] = @p_TITLE
      ,[REPORT_DT] = CONVERT(DATETIME, @p_REPORT_DT, 103) 
      ,[SCHEME] = @p_SCHEME_IN
      ,[TYPE_TEMPLATE_ID] = @p_TYPE_TEMPLATE_ID
	  WHERE [REQUEST_TEMPLATE_ID] = @p_REQUEST_TEMPLATE_ID
		IF @@Error <> 0 GOTO ABORT

	DELETE CM_SENT_TO_TEMPLATE WHERE TEMPLATE_ID=@p_REQUEST_TEMPLATE_ID
	declare @sentToTlb table(CONTENT nvarchar(200))
	IF(@p_REQUEST_TEMPLATE_XML <> '' AND @p_REQUEST_TEMPLATE_XML IS NOT NULL)
	BEGIN
		insert into @sentToTlb
		select *
		FROM WSISPLIT(@p_REQUEST_TEMPLATE_XML,',')
	END
	DECLARE @TOT INT = (SELECT COUNT(CONTENT) from @sentToTlb)
	DECLARE @ind INT,@p_SENT_TO_TEMPLATE_ID nvarchar(50)
	EXEC SYS_CodeMasters_Gen_Mult 'CM_SENT_TO_TEMPLATE',@TOT, @p_SENT_TO_TEMPLATE_ID out, @ind out
	--EXEC SYS_CodeMasters_Gen 'CM_SENT_TO_TEMPLATE', @l_SENT_TO_TEMPLATE_ID out

	INSERT INTO [dbo].[CM_SENT_TO_TEMPLATE]
		   ([TEMPLATE_ID]
		   ,[SENT_TO_TEMPLATE_ID]
		   ,[CONTENT])
	SELECT @p_REQUEST_TEMPLATE_ID
		   ,('CSTM' + right('0000000000000'+convert(varchar(15),(@ind -1 + row_number() over(order by (select 1)))),10)),
		CONTENT
	FROM @sentToTlb
	IF @@Error <> 0 GOTO ABORT
	--ADD LOG
	DECLARE @DONE_INS_APPROVE_GROUP BIT
	EXEC CM_TEMPLATE_LOG_Ins @p_REQUEST_TEMPLATE_ID,'UPD','',@p_MAKER_ID,NULL, @DONE_INS_APPROVE_GROUP OUT
	IF @DONE_INS_APPROVE_GROUP <> 1 GOTO ABORT_CHILD
	--

COMMIT TRANSACTION

SELECT '0' as Result, @p_REQUEST_TEMPLATE_ID  ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, '' ErrorDesc
		RETURN '-1'
END
ABORT_CHILD:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, N'THÊM LỊCH SỬ THẤT BẠI' ErrorDesc
		RETURN '-1'
End
END
GO