CREATE TABLE [dbo].[CM_ATTACH_FILE_LOG](
	[ATTACH_ID] [varchar](15) NOT NULL,
	[TYPE] [varchar](50) NULL,
	[REF_ID] [varchar](15) NULL,
	[FILE_NAME_OLD] [nvarchar](200) NULL,
	[PATH_OLD] [nvarchar](1000) NULL,
	[FILE_NAME_NEW] [nvarchar](200) NULL,
	[PATH_NEW] [nvarchar](1000) NULL,
	[FILE_SIZE] [decimal](18, 0) NULL,
	[FILE_TYPE] [varchar](50) NULL,
	[ATTACH_DT] [date] NULL,
	[EMP_ID] [varchar](15) NULL,
	[INDEX] [varchar](50) NULL,
	[NOTES] [nvarchar](1000) NULL,
	[STATUS] [varchar](1) NULL,
	[REF_MASTER] [varchar](15) NULL,
 CONSTRAINT [PK_CM_ATTACH_FILE_LOG] PRIMARY KEY CLUSTERED 
(
	[ATTACH_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
¿
ALTER PROCEDURE [dbo].[CM_ATTACH_FILE_Upd_Xml_v2]
@p_REF_ID	varchar(15)  = null,
@p_AttachDetail	xml  = NULL,
@p_TYPE VARCHAR(20)= NULL
AS
Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output, @p_AttachDetail
	DECLARE attDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/AttachDetail',2)
	WITH 
	(
	ATTACH_ID	varchar(15),
	[TYPE]	varchar(50),
	REF_ID	varchar(15),
	FILE_NAME_OLD	nvarchar(200),
	PATH_OLD	nvarchar(1000),
	FILE_NAME_NEW	nvarchar(200),
	PATH_NEW	nvarchar(1000),
	FILE_SIZE	decimal(18),
	FILE_TYPE	varchar(50),
	ATTACH_DT	VARCHAR(20),
	EMP_ID	varchar(15),
	[INDEX]	varchar(50),
	NOTES NVARCHAR(1000),
	IS_VIEW BIT
	)
	OPEN attDetail

BEGIN TRANSACTION
		Declare @ATTACH_ID VARCHAR(15), @TYPE varchar(50), @REF_ID varchar(15), @FILE_NAME_OLD	nvarchar(200), @PATH_OLD nvarchar(1000), @FILE_NAME_NEW nvarchar(200), 
		@PATH_NEW nvarchar(1000), @FILE_SIZE decimal(18), @FILE_TYPE varchar(50), @ATTACH_DT VARCHAR(20), @EMP_ID varchar(15), @INDEX varchar(50),@NOTES NVARCHAR(1000),
		@IS_VIEW BIT
		--Xoa detail
		-- 29102020 - LOI GUI PHE DUYET THI MAT FILE. NEN FIX STORE NEU PHIEU DA GUI PHE DUYET ROI THI KHONG XOA FILE
		IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REF_ID))
		BEGIN
			IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REF_ID AND AUTH_STATUS NOT IN ('R','E','U')))
			BEGIN
				DELETE dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REF_ID
			END
			ELSE
			BEGIN
				DELETE CM_ATTACH_FILE WHERE REF_ID = @p_REF_ID;
			END
		END
		ELSE IF EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REF_ID)
		BEGIN
			IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REF_ID AND AUTH_STATUS  NOT IN ('R','E','U')))
			BEGIN
				DELETE dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REF_ID
			END
			ELSE
			BEGIN
				DELETE CM_ATTACH_FILE WHERE REF_ID = @p_REF_ID;
			END
		END
		ELSE
		BEGIN
			DELETE CM_ATTACH_FILE WHERE REF_ID = @p_REF_ID;
			DELETE dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REF_ID
		END
		IF @@Error <> 0 GOTO ABORT
		UPDATE CM_ATTACH_FILE SET REF_ID = 'X'+RIGHT(REF_ID,14) WHERE REF_ID = @p_REF_ID;
		UPDATE dbo.TR_REQUEST_DOC_FILE SET  REQ_ID = 'X'+RIGHT(REQ_ID,14)  WHERE REQ_ID=@p_REF_ID
		FETCH NEXT FROM attDetail INTO @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,@IS_VIEW
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @ATTACH_ID out
			IF @ATTACH_ID='' OR @ATTACH_ID IS NULL GOTO ABORT
			--IF(LEN(@ATTACH_ID) = 0)
			--	BEGIN
			--		EXEC SYS_CodeMasters_Gen 'CM_ATTACH_FILE', @ATTACH_ID out
			--		IF @ATTACH_ID='' OR @ATTACH_ID IS NULL GOTO ABORT
			--	END		
			BEGIN				
				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])
				VALUES(@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 )
				----2021913 LUCTV BO SUNG VAO LOG DINH KEM FILE
				INSERT INTO CM_ATTACH_FILE_LOG([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])
				VALUES(@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 )
				IF @@Error <> 0 GOTO ABORT

				IF(@p_TYPE='PYC')
				BEGIN
					DECLARE @TR_REQ_DOC_FILE_ID VARCHAR(20)

					EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_FILE', @TR_REQ_DOC_FILE_ID out
					IF @TR_REQ_DOC_FILE_ID='' OR @TR_REQ_DOC_FILE_ID IS NULL GOTO ABORT
					INSERT INTO dbo.TR_REQUEST_DOC_FILE
					(
					    TR_REQUEST_DOC_FILE_ID,
					    ATTACH_ID,
					    IS_VIEW,
					    REQ_ID
					)
					VALUES
					(   @TR_REQ_DOC_FILE_ID,   -- TR_REQUEST_DOC_FILE_ID - varchar(20)
					    @ATTACH_ID,   -- ATTACH_ID - varchar(20)
					    @IS_VIEW, -- IS_VIEW - bit
					    @REF_ID    -- REQ_ID - varchar(20)
					    )
				END
				-- next Id
				FETCH NEXT FROM attDetail INTO @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,@IS_VIEW
			END
		END
		CLOSE attDetail
		DEALLOCATE attDetail
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE attDetail
		DEALLOCATE attDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End