金蝶K3采购报价单审核后自动写入采购价格管理来控制采购订单价格(二)

之前金蝶K3采购报价单审核后自动写入采购价格管理来控制采购订单价格 在使用过程中发现，金蝶自带的采购报价单有问题：供应商能选择禁用的供应商。 一查bos才知道原来是取自供应商档案的。而禁用供应商，供应商档案又不会自动禁用的

 要解决这问题。只有不用不用采购报价单自带的供应商，自己定义一个供应商，取自基础数据的供应商。

1、通过跟踪查到自定义的供应商在数据库中字段名为FBase ， 把原来数据的供应商ID的值更新到自定义的供应商字段上

 update   ICSMQuotation   set FBase= FVendorID  ;

2、重建视图v_ICSMQuotation，让视图有FBase字段

drop view v_ICSMQuotation;
 go
 create view v_ICSMQuotation
  as select m.*,e.FItemID,FAuxTaxPrice from  ICSMQuotationEntry e  left join  ICSMQuotation m on  m.FID=e.FID  and  m.FClassTypeID=1007311 ;
 go

3、修改触发器ICSMQuotationEntry_insert，参数@FVendorID的取自

ALTER trigger [dbo].[ICSMQuotationEntry_insert]
  on [dbo].[ICSMQuotationEntry]
  after insert
 as
  declare @FVendorID varchar(50),
   @FItemID varchar(50),@FID varchar(50), @count int,@FItemName varchar(500), @msg  varchar(1000)
  
   select  @FItemID=FItemID from inserted;
   select @FVendorID=FBase  from ICSMQuotation where FID  in(select FID from inserted)
   select  @FID=FID from inserted;
    select @count= COUNT(1) FROM v_ICSMQuotation  WHERE FVendorID=@FVendorID and FItemID=@FItemID    
 if @count>1
begin
 select @FItemName=  fname from t_ICItem  where FItemID=@FItemID;
   set @msg='物料【'+@FItemName+'】'+'报价已经存在，不能再增加，只能执行修改数据'
  --  RAISERROR( @FID   ,1,1)
    RAISERROR(@msg,1,1)
	ROLLBACK TRANSACTION
end 
else
	begin
		 update ICSMQuotationEntry set FNOTE2=
		    (select  top 1 '在时间'+CONVERT(varchar(20),fstartdate,120) +'定价'  +CAST(   fprice  as varchar(50))   from t_SupplyHis  
		    where  fsupid=@FVendorID  and fitemid=@FItemID  order by fdeldate desc )
		  where FID=@FID and fitemid=@FItemID
	end
	

4、修改试图ICSMQuotationEntry_update参数@FVendorID的取自

 -----4、创建触发器 修改报价单的价格要取历史数据信息写入到表中--------------

	ALTER TRIGGER [dbo].[ICSMQuotationEntry_update]
   ON  [dbo].[ICSMQuotationEntry]
   after UPDATE 
 as
 if update (FAuxTaxPrice)
 begin
  declare @FVendorID varchar(50),
   @FItemID varchar(50),@FID varchar(50), @count int,@FItemName varchar(500), @msg  varchar(1000)
  
   select  @FItemID=FItemID from inserted;
   select @FVendorID=FBase  from ICSMQuotation where FID  in(select FID from inserted)
   select  @FID=FID from inserted;
  update ICSMQuotationEntry set FNOTE2=
		    (select  top 1 '在时间'+CONVERT(varchar(20),fstartdate,120) +'定价'  +CAST(  fprice  as varchar(50))   from t_SupplyHis  
		    where  fsupid=@FVendorID  and fitemid=@FItemID  order by fdeldate desc )
		  where FID=@FID and fitemid=@FItemID

 end

5、修改触发器inserted.FVendorID改为 inserted.FBase

-------5 触发器处理  审核报价单，把价写入到采购价格管理   反审价格单,把采购价格管理数据删除        -----
ALTER TRIGGER [dbo].[ICSMQuotation_update]
   ON  [dbo].[ICSMQuotation]
   after UPDATE
