ALTER   PROCEDURE [dbo].[PL_REQ_GOOD_Search]  
@P_REQDT_TYPE varchar(1)= NULL,  
@p_GD_ID VARCHAR(15) = NULL,  
@p_GD_CODE varchar(20) = 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_DVDM_ID VARCHAR(15) = NULL,
@p_PLAN_TYPE_ID VARCHAR(15) = NULL,
@p_TYPE_NS VARCHAR(15) = NULL,
@p_YEAR VARCHAR(4),
@P_TOP INT = null  
AS  
BEGIN -- PAGING
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  
DECLARE @IS_HANHCHINH BIT  
SET @IS_HANHCHINH=0  
IF(@p_YEAR IS NULL OR @p_YEAR='' OR @p_YEAR = '0')
BEGIN
	SET @p_YEAR=YEAR(GETDATE())
END
IF(@p_YEAR <  YEAR(GETDATE()))
BEGIN
	SET @p_YEAR='1'
END

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

SELECT @BRANCH_LOGIN=TLSUBBRID,@DEP_LOGIN=SECUR_CODE ,@ROLE_USER=RoleName FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN  


IF(@p_BRANCH_CODE IS NOT NULL AND @p_BRANCH_CODE <>'')
	SELECT TOP 1 @p_DEP_ID=DEP_ID,@p_BRANCH_ID=BRANCH_ID FROM dbo.CM_DEPARTMENT WHERE DEP_CODE=@p_BRANCH_CODE

IF(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)
	SET @p_BRANCH_ID=@BRANCH_LOGIN

	
		DECLARE @AUTHOR_DVDM TABLE
		(
		ROLE_ID VARCHAR(100),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20)
	
		)


	INSERT INTO @AUTHOR_DVDM
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID	    
	)
	SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE FROM dbo.TL_USER TU 	
	WHERE TU.TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE FROM dbo.TL_USER TU 
	
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE FROM dbo.TL_SYS_ROLE_MAPPING TU 
	WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE FROM dbo.TL_SYS_ROLE_MAPPING TU 
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	UNION ALL
	SELECT TU.RoleName,TU.TLSUBBRID,DE.FATHER_ID SECUR_CODE FROM dbo.TL_USER TU 
	LEFT JOIN dbo.CM_DEPARTMENT DE ON TU.SECUR_CODE = DE.DEP_ID
	WHERE TU.TLNANME=@p_USER_LOGIN

	UNION ALL --- 18.10.2022 LUCTV DIEU CHINH CHO PHEP TRA CUU NGAN SACH PHONG BAN CHA
	SELECT TU.RoleName,TU.TLSUBBRID,DP.FATHER_ID FROM
	dbo.TL_USER TU 	
	INNER JOIN CM_DEPARTMENT DP ON TU.DEP_ID =TU.DEP_ID
	WHERE TU.TLNANME=@p_USER_LOGIN

IF(EXISTS(SELECT * FROM dbo.CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID AND BRANCH_TYPE = 'HS'))
BEGIN
IF(@p_DEP_ID='' OR @p_DEP_ID IS NULL)
	SET @p_DEP_ID=@DEP_LOGIN
END

SET @BRANCH_TYPE= (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_ID)  

INSERT @TRADE_REF
SELECT TRADE_ID  FROM dbo.PL_TRADE_REF WHERE REF_CODE IN (SELECT DEP_CODE FROM dbo.CM_DEPARTMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @AUTHOR_DVDM))

PRINT @BRANCH_TYPE
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  
 
 
 
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=@DEP_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(EXISTS(SELECT ID FROM dbo.PL_ROLE_DATA_CONFIG WHERE BRANCH_ID=@BRANCH_LOGIN AND DEP_ID=@DEP_LOGIN AND ROLE_TYPE='HANHCHINH'))  
BEGIN  
SET @IS_HANHCHINH=1  
END  
 
 
---PRINT @IS_CHUNG  
 
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 = @p_YEAR OR @p_YEAR ='' OR @p_YEAR 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 @IS_HANHCHINH=1 OR @IS_ALL=1 OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR ((@p_DEP_ID IN (SELECT DEP_ID FROM @AUTHOR_DVDM)) OR (NOT EXISTS (SELECT AUTH.DEP_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.DEP_ID=@p_DEP_ID) 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 IN (SELECT BRANCH_ID FROM @AUTHOR_DVDM)) OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))  ))  
OR  
(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID AND (@p_BRANCH_ID IN (SELECT BRANCH_ID FROM @AUTHOR_DVDM) OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR @IS_HANHCHINH=1 OR @IS_ALL=1  OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) OR (NOT EXISTS (SELECT AUTH.BRANCH_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.BRANCH_ID=@p_BRANCH_ID) AND PM.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))) )  

