2010年7月31日——前一家公司写的——库存配套处理函数

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER                    function  dbo._n_matchWIP_alim
(
 @bac2 nvarchar(10)

)returns  @match table (BP nvarchar(10),客戶 nvarchar(30),成品 nvarchar(12), 半成品 nvarchar(12),配套數量 int, 餘量 int,理論配套量 int,理論庫存餘量 int,理論最大配套量 int)
as
begin

declare @master table( bac nvarchar(10),inputbominst_item nvarchar(15),em_resoritem nvarchar(15))

declare @master0 table (bac nvarchar(10),InputBomInst_Item nvarchar(20),in_em_resoritem nvarchar(20),Bom_ProcNo int,out_em_resoritem nvarchar(20),InputBomInst_Yield decimal(18,4))
--alimzhao 2008.11.02

declare @wipmasterinv table (bac nvarchar(10),InputBomInst_Item nvarchar(15),em_resoritem nvarchar(15),em_resoritem1 nvarchar(15),Bom_ProcNo int,Bom_Proc nvarchar(4), InputBomInst_Yield numeric(18,4),qty1 numeric(18,4))
--alimzhao 2008.10.31

declare @tinv table (bac nvarchar(10),item nvarchar(12),qty int)

declare @wiptinv table (bac nvarchar(10),InputBomInst_Item nvarchar(15), em_resoritem nvarchar(15), em_resoritem1 nvarchar(15), Bom_ProcNo int,Bom_Proc nvarchar(4), InputBomInst_Yield numeric(18,4),qty1 numeric(18,4),aqty numeric(18,4))
--alimzhao 2008.10.31

declare @count table (bac nvarchar(10),em_resoritem nvarchar(12), [count] int)

declare @plantinv table (bac varchar(10),item varchar(12),qty int)

declare @bac nvarchar(10),
        @inputbominst_item nvarchar(15),
        @item nvarchar(15),
        @em_resoritem nvarchar(15),
        @qty int,
        @i int, 
                @qty2               int,
                @半成品 nvarchar(12),
                @成品 nvarchar(12),
                @BP nvarchar(10),
                @配套數量 int,
                @餘量 int,   
  @em_resoritem1 nvarchar(15),
  @Bom_ProcNo  int,
  @Bom_Proc  nvarchar(4),
  @InputBomInst_Yield numeric(18,4),
  @qty1   numeric(18,4),
                @aqty   numeric(18,4),
  @NextYield  numeric(18,4),
  @NextQty  numeric(18,4)   --alim 2008.10.31

insert into @master
select distinct b.bac,a.inputbominst_item,a.em_resoritem
from mmaster a,mbac b
where left(a.em_procsel,1)=b.serialcode
and em_instructiontype='i'
and em_resoritem in(select item_code from aitem where silitech_type='s' and len(item_code)=12)
and inputbominst_item in(select order_item from aorders where order_type='m'and order_qty>0)
and b.bac=@bac2
order by b.bac,a.inputbominst_item,a.em_resoritem

insert into @master0
select distinct c.bac, a.InputBomInst_Item,a.em_resoritem,a.Bom_ProcNo,b.em_resoritem,b.InputBomInst_Yield
from (select EM_ProcSel, InputBomInst_Item ,EM_InstructionType,em_resoritem,bom_proc,Bom_ProcNo,InputBomInst_Yield from mmaster
       where EM_InstructionType='i' and left(bom_proc,1)<>'@')as a ,
     (select EM_ProcSel, InputBomInst_Item ,EM_InstructionType,em_resoritem,bom_proc,Bom_ProcNo,InputBomInst_Yield from mmaster
       where EM_InstructionType='o' and left(bom_proc,1)<>'@')as b,mbac c
             where  left(a.EM_ProcSel,1)=left(b.EM_ProcSel,1)
      and a.InputBomInst_Item=b.InputBomInst_Item
      and a.Bom_ProcNo=b.Bom_ProcNo
      and c.bac=@bac2
      and c.serialcode=left (a.EM_ProcSel,1)
      and c.serialcode=left (b.EM_ProcSel,1)
order by c.bac, a.InputBomInst_Item,a.Bom_ProcNo
--2008.11.02

