Project

General

Profile

Download (116 KB)

FILE_3_COMPARE_UAT.txt

Luc Tran Van, 04/18/2023 02:13 PM

 
1 
??/*
2 
Run this script on:
3
4 
        118.69.72.241,5036.gAMSPro_VIETBANK_UAT_CUS    -  This database will be modified
5
6 
to synchronize it with:
7
8 
        118.69.72.241,5036.gAMSPro_VIETBANK_DEV
9
10 
You are recommended to back up your database before running this script
11
12 
Script created by SQL Compare version 13.1.6.5463 from Red Gate Software Ltd at 4/18/2023 2:14:51 PM
13
14 
*/
15 
SET NUMERIC_ROUNDABORT OFF
16 
GO
17 
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
18 
GO
19 
SET XACT_ABORT ON
20 
GO
21 
SET TRANSACTION ISOLATION LEVEL Serializable
22 
GO
23 
BEGIN TRANSACTION
24 
GO
25 
IF @@ERROR <> 0 SET NOEXEC ON
26 
GO
27 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_DT_ById]'
28 
GO
29 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_DT_ById]
30 
	@p_IMP_MASTER_ID	VARCHAR(15)
31 
AS
32 
SELECT A.*,B.BRANCH_NAME,C.DEP_NAME,D.EMP_NAME,E.ASSET_CODE,
33 
F.BRANCH_NAME AS BRANCH_NAME_OLD, G.DEP_NAME AS DEP_NAME_OLD, H.EMP_NAME AS EMP_NAME_OLD
34 
FROM ASS_IMPORT_UPDATE_DT A
35 
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
36 
LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
37 
LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID
38 
LEFT JOIN ASS_MASTER E ON A.ASSET_ID = E.ASSET_ID
39
40 
LEFT JOIN CM_BRANCH F ON A.BRANCH_ID_OLD = F.BRANCH_ID
41 
LEFT JOIN CM_DEPARTMENT G ON A.DEP_ID_OLD = G.DEP_ID
42 
LEFT JOIN CM_EMPLOYEE H ON A.EMP_ID_OLD = H.EMP_ID
43
44 
WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID
45 
GO
46 
IF @@ERROR <> 0 SET NOEXEC ON
47 
GO
48 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_App]'
49 
GO
50 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_App]
51 
	@p_IMP_MASTER_ID	VARCHAR(15),
52 
  @p_CHECKER_ID VARCHAR(100),
53 
  @p_APPROVE_DT VARCHAR(50)
54 
AS
55 
	DECLARE @MESSAGE_VALIDATION NVARCHAR(MAX)
56
57 
	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
58 
	BEGIN
59 
		SELECT '-1' Result, N'Phi?u ? ??c ph? duy?t' ErrorDesc
60 
		RETURN '-1'
61 
	END
62
63 
  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'R'))
64 
	BEGIN
65 
		SELECT '-1' Result, N'Phi?u ang ??c tr? v?' ErrorDesc
66 
		RETURN '-1'
67 
	END
68
69
70 
BEGIN TRANSACTION
71 
		UPDATE ASS_IMPORT_UPDATE_MASTER SET AUTH_STATUS = 'A' , CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103)
72 
    WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
73
74 
    --ki?m tra branch,dep c?a t?i s?n hi?n t?i c? gi?ng branch,dep import kh?ng
75 
    SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT
76 
                                    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
77 
                                    ELSE '|' END
78 
                                    + N'T?i s?n ' + C.ASSET_CODE + N' ? b? thay ?i ?n v?/Ph?ng ban so v?i File Import'
79 
                              FROM ( SELECT B.ASSET_CODE
80 
                                    FROM ASS_IMPORT_UPDATE_DT A
81 
                                    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
82 
                                    WHERE (ISNULL(A.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'')
83 
                                          OR ISNULL(A.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
84 
                                          AND A.IMP_MASTER_ID = @p_IMP_MASTER_ID) C
85 
                              FOR XML PATH (''))
86 
                            , '|', '<br />'))
87
88 
    IF(@MESSAGE_VALIDATION IS NOT NULL AND @MESSAGE_VALIDATION <> '')
89 
    BEGIN
90 
        ROLLBACK TRANSACTION
91 
    		SELECT '-1' Result, @MESSAGE_VALIDATION ErrorDesc
92 
    		RETURN '-1'
93 
    END
94
95 
    --check branch,dep c?a t?i s?n hi?n t?i c? gi?ng v?i branch,dep c?a ng??i d?ng kh?ng
96 
    SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT
97 
                                    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
98 
                                    ELSE '|' END
99 
                                    + N'Ng??i d?ng ' + C.EMP_NAME + '(' + C.EMP_CODE + ')' + N' kh?ng c?ng ?n v?/ph?ng ban v?i t?i s?n ' + C.ASSET_CODE
100 
                              FROM ( SELECT B.ASSET_CODE,D.EMP_CODE,D.EMP_NAME
101 
                                    FROM ASS_IMPORT_UPDATE_DT A
102 
                                    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
103 
                                    LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID
104 
                                    WHERE (ISNULL(D.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'')
105 
                                          OR ISNULL(D.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
106 
                                          AND A.IMP_MASTER_ID = @p_IMP_MASTER_ID) C
107 
                              FOR XML PATH (''))
108 
                            , '|', '<br />'))
109
110 
    IF(@MESSAGE_VALIDATION IS NOT NULL AND @MESSAGE_VALIDATION <> '')
111 
    BEGIN
112 
        ROLLBACK TRANSACTION
113 
    		SELECT '-1' Result, @MESSAGE_VALIDATION ErrorDesc
114 
    		RETURN '-1'
115 
    END
116
117 
    UPDATE B SET B.EMP_ID = A.EMP_ID, B.ASSET_SERIAL_NO = A.ASSET_SERIAL_NO, B.ASSET_DESC = A.ASSET_DESC, B.NOTES = A.NOTES
118 
    FROM ASS_IMPORT_UPDATE_DT A
119 
    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
120 
    WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID
121
122 
    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
123 
    VALUES (@p_IMP_MASTER_ID, 'APPROVE', @p_CHECKER_ID, GETDATE(), N'Duy?t phi?u c?p nh?t t?i s?n', N'Duy?t phi?u c?p nh?t t?i s?n th?nh c?ng');
124
125 
		IF @@Error <> 0 GOTO ABORT
126
127 
COMMIT TRANSACTION
128 
SELECT '0' as Result, '' ErrorDesc
129 
RETURN '0'
130
131 
ABORT:
132 
BEGIN
133 
		ROLLBACK TRANSACTION
134 
		SELECT '-1' as Result, '' ErrorDesc
135 
		RETURN '-1'
136 
End
137 
GO
138 
IF @@ERROR <> 0 SET NOEXEC ON
139 
GO
140 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_ById]'
141 
GO
142 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_ById]
143 
	@p_IMP_MASTER_ID	VARCHAR(15)
