
            SELECT
                dt."EMDDocumentType_id",		/*критичный параметр для отличия EvnXml типов*/
                emdr."EMDRegistry_ObjectName",
                emdr."EMDRegistry_ObjectID",
                dtl."EMDDocumentTypeLocal_Name",
                case when 											/*1-требуется подпись МО*/
                    dtl."EMDDocumentTypeLocal_IsNeedSignatures" = 1 and sign."MedStaffFact_id" is not null
                    then 1 else 0
                end "isLpuSignNeeded",
                dtl."EMDDocumentTypeLocal_IsNeedSignatures" as "IsNeedSignatures",	/*ожидается подпись МО из настроек*/
                almp.cnt as "MinSignCount",
                emds.cnt as "SignCount"
            FROM
                "EMD"."EMDRegistry" emdr
                inner join "EMD"."EMDDocumentTypeLocal" dtl on dtl."EMDDocumentTypeLocal_id" = emdr."EMDDocumentTypeLocal_id" and dtl."EMDDocumentTypeLocal_IsREMD" = 2 and dtl."EMDDocumentType_id" is not null
                inner join "EMD"."EMDDocumentType" dt on dt."EMDDocumentType_id" = dtl."EMDDocumentType_id"
                left join lateral (
                    SELECT emdr."EMDRegistry_id",
               			emdv."EMDVersion_id",
               			emdv."EMDVersion_IsReady",
               			emdv."EMDVersion_RegistrationDate"
        			FROM "EMD"."EMDVersion" emdv
        			WHERE emdr."EMDRegistry_id" = emdv."EMDRegistry_id"
                    ORDER BY emdv."EMDVersion_id" DESC
        			LIMIT 1) version on true
        		left join "EMD"."EMDVersionStatus" emdvs on version."EMDVersion_IsReady" = emdvs."EMDVersionStatus_id"
                left join lateral(
                    SELECT
                        emdv."EMDVersion_id",
                        emds."MedStaffFact_id",
                        emds."Signatures_id",
                        emdv."EMDVersion_insDT",
                        emdv."EMDVersion_RegistrationDate"
                    FROM "EMD"."EMDVersion" emdv
                    left join "EMD"."EMDSignatures" emds on emds."EMDVersion_id" = emdv."EMDVersion_id"
                    WHERE emdv."EMDRegistry_id" = emdr."EMDRegistry_id"
                    AND emds."Signatures_id" is not null
                    ORDER BY emdv."EMDVersion_id" desc, emds."MedStaffFact_id" desc
                    LIMIT 1
                ) sign on true

                /*кол-во требуемых и имеющихся подписей*/
                inner join lateral (
                    select
                        count("EMDSignatures_id") as cnt
                    from
                        "EMD"."EMDSignatures"
                    where
                        "EMDVersion_id" = version."EMDVersion_id"
                ) emds on true
                inner join lateral (
                    select SUM (tmp."EMDSignatureRules_MinCount") as cnt from (
                        select distinct
                            sr."EMDPersonRole_id",
                            sr."EMDSignatureRules_MinCount"
                        from
                            "EMD"."ApprovalList" al
                            inner join "EMD"."ApprovalListMedPersonal" almp on almp."ApprovalList_id" = al."ApprovalList_id"
                            inner join "EMD"."EMDSignatureRules" sr on sr."EMDPersonRole_id" = almp."EMDPersonRole_id"
                                and dt."EMDDocumentType_id" = sr."EMDDocumentType_id"
                        where
                            al."ApprovalList_ObjectName" = emdr."EMDRegistry_ObjectName"
                            and al."ApprovalList_ObjectId" = emdr."EMDRegistry_ObjectID"
                    ) tmp
                ) almp on true
            WHERE
                sign."Signatures_id" is not null  and (emdr."EMDRegistry_deleted" = 1 or emdr."EMDRegistry_deleted" is null)  and emdr."Lpu_id" = '9990010252' 
                and emdr."EMDRegistry_EMDDate" >= '2023-04-01 00:00:00'
                and emdr."EMDRegistry_EMDDate" <= '2023-07-21 23:59:59'
              and 1=1 
                and dtl."EMDDocumentTypeLocal_IsNeedSignatures" = 1 and sign."MedStaffFact_id" is not null OFFSET 0 ROWS FETCH NEXT 5000 ROWS ONLY
