ALTER PROCEDURE dbo.CM_ATTACH_FILE_Ins
@p_TYPE	varchar(50)  = NULL,
@p_REF_ID	varchar(15)  = NULL,
@p_FILE_NAME_OLD	nvarchar(MAX)  = NULL,
@p_PATH_OLD	nvarchar(MAX)  = NULL,
@p_FILE_NAME_NEW	nvarchar(MAX)  = NULL,
@p_PATH_NEW	nvarchar(MAX)  = NULL,
@p_FILE_SIZE	decimal(18)  = NULL,
@p_FILE_TYPE	varchar(MAX)  = NULL,
@p_ATTACH_DT	VARCHAR(20) = NULL,
@p_EMP_ID	varchar(15)  = NULL,
@p_INDEX	varchar(50)  = NULL,
@P_NOTES NVARCHAR(1000) = NULL,
@p_AttachDetail	xml  = NULL
AS
--Validation is here
--DECLARE @ERRORSYS NVARCHAR(15) = '' 
--	IF(@p_REF_ID <> '')
--		IF ( NOT EXISTS ( SELECT * FROM CM_ATTACH_FILE WHERE ATTACH_ID = @p_REF_ID ))
--			SET @ERRORSYS = 'CMA-99999'
--IF @ERRORSYS <> '' 
--BEGIN
--	SELECT ErrorCode Result, ''  ATTACH_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
--	RETURN '0'
--END 
BEGIN TRANSACTION
DECLARE @l_ATTACH_ID VARCHAR(15)
		
		IF @p_PATH_NEW IS NOT NULL AND @p_PATH_NEW <> ''
		BEGIN
			EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @l_ATTACH_ID out
			IF @l_ATTACH_ID='' OR @l_ATTACH_ID IS NULL GOTO ABORT

			INSERT INTO CM_ATTACH_FILE([ATTACH_ID],[TYPE],[REF_ID],[FILE_NAME_OLD],[PATH_OLD],[FILE_NAME_NEW],[PATH_NEW],[FILE_SIZE],[FILE_TYPE],[ATTACH_DT],[EMP_ID],[INDEX],[NOTES], [REF_MASTER])
			VALUES(@l_ATTACH_ID ,@p_TYPE ,@p_REF_ID ,@p_FILE_NAME_OLD ,@p_PATH_OLD ,@p_FILE_NAME_NEW ,@p_PATH_NEW ,@p_FILE_SIZE ,@p_FILE_TYPE ,CONVERT(DATETIME, @p_ATTACH_DT, 103) ,@p_EMP_ID, @p_INDEX,@P_NOTES, @p_REF_ID)
			IF @@Error <> 0 GOTO ABORT

			-- INSERT HIST
			EXEC CM_ATTACH_FILE_HIS_Ins @l_ATTACH_ID, @p_TYPE, @p_REF_ID, @p_FILE_NAME_OLD, @p_PATH_OLD, @p_FILE_NAME_NEW, @p_PATH_NEW, @p_FILE_SIZE, @p_FILE_TYPE, @p_ATTACH_DT, @p_EMP_ID, @p_INDEX, @P_NOTES, 0, 'CreateFile'
			IF @@Error <> 0 GOTO ABORT
		END
		
		

		-- INSERT LIST IF EXISTS
		IF(@p_AttachDetail IS NOT NULL)
		BEGIN

		Declare @hdoc INT
		Exec sp_xml_preparedocument @hdoc Output, @p_AttachDetail
		DECLARE attDetail CURSOR FOR
		SELECT *
		FROM OPENXML(@hDoc,'/Root/AttachDetail',2)
		WITH 
		(
			[TYPE]	varchar(50),
			REF_ID	varchar(15),
			FILE_NAME_OLD	nvarchar(MAX),
			PATH_OLD	nvarchar(MAX),
			FILE_NAME_NEW	nvarchar(MAX),
			PATH_NEW	nvarchar(MAX),
			FILE_SIZE	decimal(18),
			FILE_TYPE	varchar(MAX),
			ATTACH_DT	VARCHAR(20),
			EMP_ID	varchar(15),
			[INDEX]	varchar(50),
			NOTES NVARCHAR(1000),
			REF_MASTER	varchar(15)
		)
		OPEN attDetail

		Declare @TYPE varchar(50), @REF_ID varchar(15), @REF_MASTER varchar(15), @FILE_NAME_OLD	nvarchar(MAX), @PATH_OLD nvarchar(MAX), @FILE_NAME_NEW nvarchar(MAX), 
		@PATH_NEW nvarchar(MAX), @FILE_SIZE decimal(18), @FILE_TYPE varchar(50), @ATTACH_DT VARCHAR(20), @EMP_ID varchar(15), @INDEX varchar(50),@NOTES NVARCHAR(1000)
		
		FETCH NEXT FROM attDetail INTO @TYPE, @REF_ID, @FILE_NAME_OLD, @PATH_OLD, @FILE_NAME_NEW, @PATH_NEW, @FILE_SIZE, @FILE_TYPE, @ATTACH_DT, @EMP_ID, @INDEX,@NOTES,@REF_MASTER
		
		WHILE @@FETCH_STATUS = 0	
		BEGIN

			EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @l_ATTACH_ID out
			IF(@l_ATTACH_ID='' OR @l_ATTACH_ID IS NULL)
				GOTO ABORT

			INSERT INTO CM_ATTACH_FILE([ATTACH_ID],[TYPE],[REF_ID],[FILE_NAME_OLD],[PATH_OLD],[FILE_NAME_NEW],[PATH_NEW],[FILE_SIZE],[FILE_TYPE],[ATTACH_DT],[EMP_ID], [INDEX],[NOTES], [REF_MASTER])
			VALUES(@l_ATTACH_ID ,@TYPE ,@REF_ID ,@FILE_NAME_OLD ,@PATH_OLD ,@FILE_NAME_NEW ,@PATH_NEW ,@FILE_SIZE ,@FILE_TYPE ,CONVERT(DATETIME, @ATTACH_DT, 103), @EMP_ID, @INDEX, @NOTES, @p_REF_ID)
			IF @@Error <> 0 GOTO ABORT

			-- INSERT HIST
			EXEC CM_ATTACH_FILE_HIS_Ins @l_ATTACH_ID, @TYPE, @REF_ID, @FILE_NAME_OLD, @PATH_OLD, @FILE_NAME_NEW, @PATH_NEW, @FILE_SIZE, @FILE_TYPE, @ATTACH_DT, @EMP_ID, @INDEX, @NOTES, 0, 'CreateFile'
			IF @@Error <> 0 GOTO ABORT

			-- next Id
			FETCH NEXT FROM attDetail INTO @TYPE, @REF_ID, @FILE_NAME_OLD, @PATH_OLD, @FILE_NAME_NEW, @PATH_NEW, @FILE_SIZE, @FILE_TYPE, @ATTACH_DT, @EMP_ID, @INDEX,@NOTES,@REF_MASTER
			END
			CLOSE attDetail
			DEALLOCATE attDetail

		END

		-------------------------BAODNQ 16/10/2022 : HOT FIX ĐÍNH KÈM FILE PYCMS----------------------
		-----------------------UPDATE ATTACH_ID VÀO BẢNG TR_REQUEST_DOC_FILE---------------------
		IF(EXISTS(
			SELECT * FROM TR_REQUEST_DOC_FILE TRF 
			INNER JOIN CM_ATTACH_FILE CAF ON TRF.TR_REQUEST_DOC_FILE_ID = CAF.REF_ID)
		)
		BEGIN
--			UPDATE TR_REQUEST_DOC_FILE SET
--				ATTACH_ID = CAF.ATTACH_ID
--			FROM TR_REQUEST_DOC_FILE TRF 
--			INNER JOIN CM_ATTACH_FILE CAF ON TRF.TR_REQUEST_DOC_FILE_ID = CAF.REF_ID
      --hieuhm 06/04/2023 hotfix chỉnh sửa đính kèm file ở DMMS
      UPDATE TR_REQUEST_DOC_FILE SET ATTACH_ID = (SELECT TOP(1) ATTACH_ID FROM CM_ATTACH_FILE WHERE TR_REQUEST_DOC_FILE.TR_REQUEST_DOC_FILE_ID = CM_ATTACH_FILE.REF_ID ORDER BY ATTACH_ID DESC)
		  --hieuhm 06/04/2023 hotfix chỉnh sửa đính kèm file ở DMMS
    END
		-------------------------END BADNQ-------------------------------

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