Project

General

Profile

Download (65 KB)

TIM KIEM TT CHU TRUONG.txt

Luc Tran Van, 10/15/2020 09:55 AM

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