Project

General

Profile

Download (31.2 KB)

IN TO TRINH.txt

Luc Tran Van, 03/18/2021 11:54 AM

 
1 
??
2 
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_ById]
3 
@P_REQ_ID varchar(15)
4 
AS
5
6 
	DECLARE  @STEP_KT_PROCESS_ID VARCHAR(1)
7 
	SET @STEP_KT_PROCESS_ID= (SELECT MAX(ID) FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='KT')
8 
	DECLARE @listTRADE TABLE
9 
			(
10 
				GOODS_ID VARCHAR(20),
11 
				TRADE_ID VARCHAR(20),
12 
				PLAN_ID VARCHAR(20),
13 
				AMT_APP DECIMAL(18,2),
14 
				AMT_EXE DECIMAL (18,2),
15 
				AMT_ETM DECIMAL (18,2),
16 
				AMT_TF DECIMAL (18,2),
17 
				AMT_RECEIVE_TF DECIMAL (18,2)
18 
			)
19
20 
			IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID='APPROVE'))
21 
			BEGIN
22 
				IF(EXISTS(SELECT * FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID))
23 
				BEGIN
24 
					INSERT INTO @listTRADE
25 
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
26 
					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
27 
				END
28 
				ELSE
29 
				BEGIN
30 
					INSERT INTO @listTRADE
31 
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
32 
					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
33 
					LEFT JOIN
34 
					(
35 
					SELECT TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
36 
					dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
37 
					GROUP BY TRADE_ID
38 
					) PLDT ON PLDT.TRADE_ID = PT.TRADE_ID
39 
					LEFT JOIN
40 
					(
41 
					SELECT FR_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
42 
					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
43 
					GROUP BY FR_TRADE_ID
44 
					) PLFT ON PLFT.FR_TRADE_ID = PT.TRADE_ID
45
46 
					LEFT JOIN
47 
					(
48 
					SELECT TO_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
49 
					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
50 
					GROUP BY TO_TRADE_ID
51 
					) PLTT ON PLTT.TO_TRADE_ID = PT.TRADE_ID
52 
					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))
53 
				END
54 
			END
55 
			ELSE
56 
			BEGIN
57 
					INSERT INTO @listTRADE
58 
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
59 
					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_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
60 
			END
61
62
63
64 
DECLARE @DV_QLNS NVARCHAR(1000)
65 
DECLARE @TB_DV TABLE ( ID INT IDENTITY NOT NULL PRIMARY KEY,DV_QL_NS NVARCHAR(1000))
66 
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
67 
			INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
68 
			WHERE REQ_ID =@P_REQ_ID
69 
			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),'')
70 
			DECLARE @TABLE1 TABLE (
71 
				STT INT,
72 
				GD_NAME NVARCHAR(200),
73 
				AMT_APP DECIMAL(18, 2),
74 
				AMT_EXE DECIMAL(18, 2),
75 
				AMT_REMAIN DECIMAL(18, 2),
76 
				TOTAL_AMT DECIMAL(18, 2),
77 
				TOTAL_AMT_FN DECIMAL(18, 2),
78 
				CHECKER_NAME NVARCHAR(200),
79 
				XN_NAME VARCHAR(15)
80 
			)
81 
			INSERT INTO @TABLE1(STT,GD_NAME,AMT_APP,AMT_EXE,AMT_REMAIN,TOTAL_AMT,TOTAL_AMT_FN,CHECKER_NAME,XN_NAME)
82 
			SELECT ROW_NUMBER() OVER (ORDER BY CG.GD_NAME) AS STT ,CG.GD_NAME,
83 
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) AS AMT_APP,
84 
			ISNULL(PT.AMT_ETM,0) AS AMT_EXE,
85 
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) - ISNULL(PT.AMT_ETM,0) AS AMT_REMAIN,
86 
			SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT,
87 
			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,
88 
			CASE
89 
				WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName
90
91 
				ELSE N''
92 
			END AS CHECKER_NAME,
93
94 
			'' AS XN_NAME
95 
			FROM
96 
			dbo.PL_REQUEST_DOC_DT PLDT
97
98 
			LEFT JOIN @listTRADE PT ON PT.TRADE_ID=PLDT.TRADE_ID
