		
			--  Создаем временную таблицу для оптимизации запроса
			with dor as (
                select
                    i_dor.DrugShipment_id,
                    i_dor.DrugFinance_id,
                    i_dor.PrepSeries_id,
                    i_dor.Drug_id,
                    i_dor.DrugOstatRegistry_Cost,
                    sum(DrugOstatRegistry_Kolvo) as DrugOstatRegistry_Kolvo
                from
                    v_DrugOstatRegistry i_dor
                    inner join rls.v_drug dr on dr.Drug_id = i_dor.Drug_id
                    inner join rls.v_drug dr0 on  dr.DrugComplexMnn_id = dr0.DrugComplexMnn_id
                    and dr0.Drug_id = case when 2 = 2 then dr.Drug_id  else dr0.Drug_id end 	
                where
                    1=1 and i_dor.Storage_id = '6767'
and (
	i_dor.SubAccountType_id = 1 or -- субсчет Доступно
	(
		i_dor.SubAccountType_id = 2 and -- субсчет Резерв
		exists ( -- учитывать нужно только резерв являющийся резервом под EvnDrug
			select
				i_dorl.DrugOstatRegistryLink_id
			from
				v_DrugOstatRegistryLink i_dorl
				inner join v_EvnDrug i_ed on i_ed.DocumentUcStr_id = i_dorl.DrugOstatRegistryLink_TableID
			where
				i_dorl.DrugOstatRegistry_id = i_dor.DrugOstatRegistry_id and
				i_dorl.DrugOstatRegistryLink_TableName = 'DocumentUcStr'
				and 50 <> 2
		)
	)
)
                group by
                    i_dor.DrugShipment_id,
                    i_dor.DrugFinance_id,
                    i_dor.PrepSeries_id,
                    i_dor.Drug_id,
                    i_dor.DrugOstatRegistry_Cost			
			),
			mv as (
				select distinct
					DUS.DocumentUcStr_id,
					DUS.DocumentUc_id,
					PS.PrepSeries_Ser,
					PS.PrepSeries_GodnDate,
					DF.DrugFinance_id,
					DF.DrugFinance_Name,
					DrugOstatRegistry_Kolvo,
					DrugOstatRegistry_Cost,
					DSL.DrugShipment_id,
					coalesce(DUS.GoodsUnit_id, GPC.GoodsUnit_id, '57') as GoodsUnit_id,
					coalesce(DUS.GoodsUnit_bid, '57') as GoodsUnit_bid,
					D.DrugComplexMnn_id,
					D.DrugTorg_id
				from
					v_DrugShipmentLink DSL				
					inner join v_DrugShipment DS on DS.DrugShipment_id = DSL.DrugShipment_id
					inner join dor on dor.DrugShipment_id = DSL.DrugShipment_id
					inner join v_DocumentUcStr DUS on DUS.DocumentUcStr_id = DSL.DocumentUcStr_id
					inner join v_DrugFinance DF on DF.DrugFinance_id = dor.DrugFinance_id
					inner join rls.v_PrepSeries PS on PS.PrepSeries_id = dor.PrepSeries_id
					left join rls.v_PrepBlock pb on pb.Drug_id = PS.Drug_id and pb.PrepSeries_Ser = PS.PrepSeries_Ser
					left join v_YesNo isdef on isdef.YesNo_id = (case when pb.PrepBlock_id is not null then 2 else 1 end)
					inner join rls.v_Drug D on D.Drug_id = dor.Drug_id
					left join lateral(
						select
						    GPC.GoodsUnit_id
						from
						    v_GoodsPackCount GPC
						where
						    GPC.DrugComplexMnn_id = D.DrugComplexMnn_id
						limit 1
					) GPC on true
				where
					1=1 and cast(DS.DrugShipment_setDT as date) <= '2022-01-19' and isdef.YesNo_id = 1 and (coalesce(PS.PrepSeries_GodnDate, '2022-01-19') >= '2022-01-19')
			)
			
			-- Выполняем запрос с использованием временной таблицы	
			Select
				t.DocumentUcStr_id as "DocumentUcStr_id",
				t.DocumentUc_id as "DocumentUc_id",
				t.PrepSeries_Ser as "PrepSeries_Ser",
				coalesce(t.PrepSeries_GodnDate, '3000-01-01') as "gDate",
				to_char(t.PrepSeries_GodnDate, 'dd.mm.yyyy') as "PrepSeries_GodnDate",
				ost.DocumentUcStr_Count as "DocumentUcStr_Count",
				ost.DocumentUcStr_Price as "DocumentUcStr_Price",
				ost.DocumentUcStr_Sum as "DocumentUcStr_Sum",
				t.DrugFinance_id as "DrugFinance_id",
				t.DrugFinance_Name as "DrugFinance_Name",
				(
					'годн. '||coalesce(to_char(t.PrepSeries_GodnDate, 'dd.mm.yyyy'),'отсут.')
					||', цена '|| ROUND(coalesce(ost.DocumentUcStr_Price,0), 2)
					||', ост. '|| cast(Round(coalesce(ost.DocumentUcStr_Count,0),4) as numeric(30,4))
					||', фин. '||RTRIM(RTRIM(coalesce(t.DrugFinance_Name, 'отсут.')))
					||', серия '||RTRIM(coalesce(t.PrepSeries_Ser, ''))
				) as "DocumentUcStr_Name",
				t.GoodsUnit_id as "GoodsUnit_id",
				t.GoodsUnit_bid as "GoodsUnit_bid",
				gu_b.GoodsUnit_Nick as "GoodsUnit_bNick",
				coalesce(b_gpc.GoodsPackCount_Count, 1) as "GoodsPackCount_bCount"
			from
				mv t			
				left join lateral(
					select
					    coalesce(sum(rED.EvnDrug_Kolvo),0) as EvnDrug_Kolvo
					from
					    v_EvnDrug rED
                        inner join v_DocumentUcStr rDUS on rDUS.DocumentUcStr_id = rED.DocumentUcStr_id
                        inner join v_DocumentUc rDU on rDU.DocumentUc_id = rDUS.DocumentUc_id
                        inner join v_DrugShipmentLink oDSL on oDSL.DocumentUcStr_id = rED.DocumentUcStr_oid
					where
					    oDSL.DrugShipment_id = t.DrugShipment_id
						and coalesce(rDU.DrugDocumentStatus_id,1) = 1 --новый	
						and 50 <> 2
				) rl on true
				
