ALTER PROCEDURE [dbo].[ASS_TRANSFER_CONFIRM_MASTER_Search]
@p_TRANS_MULTI_MASTER_ID	varchar(15)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_TRANSFER_DT	VARCHAR(20) = NULL,
@p_USER_TRANSFER	nvarchar(200)  = NULL,
@p_USER_TRANSFER_NAME	nvarchar(200)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS_KT	varchar(15)  = NULL,
@p_CREATE_DT_KT	VARCHAR(20) = NULL,
@p_APPROVE_DT_KT	VARCHAR(20) = NULL,
@p_MAKER_ID_KT	varchar(50)  = NULL,
@p_CHECKER_ID_KT	varchar(50)  = NULL,
@p_REPORT_STATUS	varchar(15)  = NULL,
@p_LEVEL	VARCHAR(50) = 'UNIT',
@p_TOP	INT = 10,
@p_FROMDATE VARCHAR(20) = NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL, -- TRẠNG THÁI XÁC NHẬN CỦA BÊN GIAO (CHO)
@p_IS_UPDATE VARCHAR(1) = NULL, -- TRẠNG THÁI XÁC NHẬN CỦA NHẬN
@p_USER_LOGIN   VARCHAR(15) = NULL,
@p_TYPE_SEARCH VARCHAR(10)= NULL -- BN  --- BG
AS