99 
			LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PLDT.PLAN_ID
100 
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID
101 
			LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLDT.CHECKER_ID
102 
			LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLDT.REQ_ID
103 
			WHERE PLDT.REQ_ID=@P_REQ_ID
104 
			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
105
106 
DECLARE @TABLE TABLE (
107 
	FR_AMT_APP DECIMAL(18,2),
108 
	FR_AMT_REMAIN DECIMAL(18,2),
109 
	TO_AMT_APP DECIMAL(18,2),
110 
	TO_AMT_EXE DECIMAL(18,2),
111 
	TO_AMT_REMAIN DECIMAL(18,2),
112 
	TOTAL_AMT DECIMAL(18,2),
113 
	FR_AMT_APP_FINAL DECIMAL(18,2),
114 
	FR_AMT_REMAIN_FINAL DECIMAL(18,2),
115 
	TO_AMT_APP_FINAL DECIMAL(18,2),
116 
	TOTAL_AMT_SIGN DECIMAL(18,2),
117 
	TOTAL_AMT_SIGN_FN DECIMAL(18,2)
118 
)
119 
INSERT INTO @TABLE(FR_AMT_APP,FR_AMT_REMAIN, TO_AMT_APP,TO_AMT_EXE, TO_AMT_REMAIN,
120 
    TOTAL_AMT, FR_AMT_APP_FINAL,FR_AMT_REMAIN_FINAL,TO_AMT_APP_FINAL,
121 
    TOTAL_AMT_SIGN,TOTAL_AMT_SIGN_FN)
122 
SELECT  ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0) AS FR_AMT_APP,
123 
		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,
124 
		ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) AS TO_AMT_APP,
125 
		ISNULL(PTT.AMT_ETM,0) AS TO_AMT_EXE,
126 
		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,
127 
		ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_AMT,
128 
		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,
129 
		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,
130 
		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,
131 
		ISNULL(PLDT.TOTAL_AMT, 0) AS TOTAL_AMT_SIGN, -- S? ti?n tr?nh s? d?ng
132 
		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
133 
FROM dbo.PL_REQUEST_TRANSFER PLTF
134 
LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID
135 
LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID
136 
LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID
137 
LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID
138 
LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'
139 
LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID
140 
LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID
141 
LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID
142 
LEFT JOIN
143 
			(
144 
			SELECT GOODS_ID,REQ_ID, SUM(TOTAL_AMT) AS TOTAL_AMT FROM
145 
			dbo.PL_REQUEST_DOC_DT
146 
			WHERE REQ_ID=@P_REQ_ID
147 
			GROUP BY GOODS_ID,REQ_ID )  PLDT ON PLDT.REQ_ID = PL.REQ_ID AND PLDT.GOODS_ID = PLTF.TO_GOOD_ID
148 
WHERE PLTF.REQ_DOC_ID=@P_REQ_ID
149
150 
SELECT TOP 1 RD.REQ_ID,
151 
       RD.REQ_CODE,
152 
       RD.REQ_NAME,
153 
       RD.REQ_DT,
154 
       RD.REQ_TYPE,
155 
       RD.REQ_CONTENT,
156 
       RD.REQ_REASON,
157 
       RD.BRANCH_ID,
158 
      (SELECT SUM(TOTAL_AMT)
159 
	   FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID ) AS TOTAL_AMT,
160 
       RD.NOTES,
161 
       RD.RECORD_STATUS,
162 
       RD.MAKER_ID,
163 
       RD.CREATE_DT,
164 
       RD.AUTH_STATUS,
165 
       RD.CHECKER_ID,
166 
	   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,
167 
	   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) +
