Project

General

Profile

Download (76.2 KB)

FILE_1_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:12:53 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'Dropping constraints from [dbo].[CM_EMPLOYEE_TRANSFER_LOG]'
28 
GO
29 
ALTER TABLE [dbo].[CM_EMPLOYEE_TRANSFER_LOG] DROP CONSTRAINT [PK__CM_EMPLO__4364C8824F8C1B4B]
30 
GO
31 
IF @@ERROR <> 0 SET NOEXEC ON
32 
GO
33 
PRINT N'Altering [dbo].[CM_EMPLOYEE]'
34 
GO
35 
IF @@ERROR <> 0 SET NOEXEC ON
36 
GO
37 
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [EMP_CODE] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
38 
GO
39 
IF @@ERROR <> 0 SET NOEXEC ON
40 
GO
41 
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [EMP_NAME] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
42 
GO
43 
IF @@ERROR <> 0 SET NOEXEC ON
44 
GO
45 
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [POS_CODE] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
46 
GO
47 
IF @@ERROR <> 0 SET NOEXEC ON
48 
GO
49 
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [POS_NAME] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
50 
GO
51 
IF @@ERROR <> 0 SET NOEXEC ON
52 
GO
53 
PRINT N'Altering [dbo].[CM_EMPLOYEE_LOG]'
54 
GO
55 
IF @@ERROR <> 0 SET NOEXEC ON
56 
GO
57 
ALTER TABLE [dbo].[CM_EMPLOYEE_LOG] ALTER COLUMN [POS_CODE] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
58 
GO
59 
IF @@ERROR <> 0 SET NOEXEC ON
60 
GO
61 
PRINT N'Altering [dbo].[ASS_LIQUIDATION_DT]'
62 
GO
63 
IF @@ERROR <> 0 SET NOEXEC ON
64 
GO
65 
ALTER TABLE [dbo].[ASS_LIQUIDATION_DT] ADD
66 
[ASS_STATUS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
67 
[AMORT_STATUS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
68 
GO
69 
IF @@ERROR <> 0 SET NOEXEC ON
70 
GO
71 
PRINT N'Altering [dbo].[ASS_ADDNEW_PO]'
72 
GO
73 
IF @@ERROR <> 0 SET NOEXEC ON
74 
GO
75 
ALTER TABLE [dbo].[ASS_ADDNEW_PO] ADD
76 
[INVOICE_SYMPOL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
77 
GO
78 
IF @@ERROR <> 0 SET NOEXEC ON
79 
GO
80 
PRINT N'Altering [dbo].[ASS_ADDNEW_GD]'
81 
GO
82 
IF @@ERROR <> 0 SET NOEXEC ON
83 
GO
84 
ALTER TABLE [dbo].[ASS_ADDNEW_GD] ADD
85 
[INVOICE_SYMPOL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
86 
GO
87 
IF @@ERROR <> 0 SET NOEXEC ON
88 
GO
89 
PRINT N'Altering [dbo].[ASS_PO]'
90 
GO
91 
IF @@ERROR <> 0 SET NOEXEC ON
92 
GO
93 
ALTER TABLE [dbo].[ASS_PO] ADD
94 
[INVOICE_SYMPOL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
95 
GO
96 
IF @@ERROR <> 0 SET NOEXEC ON
97 
GO
98 
PRINT N'Altering [dbo].[ASS_MASTER_VIEW]'
99 
GO
100 
ALTER VIEW [dbo].[ASS_MASTER_VIEW]
101 
AS SELECT
102 
  dbo.CM_SUPPLIER.SUP_NAME
103 
 ,dbo.CM_SUPPLIER.SUP_CODE
104 
 ,dbo.CM_SUPPLIER.SUP_TYPE_ID
105 
 ,dbo.CM_SUPPLIER.ADDR
106 
 ,dbo.CM_SUPPLIER.EMAIL
107 
 ,dbo.ASS_GROUP.GROUP_CODE
108 
 ,dbo.ASS_GROUP.GROUP_NAME
109 
 ,dbo.CM_EMPLOYEE.EMP_NAME
110 
 ,dbo.CM_EMPLOYEE.EMP_CODE
111 
 ,dbo.CM_DIVISION.DIV_CODE
112 
 ,dbo.CM_DIVISION.DIV_NAME
113 
 ,dbo.CM_DEPARTMENT.DEP_CODE
114 
 ,dbo.CM_DEPARTMENT.DEP_NAME
115 
 ,dbo.CM_BRANCH.BRANCH_CODE
116 
 ,dbo.CM_BRANCH.BRANCH_NAME
117 
 ,dbo.ASS_MASTER.ASSET_ID
118 
 ,dbo.ASS_MASTER.TYPE_ID
119 
 ,dbo.ASS_MASTER.GROUP_ID
120 
 ,dbo.ASS_MASTER.ASSET_CODE
121 
 ,dbo.ASS_MASTER.ASSET_NAME
122 
 ,dbo.ASS_MASTER.ASSET_SERIAL_NO
123 
 ,dbo.ASS_MASTER.ASSET_DESC
124 
 ,dbo.ASS_MASTER.SUP_ID
125 
 ,dbo.ASS_MASTER.BUY_PRICE
126 
 ,dbo.ASS_MASTER.AMORT_AMT
127 
 ,dbo.ASS_MASTER.ASS_TYPE
128 
 ,dbo.ASS_MASTER.BRANCH_ID
129 
 ,dbo.ASS_MASTER.DEPT_ID
130 
 ,dbo.ASS_MASTER.EMP_ID
131 
 ,dbo.ASS_MASTER.DIVISION_ID
132 
 ,dbo.ASS_MASTER.BUY_DATE
133 
 ,dbo.ASS_MASTER.USE_DATE
134 
 ,dbo.ASS_MASTER.SPECIAL_ASS
135 
 ,dbo.ASS_MASTER.AMORT_MONTH
136 
 ,dbo.ASS_MASTER.AMORT_RATE
137 
 ,dbo.ASS_MASTER.AMORT_START_DATE
138 
 ,dbo.ASS_MASTER.AMORT_END_DATE
139 
 ,dbo.ASS_MASTER.FIRST_AMORT_AMT
140 
 ,dbo.ASS_MASTER.MONTHLY_AMORT_AMT
141 
 ,dbo.ASS_MASTER.AMORTIZED_MONTH
142 
 ,dbo.ASS_MASTER.AMORTIZED_AMT
143 
 ,dbo.ASS_MASTER.LIQUIDATION_DT
144 
 ,dbo.ASS_MASTER.PO_ID
145 
 ,dbo.ASS_MASTER.PD_ID
146 
 ,dbo.ASS_MASTER.WAREHOUSE_ID
147 
 ,dbo.ASS_MASTER.LOCATION
148 
 ,dbo.ASS_MASTER.REF_ASSET_ID
149 
 ,dbo.ASS_MASTER.REF_AMORTIZED_AMT
150 
 ,dbo.ASS_MASTER.WARRANTY_MONTHS
151 
 ,dbo.ASS_MASTER.NOTES
152 
 ,dbo.ASS_MASTER.AMORT_STATUS
153 
 ,dbo.ASS_MASTER.ASS_STATUS
154 
 ,dbo.ASS_MASTER.ASS_STATUS_DESC
155 
 ,dbo.ASS_MASTER.RECORD_STATUS
156 
 ,dbo.ASS_MASTER.AUTH_STATUS
157 
 ,dbo.ASS_MASTER.MAKER_ID
158 
 ,dbo.ASS_MASTER.CREATE_DT
159 
 ,dbo.ASS_MASTER.CHECKER_ID
160 
 ,dbo.ASS_MASTER.APPROVE_DT
161 
 ,dbo.ASS_PO.PO_CODE
162 
 ,dbo.TR_PO_MASTER.PO_NAME
163 
 ,ISNULL(dbo.ASS_MASTER.BUY_PRICE - dbo.ASS_MASTER.AMORTIZED_AMT, 0) AS REMAIN_VALUE
164 
 ,ISNULL(dbo.ASS_MASTER.AMORT_AMT - dbo.ASS_MASTER.AMORTIZED_AMT, 0) AS REMAIN_AMORT_AMT
165 
 ,dbo.ASS_TYPE.TYPE_CODE
166 
 ,dbo.ASS_TYPE.TYPE_NAME
167 
 ,dbo.ASS_AMORT_STATUS.STATUS_NAME AS AMORT_STATUS_NAME
168 
 --,dbo.ASS_STATUS.STATUS_NAME AS ASS_STATUS_NAME,
169 
 ,CASE WHEN ASS_MASTER.LIQ_W_STATUS = '1' AND ASS_MASTER.AMORT_STATUS = 'DTL' THEN N'? thanh l?' ELSE ASS_STATUS.STATUS_NAME
170 
 END AS ASS_STATUS_NAME
171 
 ,RA.ASSET_CODE AS REF_ASSET_CODE
172 
 ,RA.ASSET_NAME AS REF_ASSET_NAME
173 
 ,dbo.ASS_MASTER.USE_DATE_KT
174 
 ,dbo.ASS_MASTER.BUY_DATE_KT
175 
 ,dbo.ASS_MASTER.USE_STATUS
176 
 ,dbo.ASS_MASTER.BRANCH_CREATE
177 
 ,BRCR.BRANCH_NAME AS BRANCH_CREATE_NAME
178 
 ,BRCR.BRANCH_CODE AS BRANCH_CREATE_CODE
179 
 ,dbo.ASS_MASTER.ACCOUNT_GL
180 
 ,dbo.ASS_MASTER.VAT
181 
 ,dbo.ASS_MASTER.PRICE_VAT
182 
 ,OS.CONTENT AS IS_OS
183 
 ,LICENSE.CONTENT AS IS_MO_LICENSE
184 
 ,ASS_MASTER.OS
185 
 ,ASS_MASTER.MO_LICENSE,
186 
--RG.GROUP_CODE AS REF_GROUP_CODE, RG.GROUP_NAME AS REF_GROUP_NAME,
187 
--RT.TYPE_CODE AS REF_TYPE_CODE, RT.TYPE_NAME AS REF_TYPE_NAME
188 
--dbo.FN_GET_CHINHANH(dbo.ASS_MASTER.BRANCH_ID,'KV') KHU_VUC,
189 
--dbo.FN_GET_CHINHANH(dbo.ASS_MASTER.BRANCH_ID,'CN') CHI_NHANH,
190 
--D.BRANCH_NAME PGD
191 
AN.PR_CODE, --S? PR
192 
AN.CONTRACT_ID AS ASS_CONTRACT_CODE, -- S? H?P ?NG
193 
AN.PL_CODE, --S? T? TR?NH
194 
AP.INVOICE_NO --S? HO? ?N
195 
FROM dbo.ASS_MASTER
196 
LEFT OUTER JOIN dbo.ASS_GROUP
197 
  ON dbo.ASS_MASTER.GROUP_ID = dbo.ASS_GROUP.GROUP_ID
198 
LEFT OUTER JOIN dbo.CM_SUPPLIER
199 
  ON dbo.ASS_MASTER.SUP_ID = dbo.CM_SUPPLIER.SUP_ID
200 
LEFT OUTER JOIN dbo.CM_EMPLOYEE
201 
  ON dbo.ASS_MASTER.EMP_ID = dbo.CM_EMPLOYEE.EMP_ID
202 
LEFT OUTER JOIN dbo.CM_BRANCH
203 
  ON dbo.ASS_MASTER.BRANCH_ID = dbo.CM_BRANCH.BRANCH_ID
204 
LEFT OUTER JOIN dbo.CM_AUTH_STATUS
205 
  ON dbo.ASS_MASTER.AUTH_STATUS = dbo.CM_AUTH_STATUS.AUTH_STATUS
206 
LEFT OUTER JOIN dbo.CM_DEPARTMENT
207 
  ON dbo.ASS_MASTER.DEPT_ID = dbo.CM_DEPARTMENT.DEP_ID
208 
LEFT OUTER JOIN dbo.CM_DIVISION
209 
  ON dbo.ASS_MASTER.DIVISION_ID = dbo.CM_DIVISION.DIV_ID
210 
LEFT OUTER JOIN dbo.TR_PO_MASTER
211 
  ON dbo.TR_PO_MASTER.PO_ID = dbo.ASS_MASTER.PO_ID
212 
LEFT OUTER JOIN dbo.ASS_TYPE
213 
  ON dbo.ASS_TYPE.TYPE_ID = dbo.ASS_MASTER.TYPE_ID
214 
LEFT OUTER JOIN dbo.ASS_AMORT_STATUS
215 
  ON dbo.ASS_AMORT_STATUS.STATUS_CODE = dbo.ASS_MASTER.AMORT_STATUS
216 
LEFT OUTER JOIN dbo.ASS_STATUS
217 
  ON dbo.ASS_STATUS.STATUS_ID = dbo.ASS_MASTER.ASS_STATUS
218 
LEFT OUTER JOIN dbo.ASS_MASTER RA
219 
  ON RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID
220 
LEFT OUTER JOIN dbo.ASS_PO
221 
  ON dbo.ASS_MASTER.PO_ID = dbo.ASS_PO.ASSPO_ID
222 
LEFT OUTER JOIN dbo.CM_BRANCH BRCR
223 
  ON dbo.ASS_MASTER.BRANCH_CREATE = BRCR.BRANCH_ID
224 
LEFT JOIN CM_ALLCODE OS
225 
  ON ASS_MASTER.OS = OS.CDVAL
226 
    AND OS.CDNAME = 'OS_LICENSE'
227 
    AND OS.CDTYPE = 'STATUS'
228 
LEFT JOIN CM_ALLCODE LICENSE
229 
  ON ASS_MASTER.MO_LICENSE = LICENSE.CDVAL
230 
    AND LICENSE.CDNAME = 'OS_LICENSE'
231 
    AND LICENSE.CDTYPE = 'STATUS'
232 
LEFT JOIN ASS_TRANSACTIONS AT ON AT.ASSET_ID = dbo.ASS_MASTER.ASSET_ID AND AT.TRN_TYPE = 'ADD_NEW'
233 
LEFT JOIN ASS_ADDNEW AN ON AT.TRN_ID = AN.ADDNEW_ID AND AT.TRN_TYPE = 'ADD_NEW'
234 
LEFT JOIN ASS_PO AP ON AN.ADDNEW_ID = AP.ADDNEW_ID
235
236 
						 --dbo.ASS_GROUP RG ON RG.GROUP_ID = RA.GROUP_ID AND RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID LEFT JOIN
237 
						 --dbo.ASS_TYPE RT ON RG.TYPE_ID = RA.TYPE_ID AND RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID
238 
						-- LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = dbo.ASS_MASTER.BRANCH_ID
239 
GO
240 
IF @@ERROR <> 0 SET NOEXEC ON
241 
GO
242 
PRINT N'Altering [dbo].[THREAD_TIME_SEND_LOG]'
243 
GO
244 
IF @@ERROR <> 0 SET NOEXEC ON
245 
GO
246 
ALTER TABLE [dbo].[THREAD_TIME_SEND_LOG] ALTER COLUMN [MESSAGE] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
247 
GO
248 
IF @@ERROR <> 0 SET NOEXEC ON
249 
GO
250 
PRINT N'Altering [dbo].[ASS_PENDING_ITEM]'
251 
GO
252 
ALTER VIEW [dbo].[ASS_PENDING_ITEM]
253 
AS SELECT A.ADDNEW_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_ADDNEW' TRAN_TYPE, N'Nh?p m?i TSCD/CCLD' TRAN_TYPE_NAME,
254 
A.BRANCH_CREATE, A.TYPE_ID, A.BUY_PRICE AMT, A.ASSET_DESC DIENGIAI, A.CREATE_DT CREATE_DT,A.MAKER_ID_KT , A.AUTH_STATUS_KT,A.CREATE_DT_KT
255 
FROM ASS_ADDNEW A
256 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
257
258 
UNION ALL
259
260 
SELECT A.USE_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME,  A.MAKER_ID, 'ASS_USE' TRAN_TYPE, N'Xu?t s? d?ng' TRAN_TYPE_NAME,
261 
A.BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, A.NOTES DIENGIAI, A.CREATE_DT CREATE_DT, '' MAKER_ID_KT , '' AUTH_STATUS_KT,'' CREATE_DT_KT
262 
--FROM ASS_USE A
263 
FROM ASS_USE A
264 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
265 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
266
267 
UNION ALL
268
269
270 
SELECT A.TRANSFER_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_TRANSFER' TRAN_TYPE, N'Giao d?ch i?u chuy?n' TRAN_TYPE_NAME,
271 
A.BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, A.[DESCRIPTION] DIENGIAI, A.CREATE_DT CREATE_DT,'' MAKER_ID_KT , '' AUTH_STATUS_KT,'' CREATE_DT_KT
272 
FROM ASS_TRANSFER A
273 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
274 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
275
276 
--UNION ALL
277
278 
--SELECT  A.ASSET_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_MASTER' TRAN_TYPE, N'B?ng TSCD/CCLD' TRAN_TYPE_NAME,
279 
--'' BRANCH_CREATE, A.TYPE_ID
280 
--FROM ASS_MASTER A
281 
--INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
282
283 
UNION ALL
284
285 
SELECT A.COLLECT_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_COLLECT' TRAN_TYPE, N'Giao d?ch thu h?i' TRAN_TYPE_NAME,
286 
A.BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, A.COLLECT_NOTE DIENGIAI, A.CREATE_DT CREATE_DT,'' MAKER_ID_KT , '' AUTH_STATUS_KT,'' CREATE_DT_KT
287 
FROM ASS_COLLECT A
288 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
289 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
290
291 
UNION ALL
292
293 
SELECT A.GROUP_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_GROUP' TRAN_TYPE, N'Nh?m t?i s?n' TRAN_TYPE_NAME,
294 
'' BRANCH_CREATE, A.TYPE_ID, 0 AMT, A.NOTES DIENGIAI, A.CREATE_DT CREATE_DT,'' MAKER_ID_KT , '' AUTH_STATUS_KT,'' CREATE_DT_KT
295 
FROM ASS_GROUP A
296 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
297
298
299 
UNION ALL
300
301 
SELECT A.AMORT_ID ID, CASE WHEN A.PROCESS_STATUS='P' THEN 'U' ELSE 'A' END  AUTH_STATUS, CASE WHEN A.PROCESS_STATUS='P' THEN N'Ch?a th?c thi' ELSE N'? th?c thi' END AUTH_STATUS_NAME,
302 
		A.MAKER_ID, 'ASS_AMORT' TRAN_TYPE, A.NOTES TRAN_TYPE_NAME,--N'Kh?u hao'
303 
		A.BRANCH_ID BRANCH_CREATE, A.ASSET_TYPE AS [TYPE_ID],
304 
		A.TOTAL_AMT AMT, A.NOTES DIENGIAI, A.CREATE_DT CREATE_DT,'' MAKER_ID_KT , '' AUTH_STATUS_KT,'' CREATE_DT_KT
305 
FROM ASS_AMORT A
306
307
308 
UNION ALL
309
310 
SELECT  A.ASSET_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_UPDATE' TRAN_TYPE, N'C?p nh?t th?ng tin t?i s?n' TRAN_TYPE_NAME,
311 
A.BRANCH_ID BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, A.NOTES DIENGIAI, A.CREATE_DT CREATE_DT,A.MAKER_ID MAKER_ID_KT ,
312 
 A.AUTH_STATUS AUTH_STATUS_KT,A.CREATE_DT CREATE_DT_KT
313 
FROM ASS_UPDATE A
314 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
315 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
316
317 
-----thieuvq 5102016 - them dieu kien kiem tra danh sach dieu chuyen nhieu tai san chua duyet
318 
UNION ALL
319
320 
SELECT  A.ASSET_ID ID, BB.AUTH_STATUS, AA.AUTH_STATUS_NAME, BB.MAKER_ID, 'ASS_TRANSFER_MULTI_MASTER' TRAN_TYPE, N'i?u chuy?n nhi?u t?i s?n.' TRAN_TYPE_NAME,
321 
A.BRANCH_ID BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, BB.NOTES DIENGIAI, BB.CREATE_DT CREATE_DT,BB.MAKER_ID_KT , BB.AUTH_STATUS_KT,BB.CREATE_DT_KT
322 
FROM ASS_TRANSFER_MULTI_DT A
323 
INNER JOIN ASS_TRANSFER_MULTI_MASTER BB ON A.TRANS_MULTI_MASTER_ID = BB.TRANS_MULTI_MASTER_ID
324 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
325 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
326
327 
UNION ALL
328
329 
-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
330 
SELECT  A.ASSET_ID ID, BB.AUTH_STATUS, AA.AUTH_STATUS_NAME, BB.MAKER_ID, 'ASS_USE_MULTI_MASTER' TRAN_TYPE, N'Xu?t nhi?u t?i s?n.' TRAN_TYPE_NAME,
331 
A.BRANCH_ID BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, BB.NOTES DIENGIAI, BB.CREATE_DT CREATE_DT,BB.MAKER_ID_KT , BB.AUTH_STATUS_KT,BB.CREATE_DT_KT
332 
FROM ASS_USE_MULTI_DT A
333 
INNER JOIN ASS_USE_MULTI_MASTER BB ON A.USER_MASTER_ID = BB.USER_MASTER_ID
334 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
335 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
336
337 
UNION ALL
338
339 
-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
340 
SELECT  A.ASSET_ID ID, BB.AUTH_STATUS, AA.AUTH_STATUS_NAME, BB.MAKER_ID, 'ASS_COLLECT_MULTI_MASTER' TRAN_TYPE, N'Thu h?i nhi?u t?i s?n.' TRAN_TYPE_NAME,
341 
A.BRANCH_ID BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, BB.NOTES DIENGIAI, BB.CREATE_DT CREATE_DT,BB.MAKER_ID_KT , BB.AUTH_STATUS_KT,BB.CREATE_DT_KT
342 
FROM ASS_COLLECT_MULTI_DT A
343 
INNER JOIN ASS_COLLECT_MULTI_MASTER BB ON A.COL_MULTI_MASTER_ID = BB.COL_MULTI_MASTER_ID
344 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
345 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
346
347
348 
UNION ALL
349
350 
-----luctv 07012018 - them dieu kien kiem tra danh sach thanh ly nhieu tai san chua duyet
351 
SELECT  A.ASSET_ID ID, BB.AUTH_STATUS, AA.AUTH_STATUS_NAME, BB.MAKER_ID, 'ASS_LIQ_MULTI_MASTER' TRAN_TYPE, N'Thanh l? nhi?u t?i s?n.' TRAN_TYPE_NAME,
352 
BB.BRANCH_ID BRANCH_CREATE, CC.TYPE_ID, CC.BUY_PRICE AMT, BB.NOTES DIENGIAI, BB.CREATE_DT CREATE_DT,BB.MAKER_ID_KT , BB.AUTH_STATUS_KT,BB.CREATE_DT_KT
353 
FROM ASS_LIQUIDATION_DT A
354 
INNER JOIN ASS_LIQUIDATION BB ON A.LIQ_ID = BB.LIQ_ID
355 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
356 
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
357 
GO
358 
IF @@ERROR <> 0 SET NOEXEC ON
359 
GO
360 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_DT]'
361 
GO
362 
CREATE TABLE [dbo].[ASS_IMPORT_UPDATE_DT]
363 
(
364 
[IMP_DT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
365 
[IMP_MASTER_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
366 
[ASSET_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
367 
[BRANCH_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
368 
[DEP_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
369 
[EMP_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
370 
[NOTES] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
371 
[ASSET_DESC] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
372 
[ASSET_SERIAL_NO] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
373 
[BRANCH_ID_OLD] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
374 
[DEP_ID_OLD] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
375 
[EMP_ID_OLD] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
376 
[NOTES_OLD] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
377 
[ASSET_DESC_OLD] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
378 
[ASSET_SERIAL_NO_OLD] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
379 
)
380 
GO
381 
IF @@ERROR <> 0 SET NOEXEC ON
382 
GO
383 
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_MASTER]'
384 
GO
385 
CREATE TABLE [dbo].[ASS_IMPORT_UPDATE_MASTER]
386 
(
387 
[IMP_MASTER_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
388 
[NOTES] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
389 
[RECORD_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
390 
[AUTH_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
391 
[MAKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
392 
[CREATE_DT] [datetime] NULL,
393 
[CHECKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
394 
[APPROVE_DT] [datetime] NULL
395 
)
396 
GO
397 
IF @@ERROR <> 0 SET NOEXEC ON
398 
GO
399 
PRINT N'Creating [dbo].[CM_CONTRACTTERMS]'
400 
GO
401 
CREATE TABLE [dbo].[CM_CONTRACTTERMS]
402 
(
403 
[TERMS_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
404 
[TERMS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
405 
[TERMS_NAME] [nvarchar] (1200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
406 
[TERMS_TYPE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
407 
[NOTES] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
408 
[RECORD_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
409 
[MAKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
410 
[CREATE_DT] [datetime] NULL,
411 
[AUTH_STATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
412 
[CHECKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
413 
[APPROVE_DT] [datetime] NULL
414 
)
415 
GO
416 
IF @@ERROR <> 0 SET NOEXEC ON
417 
GO
418 
PRINT N'Creating primary key [PK_CM_CONTRACTTERMS_ID] on [dbo].[CM_CONTRACTTERMS]'
419 
GO
420 
ALTER TABLE [dbo].[CM_CONTRACTTERMS] ADD CONSTRAINT [PK_CM_CONTRACTTERMS_ID] PRIMARY KEY NONCLUSTERED  ([TERMS_ID])
421 
GO
422 
IF @@ERROR <> 0 SET NOEXEC ON
423 
GO
424 
PRINT N'Altering [dbo].[CM_EMPLOYEE_TRANSFER_LOG]'
425 
GO
426 
IF @@ERROR <> 0 SET NOEXEC ON
427 
GO
428 
ALTER TABLE [dbo].[CM_EMPLOYEE_TRANSFER_LOG] ADD
429 
[DEP_ID_OLD] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
430 
[DEP_ID_NEW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
431 
GO
432 
IF @@ERROR <> 0 SET NOEXEC ON
433 
GO
434 
PRINT N'Creating primary key [PK__CM_EMPLO__4364C8824BDC9D23] on [dbo].[CM_EMPLOYEE_TRANSFER_LOG]'
435 
GO
436 
ALTER TABLE [dbo].[CM_EMPLOYEE_TRANSFER_LOG] ADD CONSTRAINT [PK__CM_EMPLO__4364C8824BDC9D23] PRIMARY KEY CLUSTERED  ([LOG_ID])
437 
GO
438 
IF @@ERROR <> 0 SET NOEXEC ON
439 
GO
440 
PRINT N'Creating [dbo].[CM_BRANCH_DEP_MAP]'
441 
GO
442 
CREATE TABLE [dbo].[CM_BRANCH_DEP_MAP]
443 
(
444 
[HR_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
445 
[KT_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
446 
)
447 
GO
448 
IF @@ERROR <> 0 SET NOEXEC ON
449 
GO
450 
PRINT N'Creating primary key [PK__CM_BRANC__272A3F7EB20C7BE2] on [dbo].[CM_BRANCH_DEP_MAP]'
451 
GO
452 
ALTER TABLE [dbo].[CM_BRANCH_DEP_MAP] ADD CONSTRAINT [PK__CM_BRANC__272A3F7EB20C7BE2] PRIMARY KEY CLUSTERED  ([HR_ID])
453 
GO
454 
IF @@ERROR <> 0 SET NOEXEC ON
455 
GO
456 
PRINT N'Altering [dbo].[CM_EMPLOYEE_SYNC]'
457 
GO
458 
IF @@ERROR <> 0 SET NOEXEC ON
459 
GO
460 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaNS] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
461 
GO
462 
IF @@ERROR <> 0 SET NOEXEC ON
463 
GO
464 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [HoTen] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
465 
GO
466 
IF @@ERROR <> 0 SET NOEXEC ON
467 
GO
468 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaDVCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
469 
GO
470 
IF @@ERROR <> 0 SET NOEXEC ON
471 
GO
472 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [DVCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
473 
GO
474 
IF @@ERROR <> 0 SET NOEXEC ON
475 
GO
476 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaDVMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
477 
GO
478 
IF @@ERROR <> 0 SET NOEXEC ON
479 
GO
480 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [DVMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
481 
GO
482 
IF @@ERROR <> 0 SET NOEXEC ON
483 
GO
484 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaCDCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
485 
GO
486 
IF @@ERROR <> 0 SET NOEXEC ON
487 
GO
488 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [CDCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
489 
GO
490 
IF @@ERROR <> 0 SET NOEXEC ON
491 
GO
492 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaCDMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
493 
GO
494 
IF @@ERROR <> 0 SET NOEXEC ON
495 
GO
496 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [CDMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
497 
GO
498 
IF @@ERROR <> 0 SET NOEXEC ON
499 
GO
500 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [LoaiHDLD] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
501 
GO
502 
IF @@ERROR <> 0 SET NOEXEC ON
503 
GO
504 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [Email] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
505 
GO
506 
IF @@ERROR <> 0 SET NOEXEC ON
507 
GO
508 
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [So] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
509 
GO
510 
IF @@ERROR <> 0 SET NOEXEC ON
511 
GO
512 
PRINT N'Altering [dbo].[vASS_ADDNEW]'
513 
GO
514 
ALTER VIEW [dbo].[vASS_ADDNEW]
515 
AS SELECT        A.ADDNEW_ID, A.BUY_DATE, A.TYPE_ID, C.TYPE_NAME, A.GROUP_ID, D.GROUP_CODE, D.GROUP_NAME, A.ASSET_NAME, A.ASSET_SERIAL_NO, A.ASSET_DESC, A.BRANCH_ID, A.AMORT_START_DATE, A.DEPT_ID,
516 
                         H.DEP_CODE, H.DEP_NAME, A.EMP_ID, G.EMP_CODE, G.EMP_NAME, A.DIVISION_ID, A.BUY_PRICE, A.AMORT_AMT, A.AMORT_MONTH, A.AMORT_RATE, A.IS_MULTIPLE, A.QTY, A.PO_ID, PO.PO_CODE, A.PD_ID,
517 
                         A.REF_ASSET_ID, I.ASSET_CODE AS REF_CODE, I.ASSET_NAME AS REF_NAME, A.REF_AMORTIZED_AMT, A.WARRANTY_MONTHS, A.NOTES, A.CORE_NOTE, A.RECORD_STATUS, A.AUTH_STATUS, B.AUTH_STATUS_NAME,
518 
                         A.MAKER_ID, A.POSTED_STATUS, A.BRANCH_CREATE, UM.TLFullName AS MAKER_NAME, A.CREATE_DT, A.CHECKER_ID, A.APPROVE_DT, A.ENTRY_BOOKED, A.CONSTRUCT_ID, A.AMORT_END_DATE, E.BRANCH_CODE,
519 
                         E.BRANCH_NAME, F.DIV_CODE, F.DIV_NAME, F.ADDR AS DIV_ADDR, E.BRANCH_TYPE, A.ACCOUNT_GL, A.VAT, A.PRICE_VAT, A.APPROVE_DT_KT, A.CONTRACT_ID, A.SUP_ID, S.SUP_NAME, S.ADDR AS SUP_ADDR, S.TEL AS SUP_PHONE, S.TAX_NO AS SUP_MST, A.ASS_STATUS, A.PL_CODE, A.WAR_EXPIRE_DT, A.REQ_CODE, A.PR_CODE, A.UNIT
520 
                         ,A.WIN_CRACK AS WIN, A.OFFICE_CRACK AS OFFICE, A.FORWARD_CONTENT
521 
                         ,AG1.GROUP_ID AS PARENT_GROUP_ID, AG1.GROUP_CODE AS PARENT_GROUP_CODE
522 
FROM            dbo.ASS_ADDNEW AS A LEFT OUTER JOIN
523 
                         dbo.CM_AUTH_STATUS AS B ON A.AUTH_STATUS = B.AUTH_STATUS LEFT OUTER JOIN
524 
                         dbo.ASS_TYPE AS C ON A.TYPE_ID = C.TYPE_ID LEFT OUTER JOIN
525 
                         dbo.ASS_GROUP AS D ON A.GROUP_ID = D.GROUP_ID LEFT OUTER JOIN
526 
                         dbo.TL_USER AS UM ON UM.TLNANME = A.CHECKER_ID LEFT OUTER JOIN
527 
                         dbo.TR_PO_MASTER AS PO ON PO.PO_ID = A.PO_ID LEFT OUTER JOIN
528 
                         dbo.CM_BRANCH AS E ON E.BRANCH_ID = A.BRANCH_ID LEFT OUTER JOIN
529 
                         dbo.CM_DIVISION AS F ON F.DIV_ID = A.DIVISION_ID LEFT OUTER JOIN
530 
                         dbo.CM_EMPLOYEE AS G ON G.EMP_ID = A.EMP_ID LEFT OUTER JOIN
531 
                         dbo.CM_DEPARTMENT AS H ON H.DEP_ID = A.DEPT_ID LEFT OUTER JOIN
532 
                         dbo.ASS_MASTER AS I ON I.ASSET_ID = A.REF_ASSET_ID LEFT OUTER JOIN
533 
                         dbo.CM_SUPPLIER AS S ON A.SUP_ID = S.SUP_ID LEFT OUTER JOIN
534 
                         dbo.ASS_GROUP AS AG1 ON D.PARENT_ID = AG1.GROUP_ID
535 
GO
536 
IF @@ERROR <> 0 SET NOEXEC ON
537 
GO
538 
PRINT N'Creating index [IX01_ASS_MASTER] on [dbo].[ASS_MASTER]'
539 
GO
540 
CREATE NONCLUSTERED INDEX [IX01_ASS_MASTER] ON [dbo].[ASS_MASTER] ([ASSET_ID], [GROUP_ID], [BRANCH_ID], [DEPT_ID], [EMP_ID], [BRANCH_CREATE])
541 
GO
542 
IF @@ERROR <> 0 SET NOEXEC ON
543 
GO
544 
PRINT N'Adding constraints to [dbo].[ASS_MASTER]'
545 
GO
546 
ALTER TABLE [dbo].[ASS_MASTER] ADD CONSTRAINT [df_LIQ_W_STATUS] DEFAULT ('0') FOR [LIQ_W_STATUS]
547 
GO
548 
IF @@ERROR <> 0 SET NOEXEC ON
549 
GO
550 
PRINT N'Altering trigger [dbo].[TRIG_CM_EMPLOYEE_SYNC_Ins] on [dbo].[CM_EMPLOYEE_SYNC]'
551 
GO
552 
ALTER TRIGGER [dbo].[TRIG_CM_EMPLOYEE_SYNC_Ins]
553 
	ON [dbo].[CM_EMPLOYEE_SYNC]
554 
	AFTER INSERT
555 
AS
556 
BEGIN
557 
  --STORE ?NG B? NH?N VI?N V? I?U CHUY?N T?I S?N NH?N S?
558 
  --29/09/22
559 
	SET NOCOUNT ON;
560 
          IF (EXISTS(SELECT TOP 1 MaNS FROM INSERTED))
561 
          BEGIN
562
563 
              DECLARE @MaDV_Cu_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVCu FROM INSERTED)
564 
              DECLARE @MaDV_Moi_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVMoi FROM INSERTED)
565
566 
              --UPDATE M? M?I DO KT UPDATE
567 
              IF(@MaDV_Cu_SYNC = '0100')
568 
              BEGIN
569 
                  SET @MaDV_Cu_SYNC = '0600'
570 
              END
571
572 
              IF(@MaDV_Cu_SYNC = '0101')
573 
              BEGIN
574 
                  SET @MaDV_Cu_SYNC = '0601'
575 
              END
576
577 
              IF(@MaDV_Moi_SYNC = '0100')
578 
              BEGIN
579 
                  SET @MaDV_Moi_SYNC = '0600'
580 
              END
581
582 
              IF(@MaDV_Moi_SYNC = '0101')
583 
              BEGIN
584 
                  SET @MaDV_Moi_SYNC = '0601'
585 
              END
586
587 
              --MAP DATA HR V?I KT
588 
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC))
589 
              BEGIN
590 
                  SET @MaDV_Cu_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC)
591 
              END
592 
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC))
593 
              BEGIN
594 
                  SET @MaDV_Moi_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC)
595 
              END
596 
              DECLARE @MACD_CU_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDCu FROM INSERTED)
597 
              DECLARE @MACD_MOI_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDMoi FROM INSERTED)
598
599 
            --START: PARAM INSERT NH?N VI?N M?I
600 
              DECLARE @l_EMP_ID VARCHAR(500)
601 
              DECLARE @p_EMP_CODE	varchar(500)  = (SELECT TOP 1 MaNS FROM INSERTED)
602 
              DECLARE @p_EMP_NAME	nvarchar(500)  = (SELECT TOP 1 HoTen FROM INSERTED)
603
604 
              DECLARE @p_BRANCH_ID	varchar(15) = NULL
605 
              DECLARE @p_DEP_ID	varchar(15) = NULL
606
607 
              DECLARE @p_POS_CODE VARCHAR(50) = NULL
608 
              DECLARE @p_POS_NAME NVARCHAR(MAX) = NULL
609
610 
              DECLARE @p_NOTES	nvarchar(1000)  = N'?NG B?'
611 
              DECLARE @p_RECORD_STATUS	varchar(1)  = '1'
612 
              DECLARE @p_AUTH_STATUS	varchar(1)  = 'A'
613 
              DECLARE @p_MAKER_ID	varchar(15)  = 'ADMIN'
614 
              DECLARE @p_CREATE_DT	VARCHAR(50) = GETDATE()
615 
              DECLARE @p_CHECKER_ID	varchar(15)  = 'ADMIN'
616 
              DECLARE @p_APPROVE_DT	VARCHAR(50) = GETDATE()
617 
            --END: PARAM INSERT NH?N VI?N M?I
618
619 
            --START: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
620 
              DECLARE @I_EMP_ID VARCHAR(20) = (SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED))
621 
              DECLARE @I_STATUS VARCHAR(5)
622
623 
              DECLARE @C_Ma_DV_Cu VARCHAR(500) = NULL  --M? V Ch ? CHECK IF
624 
              DECLARE @C_Ma_DV_Moi VARCHAR(500) = NULL --M? DV M?I ? CHECK IF
625 
              DECLARE @C_Ma_PB_Cu VARCHAR(500) = NULL  --M? PB Ch ? CHECK IF
626 
              DECLARE @C_Ma_PB_Moi VARCHAR(500) = NULL --M? PB M?I ? CHECK IF
627 
            --END: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
628
629 
              DECLARE @TLNAME VARCHAR(200) = NULL
630
631 
              DECLARE @BLOCK_ID_OLD VARCHAR(15) = NULL --M? kh?i ci check i?u chuy?n nh?n s?
632 
              DECLARE @CENTER_ID_OLD VARCHAR(15) = NULL  --M? trung t?m ci check i?u chuy?n nh?n s?
633 
              DECLARE @DEP_ID_OLD VARCHAR(15) = NULL  --M? ph?ng ban ci check i?u chuy?n nh?n s?
634
635 
              DECLARE @BLOCK_ID_NEW VARCHAR(15) = NULL  --M? kh?i m?i check i?u chuy?n nh?n s?
636 
              DECLARE @CENTER_ID_NEW VARCHAR(15) = NULL  --M? trung t?m m?i check i?u chuy?n nh?n s?
637 
              DECLARE @DEP_ID_NEW VARCHAR(15) = NULL  --M? ph?ng ban m?i check i?u chuy?n nh?n s?
638
639 
            --START: PARAM INSERT
640
641 
            --CHECK XEM ?N V?/PH?NG BAN C?A NH?N VI?N M?I C?A KH L?U TRONG C?T MaDVCu HAY C?T MaDVMoi
642 
            IF (EXISTS(SELECT TOP 1 MaDVCu FROM INSERTED WHERE MaDVCu IS NOT NULL AND MaDVCu <> ''))
643 
            BEGIN
644 
                --CHECK XEM M? L? ?N V? HAY PH?NG BAN
645 
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC))
646 
                BEGIN
647 
                    SET @p_BRANCH_ID = 'DV0001'
648 
                    SET @C_Ma_DV_Cu = 'DV0001'
649 
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
650 
                    SET @C_Ma_PB_Cu = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
651 
                END
652 
                ELSE
653 
                BEGIN
654 
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
655 
                    SET @C_Ma_DV_Cu = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
656 
                END
657 
            END
658 
            --ELSE
659
660 
            IF(EXISTS(SELECT TOP 1 MaDVMoi FROM INSERTED WHERE MaDVMoi IS NOT NULL AND MaDVMoi <> ''))
661 
            BEGIN
662 
                --CHECK XEM M? L? ?N V? HAY PH?NG BAN
663 
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = (SELECT MaDVMoi FROM INSERTED)))
664 
                BEGIN
665 
                    SET @p_BRANCH_ID = 'DV0001'
666 
                    SET @C_Ma_DV_Moi = 'DV0001'
667 
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
668 
                    SET @C_Ma_PB_Moi = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
669 
                END
670 
                ELSE
671 
                BEGIN
672 
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
673 
                    SET @C_Ma_DV_Moi = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
674 
                END
675 
            END
676
677 
            --GET KH?I, TRUNG T?M, PH?NG BAN
678 
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Cu ,@BLOCK_ID_OLD OUT ,@CENTER_ID_OLD OUT , @DEP_ID_OLD OUT
679
680 
            --GET KH?I, TRUNG T?M, PH?NG BAN
681 
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Moi, @BLOCK_ID_NEW OUT ,@CENTER_ID_NEW OUT ,@DEP_ID_NEW OUT
682
683 
            PRINT @C_Ma_DV_Cu
684 
            PRINT @C_Ma_DV_Moi
685 
        		IF((@C_Ma_DV_Cu IS NULL OR @C_Ma_DV_Cu = '') AND (@C_Ma_DV_Moi IS NULL OR @C_Ma_DV_Moi = ''))
686 
        		BEGIN
687 
          			--N'M? DV ci v? M? DV m?i kh?ng c? gi? tr?'
688 
                PRINT 1
689 
        		END
690 
            ELSE
691 
            BEGIN
692
693 
              IF((SELECT COUNT(*) FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE) > 1)
694 
              BEGIN
695 
                    SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE
696 
                              AND CEL.BRANCH_CODE = @MaDV_Cu_SYNC
697 
                              AND (CEL.DEP_CODE = @C_Ma_PB_Cu OR @C_Ma_PB_Cu IS NULL))
698 
              END
699 
              ELSE
700 
              BEGIN
701 
                  SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE)
702 
              END
703
704 
            --CHECK DATA CH?C DANH L?U C?T CDMOI HAY CDCU
705 
            IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDMoi IS NOT NULL AND MaCDMoi <> ''))
706 
            BEGIN
707 
                  --N?U CH?C DANH CH?A C? TRONG H? TH?NG TH? TH?M M?I CH?C DANH
708 
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_MOI_SYNC))
709 
                  BEGIN
710 
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
711 
                       VALUES (@MACD_MOI_SYNC, (SELECT TOP 1 CDMoi FROM INSERTED), NULL, NULL);
712 
                  END
713
714 
                  SET @p_POS_CODE = @MACD_MOI_SYNC
715 
                  SET @p_POS_NAME = (SELECT TOP 1 CDMoi FROM INSERTED)
716 
            END
717 
            ELSE IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDCu IS NOT NULL AND MaCDCu <> ''))
718 
            BEGIN
719 
                  --N?U CH?C DANH CH?A C? TRONG H? TH?NG TH? TH?M M?I CH?C DANH
720 
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_CU_SYNC))
721 
                  BEGIN
722 
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
723 
                       VALUES (@MACD_CU_SYNC, (SELECT TOP 1 CDCu FROM INSERTED), NULL, NULL);
724 
                  END
725
726 
                  SET @p_POS_CODE = @MACD_CU_SYNC
727 
                  SET @p_POS_NAME = (SELECT TOP 1 CDCu FROM INSERTED)
728 
            END
729
730
731 
             --CHECK NH?N VI?N XEM C? XEM TRONG H? TH?NG KH?NG
732 
             IF(EXISTS(SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED)))
733 
             BEGIN
734
735 
                IF(EXISTS(
736 
                        SELECT atmd.ASSET_ID FROM ASS_TRANSFER_MULTI_DT atmd
737 
                        WHERE (atmd.EMP_ID = @I_EMP_ID AND atmd.EMP_ID_OLD = @I_EMP_ID
738 
                                               AND (atmd.BRANCH_ID = @C_Ma_DV_Moi AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu
739 
                                                    AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'') AND ISNULL(atmd.KHOI_ID,'') = ISNULL(@BLOCK_ID_NEW,'')
740 
                                                    AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'') AND ISNULL(atmd.CENTER_ID,'') = ISNULL(@CENTER_ID_NEW,'')
741 
                                                    AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'') AND ISNULL(atmd.DEPT_ID,'') = ISNULL(@DEP_ID_NEW,''))
742 
      																				 AND atmd.AUTH_STATUS_KT <> 'A') --I?U CHUY?N TS T? V N?Y SANG V KH?C CHO C?NG NV
743 
                              OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.EMP_ID <> @I_EMP_ID
744 
                                  AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu
745 
                                  AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'')
