stock2_sp.sql

仓储系统

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].[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

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 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

-----------------------------采购入库过帐--------------------------
drop proc Cg_Rkgz
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
drop proc ZzRk_Add
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


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

drop proc ZzRk_Mod             --自制入库不能修改单价，因为直接取自库存的核算价格
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 




--------------------------自制入库过帐--------------------------------
drop proc Zz_Rkgz
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)