
            
                -- addit with
                with
                    
                dr_list(DrugRequest_id) as (
                    select
                        dr2.DrugRequest_id
                    from
                        v_DrugRequest dr

                        left join v_DrugRequest dr2  on

                            dr2.DrugRequestPeriod_id = dr.DrugRequestPeriod_id and
                            COALESCE(dr2.PersonRegisterType_id, 0) = COALESCE(dr.PersonRegisterType_id, 0) and

                            COALESCE(dr2.DrugRequestKind_id, 0) = COALESCE(dr.DrugRequestKind_id, 0) and

                            COALESCE(dr2.DrugGroup_id, 0) = COALESCE(dr.DrugGroup_id, 0) and

                            COALESCE(dr2.DrugRequest_Version, 0) = COALESCE(dr.DrugRequest_Version, 0) and

                            dr2.DrugRequestCategory_id = dr.DrugRequestCategory_id
                    	
                    LEFT JOIN LATERAL (

                        select
                            i_aro.AccessRightsName_id
                        from
                            v_Lpu i_l

                            left join v_AccessRightsOrg i_aro  on i_aro.Org_id = i_l.Org_id

                        where
                            i_l.Lpu_id = dr2.Lpu_id
                        limit 1
                    ) arl On true
                
                    where
                        dr.DrugRequest_id = '2866'
                         and (dr2.Lpu_id = dr.Lpu_id or arl.AccessRightsName_id is null)
                )
            , curDT as (select dbo.tzGetDate() as curDate), curDTY as (select date_part('year',dbo.tzGetDate()) as curDate), curD as (select cast(to_char(dbo.tzGetDate(), 'YYYYMMDD') as timestamp) as curDate)
                -- end addit with
            
            select
                -- select
                
                drpo.DrugRequestPersonOrder_id as "DrugRequestPersonOrder_id",
                drpo.Person_id as "Person_id",
                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",

                rtrim(l.Lpu_Nick) as "Lpu_Nick",
                rtrim(pcard.LpuRegion_Name) as "LpuRegion_Name",
                (case when PRYear.PersonRefuse_id is not null then 'true' else 'false' end) as "Person_IsRefuse",
                (case when PRNextYear.PersonRefuse_id is not null then 'true' else 'false' end) as "Person_IsRefuseNext",
                (case when PRPeriodYear.PersonRefuse_id is not null then 'true' else 'false' end) as "Person_IsRefuseCurr",
                (case when fedl.Person_id is not null then 'true' else 'false' end) as "Person_IsFedLgot",
                (case when fed2.Person_id is not null and PRPeriodYear.PersonRefuse_id is null then 'true' else 'false' end) as "Person_IsFedLgotCurr",
                (case when regl.OwnLpu = 1 then 'true' else (case when regl.OwnLpu is not null then 'gray' else 'false' end) end) as "Person_IsRegLgot",
                (case when reg2.OwnLpu = 1 and not (fed2.Person_id is not null and PRPeriodYear.PersonRefuse_id is null) then 'true' else (case when reg2.OwnLpu is not null and not (fed2.Person_id is not null and PRPeriodYear.PersonRefuse_id is null) then 'gray' else 'false' end) end) as "Person_IsRegLgotCurr",
                (case when disp.OwnLpu = 1 then 'true' else (case when disp.OwnLpu is not null then 'gray' else 'false' end) end) as "Person_Is7Noz",
                (case when ps.Server_pid = 0 then 'true' else 'false' end) as "Person_IsBDZ",
                to_char(DrugRequestPersonOrder_insDT, 'DD.MM.YYYY') as "DrugRequestPersonOrder_insDT",

                to_char(DrugRequestPersonOrder_updDT, 'DD.MM.YYYY') as "DrugRequestPersonOrder_updDT",

                drpo_cnt.cnt as "DrugRequestPersonOrder_Count"
                -- end select
            from
                -- from
                v_DrugRequestPersonOrder drpo
                left join v_PersonState ps  on ps.Person_id = drpo.Person_id
                left join v_DrugRequest dr  on dr.DrugRequest_id = drpo.DrugRequest_id
                left join v_DrugRequestPeriod drp  on drp.DrugRequestPeriod_id = dr.DrugRequestPeriod_id
                LEFT JOIN LATERAL (
                    select
                        pc.Lpu_id,
                        pc.LpuRegion_id,
                        pc.LpuRegion_Name
                    from
                        v_PersonCard pc
                    where
                        pc.Person_id = ps.Person_id and pc.LpuAttachType_id = 1
                    order by
                        PersonCard_begDate desc
                    limit 1
                ) as pcard ON true
                left join v_Lpu l  on pcard.Lpu_id = l.Lpu_id
                left join v_PersonRefuse PRYear  on PRYear.Person_id = ps.Person_id and PRYear.PersonRefuse_IsRefuse = 2 and PRYear.PersonRefuse_Year = (select curDate from curDTY)
                left join v_PersonRefuse PRNextYear  on PRNextYear.Person_id = ps.Person_id and PRNextYear.PersonRefuse_IsRefuse = 2 and PRNextYear.PersonRefuse_Year = (select curDate from curDTY)+1
                left join v_PersonRefuse PRPeriodYear  on PRPeriodYear.Person_id = ps.Person_id and PRPeriodYear.PersonRefuse_IsRefuse = 2 and PRPeriodYear.PersonRefuse_Year = date_part('year',drp.DrugRequestPeriod_begDate)
                LEFT JOIN LATERAL (
                    select
                        count(DrugRequestPersonOrder_id) as cnt
                    from
                        v_DrugRequestPersonOrder i_drpo
                    where
                        i_drpo.DrugRequest_id = drpo.DrugRequest_id and
                        i_drpo.Person_id = drpo.Person_id and
                        coalesce(i_drpo.Diag_id, 0) = coalesce(drpo.Diag_id, 0) and
                        (
                            i_drpo.DrugComplexMnn_id is not null or
                            i_drpo.Tradenames_id is not null
                        )
                ) drpo_cnt ON true
                LEFT JOIN LATERAL (
                    select
                        Person_id
                    from
                        PersonPrivilege reg
                        left join PrivilegeType pt  on pt.PrivilegeType_id = reg.PrivilegeType_id
                    where
                        reg.Person_id = ps.Person_id and
                        pt.ReceptFinance_id = 1 and
                        reg.PersonPrivilege_begDate <= (select curDate from curDT) and
                        (
                            reg.PersonPrivilege_endDate is null or
                            reg.PersonPrivilege_endDate >= (select curDate from curD)
                        )
                    limit 1
                ) as fedl ON true
                LEFT JOIN LATERAL (
                    select
                        Person_id
                    from
                        PersonPrivilege reg
                        left join PrivilegeType pt  on pt.PrivilegeType_id = reg.PrivilegeType_id
                    where
                        reg.Person_id = ps.Person_id and
                        pt.ReceptFinance_id = 1 and
                        reg.PersonPrivilege_begDate <= (select curDate from curDT) and
                        (
                            reg.PersonPrivilege_endDate is null or
                            reg.PersonPrivilege_endDate >= (select curDate from curD)
                        )
                    limit 1
                ) as fed2 ON true
                LEFT JOIN LATERAL (
                    select
                        max(case when Lpu_id = '13101071' then 1 else 0 end) as OwnLpu
                    from
                        PersonPrivilege reg
                        left join PrivilegeType pt  on pt.PrivilegeType_id = reg.PrivilegeType_id
                    where
                        reg.Person_id = ps.Person_id and
                        pt.ReceptFinance_id = 2 and
                        reg.PersonPrivilege_begDate <= (select curDate from curDT) and
                        (
                            reg.PersonPrivilege_endDate is null or
                            reg.PersonPrivilege_endDate >= (select curDate from curD)
                        )
                ) as regl ON true
                LEFT JOIN LATERAL (
                    select
                        max(case when Lpu_id = '13101071' then 1 else 0 end) as OwnLpu
                    from
                        PersonPrivilege reg
                        left join PrivilegeType pt  on pt.PrivilegeType_id = reg.PrivilegeType_id
                    where
                        reg.Person_id = ps.Person_id and
                        pt.ReceptFinance_id = 2 and
                        reg.PersonPrivilege_begDate <= (select curDate from curDT) and
                        (
                            reg.PersonPrivilege_endDate is null or
                            reg.PersonPrivilege_endDate >= (select curDate from curD)
                        )
                ) as reg2 ON true
                LEFT JOIN LATERAL (
                    select
                        max(case when Lpu_id = '13101071' then 1 else 0 end) as OwnLpu
                    from
                        v_PersonDisp
                    where
                        Person_id = ps.Person_id and
                        (
                            PersonDisp_endDate is null or
                            PersonDisp_endDate > (select curDate from curDT)
                        ) and
                        Sickness_id is not null
                ) as disp ON true
                
                LEFT JOIN LATERAL (

                    select
                        ii_drpo.DrugRequestPersonOrder_id
                    from
                        v_DrugRequestPersonOrder ii_drpo

                        inner join dr_list ii_dl on ii_dl.DrugRequest_id = ii_drpo.DrugRequest_id
                    where
                        ii_drpo.Person_id = drpo.Person_id and
                        (
                            ii_drpo.DrugComplexMnn_id is not null or
                            ii_drpo.Tradenames_id is not null
                        )
                    limit 1
                ) drpo_wd ON true
            
                -- end from
            
                where
                    -- where
                    drpo.DrugRequest_id = '2866' and drpo.DrugComplexMnn_id is null and drpo.Tradenames_id is null and drpo_wd.DrugRequestPersonOrder_id is null
                    -- end where
            
            order by
                -- order by
                ps.Person_SurName, ps.Person_FirName, ps.Person_SecName
                -- end order by
        
