计算特殊型号可用库存

declare @billno varchar(20),
@autoid int,
@qty decimal(10,2),
@qty1 decimal(10,2),
@typesprc varchar(50)
set @billno=[cd_master@billno]
set @qty1=0
create table #aa(typesprc varchar(50),qty decimal(10,2))
declare  yhd_endqty cursor
for select a.autoid,a.qty,a.typesprc from sa_baojlist a  with(nolock) inner join ba_cpinfo b with(nolock) on a.typesprc=b.sprc 
where a.billno=@billno 
open yhd_endqty
fetch next from yhd_endqty into @autoid,@qty,@typesprc 
while @@FETCH_STATUS=0
begin 
if  exists(select 1  from sa_baojlist a with(nolock) inner join ba_cpinfo cp with(nolock) on a.typesprc=cp.sprc 
where isnull(cp.mcode,'') in(select distinct pnode from pd_designbom with(nolock)) and (left(cp.typeno,3)='D07' or cp.typeno in('A03.01.04','D04.01.01','E02.01.01','E01.02.01','E02.02.01','E05.03.01','E05.05.01','E06.01.01',
'E06.01.03','E06.02.01','E06.02.02','E06.02.03','E08.03.01','E51.01.01','F03.01.05','F03.03.01','F08.01.01','F08.02.01','J03.02.01','J03.02.81','J01.01.01','J01.02.01','J01.03.01')) and a.billno=@billno and a.autoid=@autoid)
begin
if not exists ( select pd.node,b.endqty,ISNULL(cc.qty,0),ISNULL(cc1.qty,0),isnull(a.qty,0) from sa_baojlist a with(nolock)

                inner join ba_cpinfo cp with(nolock) on a.typesprc=cp.sprc

                left join pd_designBom pd with(nolock) on pd.pnode=cp.mcode

                left outer join (select t.mcode,sum(t.endqty) as endqty from st_balancenew t with(nolock) where t.goodflg=0 and t.storageno in('01.01','01.04')  group by t.mcode) b on pd.node=b.mcode

                left outer join (select t.mcode,sum(t.endqty) as endqty from st_balancenew t with(nolock) where t.goodflg=0 and t.storageno in('01.01','01.04')  group by t.mcode) c on cp.mcode=c.mcode

                left join (select c.mcode,sum(c.qty-ISNULL(c.kcoutqty,0))as qty from sa_orderlist c with(nolock) left join sa_order c1 with(nolock) on c.orderno=c1.orderno
                where c1.istates=2 and c.stopflg<2 group by c.mcode)cc on pd.node=cc.mcode

                left join (select c.mcode,sum(c.qty-ISNULL(c.kcoutqty,0))as qty from po_leadlist c with(nolock) left join po_lead c1 with(nolock) on c.leadno=c1.leadno
                where c1.istates=2 and c.stopflg<2 and c1.wclistno in('1007','1008') group by c.mcode)cc1 on pd.node=cc1.mcode

                left join #aa aa1 on aa1.typesprc=a.typesprc

                where  a.billno=@billno and a.autoid=@autoid  and isnull(b.endqty,0)+isnull(c.endqty,0)-ISNULL(cc.qty,0)-ISNULL(cc1.qty,0)-isnull(aa1.qty,0)<isnull(a.qty,0)*isnull(pd.Mnum,1))
        begin
                update a set a.tprice=case when a.tprice=1 then 1 when a.qty<=200 then b.ktprice when a.qty>200 then a.tprice else 1 end  from sa_baojlist a 
                inner join ha_lfbomdatalist b on b.typeno=a.typeno1 
                inner join ha_lfbomdata c on c.billno=b.billno where a.billno=@billno and a.autoid=@autoid and c.istates=2 and b.iswork=1

                if @typesprc not in(select typesprc from #aa)
                begin
                insert #aa(typesprc,qty) select typesprc,qty from sa_baojlist a with(nolock) where a.billno=@billno and a.autoid=@autoid
                end
                else 
                update a set a.qty=a.qty+@qty from #aa  a where a.typesprc=@typesprc
                end
        end
                fetch next from yhd_endqty into @autoid,@qty,@typesprc
end
                close yhd_endqty
                Deallocate yhd_endqty
                drop table #aa