
                
                
                        with curDT as (select localtimestamp as curDate),
                        klp_code_prep as (
                            select
                                ICMP.CatalogMedProduct_KTRU_CODE as Code,
                                CMPPL.Prep_id
                            from
                                integra.CatalogMedProduct ICMP
                                left join v_catalogmedproductpreplink CMPPL on CMPPL.CatalogMedProduct_id = ICMP.CatalogMedProduct_id
                            where 1=1
                                and CMPPL.Prep_id is not null
                                and (
                                    ICMP.CatalogMedProduct_endDT is null
                                    or ICMP.CatalogMedProduct_endDT > (select curDate from curDT)
                                )
                                --and ICMP.CatalogMedProduct_KTRU_CODE like '21.2%'
                        ),
                        klp_code_am as (
                            select
                                NMN.MedNutrition_Code as Code,
                                RDCM.ActMatters_id as ActMatters_id,
                                NMNL.Prep_id,
                                NMNL.Drug_id,
                                NMNL.Region_id
                            from
                                nsi.MedNutrition NMN
                                left join nsi.MedNutritionLink NMNL on NMNL.MedNutrition_id = NMN.MedNutrition_id
                                left join rls.DrugComplexMnn RDCM on RDCM.DrugComplexMnn_id = NMNL.DrugComplexMnn_id
                            where 1=1
                                and (NMN.MedNutrition_endDT is null or NMN.MedNutrition_endDT > (select curDate from curDT))
                                --and RDCM.ActMatters_id is not null
                                --and NMN.MedNutrition_Code like '21.2%'
                        )
                        
                SELECT 
                        
                    
                    coalesce(
                        (
                            select string_agg(cast(ID.KLP_CODE as varchar),',') as codes
                            from (
                                select
                                    ID.KLP_CODE
                                from
                                    v_IntegraDrugRlsLink IDRL
                                    left join integra.Drug ID on ID.id = IDRL.Drug_iid
                                where 1=1
                                    and IDRL.Region_id = 52
                                    and IDRL.Drug_rlsid = Nomen.NOMEN_ID
                                    and (ID.Drug_EndDT is null or ID.Drug_EndDT > (select curDate from curDT))
                                order by IDRL.IntegraDrugRlsLink_id desc
                                limit 4
                            ) ID
                        ),
                        kc_p.Code,
                        kc_am.Code
                    )
                 as "RlsKTRU_Code",
                rcr.CodeRZN_Code as "CodeRZN_Code",
                        Nomen.NOMEN_ID as "RlsNomen_id",
                        PREP.Prep_id as "RlsPrep_id",
                        TRADENAMES.TRADENAMES_ID as "RlsTorg_id",
                        TRADENAMES.NAME as "RlsTorg_Name",
                        case when ltrim(RC.RlsPack_Code) != 'Признак АНГРО - нет' then RC.RlsPack_Code else TRADENAMES.NAME end as "RlsPack_Code",
                        (case when FIRMNAMES.NAME is not null then FIRMNAMES.NAME else FIRMS.FULLNAME end) as "RlsFirms_Name",
                        REGCERT.REGNUM as "RlsRegcert_Number",
                        to_char(cast(REGCERT.REGDATE as timestamp(3)), 'DD.MM.YYYY') as "RlsRegcert_Date",

                        COUNTRIES.NAME as "RlsCountries_Name",
                        COALESCE(Nomen.PrepType_id, 1) as "RlsPrepType_id",

                        COALESCE(PREP.NTFRID, 1) as "RlsNTFR_id",
                        COALESCE(NTFR.PARENTID, 1) as "RlsNTFRparent_id",
                        case when (NTFR.PARENTID not in (1, 176) and
                            NTFR.CLSNTFR_ID not in (1, 137, 138, 139, 140, 141, 142, 144, 146, 149, 153, 159, 176, 180, 181, 184, 199, 207))
                            then 2
                            else
                                case when (NTFR.CLSNTFR_ID = 176 OR NTFR.PARENTID = 176) then 3
                                else 1
                                end
                        end AS "NTFRClass",
                        CLSDRUGFORMS.NAME as "RlsForm_Name",
                        drug_dose."RlsPrep_DrugDose" as "RlsPrep_DrugDose",
                        concat('c ',REGCERT.REGDATE,(
                      		case when REGCERT.REGCERT_excDT is not null then concat(' искл. ', REGCERT.REGCERT_excDT)
                      		else
                      			case when REGCERT.Reregdate is not null then concat(' по ', REGCERT.ENDDATE,', перерег. ',REGCERT.Reregdate)
                      			else
                      				case when REGCERT.ENDDATE is not null then concat(' по ',REGCERT.ENDDATE)
                      				else ''
                      				end
                      			end
                      		end
                        )) as "RlsRegcert_Period",
                        ACTMATTERS.RUSNAME as "RlsActmatters_RusName",
                        concat(
                            concat('Фасовка - №', case when Drug.Drug_Fas is not null then cast(Drug.Drug_Fas as varchar) else '1' end),(
                            concat(case when Drug.Drug_Mass is not null then concat(', ',Drug.Drug_Mass) else '' end,
                            case when Drug.Drug_Volume is not null then concat(', ',Drug.Drug_Volume) else '' end)
                        )) as "Drug_Fas",
                        Drug.Drug_Name as "RlsDrug_Name",
                        Drug.Drug_id as "RlsDrug_id"
                        
                FROM
                
                        rls.v_Nomen Nomen
                        INNER JOIN rls.v_PREP PREP on PREP.Prep_id = Nomen.PREPID
                        LEFT JOIN rls.CLSNTFR NTFR on NTFR.CLSNTFR_ID = PREP.NTFRID
                        INNER JOIN rls.v_TRADENAMES TRADENAMES on TRADENAMES.TRADENAMES_ID = PREP.TRADENAMEID
                        LEFT JOIN LATERAL (
                            select
                                (DP.NAME ||
                                (case when N.DRUGSINPPACK is not null and N.DRUGSINPPACK > 0 then cast(N.DRUGSINPPACK as varchar(10)) else '' end) || ' ' ||
                                (case when N.PPACKMASS is not null then cast(cast(N.PPACKMASS as double precision) as varchar(14)) else '' end)
                                    || coalesce(MU.SHORTNAME, '') ||  ' ' ||
                                (case when N.PPACKVOLUME is not null then cast(cast(N.PPACKVOLUME as double precision) as varchar(14)) || coalesce(CU.SHORTNAME, '') else '' end) ||
                                ' ' || coalesce(DS.SHORTNAME, '') || ' ' || coalesce(UPD.NAME, '') ||
                                (case when N.PPACKINUPACK is not null and N.PPACKINUPACK > 0 then cast(N.PPACKINUPACK as varchar(10)) else '' end) || ' ' || coalesce(SPD.NAME, '') ||
                                (case when N.UPACKINSPACK is not null then cast(N.UPACKINSPACK as varchar(10)) else '' end) || ' Признак АНГРО - ' ||
                                (case when N.INANGRO = 'N' then 'нет' else 'да' end)) as RlsPack_Code
                            from
                                rls.v_Nomen N
                                left join rls.v_DRUGPACK DP  on DP.DRUGPACK_ID = N.PPACKID
                                left join rls.v_MASSUNITS MU  on MU.MASSUNITS_ID = N.PPACKMASSUNID
                                left join rls.v_CUBICUNITS CU  on CU.CUBICUNITS_ID = N.PPACKCUBUNID
                                left join rls.v_DRUGSET DS  on DS.DRUGSET_ID = N.SETID
                                left join rls.v_DRUGPACK UPD  on UPD.DRUGPACK_ID = N.UPACKID
                                left join rls.v_DRUGPACK SPD  on SPD.DRUGPACK_ID = N.SPACKID
                            where
                                N.NOMEN_ID = Nomen.NOMEN_ID
                        ) as RC ON TRUE
                        LEFT JOIN rls.v_Drug Drug  on Drug.Drug_id = Nomen.NOMEN_ID
                        left JOIN LATERAL (
                            select PREPID, MATTERID from rls.PREP_ACTMATTERS where PREPID = PREP.Prep_id limit 1
                        ) as PREP_ACTMATTERS ON TRUE
                         left join klp_code_prep kc_p on kc_p.Prep_id = Nomen.PREPID left join klp_code_am kc_am on (kc_am.ActMatters_id = PREP_ACTMATTERS.MATTERID
                                    or kc_am.Prep_id = Nomen.PREPID
                                    or (kc_am.Drug_id = Nomen.NOMEN_ID and kc_am.Region_id = 52))left join rls.v_coderzn rcr on PREP.Prep_id = rcr.Prep_id
                        left join rls.v_SIZEUNITS SIZEUNITS on SIZEUNITS.SIZEUNITS_ID = PREP.DFSIZEID
                        left join rls.v_MASSUNITS MASSUNITS on MASSUNITS.MASSUNITS_ID = PREP.DFMASSID
                        LEFT JOIN rls.v_REGCERT REGCERT  on REGCERT.REGCERT_ID = PREP.REGCERTID
                        LEFT JOIN rls.v_FIRMS FIRMS  on FIRMS.FIRMS_ID = PREP.FIRMID
                        left join rls.v_FIRMNAMES FIRMNAMES  on FIRMNAMES.FIRMNAMES_ID = FIRMS.NAMEID --
                        LEFT JOIN rls.v_COUNTRIES COUNTRIES  on COUNTRIES.COUNTRIES_ID = FIRMS.COUNTID
                        LEFT join rls.v_CONCENUNITS CONC on CONC.CONCENUNITS_ID = PREP.DFCONCID
                        left join rls.v_ACTUNITS ACT on ACT.ACTUNITS_ID = PREP.DFACTID
                        LEFT JOIN rls.v_ACTMATTERS ACTMATTERS on ACTMATTERS.ACTMATTERS_ID = PREP_ACTMATTERS.MATTERID
                        left join lateral( select  PREPID, UNIQID, PHGRID from rls.PREP_IIC where PREPID = PREP.Prep_id limit 1 ) as PREP_IIC on true
                        LEFT JOIN rls.v_CLSIIC CLSIIC  on CLSIIC.CLSIIC_ID = PREP_IIC.UNIQID
                        LEFT JOIN rls.v_CLSDRUGFORMS CLSDRUGFORMS  on CLSDRUGFORMS.CLSDRUGFORMS_ID = PREP.DRUGFORMID
                        LEFT JOIN rls.TRADENAMES_DRUGFORMS TRADENAMES_DRUGFORMS  on TRADENAMES_DRUGFORMS.DRUGFORMID = CLSDRUGFORMS.CLSDRUGFORMS_ID
                            and TRADENAMES.TRADENAMES_ID = TRADENAMES_DRUGFORMS.TRADENAMEID
                        LEFT JOIN LATERAL (
                            select  ACTMATTERS_DRUGFORMS.ACTMATTERID, ACTMATTERS_DRUGFORMS.MZ_PHGR_ID from
                            rls.ACTMATTERS_DRUGFORMS ACTMATTERS_DRUGFORMS
                            where ACTMATTERS_DRUGFORMS.ACTMATTERID = ACTMATTERS.ACTMATTERS_ID
                            limit 1
                        ) as ACTMATTERS_DRUGFORMS on true
                        left join lateral (
                            select case
                                       when (PREP.DFSIZE is not null)
                                       then concat(concat(PREP.DFSIZE, ' '), SIZEUNITS.SHORTNAME)
                                       else
                                           case
                                               when (PREP.DFMASS is not null)
                                               then
                                               concat(concat(cast(cast(PREP.DFMASS as numeric(17, 2)) as varchar), ' '),
                                                      MASSUNITS.SHORTNAME)
                                               else
                                                   case
                                                       when (PREP.DFCONC is not null)
                                                       then
                                                       concat(
                                                        concat(
                                                            cast(cast(PREP.DFCONC as numeric(17, 2)) as varchar), ' '),
                                                                  CONC.SHORTNAME)
                                                       else
                                                           concat(
                                                            concat(
                                                            cast(cast(PREP.DFACT as numeric(17, 2)) as varchar), ' '),
                                                                  ACT.SHORTNAME)
                                                       end
                                               end
                                       end as "RlsPrep_DrugDose"
                        ) drug_dose on true
                        
                         WHERE 
                        (1 = 1) and 
                    coalesce(
                        (
                            select string_agg(cast(ID.KLP_CODE as varchar),',') as codes
                            from (
                                select
                                    ID.KLP_CODE
                                from
                                    v_IntegraDrugRlsLink IDRL
                                    left join integra.Drug ID on ID.id = IDRL.Drug_iid
                                where 1=1
                                    and IDRL.Region_id = 52
                                    and IDRL.Drug_rlsid = Nomen.NOMEN_ID
                                    and (ID.Drug_EndDT is null or ID.Drug_EndDT > (select curDate from curDT))
                                order by IDRL.IntegraDrugRlsLink_id desc
                                limit 4
                            ) ID
                        ),
                        kc_p.Code,
                        kc_am.Code
                    )
                 ilike '%0094%'
                        
                
                        ORDER BY
                            
                        TRADENAMES.NAME
                        
                OFFSET 0 ROWS
                FETCH NEXT 50 ROWS ONLY
                
