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