144 
AS
145 
SELECT A.* , B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
146 
FROM ASS_IMPORT_UPDATE_MASTER A
147 
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
148 
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
149 
WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID
150 
GO
151 
IF @@ERROR <> 0 SET NOEXEC ON
152 
GO
153 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Del]'
154 
GO
155 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Del]
156 
	@p_IMP_MASTER_ID	VARCHAR(15)
157 
AS
158
159 
	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'U'))
160 
	BEGIN
161 
		SELECT '-1' Result, N'Phi?u ang ch? ph? duy?t. Xo? th?t b?i' ErrorDesc
162 
		RETURN '-1'
163 
	END
164
165 
  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
166 
	BEGIN
167 
		SELECT '-1' Result, N'Phi?u ? ??c ph? duy?t. Xo? th?t b?i' ErrorDesc
168 
		RETURN '-1'
169 
	END
170
171
172 
BEGIN TRANSACTION
173 
		DELETE ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
174 
    DELETE ASS_IMPORT_UPDATE_DT WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
175
176 
		IF @@Error <> 0 GOTO ABORT
177
178 
COMMIT TRANSACTION
179 
SELECT '0' as Result, '' ErrorDesc
180 
RETURN '0'
181
182 
ABORT:
183 
BEGIN
184 
		ROLLBACK TRANSACTION
185 
		SELECT '-1' as Result, '' ErrorDesc
186 
		RETURN '-1'
187 
End
188 
GO
189 
IF @@ERROR <> 0 SET NOEXEC ON
190 
GO
191 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Ins]'
192 
GO
193 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Ins]
194 
@p_NOTES NVARCHAR(4000) = NULL,
195 
@p_RECORD_STATUS	varchar(1)  = NULL,
196 
@p_MAKER_ID VARCHAR(100)  = NULL,
197 
@p_CREATE_DT	VARCHAR(50) = NULL,
198 
@p_AUTH_STATUS	VARCHAR(50)  = NULL,
199 
@p_CHECKER_ID VARCHAR(100)  = NULL,
200 
@p_APPROVE_DT	VARCHAR(50) = NULL,
201 
@p_JSON_DATA NVARCHAR(MAX) = NULL
202 
AS
203
204 
  DECLARE
205 
          @ASSET_ID VARCHAR(15),
206 
          @BRANCH_ID VARCHAR(15),
207 
          @DEP_ID VARCHAR(15),
208 
          @EMP_ID VARCHAR(15),
209 
          @NOTES NVARCHAR(4000),
210 
          @ASSET_DESC NVARCHAR(4000),
211 
          @ASSET_SERIAL_NO NVARCHAR(4000),
212 
          @ASSET_ID_OLD VARCHAR(15),
213 
          @BRANCH_ID_OLD VARCHAR(15),
214 
          @DEP_ID_OLD VARCHAR(15),
215 
          @EMP_ID_OLD VARCHAR(15),
216 
          @NOTES_OLD NVARCHAR(4000),
217 
          @ASSET_DESC_OLD NVARCHAR(4000),
218 
          @ASSET_SERIAL_NO_OLD NVARCHAR(4000)
219
220 
  DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
221 
  SELECT *
222 
  FROM OPENJSON(@p_JSON_DATA)
223 
  WITH
224 
  (
225 
  ASSET_ID	VARCHAR(15) '$.ASSET_ID',
226 
  BRANCH_ID	VARCHAR(15) '$.BRANCH_ID',
227 
  DEP_ID VARCHAR(15) '$.DEP_ID',
228 
  EMP_ID VARCHAR(15) '$.EMP_ID',
229 
  NOTES NVARCHAR(4000) '$.NOTES',
230 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC',
231 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO',
232 
  BRANCH_ID_OLD	VARCHAR(15) '$.BRANCH_ID_OLD',
233 
  DEP_ID_OLD VARCHAR(15) '$.DEP_ID_OLD',
234 
  EMP_ID_OLD VARCHAR(15) '$.EMP_ID_OLD',
235 
  NOTES_OLD NVARCHAR(4000) '$.NOTES_OLD',
236 
  ASSET_DESC_OLD NVARCHAR(4000) '$.ASSET_DESC_OLD',
237 
  ASSET_SERIAL_NO_OLD NVARCHAR(4000) '$.ASSET_SERIAL_NO_OLD'
238 
  )
239
240 
  OPEN cur
241
242
243 
BEGIN TRANSACTION
244
245 
    DECLARE @p_IMP_MASTER_ID VARCHAR(15)
246 
		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_MASTER', @p_IMP_MASTER_ID out
247 
		IF @p_IMP_MASTER_ID ='' OR @p_IMP_MASTER_ID IS NULL GOTO ABORT
248
249 
    INSERT INTO ASS_IMPORT_UPDATE_MASTER (IMP_MASTER_ID, NOTES, RECORD_STATUS, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT)
250 
    VALUES (@p_IMP_MASTER_ID, @p_NOTES, '1', 'E', @p_MAKER_ID, GETDATE(), NULL, NULL);
251
252
253 
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
254
255 
    WHILE @@FETCH_STATUS = 0 BEGIN
256
257 
      DECLARE @l_IMP_DT_ID VARCHAR(15)
258 
  		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_DT', @l_IMP_DT_ID out
259 
  		IF @l_IMP_DT_ID='' OR @l_IMP_DT_ID IS NULL GOTO ABORT
260
261 
      INSERT INTO ASS_IMPORT_UPDATE_DT (IMP_DT_ID, IMP_MASTER_ID, ASSET_ID, BRANCH_ID, DEP_ID, EMP_ID, NOTES, ASSET_DESC, ASSET_SERIAL_NO, BRANCH_ID_OLD, DEP_ID_OLD, EMP_ID_OLD, NOTES_OLD, ASSET_DESC_OLD, ASSET_SERIAL_NO_OLD)
262 
      VALUES (@l_IMP_DT_ID, @p_IMP_MASTER_ID, @ASSET_ID, @BRANCH_ID, @DEP_ID, @EMP_ID, @NOTES, @ASSET_DESC, @ASSET_SERIAL_NO,  @BRANCH_ID_OLD, @DEP_ID_OLD, @EMP_ID_OLD, @NOTES_OLD, @ASSET_DESC_OLD, @ASSET_SERIAL_NO_OLD);
263
264 
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
265
266 
    END
267
268 
    CLOSE cur
269 
    DEALLOCATE cur
270
271
272
273 
    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
274 
    VALUES (@p_IMP_MASTER_ID, 'CREATE', @p_MAKER_ID, GETDATE(), N'Nh?n vi?n th?m m?i', N'Th?m m?i import t?i s?n th?nh c?ng');
275
276
277 
		IF @@ERROR <> 0 GOTO ABORT
