ALTER PROCEDURE [dbo].[PL_REQUEST_TRANSFER_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)
	)

	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 TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_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
@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 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
LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=A.TO_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 
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
LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=A.TO_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 
 --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
LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=A.TO_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 
 --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




 --EXEC TR_PO_DETAIL_ById 'TRPM00000000001', 'DV0001'