Project

General

Profile

Download (65 KB)

TIM KIEM TT CHU TRUONG 02.txt

Luc Tran Van, 10/15/2020 10:02 AM

 
1 
??
2
3 
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_Search]
4 
@p_REQ_ID varchar(15) = NULL,
5 
@p_REQ_CODE nvarchar(100) = NULL,
6 
@p_REQ_NAME nvarchar(200) = NULL,
7 
@p_REQ_DT DATETIME = NULL,
8 
@p_REQ_TYPE int = NULL,
9 
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
10 
@p_REQ_REASON NVARCHAR(500)=NULL,
11 
@p_TOTAL_AMT decimal = NULL,
12 
@p_NOTES nvarchar(1000) = NULL,
13 
@p_RECORD_STATUS varchar(1) = NULL,
14 
@p_MAKER_ID varchar(12) = NULL,
15 
@p_CREATE_DT DATETIME = NULL,
16 
@p_AUTH_STATUS varchar(50) = NULL,
17 
@p_CHECKER_ID varchar(12) = NULL,
18 
@p_APPROVE_DT DATETIME = NULL,
19 
@p_PROCESS_ID varchar(15) = NULL,
20 
@p_BRANCH_ID VARCHAR(15)=NULL,
21 
@p_BRANCH_LOGIN VARCHAR(15),
22 
@p_ROLE_USER VARCHAR(20),
23 
@p_TLNAME_USER VARCHAR(15),
24 
@p_FR_DATE DATETIME = NULL,
25 
@p_TO_DATE DATETIME = NULL,
26 
@p_TYPE_TRANFER VARCHAR(15),
27 
@p_TYPE VARCHAR(15),
28 
@p_YEAR INT,
29 
@p_TOP INT = 10,
30 
@p_IS_TRANSFER VARCHAR(10) = NULL,
31 
@p_NGUOIXULY NVARCHAR(15) = NULL
32
33 
AS
34 
BEGIN
35
36 
DECLARE @TABLE_ROLE TABLE
37 
( ROLE_ID VARCHAR(20))
38 
INSERT INTO @TABLE_ROLE VALUES(@p_ROLE_USER)
39
40
41 
INSERT INTO @TABLE_ROLE
42 
SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER
43
44
45
46 
DECLARE @DEP_ID VARCHAR(15)
47 
DECLARE @DEP_CODE VARCHAR(15)
48 
DECLARE
49 
@COST_ID TABLE (
50 
COST_ID VARCHAR(15)
51 
)
52
53 
DECLARE @DVDM_ID TABLE (
54 
DVDM_ID VARCHAR(15)
55 
)
56 
SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME_USER)
57 
SET @DEP_CODE =(SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@DEP_ID)
58 
INSERT INTO @COST_ID
59 
SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN
60 
INSERT INTO @DVDM_ID
61 
SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID
62 
IF(@p_TYPE='DVKD')
63 
BEGIN
64 
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
65 
A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
66 
UDV.TLFullName AS CHECKER_NAME_DV,
67 
A.APPROVE_DT,
68 
A.PROCESS_ID,
69 
D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
70 
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'? duy?t'
71 
WHEN A.PROCESS_ID<> 'APPROVE' AND A.AUTH_STATUS='A' THEN N'Ch? duy?t' ELSE D.AUTH_STATUS_NAME END AS AUTH_STATUS_NAME,
72 
G.BRANCH_CODE,
73 
G.BRANCH_NAME,
74 
UP.TLFullName AS CHECKER_NAME_PROCESS,
75 
RP.APPROVE_DT AS APPROVE_DT_PROCESS,
76 
UC.TLFullName AS MAKER_NAME,
77 
RPN.PROCESS_ID AS PROCESS_ID_NEXT,
78 
RP.ROLE_USER,
79 
RP.NOTES AS PROCESS_STATUS ,
80 
RPN.NOTES AS PROCESS_STATUS_NEXT,
81 
A.DVDM_APP_ID,
82 
CD.DVDM_NAME AS DVDM_APP_NAME,
83 
A.REQ_PARENT_ID,
84 
PARENT.REQ_NAME AS REQ_PARENT_NAME,
85 
PARENT.REQ_CODE AS REQ_PARENT_CODE,
86 
PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
87 
A.BRANCH_FEE,
88 
A.DEP_ID,
89 
A.DEP_FEE,
90 
DEP.DEP_NAME,
91 
DEP.DEP_CODE,
92 
BF.BRANCH_NAME AS BRANCH_FEE_NAME,
93 
BF.BRANCH_CODE AS BRANCH_FEE_CODE,
94 
DF.DEP_NAME AS DEP_FEE_NAME,
95 
DF.DEP_CODE AS DEP_FEE_CODE,
96 
'' AS BRANCH_DEP,
97 
'' AS BRANCH_DEP_FEE,
98
99
100
101 
'' AS TYPE_JOB,
102 
'' AS USER_JOB,
103 
'' AS USER_JOB_NAME,
104 
'' AS TRANSFER_MAKER,
105 
A.CREATE_DT AS TRANFER_DT ,
106 
'' AS TRANSFER_MAKER_ID,
107 
A.EFFEC_DT,A.IS_BACKDAY,
108 
'' AS TYPE_JOB_XL,
109 
'' AS USER_JOB_XL,
110 
RP.ID AS REF_ID,
111 
RPN.STATUS AS STATUS_NEXT,
112 
RP.STATUS AS STATUS_CURR,
113 
'' AS STATUS_JOB,
114 
A.BRANCH_CREATE,
115 
A.DEP_CREATE,
116 
A.REQ_LINE,
117 
A.TC_NOTES,
118 
A.SIGN_USER,
119 
TL.TLFullName AS SIGN_USER_NAME,
120 
A.KT_NOTES,
121 
A.IS_CHECKALL,
122 
A.BASED_CONTENT,
123 
A.PL_BASED_ID, PL_B.REQ_CODE AS PL_BASED_CODE, PL_B.REQ_CONTENT AS PL_BASED_CONTENT,
124 
'' AS IS_TRANSFER,
125 
--NGUOI XU LY
126 
dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
127
128 
FROM PL_REQUEST_DOC A
129 
--LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <>'U'
130 
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
131 
LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
132 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID
133
134 
LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
135 
LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
136
137 
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
138
139 
LEFT JOIN dbo.PL_REQUEST_PROCESS RPN
140 
ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
141 
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID IN(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID AND PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID)
142 
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
143 
LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
144 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
145 
LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
146 
LEFT JOIN dbo.PL_REQUEST_DOC PL_B ON PL_B.REQ_ID=A.PL_BASED_ID
147 
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
148
149
150 
WHERE 1 = 1
151
152 
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
153 
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
154 
AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
155 
AND (A.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
156 
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
157 
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
158 
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
159 
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
160 
AND (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
161
162 
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
163 
AND (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
164 
AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')
165 
AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
166 
AND A.RECORD_STATUS = '1'
167
168 
AND(
169 
@p_REQ_TYPE = -1 OR @p_REQ_TYPE IS NULL
170 
OR
171 
(
172 
@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
173 
)
174 
OR
175 
(
176 
@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
177 
)
178 
)
179
180 
AND (A.MAKER_ID=@p_TLNAME_USER OR EXISTS(SELECT ID FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_REQUEST_DOC_ALL' AND BRANCH_ID=@p_TLNAME_USER ) OR (EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS PLRP WHERE A.REQ_ID=PLRP.REQ_ID AND PLRP.PROCESS_ID='A
181 
PPNEW'
182 
AND PLRP.ROLE_USER IN (SELECT ROLE_ID FROM @TABLE_ROLE) AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN
183 
AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID =''))) OR
184 
(A.SIGN_USER IS NOT NULL AND A.SIGN_USER <>'' AND A.SIGN_USER =@p_TLNAME_USER AND A.AUTH_STATUS<>'E')
185 
OR (@DEP_CODE ='0690802') OR (A.IS_CHECKALL='1' OR A.PROCESS_ID ='APPROVE'))
186 
--AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS IN('U','R','E') AND (A.PROCESS_ID <> 'APPROVE' OR A.PROCESS_ID IS NULL)) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
187 
--AND ((A.AUTH_STATUS='E' AND A.MAKER_ID=@p_TLNAME_USER) OR (A.AUTH_STATUS <> 'E' AND (A.MAKER_ID=@p_TLNAME_USER OR (PLRP.PROCESS_ID='DVKD' AND PLRP.ROLE_USER=@p_ROLE_USER AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NUL
188 
L OR PLRP.DEP_ID ='')))))
189 
AND (A.AUTH_STATUS = @p_AUTH_STATUS OR @p_AUTH_STATUS ='' OR @p_AUTH_STATUS IS NULL)
190 
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
191 
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
192 
AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)
193 
AND(
194 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
195 
OR
196 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
197 
)
198
199 
ORDER BY A.CREATE_DT DESC
200 
END
201 
ELSE IF(@p_TYPE='PDTT')
202 
BEGIN
203
204 
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
205 
A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
206
207 
UDV.TLFullName AS CHECKER_NAME_DV,
208
209 
A.APPROVE_DT,
210 
A.PROCESS_ID,
211 
D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
212 
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'? duy?t'
213 
ELSE N'Ch? duy?t' END AS AUTH_STATUS_NAME,
214 
G.BRANCH_CODE,
215 
G.BRANCH_NAME,
216 
UP.TLFullName AS CHECKER_NAME_PROCESS,
217 
RP.APPROVE_DT AS APPROVE_DT_PROCESS,
218 
UC.TLFullName AS MAKER_NAME,
219 
RPN.PROCESS_ID AS PROCESS_ID_NEXT,
220 
RPN.ROLE_USER,
221 
RP.NOTES AS PROCESS_STATUS ,
222 
RPN.NOTES AS PROCESS_STATUS_NEXT,
223 
A.DVDM_APP_ID,
224 
CD.DVDM_NAME AS DVDM_APP_NAME,
225 
A.REQ_PARENT_ID,
226 
PARENT.REQ_NAME AS REQ_PARENT_NAME,
227 
PARENT.REQ_CODE AS REQ_PARENT_CODE,
228 
PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
229 
A.BRANCH_FEE,
230 
A.DEP_ID,
231 
A.DEP_FEE,
232 
DEP.DEP_NAME,
233 
DEP.DEP_CODE,
234 
BF.BRANCH_NAME AS BRANCH_FEE_NAME,
235 
BF.BRANCH_CODE AS BRANCH_FEE_CODE,
236 
DF.DEP_NAME AS DEP_FEE_NAME,
237 
DF.DEP_CODE AS DEP_FEE_CODE,
238 
'' AS BRANCH_DEP,
239 
'' AS BRANCH_DEP_FEE,
240
241
242
243 
RPC.TYPE_JOB AS TYPE_JOB,
244 
RPC.TLNAME AS USER_JOB,
245 
TU.TLFullName AS USER_JOB_NAME,
246 
TFM.TLNANME AS TRANSFER_MAKER,
247 
RPC.TRANFER_DT AS TRANFER_DT ,
248 
RPC.MAKER_ID AS TRANSFER_MAKER_ID,
249 
A.EFFEC_DT,A.IS_BACKDAY,
250 
'' AS TYPE_JOB_XL,
251 
'' AS USER_JOB_XL,
252 
RP.ID AS REF_ID,
253 
RPN.STATUS AS STATUS_NEXT,
254 
PLRP.STATUS AS STATUS_CURR,
255 
RPC.STATUS_JOB AS STATUS_JOB,
256 
A.BRANCH_CREATE,
257 
A.DEP_CREATE,
258 
A.REQ_LINE,
259 
A.TC_NOTES,
260 
A.SIGN_USER,
261 
TL.TLFullName AS SIGN_USER_NAME,
262 
A.KT_NOTES,
263 
A.IS_CHECKALL,
264 
A.BASED_CONTENT,
265 
'' AS IS_TRANSFER,
266 
--NGUOI XU LY
267 
dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
268
269 
FROM PL_REQUEST_DOC A
270 
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <>'U'
271 
LEFT JOIN dbo.PL_REQUEST_PROCESS RPN
272 
ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
273 
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
274 
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
275 
LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
276
277 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID
278 
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
279 
LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
280 
LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
281 
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
282 
LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
283 
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
284 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
285 
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
286 
LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
287 
LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
288 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
289 
LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
290 
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
291 
WHERE 1 = 1
292 
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
293 
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
294 
AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
295 
AND (A.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
296 
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
297 
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
298 
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
299 
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
300 
AND (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
301
302 
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
303 
AND (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
304 
AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')
305 
AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
306 
--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
307 
AND A.RECORD_STATUS = '1'
308
309
310 
AND(
311 
@p_REQ_TYPE = -1 OR @p_REQ_TYPE IS NULL
312 
OR
313 
(
314 
@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
315 
)
316 
OR
317 
(
318 
@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
319 
)
320 
)
321
322
323 
AND PLRP.ROLE_USER IN (SELECT ROLE_ID FROM @TABLE_ROLE) AND (
324 
(PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID=''))
325 
OR ( PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
326 
OR
327 
( (PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL) AND (PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_ID=''))
328 
)
329 
AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)
330 
AND (PLRP.IS_HAS_CHILD=0 OR PLRP.IS_HAS_CHILD IS NULL)
331 
AND (( @p_AUTH_STATUS='A' AND PLRP.STATUS='P') OR( @p_AUTH_STATUS='U' AND (PLRP.STATUS='C')) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
332 
-- AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
333 
--AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
334 
AND(
335 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
336 
OR
337 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
338 
)
339 
ORDER BY A.CREATE_DT DESC
340
341 
END
342 
ELSE IF(@p_TYPE='TFJOB')
343 
BEGIN
344
345 
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
346 
A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
347
348 
UDV.TLFullName AS CHECKER_NAME_DV,
349
350 
A.APPROVE_DT,
351 
A.PROCESS_ID,
352 
D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
353 
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'? duy?t'
354 
ELSE N'Ch? duy?t' END AS AUTH_STATUS_NAME,
355 
G.BRANCH_CODE,
356 
G.BRANCH_NAME,
357 
UP.TLFullName AS CHECKER_NAME_PROCESS,
358 
RP.APPROVE_DT AS APPROVE_DT_PROCESS,
359 
UC.TLFullName AS MAKER_NAME,
360 
RPN.PROCESS_ID AS PROCESS_ID_NEXT,
361 
RP.ROLE_USER,
362 
RP.NOTES AS PROCESS_STATUS ,
363 
RPN.NOTES AS PROCESS_STATUS_NEXT,
364 
A.DVDM_APP_ID,
365 
CD.DVDM_NAME AS DVDM_APP_NAME,
366 
A.REQ_PARENT_ID,
367 
PARENT.REQ_NAME AS REQ_PARENT_NAME,
368 
PARENT.REQ_CODE AS REQ_PARENT_CODE,
369 
PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
370 
A.BRANCH_FEE,
371 
A.DEP_ID,
372 
A.DEP_FEE,
373 
DEP.DEP_NAME,
374 
DEP.DEP_CODE,
375 
BF.BRANCH_NAME AS BRANCH_FEE_NAME,
376 
BF.BRANCH_CODE AS BRANCH_FEE_CODE,
377 
DF.DEP_NAME AS DEP_FEE_NAME,
378 
DF.DEP_CODE AS DEP_FEE_CODE,
379 
'' AS BRANCH_DEP,
380 
'' AS BRANCH_DEP_FEE,
381 
RPC.TYPE_JOB AS TYPE_JOB,
382 
RPC.TLNAME AS USER_JOB,
383 
TU.TLFullName AS USER_JOB_NAME,
384 
TFM.TLNANME AS TRANSFER_MAKER,
385 
RPC.TRANFER_DT AS TRANFER_DT ,
386 
RPC.MAKER_ID AS TRANSFER_MAKER_ID,
387 
A.EFFEC_DT,A.IS_BACKDAY,
388 
'' AS TYPE_JOB_XL,
389 
'' AS USER_JOB_XL,
390 
PLRP.ID AS REF_ID,
391 
RPN.STATUS AS STATUS_NEXT,
392 
PLRP.STATUS AS STATUS_CURR,
393 
RPC.STATUS_JOB AS STATUS_JOB,
394 
A.BRANCH_CREATE,
395 
A.DEP_CREATE,
396 
A.REQ_LINE,
397 
A.TC_NOTES,
398 
A.SIGN_USER,
399 
TL.TLFULLNAME AS SIGN_USER_NAME,
400 
A.KT_NOTES,
401 
A.IS_CHECKALL,
402 
A.BASED_CONTENT,
403 
'' AS IS_TRANSFER,
404 
--NGUOI XU LY
405 
dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
406
407 
FROM PL_REQUEST_DOC A
408 
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS = 'C' AND PLRP.IS_HAS_CHILD=1
409 
LEFT JOIN dbo.PL_REQUEST_PROCESS RPN
410 
ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
411 
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
412 
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
413 
LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
414 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID
415
416 
LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
417 
LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
418 
LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
419 
LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
420 
LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
421 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
422 
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
423
424 
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
425
426 
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
427 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
428 
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
429 
LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
430 
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
431 
WHERE 1 = 1
432 
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
433 
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
434 
AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
435 
AND (A.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
436 
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
437 
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
438 
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
439 
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
440 
AND (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
441
442 
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
443 
AND (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
444 
AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')
445 
AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
446 
--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
447 
AND A.RECORD_STATUS = '1'
448
449
450 
AND(
451 
@p_REQ_TYPE = -1 OR @p_REQ_TYPE IS NULL
452 
OR
453 
(
454 
@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
455 
)
456 
OR
457 
(
458 
@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
459 
)
460 
)
461
462
463
464 
AND(( PLRP.ROLE_USER IN (SELECT ROLE_ID FROM @TABLE_ROLE) AND ((PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID) )
465 
OR (PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID=''))
466 
OR ( (PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL) AND (PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_ID=''))
467 
)) OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND TYPE_JOB='KS' AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID) ) )
468 
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
469 
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
470 
AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)
471 
AND (( @p_AUTH_STATUS='A' AND PLRP.STATUS='P') OR( @p_AUTH_STATUS='U' AND (PLRP.STATUS='C')) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
472 
AND (NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND PL_REQUEST_PROCESS_CHILD.STATUS_JOB='C' AND PL_REQUEST_PROCESS_CHILD.TYPE_JOB='TP'))
473 
AND (
474 
((RPC.REQ_ID IS NOT NULL OR RPC.REQ_ID <> '') AND @p_IS_TRANSFER = 'Y')
475 
OR ((RPC.REQ_ID IS NULL OR RPC.REQ_ID = '') AND @p_IS_TRANSFER = 'N')
476 
OR @p_IS_TRANSFER = '' OR @p_IS_TRANSFER IS NULL
477 
)
478
479 
AND(
480 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
481 
OR
482 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
483 
)
484 
ORDER BY A.CREATE_DT DESC
485 
END
486
487 
ELSE IF(@p_TYPE='XLTT')
488 
BEGIN
489
490 
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
491 
A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
492
493 
UDV.TLFullName AS CHECKER_NAME_DV,
494
495 
A.APPROVE_DT,
496 
A.PROCESS_ID,
497 
D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
498 
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'? duy?t'
499 
ELSE N'Ch? duy?t' END AS AUTH_STATUS_NAME,
500 
G.BRANCH_CODE,
501 
G.BRANCH_NAME,
502 
UP.TLFullName AS CHECKER_NAME_PROCESS,
503 
RP.APPROVE_DT AS APPROVE_DT_PROCESS,
504 
UC.TLFullName AS MAKER_NAME,
505 
RPN.PROCESS_ID AS PROCESS_ID_NEXT,
506 
RPN.ROLE_USER,
507 
RP.NOTES AS PROCESS_STATUS ,
508 
RPN.NOTES AS PROCESS_STATUS_NEXT,
509 
A.DVDM_APP_ID,
510 
CD.DVDM_NAME AS DVDM_APP_NAME,
511 
A.REQ_PARENT_ID,
512 
PARENT.REQ_NAME AS REQ_PARENT_NAME,
513 
PARENT.REQ_CODE AS REQ_PARENT_CODE,
514 
PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
515 
A.BRANCH_FEE,
516 
A.DEP_ID,
517 
A.DEP_FEE,
518 
DEP.DEP_NAME,
519 
DEP.DEP_CODE,
520 
BF.BRANCH_NAME AS BRANCH_FEE_NAME,
521 
BF.BRANCH_CODE AS BRANCH_FEE_CODE,
522 
DF.DEP_NAME AS DEP_FEE_NAME,
523 
DF.DEP_CODE AS DEP_FEE_CODE,
524 
'' AS BRANCH_DEP,
525 
'' AS BRANCH_DEP_FEE,
526
527
528
529 
RPC.TYPE_JOB AS TYPE_JOB,
530 
RPC.TLNAME AS USER_JOB,
531 
TU.TLFullName AS USER_JOB_NAME,
532 
TFM.TLNANME AS TRANSFER_MAKER,
533 
RPC.TRANFER_DT AS TRANFER_DT ,
534 
RPC.MAKER_ID AS TRANSFER_MAKER_ID,
535 
A.EFFEC_DT,A.IS_BACKDAY,
536 
'' AS TYPE_JOB_XL,
537 
'' AS USER_JOB_XL,
538 
PLRP.ID AS REF_ID,
539 
RPN.STATUS AS STATUS_NEXT,
540 
PLRP.STATUS AS STATUS_CURR,
541 
RPC.STATUS_JOB AS STATUS_JOB,
542 
A.BRANCH_CREATE,
543 
A.DEP_CREATE,
544 
A.REQ_LINE,
545 
A.TC_NOTES,
546 
A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
547 
A.KT_NOTES,
548 
A.IS_CHECKALL,
549 
A.BASED_CONTENT,
550 
'' AS IS_TRANSFER,
551 
--NGUOI XU LY
552 
dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
553
554 
FROM PL_REQUEST_DOC A
555 
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <> 'U' AND PLRP.IS_HAS_CHILD=1
556 
LEFT JOIN dbo.PL_REQUEST_PROCESS RPN
557 
ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
558 
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
559 
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
560 
LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
561 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID
562
563 
LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
564 
LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
565 
LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
566 
LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
567 
LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
568 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
569 
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
570
571 
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
572
573 
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
574 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
575 
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
576 
LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
577 
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
578 
WHERE 1 = 1
579 
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
580 
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
581 
AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
582 
AND (A.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
583 
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
584 
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
585 
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
586 
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
587 
AND (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
588
589 
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
590 
AND (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
591 
AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')
592 
AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
593 
--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
594 
AND A.RECORD_STATUS = '1'
595
596
597 
AND(
598 
@p_REQ_TYPE = -1 OR @p_REQ_TYPE IS NULL
599 
OR
600 
(
601 
@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
602 
)
603 
OR
604 
(
605 
@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
606 
)
607 
)
608
609
610
611 
AND(( PLRP.ROLE_USER IN (SELECT ROLE_ID FROM @TABLE_ROLE) AND ((PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID) )
612 
OR (PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID=''))
613 
OR ( (PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL) AND ( PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_ID=''))
614 
)) OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID) ))
615 
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
616 
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
617 
AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)
618 
AND (( @p_AUTH_STATUS='A' AND PLRP.STATUS='P') OR( @p_AUTH_STATUS='U' AND (PLRP.STATUS='C')) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
619
620 
AND(
621 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
622 
OR
623 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
624 
)
625
626 
ORDER BY A.CREATE_DT DESC
627 
END
628 
ELSE IF(@p_TYPE='DVKD_PARENT')
629 
BEGIN
630 
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
631 
A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
632 
UDV.TLFullName AS CHECKER_NAME_DV,
633 
A.APPROVE_DT,
634 
A.PROCESS_ID,
635 
D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
636 
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'? duy?t'
637 
ELSE N'Ch? duy?t' END AS AUTH_STATUS_NAME,
638 
G.BRANCH_CODE,
639 
G.BRANCH_NAME,
640 
UP.TLFullName AS CHECKER_NAME_PROCESS,
641 
RP.APPROVE_DT AS APPROVE_DT_PROCESS,
642 
UC.TLFullName AS MAKER_NAME,
643 
RPN.PROCESS_ID AS PROCESS_ID_NEXT,
644 
RP.ROLE_USER,
645 
RP.NOTES AS PROCESS_STATUS ,
646 
RPN.NOTES AS PROCESS_STATUS_NEXT,
647 
A.DVDM_APP_ID,
648 
CD.DVDM_NAME AS DVDM_APP_NAME,
649 
A.REQ_PARENT_ID,
650 
PARENT.REQ_NAME AS REQ_PARENT_NAME,
651 
PARENT.REQ_CODE AS REQ_PARENT_CODE,
652 
PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
653 
A.BRANCH_FEE,
654 
A.DEP_ID,
655 
A.DEP_FEE,
656 
DEP.DEP_NAME,
657 
DEP.DEP_CODE,
658 
BF.BRANCH_NAME AS BRANCH_FEE_NAME,
659 
BF.BRANCH_CODE AS BRANCH_FEE_CODE,
660 
DF.DEP_NAME AS DEP_FEE_NAME,
661 
DF.DEP_CODE AS DEP_FEE_CODE,
662 
'' AS BRANCH_DEP,
663 
'' AS BRANCH_DEP_FEE,
664
665
666
667 
'' AS TYPE_JOB,
668 
'' AS USER_JOB,
669 
'' AS USER_JOB_NAME,
670 
'' AS TRANSFER_MAKER,
671 
A.CREATE_DT AS TRANFER_DT ,
672 
'' AS TRANSFER_MAKER_ID,
673 
A.EFFEC_DT,A.IS_BACKDAY,
674 
'' AS TYPE_JOB_XL,
675 
'' AS USER_JOB_XL,
676 
RP.ID AS REF_ID,
677 
RPN.STATUS AS STATUS_NEXT,
678 
RP.STATUS AS STATUS_CURR,
679 
'' AS STATUS_JOB,
680 
A.BRANCH_CREATE,
681 
A.DEP_CREATE,
682 
A.REQ_LINE,
683 
A.TC_NOTES, A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
684 
A.KT_NOTES,
685 
A.IS_CHECKALL,
686 
A.BASED_CONTENT,
687 
'' AS IS_TRANSFER,
688 
--NGUOI XU LY
689 
dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
690
691 
FROM PL_REQUEST_DOC A
692 
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
693 
LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
694 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID
695
696 
LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
697 
LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
698
699 
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
700
701 
LEFT JOIN dbo.PL_REQUEST_PROCESS RPN
702 
ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
703 
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID IN(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID AND PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID)
704 
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
705 
LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
706 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
707 
LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
708 
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
709 
WHERE 1 = 1
710
711 
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
712 
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
713 
AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
714 
AND (A.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
715 
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
716 
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
717 
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
718 
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
719 
AND (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
720
721 
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
722 
AND (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
723 
AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')
724 
AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
725 
AND A.RECORD_STATUS = '1'
726
727 
AND(
728 
@p_REQ_TYPE = -1 OR @p_REQ_TYPE IS NULL
729 
OR
730 
(
731 
@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
732 
)
733 
OR
734 
(
735 
@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
736 
)
737 
)
738
739 
AND ( (A.BRANCH_CREATE = @p_BRANCH_LOGIN AND( A.DEP_CREATE=@DEP_ID OR A.DEP_CREATE IS NULL OR A.DEP_ID='')) OR A.IS_CHECKALL=1)
740 
AND A.PROCESS_ID='APPROVE'
741 
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
742 
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
743 
AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)
744 
AND(A.REQ_PARENT_ID IS NULL OR A.REQ_PARENT_ID='')
745
746 
AND(
747 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
748 
OR
749 
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
750 
)
751
752
753 
ORDER BY A.CREATE_DT DESC
754
755 
END
756 
END
757
758
759