ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_ById]  
@P_REQ_ID varchar(15)  
AS  
 
DECLARE @STEP_KT_PROCESS_ID VARCHAR(1)  
SET @STEP_KT_PROCESS_ID= (SELECT MAX(ID) FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='KT')  
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)  
)  
 
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)  
SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF 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)  
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 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)  
SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF 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_TRANS 
FER WHERE REQ_DOC_ID=@p_REQ_ID))  
END  
 
 
 
DECLARE @DV_QLNS NVARCHAR(1000)  
DECLARE @TB_DV TABLE ( ID INT IDENTITY NOT NULL PRIMARY KEY,DV_QL_NS NVARCHAR(1000))  
INSERT INTO @TB_DV SELECT B.TLFullName +N' chấp thuận '+ ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS TEN_DVCM FROM PL_REQUEST_COSTCENTER A  
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME  
WHERE REQ_ID =@P_REQ_ID  
SET @DV_QLNS = (SELECT DV_QL_NS FROM @TB_DV WHERE ID =1) + ISNULL(' , ' + (SELECT DV_QL_NS FROM @TB_DV WHERE ID =2) ,'')+ ISNULL( ', '+ (SELECT DV_QL_NS FROM @TB_DV WHERE ID =3),'') + ISNULL(', '+ (SELECT DV_QL_NS FROM @TB_DV WHERE ID =4),'')  
DECLARE @TABLE1 TABLE (  
STT INT,  
GD_NAME NVARCHAR(200),  
AMT_APP DECIMAL(18, 2),  
AMT_EXE DECIMAL(18, 2),  
AMT_REMAIN DECIMAL(18, 2),  
TOTAL_AMT DECIMAL(18, 2),  
TOTAL_AMT_FN DECIMAL(18, 2),  
CHECKER_NAME NVARCHAR(200),  
XN_NAME VARCHAR(15)  
)  
INSERT INTO @TABLE1(STT,GD_NAME,AMT_APP,AMT_EXE,AMT_REMAIN,TOTAL_AMT,TOTAL_AMT_FN,CHECKER_NAME,XN_NAME)  
SELECT ROW_NUMBER() OVER (ORDER BY CG.GD_NAME) AS STT ,CG.GD_NAME,  
ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) AS AMT_APP,  
ISNULL(PT.AMT_ETM,0) AS AMT_EXE,  
ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) - ISNULL(PT.AMT_ETM,0) AS AMT_REMAIN,  
SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT,  
ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) -ISNULL(PT.AMT_ETM,0) -SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT_FN,  
CASE  
WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName  
 
ELSE N''  
END AS CHECKER_NAME,  
 
'' AS XN_NAME  
FROM  
dbo.PL_REQUEST_DOC_DT PLDT  
 
LEFT JOIN @listTRADE PT ON PT.TRADE_ID=PLDT.TRADE_ID  
LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PLDT.PLAN_ID  
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID  
LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLDT.CHECKER_ID  
LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLDT.REQ_ID  
WHERE PLDT.REQ_ID=@P_REQ_ID  
GROUP BY PT.GOODS_ID,PT.AMT_APP,PT.AMT_ETM,PT.AMT_RECEIVE_TF,PT.AMT_TF,CG.GD_NAME,PLDT.REQ_ID,USC.TLFullName,PL.PROCESS_ID  
 
DECLARE @TABLE TABLE (  
FR_AMT_APP DECIMAL(18,2),  
FR_AMT_REMAIN DECIMAL(18,2),  
TO_AMT_APP DECIMAL(18,2),  
TO_AMT_EXE DECIMAL(18,2),  
TO_AMT_REMAIN DECIMAL(18,2),  
TOTAL_AMT DECIMAL(18,2),  
FR_AMT_APP_FINAL DECIMAL(18,2),  
FR_AMT_REMAIN_FINAL DECIMAL(18,2),  
TO_AMT_APP_FINAL DECIMAL(18,2),  
TOTAL_AMT_SIGN DECIMAL(18,2),  
TOTAL_AMT_SIGN_FN DECIMAL(18,2)  
)  
INSERT INTO @TABLE(FR_AMT_APP,FR_AMT_REMAIN, TO_AMT_APP,TO_AMT_EXE, TO_AMT_REMAIN,  
TOTAL_AMT, FR_AMT_APP_FINAL,FR_AMT_REMAIN_FINAL,TO_AMT_APP_FINAL,  
TOTAL_AMT_SIGN,TOTAL_AMT_SIGN_FN)  
SELECT ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0) AS FR_AMT_APP,  
ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0)-ISNULL(PTF.AMT_ETM,0) AS FR_AMT_REMAIN,  
ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) AS TO_AMT_APP,  
ISNULL(PTT.AMT_ETM,0) AS TO_AMT_EXE,  
ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) -ISNULL(PTT.AMT_ETM,0) AS TO_AMT_REMAIN,  
ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_AMT,  
ISNULL(PTF.AMT_APP, 0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0) - ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_APP_FINAL,  
ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0)-ISNULL(PTF.AMT_ETM,0) - ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_REMAIN_FINAL,  
ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) +ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS TO_AMT_APP_FINAL,  
ISNULL(PLDT.TOTAL_AMT, 0) AS TOTAL_AMT_SIGN, -- Số tiền trình sử dụng  
ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) -ISNULL(PTT.AMT_ETM,0) + ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) - PLDT.TOTAL_AMT AS TOTAL_AMT_SIGN_FN  
FROM dbo.PL_REQUEST_TRANSFER PLTF  
LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID  
LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID  
LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID  
LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID  
LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'  
LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID  
LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID  
LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID  
LEFT JOIN  
(  
SELECT GOODS_ID,REQ_ID, SUM(TOTAL_AMT) AS TOTAL_AMT FROM  
dbo.PL_REQUEST_DOC_DT  
WHERE REQ_ID=@P_REQ_ID  
GROUP BY GOODS_ID,REQ_ID ) PLDT ON PLDT.REQ_ID = PL.REQ_ID AND PLDT.GOODS_ID = PLTF.TO_GOOD_ID  
WHERE PLTF.REQ_DOC_ID=@P_REQ_ID  
 
