5.pr_inv_init.sql

学习PB入门的好帮手,附件中包含以下文件夹

/*----本存储过程pr_inv_init用于触发器tr_inv_insert------	*/
/*参数1：公司ID                               */
/*参数2：货品ID									*/
/*参数3：仓库ID									*/
/*参数4：货品计量单位							*/
/*参数5：年										*/
/*参数6：月										*/
/*作者：吕勇									*/
/*日期：2006-04-10								*/
/*版本：v1.0									*/
/*修改人：										*/
/*修改日期：									*/
GO
IF EXISTS(SELECT name FROM sysobjects
      WHERE name = 'pr_inv_init' AND type = 'P')
   DROP PROCEDURE pr_inv_init
GO
--USE ercdata
GO
CREATE PROCEDURE pr_inv_init @ls_corpid char(36),@ls_part char(16),@ls_prloc char(30),@ls_uom varchar(10),@l_year smallint,@l_month tinyint
WITH ENCRYPTION
AS
declare @ll_row	     int,
		@l_count     int,
		@ldb_beginqty numeric(12,4),
		@ldb_beginamt money,
		@ldb_tzqty numeric(12,4),  
		@ldb_tzamt  money,
		@ldb_beginauxqty numeric(12,4),
		@ldb_avgcost  money
--初始化
select @ldb_beginqty =0
select @ldb_beginamt = 0
select @ldb_tzqty = 0
select @ldb_tzamt = 0
select @ldb_beginauxqty = 0
select @ldb_avgcost = 0
--
select @l_count = count(*) from f_storepart where f_storepart.c_part = @ls_part and f_storepart.c_prloc = @ls_prloc and c_corporationid = @ls_corpid
if @l_count < 1
	--如果库存档案没有开帐，则自动开帐
	begin
		insert into f_storepart(c_corporationid,c_part,c_prloc,c_uom,i_onhand,i_startqty,i_startmoney,i_onhandmoney)  
			values (@ls_corpid,@ls_part,@ls_prloc,@ls_uom,0,0,0,0)
		if @@error <> 0 
			begin
				rollback tran 
				return 
			end
		end 
select @l_count = count(*) from f_inv_detail where c_corporationid = @ls_corpid and c_part = @ls_part and i_year = @l_year and i_month = @l_month and c_prloc = @ls_prloc
if @l_count < 1
	--如果库存明细账没有开帐，则自动开帐
	begin
		select @l_count = count(*) from f_inv_detail where c_corporationid = @ls_corpid and c_part = @ls_part and i_year = @l_year and i_month = @l_month -1 and c_prloc = @ls_prloc
		if @l_count = 1
		--如果有上月余额
			begin
				select  @ldb_beginqty =i_beginqty + i_inqty - i_outqty,
				@ldb_beginamt = i_beginamt + i_inamt - i_outamt ,
				@ldb_avgcost = i_avgcost
				from f_inv_detail 
				where c_corporationid = @ls_corpid and c_part = @ls_part and i_year = @l_year and i_month = @l_month -1 and c_prloc = @ls_prloc
			end
		else
		--否则
			begin
				select @l_count = count(*) from f_inv_detail where c_corporationid = @ls_corpid and c_part = @ls_part  and i_month = 99 and c_prloc = @ls_prloc
				if @l_count =1
				--如果有期初余额
					begin
						select @ldb_beginqty =i_beginqty,
						@ldb_beginamt = i_beginamt,
						@ldb_avgcost = i_avgcost
						from f_inv_detail 
						where c_corporationid = @ls_corpid and c_part = @ls_part  and i_month = 99 and c_prloc = @ls_prloc
						update f_inv_detail set i_month = 0  ,i_beginqty =0,i_beginamt = 0 where c_corporationid = @ls_corpid and c_part = @ls_part  and i_month = 99 and c_prloc = @ls_prloc
					end
			end
		select @ldb_beginqty =isnull(@ldb_beginqty,0)
		select @ldb_beginamt = isnull(@ldb_beginamt,0)
		select @ldb_tzqty = isnull(@ldb_tzqty,0)
		select @ldb_tzamt = isnull(@ldb_tzamt,0)
		select @ldb_beginauxqty = isnull(@ldb_beginauxqty  ,0)
		select @ldb_avgcost = isnull(@ldb_avgcost,0)
	
	
		insert into f_inv_detail(c_corporationid,i_year,i_month,c_part,c_prloc,i_beginqty,i_beginamt,i_inqty,i_inamt,i_outqty,i_outamt,i_stiqty,
		                        i_stiamt,i_stoqty,i_stoamt,i_shpqty,i_shpamt,i_rtnqty,i_rtnamt,i_maiqty,i_maiamt,i_maoqty,i_maoamt,i_csuqty,i_csuamt,i_cloqty,i_cloamt,
					i_avgcost)
		values (@ls_corpid,@l_year,@l_month,@ls_part,@ls_prloc,@ldb_beginqty,@ldb_beginamt,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,@ldb_avgcost)
		if @@error <> 0 
			begin
				rollback tran 
                return 
		end
	end 
select  @l_count = count(*) from f_inv_zz where c_corporationid = @ls_corpid and i_year = @l_year and i_month = @l_month
if @l_count < 1
	--如果库存总账没有开帐，则自动开帐
	begin
		select @l_count = count(*) from f_inv_zz where c_corporationid = @ls_corpid and i_year = @l_year and i_month = @l_month -1
		if @l_count = 1
		--如果有上月余额
			begin
				select @ldb_beginamt = i_beginamt + i_inamt - i_outamt 
				from f_inv_zz 
				where c_corporationid = @ls_corpid and i_year = @l_year and i_month = @l_month -1
			end
		else
		--否则
			begin
				select @l_count = count(*) from f_inv_zz where c_corporationid = @ls_corpid and i_month = 99
				if @l_count =1
				--如果有期初余额
					begin
						select @ldb_beginamt = i_beginamt  from f_inv_zz where c_corporationid = @ls_corpid and i_month = 99
						update f_inv_zz set i_month = 0  ,i_beginamt = 0 where c_corporationid = @ls_corpid and i_month = 99
					end
			end
		select @ldb_beginamt = isnull(@ldb_beginamt,0)
		select @ldb_tzamt = isnull(@ldb_tzamt,0)
	
		insert into f_inv_zz(c_corporationid,i_year,i_month,i_beginamt,i_inamt,i_outamt,i_stiamt,i_stoamt,i_shpamt,i_rtnamt,i_maiamt,i_maoamt,i_csuamt,i_cloamt)
			values (@ls_corpid,@l_year,@l_month,@ldb_beginamt,0,0,0,0,0,0,0,0,0,0)
		if @@error <> 0 
			begin
				rollback tran 
				return 
		end
	end
GO