OR
( 
	EXISTS (SELECT * FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='TRADE_USER_VIEW' AND BRANCH_ID=@p_USER_LOGIN AND DEP_ID=PT.GOODS_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  
PRINT @P_REQDT_TYPE
IF(@P_REQDT_TYPE='I')  
BEGIN  
	IF(@P_PLAN_ID IS NOT NULL AND @P_PLAN_ID <>'')  
	BEGIN  
 -- PAGING 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)
      +
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_DOC_DT DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.TRADE_ID = DT.TRADE_ID)
      +
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_TRANSFER DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.FR_TRADE_ID = DT.TRADE_ID)
      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)
      -
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_TRANSFER DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.FR_TRADE_ID = DT.TRADE_ID) AS AMT_REMAIN,  
		ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)
      -
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_DOC_DT DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.TRADE_ID = DT.TRADE_ID)
      -
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_TRANSFER DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.FR_TRADE_ID = DT.TRADE_ID)
    AS AMT_REMAIN_ETM,
		CB.BRANCH_CODE,'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,
		PM.PLAN_TYPE_ID, CG.GD_TYPE_ID AS TYPE_NS,ISNULL(PM.YEAR,'') AS YEAR,
		CB.BRANCH_NAME + CASE WHEN CD.DEP_NAME IS NOT NULL THEN ' - ' + CD.DEP_NAME ELSE '' END AS BRANCH_NAME,TGT.BUDGET_TYPE
-- SELECT END
	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.TR_GOODSTYPE TGT ON TGT.GD_ID = CG.GD_ID
 
		LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=PM.COST_ID  
		LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=DM.DVDM_ID  
		LEFT JOIN dbo.CM_DVDM_KHOI DMKH ON DMKH.DVDM_ID = DM.DVDM_ID
		LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=DMKH.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.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 (PM.YEAR = @p_YEAR OR @p_YEAR ='' OR @p_YEAR IS NULL )

		AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')  
		AND(PM.DEPT_ID= @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID='')
		AND(DM.DVDM_ID = @p_DVDM_ID OR @p_DVDM_ID IS NULL OR @p_DVDM_ID = '')
		AND(PM.PLAN_TYPE_ID = @p_PLAN_TYPE_ID OR @p_PLAN_TYPE_ID IS NULL OR @p_PLAN_TYPE_ID = '') 
		AND(CG.GD_TYPE_ID = @p_TYPE_NS OR @p_TYPE_NS IS NULL OR @p_TYPE_NS = '')

		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 ='') 
		AND DT.RECORD_STATUS='1'
-- PAGING END
	END  
	ELSE  
	BEGIN
-- PAGING BEGIN
		SELECT DISTINCT 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)
      +
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_DOC_DT DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.TRADE_ID = DT.TRADE_ID)
      +
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_TRANSFER DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.FR_TRADE_ID = DT.TRADE_ID)
      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)
      -
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_TRANSFER DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.FR_TRADE_ID = DT.TRADE_ID) AS AMT_REMAIN,
		ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)
      -
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
        
      FROM dbo.PL_REQUEST_DOC_DT DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.TRADE_ID = DT.TRADE_ID)
      -
      (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
      FROM dbo.PL_REQUEST_TRANSFER DDT
      LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
      WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE')
      AND DDT.FR_TRADE_ID = DT.TRADE_ID)
    AS AMT_REMAIN_ETM,
		CB.BRANCH_CODE,'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,
		PM.PLAN_TYPE_ID, CG.GD_TYPE_ID AS TYPE_NS,ISNULL(PM.YEAR,'') AS YEAR,
		CB.BRANCH_NAME + CASE WHEN CD.DEP_NAME IS NOT NULL AND CB.BRANCH_TYPE = 'HS' THEN ' - ' + CD.DEP_NAME ELSE '' END AS BRANCH_NAME,TGT.BUDGET_TYPE
