--------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_place_id" INTEGER; COMMENT ON COLUMN "oms"."person_info"."fact_place_id" IS 'Ид-р населенного пункта по фактическому месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_street_id" INTEGER; COMMENT ON COLUMN "oms"."person_info"."fact_street_id" IS 'Ид-р улицы по фактическому месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD CONSTRAINT "person_info_fk_fact_place" FOREIGN KEY ("fact_place_id") REFERENCES "oms"."place"("place_id") ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD CONSTRAINT "person_info_fk_fact_street" FOREIGN KEY ("fact_street_id") REFERENCES "oms"."street"("street_id") ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_postcode" VARCHAR(10); COMMENT ON COLUMN "oms"."person_info"."fact_postcode" IS 'Почтовый индекс по месту фактического жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_house_num" VARCHAR(15); COMMENT ON COLUMN "oms"."person_info"."fact_house_num" IS 'Номер дома по фактическому месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_house_letter" VARCHAR(15); COMMENT ON COLUMN "oms"."person_info"."fact_house_letter" IS 'Литера дома по фактическому месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_building_num" VARCHAR(15); COMMENT ON COLUMN "oms"."person_info"."fact_building_num" IS 'Номер строения по фактическому месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_flat_num" VARCHAR(15); COMMENT ON COLUMN "oms"."person_info"."fact_flat_num" IS 'Номер квартиры по фактическому месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "fact_flat_letter" VARCHAR(15); COMMENT ON COLUMN "oms"."person_info"."fact_flat_letter" IS 'Литера квартиры по фактическому месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "reg_date" DATE; COMMENT ON COLUMN "oms"."person_info"."reg_date" IS 'Дата регистрации по месту жительства'; --------------- SQL --------------- ALTER TABLE "oms"."person_info" ADD COLUMN "loc_reg_date" DATE; COMMENT ON COLUMN "oms"."person_info"."loc_reg_date" IS 'Дата регистрации по месту нахождения (пребывания)'; --------------- SQL --------------- DROP FUNCTION "oms"."check_change_person_info"(_person_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar); CREATE OR REPLACE FUNCTION "oms"."check_change_person_info" (_person_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _workshop varchar, _fact_place_id integer, _fact_street_id integer, _fact_postcode varchar, _fact_house_num varchar, _fact_house_letter varchar, _fact_building_num varchar, _fact_flat_num varchar, _fact_flat_letter varchar, _reg_date date, _loc_reg_date date) RETURNS boolean AS $body$ DECLARE cnt INTEGER; cfgSaveVal VARCHAR; retVal VARCHAR; BEGIN SELECT COUNT(*) INTO cnt FROM oms.person p INNER JOIN oms.person_info pi ON p.person_info_id = pi.person_info_id WHERE p.person_id = _person_id AND pi.last_name = _last_name AND pi.first_name = _first_name AND cmp(pi.patronymic, _patronymic) AND pi.sex = _sex AND cmp(pi.inn, _inn) AND cmp(pi.snils, _snils) AND cmp(pi.nation_code, _nation_code) AND cmp(pi.birth_day, _birth_day) AND cmp(pi.parent_person_id, _parent_person_id) AND cmp(pi.doc_type_id, _doc_type_id) AND cmp(pi.doc_series, _doc_series) AND cmp(pi.doc_number, _doc_number) AND cmp(pi.doc_issue, _doc_issue) AND cmp(pi.doc_expire, _doc_expire) AND cmp(pi.doc_issue_name, _doc_issue_name) AND cmp(pi.birth_place, _birth_place) AND cmp(pi.postcode, _postcode) AND cmp(pi.place_id, _place_id) AND cmp(pi.street_id, _street_id) AND cmp(pi.house_num, _house_num) AND cmp(pi.house_letter, _house_letter) AND cmp(pi.building_num, _building_num) AND cmp(pi.flat_num, _flat_num) AND cmp(pi.flat_letter, _flat_letter) AND cmp(pi.loc_place_id, _loc_place_id) AND cmp(pi.loc_street_id, _loc_street_id) AND cmp(pi.loc_house_num, _loc_house_num) AND cmp(pi.loc_house_letter, _loc_house_letter) AND cmp(pi.loc_building_num, _loc_building_num) AND cmp(pi.loc_flat_num, _loc_flat_num) AND cmp(pi.loc_flat_letter, _loc_flat_letter) AND cmp(pi.work_ticket_num, _work_ticket_num) AND cmp(pi.work_ticket_date, _work_ticket_date) AND cmp(pi.work_ticket_expire_date, _work_ticket_expire_date) AND cmp(pi.work_ticket_issue, _work_ticket_issue) AND cmp(pi.death_day, _death_day) AND cmp(pi.death_doc_series, _death_doc_series) AND cmp(pi.death_doc_num, _death_doc_num) AND cmp(pi.death_doc_date, _death_doc_date) AND cmp(pi.death_doc_date, _death_doc_date) AND cmp(pi.workshop, _workshop) AND cmp(pi.fact_place_id, _fact_place_id) AND cmp(pi.fact_street_id, _fact_street_id) AND cmp(pi.fact_postcode, _fact_postcode) AND cmp(pi.fact_house_num, _fact_house_num) AND cmp(pi.fact_house_letter, _fact_house_letter) AND cmp(pi.fact_building_num, _fact_building_num) AND cmp(pi.fact_flat_num, _fact_flat_num) AND cmp(pi.fact_flat_letter, _fact_flat_letter) AND cmp(pi.reg_date, _reg_date) AND cmp(pi.loc_reg_date, _loc_reg_date); IF cnt = 1 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; COMMENT ON FUNCTION "oms"."check_change_person_info"(_person_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _workshop varchar, _fact_place_id integer, _fact_street_id integer, _fact_postcode varchar, _fact_house_num varchar, _fact_house_letter varchar, _fact_building_num varchar, _fact_flat_num varchar, _fact_flat_letter varchar, _reg_date date, _loc_reg_date date) IS 'Поиск различий в персональной информации'; --------------- SQL --------------- DROP FUNCTION "oms"."check_change_person_info"(_person_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _workshop varchar, _fact_place_id integer, _fact_street_id integer, _fact_postcode varchar, _fact_house_num varchar, _fact_house_letter varchar, _fact_building_num varchar, _fact_flat_num varchar, _fact_flat_letter varchar, _reg_date date, _loc_reg_date date); CREATE OR REPLACE FUNCTION "oms"."check_change_person_info" (_person_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _fact_place_id integer, _fact_street_id integer, _fact_postcode varchar, _fact_house_num varchar, _fact_house_letter varchar, _fact_building_num varchar, _fact_flat_num varchar, _fact_flat_letter varchar, _reg_date date, _loc_reg_date date) RETURNS boolean AS $body$ DECLARE cnt INTEGER; cfgSaveVal VARCHAR; retVal VARCHAR; BEGIN SELECT COUNT(*) INTO cnt FROM oms.person p INNER JOIN oms.person_info pi ON p.person_info_id = pi.person_info_id WHERE p.person_id = _person_id AND pi.last_name = _last_name AND pi.first_name = _first_name AND cmp(pi.patronymic, _patronymic) AND pi.sex = _sex AND cmp(pi.inn, _inn) AND cmp(pi.snils, _snils) AND cmp(pi.nation_code, _nation_code) AND cmp(pi.birth_day, _birth_day) AND cmp(pi.parent_person_id, _parent_person_id) AND cmp(pi.doc_type_id, _doc_type_id) AND cmp(pi.doc_series, _doc_series) AND cmp(pi.doc_number, _doc_number) AND cmp(pi.doc_issue, _doc_issue) AND cmp(pi.doc_expire, _doc_expire) AND cmp(pi.doc_issue_name, _doc_issue_name) AND cmp(pi.birth_place, _birth_place) AND cmp(pi.postcode, _postcode) AND cmp(pi.place_id, _place_id) AND cmp(pi.street_id, _street_id) AND cmp(pi.house_num, _house_num) AND cmp(pi.house_letter, _house_letter) AND cmp(pi.building_num, _building_num) AND cmp(pi.flat_num, _flat_num) AND cmp(pi.flat_letter, _flat_letter) AND cmp(pi.loc_place_id, _loc_place_id) AND cmp(pi.loc_street_id, _loc_street_id) AND cmp(pi.loc_house_num, _loc_house_num) AND cmp(pi.loc_house_letter, _loc_house_letter) AND cmp(pi.loc_building_num, _loc_building_num) AND cmp(pi.loc_flat_num, _loc_flat_num) AND cmp(pi.loc_flat_letter, _loc_flat_letter) AND cmp(pi.work_ticket_num, _work_ticket_num) AND cmp(pi.work_ticket_date, _work_ticket_date) AND cmp(pi.work_ticket_expire_date, _work_ticket_expire_date) AND cmp(pi.work_ticket_issue, _work_ticket_issue) AND cmp(pi.death_day, _death_day) AND cmp(pi.death_doc_series, _death_doc_series) AND cmp(pi.death_doc_num, _death_doc_num) AND cmp(pi.death_doc_date, _death_doc_date) AND cmp(pi.death_doc_date, _death_doc_date) AND cmp(pi.fact_place_id, _fact_place_id) AND cmp(pi.fact_street_id, _fact_street_id) AND cmp(pi.fact_postcode, _fact_postcode) AND cmp(pi.fact_house_num, _fact_house_num) AND cmp(pi.fact_house_letter, _fact_house_letter) AND cmp(pi.fact_building_num, _fact_building_num) AND cmp(pi.fact_flat_num, _fact_flat_num) AND cmp(pi.fact_flat_letter, _fact_flat_letter) AND cmp(pi.reg_date, _reg_date) AND cmp(pi.loc_reg_date, _loc_reg_date); IF cnt = 1 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; COMMENT ON FUNCTION "oms"."check_change_person_info"(_person_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _fact_place_id integer, _fact_street_id integer, _fact_postcode varchar, _fact_house_num varchar, _fact_house_letter varchar, _fact_building_num varchar, _fact_flat_num varchar, _fact_flat_letter varchar, _reg_date date, _loc_reg_date date) IS 'Поиск различий в персональной информации'; --------------- SQL --------------- DROP FUNCTION "import"."load_person_info"(_insured_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_postcode varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _reason_id integer, _clock_num varchar, _enrolmen_date date, _dismissal_date date, _dismissal_order_number varchar, _dismissal_order_date date, _work_contract_num varchar, _work_contract_date date, _work_contract_expire_date date, _department varchar, _license_num varchar, _license_expire_date date, _license_date date, _license_issue varchar, _person_id integer, _rec_id varchar, _protocol_id integer, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _policy_num integer, _workshop varchar); CREATE OR REPLACE FUNCTION "import"."load_person_info" (_insured_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_postcode varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _reason_id integer, _clock_num varchar, _enrolmen_date date, _dismissal_date date, _dismissal_order_number varchar, _dismissal_order_date date, _work_contract_num varchar, _work_contract_date date, _work_contract_expire_date date, _department varchar, _license_num varchar, _license_expire_date date, _license_date date, _license_issue varchar, _person_id integer, _rec_id varchar, _protocol_id integer, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _policy_num integer, _workshop varchar, _fact_place_id integer, _fact_street_id integer, _fact_postcode varchar, _fact_house_num varchar, _fact_house_letter varchar, _fact_building_num varchar, _fact_flat_num varchar, _fact_flat_letter varchar, _reg_date date, _loc_reg_date date) RETURNS integer AS $body$ DECLARE curr_person_id INTEGER; need_create_person_info BOOLEAN; need_create_insured_person_info BOOLEAN; need_create_policy BOOLEAN; cnt INTEGER; foms_reason VARCHAR; prev_policy_id INTEGER; -- Последний активный полис в той же организации curr_policy_id INTEGER; -- Новосоздаваеммый полис last_policy_id INTEGER; -- Предыдущий полис застрахованного curr_person_info_id INTEGER; curr_insured_person_info_id INTEGER; cur_policy_num INTEGER; log_action_code VARCHAR; log_message VARCHAR; _deactivate_date DATE; curr_deactivate_date DATE; BEGIN curr_person_id := NULL; need_create_person_info := FALSE; need_create_insured_person_info := FALSE; need_create_policy := FALSE; IF _protocol_id IS NULL THEN log_action_code := 'INPUT'; ELSE log_action_code := 'LOAD'; END IF; -- Получаем причину изменения записи SELECT trim(e_b_code) INTO foms_reason FROM oms.reason WHERE reason_id = _reason_id; -- IF _person_id IS NULL THEN -- Пытаемся найти персону SELECT import.search_person(_first_name, _last_name, _patronymic, _sex, _birth_day, _snils, _inn, _place_id, _street_id, _doc_type_id, _doc_series, _doc_number, _doc_issue) INTO curr_person_id; -- Создаем персону если такой нет в БД IF curr_person_id IS NULL THEN INSERT INTO oms.person (insured_id) VALUES (_insured_id); curr_person_id := lastval(); need_create_person_info := TRUE; need_create_policy := TRUE; -- Добавляем запись в журнал PERFORM import.add_to_log_person(curr_person_id, log_action_code, 'Создан работник', foms_reason); END IF; ELSE curr_person_id := _person_id; END IF; -- Получаем ид-р текущего полиса, если такой вообще существует prev_policy_id := oms.get_active_policy_id(_insured_id, curr_person_id); -- Получаем ссылку на информацию по человеку, если ее можно получить IF prev_policy_id IS NULL THEN SELECT person_info_id INTO curr_person_info_id FROM oms.person WHERE person_id = curr_person_id; SELECT insured_person_info_id INTO curr_insured_person_info_id FROM oms.insured_person WHERE person_id = curr_person_id AND insured_id = _insured_id; ELSE SELECT person_info_id INTO curr_person_info_id FROM oms.policy WHERE policy_id = prev_policy_id; SELECT insured_person_info_id INTO curr_insured_person_info_id FROM oms.policy WHERE policy_id = prev_policy_id; END IF; -- Ищем изменения в персональных данных, если персона не была только что создана IF need_create_person_info = FALSE THEN need_create_person_info := oms.check_change_person_info(curr_person_id, _last_name, _first_name, _patronymic, _sex, _inn, _snils, _nation_code, _birth_day, _parent_person_id, _doc_type_id, _doc_series, _doc_number, _doc_issue, _doc_expire, _doc_issue_name, _birth_place, _postcode, _place_id, _street_id, _house_num, _house_letter, _building_num, _flat_num, _flat_letter, _loc_place_id, _loc_street_id, _loc_house_num, _loc_house_letter, _loc_building_num, _loc_flat_num, _loc_flat_letter, _work_ticket_num, _work_ticket_date, _work_ticket_expire_date, _work_ticket_issue, _death_day, _death_doc_series, _death_doc_num, _death_doc_date, _death_doc_issue, _workshop, _fact_place_id, _fact_street_id, _fact_postcode, _fact_house_num, _fact_house_letter, _fact_building_num, _fact_flat_num, _fact_flat_letter, _reg_date, _loc_reg_date); IF foms_reason = '01' OR foms_reason = '18' THEN need_create_person_info := FALSE; -- Не создаем исторических персональных данных, если человек уволен, потерял полис, или необосновано включен в список END IF; END IF; -- Если работника удаляем в связи с неправомочным добавлением, то исторические данные его не трогаем IF foms_reason = '18' THEN need_create_person_info := FALSE; END IF; -- Создаем запись в исторических данных если это необходимо IF need_create_person_info = TRUE THEN INSERT INTO oms.person_info (person_id, last_name, first_name, patronymic, sex, inn, snils, nation_code, birth_day, parent_person_id, doc_type_id, doc_series, doc_number, doc_issue, doc_expire, doc_issue_name, birth_place, postcode, place_id, street_id, house_num, house_letter, building_num, flat_num, flat_letter, loc_postcode, loc_place_id, loc_street_id, loc_house_num, loc_house_letter, loc_building_num, loc_flat_num, loc_flat_letter, work_ticket_num, work_ticket_date, work_ticket_expire_date, work_ticket_issue, death_day, reason_id, death_doc_series, death_doc_num, death_doc_date, death_doc_issue, fact_place_id, fact_street_id, fact_postcode, fact_house_num, fact_house_letter, fact_building_num, fact_flat_num, fact_flat_letter, reg_date, loc_reg_date) VALUES( curr_person_id, _last_name, _first_name, _patronymic, _sex, _inn, _snils, _nation_code, _birth_day, _parent_person_id, _doc_type_id, _doc_series, _doc_number, _doc_issue, _doc_expire, _doc_issue_name, _birth_place, _postcode, _place_id, _street_id, _house_num, _house_letter, _building_num, _flat_num, _flat_letter, _loc_postcode, _loc_place_id, _loc_street_id, _loc_house_num, _loc_house_letter, _loc_building_num, _loc_flat_num, _loc_flat_letter, _work_ticket_num, _work_ticket_date, _work_ticket_expire_date, _work_ticket_issue, _death_day, _reason_id, _death_doc_series, _death_doc_num, _death_doc_date, _death_doc_issue, _fact_place_id, _fact_street_id, _fact_postcode, _fact_house_num, _fact_house_letter, _fact_building_num, _fact_flat_num, _fact_flat_letter, _reg_date, _loc_reg_date); curr_person_info_id := lastval(); END IF; -- Определяем существует ли застрахованный сотрудник SELECT COUNT(*) INTO cnt FROM oms.insured_person WHERE insured_id = _insured_id AND person_id = curr_person_id; -- Если застрахованный сотрудник не найден, создаем его IF cnt = 0 THEN INSERT INTO oms.insured_person (insured_id, person_id) VALUES (_insured_id, curr_person_id); need_create_insured_person_info := TRUE; END IF; -- Ищем изменения в данных застрахованного, если он не был только что создан IF need_create_insured_person_info = FALSE THEN need_create_insured_person_info := oms.check_change_insured_person_info(_insured_id, curr_person_id, _clock_num, _enrolmen_date, _dismissal_date, _dismissal_order_number, _dismissal_order_date, _work_contract_num, _work_contract_date, _work_contract_expire_date, _department, _license_num, _license_expire_date, _license_date, _license_issue, _workshop); END IF; -- Если работника удаляем в связи с неправомочным добавлением, то исторические данные его не трогаем IF foms_reason = '18' THEN need_create_insured_person_info := FALSE; END IF; -- Создаем запись в исторических данных застрахованного сотрудника если это необходимо IF need_create_insured_person_info = TRUE THEN INSERT INTO oms.insured_person_info (insured_id, person_id, clock_num, enrolmen_date, dismissal_date, dismissal_order_number, dismissal_order_date, work_contract_num, work_contract_date, work_contract_expire_date, department, license_num, license_expire_date, license_date, license_issue, reason_id, workshop) VALUES( _insured_id, curr_person_id, _clock_num, _enrolmen_date, _dismissal_date, _dismissal_order_number, _dismissal_order_date, _work_contract_num, _work_contract_date, _work_contract_expire_date, _department, _license_num, _license_expire_date, _license_date, _license_issue, _reason_id, _workshop); curr_insured_person_info_id := lastval(); END IF; -- Создаем версию информации о человеке IF need_create_insured_person_info = TRUE OR need_create_person_info = TRUE THEN INSERT INTO oms.person_ver(person_id, person_info_id, insured_person_info_id, reason_id) VALUES(curr_person_id, curr_person_info_id, curr_insured_person_info_id, _reason_id); END IF; IF prev_policy_id IS NULL THEN -- Получаем ид-р последнего полиса, если такой вообще существует prev_policy_id := oms.get_last_policy_id(_insured_id, curr_person_id); END IF; ---------------------------------------------------------------------------- -- В зависимости от ситуации создаем или деактивируем полиса IF foms_reason = '02' THEN -- Прием на работу -- Создаем полис curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, 'Создан полис в связи с приемом на работу', NULL); END IF; ELSEIF foms_reason IN ('01', '06', '16') THEN -- Увольнение или смерть -- Добавляем запись в журнал log_message := import.get_msg_for_log(foms_reason, NULL); PERFORM import.add_to_log_person(curr_person_id, log_action_code, log_message, foms_reason); -- Вычисляем дату деактивации IF _dismissal_date IS NULL THEN _deactivate_date := NOW(); ELSE _deactivate_date := _dismissal_date; END IF; last_policy_id := prev_policy_id; -- Проверяем, а не деактивирован ли уже данный полис SELECT p.deactivate_date INTO curr_deactivate_date FROM oms.policy p WHERE p.policy_id = last_policy_id; IF curr_deactivate_date IS NULL THEN -- Если полис еще не деактивирован -- Вносим изменения в полис UPDATE oms.policy SET de_op_code = foms_reason, status = 1, deactivate_date = _deactivate_date, modify_date = NOW() WHERE policy_id = last_policy_id; -- Добавляем запись в журнал log_message := import.get_msg_for_log(foms_reason, '04'); PERFORM import.add_to_log_policy( curr_person_id, prev_policy_id, log_action_code, foms_reason, '04', log_message, 1); END IF; -- Деактивируем все другие полисы данного человека у данного страхователя -- В журнал данные не заносим, так как это не выданные полисы UPDATE oms.policy SET de_op_code = foms_reason, status = 1, deactivate_date = _deactivate_date, modify_date = NOW() WHERE person_id = _person_id AND insured_id = _insured_id AND deactivate_date IS NULL; IF _protocol_id IS NOT NULL THEN INSERT INTO oms.protocol_policy (protocol_id, policy_id, action) VALUES (_protocol_id, last_policy_id, 1); END IF; ELSEIF foms_reason IN ('14') THEN -- Изменения не затрагивающие реквизитов полиса -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, prev_policy_id, log_action_code, foms_reason, '02', 'Изменения не затрагивающие реквизиты полиса', NULL); -- Модифицируем полис UPDATE oms.policy SET person_info_id = curr_person_info_id, insured_person_info_id = curr_insured_person_info_id WHERE policy_id = prev_policy_id; ELSEIF foms_reason IN ('03', '04', '05', '07', '08', '11', '20', '21') THEN log_message := import.get_msg_for_log(foms_reason, NULL); IF log_message!='' THEN -- Добавляем запись в журнал PERFORM import.add_to_log_person(curr_person_id, log_action_code, log_message, foms_reason); END IF; last_policy_id := prev_policy_id; -- Указываем причину деактивации для старого полиса UPDATE oms.policy SET de_op_code = foms_reason WHERE policy_id = last_policy_id; -- Создаем новый полис curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.set_ref_policy(curr_policy_id, last_policy_id); -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, import.get_msg_for_log(foms_reason, '03'), NULL); END IF; ELSEIF foms_reason IN ('28', '26') THEN -- Перепечатка last_policy_id := prev_policy_id; -- Указываем причину деактивации для старого полиса UPDATE oms.policy SET de_op_code = foms_reason WHERE policy_id = last_policy_id; -- Создаем новый полис curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.set_ref_policy(curr_policy_id, last_policy_id); -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, import.get_msg_for_log(foms_reason, '03'), NULL); END IF; ELSEIF foms_reason = '18' THEN -- Удаление в связи с неверным заполнением last_policy_id := prev_policy_id; -- Вносим изменения в полис UPDATE oms.policy SET de_op_code = '18', status = 1, deactivate_date = NOW() WHERE policy_id = last_policy_id; -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, prev_policy_id, log_action_code, foms_reason, '04', 'Полис деактивирован в связи с неверным заполнением', 1); ELSEIF foms_reason = '17' THEN -- Получаем номер старого полиса SELECT policy_number INTO cur_policy_num FROM oms.policy WHERE policy_id = prev_policy_id; last_policy_id := prev_policy_id; -- Добавляем запись в журнал PERFORM import.add_to_log_person(curr_person_id, log_action_code, 'Изменение информации в связи с опечатками', foms_reason); -- Указываем причину деактивации для старого полиса UPDATE oms.policy SET de_op_code = '06' WHERE policy_id = last_policy_id; -- Создаем новый полис и делам ссылку на связанный curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.set_ref_policy(curr_policy_id, last_policy_id); -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, 'Создан новый полис, потому что в старом были опечатки', NULL); END IF; END IF; -- Запись в лог протокола IF _protocol_id IS NOT NULL THEN PERFORM import.add_to_protocol_log(_protocol_id, _rec_id, curr_person_id, foms_reason, _last_name || ' ' || _first_name || ' ' || _patronymic, _inn, _snils, _birth_day, _clock_num); END IF; RETURN curr_person_id; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; --------------- SQL --------------- DROP FUNCTION "oms"."save_person"(id integer, vlast_name varchar, vfirst_name varchar, vpatronymic varchar, vsex varchar, vinn varchar, vsnils varchar, vbirth_day date, vparent_person_id integer, vdeath_day date, vdoc_type_id integer, vdoc_number varchar, vdoc_series varchar, vdoc_issue date, vdoc_expire date, vdoc_issue_name varchar, vpostcode varchar, vplace_id integer, vstreet_id integer, vhouse_num varchar, vhouse_letter char, vbuilding_num varchar, vflat_num varchar, vflat_letter char, vwork_ticket_num varchar, vwork_ticket_date date, vwork_ticket_expire_date date, vwork_ticket_issue varchar, vjob_less varchar, vdental_lpu integer, varea_lpu integer, vwoman_lpu integer, vchild_lpu integer, vinsured_id integer, vdismissal_date date, vdismissal_order_number varchar, vdismissal_order_date date, vclock_num varchar, vwork_contract_num varchar, vwork_contract_date date, vwork_contract_expire_date date, vdepartment varchar, vlicense_num varchar, vlicense_expire_date date, vlicense_date date, vlicense_issue varchar, vreason_id integer, vpolicy integer, vloc_house_num varchar, vloc_house_letter char, vloc_building_num varchar, vloc_flat_num varchar, vloc_flat_letter char, vloc_place_id integer, vloc_street_id integer, vloc_postcode varchar, vnation_code char, vproxy_id integer, flag integer, vdeath_doc_series varchar, vdeath_doc_num varchar, vdeath_doc_date date, vdeath_doc_issue varchar, vworkshop varchar, vbirth_place varchar); CREATE OR REPLACE FUNCTION "oms"."save_person" (id integer, vlast_name varchar, vfirst_name varchar, vpatronymic varchar, vsex varchar, vinn varchar, vsnils varchar, vbirth_day date, vparent_person_id integer, vdeath_day date, vdoc_type_id integer, vdoc_number varchar, vdoc_series varchar, vdoc_issue date, vdoc_expire date, vdoc_issue_name varchar, vpostcode varchar, vplace_id integer, vstreet_id integer, vhouse_num varchar, vhouse_letter char, vbuilding_num varchar, vflat_num varchar, vflat_letter char, vwork_ticket_num varchar, vwork_ticket_date date, vwork_ticket_expire_date date, vwork_ticket_issue varchar, vjob_less varchar, vdental_lpu integer, varea_lpu integer, vwoman_lpu integer, vchild_lpu integer, vinsured_id integer, vdismissal_date date, vdismissal_order_number varchar, vdismissal_order_date date, vclock_num varchar, vwork_contract_num varchar, vwork_contract_date date, vwork_contract_expire_date date, vdepartment varchar, vlicense_num varchar, vlicense_expire_date date, vlicense_date date, vlicense_issue varchar, vreason_id integer, vpolicy integer, vloc_house_num varchar, vloc_house_letter char, vloc_building_num varchar, vloc_flat_num varchar, vloc_flat_letter char, vloc_place_id integer, vloc_street_id integer, vloc_postcode varchar, vnation_code char, vproxy_id integer, flag integer, vdeath_doc_series varchar, vdeath_doc_num varchar, vdeath_doc_date date, vdeath_doc_issue varchar, vworkshop varchar, vbirth_place varchar, vfact_place_id integer, vfact_street_id integer, vfact_postcode varchar, vfact_house_num varchar, vfact_house_letter varchar, vfact_building_num varchar, vfact_flat_num varchar, vfact_flat_letter varchar, vreg_date date, vloc_reg_date date) RETURNS boolean AS $body$ DECLARE personId INTEGER; vperson_id INTEGER; BEGIN IF id = 0 THEN vperson_id = NULL; ELSE vperson_id = id; END IF; personId := import.load_person_info(vinsured_id, vlast_name, vfirst_name, vpatronymic, vsex, vinn, vsnils, vnation_code, vbirth_day, vparent_person_id, vdoc_type_id, vdoc_series, vdoc_number, vdoc_issue, vdoc_expire, vdoc_issue_name, vbirth_place, vpostcode, vplace_id, vstreet_id, vhouse_num, vhouse_letter, vbuilding_num, vflat_num, vflat_letter, vloc_postcode, vloc_place_id, vloc_street_id, vloc_house_num, vloc_house_letter, vloc_building_num, vloc_flat_num, vloc_flat_letter, vwork_ticket_num, vwork_ticket_date, vwork_ticket_expire_date, vwork_ticket_issue, vdeath_day, vreason_id, vclock_num, NULL, vdismissal_date, vdismissal_order_number, vdismissal_order_date, vwork_contract_num, vwork_contract_date, vwork_contract_expire_date, vdepartment, vlicense_num, vlicense_expire_date, vlicense_date, vlicense_issue, vperson_id, NULL, NULL, vdeath_doc_series, vdeath_doc_num, vdeath_doc_date, vdeath_doc_issue, vpolicy, vworkshop, vfact_place_id, vfact_street_id, vfact_postcode, vfact_house_num, vfact_house_letter, vfact_building_num, vfact_flat_num, vfact_flat_letter, vreg_date, vloc_reg_date); RETURN TRUE; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; --------------- SQL --------------- CREATE OR REPLACE FUNCTION "import"."load_person_info" (_insured_id integer, _last_name varchar, _first_name varchar, _patronymic varchar, _sex char, _inn varchar, _snils varchar, _nation_code char, _birth_day date, _parent_person_id integer, _doc_type_id integer, _doc_series varchar, _doc_number varchar, _doc_issue date, _doc_expire date, _doc_issue_name varchar, _birth_place varchar, _postcode varchar, _place_id integer, _street_id integer, _house_num varchar, _house_letter varchar, _building_num varchar, _flat_num varchar, _flat_letter varchar, _loc_postcode varchar, _loc_place_id integer, _loc_street_id integer, _loc_house_num varchar, _loc_house_letter varchar, _loc_building_num varchar, _loc_flat_num varchar, _loc_flat_letter varchar, _work_ticket_num varchar, _work_ticket_date date, _work_ticket_expire_date date, _work_ticket_issue varchar, _death_day date, _reason_id integer, _clock_num varchar, _enrolmen_date date, _dismissal_date date, _dismissal_order_number varchar, _dismissal_order_date date, _work_contract_num varchar, _work_contract_date date, _work_contract_expire_date date, _department varchar, _license_num varchar, _license_expire_date date, _license_date date, _license_issue varchar, _person_id integer, _rec_id varchar, _protocol_id integer, _death_doc_series varchar, _death_doc_num varchar, _death_doc_date date, _death_doc_issue varchar, _policy_num integer, _workshop varchar, _fact_place_id integer, _fact_street_id integer, _fact_postcode varchar, _fact_house_num varchar, _fact_house_letter varchar, _fact_building_num varchar, _fact_flat_num varchar, _fact_flat_letter varchar, _reg_date date, _loc_reg_date date) RETURNS integer AS $body$ DECLARE curr_person_id INTEGER; need_create_person_info BOOLEAN; need_create_insured_person_info BOOLEAN; need_create_policy BOOLEAN; cnt INTEGER; foms_reason VARCHAR; prev_policy_id INTEGER; -- Последний активный полис в той же организации curr_policy_id INTEGER; -- Новосоздаваеммый полис last_policy_id INTEGER; -- Предыдущий полис застрахованного curr_person_info_id INTEGER; curr_insured_person_info_id INTEGER; cur_policy_num INTEGER; log_action_code VARCHAR; log_message VARCHAR; _deactivate_date DATE; curr_deactivate_date DATE; BEGIN curr_person_id := NULL; need_create_person_info := FALSE; need_create_insured_person_info := FALSE; need_create_policy := FALSE; IF _protocol_id IS NULL THEN log_action_code := 'INPUT'; ELSE log_action_code := 'LOAD'; END IF; -- Получаем причину изменения записи SELECT trim(e_b_code) INTO foms_reason FROM oms.reason WHERE reason_id = _reason_id; -- IF _person_id IS NULL THEN -- Пытаемся найти персону SELECT import.search_person(_first_name, _last_name, _patronymic, _sex, _birth_day, _snils, _inn, _place_id, _street_id, _doc_type_id, _doc_series, _doc_number, _doc_issue) INTO curr_person_id; -- Создаем персону если такой нет в БД IF curr_person_id IS NULL THEN INSERT INTO oms.person (insured_id) VALUES (_insured_id); curr_person_id := lastval(); need_create_person_info := TRUE; need_create_policy := TRUE; -- Добавляем запись в журнал PERFORM import.add_to_log_person(curr_person_id, log_action_code, 'Создан работник', foms_reason); END IF; ELSE curr_person_id := _person_id; END IF; -- Получаем ид-р текущего полиса, если такой вообще существует prev_policy_id := oms.get_active_policy_id(_insured_id, curr_person_id); -- Получаем ссылку на информацию по человеку, если ее можно получить IF prev_policy_id IS NULL THEN SELECT person_info_id INTO curr_person_info_id FROM oms.person WHERE person_id = curr_person_id; SELECT insured_person_info_id INTO curr_insured_person_info_id FROM oms.insured_person WHERE person_id = curr_person_id AND insured_id = _insured_id; ELSE SELECT person_info_id INTO curr_person_info_id FROM oms.policy WHERE policy_id = prev_policy_id; SELECT insured_person_info_id INTO curr_insured_person_info_id FROM oms.policy WHERE policy_id = prev_policy_id; END IF; -- Ищем изменения в персональных данных, если персона не была только что создана IF need_create_person_info = FALSE THEN need_create_person_info := oms.check_change_person_info(curr_person_id, _last_name, _first_name, _patronymic, _sex, _inn, _snils, _nation_code, _birth_day, _parent_person_id, _doc_type_id, _doc_series, _doc_number, _doc_issue, _doc_expire, _doc_issue_name, _birth_place, _postcode, _place_id, _street_id, _house_num, _house_letter, _building_num, _flat_num, _flat_letter, _loc_place_id, _loc_street_id, _loc_house_num, _loc_house_letter, _loc_building_num, _loc_flat_num, _loc_flat_letter, _work_ticket_num, _work_ticket_date, _work_ticket_expire_date, _work_ticket_issue, _death_day, _death_doc_series, _death_doc_num, _death_doc_date, _death_doc_issue, _fact_place_id, _fact_street_id, _fact_postcode, _fact_house_num, _fact_house_letter, _fact_building_num, _fact_flat_num, _fact_flat_letter, _reg_date, _loc_reg_date); IF foms_reason = '01' OR foms_reason = '18' THEN need_create_person_info := FALSE; -- Не создаем исторических персональных данных, если человек уволен, потерял полис, или необосновано включен в список END IF; END IF; -- Если работника удаляем в связи с неправомочным добавлением, то исторические данные его не трогаем IF foms_reason = '18' THEN need_create_person_info := FALSE; END IF; -- Создаем запись в исторических данных если это необходимо IF need_create_person_info = TRUE THEN INSERT INTO oms.person_info (person_id, last_name, first_name, patronymic, sex, inn, snils, nation_code, birth_day, parent_person_id, doc_type_id, doc_series, doc_number, doc_issue, doc_expire, doc_issue_name, birth_place, postcode, place_id, street_id, house_num, house_letter, building_num, flat_num, flat_letter, loc_postcode, loc_place_id, loc_street_id, loc_house_num, loc_house_letter, loc_building_num, loc_flat_num, loc_flat_letter, work_ticket_num, work_ticket_date, work_ticket_expire_date, work_ticket_issue, death_day, reason_id, death_doc_series, death_doc_num, death_doc_date, death_doc_issue, fact_place_id, fact_street_id, fact_postcode, fact_house_num, fact_house_letter, fact_building_num, fact_flat_num, fact_flat_letter, reg_date, loc_reg_date) VALUES( curr_person_id, _last_name, _first_name, _patronymic, _sex, _inn, _snils, _nation_code, _birth_day, _parent_person_id, _doc_type_id, _doc_series, _doc_number, _doc_issue, _doc_expire, _doc_issue_name, _birth_place, _postcode, _place_id, _street_id, _house_num, _house_letter, _building_num, _flat_num, _flat_letter, _loc_postcode, _loc_place_id, _loc_street_id, _loc_house_num, _loc_house_letter, _loc_building_num, _loc_flat_num, _loc_flat_letter, _work_ticket_num, _work_ticket_date, _work_ticket_expire_date, _work_ticket_issue, _death_day, _reason_id, _death_doc_series, _death_doc_num, _death_doc_date, _death_doc_issue, _fact_place_id, _fact_street_id, _fact_postcode, _fact_house_num, _fact_house_letter, _fact_building_num, _fact_flat_num, _fact_flat_letter, _reg_date, _loc_reg_date); curr_person_info_id := lastval(); END IF; -- Определяем существует ли застрахованный сотрудник SELECT COUNT(*) INTO cnt FROM oms.insured_person WHERE insured_id = _insured_id AND person_id = curr_person_id; -- Если застрахованный сотрудник не найден, создаем его IF cnt = 0 THEN INSERT INTO oms.insured_person (insured_id, person_id) VALUES (_insured_id, curr_person_id); need_create_insured_person_info := TRUE; END IF; -- Ищем изменения в данных застрахованного, если он не был только что создан IF need_create_insured_person_info = FALSE THEN need_create_insured_person_info := oms.check_change_insured_person_info(_insured_id, curr_person_id, _clock_num, _enrolmen_date, _dismissal_date, _dismissal_order_number, _dismissal_order_date, _work_contract_num, _work_contract_date, _work_contract_expire_date, _department, _license_num, _license_expire_date, _license_date, _license_issue, _workshop); END IF; -- Если работника удаляем в связи с неправомочным добавлением, то исторические данные его не трогаем IF foms_reason = '18' THEN need_create_insured_person_info := FALSE; END IF; -- Создаем запись в исторических данных застрахованного сотрудника если это необходимо IF need_create_insured_person_info = TRUE THEN INSERT INTO oms.insured_person_info (insured_id, person_id, clock_num, enrolmen_date, dismissal_date, dismissal_order_number, dismissal_order_date, work_contract_num, work_contract_date, work_contract_expire_date, department, license_num, license_expire_date, license_date, license_issue, reason_id, workshop) VALUES( _insured_id, curr_person_id, _clock_num, _enrolmen_date, _dismissal_date, _dismissal_order_number, _dismissal_order_date, _work_contract_num, _work_contract_date, _work_contract_expire_date, _department, _license_num, _license_expire_date, _license_date, _license_issue, _reason_id, _workshop); curr_insured_person_info_id := lastval(); END IF; -- Создаем версию информации о человеке IF need_create_insured_person_info = TRUE OR need_create_person_info = TRUE THEN INSERT INTO oms.person_ver(person_id, person_info_id, insured_person_info_id, reason_id) VALUES(curr_person_id, curr_person_info_id, curr_insured_person_info_id, _reason_id); END IF; IF prev_policy_id IS NULL THEN -- Получаем ид-р последнего полиса, если такой вообще существует prev_policy_id := oms.get_last_policy_id(_insured_id, curr_person_id); END IF; ---------------------------------------------------------------------------- -- В зависимости от ситуации создаем или деактивируем полиса IF foms_reason = '02' THEN -- Прием на работу -- Создаем полис curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, 'Создан полис в связи с приемом на работу', NULL); END IF; ELSEIF foms_reason IN ('01', '06', '16') THEN -- Увольнение или смерть -- Добавляем запись в журнал log_message := import.get_msg_for_log(foms_reason, NULL); PERFORM import.add_to_log_person(curr_person_id, log_action_code, log_message, foms_reason); -- Вычисляем дату деактивации IF _dismissal_date IS NULL THEN _deactivate_date := NOW(); ELSE _deactivate_date := _dismissal_date; END IF; last_policy_id := prev_policy_id; -- Проверяем, а не деактивирован ли уже данный полис SELECT p.deactivate_date INTO curr_deactivate_date FROM oms.policy p WHERE p.policy_id = last_policy_id; IF curr_deactivate_date IS NULL THEN -- Если полис еще не деактивирован -- Вносим изменения в полис UPDATE oms.policy SET de_op_code = foms_reason, status = 1, deactivate_date = _deactivate_date, modify_date = NOW() WHERE policy_id = last_policy_id; -- Добавляем запись в журнал log_message := import.get_msg_for_log(foms_reason, '04'); PERFORM import.add_to_log_policy( curr_person_id, prev_policy_id, log_action_code, foms_reason, '04', log_message, 1); END IF; -- Деактивируем все другие полисы данного человека у данного страхователя -- В журнал данные не заносим, так как это не выданные полисы UPDATE oms.policy SET de_op_code = foms_reason, status = 1, deactivate_date = _deactivate_date, modify_date = NOW() WHERE person_id = _person_id AND insured_id = _insured_id AND deactivate_date IS NULL; IF _protocol_id IS NOT NULL THEN INSERT INTO oms.protocol_policy (protocol_id, policy_id, action) VALUES (_protocol_id, last_policy_id, 1); END IF; ELSEIF foms_reason IN ('14') THEN -- Изменения не затрагивающие реквизитов полиса -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, prev_policy_id, log_action_code, foms_reason, '02', 'Изменения не затрагивающие реквизиты полиса', NULL); -- Модифицируем полис UPDATE oms.policy SET person_info_id = curr_person_info_id, insured_person_info_id = curr_insured_person_info_id WHERE policy_id = prev_policy_id; ELSEIF foms_reason IN ('03', '04', '05', '07', '08', '11', '20', '21') THEN log_message := import.get_msg_for_log(foms_reason, NULL); IF log_message!='' THEN -- Добавляем запись в журнал PERFORM import.add_to_log_person(curr_person_id, log_action_code, log_message, foms_reason); END IF; last_policy_id := prev_policy_id; -- Указываем причину деактивации для старого полиса UPDATE oms.policy SET de_op_code = foms_reason WHERE policy_id = last_policy_id; -- Создаем новый полис curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.set_ref_policy(curr_policy_id, last_policy_id); -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, import.get_msg_for_log(foms_reason, '03'), NULL); END IF; ELSEIF foms_reason IN ('28', '26') THEN -- Перепечатка last_policy_id := prev_policy_id; -- Указываем причину деактивации для старого полиса UPDATE oms.policy SET de_op_code = foms_reason WHERE policy_id = last_policy_id; -- Создаем новый полис curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.set_ref_policy(curr_policy_id, last_policy_id); -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, import.get_msg_for_log(foms_reason, '03'), NULL); END IF; ELSEIF foms_reason = '18' THEN -- Удаление в связи с неверным заполнением last_policy_id := prev_policy_id; -- Вносим изменения в полис UPDATE oms.policy SET de_op_code = '18', status = 1, deactivate_date = NOW() WHERE policy_id = last_policy_id; -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, prev_policy_id, log_action_code, foms_reason, '04', 'Полис деактивирован в связи с неверным заполнением', 1); ELSEIF foms_reason = '17' THEN -- Получаем номер старого полиса SELECT policy_number INTO cur_policy_num FROM oms.policy WHERE policy_id = prev_policy_id; last_policy_id := prev_policy_id; -- Добавляем запись в журнал PERFORM import.add_to_log_person(curr_person_id, log_action_code, 'Изменение информации в связи с опечатками', foms_reason); -- Указываем причину деактивации для старого полиса UPDATE oms.policy SET de_op_code = '06' WHERE policy_id = last_policy_id; -- Создаем новый полис и делам ссылку на связанный curr_policy_id := import.create_policy(_insured_id, curr_person_id, _reason_id, _protocol_id, _policy_num, curr_person_info_id, curr_insured_person_info_id); IF curr_policy_id IS NOT NULL THEN PERFORM import.set_ref_policy(curr_policy_id, last_policy_id); -- Добавляем запись в журнал PERFORM import.add_to_log_policy( curr_person_id, curr_policy_id, log_action_code, foms_reason, NULL, 'Создан новый полис, потому что в старом были опечатки', NULL); END IF; END IF; -- Запись в лог протокола IF _protocol_id IS NOT NULL THEN PERFORM import.add_to_protocol_log(_protocol_id, _rec_id, curr_person_id, foms_reason, _last_name || ' ' || _first_name || ' ' || _patronymic, _inn, _snils, _birth_day, _clock_num); END IF; RETURN curr_person_id; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; --------------- SQL --------------- DROP FUNCTION "oms"."get_person_by_id"(_person_id integer, _person_ver_id integer, _user_id integer); --------------- SQL --------------- DROP VIEW "oms"."insured_person_view"; CREATE VIEW "oms"."insured_person_view" ( person_id, last_name, first_name, patronymic, sex, birth_day, inn, snils, parent_person_id, death_day, doc_type_id, doc_number, doc_series, doc_issue, doc_expire, doc_issue_name, postcode, house_num, house_letter, building_num, flat_num, flat_letter, loc_postcode, loc_place_id, loc_street_id, loc_house_num, loc_house_letter, loc_building_num, loc_flat_num, loc_flat_letter, work_ticket_num, work_ticket_date, work_ticket_expire_date, work_ticket_issue, job_less, reason_id, editor_login, modify_time, dismissal_date, dismissal_order_number, dismissal_order_date, clock_num, work_contract_num, work_contract_date, work_contract_expire_date, license_num, license_expire_date, license_date, license_issue, proxy_id, parent_person_id_name, insured, person_info_id, dental_lpu_name, area_lpu_name, woman_lpu_name, child_lpu_name, nation_code, nation_name, dental_lpu, area_lpu, woman_lpu, child_lpu, branch, insured_id, department, place, street, place_id, street_id, region_code, loc_place, loc_street, subregion, loc_subregion, loc_reg_code, death_doc_series, death_doc_num, death_doc_date, death_doc_issue, workshop, sub_name, sub_type, loc_sub_name, loc_sub_type, contract_number, contract_date, confirm_date, birth_place, fact_place_id, fact_street_id, fact_postcode, fact_house_num, fact_house_letter, fact_building_num, fact_flat_num, fact_flat_letter, reg_date, loc_reg_date, fact_place, fact_street, fact_subregion, fact_reg_code) AS SELECT p.person_id, pi.last_name, pi.first_name, pi.patronymic, pi.sex, pi.birth_day, pi.inn, pi.snils, pi.parent_person_id, pi.death_day, pi.doc_type_id, pi.doc_number, pi.doc_series, pi.doc_issue, pi.doc_expire, pi.doc_issue_name, pi.postcode, pi.house_num, pi.house_letter, pi.building_num, pi.flat_num, pi.flat_letter, pi.loc_postcode, pi.loc_place_id, pi.loc_street_id, pi.loc_house_num, pi.loc_house_letter, pi.loc_building_num, pi.loc_flat_num, pi.loc_flat_letter, pi.work_ticket_num, pi.work_ticket_date, pi.work_ticket_expire_date, pi.work_ticket_issue, pi.job_less, pi.reason_id, pi.editor_login, pi.modify_time, ipi.dismissal_date, ipi.dismissal_order_number, ipi.dismissal_order_date, ipi.clock_num, ipi.work_contract_num, ipi.work_contract_date, ipi.work_contract_expire_date, ipi.license_num, ipi.license_expire_date, ipi.license_date, ipi.license_issue, ipi.proxy_id, parent.last_name AS parent_person_id_name, i.insured_id AS insured, pi.person_info_id, dental.name_short AS dental_lpu_name, area.name_short AS area_lpu_name, woman.name_short AS woman_lpu_name, child.name_short AS child_lpu_name, pi.nation_code, nation.name AS nation_name, pi.dental_lpu, pi.area_lpu, pi.woman_lpu, pi.child_lpu, d.name AS branch, ii.name AS insured_id, ipi.department, (((pl.type)::text || ' '::text) || (pl.name)::text) AS place, (((st.prefix)::text || ' '::text) || (st.name)::text) AS street, pi.place_id, pi.street_id, pl.region_code, loc_pl.name AS loc_place, (((loc_st.prefix)::text || ' '::text) || (loc_st.name)::text) AS loc_street, subreg.name AS subregion, loc_subreg.name AS loc_subregion, loc_pl.region_code AS loc_reg_code, pi.death_doc_series, pi.death_doc_num, pi.death_doc_date, pi.death_doc_issue, ipi.workshop, pl.sub_name, pl.sub_type, loc_pl.sub_name AS loc_sub_name, loc_pl.sub_type AS loc_sub_type, i.contract_number, i.contract_date, ip.confirm_date, pi.birth_place, pi.fact_place_id, pi.fact_street_id, pi.fact_postcode, pi.fact_house_num, pi.fact_house_letter, pi.fact_building_num , pi.fact_flat_num, pi.fact_flat_letter, pi.reg_date, pi.loc_reg_date, fact_pl.name AS fact_place, (((fact_st.prefix)::text || ' '::text) || (fact_st.name)::text) AS fact_street, fact_subreg.name AS fact_subregion, fact_pl.region_code AS fact_reg_code FROM oms.person p JOIN oms.insured_person_info ipi ON p.insured_person_info_id = ipi.insured_person_info_id JOIN oms.insured_person ip ON ip.insured_id = ipi.insured_id AND ip.person_id = p.person_id JOIN oms.person_info pi ON p.person_info_id = pi.person_info_id JOIN oms.insured i ON i.insured_id = ipi.insured_id JOIN oms.insured_info ii ON ii.insured_info_id = i.insured_info_id LEFT JOIN oms.place pl ON pl.place_id = pi.place_id LEFT JOIN oms.street st ON st.street_id = pi.street_id LEFT JOIN oms.subregion subreg ON subreg.subregion_id = pl.subregion_id LEFT JOIN oms.place loc_pl ON loc_pl.place_id = pi.loc_place_id LEFT JOIN oms.street loc_st ON loc_st.street_id = pi.loc_street_id LEFT JOIN oms.subregion loc_subreg ON loc_subreg.subregion_id = loc_pl.subregion_id LEFT JOIN oms.place fact_pl ON fact_pl.place_id = pi.fact_place_id LEFT JOIN oms.street fact_st ON fact_st.street_id = pi.fact_street_id LEFT JOIN oms.subregion fact_subreg ON fact_subreg.subregion_id = fact_pl.subregion_id LEFT JOIN oms.person p2 ON pi.parent_person_id = p2.person_id LEFT JOIN oms.person_info parent ON parent.person_info_id = p2.person_info_id LEFT JOIN foms.lpu dental ON dental.nomlpu = pi.dental_lpu LEFT JOIN foms.lpu area ON area.nomlpu = pi.area_lpu LEFT JOIN foms.lpu woman ON woman.nomlpu = pi.woman_lpu LEFT JOIN foms.lpu child ON child.nomlpu = pi.child_lpu LEFT JOIN foms.nation nation ON nation.code = pi.nation_code LEFT JOIN qe_department d ON d.departmentid = i.branch_code; --------------- SQL --------------- DROP VIEW "oms"."insured_person_view_ver"; CREATE VIEW "oms"."insured_person_view_ver" ( person_id, last_name, first_name, patronymic, sex, birth_day, inn, snils, parent_person_id, death_day, doc_type_id, doc_number, doc_series, doc_issue, doc_expire, doc_issue_name, postcode, house_num, house_letter, building_num, flat_num, flat_letter, loc_postcode, loc_place_id, loc_street_id, loc_house_num, loc_house_letter, loc_building_num, loc_flat_num, loc_flat_letter, work_ticket_num, work_ticket_date, work_ticket_expire_date, work_ticket_issue, job_less, reason_id, editor_login, modify_time, dismissal_date, dismissal_order_number, dismissal_order_date, clock_num, work_contract_num, work_contract_date, work_contract_expire_date, license_num, license_expire_date, license_date, license_issue, proxy_id, parent_person_id_name, insured, person_info_id, dental_lpu_name, area_lpu_name, woman_lpu_name, child_lpu_name, nation_code, nation_name, dental_lpu, area_lpu, woman_lpu, child_lpu, branch, insured_id, department, place, street, place_id, street_id, region_code, loc_place, loc_street, subregion, loc_subregion, loc_reg_code, death_doc_series, death_doc_num, death_doc_date, death_doc_issue, workshop, sub_name, sub_type, loc_sub_name, loc_sub_type, contract_number, contract_date, confirm_date, birth_place, fact_place_id, fact_street_id, fact_postcode, fact_house_num, fact_house_letter, fact_building_num, fact_flat_num, fact_flat_letter, reg_date, loc_reg_date, fact_place, fact_street, fact_subregion, fact_reg_code, person_ver_id) AS SELECT pv.person_id, pi.last_name, pi.first_name, pi.patronymic, pi.sex, pi.birth_day, pi.inn, pi.snils, pi.parent_person_id, pi.death_day, pi.doc_type_id, pi.doc_number, pi.doc_series, pi.doc_issue, pi.doc_expire, pi.doc_issue_name, pi.postcode, pi.house_num, pi.house_letter, pi.building_num, pi.flat_num, pi.flat_letter, pi.loc_postcode, pi.loc_place_id, pi.loc_street_id, pi.loc_house_num, pi.loc_house_letter, pi.loc_building_num, pi.loc_flat_num, pi.loc_flat_letter, pi.work_ticket_num, pi.work_ticket_date, pi.work_ticket_expire_date, pi.work_ticket_issue, pi.job_less, pi.reason_id, pi.editor_login, pi.modify_time, ipi.dismissal_date, ipi.dismissal_order_number, ipi.dismissal_order_date, ipi.clock_num, ipi.work_contract_num, ipi.work_contract_date, ipi.work_contract_expire_date, ipi.license_num, ipi.license_expire_date, ipi.license_date, ipi.license_issue, ipi.proxy_id, parent.last_name AS parent_person_id_name, i.insured_id AS insured, pi.person_info_id, dental.name_short AS dental_lpu_name, area.name_short AS area_lpu_name, woman.name_short AS woman_lpu_name, child.name_short AS child_lpu_name, pi.nation_code, nation.name AS nation_name, pi.dental_lpu, pi.area_lpu, pi.woman_lpu, pi.child_lpu, d.name AS branch, ii.name AS insured_id, ipi.department, (((pl.type)::text || ' '::text) || (pl.name)::text) AS place, (((st.prefix)::text || ' '::text) || (st.name)::text) AS street, pi.place_id, pi.street_id, pl.region_code, loc_pl.name AS loc_place, (((loc_st.prefix)::text || ' '::text) || (loc_st.name)::text) AS loc_street, subreg.name AS subregion, loc_subreg.name AS loc_subregion, loc_pl.region_code AS loc_reg_code, pi.death_doc_series, pi.death_doc_num, pi.death_doc_date, pi.death_doc_issue, ipi.workshop, pl.sub_name, pl.sub_type, loc_pl.sub_name AS loc_sub_name, loc_pl.sub_type AS loc_sub_type, i.contract_number, i.contract_date, ip.confirm_date, pi.birth_place, pi.fact_place_id, pi.fact_street_id, pi.fact_postcode, pi.fact_house_num, pi.fact_house_letter, pi.fact_building_num , pi.fact_flat_num, pi.fact_flat_letter, pi.reg_date, pi.loc_reg_date, fact_pl.name AS fact_place, (((fact_st.prefix)::text || ' '::text) || (fact_st.name)::text) AS fact_street, fact_subreg.name AS fact_subregion, fact_pl.region_code AS fact_reg_code , pv.person_ver_id FROM oms.person_ver pv JOIN oms.insured_person_info ipi ON pv.insured_person_info_id = ipi.insured_person_info_id JOIN oms.insured_person ip ON ip.insured_id = ipi.insured_id AND ip.person_id = pv.person_id JOIN oms.person_info pi ON pv.person_info_id = pi.person_info_id JOIN oms.insured i ON i.insured_id = ipi.insured_id JOIN oms.insured_info ii ON ii.insured_info_id = i.insured_info_id LEFT JOIN oms.place pl ON pl.place_id = pi.place_id LEFT JOIN oms.street st ON st.street_id = pi.street_id LEFT JOIN oms.subregion subreg ON subreg.subregion_id = pl.subregion_id LEFT JOIN oms.place loc_pl ON loc_pl.place_id = pi.loc_place_id LEFT JOIN oms.street loc_st ON loc_st.street_id = pi.loc_street_id LEFT JOIN oms.subregion loc_subreg ON loc_subreg.subregion_id = loc_pl.subregion_id LEFT JOIN oms.place fact_pl ON fact_pl.place_id = pi.fact_place_id LEFT JOIN oms.street fact_st ON fact_st.street_id = pi.fact_street_id LEFT JOIN oms.subregion fact_subreg ON fact_subreg.subregion_id = fact_pl.subregion_id LEFT JOIN oms.person p2 ON pi.parent_person_id = p2.person_id LEFT JOIN oms.person_info parent ON parent.person_info_id = p2.person_info_id LEFT JOIN foms.lpu dental ON dental.nomlpu = pi.dental_lpu LEFT JOIN foms.lpu area ON area.nomlpu = pi.area_lpu LEFT JOIN foms.lpu woman ON woman.nomlpu = pi.woman_lpu LEFT JOIN foms.lpu child ON child.nomlpu = pi.child_lpu LEFT JOIN foms.nation nation ON nation.code = pi.nation_code LEFT JOIN qe_department d ON d.departmentid = i.branch_code; CREATE OR REPLACE FUNCTION "oms"."get_person_by_id" (_person_id integer, _person_ver_id integer, _user_id integer) RETURNS SETOF "oms"."insured_person_view" AS $body$ DECLARE rec oms.insured_person_view%ROWTYPE; BEGIN IF _person_ver_id IS NULL THEN FOR rec IN SELECT * FROM oms.insured_person_view WHERE person_id = _person_id LOOP RETURN NEXT rec; END LOOP; ELSE FOR rec IN SELECT * FROM oms.insured_person_view_ver WHERE person_ver_id = _person_ver_id LOOP RETURN NEXT rec; END LOOP; END IF; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "reg_date" DATE; COMMENT ON COLUMN "import"."base"."reg_date" IS 'Дата регистрации'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "r_reg_date" DATE; COMMENT ON COLUMN "import"."base"."r_reg_date" IS 'Дата регистрации по месту нахождения (пребывания)'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_nation" CHAR(2); COMMENT ON COLUMN "import"."base"."f_nation" IS 'Код по гражданству и месту жительства'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_ind_post" VARCHAR(6); COMMENT ON COLUMN "import"."base"."f_ind_post" IS 'Почтовый индекс'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_region" CHAR(2); COMMENT ON COLUMN "import"."base"."f_region" IS 'Код региона РФ'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_subregion" VARCHAR(100); COMMENT ON COLUMN "import"."base"."f_subregion" IS 'Районное деление региона'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_okato" VARCHAR(11); --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_ocatd" VARCHAR(11); COMMENT ON COLUMN "import"."base"."f_ocatd" IS 'Код OKATO (из S_OKATO.DBF S_OKATO.CODE)'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_town" VARCHAR(100); COMMENT ON COLUMN "import"."base"."f_town" IS 'Город'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_type_np" VARCHAR(10); COMMENT ON COLUMN "import"."base"."f_type_np" IS 'Тип населенного пункта'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_name_np" VARCHAR(100); COMMENT ON COLUMN "import"."base"."f_name_np" IS 'Наименование населённого пункта'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_kodul" VARCHAR(20); COMMENT ON COLUMN "import"."base"."f_kodul" IS 'Код улицы (из STREET.DBF STREET.CODE)'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_type_ul" VARCHAR(20); COMMENT ON COLUMN "import"."base"."f_type_ul" IS 'Тип улицы из (STREET.DBF STREET.SOCR)'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_name_ul" VARCHAR(200); COMMENT ON COLUMN "import"."base"."f_name_ul" IS 'Название улицы'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_house" VARCHAR(7); COMMENT ON COLUMN "import"."base"."f_house" IS '№ дома'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_houselet" VARCHAR(2); COMMENT ON COLUMN "import"."base"."f_houselet" IS 'Литера дома'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_block" VARCHAR(2); COMMENT ON COLUMN "import"."base"."f_block" IS '№ корпуса/строения'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_flat" VARCHAR(5); COMMENT ON COLUMN "import"."base"."f_flat" IS '№ квартиры'; --------------- SQL --------------- ALTER TABLE "import"."base" ADD COLUMN "f_flatlet" VARCHAR(2); COMMENT ON COLUMN "import"."base"."f_flatlet" IS 'Литера квартиры'; --------------- SQL --------------- DROP VIEW "import"."base_diff"; CREATE VIEW "import"."base_diff" ( rec_id, protocol_id, snils, insur_inn, fam, name, otch, drod, sex, doc_type_id, d_series, d_number, s_date, f_date, issue_name, nation, ind_post, region, subregion, town, type_np, name_np, type_ul, name_ul, house, houselet, block, flat, flatlet, reg_date, r_ind_post, r_region, r_subreg, r_town, r_type_np, r_name_np, r_type_ul, r_name_ul, r_house, r_housel, r_block, r_flat, r_flatl, r_reg_date, tab, subpodr, tg_number, tg_date, tg_end, uv_number, uv_date_pr, uv_date, d_type, in_number, in_date, in_f_date, in_issue, rz_number, rz_date, rz_f_date, rz_issue, birth_pl, f_nation, f_ind_post, f_region, f_subregion, f_town, f_type_np, f_name_np, f_type_ul, f_name_ul, f_house, f_houselet, f_block, f_flat, f_flatlet) AS SELECT b.rec_id, b.protocol_id, pi.snils, pi.inn AS insur_inn, pi.last_name AS fam, pi.first_name AS name, pi.patronymic AS otch, pi.birth_day AS drod, pi.sex, pi.doc_type_id, pi.doc_series AS d_series, pi.doc_number AS d_number, pi.doc_issue AS s_date, pi.doc_expire AS f_date, pi.doc_issue_name AS issue_name, pi.nation_code AS nation, pi.postcode AS ind_post, COALESCE(pl.region_code, lpl.region_code) AS region, sr.name AS subregion, CASE WHEN ((pl.type)::text = 'г'::text) THEN pl.name ELSE ''::character varying END AS town, CASE WHEN ((pl.type)::text <> 'г'::text) THEN pl.type ELSE ''::character varying END AS type_np, CASE WHEN ((pl.type)::text <> 'г'::text) THEN pl.name ELSE ''::character varying END AS name_np, st.prefix AS type_ul, st.name AS name_ul, pi.house_num AS house, pi.house_letter AS houselet, pi.building_num AS block, pi.flat_num AS flat, pi.flat_letter AS flatlet, pi.reg_date, pi.loc_postcode AS r_ind_post, lpl.region_code AS r_region, lsr.name AS r_subreg, CASE WHEN ((lpl.type)::text = 'г'::text) THEN lpl.name ELSE ''::character varying END AS r_town, CASE WHEN ((lpl.type)::text <> 'г'::text) THEN lpl.type ELSE ''::character varying END AS r_type_np, CASE WHEN ((lpl.type)::text <> 'г'::text) THEN lpl.name ELSE ''::character varying END AS r_name_np, lst.prefix AS r_type_ul, lst.name AS r_name_ul, pi.loc_house_num AS r_house, pi.loc_house_letter AS r_housel, pi.loc_building_num AS r_block, pi.loc_flat_num AS r_flat, pi.loc_flat_letter AS r_flatl, pi.loc_reg_date as r_reg_date, ipi.clock_num AS tab, ipi.department AS subpodr, ipi.work_contract_num AS tg_number, ipi.work_contract_date AS tg_date, ipi.work_contract_expire_date AS tg_end, ipi.dismissal_order_number AS uv_number, ipi.dismissal_order_date AS uv_date_pr, ipi.dismissal_date AS uv_date, dt.code AS d_type, pi.work_ticket_num AS in_number, pi.work_ticket_date AS in_date, pi.work_ticket_expire_date AS in_f_date, pi.work_ticket_issue AS in_issue, ipi.license_num AS rz_number, ipi.license_date AS rz_date, ipi.license_expire_date AS rz_f_date, ipi.license_issue AS rz_issue, pi.birth_place AS birth_pl, '' as f_nation, pi.fact_postcode AS f_ind_post, fpl.region_code AS f_region, fsr.name AS f_subregion, CASE WHEN ((fpl.type)::text = 'г'::text) THEN fpl.name ELSE ''::character varying END AS f_town, CASE WHEN ((fpl.type)::text <> 'г'::text) THEN fpl.type ELSE ''::character varying END AS f_type_np, CASE WHEN ((fpl.type)::text <> 'г'::text) THEN fpl.name ELSE ''::character varying END AS f_name_np, fst.prefix AS f_type_ul, fst.name AS f_name_ul, pi.fact_house_num AS f_house, pi.fact_house_letter AS f_houselet, pi.fact_building_num AS f_block, pi.fact_flat_num AS f_flat, pi.fact_flat_letter AS f_flatlet FROM import.base b JOIN oms.person p ON b.person_id = p.person_id JOIN oms.person_info pi ON p.person_info_id = pi.person_info_id JOIN oms.protocol prot ON b.protocol_id = prot.protocol_id JOIN oms.insured_person ip ON b.person_id = ip.person_id AND prot.insured_id = ip.insured_id JOIN oms.insured_person_info ipi ON ipi.insured_person_info_id = ip.insured_person_info_id JOIN oms.doc_type dt ON dt.doc_type_id = pi.doc_type_id LEFT JOIN oms.place pl ON pi.place_id = pl.place_id LEFT JOIN oms.subregion sr ON pl.subregion_id = sr.subregion_id LEFT JOIN oms.street st ON pi.street_id = st.street_id LEFT JOIN oms.place lpl ON pi.loc_place_id = lpl.place_id LEFT JOIN oms.subregion lsr ON lpl.subregion_id = lsr.subregion_id LEFT JOIN oms.street lst ON pi.loc_street_id = lst.street_id LEFT JOIN oms.place fpl ON pi.fact_place_id = fpl.place_id LEFT JOIN oms.subregion fsr ON fpl.subregion_id = fsr.subregion_id LEFT JOIN oms.street fst ON pi.fact_street_id = fst.street_id;