WITH t AS (SELECT pr.id, pr.amount, r.id AS reg_id, r.name AS reg_name, pr.status_id, pr.description, s.name AS status_name
, r.trigram, pm.id AS mreg_prod_id, pm.order_number AS order_number, pr.file_info_id,
tf.id AS tariff_id, tf.name AS tariff_name, tf.personality AS personality, tf.code AS tariff_code, tf.tech_name AS tariff_tech_name,
v.id AS vendor_id, v.name AS vendor_name, hlr.id AS hlr_id, hlr.VALUE AS hlr_value,
p.id AS profile_id, p.name AS profile_name, mr.id AS mr_id, mr.name AS mr_name, cd.code AS code_code,
a.name AS auth_alg_name, d.id AS card_type_id, d.name AS card_type_name, p.code AS profile_code
FROM usim_production_region pr
JOIN usim_region r ON r.id = pr.reg_id
JOIN usim_production_macroregion pm ON pm.id = pr.mreg_prod_id
JOIN usim_status s ON s.id = pr.status_id
JOIN usim_vendor v ON v.id = pm.vendor_id
JOIN usim_profile p ON p.id = pr.profile_id
LEFT JOIN usim_auth_algorythm a ON p.auth_algo_id=a.id
JOIN usim_hlr hlr ON hlr.id = pr.hlr_id
JOIN usim_macro_region mr ON mr.id = pm.mreg_id
JOIN usim_tariff tf ON tf.id = pm.tariff_id
LEFT JOIN usim_code_macro_region cm ON cm.mreg_id = mr.id
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_card_type d ON d.id = cd.card_type_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
WHERE (pr.status_id IN (35,48,38,55,39,36,37,40,47))
),
t_ne AS (SELECT DISTINCT pr.id, pr.amount, r.id AS reg_id, r.name AS reg_name, pr.status_id, pr.description, s.name AS status_name
, r.trigram, pm.id AS mreg_prod_id, pm.order_number AS order_number, pr.file_info_id,
tf.id AS tariff_id, tf.name AS tariff_name, tf.personality AS personality, tf.code AS tariff_code, tf.tech_name AS tariff_tech_name,
v.id AS vendor_id, v.name AS vendor_name, hlr.id AS hlr_id, hlr.VALUE AS hlr_value,
p.id AS profile_id, p.name AS profile_name, mr.id AS mr_id, mr.name AS mr_name, cd.code AS code_code,
a.name AS auth_alg_name, d.id AS card_type_id, d.name AS card_type_name, p.code AS profile_code
FROM usim_production_region pr
JOIN usim_region r ON r.id = pr.reg_id
JOIN usim_production_macroregion pm ON pm.id = pr.mreg_prod_id
JOIN usim_status s ON s.id = pr.status_id
JOIN usim_vendor v ON v.id = pm.vendor_id
JOIN usim_profile p ON p.id = pr.profile_id
LEFT JOIN usim_auth_algorythm a ON p.auth_algo_id=a.id
JOIN usim_hlr hlr ON hlr.id = pr.hlr_id
JOIN usim_macro_region mr ON mr.id = pm.mreg_id
JOIN usim_tariff tf ON tf.id = pm.tariff_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_card_type d ON d.id = cd.card_type_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
WHERE (pr.status_id IN (35,48,38,55,39,36,37,40,47)) AND pr.id NOT IN (SELECT id FROM t)
),
res AS (SELECT * FROM t UNION ALL (SELECT * FROM t_ne))

SELECT q.* FROM (SELECT res.id, res.amount, res.reg_id, res.reg_name, res.status_id, res.description, res.status_name,
res.trigram, res.mreg_prod_id, res.order_number,
ROW_NUMBER() OVER (ORDER BY res.id) AS paging,
(
  SELECT MAX(h.history_date) FROM usim_status_change_history h
  join usim_status_change ch ON ch.id = h.status_change_id
  WHERE h.entity_type = 7 AND h.entity_id = res.id AND ch.status_to_id = 47
) AS attach_status_date,
CASE WHEN INSTR(f.name, '_') = 0 THEN SUBSTR(f.name, 0, INSTR(f.name, '.') - 1) ELSE SUBSTR(f.name, 0, INSTR(f.name, '_') - 1) END AS full_file_name,
CASE WHEN h_ret_flag.entity_id IS NOT NULL THEN 1 ELSE 0 END AS ret_flag, -- признак возврата из планирования,
CASE WHEN h_files.cnt >= 3 THEN 1 ELSE 0 END AS all_files_flag -- признак того, что прикреплен полный комлект файлов
FROM res
LEFT JOIN usim_file f ON f.id = res.file_info_id AND file_type_id = 6 --OUT файл
LEFT JOIN (
  SELECT entity_id FROM usim_status_change_history
  WHERE entity_type = 7 AND status_change_id IN (SELECT id FROM usim_status_change WHERE status_from_id = 35 AND status_to_id IN (47,55))
  GROUP BY entity_id
) h_ret_flag ON h_ret_flag.entity_id = res.id
LEFT JOIN (
  SELECT entity_id, COUNT(id) AS cnt FROM usim_file WHERE entity_type_id = 7 AND file_type_id IN (6,7,8) GROUP BY entity_id
) h_files ON h_files.entity_id = res.id --список прикрепленных файлов
)q
WHERE paging BETWEEN 1 AND 100
;