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, ACC.ACC_NAME AS 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_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 B.BRANCH_ID = D.BRANCH_ID --AND T.DEP_NAME = D.DEP_NAME
		LEFT JOIN CM_ALLCODE CM ON T.DR_CR_NAME = CM.CONTENT AND CM.CDNAME = 'DR_CR' AND CM.CDTYPE = 'REQ_PAY_ENTRIES'
		LEFT JOIN CM_ACCOUNT ACC ON T.ACCT = ACC.ACC_NO
	WHERE T.ACCT_NAME <> N'Tổng cộng' AND D.DEP_NAME <> N'Ban giám đốc'
	ORDER BY T.STT ASC

END


DECLARE @T VARCHAR(2) = '0'
DECLARE @REQ_PAY_ID VARCHAR(20), @REQ_PAY_CODE VARCHAR(50), @BRANCH_CREATE VARCHAR(50), @p_DEP_ID VARCHAR(50)
	SET @REQ_PAY_CODE = 'XLN.2022.000235'
	SET @BRANCH_CREATE = (SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_CODE = @REQ_PAY_CODE)
	SET @p_DEP_ID = (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_CODE = @REQ_PAY_CODE)

	SET @REQ_PAY_ID = (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_CODE = @REQ_PAY_CODE)
IF(@T = '0')
BEGIN
	

	SELECT PROCESS AS PROCESS_STATUS,  * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_CODE = @REQ_PAY_CODE

	SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = @REQ_PAY_ID

	SELECT * FROM PL_PROCESS WHERE REQ_ID = @REQ_PAY_ID
	SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE
	/* dep_ID = DEP000000000205
	delete from PL_REQUEST_PROCESS where id= 83413
	update PL_REQUEST_PROCESS set STATUS = 'C' where id = 83390
	update TR_REQ_ADVANCE_PAYMENT set PROCESS = '0' where REQ_PAY_CODE =  'NBO.2022.000344'

	exec TR_REQ_ADVANCE_PAYMENT_Appr @p_REQ_PAY_ID=N'TADY00000203480',@p_AUTH_STATUS=N'C',@p_COST_ID=NULL,@p_CHECKER_ID=N'muoilvb',@p_IS_AUTHORITY=N'',@p_PROCESS_NOTES=NULL
	go
	*/
	--select * from TL_SYSROLE_LIMIT where ROLE_ID = 'PTGD'

	--update TL_SYSROLE_LIMIT set LIMIT_PERCENT = 150000000 where LIMIT_ID = 'TSRL00000000156'

	exec TR_CHECK_ROLE_APPROVE @TYPE = 'ADV_PAY', @p_REQ_ID = @REQ_PAY_ID, @p_USER_LOGIN = 'sangnm1'

	DECLARE @MAX_STEP INT, @NEXT_ROLE VARCHAR(20), @MESSAGE NVARCHAR(100), @PROCESS_ID_NEXT VARCHAR(5), @PROCESS_PARENT VARCHAR(5),
	@BRANCH_ID_PROC VARCHAR(15), @NEX_ROLE_STEP VARCHAR(25), @CURRENT_PROCESS VARCHAR(15), @p_REQ_PAY_ID VARCHAR(20)
	SET @p_REQ_PAY_ID = @REQ_PAY_ID
	DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))

	INSERT INTO @TABLE_ROLE SELECT (SELECT ROLENAME FROM TL_USER WHERE TLNANME ='muoilvb')
	INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT ROLENAME FROM TL_USER WHERE TLNANME ='muoilvb') 
	SELECT ROLE_USER, PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND STATUS = 'C' AND ROLE_USER IN (SELECT ROLE_AUTH FROM @TABLE_ROLE)
	SET @CURRENT_PROCESS = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND STATUS = 'C' AND ROLE_USER IN (SELECT ROLE_AUTH FROM @TABLE_ROLE))
	SELECT ROLE_USER, PROCESS_ID FROM PL_REQUEST_PROCESS WHERE PARENT_PROCESS_ID =@CURRENT_PROCESS AND REQ_ID = @p_REQ_PAY_ID
	SET @NEX_ROLE_STEP = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE PARENT_PROCESS_ID =@CURRENT_PROCESS AND REQ_ID = @p_REQ_PAY_ID )