168 
	   (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') )
169 
	   ELSE '' END AS APPROVE_DT_KT,
170 
       RD.PROCESS_ID,
171 
       RD.DVDM_APP_ID,
172 
       RD.REQ_PARENT_ID,
173 
       RD.BRANCH_FEE,
174 
       RD.EFFEC_DT,
175 
       RD.IS_BACKDAY,
176 
       RD.DEP_ID,
177 
	   RD.BASED_CONTENT,
178 
	   BR.BRANCH_NAME AS BRANCH_FEE_NAME,
179 
       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,
180 
	   (SELECT SUM(AMT_APP) FROM @TABLE1) AS TOTAL_AMT_APP,
181 
	   (SELECT SUM(AMT_EXE) FROM @TABLE1) AS TOTAL_AMT_EXE,
182 
	   (SELECT SUM(AMT_REMAIN) FROM @TABLE1) AS TOTAL_AMT_REMAIN,
183 
	   --(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,
184 
	   (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID) AS AMT_IN,
185 
	   (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,
186 
	   (SELECT SUM(TOTAL_AMT_FN) FROM @TABLE1) AS TOTAL_AMT_ETM,
187 
		CASE
188 
		WHEN NOT EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(0 AS BIT)
189 
		WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(1 AS BIT)
190 
		END AS IS_VCCB,
191 
		CASE
192 
		WHEN NOT EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(1 AS BIT)
193 
		WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(0 AS BIT)
194 
		END AS IS_OTHER,
195
196 
		(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE='O') AS AMT_OUT,
197 
		(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
198 
		DAY(RD.REQ_DT) AS DATE,
199 
		MONTH(RD.REQ_DT) AS MONTH,
200 
		YEAR(RD.REQ_DT) AS YEAR,
201 
		--CASE WHEN DP.DEP_NAME IS NULL THEN BR.BRANCH_NAME  ELSE DP.DEP_NAME  END AS BRANCH_DEP,
202 
		--'' BRANCH_DEP,
203 
		 DP.DEP_NAME AS BRANCH_DEP ,
204 
		--BR.BRANCH_NAME AS BRANCH_NAME,
205 
		CASE WHEN PRC.ID IS NULL THEN UPRD.TLFullName ELSE UPRC.TLFullName END AS TDV_NAME,
206 
		UTGD.TLFullName AS TGD_NAME,
207 
		UHDQT.TLFullName AS HDQT_NAME,
208 
		UPKT.TLFullName AS PKT_NAME,
209 
		RD.IS_CHECKALL,RD.BASED_CONTENT,RD.PL_BASED_ID, '' AS PL_BASED_CONTENT, '' AS PL_BASED_CODE,
210 
		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,
211 
		--UPTC.TLFullName + N' ? ch?p thu?n '+ ISNULL(FORMAT(PTC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')  AS TC_CONFIRM,
212 
		'' AS TC_CONFIRM,
213 
		UPTC.TLFullName + N' ? ch?p thu?n l?c: '+ ISNULL(FORMAT(PTC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')  AS TAI_CHINH_CONFIRM,
214 
		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 AND CURRENCY <>'VND') ELSE 'VND' END AS CURR
215 
		,(SELECT FORMAT(SUM(ISNULL(FR_AMT_APP, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_APP,
216 
		(SELECT FORMAT(SUM(ISNULL(FR_AMT_REMAIN, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_REMAIN,
217 
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_APP, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_APP,
218 
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_EXE, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_EXE,
219 
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_REMAIN, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_REMAIN,
220 
		(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT,
221 
		(SELECT FORMAT(SUM(ISNULL(FR_AMT_APP_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_APP_FINAL,
222 
		(SELECT FORMAT(SUM(ISNULL(FR_AMT_REMAIN_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_REMAIN_FINAL,
223 
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_APP_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_APP_FINAL,
224 
		(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT_SIGN, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT_SIGN,
225 
		(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT_SIGN_FN, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT_SIGN_FN,
226 
		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,
227 
		CASE WHEN HDQT.ID IS NOT NULL THEN N'Ph? duy?t c?p HQT (6)' ELSE N'' END AS  HDQT_TITLE,
228 
		CASE WHEN PRHDQT.ID IS NOT NULL THEN N'Ch? T?ch H?i ?ng Qu?n Tr?' + CHAR(10)+ N'? ch?p thu?n '+ ISNULL(FORMAT(PRHDQT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS HDQT_APPROVE_DT,
229 
		CASE WHEN PRTGD.ID IS NOT NULL THEN N'Ph? duy?t c?p TG (5)' ELSE N'' END AS  TGD_TITLE,
230 
		CASE WHEN TGD.ID IS NOT NULL THEN N'T?ng Gi?m ?c' + CHAR(10)+ N'? ch?p thu?n '+ ISNULL(FORMAT(TGD.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS TGD_APPROVE_DT,
231 
		CASE WHEN DVDC.ID IS NOT NULL THEN (SELECT TLFullName FROM TL_USER WHERE TLNANME =DVDC.CHECKER_ID) + N' ? ch?p thu?n '+ ISNULL(FORMAT(DVDC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS DV_DC_NS_CONFIRM
232 
		--CASE WHEN PRHDQT.ID IS NOT NULL THEN UHDQT.TLFullName ELSE '' END AS  HDQT_TITLE,
233 
		FROM dbo.PL_REQUEST_DOC RD
234 
		LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID=RD.BRANCH_FEE
235 
		LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=RD.DEP_FEE
236 
		LEFT JOIN dbo.TL_USER UD ON UD.TLNANME=RD.CHECKER_ID
237 
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRC ON PRC.REQ_ID=RD.REQ_ID AND PRC.PROCESS_ID='DVC'
238 
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRD ON PRD.REQ_ID=RD.REQ_ID AND PRD.PROCESS_ID='APPNEW'
239 
		LEFT JOIN dbo.TL_USER UPRC ON UPRC.TLNANME=PRC.CHECKER_ID
240 
		LEFT JOIN dbo.TL_USER UPRD ON UPRD.TLNANME=PRD.CHECKER_ID
241 
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRTGD ON PRTGD.REQ_ID=RD.REQ_ID AND PRTGD.PROCESS_ID LIKE 'TGD%'
242 
		LEFT JOIN dbo.TL_USER UTGD ON UTGD.TLNANME=PRTGD.CHECKER_ID
243 
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRHDQT ON PRHDQT.REQ_ID=RD.REQ_ID AND PRHDQT.PROCESS_ID='HDQT' AND PRHDQT.STATUS='P'
244 
		LEFT JOIN dbo.PL_REQUEST_PROCESS TGD ON TGD.REQ_ID=RD.REQ_ID AND TGD.PROCESS_ID LIKE 'TGD%' AND TGD.STATUS ='P'
245 
		LEFT JOIN dbo.PL_REQUEST_PROCESS HDQT ON HDQT.REQ_ID=RD.REQ_ID AND HDQT.PROCESS_ID='HDQT'
246 
		LEFT JOIN dbo.TL_USER UHDQT ON UHDQT.TLNANME=PRHDQT.CHECKER_ID
247 
		LEFT JOIN dbo.PL_REQUEST_PROCESS PKT ON PKT.REQ_ID=RD.REQ_ID AND PKT.PROCESS_ID='KT'
248 
		LEFT JOIN dbo.PL_REQUEST_PROCESS PTC ON PTC.REQ_ID=RD.REQ_ID AND PTC.PROCESS_ID='TC'
249 
		LEFT JOIN dbo.TL_USER UPKT ON UPKT.TLNANME=PKT.CHECKER_ID
250 
		LEFT JOIN dbo.TL_USER UPTC ON UPTC.TLNANME=PTC.CHECKER_ID
251 
		LEFT JOIN CM_BRANCH BR_C ON RD.BRANCH_CREATE = BR_C.BRANCH_ID
252 
		LEFT JOIN CM_DEPARTMENT DP_C ON RD.DEP_CREATE = DP_C.DEP_ID
253
254 
		-- i?u chuy?n ng?n s?ch
255 
		LEFT JOIN dbo.PL_REQUEST_TRANSFER PLTF ON PLTF.REQ_DOC_ID = RD.REQ_ID
256 
		LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID
257 
		LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID
258 
		LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID
259 
		LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID
260 
		LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'
261 
		LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID
262 
		LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID
263 
		LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID
264 
		LEFT JOIN dbo.PL_REQUEST_PROCESS DVDC ON DVDC.REQ_ID=RD.REQ_ID AND DVDC.PROCESS_ID LIKE 'DVDC%' AND DVDC.STATUS ='P'
265
266 
		-- DON VI QUAN LY NGAN SACH
267 
		 WHERE RD.REQ_ID=@P_REQ_ID
268 
		 ORDER BY RD.REQ_ID
269