BEGIN -- PAGING
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SET @p_RECORD_STATUS = '1'

	DECLARE @BRANCH_LOGIN VARCHAR(15),@BRANCH_TYPE VARCHAR(10), @DEP_LOGIN VARCHAR(15)
		SET @BRANCH_LOGIN = (SELECT TOP 1 TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)
		SET @DEP_LOGIN = (SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)
		SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_LOGIN)

	DECLARE @ASSIGN_ROLES TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))
	INSERT INTO @ASSIGN_ROLES SELECT * FROM [dbo].[TL_USER_GET_ASSIGNED_BRANCH](@p_USER_LOGIN)

	DECLARE @USER_ROLES TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))
	INSERT INTO @USER_ROLES SELECT * FROM [dbo].[TL_USER_GET_ROLES](@p_USER_LOGIN)

	DECLARE @tmp TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15),BRANCH_ID_OLD VARCHAR(20), DEPT_ID_OLD VARCHAR(15))
	INSERT INTO @tmp 
	SELECT DT.BRANCH_ID, DT.DEPT_ID, DT.BRANCH_ID_OLD, DT.DEPT_ID_OLD FROM ASS_TRANSFER_MULTI_MASTER AM
	LEFT JOIN ASS_TRANSFER_MULTI_DT DT ON DT.TRANSFER_MULTI_ID IN (SELECT MIN(TRANSFER_MULTI_ID) FROM ASS_TRANSFER_MULTI_DT  TEMP WHERE TEMP.TRANS_MULTI_MASTER_ID=AM.TRANS_MULTI_MASTER_ID)
	WHERE 1= 1
	AND ((@p_TYPE_SEARCH = 'BG' AND ((EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID_OLD AND BRANCH_TYPE ='HS')
		AND (DT.DEPT_ID_OLD IN (SELECT R.DEPT_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TP')))
				OR DT.DEPT_ID_OLD IN (SELECT DEPT_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TP')))
		OR (NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID_OLD AND BRANCH_TYPE ='HS')
			AND (DT.BRANCH_ID_OLD IN (SELECT R.BRANCH_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TPGD'))))
				OR DT.BRANCH_ID_OLD IN (SELECT BRANCH_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TPGD'))))
	OR (@p_TYPE_SEARCH = 'BN' AND ((EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID AND BRANCH_TYPE ='HS')
			AND (DT.DEPT_ID IN (SELECT R.DEPT_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TP')))
				OR DT.DEPT_ID IN (SELECT DEPT_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TP')))
		OR (NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID AND BRANCH_TYPE ='HS')
			AND (DT.BRANCH_ID IN (SELECT R.BRANCH_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TPGD'))))
				OR DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TPGD')))))
	

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
	-- PAGING BEGIN

		SELECT A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.AUTH_STATUS_NAME,C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
		ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT,T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,
		@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
		ACM.CONFIRM_STATUS_BG, ACM.CONFIRM_STATUS_BN, ACM.TRANS_MULTI_MASTER_CONFIRM_ID
	-- SELECT END

	FROM ASS_TRANSFER_MULTI_MASTER A
	INNER JOIN dbo.ASS_TRANSFER_MULTI_DT DT ON A.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID
	INNER JOIN dbo.ASS_TRANSFER_CONFIRM_MASTER ACM ON A.TRANS_MULTI_MASTER_ID = ACM.TRANS_MULTI_MASTER_ID
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
	LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT
	LEFT JOIN TL_USER T1 ON A.MAKER_ID = T1.TLNANME 
	LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT = T2.TLNANME
	LEFT JOIN TL_USER T3 ON A.CHECKER_ID = T3.TLNANME 
	LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT = T4.TLNANME

	--INNER JOIN (SELECT BRANCH_ID,TRANS_MULTI_MASTER_ID,COUNT(*) AS SL FROM ASS_TRANSFER_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,TRANS_MULTI_MASTER_ID) DTT ON DTT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID   

	 WHERE 1 = 1
		AND (A.TRANS_MULTI_MASTER_ID LIKE '%' + @p_TRANS_MULTI_MASTER_ID + '%' OR  @p_TRANS_MULTI_MASTER_ID IS NULL OR @p_TRANS_MULTI_MASTER_ID = '')	
		AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.TRANSFER_DT),103) >= CONVERT(DATE,@p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE ='')
		AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.TRANSFER_DT),103) <= CONVERT(DATE,@p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE ='')
		AND A.AUTH_STATUS = 'A' AND [dbo].[FN_CHECK_PRIVATE_TRANSFER](A.TRANS_MULTI_MASTER_ID) = 0
		--TRẠNG THÁI XÁC NHẬN CỦA BÊN GIAO (CHO)
		AND ((@p_AUTH_STATUS = 'Y' AND ACM.CONFIRM_STATUS_BG = 'Y') 
			OR (@p_AUTH_STATUS = 'N' AND ACM.CONFIRM_STATUS_BG IS NULL)
			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' ))

		--  TRẠNG THÁI XÁC NHẬN CỦA NHẬN
		AND ((@p_IS_UPDATE = 'Y' AND ACM.CONFIRM_STATUS_BN = 'Y') 
				OR (@p_IS_UPDATE = 'N' AND ACM.CONFIRM_STATUS_BN IS NULL)
				OR (@p_IS_UPDATE IS NULL OR @p_IS_UPDATE = '' ))

		AND ((@p_TYPE_SEARCH = 'BG' AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) <> 'HS' AND (DT.BRANCH_ID_OLD IN (SELECT BRANCH_ID_OLD FROM @tmp))
				OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) = 'HS' AND DT.DEPT_ID_OLD IN (SELECT DEPT_ID_OLD FROM @tmp))))
			OR (@p_TYPE_SEARCH = 'BN' AND (((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) <> 'HS' AND DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp) AND ACM.CONFIRM_STATUS_BG = 'Y')
				OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) = 'HS' AND DT.DEPT_ID IN (SELECT DEPT_ID FROM @tmp) AND ACM.CONFIRM_STATUS_BG = 'Y')))
			OR (@p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = ''))
		
	ORDER BY A.CREATE_DT DESC
	-- PAGING END

ELSE
	-- PAGING BEGIN

		SELECT A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.AUTH_STATUS_NAME,C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
		ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT,T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,
		@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
		ACM.CONFIRM_STATUS_BG, ACM.CONFIRM_STATUS_BN, ACM.TRANS_MULTI_MASTER_CONFIRM_ID	

		-- SELECT END

		FROM ASS_TRANSFER_MULTI_MASTER A
		INNER JOIN dbo.ASS_TRANSFER_MULTI_DT DT ON A.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID
		INNER JOIN dbo.ASS_TRANSFER_CONFIRM_MASTER ACM ON A.TRANS_MULTI_MASTER_ID = ACM.TRANS_MULTI_MASTER_ID
		LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
		LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
		LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT
		LEFT JOIN TL_USER T1 ON A.MAKER_ID = T1.TLNANME 
		LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT = T2.TLNANME
		LEFT JOIN TL_USER T3 ON A.CHECKER_ID = T3.TLNANME 
		LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT = T4.TLNANME

	 WHERE 1 = 1
		AND (A.TRANS_MULTI_MASTER_ID LIKE '%' + @p_TRANS_MULTI_MASTER_ID + '%' OR  @p_TRANS_MULTI_MASTER_ID IS NULL OR @p_TRANS_MULTI_MASTER_ID = '')	
		AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.TRANSFER_DT),103) >= CONVERT(DATE,@p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE ='')
		AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.TRANSFER_DT),103) <= CONVERT(DATE,@p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE ='')
		AND A.AUTH_STATUS = 'A' AND [dbo].[FN_CHECK_PRIVATE_TRANSFER](A.TRANS_MULTI_MASTER_ID) = 0
		--TRẠNG THÁI XÁC NHẬN CỦA BÊN GIAO (CHO)
		AND ((@p_AUTH_STATUS = 'Y' AND ACM.CONFIRM_STATUS_BG = 'Y') 
			OR (@p_AUTH_STATUS = 'N' AND ACM.CONFIRM_STATUS_BG IS NULL)
			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' ))

		--  TRẠNG THÁI XÁC NHẬN CỦA NHẬN
		AND ((@p_IS_UPDATE = 'Y' AND ACM.CONFIRM_STATUS_BN = 'Y') 
				OR (@p_IS_UPDATE = 'N' AND ACM.CONFIRM_STATUS_BN IS NULL)
				OR (@p_IS_UPDATE IS NULL OR @p_IS_UPDATE = '' ))

		AND ((@p_TYPE_SEARCH = 'BG' AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) <> 'HS' AND (DT.BRANCH_ID_OLD IN (SELECT BRANCH_ID_OLD FROM @tmp))
				OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) = 'HS' AND DT.DEPT_ID_OLD IN (SELECT DEPT_ID_OLD FROM @tmp))))
			OR (@p_TYPE_SEARCH = 'BN' AND (((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) <> 'HS' AND DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp) AND ACM.CONFIRM_STATUS_BG = 'Y')
				OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) = 'HS' AND DT.DEPT_ID IN (SELECT DEPT_ID FROM @tmp) AND ACM.CONFIRM_STATUS_BG = 'Y')))
			OR (@p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = ''))
	ORDER BY A.CREATE_DT DESC
	-- PAGING END

END -- PAGING