一、公司现在要求不想每张采购订单都要领导审核。 经常相同的物料在相同的供应商上采购, 天天重复审核,领导觉得很浪费时间。让我想办法解决该问题。
思路如下:
二、我就想到用采购价格管理维护的最高价来限制采购订单的价格。勾上下图的参数。
主要是维护好采购价格管理的最高限价就可以了。如果用金蝶K3的界面操作得 先维护采购价格,,再维护最高限价,然后让领导在众多物料中找到该数据来审核。 理论上是可以行的通的,就是太耗人力了。领导肯定也不会同意的。
四、所以我就想到 用系统提供的采购报价单。但现成的采购报价单也存在问题 1、采购价格是能同步到采购价格管理上,但 最高限价没过去。 2、采购报价在在审核后还要点接受价格,才能点价格更新,才能过去价格管理那,操作繁琐不说,如果漏点了就没有价格过去。 3、如果接受了价格后想改价格又的不允许改。
五、诸多麻烦,我决定自己改造采购报价单,当点击审核后自动同步价格到采购价格管理的价格和最高价格上,并自动审核采购价格管理。
(1)因为价格管理的价格和最高价格分别在两张表上 ICSMQuotationEntry 和ICSMQuotation,为了方便查询我建一个视图
----2、创建视图报价表视图,用于触发器新增报价单保存的触发器校验----------
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 ;
同事创建一张表存采购价格历史记录,也方便以后做价格变化分析
----创建采购价格历史记录表
drop TABLE t_SupplyHis
CREATE TABLE [dbo].[t_SupplyHis](
[FSupID] [int] NULL,
[FItemID] [int] NULL,
[FUnitID] [int] NULL,
[FCyID] [int] NULL,
[FPrice] [decimal](28, 10) NULL,
[FPOHighPrice] [decimal](28, 10) NULL,
[FPType] [int] NULL,
[FStartDate] [datetime] NULL,
[FDelDate] [datetime] NULL
)
(2)得想办法控制采购报价单不能出现相同供应商相同物料能重复保存,因为一旦出现了到时过去价格管理那就有两个价格,不好控制。 所以得在保存采购报价单的时候用触发器限制,如下
-----3、创建触发器 ,新增采购报价单,如果存在相同供应商+物料数据就不能保存,如果不存在再把历史报价信息写入到表中----------------
CREATE 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=FVendorID 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、创建触发器 修改报价单的价格要取历史数据信息写入到表中--------------
drop TRIGGER ICSMQuotationEntry_update;
go
CREATE 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=FVendorID 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
【说明】这里FNOTE2 是为了在界面显示出历史报价, 方便领导审核报价时候知道这次跟上次的价格变化,做对比。t_SupplyHis的数据是在删除的采购价格管理写入的数据,下面会讲到。
(3)审核报价单,把价写入到采购价格管理 反审价格单,把采购价格管理数据删除
-------5 触发器处理 审核报价单,把价写入到采购价格管理 反审价格单,把采购价格管理数据删除 -----
drop TRIGGER [ICSMQuotation_update];
go
CREATE 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.FVendorID 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.FVendorID 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.FVendorID 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.FVendorID,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.FVendorID,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=FVendorID from inserted;
select @FID=FID from inserted;
delete @FItemIDtable;
insert into @FItemIDtable (FVendorID,FItemID)
select v.FVendorID,v.FItemID from v_ICSMQuotation v where FClassTypeID='1007311' and v.FVendorID=@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
GO
(4) 删除采购价格管理数据时把当前数据保存到价格历史表
--- 删除采购价格管理数据时把当前数据保存到价格历史表
drop trigger [t_SupplyEntry_del]
go
CREATE trigger [dbo].[t_SupplyEntry_del]
on [dbo].[t_SupplyEntry]
after delete
as
INSERT INTO t_SupplyHis (FSupID,FItemID,FUnitID,FCyID,FPrice,FPOHighPrice,FPType,FStartDate,FDelDate)
select b.FSupID,b.FItemID,b.FUnitID, b.FCyID ,b.FPrice,a.FPOHighPrice,a.FPType,b.FLastModifiedDate, GETDATE() from t_Supply a join deleted b
on a.FPType= b.FPType and a.FSupID=b.FSupID and a.FItemID=b.FItemID
至此,已经能实现想要的功能了,保存的时候如果超过最高价格会弹出如下窗口
(4)但没有维护价格管理的物料还是没有拦截。所以我加了个参数,看公司要求是否需要拦截没维护价格管理的物料。
-- 增加一个控制参数,觉得该功能是否启用
INSERT INTO[dbo].[t_SystemProfile]
([FCategory]
,[FKey]
,[FValue]
,[FReadonly]
,[FDescription]
,[FLevel]
,[FExplanation]
,[FFormat]
,[FSort]
,[FDescription_cht]
,[FDescription_en])
VALUES
('IC'
,'meiyoubaojiabunengbaocun'
,1
,0
,'没有报价单不能保存'
,0
,'0:不启用,1:启用'
,NULL
,NULL
,'没有报价单不能保存'
,''
)
-- 设置启用
update t_SystemProfile set FValue=1 where FCategory='IC' and (FKey='meiyoubaojiabunengbaocun' )
(5)触发器控制没有价格政策维护的物料不允许保存
Create 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 FVendorID=@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
【说明】这里可以查采购报价单也可以查采购价格管理
至此功能完成
【注意】要通过金蝶K3界面控制不要让人随便能修改删除采购价格管理,否则容易出现跟采购报价单单价不一致
整个过程的处理sql如附件