---

UNV_CREATE_REGISTRY

Созданы реестры
WITH codes AS (
    SELECT id AS codes_id 
    FROM report.smev_register_codes 
    WHERE short_name = 'УНВ'
)
INSERT INTO report.smev_register (
    id,
    smev_register_type_code,
    smev_register_codes_id,
    "year",
    "date",
    "type",
    sample_status,
    guid
)
SELECT id, type_code, codes_id, year_val, date_val, type_val, status_val, guid_val
FROM (
    VALUES
    (
        CAST(:registry_id_1 AS int),
        'УНВ',
        CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
        CURRENT_DATE,
        'OUT',
        'Сформирован',
        CAST(:guid1 AS varchar)
    ),
    (
        CAST(:registry_id_2 AS int),
        'УНВ',
        CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
        CURRENT_DATE,
        'OUT',
        'Сформирован',
        CAST(:guid2 AS varchar)
    ),
    (
        CAST(:registry_id_3 AS int),
        'УНВ',
        CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3,
        CURRENT_DATE,
        'OUT',
        'Сформирован',
        CAST(:guid3 AS varchar)
    )
) AS v(id, type_code, year_val, date_val, type_val, status_val, guid_val)
CROSS JOIN codes
WHERE NOT EXISTS (
    SELECT 1
    FROM report.smev_register r
    WHERE r."year" = v.year_val
      AND r.smev_register_type_code = 'УНВ'
      AND r.sample_status <> 'Удалён'
)


Реестры сохранены

INSERT INTO report.unv_registry_item (
    registry_id,
    client_id,
    snils,
    contract_id,
    contract_type,
    tax_period_year,
    contributions_amount
)
WITH
newly_created AS (
    SELECT id, "year" AS tax_period_year
    FROM report.smev_register
    WHERE smev_register_type_code = 'УНВ'
      AND "year" IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3
      )
      AND id NOT IN (SELECT DISTINCT registry_id FROM report.unv_registry_item)
),
dead_individuals AS (
    SELECT DISTINCT individual_id
    FROM ourpension.application_death_info
    WHERE accepted IS NOT NULL
      AND individual_id IS NOT NULL
),
npo_contributions AS (
    SELECT
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS int)  AS contribution_year,
        SUM(t.value)                              AS contributions_amount
    FROM back_office.incoming_order io
    JOIN back_office.incoming_order_operation ioo
        ON ioo.incoming_order_id = io.id
    JOIN back_office.operation o
        ON o.id = ioo.operation_id
    JOIN back_office."transaction" t
        ON t.operation_id = ioo.operation_id
    JOIN back_office.account a
        ON a.id = t.credit_account_id
    JOIN back_office.account_type at
        ON at.id = a.account_type_id
    WHERE at.mnemonics = 'ИПС_ФЛ'
      AND io.payment_return = false
      AND CAST(EXTRACT(YEAR FROM io.date) AS int) IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3
      )
      AND io.contract_id IS NOT NULL
    GROUP BY
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS int)
),
pds_contributions AS (
    SELECT
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS int)  AS contribution_year,
        SUM(t.value)                              AS contributions_amount
    FROM back_office.incoming_order io
    JOIN back_office.incoming_order_operation ioo
        ON ioo.incoming_order_id = io.id
    JOIN back_office.operation o
        ON o.id = ioo.operation_id
    JOIN back_office."transaction" t
        ON t.operation_id = ioo.operation_id
    JOIN back_office.account a
        ON a.id = t.credit_account_id
    JOIN back_office.account_type at
        ON at.id = a.account_type_id
    WHERE at.mnemonics = 'ПДС СВ'
      AND io.payment_return = false
      AND CAST(EXTRACT(YEAR FROM io.date) AS int) IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3
      )
      AND io.contract_id IS NOT NULL
    GROUP BY
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS int)
),
npo_result AS (
    SELECT
        nc.id                       AS registry_id,
        ind.id                      AS client_id,
        ind.insurance_number        AS snils,
        c.id                        AS contract_id,
        CAST('NPO' AS varchar)      AS contract_type,
        npo.contribution_year       AS tax_period_year,
        npo.contributions_amount
    FROM npo_contributions npo
    JOIN newly_created nc           ON nc.tax_period_year = npo.contribution_year
    JOIN ourpension.contract c      ON c.id = npo.contract_id
    JOIN ourpension.individual ind  ON ind.id = npo.individual_id
    WHERE c.service_type = 'NPO'
      AND ind.insurance_number IS NOT NULL
      AND ind.insurance_number <> ''
      AND ind.id NOT IN (SELECT individual_id FROM dead_individuals)
),
pds_result AS (
    SELECT
        nc.id                       AS registry_id,
        ind.id                      AS client_id,
        ind.insurance_number        AS snils,
        c.id                        AS contract_id,
        CAST('PDS' AS varchar)      AS contract_type,
        pds.contribution_year       AS tax_period_year,
        pds.contributions_amount
    FROM pds_contributions pds
    JOIN newly_created nc           ON nc.tax_period_year = pds.contribution_year
    JOIN ourpension.contract c      ON c.id = pds.contract_id
    JOIN ourpension.individual ind  ON ind.id = pds.individual_id
    WHERE c.service_type = 'PDS'
      AND ind.insurance_number IS NOT NULL
      AND ind.insurance_number <> ''
      AND ind.id NOT IN (SELECT individual_id FROM dead_individuals)
)
SELECT * FROM npo_result
UNION ALL
SELECT * FROM pds_result





UNV_REGISTRY_ITEM_TABLE
SELECT
  ri.id as id,
  ind.last_name || ' ' || ind.first_name || 
       COALESCE(' ' || ind.middle_name, '') as client_name,
  ri.snils as snils,
  CASE 
       WHEN ri.contract_type = 'NPO' THEN 'НПО'
       WHEN ri.contract_type = 'PDS' THEN 'ПДС'
   END as contract_type_name,
  c.number as contract_number,
  ri.contributions_amount as contributions_amount,
  CASE
       WHEN ri.has_fns_lk = 1  THEN 'Есть'
       WHEN ri.has_fns_lk = 0  THEN 'Нет'
       WHEN ri.has_fns_lk = 99 THEN 'Ошибка проверки'
       ELSE 'Не проверялось'
   END as has_fns_lk_name,
  ri.last_sent_date as last_sent_date,
  ri.error_code as error_code,
  ri.error_description as error_description,
  CASE
       WHEN ri.error_status = 'NOT_FIXED' THEN 'Не исправлено'
       WHEN ri.error_status = 'FIXED'     THEN 'Исправлено'
       ELSE null
   END as error_status,
  ri.tax_period_year as tax_period_year 
FROM report.unv_registry_item ri
JOIN ourpension.individual ind ON ind.id = ri.client_id
JOIN ourpension.contract c     ON c.id = ri.contract_id
WHERE ri.registry_id = :registry_id 
AND (:error_code IS NULL OR ri.error_code = :error_code)
AND (:error_status IS NULL OR ri.error_status = :error_status)