
                
                
                with LpuTable as (
                    select Lpu_id, Lpu_Nick
                    from
                        v_lpu AS L
                    where (1 = 1) and L.Org_id = '155653'
                ),
                curDT as (select localtimestamp as curDate)
                
                SELECT 
                    0 as "Is_Checked",
                    PS.Person_id as "Person_id",
                    coalesce(PS.Person_SurName,'') || ' ' || coalesce(PS.Person_FirName,'') || ' ' || coalesce(PS.Person_SecName,'') as "Person_FIO",
                    to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay",
                    case when PC.PersonCard_id is not null then 'Да' else 'Нет' end as "PersonStatus",
                    case when PC.PersonCard_id is not null then coalesce(L.Lpu_Nick,'') else '' end as "Lpu_Name",
                    case when PC.PersonCard_id is not null then coalesce(LRP.LpuRegion_Name,'') else '' end as "LpuRegion_Name",
                    case when PC.PersonCard_id is not null then coalesce(LR.LpuRegion_Name,'') else '' end as "LpuRegion_FapName",
                    case when PC.PersonCard_id is not null then coalesce(LRT.LpuRegionType_Name,'') else '' end as "LpuRegionType_Name",
                    coalesce(UA.Address_Address,'') as "UAddress_Name",
                    coalesce(PA.Address_Address,'') as "PAddress_Name",
                    S.Sex_Name as "Sex_Name",
                    CASE WHEN pls.Polis_endDate is not null and pls.Polis_endDate <= (select curDate from curDT)
                        THEN 'yellow'
                        ELSE CASE WHEN ps.PersonCloseCause_id = 2 and Person_closeDT is not null
                            THEN 'red'
                            ELSE CASE WHEN ps.Server_pid = 0
                                THEN 'true'
                                ELSE 'false'
                            END
                        END
                    END as "Person_IsBDZ"
                
                FROM
                
                    LpuTable L
                    inner join v_PersonCardState PC on PC.Lpu_id=L.Lpu_id
                    inner join v_PersonState PS on PS.Person_id = PC.Person_id
                    left join Sex S on S.Sex_id = PS.Sex_id
                    LEFT JOIN LpuRegion LRP ON PC.LpuRegion_id = LRP.LpuRegion_id
                    LEFT JOIN LpuRegionType LRT ON LRT.LpuRegionType_id = PC.LpuRegionType_id
                    LEFT JOIN LpuRegion LR on LR.LpuRegion_id = PC.LpuRegion_fapid
                    LEFT JOIN Address UA on UA.Address_id = PS.UAddress_id
                    INNER JOIN Address PA on PA.Address_id = PS.PAddress_id
                    LEFT JOIN Polis pls on pls.Polis_id = ps.Polis_id
                    
                 WHERE 
                        PS.Person_deadDT is null
                    and (COALESCE(PS.Person_SurName, '') <> '' or COALESCE(PS.Person_FirName, '') <> '' or COALESCE(PS.Person_SecName, '') <> '')
                     and PC.LpuAttachType_id = '1' and PA.KLCountry_id = '643' and PA.KLRGN_id = '19' and PA.KLCity_id = '2117' and PA.KLStreet_id = '102355' and PA.Address_House = '131'
                
                
                        ORDER BY
                            
                    PS.Person_SurName,
                    PS.Person_FirName,
                    PS.Person_SecName
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