746 
                                  AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'')
747 
                                  AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'')
748 
                                  AND atmd.AUTH_STATUS_KT <> 'A') --I?U CHUY?N TS T? V N?Y SANG V KH?C NH?NG KH?C NV
749 
                        UNION ALL
750 
                        SELECT am.ASSET_ID FROM ASS_MASTER am WHERE am.BRANCH_ID = @C_Ma_DV_Cu AND ISNULL(am.DEPT_ID,'') = ISNULL(@C_Ma_PB_Cu,'') AND am.EMP_ID = @I_EMP_ID ))
751 
                  SET @I_STATUS = '0'
752 
                ELSE
753 
                  SET @I_STATUS = '1'
754
755 
                INSERT INTO CM_EMPLOYEE_TRANSFER_LOG (EMP_ID, TRANSFER_DT, BRANCH_ID_OLD, DEP_ID_OLD, BRANCH_ID_NEW, DEP_ID_NEW, STATUS)
756 
                VALUES (@I_EMP_ID, CONVERT(DATETIME,(SELECT TOP 1 TGTao FROM INSERTED),103), @C_Ma_DV_Cu,@C_Ma_PB_Cu, @C_Ma_DV_Moi, @C_Ma_PB_Moi, @I_STATUS);
757
758 
                --CHECK TR??NG H?P 1 NH?N VI?N C? QUY?N 2 CHI NH?NH TR? TR?N (KH?C TLNAME)
