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

仓储系统

--------------------这是从数据库中导出的存储过程----------2003-07-30------------
--------------------可以直接执行下面的存储过程------------2003-07-30----------




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bf_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bf_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bf_CkGz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bf_CkGz]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bf_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bf_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bf_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bf_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CgRk_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CgRk_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CgRk_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CgRk_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CgRk_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CgRk_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cg_RkGz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Cg_RkGz]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ly_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Ly_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ly_CkGz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Ly_CkGz]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ly_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Ly_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ly_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Ly_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MateBasic_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MateBasic_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MateBasic_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MateBasic_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MateBasic_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MateBasic_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pd_Gz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Pd_Gz]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductIn_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductIn_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductIn_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductIn_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductIn_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductIn_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductOut_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductOut_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductOut_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductOut_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductOut_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductOut_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product_Ck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Product_Ck]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product_CkGz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Product_CkGz]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product_RkGz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Product_RkGz]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PyPk_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PyPk_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PyPk_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PyPk_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PyPk_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PyPk_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TL_RkGz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TL_RkGz]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TuiLiao_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TuiLiao_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TuiLiao_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TuiLiao_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TuiLiao_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TuiLiao_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZzRk_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ZzRk_Add]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZzRk_Erase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ZzRk_Erase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZzRk_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ZzRk_Mod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Zz_RkGz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Zz_RkGz]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc Bf_Add(@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
    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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc Bf_Erase(@ckdh char(8)) as  
begin transaction  
    delete from Mate_Bf where Bf_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 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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc CgRk_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_CgRk(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 CgRk_Erase(@rkdh char(8)) as  
begin transaction  
    delete from Mate_CgRk 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 CgRk_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_CgRk 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 Cg_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_CgRk 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_CgRk 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        
      --begin transaction    
        print '进入内部更新'     
        update mate_basic      
          set Stoc_Amount=@T1+@p5,Mate_TotalPrice=@T1*@T2+@p4*@p5,Mate_Price=(@T1*@T2+@p4*@p5)/(@T1+@p5)      
          where mate_code=@p1  
        print '更新mate_basic'    
        print @T1+@p5    
        print @T1*@T2    
        print @p4*@p5    
        print (@T1*@T2+@p4*@p5)/(@T1+@p5)    
     --commit    
    end      
  commit    
  FETCH next FROM TEMP_CUR INTO @p1,@p4,@p5,@kf,@xh      
end      
close Temp_Cur      
Deallocate Temp_Cur      
begin transaction    
  select @T3=(select In_Zj from mate_CgRk where (In_Bill=@rkdh) and (In_Type='CGR') )    
  update Mate_CgRk set In_Post='Y',In_Zj=@T3 where (In_Bill=@rkdh) and (In_Type='CGR')      
  print convert(varchar,@T3)  
commit   

--exec Cg_Rkgz @rkdh='00000001'
--exec Cg_Rkgz @rkdh='00000002'
--select * from Mate_CgRk
--select * from Mate_Basic





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc Ly_Add(@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),
                        @shr char(8),@lyr char(8),@zdr char(8),@bz varchar(150)) as