ALTER PROC [dbo].[rpt_TR_REQ_PAY_AUTO_Inventory]
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_PAY_AUTO_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_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 @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)

	-- DOANPTT 280922: CHO PHEP PHONG KE TOAN TIM KIEM THEO DON VI DUOC CHON O GIAO DIEN. DOI VOI DON VI KINH DOANH THI KHONG TIM KIEM THEO PHONG BAN
	IF(@DEP_CODE_LG = 'DEP000000000022')
	BEGIN
		SET @p_BRANCH_LOGIN = @p_BRANCH_ID
	END
	ELSE
	BEGIN
		SET @p_LEVEL = 'UNIT'
		IF(@BRANCH_TYPE <> 'HS')
		BEGIN
			SET @p_DEP_ID = ''
		END
	END

	DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))
	INSERT INTO @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
	IF(@p_LEVEL LIKE 'UNIT')
	BEGIN
	 DELETE FROM @tmp
	 INSERT INTO @tmp(BRANCH_ID) VALUES (@p_BRANCH_LOGIN)
	END
BEGIN
		SELECT
		ROW_NUMBER() OVER (ORDER BY KQ.REQ_DT ASC) AS STT,KQ.* FROM
		(
		SELECT ALLCODE.CONTENT 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, G.BRANCH_CODE AS DON_VI_QUAN_LY_HOP_DONG, H.TLFullName + '/VCCB' AS GDV_AUTO , I.TLFullName + '/VCCB' AS KSV_AUTO, 
		CASE 
			WHEN F.REQ_PAY_AUTO_TYPE = 'A' THEN ALLCODE2.CONTENT
			ELSE ALLCODE3.CONTENT
		END LOAI_THANH_TOAN
		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
		LEFT JOIN CM_ALLCODE ALLCODE ON A.TYPE_AUTO = ALLCODE.CDVAL AND CDNAME = 'PAY_TYPE_AUTO' AND CDTYPE = 'REQ_AUTO'
		LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS E ON A.REQ_PAY_ID = E.REQ_PAY_ID
		LEFT JOIN TR_REQ_PAYMENT_AUTO F ON E.REQ_PAY_AUTO_ID = F.REQ_PAY_AUTO_ID
		LEFT JOIN CM_BRANCH G ON F.BRANCH_MANAGE_ID = G.BRANCH_ID
		LEFT JOIN TL_USER H ON F.MAKER_ID_KT = H.TLNANME
		LEFT JOIN TL_USER I ON F.CHECKER_ID_KT = I.TLNANME 
		LEFT JOIN CM_ALLCODE ALLCODE2 ON F.REQ_PAY_AUTO_SERVICE_TYPE = ALLCODE2.CDVAL AND ALLCODE2.CDNAME = 'PAY_SER_AUTO_TS' AND ALLCODE2.CDTYPE = 'REQ_AUTO' 
		LEFT JOIN CM_ALLCODE ALLCODE3 ON F.REQ_PAY_AUTO_SERVICE_TYPE = ALLCODE2.CDVAL AND ALLCODE2.CDNAME = 'PAY_SER_AUTO' AND ALLCODE2.CDTYPE = 'REQ_AUTO' 
		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.TYPE_AUTO LIKE N'%'+@p_REQ_PAY_AUTO_TYPE+'%' OR @p_REQ_PAY_AUTO_TYPE = '' OR @p_REQ_PAY_AUTO_TYPE 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.REQ_REASON LIKE N'%'+@p_REASON+'%' or @p_REASON='' OR @p_REASON IS NULL)
		AND(A.DEP_ID = @p_DEP_ID  OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
		AND A.AUTH_STATUS = 'A'
		AND A.AUTH_STATUS_KT = 'A'
		AND A.IS_CREATE_AUTO = 'Y'
		AND A.IS_CREATE_AUTO_DONE = 'Y'

		AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
		AND (	-- PHONG KE TOAN
				(@DEP_CODE_LG ='DEP000000000022' AND A.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
				OR (	@DEP_CODE_LG ='DEP000000000022'
						AND EXISTS	(	SELECT * 
										FROM TR_REQ_ADVANCE_DT  
										WHERE REQ_PAY_ID = A.REQ_PAY_ID 
										AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp)) 
										AND A.AUTH_STATUS_KT ='A'
									)-- CAC PHIEU DE NGHI CO LINK VOI HOP DONG MÀ DON VI KINH DOANH NAY QUAN LY
					)
				-- PHONG BAN THUOC HOI SO
				OR (@BRANCH_TYPE ='HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN AND A.DEP_ID = @DEP_CODE_LG)
				-- DVKD
				OR(@BRANCH_TYPE <>'HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN)
				OR (	@BRANCH_TYPE <> 'HS' 
						AND EXISTS	(	SELECT * 
										FROM TR_REQ_ADVANCE_DT  
										WHERE REQ_PAY_ID = A.REQ_PAY_ID 
										AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
										AND A.AUTH_STATUS_KT ='A'
									)-- CAC PHIEU DE NGHI CO LINK VOI HOP DONG MÀ DON VI KINH DOANH NAY QUAN LY
					)
			)
		) AS KQ ORDER BY KQ.REQ_DT
END