
            SELECT part.*
            FROM (
                        SELECT
            ds.EMDDocumentTypeLocal_id as "EMDDocumentTypeLocal_id",
            ds.DocumentsSigning_IsSigned as "DocumentsSigning_IsSigned",
           (select string_agg(ds2.EMDPersonRole_id::text, ',')
               from dbo.DocumentsSigning ds2
                    where
                        ds2.DocumentsSigning_ObjectID = ds.DocumentsSigning_ObjectID
                        and (ds2.DocumentsSigning_IsSigned is NULL OR ds2.DocumentsSigning_IsSigned = 1)
                        and (ds2.DocumentsSigning_deleted = 1 or ds2.DocumentsSigning_deleted is null)) as "needEMDPersonRole_id",
            ds.Person_id as "Person_id",
            ds.LpuSection_id as "LpuSection_id",
            ds.DocumentsSigning_deleted as "DocumentsSigning_deleted",
            ds.DocumentsSigning_ObjectName as "DocumentsSigning_ObjectName",
            ds.DocumentsSigning_ObjectID as "DocumentsSigning_ObjectID",
            ds.DocumentsSigning_ObjectID as "Evn_id",
            to_char(ds.DocumentsSigning_setDT, 'DD.MM.YYYY HH24:MI:SS') as "Evn_insDT",
            to_char(ds.DocumentsSigning_updDT, 'DD.MM.YYYY HH24:MI:SS') as "Evn_updDT",
            psm.Person_SurName || coalesce(' ' || psm.Person_FirName,'') || coalesce(' ' || psm.Person_SecName,'') as "MedPersonal_Fio",
            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",
            ex.EvnXml_id as "EvnXml_id",
                CASE
         WHEN ds.DocumentsSigning_ObjectName = 'BirthSvid' then concat(bsv.BirthSvid_Prefix, bsv.BirthSvid_Num)::text  WHEN ds.DocumentsSigning_ObjectName = 'EvnStickStudent' then ess.EvnStickStudent_Num::text  WHEN ds.DocumentsSigning_ObjectName = 'EvnReceptGeneral' then erg.EvnReceptGeneral_Num::text  WHEN ds.DocumentsSigning_ObjectName = 'EvnPLDispDriver' then epldd.EvnPLDispDriver_Num::text  WHEN ds.DocumentsSigning_ObjectName = 'PntDeathSvid' then pds.PntDeathSvid_Num::text  WHEN ds.DocumentsSigning_ObjectName = 'EvnXml' then ex2.Evn_id::text  WHEN ds.DocumentsSigning_ObjectName = 'EvnPLStom' then epls.EvnPLStom_NumCard::text  WHEN ds.DocumentsSigning_ObjectName = 'DeathSvid' then dsv.DeathSvid_Num::text  WHEN ds.DocumentsSigning_ObjectName = 'EvnPL' then epl.EvnPL_NumCard::text  WHEN ds.DocumentsSigning_ObjectName = 'EvnDirection' then eda.EvnDirection_Num::text 
        ELSE ds.DocumentsSigning_ObjectID::text
    END as "DocNum",
             case when ex2.Evn_id is null then 1 else ROW_NUMBER() OVER (PARTITION BY ex2.Evn_id ORDER BY ex2.EvnXml_id DESC) end as ColNumEvnXml,
            ROW_NUMBER() OVER (PARTITION BY ds.DocumentsSigning_ObjectID ORDER BY ds.DocumentsSigning_deleted) as ColNum
        FROM dbo.DocumentsSigning ds
        LEFT JOIN v_PersonState ps on ps.Person_id = ds.Person_id
        left join lateral (
                    SELECT DISTINCT mpc.Person_id
                        FROM MedPersonalCache mpc
                    WHERE mpc.MedPersonal_id = ds.MedPersonal_id) as mpcpid on true
                LEFT JOIN v_PersonState psm on psm.Person_id = mpcpid.Person_id
        LEFT JOIN v_EvnXml ex on ex.Evn_id = ds.DocumentsSigning_ObjectID
         LEFT JOIN v_BirthSvid bsv on bsv.BirthSvid_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_EvnStickStudent ess on ess.EvnStickStudent_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_EvnReceptGeneral erg on erg.EvnReceptGeneral_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_EvnPLDispDriver epldd on epldd.EvnPLDispDriver_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_PntDeathSvid pds on pds.PntDeathSvid_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_EvnXml ex2 on ex2.EvnXml_id = ds.DocumentsSigning_ObjectID  LEFT JOIN EvnDirection ed on ed.Evn_id = ex.Evn_id LEFT JOIN v_EvnPLStom epls on epls.EvnPLStom_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_DeathSvid dsv on dsv.DeathSvid_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_EvnPL epl on epl.EvnPL_id = ds.DocumentsSigning_ObjectID  LEFT JOIN v_EvnDirection_all eda on eda.EvnDirection_id = ds.DocumentsSigning_ObjectID  WHERE ds.Person_id IS NOT NULL AND ds.EMDDocumentTypeLocal_id in (382,1,175,170,6,8,40,4,23,57,24,76,25,28,41,31,160,172,173,174,59,161,384,12,100068,171,49,9,56,58,69,71,81,27,88,89,90,91,96,393,106,109,111,119,121,122,137,142,26,371,372,373,374,375,379,383,385,386,389,390,394,397,398,399,400,401,402,405,406,409,410,411,413,414,415,416,418,419,420,421,422,17,100040,100041,100063,100064,100065,100066,77,100067)
            AND ds.EMDPersonRole_id in (1)  AND ds.Lpu_id = 4314984
                AND cast(ds.DocumentsSigning_setDT as date) >= '2022-07-25' AND cast(ds.DocumentsSigning_setDT as date) <= '2023-07-31'
             AND (ds.DocumentsSigning_deleted = 1 or ds.DocumentsSigning_deleted is null) AND coalesce(ds.DocumentsSigning_IsSigned, 1) = 1 AND ds.DocumentsSigning_isNotice = 2 AND ds.DocumentsSigning_MinCount > 0  and coalesce(ed.EvnDirection_IsConcilium, 1) = 1
            ) part
            WHERE part.ColNum = 1  and part.ColNumEvnXml = 1
            offset 0 limit 100
        
