
                
                
            with curDT as (select localtimestamp as curDate)
            
                SELECT distinct
                ds.DrugShipment_id as "DrugShipment_id",
                -- dors.DrugOstatRegistry_ids as DrugListKey,
                dors.DrugOstatRegistry_ids as "DrugOstatRegistry_ids",
                dor.Drug_id as "Drug_id",
                dor.Drug_Code as "Drug_Code",
                dor.Drug_Name as "Drug_Name",
                dor.PrepSeries_Ser as "PrepSeries_Ser",
                dor.PrepSeries_id as "PrepSeries_id",
                s.Storage_id as "Storage_id",
                dor.PrepSeries_GodnDate as "PrepSeries_GodnDate",
                psmc.PrepSeries_MonthCount as "PrepSeries_MonthCount",
                ostat.kolvo as "DrugCount",
                gu.GoodsUnit_Name as "GoodsUnit_Name",
                rtrim(ltrim( COALESCE(dor.DrugOstatRegistry_Cost,'') || ' ' || COALESCE(dor.DrugFinance_Name,'') || ' ' || COALESCE(dor.WhsDocumentCostItemType_Name,'') || ' ' || COALESCE(wds.WhsDocumentUc_Num,'') || ' ' || COALESCE(ds.DrugShipment_Name,'')  || ' ' || COALESCE(to_char(ds.DrugShipment_setDT, 'DD.MM.YYYY'),''))) as "DrugShipment_Row"


            
                FROM
                
                v_Storage s

                inner join v_StorageStructLevel ssl  on ssl.Storage_id = s.Storage_id

                left join v_Lpu lpu  on lpu.Lpu_id = ssl.Lpu_id

                inner join v_DrugOstatRegistry door  on COALESCE(ssl.Org_id,lpu.Org_id) = door.Org_id and s.Storage_id = door.Storage_id


                left join v_DrugShipment ds  on ds.DrugShipment_id = door.DrugShipment_id

                left join v_WhsDocumentSupply wds  on wds.WhsDocumentSupply_id = ds.WhsDocumentSupply_id

                left join v_GoodsUnit gu  on COALESCE(gu.GoodsUnit_id,
                '57'
                ) = COALESCE(door.GoodsUnit_id,
                '57'
                )


                LEFT JOIN LATERAL (

                    SELECT
                        (SELECT
                            string_agg(cast(dor2.DrugOstatRegistry_id as varchar(20)), ',')
                        FROM
                            v_DrugOstatRegistry dor2

                        WHERE
                            COALESCE(ssl.Org_id,lpu.Org_id) = dor2.Org_id

                            and s.Storage_id = dor2.Storage_id
                            and ds.DrugShipment_id = dor2.DrugShipment_id
                    ) as DrugOstatRegistry_ids
                ) dors ON true
                LEFT JOIN LATERAL (

                    select
                        dor3.Drug_id,
                        d.Drug_Code,
                        d.Drug_Name,
                        dor3.PrepSeries_id,
                        dcm.DrugComplexMnn_RusName,
                        ps.PrepSeries_Ser,
                        to_char(ps.PrepSeries_GodnDate, 'DD.MM.YYYY') as PrepSeries_GodnDate,

                        cast(dor3.DrugOstatRegistry_Cost as varchar(20)) as DrugOstatRegistry_Cost,
                        dor3.DrugFinance_id,
                        df.DrugFinance_Name,
                        dor3.WhsDocumentCostItemType_id,
                        wcit.WhsDocumentCostItemType_Name
                    from v_DrugOstatRegistry dor3

                    left join rls.v_Drug d  on d.Drug_id = dor3.Drug_id

                    left join rls.v_DrugComplexMnn dcm  on dcm.DrugComplexMnn_id = d.DrugComplexMnn_id

                    left join rls.v_PrepSeries ps  on ps.PrepSeries_id = dor3.PrepSeries_id

                    left join v_DrugFinance df  on df.DrugFinance_id = dor3.DrugFinance_id

                    left join v_WhsDocumentCostItemType wcit  on wcit.WhsDocumentCostItemType_id = dor3.WhsDocumentCostItemType_id

                    where COALESCE(ssl.Org_id,lpu.Org_id) = dor3.Org_id

                        and s.Storage_id = dor3.Storage_id
                        and ds.DrugShipment_id = dor3.DrugShipment_id
                    limit 1
                ) dor ON true
                LEFT JOIN LATERAL (
                    select
                        (DATE_PART('year', TO_DATE(dor.PrepSeries_GodnDate, 'dd.mm.yyyy')) - DATE_PART('year', (select curDate from curDT))) * 12 +
                        (DATE_PART('month', TO_DATE(dor.PrepSeries_GodnDate, 'dd.mm.yyyy')) - DATE_PART('month', (select curDate from curDT))) as PrepSeries_MonthCount
                ) psmc ON true
                LEFT JOIN LATERAL (

                    select
                        sum(dor4.DrugOstatRegistry_Kolvo) as kolvo
                    from v_DrugOstatRegistry dor4

                    where COALESCE(ssl.Org_id,lpu.Org_id) = dor4.Org_id

                        and s.Storage_id = dor4.Storage_id
                        and ds.DrugShipment_id = dor4.DrugShipment_id
                ) ostat ON true
             WHERE 
                (1=1)
                 and dor.DrugComplexMnn_RusName iLIKE ('%'||'регевак'||'%') and dor.DrugFinance_id = '3' and ostat.kolvo > 0
            
                
                        ORDER BY
                            
                dor.Drug_Name
            
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