278
279 
COMMIT TRANSACTION
280 
SELECT '0' as Result,@p_IMP_MASTER_ID ID, N'Th?m m?i th?nh c?ng' ErrorDesc
281 
RETURN '0'
282 
ABORT:
283 
BEGIN
284 
		ROLLBACK TRANSACTION
285 
		SELECT '-1' AS RESULT
286 
		RETURN '-1'
287 
End
288 
GO
289 
IF @@ERROR <> 0 SET NOEXEC ON
290 
GO
291 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Search]'
292 
GO
293 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Search]
294 
		@p_IMP_MASTER_ID	VARCHAR(15)  = NULL,
295 
		@p_NOTES	NVARCHAR(4000)  = NULL,
296 
		@p_MAKER_NAME	NVARCHAR(1000)  = NULL,
297 
		@p_TOP INT		= NULL
298 
AS
299 
BEGIN -- PAGING
300
301 
	IF @p_TOP IS NULL OR @p_TOP  = ''
302 
	BEGIN
303 
	-- PAGING BEGIN
304 
		SELECT A.*,B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
305 
		-- SELECT END
306 
		FROM ASS_IMPORT_UPDATE_MASTER A
307 
    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
308 
    LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
309 
    WHERE 1=1
310 
    AND (@p_IMP_MASTER_ID = A.IMP_MASTER_ID OR @p_IMP_MASTER_ID IS NULL OR @p_IMP_MASTER_ID = '')
311 
    AND (B.TLFullName  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_MAKER_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_NAME IS NULL OR @p_MAKER_NAME = '')
312 
    AND (A.NOTES  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_NOTES + N'%' COLLATE Latin1_general_CI_AI OR @p_NOTES IS NULL OR @p_NOTES = '')
313 
		ORDER BY A.CREATE_DT DESC
314 
	-- PAGING END
315 
	END
316 
	ELSE
317 
	BEGIN
318 
	-- PAGING BEGIN
319 
		SELECT TOP (@p_TOP)	A.*, B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
320 
		-- SELECT END
321 
		FROM ASS_IMPORT_UPDATE_MASTER A
322 
    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
323 
    LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
324 
    WHERE 1=1
325 
    AND (@p_IMP_MASTER_ID = A.IMP_MASTER_ID OR @p_IMP_MASTER_ID IS NULL OR @p_IMP_MASTER_ID = '')
326 
    AND (B.TLFullName  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_MAKER_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_NAME IS NULL OR @p_MAKER_NAME = '')
327 
    AND (A.NOTES  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_NOTES + N'%' COLLATE Latin1_general_CI_AI OR @p_NOTES IS NULL OR @p_NOTES = '')
328 
		ORDER BY A.CREATE_DT DESC
329 
	-- PAGING END
330 
	END
331 
END -- PAGING
332 
GO
333 
IF @@ERROR <> 0 SET NOEXEC ON
334 
GO
335 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_SendAppr]'
336 
GO
337 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_SendAppr]
338 
	@p_IMP_MASTER_ID	VARCHAR(15)
339 
AS
340
341 
	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'U'))
342 
	BEGIN
343 
		SELECT '-1' Result, N'Phi?u ? ??c g?i ph? duy?t' ErrorDesc
344 
		RETURN '-1'
345 
	END
346
347 
  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
348 
	BEGIN
349 
		SELECT '-1' Result, N'Phi?u ? ??c ph? duy?t' ErrorDesc
350 
		RETURN '-1'
351 
	END
352
353
354 
BEGIN TRANSACTION
355 
		UPDATE ASS_IMPORT_UPDATE_MASTER SET AUTH_STATUS = 'U' WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
356
357 
		IF @@Error <> 0 GOTO ABORT
358
359 
COMMIT TRANSACTION
360 
SELECT '0' as Result, N'G?i ph? duy?t th?nh c?ng' ErrorDesc
361 
RETURN '0'
362
363 
ABORT:
364 
BEGIN
365 
		ROLLBACK TRANSACTION
366 
		SELECT '-1' as Result, '' ErrorDesc
367 
		RETURN '-1'
368 
End
369 
GO
370 
IF @@ERROR <> 0 SET NOEXEC ON
371 
GO
372 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Upd]'
373 
GO
374 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Upd]
375 
@p_IMP_MASTER_ID VARCHAR(15) = NULL,
376 
@p_NOTES NVARCHAR(4000) = NULL,
377 
@p_RECORD_STATUS	varchar(1)  = NULL,
378 
@p_MAKER_ID VARCHAR(100)  = NULL,
379 
@p_CREATE_DT	VARCHAR(50) = NULL,
380 
@p_AUTH_STATUS	VARCHAR(50)  = NULL,
381 
@p_CHECKER_ID VARCHAR(100)  = NULL,
382 
@p_APPROVE_DT	VARCHAR(50) = NULL,
383 
@p_JSON_DATA NVARCHAR(MAX) = NULL
384 
AS
385
386 
  DECLARE
387 
          @ASSET_ID VARCHAR(15),
388 
          @BRANCH_ID VARCHAR(15),
389 
          @DEP_ID VARCHAR(15),
390 
          @EMP_ID VARCHAR(15),
391 
          @NOTES NVARCHAR(4000),
392 
          @ASSET_DESC NVARCHAR(4000),
393 
          @ASSET_SERIAL_NO NVARCHAR(4000),
394 
          @ASSET_ID_OLD VARCHAR(15),
395 
          @BRANCH_ID_OLD VARCHAR(15),
396 
          @DEP_ID_OLD VARCHAR(15),
397 
          @EMP_ID_OLD VARCHAR(15),
398 
          @NOTES_OLD NVARCHAR(4000),
399 
          @ASSET_DESC_OLD NVARCHAR(4000),
400 
          @ASSET_SERIAL_NO_OLD NVARCHAR(4000)
401
402 
  DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
403 
  SELECT *
404 
  FROM OPENJSON(@p_JSON_DATA)
405 
  WITH
406 
  (
407 
  ASSET_ID	VARCHAR(15) '$.ASSET_ID',
408 
  BRANCH_ID	VARCHAR(15) '$.BRANCH_ID',
409 
  DEP_ID VARCHAR(15) '$.DEP_ID',
410 
  EMP_ID VARCHAR(15) '$.EMP_ID',
411 
  NOTES NVARCHAR(4000) '$.NOTES',
412 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC',
413 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO',
414 
  BRANCH_ID_OLD	VARCHAR(15) '$.BRANCH_ID_OLD',
415 
  DEP_ID_OLD VARCHAR(15) '$.DEP_ID_OLD',
416 
  EMP_ID_OLD VARCHAR(15) '$.EMP_ID_OLD',
417 
  NOTES_OLD NVARCHAR(4000) '$.NOTES_OLD',
418 
  ASSET_DESC_OLD NVARCHAR(4000) '$.ASSET_DESC_OLD',
419 
  ASSET_SERIAL_NO_OLD NVARCHAR(4000) '$.ASSET_SERIAL_NO_OLD'
420 
  )
