Kingdee K3 to extract order data in transit and set triggers (available in transit order delivery Reply)

Extract the in-Transit order data SQL statement as follows:

Use the [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,--order number t1.fcheckdate fwcqi5q,--next single date take order audit date t.fdate f4mfu5l,--po Demand Date T3.fname -Vendor Name T2.fnumber ffmev5u,--Item code t2.fname FQMBUVG,--Item name T.fqty f73uqxy,--order quantity t.fstockqty f9d8ksi,--warehousing quantity T. Fauxcommitqty ft4stbk,--Delivered quantity t.fdetailid fwlkxmi,--inline code t2. Fversion FIMROWM,--Item version t.fsupconfetchdate FBKFLZR,--first back resumption period convert (datetime,t.fentryselfp0270,120) t.fentryselfp0269--convert (datetime,t.fentryselfp0278,120) f1e289l,--Secondary return resumption period T.fsupconmem FN7ER51,--the cause of abnormal delivery CONVERT ( nvarchar (MB), t.fentryselfp0271) t.fentryselfp0270 T2.fmodel f3cundq,--spec. Model--T.FENTRYSELFP0272 F4ac2iw,--Delivery Method--t2.f_119 f9gzpoj,--manufacturer T.fauxpricediscount f7693xc,--actual tax-included unit price t.fcess Fin1adr,--tax rate t.fnote F7qe8kn--po notes--convert (nvarchar), t.fentryselfp0279) fx2dznu--whether to accept the delivery from [AIS20130122132632]. [dbo]. Poorderentry t--Purchase Order time book information inner JOIN [AIS20130122132632]. [dbo]. Poorder T1 on T1. Finterid = t.finterid--Associated Purchase order Master table, inline code, line business not canceled, status is not closed, approver is not empty, line business does not close left JOIN [AIS20130122132632]. [Dbo].t_icitem T2 on T2. Fitemid = t.fitemid--The Item table, introducing the item information left JOIN [AIS20130122132632]. [Dbo].t_supplier T3 on T3. Fitemid = t1. fsupplyid--supplier table, introduce supplier name left JOIN [AIS20130122132632]. [Dbo].t_user T4 on T4. Fuserid = t1. fbillerid--staff table, introducing single name left JOIN [AIS20130122132632]. [Dbo].t_user T5 on T5. Fuserid = t1. fcheckerid--staff table, the introduction of the auditor name 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--line business off identity go

The trigger statement is as follows:

Use the [portal] go/****** object:trigger [dbo].    [TR_TNU80Z1] Script date:01/04/2018 11:19:23 ******/set ANSI_NULLS on Go Set QUOTED_IDENTIFIER in 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,--delivery fentryselfP0269 t1. Fsupconmem=fn7er51,--reason fentryselfP0270 t1.fsupcondate=getdate ()--confirmation date from Inserted T INNER JOIN [AIS20130122132632]. Dbo. Poorderentry T1 on T1. Fdetailid = t.id--where t1. Fsupconfetchdate is null--and t1.fentryselfp0278 is null-this trigger is valid for first delivery and two delivery time, mainly for supplier first delivery reply, after data is written for first delivery, written notes, Write whether accept the delivery value of ' accept ', first confirm time--update T1 set t1.fentryselfp0274=fn7er51,t1.fentryselfp0279= ' not accept ', 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--the above triggers are valid for the first delivery reply and the second failure to reply, and are used primarily for plan counter validation. After data is updated write whether accept the delivery value is ' not accepted ', and write back the note, write the first acknowledgment time--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= ' do not accept '--the above triggers in the first Secondary delivery response and the second not reply, whether to accept the value of the delivery is ' not acceptable ' when the effective, mainly for suppliers two delivery, update the data to write two times, to write notes, write whether accept the delivery value is empty--update T1 set t1.fentryselfp0274= Fbkflzr,t1.fentryselfp0279= ' Accept ' 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= '--  
            The trigger is valid for two times of delivery and if the delivery value is empty, it is mainly used to plan to confirm two changes, write back the notes after updating the data, write whether accept the delivery value of ' Accept ' End--if (@ @error <> 0) --rollback TRANSACTION--else--commit TRANSACTION End Go