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

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,FG.GD_CODE AS FR_GD_CODE,FG.GD_NAME AS FR_GD_NAME,TG.GD_CODE AS TO_GD_CODE,TG.GD_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.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 dbo.PL_TRADEDETAIL 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,FG.GD_CODE AS FR_GD_CODE,FG.GD_NAME AS FR_GD_NAME,TG.GD_CODE AS TO_GD_CODE,TG.GD_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.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 dbo.PL_TRADEDETAIL 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,FG.GD_CODE AS FR_GD_CODE,FG.GD_NAME AS FR_GD_NAME,TG.GD_CODE AS TO_GD_CODE,TG.GD_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.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 dbo.PL_TRADEDETAIL 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'