|
1
|
UPDATE dbo.CM_ALLCODE SET CONTENT=N'Căn cứ Quyết định 166/2020/QĐ-HC ngày 21/5/2020 về việc Ban hành Quy định mua sắm hàng hóa dịch vụ; Căn cứ nhu cầu mua sắm thực tế' WHERE CDTYPE='TR' AND CDNAME='PL_BASED'
|
|
2
|
|
|
3
|
ALTER PROC [dbo].[TR_REQUEST_CAR_Upd]
|
|
4
|
@p_REQ_ID varchar(15) =NULL,
|
|
5
|
@p_REQ_CODE varchar(150) = NULL,
|
|
6
|
@p_EMP_ID varchar(15) = NULL,
|
|
7
|
@p_EMP_PHONE varchar(15) = NULL,
|
|
8
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
9
|
@p_BRANCH_CREATE varchar(15) = NULL,
|
|
10
|
@p_DEP_ID varchar(15) = NULL,
|
|
11
|
@p_DEP_CREATE varchar(15) = NULL,
|
|
12
|
@p_ORIGN nvarchar(100) = NULL,
|
|
13
|
@p_DESTINATION nvarchar(100) = NULL,
|
|
14
|
@p_IS_SEND_APPR varchar(15) = NULL,
|
|
15
|
@p_SEND_APPR_DT varchar(20) = NULL,
|
|
16
|
@p_SIGN_USER varchar(15) = NULL,
|
|
17
|
@p_SIGN_DT varchar(20) = NULL,
|
|
18
|
@p_FROM_DATE varchar(20) = NULL,
|
|
19
|
@p_REQ_DT varchar(20) = NULL,
|
|
20
|
@p_TO_DATE varchar(20) = NULL,
|
|
21
|
@p_NOTES nvarchar(MAX) = NULL,
|
|
22
|
@p_CAR_ID varchar(15) = NULL,
|
|
23
|
@p_DRIVER_ID varchar(15) = NULL,
|
|
24
|
@p_DRIVER_PHONE varchar(15) = NULL,
|
|
25
|
@p_QUANTITY_TRIP int = NULL,
|
|
26
|
@p_NOTES_TRIP nvarchar(MAX) = NULL,
|
|
27
|
@p_UNIT_TRIP varchar(5) = NULL,
|
|
28
|
@p_TO_TRIP nvarchar(100) = NULL,
|
|
29
|
@p_FROM_TRIP nvarchar(100) = NULL,
|
|
30
|
@p_MILRAGE_TRIP int = NULL,
|
|
31
|
@p_START_TIME_TRIP varchar(20) = NULL,
|
|
32
|
@p_QUANTITY_RETURN_TRIP int = NULL,
|
|
33
|
@p_NOTES_RETURN_TRIP varchar(MAX) = NULL,
|
|
34
|
@p_UNIT_RETURN_TRIP varchar(5) = NULL,
|
|
35
|
@p_TO_RETURN_TRIP nvarchar(100) = NULL,
|
|
36
|
@p_FROM_RETURN_TRIP nvarchar(100) = NULL,
|
|
37
|
@p_MILRAGE_RETURN_TRIP int = NULL,
|
|
38
|
@p_START_TIME_RETURN_TRIP varchar(20) = NULL,
|
|
39
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
40
|
@p_APPROVE_DT varchar(20) = NULL,
|
|
41
|
@p_CREATE_DT varchar(20) = NULL,
|
|
42
|
@p_MAKER_ID varchar(20) = NULL,
|
|
43
|
@p_ListCarDT xml,
|
|
44
|
@p_USER_LOGIN varchar(20) = NULL,
|
|
45
|
@p_USER_UPDATE varchar(20) = NULL,
|
|
46
|
@p_N_PLATE varchar(20) = NULL,
|
|
47
|
@p_MO_NAME varchar(20) = NULL,
|
|
48
|
@p_LAST_BALANCE decimal(18,2)= NULL,
|
|
49
|
@p_FIRST_BALANCE decimal(18,2)= NULL,
|
|
50
|
@p_CURRENT_POWER_RATE decimal(18,2)= NULL,
|
|
51
|
@p_POWER_RATE decimal(18,2)= NULL,
|
|
52
|
@p_ListFuel xml,
|
|
53
|
@p_IS_CONTRACT_CAR varchar(1) = NULL,
|
|
54
|
@p_OLD_FUEL_NUMBER DECIMAL(18,2) = NULL
|
|
55
|
|
|
56
|
AS
|
|
57
|
|
|
58
|
BEGIN TRANSACTION
|
|
59
|
DECLARE @p_PROCESS_ID VARCHAR(20)
|
|
60
|
SELECT @p_PROCESS_ID=PROCESS_ID FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID
|
|
61
|
IF NOT EXISTS(SELECT * FROM TR_REQUEST_CAR WHERE REQ_ID <> @p_REQ_ID)
|
|
62
|
BEGIN
|
|
63
|
SELECT '0' Result, '' REQ_ID, N'Mã phiếu yêu cầu xe chưa tồn tại trong hệ thống' ErrorDesc
|
|
64
|
RETURN '0'
|
|
65
|
END
|
|
66
|
DECLARE @IS_SEND_APPR VARCHAR(1),@AUTH_STATUS VARCHAR(1)
|
|
67
|
IF(EXISTS(SELECT * FROM TR_REQUEST_CAR WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID IN ('REJECT','INSERT')))
|
|
68
|
BEGIN
|
|
69
|
UPDATE TR_REQUEST_CAR
|
|
70
|
SET EMP_ID=@p_EMP_ID,EMP_PHONE=@p_EMP_PHONE,BRANCH_ID=@p_BRANCH_ID,DEP_ID=@p_DEP_ID,ORIGN=@p_ORIGN,DESTINATION=@p_DESTINATION,AUTH_STATUS='U',REQ_DT=CONVERT(DATETIME,@p_REQ_DT,104),IS_SEND_APPR='N',FROM_DATE=CONVERT(DATETIME,@p_FROM_DATE,104),TO_DATE=CONVERT(DATETIME,@p_TO_DATE,104),NOTES=@p_NOTES,
|
|
71
|
QUANTITY_TRIP=@p_QUANTITY_TRIP,NOTES_TRIP=@p_NOTES_TRIP,UNIT_TRIP=@p_UNIT_TRIP,TO_TRIP=@p_TO_TRIP,FROM_TRIP=@p_FROM_TRIP,START_TIME_TRIP=CONVERT(DATETIME,@p_START_TIME_TRIP, 104)
|
|
72
|
WHERE REQ_ID =@p_REQ_ID
|
|
73
|
SET @IS_SEND_APPR='N'
|
|
74
|
SET @AUTH_STATUS='U'
|
|
75
|
DELETE PL_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='UPDATE'
|
|
76
|
INSERT INTO dbo.PL_PROCESS
|
|
77
|
(
|
|
78
|
REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
|
|
79
|
)
|
|
80
|
VALUES
|
|
81
|
(
|
|
82
|
@p_REQ_ID,'UPDATE',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),104),N'Cập nhật PYC xe thành công',N'Cập nhật PYC xe'
|
|
83
|
)
|
|
84
|
END
|
|
85
|
ELSE IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN)OR EXISTS(SELECT PROCESS_ID FROM TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND USER_UPDATE=@p_USER_LOGIN))
|
|
86
|
BEGIN
|
|
87
|
-- update master
|
|
88
|
UPDATE dbo.TR_REQUEST_CAR
|
|
89
|
SET CAR_ID=@p_CAR_ID,DRIVER_ID=@p_DRIVER_ID,DRIVER_PHONE=@p_DRIVER_PHONE,QUANTITY_TRIP=@p_QUANTITY_TRIP,NOTES_TRIP=@p_NOTES_TRIP,UNIT_TRIP=@p_UNIT_TRIP,TO_TRIP=@p_TO_TRIP,FROM_TRIP=@p_FROM_TRIP,MILRAGE_TRIP=@p_MILRAGE_TRIP,START_TIME_TRIP=CONVERT(DATETIME, @p_START_TIME_TRIP, 104),MO_NAME=@p_MO_NAME,N_PLATE=@p_N_PLATE,
|
|
90
|
QUANTITY_RETURN_TRIP=@p_QUANTITY_RETURN_TRIP,NOTES_RETURN_TRIP=@p_NOTES_RETURN_TRIP,UNIT_RETURN_TRIP=@p_UNIT_RETURN_TRIP,TO_RETURN_TRIP=@p_TO_RETURN_TRIP,FROM_RETURN_TRIP=@p_FROM_RETURN_TRIP,MILRAGE_RETURN_TRIP=@p_MILRAGE_RETURN_TRIP,START_TIME_RETURN_TRIP=CONVERT(DATETIME, @p_START_TIME_RETURN_TRIP, 104),
|
|
91
|
IS_CONTRACT_CAR=@p_IS_CONTRACT_CAR,AUTH_STATUS='U',POWER_RATE=@p_POWER_RATE,FIRST_BALANCE=@p_FIRST_BALANCE,LAST_BALANCE=@p_LAST_BALANCE,CURRENT_POWER_RATE=@p_CURRENT_POWER_RATE,USER_UPDATE = @p_USER_UPDATE,OLD_FUEL_NUMBER=@p_OLD_FUEL_NUMBER
|
|
92
|
WHERE REQ_ID=@p_REQ_ID
|
|
93
|
IF @@Error <> 0 GOTO ABORT
|
|
94
|
DELETE FROM dbo.TR_REQUEST_CAR_DETAIL WHERE REQ_ID = @p_REQ_ID
|
|
95
|
DELETE FROM dbo.TR_REQ_CAR_FUEL WHERE REQ_ID = @p_REQ_ID
|
|
96
|
-- LƯỚI CẬP NHẬT CHI PHÍ
|
|
97
|
DECLARE @lstCDT TABLE(
|
|
98
|
REQ_DT_ID VARCHAR(15),
|
|
99
|
REQ_ID VARCHAR(15),
|
|
100
|
FEE_TYPE NVARCHAR(100),
|
|
101
|
AMOUNT decimal(18, 0),
|
|
102
|
QUANTITY decimal(18, 2),
|
|
103
|
NOTES NVARCHAR(MAX),
|
|
104
|
UNIT NVARCHAR(20)
|
|
105
|
)
|
|
106
|
|
|
107
|
DECLARE @doc INT
|
|
108
|
Exec sp_xml_preparedocument @doc Output,@p_ListCarDT
|
|
109
|
|
|
110
|
INSERT INTO @lstCDT
|
|
111
|
SELECT *
|
|
112
|
FROM OPENXML(@doc,'/Root/ListCarDetail',2)
|
|
113
|
WITH
|
|
114
|
(
|
|
115
|
REQ_DT_ID VARCHAR(15),
|
|
116
|
REQ_ID VARCHAR(15),
|
|
117
|
FEE_TYPE NVARCHAR(100),
|
|
118
|
AMOUNT decimal(18, 0),
|
|
119
|
QUANTITY decimal(18, 2),
|
|
120
|
NOTES NVARCHAR(MAX),
|
|
121
|
UNIT NVARCHAR(20)
|
|
122
|
)
|
|
123
|
|
|
124
|
|
|
125
|
DECLARE CarDTCur CURSOR FOR SELECT REQ_DT_ID,REQ_ID,QUANTITY,FEE_TYPE,AMOUNT,NOTES FROM @lstCDT
|
|
126
|
OPEN CarDTCur
|
|
127
|
|
|
128
|
DECLARE
|
|
129
|
@pl_REQ_DT_ID VARCHAR(15),
|
|
130
|
@pl_REQ_ID VARCHAR(15),
|
|
131
|
@pl_FEE_TYPE NVARCHAR(100),
|
|
132
|
@pl_QUANTITY int,
|
|
133
|
@pl_AMOUNT decimal(18, 0),
|
|
134
|
@pl_NOTES NVARCHAR(MAX),
|
|
135
|
@pl_UNIT NVARCHAR(20)
|
|
136
|
|
|
137
|
FETCH NEXT FROM CarDTCur INTO @pl_REQ_DT_ID,@pl_REQ_ID,@pl_QUANTITY,@pl_FEE_TYPE,@pl_AMOUNT,@pl_NOTES,@pl_UNIT
|
|
138
|
|
|
139
|
WHILE @@FETCH_STATUS = 0
|
|
140
|
BEGIN
|
|
141
|
|
|
142
|
DECLARE @l_CarDT_ID VARCHAR(15)
|
|
143
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_CAR_DETAIL', @l_CarDT_ID out
|
|
144
|
IF @l_CarDT_ID='' OR @l_CarDT_ID IS NULL GOTO ABORT
|
|
145
|
|
|
146
|
INSERT INTO dbo.TR_REQUEST_CAR_DETAIL
|
|
147
|
(
|
|
148
|
REQ_DT_ID,REQ_ID,QUANTITY,FEE_TYPE,AMOUNT,NOTES,UNIT
|
|
149
|
)
|
|
150
|
VALUES
|
|
151
|
(
|
|
152
|
@l_CarDT_ID,@p_REQ_ID,@pl_QUANTITY,@pl_FEE_TYPE,@pl_AMOUNT,@pl_NOTES,@pl_UNIT
|
|
153
|
)
|
|
154
|
|
|
155
|
IF @@ERROR <> 0 GOTO ABORT1
|
|
156
|
FETCH NEXT FROM CarDTCur INTO @pl_REQ_DT_ID,@pl_REQ_ID,@pl_QUANTITY,@pl_FEE_TYPE,@pl_AMOUNT,@pl_NOTES,@pl_UNIT
|
|
157
|
END
|
|
158
|
CLOSE CarDTCur
|
|
159
|
DEALLOCATE CarDTCur
|
|
160
|
--lưới nhiên liệu
|
|
161
|
DECLARE @lstNL TABLE(
|
|
162
|
[FUEL_ID] VARCHAR(20),
|
|
163
|
[REQ_ID] VARCHAR(20),
|
|
164
|
[REFUEL_DATE] VARCHAR(20),
|
|
165
|
[NUMBER_LITERS] decimal(18, 2),
|
|
166
|
[NOTES] NVARCHAR(MAX),
|
|
167
|
[AMOUNT] DECIMAL(18,0)
|
|
168
|
)
|
|
169
|
|
|
170
|
Exec sp_xml_preparedocument @doc Output,@p_ListFuel
|
|
171
|
|
|
172
|
INSERT INTO @lstNL
|
|
173
|
SELECT *
|
|
174
|
FROM OPENXML(@doc,'/Root/ListFuel',2)
|
|
175
|
WITH
|
|
176
|
(
|
|
177
|
FUEL_ID VARCHAR(20),
|
|
178
|
REQ_ID VARCHAR(20),
|
|
179
|
REFUEL_DATE VARCHAR(20),
|
|
180
|
NUMBER_LITERS decimal(18, 2),
|
|
181
|
NOTES NVARCHAR(MAX),
|
|
182
|
AMOUNT DECIMAL(18,0)
|
|
183
|
)
|
|
184
|
|
|
185
|
DECLARE NLCur CURSOR FOR SELECT REFUEL_DATE,NUMBER_LITERS,[NOTES],AMOUNT FROM @lstNL
|
|
186
|
OPEN NLCur
|
|
187
|
|
|
188
|
DECLARE
|
|
189
|
@pl_REFUEL_DATE VARCHAR(20),
|
|
190
|
@pl_NUMBER_LITERS INT
|
|
191
|
|
|
192
|
FETCH NEXT FROM NLCur INTO @pl_REFUEL_DATE,@pl_NUMBER_LITERS,@pl_NOTES,@pl_AMOUNT
|
|
193
|
|
|
194
|
WHILE @@FETCH_STATUS = 0
|
|
195
|
BEGIN
|
|
196
|
|
|
197
|
DECLARE @l_FUEL_ID VARCHAR(20)
|
|
198
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_CAR_FUEL', @l_FUEL_ID out
|
|
199
|
IF @l_FUEL_ID='' OR @l_FUEL_ID IS NULL GOTO ABORT
|
|
200
|
|
|
201
|
INSERT INTO dbo.TR_REQ_CAR_FUEL
|
|
202
|
(
|
|
203
|
[FUEL_ID],[REFUEL_DATE],[NUMBER_LITERS],[REQ_ID],[NOTES],[AMOUNT]
|
|
204
|
)
|
|
205
|
VALUES
|
|
206
|
(
|
|
207
|
@l_FUEL_ID,CONVERT(DATETIME,@pl_REFUEL_DATE,103),@pl_NUMBER_LITERS,@p_REQ_ID,@pl_NOTES,@pl_AMOUNT
|
|
208
|
)
|
|
209
|
|
|
210
|
IF @@ERROR <> 0 GOTO ABORT1
|
|
211
|
FETCH NEXT FROM NLCur INTO @pl_REFUEL_DATE,@pl_NUMBER_LITERS,@pl_NOTES,@pl_AMOUNT
|
|
212
|
END
|
|
213
|
CLOSE NLCur
|
|
214
|
DEALLOCATE NLCur
|
|
215
|
IF(NOT EXISTS(SELECT USER_UPDATE FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND USER_UPDATE=@p_USER_LOGIN))
|
|
216
|
BEGIN
|
|
217
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TLNAME=@p_USER_UPDATE WHERE TYPE_JOB='XL' AND REQ_ID=@p_REQ_ID
|
|
218
|
END
|
|
219
|
IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN) AND
|
|
220
|
NOT EXISTS(SELECT * FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID IN ('CVINFO_UPD','USER_SEND','CVCOSTUPD','COSTREJECT','CVREJUPD')))
|
|
221
|
BEGIN
|
|
222
|
UPDATE TR_REQUEST_CAR
|
|
223
|
SET PROCESS_ID='CV_CAR_UPD'
|
|
224
|
WHERE REQ_ID=@p_REQ_ID
|
|
225
|
DELETE FROM PL_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID ='CV_CAR_UPD'
|
|
226
|
INSERT INTO dbo.PL_PROCESS
|
|
227
|
(
|
|
228
|
REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
|
|
229
|
)
|
|
230
|
VALUES
|
|
231
|
(
|
|
232
|
@p_REQ_ID,'CV_CAR_UPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),104),N'Chuyên viên điều động xe cập nhật thông tin xe thành công',N'Chuyên viên điều động xe cập nhật thông tin xe'
|
|
233
|
)
|
|
234
|
END
|
|
235
|
ELSE IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND USER_UPDATE=@p_USER_LOGIN AND PROCESS_ID IN('CV_SEND','USER_UPD','MAKER_REJ','MAKER_CONF','CV_REJECT')))
|
|
236
|
BEGIN
|
|
237
|
UPDATE TR_REQUEST_CAR
|
|
238
|
SET PROCESS_ID='USER_UPD'
|
|
239
|
WHERE REQ_ID=@p_REQ_ID
|
|
240
|
DELETE FROM PL_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID ='USER_UPD'
|
|
241
|
INSERT INTO dbo.PL_PROCESS
|
|
242
|
(
|
|
243
|
REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
|
|
244
|
)
|
|
245
|
VALUES
|
|
246
|
(
|
|
247
|
@p_REQ_ID,'USER_UPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),103),N'Tài xế cập nhật thông tin xe thành công',N'Tài xế cập nhật thông tin xe'
|
|
248
|
)
|
|
249
|
END
|
|
250
|
ELSE IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN) AND @p_PROCESS_ID ='USER_SEND')
|
|
251
|
BEGIN
|
|
252
|
UPDATE TR_REQUEST_CAR
|
|
253
|
SET PROCESS_ID='CVCOSTUPD'
|
|
254
|
WHERE REQ_ID=@p_REQ_ID
|
|
255
|
INSERT INTO dbo.PL_PROCESS
|
|
256
|
(
|
|
257
|
REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
|
|
258
|
)
|
|
259
|
VALUES
|
|
260
|
(
|
|
261
|
@p_REQ_ID,'CV_COSTUPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),103),N'Chuyên viên điều động xe cập nhật chi phí thành công',N'Chuyên viên điều động xe cập nhật chi phí'
|
|
262
|
)
|
|
263
|
END
|
|
264
|
ELSE IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN) AND @p_PROCESS_ID IN ('COSTREJECT'))
|
|
265
|
BEGIN
|
|
266
|
UPDATE TR_REQUEST_CAR
|
|
267
|
SET PROCESS_ID='CVREJUPD'
|
|
268
|
WHERE REQ_ID=@p_REQ_ID
|
|
269
|
INSERT INTO dbo.PL_PROCESS
|
|
270
|
(
|
|
271
|
REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
|
|
272
|
)
|
|
273
|
VALUES
|
|
274
|
(
|
|
275
|
@p_REQ_ID,'CVREJUPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),103),N'Chuyên viên điều động xe cập nhật thông tin thành công',N'Chuyên viên điều động xe cập nhật thông tin'
|
|
276
|
)
|
|
277
|
END
|
|
278
|
END
|
|
279
|
|
|
280
|
COMMIT TRANSACTION
|
|
281
|
SELECT '0' as Result, @p_REQ_ID REQ_ID,@IS_SEND_APPR AS IS_SEND_APPR,@AUTH_STATUS AS AUTH_STATUS, '' ErrorDesc
|
|
282
|
RETURN '0'
|
|
283
|
ABORT:
|
|
284
|
BEGIN
|
|
285
|
ROLLBACK TRANSACTION
|
|
286
|
SELECT '-1' as Result, '' REQ_ID, '' ErrorDesc
|
|
287
|
RETURN '-1'
|
|
288
|
END
|
|
289
|
ABORT1:
|
|
290
|
BEGIN
|
|
291
|
CLOSE CarDTCur
|
|
292
|
DEALLOCATE CarDTCur
|
|
293
|
ROLLBACK TRANSACTION
|
|
294
|
SELECT '-1' AS RESULT
|
|
295
|
RETURN '-1'
|
|
296
|
End
|