421
422 
  OPEN cur
423
424
425 
BEGIN TRANSACTION
426
427 
    UPDATE ASS_IMPORT_UPDATE_MASTER
428 
    SET NOTES = @p_NOTES
429 
    WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
430
431
432 
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
433
434 
    WHILE @@FETCH_STATUS = 0 BEGIN
435
436 
      DECLARE @l_IMP_DT_ID VARCHAR(15)
437 
  		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_DT', @l_IMP_DT_ID out
438 
  		IF @l_IMP_DT_ID='' OR @l_IMP_DT_ID IS NULL GOTO ABORT
439
440 
      INSERT INTO ASS_IMPORT_UPDATE_DT (IMP_DT_ID, IMP_MASTER_ID, ASSET_ID, BRANCH_ID, DEP_ID, EMP_ID, NOTES, ASSET_DESC, ASSET_SERIAL_NO, BRANCH_ID_OLD, DEP_ID_OLD, EMP_ID_OLD, NOTES_OLD, ASSET_DESC_OLD, ASSET_SERIAL_NO_OLD)
441 
      VALUES (@l_IMP_DT_ID, @p_IMP_MASTER_ID, @ASSET_ID, @BRANCH_ID, @DEP_ID, @EMP_ID, @NOTES, @ASSET_DESC, @ASSET_SERIAL_NO,  @BRANCH_ID_OLD, @DEP_ID_OLD, @EMP_ID_OLD, @NOTES_OLD, @ASSET_DESC_OLD, @ASSET_SERIAL_NO_OLD);
442
443 
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
444
445 
    END
446
447 
    CLOSE cur
448 
    DEALLOCATE cur
449
450
451
452 
    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
453 
    VALUES (@p_IMP_MASTER_ID, 'UPDATE', @p_MAKER_ID, GETDATE(), N'Nh?n vi?n c?p nh?t', N'C?p nh?t import t?i s?n th?nh c?ng');
454
455
456 
		IF @@ERROR <> 0 GOTO ABORT
457
458 
COMMIT TRANSACTION
459 
SELECT '0' as Result,@p_IMP_MASTER_ID ID, N'C?p nh?t th?nh c?ng' ErrorDesc
460 
RETURN '0'
461 
ABORT:
462 
BEGIN
463 
		ROLLBACK TRANSACTION
464 
		SELECT '-1' AS RESULT
465 
		RETURN '-1'
466 
End
467 
GO
468 
IF @@ERROR <> 0 SET NOEXEC ON
469 
GO
470 
PRINT N'Creating [dbo].[CM_DEPARTMENT_GET_BLOCK_CENTER]'
471 
GO
472 
CREATE PROCEDURE [dbo].[CM_DEPARTMENT_GET_BLOCK_CENTER]
473 
	@p_DEP_ID_INPUT VARCHAR(15),
474 
	@p_BLOCK_ID VARCHAR(15)  OUT,
475 
  @p_CENTER_ID VARCHAR(15)  OUT,
476 
  @p_DEP_ID VARCHAR(15)  OUT
477 
AS
478
479 
SELECT
480 
@p_BLOCK_ID = (CASE WHEN DP.TYPE = 'K' THEN DP.DEP_ID ELSE K.DEP_ID END),
481 
@p_CENTER_ID = (CASE WHEN DP.TYPE = 'TT' THEN DP.DEP_ID ELSE TT.DEP_ID END),
482 
@p_DEP_ID = (CASE WHEN DP.TYPE = 'PB' THEN DP.DEP_ID ELSE NULL END)
483
484 
FROM CM_DEPARTMENT DP
485 
LEFT JOIN CM_DEPARTMENT TT ON DP.FATHER_ID = TT.DEP_ID --TRUNG T?M
486 
LEFT JOIN CM_DEPARTMENT K ON DP.KHOI_ID = K.DEP_ID --KH?I
487 
WHERE DP.DEP_ID = @p_DEP_ID_INPUT
488 
GO
489 
IF @@ERROR <> 0 SET NOEXEC ON
490 
GO
491 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Import]'
492 
GO
493 
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Import]
494 
	@P_JSON_DATA NVARCHAR(MAX)
495 
AS
496 
BEGIN
497
498 
	DECLARE @TMPASSETCODE TABLE(
499 
		ASSET_CODE		VARCHAR(100),
500 
		BRANCH_CODE		VARCHAR(100),
501 
		DEP_CODE		VARCHAR(100),
502 
    EMP_CODE		VARCHAR(100),
503 
    NOTES NVARCHAR(4000),
504 
    ASSET_DESC NVARCHAR(4000),
505 
    ASSET_SERIAL_NO NVARCHAR(4000)
506
507 
	)
508
509 
	INSERT INTO @TMPASSETCODE
510 
  SELECT *
511 
  FROM OPENJSON(@P_JSON_DATA)
512 
  WITH
513 
  (
514 
  ASSET_CODE	VARCHAR(100) '$.ASSET_CODE',
515 
  BRANCH_CODE	VARCHAR(1000) '$.BRANCH_CODE',
516 
  DEP_CODE VARCHAR(100) '$.DEP_CODE',
517 
  EMP_CODE VARCHAR(100) '$.EMP_CODE',
518 
  NOTES NVARCHAR(4000) '$.NOTES',
519 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC',
520 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO'
521 
  )
522
523 
	DECLARE @ERROR_MESSAGE NVARCHAR(MAX)
