with  curDT as (select dbo.tzGetDate() as curDate)
            select
                dus.Drug_id as "Drug_id",
                dus.DocumentUcStr_id as "DocumentUcStr_id",
                ds.DrugShipment_Name as "DrugShipment_Name",
                ds.DrugShipment_id as "DrugShipment_id",
                at.AccountType_Name as "AccountType_Name",
                (
                    '№ '||ds.DrugShipment_Name||
                    COALESCE(', '||ps.PrepSeries_Ser, '')||

                    COALESCE(', '||to_char(ps.PrepSeries_GodnDate, 'DD.MM.YYYY'), '')||


                    (
                        case
                            when COALESCE(isdef.YesNo_Code, 0) = 1 then ', забракованная серия'

                            else ''
                        end
                    )||


                    COALESCE(', '||cast(dn.DrugNds_Code as varchar(1)), '')||

                    COALESCE(', '||cast(cast(ost.cnt as double precision) as varchar(20))||' '||gu_b.GoodsUnit_Nick, '')||

                    (case
                        when 1 = 1 then COALESCE(' / '||cast(cast(gu_ost.cnt as double precision) as varchar(20))||' '||gu.GoodsUnit_Nick, '')

                        else ''
                    end)||
                    COALESCE(', '||df.DrugFinance_Name, '')||

                    COALESCE(', '||wdcit.WhsDocumentCostItemType_Name, '')||

                    COALESCE(', '||at.AccountType_Name, '')

                ) as "DocumentUcStr_Name",
                dus.DocumentUcStr_Price as "DocumentUcStr_Price",
                dus.DocumentUcStr_PriceR as "DocumentUcStr_PriceR",
                dus.DrugNds_id as "DrugNds_id",
                COALESCE(isnds.YesNo_Code, 0) as "DocumentUcStr_IsNDS",

                ps.PrepSeries_Ser as "DocumentUcStr_Ser",
                to_char(ps.PrepSeries_GodnDate, 'DD.MM.YYYY') as "PrepSeries_GodnDate",

                COALESCE(isdef.YesNo_Code, 0) as "PrepSeries_isDefect",

                dus.DocumentUcStr_CertNum as "DocumentUcStr_CertNum",
                dus.DocumentUcStr_CertOrg as "DocumentUcStr_CertOrg",
                to_char(dus.DocumentUcStr_CertDate, 'DD.MM.YYYY') as "DocumentUcStr_CertDate",

                to_char(dus.DocumentUcStr_CertGodnDate, 'DD.MM.YYYY') as "DocumentUcStr_CertGodnDate",

                dus.DrugLabResult_Name as "DrugLabResult_Name",
                dn.DrugNds_Code as "DrugNds_Code",
                cast(ost.cnt as double precision) as "DocumentUcStr_OstCount",
                COALESCE(df.DrugFinance_Name, '') as "DrugFinance_Name",

                COALESCE(wdcit.WhsDocumentCostItemType_Name, '') as "WhsDocumentCostItemType_Name",

                gu_b.GoodsUnit_id as "GoodsUnit_bid",
                COALESCE(gu_b.GoodsUnit_Nick, '') as "GoodsUnit_bNick",

                gu.GoodsUnit_id as "GoodsUnit_id",
                COALESCE(gu.GoodsUnit_Nick, '') as "GoodsUnit_Nick",

                cast(gu_ost.cnt as double precision) as "GoodsUnit_OstCount"
            from
                v_DocumentUcStr dus

                inner join v_DrugShipmentLink dsl  on dsl.DocumentUcStr_id = dus.DocumentUcStr_id

                inner join v_DocumentUc du  on du.DocumentUc_id = dus.DocumentUc_id
                inner join v_DrugDocumentType ddt  on ddt.DrugDocumentType_id = du.DrugDocumentType_id

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

                left join v_DocumentUcStr o_dus  on o_dus.DocumentUcStr_id = dus.DocumentUcStr_oid

                LEFT JOIN LATERAL (
                    Select * from v_DocumentUc o_du  where o_du.DocumentUc_id = COALESCE(o_dus.DocumentUc_id, dus.DocumentUc_id) -- если ссылки на приходную партию нет, значит текущая партия и есть приходная
                    limit 1
                ) o_du ON true
                left join v_AccountType at  on at.AccountType_id = ds.AccountType_id

                left join v_Contragent con_s  on con_s.Contragent_id = du.Contragent_sid

                left join v_Lpu lpu_s  on lpu_s.Lpu_id = con_s.Lpu_id

                left join v_Contragent con_t  on con_t.Contragent_id = du.Contragent_tid

                left join v_Lpu lpu_t  on lpu_t.Lpu_id = con_t.Lpu_id

                left join v_DrugNds dn on dn.DrugNds_id = dus.DrugNds_id

                left join lateral (select * from  rls.v_PrepSeries ps  where  ps.PrepSeries_id = dus.PrepSeries_id limit 1) ps on true
                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.v_Drug d  on d.Drug_id = dus.Drug_id

                left join v_YesNo isnds  on isnds.YesNo_id = dus.DocumentUcStr_IsNDS

                left join v_GoodsUnit gu_b  on gu_b.GoodsUnit_id = COALESCE(dus.GoodsUnit_bid, '57')

                left join v_GoodsUnit gu  on gu.GoodsUnit_id = dus.GoodsUnit_id

                LEFT JOIN LATERAL (

                    select (
                        case
                            when COALESCE(isnds.YesNo_Code, 0) = 1 then dus.DocumentUcStr_PriceR

                            else dus.DocumentUcStr_PriceR*(1+(COALESCE(dn.DrugNds_Code, 0)/100.0))

                        end
                    ) as price
                ) price ON true
                LEFT JOIN LATERAL (

                    select
                        COALESCE(sum(dor.DrugOstatRegistry_Kolvo), 0) as cnt,
                        max(dor.DrugFinance_id) as DrugFinance_id,
                        max(dor.WhsDocumentCostItemType_id) as WhsDocumentCostItemType_id
                    from
                        v_DrugOstatRegistry dor

                        inner join v_SubAccountType sat  on sat.SubAccountType_id = dor.SubAccountType_id

                        left join lateral(select PrepSeries_Ser,PrepSeries_GodnDate,PrepSeries_id from   rls.v_PrepSeries ps2  where ps2.PrepSeries_id = dor.PrepSeries_id limit 1) ps2 on true

                    where
                        (
                            sat.SubAccountType_Code = 1  and
                            (
                                dor.Org_id is NULL or
                                (ddt.DrugDocumentType_Code in (3, 6, 32, 35) and dor.Org_id = COALESCE(con_t.Org_id, lpu_t.Org_id)) or

                                (ddt.DrugDocumentType_Code = 15 and dor.Org_id = COALESCE(con_s.Org_id, lpu_s.Org_id))

                            ) and
                            dor.Drug_id = dus.Drug_id and
                            (NULL is null or dor.Storage_id = NULL) and
                            (dor.Contragent_id is null or NULL is null or dor.Contragent_id = NULL) and
                            (ps2.PrepSeries_Ser = dus.DocumentUcStr_Ser or ps2.PrepSeries_id = dus.PrepSeries_id) and
                            dor.DrugOstatRegistry_Cost = cast(price.price as Numeric(18,2)) and
                            dor.DrugShipment_id = dsl.DrugShipment_id
                            
                        )
                         or (
                sat.SubAccountType_Code = 2
                and dor.DrugOstatRegistry_id in (
                    select
                        DrugOstatRegistry_id
                    from
                    	v_DrugOstatRegistryLink

                    where
                        DrugOstatRegistryLink_TableName = 'DocumentUcStr' and
                        DrugOstatRegistryLink_TableID = dus.DocumentUcStr_id
                    limit 1
                )
            )
                ) ost ON true
                LEFT JOIN LATERAL ( -- для ускорения рассчета, рассчет остатка в ед. списания сделан через пропорцию

                    select (
                        case
                            when gu_b.GoodsUnit_id = gu.GoodsUnit_id then ost.cnt
                            when gu_b.GoodsUnit_id <> gu.GoodsUnit_id and dus.DocumentUcStr_Count > 0 then (dus.DocumentUcStr_EdCount/dus.DocumentUcStr_Count)*ost.cnt
                            when dus.DocumentUcStr_Count = 0 then 0
                            else null
                        end
                    ) as cnt
                ) gu_ost ON true
                LEFT JOIN LATERAL (
                    select
                        COALESCE(o_du.DrugFinance_id, ost.DrugFinance_id) as DrugFinance_id,
                        COALESCE(o_du.WhsDocumentCostItemType_id, ost.WhsDocumentCostItemType_id) as WhsDocumentCostItemType_id
                ) fin_val ON true
                left join v_DrugFinance df on df.DrugFinance_id = fin_val.DrugFinance_id
                left join v_WhsDocumentCostItemType wdcit on wdcit.WhsDocumentCostItemType_id = fin_val.WhsDocumentCostItemType_id
                
                        LEFT JOIN LATERAL (

                            select
                                datediff('month', (select curDate from curDT), PS.PrepSeries_GodnDate) as PrepSeries_MonthCount
                        ) psmc ON true
                    
            where
                (ddt.DrugDocumentType_Code in (3, 6, 15, 32, 35) and ost.cnt > 0 and du.DocumentUc_id = '500801000118609') or dus.DocumentUcStr_id = '500801000924565'
            ;
        
