<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>

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans        http://www.springframework.org/schema/beans/spring-beans-3.2.xsd" default-lazy-init="true">
   <bean id="SO_(vw_cmj_all_deps)Metadata" class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.CollectionMetadataNew" p:searchArea="so_departments_search_uicoll">
      <constructor-arg>
         <list value-type="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field">
            <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field" p:name="self">
               <property name="virtualField">
                  <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.BuildVirtualField">
                     <property name="realFields">
                        <list>
                           <value>self_1</value>
                           <value>Module</value>
                           <value>self_2</value>
                           <value>ID</value>
                           <value>created_date</value>
                           <value>self_3</value>
                        </list>
                     </property>
                     <property name="pattern" value="<id>([0-9]{16}):([0-9A-F]{32})</>" />
                     <property name="separator" value="" />
                     <property name="emptySeparator" value="true" />
                  </bean>
               </property>
            </bean>
            <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field" p:name="beard">
               <property name="virtualField">
                  <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.BeardVirtualField">
                     <property name="realFields">
                        <list>
                           <value>Module</value>
                           <value>self_2</value>
                           <value>ID</value>
                           <value>created_date</value>
                        </list>
                     </property>
                  </bean>
               </property>
            </bean>
            <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field" p:name="depType" p:sortOrder="ASCENDING" />
            <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field" p:name="fullName" p:filter="fullName" p:sortOrder="ASCENDING" />
         </list>
      </constructor-arg>
   </bean>
</beans>