
                
                
            
            with mv as (
                select dbo.tzgetdate() as dt
            )
        
            
                SELECT 
                l.Lpu_Name as "Lpu_Name",
                l.Lpu_Nick as "Lpu_Nick",
                l.LpuType_id as "LpuType_id",
                l.MesAgeLpuType_id as "LpuAgeType_id",
                lu.LpuUnit_Name as "LpuUnit_Name",
                lu.LpuUnit_Descr as "LpuUnit_Descr",
                luat.KLArea_Name as "Town",
                (
                    Rtrim(luas.KLStreet_Name) || ' ' || lua.Address_House
                    || COALESCE(NULLIF(
                        ' корп. ' || lua.Address_Corpus,
                        ' корп. '
                    ), '')
                ) as "LpuUnit_Address",
                lu.LpuUnit_id as "LpuUnit_id",
                lu.Lpu_id as "Lpu_id",
                lu.LpuUnit_Phone as "LpuUnit_Phone",
                lu.LpuUnit_Enabled as "LpuUnit_Enabled",
                lu.LpuUnit_ExtMedCnt as "ExtMed",
                lu.LpuUnitType_id as "LpuUnitType_id",
                rtrim(pmUser_Name) as "pmuser_name",
                to_char(LpuUnit_updDT, 'dd.mm.yyyy HH24:mi:ss') as "LpuUnit_updDT",
                LpuFondHolder_IsEnabled as "LpuFondHolder_IsEnabled",
                case when lfh.LpuFondHolder_IsEnabled = 2 then 'Да' end as "FondHolder",
                lut.LpuUnitType_SysNick as "LpuUnitType_SysNick"
                
                FROM
                
                v_LpuUnit_ER lu
                inner join v_Lpu l on l.Lpu_id = lu.Lpu_id
                left join v_LpuUnitType lut on lu.LpuUnitType_id = lut.LpuUnitType_id
                left outer join Address lua on lu.Address_id = lua.Address_id
                left outer join KLStreet luas on lua.KLStreet_id = luas.KLStreet_id
                left outer join KLArea luat on lua.KLTown_id = luat.KLArea_id
                left join LpuFondHolder lfh on l.Lpu_id = lfh.Lpu_id and LpuFondHolder_IsEnabled = 2
                left join v_pmUserCache pu on lu.pmUser_updId = pu.pmUser_id -- оптимизация - поменял на v_pmUserCache, т.к. остальные пользователе редактировать LpuUnit не могут
                
                
                INNER JOIN (
                    SELECT
                        ls.Lpu_id,
                        ls.LpuUnit_id,
                        RecType_id,
                        lu_er.LpuUnitType_SysNick,
                        LpuSectionHospType_id,
                        ls.LpuSection_IsDirRec
                    FROM
                        v_MedStaffFact msf
                        INNER JOIN v_LpuSection ls ON (ls.LpuSection_id = msf.LpuSection_id)
                        INNER JOIN v_LpuUnit_ER lu_er ON (ls.LpuUnit_id = lu_er.LpuUnit_id)
                    WHERE
                        lower(msf.Person_Fio) LIKE lower('м%')
                        AND coalesce(MedStatus_id::bigint, 1) = 1
                        AND coalesce(cast(msf.Medstafffact_disDate as date), '2030-01-01') >= '2022-05-04'
                ) ls
                    ON (lu.LpuUnit_id = ls.LpuUnit_id)
                        AND (
                            (
                                coalesce(ls.RecType_id, 6) != 6
                                AND ls.LpuUnitType_SysNick in ('polka', 'ccenter', 'fap')
                            ) OR (
                                ls.LpuUnitType_SysNick in ('stac', 'dstac', 'pstac')
                                 and coalesce(LpuSectionHospType_id, 1) != 5 
                            )
                        )
            
                 WHERE 
                lut.LpuUnitType_Code != 10
                and (l.Lpu_endDate is null or l.Lpu_endDate > (select dt from mv))
                and (lu.LpuUnit_endDate is null or lu.LpuUnit_endDate > (select dt from mv))
                 and lu.Lpu_id = '1143538' and lu.LpuUnit_Enabled = 1   and lut.LpuUnitType_Code in ('1','7','11')   and exists(
                select
                    ls.LpuUnit_id
                from v_LpuSection ls
                where (1=1)  and coalesce(LpuSectionHospType_id, 1) != 5  
                )
            
                
                
                        ORDER BY
                            
                lu.LpuUnit_Enabled DESC,
                l.Lpu_Nick ASC,
                lu.LpuUnit_Name ASC
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
