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