<collection name="SO_(vw_cmj_all_deps)" idField="id">
   <prototype>
      <![CDATA[
         SELECT 
            s.id,
            s.created_date,
            ss_module_org.Module,
            s.self_1,
            s.self_2,
            s.self_3,
            s.orgsystem_id,
            s.fullName,
            s.gorf,
            cast (s.departmenttype as int) as deptype
         FROM (
            SELECT
               so_department.id,
               so_unit.created_date,
               '<id>' as self_1,
               ':' as self_2,
               '</>' as self_3,
               so_department.HierRoot as orgsystem_id,
               fullName,
               getisolated(so_department.id) as gorf,
               CASE
                  WHEN so_department.departmenttype = 'ГО: ГОЛОВНОЙ ОФИС' then '0'
                  WHEN so_department.departmenttype = 'РФ: РЕГИОНАЛЬНЫЙ ФИЛИАЛ' then '1'
                  WHEN so_department.departmenttype = 'Р: РУКОВОДСТВО' then '2'
                  WHEN so_department.departmenttype = 'Д: ДЕПАРТАМЕНТ' then '3'
                  WHEN so_department.departmenttype = 'ЕСЦ: ЕСЦ' then '4'
                  WHEN so_department.departmenttype = 'У: УПРАВЛЕНИЕ' then '5'
                  WHEN so_department.departmenttype = 'С: СЛУЖБА' then '6'
                  WHEN so_department.departmenttype = 'О: ОТДЕЛ' then '7'
                  WHEN so_department.departmenttype = 'Г: ГРУППА' then '8'
                  WHEN so_department.departmenttype = 'ДО: ДОПОЛНИТЕЛЬНЫЙ ОФИС' then '9'
                  WHEN so_department.departmenttype = 'ОО: ОПЕРАЦИОННЫЙ ОФИС' then '10'
                  WHEN so_department.departmenttype = 'КО: КРЕДИТНЫЙ ОФИС' then '11'
                  WHEN so_department.departmenttype = 'ОКВКУ: ОПЕРАЦИОННАЯ КАССА ВНЕ КАССОВОГО УЗЛА' then '12'
                  ELSE '13'
               END    as departmenttype
            FROM
               SO_Department so_department
               natural join so_unit
               natural join so_structureunit
               inner join so_beard beard on beard.id = so_structureunit.beard and beard.id_type = so_structureunit.beard_type
            WHERE
               so_department.accessRedirect is null
         ) s
         ::from-clause
         WHERE
            1 = 1
            ::where-clause          
           ]]>
   </prototype>
   <filter name="MODULE">
      <reference placeholder="from-clause">
         <![CDATA[
                   join SS_ModuleOrg ss_module_org on ss_module_org.Organization = orgsystem_id
               ]]>
      </reference>
      <criteria placeholder="where-clause">
         <![CDATA[
                   ss_module_org.Module = {0}
               ]]>
      </criteria>
   </filter>
   <filter name="fullName_partial">
      <criteria placeholder="where-clause">
         <![CDATA[
               lower(s.fullName) like ('%' || lower({0}) || '%')
                ]]>
      </criteria>
   </filter>
   <filter name="gorf_partial">
      <criteria placeholder="where-clause">
         <![CDATA[
               lower(s.gorf) like ('%' || lower({0}) || '%')
                ]]>
      </criteria>
   </filter>
   <filter name="CQSEARCH">
      <criteria placeholder="where-clause">
         <![CDATA[
                    lower(s.fullName) like ('%' || lower({0}) || '%')
                ]]>
      </criteria>
   </filter>
</collection>