
                
            with mv as (
                select
                dbo.tzGetDate() as dt
            ),
            
                
                
                        PCSD1 as (
                            select
                                max(Personcard_id) as Personcard_id,
                                Person_id
                            from
                                v_PersonCard_all pc
                            where
                                pc.Lpu_id != '1204020'
                            group by Person_id
                        )
                
                SELECT 
        
            DOr.PersonDispOrp_id as "PersonDispOrp_id",
            PS.Person_id as "Person_id",
            PS.Server_id as "Server_id",
            PS.PersonEvn_id as "PersonEvn_id",
            UAdd.Address_Nick as "ua_name",
            PAdd.Address_Nick as "pa_name",
            Sex.Sex_Name as "Sex_Name",
            PS.Polis_Ser as "Polis_Ser",
            PS.Polis_Num as "Polis_Num",
            okved1.Okved_Name as "PersonOrg_Okved",
            org1.Org_OGRN as "PersonOrg_OGRN",
            astat1.KLArea_Name as "Person_KLAreaStat_Name",
            astat2.KLArea_Name as "PersonOrg_KLAreaStat_Name",
            rtrim(addr1.Address_Nick) as "UAddress_Address",
            case when DOr.Org_id IS NOT NULL then 'Да' else 'Нет' end as "OrgExist",
            coalesce(rtrim(ODL.Lpu_Nick), '') as "OnDispInOtherLpu",
            EPLDO.EvnPLDispOrp_id as "EvnPLDispOrp_id",
             null as "EvnPLDispOrp_OtherLpu",
         	case when EPLDO.EvnPLDispOrp_id  is null then 'false' else 'true' end as "ExistsDOPL"
         
        
                ,rtrim(PS.Person_SurName) as "Person_Surname"
                ,rtrim(PS.Person_FirName) as "Person_Firname"
                ,rtrim(PS.Person_SecName) as "Person_Secname"
                ,to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday"
            
            
                FROM
                
        
                	    PCSD1
                        inner join lateral (
                            select *
                            from v_PersonState_all PS
                            where PCSD1.person_id = PS.person_id
                            limit 1
                        ) PS on true
                    inner join v_PersonDispOrp DOr on DOr.Person_id = PS.Person_id  and DOr.Lpu_id = '1204020' left join Sex on PS.Sex_id = Sex.Sex_id
            left join v_Job as job1 ON PS.Job_id = job1.Job_id
            left join v_Org as org1 ON job1.Org_id = org1.Org_id
            left join v_Okved as okved1 ON okved1.Okved_id = org1.Okved_id
            left join v_Address UAdd on UAdd.Address_id = ps.UAddress_id
            left join v_Address PAdd on PAdd.Address_id = ps.PAddress_id
            left join v_Address as addr1 ON PS.UAddress_id = addr1.Address_id
            left join lateral(
                select KLArea_Name
                from v_KLAreaStat
                where (KLCountry_id = addr1.KLCountry_id or KLCountry_id is null)
                  and (KLRGN_id = addr1.KLRGN_id or KLRGN_id is null)
                  and (KLSubRGN_id = addr1.KLSubRGN_id or KLSubRGN_id is null)
                  and (KLCity_id = addr1.KLCity_id or KLCity_id is null)
                  and (KLTown_id = addr1.KLTown_id or KLTown_id is null)
                order by
                    KLCountry_id desc,
                    KLRGN_id desc,
                    KLSubRGN_id desc,
                    KLCity_id desc,
                    KLTown_id desc
                limit 1
            ) as astat1 on true
            left join v_Address as addr2 ON org1.UAddress_id=addr2.Address_id
            left join lateral(
                select KLArea_Name
                from v_KLAreaStat
                where (KLCountry_id = addr2.KLCountry_id or KLCountry_id is null)
                  and (KLRGN_id = addr2.KLRGN_id or KLRGN_id is null)
                  and (KLSubRGN_id = addr2.KLSubRGN_id or KLSubRGN_id is null)
                  and (KLCity_id = addr2.KLCity_id or KLCity_id is null)
                  and (KLTown_id = addr2.KLTown_id or KLTown_id is null)
                order by
                    KLCountry_id desc,
                    KLRGN_id desc,
                    KLSubRGN_id desc,
                    KLCity_id desc,
                    KLTown_id desc
                limit 1
            ) as astat2 on true
            left join lateral(
                select Lpu_Nick
                from
                    PersonDispOrp pdd
                    inner join v_Lpu vlp on vlp.Lpu_id = pdd.Lpu_id
                where pdd.Person_id = PS.Person_id
                  and pdd.Lpu_id <> '1204020'
                  and pdd.PersonDispOrp_Year = 2022
            ) as ODL on true
            left join lateral(
                select EvnPLDispOrp_id
                from v_EvnPLDispOrp epldd
                where epldd.Person_id=PS.Person_id
                  and epldd.Lpu_id = '1204020'
                   and epldd.DispClass_id = 3
                  and date_part('year', epldd.EvnPLDispOrp_setDate) = 2022
                limit 1
            ) as EPLDO on true
            
        
                         WHERE 
                        (1 = 1) and DOr.PersonDispOrp_Year = 2022  and DOr.CategoryChildType_id IN (1,2,3,4) and ( exists (
                        select
                            PC.personcard_id
                        from
                            
                                v_PersonCard_all PC
                                left join v_LpuRegion LR on LR.LpuRegion_id = PC.LpuRegion_id
                                left join v_LpuRegion LR_Fap on LR_Fap.LpuRegion_id = PC.LpuRegion_fapid
                                left join v_MedStaffRegion MedStaffRegion on MedStaffRegion.LpuRegion_id = PC.LpuRegion_id
                                left join v_MedStaffFact msf on msf.MedStaffFact_id = MedStaffRegion.MedStaffFact_id and MedStaffRegion.Lpu_id = msf.Lpu_id
                                left join persis.Post p on p.id = msf.Post_id
                            
                        where
                            PC.Person_id = PS.Person_id
                                    and (
                                        PC.PersonCard_endDate is null
                                        or PC.PersonCard_endDate::timestamp > (select dt from mv)
                                    )
                             and PC.Lpu_id is not null and PC.Lpu_id <> '1204020'
                        limit 1
                    ))  and DOr.pmUser_insID = '392536775516' and DOr.PersonDispOrp_insDT::date = '2022-03-15'::date
                        
                
                        ORDER BY
                            
                        
                        PS.Person_SurName,
                        PS.Person_FirName,
                        PS.Person_SecName
                        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