524
525 
  SELECT @ERROR_MESSAGE = STUFF( (
526 
  SELECT ',' + A.ROW FROM (
527 
  SELECT CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS ROW, T.ASSET_CODE
528 
  FROM @TMPASSETCODE T ) A
529 
  WHERE A.ASSET_CODE IS NULL
530 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
531
532 
	IF(LEN(@ERROR_MESSAGE) > 0)
533 
	BEGIN
534 
		SET @ERROR_MESSAGE = N'D?ng: ' + @ERROR_MESSAGE + N' m? t?i s?n kh?ng ??c ? tr?ng'
535 
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
536 
		RETURN '-1'
537 
	END
538
539 
  SELECT @ERROR_MESSAGE = STUFF( (
540 
  SELECT ',' + A.ROW FROM (
541 
  SELECT CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS ROW, T.BRANCH_CODE
542 
  FROM @TMPASSETCODE T
543 
  LEFT JOIN ASS_MASTER B ON T.ASSET_CODE = B.ASSET_CODE
544 
  WHERE B.BRANCH_ID IS NOT NULL AND B.BRANCH_ID <> '' ) A
545 
  WHERE A.BRANCH_CODE IS NULL
546 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
547
548 
	IF(LEN(@ERROR_MESSAGE) > 0)
549 
	BEGIN
550 
		SET @ERROR_MESSAGE = N'D?ng: ' + @ERROR_MESSAGE + N' m? ?n v? kh?ng ??c ? tr?ng'
551 
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
552 
		RETURN '-1'
553 
	END
554
555 
  SELECT @ERROR_MESSAGE = STUFF( (
556 
  SELECT T.EMP_CODE
557 
  FROM @TMPASSETCODE T
558 
  LEFT JOIN CM_EMPLOYEE E ON T.EMP_CODE = E.EMP_CODE
559 
  WHERE E.EMP_ID IS NULL AND T.EMP_CODE IS NOT NULL AND T.EMP_CODE <> ''
560 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
561
562 
	IF(LEN(@ERROR_MESSAGE) > 0)
563 
	BEGIN
564 
		SET @ERROR_MESSAGE = N'Ng??i d?ng kh?ng t?n t?i tr?n h? th?ng: ' + @ERROR_MESSAGE
565 
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
566 
		RETURN '-1'
567 
	END
568
569 
  SELECT @ERROR_MESSAGE = STUFF( (
570 
  SELECT N'Ng??i d?ng '  + A.EMP_CODE + N' v? t?i s?n ' + A.ASSET_CODE + N' kh?ng c?ng ?n v?/Ph?ng ban v?i nhau </br>'
571 
  FROM @TMPASSETCODE A
572 
  LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE
573 
  LEFT JOIN CM_EMPLOYEE E ON A.EMP_CODE = E.EMP_CODE
574 
  WHERE A.EMP_CODE IS NOT NULL AND A.EMP_CODE <> '' AND (ISNULL(E.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'') OR ISNULL(E.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
575 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
576
577 
	IF(LEN(@ERROR_MESSAGE) > 0)
578 
	BEGIN
579 
		SET @ERROR_MESSAGE = @ERROR_MESSAGE
580 
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
581 
		RETURN '-1'
582 
	END
583
584
585
586 
	SELECT @ERROR_MESSAGE = STUFF( (
587 
	SELECT ',' + T.ASSET_CODE
588 
  FROM @TMPASSETCODE T
589 
	LEFT JOIN ASS_MASTER B ON T.ASSET_CODE = B.ASSET_CODE
590 
  WHERE B.ASSET_ID IS NULL
591 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
592
593 
	IF(LEN(@ERROR_MESSAGE) > 0)
594 
	BEGIN
595 
		SET @ERROR_MESSAGE = N'M? t?i s?n kh?ng t?n t?i trong h? th?ng: ' + @ERROR_MESSAGE
596 
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
597 
		RETURN '-1'
598 
	END
599
600
601 
	SELECT @ERROR_MESSAGE = STUFF( (
602 
	SELECT ',' + T.ASSET_CODE FROM @TMPASSETCODE T
603 
  GROUP BY T.ASSET_CODE
604 
  HAVING COUNT(*) > 1
605 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
606
607 
	IF(LEN(@ERROR_MESSAGE) > 0)
608 
	BEGIN
609 
		SET @ERROR_MESSAGE = N'M? t?i s?n b? tr?ng: ' + @ERROR_MESSAGE
610 
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
611 
		RETURN '-1'
612 
	END
613
614
615 
	SELECT @ERROR_MESSAGE = STUFF( (
616 
	SELECT ',' + T.BRANCH_CODE FROM @TMPASSETCODE T
617 
	WHERE T.BRANCH_CODE IS NOT NULL AND T.BRANCH_CODE <> ''
618 
  AND NOT EXISTS(SELECT 1 FROM CM_BRANCH B WHERE B.BRANCH_CODE = T.BRANCH_CODE AND B.RECORD_STATUS = '1')
619 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
620
621 
	IF(LEN(@ERROR_MESSAGE) > 0)
622 
	BEGIN
623 
		SET @ERROR_MESSAGE = N'M? ?n v? kh?ng t?n t?i trong h? th?ng: ' + @ERROR_MESSAGE
624 
    EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
625 
		RETURN '-1'
626 
	END
627
628
629 
	SELECT @ERROR_MESSAGE = STUFF( (
630 
	  SELECT ',' + A.ASSET_CODE
631 
    FROM @TMPASSETCODE A
632 
    LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE
633 
    LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
634 
    LEFT JOIN CM_DEPARTMENT D ON B.DEPT_ID = D.DEP_ID
635 
    WHERE (ISNULL(C.BRANCH_CODE,'') <> ISNULL(A.BRANCH_CODE,'')
636 
          OR ISNULL(D.DEP_CODE,'') <> ISNULL(A.DEP_CODE,'')) AND A.BRANCH_CODE IS NOT NULL AND A.BRANCH_CODE <> ''
637 
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
638
639 
	IF(LEN(@ERROR_MESSAGE) > 0)
640 
	BEGIN
641 
		SET @ERROR_MESSAGE = N'Nh?ng t?i s?n c? ?n v?/Ph?ng ban kh?ng kh?p v?i t?i s?n tr?n h? th?ng: ' + @ERROR_MESSAGE
642 
    EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
643 
		RETURN '-1'
644 
	END
645
646
647 
  SELECT B.ASSET_ID,B.ASSET_CODE,B.ASSET_NAME,
648 
          G.BRANCH_ID,G.BRANCH_CODE,G.BRANCH_NAME,
649 
          H.DEP_ID,H.DEP_CODE,H.DEP_NAME,
650 
          E.EMP_ID,E.EMP_CODE,E.EMP_NAME,
651 
          A.NOTES,A.ASSET_DESC,A.ASSET_SERIAL_NO,
652
653 
          B.ASSET_ID AS ASSET_ID_OLD,B.ASSET_CODE AS ASSET_CODE_OLD,B.ASSET_NAME AS ASSET_NAME_OLD,
654 
          C.BRANCH_ID AS BRANCH_ID_OLD,C.BRANCH_CODE AS BRANCH_CODE_OLD,C.BRANCH_NAME AS BRANCH_NAME_OLD,
655 
          D.DEP_ID AS DEP_ID_OLD,D.DEP_CODE AS DEP_CODE_OLD,D.DEP_NAME AS DEP_NAME_OLD,
656 
          F.EMP_ID AS EMP_ID_OLD,F.EMP_CODE AS EMP_CODE_OLD,F.EMP_NAME AS EMP_NAME_OLD,
657 
          B.NOTES AS NOTES_OLD,B.ASSET_DESC AS ASSET_DESC_OLD,B.ASSET_SERIAL_NO AS ASSET_SERIAL_NO_OLD
658
659
660 
  FROM @TMPASSETCODE A
661 
  LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE
662
663 
  LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
664 
  LEFT JOIN CM_DEPARTMENT D ON B.DEPT_ID = D.DEP_ID
665 
  LEFT JOIN CM_EMPLOYEE F ON B.EMP_ID = F.EMP_ID
666
667
668 
  LEFT JOIN CM_BRANCH G ON A.BRANCH_CODE = G.BRANCH_CODE
669 
  LEFT JOIN CM_DEPARTMENT H ON A.DEP_CODE = H.DEP_CODE
670 
  LEFT JOIN CM_EMPLOYEE E ON A.EMP_CODE = E.EMP_CODE
671
672
673 
END
674 
GO
675 
IF @@ERROR <> 0 SET NOEXEC ON
676 
GO
677 
PRINT N'Creating [dbo].[ASS_COST_ALLOCATION_Import]'
678 
GO
679 
CREATE PROCEDURE [dbo].[ASS_COST_ALLOCATION_Import]
680 
@p_XmlData XML = NULL
681
682 
AS
683 
DECLARE
684 
@BRANCH_CODE VARCHAR(25),
685 
@DEP_CODE VARCHAR(25),
686 
@COST_AMOUNT DECIMAL(18,2),
687 
@COST_MONTH INT,
688 
@COST_RATE DECIMAL(18,2)
689
690
691 
DECLARE @TableCost  TABLE (
692 
BRANCH_ID   VARCHAR(25),
693 
DEPT_ID     VARCHAR(25),
694 
COST_AMOUNT VARCHAR(25),
695 
COST_RATE   VARCHAR(25),
696 
COST_MONTH  VARCHAR(25)
697 
)
698
699 
  Declare @hdoc INT
700 
	Exec sp_xml_preparedocument @hdoc Output,@p_XmlData
701
702 
 SELECT T.BRANCH_CODE
703 
       ,T.DEP_CODE
704 
       ,T.COST_AMOUNT
705 
       ,T.COST_MONTH
706 
       ,T.COST_RATE,
707 
       cb.BRANCH_ID,
708 
       cb.BRANCH_NAME,
709 
       cd.DEP_ID,
710 
       cd.DEP_NAME FROM
711 
  (
712 
	SELECT BRANCH_CODE,DEP_CODE,COST_AMOUNT,COST_MONTH,COST_RATE
713 
	FROM OPENXML(@hdoc,'/Root/XmlData',2)
714 
	WITH
715 
	(
716 
		BRANCH_CODE varchar(15),
717 
		DEP_CODE	VARCHAR(15),
718 
		COST_AMOUNT DECIMAL(18,2),
719 
    COST_MONTH INT,
720 
		COST_RATE DECIMAL(18,2)
721 
	)
722
723 
) T
724 
LEFT JOIN CM_BRANCH cb ON cb.BRANCH_CODE=T.BRANCH_CODE
725 
LEFT JOIN CM_DEPARTMENT cd ON cd.DEP_CODE=T.DEP_CODE
726 
GO
727 
IF @@ERROR <> 0 SET NOEXEC ON
728 
GO
729 
PRINT N'Creating [dbo].[TR_REQUEST_SHOP_DOC_Excel]'
730 
GO
731 
CREATE PROCEDURE [dbo].[TR_REQUEST_SHOP_DOC_Excel]
732 
@p_REQ_ID	varchar(15)  = NULL,
733 
@p_REQ_CODE	nvarchar(100)  = NULL,
734 
@p_REQ_NAME	nvarchar(200)  = NULL,
735 
@p_REQ_DT	VARCHAR(20) = NULL,
736 
@p_REQ_TYPE	varchar(20) = NULL,
737 
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
738 
@p_TOTAL_AMT	decimal = NULL,
739 
@p_NOTES	nvarchar(1000)  = NULL,
740 
@p_RECORD_STATUS	varchar(1)  = NULL,
741 
@p_MAKER_ID NVARCHAR(100)  = NULL,
742 
@p_CREATE_DT	VARCHAR(20) = NULL,
743 
@p_AUTH_STATUS	varchar(50)  = NULL,
744 
@p_CHECKER_ID VARCHAR(100)  = NULL,
745 
@p_APPROVE_DT	VARCHAR(20) = NULL,
746 
@p_RECEIVE_BRANCH VARCHAR(15) = NULL,
747 
@p_USERNAME VARCHAR(100) = NULL,
748 
@p_BRANCH_ID VARCHAR(15)=NULL,
749 
@p_DEP_ID VARCHAR(15)=NULL,
750 
@p_STATUS  VARCHAR(15)=NULL,
751 
@p_TOP	INT = 10,
752 
@p_DVKD_MANAGE_APP_FROM	VARCHAR(20) = NULL,
753 
@p_DVKD_MANAGE_APP_TO	VARCHAR(20) = NULL,
754 
@p_REGION_ID varchar(15)  = NULL,
755 
@p_CDTYPE_PYC VARCHAR(20) = NULL --Phucvh Truy?n th?m CDTYPE ? x?c ?nh lo?i PYC
756
757
758 
AS
759 
BEGIN -- PAGING
760 
    DECLARE @ROLE_KT VARCHAR(50) = (SELECT TU.RoleName FROM TL_USER TU WHERE TU.TLNANME = @p_USERNAME)
761 
	DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)
762 
	DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
763 
--	INSERT INTO @ROLE_LOGIN SELECT DisplayName AS ROLE_USER FROM AbpRoles WHERE Id IN (SELECT A.RoleId FROM AbpUserRoles A
764 
--																		LEFT JOIN TL_USER B ON A.UserId = B.ID
765 
--																		WHERE B.TLNANME = @p_USERNAME)
766 
  INSERT INTO @ROLE_LOGIN
767 
  SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME) tugr
768 
	SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
769 
	SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
770 
	DECLARE @lstCOST TABLE
771 
	(
772 
		COST_ID VARCHAR(20)
773 
	)
774 
	INSERT INTO @lstCOST
775 
	SELECT COST_ID FROM dbo.CM_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID
776 
	-- TienLee 11/14/21 --
777
778 
	--
779
780 
	DECLARE @TempSTATUS   TABLE
781 
	(
782 
		STATUS VARCHAR(20)
783 
	)
784
785 
	IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='QLTS' ))
786 
	BEGIN
787 
		INSERT INTO @TempSTATUS VALUES('DVKD')
788 
		INSERT INTO @TempSTATUS VALUES('DVCM')
789 
	END
790 
	ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' ))
791 
	BEGIN
792 
		INSERT INTO @TempSTATUS VALUES('QLTS_N')
793 
		INSERT INTO @TempSTATUS VALUES('DVCM')
794 
	END
795 
	ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='CVMS' ))
796 
	BEGIN
797 
		INSERT INTO @TempSTATUS VALUES('QLTS_N')
798 
		INSERT INTO @TempSTATUS VALUES('QLTS_XL')
799 
		INSERT INTO @TempSTATUS VALUES('DVCM')
800 
	END
801
802 
		DECLARE @lstBRANCH_DEP TABLE
803 
	(
804 
		BRANCH_ID VARCHAR(20),
805 
		DEP_ID VARCHAR(20)
806 
	)
807
808 
--	IF(NOT EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' OR ROLE_USER='CVMS_TBP' ))
809 
--	BEGIN
810 
--		INSERT INTO @lstBRANCH_DEP
811 
--		(BRANCH_ID,DEP_ID)
812 
--		SELECT TLSUBBRID,DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME
813 
--
814 
--		INSERT INTO @lstBRANCH_DEP
815 
--		(BRANCH_ID,DEP_ID)
816 
--		SELECT BRANCH_ID,DEP_ID FROM dbo.CM_KHOI_DT
817 
--		WHERE KHOI_ID IN (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID= @DEP_ID AND BRANCH_ID=@BRANCH_ID)
818 
--	END
819 
--
820 
--
821 
--
822 
--  DECLARE @tbDep TABLE(DEP_ID VARCHAR(20))
823 
--INSERT INTO @tbDep
824 
--SELECT ckd.DEP_ID FROM CM_DEPARTMENT cd
825 
----LEFT JOIN CM_KHOI ck ON cd.DEP_CODE=ck.KHOI_CODE
826 
----LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID
827 
--WHERE cd.DEP_ID=@p_DEP_ID
828
829 
DECLARE @REQ_ID_Temp TABLE(REQ_ID VARCHAR(20))
830 
INSERT INTO @REQ_ID_Temp
831 
SELECT A.REQ_ID FROM TR_REQUEST_SHOP_DOC A
832 
LEFT JOIN PL_REQUEST_PROCESS B ON A.REQ_ID = B.REQ_ID AND B.STATUS = 'C'
833 
WHERE (EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID
834 
    AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001')
835 
    AND RL.ROLE_USER = B.ROLE_USER)
836 
  OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
837 
    JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.REQ_ID AND C.STATUS = 'P' AND RL.BRANCH_ID = C.BRANCH_ID
838 
    AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001')
839 
    AND RL.ROLE_USER = C.ROLE_USER)
840 
  OR A.MAKER_ID = @p_USERNAME)
841 
GROUP BY A.REQ_ID
842
843 
	IF((EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_SLAS' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
844 
	BEGIN
845 
		IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
846 
			BEGIN
847 
			-- PAGING BEGIN
848 
				SELECT A.REQ_ID,
849 
					   A.REQ_CODE,
850 
					   A.REQ_NAME,
851 
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
852 
                       A.REQ_DT,
853 
					   A.REQ_TYPE,
854 
					   A.REQ_CONTENT,
855 
					   A.TOTAL_AMT,
856 
					   A.NOTES,
857 
					   A.RECORD_STATUS,
858 
					   A.MAKER_ID,
859 
					   A.CREATE_DT,
860 
					   A.AUTH_STATUS,
861 
					   A.CHECKER_ID,
862 
					   A.APPROVE_DT,
863 
					   A.BRANCH_ID,
864 
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
865 
					   A.DEP_ID,
866 
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME,
867 
                       --I.CONTENT AS REQ_STATUS_NAME,
868 
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Ho?n t?t'
869 
                       ELSE I.CONTENT
870 
                       END REQ_STATUS_NAME,
871 
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
872 
             CASE
873 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
874 
             	 ELSE G.BRANCH_NAME
875 
             END AS BRANCH_NAME,
876 
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Ch? duy?t'
877 
						--ELSE N'? duy?t' END AS AUTH_STATUS_NAME
878 
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
879 
            I.CONTENT AS REQ_TYPE_NAME,
880 
             CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R'
881 
                      AND A.AUTH_STATUS <> 'E' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
882 
            END AS COLOR
883 
						--D.AUTH_STATUS_NAME
884 
				-- SELECT END
885 
				FROM TR_REQUEST_SHOP_DOC A
886
887 
				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
888 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
889 
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
890 
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
891 
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
892 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
893 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID
894 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
895 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
896 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
897
898 
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
899 
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
900 
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
901 
				WHERE 1 = 1
902 
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
903 
--				AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
904 
--				AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
905 
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
906 
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103))
907 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
908 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
909 
            	)
910 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
911 
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))
912 
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%'
913 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
914 
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
915 
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
916 
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
917 
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
918 
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
919 
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
920 
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
921
922 
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
923 
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
924 
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
925 
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
926 
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
927 
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
928 
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
929 
                WHERE US.TLNANME = @p_USERNAME))
930 
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
931 
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID)
932 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' )
933 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
934 
                WHERE US.TLNANME = @p_USERNAME))
935
936 
				AND A.RECORD_STATUS = '1'
937 
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
938 
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
939 
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI
940 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
941
942 
				ORDER BY A.REQ_DT DESC
943 
			-- PAGING END
944 
			END
945 
		   ELSE
946 
		   BEGIN
947 
		   -- PAGING BEGIN
948 
				SELECT TOP(CONVERT(INT,@p_TOP))  A.REQ_ID,
949 
					   A.REQ_CODE,
950 
					   A.REQ_NAME,
951 
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
952 
                       A.REQ_DT,
953 
					   A.REQ_TYPE,
954 
					   A.REQ_CONTENT,
955 
					   A.TOTAL_AMT,
956 
					   A.NOTES,
957 
					   A.RECORD_STATUS,
958 
					   A.MAKER_ID,
959 
					   A.CREATE_DT,
960 
					   A.AUTH_STATUS,
961 
					   A.CHECKER_ID,
962 
					   A.APPROVE_DT,
963 
					   A.BRANCH_ID,
964 
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
965 
					   A.DEP_ID,
966 
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME,
967 
                       --I.CONTENT AS REQ_STATUS_NAME,
968 
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Ho?n t?t'
969 
                       ELSE I.CONTENT
970 
                       END REQ_STATUS_NAME,
971 
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
972 
              CASE
973 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
974 
             	 ELSE G.BRANCH_NAME
975 
              END AS BRANCH_NAME,
976 
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Ch? duy?t'
977 
						--ELSE N'? duy?t' END AS AUTH_STATUS_NAME
978 
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
979 
            I.CONTENT AS REQ_TYPE_NAME,
980 
             CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R'
981 
                      AND A.AUTH_STATUS <> 'E' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
982 
            END AS COLOR
983 
						--D.AUTH_STATUS_NAME
984 
				-- SELECT END
985 
				FROM TR_REQUEST_SHOP_DOC A
986
987 
				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
988 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
989 
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
990 
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
991 
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
992
993 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
994 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID
995 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
996 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
997 
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
998
999 
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
1000 
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
1001 
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
1002 
				WHERE 1 = 1
1003 
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
1004 
			--	AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
1005 
			--	AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
1006 
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
1007 
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103))
1008 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
1009 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
1010 
            	)
