· 10 years ago · Aug 31, 2016, 12:06 PM
1CREATE OR REPLACE PROCEDURE "DB2INST"."MIGRATE_ASV_TO_FNS_OBJECT_1"(
2 IN i_reg_num VARCHAR(12) DEFAULT NULL,
3 IN i_reg_num_id BIGINT DEFAULT NULL,
4 OUT o_pr DEC(1),
5 OUT o_msg CLOB(
6 20 M
7 )
8 ) -----------------------------------------------------------------------------------
9 -- Выгрузка объекта-1 (VO_NUSSVPFR)
10 -- ÐšÐ°Ñ€Ð¶Ð¸Ñ†ÐºÐ°Ñ Ð“Ð°Ð»Ð¸Ð½Ð° (образец)
11 -----------------------------------------------------------------------------------
12 -- выходные параметры O_MSG и O_PR
13 -----------------------------------------------------------------------------------
14 -- в o_msg чаÑÑ‚ÑŒ xml формата VO_NUSSVPFR_2_312_35_04_01 Ñ Ð´Ð°Ð½Ð½Ñ‹Ð¼Ð¸ по плательщику
15 ----------------------------------------------------------------------------------
16 -- o_pr = 1 еÑли данные Ñформированы
17 -- o_pr = 0 еÑли нет данных
18 ----------------------------------------------------------------------------------
19
20LANGUAGE SQL
21NO EXTERNAL ACTION
22MODIFIES SQL DATA
23SPECIFIC SQL160816180341318
24BEGIN
25 DECLARE l_error_message,
26 l_error_message2 VARCHAR(4000);
27
28
29 DECLARE l_errorstate,
30 SQLSTATE CHAR( 5 );
31
32
33 DECLARE l_errorcode,
34 SQLCODE INTEGER;
35
36
37 --[рабочие переменные
38 DECLARE l_kol_kbk INTEGER;
39
40
41 DECLARE l_date VARCHAR(10);
42
43
44 DECLARE l_report VARCHAR(4000);
45
46 --[Переменные Ð´Ð»Ñ Ñ€Ð°Ñчёта Ñальдо
47 DECLARE total_saldo DECIMAL(17,2) DEFAULT 0;
48
49 --sum by column PAYMENT_KBK_SUM table ASV_PAYMENT
50 DECLARE p_sum DECIMAL(17,2) DEFAULT 0;
51
52 --sum by column EC_PAYMENT_KBK_SUM table ASV_EC_PAYMENT
53 DECLARE ec_p_sum DECIMAL(17,2) DEFAULT 0;
54
55 --sum by column PAYMENT_212_FZ_KBK_SUM table ASV_PAYMENT_212_FZ
56 DECLARE p_212_fz_sum DECIMAL(17,2) DEFAULT 0;
57
58 --sum by column EC_PAYMENT_212_FZ_KBK_SUM table ASV_EC_PAYMENT_212
59 DECLARE ec_p_212_fz_sum DECIMAL(17,2) DEFAULT 0;
60
61 --sum by column EC_REFERENCE_PFR_KBK_SUM table ASV_EC_REFERENCE_PFR
62 DECLARE ec_ref_pfr_sum DECIMAL(17,2) DEFAULT 0;
63
64 --sum by column CHARGE_KBK_SUM table ASV_CLARIFICATION_CHARGE
65 DECLARE charge_sum DECIMAL(17,2) DEFAULT 0;
66
67 --sum by column EC_CHARGE_KBK_SUM table ASV_EC_CLARIFICATION_CHARGE
68 DECLARE ec_charge_sum DECIMAL(17,2) DEFAULT 0;
69
70 --sum by column CREDIT_SUM table ASV_PAYMENT_CREDIT
71 DECLARE credit_sum DECIMAL(17,2) DEFAULT 0;
72
73
74 DECLARE clear_sum DECIMAL(17,2) DEFAULT 0;
75
76 --разница между ÐеурегЗадолж R_AMOUNT_PAID_SUM - R_AMOUNT_TOTAL_SUM
77 DECLARE debts_total DECIMAL(17,2) DEFAULT 0;
78
79 --Ñумма по ÐеурегЗадолж R_AMOUNT_PAID_SUM
80 DECLARE fee_tmp_paid_sum DECIMAL(17,2) DEFAULT 0;
81
82 --Ñумма по ÐеурегЗадолж R_AMOUNT_TOTAL_SUM
83 DECLARE fee_tmp_total_sum DECIMAL(17,2) DEFAULT 0;
84
85 --[Обработка ошибок
86 DECLARE l_exception02 CONDITION FOR SQLSTATE '99002';
87
88 DECLARE l_ver VARCHAR(15) DEFAULT '29.08.2016v1';
89
90 DECLARE l_build VARCHAR(100);
91
92
93
94 BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
95 BEGIN
96 GET DIAGNOSTICS EXCEPTION 1 l_error_message = DB2_TOKEN_STRING,
97 l_error_message2 = MESSAGE_TEXT;
98 SET l_ERRORSTATE = SQLSTATE, l_errorcode = SQLCODE;
99 SET l_report = l_report
100 || ' sqlcode=' || l_errorcode || '/' || l_ERRORSTATE || ' ' || l_error_message2 || ' ' || l_error_message;
101 INSERT INTO safe_report(report_str) VALUES(l_report);
102 SET l_report = l_report || ' sqlcode=' || l_errorcode || '/' || l_ERRORSTATE || ' ' || l_error_message2 || ' ' || l_error_message;
103 SIGNAL l_exception02 SET MESSAGE_TEXT = l_report;
104 END;
105 -------------------------------------------------------
106 SET l_build = (SELECT SETTING_VALUE FROM asv_setting WHERE SETTING_ID = 1 WITH ur);
107 SET l_build = COALESCE(l_build,'N/A');
108
109 SET o_pr = 0;
110 IF i_reg_num IS NULL OR i_reg_num_id IS NULL
111 THEN SET l_report = 'MIGRATE_ASV_TO_FNS_OBJECT_1 (' || l_ver|| '/' || l_build || ') --> Параметры пуÑтые' ;
112 INSERT INTO safe_report(report_str) VALUES(l_report);
113 SIGNAL l_exception02 SET MESSAGE_TEXT = l_report;
114 ELSE SET l_report = 'MIGRATE_ASV_TO_FNS_OBJECT_1 (' || l_ver|| '/' || l_build || ') '|| i_reg_num ;
115 END IF;
116
117 -- формирование Ñведений по документу в o_msg
118 SET
119 o_msg = '';
120
121 SET
122 l_kol_kbk = 0;
123
124 --cursor for ÐеурегЗадолж тип взноÑÑ‹
125 --DECLARE debts_cursor_group_1 CURSOR;
126 FOR debts_curs AS debts_cursor_group_1 CURSOR FOR
127 SELECT
128 --debts_amount.kbk_id AS debts_kbk_id,
129 debts_kbk.kbk_code AS debts_kbk_code,
130 debts_amount.R_AMOUNT_FEE_DEADLINE_DATE AS fee_deadline_date,
131 sum(debts_amount.R_AMOUNT_PAID_SUM) AS debts_paid_sum,
132 sum(debts_amount.R_AMOUNT_TOTAL_SUM) AS debts_total_sum
133 FROM
134 ASV_RESPONSIBILITY_AMOUNT debts_amount
135 JOIN ASV_RESPONSIBILITY debts_responsibility
136 ON debts_responsibility.R_ID = debts_amount.R_ID
137 JOIN ASV_RESPONSIBILITY_DOCUMENT debts_document
138 ON debts_document.R_DOCUMENT_ID = debts_responsibility.R_DOCUMENT_ID
139 JOIN ASV_KBK debts_kbk
140 ON debts_kbk.KBK_ID = debts_amount.KBK_ID
141 JOIN ASV_MIGRATE_FNS_INSURER debts_insurer
142 ON debts_insurer.insurer_id = debts_responsibility.insurer_id
143 AND debts_insurer.insurer_id = i_reg_num_id
144 JOIN ASV_TAX_PERIOD debts_tax
145 ON debts_tax.TAX_PERIOD_ID = debts_amount.TAX_PERIOD_ID
146 WHERE
147 debts_amount.R_AMOUNT_ACTUAL_STATE = 1
148 AND debts_amount.kbk_group_id = 1
149 AND debts_amount.R_AMOUNT_ALREADY_CHECKED = 0
150 AND (debts_tax.TAX_PERIOD_TYPE_CODE = 'МС' OR debts_tax.TAX_PERIOD_TYPE_CODE = 'ГД')
151 AND debts_tax.TAX_PERIOD_YEAR = 2016
152 AND debts_tax.TAX_PERIOD_DURATION in (00,10,11,12)
153 GROUP BY debts_kbk.kbk_code,debts_amount.R_AMOUNT_FEE_DEADLINE_DATE--, debts_amount.kbk_id,
154 WITH ur
155 DO
156 SET debts_paid_sum =
157 COALESCE(debts_paid_sum,0);
158
159 SET debts_total_sum =
160 COALESCE(debts_total_sum,0);
161
162 SET
163 debts_total = debts_paid_sum - debts_total_sum;
164 IF debts_total != 0 THEN
165 IF fee_deadline_date IS NULL THEN
166 SET
167 o_msg = o_msg || ' <ÐеурегЗадолж КБК="' || debts_kbk_code || '" ЗадолжВзноÑ="' || debts_total || '"/>';
168 ELSE
169 SET
170 o_msg = o_msg || ' <ÐеурегЗадолж КБК="' || debts_kbk_code || '" ЗадолжВзноÑ="'|| debts_total || '" СрокУплВз="'
171 || DATE(fee_deadline_date) ||'"/>';
172 END IF;
173 END IF;
174 END FOR;
175
176 -- coursor for ÐеурегЗадолж тип пенÑ
177 -- DECLARE debts_cursor_group_2 CURSOR;
178 FOR debts_curs AS debts_cursor_group_2 CURSOR FOR
179 SELECT
180 --debts_amount.kbk_id as debts_kbk_id,
181 debts_kbk.kbk_code AS debts_kbk_code,
182 SUM(debts_amount.R_AMOUNT_PAID_SUM) AS debts_paid_sum,
183 SUM(debts_amount.R_AMOUNT_TOTAL_SUM) AS debts_total_sum
184 FROM
185 ASV_RESPONSIBILITY_AMOUNT debts_amount
186 JOIN ASV_RESPONSIBILITY debts_responsibility
187 ON debts_responsibility.R_ID = debts_amount.R_ID
188 JOIN ASV_RESPONSIBILITY_DOCUMENT debts_document
189 ON debts_document.R_DOCUMENT_ID = debts_responsibility.R_DOCUMENT_ID
190 JOIN ASV_KBK debts_kbk
191 ON debts_kbk.KBK_ID = debts_amount.KBK_ID
192 JOIN ASV_MIGRATE_FNS_INSURER debts_insurer
193 ON debts_insurer.insurer_id = debts_responsibility.insurer_id
194 AND debts_insurer.insurer_id = i_reg_num_id
195 WHERE
196 debts_amount.R_AMOUNT_ACTUAL_STATE = 1
197 AND debts_amount.kbk_group_id = 2
198 AND debts_amount.R_AMOUNT_ALREADY_CHECKED = 0
199 AND TIMESTAMP('2016-10-01-00.00.00') <= debts_amount.R_AMOUNT_CREATED_TS
200 GROUP BY /*debts_amount.kbk_id,*/ debts_kbk.kbk_code, debts_amount.kbk_group_id
201 WITH ur
202 DO
203 SET debts_paid_sum =
204 COALESCE(debts_paid_sum,0);
205
206 SET debts_total_sum =
207 COALESCE(debts_total_sum,0);
208
209 SET
210 debts_total = debts_paid_sum - debts_total_sum;
211
212 IF debts_total != 0 THEN
213 SET
214 o_msg = o_msg || '<ÐеурегЗадолж КБК="' || debts_kbk_code || '" ЗадолжПен="' || debts_total || '"/>';
215 END IF;
216 END FOR;
217
218 -- coursor for ÐеурегЗадолж тип штраф
219 -- DECLARE debts_cursor_group_3 CURSOR;
220
221 FOR debts_curs AS debts_cursor_group_3 CURSOR FOR
222 SELECT
223 --debts_amount.kbk_id as debts_kbk_id,
224 debts_kbk.kbk_code AS debts_kbk_code,
225 SUM(debts_amount.R_AMOUNT_PAID_SUM) AS debts_paid_sum,
226 SUM(debts_amount.R_AMOUNT_TOTAL_SUM) AS debts_total_sum
227 FROM
228 ASV_RESPONSIBILITY_AMOUNT debts_amount
229 JOIN ASV_RESPONSIBILITY debts_responsibility
230 ON debts_responsibility.R_ID = debts_amount.R_ID
231 JOIN ASV_RESPONSIBILITY_DOCUMENT debts_document
232 ON debts_document.R_DOCUMENT_ID = debts_responsibility.R_DOCUMENT_ID
233 JOIN ASV_KBK debts_kbk
234 ON debts_kbk.KBK_ID = debts_amount.KBK_ID
235 JOIN ASV_MIGRATE_FNS_INSURER debts_insurer
236 ON debts_insurer.insurer_id = debts_responsibility.insurer_id
237 AND debts_insurer.insurer_id = i_reg_num_id
238 WHERE
239 debts_amount.R_AMOUNT_ACTUAL_STATE = 1
240 AND debts_amount.kbk_group_id = 3
241 AND debts_amount.R_AMOUNT_ALREADY_CHECKED = 0
242 AND TIMESTAMP('2016-10-01-00.00.00') <= debts_amount.R_AMOUNT_CREATED_TS
243 GROUP BY /*debts_amount.kbk_id,*/ debts_kbk.kbk_code, debts_amount.kbk_group_id
244 WITH ur
245 DO
246 SET debts_paid_sum =
247 COALESCE(debts_paid_sum,0);
248
249 SET debts_total_sum =
250 COALESCE(debts_total_sum,0);
251
252 SET
253 debts_total = debts_paid_sum - debts_total_sum;
254 IF debts_total != 0 THEN
255 SET
256 o_msg = o_msg || '<ÐеурегЗадолж КБК="' || debts_kbk_code || '" ЗадолжШтраф="' || debts_total || '"/>';
257 END IF;
258 END FOR;
259
260
261
262
263 FOR saldo_curs AS saldo_cursor CURSOR FOR
264 SELECT
265 saldo_amount.kbk_id AS l_kbk_id,
266 saldo_kbk.kbk_code AS l_kbk_code,
267 saldo_amount.kbk_group_id AS group_id,
268 SUM(saldo_amount.R_AMOUNT_PAID_SUM) AS saldo_amount_paid_sum,
269 SUM(saldo_amount.R_AMOUNT_TOTAL_SUM) AS saldo_amount_total_sum
270 FROM
271 ASV_RESPONSIBILITY_AMOUNT saldo_amount
272 JOIN ASV_RESPONSIBILITY saldo_responsibility ON
273 saldo_responsibility.R_ID = saldo_amount.R_ID
274 JOIN ASV_RESPONSIBILITY_DOCUMENT saldo_document ON
275 saldo_document.R_DOCUMENT_ID = saldo_responsibility.R_DOCUMENT_ID
276 JOIN ASV_KBK saldo_kbk ON
277 saldo_kbk.KBK_ID = saldo_amount.KBK_ID
278 JOIN ASV_MIGRATE_FNS_INSURER saldo_insurer ON
279 saldo_insurer.insurer_id = saldo_responsibility.insurer_id
280 WHERE
281 saldo_amount.R_AMOUNT_ACTUAL_STATE = 1
282 AND saldo_insurer.insurer_id = i_reg_num_id
283 AND saldo_amount.R_AMOUNT_ALREADY_CHECKED = 0
284 GROUP BY saldo_amount.kbk_id, saldo_kbk.kbk_code, saldo_amount.kbk_group_id
285 WITH ur
286 DO
287 SET saldo_amount_paid_sum =
288 COALESCE(saldo_amount_paid_sum,0);
289
290 SET saldo_amount_total_sum =
291 COALESCE(saldo_amount_total_sum,0);
292
293 IF l_kbk_id IS NULL THEN
294 SET
295 l_kbk_code = 'n/a';
296 ELSE
297 ---------------------------------------------------------------------------
298 -- TODO: Ñформировать данные атрибутов Сальдо2017 и ÐеурегЗадолж
299 --------------------------------------------------------------------------
300 SELECT
301 SUM(COALESCE(subsel2.payKbkSum, 0)) into clear_sum
302 FROM (
303 SELECT SUM(PAYMENT_KBK_SUM) AS payKbkSum
304 FROM ASV_PAYMENT
305 WHERE PAYMENT_INSURER_ID = i_reg_num_id
306 AND PAYMENT_KBK_ID = l_kbk_id
307 UNION ALL
308 SELECT SUM(EC_PAYMENT_KBK_SUM) AS payKbkSum
309 FROM ASV_EC_PAYMENT
310 WHERE EC_PAYMENT_REG_NUM = i_reg_num
311 AND EC_PAYMENT_KBK_ID = l_kbk_id
312
313 UNION ALL
314 SELECT SUM(PAYMENT_212_FZ_KBK_SUM) AS payKbkSum
315 FROM ASV_PAYMENT_212_FZ
316 WHERE PAYMENT_212_FZ_INSURER_ID = i_reg_num_id
317 AND PAYMENT_212_FZ_KBK_ID = l_kbk_id
318 UNION ALL
319 SELECT SUM(EC_PAYMENT_212_FZ_KBK_SUM) AS payKbkSum
320 FROM ASV_EC_PAYMENT_212
321 WHERE EC_PAYMENT_212_FZ_KBK_ID = l_kbk_id
322 AND EC_PAYMENT_212_FZ_REG_NUM = i_reg_num
323 UNION ALL
324 SELECT SUM(EC_REFERENCE_PFR_KBK_SUM) AS payKbkSum
325 FROM ASV_EC_REFERENCE_PFR
326 WHERE EC_REFERENCE_PFR_INSURER_ID = i_reg_num_id
327 AND EC_REFERENCE_PFR_KBK_ID = l_kbk_id
328 UNION ALL
329 SELECT SUM(CHARGE_KBK_SUM) AS payKbkSum
330 FROM ASV_CLARIFICATION_CHARGE
331 WHERE CHARGE_INSURER_ID = i_reg_num_id
332 AND CHARGE_KBK_CODE = l_kbk_code
333 UNION ALL
334 SELECT SUM(EC_CHARGE_KBK_SUM) AS payKbkSum
335 FROM ASV_EC_CLARIFICATION_CHARGE
336 WHERE EC_CHARGE_INSURER_REG_NUM = i_reg_num
337 AND EC_CHARGE_KBK_CODE = l_kbk_code
338 UNION ALL
339 SELECT SUM(-CREDIT_SUM)
340 FROM ASV_PAYMENT_CREDIT
341 WHERE CREDIT_INSURER_ID = i_reg_num_id
342 AND CREDIT_KBK_ID = l_kbk_id
343 WITH ur
344 ) subsel2;
345
346 SET total_saldo =
347 -- вÑе Ð·Ð½Ð°Ñ‡ÐµÐ½Ð¸Ñ ÑƒÑ‡Ñ‚ÐµÐ½Ð½Ñ‹Ñ… Ñумм по ÑоответÑтвующему КБК
348 clear_sum
349 -- разницу между значениÑми общей Ñуммы по обÑзательÑтву
350 - (saldo_amount_paid_sum - saldo_amount_total_sum);
351
352 -- можно добавить проверку на итоговую Ñумму в 0, в таком Ñлучае
353 -- выводить 0.00 в трех Ñальдо, либо не запиÑывать
354
355 IF total_saldo != 0 THEN
356 IF group_id = 1 OR group_id = 2 OR group_id = 3 THEN
357 IF NOT EXISTS (
358 SELECT 1
359 FROM ASV_MIGRATE_FNS_INSURER_KLSP
360 WHERE INSURER_ID = i_reg_num_id
361 AND KBK_ID = l_kbk_id)
362 THEN
363 INSERT INTO ASV_MIGRATE_FNS_INSURER_KLSP
364 (INSURER_ID, INSURER_REGNUM, KBK_CODE, KBK_ID, MIGRATE_FNS_INSURER_KLSP_DATE, MIGRATE_FNS_INSURER_KLSP_SUMMA)
365 VALUES
366 (i_reg_num_id, i_reg_num, l_kbk_code, l_kbk_id, CURRENT TIMESTAMP, total_saldo);
367 ELSE
368 UPDATE ASV_MIGRATE_FNS_INSURER_KLSP AS insurer_klsp
369 SET MIGRATE_FNS_INSURER_KLSP_DATE = CURRENT TIMESTAMP ,
370 MIGRATE_FNS_INSURER_KLSP_SUMMA = total_saldo
371 WHERE
372 insurer_klsp.KBK_ID = l_kbk_id
373 AND insurer_klsp.INSURER_ID = i_reg_num_id;
374 END IF;
375 END IF;
376 IF group_id = 1 THEN
377 SET
378 l_kol_kbk = l_kol_kbk + 1;
379 SET
380 o_msg = o_msg || '<Сальдо2017 КБК="' || l_kbk_code ||
381 '" Сальдо2017Ð="'|| total_saldo || '" Сальдо2017П="0.00" Сальдо2017Ш="0.00"/>' ;
382 --for debug
383 --|| '<'
384 --|| 'PAYMENT_KBK_SUM = ' || p_sum || ' '
385 --|| 'EC_PAYMENT_KBK_SUM = ' || ec_p_sum || ' '
386 --|| 'PAYMENT_212_FZ_KBK_SUM = ' || p_212_fz_sum || ' '
387 --|| 'EC_PAYMENT_212_FZ_KBK_SUM = ' || ec_p_212_fz_sum || ' '
388 --|| 'EC_REFERENCE_PFR_KBK_SUM = ' || ec_ref_pfr_sum || ' '
389 --|| 'CHARGE_KBK_SUM = ' || charge_sum || ' '
390 --|| 'EC_CHARGE_KBK_SUM = ' || ec_charge_sum || ' '
391 --|| 'TOTAL SUM = '|| total_saldo ||'/>'
392
393 ELSE
394 -- пенÑ
395 IF group_id = 2 THEN
396 SET l_kol_kbk =
397 l_kol_kbk + 1;
398 SET o_msg =
399 o_msg || '<Сальдо2017 КБК="' || l_kbk_code ||
400 '" Сальдо2017Ð="0.00" Сальдо2017П="' || total_saldo || '" Сальдо2017Ш="0.00" />' ;
401 ELSE
402 --штрафы
403 IF group_id = 3 THEN
404 SET l_kol_kbk =
405 l_kol_kbk + 1;
406 SET o_msg =
407 o_msg || '<Сальдо2017 КБК="' || l_kbk_code ||
408 '" Сальдо2017Ð="0.00" Сальдо2017П="0.00" Сальдо2017Ш="' || total_saldo || '" />' ;
409 END IF;
410 END IF;
411 END IF;
412 END IF;
413 END IF;
414 END FOR;
415
416 IF l_kol_kbk > 0 THEN
417 SET
418 o_pr = 1;
419 -- документ подготовлен Ð´Ð»Ñ ÑÐ¾Ñ…Ñ€Ð°Ð½ÐµÐ½Ð¸Ñ Ð² xml
420 insert into stelmah_debug (MES) values (o_msg);
421 END IF;
422
423
424 ------------------------------------------------------------------------------
425 MEND : IF o_pr = 0 THEN
426 SET
427 o_msg = NULL;
428 END IF;
429
430 END;
431
432END