|
1
|
|
|
2
|
-- Stored Procedure
|
|
3
|
|
|
4
|
/*
|
|
5
|
SELECT * FROM ASS_MASTER
|
|
6
|
SELECT * FROM ASS_MASTER WHERE A
|
|
7
|
SELECT * FROM CAR_ACCESSORY WHERE ASSET_ID ='ASS000000000003'
|
|
8
|
[CAR_MASTER_Ins] 'ASS000000000029','CT00003','','','','','','','','','','','','','',100,100,100,100,100,100,
|
|
9
|
'','','','','','','','','',
|
|
10
|
'
|
|
11
|
<Root xmlns="">
|
|
12
|
<CarAccessory>
|
|
13
|
<CAR_AC_ID></CAR_AC_ID>
|
|
14
|
<ASSET_ID>ASS000000000003</ASSET_ID>
|
|
15
|
<ASSET_NAME>XE BMW</ASSET_NAME>
|
|
16
|
<ACCESSORY_NAME>MAN HINH</ACCESSORY_NAME>
|
|
17
|
<QUANTITY>1</QUANTITY>
|
|
18
|
<NOTES>MOI MUA</NOTES>
|
|
19
|
</CarAccessory>
|
|
20
|
</Root>
|
|
21
|
',
|
|
22
|
'
|
|
23
|
<Root xmlns="">
|
|
24
|
<CarCureSch>
|
|
25
|
<CAR_CUR_ID></CAR_CUR_ID>
|
|
26
|
<ASSET_ID>ASS000000000003</ASSET_ID>
|
|
27
|
<ASSET_NAME>XE BMW </ASSET_NAME>
|
|
28
|
<INDEX_NUMBER>1</INDEX_NUMBER>
|
|
29
|
<CURE_DT>20/11/2013</CURE_DT>
|
|
30
|
<COUNT>1</COUNT>
|
|
31
|
<NOTES>MOI MUA</NOTES>
|
|
32
|
</CarCureSch>
|
|
33
|
</Root>
|
|
34
|
'
|
|
35
|
*/
|
|
36
|
|
|
37
|
|
|
38
|
ALTER PROCEDURE dbo.CAR_MASTER_Ins
|
|
39
|
@p_ASSET_ID varchar(15) = NULL,
|
|
40
|
@p_CAR_TYPE_ID varchar(15) = NULL,
|
|
41
|
@p_MODEL nvarchar(100) = NULL,
|
|
42
|
@p_N_PLATE varchar(20) = NULL,
|
|
43
|
@p_PROCOUNTRY varchar(15) = NULL,
|
|
44
|
@p_CAR_COLOR nvarchar(100) = NULL,
|
|
45
|
@p_MACHINES_ID nvarchar(100) = NULL,
|
|
46
|
@p_SLOPES_ID nvarchar(100) = NULL,
|
|
47
|
@p_MANUFACTURER varchar(15) = NULL,
|
|
48
|
@p_START_DT VARCHAR(20) = NULL,
|
|
49
|
@p_OWNER nvarchar(100) = NULL,
|
|
50
|
@p_ENGINE_TYPE nvarchar(100) = NULL,
|
|
51
|
@p_ENGINE_NUMBER varchar(15) = NULL,
|
|
52
|
@p_GEARBOX_TYPE varchar(15) = NULL,
|
|
53
|
@p_FUELS_TYPE varchar(15) = NULL,
|
|
54
|
@p_POWER_RATE decimal(18,2) = NULL,
|
|
55
|
@p_VOLUME decimal(18,2) = NULL,
|
|
56
|
@p_TIRE_SIZE varchar(100) = NULL,
|
|
57
|
@p_LENGTH decimal(18) = NULL,
|
|
58
|
@p_WIDTH decimal(18) = NULL,
|
|
59
|
@p_HEIGHT decimal(18) = NULL,
|
|
60
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
61
|
@p_EMPLOYEE_ID varchar(15) = NULL,
|
|
62
|
@p_PURPOSE varchar(50)=NULL,
|
|
63
|
@p_NOTES nvarchar(1000) = NULL,
|
|
64
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
65
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
66
|
@p_MAKER_ID varchar(15) = NULL,
|
|
67
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
68
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
69
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
70
|
@p_MANUFACTURE_YEAR VARCHAR(4) = NULL,
|
|
71
|
@p_IS_SPECIAL VARCHAR(1)=NULL,
|
|
72
|
@p_CAR_ACCESSORY XML = NULL,
|
|
73
|
@p_CAR_CURE_SCH XML = NULL,
|
|
74
|
@p_CAR_DRIVER XML = NULL,--doanptt them luoi danh sach tai xe
|
|
75
|
-- KHANGTH - 080620, BO SUNG COLMN NEXT_MAT_NUMBER
|
|
76
|
-- So km tiep theo
|
|
77
|
@p_NEXT_MAT_NUMBER DECIMAL(18,0) = NULL,
|
|
78
|
-- Nội dung bảo trì
|
|
79
|
@p_MAINT_NOTE NVARCHAR(1000) = NULL,
|
|
80
|
@p_EMP_NAME NVARCHAR(250) = NULL
|
|
81
|
--//-------------------------------------------------
|
|
82
|
AS
|
|
83
|
|
|
84
|
DECLARE @ERRORSYS VARCHAR(20)
|
|
85
|
DECLARE @l_BRANCH_ID VARCHAR(20)
|
|
86
|
--kiem tra ma tai san
|
|
87
|
IF( NOT EXISTS ( SELECT * FROM ASS_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID ) OR @p_ASSET_ID IS NULL OR @p_ASSET_ID ='')
|
|
88
|
BEGIN
|
|
89
|
SET @ERRORSYS = 'ASS-99999'
|
|
90
|
END
|
|
91
|
ELSE
|
|
92
|
BEGIN IF(EXISTS ( SELECT * FROM ASS_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID ) OR @p_ASSET_ID IS NULL OR @p_ASSET_ID ='' )
|
|
93
|
--LAY THONG TIN DON VI SU DUNG TAI SAN
|
|
94
|
SET @l_BRANCH_ID = (SELECT BRANCH_ID FROM ASS_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID)
|
|
95
|
END
|
|
96
|
--kiem tra ma tai san da cap phat cho xe chua
|
|
97
|
IF( EXISTS ( SELECT * FROM CAR_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID ) OR @p_ASSET_ID IS NULL OR @p_ASSET_ID ='')
|
|
98
|
BEGIN
|
|
99
|
SET @ERRORSYS = 'CAR-99997'
|
|
100
|
END
|
|
101
|
--kiem tra ma so xe co ton tai chua
|
|
102
|
IF( EXISTS ( SELECT * FROM CAR_MASTER A WHERE A.N_PLATE = @p_N_PLATE ) )
|
|
103
|
BEGIN
|
|
104
|
SET @ERRORSYS = 'CAR-99999'
|
|
105
|
END
|
|
106
|
|
|
107
|
|
|
108
|
IF( @ERRORSYS <> '' )
|
|
109
|
BEGIN
|
|
110
|
SELECT ErrorCode Result, '' ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
|
|
111
|
RETURN '0'
|
|
112
|
END
|
|
113
|
|
|
114
|
Declare @hdoc INT
|
|
115
|
Exec sp_xml_preparedocument @hdoc Output,@p_CAR_ACCESSORY
|
|
116
|
DECLARE CarAccessory CURSOR FOR
|
|
117
|
SELECT *
|
|
118
|
FROM OPENXML(@hDoc,'/Root/CarAccessory',2)
|
|
119
|
WITH
|
|
120
|
(
|
|
121
|
ASSET_ID varchar(15),
|
|
122
|
ASSET_NAME nvarchar(200),
|
|
123
|
DEVICE_CODE VARCHAR(15),
|
|
124
|
ACCESSORY_NAME nvarchar(1000) ,
|
|
125
|
UNIT_ID varchar(15) ,
|
|
126
|
QUANTITY int ,
|
|
127
|
NOTES nvarchar(1000)
|
|
128
|
)
|
|
129
|
OPEN CarAccessory
|
|
130
|
|
|
131
|
Exec sp_xml_preparedocument @hdoc Output,@p_CAR_CURE_SCH
|
|
132
|
DECLARE CarCureSch CURSOR FOR
|
|
133
|
SELECT *
|
|
134
|
FROM OPENXML(@hDoc,'/Root/CarCureSch',2)
|
|
135
|
WITH
|
|
136
|
(
|
|
137
|
ASSET_ID varchar(15),
|
|
138
|
ASSET_NAME nvarchar(200) ,
|
|
139
|
INDEX_NUMBER decimal(18) ,
|
|
140
|
CURE_DT VARCHAR(20) ,
|
|
141
|
[COUNT] int,
|
|
142
|
NOTES nvarchar(1000)
|
|
143
|
)
|
|
144
|
OPEN CarCureSch
|
|
145
|
|
|
146
|
Exec sp_xml_preparedocument @hdoc Output,@p_CAR_DRIVER --doanptt
|
|
147
|
DECLARE CarDriver CURSOR FOR
|
|
148
|
SELECT *
|
|
149
|
FROM OPENXML(@hDoc,'/Root/CarDriver',2)
|
|
150
|
WITH
|
|
151
|
(
|
|
152
|
CAR_DRIVER VARCHAR(15),
|
|
153
|
CAR_DRIVER_NAME Nvarchar(200),
|
|
154
|
FROM_DT varchar(20),
|
|
155
|
TO_DT varchar(20),
|
|
156
|
IS_USED varchar(1),
|
|
157
|
NOTES nvarchar(1000)
|
|
158
|
)
|
|
159
|
OPEN CarDriver
|
|
160
|
|
|
161
|
BEGIN TRANSACTION
|
|
162
|
DECLARE @l_CAR_ID VARCHAR(15)
|
|
163
|
EXEC SYS_CodeMasters_Gen 'CAR_MASTER', @l_CAR_ID out
|
|
164
|
IF @l_CAR_ID='' OR @l_CAR_ID IS NULL GOTO ABORT
|
|
165
|
-- TuNT 19/02/2020 them truong IS_LIQUID danh dau xe da bi thanh ly
|
|
166
|
-- KHANGTH 11/06/2020 them truong MAINT_NOTE lich bao tri
|
|
167
|
INSERT INTO CAR_MASTER([CAR_ID],[ASSET_ID],[CAR_TYPE_ID],[MODEL],[N_PLATE],[PROCOUNTRY],[CAR_COLOR],[MACHINES_ID],[SLOPES_ID],[MANUFACTURER],[START_DT],[OWNER],[ENGINE_TYPE],[ENGINE_NUMBER],[GEARBOX_TYPE],[FUELS_TYPE],[POWER_RATE],[VOLUME],[TIRE_SIZE],[LENGTH],[WIDTH],[HEIGHT],[BRANCH_ID],[EMPLOYEE_ID],[PURPOSE],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[MANUFACTURE_YEAR],[IS_SPECIAL], [IS_LIQUID],[NEXT_MAT_NUMBER],[MAINT_NOTE], [EMP_NAME])
|
|
168
|
VALUES(@l_CAR_ID ,@p_ASSET_ID ,@p_CAR_TYPE_ID ,@p_MODEL ,@p_N_PLATE ,@p_PROCOUNTRY ,@p_CAR_COLOR ,@p_MACHINES_ID ,@p_SLOPES_ID ,@p_MANUFACTURER ,CONVERT(DATETIME, @p_START_DT, 103) ,@p_OWNER ,@p_ENGINE_TYPE ,@p_ENGINE_NUMBER ,@p_GEARBOX_TYPE ,@p_FUELS_TYPE ,@p_POWER_RATE ,@p_VOLUME ,@p_TIRE_SIZE ,@p_LENGTH ,@p_WIDTH ,@p_HEIGHT ,@l_BRANCH_ID ,@p_EMPLOYEE_ID ,@p_PURPOSE,@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_MANUFACTURE_YEAR,@p_IS_SPECIAL, '0',@p_NEXT_MAT_NUMBER,@p_MAINT_NOTE, @p_EMP_NAME)
|
|
169
|
PRINT 'MASTER'
|
|
170
|
IF @@Error <> 0 GOTO ABORT
|
|
171
|
--KHAI BAO TOAN BO BIEN TRUOC KHI FETCH DATA
|
|
172
|
DECLARE
|
|
173
|
@ASSET_ID varchar(15) ,
|
|
174
|
@ASSET_NAME nvarchar(200),
|
|
175
|
@DEVICE_CODE VARCHAR(15),
|
|
176
|
@ACCESSORY_NAME nvarchar(1000) ,
|
|
177
|
@UNIT_ID varchar(15) ,
|
|
178
|
@QUANTITY INT,
|
|
179
|
@NOTES nvarchar(1000) ,
|
|
180
|
@CAR_ID varchar(15),
|
|
181
|
@INDEX_NUMBER decimal(18) ,
|
|
182
|
@CURE_DT VARCHAR(20),
|
|
183
|
@COUNT int,
|
|
184
|
-- declare driver doanptt
|
|
185
|
@CAR_DRIVER VARCHAR(15),
|
|
186
|
@CAR_DRIVER_NAME Nvarchar(200),
|
|
187
|
@FROM_DT varchar(20) ,
|
|
188
|
@TO_DT varchar(20),
|
|
189
|
@IS_USED varchar(1)
|
|
190
|
|
|
191
|
--INSERT ACCESSORY DETAIL
|
|
192
|
FETCH NEXT FROM CarAccessory INTO @ASSET_ID,@ASSET_NAME, @DEVICE_CODE,@ACCESSORY_NAME,@UNIT_ID,@QUANTITY,@NOTES
|
|
193
|
WHILE @@FETCH_STATUS = 0
|
|
194
|
BEGIN
|
|
195
|
DECLARE @l_CAR_ACC_ID VARCHAR(15)
|
|
196
|
EXEC SYS_CodeMasters_Gen 'CAR_ACCESSORY', @l_CAR_ACC_ID out
|
|
197
|
IF @l_CAR_ACC_ID='' OR @l_CAR_ACC_ID IS NULL GOTO ABORT
|
|
198
|
INSERT INTO CAR_ACCESSORY([CAR_ACC_ID],[CAR_ID],[ASSET_ID],[ASSET_NAME],[DEVICE_CODE], [ACCESSORY_NAME],[UNIT_ID],[QUANTITY],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])
|
|
199
|
VALUES(@l_CAR_ACC_ID ,@l_CAR_ID ,@ASSET_ID ,@ASSET_NAME ,@DEVICE_CODE, @ACCESSORY_NAME ,@UNIT_ID ,
|
|
200
|
CONVERT(INT,@QUANTITY) ,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )
|
|
201
|
IF @@ERROR <> '' GOTO ABORT
|
|
202
|
FETCH NEXT FROM CarAccessory INTO @ASSET_ID,@ASSET_NAME,@DEVICE_CODE, @ACCESSORY_NAME,@UNIT_ID,@QUANTITY,@NOTES
|
|
203
|
END
|
|
204
|
|
|
205
|
|
|
206
|
PRINT 'CAR ACCES'
|
|
207
|
--INSERT CAR CUR SCHEDULE
|
|
208
|
FETCH NEXT FROM CarCureSch INTO @ASSET_ID,@ASSET_NAME,@INDEX_NUMBER,@CURE_DT,@COUNT,@NOTES
|
|
209
|
WHILE @@FETCH_STATUS = 0
|
|
210
|
BEGIN
|
|
211
|
DECLARE @l_CAR_CU_SCH_ID VARCHAR(15)
|
|
212
|
EXEC SYS_CodeMasters_Gen 'CAR_CURE_SCH', @l_CAR_CU_SCH_ID out
|
|
213
|
IF @l_CAR_CU_SCH_ID='' OR @l_CAR_CU_SCH_ID IS NULL GOTO ABORT
|
|
214
|
INSERT INTO CAR_CURE_SCH([CAR_CU_SCH_ID],[CAR_ID],[ASSET_ID],[ASSET_NAME],[INDEX_NUMBER],[CURE_DT],[COUNT],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])
|
|
215
|
VALUES(@l_CAR_CU_SCH_ID ,@l_CAR_ID ,@ASSET_ID ,@ASSET_NAME ,@INDEX_NUMBER ,
|
|
216
|
CONVERT(DATETIME, @CURE_DT, 103) ,@COUNT,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )
|
|
217
|
IF @@ERROR <> '' GOTO ABORT
|
|
218
|
FETCH NEXT FROM CarCureSch INTO @ASSET_ID,@ASSET_NAME,@INDEX_NUMBER,@CURE_DT,@COUNT,@NOTES
|
|
219
|
END
|
|
220
|
|
|
221
|
--INSERT CAR DRIVER doanptt
|
|
222
|
FETCH NEXT FROM CarDriver INTO @CAR_DRIVER,@CAR_DRIVER_NAME,@FROM_DT,@TO_DT, @IS_USED, @NOTES
|
|
223
|
WHILE @@FETCH_STATUS = 0
|
|
224
|
BEGIN
|
|
225
|
DECLARE @l_CAR_DRIVER_ID VARCHAR(15)
|
|
226
|
EXEC SYS_CodeMasters_Gen 'CAR_DRIVER_DT', @l_CAR_DRIVER_ID out
|
|
227
|
IF @l_CAR_DRIVER_ID='' OR @l_CAR_DRIVER_ID IS NULL GOTO ABORT
|
|
228
|
INSERT INTO CAR_DRIVER_DT([CAR_DRIVER_ID],[CAR_ID],[CAR_DRIVER],[CAR_DRIVER_NAME],[FROM_DT],[TO_DT],[IS_USED],[NOTES],[RECORD_STATUS],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])
|
|
229
|
VALUES(@l_CAR_DRIVER_ID ,@l_CAR_ID ,@CAR_DRIVER ,@CAR_DRIVER_NAME ,CONVERT(DATETIME, @FROM_DT, 103) , CONVERT(DATETIME, @TO_DT, 103), @IS_USED,
|
|
230
|
@NOTES ,@p_RECORD_STATUS ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )
|
|
231
|
IF @@ERROR <> '' GOTO ABORT
|
|
232
|
FETCH NEXT FROM CarDriver INTO @CAR_DRIVER,@CAR_DRIVER_NAME,@FROM_DT,@TO_DT, @IS_USED, @NOTES
|
|
233
|
END
|
|
234
|
|
|
235
|
CLOSE CarAccessory
|
|
236
|
DEALLOCATE CarAccessory
|
|
237
|
CLOSE CarCureSch
|
|
238
|
DEALLOCATE CarCureSch
|
|
239
|
CLOSE CarDriver
|
|
240
|
DEALLOCATE CarDriver
|
|
241
|
COMMIT TRANSACTION
|
|
242
|
SELECT '0' as Result, @l_CAR_ID ID, '' ErrorDesc
|
|
243
|
RETURN '0'
|
|
244
|
ABORT:
|
|
245
|
BEGIN
|
|
246
|
CLOSE CarAccessory
|
|
247
|
DEALLOCATE CarAccessory
|
|
248
|
CLOSE CarCureSch
|
|
249
|
DEALLOCATE CarCureSch
|
|
250
|
CLOSE CarDriver
|
|
251
|
DEALLOCATE CarDriver
|
|
252
|
ROLLBACK TRANSACTION
|
|
253
|
SELECT '-1' as Result, '' ID, '' ErrorDesc
|
|
254
|
RETURN '-1'
|
|
255
|
End
|