之前金蝶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
完整执行语句如附件