需求说明:
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 | 默认值 | 改后值 |
---|---|---|---|
外购入库单 | a01 | 48 | 0 |
产品入库单 | a02 | 48 | 0 |
其他入库单 | a97 | 48 | 0 |
销售出库单 | b01 | 48 | 0 |
领料单 | b04 | 48 | 0 |
其他出库单 | b09 | 48 | 0 |
调拨单 | d01 | 48 | 0 |
购货发票-专 | i02 | 48 | |
普 | i03 | 48 | |
销售发票-专 | i04 | 48 | |
普 | i05 | 48 | |
采购申请单 | p01 | 48 | |
采购订单 | p02 | 112 | |
销售订单 | s01 | 112 |
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中设置可见性。