9.tr_inv_insert.sql

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

						--更新库存档案&公司货品档案
						exec pr_inv_update '-',@ls_corpid,@ls_part, @ls_prloc, @ls_uom,@ldb_updqty,@ldb_acramt,@ls_txcode
						--注释：更新库存明细账
						update f_inv_detail set i_inqty = i_inqty - @ldb_updqty,i_inamt = i_inamt - @ldb_acramt,i_rtnqty = i_rtnqty - @ldb_updqty,i_rtnamt = i_rtnamt - @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_rtnamt = i_rtnamt - @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_inqty = i_inqty + @ldb_updqty, i_inamt = i_inamt + @ldb_acramt,i_rtnqty = i_rtnqty + @ldb_updqty,i_rtnamt = i_rtnamt + @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_rtnamt = i_rtnamt + @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 = 'MAI' 
       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_inqty = i_inqty - @ldb_updqty, i_inamt = i_inamt - @ldb_acramt,i_maiqty = i_maiqty - @ldb_updqty,i_maiamt = i_maiamt - @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_maiamt = i_maiamt - @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
					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_maiqty = i_maiqty + @ldb_updqty,i_maiamt = i_maiamt + @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_maiamt = i_maiamt + @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
		end
     end
     /*生产领料单*/
     else if  @ls_txcode = 'MAO' 
       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_maoqty = i_maoqty - @ldb_updqty,i_maoamt = i_maoamt - @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_maoamt = i_maoamt - @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
					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_maoqty = i_maoqty + @ldb_updqty,i_maoamt = i_maoamt + @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_maoamt = i_maoamt + @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
		end
     end
     /*盘盈单*/
     else if  @ls_txcode = 'CSU' 
       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_inqty = i_inqty - @ldb_updqty,i_inamt = i_inamt - @ldb_acramt,i_csuqty = i_csuqty - @ldb_updqty,i_csuamt = i_csuamt - @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_csuamt = i_csuamt - @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
					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_csuqty = i_csuqty + @ldb_updqty,i_csuamt = i_csuamt + @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_csuamt = i_csuamt + @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
		end
     end
     /*盘亏单*/
     else if  @ls_txcode = 'CLO' 
       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_cloqty = i_cloqty - @ldb_updqty,i_cloamt = i_cloamt - @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_cloamt = i_cloamt - @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
					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_cloqty = i_cloqty + @ldb_updqty,i_cloamt = i_cloamt + @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_cloamt = i_cloamt + @ldb_acramt where c_corporationid = @ls_corpid and i_year = @li_year and i_month = @li_month
		end
     end
  END

if @@error <> 0 
   begin 
      rollback tran
      return
   end
return