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

仓储系统

begin transaction
    insert into Mate_Ly(Ly_Bill,Plan_id,Mate_Code,Dept_id,Out_Date,KfNo,KwNo,Out_Type,
                        Out_Amount,Out_Price,Out_Zj,Out_Post,Checker,Out_man,Oper_id,Out_Memo) 
    values(@ckdh,@jhbh,@wlbm,@bm,@rq,@kf,@kw,@lb,@Num,@dj,@zj,@PostMark,@shr,@lyr,@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 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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc Ly_Erase(@ckdh char(8)) as  
begin transaction  
    delete from Mate_Ly where Ly_Bill=@ckdh  
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc Ly_Mod(@ckdh 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),
                        @shr char(8),@lyr char(8),@zdr char(8),@bz varchar(150)) as
begin transaction
    update Mate_Ly 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,
                       Checker=@shr,Out_man=@lyr,Oper_id=@zdr,Out_memo=@bz
    where Ly_Bill=@ckdh
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create proc MateBasic_Add(@wlbm char(25),@wlmc char(50),@xh char(50),@dw char(10),@lb char(25),  
                          @PZ char(2),@abc char(1),@num decimal,@dj money,  
                          @bm char(4),@zdr char(8),@bz varchar(150)) as  
begin transaction  
   insert into Mate_Basic(Mate_Code,Mate_Name,Mate_Type,Mate_Unit,Mate_Class,  
                          Mate_Quality,ABC_Class,Stoc_Amount,Mate_Price,
                          DeptNo,Oper_id,Mate_Memo)   
   values(@wlbm,@wlmc,@xh,@dw,@lb,@PZ,@abc,@num,@dj,@bm,@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 MateBasic_Erase(@wlbm char(25)) as  
begin transaction  
   delete Mate_Basic where mate_code=@wlbm  --是否删除某一物料编码，就要删除其他任意表内的对应的数据
commit

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create proc MateBasic_Mod(@wlbm char(25),@wlmc char(50),@xh char(50),@dw char(10),  
                          @PZ char(2),@abc char(1),@num decimal,@dj money,  
                          @bm char(4),@zdr char(8),@bz varchar(150)) as  
begin transaction  
   update Mate_Basic set Mate_Name=@wlmc,Mate_Type=@xh,Mate_Unit=@dw,  
                          Mate_Quality=@PZ,ABC_Class=@abc,Stoc_Amount=@num,Mate_Price=@dj,  
                          DeptNo=@bm,Oper_id=@zdr,Mate_Memo=@bz  
   where Mate_Code=@wlbm  
commit  


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


--select * from Mate_PyPk

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc ProductIn_Add(@rkdh 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),@jyr char(8),@zdr char(8),@zy varchar(50)) as
begin transaction
  insert into Product_Rk(In_Bill,Plan_id,Mate_Code,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,@jhbh,@wlbm,@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 ProductIn_Erase(@rkdh char(8)) as
begin transaction
  delete from Product_Rk 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 ProductIn_Mod(@rkdh 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),@jyr char(8),@zdr char(8),@zy varchar(50)) as
begin transaction
  update Product_Rk set Plan_id=@jhbh,Mate_Code=@wlbm,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 proc ProductOut_Add(@ckdh char(8),@jhbh varchar(12),@wlbm char(25),@kh char(4),@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),@bz varchar(150)) as
begin transaction
    insert into Mate_Ck(Out_Bill,Plan_id,Mate_Code,Cust_id,Out_Date,KfNo,KwNo,Out_Type,
                        Out_Amount,Out_Price,Out_Zj,Out_Post,
                        Out_man,Exam_man,Oper_id,Ck_memo) 
    values(@ckdh,@jhbh,@wlbm,@kh,@rq,@kf,@kw,@lb,@Num,@dj,@zj,@PostMark,@jsr,@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 ProductOut_Erase(@ckdh char(8)) as  
begin transaction  
    delete from Mate_Ck where Out_Bill=@ckdh  
commit 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc ProductOut_Mod(@ckdh char(8),@jhbh varchar(12),@wlbm char(25),@kh 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),@bz varchar(150)) as
begin transaction
    update Mate_Ck set Plan_id=@jhbh,Mate_Code=@wlbm,Cust_id=@kh,Out_Date=@rq,
                       KfNo=@kf,KwNo=@kw,Out_Type=@lb,
                       Out_Amount=@Num,Out_Price=@dj,Out_Zj=@zj,Out_Post=@PostMark,
                       Out_man=@jsr,Exam_man=@jyr,Oper_id=@zdr,Ck_memo=@bz
    where Out_Bill=@ckdh
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_Ck(@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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Product_CkGz(@ckdh char(8)) as        
Declare @p1 char(25),@p4 money,@p5 float,@p6 money,