金蝶K3--委外加工订单--触发器--更新预测单号

本文深入探讨了SQL触发器的创建与使用,通过具体示例展示了如何在插入或更新操作后触发特定的数据库操作。文章重点介绍了在特定条件下更新关联表的字段,并通过检查供应商资料是否已审核来确保数据一致性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

create trigger icsubcontract_cust
	on icsubcontract
for insert,update
as	--if exists(select 1 from sys.triggers where object_name(parent_id)='icsubcontract') drop trigger icsubcontract_cust
begin
	update b set forderno=c.fsourcebillno,finteridorder_src=c.fsourceinterid,fentryidorder_src=c.fsourceentryid from inserted a inner join icsubcontractentry b on a.finterid=b.finterid left outer join porequestentry c on b.finterid_src=c.finterid and b.fentryid_src=c.fentryid where abs(c.fsourcetrantype) in (87)
	if exists(select 1 from inserted a,t_item b where a.fsupplyid=b.fitemid and isnull(fchkuserid,0)=0)
	begin
		rollback tran
		raiserror('供应商资料没有审核,请联络财务部审核该资料',18,18)
	end
end

 

转载于:https://my.oschina.net/ansenchina/blog/3024788

CREATE trigger icstockbill_check --实现对物料单据管控 on Icstockbill for insert as Declare @Ftrantype int --单据类别定义/生产领料单24/调拔单41/销售出库单21 Select @Ftrantype=Ftrantype from inserted --控制领料单的领料日期不能小于生产任务单的计划开工日期 倒扣物料只能车间仓库发料 if (@Ftrantype=24) begin declare @icmo varchar(20) declare @message varchar(200) declare @message0 varchar(200) declare @message00 varchar(200) declare @message000 varchar(200) declare @finterid240 int set @message=&#39;错误!领料日期不能小于生产任务单计划开工日期,请与生管人员联系!错误单号:&#39; set @message0=&#39;生产领料单发料仓库不正确,倒扣物料不能从 原料仓/半成品仓/成品仓 发料&#39; set @message00=&#39;已完工生产任务单不能跨月领料.请检查领料日期!&#39; set @message000=&#39;生产领料单必需关联源生产任务单号,请重新录入单据!&#39; if exists ( select t1.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid where t2.ficmointerid=0 ) begin RAISERROR(@message000,18,18) ROLLBACK end if exists( select t3.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid where t2.fsourceinterid>0 and t1.fdate0 and t1.fdateCast(year(max(t4.fdate)) as int(10))*100+Cast(month(max(t4.fdate)) as int(5)) ) begin RAISERROR(@message00,18,18) ROLLBACK end --更新领料单上的销售订单号 select @finterid240=finterid from inserted update t2 set t2.fentryselfb0445=t4.fbillno from icstockbill as t1 inner join icstockbillentry as t2 on t1.ftrantype=24 and t1.finterid=t2.finterid inner join icmo as t3 on t3.finterid=t2.ficmointerid left join seorder as t4 on t4.finterid=t3.forderinterid where isnull(t4.fbillno,&#39;no&#39;)&#39;no&#39; and t2.finterid=@finterid240 return end --控制加工生产任务单领料日期不对小于计划开工日期 if (@Ftrantype=28) begin declare @icmo3 varchar(20) declare @message3 varchar(200) set @message3=&#39;错误!加工发出日期不能小于对应加工生产任务单计划开工日期,请与采购人员联系变更!错误单号:&#39; if exists( select t3.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid where t2.fsourceinterid>0 and t1.fdate0 and t1.fdate0 and t1.fdate0 and t1.fdate<t3.FPlanCommitDate set @message4=@message4+@icmo4 RAISERROR(@message4,18,18) ROLLBACK end ; --更新产品入库单上的销售订单号 select @finterid20=finterid from inserted update t2 set t2.fentryselfa0236=t4.fbillno from icstockbill as t1 inner join icstockbillentry as t2 on t1.ftrantype=2 and t1.finterid=t2.finterid inner join icmo as t3 on t3.finterid=t2.ficmointerid left join seorder as t4 on t4.finterid=t3.forderinterid where isnull(t4.fbillno,&#39;no&#39;)&#39;no&#39; and t2.finterid=@finterid20 return end ---更新加工入单销售订单号 if (@Ftrantype=5) begin update t2 set t2.fentryselfa0548=t4.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid inner join seorder as t4 on t4.finterid=t3.forderinterid return end ---控制出全部出货的销售订单所对应的采购订单不能退料(红字购入库单) /*if (@Ftrantype=1) begin declare @message500 varchar(200) set @message500=&#39;已全部出货的销售订单所对应的采购订单不能退料!请检查您所退料的采购订单号码是否正确!&#39; if exists (select t1.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join seorder as t3 on t3.fbillno=t2.fentryselfa0152 where t2.fqty<0 and t3.fclosed=1 ) begin RAISERROR(@message500,18,18) ROLLBACK end return end */
<think>我们已知:用户希望将金蝶K3购入库单的日期信息反写到对应的采购订单中。 分析: 1. 业务逻辑:购入库单通常由采购订单下推生成,两者存在关联关系(通过源单类型和源单编号)。 2. 反写需求:当购入库单审核后,将其日期(如审核日期)更新到对应的采购订单的某个字段(如“实际到货日期”)。 步骤: 步骤1:确定关联关系 - 采购订单表:POOrder(单据类型为采购订单- 购入库单表:ICStockBill(单据类型为购入库单,FBillTypeID=1) - 关联关系:购入库单明细表(ICStockBillEntry)中的FSourceTranType和FSourceBillNo、FSourceEntryID与采购订单明细表(POOrderEntry)关联。 步骤2:确定反写字段 - 采购订单表:POOrder(主表)中可能没有直接用于记录入库日期的字段,通常需要反写到采购订单明细(POOrderEntry)的某个预留字段(如自定义字段)或系统提供的字段(如FReceiveDate)。 - 注意:标准采购订单明细表(POOrderEntry)中可能有记录收货日期的字段(如FReceiveDate),但需确认系统是否已使用。若无,可能需要扩展自定义字段。 步骤3:反写时机 - 建议在购入库单审核时触发反写操作。 步骤4:实现方法 - 方法1:通过金蝶BOS平台开发插件(推荐,但需要开发能力) - 方法2:通过SQL触发器(需谨慎,避免影响系统稳定性) - 方法3:通过后台定时任务(如存储过程)批量处理(适用于非实时要求) 这里我们提供方法2(触发器)的示例(仅作参考,生产环境需测试): 假设: - 购入库单主表:ICStockBill(FBillID, FDate 单据日期, FCheckerID 审核人) - 购入库单明细表:ICStockBillEntry(FEntryID, FBillID, FSourceTranType, FSourceBillNo, FSourceEntryID) - 采购订单明细表:POOrderEntry(FEntryID, FBillNo, FReceiveDate)-- 假设FReceiveDate用于记录收货日期 注意:购入库单审核时(即FCheckerID不为空),将日期反写到采购订单明细的FReceiveDate字段。 步骤5:编写触发器(示例) 由于金蝶K3系统表结构复杂,以下为简化示例,实际应用需根据具体表结构调整: ```sql -- 创建购入库单审核触发器(审核后更新采购订单明细的收货日期) CREATE TRIGGER UpdatePOReceiveDate ON ICStockBill -- 购入库单主表 AFTER UPDATE AS BEGIN -- 如果审核状态发生变化(从未审核到审核) IF UPDATE(FCheckerID) BEGIN DECLARE @BillID INT, @BillDate DATETIME, @CheckerID INT -- 获取被审核的购入库单 SELECT @BillID = inserted.FBillID, @BillDate = inserted.FDate, @CheckerID = inserted.FCheckerID FROM inserted WHERE inserted.FCheckerID IS NOT NULL AND inserted.FBillTypeID = 1 -- 购入库单 AND deleted.FCheckerID IS NULL -- 之前未审核 IF @CheckerID IS NOT NULL BEGIN -- 更新采购订单明细的收货日期 UPDATE POOrderEntry SET FReceiveDate = @BillDate -- 使用购入库单的日期 FROM POOrderEntry INNER JOIN ICStockBillEntry ON ICStockBillEntry.FSourceTranType = 80 -- 源单类型为采购订单(注意:根据实际值调整,通常采购订单为80) AND ICStockBillEntry.FSourceBillNo = POOrderEntry.FBillNo -- 关联采购订单号 AND ICStockBillEntry.FSourceEntryID = POOrderEntry.FEntryID -- 关联采购订单分录ID WHERE ICStockBillEntry.FBillID = @BillID -- 当前审核的购入库单 END END END ``` 注意:上述触发器中的源单类型(FSourceTranType)值80为采购订单,需根据系统实际值确认(常见值:80=采购订单,81=采购入库单)[^1][^2]。 步骤6:替代方案(存储过程批量处理) 如果触发器风险高,可编写存储过程,定时将已审核的购入库单日期反写到采购订单明细: ```sql CREATE PROCEDURE SyncReceiveDate AS BEGIN UPDATE POOrderEntry SET FReceiveDate = ICStockBill.FDate FROM POOrderEntry INNER JOIN ICStockBillEntry ON ICStockBillEntry.FSourceTranType = 80 AND ICStockBillEntry.FSourceBillNo = POOrderEntry.FBillNo AND ICStockBillEntry.FSourceEntryID = POOrderEntry.FEntryID INNER JOIN ICStockBill ON ICStockBill.FBillID = ICStockBillEntry.FBillID WHERE ICStockBill.FBillTypeID = 1 -- 购入库单 AND ICStockBill.FCheckerID IS NOT NULL -- 已审核 AND (POOrderEntry.FReceiveDate IS NULL OR POOrderEntry.FReceiveDate <> ICStockBill.FDate) -- 避免重复更新 END ``` 步骤7:注意事项 - 权限:执行触发器或存储过程需要数据库操作权限。 - 性能:频繁操作可能影响系统性能,建议在非业务高峰期执行。 - 测试:务必在测试环境验证。 - 字段确认:采购订单明细表中是否有合适的字段?若没有,需通过BOS平台扩展自定义字段(如F_MyReceiveDate)。 结论: 实现购入库单日期反写采购订单,关键在于关联关系和反写时机的处理。建议优先考虑金蝶BOS平台开发,其次使用数据库脚本(需谨慎)。 相关问题: 1. 如何确认金蝶K3中采购订单购入库单的关联字段? 2. 金蝶K3中如何扩展采购订单明细表添加自定义字段? 3. 金蝶BOS平台开发审核反写功能的步骤是什么?</think>### 金蝶K3购入库单日期反写到采购订单的实现方案 #### 一、核心原理分析 1. **单据关联关系** - 购入库单(`ICStockBill`)通过`FSourceTranType`和`FSourceBillNo`字段关联采购订单(`POOrder`)[^1] - 关键字段说明: ```sql /* 购入库单表 */ SELECT FBillNo, FDate AS 入库日期, FSourceBillNo AS 源单号 FROM ICStockBill WHERE FBillTypeID = 1 -- 1=购入库单类型标识[^1] /* 采购订单表 */ SELECT FBillNo, FDate AS 订单日期, FReceiveDate AS 收货日期 FROM POOrder WHERE FBillType = &#39;PUR_PO&#39; -- 采购订单类型[^2] ``` 2. **反写逻辑** ```mermaid graph LR A[购入库单审核] --> B{检查源单类型} B -->|FSourceTranType=80| C[定位采购订单] C --> D[更新POOrder.FReceiveDate] D --> E[同步更新POOrderEntry明细] ``` #### 二、实现方法(SQL触发器方案) ```sql CREATE TRIGGER UpdatePOReceiveDate ON ICStockBill -- 购入库单主表 AFTER UPDATE AS BEGIN /* 仅处理审核操作 */ IF UPDATE(FCheckerID) AND EXISTS(SELECT 1 FROM inserted WHERE FCheckerID > 0) BEGIN UPDATE POOrder SET FReceiveDate = inserted.FDate -- 将入库日期写入采购订单收货日期 FROM inserted INNER JOIN ICStockBillEntry e ON inserted.FBillID = e.FBillID WHERE POOrder.FBillNo = e.FSourceBillNo AND e.FSourceTranType = 80 -- 80=采购订单源单类型[^1] AND inserted.FBillTypeID = 1 -- 购入库单 END END ``` #### 三、关键配置说明 1. **采购订单字段扩展** 若标准字段`FReceiveDate`不可用,需通过BOS平台扩展自定义字段: - 在`POOrder`表添加`F_MyReceiveDate`(日期类型) - 在`POOrderEntry`明细表添加关联字段 2. **反写时机控制** ```sql /* 防止重复反写检查 */ AND NOT EXISTS( SELECT 1 FROM POOrder p WHERE p.FBillNo = e.FSourceBillNo AND p.FReceiveDate = inserted.FDate ) ``` #### 四、业务验证方案 1. 执行反写后检查: ```sql /* 验证反写结果 */ SELECT po.FBillNo AS 订单号, po.FDate AS 订单日期, po.FReceiveDate AS 反写收货日期, ic.FBillNo AS 入库单号, ic.FDate AS 实际入库日期 FROM POOrder po INNER JOIN ICStockBillEntry e ON po.FBillNo = e.FSourceBillNo INNER JOIN ICStockBill ic ON e.FBillID = ic.FBillID WHERE ic.FCheckerID > 0 AND e.FSourceTranType = 80 ``` #### 五、注意事项 1. **权限要求** - 触发器执行需`db_owner`权限 - 字段更新需采购订单的"修改"权限[^1] 2. **数据一致性** ```sql /* 检查未关联的入库单 */ SELECT FBillNo FROM ICStockBill WHERE FBillTypeID=1 AND FCheckerID>0 AND NOT EXISTS( SELECT 1 FROM ICStockBillEntry WHERE FSourceTranType=80 AND FBillID=ICStockBill.FBillID ) ``` 3. **性能优化** ```sql CREATE INDEX idx_source ON ICStockBillEntry(FSourceTranType, FSourceBillNo) INCLUDE (FBillID) -- 覆盖查询[^1] ``` > **系统配置参考**: > 采购订单收货日期字段是否启用,需检查系统参数: > ```sql > SELECT * FROM t_SystemProfile > WHERE FCategory=&#39;PO&#39; AND FKey=&#39;EnableReceiveDate&#39; > ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值