|
1
|
ALTER PROCEDURE [dbo].[BID_MASTER_Upd]
|
|
2
|
@p_BID_ID VARCHAR(15),
|
|
3
|
@p_BID_CODE varchar(50) = NULL,
|
|
4
|
@p_BID_YPE varchar(1) = NULL,
|
|
5
|
@p_PROJECT_ID varchar(15) = NULL,
|
|
6
|
@p_TERM_BID nvarchar(200) = NULL,
|
|
7
|
@p_FORM varchar(4) = NULL,
|
|
8
|
@p_INPUT_DT VARCHAR(20) = NULL,
|
|
9
|
@p_EXP_DT VARCHAR(20) = NULL,
|
|
10
|
@p_OPEN_DT VARCHAR(20) = NULL,
|
|
11
|
@p_BUDGET decimal(18) = NULL,
|
|
12
|
@p_TOTAL_AMT decimal(18) = NULL,
|
|
13
|
@p_GUARANTEE_AMT decimal(18) = NULL,
|
|
14
|
@p_GUARANTEE_PER decimal(18) = NULL,
|
|
15
|
@p_GUARANTEE_EXP VARCHAR(20) = NULL,
|
|
16
|
@p_NOTES nvarchar(1000) = NULL,
|
|
17
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
18
|
@p_MAKER_ID varchar(12) = NULL,
|
|
19
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
20
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
21
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
22
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
23
|
@p_BID_CATEGORY VARCHAR(15)=NULL,
|
|
24
|
@p_Contractor_Det XML = NULL
|
|
25
|
|
|
26
|
AS
|
|
27
|
Declare @hdoc INT
|
|
28
|
Exec sp_xml_preparedocument @hdoc Output,@p_Contractor_Det
|
|
29
|
DECLARE ContractorDetail CURSOR FOR
|
|
30
|
SELECT *
|
|
31
|
FROM OPENXML(@hDoc,'/Root/ContractorDetail',2)
|
|
32
|
WITH
|
|
33
|
(
|
|
34
|
ID VARCHAR(15),
|
|
35
|
SUP_ID varchar(15) ,
|
|
36
|
SEND_DT VARCHAR(20) ,
|
|
37
|
OFFERING_VALUE decimal(18),
|
|
38
|
FORM varchar(4),
|
|
39
|
EXP_DT VARCHAR(20),
|
|
40
|
BANK nvarchar(200),
|
|
41
|
VOUCHER varchar(20) ,
|
|
42
|
PROGRESS_TIME INT,
|
|
43
|
ISVALID varchar(1),
|
|
44
|
IS_BID_WIN varchar(1) ,
|
|
45
|
NOTES nvarchar(1000)
|
|
46
|
)
|
|
47
|
OPEN ContractorDetail
|
|
48
|
-- Put validation here
|
|
49
|
|
|
50
|
BEGIN TRANSACTION
|
|
51
|
----
|
|
52
|
--IF(@p_BID_CATEGORY IS NOT NULL AND @p_BID_CATEGORY <>'')
|
|
53
|
--BEGIN
|
|
54
|
|
|
55
|
IF(NOT EXISTS(SELECT BID_ID FROM dbo.BID_MASTER WHERE BID_ID=@p_BID_ID AND BID_CATEGORY =@p_BID_CATEGORY))
|
|
56
|
BEGIN
|
|
57
|
EXEC dbo.BID_CODE_GenKey 'BID_MASTER', @p_BID_CATEGORY, @p_BID_CODE OUTPUT
|
|
58
|
END
|
|
59
|
-- Kiem tra ma ke hoach co ton tai hay chua
|
|
60
|
IF EXISTS(SELECT * FROM BID_MASTER WHERE [BID_CODE]=@p_BID_CODE AND [BID_ID] != @p_BID_ID)
|
|
61
|
BEGIN
|
|
62
|
ROLLBACK TRANSACTION
|
|
63
|
SELECT ErrorCode Result, '' BID_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'BID-00001'
|
|
64
|
RETURN '0'
|
|
65
|
END
|
|
66
|
|
|
67
|
----
|
|
68
|
UPDATE BID_MASTER SET [BID_CODE] = @p_BID_CODE, [BID_TYPE] = @p_BID_YPE, [PROJECT_ID] = @p_PROJECT_ID,[TERM_BID] = @p_TERM_BID,[FORM] = @p_FORM,[INPUT_DT] = CONVERT(DATETIME, @p_INPUT_DT, 103),[EXP_DT] = CONVERT(DATETIME, @p_EXP_DT, 103),[OPEN_DT] = CONVERT(DATETIME, @p_OPEN_DT, 103),[BUDGET] = @p_BUDGET,[TOTAL_AMT] = @p_TOTAL_AMT,[GUARANTEE_AMT] = @p_GUARANTEE_AMT,[GUARANTEE_PER] = @p_GUARANTEE_PER,[GUARANTEE_EXP] = CONVERT(DATETIME, @p_GUARANTEE_EXP, 103),[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),BID_CATEGORY=@p_BID_CATEGORY
|
|
69
|
WHERE BID_ID= @p_BID_ID
|
|
70
|
--IF @@Error <> 0 GOTO ABORT
|
|
71
|
-- DECLARE
|
|
72
|
-- @ID VARCHAR(15),
|
|
73
|
-- @SUP_ID varchar(15) ,
|
|
74
|
-- @SEND_DT VARCHAR(20) ,
|
|
75
|
-- @OFFERING_VALUE decimal(18),
|
|
76
|
-- @FORM varchar(4),
|
|
77
|
-- @EXP_DT VARCHAR(20),
|
|
78
|
-- @BANK nvarchar(200),
|
|
79
|
-- @VOUCHER varchar(20) ,
|
|
80
|
-- @ISVALID varchar(1),
|
|
81
|
-- @IS_BID_WIN varchar(1) ,
|
|
82
|
-- @PROGRESS_TIME INT,
|
|
83
|
-- @NOTES nvarchar(1000)
|
|
84
|
-- FETCH NEXT FROM ContractorDetail INTO @ID,@SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
|
|
85
|
-- @EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
|
|
86
|
|
|
87
|
-- DELETE FROM BID_CONTRACTOR_DT WHERE BID_ID = @p_BID_ID
|
|
88
|
|
|
89
|
-- WHILE @@FETCH_STATUS = 0
|
|
90
|
-- BEGIN
|
|
91
|
-- IF(LEN(@ID) = 0)
|
|
92
|
-- --INSERT NEW
|
|
93
|
-- --EXEC BID_CONTRACTOR_DT_Ins @SUP_ID,@P_BID_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
|
|
94
|
-- --@EXP_DT,@BANK,@VOUCHER,@IS_BID_WIN,@NOTES,NOW,'','','','',''
|
|
95
|
-- --IF @@ERROR <> 0 GOTO ABORT
|
|
96
|
-- EXEC SYS_CodeMasters_Gen 'BID_CONTRACTOR_DT', @ID out
|
|
97
|
-- IF @ID='' OR @ID IS NULL GOTO ABORT
|
|
98
|
-- --ELSE
|
|
99
|
-- BEGIN
|
|
100
|
-- IF @EXP_DT = '' SET @EXP_DT = NULL
|
|
101
|
-- INSERT INTO BID_CONTRACTOR_DT([ID],[SUP_ID],[BID_ID],[SEND_DT],[OFFERING_VALUE],[FORM],[EXP_DT],
|
|
102
|
-- [BANK],[VOUCHER],[ISVALID],[IS_BID_WIN],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],
|
|
103
|
-- [CHECKER_ID],[APPROVE_DT],[PROGRESS_TIME])
|
|
104
|
-- VALUES(@ID ,@SUP_ID ,@P_BID_ID ,CONVERT(DATETIME, @SEND_DT, 103) ,@OFFERING_VALUE ,@FORM,
|
|
105
|
-- CASE WHEN @EXP_DT IS NOT NULL AND @EXP_DT <> '' then CONVERT(DATETIME, @EXP_DT, 103) ELSE NULL END ,@BANK ,@VOUCHER ,@ISVALID,@IS_BID_WIN ,
|
|
106
|
-- @NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,
|
|
107
|
-- CONVERT(DATETIME, @p_APPROVE_DT, 103),@PROGRESS_TIME)
|
|
108
|
-- END
|
|
109
|
-- --IF @@ERROR <> 0 GOTO ABORT
|
|
110
|
-- FETCH NEXT FROM ContractorDetail INTO @ID,@SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
|
|
111
|
-- @EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
|
|
112
|
-- END
|
|
113
|
|
|
114
|
-- CLOSE ContractorDetail
|
|
115
|
-- DEALLOCATE ContractorDetail
|
|
116
|
|
|
117
|
COMMIT TRANSACTION
|
|
118
|
SELECT '0' as Result, @P_BID_ID BID_ID, '' ErrorDesc
|
|
119
|
RETURN '0'
|
|
120
|
ABORT:
|
|
121
|
BEGIN
|
|
122
|
CLOSE ContractorDetail
|
|
123
|
DEALLOCATE ContractorDetail
|
|
124
|
ROLLBACK TRANSACTION
|
|
125
|
SELECT '-1' as Result, '' BID_ID, '' ErrorDesc
|
|
126
|
RETURN '-1'
|
|
127
|
End
|
|
128
|
|
|
129
|
¿
|
|
130
|
ALTER PROCEDURE [dbo].[BID_MASTER_Ins]
|
|
131
|
@p_BID_CODE varchar(50) = NULL,
|
|
132
|
@p_BID_TYPE varchar(1) = NULL,
|
|
133
|
@p_PROJECT_ID varchar(15) = NULL,
|
|
134
|
@p_TERM_BID nvarchar(200) = NULL,
|
|
135
|
@p_FORM varchar(4) = NULL,
|
|
136
|
@p_INPUT_DT VARCHAR(20) = NULL,
|
|
137
|
@p_EXP_DT VARCHAR(20) = NULL,
|
|
138
|
@p_OPEN_DT VARCHAR(20) = NULL,
|
|
139
|
@p_BUDGET decimal(18) = NULL,
|
|
140
|
@p_TOTAL_AMT decimal(18) = NULL,
|
|
141
|
@p_GUARANTEE_AMT decimal(18) = NULL,
|
|
142
|
@p_GUARANTEE_PER decimal(18) = NULL,
|
|
143
|
@p_GUARANTEE_EXP VARCHAR(20) = NULL,
|
|
144
|
@p_NOTES nvarchar(1000) = NULL,
|
|
145
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
146
|
@p_MAKER_ID varchar(12) = NULL,
|
|
147
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
148
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
149
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
150
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
151
|
@p_BID_CATEGORY VARCHAR(15)=NULL,
|
|
152
|
@p_Contractor_Det XML = NULL
|
|
153
|
AS
|
|
154
|
Declare @hdoc INT
|
|
155
|
Exec sp_xml_preparedocument @hdoc Output,@p_Contractor_Det
|
|
156
|
DECLARE ContractorDetail CURSOR FOR
|
|
157
|
SELECT *
|
|
158
|
FROM OPENXML(@hDoc,'/Root/ContractorDetail',2)
|
|
159
|
WITH
|
|
160
|
(
|
|
161
|
SUP_ID varchar(15) ,
|
|
162
|
SEND_DT VARCHAR(20) ,
|
|
163
|
OFFERING_VALUE decimal(18),
|
|
164
|
FORM varchar(4),
|
|
165
|
EXP_DT VARCHAR(20),
|
|
166
|
BANK nvarchar(200),
|
|
167
|
VOUCHER varchar(20) ,
|
|
168
|
PROGRESS_TIME INT,
|
|
169
|
ISVALID varchar(1),
|
|
170
|
IS_BID_WIN varchar(1) ,
|
|
171
|
NOTES nvarchar(1000)
|
|
172
|
)
|
|
173
|
OPEN ContractorDetail
|
|
174
|
|
|
175
|
BEGIN TRANSACTION
|
|
176
|
IF(@p_BID_CATEGORY IS NOT NULL AND @p_BID_CATEGORY <>'')
|
|
177
|
BEGIN
|
|
178
|
EXEC dbo.BID_CODE_GenKey 'BID_MASTER',@p_BID_CATEGORY,@p_BID_CODE OUTPUT
|
|
179
|
END
|
|
180
|
IF EXISTS(SELECT * FROM BID_MASTER WHERE [BID_CODE]=@p_BID_CODE)
|
|
181
|
BEGIN
|
|
182
|
ROLLBACK TRANSACTION
|
|
183
|
SELECT ErrorCode Result, '' BID_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'BID-00001'
|
|
184
|
RETURN 0
|
|
185
|
END
|
|
186
|
DECLARE @l_BID_ID VARCHAR(15)
|
|
187
|
EXEC SYS_CodeMasters_Gen 'BID_MASTER', @l_BID_ID out
|
|
188
|
IF @l_BID_ID='' OR @l_BID_ID IS NULL GOTO ABORT
|
|
189
|
IF @p_CREATE_DT = '' SET @p_CREATE_DT = NULL
|
|
190
|
INSERT INTO BID_MASTER([BID_ID],[BID_CODE],[BID_TYPE],[PROJECT_ID],[TERM_BID],[FORM],[INPUT_DT],[EXP_DT],[OPEN_DT],[BUDGET],[TOTAL_AMT],[GUARANTEE_AMT],[GUARANTEE_PER],[GUARANTEE_EXP],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],BID_CATEGORY)
|
|
191
|
VALUES(@l_BID_ID ,@p_BID_CODE , @p_BID_TYPE,@p_PROJECT_ID ,@p_TERM_BID ,@p_FORM ,CONVERT(DATETIME, @p_INPUT_DT, 103) ,CONVERT(DATETIME, @p_EXP_DT, 103) ,CONVERT(DATETIME, @p_OPEN_DT, 103) ,@p_BUDGET ,@p_TOTAL_AMT ,@p_GUARANTEE_AMT ,@p_GUARANTEE_PER ,CONVERT(DATETIME, @p_GUARANTEE_EXP, 103) ,@p_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),@p_BID_CATEGORY )
|
|
192
|
IF @@Error <> 0 GOTO ABORT
|
|
193
|
DECLARE
|
|
194
|
@SUP_ID varchar(15) ,
|
|
195
|
@SEND_DT VARCHAR(20) ,
|
|
196
|
@PROGRESS_TIME INT,
|
|
197
|
@OFFERING_VALUE decimal(18),
|
|
198
|
@FORM varchar(4),
|
|
199
|
@EXP_DT VARCHAR(20),
|
|
200
|
@BANK nvarchar(200) ,
|
|
201
|
@VOUCHER varchar(20) ,
|
|
202
|
@ISVALID varchar(1),
|
|
203
|
@IS_BID_WIN varchar(1) ,
|
|
204
|
@NOTES nvarchar(1000)
|
|
205
|
FETCH NEXT FROM ContractorDetail INTO @SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
|
|
206
|
@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
|
|
207
|
|
|
208
|
WHILE @@FETCH_STATUS = 0
|
|
209
|
BEGIN
|
|
210
|
IF(@OFFERING_VALUE =0)
|
|
211
|
BEGIN
|
|
212
|
ROLLBACK TRANSACTION
|
|
213
|
SELECT '-1' as Result, '' BID_ID, N'Giá chào thầu bắc buộc nhập' ErrorDesc
|
|
214
|
RETURN -1
|
|
215
|
END
|
|
216
|
--EXEC BID_CONTRACTOR_DT_Ins @SUP_ID,@l_BID_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
|
|
217
|
--@EXP_DT,@BANK,@VOUCHER,@IS_BID_WIN,@NOTES,NOW,'','','','',''
|
|
218
|
DECLARE @l_ID VARCHAR(15)
|
|
219
|
EXEC SYS_CodeMasters_Gen 'BID_CONTRACTOR_DT', @l_ID out
|
|
220
|
IF @l_ID='' OR @l_ID IS NULL GOTO ABORT
|
|
221
|
IF @SEND_DT = '' SET @SEND_DT = NULL
|
|
222
|
IF @EXP_DT = '' SET @EXP_DT = NULL
|
|
223
|
INSERT INTO BID_CONTRACTOR_DT([ID],[SUP_ID],[BID_ID],[SEND_DT],[OFFERING_VALUE],[FORM],[EXP_DT],
|
|
224
|
[BANK],[VOUCHER],[ISVALID],[IS_BID_WIN],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],
|
|
225
|
[CHECKER_ID],[APPROVE_DT],[PROGRESS_TIME])
|
|
226
|
VALUES(@l_ID ,@SUP_ID ,@l_BID_ID ,CONVERT(DATETIME, @SEND_DT, 103) ,@OFFERING_VALUE ,@FORM,
|
|
227
|
CONVERT(DATETIME, @EXP_DT, 103) ,@BANK ,@VOUCHER ,@ISVALID, @IS_BID_WIN ,
|
|
228
|
@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,
|
|
229
|
@p_CHECKER_ID ,NULL,@PROGRESS_TIME )
|
|
230
|
|
|
231
|
--IF @@ERROR <> 0 GOTO ABORT
|
|
232
|
|
|
233
|
FETCH NEXT FROM ContractorDetail INTO @SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
|
|
234
|
@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
|
|
235
|
END
|
|
236
|
|
|
237
|
CLOSE ContractorDetail
|
|
238
|
DEALLOCATE ContractorDetail
|
|
239
|
|
|
240
|
COMMIT TRANSACTION
|
|
241
|
SELECT '0' as Result, @l_BID_ID BID_ID, @p_BID_CODE ErrorDesc
|
|
242
|
RETURN 0
|
|
243
|
ABORT:
|
|
244
|
BEGIN
|
|
245
|
CLOSE ContractorDetail
|
|
246
|
DEALLOCATE ContractorDetail
|
|
247
|
ROLLBACK TRANSACTION
|
|
248
|
SELECT '-1' as Result, '' BID_ID, '' ErrorDesc
|
|
249
|
RETURN -1
|
|
250
|
End
|