---

WITH 
opf_all AS (
  SELECT 'ООО'  AS opf FROM dual UNION ALL
  SELECT 'ОАО'  FROM dual UNION ALL
  SELECT 'ЗАО'  FROM dual UNION ALL
  SELECT 'ПАО'  FROM dual UNION ALL
  SELECT 'АО'   FROM dual UNION ALL
  SELECT 'НАО'  FROM dual UNION ALL
  SELECT 'ТОО'  FROM dual UNION ALL
  SELECT 'ИП'   FROM dual UNION ALL
  SELECT 'НКО'  FROM dual UNION ALL
  SELECT 'ФГУП' FROM dual UNION ALL
  SELECT 'МУП'  FROM dual UNION ALL
  SELECT 'ГУП'  FROM dual UNION ALL
  SELECT 'ГБУ'  FROM dual UNION ALL
  SELECT 'ГКУ'  FROM dual UNION ALL
  SELECT 'ГАУ'  FROM dual UNION ALL
  SELECT 'АНО'  FROM dual UNION ALL
  SELECT 'ПК'   FROM dual UNION ALL
  SELECT 'СПК'  FROM dual UNION ALL
  SELECT 'КФХ'  FROM dual UNION ALL
  SELECT 'РПО'  FROM dual UNION ALL
  SELECT 'СНТ'  FROM dual UNION ALL
  SELECT 'ТСЖ'  FROM dual UNION ALL
  SELECT 'ЖСК'  FROM dual UNION ALL
  SELECT 'АКБ'  FROM dual UNION ALL
  SELECT 'КБ'   FROM dual UNION ALL
  SELECT 'Общество[[:space:]]+с[[:space:]]+ограниченной[[:space:]]+ответственностью' FROM dual UNION ALL
  SELECT 'Акционерное[[:space:]]+общество' FROM dual UNION ALL
  SELECT 'Публичное[[:space:]]+акционерное[[:space:]]+общество' FROM dual UNION ALL
  SELECT 'Закрытое[[:space:]]+акционерное[[:space:]]+общество' FROM dual UNION ALL
  SELECT 'Открытое[[:space:]]+акционерное[[:space:]]+общество' FROM dual UNION ALL
  SELECT 'Индивидуальный[[:space:]]+предприниматель' FROM dual UNION ALL
  SELECT 'LLC'  FROM dual UNION ALL
  SELECT 'LTD'  FROM dual UNION ALL
  SELECT 'JSC'  FROM dual UNION ALL
  SELECT 'PJSC' FROM dual UNION ALL
  SELECT 'INC'  FROM dual UNION ALL
  SELECT 'GMBH' FROM dual
),
src AS (
  SELECT INN, NAIMEN
  FROM   SH.UCH_VSE
  WHERE  NAIMEN IS NOT NULL
)
SELECT DISTINCT
       COUNT(*) OVER () AS TOTAL_ROWS,
       s.INN,
       s.NAIMEN AS "Наименование"
FROM   src s
JOIN   opf_all o
  ON   REGEXP_LIKE(
         s.NAIMEN,
         '^[[:space:]]*' || o.opf || '([[:space:]]|["«„''‘’]|$)',
         'i'
       )
WHERE  NOT REGEXP_LIKE(s.NAIMEN, '^[А-ЯЁ][А-ЯЁа-яё\-]+[[:space:]]+[А-ЯЁ]\.', 'c')
  AND  LENGTH(s.NAIMEN) BETWEEN 5 AND 300
ORDER  BY s.NAIMEN;