USE [gAMSPro_VietcapitalBank_v2]
GO
/****** Object:  StoredProcedure [dbo].[PL_REQ_GOOD_Search]    Script Date: 16-Aug-20 13:43:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
SELECT * FROM PL_TRADEDETAIL WHERE PLAN_ID = 'PLM000000000143'
select * from TR_CONTRACT_DT WHERE CONTRACT_ID = 'TRC000000000039'
	[TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000143','TRC000000000039','','',''
	exec [TR_PO_GOODS_Search] 1,'','','','','',NULL,'','PLM000000000003','','','',''
	exec [TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000003','','','',''
	SELECT * FROM TR_CONTRACT WHERE CONTRACT_TYPE = '1'
	SELECT * FROM PL_MASTER 
*/

ALTER PROCEDURE [dbo].[PL_REQ_GOOD_Search]
	@P_REQDT_TYPE  varchar(1)= NULL,
	@p_GD_ID VARCHAR(15) = NULL,
	@p_GD_CODE	varchar(15)  = NULL,
	@p_GD_NAME	nvarchar(200)  = NULL,
	@p_BRANCH_CODE	varchar(15)  = NULL,
	@p_BRANCH_ID VARCHAR(20) = NULL,
	@p_DEP_ID VARCHAR(20) = NULL,	
	@p_BRANCH_LOGIN varchar(15)  = NULL,
	@p_USER_LOGIN VARCHAR(20) = NULL,
	@P_PLAN_ID NVARCHAR(15) = NULL,
	@P_HH_ID VARCHAR(15),
	@p_GD_TYPE_ID VARCHAR(20) = NULL,
	@p_GD_TYPE_CODE VARCHAR(20)= NULL,
	@p_GD_TYPE_NAME NVARCHAR(500)= NULL,
	@P_TOP	INT = null
