-
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 -
3142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