759
760 
                --PHUCVH 22/12/22 CHECK N?U MADV,PB M?I THAY ?I TH? UPDATE L?I MADV,PB CHO NHANVIEN
761 
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE
762 
                              AND (ISNULL(A.BRANCH_ID,'') <> ISNULL(@p_BRANCH_ID,'')
763 
                                  OR ISNULL(A.DEP_ID,'') <> ISNULL(@p_DEP_ID,''))))
764 
                BEGIN
765 
                      UPDATE CM_EMPLOYEE SET BRANCH_ID = @p_BRANCH_ID, DEP_ID = @p_DEP_ID
766 
                      WHERE EMP_CODE = @p_EMP_CODE
767
768 
                      --CHECK UPDATE TL_USER V? CM_EMPLOYEE_LOG
769 
                      IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE))
770 
                      BEGIN
771 
                          UPDATE CM_EMPLOYEE_LOG SET BRANCH_CODE = (SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID),
772 
                                                     DEP_CODE = (SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
773 
                          WHERE EMP_CODE = @p_EMP_CODE AND USER_DOMAIN = @TLNAME
774
775 
                          UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID
776 
                          WHERE TLNANME = @TLNAME
777 
                      END
778
779 
                END
780
781 
                --PHUCVH 22/12/22 CHECK N?U MA CHUC DANH THAY ?I TH? UPDATE
782 
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE
783 
                                AND ISNULL(A.POS_CODE,'') <> ISNULL(@p_POS_CODE,'')))