SELECT TOP 1 RD.REQ_ID,  
RD.REQ_CODE,  
RD.REQ_NAME,  
RD.REQ_DT,  
RD.REQ_TYPE,  
RD.REQ_CONTENT,  
RD.REQ_REASON,  
RD.BRANCH_ID,  
(SELECT SUM(TOTAL_AMT)  
FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID ) AS TOTAL_AMT,  
RD.NOTES,  
RD.RECORD_STATUS,  
RD.MAKER_ID,  
RD.CREATE_DT,  
RD.AUTH_STATUS,  
RD.CHECKER_ID,  
CASE WHEN (SELECT ISNULL(FORMAT(RD.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')) <>'' THEN N'Đã chấp thuận '+ ISNULL(FORMAT(RD.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS APPROVE_DT,  
CASE WHEN (SELECT ISNULL(FORMAT(PKT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')) <> '' THEN N'Đã chấp thuận '+ ISNULL(FORMAT(PKT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') + CHAR (10) +  
(SELECT PROCESS_DESC FROM PL_PROCESS WHERE PROCESS_ID ='KT' AND REQ_ID =@P_REQ_ID AND NOTES =N'Trưởng đơn vị đã phê duyệt' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID =@P_REQ_ID AND PROCESS_ID ='KT') )  
ELSE '' END AS APPROVE_DT_KT,  
RD.PROCESS_ID,  
RD.DVDM_APP_ID,  
RD.REQ_PARENT_ID,  
RD.BRANCH_FEE,  
RD.EFFEC_DT,  
RD.IS_BACKDAY,  
RD.DEP_ID,  
RD.BASED_CONTENT,  
BR.BRANCH_NAME AS BRANCH_FEE_NAME,  
RD.DEP_FEE, CASE WHEN RD.NOTES IS NOT NULL AND RD.NOTES <> '' THEN RD.NOTES ELSE BR.BRANCH_NAME END AS BRANCH_NAME,BR.BRANCH_CODE,  
(SELECT SUM(AMT_APP) FROM @TABLE1) AS TOTAL_AMT_APP,  
(SELECT SUM(AMT_EXE) FROM @TABLE1) AS TOTAL_AMT_EXE,  
(SELECT SUM(AMT_REMAIN) FROM @TABLE1) AS TOTAL_AMT_REMAIN,  
--(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID) - (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE='O') AS AMT_IN,  
(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID) AS AMT_IN,  
(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID) - ISNULL((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID ),0) AS AMT_IN_TKH,  
(SELECT SUM(TOTAL_AMT_FN) FROM @TABLE1) AS TOTAL_AMT_ETM,  
CASE  
WHEN NOT EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(0 AS BIT)  
WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(1 AS BIT)  
END AS IS_VCCB,  
CASE  
WHEN NOT EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(1 AS BIT)  
WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(0 AS BIT)  
END AS IS_OTHER,  
 
(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE='O') AS AMT_OUT,  
(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID) AS AMT_TRANSFER, -- Số tiền nhận điều chuyển  
DAY(RD.REQ_DT) AS DATE,  
MONTH(RD.REQ_DT) AS MONTH,  
YEAR(RD.REQ_DT) AS YEAR,  
--CASE WHEN DP.DEP_NAME IS NULL THEN BR.BRANCH_NAME ELSE DP.DEP_NAME END AS BRANCH_DEP,  
--'' BRANCH_DEP,  
DP.DEP_NAME AS BRANCH_DEP ,  
--BR.BRANCH_NAME AS BRANCH_NAME,  
CASE WHEN PRC.ID IS NULL THEN UPRD.TLFullName ELSE UPRC.TLFullName END AS TDV_NAME,  
UTGD.TLFullName AS TGD_NAME,  
UHDQT.TLFullName AS HDQT_NAME,  
UPKT.TLFullName AS PKT_NAME,  
RD.IS_CHECKALL,RD.BASED_CONTENT,RD.PL_BASED_ID, '' AS PL_BASED_CONTENT, '' AS PL_BASED_CODE,  
CASE WHEN DP_C.DEP_NAME IS NULL THEN BR_C.BRANCH_NAME ELSE DP_C.DEP_NAME END AS DV_CREATE, (SELECT @DV_QLNS) AS DV_QL_CONFIRM,  
--UPTC.TLFullName + N' Đã chấp thuận '+ ISNULL(FORMAT(PTC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS TC_CONFIRM,  
'' AS TC_CONFIRM,  
UPTC.TLFullName + N' Đã chấp thuận '+ ISNULL(FORMAT(PTC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS TAI_CHINH_CONFIRM,  
CASE WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID = @p_REQ_ID AND CURRENCY <>'VND') THEN (SELECT TOP 1 CURRENCY FROM PL_REQUEST_DOC_DT WHERE REQ_ID = @p_REQ_ID) ELSE 'VND' END AS CURR  
,(SELECT FORMAT(SUM(ISNULL(FR_AMT_APP, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_APP,  
(SELECT FORMAT(SUM(ISNULL(FR_AMT_REMAIN, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_REMAIN,  
(SELECT FORMAT(SUM(ISNULL(TO_AMT_APP, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_APP,  
(SELECT FORMAT(SUM(ISNULL(TO_AMT_EXE, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_EXE,  
(SELECT FORMAT(SUM(ISNULL(TO_AMT_REMAIN, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_REMAIN,  
(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT,  
(SELECT FORMAT(SUM(ISNULL(FR_AMT_APP_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_APP_FINAL,  
(SELECT FORMAT(SUM(ISNULL(FR_AMT_REMAIN_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_REMAIN_FINAL,  
(SELECT FORMAT(SUM(ISNULL(TO_AMT_APP_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_APP_FINAL,  
(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT_SIGN, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT_SIGN,  
(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT_SIGN_FN, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT_SIGN_FN,  
CASE WHEN RD.NOTES IS NOT NULL AND RD.NOTES <> '' THEN RD.NOTES ELSE ISNULL(DP.DEP_NAME,'') +' '+ ISNULL(BR.BRANCH_NAME,'') END AS DV_CP  
FROM dbo.PL_REQUEST_DOC RD  
LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID=RD.BRANCH_FEE  
LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=RD.DEP_FEE  
LEFT JOIN dbo.TL_USER UD ON UD.TLNANME=RD.CHECKER_ID  
LEFT JOIN dbo.PL_REQUEST_PROCESS PRC ON PRC.REQ_ID=RD.REQ_ID AND PRC.PROCESS_ID='DVC'  
LEFT JOIN dbo.PL_REQUEST_PROCESS PRD ON PRD.REQ_ID=RD.REQ_ID AND PRD.PROCESS_ID='APPNEW'  
LEFT JOIN dbo.TL_USER UPRC ON UPRC.TLNANME=PRC.CHECKER_ID  
LEFT JOIN dbo.TL_USER UPRD ON UPRD.TLNANME=PRD.CHECKER_ID  
LEFT JOIN dbo.PL_REQUEST_PROCESS PRTGD ON PRTGD.REQ_ID=RD.REQ_ID AND PRTGD.PROCESS_ID LIKE 'TGD%'  
LEFT JOIN dbo.TL_USER UTGD ON UTGD.TLNANME=PRTGD.CHECKER_ID  
LEFT JOIN dbo.PL_REQUEST_PROCESS PRHDQT ON PRHDQT.REQ_ID=RD.REQ_ID AND PRHDQT.PROCESS_ID='HDQT'  
LEFT JOIN dbo.TL_USER UHDQT ON UHDQT.TLNANME=PRHDQT.CHECKER_ID  
LEFT JOIN dbo.PL_REQUEST_PROCESS PKT ON PKT.REQ_ID=RD.REQ_ID AND PKT.PROCESS_ID='KT'  
LEFT JOIN dbo.PL_REQUEST_PROCESS PTC ON PTC.REQ_ID=RD.REQ_ID AND PTC.PROCESS_ID='TC'  
LEFT JOIN dbo.TL_USER UPKT ON UPKT.TLNANME=PKT.CHECKER_ID  
LEFT JOIN dbo.TL_USER UPTC ON UPTC.TLNANME=PTC.CHECKER_ID  
LEFT JOIN CM_BRANCH BR_C ON RD.BRANCH_CREATE = BR_C.BRANCH_ID  
LEFT JOIN CM_DEPARTMENT DP_C ON RD.DEP_CREATE = DP_C.DEP_ID  
 
-- điều chuyển ngân sách  
LEFT JOIN dbo.PL_REQUEST_TRANSFER PLTF ON PLTF.REQ_DOC_ID = RD.REQ_ID  
LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID  
LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID  
LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID  
LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID  
LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'  
LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID  
LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID  
LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID  
 
 
-- DON VI QUAN LY NGAN SACH  
WHERE RD.REQ_ID=@P_REQ_ID  
ORDER BY RD.REQ_ID