从数据库中导出的存储过程.sql

仓储系统

        @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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Product_RkGz(@rkdh 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(In_Price,0.00) as In_Price,isnull(In_Amount,0.00) as In_Amount,KfNo,KwNo    
                 from Product_Rk where (In_Bill=@rkdh)    
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)      

  select @T2=(select Mate_Price from mate_basic where (mate_code=@p1))  --库存核算价格                  
  print '@T2'    
  print convert(varchar,@T2)    
    
  begin transaction        
    update Product_Rk set In_Zj=@p6 where In_Bill=@rkdh 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,Mate_TotalPrice=@T1*@T2+@p4*@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 In_Zj from Product_Rk where (In_Bill=@rkdh) and (In_Type='CPR') )      
  update Product_Rk set In_Post='Y',In_Zj=@T3 where (In_Bill=@rkdh) and (In_Type='CPR')        
  print convert(varchar,@T3)    
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure TL_RkGz(@rkdh 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(TL_Price,0.00) as TL_Price,isnull(TL_Amount,0.00) as TL_Amount,KfNo,KwNo    
                 from TuiLiao_Rk where (TL_Bill=@rkdh)    
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) 

  select @T2=(select Mate_Price from mate_basic where (mate_code=@p1))  --库存核算价格                  
  print '@T2'    
  print convert(varchar,@T2)     
    
  begin transaction        
    update TuiLiao_Rk set TL_Zj=@p6 where TL_Bill=@rkdh 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,Mate_TotalPrice=@T1*@T2+@p4*@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 TL_Zj from TuiLiao_Rk where (TL_Bill=@rkdh) and (TL_Type='TLR') )      
  update TuiLiao_Rk set TL_Post='Y',TL_Zj=@T3 where (TL_Bill=@rkdh) and (TL_Type='TLR')        
  print convert(varchar,@T3)    
commit

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc TuiLiao_Add(@tldh char(8),@jhbh varchar(12),@wlbm char(25),@bm char(2),@rq datetime,
                        @kf char(2),@kw char(5),@lb char(3),@Num decimal,@dj money,@zj money,@PostMark char(1),
                        @jsr char(8),@spr char(8),@jyr char(8),@zdr char(8),@bz varchar(150) ) as
begin transaction
    insert into TuiLiao_Rk(TL_Bill,Plan_id,Mate_Code,Dept_id,TL_Date,KfNo,KwNo,TL_Type,
                           TL_Amount,TL_Price,TL_Zj,TL_Post,
                           TL_man,Audit_man,Exam_man,Oper_id,TL_memo) 
    values(@tldh,@jhbh,@wlbm,@bm,@rq,@kf,@kw,@lb,@Num,@dj,@zj,@PostMark,@jsr,@spr,@jyr,@zdr,@bz)
commit 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc TuiLiao_Erase(@tldh char(8)) as  
begin transaction  
    delete from TuiLiao_Rk where TL_Bill=@tldh  
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc TuiLiao_Mod(@tldh char(8),@jhbh varchar(12),@wlbm char(25),@bm char(2),@rq datetime,
                        @kf char(2),@kw char(5),@lb char(3),@Num decimal,@dj money,@zj money,@PostMark char(1),
                        @jsr char(8),@spr char(8),@jyr char(8),@zdr char(8),@bz varchar(150)) as
begin transaction
    update TuiLiao_Rk set Plan_id=@jhbh,Mate_Code=@wlbm,Dept_id=@bm,TL_Date=@rq,KfNo=@kf,KwNo=@kw,
                          TL_Type=@lb,TL_Amount=@Num,TL_Price=@dj,TL_Zj=@zj,TL_Post=@PostMark,
                          TL_man=@jsr,Audit_man=@spr,Exam_man=@jyr,Oper_id=@zdr,TL_memo=@bz 
    where TL_Bill=@tldh
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc ZzRk_Add(@rkdh char(8),@ht varchar(12),@jhbh varchar(12),@wlbm char(25),@gys char(4),@bm char(2),
                  @rq datetime,@kf char(2),@kw char(5),@lb char(3),@Num decimal,@dj money,@zj money,@PostMark char(1),
                  @jsr char(8),@jyr char(8),@zdr char(8),@zy varchar(50)) as
begin transaction
    insert into Mate_ZzRk(In_Bill,Pr_No,Plan_id,Mate_Code,Gys,Dept_id,In_Date,
                          KfNo,KwNo,In_Type,In_Amount,In_Price,In_Zj,
                          In_Post,In_man,Exam_man,Oper_id,Rk_memo) 
    values(@rkdh,@ht,@jhbh,@wlbm,@gys,@bm,@rq,@kf,@kw,@lb,@Num,@dj,@zj,@PostMark,@jsr,@jyr,@zdr,@zy)
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc ZzRk_Erase(@rkdh char(8)) as  
begin transaction  
    delete from Mate_ZzRk where In_Bill=@rkdh  
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc ZzRk_Mod(@rkdh char(8),@ht varchar(12),@jhbh varchar(12),@wlbm char(25),@gys char(4),
                     @bm char(2),@rq datetime,@kf char(2),@kw char(5),@lb char(3),
                     @Num decimal,@dj money,@zj money,@PostMark char(1),
                     @jsr char(8),@jyr char(8),@zdr char(8),@zy varchar(50)) as
begin transaction
    update Mate_ZzRk set Pr_No=@ht,Plan_id=@jhbh,Mate_Code=@wlbm,Gys=@gys,Dept_id=@bm,In_Date=@rq,
                         KfNo=@kf,KwNo=@kw,In_Type=@lb,
                         In_Amount=@Num,In_Price=@dj,In_Zj=@zj,
                         In_Post=@PostMark,In_man=@jsr,Exam_man=@jyr,Oper_id=@zdr,Rk_memo=@zy       
    where In_Bill=@rkdh
commit 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Zz_RkGz(@rkdh 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(In_Price,0.00) as In_Price,isnull(In_Amount,0.00) as In_Amount,KfNo,KwNo  
                 from Mate_ZzRk where (In_Bill=@rkdh)  
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)    

  select @T2=(select Mate_Price from mate_basic where (mate_code=@p1))  --库存核算价格                  
  print '@T2'    
  print convert(varchar,@T2)    
  
  begin transaction      
    update mate_ZzRk set In_Zj=@p6 where In_Bill=@rkdh 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,Mate_TotalPrice=@T1*@T2+@p4*@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 In_Zj from mate_ZzRk where (In_Bill=@rkdh) and (In_Type='ZZR') )    
  update Mate_ZzRk set In_Post='Y',In_Zj=@T3 where (In_Bill=@rkdh) and (In_Type='ZZR')      
  print convert(varchar,@T3)  
commit

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO