acct_settle.sql

提供商业企业进销存、成本核算、销售人员工资考核结算、地区管理报表（特别是二维报表）

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

go
create procedure Acct_settle @oldacct  int=10  ,@newacct  int=11
as
begin tran
        declare
        @vgoods_code varchar(9),
        @vstock_code varchar(2),
        @vedqty        numeric(15,2),
        @vedcost       numeric(15,2),
        @vavgprice     numeric(15,2),
        @flag          int ,
        @vrecord       int      
 begin 
        set @flag=1
        update inventory  set edqty=bgnqty + inqty - outqty,
                     edcost=bgncost + incost - outcost
		where acct=@oldacct;
        
        declare  acct_cursor cursor for 
            select goods_code,stock_code,edqty,edcost,avgprice from
                   inventory where acct=@oldacct ;
         --打开游标,取数
         open acct_cursor;
         fetch acct_cursor
	   into @vgoods_code,
		    @vstock_code,
			 @vedqty,
			 @vedcost,
			 @vavgprice;

         while @@FETCH_STATUS =0
     begin
         --选择性更新库存月报表
         set @vrecord=0
         select  @vrecord=count(*)    from   inventory  where acct=@newacct and goods_code=@vgoods_code and stock_code=@vstock_code;
         if @vrecord>0 
            begin 
            update inventory  set  bgnqty=@vedqty,bgncost=@vedcost,edqty=(@vedqty + inqty - outqty),avgprice=@vavgprice
            where  acct=@newacct and goods_code=@vgoods_code and stock_code=@vstock_code; 
            end       
         else
            begin
            insert into inventory                                          
	    (goods_code,stock_code,acct,bgnqty,bgncost,inqty,incost,outqty,outcost,edqty,edcost,saleqty,sale,saleprice,avgprice)  
	    values(@vgoods_code,@vstock_code,@newacct,@vedqty,@vedcost,0,0,0,0,@vedqty,0,0,0,0,@vavgprice)  ; 
            end
         set @vrecord=0
         fetch next from acct_cursor
          into @vgoods_code,
		    @vstock_code,
			 @vedqty,
			 @vedcost,
			 @vavgprice;
     end;
        close acct_cursor;
        deallocate acct_cursor;
end;

commit

go

 exec Acct_settle;
go