CREATE PROC  [dbo].[rpt_TR_REQ_PAYMENT_Invoice_DVKD]
@p_REQ_PAY_CODE	VARCHAR(50)	= NULL,
@p_REQ_TYPE	VARCHAR(15)	= NULL,
@p_FRMDATE VARCHAR(20) = NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_PO_CODE	VARCHAR(15)	= NULL,
@p_REQ_REASON	NVARCHAR(MAX)	= NULL,
@p_BRANCH_ID	VARCHAR(15)	= NULL,
@p_DEP_ID	VARCHAR(15)	= NULL,
@p_LEVEL VARCHAR(10) = NULL,
@p_BRANCH_CREATE	VARCHAR(15)	= NULL,
@p_REF_ID VARCHAR(15) = NULL,
@p_REGION VARCHAR(20) = NULL,
@p_EMP_ID VARCHAR(20) = NULL,
@p_TAX_NO VARCHAR(20) = NULL,
@p_SELLER NVARCHAR(255) = NULL,
@p_TOTAL_AMT_KT DECIMAL(18,0) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL
AS
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
BEGIN
	SELECT ROW_NUMBER() OVER (ORDER BY A.REQ_PAYDT_ID) AS STT,
		-- DON VI TAO PHIEU
		BR_CR.BRANCH_NAME AS BRANCH_NAME_CRE,
		-- DON VI
		BR.BRANCH_CODE BRANCH_CODE,
		BR.BRANCH_NAME BRANCH_NAME,
		-- USER
		UPPER(A.MAKER_ID_KT) AS TLFNAME,
		-- VAT KHAU TRU 100%/ KHAU TRU THEO TY LE
		CASE WHEN A.TYPE_VAT = '0' THEN '353200001'  ELSE '353200002' END AS ACC,
		-- SO GIAO DICH
		B.REQ_PAY_CODE AS TRANS_NO,
		-- NGAY GIAO DICH
		ISNULL(FORMAT(B.APPROVE_DT_KT,'dd/MM/yyyy'),'') AS TRANS_DT,
		-- KY HIEU MAU HOA DON
		A.INVOICE_SIGN INVOICE_SIGN,
		-- KY HIEU HOA DON
		A.INVOICE_NO_SIGN INVOICE_NO_SIGN,
		-- NGAY THANG NAM LAP HOA DON
		ISNULL(FORMAT(A.INVOICE_DT,'dd/MM/yyyy'),'') AS INVOICE_DT,
		-- TEN NGUOI BAN
		A.SELLER SELLER,
		-- MA SO THUE NGUOI BAN
		A.TAX_NO TAX_NO,
		-- MAT HANG
		A.GOODS_NAME GOODS_NAME,
		-- DOANH SO MUA CHUA CO THUE
		A.PRICE PRICE,
		-- THUE SUAT
		A.TAX TAX,
		-- THUE GTGT
		A.VAT VAT, 
		-- THANH TIEN SAU THUE
		A.TOTAL_AMT_KT AS TOTAL_AMT,
		A.INVOICE_NO INVOICE_NO
	FROM TR_REQ_PAY_INVOICE A
		LEFT JOIN TR_REQ_PAYMENT B ON  A.REQ_PAY_ID = B.REQ_PAY_ID
		LEFT JOIN CM_BRANCH BR ON B.BRANCH_ID = BR.BRANCH_ID
		LEFT JOIN TL_USER US ON B.MAKER_ID_KT = US.TLNANME
		LEFT JOIN CM_BRANCH BR_HT ON US.TLSUBBRID = BR_HT.BRANCH_ID
		LEFT JOIN CM_BRANCH BR_CR ON B.BRANCH_CREATE = BR_CR.BRANCH_ID
		--LEFT JOIN PAY_ENTRIES_POST EN ON A.REQ_PAY_ID = EN.TRN_ID
	WHERE 1=1
		AND B.AUTH_STATUS_KT='A'
		AND A.TYPE_FUNC ='HC' 
		AND A.TAX > 0
		--BEGIN FILTER
		AND(B.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
		AND(B.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
		AND (CONVERT(DATE, B.APPROVE_DT_KT, 103) >= CONVERT(DATE, @p_FRMDATE, 103) OR @p_FRMDATE IS NULL OR @p_FRMDATE ='')
		AND (CONVERT(DATE, B.APPROVE_DT_KT, 103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE ='')
		AND(BR.REGION_ID = @p_REGION or @p_REGION='' OR @p_REGION IS NULL)
		AND(A.MAKER_ID_KT = @p_EMP_ID OR @p_EMP_ID='' OR @p_EMP_ID IS NULL)
		AND(A.TAX_NO LIKE N'%'+@p_TAX_NO +'%' or @p_TAX_NO='' OR @p_TAX_NO IS NULL)
		AND(A.SELLER LIKE N'%'+@p_SELLER +'%' or @p_SELLER='' OR @p_SELLER IS NULL)
		AND(A.TOTAL_AMT_KT = @p_TOTAL_AMT_KT OR @p_TOTAL_AMT_KT IS NULL)

		
		AND(B.DEP_ID = @p_DEP_ID  OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
		AND	(	(	@p_LEVEL='ALL' 
					AND B.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp)	
				)
				OR	(	(	@p_LEVEL='UNIT' 
							AND B.BRANCH_ID = @p_BRANCH_ID
						)
						OR	(	@p_BRANCH_ID='' 
								OR @p_BRANCH_ID IS NULL
							)
					)
			)
		--AND (B.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID ='' OR @p_BRANCH_ID IS NULL)
		AND(B.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
END