-- SELECT END
	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.TR_GOODSTYPE TGT ON TGT.GD_ID = CG.GD_ID
 
		LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=PM.COST_ID  
		LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=DM.DVDM_ID  
		LEFT JOIN dbo.CM_DVDM_KHOI DMKH ON DMKH.DVDM_ID = DM.DVDM_ID
		LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=DMKH.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 @IS_HANHCHINH=1 OR @IS_ALL=1 OR DT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR ((@p_DEP_ID IN (SELECT DEP_ID FROM @AUTHOR_DVDM)) OR (NOT EXISTS (SELECT AUTH.DEP_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.DEP_ID=@p_DEP_ID) 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 IN (SELECT BRANCH_ID FROM @AUTHOR_DVDM)) OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR DT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))  ))  
		OR  
		(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID AND (@p_BRANCH_ID IN (SELECT BRANCH_ID FROM @AUTHOR_DVDM) OR DT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR @IS_HANHCHINH=1 OR @IS_ALL=1  OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) OR (NOT EXISTS (SELECT AUTH.BRANCH_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.BRANCH_ID=@p_BRANCH_ID) AND PM.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))) )  
		OR
		( 
			EXISTS (SELECT * FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='TRADE_USER_VIEW' AND BRANCH_ID=@p_USER_LOGIN AND DEP_ID=DT.GOODS_ID)
		)
		)  
  
		AND (PM.YEAR = @p_YEAR OR @p_YEAR ='' OR @p_YEAR IS NULL )

		--AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')  
		--AND(PM.DEPT_ID= @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID='')
		AND(DM.DVDM_ID = @p_DVDM_ID OR @p_DVDM_ID IS NULL OR @p_DVDM_ID = '')
		AND(PM.PLAN_TYPE_ID = @p_PLAN_TYPE_ID OR @p_PLAN_TYPE_ID IS NULL OR @p_PLAN_TYPE_ID = '') 
		AND(CG.GD_TYPE_ID = @p_GD_TYPE_ID OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
		AND(CG.GD_TYPE_ID = @p_TYPE_NS OR @p_TYPE_NS IS NULL OR @p_TYPE_NS = '')
 
		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 ='')  
		AND DT.RECORD_STATUS='1'
-- PAGING END
	END  
END  
ELSE  
BEGIN
	DECLARE @BRANCH_CODE VARCHAR(15), @BRANCH_NAME NVARCHAR(200), @DEP_CODE VARCHAR(15), @DEP_NAME NVARCHAR(200)
	SET @BRANCH_CODE = (SELECT BRANCH_CODE FROM dbo.CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID)
	SET @BRANCH_NAME = (SELECT BRANCH_NAME FROM dbo.CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID)
	SET @DEP_CODE = (SELECT DEP_CODE FROM dbo.CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
	SET @DEP_NAME = (SELECT DEP_NAME FROM dbo.CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
-- PAGING 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,  
		@BRANCH_CODE AS BRANCH_CODE,  
		ISNULL(@BRANCH_NAME,'') + ISNULL(' - ' + @DEP_NAME,'') AS BRANCH_NAME, 
		--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 AS DEP_CODE, @DEP_NAME AS DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID AS TYPE_NS,'' AS YEAR,TGT.BUDGET_TYPE
-- SELECT END
	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  
		LEFT JOIN dbo.TR_GOODSTYPE TGT ON TGT.GD_ID = CG.GD_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 RIGHT (CG.GD_CODE,4)=YEAR (GETDATE())
		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 ='')  
		AND NOT EXISTS(SELECT PM.PLAN_ID FROM dbo.PL_MASTER PM  
		LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.PLAN_ID=PM.PLAN_ID
		LEFT JOIN dbo.CM_GOODS CMG ON CMG.GD_ID=PT.GOODS_ID
		LEFT JOIN CM_GOODS CI ON CI.GD_CODE= REPLACE(CMG.GD_CODE,'.I.','.O.')
		WHERE (PT.GOODS_ID =CG.GD_ID OR CI.GD_ID=CG.GD_ID)  
		AND (PM.YEAR = @p_YEAR OR @p_YEAR ='' OR @p_YEAR 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 @IS_HANHCHINH=1 OR @IS_ALL=1 OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR ((@p_DEP_ID IN (SELECT DEP_ID FROM @AUTHOR_DVDM)) OR (NOT EXISTS (SELECT AUTH.DEP_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.DEP_ID=@p_DEP_ID) 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 IN (SELECT BRANCH_ID FROM @AUTHOR_DVDM)) OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))  ))  
		OR  
		(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID AND (@p_BRANCH_ID IN (SELECT BRANCH_ID FROM @AUTHOR_DVDM) OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR @IS_HANHCHINH=1 OR @IS_ALL=1  OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) OR (NOT EXISTS (SELECT AUTH.BRANCH_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.BRANCH_ID=@p_BRANCH_ID) AND PM.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))) )  

		OR
		( 
			EXISTS (SELECT * FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='TRADE_USER_VIEW' AND BRANCH_ID=@p_USER_LOGIN AND DEP_ID=PT.GOODS_ID)
		))
		)
-- PAGING END
	END  
END -- PAGING