AS
BEGIN
DECLARE	
	 @l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,
	 @l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,@GD_CODE VARCHAR(50),
	 @BRANCH_TYPE VARCHAR(20),@BRANCH_LOGIN VARCHAR(20),@DEP_LOGIN VARCHAR(20),@ROLE_USER VARCHAR(20),@IS_ALL BIT,@IS_CHUNG BIT

	
	SET @IS_ALL=0
	 
	 DECLARE @DVDM_ID TABLE( DVDM_ID VARCHAR(20))

	 SET @BRANCH_TYPE= (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_ID)
	 SELECT @BRANCH_LOGIN=TLSUBBRID,@DEP_LOGIN=SECUR_CODE ,@ROLE_USER=RoleName FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN
	 IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_LOGIN AND DEP_ID=@DEP_LOGIN))
		SET @IS_ALL=1

	 SET @IS_CHUNG=0

	 IF(EXISTS(SELECT ID FROM dbo.PL_ROLE_DATA_CONFIG WHERE BRANCH_ID=@p_BRANCH_ID AND DEP_ID=@p_DEP_ID AND ROLE_TYPE='TRADE_USER_ALL'))
	 BEGIN

		SELECT CC.DVDM_ID FROM dbo.CM_DVDM DM
		LEFT JOIN dbo.PL_COSTCENTER CC ON DM.DVDM_ID=CC.DVDM_ID
		LEFT JOIN dbo.PL_COSTCENTER_DT CT ON CT.COST_ID=CC.COST_ID
		WHERE CT.DEP_ID=@p_DEP_ID AND CT.BRANCH_ID=@p_BRANCH_ID AND DM.IS_KHOI=1

		IF(EXISTS(
		SELECT CC.DVDM_ID FROM dbo.CM_DVDM DM
		LEFT JOIN dbo.PL_COSTCENTER CC ON DM.DVDM_ID=CC.DVDM_ID
		LEFT JOIN dbo.PL_COSTCENTER_DT CT ON CT.COST_ID=CC.COST_ID
		WHERE CT.DEP_ID=@p_BRANCH_LOGIN AND CT.BRANCH_ID=@p_BRANCH_ID AND DM.IS_KHOI=1 AND CC.DVDM_ID IN (SELECT CC.DVDM_ID FROM dbo.CM_DVDM DM
		LEFT JOIN dbo.PL_COSTCENTER CC ON DM.DVDM_ID=CC.DVDM_ID
		LEFT JOIN dbo.PL_COSTCENTER_DT CT ON CT.COST_ID=CC.COST_ID
		WHERE CT.DEP_ID=@p_DEP_ID AND CT.BRANCH_ID=@p_BRANCH_ID AND DM.IS_KHOI=1
		)))
			SET @IS_CHUNG=1
	END

	IF(@IS_ALL=1 OR @ROLE_USER='TGD')
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1
	END
	ELSE
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  PC.DVDM_ID FROM 
									dbo.PL_COSTCENTER PC 
									LEFT JOIN dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
									WHERE PT.DEP_ID=@DEP_LOGIN AND PT.BRANCH_ID=@BRANCH_LOGIN AND EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1)
		
	END




	DECLARE @lstGD TABLE (
	GD_ID VARCHAR(20)
	)

	DECLARE @lstGD_CODE TABLE (
	GD_CODE VARCHAR(20)
	)
	INSERT INTO @lstGD
	SELECT GD_ID  FROM dbo.CM_HANGHOA_GOODS WHERE HH_ID=@P_HH_ID
	

	IF(@P_HH_ID IS NOT NULL AND @P_HH_ID <>'')
	BEGIN	
		IF(NOT EXISTS(SELECT PM.PLAN_ID FROM dbo.PL_MASTER PM 
		LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.PLAN_ID=PM.PLAN_ID
		WHERE (PT.GOODS_ID =@p_GD_ID OR EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=PT.GOODS_ID)) 
		AND PM.YEAR = YEAR(GETDATE()) 
		AND     ( 
						(
							@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID 
							AND ((@p_DEP_ID = @DEP_LOGIN) OR (@p_DEP_ID <> @DEP_LOGIN AND PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))
						) 
					OR 
					(@BRANCH_TYPE='CN' AND PM.BRANCH_ID =@p_BRANCH_ID  AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))   ))
					OR
					(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID      )
				)
			)
			
		)
		BEGIN
			SET @P_REQDT_TYPE='O'
			INSERT INTO @lstGD_CODE			
			SELECT REPLACE(GD_CODE,'.I.','.O.') FROM dbo.CM_GOODS WHERE GD_ID IN (SELECT GD_ID FROM @lstGD)				
		END                                                         
	END




	IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
	BEGIN	
		IF(@P_REQDT_TYPE='I')
		BEGIN
			SELECT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,CG.GD_CODE,CG.GD_NAME,PM.BRANCH_ID,PM.DEPT_ID,
			ISNULL(DT.QUANTITY,0) AS QUANTITY,
			ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
			ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
			ISNULL(DT.QUANTITY,0)-	ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
			ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM, 
			ISNULL( DT.AMT_APP,0) AS AMT_APP,
			ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
			ISNULL( DT.AMT_ETM,0) AS AMT_ETM, 
			ISNULL( DT.AMT_TF,0) AS AMT_TF, 
			ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,	
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN, 
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0) AS AMT_REMAIN_ETM,
			CB.BRANCH_CODE,CB.BRANCH_NAME,'I' AS REQDT_TYPE,
			DT.NOTES,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,CD.DEP_CODE,CD.DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM dbo.PL_MASTER PM
			LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
			LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
			
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
			
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND (CB.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE +'%' OR @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE ='')
			--AND (PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)))
			--AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')
			AND (PM.PLAN_ID LIKE '%' + @P_PLAN_ID+'%' OR @P_PLAN_ID IS NULL OR @P_PLAN_ID='' )
			AND (DT.GOODS_ID = @p_GD_ID    OR @p_GD_ID IS NULL OR @p_GD_ID ='')
			AND  (EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=DT.GOODS_ID) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
			AND     ( 
						(
							@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID 

							AND(  @IS_CHUNG=1 OR ((@p_DEP_ID = @DEP_LOGIN) OR (@p_DEP_ID <> @DEP_LOGIN AND (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))))
						) 
					OR 
					(@BRANCH_TYPE='CN' AND PM.BRANCH_ID =@p_BRANCH_ID  AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))   ))
					OR
					(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID      )
				)
			AND PM.YEAR = YEAR(GETDATE())

			AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
		ELSE
        BEGIN
			SELECT '' AS PLAN_ID,'' AS PLAN_CODE,'' AS TRADE_ID, CG.GD_ID AS GOODS_ID, CG.GD_CODE ,CG.GD_NAME ,'' AS BRANCH_ID,'' AS DEPT_ID,
			@l_QUANTITY AS QUANTITY,
			@l_QUANTITY_EXE AS QUANTITY_EXE,
			@l_QUANTITY_EXE AS QUANTITY_ETM,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN_ETM,
			@l_AMT AS AMT_APP,
			@l_AMT_EXE AS AMT_EXE,
			@l_AMT_EXE AS AMT_ETM,
			@l_AMT_EXE AS AMT_TF,
			@l_AMT_EXE AS AMT_RECEIVE_TF,
			@l_AMT_REMAIN AS AMT_REMAIN,
			@l_AMT_REMAIN AS AMT_REMAIN_ETM,
			'' AS BRANCH_CODE,
			N'Ngoài kế hoạch' AS BRANCH_NAME,
			'O' AS REQDT_TYPE,
			'' AS NOTES
			,'' AS PLAN_TYPE_CODE,'' AS PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,'' DEP_CODE,'' DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM  dbo.CM_GOODS CG 
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID


		
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND CG.GD_CODE LIKE '%.O.%'
			AND (EXISTS(SELECT GD_CODE FROM @lstGD_CODE WHERE [@lstGD_CODE].GD_CODE=CG.GD_CODE ) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
			AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
	END
	ELSE
	BEGIN	
		IF(@P_REQDT_TYPE='I')
		BEGIN
			SELECT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,CG.GD_CODE,CG.GD_NAME,PM.BRANCH_ID,PM.DEPT_ID,
			ISNULL(DT.QUANTITY,0) AS QUANTITY,
			ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
			ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
			ISNULL(DT.QUANTITY,0)-	ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
			ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM, 
			ISNULL( DT.AMT_APP,0) AS AMT_APP,
			ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
			ISNULL( DT.AMT_ETM,0) AS AMT_ETM, 
			ISNULL( DT.AMT_TF,0) AS AMT_TF, 
			ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,	
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN, 
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0) AS AMT_REMAIN_ETM,
			CB.BRANCH_CODE,CB.BRANCH_NAME,'I' AS REQDT_TYPE,
			DT.NOTES,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,CD.DEP_CODE,CD.DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM dbo.PL_MASTER PM
			LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
			LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
			
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
			
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND (CB.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE +'%' OR @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE ='')
			--AND (PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)))
			--AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')
			AND (PM.PLAN_ID LIKE '%' + @P_PLAN_ID+'%' OR @P_PLAN_ID IS NULL OR @P_PLAN_ID='' )
				AND (DT.GOODS_ID = @p_GD_ID    OR @p_GD_ID IS NULL OR @p_GD_ID ='')
			AND  (EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=DT.GOODS_ID) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
		AND     ( 
						(
							@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID 
							AND(  @IS_CHUNG=1 OR ((@p_DEP_ID = @DEP_LOGIN) OR (@p_DEP_ID <> @DEP_LOGIN AND (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))))
						) 
					OR 
					(@BRANCH_TYPE<>'HS' AND PM.BRANCH_ID =@p_BRANCH_ID  AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID) ))   )
				)
		AND PM.YEAR = YEAR(GETDATE())
		AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
		ELSE
        BEGIN
			SELECT '' AS PLAN_ID,'' AS PLAN_CODE,'' AS TRADE_ID, CG.GD_ID AS GOODS_ID, CG.GD_CODE ,CG.GD_NAME ,'' AS BRANCH_ID,'' AS DEPT_ID,
			@l_QUANTITY AS QUANTITY,
			@l_QUANTITY_EXE AS QUANTITY_EXE,
			@l_QUANTITY_EXE AS QUANTITY_ETM,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN_ETM,
			@l_AMT AS AMT_APP,
			@l_AMT_EXE AS AMT_EXE,
			@l_AMT_EXE AS AMT_ETM,
			@l_AMT_EXE AS AMT_TF,
			@l_AMT_EXE AS AMT_RECEIVE_TF,
			@l_AMT_REMAIN AS AMT_REMAIN,
			@l_AMT_REMAIN AS AMT_REMAIN_ETM,
			'' AS BRANCH_CODE,
			N'Ngoài kế hoạch' AS BRANCH_NAME,
			'O' AS REQDT_TYPE,
			'' AS NOTES
			,'' AS PLAN_TYPE_CODE,'' AS PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,'' DEP_CODE,'' DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM  dbo.CM_GOODS CG 
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND CG.GD_CODE LIKE '%.O.%'
			AND (EXISTS(SELECT GD_CODE FROM @lstGD_CODE WHERE [@lstGD_CODE].GD_CODE=CG.GD_CODE ) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
			AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
	END
END
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s