END


ELSE
BEGIN
		DECLARE 
		@PO_ID VARCHAR(20) = 'TADY00000203475',
		@DEP_CREATE VARCHAR(15),
		@BRANCH_TYPE VARCHAR(15),
		@FATHER_ID VARCHAR(15),
		--@FLAG VARCHAR(1), -- FLAG = 1: THANH TOÁN / TẠM ỨNG
		---------BAODNQ 26/10/2022 : TĂNG KÍCH THƯỚC BIẾN @FLAG
		@FLAG VARCHAR(5), -- FLAG = 1: THANH TOÁN / TẠM ỨNG
		@AUTH_STATUS VARCHAR(10),
		@PROCESS VARCHAR(10),
		@MAKER_ID VARCHAR(15),
		@DEP_CODE VARCHAR(15),
		@NV_XL_MS VARCHAR(15)
	DECLARE @ROLE_CURRENT VARCHAR(15), @USER_RECIVE_MAIL VARCHAR(15), @REQ_TYPE VARCHAR(15)
	DECLARE @SYS_PREFIX VARCHAR(15),@PAGE NVARCHAR(200)
	DECLARE @l_LST_REQ_ID TABLE (
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[REQ_PAY_ID] [VARCHAR](50) NULL)
	INSERT INTO @l_LST_REQ_ID SELECT VALUE FROM WSISPLIT(@PO_ID,',')
	DECLARE @LST_POID TABLE(ID VARCHAR(15))
	DECLARE @LST_USER_RECIVE TABLE (TLNAME VARCHAR(10))

	SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =  @PO_ID)
	SET @BRANCH_CREATE = (SELECT BRANCH_CREATE FROM TR_REQ_ADVANCE_PAYMENT  WHERE REQ_PAY_ID =  @PO_ID)
	SET @DEP_CREATE = (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT  WHERE REQ_PAY_ID =  @PO_ID)
	SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
	SET @FATHER_ID = (SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
	SET @REQ_TYPE =(SELECT TOP 1 REQ_TYPE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@PO_ID)
	SET @DEP_CODE =(SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@DEP_CREATE)

	SELECT @AUTH_STATUS AS AUTH_STATUS, @BRANCH_CREATE AS BRANCH_CREATE, @DEP_CREATE AS DEP_CREATE, @BRANCH_TYPE AS BRANCH_TYPE, @FATHER_ID AS FATHER_ID, @REQ_TYPE AS REQ_TYPE, @DEP_CODE AS DEP_CODE
END


select * from MW_TRANSFER where TRANSFER_ID = 'MWTF00000250202'

select * from MW_TRANSFER_DT where TRANSFER_ID = 'MWTF00000250202'--MWTDT0000265247

select * from MW_IN

select * from MW_MAST_BAL

select * from MW_MAST_BAL_STMT A

select * from MW_TRANSFER_DT A
LEFT JOIN MW_MAST_BAL B ON a.MAST_BAL_ID = B.MAST_BAL_ID
where A.TRANSFER_DT_ID = 'MWTDT0000265247'


select * from MW_TRANSFER_DT A
LEFT JOIN MW_MAST_BAL B ON a.MAST_BAL_ID = B.MAST_BAL_ID
LEFT JOIN MW_MAST_BAL_STMT C ON B.MAST_BAL_ID = C.MAST_BAL_ID
where A.TRANSFER_DT_ID = 'MWTDT0000265247'

select * from MW_MAST_BAL_STMT where TOTAL_AMT = 7899240.00

select * from CM_DEPARTMENT where DEP_CODE = '0010001'

select * from CM_DEPARTMENT where DEP_NAME like N'%Ban giám đốc%'