AS 
   declare @FVendorID varchar(50),@FID varchar(50);
 declare @FItemIDtable table (FVendorID varchar(50), FItemID varchar(50))    
   if update (FCheckerID)
   begin
     
      if exists (SELECT 1 FROM inserted  WHERE FCheckerID <> 0)                                                                             
      ---审核
       BEGIN 
       
          if exists (SELECT 1 FROM t_Supply   inner  join ICSMQuotationEntry on ICSMQuotationEntry.FItemID=t_Supply.FItemID
              inner join inserted on    t_Supply.FSupID =inserted.FBase  and   inserted.fid=ICSMQuotationEntry.fid        
              where t_Supply.FPType=1)
             begin  --如果存在就更新  
		  --更新最高价格		
			update t_Supply set FPOHighPrice=ICSMQuotationEntry.FAuxTaxPrice,FcheckDate= GETDATE() from t_Supply 
				   inner  join ICSMQuotationEntry on ICSMQuotationEntry.FItemID=t_Supply.FItemID
				   inner join inserted on    t_Supply.FSupID =inserted.FBase  and   inserted.fid=ICSMQuotationEntry.fid        
				   where t_Supply.FPType=1
			 --更新报价      
			update t_SupplyEntry 
					 set FPrice=ICSMQuotationEntry.FAuxTaxPrice ,
					   FUsed=1,
					  FCheckerID=inserted.FCheckerID,
					   FCheckDate=GETDATE(),
					   FQuoteTime=GETDATE(),
					   FDisableDate='2100-01-01 00:00:00.000',
					   FLastModifiedBy =inserted.FCheckerID,
					   FLastModifiedDate=GETDATE(),
					   FRemark=inserted.FBillNo+'报价单自动更新'
					 from t_SupplyEntry 
				   inner  join ICSMQuotationEntry on ICSMQuotationEntry.FItemID=t_SupplyEntry.FItemID
				   inner join inserted on    t_SupplyEntry.FSupID =inserted.FBase  and   inserted.fid=ICSMQuotationEntry.fid     
				   where t_SupplyEntry.FPType=1
	  end
	     else
		   begin-- 插入
			  DECLARE @FInterID INT;
			   exec GetICMaxNumOld 't_Supply', @FInterID output  
			 
		 
		 
		  INSERT INTO t_SupplyEntry	([FBrNo],[FSupID],[FItemID] ,[FEntryID],[FUnitID] ,[FStartQty],[FEndQty] ,[FPrice] ,[FCyID],[FDiscount],[FLeadTime],[FQuoteTime],[FUsed],[FDisableDate],[FRemark]
			   ,[FPType] ,[FLastModifiedDate],[FLastModifiedBy],[FCheckerID],[FCheckDate])
		  select 0,b.FBase,a.FItemID ,@FInterID,a.FUnitID,0,0, a.FAuxTaxPrice,b.FCurrencyID,0,0,GETDATE(),1,'2100-01-01 00:00:00.000',b.FBillNo+'报价单自动更新',
		  1,GETDATE(),b.FBiller, b.FCheckerID,GETDATE()
		   from ICSMQuotationEntry a   join inserted b on a.FID= b.FID
		 
		  
		  
		     INSERT INTO t_Supply (FBrNo,FItemID,FSupID,FCurrencyID,FPOHighPrice,FPType,FcheckDate) 
		     select 0,a.FItemID, b.FBase,b.FCurrencyID ,a.FAuxTaxPrice*1.05,1, GETDATE() from ICSMQuotationEntry a   join inserted b on a.FID= b.FID
		     
		        
		  end
       end   
       
       else
       --反审
          begin          
           select  @FVendorID=FBase from inserted;   
           select  @FID=FID from inserted; 
         
         delete @FItemIDtable;
          insert  into @FItemIDtable (FVendorID,FItemID)
           select v.FBase,v.FItemID from v_ICSMQuotation v where FClassTypeID='1007311' and  v.FBase=@FVendorID  and  v.FID=@FID
  
  delete       t_SupplyEntry   where  FPType=1 and FSupID   in (select  FVendorID  from  @FItemIDtable) and FItemID  in (select  FItemID  from  @FItemIDtable)
   delete     t_Supply where FPType=1 and FSupID   in (select  FVendorID  from  @FItemIDtable) and FItemID  in (select  FItemID  from  @FItemIDtable)
     
      
          end
       
   end

6、修改触发器POOrder_insert

  --- 采购订单触发器
   ALTER trigger [dbo].[POOrder_insert]
  on [dbo].[POOrder]
  after insert
 as
  declare @FVendorID varchar(50),@FInterID varchar(50),
   @count int,@FItemName varchar(500), @msg  varchar(1000),@FValue varchar(50)
   declare @FItemIDtable table (FVendorID varchar(50), FItemID varchar(50),FName varchar(500))    
  
  select @FValue=FValue  from t_SystemProfile where FCategory='IC' and  FKey='meiyoubaojiabunengbaocun'  
  if @FValue=1
   begin
		   select  @FVendorID=FSupplyID from inserted;
		   select  @FInterID=FInterID  from  inserted;	
		   insert  into @FItemIDtable (FVendorID,FItemID,FName)
		      select @FVendorID,FItemID,(select top 1 fname from t_ICItem  where FItemID=pe.FItemID)as FName from  POOrderEntry pe where  FInterID =@FInterID
		      and FItemID not  in (select FItemID from  ICSMQuotationEntry e  where  FID  in(select FID from  ICSMQuotation  where FBase=@FVendorID and fcheckdate  is not  null) )
             select @count= COUNT(1) FROM @FItemIDtable   
		 if @count>0
		begin
		 select @msg=   ( stuff((select ',' + FName from @FItemIDtable   for xml path('')), 1, 1, '') ) from   @FItemIDtable 
		   set @msg=@msg+ '    如上物料没有报价，请先创建报价单或审核报价单后，再操作'
			RAISERROR(@msg,18,18)
			ROLLBACK TRANSACTION
		end 
end

完整执行语句如附件