金蝶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

控制单据日期不可修改

常用单据

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
【建议】使用使用系统原有的“一级审核日期”字段，将FCtlType的值修改为32 ，然后在bos中设置可见性。