/*
Run this script on:

        118.69.72.241,5036.gAMSPro_VIETBANK_UAT_CUS    -  This database will be modified

to synchronize it with:

        118.69.72.241,5036.gAMSPro_VIETBANK_DEV

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.1.6.5463 from Red Gate Software Ltd at 4/18/2023 2:12:53 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[CM_EMPLOYEE_TRANSFER_LOG]'
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_TRANSFER_LOG] DROP CONSTRAINT [PK__CM_EMPLO__4364C8824F8C1B4B]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[CM_EMPLOYEE]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [EMP_CODE] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [EMP_NAME] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [POS_CODE] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE] ALTER COLUMN [POS_NAME] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[CM_EMPLOYEE_LOG]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_LOG] ALTER COLUMN [POS_CODE] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ASS_LIQUIDATION_DT]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ASS_LIQUIDATION_DT] ADD
[ASS_STATUS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AMORT_STATUS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ASS_ADDNEW_PO]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ASS_ADDNEW_PO] ADD
[INVOICE_SYMPOL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ASS_ADDNEW_GD]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ASS_ADDNEW_GD] ADD
[INVOICE_SYMPOL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ASS_PO]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ASS_PO] ADD
[INVOICE_SYMPOL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ASS_MASTER_VIEW]'
GO
ALTER VIEW [dbo].[ASS_MASTER_VIEW] 
AS SELECT
  dbo.CM_SUPPLIER.SUP_NAME
 ,dbo.CM_SUPPLIER.SUP_CODE
 ,dbo.CM_SUPPLIER.SUP_TYPE_ID
 ,dbo.CM_SUPPLIER.ADDR
 ,dbo.CM_SUPPLIER.EMAIL
 ,dbo.ASS_GROUP.GROUP_CODE
 ,dbo.ASS_GROUP.GROUP_NAME
 ,dbo.CM_EMPLOYEE.EMP_NAME
 ,dbo.CM_EMPLOYEE.EMP_CODE
 ,dbo.CM_DIVISION.DIV_CODE
 ,dbo.CM_DIVISION.DIV_NAME
 ,dbo.CM_DEPARTMENT.DEP_CODE
 ,dbo.CM_DEPARTMENT.DEP_NAME
 ,dbo.CM_BRANCH.BRANCH_CODE
 ,dbo.CM_BRANCH.BRANCH_NAME
 ,dbo.ASS_MASTER.ASSET_ID
 ,dbo.ASS_MASTER.TYPE_ID
 ,dbo.ASS_MASTER.GROUP_ID
 ,dbo.ASS_MASTER.ASSET_CODE
 ,dbo.ASS_MASTER.ASSET_NAME
 ,dbo.ASS_MASTER.ASSET_SERIAL_NO
 ,dbo.ASS_MASTER.ASSET_DESC
 ,dbo.ASS_MASTER.SUP_ID
 ,dbo.ASS_MASTER.BUY_PRICE
 ,dbo.ASS_MASTER.AMORT_AMT
 ,dbo.ASS_MASTER.ASS_TYPE
 ,dbo.ASS_MASTER.BRANCH_ID
 ,dbo.ASS_MASTER.DEPT_ID
 ,dbo.ASS_MASTER.EMP_ID
 ,dbo.ASS_MASTER.DIVISION_ID
 ,dbo.ASS_MASTER.BUY_DATE
 ,dbo.ASS_MASTER.USE_DATE
 ,dbo.ASS_MASTER.SPECIAL_ASS
 ,dbo.ASS_MASTER.AMORT_MONTH
 ,dbo.ASS_MASTER.AMORT_RATE
 ,dbo.ASS_MASTER.AMORT_START_DATE
 ,dbo.ASS_MASTER.AMORT_END_DATE
 ,dbo.ASS_MASTER.FIRST_AMORT_AMT
 ,dbo.ASS_MASTER.MONTHLY_AMORT_AMT
 ,dbo.ASS_MASTER.AMORTIZED_MONTH
 ,dbo.ASS_MASTER.AMORTIZED_AMT
 ,dbo.ASS_MASTER.LIQUIDATION_DT
 ,dbo.ASS_MASTER.PO_ID
 ,dbo.ASS_MASTER.PD_ID
 ,dbo.ASS_MASTER.WAREHOUSE_ID
 ,dbo.ASS_MASTER.LOCATION
 ,dbo.ASS_MASTER.REF_ASSET_ID
 ,dbo.ASS_MASTER.REF_AMORTIZED_AMT
 ,dbo.ASS_MASTER.WARRANTY_MONTHS
 ,dbo.ASS_MASTER.NOTES
 ,dbo.ASS_MASTER.AMORT_STATUS
 ,dbo.ASS_MASTER.ASS_STATUS
 ,dbo.ASS_MASTER.ASS_STATUS_DESC
 ,dbo.ASS_MASTER.RECORD_STATUS
 ,dbo.ASS_MASTER.AUTH_STATUS
 ,dbo.ASS_MASTER.MAKER_ID
 ,dbo.ASS_MASTER.CREATE_DT
 ,dbo.ASS_MASTER.CHECKER_ID
 ,dbo.ASS_MASTER.APPROVE_DT
 ,dbo.ASS_PO.PO_CODE
 ,dbo.TR_PO_MASTER.PO_NAME
 ,ISNULL(dbo.ASS_MASTER.BUY_PRICE - dbo.ASS_MASTER.AMORTIZED_AMT, 0) AS REMAIN_VALUE
 ,ISNULL(dbo.ASS_MASTER.AMORT_AMT - dbo.ASS_MASTER.AMORTIZED_AMT, 0) AS REMAIN_AMORT_AMT
 ,dbo.ASS_TYPE.TYPE_CODE
 ,dbo.ASS_TYPE.TYPE_NAME
 ,dbo.ASS_AMORT_STATUS.STATUS_NAME AS AMORT_STATUS_NAME
 --,dbo.ASS_STATUS.STATUS_NAME AS ASS_STATUS_NAME,
 ,CASE WHEN ASS_MASTER.LIQ_W_STATUS = '1' AND ASS_MASTER.AMORT_STATUS = 'DTL' THEN N'Đã thanh lý' ELSE ASS_STATUS.STATUS_NAME
 END AS ASS_STATUS_NAME
 ,RA.ASSET_CODE AS REF_ASSET_CODE
 ,RA.ASSET_NAME AS REF_ASSET_NAME
 ,dbo.ASS_MASTER.USE_DATE_KT
 ,dbo.ASS_MASTER.BUY_DATE_KT
 ,dbo.ASS_MASTER.USE_STATUS
 ,dbo.ASS_MASTER.BRANCH_CREATE
 ,BRCR.BRANCH_NAME AS BRANCH_CREATE_NAME
 ,BRCR.BRANCH_CODE AS BRANCH_CREATE_CODE
 ,dbo.ASS_MASTER.ACCOUNT_GL
 ,dbo.ASS_MASTER.VAT
 ,dbo.ASS_MASTER.PRICE_VAT
 ,OS.CONTENT AS IS_OS
 ,LICENSE.CONTENT AS IS_MO_LICENSE
 ,ASS_MASTER.OS
 ,ASS_MASTER.MO_LICENSE,
--RG.GROUP_CODE AS REF_GROUP_CODE, RG.GROUP_NAME AS REF_GROUP_NAME,
--RT.TYPE_CODE AS REF_TYPE_CODE, RT.TYPE_NAME AS REF_TYPE_NAME
--dbo.FN_GET_CHINHANH(dbo.ASS_MASTER.BRANCH_ID,'KV') KHU_VUC,
--dbo.FN_GET_CHINHANH(dbo.ASS_MASTER.BRANCH_ID,'CN') CHI_NHANH, 
--D.BRANCH_NAME PGD
AN.PR_CODE, --SỐ PR
AN.CONTRACT_ID AS ASS_CONTRACT_CODE, -- SỐ HỢP ĐỒNG
AN.PL_CODE, --SỐ TỜ TRÌNH
AP.INVOICE_NO --SỐ HOÁ ĐƠN
FROM dbo.ASS_MASTER
LEFT OUTER JOIN dbo.ASS_GROUP
  ON dbo.ASS_MASTER.GROUP_ID = dbo.ASS_GROUP.GROUP_ID
LEFT OUTER JOIN dbo.CM_SUPPLIER
  ON dbo.ASS_MASTER.SUP_ID = dbo.CM_SUPPLIER.SUP_ID
LEFT OUTER JOIN dbo.CM_EMPLOYEE
  ON dbo.ASS_MASTER.EMP_ID = dbo.CM_EMPLOYEE.EMP_ID
LEFT OUTER JOIN dbo.CM_BRANCH
  ON dbo.ASS_MASTER.BRANCH_ID = dbo.CM_BRANCH.BRANCH_ID
LEFT OUTER JOIN dbo.CM_AUTH_STATUS
  ON dbo.ASS_MASTER.AUTH_STATUS = dbo.CM_AUTH_STATUS.AUTH_STATUS
LEFT OUTER JOIN dbo.CM_DEPARTMENT
  ON dbo.ASS_MASTER.DEPT_ID = dbo.CM_DEPARTMENT.DEP_ID
LEFT OUTER JOIN dbo.CM_DIVISION
  ON dbo.ASS_MASTER.DIVISION_ID = dbo.CM_DIVISION.DIV_ID
LEFT OUTER JOIN dbo.TR_PO_MASTER
  ON dbo.TR_PO_MASTER.PO_ID = dbo.ASS_MASTER.PO_ID
LEFT OUTER JOIN dbo.ASS_TYPE
  ON dbo.ASS_TYPE.TYPE_ID = dbo.ASS_MASTER.TYPE_ID
LEFT OUTER JOIN dbo.ASS_AMORT_STATUS
  ON dbo.ASS_AMORT_STATUS.STATUS_CODE = dbo.ASS_MASTER.AMORT_STATUS
LEFT OUTER JOIN dbo.ASS_STATUS
  ON dbo.ASS_STATUS.STATUS_ID = dbo.ASS_MASTER.ASS_STATUS
LEFT OUTER JOIN dbo.ASS_MASTER RA
  ON RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID
LEFT OUTER JOIN dbo.ASS_PO
  ON dbo.ASS_MASTER.PO_ID = dbo.ASS_PO.ASSPO_ID
LEFT OUTER JOIN dbo.CM_BRANCH BRCR
  ON dbo.ASS_MASTER.BRANCH_CREATE = BRCR.BRANCH_ID
LEFT JOIN CM_ALLCODE OS
  ON ASS_MASTER.OS = OS.CDVAL
    AND OS.CDNAME = 'OS_LICENSE'
    AND OS.CDTYPE = 'STATUS'
LEFT JOIN CM_ALLCODE LICENSE
  ON ASS_MASTER.MO_LICENSE = LICENSE.CDVAL
    AND LICENSE.CDNAME = 'OS_LICENSE'
    AND LICENSE.CDTYPE = 'STATUS'
LEFT JOIN ASS_TRANSACTIONS AT ON AT.ASSET_ID = dbo.ASS_MASTER.ASSET_ID AND AT.TRN_TYPE = 'ADD_NEW'
LEFT JOIN ASS_ADDNEW AN ON AT.TRN_ID = AN.ADDNEW_ID AND AT.TRN_TYPE = 'ADD_NEW'
LEFT JOIN ASS_PO AP ON AN.ADDNEW_ID = AP.ADDNEW_ID

						 --dbo.ASS_GROUP RG ON RG.GROUP_ID = RA.GROUP_ID AND RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID LEFT JOIN
						 --dbo.ASS_TYPE RT ON RG.TYPE_ID = RA.TYPE_ID AND RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID
						-- LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = dbo.ASS_MASTER.BRANCH_ID
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[THREAD_TIME_SEND_LOG]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[THREAD_TIME_SEND_LOG] ALTER COLUMN [MESSAGE] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ASS_PENDING_ITEM]'
GO
ALTER VIEW [dbo].[ASS_PENDING_ITEM] 
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,
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
FROM ASS_ADDNEW A
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS

UNION ALL

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,
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
--FROM ASS_USE A
FROM ASS_USE A
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID

UNION ALL


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,
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
FROM ASS_TRANSFER A
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID

--UNION ALL

--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,
--'' BRANCH_CREATE, A.TYPE_ID
--FROM ASS_MASTER A 
--INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS

UNION ALL

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,
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
FROM ASS_COLLECT A
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID

UNION ALL

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,
'' BRANCH_CREATE, A.TYPE_ID, 0 AMT, A.NOTES DIENGIAI, A.CREATE_DT CREATE_DT,'' MAKER_ID_KT , '' AUTH_STATUS_KT,'' CREATE_DT_KT
FROM ASS_GROUP A
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS


UNION ALL

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, 
		A.MAKER_ID, 'ASS_AMORT' TRAN_TYPE, A.NOTES TRAN_TYPE_NAME,--N'Khấu hao' 
		A.BRANCH_ID BRANCH_CREATE, A.ASSET_TYPE AS [TYPE_ID],
		A.TOTAL_AMT AMT, A.NOTES DIENGIAI, A.CREATE_DT CREATE_DT,'' MAKER_ID_KT , '' AUTH_STATUS_KT,'' CREATE_DT_KT
FROM ASS_AMORT A


UNION ALL

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,
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 ,
 A.AUTH_STATUS AUTH_STATUS_KT,A.CREATE_DT CREATE_DT_KT
FROM ASS_UPDATE A 
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID

-----thieuvq 5102016 - them dieu kien kiem tra danh sach dieu chuyen nhieu tai san chua duyet
UNION ALL

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,
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
FROM ASS_TRANSFER_MULTI_DT A 
INNER JOIN ASS_TRANSFER_MULTI_MASTER BB ON A.TRANS_MULTI_MASTER_ID = BB.TRANS_MULTI_MASTER_ID
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID

UNION ALL

-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
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,
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
FROM ASS_USE_MULTI_DT A 
INNER JOIN ASS_USE_MULTI_MASTER BB ON A.USER_MASTER_ID = BB.USER_MASTER_ID
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID

UNION ALL

-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
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,
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
FROM ASS_COLLECT_MULTI_DT A 
INNER JOIN ASS_COLLECT_MULTI_MASTER BB ON A.COL_MULTI_MASTER_ID = BB.COL_MULTI_MASTER_ID
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID


UNION ALL

-----luctv 07012018 - them dieu kien kiem tra danh sach thanh ly nhieu tai san chua duyet
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,
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
FROM ASS_LIQUIDATION_DT A 
INNER JOIN ASS_LIQUIDATION BB ON A.LIQ_ID = BB.LIQ_ID
INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC ON CC.ASSET_ID = A.ASSET_ID
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_DT]'
GO
CREATE TABLE [dbo].[ASS_IMPORT_UPDATE_DT]
(
[IMP_DT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IMP_MASTER_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSET_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEP_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMP_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NOTES] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSET_DESC] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSET_SERIAL_NO] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH_ID_OLD] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEP_ID_OLD] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMP_ID_OLD] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NOTES_OLD] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSET_DESC_OLD] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSET_SERIAL_NO_OLD] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_MASTER]'
GO
CREATE TABLE [dbo].[ASS_IMPORT_UPDATE_MASTER]
(
[IMP_MASTER_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NOTES] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RECORD_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AUTH_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CREATE_DT] [datetime] NULL,
[CHECKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[APPROVE_DT] [datetime] NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CM_CONTRACTTERMS]'
GO
CREATE TABLE [dbo].[CM_CONTRACTTERMS]
(
[TERMS_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TERMS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TERMS_NAME] [nvarchar] (1200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TERMS_TYPE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NOTES] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RECORD_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CREATE_DT] [datetime] NULL,
[AUTH_STATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CHECKER_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[APPROVE_DT] [datetime] NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_CM_CONTRACTTERMS_ID] on [dbo].[CM_CONTRACTTERMS]'
GO
ALTER TABLE [dbo].[CM_CONTRACTTERMS] ADD CONSTRAINT [PK_CM_CONTRACTTERMS_ID] PRIMARY KEY NONCLUSTERED  ([TERMS_ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[CM_EMPLOYEE_TRANSFER_LOG]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_TRANSFER_LOG] ADD
[DEP_ID_OLD] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEP_ID_NEW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK__CM_EMPLO__4364C8824BDC9D23] on [dbo].[CM_EMPLOYEE_TRANSFER_LOG]'
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_TRANSFER_LOG] ADD CONSTRAINT [PK__CM_EMPLO__4364C8824BDC9D23] PRIMARY KEY CLUSTERED  ([LOG_ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CM_BRANCH_DEP_MAP]'
GO
CREATE TABLE [dbo].[CM_BRANCH_DEP_MAP]
(
[HR_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[KT_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK__CM_BRANC__272A3F7EB20C7BE2] on [dbo].[CM_BRANCH_DEP_MAP]'
GO
ALTER TABLE [dbo].[CM_BRANCH_DEP_MAP] ADD CONSTRAINT [PK__CM_BRANC__272A3F7EB20C7BE2] PRIMARY KEY CLUSTERED  ([HR_ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[CM_EMPLOYEE_SYNC]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaNS] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [HoTen] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaDVCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [DVCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaDVMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [DVMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaCDCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [CDCu] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [MaCDMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [CDMoi] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [LoaiHDLD] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [Email] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[CM_EMPLOYEE_SYNC] ALTER COLUMN [So] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[vASS_ADDNEW]'
GO
ALTER VIEW [dbo].[vASS_ADDNEW] 
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, 
                         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, 
                         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, 
                         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, 
                         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
                         ,A.WIN_CRACK AS WIN, A.OFFICE_CRACK AS OFFICE, A.FORWARD_CONTENT
                         ,AG1.GROUP_ID AS PARENT_GROUP_ID, AG1.GROUP_CODE AS PARENT_GROUP_CODE
FROM            dbo.ASS_ADDNEW AS A LEFT OUTER JOIN
                         dbo.CM_AUTH_STATUS AS B ON A.AUTH_STATUS = B.AUTH_STATUS LEFT OUTER JOIN
                         dbo.ASS_TYPE AS C ON A.TYPE_ID = C.TYPE_ID LEFT OUTER JOIN
                         dbo.ASS_GROUP AS D ON A.GROUP_ID = D.GROUP_ID LEFT OUTER JOIN
                         dbo.TL_USER AS UM ON UM.TLNANME = A.CHECKER_ID LEFT OUTER JOIN
                         dbo.TR_PO_MASTER AS PO ON PO.PO_ID = A.PO_ID LEFT OUTER JOIN
                         dbo.CM_BRANCH AS E ON E.BRANCH_ID = A.BRANCH_ID LEFT OUTER JOIN
                         dbo.CM_DIVISION AS F ON F.DIV_ID = A.DIVISION_ID LEFT OUTER JOIN
                         dbo.CM_EMPLOYEE AS G ON G.EMP_ID = A.EMP_ID LEFT OUTER JOIN
                         dbo.CM_DEPARTMENT AS H ON H.DEP_ID = A.DEPT_ID LEFT OUTER JOIN
                         dbo.ASS_MASTER AS I ON I.ASSET_ID = A.REF_ASSET_ID LEFT OUTER JOIN
                         dbo.CM_SUPPLIER AS S ON A.SUP_ID = S.SUP_ID LEFT OUTER JOIN 
                         dbo.ASS_GROUP AS AG1 ON D.PARENT_ID = AG1.GROUP_ID
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX01_ASS_MASTER] on [dbo].[ASS_MASTER]'
GO
CREATE NONCLUSTERED INDEX [IX01_ASS_MASTER] ON [dbo].[ASS_MASTER] ([ASSET_ID], [GROUP_ID], [BRANCH_ID], [DEPT_ID], [EMP_ID], [BRANCH_CREATE])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding constraints to [dbo].[ASS_MASTER]'
GO
ALTER TABLE [dbo].[ASS_MASTER] ADD CONSTRAINT [df_LIQ_W_STATUS] DEFAULT ('0') FOR [LIQ_W_STATUS]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering trigger [dbo].[TRIG_CM_EMPLOYEE_SYNC_Ins] on [dbo].[CM_EMPLOYEE_SYNC]'
GO
ALTER TRIGGER [dbo].[TRIG_CM_EMPLOYEE_SYNC_Ins]
	ON [dbo].[CM_EMPLOYEE_SYNC]
	AFTER INSERT
AS 
BEGIN
  --STORE ĐỒNG BỘ NHÂN VIÊN VÀ ĐIỀU CHUYỂN TÀI SẢN NHÂN SỰ
  --29/09/22
	SET NOCOUNT ON;
          IF (EXISTS(SELECT TOP 1 MaNS FROM INSERTED)) 
          BEGIN  
          
              DECLARE @MaDV_Cu_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVCu FROM INSERTED)
              DECLARE @MaDV_Moi_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVMoi FROM INSERTED) 

              --UPDATE MÃ MỚI DO KT UPDATE
              IF(@MaDV_Cu_SYNC = '0100')
              BEGIN
                  SET @MaDV_Cu_SYNC = '0600'
              END

              IF(@MaDV_Cu_SYNC = '0101')
              BEGIN
                  SET @MaDV_Cu_SYNC = '0601'
              END

              IF(@MaDV_Moi_SYNC = '0100')
              BEGIN
                  SET @MaDV_Moi_SYNC = '0600'
              END

              IF(@MaDV_Moi_SYNC = '0101')
              BEGIN
                  SET @MaDV_Moi_SYNC = '0601'
              END
                
              --MAP DATA HR VỚI KT
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC))
              BEGIN
                  SET @MaDV_Cu_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC)
              END
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC))
              BEGIN
                  SET @MaDV_Moi_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC)
              END
              DECLARE @MACD_CU_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDCu FROM INSERTED)
              DECLARE @MACD_MOI_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDMoi FROM INSERTED)
    
            --START: PARAM INSERT NHÂN VIÊN MỚI
              DECLARE @l_EMP_ID VARCHAR(500)
              DECLARE @p_EMP_CODE	varchar(500)  = (SELECT TOP 1 MaNS FROM INSERTED)
              DECLARE @p_EMP_NAME	nvarchar(500)  = (SELECT TOP 1 HoTen FROM INSERTED)
      
              DECLARE @p_BRANCH_ID	varchar(15) = NULL 
              DECLARE @p_DEP_ID	varchar(15) = NULL             
              
              DECLARE @p_POS_CODE VARCHAR(50) = NULL
              DECLARE @p_POS_NAME NVARCHAR(MAX) = NULL             
      
              DECLARE @p_NOTES	nvarchar(1000)  = N'ĐỒNG BỘ'
              DECLARE @p_RECORD_STATUS	varchar(1)  = '1'
              DECLARE @p_AUTH_STATUS	varchar(1)  = 'A'
              DECLARE @p_MAKER_ID	varchar(15)  = 'ADMIN'
              DECLARE @p_CREATE_DT	VARCHAR(50) = GETDATE()
              DECLARE @p_CHECKER_ID	varchar(15)  = 'ADMIN'
              DECLARE @p_APPROVE_DT	VARCHAR(50) = GETDATE()
            --END: PARAM INSERT NHÂN VIÊN MỚI
    
            --START: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
              DECLARE @I_EMP_ID VARCHAR(20) = (SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED))
              DECLARE @I_STATUS VARCHAR(5) 
    
              DECLARE @C_Ma_DV_Cu VARCHAR(500) = NULL  --MÃ ĐV CŨ ĐỂ CHECK IF
              DECLARE @C_Ma_DV_Moi VARCHAR(500) = NULL --MÃ DV MỚI ĐỂ CHECK IF
              DECLARE @C_Ma_PB_Cu VARCHAR(500) = NULL  --MÃ PB CŨ ĐỂ CHECK IF
              DECLARE @C_Ma_PB_Moi VARCHAR(500) = NULL --MÃ PB MỚI ĐỂ CHECK IF
            --END: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG

              DECLARE @TLNAME VARCHAR(200) = NULL

              DECLARE @BLOCK_ID_OLD VARCHAR(15) = NULL --Mã khối cũ check điều chuyển nhân sự
              DECLARE @CENTER_ID_OLD VARCHAR(15) = NULL  --Mã trung tâm cũ check điều chuyển nhân sự
              DECLARE @DEP_ID_OLD VARCHAR(15) = NULL  --Mã phòng ban cũ check điều chuyển nhân sự

              DECLARE @BLOCK_ID_NEW VARCHAR(15) = NULL  --Mã khối mới check điều chuyển nhân sự
              DECLARE @CENTER_ID_NEW VARCHAR(15) = NULL  --Mã trung tâm mới check điều chuyển nhân sự
              DECLARE @DEP_ID_NEW VARCHAR(15) = NULL  --Mã phòng ban mới check điều chuyển nhân sự
    
            --START: PARAM INSERT 
    
            --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
            IF (EXISTS(SELECT TOP 1 MaDVCu FROM INSERTED WHERE MaDVCu IS NOT NULL AND MaDVCu <> ''))
            BEGIN 
                --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC))
                BEGIN
                    SET @p_BRANCH_ID = 'DV0001'
                    SET @C_Ma_DV_Cu = 'DV0001'
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
                    SET @C_Ma_PB_Cu = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
                END
                ELSE
                BEGIN
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
                    SET @C_Ma_DV_Cu = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
                END         
            END
            --ELSE
    
            IF(EXISTS(SELECT TOP 1 MaDVMoi FROM INSERTED WHERE MaDVMoi IS NOT NULL AND MaDVMoi <> ''))
            BEGIN
                --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = (SELECT MaDVMoi FROM INSERTED)))
                BEGIN
                    SET @p_BRANCH_ID = 'DV0001'
                    SET @C_Ma_DV_Moi = 'DV0001'
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
                    SET @C_Ma_PB_Moi = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
                END
                ELSE
                BEGIN
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
                    SET @C_Ma_DV_Moi = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
                END
            END        
            
            --GET KHỐI, TRUNG TÂM, PHÒNG BAN
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Cu ,@BLOCK_ID_OLD OUT ,@CENTER_ID_OLD OUT , @DEP_ID_OLD OUT   
                                   
            --GET KHỐI, TRUNG TÂM, PHÒNG BAN
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Moi, @BLOCK_ID_NEW OUT ,@CENTER_ID_NEW OUT ,@DEP_ID_NEW OUT     
                               
            PRINT @C_Ma_DV_Cu
            PRINT @C_Ma_DV_Moi
        		IF((@C_Ma_DV_Cu IS NULL OR @C_Ma_DV_Cu = '') AND (@C_Ma_DV_Moi IS NULL OR @C_Ma_DV_Moi = ''))
        		BEGIN
          			--N'Mã DV cũ và Mã DV mới không có giá trị'
                PRINT 1
        		END
            ELSE
            BEGIN
            
              IF((SELECT COUNT(*) FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE) > 1)
              BEGIN
                    SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE 
                              AND CEL.BRANCH_CODE = @MaDV_Cu_SYNC
                              AND (CEL.DEP_CODE = @C_Ma_PB_Cu OR @C_Ma_PB_Cu IS NULL))
              END
              ELSE
              BEGIN
                  SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE)
              END

            --CHECK DATA CHỨC DANH LƯU CỘT CDMOI HAY CDCU
            IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDMoi IS NOT NULL AND MaCDMoi <> ''))
            BEGIN
                  --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_MOI_SYNC))
                  BEGIN
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
                       VALUES (@MACD_MOI_SYNC, (SELECT TOP 1 CDMoi FROM INSERTED), NULL, NULL);
                  END
    
                  SET @p_POS_CODE = @MACD_MOI_SYNC
                  SET @p_POS_NAME = (SELECT TOP 1 CDMoi FROM INSERTED)
            END
            ELSE IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDCu IS NOT NULL AND MaCDCu <> ''))
            BEGIN
                  --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_CU_SYNC))
                  BEGIN
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
                       VALUES (@MACD_CU_SYNC, (SELECT TOP 1 CDCu FROM INSERTED), NULL, NULL);
                  END
    
                  SET @p_POS_CODE = @MACD_CU_SYNC
                  SET @p_POS_NAME = (SELECT TOP 1 CDCu FROM INSERTED)
            END
    
    
             --CHECK NHÂN VIÊN XEM CÓ XEM TRONG HỆ THỐNG KHÔNG
             IF(EXISTS(SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED)))
             BEGIN
                
                IF(EXISTS(
                        SELECT atmd.ASSET_ID FROM ASS_TRANSFER_MULTI_DT atmd
                        WHERE (atmd.EMP_ID = @I_EMP_ID AND atmd.EMP_ID_OLD = @I_EMP_ID 
                                               AND (atmd.BRANCH_ID = @C_Ma_DV_Moi AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu
                                                    AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'') AND ISNULL(atmd.KHOI_ID,'') = ISNULL(@BLOCK_ID_NEW,'')
                                                    AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'') AND ISNULL(atmd.CENTER_ID,'') = ISNULL(@CENTER_ID_NEW,'')
                                                    AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'') AND ISNULL(atmd.DEPT_ID,'') = ISNULL(@DEP_ID_NEW,''))  
      																				 AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC CHO CÙNG NV
                              OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.EMP_ID <> @I_EMP_ID  
                                  AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu 
                                  AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'')
                                  AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'')
                                  AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'')
                                  AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC NHƯNG KHÁC NV
                        UNION ALL
                        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 ))                             
                  SET @I_STATUS = '0'
                ELSE
                  SET @I_STATUS = '1'
      
                INSERT INTO CM_EMPLOYEE_TRANSFER_LOG (EMP_ID, TRANSFER_DT, BRANCH_ID_OLD, DEP_ID_OLD, BRANCH_ID_NEW, DEP_ID_NEW, STATUS)
                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);  
      
                --CHECK TRƯỜNG HỢP 1 NHÂN VIÊN CÓ QUYỀN 2 CHI NHÁNH TRỞ TRÊN (KHÁC TLNAME)

                --PHUCVH 22/12/22 CHECK NẾU MADV,PB MỚI THAY ĐỔI THÌ UPDATE LẠI MADV,PB CHO NHANVIEN
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE 
                              AND (ISNULL(A.BRANCH_ID,'') <> ISNULL(@p_BRANCH_ID,'')
                                  OR ISNULL(A.DEP_ID,'') <> ISNULL(@p_DEP_ID,''))))
                BEGIN
                      UPDATE CM_EMPLOYEE SET BRANCH_ID = @p_BRANCH_ID, DEP_ID = @p_DEP_ID 
                      WHERE EMP_CODE = @p_EMP_CODE
    
                      --CHECK UPDATE TL_USER VÀ CM_EMPLOYEE_LOG
                      IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE))
                      BEGIN
                          UPDATE CM_EMPLOYEE_LOG SET BRANCH_CODE = (SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID),
                                                     DEP_CODE = (SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
                          WHERE EMP_CODE = @p_EMP_CODE AND USER_DOMAIN = @TLNAME
    
                          UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID
                          WHERE TLNANME = @TLNAME
                      END
    
                END  
                
                --PHUCVH 22/12/22 CHECK NẾU MA CHUC DANH THAY ĐỔI THÌ UPDATE
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE
                                AND ISNULL(A.POS_CODE,'') <> ISNULL(@p_POS_CODE,'')))
                BEGIN
                     UPDATE CM_EMPLOYEE SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
                     WHERE EMP_CODE = @p_EMP_CODE 
    
                     UPDATE CM_EMPLOYEE_LOG SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
                     WHERE EMP_CODE = @p_EMP_CODE AND USER_DOMAIN = @TLNAME
                END                     
    
                --IF @@Error <> 0 GOTO ABORT
          		  --SET @Message = N'Đồng bộ điều chuyển nhân sự thành công'
             END
             ELSE
             BEGIN
                --KHÔNG CÓ TRONG HỆ THỐNG
                --THÊM THÔNG TIN NHÂN VIÊN
                		EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out		       
                		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])
                		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 )
                    --IF @@Error <> 0 GOTO ABORT
            				--SET @Message = N'Đồng bộ nhân viên mới thành công'
             END
            END
    
            
          END
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO