            with cte as (
                select cast(localtimestamp as date) as curDate
            )
            select
                1 as "N_ZAP"
                ,L.Lpu_id as "Lpu_id"
                ,dsl.DataStorage_Value as "LOGIN"
                ,dsp.DataStorage_Value as "PASSWORD"
                ,ED.EvnDirection_id as "id"
                ,'EvnDirection' as "object"
                ,rd.RUIDDirection_RUID as "RUID_NAPR"
                ,case
                    when ED.Lpu_cid = ED.Lpu_did then 2
                    else 3
                end as "ORG"
                ,cL.Lpu_f003mcod as "CODE"
                ,coalesce(LpuBuildingCode.Value, cast(cLB.LpuBuilding_Code as integer)) as "KODLPU"
                ,to_char(ED.EvnDirection_failDT, 'YYYY-MM-DD') as "D_CANC"
                ,case
                    when ESH.EvnStatusCause_id = 18 then 1 -- Неявка пациента на госпитализацию
                    when ESH.EvnStatusCause_id = 22 then 2 -- не предоставление необходимого пакета документов
                    when ESH.EvnStatusCause_id = 1 then 3 -- инициативный отказ от госпитализации пациентом
                    when ESH.EvnStatusCause_id = 5 then 4 -- Смерть пациента
                    when ESH.EvnStatusCause_id = 6 then 6 -- отказ в госпитализации из-за отсутствия медицинских показаний
                    when ESH.EvnStatusCause_id = 13 then 7 -- отказ пациента от госпитализации по эпидемиологическим показаниям
                    when ESH.EvnStatusCause_id = 20 then 8 -- карантин в стационарном отделении
                    when ESH.EvnStatusCause_id = 12 then 9 -- направление пациента не по профилю заболевания
                    when ESH.EvnStatusCause_id = 10 then 10 -- экстренная госпитализация в период ожидания плановой
                    else 5 -- прочие
                end as "CODE_PR"
            from
                v_EvnDirection ED
                inner join v_Lpu L  on L.Lpu_id = ED.Lpu_id
                inner join v_Lpu cL  on cL.Lpu_id = ED.Lpu_cid
                left join v_MedStaffFact cMSF  on cMSF.MedStaffFact_id = ED.MedStaffFact_fid
                inner join v_LpuSection cLS  on cLS.LpuSection_id = COALESCE(cMSF.LpuSection_id, ED.LpuSection_did)
                inner join v_LpuBuilding cLB  on cLB.LpuBuilding_id = cLS.LpuBuilding_id
                LEFT JOIN LATERAL(
                    select ESH.EvnStatusCause_id
                    from EvnStatusHistory ESH
                    where ESH.Evn_id = ED.EvnDirection_id and ESH.EvnStatus_id = ED.EvnStatus_id
                    order by ESH.EvnStatusHistory_begDate desc
                    limit 1
                ) ESH On true
                left join stg.ServiceListPackage SLP on
                    SLP.ServiceListPackageType_id = '2' and
                    SLP.ServiceListPackage_ObjectID = ED.EvnDirection_id and
                    SLP.PackageStatus_id = 5
                inner join DataStorage dsl  on dsl.Lpu_id = ED.Lpu_cid and dsl.DataStorage_Name = 'rir_login'
                inner join DataStorage dsp  on dsp.Lpu_id = ED.Lpu_cid and dsp.DataStorage_Name = 'rir_pass'
                LEFT JOIN LATERAL (
                    select AV.AttributeValue_ValueInt as Value
                    from v_AttributeSignValue ASV
                    inner join v_AttributeSign ASign on ASign.AttributeSign_id = ASV.AttributeSign_id
                    inner join v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id
                    inner join v_Attribute A on A.Attribute_id = AV.Attribute_id
                    where
                        ASign.AttributeSign_Code = 22
                        and ASV.AttributeSignValue_TablePKey = cL.Lpu_id
                        and A.Attribute_SysNick ilike 'LpuBuildingCode'
                        and (select curDate from cte) between ASV.AttributeSignValue_begDate and coalesce(ASV.AttributeSignValue_endDate,(select curDate from cte))
                    order by
                        ASV.AttributeSignValue_begDate desc,
                        ASV.AttributeSignValue_insDT desc
                    limit 1
                ) LpuBuildingCode On true
                inner join r50.RUIDDirection rd on rd.Evn_id = ED.EvnDirection_id
            where
                SLP.ServiceListPackage_id is null
                and ED.DirType_id in (1,5)
                and ED.PayType_id = 201
                and ED.EvnDirection_failDT is not null
                and nullif(nullif(cL.Lpu_f003mcod,'0'),'') is not null
                 and ED.EvnDirection_insDT > '2021-07-05'
            limit 100
