stock2_sp.sql

仓储系统

                        @bfNo char(6),@reason char(100),@shr char(8),@jyr char(8),@zdr char(8),@bz varchar(150)) as
begin transaction
    insert into Mate_Bf(Bf_Bill,Plan_id,Mate_Code,Dept_id,Out_Date,
                        KfNo,KwNo,Out_Type,Out_Amount,Out_Price,Out_Zj,Out_Post,Bf_No,Bf_Reason,
                        Checker,Exam_man,Oper_id,Out_memo) 
    values(@ckdh,@jhbh,@wlbm,@bm,@rq,@kf,@kw,@lb,@Num,@dj,@zj,@PostMark,@bfNo,@reason,@shr,@jyr,@zdr,@bz)
commit 
go


drop proc Bf_Erase
go
create proc Bf_Erase(@ckdh char(8)) as  
begin transaction  
    delete from Mate_Bf where Bf_Bill=@ckdh  
commit 
go

drop proc Bf_Mod    
go
create proc Bf_Mod(@ckdh char(8),@jhbh varchar(12),@wlbm char(25),@bm char(4),@rq datetime,
                        @kf char(2),@kw char(5),@lb char(3),@Num decimal,@dj money,@zj money,@PostMark char(1),
                        @bfNo char(6),@reason char(100),@shr char(8),@jyr char(8),@zdr char(8),@bz varchar(150)) as
begin transaction
    update Mate_Bf set Plan_id=@jhbh,Mate_Code=@wlbm,Dept_id=@bm,Out_Date=@rq,
                       KfNo=@kf,KwNo=@kw,Out_Type=@lb,
                       Out_Amount=@Num,Out_Price=@dj,Out_Zj=@zj,Out_Post=@PostMark,Bf_No=@bfNo,
                       Bf_Reason=@reason,Checker=@shr,Exam_man=@jyr,Oper_id=@zdr,Out_memo=@bz
    where Bf_Bill=@ckdh
commit 
go

------------下面分别是成品出库、领用出库、报废出库------过帐存储过程-----------------

--成品出库
drop proc Product_CkGz
go
create procedure Product_CkGz(@ckdh char(8)) as        
Declare @p1 char(25),@p4 money,@p5 float,@p6 money,        
        @kf char(2),@kw char(5),@T1 decimal,@T3 money,@xh tinyint        
Declare Temp_cur Cursor for        
     select mate_code,isnull(Out_Price,0.00) as Out_Price,isnull(Out_Amount,0.00) as Out_Amount,KfNo,KwNo    
                 from Mate_Ck where (Out_Bill=@ckdh)    
for Update         
open Temp_Cur        
fetch from Temp_Cur into @p1,@p4,@p5,@kf,@kw        
while @@fetch_status=0     --0:读取成功； -1：读取失败； -2：你所读取的数据已被删除        
begin        
  --更新明细表的金额        
  select @p6=@p4*@p5         --金额@p6=单价@p4*数量@p5                                      
  print '@p6 '      
  print convert(varchar,@p6)      
    
  select @T1=(select Stoc_Amount from mate_basic where (mate_code=@p1))  --库存数量        
  print '@T1'      
  print convert(varchar,@T1)      
    
  begin transaction        
    update Mate_Ck set Out_Zj=@p6 where Out_Bill=@ckdh and mate_Code=@p1 and KfNo=@kf and KwNo=@kw    
    print @p1      
    print @kf    
    print @kw    
    --查找库存物料        
    if exists(select * from mate_basic where mate_Code=@p1)        
    begin          
       print '进入内部更新'       
       update mate_basic set Stoc_Amount=@T1-@p5 where mate_code=@p1    
       print '更新mate_basic'      
       print @T1+@p5      
       print @p4*@p5      
    end        
  commit      
  FETCH next FROM TEMP_CUR INTO @p1,@p4,@p5,@kf,@kw  
end        
close Temp_Cur        
Deallocate Temp_Cur        
begin transaction      
  select @T3=(select Out_Zj from Mate_Ck where (Out_Bill=@ckdh) and (Out_Type='CPC') )      
  update Mate_Ck set Out_Post='Y',Out_Zj=@T3 where (Out_Bill=@Ckdh) and (Out_Type='CPC')        
  print convert(varchar,@T3)    
commit
go


