ALTER PROCEDURE dbo.PL_REQUEST_TRANSFER_DT_ByID
@P_REQ_ID varchar(15),
@p_TYPE VARCHAR(15),
@p_TLNAME VARCHAR(20)
AS

DECLARE @listTRADE TABLE
	(
		GOODS_ID VARCHAR(20),
		TRADE_ID VARCHAR(20),
		PLAN_ID VARCHAR(20),
		AMT_APP DECIMAL(18,2),
		AMT_EXE DECIMAL (18,2),
		AMT_ETM DECIMAL (18,2),
		AMT_TF DECIMAL (18,2),
		AMT_RECEIVE_TF DECIMAL (18,2),
		NOTES NVARCHAR(1000),
    AMT_ETM_TMP DECIMAL(18,2)
	)

--	IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID='APPROVE'))
--	BEGIN
--		IF(EXISTS(SELECT * FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID))
--		BEGIN
--			INSERT INTO @listTRADE
--			( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES)
--			SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID
--		END
--		ELSE
--		BEGIN
--			INSERT INTO @listTRADE
--			( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES)
--			SELECT PT.GOODS_ID,PT.TRADE_ID,PT.PLAN_ID,PT.AMT_APP,PT.AMT_EXE,PT.AMT_ETM-PLDT.TOTAL_AMT,PT.AMT_TF-PLFT.TOTAL_AMT,PT.AMT_RECEIVE_TF-PLTT.TOTAL_AMT, PT.NOTES FROM dbo.PL_TRADEDETAIL PT
--			LEFT JOIN 
--			(
--			SELECT TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
--			dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
--			GROUP BY TRADE_ID
--			) PLDT ON PLDT.TRADE_ID = PT.TRADE_ID
--			LEFT JOIN 
--			(
--			SELECT FR_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
--			dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
--			GROUP BY FR_TRADE_ID
--			) PLFT ON PLFT.FR_TRADE_ID = PT.TRADE_ID
--
--			LEFT JOIN 
--			(
--			SELECT TO_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
--			dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
--			GROUP BY TO_TRADE_ID
--			) PLTT ON PLTT.TO_TRADE_ID = PT.TRADE_ID
--			WHERE (PT.TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR PT.TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
--		END
--	END
--	ELSE
--	BEGIN
--			INSERT INTO @listTRADE
--			( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES)
--			SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES FROM dbo.PL_TRADEDETAIL WHERE (TRADE_ID IN (SELECT TO_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID) OR TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
--	END
DECLARE @DEP_FEE_ID VARCHAR(15), @DEP_FEE_NAME NVARCHAR(1000), @BRANCH_FEE_ID VARCHAR(15), @BRANCH_FEE_NAME NVARCHAR(1000), @DVCCP NVARCHAR(1000)
SET @DEP_FEE_ID =(SELECT DEP_FEE FROM PL_REQUEST_DOC WHERE REQ_ID =@P_REQ_ID)
SET @DEP_FEE_NAME =(SELECT DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID =@DEP_FEE_ID)
SET @BRANCH_FEE_ID =(SELECT BRANCH_FEE FROM PL_REQUEST_DOC WHERE REQ_ID =@P_REQ_ID)
SET @BRANCH_FEE_NAME =(SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_FEE_ID)
IF(@BRANCH_FEE_ID ='DV0001')
BEGIN
	SET @DVCCP =@BRANCH_FEE_NAME + ' - '+ @DEP_FEE_NAME
END
ELSE
BEGIN
	SET @DVCCP =@BRANCH_FEE_NAME;
END
  IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID IN ('','SIGN','APPNEW','REJECT')))
      BEGIN
  			INSERT INTO @listTRADE
  			( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
  			SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,
        (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 = PL.TRADE_ID
        AND DOC.REQ_ID <> @p_REQ_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 = PL.TRADE_ID
        AND DOC.REQ_ID <> @p_REQ_ID)
        AS AMT_ETM_TMP
        FROM dbo.PL_TRADEDETAIL PL
        WHERE (TRADE_ID IN (SELECT TO_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID)
        OR TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
      END
      ELSE
			BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
          SELECT * FROM
          (
          SELECT DISTINCT GOODS_ID,TRADE_ID,PLAN_ID,ISNULL(AMT_APP,0) AS AMT_APP,ISNULL(AMT_EXE,0) AS AMT_EXE,ISNULL(AMT_ETM,0) AS AMT_ETM,ISNULL(AMT_TF,0) AS AMT_TF,ISNULL(AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,'' NOTES,ISNULL(AMT_ETM_TMP,0) AS AMT_ETM_TMP FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
          UNION ALL
          SELECT DISTINCT FR_GOOD_ID GOODS_ID,FR_TRADE_ID TRADE_ID,FR_PLAN_ID PLAN_ID,ISNULL(FR_AMT_APP,0) AMT_APP,ISNULL(FR_AMT_EXE,0) AMT_EXE,ISNULL(FR_AMT_ETM,0) AMT_ETM,ISNULL(FR_AMT_TF,0) AMT_TF,ISNULL(FR_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(FR_AMT_ETM_TMP,0) AMT_ETM_TMP FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
          UNION ALL
          SELECT DISTINCT TO_GOOD_ID GOODS_ID,TO_TRADE_ID TRADE_ID,TO_PLAN_ID PLAN_ID,ISNULL(TO_AMT_APP,0) AMT_APP,ISNULL(TO_AMT_EXE,0) AMT_EXE,ISNULL(TO_AMT_ETM,0) AMT_ETM,ISNULL(TO_AMT_TF,0) AMT_TF,ISNULL(TO_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(TO_AMT_ETM_TMP,0) AMT_ETM_TMP FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
          ) T GROUP BY GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP
			END

DECLARE
@COST_ID TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID TABLE (
	DVDM_ID VARCHAR(15)
)

DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID  VARCHAR(15),@ROLE VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0

SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)

IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID))
		SET @IS_ALL=1

SET @ROLE=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)

	INSERT INTO @COST_ID
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID

	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID

IF(@p_TYPE='DVKD')
BEGIN
	SELECT DISTINCT A.*,FB.BRANCH_CODE AS FR_BRANCH_CODE, FB.BRANCH_NAME AS FR_BRANCH_NAME,TB.BRANCH_CODE AS TO_BRANCH_CODE,
		ISNULL(FG.GD_CODE,LF_DT.GOOD_CODE) AS FR_GD_CODE,ISNULL(FG.GD_NAME,LF_DT.GOOD_NAME) AS FR_GD_NAME,
		ISNULL(TG.GD_CODE,LT_DT.GOOD_CODE) AS TO_GD_CODE,ISNULL(TG.GD_NAME,LT_DT.GOOD_NAME) AS TO_GD_NAME,
		CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME,
		DMF.DVDM_CODE AS FR_DVDM_CODE, DMF.DVDM_NAME AS FR_DVDM_NAME, DMT.DVDM_CODE AS TO_DVDM_CODE, DMT.DVDM_NAME AS TO_DVDM_NAME,
		CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
		FD.DEP_NAME AS FR_DEP_NAME,FD.DEP_CODE AS FR_DEP_CODE,TD.DEP_NAME AS TO_DEP_NAME,TD.DEP_CODE AS TO_DEP_CODE,
		ISNULL(PLF.AMT_APP,0) AS FR_AMT_APP,
    ISNULL(PLF.AMT_APP,0) + ISNULL(PLF.AMT_RECEIVE_TF,0) - ISNULL(PLF.AMT_TF,0) - ISNULL(PLF.AMT_ETM,0) - ISNULL(PLF.AMT_ETM_TMP,0) AS FR_AMT_REMAIN_ETM,
    ISNULL(PLT.AMT_APP,0) AS TO_AMT_APP,
    ISNULL(PLT.AMT_APP,0) + ISNULL(PLT.AMT_RECEIVE_TF,0) - ISNULL(PLT.AMT_TF,0) - ISNULL(PLT.AMT_ETM,0) - ISNULL(PLT.AMT_ETM_TMP,0) AS TO_AMT_REMAIN_ETM,
		ISNULL(PLF.AMT_EXE,0) AS AMT_EXE, 
		ISNULL(PLF.AMT_ETM,0) AS AMT_ETM,
		ISNULL(PLF.AMT_TF,0) AS AMT_TF, 
		ISNULL(PLF.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
		ISNULL(PLF.AMT_APP,0) + ISNULL(PLF.AMT_RECEIVE_TF,0) - ISNULL(PLF.AMT_TF,0) - ISNULL(PLF.AMT_EXE,0) AS AMT_REMAIN,
		ISNULL(PLF.AMT_APP,0) + ISNULL(PLF.AMT_RECEIVE_TF,0) - ISNULL(PLF.AMT_TF,0) - ISNULL(PLF.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 = PLF.TRADE_ID
      AND DOC.REQ_ID <> A.REQ_DOC_ID)  AS AMT_REMAIN_ETM,
		ISNULL(PLF.AMT_APP,0) + ISNULL(PLF.AMT_RECEIVE_TF,0) - ISNULL(PLF.AMT_TF,0) - ISNULL(PLF.AMT_ETM,0) - ISNULL((SELECT  SUM(Temp.TOTAL_AMT) AS TOTAL_AMT 
		FROM dbo.PL_REQUEST_TRANSFER Temp WHERE
			Temp.REQ_DOC_ID=@P_REQ_ID AND Temp.FR_TRADE_ID= A.FR_TRADE_ID AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <> 'APPROVE' )
		GROUP BY Temp.FR_TRADE_ID
		),0)AS AMT_REMAIN_ETM_TF,
		ISNULL(PLT.AMT_APP,0) + ISNULL(PLT.AMT_RECEIVE_TF,0) - ISNULL(PLT.AMT_TF,0) - ISNULL(PLT.AMT_ETM,0) AS AMT_REMAIN_ETM_TO,
		GTF.GD_TYPE_NAME AS FR_GD_TYPE_NAME,GTF.GD_TYPE_CODE AS FR_GD_TYPE_CODE,GTF.GD_TYPE_ID AS FR_GD_TYPE_ID,
		GTT.GD_TYPE_NAME AS TO_GD_TYPE_NAME,GTT.GD_TYPE_CODE AS TO_GD_TYPE_CODE,GTT.GD_TYPE_ID AS TO_GD_TYPE_ID,
		 --GIANT 22/10/2021
		 --CASE WHEN A.FR_DEP_ID <> '' 
			--	THEN FD.DEP_NAME + N' - ' + FB.BRANCH_NAME 
			--ELSE
			--	FB.BRANCH_NAME END AS FR_BRANCH_NAME,
      CASE WHEN A.FR_PLAN_ID IS NOT NULL AND A.FR_PLAN_ID <> ''
      THEN ISNULL(AD1.DEP_NAME + ' - ','') + AC1.BRANCH_NAME
      ELSE ISNULL(FD.DEP_NAME + N' - ','') + FB.BRANCH_NAME END AS FR_BRANCH_NAME, --- LUCTV 25.11.2022 DON VI CHUYEN LOAD DON VI DANG QUAN LY NGAN SACH
			CASE 
				--WHEN A.TO_DEP_ID <> '' AND A.TO_BRN_ID ='DV0001' THEN TD.DEP_NAME + N' - ' + TB.BRANCH_NAME  --- LUCTV 14.11.2022 FIX CACH SHOW DON VI NHAN NEU HOI SO MOI SHOW PHONG BAN
				--WHEN A.TO_BRN_ID <> '' AND  (A.TO_DEP_ID ='' OR A.TO_BRN_ID <>'DV0001') THEN  TB.BRANCH_NAME --- LUCTV 14.11.2022 FIX CACH SHOW DON VI NHAN
		WHEN ISNULL(A.TO_TRADE_ID,'') <> '' THEN ISNULL(AD2.DEP_NAME + ' - ','') + AC2.BRANCH_NAME
		--WHEN ISNULL(A.TO_DEP_ID,'') ='' AND ISNULL(A.TO_BRN_ID,'') ='' THEN @DVCCP
		WHEN ISNULL(A.TO_TRADE_ID,'')='' THEN @DVCCP
		END AS TO_BRANCH_NAME,
		--A.FR_AMT_FINAL + A.TOTAL_AMT AS FR_AMT_REMAIN,
		--A.TO_AMT_FINAL - A.TOTAL_AMT AS TO_AMT_REMAIN
		A.FR_AMT_FINAL - A.TOTAL_AMT AS FR_AMT_REMAIN,
		A.TO_AMT_FINAL + A.TOTAL_AMT AS TO_AMT_REMAIN,
		FPM.YEAR AS fR_GD_YEAR, TPM.YEAR AS tO_GD_YEAR,
    ISNULL(AD1.DEP_NAME + ' - ','') + AC1.BRANCH_NAME AS FR_BRANCH_FEE_FULL
	FROM dbo.PL_REQUEST_TRANSFER A
	LEFT JOIN dbo.CM_BRANCH FB ON FB.BRANCH_ID=A.FR_BRN_ID
	LEFT JOIN dbo.CM_BRANCH TB ON TB.BRANCH_ID=A.TO_BRN_ID
	LEFT JOIN dbo.CM_DEPARTMENT FD ON FD.DEP_ID=A.FR_DEP_ID
	LEFT JOIN dbo.CM_DEPARTMENT TD ON TD.DEP_ID=A.TO_DEP_ID
	LEFT JOIN dbo.CM_GOODS FG ON FG.GD_ID=A.FR_GOOD_ID
	LEFT JOIN dbo.CM_GOODS TG ON TG.GD_ID=A.TO_GOOD_ID
	LEFT JOIN dbo.PL_IMPORT_DT LF_DT ON LF_DT.TRADE_ID=A.FR_TRADE_ID AND( LF_DT.TRADE_ID IS NOT NULL AND LF_DT.TRADE_ID<>'')
	LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=A.TO_TRADE_ID AND( LT_DT.TRADE_ID IS NOT NULL AND LT_DT.TRADE_ID<>'')
	LEFT JOIN dbo.CM_DVDM DMF ON DMF.DVDM_ID=A.FR_DVDM_ID
	LEFT JOIN dbo.CM_DVDM DMT ON DMT.DVDM_ID=A.TO_DVDM_ID
	LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.FR_KHOI_ID
	--INNER JOIN @listTRADE PLF ON PLF.TRADE_ID=A.FR_TRADE_ID
	--INNER JOIN @listTRADE PLT ON PLT.TRADE_ID=A.TO_TRADE_ID
	LEFT JOIN @listTRADE PLF ON PLF.TRADE_ID=A.FR_TRADE_ID
	LEFT JOIN @listTRADE PLT ON PLT.TRADE_ID=A.TO_TRADE_ID
	LEFT JOIN dbo.CM_GOODSTYPE GTF ON FG.GD_CODE LIKE '%.' + GTF.GD_TYPE_CODE +'.%'
	LEFT JOIN dbo.CM_GOODSTYPE GTT ON TG.GD_CODE LIKE '%.' + GTT.GD_TYPE_CODE +'.%'
	LEFT JOIN dbo.PL_MASTER FPM ON FPM.PLAN_ID = A.FR_PLAN_ID
	LEFT JOIN dbo.PL_MASTER TPM ON TPM.PLAN_ID = A.TO_PLAN_ID
	LEFT JOIN CM_BRANCH AC1 ON AC1.BRANCH_ID = FPM.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT AD1 ON AD1.DEP_ID = FPM.DEPT_ID
	LEFT JOIN CM_BRANCH AC2 ON AC2.BRANCH_ID = TPM.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT AD2 ON AD2.DEP_ID = TPM.DEPT_ID
	WHERE A.REQ_DOC_ID=@P_REQ_ID
    ORDER BY A.REQ_TRANSFER_ID
END
ELSE IF(@p_TYPE='PDTT')
BEGIN
	SELECT A.*,FB.BRANCH_CODE AS FR_BRANCH_CODE, FB.BRANCH_NAME AS FR_BRANCH_NAME,TB.BRANCH_CODE AS TO_BRANCH_CODE,
	TB.BRANCH_NAME AS TO_BRANCH_NAME,
	ISNULL(FG.GD_CODE,LF_DT.GOOD_CODE) AS FR_GD_CODE,ISNULL(FG.GD_NAME,LT_DT.GOOD_NAME) AS FR_GD_NAME,
	ISNULL(TG.GD_CODE,LT_DT.GOOD_CODE) AS TO_GD_CODE,ISNULL(TG.GD_NAME,LT_DT.GOOD_NAME) AS TO_GD_NAME,
	CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
	,DM.DVDM_CODE,DM.DVDM_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,FD.DEP_NAME AS FR_DEP_NAME,FD.DEP_CODE AS FR_DEP_CODE,TD.DEP_NAME AS TO_DEP_NAME,TD.DEP_CODE AS TO_DEP_CODE,
	(ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)) AS AMT_APP,ISNULL(PL.AMT_EXE,0) AS AMT_EXE, ISNULL(PL.AMT_ETM,0) AS AMT_ETM,ISNULL(PL.AMT_TF,0) AS AMT_TF, ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
	 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
	 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
	 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL((SELECT  SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_TRANSFER Temp WHERE
	 Temp.REQ_DOC_ID=@P_REQ_ID AND Temp.FR_TRADE_ID= A.FR_TRADE_ID AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <> 'APPROVE' )
	 GROUP BY Temp.FR_TRADE_ID
	 ),0)AS AMT_REMAIN_ETM_TF,GTF.GD_TYPE_NAME AS FR_GD_TYPE_NAME,GTF.GD_TYPE_CODE AS FR_GD_TYPE_CODE,GTF.GD_TYPE_ID AS FR_GD_TYPE_ID,
	 GTT.GD_TYPE_NAME AS TO_GD_TYPE_NAME,GTT.GD_TYPE_CODE AS TO_GD_TYPE_CODE,GTT.GD_TYPE_ID AS TO_GD_TYPE_ID
	FROM dbo.PL_REQUEST_TRANSFER A
	LEFT JOIN dbo.CM_BRANCH FB ON FB.BRANCH_ID=A.FR_BRN_ID
	LEFT JOIN dbo.CM_BRANCH TB ON TB.BRANCH_ID=A.TO_BRN_ID
	LEFT JOIN dbo.CM_DEPARTMENT FD ON FD.DEP_ID=A.FR_DEP_ID
	LEFT JOIN dbo.CM_DEPARTMENT TD ON TD.DEP_ID=A.TO_DEP_ID
	LEFT JOIN dbo.CM_GOODS FG ON FG.GD_ID=A.FR_GOOD_ID
	LEFT JOIN dbo.CM_GOODS TG ON TG.GD_ID=A.TO_GOOD_ID
	LEFT JOIN dbo.PL_IMPORT_DT LF_DT ON LF_DT.TRADE_ID=A.FR_TRADE_ID AND( LF_DT.TRADE_ID IS NOT NULL AND LF_DT.TRADE_ID<>'')
	LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=A.TO_TRADE_ID AND( LT_DT.TRADE_ID IS NOT NULL AND LT_DT.TRADE_ID<>'')
	LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.FR_DVDM_ID
	LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.FR_KHOI_ID
	LEFT JOIN @listTRADE PL ON PL.TRADE_ID=A.FR_TRADE_ID
	LEFT JOIN dbo.CM_GOODSTYPE GTF ON FG.GD_CODE LIKE '%.' + GTF.GD_TYPE_CODE +'.%'
	LEFT JOIN dbo.CM_GOODSTYPE GTT ON TG.GD_CODE LIKE '%.' + GTT.GD_TYPE_CODE +'.%'
	 WHERE A.REQ_DOC_ID=@P_REQ_ID 
   ORDER BY A.REQ_TRANSFER_ID
	 --AND (@ROLE='TGD' OR @IS_ALL=1 OR @ROLE='HDQT' OR EXISTS(SELECT * FROM @DVDM_ID WHERE DVDM_ID=A.FR_DVDM_ID OR DVDM_ID=A.FR_KHOI_ID OR
	 --A.TO_DVDM_ID=DVDM_ID OR A.TO_KHOI_ID=DVDM_ID
	 --))
