
            select distinct
                RTRIM(coalesce(DrugPrep.DrugPrep_Name, '')) as "DrugPrep_Name",
                DrugPrep.DrugPrep_id as "DrugPrep_id",
                DrugPrep.DrugPrepFas_id as "DrugPrepFas_id",
                DOR.Storage_id as "Storage_id",
                Drug.Drug_Nomen as "hintPackagingData",  --Данные об упаковке – это часть поля Упаковка (drug_nomen) от начала строки до данных о производителе.
                coalesce(Drug.Drug_RegNum
                    || ',   ', '') || coalesce(to_char(Drug.Drug_begDate, 'dd.mm.yyyy')
                    || ', ', '') || coalesce(to_char(Drug.Drug_endDate, 'dd.mm.yyyy')
                    ||', ', '--, ')
                || coalesce(to_char(REG.REGCERT_insDT, 'dd.mm.yyyy')||', ', '')
                || coalesce(REGISTR.regNameCauntries, '') as "hintRegistrationData", --Данные о регистрации
                case
                    when NOMENF.FIRMS_ID = MANUFACTURF.FIRMS_ID
                    then coalesce(MANUFACTURF.FULLNAME, '')
                    else
                        case
                            when (NOMENF.FULLNAME IS NULL OR rtrim(NOMENF.FULLNAME)='') AND (MANUFACTURF.FULLNAME IS NULL OR rtrim(MANUFACTURF.FULLNAME)='')
                                then coalesce(REGISTR.regNameCauntries, '')
                            when NOMENF.FULLNAME IS NULL OR rtrim(NOMENF.FULLNAME)=''
                                then coalesce(MANUFACTURF.FULLNAME, '')
                            else coalesce(MANUFACTURF.FULLNAME, '') || ' / ' || NOMENF.FULLNAME
                        end
                end as "hintPRUP",	--ПР./УП.
                FNM.NAME as "FirmNames",
                coalesce(Drug.Drug_Ean,EAN.DrugEAN_Code) as "DrugEAN_Code"
            from
                rls.v_DrugPrep DrugPrep
                inner join rls.v_Drug Drug on Drug.DrugPrepFas_id = DrugPrep.DrugPrepFas_id
                inner join v_DrugOstatRegistry DOR on DOR.Drug_id = Drug.Drug_id
                inner join v_DrugShipment DS on DS.DrugShipment_id = DOR.DrugShipment_id
                left join rls.v_PrepSeries PS on PS.PrepSeries_id = DOR.PrepSeries_id
                left join lateral (
                    select
                        *
                    from
                        rls.v_PrepBlock pb
                    where
                        pb.Drug_id = ps.Drug_id and
                        pb.PrepSeries_Ser = ps.PrepSeries_Ser
                    limit 1
                ) pb on true
                left join v_YesNo isdef on isdef.YesNo_id = (case when pb.PrepBlock_id is not null then 2 else 1 end)
                left join rls.DrugEAN EAN on EAN.Drug_id = Drug.Drug_id
                left join rls.PREP P on P.Prep_id = Drug.DrugPrep_id
                left join rls.REGCERT REG on REG.REGCERT_ID =P.REGCERTID
                left join rls.NOMEN NM on NM.Nomen_id = Drug.Drug_id
                left join rls.FIRMS NOMENF on NM.FIRMID = NOMENF.FIRMS_ID
                left join rls.FIRMS MANUFACTURF on P.FIRMID = MANUFACTURF.FIRMS_ID
                left join rls.FIRMNAMES FNM on FNM.FIRMNAMES_ID = MANUFACTURF.NAMEID
                
                left join lateral(
                    SELECT
                        FN.NAME || ' (' || C.NAME || ')' as regNameCauntries
                    FROM
                        rls.REGCERT_EXTRAFIRMS RE
                        left join rls.FIRMS F on RE.FIRMID = F.FIRMS_ID
                        left join rls.FIRMNAMES FN on FN.FIRMNAMES_ID = F.NAMEID
                        left join rls.COUNTRIES C on C.COUNTRIES_ID = F.COUNTID
                    WHERE RE.CERTID = P.REGCERTID
                    limit 1
                ) REGISTR on true
            where (1=1)
                 and (EAN.DrugEAN_Code = '4602824000097' or Drug.Drug_Ean = '4602824000097') and cast(DS.DrugShipment_setDT as date) <= '2022-03-31' and DOR.DrugOstatRegistry_Kolvo > 0 and isdef.YesNo_Code = 0 and (coalesce(PS.PrepSeries_GodnDate,'2022-03-31') >= '2022-03-31')
            limit 500
        