1011 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
1012 
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))
1013 
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%'
1014 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
1015 
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
1016 
        AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
1017 
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
1018 
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
1019 
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1020 
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1021 
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
1022
1023 
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
1024 
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
1025 
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
1026 
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
1027 
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
1028 
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
1029 
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
1030 
                WHERE US.TLNANME = @p_USERNAME))
1031 
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
1032 
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID)
1033 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' )
1034 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
1035 
                WHERE US.TLNANME = @p_USERNAME))
1036 
				AND A.RECORD_STATUS = '1'
1037 
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
1038 
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1039 
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI
1040 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
1041 
				ORDER BY A.REQ_DT DESC
1042 
			 -- PAGING END
1043 
		END
1044 
	END
1045 
	ELSE
1046 
	BEGIN
1047 
		IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
1048 
			BEGIN
1049 
			-- PAGING BEGIN
1050 
				SELECT  A.REQ_ID,
1051 
					   A.REQ_CODE,
1052 
					   A.REQ_NAME,
1053 
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
1054 
                       A.REQ_DT,
1055 
					   A.REQ_TYPE,
1056 
					   A.REQ_CONTENT,
1057 
					   A.TOTAL_AMT,
1058 
					   A.NOTES,
1059 
					   A.RECORD_STATUS,
1060 
					   A.MAKER_ID,
1061 
					   A.CREATE_DT,
1062 
					   A.AUTH_STATUS,
1063 
					   A.CHECKER_ID,
1064 
					   A.APPROVE_DT,
1065 
					   A.BRANCH_ID,
1066 
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
1067 
					   A.DEP_ID,
1068 
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME,
1069 
                       --I.CONTENT AS REQ_STATUS_NAME,
1070 
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Ho?n t?t'
1071 
                       ELSE I.CONTENT
1072 
                       END REQ_STATUS_NAME,
1073 
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
1074 
              CASE
1075 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
1076 
             	 ELSE G.BRANCH_NAME
1077 
              END AS BRANCH_NAME,
1078 
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Ch? duy?t'
1079 
						--ELSE N'? duy?t' END AS AUTH_STATUS_NAME
1080 
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
1081 
            CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R'
1082 
                  AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
1083 
            END AS COLOR
1084 
						--D.AUTH_STATUS_NAME
1085 
				-- SELECT END
1086 
				FROM TR_REQUEST_SHOP_DOC A
1087
1088 
				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
1089 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
1090 
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
1091 
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
1092 
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
1093
1094 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
1095 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID
1096 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
1097 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
1098 
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
1099
1100 
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
1101 
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
1102 
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
1103 
				WHERE 1 = 1
