ALTER   PROCEDURE [dbo].[rpt_TR_REQ_DOC_Inventory]
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_TYPE	varchar(15)	= 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_ID	varchar(15)	= NULL,
--@p_DEP_ID	varchar(15)	= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_LEVEL varchar(10) = 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_SO_TO_TRINH VARCHAR(15) = 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_ID_LOGIN VARCHAR(15), @BRANCH_TYPE_LOGIN VARCHAR(15), @ROLE_USER_LOGIN VARCHAR(15)
SET @DEP_ID_LOGIN=(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
SET @BRANCH_TYPE_LOGIN=(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
SET @ROLE_USER_LOGIN =  (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
BEGIN 


	SELECT 
	ROW_NUMBER() OVER (ORDER BY A.REQ_ID DESC) AS STT,
	U.TLFullName AS NV_MS,
	A.REQ_CODE AS SP_MS,	
	A.CREATE_DT AS NHANPHIEU,
	A.APPROVE_DT AS DUYETPHIEU,
	-------
	CMS.DMMS_NAME AS DV_YC_MS,	
	PLRD.REQ_CODE AS SO_TTCT,
	PLRD.REQ_NAME AS TEN_TTCT,
	PLRD.REQ_CONTENT AS ND_HH,
	--------------------
	DT.QUANTITY AS SL,
	UN.UNIT_NAME AS DVT,
	DT.PRICE_ETM AS DG_VAT,	
	DT.TOTAL_AMT_ETM AS THANH_TIEN_VAT,
	DT.TOTAL_AMT AS CP_MSTT,
	ABS(DT.TOTAL_AMT - DT.TOTAL_AMT_ETM) AS CP_MSTK,
	S.SUP_NAME AS NCC,
	THH.HH_TYPE_NAME AS LOAI_HH,
	HH.HH_NAME AS TEN_HH,
	CT.CONTRACT_NAME AS LOAI_HD,
	'' AS NGAY_TT_DOT1, 
	'' AS TT_DOT1,	
	'' AS NGAY_TT_DOT2,
	'' AS TT_DOT2,	
	'' AS NGAY_TT_DOT3,
	'' AS TT_DOT3,	
	'' AS TIENDO_TT,	
	'' AS CP_BL_BH,
	'' AS FROMDATE,
	'' AS TODATE
	FROM TR_REQUEST_DOC  A
	LEFT JOIN PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
	LEFT JOIN 
	(
		SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
		dbo.CM_DMMS 
		LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
		UNION ALL
		SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
		FROM dbo.CM_DVDM
	)CMS ON CMS.DMMS_ID=A.DMMS_ID
	LEFT JOIN TL_USER U ON U.TLNANME = A.USER_DVMS
	LEFT JOIN TR_REQUEST_DOC_DT DT ON A.REQ_ID = DT.REQ_DOC_ID
	LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = DT.SUP_ID
	LEFT JOIN CM_HANGHOA HH ON HH.HH_ID = DT.HANGHOA_ID 
	LEFT JOIN CM_HANGHOA_TYPE THH ON THH.HH_TYPE_ID = HH.HH_TYPE_ID
	LEFT JOIN CM_UNIT UN ON UN.UNIT_ID = HH.UNIT_ID
	LEFT JOIN TR_CONTRACT CT ON CT.REQ_DOC_ID = A.REQ_ID
	--LEFT JOIN TR_CONTRACT_PAYMENT P ON P.CONTRACT_ID = CT.CONTRACT_ID 
	WHERE 1=1
	--Thiếu những field không biết lấy như nào nên em để lại 
	--AND(A.REQ_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 CONVERT(DATE, A.CREATE_DT, 103) >= CONVERT(DATE, @p_FromDate, 103)
	--AND CONVERT(DATE, A.CREATE_DT, 103) <= CONVERT(DATE, @p_ToDate, 103)
	AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp))
	OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))
	AND (A.PROCESS_ID ='APPROVE')
	--ORDER BY A.CREATE_DT DESC
END