|
1
|
|
|
2
|
INSERT INTO SYS_PREFIX (ID, Prefix, Description) VALUES
|
|
3
|
('ASS_IMPORT_UPDATE_DT', 'IMP_DT', N'Import tài sản update'),
|
|
4
|
('ASS_IMPORT_UPDATE_MASTER', 'IMP', N'Import tài sản update')
|
|
5
|
GO
|
|
6
|
ALTER PROCEDURE dbo.ASS_PRIVATE_TRANSFER_MASTER_App
|
|
7
|
@p_TRANS_MULTI_MASTER_ID varchar(15),
|
|
8
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
9
|
@p_CHECKER_ID varchar(100) = NULL,
|
|
10
|
@p_APPROVE_DT VARCHAR(20) = NULL
|
|
11
|
AS
|
|
12
|
|
|
13
|
|
|
14
|
BEGIN TRANSACTION
|
|
15
|
declare @l_OLD_LOCHIST_ID varchar(15)
|
|
16
|
declare @l_LOCHIST_ID varchar(15)
|
|
17
|
declare @l_ASSHIST_ID varchar(15)
|
|
18
|
declare @l_ASSET_ID varchar(15)
|
|
19
|
declare @l_BRANCH_ID varchar(15)
|
|
20
|
declare @l_DEPT_ID varchar(15)
|
|
21
|
declare @l_EMP_ID varchar(15)
|
|
22
|
declare @l_DESC nvarchar(1000)
|
|
23
|
declare @l_LOCATION nvarchar(500)
|
|
24
|
DECLARE @l_MAKER_ID varchar(15)
|
|
25
|
--DECLARE @sToday varchar(10) = convert(varchar(10), @p_APPROVE_DT, 103)
|
|
26
|
|
|
27
|
--DECLARE @l_CUR_BRANCH_ID VARCHAR(15), @l_ASSET_TYPE VARCHAR(15), @l_AMORT_ACCTNO VARCHAR(50),
|
|
28
|
-- @l_ASSET_GROUP VARCHAR(15), @l_ET_ID VARCHAR(15), @l_TRN_REF_NO VARCHAR(15),
|
|
29
|
-- @l_DO_BRANCH_ID VARCHAR(15), @l_CUR_AMORT_AMT DECIMAL(18), @l_AMORT_STATUS VARCHAR(15)
|
|
30
|
--
|
|
31
|
--DECLARE @NOTE NVARCHAR(500), @CRET DATETIME
|
|
32
|
--SELECT @NOTE = NOTES, @CRET = CREATE_DT
|
|
33
|
--FROM [ASS_TRANSFER_MULTI_MASTER]
|
|
34
|
--WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
|
|
35
|
--LUCTV: 26-12-2018 KIEM TRA NEU TINH TRANG DANG LA 'R' THI KHONG CHO PHEP DUYET
|
|
36
|
IF(EXISTS(SELECT * FROM ASS_PRIVATE_TRANSFER_MASTER WHERE AUTH_STATUS ='R' AND TRANS_MULTI_MASTER_ID =@p_TRANS_MULTI_MASTER_ID))
|
|
37
|
BEGIN
|
|
38
|
ROLLBACK TRANSACTION
|
|
39
|
SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, N'Thông tin điều chuyển tài sản đang bị trả về. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
|
|
40
|
RETURN '-1'
|
|
41
|
END
|
|
42
|
IF(NOT EXISTS (SELECT * FROM dbo.TL_USER_GET_ROLES(@p_CHECKER_ID) WHERE (ROLE_ID = 'GDDV' OR ROLE_ID = 'GDDV_QLTS')))
|
|
43
|
BEGIN
|
|
44
|
ROLLBACK TRANSACTION
|
|
45
|
SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, N'Bạn không có quyền duyệt phiếu này' ErrorDesc
|
|
46
|
RETURN '-1'
|
|
47
|
END
|
|
48
|
--IF (SELECT CHARINDEX('ATGETD',@NOTE)) > 0
|
|
49
|
--BEGIN
|
|
50
|
-- SET @sToday = convert(varchar(10), @p_APPROVE_DT, 103)
|
|
51
|
-- SET @p_APPROVE_DT = @sToday
|
|
52
|
--END
|
|
53
|
--APPROVE MASTER
|
|
54
|
-- UPDATE [dbo].[ASS_PRIVATE_TRANSFER_MASTER]
|
|
55
|
-- SET AUTH_STATUS = 'A',AUTH_STATUS_KT='U',
|
|
56
|
-- CHECKER_ID = @p_CHECKER_ID,
|
|
57
|
-- APPROVE_DT = CONVERT(datetime, @p_APPROVE_DT, 103),MAKER_ID_KT = NULL,APPROVE_DT_KT = NULL, CHECKER_ID_KT = NULL
|
|
58
|
-- WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
|
|
59
|
--
|
|
60
|
DECLARE @TRANSFER_MULTI_ID varchar(15), @IS_PRIVATE int = 0, @count int =0
|
|
61
|
DECLARE @ASSET_ID varchar(15), @BRANCH_ID_OLD VARCHAR(15), @DEP_ID_OLD VARCHAR(15), @EMP_ID_OLD VARCHAR(15)
|
|
62
|
DECLARE DataCusor SCROLL CURSOR
|
|
63
|
FOR
|
|
64
|
SELECT A.TRANSFER_MULTI_ID,A.ASSET_ID
|
|
65
|
FROM [dbo].[ASS_PRIVATE_TRANSFER_DT] A
|
|
66
|
WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
|
|
67
|
|
|
68
|
OPEN DataCusor
|
|
69
|
|
|
70
|
FETCH NEXT FROM DataCusor INTO @TRANSFER_MULTI_ID,@ASSET_ID
|
|
71
|
WHILE @@FETCH_STATUS = 0
|
|
72
|
BEGIN
|
|
73
|
--Lay thong tin
|
|
74
|
SELECT @l_ASSET_ID = ASSET_ID,
|
|
75
|
@l_BRANCH_ID = BRANCH_ID,
|
|
76
|
@l_DEPT_ID = DEPT_ID,
|
|
77
|
@l_EMP_ID = EMP_ID,
|
|
78
|
@l_MAKER_ID = MAKER_ID,
|
|
79
|
@BRANCH_ID_OLD = BRANCH_ID_OLD,
|
|
80
|
@DEP_ID_OLD = DEPT_ID_OLD, @EMP_ID_OLD = EMP_ID_OLD
|
|
81
|
FROM [ASS_PRIVATE_TRANSFER_DT]
|
|
82
|
WHERE TRANSFER_MULTI_ID = @TRANSFER_MULTI_ID
|
|
83
|
|
|
84
|
EXEC SYS_CodeMasters_Gen 'ASS_MASTER_HIST', @l_ASSHIST_ID out
|
|
85
|
IF @l_ASSHIST_ID='' OR @l_ASSHIST_ID IS NULL GOTO ABORT
|
|
86
|
|
|
87
|
INSERT INTO ASS_MASTER_HIST
|
|
88
|
SELECT @l_ASSHIST_ID, a.*
|
|
89
|
FROM ASS_MASTER a
|
|
90
|
where a.ASSET_ID = @l_ASSET_ID
|
|
91
|
IF @@Error <> 0 GOTO ABORT
|
|
92
|
|
|
93
|
UPDATE ASS_MASTER
|
|
94
|
SET EMP_ID = @l_EMP_ID
|
|
95
|
WHERE ASSET_ID = @l_ASSET_ID
|
|
96
|
|
|
97
|
IF @@Error <> 0 GOTO ABORT
|
|
98
|
|
|
99
|
SELECT @l_OLD_LOCHIST_ID = LOCHIST_ID FROM ASS_LOCATION_HIST WHERE ASSET_ID = @l_ASSET_ID AND ISLEAF='Y'
|
|
100
|
|
|
101
|
--Uptdae isleaf='N' and END_DATE = ngay hien tai cho record hien tai
|
|
102
|
UPDATE ASS_LOCATION_HIST
|
|
103
|
SET USE_END_DT = CONVERT(DATETIME,@p_APPROVE_DT,103),
|
|
104
|
ISLEAF='N'
|
|
105
|
WHERE ASSET_ID = @l_ASSET_ID AND ISLEAF='Y'
|
|
106
|
|
|
107
|
EXEC SYS_CodeMasters_Gen 'ASS_LOCATION_HIST', @l_LOCHIST_ID out
|
|
108
|
IF @l_LOCHIST_ID='' OR @l_LOCHIST_ID IS NULL GOTO ABORT
|
|
109
|
|
|
110
|
--insert location moi
|
|
111
|
INSERT INTO ASS_LOCATION_HIST
|
|
112
|
(
|
|
113
|
LOCHIST_ID, ASSET_ID, USE_START_DT, USE_END_DT, BRANCH_ID, DEPT_ID,
|
|
114
|
EMP_ID, LOCATION, ISLEAF, PARENT_ID
|
|
115
|
)
|
|
116
|
VALUES
|
|
117
|
(
|
|
118
|
@l_LOCHIST_ID, @l_ASSET_ID, CONVERT(DATETIME,@p_APPROVE_DT,103), NULL, @l_BRANCH_ID, @l_DEPT_ID,
|
|
119
|
@l_EMP_ID, '', 'Y', @l_OLD_LOCHIST_ID
|
|
120
|
)
|
|
121
|
IF @@Error <> 0 GOTO ABORT
|
|
122
|
|
|
123
|
--INSERT VAO BANG ASS_TRANSACTIONS
|
|
124
|
INSERT INTO ASS_TRANSACTIONS(ASSET_ID, TRN_ID, TRN_TYPE, TRN_DATE, RECORD_STATUS, AUTH_STATUS,
|
|
125
|
[MAKER_ID], [CREATE_DT], [CHECKER_ID], [APPROVE_DT],ASSHIST_ID,LOCHIST_ID
|
|
126
|
)VALUES
|
|
127
|
(
|
|
128
|
@l_ASSET_ID, @TRANSFER_MULTI_ID, 'PRIVATE_TRANSFER', CONVERT(DATETIME, @p_APPROVE_DT, 103), '1', 'A',
|
|
129
|
@l_MAKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_CHECKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@l_ASSHIST_ID,@l_LOCHIST_ID
|
|
130
|
)
|
|
131
|
IF @@Error <> 0 GOTO ABORT
|
|
132
|
|
|
133
|
FETCH NEXT FROM DataCusor INTO @TRANSFER_MULTI_ID,@ASSET_ID
|
|
134
|
END
|
|
135
|
|
|
136
|
UPDATE [dbo].[ASS_PRIVATE_TRANSFER_MASTER]
|
|
137
|
SET AUTH_STATUS_KT = 'A',AUTH_STATUS = 'A',
|
|
138
|
CHECKER_ID = @p_CHECKER_ID,
|
|
139
|
APPROVE_DT = CONVERT(datetime, @p_APPROVE_DT, 103),
|
|
140
|
CHECKER_ID_KT = 'ADMIN',
|
|
141
|
APPROVE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
|
|
142
|
CREATE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
|
|
143
|
MAKER_ID_KT = 'ADMIN'
|
|
144
|
WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
|
|
145
|
|
|
146
|
UPDATE ASS_PRIVATE_TRANSFER_DT
|
|
147
|
SET AUTH_STATUS_KT = 'A',AUTH_STATUS = 'A',
|
|
148
|
CHECKER_ID = @p_CHECKER_ID,
|
|
149
|
APPROVE_DT = CONVERT(datetime, @p_APPROVE_DT, 103),
|
|
150
|
CHECKER_ID_KT = 'ADMIN',
|
|
151
|
APPROVE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
|
|
152
|
CREATE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),
|
|
153
|
MAKER_ID_KT = 'ADMIN'
|
|
154
|
WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID
|
|
155
|
|
|
156
|
UPDATE B
|
|
157
|
SET B.EMP_ID = A.EMP_ID
|
|
158
|
FROM ASS_PRIVATE_TRANSFER_DT A
|
|
159
|
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
|
|
160
|
WHERE A.TRANS_MULTI_MASTER_ID = @p_TRANS_MULTI_MASTER_ID
|
|
161
|
|
|
162
|
-- GIANT 21/09/2021
|
|
163
|
INSERT INTO dbo.PL_PROCESS
|
|
164
|
(
|
|
165
|
REQ_ID,
|
|
166
|
PROCESS_ID,
|
|
167
|
CHECKER_ID,
|
|
168
|
APPROVE_DT,
|
|
169
|
PROCESS_DESC,NOTES
|
|
170
|
)
|
|
171
|
VALUES
|
|
172
|
( @p_TRANS_MULTI_MASTER_ID, -- REQ_ID - varchar(15)
|
|
173
|
'APPROVE', -- PROCESS_ID - varchar(10)
|
|
174
|
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
|
|
175
|
GETDATE(), -- APPROVE_DT - datetime
|
|
176
|
N'Trưởng đơn vị phê duyệt ' ,
|
|
177
|
N'Trưởng đơn vị phê duyệt thành công' -- PROCESS_DESC - nvarchar(1000)
|
|
178
|
)
|
|
179
|
|
|
180
|
-- HUYHT 01/11/2021
|
|
181
|
-- DECLARE @BRANCH_ID_BG VARCHAR(15), @DEP_ID_BG VARCHAR(15), @BRANCH_ID_BN VARCHAR(15), @DEP_ID_BN VARCHAR(15)
|
|
182
|
-- SELECT TOP 1 @BRANCH_ID_BG = DT.BRANCH_ID_OLD, @DEP_ID_BG = DT.DEPT_ID_OLD, @BRANCH_ID_BN = DT.BRANCH_ID, @DEP_ID_BN = DT.DEPT_ID
|
|
183
|
-- FROM dbo.ASS_TRANSFER_MULTI_MASTER AM
|
|
184
|
-- INNER JOIN dbo.ASS_TRANSFER_MULTI_DT DT ON AM.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID
|
|
185
|
-- WHERE AM.TRANS_MULTI_MASTER_ID = @p_TRANS_MULTI_MASTER_ID
|
|
186
|
-- ORDER BY DT.TRANSFER_MULTI_ID ASC
|
|
187
|
|
|
188
|
-- DECLARE @l_TRANS_MULTI_MASTER_CONFIRM_ID VARCHAR(15)
|
|
189
|
-- EXEC SYS_CodeMasters_Gen 'ASS_TRANSFER_CONFIRM_MASTER', @l_TRANS_MULTI_MASTER_CONFIRM_ID out
|
|
190
|
-- IF @l_TRANS_MULTI_MASTER_CONFIRM_ID='' OR @l_TRANS_MULTI_MASTER_CONFIRM_ID IS NULL GOTO ABORT
|
|
191
|
--
|
|
192
|
-- INSERT INTO [ASS_TRANSFER_CONFIRM_MASTER]
|
|
193
|
-- (
|
|
194
|
-- [TRANS_MULTI_MASTER_CONFIRM_ID],
|
|
195
|
-- [TRANS_MULTI_MASTER_ID],
|
|
196
|
-- [BRANCH_ID_BG],
|
|
197
|
-- [DEP_ID_BG],
|
|
198
|
-- [BRANCH_ID_BN],
|
|
199
|
-- [DEP_ID_BN]
|
|
200
|
-- )
|
|
201
|
-- VALUES
|
|
202
|
-- (
|
|
203
|
-- @l_TRANS_MULTI_MASTER_CONFIRM_ID,
|
|
204
|
-- @p_TRANS_MULTI_MASTER_ID ,
|
|
205
|
-- @BRANCH_ID_BG,
|
|
206
|
-- @DEP_ID_BG,
|
|
207
|
-- @BRANCH_ID_BN,
|
|
208
|
-- @DEP_ID_BN
|
|
209
|
-- )
|
|
210
|
|
|
211
|
--PHUCVH 14/11/22 UPDATE TÀI SẢN ĐÃ XONG GIAO DỊCH
|
|
212
|
UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL
|
|
213
|
WHERE ASSET_ID IN (SELECT A.ASSET_ID FROM ASS_PRIVATE_TRANSFER_DT A WHERE A.TRANS_MULTI_MASTER_ID = @p_TRANS_MULTI_MASTER_ID)
|
|
214
|
|
|
215
|
COMMIT TRANSACTION
|
|
216
|
SELECT '0' as Result, @p_TRANS_MULTI_MASTER_ID COL_MULTI_MASTER_ID, '' ErrorDesc
|
|
217
|
RETURN '0'
|
|
218
|
ABORT:
|
|
219
|
BEGIN
|
|
220
|
ROLLBACK TRANSACTION
|
|
221
|
SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, ERROR_MESSAGE() ErrorDesc
|
|
222
|
RETURN '-1'
|
|
223
|
End
|
|
224
|
GO
|
|
225
|
|
|
226
|
ALTER TRIGGER dbo.TRIG_CM_EMPLOYEE_SYNC_Ins
|
|
227
|
ON dbo.CM_EMPLOYEE_SYNC
|
|
228
|
AFTER INSERT
|
|
229
|
AS
|
|
230
|
BEGIN
|
|
231
|
--STORE ĐỒNG BỘ NHÂN VIÊN VÀ ĐIỀU CHUYỂN TÀI SẢN NHÂN SỰ
|
|
232
|
--29/09/22
|
|
233
|
SET NOCOUNT ON;
|
|
234
|
IF (EXISTS(SELECT TOP 1 MaNS FROM INSERTED))
|
|
235
|
BEGIN
|
|
236
|
|
|
237
|
DECLARE @MaDV_Cu_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVCu FROM INSERTED)
|
|
238
|
DECLARE @MaDV_Moi_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVMoi FROM INSERTED)
|
|
239
|
|
|
240
|
--UPDATE MÃ MỚI DO KT UPDATE
|
|
241
|
IF(@MaDV_Cu_SYNC = '0100')
|
|
242
|
BEGIN
|
|
243
|
SET @MaDV_Cu_SYNC = '0600'
|
|
244
|
END
|
|
245
|
|
|
246
|
IF(@MaDV_Cu_SYNC = '0101')
|
|
247
|
BEGIN
|
|
248
|
SET @MaDV_Cu_SYNC = '0601'
|
|
249
|
END
|
|
250
|
|
|
251
|
IF(@MaDV_Moi_SYNC = '0100')
|
|
252
|
BEGIN
|
|
253
|
SET @MaDV_Moi_SYNC = '0600'
|
|
254
|
END
|
|
255
|
|
|
256
|
IF(@MaDV_Moi_SYNC = '0101')
|
|
257
|
BEGIN
|
|
258
|
SET @MaDV_Moi_SYNC = '0601'
|
|
259
|
END
|
|
260
|
|
|
261
|
--MAP DATA HR VỚI KT
|
|
262
|
IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC))
|
|
263
|
BEGIN
|
|
264
|
SET @MaDV_Cu_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC)
|
|
265
|
END
|
|
266
|
IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC))
|
|
267
|
BEGIN
|
|
268
|
SET @MaDV_Moi_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC)
|
|
269
|
END
|
|
270
|
DECLARE @MACD_CU_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDCu FROM INSERTED)
|
|
271
|
DECLARE @MACD_MOI_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDMoi FROM INSERTED)
|
|
272
|
|
|
273
|
--START: PARAM INSERT NHÂN VIÊN MỚI
|
|
274
|
DECLARE @l_EMP_ID VARCHAR(500)
|
|
275
|
DECLARE @p_EMP_CODE varchar(500) = (SELECT TOP 1 MaNS FROM INSERTED)
|
|
276
|
DECLARE @p_EMP_NAME nvarchar(500) = (SELECT TOP 1 HoTen FROM INSERTED)
|
|
277
|
|
|
278
|
DECLARE @p_BRANCH_ID varchar(15) = NULL
|
|
279
|
DECLARE @p_DEP_ID varchar(15) = NULL
|
|
280
|
|
|
281
|
DECLARE @p_POS_CODE VARCHAR(50) = NULL
|
|
282
|
DECLARE @p_POS_NAME NVARCHAR(MAX) = NULL
|
|
283
|
|
|
284
|
DECLARE @p_NOTES nvarchar(1000) = N'ĐỒNG BỘ'
|
|
285
|
DECLARE @p_RECORD_STATUS varchar(1) = '1'
|
|
286
|
DECLARE @p_AUTH_STATUS varchar(1) = 'A'
|
|
287
|
DECLARE @p_MAKER_ID varchar(15) = 'ADMIN'
|
|
288
|
DECLARE @p_CREATE_DT VARCHAR(50) = GETDATE()
|
|
289
|
DECLARE @p_CHECKER_ID varchar(15) = 'ADMIN'
|
|
290
|
DECLARE @p_APPROVE_DT VARCHAR(50) = GETDATE()
|
|
291
|
--END: PARAM INSERT NHÂN VIÊN MỚI
|
|
292
|
|
|
293
|
--START: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
|
|
294
|
DECLARE @I_EMP_ID VARCHAR(20) = (SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED))
|
|
295
|
DECLARE @I_STATUS VARCHAR(5)
|
|
296
|
|
|
297
|
DECLARE @C_Ma_DV_Cu VARCHAR(500) = NULL --MÃ ĐV CŨ ĐỂ CHECK IF
|
|
298
|
DECLARE @C_Ma_DV_Moi VARCHAR(500) = NULL --MÃ DV MỚI ĐỂ CHECK IF
|
|
299
|
DECLARE @C_Ma_PB_Cu VARCHAR(500) = NULL --MÃ PB CŨ ĐỂ CHECK IF
|
|
300
|
DECLARE @C_Ma_PB_Moi VARCHAR(500) = NULL --MÃ PB MỚI ĐỂ CHECK IF
|
|
301
|
--END: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
|
|
302
|
|
|
303
|
--START: PARAM INSERT
|
|
304
|
|
|
305
|
--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
|
|
306
|
IF (EXISTS(SELECT TOP 1 MaDVCu FROM INSERTED WHERE MaDVCu IS NOT NULL AND MaDVCu <> ''))
|
|
307
|
BEGIN
|
|
308
|
--CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
|
|
309
|
IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC))
|
|
310
|
BEGIN
|
|
311
|
SET @p_BRANCH_ID = 'DV0001'
|
|
312
|
SET @C_Ma_DV_Cu = 'DV0001'
|
|
313
|
SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
|
|
314
|
SET @C_Ma_PB_Cu = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
|
|
315
|
END
|
|
316
|
ELSE
|
|
317
|
BEGIN
|
|
318
|
SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
|
|
319
|
SET @C_Ma_DV_Cu = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
|
|
320
|
END
|
|
321
|
END
|
|
322
|
--ELSE
|
|
323
|
|
|
324
|
IF(EXISTS(SELECT TOP 1 MaDVMoi FROM INSERTED WHERE MaDVMoi IS NOT NULL AND MaDVMoi <> ''))
|
|
325
|
BEGIN
|
|
326
|
--CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
|
|
327
|
IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = (SELECT MaDVMoi FROM INSERTED)))
|
|
328
|
BEGIN
|
|
329
|
SET @p_BRANCH_ID = 'DV0001'
|
|
330
|
SET @C_Ma_DV_Moi = 'DV0001'
|
|
331
|
SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
|
|
332
|
SET @C_Ma_PB_Moi = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
|
|
333
|
END
|
|
334
|
ELSE
|
|
335
|
BEGIN
|
|
336
|
SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
|
|
337
|
SET @C_Ma_DV_Moi = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
|
|
338
|
END
|
|
339
|
END
|
|
340
|
|
|
341
|
PRINT @C_Ma_DV_Cu
|
|
342
|
PRINT @C_Ma_DV_Moi
|
|
343
|
IF((@C_Ma_DV_Cu IS NULL OR @C_Ma_DV_Cu = '') AND (@C_Ma_DV_Moi IS NULL OR @C_Ma_DV_Moi = ''))
|
|
344
|
BEGIN
|
|
345
|
--N'Mã DV cũ và Mã DV mới không có giá trị'
|
|
346
|
PRINT 1
|
|
347
|
END
|
|
348
|
ELSE
|
|
349
|
BEGIN
|
|
350
|
--CHECK DATA CHỨC DANH LƯU CỘT CDMOI HAY CDCU
|
|
351
|
IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDMoi IS NOT NULL AND MaCDMoi <> ''))
|
|
352
|
BEGIN
|
|
353
|
--NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
|
|
354
|
IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_MOI_SYNC))
|
|
355
|
BEGIN
|
|
356
|
INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
|
|
357
|
VALUES (@MACD_MOI_SYNC, (SELECT TOP 1 CDMoi FROM INSERTED), NULL, NULL);
|
|
358
|
END
|
|
359
|
|
|
360
|
SET @p_POS_CODE = @MACD_MOI_SYNC
|
|
361
|
SET @p_POS_NAME = (SELECT TOP 1 CDMoi FROM INSERTED)
|
|
362
|
END
|
|
363
|
ELSE IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDCu IS NOT NULL AND MaCDCu <> ''))
|
|
364
|
BEGIN
|
|
365
|
--NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
|
|
366
|
IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_CU_SYNC))
|
|
367
|
BEGIN
|
|
368
|
INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
|
|
369
|
VALUES (@MACD_CU_SYNC, (SELECT TOP 1 CDCu FROM INSERTED), NULL, NULL);
|
|
370
|
END
|
|
371
|
|
|
372
|
SET @p_POS_CODE = @MACD_CU_SYNC
|
|
373
|
SET @p_POS_NAME = (SELECT TOP 1 CDCu FROM INSERTED)
|
|
374
|
END
|
|
375
|
|
|
376
|
|
|
377
|
--CHECK NHÂN VIÊN XEM CÓ XEM TRONG HỆ THỐNG KHÔNG
|
|
378
|
IF(EXISTS(SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED)))
|
|
379
|
BEGIN
|
|
380
|
|
|
381
|
IF(EXISTS(
|
|
382
|
SELECT atmd.ASSET_ID FROM ASS_TRANSFER_MULTI_DT atmd
|
|
383
|
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)
|
|
384
|
AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC CHO CHÙNG NV
|
|
385
|
OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu AND atmd.EMP_ID <> @I_EMP_ID AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC NHƯNG KHÁC NV
|
|
386
|
UNION ALL
|
|
387
|
SELECT am.ASSET_ID FROM ASS_MASTER am WHERE am.BRANCH_ID = @C_Ma_DV_Cu AND am.EMP_ID = @I_EMP_ID))
|
|
388
|
SET @I_STATUS = '0'
|
|
389
|
ELSE
|
|
390
|
SET @I_STATUS = '1'
|
|
391
|
|
|
392
|
INSERT INTO CM_EMPLOYEE_TRANSFER_LOG (EMP_ID, TRANSFER_DT, BRANCH_ID_OLD, BRANCH_ID_NEW, STATUS)
|
|
393
|
VALUES (@I_EMP_ID, CONVERT(DATETIME,(SELECT TOP 1 TGTao FROM INSERTED),103), @C_Ma_DV_Cu, @C_Ma_DV_Moi, @I_STATUS);
|
|
394
|
|
|
395
|
--PHUCVH 22/12/22 CHECK NẾU MADV,PB MỚI THAY ĐỔI THÌ UPDATE LẠI MADV,PB CHO NHANVIEN
|
|
396
|
IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE
|
|
397
|
AND (ISNULL(A.BRANCH_ID,'') <> ISNULL(@p_BRANCH_ID,'')
|
|
398
|
OR ISNULL(A.DEP_ID,'') <> ISNULL(@p_DEP_ID,''))))
|
|
399
|
BEGIN
|
|
400
|
UPDATE CM_EMPLOYEE SET BRANCH_ID = @p_BRANCH_ID, DEP_ID = @p_DEP_ID
|
|
401
|
WHERE EMP_CODE = @p_EMP_CODE
|
|
402
|
|
|
403
|
--CHECK UPDATE TL_USER VÀ CM_EMPLOYEE_LOG
|
|
404
|
IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE))
|
|
405
|
BEGIN
|
|
406
|
UPDATE CM_EMPLOYEE_LOG SET BRANCH_CODE = (SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID),
|
|
407
|
DEP_CODE = (SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
|
|
408
|
WHERE EMP_CODE = @p_EMP_CODE
|
|
409
|
|
|
410
|
UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID
|
|
411
|
WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE)
|
|
412
|
END
|
|
413
|
|
|
414
|
END
|
|
415
|
|
|
416
|
--PHUCVH 22/12/22 CHECK NẾU MA CHUC DANH THAY ĐỔI THÌ UPDATE
|
|
417
|
IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE
|
|
418
|
AND ISNULL(A.POS_CODE,'') <> ISNULL(@p_POS_CODE,'')))
|
|
419
|
BEGIN
|
|
420
|
UPDATE CM_EMPLOYEE SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
|
|
421
|
WHERE EMP_CODE = @p_EMP_CODE
|
|
422
|
|
|
423
|
UPDATE CM_EMPLOYEE_LOG SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
|
|
424
|
WHERE EMP_CODE = @p_EMP_CODE
|
|
425
|
END
|
|
426
|
|
|
427
|
--IF @@Error <> 0 GOTO ABORT
|
|
428
|
--SET @Message = N'Đồng bộ điều chuyển nhân sự thành công'
|
|
429
|
END
|
|
430
|
ELSE
|
|
431
|
BEGIN
|
|
432
|
--KHÔNG CÓ TRONG HỆ THỐNG
|
|
433
|
--THÊM THÔNG TIN NHÂN VIÊN
|
|
434
|
EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out
|
|
435
|
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])
|
|
436
|
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 )
|
|
437
|
--IF @@Error <> 0 GOTO ABORT
|
|
438
|
--SET @Message = N'Đồng bộ nhân viên mới thành công'
|
|
439
|
END
|
|
440
|
END
|
|
441
|
|
|
442
|
|
|
443
|
END
|
|
444
|
END
|
|
445
|
|
|
446
|
GO
|
|
447
|
|
|
448
|
|
|
449
|
BEGIN TRANSACTION
|
|
450
|
DECLARE @FULLNAME NVARCHAR(MAX) =N'Nguyễn Hồng Hải ,Nguyễn Thị Lợi ,Phan Thị Cẩm Tú ,Nông Bùi Tấn Huy ,Nguyễn Thị Hường ,Nguyễn Thị Bích Ngọc ,Phạm Mạnh Hiệp ,Vũ Đức Dũng ,Trần Kim Thành ,Lâm Thu Thư ,Lục Mỹ Phụng ,Châu Văn Hiệp ,NGUYỄN HOÀNG THÔNG ,NGUYỄN THỊ THÙY DUNG ,LÊ THỊ CHÂU GIANG ,ĐỖ THỊ HIỀN ,NGUYỄN THỊ HIỀN ,PHAN THỊ LAN HƯƠNG ,Nguyễn Thị Vân ,Hoàng Thị Thanh Huyền ,Nguyễn Thanh Tùng ,Nguyễn Duy Hiến ,Trần Thái Hà ,Lương Thị Y Duyên ,Nguyễn Thị Phương Mai ,Tiêu Ngoc Lâm Thảo ,Dương Mộc Triều ,Châu Văn Hiệp ,Nguyễn Thị Phương Nhung ,Trịnh Thị Mai Soan ,Hà Quang Kiên ,NGUYỄN HOÀNG THÔNG ,NGUYỄN THỊ THÙY DUNG ,LÊ THỊ CHÂU GIANG ,BÙI THỊ HỒNG ÁNH ,TRẦN LÊ TƯỜNG VY ,NGUYỄN THỊ THÙY NGÂN ,Bùi Ngô Anh ,Phạm Thị Bích Phượng ,Đỗ Thị Nga ,Lê Văn Trung ,Lê Hoàng Thanh'
|
|
451
|
DECLARE @EMP_CODE VARCHAR(MAX) = N'2018-07001 ,0000-10139 ,0000-10129 ,2019-08061 ,2020-07054 ,2020-06056 ,2020-07076 ,0000-10348 ,0201-12003 ,STR-08-028 ,STR-08-003 ,STR-08-005 ,HNI-08-034 ,0000-09213 ,2020-03021 ,2022-08040 ,040-09-012 ,0412-14001 ,HNI-08-031 ,0401-10029 ,017-08-003 ,0405-14002 ,0401-10031 ,040-09-054 ,020-09-001 ,STR-08-032 ,STR-08-017 ,STR-08-005 ,0401- 09023 ,2018-04041 ,0501-10003 ,HNI-08-034 ,0000-09213 ,2020-03021 ,HSO-08-091 ,2023-02019 ,0135-17001 ,0401-11009 ,2017-04051 ,0417-15002 ,21.0-17063 ,2022-10030'
|
|
452
|
DECLARE @USER_NAME VARCHAR(MAX) =N'hainh1200,loint,tuptc,huynbt,huongnt2800,ngocntb2800,hieppm2800,dungvd,thanhtk,thult,phunglm,hiepcv,thongnh,dungntt,giangltc0714,HIENDT0811,HIENNT0811,HUONGPTL,VANNT0812,HUYENHTT,TUNGNT0812,hiennd0802,hatt0802,duyenlty,maintp,thaotnl,trieudm,hiepcv,nhungntp,soanttm,kienhq,thongnh,dungntt,giangltc0714,ANHBTH1901,VYTLT1901,NGANNTT,anhbn,phuongptb,ngadt,trunglv,thanhlh0713'
|
|
453
|
DECLARE @EMAIL NVARCHAR(MAX) = N'nguyenhonghai@vietbank.com.vn,nguyenthiloi@vietbank.com.vn,phanthicamtu@vietbank.com.vn,nongbuitanhuy@vietbank.com.vn,nguyenthihuong1@vietbank.com.vn,nguyenthibichngoc1@vietbank.com.vn,phammanhhiep@vietbank.com.vn,vuducdung@vietbank.com.vn,trankimthanh@vietbank.com.vn,lamthuthu@vietbank.com.vn,lucmyphung@vietbank.com.vn,chauvanhiep@vietbank.com.vn,nguyenhoangthong@vietbank.com.vn,nguyenthithuydung@vietbank.com.vn,lethichaugiang@vietbank.com.vn,dothihien@vietbank.com.vn,nguyenthihien2@vietbank.com.vn,phanthilanhuong@vietbank.com.vn,nguyenthivan@vietbank.com.vn,hoangthithanhhuyen@vietbank.com.vn,nguyenthanhtung@vietbank.com.vn,nguyenduyhien@vietbank.com.vn,tranthaiha@vietbank.com.vn,luongthiyduyen@vietbank.com.vn,nguyenthiphuongmai@vietbank.com.vn,tieungoclamthao@vietbank.com.vn,duongmoctrieu@vietbank.com.vn,chauvanhiep@vietbank.com.vn,nguyenthiphuongnhung@vietbank.com.vn,trinhthimaisoan@vietbank.com.vn,haquangkien@vietbank.com.vn,nguyenhoangthong@vietbank.com.vn,nguyenthithuydung@vietbank.com.vn,lethichaugiang@vietbank.com.vn,buithihonganh@vietbank.com.vn,tranletuongvy@vietbank.com.vn,nguyenthithuyngan@vietbank.com.vn,buingoanh@vietbank.com.vn,phamthibichphuong@vietbank.com.vn,dothinga@vietbank.com.vn,levantrung@vietbank.com.vn,lehoangthanh@vietbank.com.vn'
|
|
454
|
DECLARE @BRANCH_CODE VARCHAR(MAX) = N'1200,1200,1200,1200,2800,2800,2800,2800,0600,0600,0600,0600,0714,0714,0714,0811,0811,0811,0812,0812,0812,0802,0802,0802,0601,0601,0601,0600,1003,1003,1003,0714,0714,0714,1901,1901,1901,0808,0808,0808,0718,0718'
|
|
455
|
DECLARE @DEP_ID_IMP VARCHAR(MAX) = N',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'
|
|
456
|
DECLARE @ROLE VARCHAR(MAX) = 'GDDV,NVTT,NVTT,DVCM,NVTT,NVTT,DVCM,GDDV,NVTT,NVTT,GDDV,DVCM,GDDV,NVTT,NVTT,NVTT,NVTT,GDDV,NVTT,NVTT,GDDV,GDDV,NVTT,NVTT,NVTT,NVTT,GDDV,DVCM,NVTT,NVTT,GDDV,GDDV,NVTT,NVTT,NVTT,NVTT,GDDV,GDDV,NVTT,NVTT,NVTT,GDDV'
|
|
457
|
--DELETE IMPORT_USER_QLTS
|
|
458
|
--INSERT INTO IMPORT_USER_QLTS (TLNAME, FULLNAME, EMAIL, EMP_CODE, BRANCH_CODE, DEP_CODE, ROLE_NAME)
|
|
459
|
SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME
|
|
460
|
FROM (
|
|
461
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME
|
|
462
|
FROM STRING_SPLIT(@FULLNAME,','))A
|
|
463
|
LEFT JOIN (
|
|
464
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE
|
|
465
|
FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID
|
|
466
|
LEFT JOIN (
|
|
467
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME
|
|
468
|
FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID
|
|
469
|
LEFT JOIN (
|
|
470
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL
|
|
471
|
FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID
|
|
472
|
LEFT JOIN (
|
|
473
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE
|
|
474
|
FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID
|
|
475
|
LEFT JOIN (
|
|
476
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE
|
|
477
|
FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID
|
|
478
|
LEFT JOIN (
|
|
479
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME
|
|
480
|
FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID
|
|
481
|
DECLARE @INS_MESSAGE NVARCHAR(1000)
|
|
482
|
DECLARE @INS_FULLNAME NVARCHAR(1000)
|
|
483
|
DECLARE @INS_EMP_CODE VARCHAR(1000)
|
|
484
|
DECLARE @INS_USER_NAME VARCHAR(1000)
|
|
485
|
DECLARE @INS_EMAIL NVARCHAR(1000)
|
|
486
|
DECLARE @INS_BRANCH_CODE VARCHAR(1000)
|
|
487
|
DECLARE @INS_DEP_CODE VARCHAR(1000)
|
|
488
|
DECLARE @INS_ROLE VARCHAR(1000)
|
|
489
|
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
|
|
490
|
--SELECT TLNAME
|
|
491
|
-- ,FULLNAME
|
|
492
|
-- ,EMAIL
|
|
493
|
-- ,EMP_CODE
|
|
494
|
-- ,BRANCH_CODE
|
|
495
|
-- ,DEP_CODE
|
|
496
|
-- ,ROLE_NAME FROM IMPORT_USER_QLTS
|
|
497
|
-- where TLNAME = 'khoidt'
|
|
498
|
SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME
|
|
499
|
FROM (
|
|
500
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME
|
|
501
|
FROM STRING_SPLIT(@FULLNAME,','))A
|
|
502
|
LEFT JOIN (
|
|
503
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE
|
|
504
|
FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID
|
|
505
|
LEFT JOIN (
|
|
506
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME
|
|
507
|
FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID
|
|
508
|
LEFT JOIN (
|
|
509
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL
|
|
510
|
FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID
|
|
511
|
LEFT JOIN (
|
|
512
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE
|
|
513
|
FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID
|
|
514
|
LEFT JOIN (
|
|
515
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE
|
|
516
|
FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID
|
|
517
|
LEFT JOIN (
|
|
518
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME
|
|
519
|
FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID
|
|
520
|
OPEN cur
|
|
521
|
FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE
|
|
522
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
523
|
|
|
524
|
IF(NOT EXISTS(SELECT 1 FROM TL_USER WHERE TLNANME = @INS_USER_NAME))
|
|
525
|
BEGIN
|
|
526
|
DECLARE @BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15), @DEP_CODE VARCHAR(100)
|
|
527
|
|
|
528
|
SELECT @BRANCH_ID = cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @INS_BRANCH_CODE
|
|
529
|
SELECT @DEP_ID = cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @INS_DEP_CODE
|
|
530
|
SET @DEP_CODE = @INS_DEP_CODE
|
|
531
|
|
|
532
|
if(@INS_ROLE = 'DVCM')
|
|
533
|
BEGIN
|
|
534
|
SET @BRANCH_ID = 'DV0001'
|
|
535
|
SET @DEP_ID = 'DEP000000000027'
|
|
536
|
SET @DEP_CODE = '05J03'
|
|
537
|
END
|
|
538
|
|
|
539
|
INSERT INTO TL_USER ( TLID, TLNANME, Password, TLFullName, TLSUBBRID, BRANCH_TYPE, EMAIL, ADDRESS, PHONE, AUTH_STATUS, MARKER_ID, AUTH_ID, APPROVE_DT, ISAPPROVE, Birthday, ISFIRSTTIME, SECUR_CODE, AccessFailedCount, AuthenticationSource, ConcurrencyStamp, CreatorUserId, DeleterUserId, EmailAddress, EmailConfirmationCode, IsActive, IsDeleted, IsEmailConfirmed, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, LastModifierUserId, LockoutEndDateUtc, Name, NormalizedEmailAddress, NormalizedUserName, PasswordResetCode, PhoneNumber, ProfilePictureId, SecurityStamp, ShouldChangePasswordOnNextLogin, Surname, TenantId, SignInToken, SignInTokenExpireTimeUtc, GoogleAuthenticatorKey, SendActivationEmail, DEP_ID, CreationTime, UamFullName, UamEmployeeId, UamCompanyCode, UamWfDataId, UamCompanyName, UamJobTitle, UserCurrentLanguage, EMAILTEMP) VALUES
|
|
540
|
(NULL, @INS_USER_NAME, N'AQAAAAEAACcQAAAAEMF7sJx/2L/X7bkO6YmSRfr8d7Na/RURfT4tDZYFIDMaik/cy+y7PSfq48Btaka28A==', @INS_FULLNAME, @BRANCH_ID, '', @INS_EMAIL, N'', '', 'A', 'bichnn', NULL, GETDATE(), '1', GETDATE(), '1', @DEP_ID, 0, NULL, N'fd2b0a93-3081-460d-b969-b5a75f96c0de', NULL, NULL, @INS_EMAIL, NULL, CONVERT(bit, 'True'), CONVERT(bit, 'False'), CONVERT(bit, 'True'), CONVERT(bit, 'False'), CONVERT(bit, 'True'), CONVERT(bit, 'False'), 259, NULL, NULL, @INS_EMAIL, @INS_USER_NAME, NULL, NULL, NULL, N'QSTE4B7VTYWRQON4JZTJST6QN4PEVVLB', CONVERT(bit, 'False'), NULL, 1, NULL, NULL, NULL, CONVERT(bit, 'False'), @DEP_ID, GETDATE(), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
|
|
541
|
|
|
542
|
INSERT INTO CM_EMPLOYEE_LOG (EMP_CODE, EMP_NAME, BRANCH_CODE, DEP_CODE, USER_DOMAIN, POS_CODE, POS_NAME, CREATE_DT)
|
|
543
|
VALUES (@INS_EMP_CODE, @INS_FULLNAME, @INS_BRANCH_CODE, @INS_DEP_CODE, @INS_USER_NAME, '', N'', GETDATE());
|
|
544
|
|
|
545
|
INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)
|
|
546
|
VALUES (SYSDATETIME(), 0, (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE), 1, (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME));
|
|
547
|
|
|
548
|
END
|
|
549
|
|
|
550
|
IF(NOT EXISTS(SELECT 1 FROM AbpUserRoles AUR WHERE AUR.RoleId = (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE) AND AUR.UserId = (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME)))
|
|
551
|
BEGIN
|
|
552
|
INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)
|
|
553
|
VALUES (SYSDATETIME(), 0, (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE), 1, (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME));
|
|
554
|
END
|
|
555
|
|
|
556
|
SET @BRANCH_ID = NULL
|
|
557
|
SET @DEP_ID = NULL
|
|
558
|
SET @DEP_CODE = NULL
|
|
559
|
|
|
560
|
FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE
|
|
561
|
END
|
|
562
|
CLOSE cur
|
|
563
|
DEALLOCATE cur
|
|
564
|
COMMIT TRANSACTION
|