1104 
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
1105 
--				AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
1106 
--				AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
1107 
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
1108 
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103))
1109 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
1110 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
1111 
            	)
1112 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
1113 
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))
1114 
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%'
1115 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
1116 
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
1117 
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
1118 
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
1119 
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
1120 
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1121 
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1122 
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
1123
1124 
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
1125 
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
1126 
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
1127 
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
1128 
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
1129 
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
1130 
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
1131 
                WHERE US.TLNANME = @p_USERNAME))
1132 
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
1133 
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID)
1134 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' )
1135 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
1136 
                WHERE US.TLNANME = @p_USERNAME))
1137
1138 
				AND A.RECORD_STATUS = '1'
1139 
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
1140 
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1141 
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI
1142 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
1143 
				ORDER BY  A.REQ_DT  DESC
1144 
			-- PAGING END
1145 
			END
1146 
		   ELSE
1147 
		   BEGIN
1148 
		   -- PAGING BEGIN
1149 
				SELECT TOP(CONVERT(INT,@p_TOP))  A.REQ_ID,
1150 
					   A.REQ_CODE,
1151 
					   A.REQ_NAME,
1152 
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
1153 
                       A.REQ_DT,
1154 
					   A.REQ_TYPE,
1155 
					   A.REQ_CONTENT,
1156 
					   A.TOTAL_AMT,
1157 
					   A.NOTES,
1158 
					   A.RECORD_STATUS,
1159 
					   A.MAKER_ID,
1160 
					   A.CREATE_DT,
1161 
					   A.AUTH_STATUS,
1162 
					   A.CHECKER_ID,
1163 
					   A.APPROVE_DT,
1164 
					   A.BRANCH_ID,
1165 
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
1166 
					   A.DEP_ID,
1167 
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME,
1168 
                       --I.CONTENT AS REQ_STATUS_NAME,
1169 
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Ho?n t?t'
1170 
                       ELSE I.CONTENT
1171 
                       END REQ_STATUS_NAME,
1172 
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
1173 
              CASE
1174 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
1175 
             	 ELSE G.BRANCH_NAME
1176 
              END AS BRANCH_NAME,
1177 
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Ch? duy?t'
1178 
						--ELSE N'? duy?t' END AS AUTH_STATUS_NAME
1179 
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
1180 
            CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R'
1181 
                  AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
1182 
            END AS COLOR
1183 
						--D.AUTH_STATUS_NAME
1184 
				-- SELECT END
1185 
				FROM TR_REQUEST_SHOP_DOC A
1186
1187 
				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
1188 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
1189 
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
1190 
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
1191 
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
1192
1193 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
1194 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID
1195 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
1196 
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
1197 
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
1198
1199 
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
1200 
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
1201 
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
1202 
				WHERE 1 = 1
1203 
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
1204 
				--AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
1205 
				--AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
1206 
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
1207 
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103))
1208 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
1209 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
1210 
            	)
1211 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
1212 
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))
1213 
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%'
1214 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
1215 
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
1216 
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
1217 
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
1218 
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
1219 
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1220 
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1221 
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
1222
1223 
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
1224 
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
1225 
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
1226 
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
1227 
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
1228 
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
1229 
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
1230 
                WHERE US.TLNANME = @p_USERNAME))
1231 
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
1232 
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID)
1233 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' )
1234 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
1235 
                WHERE US.TLNANME = @p_USERNAME))
1236
1237 
				AND A.RECORD_STATUS = '1'
1238 
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
1239 
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
1240 
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI
1241 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
1242 
				ORDER BY A.REQ_DT  DESC
1243 
			 -- PAGING END
1244 
		END
1245 
	END
1246
1247
1248
1249 
   END
1250 
GO
1251 
IF @@ERROR <> 0 SET NOEXEC ON
1252 
GO
1253 
COMMIT TRANSACTION
1254 
GO
1255 
IF @@ERROR <> 0 SET NOEXEC ON
1256 
GO
1257 
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
1258 
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
1259 
BEGIN
1260 
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
1261 
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
1262 
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
1263 
    EXECUTE sys.xp_logevent 55000, @eventMessage
1264 
END
1265 
GO
1266 
DECLARE @Success AS BIT
1267 
SET @Success = 1
1268 
SET NOEXEC OFF
1269 
IF (@Success = 1) PRINT 'The database update succeeded'
1270 
ELSE BEGIN
1271 
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
1272 
	PRINT 'The database update failed'
1273 
END
1274 
GO
1275