本站使用了 Pjax 等基于 JavaScript 的开发技术,但您的浏览器已禁用 JavaScript,请开启 JavaScript 以保证网站正常显示!

金蝶K3WISE月末单据日期自动变成下月1号

需求说明:
1.为了给供应链期末核算留出一些时间,要求每月的28号之后的库存类单据,单据日期自动变成下月1号;
2.控制系统中所有单据的日期不能手工修改;
3.库存类单据需要增加'制单日期'字段,便于追踪月末单据的实际录入日期;

月末库存单据日期自动变更

12月:不要自动变;
2月:大于等于26号的变成下月1号;
其他月份:大于等于28号的变成下月1号;
库存单
包括:外购入库、产品入库、其他入库、销售出库、生产领料、其他出库、调拨单

-- 库存单据
alter trigger tr_icstockbill_changedate
on icstockbill 
after insert,update
as
begin
  declare @FDate as date
  declare @FInterID as int 
  declare @FTranType as int
  if not exists (select 1 from deleted ) begin
    select @FInterID = FInterID,@FDate = FDate, @FTranType = FTranType from inserted 
    if month(@FDate) in(1,3,4,5,6,7,8,9,10,11) and DAY(@FDate)> 28 and @FTranType  in (1,2,10,21,24,29,41)
    update ICStockBill set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FInterID = @FInterID 
    if month(@FDate) = 2 and DAY(@FDate)> 26  and @FTranType  in (1,2,10,21,24,29,41)
    update ICStockBill set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FInterID = @FInterID 
    end
    
  if exists (select 1 from inserted) and exists(select 1 from deleted where fcheckerid is null) begin
    select @FInterID = FInterID,@FDate = FDate, @FTranType = FTranType from inserted 
    if month(@FDate) in(1,3,4,5,6,7,8,9,10,11) and DAY(@FDate)> 28 and @FTranType  in (1,2,10,21,24,29,41)
    update ICStockBill set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FInterID = @FInterID 
    if month(@FDate) = 2 and DAY(@FDate)> 26  and @FTranType  in (1,2,10,21,24,29,41)
    update ICStockBill set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FInterID = @FInterID 
  end
end

mto调整单

create trigger tr_mto_changedate
on t_mtochange
after insert,update
as
begin
    declare @FDate as date
    declare @FInterID as int 
    select @FInterID = FID,@FDate = FDate from inserted
    if month(@FDate) in(1,3,4,5,6,7,8,9,10,11) and DAY(@FDate)>= 28 
        update t_MTOChange set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FID = @FInterID 
    if month(@FDate) = 2 and DAY(@FDate)>= 26 
        update t_MTOChange set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FID = @FInterID 
end

采购发票

create trigger tr_ICPurchase_changedate
on ICPurchase
after insert,update
as
begin
    declare @FDate as date
    declare @FInterID as int 
    select @FInterID = FInterID,@FDate = FDate from inserted
    if month(@FDate) in(1,3,4,5,6,7,8,9,10,11) and DAY(@FDate)>= 28 
        update ICPurchase set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FInterID = @FInterID 
    if month(@FDate) = 2 and DAY(@FDate)>= 26 
        update ICPurchase set FDate = cast(YEAR(@FDate) as varchar)+'-'+cast(MONTH(@FDate)+1 as varchar)+'-'+'01 00:00:00.000' where FInterID = @FInterID 
end

销售发票

如果销售发票的单据头的月份 小于 源单销售出库单的最大月份,则将销售发票的日期改成销售出库单的最大日期

ALTER trigger [dbo].[tr_icsale_changedate]
on [dbo].[ICSale]
after insert,update
as
begin
    declare @FDate as date
    declare @FInterID as int 
    declare @FSourceDate as date
    select @FInterID = FInterID,@FDate = FDate from inserted
    select @FSourceDate = max(FDate) from ICStockBill where FInterID in (select FSourceInterId from ICSaleEntry where FInterID = @FInterID) 
    if month(@FDate) in(1,2,3,4,5,6,7,8,9,10,11) and MONTH(@FDate)<Month(@FSourceDate)
        update ICSale set FDate = @FSourceDate where FInterID = @FInterID 
end

控制单据日期不可修改

常用单据

单据名称FID默认值改后值
外购入库单a01480
产品入库单a02480
其他入库单a97480
销售出库单b01480
领料单b04480
其他出库单b09480
调拨单d01480
购货发票-专i0248
i0348
销售发票-专i0448
i0548
采购申请单p0148
采购订单p02112
销售订单s01112
select * from ICTemplate where FID in ('a01','a02','a97','b01','b04','b09','d01','i02','i03','i04','i05','p01','p02','s01')
and FFieldName = 'fdate'
and FCtlIndex = -1

update ICTemplate set FEnable = 0 where FFieldName = 'fdate' and FID in ('a01','a02','a97','b01','b04','b09','d01')

mto调整单日期控制在bos平台修改

库存单据增加制单日期字段

数据表中增加一列

alter table icstockbill add FCreateDate datetime

单据模板表上添加'制单日期'

insert into ICTemplate values('A02',31,46,'制单日期:',32,-1,1,2,0,'FCreateDate',    
160,420,2255,330,0,1,'宋体',9,0,0,'','','',0,3,1,31,'Kdtext',0,0,'制单日期',
'create date',0,0,1,1,1,'','','',1,0,1,0,0,'','','',0,8325,1245,2250,330,0)

【慎用】如果在数据库中手工添加了列之后,结账时会报错:列名无效'FCreateDate';
【解决方法】https://www.cnblogs.com/hjsofware/p/12184897.html
先找到存储过程:sp_helptext Prc_synchronizationData
然后将存储过程的代码黏贴到查询分析器里面,增加如下代码:

Snipaste_2020-07-30_21-37-26.png

然后alter 存储过程即可。

【建议】使用使用系统原有的“一级审核日期”字段,将FCtlType的值修改为32 ,然后在bos中设置可见性。


Offer

推广

 继续浏览关于 金蝶k3wise 的文章

 本文最后更新于:2020/08/15 12:35:32,可能因经年累月而与现状有所差异,有任何疑问可以随时与258771198@qq.com沟通,邮箱永久有效

 引用转载请注明:manacode(码扣) > SQL,ERP > 金蝶K3WISE月末单据日期自动变成下月1号