金蝶K3提取在途订单数据并设置触发（可用于在途订单交期回复）

提取在途订单数据sql语句如下：

USE [portal]
GO

/****** Object:  View [dbo].[TNU80Z1]    Script Date: 01/04/2018 11:17:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER view [dbo].[TNU80Z1]
as
SELECT convert(int,t.FDetailid) as ID,66 DirID,convert(tinyint,'0') SecLevel,(case when t4.fname<>'' then t4.fname else 'admin' end) Builder,t1.fdate LastModified,(case when t5.fname<>'' then t5.fname else 'admin' end) LastMender,
t1.fbillno FBX0TCY,--订单编号
t1.fcheckdate FWCQI5Q,--下单日期取订单审核日期
t.Fdate F4MFU5L,--PO需求日期
t3.fname FY43Y7N,--供应商名称
t2.fnumber FFMEV5U,--物料代码
t2.fname FQMBUVG,--物料名称
t.fqty F73UQXY,--订单数量
t.fstockqty F9D8KSI,--入库数量
t.FAuxCommitQty FT4STBK,--已交货数量
t.fdetailID FWLKXMI,--行内码
t2.FVersion FIMROWM,--物料版本
t.FSupConFetchDate FBKFLZR,--首次回复交期CONVERT(DATETIME,t.fentryselfP0270,120)t.fentryselfP0269
--CONVERT(DATETIME,t.fentryselfP0278,120) F1E289L,--二次回复交期
t.FSupConMem FN7ER51,--交期异常原因convert(nvarchar(100),t.fentryselfP0271)t.fentryselfP0270
t2.fmodel F3CUNDQ,--规格型号
--t.fentryselfP0272 F4AC2IW,--交付方式
--t2.f_119 F9GZPOJ,--制造商
t.fauxpricediscount F7693XC,--实际含税单价
t.fcess FIN1ADR,--税率
t.fnote F7QE8KN--PO备注
--convert(nvarchar(100),t.fentryselfP0279) FX2DZNU--是否接受交期
FROM [AIS20130122132632].[dbo].Poorderentry t--采购订单序时簿信息
inner JOIN [AIS20130122132632].[dbo].POOrder t1 ON t1.FInterID = t.FInterID--关联采购订单主表，行内码一致，行业务未取消、状态为非关闭、审核人不为空、行业务未关闭
LEFT JOIN [AIS20130122132632].[dbo].t_icitem t2 ON t2.FItemID = t.FitemID--物料表，引入物料信息
LEFT JOIN [AIS20130122132632].[dbo].t_supplier t3 ON t3.Fitemid = t1.FsupplyID--供应商表，引入供应商姓名
LEFT JOIN [AIS20130122132632].[dbo].t_user t4 ON t4.Fuserid = t1.FbillerID--职员表，引入制单人姓名
LEFT JOIN [AIS20130122132632].[dbo].t_user t5 ON t5.Fuserid = t1.FcheckerID--职员表，引入审核员姓名
INNER JOIN [AIS20130122132632].[dbo].t_BaseProperty  t6 ON t6.FItemID= t.Fitemid
where  t.FMrpClosed=0 and t1.fcancellation =0 and t1.fstatus >0 and t1.FCheckerID is not null and t1.fclosed=0--行业务关闭标识



GO

触发语句如下：

USE [portal]
GO

/****** Object:  Trigger [dbo].[TR_TNU80Z1]    Script Date: 01/04/2018 11:19:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER TRIGGER [dbo].[TR_TNU80Z1] ON [dbo].[TNU80Z1]
        INSTEAD OF UPDATE  
        AS
BEGIN  
           --BEGIN TRANSACTION  
           SET NOCOUNT ON  
            BEGIN   
--   INSERT INTO [10.32.4.1].[AIS20100809145719].dbo.POOrderEntry
--(T1.fentryselfP0273,T1.fentryselfP0274) 
--SELECT FA14855d5162616,F12I44851h62855 FROM



update t1 set 
t1.FSupConFetchDate=t.FBKFLZR,--交期fentryselfP0269
t1.FSupConMem=FN7ER51,--原因fentryselfP0270
t1.fsupcondate=getdate() --确认日期
from Inserted T
INNER JOIN [AIS20130122132632].dbo.POOrderEntry t1 ON t1.FdetailID = t.id --where t1.FSupConFetchDate is null --and t1.fentryselfP0278 is null
--上述触发器在首次交期及二次交期为空时有效，主要用于供应商首次交期回复，更新数据后写入首次交期、写入备注、写入是否接受交期值为‘接受’、首次确认时间
--update t1 set t1.fentryselfP0274=FN7ER51,t1.fentryselfP0279='不接受',t1.fentryselfP0281=getdate() from Inserted T
--INNER JOIN [10.32.4.1].[AIS20100809145719].dbo.POOrderEntry t1 ON t1.FdetailID = t.id where t1.fentryselfP0273<>'' and t1.fentryselfP0278 is null
--上述触发器在首次交期回复且第二次未回复时有效，主要用于计划反确认，更新数据后写入是否接受交期值为‘不接受’、且重新写入备注、写入首次确认时间
--update t1 set t1.fentryselfP0274=FN7ER51,t1.fentryselfP0278=F1E289L,t1.fentryselfP0279='' from Inserted T
--INNER JOIN [10.32.4.1].[AIS20100809145719].dbo.POOrderEntry t1 ON t1.FdetailID = t.id where t1.fentryselfP0273<>'' and t1.fentryselfP0278 is null and t1.fentryselfP0279='不接受'
--上述触发器在首次交期回复且第二次未回复、是否接受交期值为‘不接受’时有效，主要用于供应商二次交期回复，更新数据后写入二次交期、重新写入备注、写入是否接受交期值为空
--update t1 set t1.fentryselfP0274=FBKFLZR,t1.fentryselfP0279='接受' from Inserted T
--INNER JOIN [10.32.4.1].[AIS20100809145719].dbo.POOrderEntry t1 ON t1.FdetailID = t.id where t1.fentryselfP0273<>'' and t1.fentryselfP0278<>'' and t1.fentryselfP0279=''
--上述触发器在两次交期均已回复、是否接受交期值为空时有效，主要用于计划确认二次变更，更新数据后重新写入备注、写入是否接受交期值为‘接受’
 
      END
        --IF (@@error <> 0)  
            --ROLLBACK TRANSACTION  
        --ELSE  
            --COMMIT TRANSACTION  
        END


GO