SELECT COUNT(id) FROM (SELECT  res.id, res.reg_id, res.profile_id, res.hlr_id, res.amount, res.status_id, res.mreg_prod_id, res.reg_name, res.description, res.mr_name, res.file_info_id, res.order_details_id, res.status_name, res.profile_name, res.file_name, res.hlr_value, res.hlr_description,
res.pm_description, res.prod_order_year, res.prod_order_month, res.order_number, res.tariff_id, res.card_type_id, res.tariff_name, res.personality, res.vendor_name, res.vendor_id, res.full_file_name, res.ret_flag FROM
(SELECT DISTINCT pr.id, pr.reg_id, pr.profile_id, pr.hlr_id, pr.amount, pr.status_id, pr.mreg_prod_id, pr.description, mr.name AS mr_name, pr.file_info_id,
pr.order_details_id, r.name AS reg_name,
s.name AS status_name, p.name AS profile_name, f.name AS file_name, hlr.value AS hlr_value, hlr.description AS hlr_description,
pm.description  AS pm_description, pm.year AS prod_order_year, pm.month AS prod_order_month, pm.order_number AS order_number, pm.tariff_id, pm.card_type_id,
tf.name AS tariff_name, tf.personality AS personality, v.name AS vendor_name, v.id AS vendor_id,
SUBSTR(f.name, 0, INSTR(f.name, '_') - 1) as full_file_name,
case
  when exists(
    select h.id from usim_status_change_history h
    left join usim_status_change ch ON ch.id = h.status_change_id
    where h.entity_type = 7 and h.entity_id = pr.id and ch.status_from_id = 35
  ) then 1 else 0
end as ret_flag -- признак возврата из планирования
FROM usim_production_region pr
JOIN usim_region r ON r.id = pr.reg_id
JOIN usim_status s ON s.id = pr.status_id
JOIN usim_profile p ON p.id = pr.profile_id
JOIN usim_hlr hlr ON hlr.id = pr.hlr_id
JOIN usim_production_macroregion pm ON pm.id = pr.mreg_prod_id
JOIN usim_macro_region mr ON mr.id = pm.mreg_id
JOIN usim_tariff tf ON tf.id = pm.tariff_id
JOIN usim_vendor v ON v.id = pm.vendor_id
LEFT JOIN usim_file f ON f.id = pr.file_info_id
LEFT JOIN usim_status_change_history hist ON hist.ENTITY_TYPE = 7 and hist.ENTITY_ID = pr.id
LEFT JOIN usim_status_change sch ON sch.id = hist.status_change_id
LEFT JOIN usim_code_macro_region cm ON cm.mreg_id = mr.id
LEFT JOIN usim_code cd ON cd.id = cm.code_id and cd.tariff_id = tf.id and cd.card_type_id = pm.card_type_id
LEFT JOIN USIM_PRODUCTION_PLAN pp ON pp.PRODUCTION_REGION_ID = pr.id
WHERE (pr.status_id IN (35,48,38,55,39,36,37,40,47))) res
);