DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER   PROC  [dbo].[rpt_TR_REQ_PAYMENT_Invoice]

@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 VARCHAR(20) = 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 ='KT' 

		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