ALTER  PROC [dbo].[rpt_TR_REQ_PAY_Inventory]
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_LEVEL varchar(10) = NULL,
@p_FromDate VARCHAR(20) = NULL,
@p_ToDate VARCHAR(20) = NULL,
--@p_PO_CODE	varchar(15)	= NULL,
--@p_REQ_REASON	nvarchar(MAX)	= NULL,
--@p_BRANCH_CREATE	varchar(15)	= NULL,
--@p_REF_ID varchar(15) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
@p_USER_LOGIN VARCHAR(15)= NULL,
@p_IS_REQ_AD VARCHAR(5) = NULL,
@p_IS_REQ_PAY VARCHAR(5) = NULL
AS
DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))
INSERT INTO @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
DECLARE @DEP_CODE_LG VARCHAR(15) ='', @BRANCH_TYPE VARCHAR(15)=''
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
SET @DEP_CODE_LG =( SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
BEGIN
		SELECT
		ROW_NUMBER() OVER (ORDER BY KQ.REQ_PAY_CODE DESC) AS STT,KQ.* FROM
		(

		SELECT 'TT' AS [TYPE], A.REQ_PAY_CODE, A.REQ_REASON,A.APPROVE_DT_KT,'(VNĐ)' AS CURRENCY,A.REQ_AMT ,B.TLFullName + '/VCCB' AS GDV , C.TLFullName + '/VCCB' AS KSV  , D.BRANCH_CODE AS DON_VI
		FROM TR_REQ_PAYMENT A
		JOIN TL_USER B ON A.MAKER_ID_KT = B.TLNANME
		JOIN TL_USER C ON A.CHECKER_ID_KT = C.TLNANME 
		JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
		WHERE 1=1
		AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
		AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
		AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
		AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
		AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID ='' OR @p_BRANCH_ID IS NULL)
		AND CONVERT(DATE, A.APPROVE_DT_KT, 103) >= CONVERT(DATE, @p_FromDate, 103)
		AND CONVERT(DATE, A.APPROVE_DT_KT, 103) <= CONVERT(DATE, @p_ToDate, 103)
		AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp)
		AND((@DEP_CODE_LG ='DEP000000000022' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))
			OR (@BRANCH_TYPE ='HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN AND A.DEP_ID = @DEP_CODE_LG)
			OR(@BRANCH_TYPE <>'HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN))
		AND (@p_IS_REQ_PAY='Y')
		UNION
		SELECT 'TU' AS [TYPE], A.REQ_PAY_CODE, A.REQ_REASON,A.REQ_DT,'(VNĐ)' AS CURRENCY,A.REQ_AMT ,B.TLFullName + '/VCCB' AS GDV , C.TLFullName + '/VCCB' AS KSV  , D.BRANCH_CODE AS DON_VI
		FROM TR_REQ_ADVANCE_PAYMENT A
		JOIN TL_USER B ON A.MAKER_ID_KT = B.TLNANME
		JOIN TL_USER C ON A.CHECKER_ID_KT = C.TLNANME 
		JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
		WHERE 1=1
		AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
		AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
		AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
		AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
		AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID ='' OR @p_BRANCH_ID IS NULL)
		AND (CONVERT(DATE, A.APPROVE_DT_KT, 103) >= CONVERT(DATE, @p_FromDate, 103))
		AND (CONVERT(DATE, A.APPROVE_DT_KT, 103) <= CONVERT(DATE, @p_ToDate, 103))
		AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp)
		AND((@DEP_CODE_LG ='DEP000000000022' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))
			OR (@BRANCH_TYPE ='HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN AND A.DEP_ID = @DEP_CODE_LG)
			OR(@BRANCH_TYPE <>'HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN))
		AND (@p_IS_REQ_AD='Y')
		) AS KQ ORDER BY KQ.TYPE
END