|
1
|
ALTER PROCEDURE [dbo].[ASS_USE_MULTI_BVB_MASTER_Upd]
|
|
2
|
@p_USER_MASTER_ID varchar(15) = null ,
|
|
3
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
4
|
@p_USE_EXPORT_DT VARCHAR(20) = NULL,
|
|
5
|
@p_USER_EXPORT nvarchar(200) = NULL,
|
|
6
|
@p_NOTES nvarchar(1000) = NULL,
|
|
7
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
8
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
9
|
@p_MAKER_ID varchar(15) = NULL,
|
|
10
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
11
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
12
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
13
|
@p_AUTH_STATUS_KT varchar(15) = NULL,
|
|
14
|
@p_CREATE_DT_KT VARCHAR(20) = NULL,
|
|
15
|
@p_APPROVE_DT_KT VARCHAR(20) = NULL,
|
|
16
|
@p_MAKER_ID_KT varchar(50) = NULL,
|
|
17
|
@p_CHECKER_ID_KT varchar(50) = NULL,
|
|
18
|
@p_REPORT_STATUS varchar(15) = NULL,
|
|
19
|
@p_BRANCH_CREATE varchar(15) = NULL,
|
|
20
|
@p_XmlData XML = NUL
|
|
21
|
AS
|
|
22
|
/*
|
|
23
|
--Validation is here
|
|
24
|
DECLARE @ERRORSYS NVARCHAR(15) = ''
|
|
25
|
IF ( NOT EXISTS ( SELECT * FROM ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID=@p_USER_MASTER_ID
|
|
26
|
SET @ERRORSYS = ''
|
|
27
|
IF @ERRORSYS <> ''
|
|
28
|
BEGIN
|
|
29
|
SELECT ErrorCode Result, ''USER_MASTER_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
|
|
30
|
RETURN '0'
|
|
31
|
END
|
|
32
|
*/
|
|
33
|
--------LUCTV - KHAI BAO SO THANG KHAU HAO MIN, MAX CUA TAI SAN
|
|
34
|
DECLARE @NHOMTS VARCHAR(15)
|
|
35
|
DECLARE @MIN_AMR INT, @MAX_AMR INT, @INDEX INT
|
|
36
|
SET @INDEX=0
|
|
37
|
--END
|
|
38
|
DECLARE
|
|
39
|
@USE_MULTI_ID varchar(15),
|
|
40
|
@ASSET_ID varchar(15),
|
|
41
|
@BRANCH_ID varchar(15),
|
|
42
|
@DEPT_ID varchar(15),
|
|
43
|
@DIVISION_ID varchar(15),
|
|
44
|
@EMP_ID varchar(15),
|
|
45
|
@AMORT_START_DATE VARCHAR(20),
|
|
46
|
@AMORT_MONTH decimal(18),
|
|
47
|
@AMORT_END_DATE VARCHAR(20),
|
|
48
|
@CORE_NOTE nvarchar(500),
|
|
49
|
@NOTES nvarchar(1000)
|
|
50
|
Declare @hdoc INT
|
|
51
|
Exec sp_xml_preparedocument @hdoc Output,@p_XmlData
|
|
52
|
|
|
53
|
DECLARE XmlData CURSOR FOR
|
|
54
|
SELECT *
|
|
55
|
FROM OPENXML(@hDoc,'/Root/XmlData',2)
|
|
56
|
WITH
|
|
57
|
(
|
|
58
|
USE_MULTI_ID varchar(15),
|
|
59
|
ASSET_ID varchar(15),
|
|
60
|
BRANCH_ID varchar(15),
|
|
61
|
DEPT_ID varchar(15),
|
|
62
|
DIVISION_ID varchar(15),
|
|
63
|
EMP_ID varchar(15),
|
|
64
|
AMORT_START_DATE VARCHAR(20),
|
|
65
|
AMORT_MONTH decimal(18),
|
|
66
|
AMORT_END_DATE VARCHAR(20),
|
|
67
|
CORE_NOTE nvarchar(500),
|
|
68
|
NOTES nvarchar(1000)
|
|
69
|
)
|
|
70
|
OPEN XmlData
|
|
71
|
DELETE FROM ASS_USE_MULTI_DT WHERE USER_MASTER_ID = @p_USER_MASTER_ID
|
|
72
|
BEGIN TRANSACTION
|
|
73
|
UPDATE ASS_USE_MULTI_MASTER SET [BRANCH_ID] = @p_BRANCH_ID,[USE_EXPORT_DT] = CONVERT(DATETIME, @p_USE_EXPORT_DT, 103),[USER_EXPORT] = @p_USER_EXPORT,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,
|
|
74
|
[AUTH_STATUS] = @p_AUTH_STATUS,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),[AUTH_STATUS_KT] = 'U',
|
|
75
|
[CREATE_DT_KT] = CONVERT(DATETIME, @p_CREATE_DT_KT, 103),[APPROVE_DT_KT] = CONVERT(DATETIME, @p_APPROVE_DT_KT, 103),[MAKER_ID_KT] = @p_MAKER_ID_KT,[CHECKER_ID_KT] = @p_CHECKER_ID_KT,[REPORT_STATUS] = @p_REPORT_STATUS
|
|
76
|
WHERE USER_MASTER_ID= @p_USER_MASTER_ID
|
|
77
|
IF @@Error <> 0 GOTO ABORT
|
|
78
|
--Insert XmlData
|
|
79
|
FETCH NEXT FROM XmlData INTO @USE_MULTI_ID,@ASSET_ID,@BRANCH_ID,@DEPT_ID,@DIVISION_ID,@EMP_ID,@AMORT_START_DATE,@AMORT_MONTH,@AMORT_END_DATE,@CORE_NOTE,@NOTES
|
|
80
|
WHILE @@FETCH_STATUS = 0
|
|
81
|
BEGIN
|
|
82
|
SET @INDEX= @INDEX+1
|
|
83
|
-- --LUCTV - KIEM TRA SO THANG KHAU HAO PHAI NAM TRONG MIN -MAX
|
|
84
|
-- SET @NHOMTS =(SELECT GROUP_ID FROM ASS_MASTER WHERE ASSET_ID= @ASSET_ID)
|
|
85
|
-- SET @MIN_AMR =(SELECT AMORT_MONTH_MIN FROM ASS_GROUP WHERE GROUP_ID =@NHOMTS)
|
|
86
|
-- SET @MAX_AMR =(SELECT AMORT_MONTH_MAX FROM ASS_GROUP WHERE GROUP_ID=@NHOMTS)
|
|
87
|
-- IF(@AMORT_MONTH >@MAX_AMR OR @AMORT_MONTH <@MIN_AMR)
|
|
88
|
-- BEGIN
|
|
89
|
--ROLLBACK TRANSACTION
|
|
90
|
--CLOSE XmlData
|
|
91
|
--DEALLOCATE XmlData
|
|
92
|
--SELECT '-1' as Result,N'Dòng' +CONVERT(VARCHAR(15),@INDEX) +N': Số tháng khấu hao phải nằm trong khoảng ('+CONVERT(VARCHAR(15),@MIN_AMR)+' - '+CONVERT(VARCHAR(15),@MAX_AMR)+N') tháng' ErrorDesc
|
|
93
|
--RETURN '-1'
|
|
94
|
-- END
|
|
95
|
--END
|
|
96
|
if(@AMORT_START_DATE='') set @AMORT_START_DATE=NULL
|
|
97
|
if(@AMORT_END_DATE='') set @AMORT_END_DATE=NULL
|
|
98
|
DECLARE @ERRORSYS NVARCHAR(200) = '',@ASSET_CODE nVARCHAR(20)
|
|
99
|
IF ( EXISTS ( SELECT 1
|
|
100
|
FROM ASS_USE_MULTI_DT A
|
|
101
|
INNER JOIN ASS_USE_MULTI_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
|
|
102
|
WHERE [ASSET_ID] = @ASSET_ID AND A.USER_MASTER_ID<> @p_USER_MASTER_ID AND
|
|
103
|
(A.CHECKER_ID IS NULL OR A.CHECKER_ID_KT IS NULL OR (A.CHECKER_ID_KT IS NOT NULL AND A.CHECKER_ID_KT<>'system'))))
|
|
104
|
BEGIN
|
|
105
|
SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE [ASSET_ID] = @ASSET_ID)
|
|
106
|
SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang xuất sử dụng'
|
|
107
|
CLOSE XmlData
|
|
108
|
DEALLOCATE XmlData
|
|
109
|
ROLLBACK TRANSACTION
|
|
110
|
SELECT '-1' as Result, '' USER_MASTER_ID, @ERRORSYS ErrorDesc
|
|
111
|
RETURN '-1'
|
|
112
|
|
|
113
|
END
|
|
114
|
|
|
115
|
IF ( EXISTS ( SELECT * FROM ASS_USE WHERE [ASSET_ID] = @ASSET_ID))
|
|
116
|
BEGIN
|
|
117
|
SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE [ASSET_ID] = @ASSET_ID)
|
|
118
|
SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang xuất sử dụng'
|
|
119
|
CLOSE XmlData
|
|
120
|
DEALLOCATE XmlData
|
|
121
|
ROLLBACK TRANSACTION
|
|
122
|
SELECT '-1' as Result, '' USER_MASTER_ID, @ERRORSYS ErrorDesc
|
|
123
|
RETURN '-1'
|
|
124
|
|
|
125
|
END
|
|
126
|
|
|
127
|
-- luctv 03/08/2021 người nhận phải cùng đơn vị nhận
|
|
128
|
IF (SELECT BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEPT_ID) <> @BRANCH_ID
|
|
129
|
BEGIN
|
|
130
|
SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE [ASSET_ID] = @ASSET_ID)
|
|
131
|
SELECT '-1' Result, '' USER_MASTER_ID, N'TS: '+ @ASSET_CODE + N' Phòng bản nhận phải thuộc về đơn vị nhận.' ErrorDesc
|
|
132
|
CLOSE XmlData
|
|
133
|
DEALLOCATE XmlData
|
|
134
|
ROLLBACK TRANSACTION
|
|
135
|
RETURN '-1'
|
|
136
|
END
|
|
137
|
|
|
138
|
IF (SELECT DEP_ID FROM CM_EMPLOYEE WHERE EMP_ID = @EMP_ID) <> @DEPT_ID
|
|
139
|
BEGIN
|
|
140
|
SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE [ASSET_ID] = @ASSET_ID)
|
|
141
|
SELECT '-1' Result, '' TRANSFER_ID, N'TS: '+ @ASSET_CODE + N' Người nhận phải thuộc về phòng ban nhận.' ErrorDesc
|
|
142
|
CLOSE XmlData
|
|
143
|
DEALLOCATE XmlData
|
|
144
|
ROLLBACK TRANSACTION
|
|
145
|
RETURN '-1'
|
|
146
|
END
|
|
147
|
|
|
148
|
IF(LEN(@USE_MULTI_ID) = 0)
|
|
149
|
BEGIN
|
|
150
|
EXEC SYS_CodeMasters_Gen 'ASS_USE_MULTI_DT', @USE_MULTI_ID out
|
|
151
|
IF @USE_MULTI_ID='' OR @USE_MULTI_ID IS NULL GOTO ABORT
|
|
152
|
|
|
153
|
END
|
|
154
|
INSERT INTO ASS_USE_MULTI_DT([USE_MULTI_ID],[USER_MASTER_ID],[ASSET_ID],[AMORT_START_DATE],[BRANCH_ID],[DEPT_ID],[EMP_ID],[DIVISION_ID],[NOTES],
|
|
155
|
[CORE_NOTE],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[BRANCH_CREATE],[AMORT_MONTH],[AUTH_STATUS_KT],[REPORT_STATUS],[AMORT_END_DATE])
|
|
156
|
VALUES(@USE_MULTI_ID ,@p_USER_MASTER_ID,@ASSET_ID ,CONVERT(DATETIME, @AMORT_START_DATE, 103) ,@BRANCH_ID ,@DEPT_ID ,@EMP_ID ,@DIVISION_ID ,@NOTES ,@CORE_NOTE ,@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_BRANCH_CREATE ,@AMORT_MONTH ,
|
|
157
|
'U' ,'N' ,CONVERT(DATETIME, @AMORT_END_DATE, 103) )
|
|
158
|
IF @@Error <> 0 GOTO ABORT
|
|
159
|
|
|
160
|
FETCH NEXT FROM XmlData INTO @USE_MULTI_ID,@ASSET_ID,@BRANCH_ID,@DEPT_ID,@DIVISION_ID,@EMP_ID,@AMORT_START_DATE,@AMORT_MONTH,@AMORT_END_DATE,@CORE_NOTE,@NOTES
|
|
161
|
END
|
|
162
|
CLOSE XmlData
|
|
163
|
DEALLOCATE XmlData
|
|
164
|
|
|
165
|
-- GIANT 21/09/2021
|
|
166
|
INSERT INTO dbo.PL_PROCESS
|
|
167
|
(
|
|
168
|
REQ_ID,
|
|
169
|
PROCESS_ID,
|
|
170
|
CHECKER_ID,
|
|
171
|
APPROVE_DT,
|
|
172
|
PROCESS_DESC,NOTES
|
|
173
|
)
|
|
174
|
VALUES
|
|
175
|
( @p_USER_MASTER_ID, -- REQ_ID - varchar(15)
|
|
176
|
'UPDATE', -- PROCESS_ID - varchar(10)
|
|
177
|
@p_MAKER_ID, -- CHECKER_ID - varchar(15)
|
|
178
|
GETDATE(), -- APPROVE_DT - datetime
|
|
179
|
N'Cập nhật phiếu xuất sử dụng thành công' ,
|
|
180
|
N'Cập nhật phiếu xuất sử dụng' -- PROCESS_DESC - nvarchar(1000)
|
|
181
|
)
|
|
182
|
|
|
183
|
COMMIT TRANSACTION
|
|
184
|
SELECT '0' as Result, @p_USER_MASTER_ID USER_MASTER_ID, '' ErrorDesc
|
|
185
|
RETURN '0'
|
|
186
|
ABORT:
|
|
187
|
BEGIN
|
|
188
|
ROLLBACK TRANSACTION
|
|
189
|
SELECT '-1' as Result, '' USER_MASTER_ID, '' ErrorDesc
|
|
190
|
RETURN '-1'
|
|
191
|
End
|