|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[BUD_CONTRACT_MASTER_Ins]
|
|
3
|
@p_CONTRACT_ID varchar(15) = NULL,
|
|
4
|
@p_BUILDING_ID varchar(15) = NULL,
|
|
5
|
@p_CONTRACT_NO nvarchar(100) = NULL,
|
|
6
|
@p_VERSION_NO int = NULL,
|
|
7
|
@p_CUST_ID varchar(15) = NULL,
|
|
8
|
@p_INPUT_DT VARCHAR(20) = NULL,
|
|
9
|
@p_AMEND_DT VARCHAR(20) = NULL,
|
|
10
|
@p_FROM_DT VARCHAR(20) = NULL,
|
|
11
|
@p_TO_DATE VARCHAR(20) = NULL,
|
|
12
|
@p_CLOSE_DT VARCHAR(20) = NULL,
|
|
13
|
@p_MONTHS int=0,
|
|
14
|
@p_RENT_PRICE numeric(18) = NULL,
|
|
15
|
@p_NOTES NVARCHAR(MAX)= NULL,
|
|
16
|
@p_BRANCH_ID VARCHAR(15) = NULL,
|
|
17
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
18
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
19
|
@p_STATUS VARCHAR(1) = NULL,
|
|
20
|
@p_MAKER_ID varchar(15) = NULL,
|
|
21
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
22
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
23
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
24
|
@p_XML_TEMP xml = NULL,
|
|
25
|
@p_VAT DECIMAL(18,0),
|
|
26
|
@p_VAT_AMT DECIMAL(18,0),
|
|
27
|
@p_DEPOSITS_AMT DECIMAL(18,0),
|
|
28
|
@p_PAYMENT_SHEDULE NVARCHAR(500),
|
|
29
|
@p_IS_SEND_APPR VARCHAR(15),
|
|
30
|
@p_SEND_APPR_DT VARCHAR(20),
|
|
31
|
@p_SIGN_USER VARCHAR(15),
|
|
32
|
@p_SIGN_DT VARCHAR(15)
|
|
33
|
AS
|
|
34
|
---BAODNQ 30/12/2021:Thêm cột vào bảng BUD_CONTRACT_MASTER-------
|
|
35
|
IF (EXISTS ( SELECT * FROM BUD_CONTRACT_MASTER WHERE CONTRACT_NO=@p_CONTRACT_NO AND VERSION_NO=@p_VERSION_NO))
|
|
36
|
BEGIN
|
|
37
|
SELECT '-1' Result, '' CONTRACT_ID, N'Số hợp đồng này đã tồn tại' ErrorDesc
|
|
38
|
RETURN '0'
|
|
39
|
END
|
|
40
|
BEGIN TRANSACTION
|
|
41
|
|
|
42
|
DECLARE @STT INT = 0
|
|
43
|
|
|
44
|
DECLARE @l_CONTRACT_ID VARCHAR(15)
|
|
45
|
IF(LEN( @p_CONTRACT_ID)=0 OR @p_CONTRACT_ID IS NULL)
|
|
46
|
EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_MASTER', @p_CONTRACT_ID out
|
|
47
|
IF @p_CONTRACT_ID='' OR @p_CONTRACT_ID IS NULL GOTO ABORT
|
|
48
|
INSERT INTO BUD_CONTRACT_MASTER([CONTRACT_ID],[BUILDING_ID],[CONTRACT_NO],[VERSION_NO],[CUST_ID],
|
|
49
|
[INPUT_DT],[AMEND_DT],[FROM_DT],[TO_DATE],[CLOSE_DT],[MONTHS],[RENT_PRICE],[NOTES],
|
|
50
|
[BRANCH_ID],[RECORD_STATUS],[AUTH_STATUS],[STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],
|
|
51
|
[APPROVE_DT],[XML_TEMP],[VAT],[VAT_AMT],[DEPOSITS_AMT],[PAYMENT_SHEDULE],[IS_SEND_APPR],[SEND_APPR_DT],
|
|
52
|
[SIGN_USER], [SIGN_DT])
|
|
53
|
VALUES(@p_CONTRACT_ID ,@p_BUILDING_ID ,@p_CONTRACT_NO ,@p_VERSION_NO ,@p_CUST_ID ,CONVERT(DATE, @p_INPUT_DT, 103) ,
|
|
54
|
CONVERT(DATE, @p_AMEND_DT, 103) ,CONVERT(DATE, @p_FROM_DT, 103) ,CONVERT(DATE, @p_TO_DATE, 103) ,
|
|
55
|
CONVERT(DATE, @p_CLOSE_DT, 103) ,@p_MONTHS ,@p_RENT_PRICE ,@p_NOTES,@p_BRANCH_ID,
|
|
56
|
'1' ,'U','N',@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,NULL ,@p_XML_TEMP,@p_VAT,
|
|
57
|
(@p_VAT*@p_RENT_PRICE)/100,@p_DEPOSITS_AMT,@p_PAYMENT_SHEDULE,@p_IS_SEND_APPR,
|
|
58
|
@p_SEND_APPR_DT,@p_SIGN_USER,@p_SIGN_DT)
|
|
59
|
-----------------------------------------------------------------------------------------------------
|
|
60
|
----INSERT VAO BANG BUD_CONTACT
|
|
61
|
DELETE FROM BUD_CONTACT WHERE CONTRACT_ID = @p_CONTRACT_ID
|
|
62
|
INSERT INTO BUD_CONTACT VALUES(@p_CONTRACT_ID,@p_VERSION_NO,@p_BUILDING_ID,@p_CONTRACT_NO,@p_CUST_ID,CONVERT(DATE, @p_CREATE_DT, 103))
|
|
63
|
IF @@Error <> 0 GOTO ABORT
|
|
64
|
Declare @hdoc1 INT
|
|
65
|
|
|
66
|
----BAODNQ : Thêm các cột vào bảng BUD_CONTRACT_DT---
|
|
67
|
|
|
68
|
DECLARE
|
|
69
|
@d_CONTRACT_DTID VARCHAR(15),
|
|
70
|
@d_BUILD_ID VARCHAR(15),
|
|
71
|
@d_VERSION_NO INT,
|
|
72
|
@d_BUILDING_AREA_ID VARCHAR(15),
|
|
73
|
@d_RENT_AREA DECIMAL(18,2),
|
|
74
|
@d_RENT_AREA_APP DECIMAL(18,2),
|
|
75
|
@d_AREA_REMAIN DECIMAL(18,2),
|
|
76
|
@d_RENT_PRICE DECIMAL,
|
|
77
|
@d_NOTES NVARCHAR(MAX),
|
|
78
|
@d_DIEN_TICH_TANG DECIMAL(18,2),
|
|
79
|
@d_DIEN_TICH_SD_NOI_BO DECIMAL(18,2),
|
|
80
|
@d_DIEN_TICH_DA_CHO_THUE DECIMAL(18,2),
|
|
81
|
@d_DIEN_TICH_CON_LAI DECIMAL(18,2),
|
|
82
|
@d_DIEN_TICH_CAN_SD DECIMAL(18,2),
|
|
83
|
@d_REASON NVARCHAR(MAX),
|
|
84
|
@d_PRICE DECIMAL,
|
|
85
|
@d_TOTAL_AMT DECIMAL,
|
|
86
|
@d_DIEN_TICH_CON_LAI_FINAL DECIMAL(18,2),
|
|
87
|
@d_BRANCH_ID NVARCHAR(15),
|
|
88
|
@d_DEP_ID NVARCHAR(15),
|
|
89
|
@d_VAT INT,
|
|
90
|
@d_PAY_PHASE VARCHAR(15)
|
|
91
|
|
|
92
|
Exec sp_xml_preparedocument @hdoc1 Output,@p_XML_TEMP
|
|
93
|
DECLARE XmlDataDoc CURSOR FOR
|
|
94
|
SELECT *
|
|
95
|
FROM OPENXML(@hdoc1,'/Root/XmlData1',2)
|
|
96
|
WITH
|
|
97
|
(
|
|
98
|
----BAODNQ : Thêm các cột vào bảng BUD_CONTRACT_DT---
|
|
99
|
CONTRACT_DTID VARCHAR(15),
|
|
100
|
BUILD_ID VARCHAR(15),
|
|
101
|
VERSION_NO INT,
|
|
102
|
BUILDING_AREA_ID VARCHAR(15),
|
|
103
|
RENT_AREA DECIMAL(18,2),
|
|
104
|
RENT_AREA_APP DECIMAL(18,2),
|
|
105
|
AREA_REMAIN DECIMAL(18,2),
|
|
106
|
RENT_PRICE DECIMAL,
|
|
107
|
NOTES NVARCHAR(MAX),
|
|
108
|
DIEN_TICH_TANG DECIMAL(18,2),
|
|
109
|
DIEN_TICH_SD_NOI_BO DECIMAL(18,2),
|
|
110
|
DIEN_TICH_DA_CHO_THUE DECIMAL(18,2),
|
|
111
|
DIEN_TICH_CON_LAI DECIMAL(18,2),
|
|
112
|
DIEN_TICH_CAN_SD DECIMAL(18,2),
|
|
113
|
REASON NVARCHAR(MAX),
|
|
114
|
PRICE DECIMAL,
|
|
115
|
TOTAL_AMT DECIMAL,
|
|
116
|
DIEN_TICH_CON_LAI_FINAL DECIMAL(18,2),
|
|
117
|
BRANCH_ID NVARCHAR(15),
|
|
118
|
DEP_ID NVARCHAR(15),
|
|
119
|
VAT INT,
|
|
120
|
PAY_PHASE VARCHAR(15)
|
|
121
|
)
|
|
122
|
OPEN XmlDataDoc
|
|
123
|
----BAODNQ : Thêm các cột vào bảng BUD_CONTRACT_DT---
|
|
124
|
FETCH NEXT FROM XmlDataDoc INTO
|
|
125
|
@d_CONTRACT_DTID,
|
|
126
|
@d_BUILD_ID,
|
|
127
|
@d_VERSION_NO,
|
|
128
|
@d_BUILDING_AREA_ID,
|
|
129
|
@d_RENT_AREA,
|
|
130
|
@d_RENT_AREA_APP,
|
|
131
|
@d_AREA_REMAIN,
|
|
132
|
@d_RENT_PRICE,
|
|
133
|
@d_NOTES,
|
|
134
|
@d_DIEN_TICH_TANG,
|
|
135
|
@d_DIEN_TICH_SD_NOI_BO,
|
|
136
|
@d_DIEN_TICH_DA_CHO_THUE,
|
|
137
|
@d_DIEN_TICH_CON_LAI,
|
|
138
|
@d_DIEN_TICH_CAN_SD,
|
|
139
|
@d_REASON,
|
|
140
|
@d_PRICE,
|
|
141
|
@d_TOTAL_AMT,
|
|
142
|
@d_DIEN_TICH_CON_LAI_FINAL,
|
|
143
|
@d_BRANCH_ID,
|
|
144
|
@d_DEP_ID,
|
|
145
|
@d_VAT,
|
|
146
|
@d_PAY_PHASE
|
|
147
|
|
|
148
|
WHILE @@FETCH_STATUS = 0
|
|
149
|
BEGIN
|
|
150
|
|
|
151
|
-------------BAODNQ 11/3/2022: Check lỗi insert--------
|
|
152
|
DECLARE @VALUE_ERROR DECIMAL(18,0)
|
|
153
|
|
|
154
|
SET @STT= @STT+1
|
|
155
|
SET @VALUE_ERROR = (
|
|
156
|
SELECT FLOOR_AREA - (UTILZED_AREA + INTERNAL_AREA)
|
|
157
|
FROM BUD_AREA_DT WHERE BUILDING_AREA_ID = @d_BUILDING_AREA_ID
|
|
158
|
)
|
|
159
|
|
|
160
|
DECLARE @ERRORSYS NVARCHAR(200) =''
|
|
161
|
--IF ( @d_DIEN_TICH_CAN_SD > (@VALUE_ERROR + @d_RENT_AREA_APP) OR @d_DIEN_TICH_CAN_SD > @d_DIEN_TICH_CON_LAI_FINAL)
|
|
162
|
IF ( @d_DIEN_TICH_CAN_SD > (@VALUE_ERROR + @d_RENT_AREA_APP) OR @d_DIEN_TICH_CAN_SD > @d_DIEN_TICH_CON_LAI)
|
|
163
|
BEGIN
|
|
164
|
SET @ERRORSYS =
|
|
165
|
N'Lưới chi tiết DTSD nội bộ, ' +
|
|
166
|
N'Dòng '+ convert(nvarchar(2),@stt) +N': DT cần sử dụng phải nhỏ hơn hoặc bằng DT trống '
|
|
167
|
END
|
|
168
|
|
|
169
|
--IF ( @d_DIEN_TICH_CAN_SD = 0)
|
|
170
|
--BEGIN
|
|
171
|
-- SET @ERRORSYS =
|
|
172
|
-- N'Lưới chi tiết DTSD nội bộ, ' +
|
|
173
|
-- N'Dòng '+ convert(nvarchar(2),@stt) +N': DT cần sử dụng phải lớn hơn 0 '
|
|
174
|
--END
|
|
175
|
|
|
176
|
IF @ERRORSYS <> ''
|
|
177
|
BEGIN
|
|
178
|
CLOSE XmlDataDoc
|
|
179
|
DEALLOCATE XmlDataDoc
|
|
180
|
ROLLBACK TRANSACTION
|
|
181
|
SELECT '-1' as Result, '' CONTRACT_ID, @ERRORSYS ErrorDesc
|
|
182
|
RETURN '-1'
|
|
183
|
END
|
|
184
|
|
|
185
|
|
|
186
|
---BAODNQ: insert thêm vào các cột trong bảng BUD_CONTRACT_DT---
|
|
187
|
EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_DT', @d_CONTRACT_DTID out
|
|
188
|
IF @d_CONTRACT_DTID='' OR @d_CONTRACT_DTID IS NULL GOTO ABORT
|
|
189
|
INSERT INTO BUD_CONTRACT_DT([CONTRACT_DTID],[BUILD_ID],[CONTRACT_ID],[VERSION_NO],[BUILDING_AREA_ID],[RENT_AREA],[RENT_AREA_APP],
|
|
190
|
[AREA_REMAIN],[RENT_PRICE],[NOTES], [DIEN_TICH_TANG], [DIEN_TICH_SD_NOI_BO],[DIEN_TICH_DA_CHO_THUE],[DIEN_TICH_CON_LAI],
|
|
191
|
[DIEN_TICH_CAN_SD],[REASON],[PRICE],[TOTAL_AMT],[DIEN_TICH_CON_LAI_FINAL],[BRANCH_ID],[DEP_ID], [VAT], [PAY_PHASE])
|
|
192
|
VALUES(@d_CONTRACT_DTID ,@d_BUILD_ID ,@p_CONTRACT_ID ,@d_VERSION_NO ,@d_BUILDING_AREA_ID ,@d_RENT_AREA,0,@d_AREA_REMAIN,@d_RENT_PRICE,@d_NOTES,
|
|
193
|
@d_DIEN_TICH_TANG,@d_DIEN_TICH_SD_NOI_BO,@d_DIEN_TICH_DA_CHO_THUE,@d_DIEN_TICH_CON_LAI,@d_DIEN_TICH_CAN_SD,@d_REASON,
|
|
194
|
@d_PRICE,@d_TOTAL_AMT,@d_DIEN_TICH_CON_LAI_FINAL,@d_BRANCH_ID,@d_DEP_ID, @d_VAT, @d_PAY_PHASE)
|
|
195
|
IF @@Error <> 0 GOTO ABORT
|
|
196
|
FETCH NEXT FROM XmlDataDoc INTO
|
|
197
|
@d_CONTRACT_DTID,
|
|
198
|
@d_BUILD_ID,
|
|
199
|
@d_VERSION_NO,
|
|
200
|
@d_BUILDING_AREA_ID,
|
|
201
|
@d_RENT_AREA,
|
|
202
|
@d_RENT_AREA_APP,
|
|
203
|
@d_AREA_REMAIN,
|
|
204
|
@d_RENT_PRICE,
|
|
205
|
@d_NOTES,
|
|
206
|
@d_DIEN_TICH_TANG,
|
|
207
|
@d_DIEN_TICH_SD_NOI_BO,
|
|
208
|
@d_DIEN_TICH_DA_CHO_THUE,
|
|
209
|
@d_DIEN_TICH_CON_LAI,
|
|
210
|
@d_DIEN_TICH_CAN_SD,
|
|
211
|
@d_REASON,
|
|
212
|
@d_PRICE,
|
|
213
|
@d_TOTAL_AMT,
|
|
214
|
@d_DIEN_TICH_CON_LAI_FINAL,
|
|
215
|
@d_BRANCH_ID,
|
|
216
|
@d_DEP_ID,
|
|
217
|
@d_VAT,
|
|
218
|
@d_PAY_PHASE
|
|
219
|
END
|
|
220
|
CLOSE XmlDataDoc
|
|
221
|
DEALLOCATE XmlDataDoc
|
|
222
|
---BAODNQ: sửa lấy tổng thành TOTAL_AMT---
|
|
223
|
SET @p_RENT_PRICE = (SELECT SUM(TOTAL_AMT) FROM BUD_CONTRACT_DT
|
|
224
|
WHERE CONTRACT_ID =@p_CONTRACT_ID
|
|
225
|
AND VERSION_NO=@p_VERSION_NO)
|
|
226
|
|
|
227
|
UPDATE BUD_CONTRACT_MASTER
|
|
228
|
--SET RENT_PRICE=@p_RENT_PRICE*MONTHS
|
|
229
|
SET RENT_PRICE = @p_RENT_PRICE
|
|
230
|
WHERE CONTRACT_ID=@p_CONTRACT_ID AND VERSION_NO=@p_VERSION_NO
|
|
231
|
|
|
232
|
---BAODNQ 29/12/2021: Insert vào PL_PROCESS lưu lịch sử xử lý
|
|
233
|
INSERT INTO dbo.PL_PROCESS
|
|
234
|
(
|
|
235
|
REQ_ID,
|
|
236
|
PROCESS_ID,
|
|
237
|
CHECKER_ID,
|
|
238
|
APPROVE_DT,
|
|
239
|
PROCESS_DESC,
|
|
240
|
NOTES
|
|
241
|
)
|
|
242
|
VALUES
|
|
243
|
( @p_CONTRACT_ID,
|
|
244
|
'INSERT',
|
|
245
|
@p_MAKER_ID,
|
|
246
|
GETDATE(),
|
|
247
|
N'Thêm mới thông tin khai báo DTSD nội bộ thành công' ,
|
|
248
|
N'Thêm mới thông tin khai báo DTSD nội bộ'
|
|
249
|
)
|
|
250
|
|
|
251
|
COMMIT TRANSACTION
|
|
252
|
SELECT '0' as Result, @p_CONTRACT_ID CONTRACT_ID, '' ErrorDesc
|
|
253
|
RETURN '0'
|
|
254
|
ABORT:
|
|
255
|
BEGIN
|
|
256
|
--CLOSE XmlDataDoc
|
|
257
|
--DEALLOCATE XmlDataDoc
|
|
258
|
ROLLBACK TRANSACTION
|
|
259
|
SELECT '-1' as Result, '' CONTRACT_ID, '' ErrorDesc
|
|
260
|
RETURN '-1'
|
|
261
|
End
|