insert into @wipmasterinv
select distinct a.bac,a.InputBomInst_Item,b.em_resoritem,a.em_resoritem as em_resoritem1,convert(int,c.Bom_ProcNo)as Bom_ProcNo,a.Bom_Proc,convert(numeric(18,4),c.InputBomInst_Yield) as InputBomInst_Yield,convert(numeric(18,4),a.qty) as qty 
  from 
  (select distinct b.bac, a.InputBomInst_Item,a.EM_ResOrItem,a.Bom_ProcNo,a.Bom_Proc,a.EM_InstructionType ,isnull(sum(c.qty+c.aqty),0) as qty
  from mmaster a,mbac b,tinventorybalance c
  where left(a.em_procsel,1)=b.serialcode 
  and a.em_instructiontype='O'
  and a.em_resoritem in(select item_code from aitem where silitech_type='I' and len(item_code)=15) 
  and a.inputbominst_item in(select order_item from aorders where order_type='M'and order_qty>0) 
  and b.bac=@bac2
  and b.serialcode*=left(c.ordercode,1)
  and a.em_resoritem*=c.item 
  and c.grade='G'
  group by b.bac,a.InputBomInst_Item,a.Bom_ProcNo,a.Bom_Proc,a.EM_InstructionType ,a.EM_ResOrItem
  ) a,
  (select distinct b.bac,a.inputbominst_item,a.em_resoritem 
  from mmaster a,mbac b
  where left(a.em_procsel,1)=b.serialcode
  and em_instructiontype='O'
  and em_resoritem in(select item_code from aitem where silitech_type='S' and len(item_code)=12)
  and inputbominst_item in(select order_item from aorders where order_type='M'and order_qty>0)
  and b.bac=@bac2
  ) b,@master0 c
where a.bac=b.bac
and a.bac=c.bac
and a.em_resoritem=c.in_em_resoritem
and a.inputbominst_item=b.inputbominst_item
and a.inputbominst_item=c.inputbominst_item
and b.em_resoritem=left(a.em_resoritem,12)
and qty >0
order by a.bac,a.InputBomInst_Item,b.em_resoritem, c.Bom_ProcNo asc
---2008.10.31 alimzhao  2008.11.02修改

insert into @tinv
select b.bac,b.em_resoritem,isnull(sum(a.qty+a.aqty),0)
from tinventorybalance a,(select distinct a.bac,a.em_resoritem,c.serialcode from @master a,mbac c where a.bac=c.bac) b
where b.serialcode*=left(a.ordercode,1)
and b.em_resoritem*=a.item
and grade='g'
group by b.bac,b.em_resoritem

declare c1 cursor local keyset for
select distinct  bac,inputbominst_item  from @master

open c1
fetch next from c1 into @bac ,@inputbominst_item
while ( @@fetch_status = 0 )
begin
  set @qty=(select min(qty)from @tinv where bac+item in(select bac+em_resoritem from @master where bac=@bac and inputbominst_item=@inputbominst_item))

              insert  into @match 
              select @bac,b.cust_name,@inputbominst_item,em_resoritem,@qty,0,0,0,0 from @master a, (select top 1 a.bac,custcode,itemcode,cust_name from t4mfcst a ,acustomer b where a.bac=@bac and itemcode=@inputbominst_item and custcode=cust_code order by a.bac,itemcode )b
              where a.bac=@bac and inputbominst_item=@inputbominst_item
        
              update @tinv
              set qty=qty-@qty
              from @tinv a,@master b
              where a.item=b.em_resoritem and a.bac=b.bac and b.bac=@bac and b.inputbominst_item=@inputbominst_item

fetch next from c1  into @bac,@inputbominst_item
end
close c1
deallocate c1

declare c2 cursor local keyset for
select distinct  bac,item,qty  from @tinv where qty>0

open c2
fetch next from c2 into @bac ,@item,@qty
while ( @@fetch_status = 0 )
begin
  update @match
  set 餘量=@qty
  from @match a,(select top 1 bac,inputbominst_item,em_resoritem from @master where bac=@bac and em_resoritem=@item order by bac,inputbominst_item,em_resoritem)c
  where a.成品=c.inputbominst_item
   and a.bp=@bac
   and a.半成品=@item

fetch next from c2 into @bac ,@item,@qty
end
close c2
deallocate c2