--领用出库
drop proc Ly_CkGz
go
create procedure Ly_CkGz(@ckdh char(8)) as        
Declare @p1 char(25),@p4 money,@p5 float,@p6 money,        
        @kf char(2),@kw char(5),@T1 decimal,@T3 money,@xh tinyint        
Declare Temp_cur Cursor for        
     select mate_code,isnull(Out_Price,0.00) as Out_Price,isnull(Out_Amount,0.00) as Out_Amount,KfNo,KwNo    
                 from Mate_Ly where (Ly_Bill=@ckdh)    
for Update         
open Temp_Cur        
fetch from Temp_Cur into @p1,@p4,@p5,@kf,@kw        
while @@fetch_status=0     --0:读取成功； -1：读取失败； -2：你所读取的数据已被删除        
begin        
  --更新明细表的金额        
  select @p6=@p4*@p5         --金额@p6=单价@p4*数量@p5                                      
  print '@p6 '      
  print convert(varchar,@p6)      
    
  select @T1=(select Stoc_Amount from mate_basic where (mate_code=@p1))  --库存数量        
  print '@T1'      
  print convert(varchar,@T1)      
    
  begin transaction        
    update Mate_Ly set Out_Zj=@p6 where Ly_Bill=@ckdh and mate_Code=@p1 and KfNo=@kf and KwNo=@kw    
    print @p1      
    print @kf    
    print @kw    
    --查找库存物料        
    if exists(select * from mate_basic where mate_Code=@p1)        
    begin          
       print '进入内部更新'       
       update mate_basic set Stoc_Amount=@T1-@p5 where mate_code=@p1    
       print '更新mate_basic'      
       print @T1+@p5      
       print @p4*@p5      
    end        
  commit      
  FETCH next FROM TEMP_CUR INTO @p1,@p4,@p5,@kf,@kw  
end        
close Temp_Cur        
Deallocate Temp_Cur        
begin transaction      
  select @T3=(select Out_Zj from Mate_Ly where (Ly_Bill=@ckdh) and (Out_Type='LYC') )      
  update Mate_Ly set Out_Post='Y',Out_Zj=@T3 where (Ly_Bill=@Ckdh) and (Out_Type='LYC')        
  print convert(varchar,@T3)    
commit
go





--报废出库过帐
drop proc Bf_CkGz
go
create procedure Bf_CkGz(@ckdh char(8)) as        
Declare @p1 char(25),@p4 money,@p5 float,@p6 money,        
        @kf char(2),@kw char(5),@T1 decimal,@T3 money,@xh tinyint        
Declare Temp_cur Cursor for        
     select mate_code,isnull(Out_Price,0.00) as Out_Price,isnull(Out_Amount,0.00) as Out_Amount,KfNo,KwNo    
                 from Mate_Bf where (Bf_Bill=@ckdh)    
for Update         
open Temp_Cur        
fetch from Temp_Cur into @p1,@p4,@p5,@kf,@kw        
while @@fetch_status=0     --0:读取成功； -1：读取失败； -2：你所读取的数据已被删除        
begin        
  --更新明细表的金额        
  select @p6=@p4*@p5         --金额@p6=单价@p4*数量@p5                                      
  print '@p6 '      
  print convert(varchar,@p6)      
    
  select @T1=(select Stoc_Amount from mate_basic where (mate_code=@p1))  --库存数量        
  print '@T1'      
  print convert(varchar,@T1)      
    
  begin transaction        
    update Mate_Bf set Out_Zj=@p6 where Bf_Bill=@ckdh and mate_Code=@p1 and KfNo=@kf and KwNo=@kw    
    print @p1      
    print @kf    
    print @kw    
    --查找库存物料        
    if exists(select * from mate_basic where mate_Code=@p1)        
    begin          
       print '进入内部更新'       
       update mate_basic set Stoc_Amount=@T1-@p5 where mate_code=@p1    
       print '更新mate_basic'      
       print @T1+@p5      
       print @p4*@p5      
    end        
  commit      
  FETCH next FROM TEMP_CUR INTO @p1,@p4,@p5,@kf,@kw  
end        
close Temp_Cur        
Deallocate Temp_Cur        
begin transaction      
  select @T3=(select Out_Zj from Mate_Bf where (Bf_Bill=@ckdh) and (Out_Type='BFC') )      
  update Mate_Bf set Out_Post='Y',Out_Zj=@T3 where (Bf_Bill=@Ckdh) and (Out_Type='BFC')        
  print convert(varchar,@T3)    