inner join lateral(
					select
						(t.DrugOstatRegistry_Kolvo - coalesce(rl.EvnDrug_Kolvo,0)) as DocumentUcStr_Count,
						t.DrugOstatRegistry_Cost as DocumentUcStr_Price,
						((t.DrugOstatRegistry_Kolvo - coalesce(rl.EvnDrug_Kolvo,0)) * t.DrugOstatRegistry_Cost) as DocumentUcStr_Sum
					limit 1
				) ost on true
		
				left join v_GoodsUnit gu_b on gu_b.GoodsUnit_id = t.GoodsUnit_bid
				left join lateral(
                    select
                        i_gpc.GoodsPackCount_Count
                    from
                        v_GoodsPackCount i_gpc
                    where
                        i_gpc.GoodsUnit_id = t.GoodsUnit_bid and
                        i_gpc.DrugComplexMnn_id = t.DrugComplexMnn_id and
                        (
                            t.DrugTorg_id is null or
                            i_gpc.TRADENAMES_ID is null or
                            i_gpc.TRADENAMES_ID = t.DrugTorg_id
                        )
                    order by
                        i_gpc.TRADENAMES_ID desc
                    limit 1
                ) b_gpc on true
			where
				coalesce(DocumentUcStr_Count,0) > 0
			order by
				coalesce(t.PrepSeries_GodnDate, '3000-01-01')
		
