https://pastein.ru/t/LwJ
Загрузка данных
-- DROP FUNCTION ourpension.f_app_outgoing_tessa(int8, varchar, int8, bool, int8, varchar, bool);
CREATE OR REPLACE FUNCTION ourpension.f_app_outgoing_tessa(p_app_id bigint, p_code character varying, p_file_id bigint DEFAULT NULL::bigint, p_is_reject boolean DEFAULT false, p_signed_by bigint DEFAULT NULL::bigint, p_author character varying DEFAULT NULL::character varying(50), p_is_send boolean DEFAULT false)
RETURNS text
LANGUAGE plpgsql
AS $function$
declare
v_card_id text;
v_doc_id text;
v_first_name varchar(50);
v_last_name varchar(50);
v_middle_name varchar(50);
v_insurance_number varchar(15);
v_reg_number varchar(50);
v_registration_date date;
v_author varchar(50);
v_tessa_param json;
v_web_base varchar(500);
v_file_name text;
v_file bytea;
v_tessa_path text = 'Integration/CreateCard';
v_in_card_link_id text;
v_post_address text;
v_signed_by text;
v_individual_id bigint;
v_actual_address text;
v_card_sub_type_id varchar(50) = '';
v_card_sub_type_name varchar(200) = '';
v_delivery_method_id bigint;
v_delivery_method_name varchar(200);
v_delivery_type_id bigint = 2;
v_delivery_type_name varchar(200) = 'Письмо простое';
v_representative varchar(200);
v_deaded varchar(200) = '';
v_deaded_insurance_number varchar(15);
v_client_full_name varchar(200) = '';
v_recipient_full_name varchar(200) = '';
v_requester_full_name varchar(200) = '';
v_requester_insurance_number varchar(15);
v_summary text = '';
v_tessa_auto_accept boolean = false;
v_state text;
BEGIN
select web_base into v_web_base from tools.settings limit 1;
select value || v_tessa_path into v_tessa_path from tools.npf360_settings where "name" = 'TESSA_API';
-- if p_signed_by is not null then
--select u.username into v_signed_by from "security"."USER" u where id = p_signed_by;
--end if;
if p_signed_by is not null then
select u.username into v_signed_by
from ourpension.agent a
left join "security"."USER" u on u.id = a.user_id
where a.id = p_signed_by;
end if;
if p_code in ('PDN', 'BR', 'PDN_BR') then
PERFORM ourpension.app_pdn_to_tessa(p_app_id);
select i.first_name, i.last_name, i.middle_name, coalesce(i.insurance_number,'') , i.number, i.date, u.username, i.card_link_id, i.individual_id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.application_personal_data_individual i
inner join ourpension.agent e on e.id = i.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on i.information_source_id = ifs.id
left join ourpension.address adr on adr.id = i.actual_address_id
left join ourpension.address adr1 on adr1.id = i.registration_address_id
left join ourpension.address adr2 on adr2.id = i.post_address_id
where i.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('CNL') then
PERFORM ourpension.app_cnl_to_tessa(p_app_id);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, a.individual_id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.application_for_cancellation a on a.individual_id = i.id
left join ourpension.application_personal_data_individual pdn on a.application_personal_data_individual_id = pdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('NP', 'SPV') then
PERFORM ourpension.app_np_spv_to_tessa(p_app_id, p_code);
select pdn.first_name, pdn.last_name, pdn.middle_name, coalesce(pdn.insurance_number,'') , a.number, a.date, u.username, a.card_link_id, s.individual_id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.sharer s on s.individual_id = i.id
inner join ourpension.ops_pension_assignment_application a on a.sharer_id = s.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
inner join ourpension.application_personal_data_individual pdn on a.client_personal_data_id = pdn.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('SNV') then
PERFORM ourpension.app_snv_to_tessa(p_app_id);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,'') ,
a.number, a.date, u.username, a.card_link_id, a.individual_id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.application_standart_tax_deduction a on a.individual_id = i.id
left join ourpension.application_personal_data_individual pdn on a.application_personal_data_individual_id = pdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('SPN', 'OPNPO', 'OPPDS') then
PERFORM ourpension.app_rspn_to_tessa(p_app_id, p_code);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, i.id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.application_assignee_distribution a on a.individual_id = i.id
left join ourpension.application_personal_data_individual pdn on a.application_personal_data_individual_id = pdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('PP', 'OPSO') then
PERFORM ourpension.app_pp_to_tessa(p_app_id, p_code);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, i.id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
, ', ' || concat_ws(' ', d.last_name, d.first_name, d.middle_name), coalesce(d.insurance_number,''), ', ' || concat_ws(' ', rpdn.last_name, rpdn.first_name, rpdn.middle_name)
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name, v_deaded, v_deaded_insurance_number, v_representative
from ourpension.individual i
inner join ourpension.ops_assignee_payment a on a.assignee_id = i.id
inner join ourpension.individual d on a.individual_id = d.id
left join ourpension.application_personal_data_individual pdn on a.assignee_application_personal_data_id = pdn.id
left join ourpension.application_personal_data_individual rpdn on a.representative_application_personal_data_individual_id = rpdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
--left join ourpension.address adr6 on adr6.id = rpdn.actual_address_id
--left join ourpension.address adr7 on adr7.id = rpdn.registration_address_id
--left join ourpension.address adr8 on adr8.id = rpdn.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('PDSPP', 'VNS') then
PERFORM ourpension.app_pdspp_vns_to_tessa(p_app_id, p_code);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, i.id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
, ', ' || concat_ws(' ', d.last_name, d.first_name, d.middle_name), coalesce(d.insurance_number,''), ', ' || concat_ws(' ', rpdn.first_name, rpdn.last_name, rpdn.middle_name, rpdn.insurance_number)
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name, v_deaded, v_deaded_insurance_number, v_representative
from ourpension.individual i
inner join ourpension.inherited_payment a on a.inheritor_id = i.id
left join ourpension.application_personal_data_individual pdn on a.assignee_application_personal_data_id = pdn.id
left join ourpension.application_personal_data_individual rpdn on a.representative_application_personal_data_individual_id = rpdn.id
inner join ourpension.individual d on a.individual_id = d.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
--left join ourpension.address adr6 on adr6.id = rpdn.actual_address_id
--left join ourpension.address adr7 on adr7.id = rpdn.registration_address_id
--left join ourpension.address adr8 on adr8.id = rpdn.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('PROVO') then
PERFORM ourpension.app_provo_to_tessa(p_app_id, p_code);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, i.id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.suspense_or_renewal_application a on a.individual_id = i.id
left join ourpension.application_personal_data_individual pdn on a.application_personal_data_individual_id = pdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('RDNPO', 'RDPDS_TRANSFER', 'RDPDS_PAYMENT', 'RDPDS_LIFE_SITUATION') then
PERFORM ourpension.app_rdnpo_rdpds_to_tessa(p_app_id, p_code);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, a.individual_id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.application_termination_contract a on a.individual_id = i.id
left join ourpension.application_personal_data_individual pdn on a.application_personal_data_individual_id = pdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('NPDS', 'NNPO') then
PERFORM ourpension.app_npds_nnpo_to_tessa(p_app_id, p_code);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, a.individual_id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.npo_pension_assignment_application a on a.individual_id = i.id
left join ourpension.application_personal_data_individual pdn on a.application_personal_data_individual_id = pdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
elseif p_code in ('ZEV', 'UOEF') then
PERFORM ourpension.app_zev_ouef_to_tessa(p_app_id, p_code);
select coalesce(pdn.first_name, i.first_name), coalesce(pdn.last_name, i.last_name), coalesce(pdn.middle_name, i.middle_name), coalesce(pdn.insurance_number, i.insurance_number,''),
a.number, a.date, u.username, a.card_link_id, a.individual_id, coalesce(nullif(adr.address_text, ''), nullif(adr1.address_text, ''), nullif(adr2.address_text, ''), nullif(adr3.address_text, ''), nullif(adr4.address_text, ''), nullif(adr5.address_text, ''), ''), ifs.tessa_id dm_id, ifs."name" dm_name
into v_first_name, v_last_name, v_middle_name, v_insurance_number, v_reg_number, v_registration_date, v_author, v_in_card_link_id, v_individual_id, v_actual_address, v_delivery_method_id, v_delivery_method_name
from ourpension.individual i
inner join ourpension.application_for_one_time_contribution a on a.individual_id = i.id
left join ourpension.application_personal_data_individual pdn on a.client_personal_data_id = pdn.id
inner join ourpension.agent e on e.id = a.agent_id
inner join "security"."USER" u on u.id = e.user_id
inner join ourpension.information_source ifs on a.information_source_id = ifs.id
left join ourpension.address adr on adr.id = pdn.actual_address_id
left join ourpension.address adr1 on adr1.id = pdn.registration_address_id
left join ourpension.address adr2 on adr2.id = pdn.post_address_id
left join ourpension.address adr3 on adr3.id = i.actual_address_id
left join ourpension.address adr4 on adr4.id = i.registration_address_id
left join ourpension.address adr5 on adr5.id = i.post_address_id
where a.id = p_app_id
;
IF NOT FOUND then
return 'Error: Заявление не найдено.';
end if;
end if;
select sat.tessa_code_out, sat.tessa_name_out, v_web_base || sat.npf360_path || p_app_id, coalesce(sed_auto_accept, v_tessa_auto_accept)
into v_card_sub_type_id, v_card_sub_type_name, v_web_base, v_tessa_auto_accept
from ourpension.service_app_type sat
left join ourpension.service_app_notice_type nt on nt.service_app_type_id = sat.id and nt.notice_kind = 'ACCEPT'
where sat.code = p_code;
v_summary = 'Ответ на сервисное заявление ФЛ ' || concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name), trim(v_deaded));
v_delivery_method_id := 0;
v_delivery_method_name := 'Почта России';
if p_is_reject then
select coalesce(sed_auto_accept, false)
into v_tessa_auto_accept
from ourpension.service_app_type sat
left join ourpension.service_app_notice_type nt on nt.service_app_type_id = sat.id and nt.notice_kind = 'REJECT'
where sat.code = p_code;
if p_code in ('PROVO') then
if (select service_type from ourpension.suspense_or_renewal_application where id = p_app_id) = 'PDS' then
v_card_sub_type_id := '6D11F6DE-ACFC-48BD-8E3B-7064FF52476F';
v_card_sub_type_name := 'Иное';
else
v_card_sub_type_id := '321BF23E-61C6-4384-86AC-8CFB0B50C44D';
v_card_sub_type_name := 'Отказ в приеме заявления (НПО)';
end if;
else
select tessa_code_reject, tessa_name_reject into v_card_sub_type_id, v_card_sub_type_name
from ourpension.service_app_type
where code = p_code;
end if;
if p_author is not null then
v_author := p_author;
end if;
if p_code in ('PP', 'OPSO', 'PDSPP', 'VNS') then
v_summary = 'Возврат сервисного заявления ФЛ ' || concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name), trim(nullif(v_representative, '')), trim(v_deaded));
if p_code in ('PP', 'OPSO') then
if EXISTS(select 1 from ourpension.ops_assignee_payment a inner join ourpension.application_death_info d on d.id = a.death_info_id where a.id = p_app_id and d.is_svo = true) then
v_delivery_type_id := 0;
v_delivery_type_name := 'Письмо заказное';
end if;
elseif p_code in ('PDSPP') then
if EXISTS(select 1 from ourpension.inherited_payment a inner join ourpension.application_death_info d on d.id = a.deathinfoid where a.id = p_app_id and d.is_svo = true) then
v_delivery_type_id := 0;
v_delivery_type_name := 'Письмо заказное';
end if;
end if;
else
v_summary = 'Отказ в приеме сервисного заявления ФЛ ' || concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name), trim(v_deaded));
end if;
end if;
if p_is_send then
select coalesce(sed_auto_accept, false)
into v_tessa_auto_accept
from ourpension.service_app_type sat
left join ourpension.service_app_notice_type nt on nt.service_app_type_id = sat.id and nt.notice_kind = 'SEND'
where sat.code = p_code;
select tessa_code_send, tessa_name_send into v_card_sub_type_id, v_card_sub_type_name
from ourpension.service_app_type
where code = p_code;
if p_author is not null then
v_author := p_author;
end if;
if p_code in ('PP', 'OPSO') then
if EXISTS(select 1 from ourpension.ops_assignee_payment a inner join ourpension.application_death_info d on d.id = a.death_info_id where a.id = p_app_id and d.is_svo = true) then
v_delivery_type_id := 0;
v_delivery_type_name := 'Письмо заказное';
end if;
elseif p_code in ('PDSPP') then
if EXISTS(select 1 from ourpension.inherited_payment a inner join ourpension.application_death_info d on d.id = a.deathinfoid where a.id = p_app_id and d.is_svo = true) then
v_delivery_type_id := 0;
v_delivery_type_name := 'Письмо заказное';
end if;
end if;
v_summary = 'Запрос досыла документов на сервисное заявление ФЛ ' || concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name), trim(nullif(v_representative, '')), trim(v_deaded), v_deaded_insurance_number);
end if;
if p_code in ('PP', 'OPSO', 'PDSPP', 'VNS') then
v_recipient_full_name := concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name));
v_requester_full_name := concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name));
v_requester_insurance_number := v_insurance_number;
v_client_full_name := concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name)); --:= ltrim(v_deaded, ',');
v_insurance_number := v_insurance_number;--= v_deaded_insurance_number;
else
v_client_full_name := concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name));
v_recipient_full_name := concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name));
v_requester_full_name := concat_ws(' ', trim(v_last_name), trim(v_first_name), trim(v_middle_name));
v_requester_insurance_number := v_insurance_number;
end if;
v_tessa_param := json_build_object(
'system', '5',
'CardTypeID', 'e8b0557b-6410-4e8c-bfbf-7cb254da2621',
'AuthorAdLogin', v_author,
'RegistratorAdLogin', v_author,
'SubTypeID', '27EB9085-B123-4EA8-8FEC-DB4C67B90735',
'SubTypeName', 'Ответ на сервисное заявление ФЛ',
'Summary', v_summary,
'DeliveryMethodID', v_delivery_method_id,
'DeliveryMethodName', v_delivery_method_name,
'DeliveryTypeID', v_delivery_type_id,
'DeliveryTypeName', v_delivery_type_name,
'OutgoingSubTypeID', v_card_sub_type_id,
'OutgoingSubTypeName', v_card_sub_type_name,
'FIORecipient', v_recipient_full_name,
'RecipientAddress', v_actual_address,
'RequesterFullName', v_requester_full_name,
'RequesterSNILS', v_requester_insurance_number,
'AddressPost', v_actual_address,
'FullName', v_client_full_name,
'SNILS', v_insurance_number,
'InsuranceNumber', v_insurance_number,
'DocumentNumber', v_reg_number,
'ClientID', v_individual_id,
'DocumentDate', to_char(v_registration_date, 'YYYY-MM-DD') || 'T00:00:00Z',
'LinkCardID', v_in_card_link_id,
'NPF360Link', v_web_base,
'IDSource', p_code || p_app_id::text || current_timestamp::text,
'SignedByAdLogin', coalesce(v_signed_by, v_author)
);
RAISE notice 'Json param: "%"', v_tessa_param::text;
v_card_id := convert_from(robot.post_json( v_tessa_path, v_tessa_param), 'UTF8');
if utils.f_is_tessa_guid(v_card_id) then
if p_file_id > 0 then
if p_is_reject then
select file_name, file into v_file_name, v_file from ourpension.service_app_notice where id = p_file_id;
else
select "name", bytes into v_file_name, v_file from dev.query_report_result_file where id = p_file_id;
end if;
v_doc_id := utils.post_file_to_tessa_card(v_card_id, v_author, v_file_name, v_file);
if not utils.f_is_tessa_guid(v_doc_id) then
return v_doc_id;
end if;
if p_is_reject then
update ourpension.service_app_notice set file_link = v_doc_id, file = 'Смотри файл в Тесса' where id = p_file_id;
end if;
end if;
if v_tessa_auto_accept then
call utils.tessa_change_state(v_card_id, 'Draft', 'StartWithoutSigning');
-- call utils.tessa_change_state(v_card_id, 'На исполнении', 'StartWithoutSigning');
/*
v_state := utils.f_get_tessa_card_state(v_card_id)->>'stateName';
if v_state = 'На исполнении' then
for i in 1..3 loop
if utils.send_signal_to_tessa(v_card_id, 'StartWithoutSigning') then
insert into logs.tessa_send_signal_log (start_state, signal, card_id, success)
values ('На исполнении', 'StartWithoutSigning', v_card_id, true);
return v_card_id;
end if;
perform pg_sleep(3); -- Засыпаем на 3 секунды и пробуем еще разок
end loop;
insert into logs.tessa_send_signal_log (start_state, signal, card_id, success)
values ('На исполнении', 'StartWithoutSigning', v_card_id, false);
end if;
*/
end if;
end if;
return v_card_id;
END;
$function$
;
