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)	

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_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 = '' ))

  -- TIM KIEM THEO TRUONG DON VI GIAO TAI SAN
  AND ((@p_TYPE_SEARCH = 'BG' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    (SELECT TOP 1 DT.BRANCH_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),
    (SELECT TOP 1 DT.DEPT_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US
    WHERE US.TLNANME = @p_USER_LOGIN))
  -- TIM KIEM THEO TRUONG DON VI NHAN TAI SAN
  OR (@p_TYPE_SEARCH = 'BN' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),
    (SELECT TOP 1 DT.DEPT_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US
    WHERE US.TLNANME = @p_USER_LOGIN))
  OR (@p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = ''))
	AND A.CHECKER_ID>=CONVERT(DATETIME,'2022-17-10',103)
	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_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 = '' ))
    -- TIM KIEM THEO TRUONG DON VI GIAO TAI SAN
    AND ((@p_TYPE_SEARCH = 'BG' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
      (SELECT TOP 1 DT.BRANCH_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),
      (SELECT TOP 1 DT.DEPT_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US
      WHERE US.TLNANME = @p_USER_LOGIN))
    -- TIM KIEM THEO TRUONG DON VI NHAN TAI SAN
    OR (@p_TYPE_SEARCH = 'BN' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
      (SELECT TOP 1 DT.BRANCH_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),
      (SELECT TOP 1 DT.DEPT_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US
      WHERE US.TLNANME = @p_USER_LOGIN))
    OR (@p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = ''))
    AND A.CHECKER_ID>=CONVERT(DATETIME,'2022-17-10',103)
	ORDER BY A.CREATE_DT DESC
	-- PAGING END

END -- PAGING