784 
                BEGIN
785 
                     UPDATE CM_EMPLOYEE SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
786 
                     WHERE EMP_CODE = @p_EMP_CODE
787
788 
                     UPDATE CM_EMPLOYEE_LOG SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
789 
                     WHERE EMP_CODE = @p_EMP_CODE AND USER_DOMAIN = @TLNAME
790 
                END
791
792 
                --IF @@Error <> 0 GOTO ABORT
793 
          		  --SET @Message = N'?ng b? i?u chuy?n nh?n s? th?nh c?ng'
794 
             END
795 
             ELSE
796 
             BEGIN
797 
                --KH?NG C? TRONG H? TH?NG
798 
                --TH?M TH?NG TIN NH?N VI?N
799 
                		EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out
800 
                		INSERT INTO CM_EMPLOYEE([EMP_ID],[EMP_CODE],[EMP_NAME],[BRANCH_ID],[DEP_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[POS_CODE],[POS_NAME])
801 
                		VALUES(@l_EMP_ID ,@p_EMP_CODE ,@p_EMP_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_POS_CODE, @p_POS_NAME )
802 
                    --IF @@Error <> 0 GOTO ABORT
803 
            				--SET @Message = N'?ng b? nh?n vi?n m?i th?nh c?ng'
804 
             END
805 
            END
806
807
808 
          END
809 
END
810 
GO
811 
IF @@ERROR <> 0 SET NOEXEC ON
812 
GO
813 
COMMIT TRANSACTION
814 
GO
815 
IF @@ERROR <> 0 SET NOEXEC ON
816 
GO
817 
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
818 
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
819 
BEGIN
820 
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
821 
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
822 
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
823 
    EXECUTE sys.xp_logevent 55000, @eventMessage
824 
END
825 
GO
826 
DECLARE @Success AS BIT
827 
SET @Success = 1
828 
SET NOEXEC OFF
829 
IF (@Success = 1) PRINT 'The database update succeeded'
830 
ELSE BEGIN
831 
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
832 
	PRINT 'The database update failed'
833 
END
834 
GO
835