commit
go






----------------盘盈盘亏------------------------
create proc PyPk_Add(@pddh char(8),@pdlb varchar(12),@rq datetime,@wlbm char(25),@bm char(4),  
                        @kf char(2),@kw char(5),@Num decimal,@YkNum decimal,@PostMark char(6),
                        @pdr char(8),@zdr char(8),@bz varchar(150)) as  
begin transaction  
    insert into Mate_PyPk(Pd_Bill,Pd_ClassNo,Pd_date,mate_code,Dept_id,  
                          KfNo,KwNo,Pd_Amount,Yk_Amount,Pd_Post,pd_man,Oper_id,Pd_Memo)   
    values(@pddh,@pdlb,@rq,@wlbm,@bm,@kf,@kw,@Num,@YkNum,@PostMark,@pdr,@zdr,@bz)  
commit  
go


drop proc PyPk_Erase
go
create proc PyPk_Erase(@pddh char(8)) as  
begin transaction  
    delete from Mate_PyPk where Pd_Bill=@pddh  
commit 
go

drop proc PyPk_Mod    
go
create proc PyPk_Mod(@pddh char(8),@pdlb varchar(12),@rq datetime,@wlbm char(25),@bm char(4),
                        @kf char(2),@kw char(5),@Num decimal,@YkNum decimal,
                        @pdr char(8),@zdr char(8),@bz varchar(150)) as
begin transaction
    update Mate_PyPk set Pd_Bill=@pddh,Pd_ClassNo=@pdlb,Pd_date=@rq,mate_code=@wlbm,Dept_id=@bm,
                          KfNo=@kf,KwNo=@kw,Pd_Amount=@Num,Yk_Amount=@YkNum,pd_man=@pdr,Oper_id=@zdr,Pd_Memo=@bz
    where Pd_Bill=@pddh
commit 
go

CREATE TABLE Mate_PyPk(
	Pd_Bill       char(8),
	Pd_ClassNo  char(18),
	Pd_date       datetime,
        mate_code     char(25),
	Dept_id       char(2),
        KfNo          char(2),
        KwNo          char(5),
        Pd_Amount     decimal(9, 0),
        Yk_Amount     decimal(9, 0),
        pd_man        char(8),
	Oper_id       char(8),
	Pd_Memo       varchar(150) 
) ON [PRIMARY]
GO



-----------------------盘盈盘亏过帐-----------------------
--drop proc Pd_Gz
go
create procedure Pd_Gz(@pddh char(8)) as        
Declare @p1 char(25),@p4 money,@p5 float,@p6 money,        
        @kf char(2),@kw char(5),@T1 decimal,@T2 money,@T3 money,@xh tinyint        
Declare Temp_cur Cursor for        
     select mate_code,isnull(Pd_Amount,0.00) as Pd_Amount,KfNo,KwNo    
                 from Mate_PyPk where (Pd_Bill=@pddh)    
for Update         
open Temp_Cur        
fetch from Temp_Cur into @p1,@p5,@kf,@kw        
while @@fetch_status=0     --0:读取成功； -1：读取失败； -2：你所读取的数据已被删除 
begin        
  select @p5=(select Pd_Amount from Mate_PyPk where (mate_code=@p1))     --盘点数量        
  print '@p5'      
  print convert(varchar,@p5)     
    
  select @T1=(select Stoc_Amount from mate_basic where (mate_code=@p1))  --库存数量        
  print '@T1'      
  print convert(varchar,@T1) 

  select @T2=(select Mate_Price from mate_basic where (mate_code=@p1))  --库存核算价格                  
  print '@T2'    
  print convert(varchar,@T2)     
    
  begin transaction        
    --查找库存物料        
    if exists(select * from mate_basic where mate_Code=@p1)        
    begin          
       print '进入内部更新'       
       update Mate_Basic set Stoc_Amount=@p5,Mate_TotalPrice=@p5*@T2 where mate_code=@p1    
       print '更新mate_basic'      
       print @p5      
       print @p5*@T2
    end        
  commit      
  FETCH next FROM TEMP_CUR INTO @p1,@p4,@p5,@kf,@kw  
end        
close Temp_Cur        
Deallocate Temp_Cur        
begin transaction      
  update Mate_PyPk set Pd_Post='Y' where (Pd_Bill=@pddh)
commit