ALTER PROCEDURE dbo.PL_TRADEDETAIL_ById_new_V2
	@p_BRANCH_ID	varchar(15)  = NULL,
	@p_DEPT_ID	varchar(15)  = NULL,
	@p_GD_NAME NVARCHAR(200)=NULL,
	@p_GD_CODE VARCHAR(20)= NULL,
	@p_COST_ID VARCHAR(20) = NULL,
	@p_PL_TYPE_ID VARCHAR(20) = NULL,
	@p_GOOD_TYPE_ID VARCHAR(15) = NULL
AS
BEGIN -- PAGING
	-- PAGING BEGIN

	SELECT A.TRADE_ID,
           A.PLAN_ID,
           A.GOODS_ID,
           A.GOODS_CODE,
           A.GOODS_NAME,
           A.GOODS_TYPE,
           A.UNIT_ID,
           A.M1,
           A.M2,
           A.M3,
           A.M4,
           A.M5,
           A.M6,
           A.M7,
           A.M8,
           A.M9,
           A.M10,
           A.M11,
           A.M12,
           	ISNULL(A.QUANTITY,0) AS QUANTITY,
          	ISNULL( A.QUANTITY_EXE,0) AS QUANTITY_EXE,
           A.PRICE,
           A.START_DT_AMORT,
           A.MONTH_AMORT,
           A.END_DT_AMORT,
           A.RATE_AMORT,
           A.NOTES,
           A.RECORD_STATUS,
           A.MAKER_ID,
           A.CREATE_DT,
           A.AUTH_STATUS,
           A.CHECKER_ID,
           A.APPROVE_DT,
		   CC.DVDM_NAME AS COST_NAME,
		   PT.PLAN_TYPE_NAME,
       ISNULL(A.QUANTITY_ETM,0) AS QUANTITY_ETM,
       ISNULL( A.AMT_APP,0) AS AMT_APP,
       ISNULL( A.AMT_EXE,0) AS AMT_EXE,
       ISNULL( A.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','SETTLMENT')
        AND DDT.TRADE_ID = A.TRADE_ID) AS AMT_ETM, 
		   ISNULL( A.AMT_TF,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','SETTLMENT')
        AND DDT.FR_TRADE_ID = A.TRADE_ID) AS AMT_TF, 
		   ISNULL( A.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
			CG.GD_NAME,CG.GD_CODE,B.UNIT_NAME,
			ISNULL(A.QUANTITY,0) - ISNULL(A.QUANTITY_EXE,0) AS QUANTITY_REMAIN, 
			ISNULL(A.QUANTITY,0)-ISNULL(A.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM, 
			ISNULL(A.AMT_APP,0) + ISNULL( A.AMT_RECEIVE_TF,0)- ISNULL( A.AMT_TF,0)- ISNULL(A.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','SETTLMENT')
        AND DDT.FR_TRADE_ID = A.TRADE_ID) AS AMT_REMAIN, 
			ISNULL(A.AMT_APP,0) + ISNULL( A.AMT_RECEIVE_TF,0)- ISNULL( A.AMT_TF,0)-ISNULL(A.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','SETTLMENT')
        AND DDT.TRADE_ID = A.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','SETTLMENT')
        AND DDT.FR_TRADE_ID = A.TRADE_ID) AS AMT_REMAIN_ETM
	-- SELECT END

	FROM PL_TRADEDETAIL A
	LEFT JOIN PL_MASTER PLM ON PLM.PLAN_ID = A.PLAN_ID
	LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=A.GOODS_ID
	LEFT JOIN CM_UNIT B ON CG.UNIT_ID = B.UNIT_ID
	LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=PLM.COST_ID
	LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PLM.PLAN_TYPE_ID
	WHERE PLM.BRANCH_ID = @p_BRANCH_ID AND PLM.DEPT_ID = @p_DEPT_ID 
	AND (CG.GD_NAME LIKE N'%' + @p_GD_NAME +N'%' OR @p_GD_NAME='' OR @p_GD_NAME IS NULL)
	AND (CG.GD_CODE LIKE N'%' + @p_GD_CODE +N'%' OR @p_GD_CODE='' OR @p_GD_CODE IS NULL)
	AND (PLM.COST_ID = @p_COST_ID OR @p_COST_ID IS NULL OR @p_COST_ID = '')
	AND (PLM.PLAN_TYPE_ID = @p_PL_TYPE_ID OR @p_PL_TYPE_ID IS NULL OR @p_PL_TYPE_ID = '')
	AND (CG.GD_TYPE_ID = @p_GOOD_TYPE_ID OR @p_GOOD_TYPE_ID IS NULL OR @p_GOOD_TYPE_ID = '')
	AND CG.RECORD_STATUS ='1' ---2022912 KHI XEM CHI TIET KE HOACH, CHI LOAD NHUNG NGAN SACH CO RECORD_STATUS =1
	-- PAGING END

END -- PAGING