
            select distinct
                RTRIM(coalesce(Drug.Drug_Nomen, '')) as "Drug_Name",
                RTRIM(coalesce(Drug.Drug_Name, '')) as "Drug_FullName",
                Drug.Drug_id as "Drug_id",
                Drug.DrugPrepFas_id as "DrugPrepFas_id",
                Drug.Drug_Code as "Drug_Code",
                Drug.Drug_Fas as "Drug_Fas",
                RTRIM(coalesce(Drug.DrugForm_Name, '')) as "DrugForm_Name",
                RTRIM(coalesce(Drug.Drug_PackName, '')) as "DrugUnit_Name",
                B_GU.GoodsUnit_id as "GoodsUnit_bid",
                B_GU.GoodsUnit_Nick as "GoodsUnit_bName",
                (case
                    when B_GU.GoodsUnit_id = '57' then 1
                    else B_GPC.GoodsPackCount_Count
                end) as "GoodsPackCount_bCount"
            from rls.v_Drug Drug
                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 v_DrugShipmentLink DSL on DSL.DrugShipment_id = DS.DrugShipment_id
                left join v_DocumentUcStr DUS on DUS.DocumentUcStr_id = DSL.DocumentUcStr_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 v_GoodsUnit B_GU on B_GU.GoodsUnit_id = coalesce(DUS.GoodsUnit_bid, '57')
                left join lateral(
                    select
                        I_GPC.GoodsPackCount_Count
                    from
                        v_GoodsPackCount I_GPC
                    where
                        I_GPC.GoodsUnit_id = B_GU.GoodsUnit_id and
                        I_GPC.DrugComplexMnn_id = Drug.DrugComplexMnn_id and
                        (
                            Drug.DrugTorg_id is null or
                            I_GPC.TRADENAMES_ID is null or
                            I_GPC.TRADENAMES_ID = Drug.DrugTorg_id
                        )
                    order by
                        i_gpc.TRADENAMES_ID desc, i_gpc.Org_id
                    limit 1
                ) B_GPC on true
            where
                1=1
                 and cast(DS.DrugShipment_setDT as date) <= '2023-07-21' and DOR.DrugOstatRegistry_Kolvo > 0 and isdef.YesNo_id = 1 and (coalesce(PS.PrepSeries_GodnDate,'2023-07-21') >= '2023-07-21')
            limit 1000
        
