stock2_sp.sql

仓储系统

    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   

--select * from mate_basic
--exec Zz_Rkgz @rkdh='00000001'


Go
drop proc ProductIn_Add
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

drop proc ProductIn_Erase
GO
create proc ProductIn_Erase(@rkdh char(8)) as
begin transaction
  delete from Product_Rk where In_Bill=@rkdh
commit
Go

drop proc ProductIn_Mod
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


select * from Product_Rk
-----------------------------成品入库过帐----------------------------------------------------------
drop proc Product_RkGz
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
drop proc TuiLiao_Add
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

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

drop proc TuiLiao_Mod    
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 




--------------------------------退料入库过帐------------------------------------------
drop proc TL_RkGz
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
  

-------------------------成品出库业务存储过程---------------------------

---------------成品出库业务添加、删除、修改-------------------------------
drop proc ProductOut_Add
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

drop proc ProductOut_Erase
go
create proc ProductOut_Erase(@ckdh char(8)) as  
begin transaction  
    delete from Mate_Ck where Out_Bill=@ckdh  
commit 
go

drop proc ProductOut_Mod    
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 

---------------领用出库添加、删除、修改-----------------------------
drop proc Ly_Add
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
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

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

drop proc Ly_Mod    
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


---------------报废出库添加、删除、修改---------------------------
drop proc Bf_Add
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),