|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[CON_REQUEST_DOC_InsXml]
|
|
3
|
@p_CONSTRUCT_CODE varchar(15) = NULL,
|
|
4
|
@p_CONSTRUCT_NAME nvarchar(100) = NULL,
|
|
5
|
@p_PLAN_ID varchar(15) = NULL,
|
|
6
|
@p_CONST_ID varchar(15) = NULL,
|
|
7
|
@p_DIVI_ID varchar(15) = NULL,
|
|
8
|
@p_CONSTRUCT_ADDR nvarchar(200) = NULL,
|
|
9
|
@p_LENGTH decimal(18, 2) = NULL,
|
|
10
|
@p_WIDTH decimal(18, 2) = NULL,
|
|
11
|
@p_CONSTRUCT_AREA decimal(18) = NULL,
|
|
12
|
@p_FLOORS int = NULL,
|
|
13
|
@p_FLOORS_AREA decimal(18, 2) = NULL,
|
|
14
|
@p_TOTAL_AREA_USE decimal(18, 2) = NULL, -- GIANT
|
|
15
|
@p_UNUSED_AREA decimal(18, 2) = NULL,
|
|
16
|
@p_TOTAL_COST decimal(18, 2) = NULL,-- GIANT
|
|
17
|
@p_UNIT_PRICE decimal(18, 2) = NULL,-- GIANT
|
|
18
|
@p_YEAR_EXE varchar(4) = NULL,
|
|
19
|
@p_CONST_TYPE varchar(15) = NULL,
|
|
20
|
@p_HQ_TYPE varchar(15) = NULL,
|
|
21
|
@p_TOTAL_AMT decimal(18) = NULL,
|
|
22
|
@p_COST_ESTIMATE decimal(18) = NULL,
|
|
23
|
@p_COST_EXE decimal(18) = NULL,
|
|
24
|
@p_COST_INCURRED decimal(18) = NULL,
|
|
25
|
@p_CONST_PURPOSE varchar(15) = NULL,
|
|
26
|
@p_ENGINEER nvarchar(500) = NULL,
|
|
27
|
@p_BRANCH_NAME_ETX nvarchar(500) = NULL,
|
|
28
|
@p_DESCRIPTION nvarchar(2000) = NULL,
|
|
29
|
@p_START_DT VARCHAR(20) = NULL,
|
|
30
|
@p_END_DT VARCHAR(20) = NULL,
|
|
31
|
@p_DATE_EXE VARCHAR(20) = NULL,
|
|
32
|
@p_COMPLETION_DT VARCHAR(20) = NULL,
|
|
33
|
@p_CONSTRUCT_PROGRESS decimal(18) = NULL,
|
|
34
|
@p_RECORD_STATUS varchar(1) = '1',
|
|
35
|
@p_MAKER_ID varchar(15) = NULL,
|
|
36
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
37
|
@p_AUTH_STATUS varchar(1) = 'N',
|
|
38
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
39
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
40
|
@p_REQUEST_ID VARCHAR(15) = NULL,
|
|
41
|
@p_BRANCH_ID VARCHAR(15) = NULL,
|
|
42
|
@p_STREET NVARCHAR(200) = NULL,
|
|
43
|
@p_LOCATION VARCHAR(15) = NULL,
|
|
44
|
@p_SCALE NVARCHAR(1000) = NULL,
|
|
45
|
@p_UPD_DT varchar(20) = NULL,
|
|
46
|
@p_APPROVE_VALUE decimal(18) = NULL,
|
|
47
|
@P_ListRequestDoc XML = NULL, -- THONG TIN VE TO TRINH
|
|
48
|
@P_ListBid XML = NULL, -- CHI PHI SUA CHUA
|
|
49
|
@P_ListContract_DT XML = NULL, -- DANH SACH HO SO THI CONG
|
|
50
|
@P_ListPayment XML = NULL, -- CAC DOT THANH TOAN
|
|
51
|
@P_ListBid_DT XML = NULL, -- DANH SACH CHI TIET DON VI THAM GIA THAU
|
|
52
|
@P_ListContractorArise XML = NULL, -- DANH SACH CHI TIET PHAT SINH TANG GIAM
|
|
53
|
@P_ListReviewXML XML = NULL, -- ĐÁNH GIÁ NCC
|
|
54
|
@P_ListConsProcessXML XML = NULL, -- TIẾN ĐỘ CÔNG TRÌNH
|
|
55
|
@P_ListConsHHXML XML = NULL -- DANH SÁCH HÀNG HÓA
|
|
56
|
AS
|
|
57
|
|
|
58
|
DECLARE @sErrorCode VARCHAR(20),@DEP_CREATE VARCHAR(15) = NULL
|
|
59
|
SELECT @DEP_CREATE = DEP_ID FROM TL_USER WHERE TLNANME = @p_MAKER_ID
|
|
60
|
|
|
61
|
Declare @hdoc INT
|
|
62
|
|
|
63
|
-- THONG TIN VE TO TRINH
|
|
64
|
Exec sp_xml_preparedocument @hdoc Output,@P_ListRequestDoc
|
|
65
|
|
|
66
|
DECLARE @TABLE_REQ_DOC TABLE (
|
|
67
|
CON_REQUEST_DOC_ID varchar(15),
|
|
68
|
REQ_ID varchar(15),
|
|
69
|
REQ_CODE nvarchar(100),
|
|
70
|
REQ_NAME nvarchar(200),
|
|
71
|
REQ_DT VARCHAR(20),
|
|
72
|
REQ_CONTENT nvarchar(1000),
|
|
73
|
CONSTRUCT_ID varchar(15),
|
|
74
|
TOTAL_AMT decimal(18),
|
|
75
|
BRANCH_ID varchar(15),
|
|
76
|
PLAN_TYPE VARCHAR(1),
|
|
77
|
IS_PARENT VARCHAR(1),
|
|
78
|
IS_ARISE VARCHAR(1),
|
|
79
|
BUDGET varchar(15),
|
|
80
|
YEAR_BUDGET int,
|
|
81
|
REQUEST_PARENT VARCHAR(15),
|
|
82
|
APPROVE_DT VARCHAR(20)
|
|
83
|
)
|
|
84
|
INSERT INTO @TABLE_REQ_DOC SELECT *
|
|
85
|
FROM OPENXML(@hDoc,'/Root/ListRequestDoc',2)
|
|
86
|
WITH
|
|
87
|
(
|
|
88
|
CON_REQUEST_DOC_ID varchar(15),
|
|
89
|
REQ_ID varchar(15),
|
|
90
|
REQ_CODE nvarchar(100),
|
|
91
|
REQ_NAME nvarchar(200),
|
|
92
|
REQ_DT VARCHAR(20),
|
|
93
|
REQ_CONTENT nvarchar(1000),
|
|
94
|
CONSTRUCT_ID varchar(15),
|
|
95
|
TOTAL_AMT decimal(18),
|
|
96
|
BRANCH_ID varchar(15),
|
|
97
|
PLAN_TYPE VARCHAR(1),
|
|
98
|
IS_PARENT VARCHAR(1),
|
|
99
|
IS_ARISE VARCHAR(1),
|
|
100
|
BUDGET varchar(15),
|
|
101
|
YEAR_BUDGET int,
|
|
102
|
REQUEST_PARENT VARCHAR(15),
|
|
103
|
APPROVE_DT VARCHAR(20)
|
|
104
|
|
|
105
|
)
|
|
106
|
|
|
107
|
|
|
108
|
DECLARE ListRequestDoc CURSOR LOCAL FOR
|
|
109
|
SELECT *
|
|
110
|
FROM OPENXML(@hDoc,'/Root/ListRequestDoc',2)
|
|
111
|
WITH
|
|
112
|
(
|
|
113
|
CON_REQUEST_DOC_ID varchar(15),
|
|
114
|
REQ_ID varchar(15),
|
|
115
|
REQ_CODE nvarchar(100),
|
|
116
|
REQ_NAME nvarchar(200),
|
|
117
|
REQ_DT VARCHAR(20),
|
|
118
|
REQ_CONTENT nvarchar(1000),
|
|
119
|
CONSTRUCT_ID varchar(15),
|
|
120
|
TOTAL_AMT decimal(18),
|
|
121
|
BRANCH_ID varchar(15),
|
|
122
|
PLAN_TYPE VARCHAR(1),
|
|
123
|
IS_PARENT VARCHAR(1),
|
|
124
|
IS_ARISE VARCHAR(1),
|
|
125
|
BUDGET varchar(15),
|
|
126
|
YEAR_BUDGET int,
|
|
127
|
REQUEST_PARENT VARCHAR(15),
|
|
128
|
APPROVE_DT VARCHAR(20)
|
|
129
|
|
|
130
|
)
|
|
131
|
OPEN ListRequestDoc
|
|
132
|
|
|
133
|
-- DANH SACH HO SO THI CONG (HOP DONG)
|
|
134
|
Exec sp_xml_preparedocument @hdoc Output,@P_ListContract_DT
|
|
135
|
DECLARE ListContract_DT CURSOR LOCAL FOR
|
|
136
|
SELECT *
|
|
137
|
FROM OPENXML(@hDoc,'/Root/ListContract_DT',2)
|
|
138
|
WITH
|
|
139
|
(
|
|
140
|
CONTRACT_ID varchar(15),
|
|
141
|
CONTRACT_CODE varchar(15),
|
|
142
|
[CONTRACT_NAME] nvarchar(200),
|
|
143
|
CONTRACT_TYPE varchar(15),
|
|
144
|
BID_ID nvarchar(200),
|
|
145
|
TOTAL_AMT DECIMAL(18),
|
|
146
|
IS_PARENT varchar(1),
|
|
147
|
REQUEST_ID varchar(15),
|
|
148
|
REQDT_ID VARCHAR(15)
|
|
149
|
)
|
|
150
|
OPEN ListContract_DT
|
|
151
|
|
|
152
|
-- CHI TIET PHAT SINH TANG GIAM
|
|
153
|
|
|
154
|
Exec sp_xml_preparedocument @hdoc Output,@P_ListContractorArise
|
|
155
|
DECLARE ListContractorArise CURSOR LOCAL FOR
|
|
156
|
SELECT *
|
|
157
|
FROM OPENXML(@hDoc,'/Root/ListContractorArise',2)
|
|
158
|
WITH
|
|
159
|
(
|
|
160
|
ARISE_ID VARCHAR(15),
|
|
161
|
BID_ID varchar(15),
|
|
162
|
BID_CODE varchar(15),
|
|
163
|
SUPPLIER nvarchar(200),
|
|
164
|
IS_BID_WIN varchar(1),
|
|
165
|
BEGIN_VALUE decimal(18,2),
|
|
166
|
CHECKED_VALUE decimal(18,2)
|
|
167
|
)
|
|
168
|
OPEN ListContractorArise
|
|
169
|
|
|
170
|
-- DANH SÁCH HÀNG HÓA
|
|
171
|
Exec sp_xml_preparedocument @hdoc Output,@P_ListConsHHXML
|
|
172
|
DECLARE ListHH CURSOR LOCAL FOR
|
|
173
|
SELECT *
|
|
174
|
FROM OPENXML(@hDoc,'/Root/ListHH',2)
|
|
175
|
WITH
|
|
176
|
(
|
|
177
|
CON_REQ_HH_ID VARCHAR(15),
|
|
178
|
REQ_CODE VARCHAR(50),
|
|
179
|
REQ_ID VARCHAR(15),
|
|
180
|
SUP_ID VARCHAR(15),
|
|
181
|
SELLTEMENT_AMT DECIMAL(18,2),
|
|
182
|
[DESCRIPTION] NVARCHAR(4000), -- KHOONG SU DUNG
|
|
183
|
HH_ID VARCHAR(15),
|
|
184
|
ESTIMATES_AMT DECIMAL(18,2),
|
|
185
|
BRANCH_DO VARCHAR(15),
|
|
186
|
CONS_DOCUMENT_ID VARCHAR(15),
|
|
187
|
BID_ID VARCHAR(15),
|
|
188
|
BID_CODE VARCHAR(50),
|
|
189
|
REQDT_ID VARCHAR(15)
|
|
190
|
)
|
|
191
|
OPEN ListHH
|
|
192
|
-- ĐÁNH GIÁ NCC
|
|
193
|
Exec sp_xml_preparedocument @hdoc Output,@P_ListReviewXML
|
|
194
|
DECLARE ListReview CURSOR LOCAL FOR
|
|
195
|
SELECT *
|
|
196
|
FROM OPENXML(@hDoc,'/Root/ListReview',2)
|
|
197
|
WITH
|
|
198
|
(
|
|
199
|
CON_REQ_REVIEW_ID VARCHAR(15),
|
|
200
|
SUP_ID VARCHAR(15),
|
|
201
|
QCKT_REVIEW NVARCHAR(4000),
|
|
202
|
CONS_PROCESS VARCHAR(15),
|
|
203
|
MAINTENANCE VARCHAR(15),
|
|
204
|
REPORT_PLAN_EDIT VARCHAR(15),
|
|
205
|
CONS_QUALITY VARCHAR(15),
|
|
206
|
BEAUTY_IMAGE VARCHAR(15),
|
|
207
|
REQ_ID VARCHAR(15),
|
|
208
|
REQ_CODE VARCHAR(50),
|
|
209
|
SUP_NAME NVARCHAR(1000),
|
|
210
|
HANGHOA_ID VARCHAR(15)
|
|
211
|
)
|
|
212
|
OPEN ListReview
|
|
213
|
|
|
214
|
-- TIẾN ĐỘ CÔNG TRÌNH
|
|
215
|
Exec sp_xml_preparedocument @hdoc Output,@P_ListConsProcessXML
|
|
216
|
DECLARE ListConsProcess CURSOR LOCAL FOR
|
|
217
|
SELECT *
|
|
218
|
FROM OPENXML(@hDoc,'/Root/ListConsProcess',2)
|
|
219
|
WITH
|
|
220
|
(
|
|
221
|
CON_REQ_PROCESS_ID VARCHAR(15),
|
|
222
|
DATE_REPORT VARCHAR(50),
|
|
223
|
PERCENT_COMPLETED DECIMAL(18,2),
|
|
224
|
PERCENT_REMAIN DECIMAL(18,2),
|
|
225
|
DEADLINE VARCHAR(50),
|
|
226
|
ITEM_JOB NVARCHAR(MAX),
|
|
227
|
NOTES NVARCHAR(MAX),
|
|
228
|
DEADLINE_CON VARCHAR(50)
|
|
229
|
)
|
|
230
|
OPEN ListConsProcess
|
|
231
|
|
|
232
|
BEGIN TRANSACTION
|
|
233
|
|
|
234
|
-- THONG TIN VE TO TRINH
|
|
235
|
-- insert zô bảng tài liệu
|
|
236
|
---TAO BANG TAM
|
|
237
|
DECLARE @TEMP TABLE
|
|
238
|
(
|
|
239
|
[KEY] varchar(15),
|
|
240
|
[REF_ID] varchar(15),
|
|
241
|
[TYPE] varchar(50)
|
|
242
|
)
|
|
243
|
-- THEM THONG TIN TO TRINH
|
|
244
|
--KHAI BAO TOAN BO BIEN TRUOC KHI FETCH DATA
|
|
245
|
DECLARE
|
|
246
|
@INDEX int = 0,
|
|
247
|
@IS_PARENT VARCHAR(1),
|
|
248
|
@IS_ARISE VARCHAR(1),
|
|
249
|
@PLAN_TYPE VARCHAR(1),
|
|
250
|
@REQ_BUDGET varchar(15),
|
|
251
|
@REQ_YEAR_BUDGET int,
|
|
252
|
@REQUEST_PARENT VARCHAR(15),
|
|
253
|
@APPROVE_DT VARCHAR(20),
|
|
254
|
@CON_REQUEST_DOC_ID varchar(15),
|
|
255
|
@REQ_ID varchar(15),
|
|
256
|
@REQ_CODE nvarchar(100),
|
|
257
|
@REQ_NAME nvarchar(200),
|
|
258
|
@REQ_DT VARCHAR(20),
|
|
259
|
@REQ_CONTENT nvarchar(1000),
|
|
260
|
@CONSTRUCT_ID varchar(15),
|
|
261
|
@TOTAL_AMT decimal(18,2),
|
|
262
|
@BRANCH_ID varchar(15),
|
|
263
|
@DOC_REQUEST_TOTAL_AMT DECIMAL(18,2) = 0
|
|
264
|
|
|
265
|
|
|
266
|
--INSERT ListBid DETAIL
|
|
267
|
FETCH NEXT FROM ListRequestDoc INTO @CON_REQUEST_DOC_ID,@REQ_ID, @REQ_CODE, @REQ_NAME, @REQ_DT, @REQ_CONTENT,
|
|
268
|
@CONSTRUCT_ID, @TOTAL_AMT, @BRANCH_ID, @PLAN_TYPE, @IS_PARENT,@IS_ARISE, @REQ_BUDGET, @REQ_YEAR_BUDGET, @REQUEST_PARENT, @APPROVE_DT
|
|
269
|
WHILE @@FETCH_STATUS = 0
|
|
270
|
BEGIN
|
|
271
|
--25/12/2014 Anh Ai yeu cau khong kiem tra trung to trinh - mot to trinh dung cho nhieu cong trinh
|
|
272
|
---- KIEM TRA SO TO TRINH CO TRUNG HAY KHONG
|
|
273
|
--IF EXISTS(SELECT * FROM CON_REQUEST_DOC WHERE [REQ_CODE]= @REQ_CODE)
|
|
274
|
--BEGIN
|
|
275
|
-- SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'CON-0001'
|
|
276
|
-- GOTO ABORT
|
|
277
|
--END
|
|
278
|
|
|
279
|
|
|
280
|
EXEC SYS_CodeMasters_Gen 'CON_REQUEST_DOC', @CON_REQUEST_DOC_ID out
|
|
281
|
IF @CON_REQUEST_DOC_ID='' OR @CON_REQUEST_DOC_ID IS NULL GOTO ABORT
|
|
282
|
SET @INDEX = @INDEX + 1
|
|
283
|
IF (@INDEX = 1) -- TRA VE SO TO TRINH DAU TIEN
|
|
284
|
BEGIN
|
|
285
|
--SET @P_REQUEST_ID = @REQ_ID
|
|
286
|
SET @P_REQUEST_ID = @CON_REQUEST_DOC_ID
|
|
287
|
END
|
|
288
|
|
|
289
|
SET @REQUEST_PARENT = @P_REQUEST_ID
|
|
290
|
INSERT INTO CON_REQUEST_DOC([CON_REQUEST_DOC_ID],[REQ_ID], [REQ_CODE], [REQ_NAME], [REQ_DT], [REQ_CONTENT], [CONSTRUCT_ID],
|
|
291
|
[TOTAL_AMT], [NOTES], [RECORD_STATUS], [MAKER_ID], [CREATE_DT], [AUTH_STATUS], [CHECKER_ID],
|
|
292
|
[APPROVE_DT], [BRANCH_ID], [PLAN_TYPE], [IS_PARENT] , [BUDGET], [YEAR_BUDGET], [REQUEST_PARENT], [IS_ARISE])
|
|
293
|
VALUES(@CON_REQUEST_DOC_ID,@REQ_ID, @REQ_CODE, @REQ_NAME, CONVERT(DATETIME, @REQ_DT, 103), @REQ_CONTENT, @CONSTRUCT_ID,
|
|
294
|
@TOTAL_AMT, '', @p_RECORD_STATUS, @P_MAKER_ID, CONVERT(DATETIME, @P_CREATE_DT , 103), @P_AUTH_STATUS, @P_CHECKER_ID,
|
|
295
|
CONVERT(DATETIME, @APPROVE_DT, 103), @BRANCH_ID, @PLAN_TYPE, @IS_PARENT, @REQ_BUDGET, @REQ_YEAR_BUDGET, @REQUEST_PARENT, @IS_ARISE)
|
|
296
|
|
|
297
|
INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@CON_REQUEST_DOC_ID, 'CON_REQUEST_DOC')
|
|
298
|
|
|
299
|
SET @DOC_REQUEST_TOTAL_AMT += @TOTAL_AMT
|
|
300
|
|
|
301
|
PRINT @@ERROR
|
|
302
|
IF @@ERROR <> 0 GOTO ABORT
|
|
303
|
|
|
304
|
FETCH NEXT FROM ListRequestDoc INTO @CON_REQUEST_DOC_ID,@REQ_ID, @REQ_CODE, @REQ_NAME, @REQ_DT, @REQ_CONTENT,
|
|
305
|
@CONSTRUCT_ID, @TOTAL_AMT, @BRANCH_ID, @PLAN_TYPE, @IS_PARENT,@IS_ARISE, @REQ_BUDGET, @REQ_YEAR_BUDGET, @REQUEST_PARENT, @APPROVE_DT
|
|
306
|
END
|
|
307
|
CLOSE ListRequestDoc
|
|
308
|
DEALLOCATE ListRequestDoc
|
|
309
|
|
|
310
|
DECLARE @OLD_REQUEST_PARENT VARCHAR(15) = @REQUEST_PARENT
|
|
311
|
SET @REQUEST_PARENT = (SELECT TOP(1) CON_REQUEST_DOC_ID FROM CON_REQUEST_DOC WHERE REQUEST_PARENT = @OLD_REQUEST_PARENT ORDER BY TOTAL_AMT DESC)
|
|
312
|
UPDATE CON_REQUEST_DOC
|
|
313
|
SET IS_PARENT = (CASE WHEN CON_REQUEST_DOC_ID = @REQUEST_PARENT THEN '1' ELSE '0' END),
|
|
314
|
REQUEST_PARENT = @REQUEST_PARENT
|
|
315
|
WHERE REQUEST_PARENT = @OLD_REQUEST_PARENT
|
|
316
|
|
|
317
|
SET @p_REQUEST_ID = @REQUEST_PARENT
|
|
318
|
|
|
319
|
-- THEM THONG TIN CONG TRINH
|
|
320
|
IF(@p_TOTAL_COST > @DOC_REQUEST_TOTAL_AMT AND EXISTS(SELECT * FROM @TABLE_REQ_DOC))
|
|
321
|
BEGIN
|
|
322
|
DECLARE @ERRORSYS NVARCHAR(200) = N'Tổng chi phí xây dựng không lớn hơn tổng giá trị các tờ trình ('+ cast(format(@DOC_REQUEST_TOTAL_AMT, 'N', 'en-US') AS VARCHAR(20)) +')'
|
|
323
|
ROLLBACK TRANSACTION
|
|
324
|
SELECT '-1' as Result, @ERRORSYS ErrorDesc
|
|
325
|
RETURN '-1'
|
|
326
|
END
|
|
327
|
DECLARE @l_CONSTRUCT_ID VARCHAR(15)
|
|
328
|
EXEC SYS_CodeMasters_Gen 'CON_MASTER', @l_CONSTRUCT_ID out
|
|
329
|
IF @l_CONSTRUCT_ID='' OR @l_CONSTRUCT_ID IS NULL GOTO ABORT
|
|
330
|
INSERT INTO CON_MASTER([IS_FINISH],[CONSTRUCT_ID],[CONSTRUCT_CODE],[CONSTRUCT_NAME],[PLAN_ID],[DIVI_ID],[CONSTRUCT_ADDR],[LENGTH],[WIDTH], [CONSTRUCT_AREA],
|
|
331
|
[FLOORS],[FLOORS_AREA],[YEAR_EXE],[HQ_TYPE],[CONST_TYPE],[TOTAL_AMT],[COST_ESTIMATE],[COST_EXE],[COST_INCURRED],[CONST_PURPOSE],
|
|
332
|
[DESCRIPTION],[START_DT],[END_DT],[DATE_EXE],[COMPLETION_DT],[CONSTRUCT_PROGRESS],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[CONST_ID],
|
|
333
|
[REQUEST_ID], [BRANCH_ID], [STREET], [LOCATION], [SCALE], [ENGINEER], [BRANCH_NAME_ETX],UPD_DT, APPROVE_VALUE,TOTAL_AREA_USE,TOTAL_COST,UNIT_PRICE,UNUSED_AREA,
|
|
334
|
[DEP_CREATE])
|
|
335
|
VALUES('N',@l_CONSTRUCT_ID ,@l_CONSTRUCT_ID ,@p_CONSTRUCT_NAME ,@p_PLAN_ID ,@p_DIVI_ID ,@p_CONSTRUCT_ADDR ,@p_LENGTH, @p_WIDTH, @p_CONSTRUCT_AREA ,@p_FLOORS, @p_FLOORS_AREA,
|
|
336
|
@p_YEAR_EXE ,@p_HQ_TYPE ,@p_CONST_TYPE ,@p_TOTAL_AMT ,@p_COST_ESTIMATE ,@p_COST_EXE ,@p_COST_INCURRED , @p_CONST_PURPOSE, @p_DESCRIPTION ,CONVERT(DATETIME, @p_START_DT, 103) ,
|
|
337
|
CONVERT(DATETIME, @p_END_DT, 103) ,CONVERT(DATETIME, @p_DATE_EXE, 103) ,CONVERT(DATETIME, @p_COMPLETION_DT, 103),@p_CONSTRUCT_PROGRESS ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,
|
|
338
|
CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@p_CONST_ID, @p_REQUEST_ID, @p_BRANCH_ID, @p_STREET, @p_LOCATION, @p_SCALE, @p_ENGINEER, @p_BRANCH_NAME_ETX , CONVERT(DATETIME, @p_UPD_DT, 103), @p_APPROVE_VALUE,@p_TOTAL_AREA_USE,@p_TOTAL_COST,@p_UNIT_PRICE,@p_UNUSED_AREA,
|
|
339
|
@DEP_CREATE)
|
|
340
|
IF @@Error <> 0 GOTO ABORT
|
|
341
|
|
|
342
|
-- UPDATE CONSTRUCT ID CON_REQUEST_DOC
|
|
343
|
UPDATE CON_REQUEST_DOC
|
|
344
|
SET CONSTRUCT_ID = @l_CONSTRUCT_ID
|
|
345
|
WHERE REQUEST_PARENT = @p_REQUEST_ID
|
|
346
|
|
|
347
|
INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_CONSTRUCT_ID, 'CON_MASTER')
|
|
348
|
|
|
349
|
-- DANH SÁCH HÀNG HÓA
|
|
350
|
DECLARE
|
|
351
|
@CON_REQ_HH_ID VARCHAR(15),
|
|
352
|
@SUP_ID VARCHAR(15),
|
|
353
|
@SELLTEMENT_AMT DECIMAL(18,2),
|
|
354
|
@DESCRIPTION NVARCHAR(4000),
|
|
355
|
@HH_ID VARCHAR(15),
|
|
356
|
@ESTIMATES_AMT DECIMAL(18,2),
|
|
357
|
@BRANCH_DO VARCHAR(15),
|
|
358
|
@CONS_DOCUMENT_ID VARCHAR(15),
|
|
359
|
@BID_ID varchar(15),
|
|
360
|
@BID_CODE VARCHAR(15),
|
|
361
|
@REQDT_ID VARCHAR(15)
|
|
362
|
FETCH NEXT FROM ListHH INTO @CON_REQ_HH_ID,@REQ_CODE, @REQ_ID, @SUP_ID, @SELLTEMENT_AMT, @DESCRIPTION, @HH_ID, @ESTIMATES_AMT,@BRANCH_DO,@CONS_DOCUMENT_ID,@BID_ID,@BID_CODE,@REQDT_ID
|
|
363
|
WHILE @@FETCH_STATUS = 0
|
|
364
|
BEGIN
|
|
365
|
EXEC SYS_CodeMasters_Gen 'CON_REQUEST_HH_DT', @CON_REQ_HH_ID out
|
|
366
|
IF @CON_REQ_HH_ID='' OR @CON_REQ_HH_ID IS NULL GOTO ABORT
|
|
367
|
|
|
368
|
SET @DESCRIPTION = (SELECT [DESCRIPTION] FROM PL_REQUEST_DOC_DT WHERE REQDT_ID = @REQDT_ID)
|
|
369
|
|
|
370
|
print @BID_CODE
|
|
371
|
print @REQDT_ID
|
|
372
|
|
|
373
|
INSERT INTO [dbo].[CON_REQUEST_HH_DT]
|
|
374
|
(
|
|
375
|
[CON_REQ_HH_ID],
|
|
376
|
[SUP_ID],
|
|
377
|
[SELLTEMENT_AMT],
|
|
378
|
[DESCRIPTION],
|
|
379
|
[HH_ID],
|
|
380
|
[REQ_ID],
|
|
381
|
[ESTIMATES_AMT],
|
|
382
|
[BRANCH_DO],
|
|
383
|
[CONS_DOCUMENT_ID],
|
|
384
|
[BID_ID],
|
|
385
|
[REQ_CODE],
|
|
386
|
[CONSTRUCT_ID],
|
|
387
|
[REQDT_ID],
|
|
388
|
[BID_CODE]
|
|
389
|
)
|
|
390
|
VALUES
|
|
391
|
(
|
|
392
|
@CON_REQ_HH_ID,
|
|
393
|
@SUP_ID,
|
|
394
|
@SELLTEMENT_AMT,
|
|
395
|
@DESCRIPTION,
|
|
396
|
@HH_ID,
|
|
397
|
@REQ_ID,
|
|
398
|
@ESTIMATES_AMT,
|
|
399
|
@BRANCH_DO,
|
|
400
|
@CONS_DOCUMENT_ID,
|
|
401
|
@BID_ID,
|
|
402
|
@REQ_CODE,
|
|
403
|
@l_CONSTRUCT_ID,
|
|
404
|
@REQDT_ID,
|
|
405
|
@BID_CODE
|
|
406
|
)
|
|
407
|
|
|
408
|
INSERT INTO @TEMP([KEY],[REF_ID],[TYPE]) VALUES (@REQDT_ID, @CON_REQ_HH_ID, 'CON_REQUEST_HH_DT')
|
|
409
|
|
|
410
|
FETCH NEXT FROM ListHH INTO @CON_REQ_HH_ID,@REQ_CODE, @REQ_ID, @SUP_ID, @SELLTEMENT_AMT, @DESCRIPTION, @HH_ID, @ESTIMATES_AMT,@BRANCH_DO,@CONS_DOCUMENT_ID,@BID_ID,@BID_CODE,@REQDT_ID
|
|
411
|
END
|
|
412
|
CLOSE ListHH
|
|
413
|
DEALLOCATE ListHH
|
|
414
|
|
|
415
|
-- THEM CHI TIET THONG TIN HO SO THI CONG
|
|
416
|
-- HOP DONG & PHU LUC HOP DONG
|
|
417
|
DECLARE
|
|
418
|
@CDT_INDEX int = 0,
|
|
419
|
@CDT_CONTRACT_ID varchar(15),
|
|
420
|
@CONTRACT_CODE varchar(15),
|
|
421
|
@CONTRACT_NAME nvarchar(200),
|
|
422
|
@CONTRACT_TYPE varchar(1),
|
|
423
|
@CDT_BID_ID nvarchar(200),
|
|
424
|
@CDT_TOTAL_AMT decimal(18),
|
|
425
|
@CDT_IS_PARENT varchar(1),
|
|
426
|
@CDT_REQUEST_ID varchar(15),
|
|
427
|
@CDT_CONTRACT_PARENT varchar(15),
|
|
428
|
@CDT_REQDT_ID VARCHAR(15)
|
|
429
|
|
|
430
|
-- DANH SACH HO SO THI CONG (HOP DONG)
|
|
431
|
--INSERT ListContract_DT DETAIL
|
|
432
|
FETCH NEXT FROM ListContract_DT INTO @CDT_CONTRACT_ID, @CONTRACT_CODE, @CONTRACT_NAME, @CONTRACT_TYPE,
|
|
433
|
@CDT_BID_ID, @CDT_TOTAL_AMT, @CDT_IS_PARENT, @CDT_REQUEST_ID, @CDT_REQDT_ID
|
|
434
|
WHILE @@FETCH_STATUS = 0
|
|
435
|
BEGIN
|
|
436
|
---- KIEM TRA SO HOP DONG CO TRUNG HAY KHONG
|
|
437
|
--IF EXISTS(SELECT * FROM TR_CONTRACT WHERE REQUEST_ID = @P_REQUEST_ID AND [CONTRACT_CODE]= @CONTRACT_CODE AND @CONTRACT_CODE<>'' AND @CONTRACT_CODE IS NOT NULL)
|
|
438
|
--BEGIN
|
|
439
|
-- SELECT ErrorCode Result, @l_CONSTRUCT_ID CONSTRUCT_ID, '' REF_ID, '' [TYPE], ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'CTRC-0001'
|
|
440
|
-- GOTO ABORT
|
|
441
|
--END
|
|
442
|
|
|
443
|
--SELECT @BID_ID = REF_ID FROM @TEMP where [TYPE]='BID_MASTER' AND [KEY] = @CDT_BID_ID
|
|
444
|
|
|
445
|
DECLARE @l_CON_REQUEST_CONTRACT_ID VARCHAR(15), @CDT_CON_REQ_HH_ID VARCHAR(15)
|
|
446
|
EXEC SYS_CodeMasters_Gen 'CON_REQUEST_CONTRACT', @l_CON_REQUEST_CONTRACT_ID out
|
|
447
|
IF @l_CON_REQUEST_CONTRACT_ID='' OR @l_CON_REQUEST_CONTRACT_ID IS NULL GOTO ABORT
|
|
448
|
|
|
449
|
SET @CDT_CON_REQ_HH_ID = (SELECT REF_ID FROM @TEMP WHERE [KEY] = @CDT_REQDT_ID)
|
|
450
|
INSERT INTO CON_REQUEST_CONTRACT([CON_REQUEST_CONTRACT_ID],[CON_REQ_HH_ID],[CONTRACT_ID],[TOTAL_AMT],[REQDT_ID])
|
|
451
|
VALUES (@l_CON_REQUEST_CONTRACT_ID,@CDT_CON_REQ_HH_ID,@CDT_CONTRACT_ID,@CDT_TOTAL_AMT,@CDT_REQDT_ID)
|
|
452
|
INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_CON_REQUEST_CONTRACT_ID, 'CON_REQUEST_CONTRACT')
|
|
453
|
|
|
454
|
IF @@Error <> 0 GOTO ABORT
|
|
455
|
|
|
456
|
--INSERT INTO @TEMP([KEY],[REF_ID],[TYPE]) VALUES (@CONTRACT_CODE, @l_CONTRACT_ID, 'TR_CONTRACT')
|
|
457
|
|
|
458
|
FETCH NEXT FROM ListContract_DT INTO @CDT_CONTRACT_ID, @CONTRACT_CODE, @CONTRACT_NAME, @CONTRACT_TYPE,
|
|
459
|
@CDT_BID_ID, @CDT_TOTAL_AMT, @CDT_IS_PARENT, @CDT_REQUEST_ID, @CDT_REQDT_ID
|
|
460
|
END
|
|
461
|
CLOSE ListContract_DT
|
|
462
|
DEALLOCATE ListContract_DT
|
|
463
|
|
|
464
|
-- ĐÁNH GIÁ NCC
|
|
465
|
DECLARE
|
|
466
|
@CON_REQ_REVIEW_ID VARCHAR(15),
|
|
467
|
@QCKT_REVIEW NVARCHAR(4000),
|
|
468
|
@CONS_PROCESS VARCHAR(15),
|
|
469
|
@MAINTENANCE VARCHAR(15),
|
|
470
|
@REPORT_PLAN_EDIT VARCHAR(15),
|
|
471
|
@CONS_QUALITY VARCHAR(15),
|
|
472
|
@BEAUTY_IMAGE VARCHAR(15),
|
|
473
|
@SUP_NAME NVARCHAR(1000),
|
|
474
|
@HANGHOA_ID VARCHAR(15)
|
|
475
|
FETCH NEXT FROM ListReview into @CON_REQ_REVIEW_ID,@SUP_ID, @QCKT_REVIEW, @CONS_PROCESS, @MAINTENANCE, @REPORT_PLAN_EDIT, @CONS_QUALITY, @BEAUTY_IMAGE, @REQ_ID, @REQ_CODE, @SUP_NAME, @HANGHOA_ID
|
|
476
|
WHILE @@FETCH_STATUS = 0
|
|
477
|
BEGIN
|
|
478
|
EXEC SYS_CodeMasters_Gen 'CON_REQUEST_REVIEW_DT', @CON_REQ_REVIEW_ID out
|
|
479
|
IF @CON_REQ_REVIEW_ID='' OR @CON_REQ_REVIEW_ID IS NULL GOTO ABORT
|
|
480
|
|
|
481
|
INSERT INTO [dbo].[CON_REQUEST_REVIEW_DT]
|
|
482
|
(
|
|
483
|
[CON_REQ_REVIEW_ID],
|
|
484
|
[SUP_ID],
|
|
485
|
[CONS_PROCESS],
|
|
486
|
[MAINTENANCE],
|
|
487
|
[REPORT_PLAN_EDIT],
|
|
488
|
[CONS_QUALITY],
|
|
489
|
[BEAUTY_IMAGE],
|
|
490
|
[CONSTRUCT_ID],
|
|
491
|
[QCKT_REVIEW],
|
|
492
|
[SUP_NAME],
|
|
493
|
[REQ_CODE],
|
|
494
|
[REQ_ID],
|
|
495
|
[HANGHOA_ID]
|
|
496
|
)
|
|
497
|
VALUES
|
|
498
|
(
|
|
499
|
@CON_REQ_REVIEW_ID,
|
|
500
|
@SUP_ID,
|
|
501
|
@CONS_PROCESS,
|
|
502
|
@MAINTENANCE,
|
|
503
|
@REPORT_PLAN_EDIT,
|
|
504
|
@CONS_QUALITY,
|
|
505
|
@BEAUTY_IMAGE,
|
|
506
|
@l_CONSTRUCT_ID,
|
|
507
|
@QCKT_REVIEW,
|
|
508
|
@SUP_NAME,
|
|
509
|
@REQ_CODE,
|
|
510
|
@REQ_ID,
|
|
511
|
@HANGHOA_ID
|
|
512
|
)
|
|
513
|
INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@CON_REQ_REVIEW_ID, 'CON_REQUEST_REVIEW_DT')
|
|
514
|
|
|
515
|
FETCH NEXT FROM ListReview INTO @CON_REQ_REVIEW_ID,@SUP_ID, @QCKT_REVIEW, @CONS_PROCESS, @MAINTENANCE, @REPORT_PLAN_EDIT, @CONS_QUALITY, @BEAUTY_IMAGE, @REQ_ID, @REQ_CODE, @SUP_NAME, @HANGHOA_ID
|
|
516
|
END
|
|
517
|
CLOSE ListReview
|
|
518
|
DEALLOCATE ListReview
|
|
519
|
-- TIẾN ĐỘ CÔNG TRÌNH
|
|
520
|
DECLARE
|
|
521
|
@CON_REQ_PROCESS_ID VARCHAR(15),
|
|
522
|
@DATE_REPORT VARCHAR(50),
|
|
523
|
@PERCENT_COMPLETED DECIMAL(18,2),
|
|
524
|
@PERCENT_REMAIN DECIMAL(18,2),
|
|
525
|
@DEADLINE VARCHAR(50),
|
|
526
|
@ITEM_JOB NVARCHAR(MAX),
|
|
527
|
@NOTES NVARCHAR(MAX),
|
|
528
|
@DEADLINE_CON VARCHAR(50)
|
|
529
|
FETCH NEXT FROM ListConsProcess into @CON_REQ_PROCESS_ID,@DATE_REPORT, @PERCENT_COMPLETED, @PERCENT_REMAIN, @DEADLINE, @ITEM_JOB, @NOTES, @DEADLINE_CON
|
|
530
|
WHILE @@FETCH_STATUS = 0
|
|
531
|
BEGIN
|
|
532
|
EXEC SYS_CodeMasters_Gen 'CON_REQUEST_PROCESS_DT', @CON_REQ_PROCESS_ID out
|
|
533
|
IF @CON_REQ_PROCESS_ID='' OR @CON_REQ_PROCESS_ID IS NULL GOTO ABORT
|
|
534
|
|
|
535
|
INSERT INTO [dbo].[CON_REQUEST_PROCESS_DT]
|
|
536
|
(
|
|
537
|
[CON_REQ_PROCESS_ID],
|
|
538
|
[DATE_REPORT],
|
|
539
|
[PERCENT_COMPLETED],
|
|
540
|
[PERCENT_REMAIN],
|
|
541
|
[DEADLINE],
|
|
542
|
[CONSTRUCT_ID],
|
|
543
|
[ITEM_JOB],
|
|
544
|
[NOTES],
|
|
545
|
[DEADLINE_CON]
|
|
546
|
)
|
|
547
|
VALUES
|
|
548
|
(
|
|
549
|
@CON_REQ_PROCESS_ID,
|
|
550
|
CONVERT(DATETIME, @DATE_REPORT, 103),
|
|
551
|
@PERCENT_COMPLETED,
|
|
552
|
@PERCENT_REMAIN,
|
|
553
|
CASE WHEN @DEADLINE IS NULL OR @DEADLINE = '' THEN NULL ELSE CONVERT(DATETIME, @DEADLINE, 103) END,
|
|
554
|
@l_CONSTRUCT_ID,
|
|
555
|
@ITEM_JOB,
|
|
556
|
@NOTES,
|
|
557
|
CASE WHEN @DEADLINE_CON IS NULL OR @DEADLINE_CON = '' THEN NULL ELSE CONVERT(DATETIME, @DEADLINE_CON, 103) END
|
|
558
|
)
|
|
559
|
INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@CON_REQ_PROCESS_ID, 'CON_REQUEST_PROCESS_DT')
|
|
560
|
|
|
561
|
FETCH NEXT FROM ListConsProcess INTO @CON_REQ_PROCESS_ID,@DATE_REPORT, @PERCENT_COMPLETED, @PERCENT_REMAIN, @DEADLINE, @ITEM_JOB, @NOTES, @DEADLINE_CON
|
|
562
|
END
|
|
563
|
CLOSE ListConsProcess
|
|
564
|
DEALLOCATE ListConsProcess
|
|
565
|
-- start hieuhm 09/11/2022 thêm lịch sử xử lí
|
|
566
|
INSERT INTO dbo.PL_PROCESS
|
|
567
|
(
|
|
568
|
REQ_ID,
|
|
569
|
PROCESS_ID,
|
|
570
|
CHECKER_ID,
|
|
571
|
APPROVE_DT,
|
|
572
|
PROCESS_DESC,
|
|
573
|
NOTES
|
|
574
|
)
|
|
575
|
VALUES
|
|
576
|
( @l_CONSTRUCT_ID,
|
|
577
|
'INS',
|
|
578
|
@p_MAKER_ID,
|
|
579
|
GETDATE(),
|
|
580
|
N'Tạo mới công trình thành công' ,
|
|
581
|
N'Đơn vị tạo mới công trình'
|
|
582
|
)
|
|
583
|
-- end hieuhm 09/11/2022 thêm lịch sử xử lí
|
|
584
|
COMMIT TRANSACTION
|
|
585
|
BEGIN
|
|
586
|
SELECT '0' as Result, @l_CONSTRUCT_ID CONSTRUCT_ID, [REF_ID], [TYPE], '' ErrorDesc FROM @TEMP
|
|
587
|
RETURN '0'
|
|
588
|
END
|
|
589
|
ABORT:
|
|
590
|
BEGIN
|
|
591
|
CLOSE ListRequestDoc
|
|
592
|
DEALLOCATE ListRequestDoc
|
|
593
|
CLOSE ListConsProcess
|
|
594
|
DEALLOCATE ListConsProcess
|
|
595
|
CLOSE ListReview
|
|
596
|
DEALLOCATE ListReview
|
|
597
|
CLOSE ListContract_DT
|
|
598
|
DEALLOCATE ListContract_DT
|
|
599
|
CLOSE ListHH
|
|
600
|
DEALLOCATE ListHH
|
|
601
|
ROLLBACK TRANSACTION
|
|
602
|
SELECT '-1' as Result, @l_CONSTRUCT_ID CONSTRUCT_ID, '' [REF_ID], '' [TYPE], @sErrorCode ErrorDesc
|
|
603
|
RETURN '-1'
|
|
604
|
End
|