END
ELSE IF(@p_TYPE='XLTT' OR @p_TYPE='TFJOB')
BEGIN
	SELECT A.*,FB.BRANCH_CODE AS FR_BRANCH_CODE, FB.BRANCH_NAME AS FR_BRANCH_NAME,TB.BRANCH_CODE AS TO_BRANCH_CODE,
	TB.BRANCH_NAME AS TO_BRANCH_NAME,
	ISNULL(FG.GD_CODE,LF_DT.GOOD_CODE) AS FR_GD_CODE,ISNULL(FG.GD_NAME,LT_DT.GOOD_NAME) AS FR_GD_NAME,
	ISNULL(TG.GD_CODE,LT_DT.GOOD_CODE) AS TO_GD_CODE,ISNULL(TG.GD_NAME,LT_DT.GOOD_NAME) AS TO_GD_NAME,
	CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
	,DM.DVDM_CODE,DM.DVDM_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,FD.DEP_NAME AS FR_DEP_NAME,FD.DEP_CODE AS FR_DEP_CODE,TD.DEP_NAME AS TO_DEP_NAME,TD.DEP_CODE AS TO_DEP_CODE,
	(ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)) AS AMT_APP,ISNULL(PL.AMT_EXE,0) AS AMT_EXE, ISNULL(PL.AMT_ETM,0) AS AMT_ETM,ISNULL(PL.AMT_TF,0) AS AMT_TF, ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
	 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
	 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
	 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL((SELECT  SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_TRANSFER Temp WHERE
	 Temp.REQ_DOC_ID=@P_REQ_ID AND Temp.FR_TRADE_ID= A.FR_TRADE_ID AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <> 'APPROVE' )
	 GROUP BY Temp.FR_TRADE_ID
	 ),0)AS AMT_REMAIN_ETM_TF,GTF.GD_TYPE_NAME AS FR_GD_TYPE_NAME,GTF.GD_TYPE_CODE AS FR_GD_TYPE_CODE,GTF.GD_TYPE_ID AS FR_GD_TYPE_ID,
	 GTT.GD_TYPE_NAME AS TO_GD_TYPE_NAME,GTT.GD_TYPE_CODE AS TO_GD_TYPE_CODE,GTT.GD_TYPE_ID AS TO_GD_TYPE_ID
	FROM dbo.PL_REQUEST_TRANSFER A
	LEFT JOIN dbo.CM_BRANCH FB ON FB.BRANCH_ID=A.FR_BRN_ID
	LEFT JOIN dbo.CM_BRANCH TB ON TB.BRANCH_ID=A.TO_BRN_ID
	LEFT JOIN dbo.CM_DEPARTMENT FD ON FD.DEP_ID=A.FR_DEP_ID
	LEFT JOIN dbo.CM_DEPARTMENT TD ON TD.DEP_ID=A.TO_DEP_ID
	LEFT JOIN dbo.CM_GOODS FG ON FG.GD_ID=A.FR_GOOD_ID
	LEFT JOIN dbo.CM_GOODS TG ON TG.GD_ID=A.TO_GOOD_ID
	LEFT JOIN dbo.PL_IMPORT_DT LF_DT ON LF_DT.TRADE_ID=A.FR_TRADE_ID AND( LF_DT.TRADE_ID IS NOT NULL AND LF_DT.TRADE_ID<>'')
	LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=A.TO_TRADE_ID AND( LT_DT.TRADE_ID IS NOT NULL AND LT_DT.TRADE_ID<>'')
	LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.FR_DVDM_ID
	LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.FR_KHOI_ID
	LEFT JOIN @listTRADE PL ON PL.TRADE_ID=A.FR_TRADE_ID
	LEFT JOIN dbo.CM_GOODSTYPE GTF ON FG.GD_CODE LIKE '%.' + GTF.GD_TYPE_CODE +'.%'
	LEFT JOIN dbo.CM_GOODSTYPE GTT ON TG.GD_CODE LIKE '%.' + GTT.GD_TYPE_CODE +'.%'
	 WHERE A.REQ_DOC_ID=@P_REQ_ID 
   ORDER BY A.REQ_TRANSFER_ID
	 --AND  (A.FR_BRN_ID=@BRANCH_ID AND( A.FR_DEP_ID=@DEP_ID OR A.FR_DEP_ID IS NULL OR A.FR_DEP_ID='')OR  @ROLE='TGD' OR @IS_ALL=1 OR @ROLE='HDQT')
END