|
1
|
|
|
2
|
ALTER PROC [dbo].[TR_BUDGET_YEAR_DVKD_DETAIL_IMPORT] (
|
|
3
|
@p_GD_ID VARCHAR(20),
|
|
4
|
@p_BRANCH_ID VARCHAR(20),
|
|
5
|
@p_BUDGET_TYPE VARCHAR(20),
|
|
6
|
@p_BRANCH_TYPE VARCHAR(20),
|
|
7
|
@p_MAKER_ID VARCHAR(20),
|
|
8
|
@p_BRANCH_KIND VARCHAR(20),
|
|
9
|
@p_XMP_TR_BUDGET_LIMIT_YEAR_DETAIL XML = NULL
|
|
10
|
)
|
|
11
|
AS
|
|
12
|
|
|
13
|
IF(@p_GD_ID IS NULL OR @p_GD_ID = '')
|
|
14
|
BEGIN
|
|
15
|
SELECT '-1' as Result, N'Mã ngân sách chi phí không được để trống' AS ErrorDesc
|
|
16
|
RETURN '-1'
|
|
17
|
END
|
|
18
|
IF(@p_BUDGET_TYPE IS NULL OR @p_BUDGET_TYPE = '')
|
|
19
|
BEGIN
|
|
20
|
SELECT '-1' as Result, N'Loại ngân sách chi phí không được để trống' AS ErrorDesc
|
|
21
|
RETURN '-1'
|
|
22
|
END
|
|
23
|
IF(@p_BRANCH_TYPE IS NULL OR @p_BRANCH_TYPE = '')
|
|
24
|
BEGIN
|
|
25
|
SELECT '-1' as Result, N'Loại đơn vị không được để trống' AS ErrorDesc
|
|
26
|
RETURN '-1'
|
|
27
|
END
|
|
28
|
BEGIN TRANSACTION
|
|
29
|
DECLARE @hdoc INT
|
|
30
|
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_YEAR_DETAIL
|
|
31
|
|
|
32
|
DELETE FROM TR_GOODSTYPE WHERE GD_ID = @p_GD_ID AND BUDGET_TYPE = 'nam'
|
|
33
|
INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS)
|
|
34
|
VALUES (@p_GD_ID, 'nam', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')
|
|
35
|
|
|
36
|
DECLARE XmlBudgetLimitYearhDT CURSOR FOR
|
|
37
|
SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitYearhDT', 2)
|
|
38
|
WITH (
|
|
39
|
BRANCH_CODE VARCHAR(15),
|
|
40
|
BRANCH_NAME NVARCHAR(250),
|
|
41
|
BRANCH_KIND VARCHAR(2),
|
|
42
|
BUDGET_YEAR VARCHAR(15),
|
|
43
|
BUDGET_LIMIT_AMT DECIMAL(18,2),
|
|
44
|
M1 DECIMAL(18, 0),
|
|
45
|
M2 DECIMAL(18, 0),
|
|
46
|
M3 DECIMAL(18, 0),
|
|
47
|
M4 DECIMAL(18, 0),
|
|
48
|
M5 DECIMAL(18, 0),
|
|
49
|
M6 DECIMAL(18, 0),
|
|
50
|
M7 DECIMAL(18, 0),
|
|
51
|
M8 DECIMAL(18, 0),
|
|
52
|
M9 DECIMAL(18, 0),
|
|
53
|
M10 DECIMAL(18, 0),
|
|
54
|
M11 DECIMAL(18, 0),
|
|
55
|
M12 DECIMAL(18, 0)
|
|
56
|
)
|
|
57
|
|
|
58
|
DECLARE @BRANCH_CODE VARCHAR(15),
|
|
59
|
@BRANCH_NAME NVARCHAR(250),
|
|
60
|
@BRANCH_KIND VARCHAR(2),
|
|
61
|
@BUDGET_YEAR VARCHAR(15),
|
|
62
|
@BUDGET_LIMIT_AMT DECIMAL(18,2),
|
|
63
|
@M1 DECIMAL(18, 0),
|
|
64
|
@M2 DECIMAL(18, 0),
|
|
65
|
@M3 DECIMAL(18, 0),
|
|
66
|
@M4 DECIMAL(18, 0),
|
|
67
|
@M5 DECIMAL(18, 0),
|
|
68
|
@M6 DECIMAL(18, 0),
|
|
69
|
@M7 DECIMAL(18, 0),
|
|
70
|
@M8 DECIMAL(18, 0),
|
|
71
|
@M9 DECIMAL(18, 0),
|
|
72
|
@M10 DECIMAL(18, 0),
|
|
73
|
@M11 DECIMAL(18, 0),
|
|
74
|
@M12 DECIMAL(18, 0)
|
|
75
|
|
|
76
|
------------------------------------------------ validate begin ------------------------------------------------
|
|
77
|
|
|
78
|
DECLARE @ERROR_MESSAGE nvarchar(MAX)
|
|
79
|
DECLARE @INDEX INT
|
|
80
|
SET @INDEX = 2
|
|
81
|
|
|
82
|
OPEN XmlBudgetLimitYearhDT;
|
|
83
|
|
|
84
|
FETCH NEXT FROM XmlBudgetLimitYearhDT
|
|
85
|
INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BUDGET_YEAR , @BUDGET_LIMIT_AMT , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
|
86
|
WHILE @@fetch_status=0
|
|
87
|
BEGIN
|
|
88
|
SET @INDEX = @INDEX +1
|
|
89
|
DECLARE @l_BRANCH_ID VARCHAR(20);
|
|
90
|
DECLARE @l_DEP_ID VARCHAR(20);
|
|
91
|
|
|
92
|
SET @l_BRANCH_ID = (SELECT TOP 1 BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE = @BRANCH_CODE)
|
|
93
|
|
|
94
|
IF(@BRANCH_KIND IS NULL OR @BRANCH_KIND = '')
|
|
95
|
BEGIN
|
|
96
|
ROLLBACK TRANSACTION
|
|
97
|
CLOSE XmlBudgetLimitYearhDT;
|
|
98
|
DEALLOCATE XmlBudgetLimitYearhDT;
|
|
99
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Hình thức đơn vị không được để trống' AS ErrorDesc
|
|
100
|
RETURN '-1'
|
|
101
|
END
|
|
102
|
|
|
103
|
IF(ISNULL(@l_BRANCH_ID,'') = '')
|
|
104
|
BEGIN
|
|
105
|
ROLLBACK TRANSACTION
|
|
106
|
CLOSE XmlBudgetLimitYearhDT;
|
|
107
|
DEALLOCATE XmlBudgetLimitYearhDT;
|
|
108
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Đơn vị ' + @BRANCH_CODE + N' không có trong hệ thống' AS ErrorDesc
|
|
109
|
RETURN '-1'
|
|
110
|
END
|
|
111
|
-- NEU DA TON TAI NGAN SACH THI CHI UPDATE
|
|
112
|
IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND))
|
|
113
|
BEGIN
|
|
114
|
IF(ISNULL(@M1, 0) >= 0)
|
|
115
|
BEGIN
|
|
116
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M1 = @M1 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
117
|
END
|
|
118
|
IF(ISNULL(@M2, 0) >= 0)
|
|
119
|
BEGIN
|
|
120
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M2 = @M2 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
121
|
END
|
|
122
|
IF(ISNULL(@M3, 0) >= 0)
|
|
123
|
BEGIN
|
|
124
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M3 = @M3 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
125
|
END
|
|
126
|
IF(ISNULL(@M4, 0) >= 0)
|
|
127
|
BEGIN
|
|
128
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M4 = @M4 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
129
|
END
|
|
130
|
IF(ISNULL(@M5, 0) >= 0)
|
|
131
|
BEGIN
|
|
132
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M5 = @M5 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
133
|
END
|
|
134
|
IF(ISNULL(@M6, 0) >= 0)
|
|
135
|
BEGIN
|
|
136
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M6 = @M6 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
137
|
END
|
|
138
|
IF(ISNULL(@M7, 0) >= 0)
|
|
139
|
BEGIN
|
|
140
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M7 = @M7 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
141
|
END
|
|
142
|
IF(ISNULL(@M8, 0) >= 0)
|
|
143
|
BEGIN
|
|
144
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M8 = @M8 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
145
|
END
|
|
146
|
IF(ISNULL(@M9, 0) >= 0)
|
|
147
|
BEGIN
|
|
148
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M9 = @M9 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
149
|
END
|
|
150
|
IF(ISNULL(@M10, 0) >= 0)
|
|
151
|
BEGIN
|
|
152
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M10 = @M10 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
153
|
END
|
|
154
|
IF(ISNULL(@M11, 0) >= 0)
|
|
155
|
BEGIN
|
|
156
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M11 = @M11 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
157
|
END
|
|
158
|
IF(ISNULL(@M12, 0) >= 0)
|
|
159
|
BEGIN
|
|
160
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M12 = @M12 WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
161
|
END
|
|
162
|
IF(ISNULL(@BUDGET_LIMIT_AMT, 0) >= 0)
|
|
163
|
BEGIN
|
|
164
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET BUDGET_LIMIT_AMT = ISNULL(@BUDGET_LIMIT_AMT, 0) WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
|
165
|
END
|
|
166
|
IF @@error<>0 GOTO ABORT;
|
|
167
|
FETCH NEXT FROM XmlBudgetLimitYearhDT
|
|
168
|
INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BUDGET_YEAR, @BUDGET_LIMIT_AMT , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
|
169
|
END
|
|
170
|
ELSE
|
|
171
|
BEGIN
|
|
172
|
-- Tự động sinh mã code
|
|
173
|
DECLARE @BUDGET_YEAR_LIMIT_ID VARCHAR(15);
|
|
174
|
EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_YEAR', @BUDGET_YEAR_LIMIT_ID OUT;
|
|
175
|
IF @BUDGET_YEAR_LIMIT_ID='' OR @BUDGET_YEAR_LIMIT_ID IS NULL GOTO ABORT;
|
|
176
|
|
|
177
|
INSERT INTO TR_REQ_BUDGET_YEAR_LIMIT(BUDGET_YEAR_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, BUDGET_LIMIT_AMT,
|
|
178
|
M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT, BRANCH_KIND)
|
|
179
|
VALUES(@BUDGET_YEAR_LIMIT_ID, @p_GD_ID, 'nam', @p_BRANCH_TYPE, @BUDGET_YEAR, @l_BRANCH_ID, NULL, ISNULL(@BUDGET_LIMIT_AMT, 0),
|
|
180
|
@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL, @BRANCH_KIND)
|
|
181
|
|
|
182
|
IF @@error<>0 GOTO ABORT;
|
|
183
|
FETCH NEXT FROM XmlBudgetLimitYearhDT
|
|
184
|
INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BUDGET_YEAR, @BUDGET_LIMIT_AMT , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
|
185
|
END
|
|
186
|
END
|
|
187
|
CLOSE XmlBudgetLimitYearhDT;
|
|
188
|
DEALLOCATE XmlBudgetLimitYearhDT
|
|
189
|
COMMIT TRANSACTION
|
|
190
|
|
|
191
|
SELECT '0' AS Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
|
|
192
|
RETURN '0'
|
|
193
|
|
|
194
|
ABORT:
|
|
195
|
BEGIN
|
|
196
|
ROLLBACK TRANSACTION
|
|
197
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, '' ErrorDesc
|
|
198
|
RETURN '-1'
|
|
199
|
END
|
|
200
|
|