ALTER   PROC [dbo].[TR_REQ_ENTRY_DT_Import] (
	@p_XMP_TEMP_DATA_ENTRIES XML = NULL
)
AS
BEGIN

	DECLARE @tmpEntryDT TABLE(
		STT INT IDENTITY(6, 1) PRIMARY KEY,
		ENTRY_PAIR VARCHAR(15),
		DR_CR_NAME  nVARCHAR(250),
		ACCT  VARCHAR(15),
		ACCT_NAME NVARCHAR(250),
		AMT DECIMAL(18, 0),
		BRANCH_ID VARCHAR(20),
		BRANCH_CODE VARCHAR(20),
		BRANCH_NAME nVARCHAR(250),
		DEP_ID VARCHAR(15),
		DEP_CODE VARCHAR(15),
		DEP_NAME NVARCHAR(250),
		TRN_DESC NVARCHAR(255)
	)

	DECLARE @hdoc INT
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP_DATA_ENTRIES
	
	INSERT INTO @tmpEntryDT
	SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataEntries', 2)
	WITH (
		ENTRY_PAIR VARCHAR(15),
		DR_CR_NAME  nVARCHAR(250),
		ACCT  VARCHAR(15),
		ACCT_NAME NVARCHAR(250),
		AMT DECIMAL(18, 0),
		BRANCH_ID VARCHAR(20),
		BRANCH_CODE VARCHAR(20),
		BRANCH_NAME nVARCHAR(250),
		DEP_ID VARCHAR(15),
		DEP_CODE VARCHAR(15),
		DEP_NAME NVARCHAR(250),
		TRN_DESC NVARCHAR(255)
	)
	
	------------------------------------------------ validate begin ------------------------------------------------
	-- Phần kiểm tra không để trống có thể kiểm tra ở phía front-end,
	-- thông qua biến bakValidation

	DECLARE @ERROR_MESSAGE nvarchar(MAX)

	-- Mã đơn vị
	SELECT @ERROR_MESSAGE = STUFF((
	SELECT ', dòng ' + CAST(T.STT AS VARCHAR(15)) FROM @tmpEntryDT T
	WHERE 
			NOT EXISTS (	SELECT BRANCH_CODE
							FROM dbo.CM_BRANCH
							WHERE BRANCH_CODE = T.BRANCH_CODE OR T.ACCT_NAME = N'Tổng cộng'
						)
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã đơn vị không có trong hệ thống tại: ' + @ERROR_MESSAGE
		EXEC sp_seterrormessage @ERROR_MESSAGE
		RETURN '-1'
	END
	/*
	--Mã phòng ban không thuộc đơn vị
	SELECT @ERROR_MESSAGE = STUFF((
	SELECT ', dòng ' + CAST(T.STT AS VARCHAR(15)) FROM @tmpEntryDT T
	WHERE NOT EXISTS (SELECT A.DEP_CODE
			FROM CM_DEPARTMENT A
				LEFT JOIN dbo.CM_BRANCH B ON B.BRANCH_CODE = T.BRANCH_CODE
			WHERE A.BRANCH_ID = B.BRANCH_ID AND A.DEP_CODE = T.DEP_CODE
			)
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã phòng ban không nằm trong đơn vị được chọn tại: ' + @ERROR_MESSAGE
		EXEC sp_seterrormessage @ERROR_MESSAGE
		RETURN '-1'
	END
	*/

	
	------------------------------------------------ validate end ------------------------------------------------

	SELECT T.STT, T.ENTRY_PAIR,
	CM.CONTENT AS DR_CR_NAME, CM.CDVAL AS DR_CR, CM.CONTENT AS DR_CR_NAME,
	T.ACCT, T.ACCT_NAME, 
	T.AMT,
	B.BRANCH_ID, B.BRANCH_CODE, B.BRANCH_CODE + ' - ' + B.BRANCH_NAME AS BR_FULLNAME,
	D.DEP_ID, D.DEP_CODE, d.DEP_NAME as DP_FULLNAME,
	T.TRN_DESC
	FROM  CM_BRANCH B
		INNER JOIN @tmpEntryDT T ON T.BRANCH_CODE = B.BRANCH_CODE
		LEFT JOIN CM_DEPARTMENT D ON T.DEP_CODE = D.DEP_CODE --AND T.DEP_NAME = D.DEP_NAME
		LEFT JOIN CM_ALLCODE CM ON T.DR_CR_NAME = CM.CONTENT AND CM.CDNAME = 'TR_REQ' AND CM.CDTYPE = 'ACC_ENTRY'
	WHERE T.ACCT_NAME <> N'Tổng cộng'
	ORDER BY T.STT ASC

END