--以下新增 alim 2008.10.31
 declare c3 cursor local keyset for
  select distinct bac,InputBomInst_Item,em_resoritem from @wipmasterinv  
 open c3
 fetch next from c3 into @bac,@InputBomInst_Item,@em_resoritem
 while ( @@fetch_status = 0 )
 begin  
  set @NextQty=0
  set @NextYield=1
  declare c4 cursor local keyset for
   select bac,InputBomInst_Item,em_resoritem,em_resoritem1,Bom_ProcNo,Bom_Proc,InputBomInst_Yield,qty1
   from @wipmasterinv
   where bac=@bac
   and InputBomInst_Item=@InputBomInst_Item
   and em_resoritem=@em_resoritem
   order by bac,InputBomInst_Item,em_resoritem,Bom_ProcNo asc
  open c4
  fetch next from c4 into @bac,@InputBomInst_Item,@em_resoritem,@em_resoritem1,@Bom_ProcNo,@Bom_Proc,@InputBomInst_Yield,@qty1
  while ( @@fetch_status = 0 )
  begin 
   insert into @wiptinv(bac,InputBomInst_Item,em_resoritem,em_resoritem1,Bom_ProcNo,Bom_Proc,InputBomInst_Yield,qty1,aqty)
   select @bac,@InputBomInst_Item,@em_resoritem,@em_resoritem1,@Bom_ProcNo,@Bom_Proc,@InputBomInst_Yield,@qty1,@qty1+@NextQty*@InputBomInst_Yield
   set @NextQty=isnull(@NextQty,0)*isnull(@InputBomInst_Yield,0)+isnull(@qty1,0)
   set @NextYield=@InputBomInst_Yield
  fetch next from c4 into @bac,@InputBomInst_Item,@em_resoritem,@em_resoritem1,@Bom_ProcNo,@Bom_Proc,@InputBomInst_Yield,@qty1
  end
  close c4
  deallocate c4
 fetch next from c3 into @bac,@InputBomInst_Item,@em_resoritem
 end
 close c3
 deallocate c3

insert into  @plantinv
select bac,em_resoritem,aqty
from @wiptinv
where bom_procno in(select Bom_ProcNo from @master0 where len(out_em_resoritem)=12)
union select bac,em_resoritem,0
from @master
where em_resoritem not in(select em_resoritem from @plantinv )

 

declare c5 cursor local keyset for
select distinct bp,成品,半成品,餘量  from @match

open c5
fetch next from c5 into @BP,@成品,@半成品,@餘量
while ( @@fetch_status = 0 )
begin          
              update @plantinv
              set qty=qty+@餘量
              where bac=@BP and item=@半成品
fetch next from c5  into @BP,@成品,@半成品,@餘量
end
close c5
deallocate c5

 

declare c1 cursor local keyset for
select distinct  bac,inputbominst_item  from @master

open c1
fetch next from c1 into @bac ,@inputbominst_item
while ( @@fetch_status = 0 )
begin
  set @qty=(select min(qty)from @plantinv where item+bac in(select em_resoritem+bac from @master where bac=@bac and inputbominst_item=@inputbominst_item)and qty>0)
   set @qty2=(select max(qty)from @plantinv where item+bac in(select em_resoritem+bac from @master where bac=@bac and inputbominst_item=@inputbominst_item))
              update @match 
              set 理論配套量=@qty,理論最大配套量=@qty2
              where bp=@bac and 成品=@inputbominst_item
        
              update @plantinv
              set qty=qty-@qty
              from @plantinv a,@master b
              where a.item=b.em_resoritem and a.bac=b.bac and b.bac=@bac and b.inputbominst_item=@inputbominst_item

fetch next from c1  into @bac,@inputbominst_item
end
close c1
deallocate c1

declare c2 cursor local keyset for
select distinct  bac,item,qty  from @plantinv where qty>0

open c2
fetch next from c2 into @bac ,@item,@qty
while ( @@fetch_status = 0 )
begin
  update @match
  set 理論庫存餘量=@qty
  from @match a,(select top 1 bac,inputbominst_item,em_resoritem from @master where bac=@bac and em_resoritem=@item order by bac,inputbominst_item,em_resoritem)c
  where a.成品=c.inputbominst_item
   and a.bp=@bac
   and a.半成品=@item

fetch next from c2 into @bac ,@item,@qty
end
close c2
deallocate c2

return
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO