9.tr_inv_insert.sql

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

/*----触发器tr_inv_insert----------------------	*/
/*用到的存储过程：pr_inv_init,pr_inv_update,pr_update_wlclient,pr_update_wlvendor                    */
/*作者：吕勇									*/
/*日期：2006-04-10								*/
/*版本：v1.0									*/
/*修改人：LY									*/
/*修改日期：2006-05-13							*/
if exists (select 1 from sysobjects where id = object_id('tr_inv_insert'))
   drop trigger tr_inv_insert
go

create trigger tr_inv_insert on f_invtx_detail WITH ENCRYPTION
for insert,update,delete
as
declare @ls_part     char(16), --货品代号
        @ls_prloc    char(30),--仓库
        @ls_txcode   char(3),--交易类型
        @ldb_orgqty  numeric(12,4),--交易数量
        @ls_status   char(2),--状态
        @ll_docseq   int,--序号
        @ls_uom      varchar(10),--单位
        @ldt_docdate datetime,--审核日期
		@ldb_updqty  numeric(12,4),--更新数量
		@li_year	 smallint,
		@li_month    tinyint,
		@ls_corpid   char(36),
		@ldb_acramt  money,
		@ll_originalcount int,
		@ls_id		char(36),
		@ls_vendorid char(36)
if @@rowcount < 1 return
select @ls_id=c_id,@ls_corpid=c_corporationid,@ls_txcode=c_txcode,@ls_part=c_part,@ls_prloc=c_toprloc,
	   @ldb_orgqty=i_orgqty,@ls_status=c_status,@ll_docseq=i_docseq,
       @ls_uom=c_uom,@ldt_docdate=d_docdate,@ldb_acramt=i_acramt,@ls_vendorid=c_vendor
from inserted
order by c_id
  BEGIN
     select @li_year = year(@ldt_docdate)
     select @li_month = month(@ldt_docdate)
     /*采购入库单*/
     if  @ls_txcode = 'STI' 
       begin
		  IF @ls_status = 'NA'
			begin
			    --如果是从'OP'状态改为'NA'状态，则需要反向更新
				select @ll_originalcount = count(*) from deleted where c_id = @ls_id and i_docseq = @ll_docseq and  c_status  = 'OP'
				if @ll_originalcount >= 1
					begin
						select @ldt_docdate = d_docdate from deleted where c_id = @ls_id and i_docseq = @ll_docseq and  c_status  = 'OP'
						select @li_year = year(@ldt_docdate)
						select @li_month = month(@ldt_docdate)
						select @ldb_updqty = @ldb_orgqty
						--更新库存档案&公司货品档案
						exec pr_inv_update '-',@ls_corpid,@ls_part, @ls_prloc, @ls_uom,@ldb_updqty,@ldb_acramt
						--注释：更新库存明细账
						update f_inv_detail set i_inqty = i_inqty - @ldb_updqty,i_inamt = i_inamt - @ldb_acramt,i_stiqty = i_stiqty - @ldb_updqty,i_stiamt = i_stiamt - @ldb_acramt  where c_corporationid = @ls_corpid and c_part = @ls_part and c_prloc = @ls_prloc and i_year = @li_year and i_month = @li_month
					     --注释：更新库存总账
						update f_inv_zz set i_inamt = i_inamt - @ldb_acramt ,i_stiamt = i_stiamt - @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
						--更新供应商档案
					    exec pr_update_wlvendor '-',@ls_vendorid,@ldb_acramt            
						select @ldt_docdate = d_docdate from deleted where c_id = @ls_id and i_docseq = @ll_docseq and  c_status  = 'OP'											
					end					
			end
		if @ls_status = 'OP' 
            begin
            select @ldb_updqty = @ldb_orgqty  
			 --自动对未开帐的货品开帐
			exec pr_inv_init   @ls_corpid,@ls_part,@ls_prloc,@ls_uom,@li_year,@li_month
			--更新库存档案
			exec pr_inv_update '+',@ls_corpid,@ls_part, @ls_prloc, @ls_uom,@ldb_updqty,@ldb_acramt
			--注释：更新库存明细账
			update f_inv_detail set i_inqty = i_inqty + @ldb_updqty,i_inamt = i_inamt + @ldb_acramt,i_stiqty = i_stiqty + @ldb_updqty,i_stiamt = i_stiamt + @ldb_acramt  where c_corporationid = @ls_corpid and c_part = @ls_part and c_prloc = @ls_prloc and i_year = @li_year and i_month = @li_month
			--注释：更新库存总账
			update f_inv_zz set i_inamt = i_inamt + @ldb_acramt ,i_stiamt = i_stiamt + @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
			--更新供应商档案
			exec pr_update_wlvendor '+',@ls_vendorid,@ldb_acramt
		end
	 end
     /*采购退库单*/
     else if  @ls_txcode = 'STO' 
       begin
		  IF @ls_status = 'NA'
			begin
				select @ll_originalcount = count(*) from deleted where c_id = @ls_id and i_docseq = @ll_docseq and  c_status  = 'OP'
				if @ll_originalcount >= 1
					begin
						select @ldb_updqty = @ldb_orgqty
						--更新库存档案&公司货品档案
						exec pr_inv_update '+',@ls_corpid,@ls_part, @ls_prloc, @ls_uom,@ldb_updqty,@ldb_acramt
						--注释：更新库存明细账						
						update f_inv_detail set i_outqty = i_outqty - @ldb_updqty,i_outamt = i_outamt - @ldb_acramt,i_stoqty = i_stoqty - @ldb_updqty,i_stoamt = i_stoamt - @ldb_acramt  where c_corporationid = @ls_corpid and c_part = @ls_part and c_prloc = @ls_prloc and i_year = @li_year and i_month = @li_month						    
						--注释：更新库存总账
						 update f_inv_zz set i_outamt = i_outamt - @ldb_acramt ,i_stoamt = i_stoamt - @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
						--更新供应商往来明细和供应商往来总帐以及供应商档案
						exec pr_update_wlvendor '+',@ls_vendorid,@ldb_acramt
					end					
			end
		if @ls_status = 'OP'
            begin
            select @ldb_updqty = @ldb_orgqty
			--自动对未开帐的货品开帐
			exec pr_inv_init   @ls_corpid,@ls_part,@ls_prloc,@ls_uom,@li_year,@li_month
			--更新库存档案&公司货品档案
			exec pr_inv_update '-',@ls_corpid,@ls_part, @ls_prloc, @ls_uom,@ldb_updqty,@ldb_acramt
			--注释：更新库存明细账			
			update f_inv_detail set i_outqty = i_outqty + @ldb_updqty,i_outamt = i_outamt + @ldb_acramt,i_stoqty = i_stoqty + @ldb_updqty,i_stoamt = i_stoamt + @ldb_acramt  where c_corporationid = @ls_corpid and c_part = @ls_part and c_prloc = @ls_prloc and i_year = @li_year and i_month = @li_month
			--注释：更新库存总账
			update f_inv_zz set i_outamt = i_outamt + @ldb_acramt ,i_stoamt = i_stoamt + @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
			--更新供应商往来明细和供应商往来总帐以及供应商档案
			exec pr_update_wlvendor '-',@ls_vendorid,@ldb_acramt
		end
     end
     /*出库单*/
     else if  @ls_txcode = 'SHP' 
       begin
		  IF @ls_status = 'NA'
			begin
				select @ll_originalcount = count(*) from deleted where c_id = @ls_id and i_docseq = @ll_docseq and  c_status  = 'OP'
				if @ll_originalcount >= 1
					begin
						select @ldb_updqty = @ldb_orgqty
						--更新库存档案&公司货品档案
						exec pr_inv_update '+',@ls_corpid,@ls_part, @ls_prloc, @ls_uom,@ldb_updqty,@ldb_acramt,@ls_txcode
						--注释：更新库存明细账
						update f_inv_detail set i_outqty = i_outqty - @ldb_updqty,i_outamt = i_outamt - @ldb_acramt,i_shpqty = i_shpqty - @ldb_updqty,i_shpamt = i_shpamt - @ldb_acramt  where c_corporationid = @ls_corpid and c_part = @ls_part and c_prloc = @ls_prloc and i_year = @li_year and i_month = @li_month
						 --注释：更新库存总账
						update f_inv_zz set i_outamt = i_outamt - @ldb_acramt ,i_shpamt = i_shpamt - @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
						--更新客户往来明细和客户商往来总帐以及客户档案
						exec pr_update_wlclient '-',@ls_vendorid,@ldb_acramt
					end					
			end
		if @ls_status = 'OP'
            begin
            select @ldb_updqty = @ldb_orgqty
			--自动对未开帐的货品开帐
			exec pr_inv_init   @ls_corpid,@ls_part,@ls_prloc,@ls_uom,@li_year,@li_month
			--更新库存档案&公司货品档案
			exec pr_inv_update '-',@ls_corpid,@ls_part, @ls_prloc, @ls_uom,@ldb_updqty,@ldb_acramt,@ls_txcode
			--注释：更新库存明细账
			update f_inv_detail set i_outqty = i_outqty + @ldb_updqty,i_outamt = i_outamt + @ldb_acramt,i_shpqty = i_shpqty + @ldb_updqty,i_shpamt = i_shpamt + @ldb_acramt  where c_corporationid = @ls_corpid and c_part = @ls_part and c_prloc = @ls_prloc and i_year = @li_year and i_month = @li_month
			 --注释：更新库存总账
			update f_inv_zz set i_outamt = i_outamt + @ldb_acramt ,i_shpamt = i_shpamt + @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
			--更新客户往来明细和客户商往来总帐以及客户档案
			exec pr_update_wlclient '+',@ls_vendorid,@ldb_acramt
		end
     end
     /*退货单*/
     else if  @ls_txcode = 'RTN' 
       begin
          IF @ls_status = 'NA'
			begin
				select @ll_originalcount = count(*) from deleted where c_id = @ls_id and i_docseq = @ll_docseq and  c_status  = 'OP'
				if @ll_originalcount >= 1
					begin
						select @ldb_updqty = @ldb_orgqty