Getting a trigger to work
I have a database table with several triggers, one of which is an update trigger, have attached as a code snippet
I tried to add an update trigger, namely
ALTER TRIGGER [dbo].[R5EVENTS_TRIGGER2]
ON [dbo].[R5EVENTS]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE R5OBJECTS
SET R5OBJECTS.OBJ_UDFCHAR07 = R5EVENTS.EVT_REQM, R5OBJECTS.OBJ_UDFCHAR09 = R5EVENTS.EVT_COMPLETED
FROM R5EVENTS, R5OBJECTS
WHERE R5EVENTS.EVT_OBJECT = R5OBJECTS.OBJ_CODE AND R5EVENTS.EVT_STATUS = 'C'
END
BEGIN
UPDATE R5EVENTS
SET R5EVENTS.EVT_UDFCHAR01 = R5EVENTS.EVT_UPDATEDBY
FROM R5EVENTS
WHERE R5EVENTS.EVT_CODE = R5EVENTS.EVT_CODE
END
but i have 2 problems
1. The second begin section above seems to conflict with the existing trigger (attached as the code snippet)
2. The update on the R5OBJECTS table (first begin section) is not picking up the last entry (i think it only shows the first entry) it needs to look at the latest R5EVENTS.EVT_COMPLETED date
Any help, please
Gordon
I tried to add an update trigger, namely
ALTER TRIGGER [dbo].[R5EVENTS_TRIGGER2]
ON [dbo].[R5EVENTS]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE R5OBJECTS
SET R5OBJECTS.OBJ_UDFCHAR07 = R5EVENTS.EVT_REQM, R5OBJECTS.OBJ_UDFCHAR09 = R5EVENTS.EVT_COMPLETED
FROM R5EVENTS, R5OBJECTS
WHERE R5EVENTS.EVT_OBJECT = R5OBJECTS.OBJ_CODE AND R5EVENTS.EVT_STATUS = 'C'
END
BEGIN
UPDATE R5EVENTS
SET R5EVENTS.EVT_UDFCHAR01 = R5EVENTS.EVT_UPDATEDBY
FROM R5EVENTS
WHERE R5EVENTS.EVT_CODE = R5EVENTS.EVT_CODE
END
but i have 2 problems
1. The second begin section above seems to conflict with the existing trigger (attached as the code snippet)
2. The update on the R5OBJECTS table (first begin section) is not picking up the last entry (i think it only shows the first entry) it needs to look at the latest R5EVENTS.EVT_COMPLETED date
Any help, please
Gordon
USE [EAMSBELIVE]
GO
/****** Object: Trigger [dbo].[POSUPD_EVT] Script Date: 10/17/2010 16:25:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[POSUPD_EVT]
on [dbo].[R5EVENTS]
for update
NOT FOR REPLICATION
as
declare @sUser nvarchar(38)
declare @sCode nvarchar(30)
declare @sDesc nvarchar(80)
declare @sType nvarchar(4)
declare @sRtype nvarchar(4)
declare @dDate datetime
declare @sStatus nvarchar(4)
declare @sRstatus nvarchar(4)
declare @sMrc nvarchar(15)
declare @sLtype nvarchar(4)
declare @sLocation nvarchar(30)
declare @sCostcode nvarchar(30)
declare @sPrinted nvarchar(1)
declare @sProject nvarchar(20)
declare @sProjbud nvarchar(20)
declare @sObtype nvarchar(4)
declare @sObrtype nvarchar(4)
declare @sObject nvarchar(30)
declare @sStandwork nvarchar(20)
declare @sPpm nvarchar(20)
declare @nFreq numeric(8)
declare @sIsstype nvarchar(1)
declare @sMeter nvarchar(20)
declare @nMeterdue numeric(24,6)
declare @sClass nvarchar(8)
declare @sRoute nvarchar(20)
declare @sSession nvarchar(8)
declare @nSchedno numeric(5)
declare @sPlanprio nvarchar(8)
declare @sFixed nvarchar(1)
declare @nSlack numeric(5)
declare @dTarget datetime
declare @dNewtarget datetime
declare @dEarlystart datetime
declare @dLateend datetime
declare @dMustend datetime
declare @dLastplan datetime
declare @nDuration numeric(8)
declare @nNewdur numeric(8)
declare @sPlanstatus nvarchar(1)
declare @sParent nvarchar(30)
declare @sDepend nvarchar(1)
declare @sOrigin nvarchar(15)
declare @sReqm nvarchar(8)
declare @sCause nvarchar(8)
declare @sAction nvarchar(8)
declare @sJobtype nvarchar(8)
declare @sPriority nvarchar(8)
declare @dReported datetime
declare @dStart datetime
declare @dMatavail datetime
declare @dDue datetime
declare @dCompleted datetime
declare @nDowntime numeric(24,6)
declare @nLabtotal numeric(24,6)
declare @nMattotal numeric(24,6)
declare @sShift nvarchar(4)
declare @nFailureusage numeric(24,6)
declare @nMeterreading numeric(24,6)
declare @sMetuom nvarchar(30)
declare @nMaxcost numeric(24,6)
declare @sScreener nvarchar(30)
declare @sRoutedfrom nvarchar(30)
declare @sRoutereason nvarchar(240)
declare @sWarranty nvarchar(1)
declare @dOkwinend datetime
declare @dNearwinstart datetime
declare @dGenwinstart datetime
declare @nOkwinendval numeric(24,6)
declare @nNearwinbegval numeric(24,6)
declare @nGenwinbegval numeric(24,6)
declare @sRoutestatus nvarchar(4)
declare @sRouterstatus nvarchar(4)
declare @nAcd integer
declare @sRejectreason nvarchar(240)
declare @sEnteredby nvarchar(30)
declare @sFailure nvarchar(8)
declare @nDowntimehrs numeric(24,6)
declare @sSafety nvarchar(1)
declare @sObjcriticality nvarchar(8)
declare @sMpproj nvarchar(20)
declare @nTransorgid integer
declare @sTranscode nvarchar(4)
declare @nTransgroup integer
declare @sCn nvarchar(24)
declare @sReopened nvarchar(1)
declare @nPpopk integer
declare @nPpmrev numeric(4)
declare @nRouterev numeric(4)
declare @sOrg nvarchar(15)
declare @sClass_org nvarchar(15)
declare @sObject_org nvarchar(15)
declare @sLocation_org nvarchar(15)
declare @sSchedgrp nvarchar(30)
declare @dRequeststart datetime
declare @dSchedend datetime
declare @dRequestend datetime
declare @sMastercal nvarchar(1)
declare @nStep numeric(3)
declare @nSeq numeric(3)
declare @sTrigevent nvarchar(30)
declare @sOldrstatus nvarchar(4)
declare @sOldstatus nvarchar(4)
declare @sNewrstatus nvarchar(4)
declare @sNewstatus nvarchar(4)
declare @sConflict nvarchar(1)
declare @sConflictresolved nvarchar(1)
declare @sConflictdesc nvarchar(255)
declare @sPackage nvarchar(24)
declare @sCode_alias nvarchar(45)
declare @dCreated datetime
declare @sCreatedby nvarchar(30)
declare @dUpdated datetime
declare @sUpdatedby nvarchar(30)
declare @sContname nvarchar(50)
declare @sContphone nvarchar(50)
declare @sContemail nvarchar(255)
declare @sContnotes nvarchar(255)
declare @sFollowup nvarchar(1)
declare @nUpdatecount numeric(38)
declare @sServicerequest nvarchar(30)
declare @sPerson nvarchar(15)
declare @sPrint nvarchar(1)
declare @sBillable nvarchar(1)
declare @sPerioduom nvarchar(10)
declare @nMeterdue2 numeric(24,6)
declare @sMetuom2 nvarchar(30)
declare @sRouteparent nvarchar(30)
declare @nIdentity int
declare @sOldst nvarchar(4)
declare @sOldrt nvarchar(20)
declare @nINCREMENT numeric(24,6)
declare @nPRECISION numeric(2)
declare @sPIDNO nvarchar(80)
declare @sPIDDRAWING nvarchar(80)
declare @nSERVICEPERC numeric(24,6)
declare @sSOP nvarchar(30)
declare @sSRQCALLNAME nvarchar(80)
declare @sSRQCUSTOMER nvarchar(30)
declare @sSRQLEVEL1 nvarchar(30)
declare @sOldCode nvarchar(30)
declare @sOldDesc nvarchar(80)
declare @sOldType nvarchar(4)
declare @sOldRtype nvarchar(4)
declare @dOldDate datetime
declare @sOld_Status nvarchar(4)
declare @sOld_Rstatus nvarchar(4)
declare @sOldMrc nvarchar(15)
declare @sOldLtype nvarchar(4)
declare @sOldLocation nvarchar(30)
declare @sOldCostcode nvarchar(30)
declare @sOldPrinted nvarchar(1)
declare @sOldProject nvarchar(20)
declare @sOldProjbud nvarchar(20)
declare @sOldObtype nvarchar(4)
declare @sOldObrtype nvarchar(4)
declare @sOldObject nvarchar(30)
declare @sOldStandwork nvarchar(20)
declare @sOldPpm nvarchar(20)
declare @nOldFreq numeric(8)
declare @sOldIsstype nvarchar(1)
declare @sOldMeter nvarchar(20)
declare @nOldMeterdue numeric(24,6)
declare @sOldClass nvarchar(8)
declare @sOldRoute nvarchar(20)
declare @sOldSession nvarchar(8)
declare @nOldSchedno numeric(5)
declare @sOldPlanprio nvarchar(8)
declare @sOldFixed nvarchar(1)
declare @nOldSlack numeric(5)
declare @dOldTarget datetime
declare @dOldNewtarget datetime
declare @dOldEarlystart datetime
declare @dOldLateend datetime
declare @dOldMustend datetime
declare @dOldLastplan datetime
declare @nOldDuration numeric(8)
declare @nOldNewdur numeric(8)
declare @sOldPlanstatus nvarchar(1)
declare @sOldParent nvarchar(30)
declare @sOldDepend nvarchar(1)
declare @sOldOrigin nvarchar(15)
declare @sOldReqm nvarchar(8)
declare @sOldCause nvarchar(8)
declare @sOldAction nvarchar(8)
declare @sOldJobtype nvarchar(8)
declare @sOldPriority nvarchar(8)
declare @dOldReported datetime
declare @dOldStart datetime
declare @dOldMatavail datetime
declare @dOldDue datetime
declare @dOldCompleted datetime
declare @nOldDowntime numeric(24,6)
declare @nOldLabtotal numeric(24,6)
declare @nOldMattotal numeric(24,6)
declare @sOldShift nvarchar(4)
declare @nOldFailureusage numeric(24,6)
declare @nOldMeterreading numeric(24,6)
declare @sOldMetuom nvarchar(30)
declare @nOldMaxcost numeric(24,6)
declare @sOldScreener nvarchar(30)
declare @sOldRoutedfrom nvarchar(30)
declare @sOldRoutereason nvarchar(240)
declare @sOldWarranty nvarchar(1)
declare @dOldOkwinend datetime
declare @dOldNearwinstart datetime
declare @dOldGenwinstart datetime
declare @nOldOkwinendval numeric(24,6)
declare @nOldNearwinbegval numeric(24,6)
declare @nOldGenwinbegval numeric(24,6)
declare @sOldRoutestatus nvarchar(4)
declare @sOldRouterstatus nvarchar(4)
declare @nOldAcd integer
declare @sOldRejectreason nvarchar(240)
declare @sOldEnteredby nvarchar(30)
declare @sOldFailure nvarchar(8)
declare @nOldDowntimehrs numeric(24,6)
declare @sOldSafety nvarchar(1)
declare @sOldObjcriticality nvarchar(8)
declare @sOldMpproj nvarchar(20)
declare @nOldTransorgid integer
declare @sOldTranscode nvarchar(4)
declare @nOldTransgroup integer
declare @sOldCn nvarchar(24)
declare @sOldReopened nvarchar(1)
declare @nOldPpopk integer
declare @nOldPpmrev numeric(4)
declare @nOldRouterev numeric(4)
declare @sOldOrg nvarchar(15)
declare @sOldClass_org nvarchar(15)
declare @sOldObject_org nvarchar(15)
declare @sOldLocation_org nvarchar(15)
declare @sOldSchedgrp nvarchar(30)
declare @dOldRequeststart datetime
declare @dOldSchedend datetime
declare @dOldRequestend datetime
declare @sOldMastercal nvarchar(1)
declare @nOldStep numeric(3)
declare @nOldSeq numeric(3)
declare @sOldTrigevent nvarchar(30)
declare @sOldOldrstatus nvarchar(4)
declare @sOldOldstatus nvarchar(4)
declare @sOldNewrstatus nvarchar(4)
declare @sOldNewstatus nvarchar(4)
declare @sOldConflict nvarchar(1)
declare @sOldConflictresolved nvarchar(1)
declare @sOldConflictdesc nvarchar(255)
declare @sOldPackage nvarchar(24)
declare @sOldCode_alias nvarchar(45)
declare @dOldCreated datetime
declare @sOldCreatedby nvarchar(30)
declare @dOldUpdated datetime
declare @sOldUpdatedby nvarchar(30)
declare @sOldContname nvarchar(50)
declare @sOldContphone nvarchar(50)
declare @sOldContemail nvarchar(255)
declare @sOldContnotes nvarchar(255)
declare @sOldFollowup nvarchar(1)
declare @nOldUpdatecount numeric(38)
declare @sOldServicerequest nvarchar(30)
declare @sOldPerson nvarchar(15)
declare @sOldPrint nvarchar(1)
declare @sOldBillable nvarchar(1)
declare @sOldPerioduom nvarchar(10)
declare @nOldMeterdue2 numeric(24,6)
declare @sOldMetuom2 nvarchar(30)
declare @sOldRouteparent nvarchar(30)
declare @sActevent nvarchar(30)
declare @nActact numeric(8)
declare @sActsupplier nvarchar(30)
declare @dActstart datetime
declare @nActtime numeric(5,2)
declare @dActnewstart datetime
declare @sActhire nvarchar(1)
declare @sActordered nvarchar(1)
declare @sActfixh nvarchar(1)
declare @sActminhours nvarchar(1)
declare @sActmrc nvarchar(15)
declare @sActtrade nvarchar(15)
declare @sActshift nvarchar(4)
declare @nActpersons numeric(8)
declare @sActproject nvarchar(20)
declare @sActprojbud nvarchar(20)
declare @sActroute nvarchar(20)
declare @nActduration numeric(8)
declare @nActnewdur numeric(8)
declare @nActmaxdur numeric(8)
declare @nActest numeric(24,6)
declare @nActrem numeric(24,6)
declare @nActnt numeric(24,6)
declare @nActntrate numeric(24,6)
declare @nActot numeric(24,6)
declare @nActotrate numeric(24,6)
declare @nActpurrate numeric(24,6)
declare @sActtask nvarchar(20)
declare @sActmatlist nvarchar(20)
declare @sActspecial nvarchar(1)
declare @sActgraph nvarchar(80)
declare @nActlevel numeric(4)
declare @sActorder nvarchar(30)
declare @nActordline numeric(8)
declare @sActordtype nvarchar(4)
declare @sActordrtype nvarchar(4)
declare @nActschedhrs numeric(24,6)
declare @dActlatestsched datetime
declare @sActcompleted nvarchar(1)
declare @nActmatlrev numeric(4)
declare @nActtaskrev numeric(4)
declare @nActqty numeric(10,2)
declare @sActuom nvarchar(30)
declare @sActreq nvarchar(30)
declare @nActreqline numeric(8)
declare @sActsupplier_org nvarchar(15)
declare @sActorder_org nvarchar(15)
declare @sActrpc nvarchar(4)
declare @sActwap nvarchar(4)
declare @sActtpf nvarchar(4)
declare @sActmanufact nvarchar(24)
declare @sActsyslevel nvarchar(30)
declare @sActasmlevel nvarchar(30)
declare @sActcomplevel nvarchar(30)
declare @sActclass nvarchar(8)
declare @sActclass_org nvarchar(15)
declare @nActpercomplete numeric(3)
declare @nActupdatecount numeric(38)
declare @sActSourcecode nvarchar(180)
declare @sActSourcesys nvarchar(180)
declare @sActPerresp nvarchar(60)
declare @sActWarranty nvarchar(1)
declare @sActrelatedwo nvarchar(30)
declare @sActnote nvarchar(80)
declare @sActdefermaintenance nvarchar(1)
declare @sActdeferredirectmats nvarchar(1)
declare @sActdeferredorigwo nvarchar(30)
declare @sActdeferredorigact numeric(8)
declare @sActudfchar01 nvarchar(80)
declare @sActudfchar02 nvarchar(80)
declare @sActudfchar03 nvarchar(80)
declare @sActudfchar04 nvarchar(80)
declare @sActudfchar05 nvarchar(80)
declare @sActudfchar06 nvarchar(80)
declare @sActudfchar07 nvarchar(80)
declare @sActudfchar08 nvarchar(80)
declare @sActudfchar09 nvarchar(80)
declare @sActudfchar10 nvarchar(80)
declare @sActudfchar11 nvarchar(80)
declare @sActudfchar12 nvarchar(80)
declare @sActudfchar13 nvarchar(80)
declare @sActudfchar14 nvarchar(80)
declare @sActudfchar15 nvarchar(80)
declare @sActudfchar16 nvarchar(80)
declare @sActudfchar17 nvarchar(80)
declare @sActudfchar18 nvarchar(80)
declare @sActudfchar19 nvarchar(80)
declare @sActudfchar20 nvarchar(80)
declare @sActudfchar21 nvarchar(80)
declare @sActudfchar22 nvarchar(80)
declare @sActudfchar23 nvarchar(80)
declare @sActudfchar24 nvarchar(80)
declare @sActudfchar25 nvarchar(80)
declare @sActudfchar26 nvarchar(80)
declare @sActudfchar27 nvarchar(80)
declare @sActudfchar28 nvarchar(80)
declare @sActudfchar29 nvarchar(80)
declare @sActudfchar30 nvarchar(80)
declare @nActudfnum01 numeric(24,6)
declare @nActudfnum02 numeric(24,6)
declare @nActudfnum03 numeric(24,6)
declare @nActudfnum04 numeric(24,6)
declare @nActudfnum05 numeric(24,6)
declare @dActudfdate01 datetime
declare @dActudfdate02 datetime
declare @dActudfdate03 datetime
declare @dActudfdate04 datetime
declare @dActudfdate05 datetime
declare @sActudfchkbox01 nvarchar(1)
declare @sActudfchkbox02 nvarchar(1)
declare @sActudfchkbox03 nvarchar(1)
declare @sActudfchkbox04 nvarchar(1)
declare @sActudfchkbox05 nvarchar(1)
declare @sEpoevent nvarchar(30)
declare @nEpoline numeric(8)
declare @sEpoobject nvarchar(30)
declare @sEpoobrtype nvarchar(4)
declare @sEpoobtype nvarchar(4)
declare @sEpopoint nvarchar(3)
declare @sEpopointtype nvarchar(4)
declare @sEpoaspect nvarchar(4)
declare @nEpoavgslope numeric(24,6)
declare @sEpocomment nvarchar(70)
declare @sEpocreate nvarchar(1)
declare @dEpodate datetime
declare @sEpofinding nvarchar(4)
declare @sEpojob nvarchar(30)
declare @nEpolastslope numeric(24,6)
declare @nEpomax numeric(24,6)
declare @dEpomaxdate datetime
declare @sEpomaxppm nvarchar(20)
declare @sEpomaxstwo nvarchar(20)
declare @nEpomaxtol numeric(4,1)
declare @dEpomaxtoldate datetime
declare @sEpomethod nvarchar(8)
declare @nEpomin numeric(24,6)
declare @dEpomindate datetime
declare @sEpominppm nvarchar(20)
declare @sEpominstwo nvarchar(20)
declare @nEpomintol numeric(4,1)
declare @dEpomintoldate datetime
declare @sEporesult nvarchar(4)
declare @sEporresult nvarchar(4)
declare @sEpostwo nvarchar(20)
declare @sEpotype nvarchar(4)
declare @sEpouom nvarchar(30)
declare @nEpovalue numeric(24,6)
declare @sEpoobject_org nvarchar(15)
declare @sEpoinspector nvarchar(15)
declare @sEpolocation nvarchar(70)
declare @sEpoclass nvarchar(8)
declare @sEpoclass_org nvarchar(15)
declare @sEpocode nvarchar(30)
declare @sEpoorigevent nvarchar(30)
declare @dEponidatemincrit datetime
declare @dEponidatemintol datetime
declare @dEponidatemaxcrit datetime
declare @dEponidatemaxtol datetime
declare @nEpoassessedslope numeric(24,6)
declare @sEpovarchar1 nvarchar(45)
declare @sEpovarchar2 nvarchar(45)
declare @sEpovarchar3 nvarchar(45)
declare @sEpovarchar4 nvarchar(45)
declare @sEpovarchar5 nvarchar(45)
declare @sEpovarchar6 nvarchar(45)
declare @sEpovarchar7 nvarchar(45)
declare @sEpovarchar8 nvarchar(45)
declare @sEpovarchar9 nvarchar(45)
declare @sEpovarchar10 nvarchar(45)
declare @nEpovarnum1 float
declare @nEpovarnum2 float
declare @nEpovarnum3 float
declare @nEpovarnum4 float
declare @nEpovarnum5 float
declare @nEpovarnum6 float
declare @nEpovarnum7 float
declare @nEpovarnum8 float
declare @nEpovarnum9 float
declare @nEpovarnum10 float
declare @dEpovardate1 datetime
declare @dEpovardate2 datetime
declare @dEpovardate3 datetime
declare @dEpovardate4 datetime
declare @dEpovardate5 datetime
declare @sEpoconfrating nvarchar(4)
declare @sEpoparent nvarchar(30)
declare @sEpocompleted nvarchar(1)
declare @sEpoprocessed nvarchar(1)
declare @sEporisk nvarchar(8)
declare @nEpoupdatecount numeric(38)
declare @dEpoCreated datetime
declare @dEpoUpdated datetime
declare @sEpovarchar11 nvarchar(45)
declare @sEpovarchar12 nvarchar(45)
declare @sEpovarchar13 nvarchar(45)
declare @sEpovarchar14 nvarchar(45)
declare @sEpovarchar15 nvarchar(45)
declare @sEpovarchar16 nvarchar(45)
declare @sEpovarchar17 nvarchar(45)
declare @sEpovarchar18 nvarchar(45)
declare @sEpovarchar19 nvarchar(45)
declare @sEpovarchar20 nvarchar(45)
declare @sEpovarchar21 nvarchar(45)
declare @sEpovarchar22 nvarchar(45)
declare @sEpovarchar23 nvarchar(45)
declare @sEpovarchar24 nvarchar(45)
declare @sEpovarchar25 nvarchar(45)
declare @sEpovarchar26 nvarchar(45)
declare @sEpovarchar27 nvarchar(45)
declare @sEpovarchar28 nvarchar(45)
declare @sEpovarchar29 nvarchar(45)
declare @sEpovarchar30 nvarchar(45)
declare @sEpovarchar31 nvarchar(45)
declare @sEpovarchar32 nvarchar(45)
declare @sEpovarchar33 nvarchar(45)
declare @sEpovarchar34 nvarchar(45)
declare @sEpovarchar35 nvarchar(45)
declare @nEpovarnum11 float
declare @nEpovarnum12 float
declare @nEpovarnum13 float
declare @nEpovarnum14 float
declare @nEpovarnum15 float
declare @nEpovarnum16 float
declare @nEpovarnum17 float
declare @nEpovarnum18 float
declare @nEpovarnum19 float
declare @nEpovarnum20 float
declare @nEpovarnum21 float
declare @nEpovarnum22 float
declare @nEpovarnum23 float
declare @nEpovarnum24 float
declare @nEpovarnum25 float
declare @nEpovarnum26 float
declare @nEpovarnum27 float
declare @nEpovarnum28 float
declare @nEpovarnum29 float
declare @nEpovarnum30 float
declare @nEpovarnum31 float
declare @nEpovarnum32 float
declare @nEpovarnum33 float
declare @nEpovarnum34 float
declare @nEpovarnum35 float
declare @dEpovardate6 datetime
declare @dEpovardate7 datetime
declare @dEpovardate8 datetime
declare @dEpovardate9 datetime
declare @dEpovardate10 datetime
declare @dEpovardate11 datetime
declare @dEpovardate12 datetime
declare @dEpovardate13 datetime
declare @dEpovardate14 datetime
declare @dEpovardate15 datetime
declare @sFlag nvarchar(2)
declare @sFlagTemp nvarchar(10)
declare @sSyslevel nvarchar(30)
declare @sSourcecode nvarchar(45)
declare @sSourcesystem nvarchar(45)
declare @dInterface datetime
declare @dOldInterface datetime
declare @dLastcal datetime
declare @dSopeffective datetime
declare @sCalstatus nvarchar(2)
declare @sCheckresult nvarchar(4)
declare @dEvtDue datetime
declare @dEvtTarget datetime
declare @sNewcode nvarchar(30) --r5events.evt_code%TYPE
declare @sX nvarchar(1)
declare @sChk nvarchar(4)
declare @sDeforg nvarchar(200) --r5install.ins_desc%TYPE
declare @sSeqno nvarchar(30)
declare @sSeqno1 nvarchar(30)
declare @sSeqno2 nvarchar(30)
declare @sChk1 nvarchar(6)
declare @nReturn int
declare @sTemp nvarchar(200)
declare @sConcat nvarchar(200)
declare @sConcat2 nvarchar(200)
declare @nErecord int
declare @nErecord2 int
declare @sGetUser nvarchar(30)
declare @dGtTime datetime
declare @sGetStype nvarchar(8)
declare @sCertnum nvarchar(20) --r5personnelqualifications.pqu_certification%TYPE
declare @sCerttype nvarchar(8) --r5personnelqualifications.pqu_certificationtype%TYPE
declare @nCount int
declare @sObjtemp nvarchar(64)
declare @sPpmtemp nvarchar(64)
declare @sTempAction nvarchar(15)
declare @sMsg nvarchar(240)
declare @sTenant nvarchar(50)
declare @sTMP_STOREVAL1 nvarchar(24)
declare @tblTrigTemp TABLE (IDENTVAL nvarchar(24))
declare @sObjcgmp nvarchar(1) --r5objects.obj_cgmp%TYPE := '+';
declare @sFirstbill nvarchar(30)
declare @sJecategory nvarchar(25)
declare @sJesource nvarchar(25)
declare @sGltransferflag nvarchar(1)
declare @dGltransfer datetime
declare @sEvent_etp nvarchar(30)
declare @sObject_etp nvarchar(30)
declare @sObject_org_etp nvarchar(15)
declare @nSeq_etp numeric(4,0)
declare @nTestpoint_etp numeric(24,6)
declare @sTestpointuom_etp nvarchar(30)
declare @nDevicetolfrom_etp numeric(24,6)
declare @nDevicetolto_etp numeric(24,6)
declare @nStandard_etp numeric(24,6)
declare @sStandarduom_etp nvarchar(30)
declare @nOutput_etp numeric(24,6)
declare @sOutputuom_etp nvarchar(30)
declare @nDevicereading_etp numeric(24,6)
declare @nDeviation_etp numeric(24,6)
declare @sStatus_etp nvarchar(4)
declare @nDevicetolfromal_etp numeric(24,6)
declare @nDevicetoltoal_etp numeric(24,6)
declare @nStandardal_etp numeric(24,6)
declare @sStandarduomal_etp nvarchar(30)
declare @nOutputal_etp numeric(24,6)
declare @sOutputuomal_etp nvarchar(30)
declare @nDevicereadingal_etp numeric(24,6)
declare @nDeviational_etp numeric(24,6)
declare @sStatusal_etp nvarchar(4)
declare @sComments_etp nvarchar(2000)
declare @sNotused_etp nvarchar(1)
declare @nUpdatecount_etp numeric(38,0)
declare @dUpdated_etp datetime
declare @nSqlidentity_etp integer
declare @nPk_est integer
declare @sEvent_est nvarchar(30)
declare @sPart_est nvarchar(30)
declare @sPart_org_est nvarchar(15)
declare @sObject_est nvarchar(30)
declare @sObject_org_est nvarchar(15)
declare @sLot_est nvarchar(12)
declare @dNextcaldate_est datetime
declare @nUpdatecount_est numeric(38,0)
declare @dUpdated_est datetime
declare @nSqlidentity_est integer
declare @sMultiequip nvarchar(1)
declare @sAgreement nvarchar(30)
declare @sSigpb nvarchar(30)
declare @sSigrb nvarchar(30)
declare @dLasttimepb datetime
declare @dLasttimerb datetime
declare @dOldLasttimepb datetime
declare @dOldLasttimerb datetime
declare @sOpenstatus nvarchar(8)
declare @sFollowstatus nvarchar(8)
declare @sContactcenterrstatus nvarchar(8)
declare @sNewcontactcenterstatus nvarchar(8)
declare @sContactrecordcode nvarchar(30)
declare @sContactrecordorg nvarchar(15)
declare @sTempMsgid nvarchar(256)
declare @dCompletedtrunc datetime
declare @sPerformedby nvarchar(30)
declare @sReviewedby nvarchar(30)
declare @nMeterinterval numeric(24,6)
declare @nMeterinterval2 numeric(24,6)
declare @sWorkaddress nvarchar(256)
declare @dPfpromisedate datetime
declare @dTfpromisedate datetime
declare @dTfdatecompleted datetime
declare @sEquipmentusability nvarchar(30)
declare @sEquipmentusability_org nvarchar(15)
declare @sProvider nvarchar(30)
declare @sProvider_org nvarchar(15)
declare @sServiceproblem nvarchar(30)
declare @sServiceproblem_org nvarchar(15)
declare @sServicecategory nvarchar(30)
declare @sServicecategory_org nvarchar(15)
declare @sUdfchar01 nvarchar(80)
declare @sUdfchar02 nvarchar(80)
declare @sUdfchar03 nvarchar(80)
declare @sUdfchar04 nvarchar(80)
declare @sUdfchar05 nvarchar(80)
declare @sUdfchar06 nvarchar(80)
declare @sUdfchar07 nvarchar(80)
declare @sUdfchar08 nvarchar(80)
declare @sUdfchar09 nvarchar(80)
declare @sUdfchar10 nvarchar(80)
declare @sUdfchar11 nvarchar(80)
declare @sUdfchar12 nvarchar(80)
declare @sUdfchar13 nvarchar(80)
declare @sUdfchar14 nvarchar(80)
declare @sUdfchar15 nvarchar(80)
declare @sUdfchar16 nvarchar(80)
declare @sUdfchar17 nvarchar(80)
declare @sUdfchar18 nvarchar(80)
declare @sUdfchar19 nvarchar(80)
declare @sUdfchar20 nvarchar(80)
declare @sUdfchar21 nvarchar(80)
declare @sUdfchar22 nvarchar(80)
declare @sUdfchar23 nvarchar(80)
declare @sUdfchar24 nvarchar(80)
declare @sUdfchar25 nvarchar(80)
declare @sUdfchar26 nvarchar(80)
declare @sUdfchar27 nvarchar(80)
declare @sUdfchar28 nvarchar(80)
declare @sUdfchar29 nvarchar(80)
declare @sUdfchar30 nvarchar(80)
declare @nUdfnum01 numeric(24,6)
declare @nUdfnum02 numeric(24,6)
declare @nUdfnum03 numeric(24,6)
declare @nUdfnum04 numeric(24,6)
declare @nUdfnum05 numeric(24,6)
declare @dUdfdate01 datetime
declare @dUdfdate02 datetime
declare @dUdfdate03 datetime
declare @dUdfdate04 datetime
declare @dUdfdate05 datetime
declare @sUdfchkbox01 nvarchar(1)
declare @sUdfchkbox02 nvarchar(1)
declare @sUdfchkbox03 nvarchar(1)
declare @sUdfchkbox04 nvarchar(1)
declare @sUdfchkbox05 nvarchar(1)
declare @nFrompoint numeric(24,6)
declare @sFromrefdesc nvarchar(80)
declare @sFromgeoref nvarchar(1000)
declare @nTopoint numeric(24,6)
declare @sTorefdesc nvarchar(80)
declare @sTogeoref nvarchar(1000)
declare @sPerformonweek nvarchar(1)
declare @nPerformonday numeric(1)
declare @sAlert nvarchar(30)
declare @sDaeDocument nvarchar(30)
declare @sDaeRtype nvarchar(4)
declare @nIcr int
declare @sResobj nvarchar(30)
declare @sResorg nvarchar(15)
declare @sResource nvarchar(80)
declare @nCooldowntime numeric(18,2)
declare @nWarmuptime numeric(18,2)
declare @sActive varchar(1)
-- pre trigger related variables
declare @sCurrUser nvarchar(30)
declare @nComdays int
declare @sClosechk nvarchar(200) --r5install.ins_desc%TYPE
declare @dMindate datetime
declare @nCountit int
declare @sEvtobtype nvarchar(8) --r5ucodes.uco_code%TYPE
declare @sEvtobrtype nvarchar(8) --r5ucodes.uco_code%TYPE
declare @sEvtstatus nvarchar(8) --r5ucodes.uco_code%TYPE
declare @sEvtrstatus nvarchar(8) --r5ucodes.uco_code%TYPE
declare @sEvttype nvarchar(8) --r5ucodes.uco_code%TYPE
declare @sEvtrtype nvarchar(8) --r5ucodes.uco_code%TYPE
declare @dToday datetime
declare @sProductionrequest nvarchar(30)
declare @nProductionrequestrev numeric(4)
declare @sProductionrequest_org nvarchar(15)
declare @sProdorder nvarchar(80)
declare @sProdpriority nvarchar(256)
declare @sAccountingentity nvarchar(22)
declare @dProductionstart datetime
declare @dProductionend datetime
declare @dLastStatusUpdate datetime
declare @nLastStatusUpdateCnt int
declare @sMp nvarchar(30)
declare @sMporg nvarchar(15)
declare @nMprev numeric(4)
declare @nMpseq numeric(8)
declare @dMeterduedate datetime
declare @dMeterduedate2 datetime
declare @sEventOut nvarchar(30)
declare @dInterfaceOut datetime
set @dToday = dbo.f_trunc_date(getdate(),N'DD')
if @@ROWCOUNT > 0
begin
SET NOCOUNT ON
declare c_ins_del cursor local static for select i.EVT_CODE, i.EVT_DESC, i.EVT_TYPE, i.EVT_RTYPE, i.EVT_DATE,
i.EVT_STATUS, i.EVT_RSTATUS, i.EVT_MRC, i.EVT_LTYPE, i.EVT_LOCATION,
i.EVT_COSTCODE, i.EVT_PRINTED, i.EVT_PROJECT, i.EVT_PROJBUD, i.EVT_OBTYPE,
i.EVT_OBRTYPE, i.EVT_OBJECT, i.EVT_STANDWORK, i.EVT_PPM, i.EVT_FREQ,
i.EVT_ISSTYPE, i.EVT_METER, i.EVT_METERDUE, i.EVT_CLASS, i.EVT_ROUTE,
i.EVT_SESSION, i.EVT_SCHEDNO, i.EVT_PLANPRIO, i.EVT_FIXED, i.EVT_SLACK,
i.EVT_TARGET, i.EVT_NEWTARGET, i.EVT_EARLYSTART, i.EVT_LATEEND, i.EVT_MUSTEND,
i.EVT_LASTPLAN, i.EVT_DURATION, i.EVT_NEWDUR, i.EVT_PLANSTATUS, i.EVT_PARENT,
i.EVT_DEPEND, i.EVT_ORIGIN, i.EVT_REQM, i.EVT_CAUSE, i.EVT_ACTION,
i.EVT_JOBTYPE, i.EVT_PRIORITY, i.EVT_REPORTED, i.EVT_START, i.EVT_MATAVAIL,
i.EVT_DUE, i.EVT_COMPLETED, i.EVT_DOWNTIME, i.EVT_LABTOTAL, i.EVT_MATTOTAL,
i.EVT_SHIFT, i.EVT_FAILUREUSAGE, i.EVT_METERREADING, i.EVT_METUOM, i.EVT_MAXCOST,
i.EVT_SCREENER, i.EVT_ROUTEDFROM, i.EVT_ROUTEREASON, i.EVT_WARRANTY, i.EVT_OKWINEND,
i.EVT_NEARWINSTART, i.EVT_GENWINSTART, i.EVT_OKWINENDVAL, i.EVT_NEARWINBEGVAL, i.EVT_GENWINBEGVAL,
i.EVT_ROUTESTATUS, i.EVT_ROUTERSTATUS, i.EVT_ACD, i.EVT_REJECTREASON, i.EVT_ENTEREDBY,
i.EVT_FAILURE, i.EVT_DOWNTIMEHRS, i.EVT_SAFETY, i.EVT_OBJCRITICALITY, i.EVT_MPPROJ,
i.EVT_TRANSORGID, i.EVT_TRANSCODE, i.EVT_TRANSGROUP, i.EVT_CN, i.EVT_REOPENED,
i.EVT_PPOPK, i.EVT_PPMREV, i.EVT_ROUTEREV, i.EVT_ORG, i.EVT_CLASS_ORG,
i.EVT_OBJECT_ORG, i.EVT_LOCATION_ORG, i.EVT_SCHEDGRP, i.EVT_REQUESTSTART, i.EVT_SCHEDEND,
i.EVT_REQUESTEND, i.EVT_MASTERCAL, i.EVT_STEP, i.EVT_SEQ, i.EVT_TRIGEVENT,
i.EVT_OLDRSTATUS, i.EVT_OLDSTATUS, i.EVT_NEWRSTATUS, i.EVT_NEWSTATUS, i.EVT_CONFLICT,
i.EVT_CONFLICTRESOLVED, i.EVT_CONFLICTDESC, i.EVT_PACKAGE, i.EVT_CODE_ALIAS, i.EVT_CREATED,
i.EVT_CREATEDBY, i.EVT_UPDATED, i.EVT_UPDATEDBY, i.EVT_CONTNAME, i.EVT_CONTPHONE,
i.EVT_CONTEMAIL, i.EVT_CONTNOTES, i.EVT_FOLLOWUP, i.EVT_UPDATECOUNT, i.EVT_SERVICEREQUEST,
i.EVT_PERSON, i.EVT_PRINT, i.EVT_BILLABLE, i.EVT_PERIODUOM, i.EVT_METERDUE2,
i.EVT_METUOM2, i.EVT_ROUTEPARENT, i.EVT_SQLIDENTITY,
i.EVT_SYSLEVEL, i.EVT_SOURCECODE, i.EVT_SOURCESYSTEM,
i.EVT_INTERFACE, i.EVT_LASTCAL, i.EVT_SOPEFFECTIVE, i.EVT_CALSTATUS,
i.EVT_INCREMENT, i.EVT_PRECISION, i.EVT_PIDNO,
i.EVT_PIDDRAWING, i.EVT_SERVICEPERC, i.EVT_SOP, i.EVT_SRQCALLNAME,
i.EVT_SRQCUSTOMER, i.EVT_SRQLEVEL1,
i.EVT_FIRSTBILL, i.EVT_JECATEGORY, i.EVT_JESOURCE, i.EVT_GLTRANSFERFLAG, i.EVT_GLTRANSFER,
i.EVT_MULTIEQUIP, i.EVT_AGREEMENT, i.EVT_SIGPB, i.EVT_SIGRB, i.EVT_LASTTIMEPB, i.EVT_LASTTIMERB,
d.EVT_CODE, d.EVT_DESC, d.EVT_TYPE, d.EVT_RTYPE, d.EVT_DATE,
d.EVT_STATUS, d.EVT_RSTATUS, d.EVT_MRC, d.EVT_LTYPE, d.EVT_LOCATION,
d.EVT_COSTCODE, d.EVT_PRINTED, d.EVT_PROJECT, d.EVT_PROJBUD, d.EVT_OBTYPE,
d.EVT_OBRTYPE, d.EVT_OBJECT, d.EVT_STANDWORK, d.EVT_PPM, d.EVT_FREQ,
d.EVT_ISSTYPE, d.EVT_METER, d.EVT_METERDUE, d.EVT_CLASS, d.EVT_ROUTE,
d.EVT_SESSION, d.EVT_SCHEDNO, d.EVT_PLANPRIO, d.EVT_FIXED, d.EVT_SLACK,
d.EVT_TARGET, d.EVT_NEWTARGET, d.EVT_EARLYSTART, d.EVT_LATEEND, d.EVT_MUSTEND,
d.EVT_LASTPLAN, d.EVT_DURATION, d.EVT_NEWDUR, d.EVT_PLANSTATUS, d.EVT_PARENT,
d.EVT_DEPEND, d.EVT_ORIGIN, d.EVT_REQM, d.EVT_CAUSE, d.EVT_ACTION,
d.EVT_JOBTYPE, d.EVT_PRIORITY, d.EVT_REPORTED, d.EVT_START, d.EVT_MATAVAIL,
d.EVT_DUE, d.EVT_COMPLETED, d.EVT_DOWNTIME, d.EVT_LABTOTAL, d.EVT_MATTOTAL,
d.EVT_SHIFT, d.EVT_FAILUREUSAGE, d.EVT_METERREADING, d.EVT_METUOM, d.EVT_MAXCOST,
d.EVT_SCREENER, d.EVT_ROUTEDFROM, d.EVT_ROUTEREASON, d.EVT_WARRANTY, d.EVT_OKWINEND,
d.EVT_NEARWINSTART, d.EVT_GENWINSTART, d.EVT_OKWINENDVAL, d.EVT_NEARWINBEGVAL, d.EVT_GENWINBEGVAL,
d.EVT_ROUTESTATUS, d.EVT_ROUTERSTATUS, d.EVT_ACD, d.EVT_REJECTREASON, d.EVT_ENTEREDBY,
d.EVT_FAILURE, d.EVT_DOWNTIMEHRS, d.EVT_SAFETY, d.EVT_OBJCRITICALITY, d.EVT_MPPROJ,
d.EVT_TRANSORGID, d.EVT_TRANSCODE, d.EVT_TRANSGROUP, d.EVT_CN, d.EVT_REOPENED,
d.EVT_PPOPK, d.EVT_PPMREV, d.EVT_ROUTEREV, d.EVT_ORG, d.EVT_CLASS_ORG,
d.EVT_OBJECT_ORG, d.EVT_LOCATION_ORG, d.EVT_SCHEDGRP, d.EVT_REQUESTSTART, d.EVT_SCHEDEND,
d.EVT_REQUESTEND, d.EVT_MASTERCAL, d.EVT_STEP, d.EVT_SEQ, d.EVT_TRIGEVENT,
d.EVT_OLDRSTATUS, d.EVT_OLDSTATUS, d.EVT_NEWRSTATUS, d.EVT_NEWSTATUS, d.EVT_CONFLICT,
d.EVT_CONFLICTRESOLVED, d.EVT_CONFLICTDESC, d.EVT_PACKAGE, d.EVT_CODE_ALIAS, d.EVT_CREATED,
d.EVT_CREATEDBY, d.EVT_UPDATED, d.EVT_UPDATEDBY, d.EVT_CONTNAME, d.EVT_CONTPHONE,
d.EVT_CONTEMAIL, d.EVT_CONTNOTES, d.EVT_FOLLOWUP, d.EVT_UPDATECOUNT, d.EVT_SERVICEREQUEST,
d.EVT_PERSON, d.EVT_PRINT, d.EVT_BILLABLE, d.EVT_PERIODUOM, d.EVT_METERDUE2,
d.EVT_METUOM2, d.EVT_ROUTEPARENT, d.EVT_INTERFACE,
d.EVT_LASTTIMEPB, d.EVT_LASTTIMERB, i.EVT_COMPLETED_TRUNC, i.EVT_PERFORMEDBY, i.EVT_REVIEWEDBY,
i.EVT_METERINTERVAL, i.EVT_METERINTERVAL2,
i.EVT_WORKADDRESS, i.EVT_PFPROMISEDATE, i.EVT_TFPROMISEDATE, i.EVT_TFDATECOMPLETED, i.EVT_EQUIPMENTUSABILITY,
i.EVT_EQUIPMENTUSABILITY_ORG, i.EVT_PROVIDER, i.EVT_PROVIDER_ORG,
i.EVT_SERVICEPROBLEM, i.EVT_SERVICEPROBLEM_ORG, i.EVT_SERVICECATEGORY,
i.EVT_SERVICECATEGORY_ORG, i.EVT_UDFCHAR01,
i.EVT_UDFCHAR02, i.EVT_UDFCHAR03, i.EVT_UDFCHAR04, i.EVT_UDFCHAR05,
i.EVT_UDFCHAR06, i.EVT_UDFCHAR07, i.EVT_UDFCHAR08, i.EVT_UDFCHAR09,
i.EVT_UDFCHAR10, i.EVT_UDFCHAR11, i.EVT_UDFCHAR12, i.EVT_UDFCHAR13,
i.EVT_UDFCHAR14, i.EVT_UDFCHAR15, i.EVT_UDFCHAR16, i.EVT_UDFCHAR17,
i.EVT_UDFCHAR18, i.EVT_UDFCHAR19, i.EVT_UDFCHAR20, i.EVT_UDFCHAR21,
i.EVT_UDFCHAR22, i.EVT_UDFCHAR23, i.EVT_UDFCHAR24, i.EVT_UDFCHAR25,
i.EVT_UDFCHAR26, i.EVT_UDFCHAR27, i.EVT_UDFCHAR28, i.EVT_UDFCHAR29,
i.EVT_UDFCHAR30, i.EVT_UDFNUM01, i.EVT_UDFNUM02, i.EVT_UDFNUM03,
i.EVT_UDFNUM04, i.EVT_UDFNUM05, i.EVT_UDFDATE01, i.EVT_UDFDATE02,
i.EVT_UDFDATE03, i.EVT_UDFDATE04, i.EVT_UDFDATE05, i.EVT_UDFCHKBOX01,
i.EVT_UDFCHKBOX02, i.EVT_UDFCHKBOX03, i.EVT_UDFCHKBOX04, i.EVT_UDFCHKBOX05,
i.EVT_FROMPOINT, i.EVT_FROMREFDESC, i.EVT_FROMGEOREF, i.EVT_TOPOINT,
i.EVT_TOREFDESC, i.EVT_TOGEOREF, i.EVT_PERFORMONWEEK, i.EVT_PERFORMONDAY,
i.EVT_ALERT, i.EVT_PRODUCTIONREQUEST, i.EVT_PRODUCTIONREQUESTREV, i.EVT_PRODUCTIONREQUEST_ORG,
i.EVT_PRODORDER, i.EVT_PRODPRIORITY, i.EVT_ACCOUNTINGENTITY, i.EVT_PRODUCTIONSTART, i.EVT_PRODUCTIONEND,
i.EVT_LASTSTATUSUPDATE, i.EVT_MP, i.EVT_MP_ORG, i.EVT_MP_REV, i.EVT_MP_SEQ, i.EVT_METERDUEDATE, i.EVT_METERDUEDATE2
from inserted i, deleted d
where i.evt_sqlidentity = d.evt_sqlidentity
create table #tEvents_posupd_evt ( rkey int, oldst nvarchar(4), oldrt nvarchar(20) )
set @sUser = dbo.O7GTSUSR()
if @sUser <> N'SYS'
begin
open c_ins_del
fetch next from c_ins_del into @sCode, @sDesc, @sType, @sRtype, @dDate,
@sStatus, @sRstatus, @sMrc, @sLtype, @sLocation,
@sCostcode, @sPrinted, @sProject, @sProjbud, @sObtype,
@sObrtype, @sObject, @sStandwork, @sPpm, @nFreq,
@sIsstype, @sMeter, @nMeterdue, @sClass, @sRoute,
@sSession, @nSchedno, @sPlanprio, @sFixed, @nSlack,
@dTarget, @dNewtarget, @dEarlystart, @dLateend, @dMustend,
@dLastplan, @nDuration, @nNewdur, @sPlanstatus, @sParent,
@sDepend, @sOrigin, @sReqm, @sCause, @sAction,
@sJobtype, @sPriority, @dReported, @dStart, @dMatavail,
@dDue, @dCompleted, @nDowntime, @nLabtotal, @nMattotal,
@sShift, @nFailureusage, @nMeterreading, @sMetuom, @nMaxcost,
@sScreener, @sRoutedfrom, @sRoutereason, @sWarranty, @dOkwinend,
@dNearwinstart, @dGenwinstart, @nOkwinendval, @nNearwinbegval, @nGenwinbegval,
@sRoutestatus, @sRouterstatus, @nAcd, @sRejectreason, @sEnteredby,
@sFailure, @nDowntimehrs, @sSafety, @sObjcriticality, @sMpproj,
@nTransorgid, @sTranscode, @nTransgroup, @sCn, @sReopened,
@nPpopk, @nPpmrev, @nRouterev, @sOrg, @sClass_org,
@sObject_org, @sLocation_org, @sSchedgrp, @dRequeststart, @dSchedend,
@dRequestend, @sMastercal, @nStep, @nSeq, @sTrigevent,
@sOldrstatus, @sOldstatus, @sNewrstatus, @sNewstatus, @sConflict,
@sConflictresolved, @sConflictdesc, @sPackage, @sCode_alias, @dCreated,
@sCreatedby, @dUpdated, @sUpdatedby, @sContname, @sContphone,
@sContemail, @sContnotes, @sFollowup, @nUpdatecount, @sServicerequest,
@sPerson, @sPrint, @sBillable, @sPerioduom, @nMeterdue2,
@sMetuom2, @sRouteparent, @nIdentity,
@sSyslevel, @sSourcecode, @sSourcesystem,
@dInterface, @dLastcal, @dSopeffective, @sCalstatus,
@nINCREMENT, @nPRECISION, @sPIDNO, @sPIDDRAWING, @nSERVICEPERC,
@sSOP, @sSRQCALLNAME, @sSRQCUSTOMER, @sSRQLEVEL1,
@sFirstbill, @sJecategory, @sJesource, @sGltransferflag,@dGltransfer,
@sMultiequip, @sAgreement, @sSigpb, @sSigrb, @dLasttimepb, @dLasttimerb,
@sOldCode, @sOldDesc, @sOldType, @sOldRtype, @dOldDate,
@sOld_Status, @sOld_Rstatus, @sOldMrc, @sOldLtype, @sOldLocation,
@sOldCostcode, @sOldPrinted, @sOldProject, @sOldProjbud, @sOldObtype,
@sOldObrtype, @sOldObject, @sOldStandwork, @sOldPpm, @nOldFreq,
@sOldIsstype, @sOldMeter, @nOldMeterdue, @sOldClass, @sOldRoute,
@sOldSession, @nOldSchedno, @sOldPlanprio, @sOldFixed, @nOldSlack,
@dOldTarget, @dOldNewtarget, @dOldEarlystart, @dOldLateend, @dOldMustend,
@dOldLastplan, @nOldDuration, @nOldNewdur, @sOldPlanstatus, @sOldParent,
@sOldDepend, @sOldOrigin, @sOldReqm, @sOldCause, @sOldAction,
@sOldJobtype, @sOldPriority, @dOldReported, @dOldStart, @dOldMatavail,
@dOldDue, @dOldCompleted, @nOldDowntime, @nOldLabtotal, @nOldMattotal,
@sOldShift, @nOldFailureusage, @nOldMeterreading, @sOldMetuom, @nOldMaxcost,
@sOldScreener, @sOldRoutedfrom, @sOldRoutereason, @sOldWarranty, @dOldOkwinend,
@dOldNearwinstart, @dOldGenwinstart, @nOldOkwinendval, @nOldNearwinbegval, @nOldGenwinbegval,
@sOldRoutestatus, @sOldRouterstatus, @nOldAcd, @sOldRejectreason, @sOldEnteredby,
@sOldFailure, @nOldDowntimehrs, @sOldSafety, @sOldObjcriticality, @sOldMpproj,
@nOldTransorgid, @sOldTranscode, @nOldTransgroup, @sOldCn, @sOldReopened,
@nOldPpopk, @nOldPpmrev, @nOldRouterev, @sOldOrg, @sOldClass_org,
@sOldObject_org, @sOldLocation_org, @sOldSchedgrp, @dOldRequeststart, @dOldSchedend,
@dOldRequestend, @sOldMastercal, @nOldStep, @nOldSeq, @sOldTrigevent,
@sOldOldrstatus, @sOldOldstatus, @sOldNewrstatus, @sOldNewstatus, @sOldConflict,
@sOldConflictresolved, @sOldConflictdesc, @sOldPackage, @sOldCode_alias, @dOldCreated,
@sOldCreatedby, @dOldUpdated, @sOldUpdatedby, @sOldContname, @sOldContphone,
@sOldContemail, @sOldContnotes, @sOldFollowup, @nOldUpdatecount, @sOldServicerequest,
@sOldPerson, @sOldPrint, @sOldBillable, @sOldPerioduom, @nOldMeterdue2,
@sOldMetuom2, @sOldRouteparent, @dOldInterface,
@dOldLasttimepb, @dOldLasttimerb, @dCompletedtrunc, @sPerformedby, @sReviewedby,
@nMeterinterval, @nMeterinterval2,
@sWorkaddress, @dPfpromisedate, @dTfpromisedate, @dTfdatecompleted, @sEquipmentusability,
@sEquipmentusability_org, @sProvider, @sProvider_org,
@sServiceproblem, @sServiceproblem_org, @sServicecategory,
@sServicecategory_org,@sUdfchar01,
@sUdfchar02, @sUdfchar03, @sUdfchar04, @sUdfchar05,
@sUdfchar06, @sUdfchar07, @sUdfchar08, @sUdfchar09,
@sUdfchar10, @sUdfchar11, @sUdfchar12, @sUdfchar13,
@sUdfchar14, @sUdfchar15, @sUdfchar16, @sUdfchar17,
@sUdfchar18, @sUdfchar19, @sUdfchar20, @sUdfchar21,
@sUdfchar22, @sUdfchar23, @sUdfchar24, @sUdfchar25,
@sUdfchar26, @sUdfchar27, @sUdfchar28, @sUdfchar29,
@sUdfchar30, @nUdfnum01, @nUdfnum02, @nUdfnum03,
@nUdfnum04, @nUdfnum05, @dUdfdate01, @dUdfdate02,
@dUdfdate03, @dUdfdate04, @dUdfdate05, @sUdfchkbox01,
@sUdfchkbox02, @sUdfchkbox03, @sUdfchkbox04, @sUdfchkbox05,
@nFrompoint, @sFromrefdesc, @sFromgeoref, @nTopoint,
@sTorefdesc, @sTogeoref, @sPerformonweek, @nPerformonday,
@sAlert, @sProductionrequest, @nProductionrequestrev, @sProductionrequest_org,
@sProdorder, @sProdpriority, @sAccountingentity, @dProductionstart, @dProductionend,
@dLaststatusupdate, @sMp, @sMporg, @nMprev, @nMpseq, @dMeterduedate, @dMeterduedate2
while @@FETCH_STATUS = 0
begin
/* pre trigger start */
if @sStatus <> @sOld_Status
begin
select @nLastStatusUpdateCnt = count(*) from r5events_laststatusupdate where ELU_CODE = @sCode
if ( @nLastStatusUpdateCnt > 0 )
update r5events_laststatusupdate
set ELU_LASTSTATUSUPDATE = dbo.o7gttime2( @sOrg )
where ELU_CODE = @sCode
else
insert into r5events_laststatusupdate (ELU_CODE, ELU_LASTSTATUSUPDATE) values(@sCode, dbo.o7gttime2( @sOrg ))
end
--preinsupd_evt portion
execute @nReturn = O7SESS_CUR_USER @sCurrUser OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
set @sUpdatedby = coalesce( @sCurrUser, coalesce(@sUpdatedby, N'R5') )
--preupd_evt portion
set @sCheckresult = N'0'
set @sX = null
execute @nReturn = O7DFLT @nComdays OUTPUT, N'COMDAYS', @sChk OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
set @nComdays = isnull( @nComdays, 0 ) * -1
select @sClosechk = substring( upper(ins_desc), 1, 1)
FROM r5install
WHERE ins_code = N'WOCLPOUT'
if @sOldRstatus = N'C' and dbo.F_TRUNC_DATE( @dOldCompleted, N'DD' ) <= DATEADD(day, @nComdays, GETDATE())
begin
if isnull( @sOldRouterstatus, N'a' ) = isnull( @sRouterstatus, N'a' ) and
@sRstatus <> N'R' AND @sRstatus <> N'C' AND @dOldUpdated = @dUpdated
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'1', @@ERROR
rollback transaction
return
end
end
if @sCode <> @sOldCode OR @sPpm <> @sOldPpm
begin
close c_ins_del
deallocate c_ins_del
execute O7ERR_RAISE_ERROR N'R5', N'TRIG', 1
rollback transaction
return
end
if @sObject is null
begin
set @sObtype = null
set @sObrtype = null
end
if @sOldObject <> @sObject OR
@sOldObject_org <> @sObject_org OR
@sOldMrc <> @sMrc OR
@sOldLocation <> @sLocation OR
@sOldLocation_org <> @sLocation_org OR
@sOldJobtype <> @sJobtype
begin
select @nCount = count(*)
FROM r5bookedhours b
WHERE b.boo_event = @sCode
if @nCount > 0
select @dMindate = min( boo_date )
FROM r5bookedhours b
WHERE b.boo_event = @sCode
else
set @dMindate = null
if @dMindate is not null and dbo.O7DATECLOSED( @dMindate ) = 1
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'2', @@ERROR
rollback transaction
return
end
select @nCount = count(*)
FROM r5translines t
WHERE t.trl_event = @sCode
if @nCount > 0
select @dMindate = min( trl_date )
FROM r5translines t
WHERE t.trl_event = @sCode
else
set @dMindate = null
if @dMindate is not null and dbo.O7DATECLOSED( @dMindate ) = 1
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'3', @@ERROR
rollback transaction
return
end
end
if @sRstatus = N'A'
begin
execute @nReturn = O7EXISTW N'TRAN', N'*', N'*', N'2', N'1', @sOldCode, @sX, @sX, @nCountit OUTPUT, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @nCountit = 0
set @sX = null
else
begin
if @nCountit > 0
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'4', @@ERROR
rollback transaction
return
end
else
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'O7EXISTW', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
execute @nReturn = O7EXISTW N'TRAN', N'*', N'*', N'2', N'2', @sOldCode, @sX, @sX, @nCountit OUTPUT, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @nCountit = 0
set @sX = null
else
begin
if @nCountit > 0
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'5', @@ERROR
rollback transaction
return
end
else
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'O7EXISTW', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
execute @nReturn = O7EXISTW N'TRAN', N'*', N'*', N'2', N'3', @sOldCode, @sX, @sX, @nCountit OUTPUT, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @nCountit = 0
set @sX = null
else
begin
if @nCountit > 0
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'6', @@ERROR
rollback transaction
return
end
else
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'O7EXISTW', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
end
if isnull( @sMpproj, N'-' ) <> N'-' and
isnull( @sOldMpproj, N'-' ) <> N'-' and
(
@dCompleted <> @dOldCompleted OR
@sDesc <> @sOldDesc OR
@dDue <> @dOldDue OR
@nDuration <> @nOldDuration OR
@sMrc <> @sOldMrc OR
@sObject <> @sOldObject OR
@sObject_org <> @sOldObject_org OR
@sObtype <> @sOldObtype OR
@sPriority <> @sOldPriority OR
@sProject <> @sOldProject OR
@sRstatus <> @sOldRstatus OR
@sStatus <> @sOldStatus OR
@dTarget <> @dOldTarget
)
begin
close c_ins_del
deallocate c_ins_del
execute O7ERR_RAISE_ERROR N'R5', N'TRIG', 1
rollback transaction
return
end
if @sObtype is not null
set @sObrtype = null
if @sType is not null
set @sRtype = null
if @sStatus is not null
set @sRstatus = null
set @sEvtobtype = @sObtype
set @sEvtobrtype = @sObrtype
set @sEvtstatus = @sStatus
set @sEvtrstatus = @sRstatus
set @sEvttype = @sType
set @sEvtrtype = @sRtype
execute @nReturn = O7PREEVT N'UPD', @sOrg, @dReported,
@dCompleted, @dStart, @dDue,
@dTarget, @sMetuom, @nMeterdue,
@sDepend, @sParent, @sEvtobtype OUTPUT,
@sEvtobrtype OUTPUT, @sObject, @sObject_org,
@sLocation, @sLtype OUTPUT,
@sProject OUTPUT, @sProjbud OUTPUT, @sReqm,
@sAction, @sCause, @sFailure,
@sJobtype, @sPriority, @sWarranty,
@sEvtstatus OUTPUT, @sEvtrstatus OUTPUT, @sEvttype OUTPUT,
@sEvtrtype OUTPUT, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @sCheckresult = N'16' AND
@sEvtrstatus = N'C' AND
isnull(@sOldRstatus, N'C') = N'C' AND
@sPerson <> @sOldPerson
SET @sCheckresult = ''
ELSE
if @sCheckresult <> N'0'
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'O7PREEVT', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
if @sLocation is null
set @sLocation_org = null
set @sStatus = @sEvtstatus
set @sRstatus = @sEvtrstatus
set @sRtype = @sEvtrtype
set @sType = @sEvttype
set @sObtype = @sEvtobtype
set @sObrtype = @sEvtobrtype
if @sRstatus <> @sOldRstatus
begin
set @sScreener = null
set @sRoutedfrom = null
set @sRoutereason = null
end
if @sRstatus = N'C' and @sClosechk <> N'Y'
begin
execute @nReturn = O7EXISTW N'EVNT', N'*', N'*', N'1', @sOldCode, @sX, @sX, @sX, @nCountit OUTPUT, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @nCountit = 0
set @sX = null
else
begin
if @nCountit > 0
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'7', @@ERROR
rollback transaction
return
end
else
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'O7EXISTW', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
execute @nReturn = O7EXISTW N'EVNT', N'*', N'*', N'3', @sOldCode, @sX, @sX, @sX, @nCountit OUTPUT, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @nCountit = 0
set @sX = null
else
begin
if @nCountit > 0
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'8', @@ERROR
rollback transaction
return
end
else
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'O7EXISTW', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
execute @nReturn = O7EXISTW N'EVNT', N'*', N'*', N'4', @sOldCode, @sX, @sX, @sX, @nCountit OUTPUT, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @nCountit = 0
set @sX = null
else
begin
if @nCountit > 0
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'PREUPD_EVT', N'TRIG', N'9', @@ERROR
rollback transaction
return
end
else
begin
close c_ins_del
deallocate c_ins_del
execute O7RAE N'O7EXISTW', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
end
if @sMpproj is null
set @sMpproj = N'-'
/* pre trigger end */
--XMLOUT_POSUPD_EVT portion
--if @sSourcesystem is null and @sSourcecode is null
--begin
if (@sRtype = N'JOB' or @sRtype = N'PPM') and ( @sRstatus = N'C' or @sRstatus = N'R')
and ( (@dInterface IS NULL and @dOldInterface IS NULL)
or (@dInterface IS NOT NULL and @dOldInterface IS NOT NULL and @dInterface = @dOldInterface) )
begin
select @nCount = count(*)
from r5install
where INS_CODE = N'@SYNCWO'
if @nCount > 0
select @sFlag = isnull( INS_DESC, N'N' )
from r5install
where INS_CODE = N'@SYNCWO'
else
set @sFlag = N'N'
if @sFlag = N'Y' or @sFlag = N'A'
begin
select @nCount = count(*)
from r5ucodes
where uco_rentity = N'JBTP' and uco_code= @sJobtype AND uco_rcode IN ( N'*', N'BR', N'ST', N'PM', N'CAL', N'RP', N'MEC')
if @nCount > 0
begin
EXECUTE @nReturn = O7SESS_GET_MESSAGEID @sTempMsgid OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
rollback transaction
return
end
if @sFlag = N'A' and @sParent is not null
begin
set @sEventOut = @sParent
select @dInterfaceOut = evt_interface from r5events where evt_code=@sParent
end
else
begin
set @sEventOut = @sCode
set @dInterfaceOut = @dInterface
end
INSERT INTO R5XMLTRANSTATUS
(XTS_TRANTYPE,
XTS_TABLE,
XTS_KEYFLD1,
XTS_KEYFLD2,
XTS_ORG,
XTS_ORIG_MESSAGEID
)
SELECT
N'SYNCMAINTORDER',
N'R5EVENTS',
@sEventOut,
CASE WHEN @dInterfaceOut IS NULL THEN N'A' ELSE N'R' END,
@sOrg,
@sTempMsgid
WHERE NOT EXISTS(select 1 from r5xmltranstatus where xts_trantype=N'SYNCMAINTORDER' and xts_keyfld1=@sEventOut)
end
end
end
--end
--POSUPD_EVT portion
set @sCheckresult = N'0'
set @sX = null
IF @sRstatus = 'C' AND @sOld_Rstatus <> 'C'
begin
declare c_contactrecord cursor local for
SELECT ctr_code,ctr_org,ctr_rstatus
FROM r5contactrecords
WHERE ctr_event = @sCode
AND ctr_rstatus IN (N'O', N'F')
OPEN c_contactrecord
FETCH NEXT FROM c_contactrecord INTO @sContactrecordcode,@sContactrecordorg,@sContactcenterrstatus
while @@FETCH_STATUS = 0
begin
IF @sContactrecordcode IS NOT NULL AND @sContactrecordorg IS NOT NULL
begin
SELECT @sOpenstatus = cop_defopenstatus,
@sFollowstatus = cop_deffollowupstatus
FROM r5contactcenteroptions
WHERE cop_org=@sContactrecordorg
IF @sContactcenterrstatus =N'O'
set @sNewcontactcenterstatus = @sOpenstatus
ELSE
begin
IF @sContactcenterrstatus =N'F'
set @sNewcontactcenterstatus = @sFollowstatus
end
UPDATE r5contactrecords SET ctr_status=@sNewcontactcenterstatus, ctr_updatedby=@sUpdatedby, ctr_updated=@dUpdated
WHERE ctr_code =@sContactrecordcode AND ctr_org =@sContactrecordorg
end
FETCH NEXT FROM c_contactrecord INTO @sContactrecordcode,@sContactrecordorg,@sContactcenterrstatus
end
close c_contactrecord
deallocate c_contactrecord
end
execute @nReturn = O7DFLT @sDeforg OUTPUT, N'DEFORG', @sChk OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
set @sChk = 0
if @sRstatus = N'C'
begin
execute @nReturn = O7DERES2 @sCode, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
if @sCheckresult <> N'0'
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
execute O7RAE N'O7DERES2', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
execute @nReturn = O7DFLT @sTemp OUTPUT, N'DELTMATL', @sChk OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
if isnull( @sTemp, N'Y' ) = N'Y'
begin
--workaround for SQL Server BS
insert into R5EVENTS_PASSTHROUGH( procname, mode, oldrstatus, code, ppm, ppmrev, rstatus, freq, object, object_org, perioduom, metuom, metuom2, status,
route, ppopk, routeparent, multiequip, completed, reqm, action, cause, failure, isstype, oldroute, dateinserted, reported )
values ( N'O7DEMLP1', null, null, @sCode, null, null,
null, null, null, null, null,
null, null, null, null, null, null, null, null,
null, null, null, null, null, null, @dToday, null )
end
UPDATE r5objusagedefs
SET oud_sincelastwork = 0
WHERE oud_object = @sObject
AND oud_object_org = @sObject_org
end
execute @nReturn = O7GTTIME @dGtTime OUTPUT, @sOrg
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
if ( @sOld_Rstatus = N'C' and @sRstatus = N'R' ) OR
( @sOld_Rstatus = N'R' and @sRstatus = N'C' and @sOldReopened = N'+' )
set @sX = null
else
begin
IF @sPpm IS NOT NULL
AND ( coalesce( @nFreq, 0 ) > 0 OR @nMeterdue IS NOT NULL OR @nMeterdue2 IS NOT NULL )
AND @sRstatus <> 'C'
AND ( coalesce( @nFreq, 0 ) <> coalesce( @nOldFreq, 0 )
OR ( coalesce( @dTarget, dateadd( day, -1001, @dGtTime ) ) <> coalesce( @dOldTarget, dateadd( day, -1001, @dGtTime ) )
AND @sIsstype = 'V' )
OR ( coalesce( @nOldMeterdue, -10 ) <> coalesce( @nMeterdue, -10 ) )
OR ( coalesce( @nOldMeterdue2, -10 ) <> coalesce( @nMeterdue2, -10 ) )
OR ( coalesce( @dDue, dateadd( day, -1001, @dGtTime ) ) <> coalesce(@dOldDue, dateadd( day, -1001, @dGtTime ) )
AND @sIsstype = 'F' )
OR ( @sIsstype <> @sOldIsstype
AND @dTarget <> @dDue) )
begin
execute @nReturn = O7CRFUT1 @sCode, @sCode, @sIsstype, @dTarget, @dDue, @nFreq,
@sPerioduom, @sPerformonweek, @nPerformonday, @nDuration, @sPpm, @nPpmrev, @sObject,
@sObject_org, @nMeterdue, @nMeterdue2, @nPpopk, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
if @sCheckresult <> N'0'
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
execute O7RAE N'O7CRFUT1', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
if @sPpm is not null and @sRstatus = N'C' and @sOld_Rstatus <> N'C'
begin
execute @nReturn = O7CRFUT1 @sCode, @sX, @sIsstype, @dTarget, @dDue, @nFreq,
@sPerioduom, @sPerformonweek, @nPerformonday, @nDuration, @sPpm, @nPpmrev, @sObject,
@sObject_org, @nMeterdue, @nMeterdue2, @nPpopk, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
if @sCheckresult <> N'0'
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
execute O7RAE N'O7CRFUT1', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
end
if @sStandwork is not null
AND coalesce( @sStandwork, N'x' ) <> coalesce( @sOldStandwork, N'x' )
begin
SELECT @nCount = COUNT(*)
FROM r5addetails
WHERE add_rentity = N'EVNT' AND add_code = @sCode
if @nCount = 0
begin
execute @nReturn = O7CRADD1 @sCode, @sStandwork, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_inserted
deallocate c_inserted
drop table #tEvents_posins_evt
rollback transaction
return
end
if @sCheckresult <> N'0'
begin
close c_inserted
deallocate c_inserted
drop table #tEvents_posins_evt
execute O7RAE N'O7CRADD1', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
end
if coalesce(@sStatus,'#') <> coalesce(@sOld_Status,'#')
begin
execute @nReturn = r5o7_o7maxseq @sSeqno OUTPUT, N'CRH', N'1', @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
INSERT INTO R5CustomerRequestHistory(
CRH_PK,
CRH_REQDATE,
CRH_EVENTTYPE,
CRH_USERCODE,
CRH_FIELD,
CRH_OLDVALUE,
CRH_NEWVALUE,
CRH_RENTITY,
CRH_EVENT )
VALUES( @sSeqno,
dbo.o7gttime2( @sOrg ),
N'SU',
@sUpdatedby,
null,
@sOld_Status,
@sStatus,
N'EVNT',
@sCode)
end
if @sDesc <> @sOldDesc
begin
execute @nReturn = O7DESCS N'UPD', @sX, N'EVNT', @sX, N'*', @sCode, @sOrg, @sDesc, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
if @sCheckresult <> N'0'
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
execute O7RAE N'O7DESCS', N'PROC', @sCheckresult, @@ERROR
rollback transaction
return
end
end
if isnull( @sOldClass, N'*') <> N'*' and ( @sOldClass <> isnull(@sClass, N'*') or @sOldClass_org <> isnull(@sClass_org, @sDeforg) )
DELETE FROM r5propertyvalues
WHERE prv_rentity = N'EVNT'
AND prv_class = @sOldClass
AND prv_class_org = @sOldClass_org
AND prv_code = @sOldCode
--O7EVT.POSROW functionality here
insert into #tEvents_posupd_evt values ( @nIdentity, @sOld_Rstatus, @sOldRoute )
execute @nReturn = O7ERECORD @nErecord OUTPUT, N'UPD', N'EVNT', @sOld_Status, @sStatus
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ERECORD @nErecord2 OUTPUT, N'UPD', N'INRH', @sOldRoutestatus, @sRoutestatus
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
IF @nErecord > 0 OR
@nErecord2 > 0
begin
set @sObjcgmp = N'+'
IF UPPER( coalesce( dbo.o7dflt2( N'CGMPONLY'), N'NO')) = N'YES'
SELECT @sObjcgmp = obj_cgmp
FROM r5objects
WHERE obj_code = @sObject
AND obj_org = @sObject_org
IF coalesce( @sObjcgmp, N'-' ) = N'+'
begin
execute @nReturn = R5O7_O7MAXSEQ @sSeqno OUTPUT, N'EREC', N'1', @sChk1 OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETUSER @sGetUser OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETSTYPE @sGetStype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7GTTIME @dGtTime OUTPUT, @sOrg
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYNUM @sCertnum OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYTYPE @sCerttype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
INSERT INTO r5elecarchive ( ELA_CODE, ELA_USER, ELA_DATE, ELA_SIGNTYPE, ELA_CERTIFYNUM, ELA_CERTIFYTYPE,
ELA_SCODE, ELA_ENTITY,ELA_ENTCODE,ELA_ENTORG, ELA_STATUS, ELA_PARENT,
ELA_FLD1, ELA_FLD2, ELA_FLD3, ELA_FLD4,
ELA_FLD5, ELA_FLD6, ELA_FLD7, ELA_FLD8,
ELA_FLD9, ELA_FLD10, ELA_FLD11, ELA_FLD12,
ELA_FLD13, ELA_FLD14, ELA_FLD15, ELA_FLD16,
ELA_FLD17, ELA_FLD18, ELA_FLD19, ELA_FLD20,
ELA_FLD21, ELA_FLD22, ELA_FLD23, ELA_FLD24,
ELA_FLD25, ELA_FLD26, ELA_FLD27, ELA_FLD28,
ELA_FLD29, ELA_FLD30, ELA_FLD31, ELA_FLD32,
ELA_FLD33, ELA_FLD34, ELA_FLD35, ELA_FLD36,
ELA_FLD37, ELA_FLD38, ELA_FLD39, ELA_FLD40,
ELA_FLD41, ELA_FLD42, ELA_FLD43, ELA_FLD44,
ELA_FLD45, ELA_FLD46, ELA_FLD47, ELA_FLD48,
ELA_FLD49, ELA_FLD50, ELA_FLD51, ELA_FLD52,
ELA_FLD53, ELA_FLD54, ELA_FLD55, ELA_FLD56,
ELA_FLD57, ELA_FLD58, ELA_FLD59, ELA_FLD60,
ELA_FLD61, ELA_FLD62, ELA_FLD63, ELA_FLD64,
ELA_FLD65, ELA_FLD66, ELA_FLD67, ELA_FLD68,
ELA_FLD69, ELA_FLD70, ELA_FLD71, ELA_FLD72,
ELA_FLD73, ELA_FLD74, ELA_FLD75, ELA_FLD76,
ELA_FLD77, ELA_FLD78, ELA_FLD79, ELA_FLD80,
ELA_FLD81, ELA_FLD82, ELA_FLD83, ELA_FLD84,
ELA_FLD85, ELA_FLD86, ELA_FLD87, ELA_FLD88,
ELA_FLD89, ELA_FLD90, ELA_FLD91, ELA_FLD92,
ELA_FLD93, ELA_FLD94, ELA_FLD95, ELA_FLD96,
ELA_FLD97, ELA_FLD98, ELA_FLD99, ELA_FLD100,
ELA_FLD101, ELA_FLD102, ELA_FLD103, ELA_FLD104,
ELA_FLD105, ELA_FLD106, ELA_FLD107, ELA_FLD108,
ELA_FLD109, ELA_FLD110, ELA_FLD111, ELA_FLD112,
ELA_FLD113, ELA_FLD114, ELA_FLD115, ELA_FLD116,
ELA_FLD117, ELA_FLD118, ELA_FLD119, ELA_FLD120,
ELA_FLD121, ELA_FLD122, ELA_FLD123, ELA_FLD124,
ELA_FLD125, ELA_FLD126, ELA_FLD127, ELA_FLD128,
ELA_FLD129, ELA_FLD130, ELA_FLD131, ELA_FLD132,
ELA_FLD133, ELA_FLD134, ELA_FLD135, ELA_FLD136,
ELA_FLD137, ELA_FLD138, ELA_FLD139, ELA_FLD140,
ELA_FLD141, ELA_FLD142, ELA_FLD143, ELA_FLD144,
ELA_FLD145, ELA_FLD146, ELA_FLD147, ELA_FLD148,
ELA_FLD149, ELA_FLD150, ELA_FLD151, ELA_FLD152,
ELA_FLD153, ELA_FLD154, ELA_FLD155, ELA_FLD156,
ELA_FLD157, ELA_FLD158, ELA_FLD159, ELA_FLD160,
ELA_FLD161, ELA_FLD162, ELA_FLD163, ELA_FLD164,
ELA_FLD165, ELA_FLD166, ELA_FLD167, ELA_FLD168,
ELA_FLD169, ELA_FLD170, ELA_FLD171, ELA_FLD172,
ELA_FLD173, ELA_FLD174, ELA_FLD175, ELA_FLD176,
ELA_FLD177, ELA_FLD178, ELA_FLD179, ELA_FLD180,
ELA_FLD181, ELA_FLD182, ELA_FLD183, ELA_FLD184,
ELA_FLD185, ELA_FLD186, ELA_FLD187, ELA_FLD188,
ELA_FLD189, ELA_FLD190, ELA_FLD191, ELA_FLD192,
ELA_FLD193, ELA_FLD194, ELA_FLD195, ELA_FLD196,
ELA_FLD197, ELA_FLD198, ELA_FLD199, ELA_FLD200,
ELA_FLD201, ELA_FLD202, ELA_FLD203, ELA_FLD204,
ELA_FLD205, ELA_FLD206, ELA_FLD207, ELA_FLD208,
ELA_FLD209, ELA_FLD210, ELA_FLD211, ELA_FLD212,
ELA_FLD213, ELA_FLD214, ELA_FLD215, ELA_FLD216,
ELA_FLD217, ELA_FLD218, ELA_FLD219, ELA_FLD220,
ELA_FLD221, ELA_FLD222, ELA_FLD223, ELA_FLD224,
ELA_FLD225, ELA_FLD226, ELA_FLD227, ELA_FLD228,
ELA_FLD229, ELA_FLD230, ELA_FLD231, ELA_FLD232,
ELA_FLD233, ELA_FLD234, ELA_FLD235, ELA_FLD236,
ELA_FLD237, ELA_FLD238, ELA_FLD239, ELA_FLD240 )
VALUES ( @sSeqno, @sGetUser, @dGtTime, @sGetStype,
@sCertnum, @sCerttype,
null, N'EVNT', @sCode, @sOrg, @sStatus, null,
@sCode, @sDesc, @sType, @sRtype,
dbo.F_DATE_TO_CHAR(@dDate, N'DD-MON-YYYY HH24:MI:SS' ),
@sStatus, @sRstatus, @sMrc,
@sLtype, @sLocation, @sCostcode, @sPrinted,
@sProject, @sProjbud, @sObtype, @sObrtype,
@sObject, @sStandwork, @sPpm, @nFreq,
@sIsstype, @sMeter, @nMeterdue, @sClass,
@sRoute, @sSession, @nSchedno, @sPlanprio,
@sFixed, @nSlack,
dbo.F_DATE_TO_CHAR(@dTarget, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dNewtarget, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dEarlystart, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dLateend, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dMustend, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dLastplan, N'DD-MON-YYYY' ),
@nDuration, @nNewdur, @sPlanstatus, @sParent,
@sDepend, @sOrigin, @sReqm, @sCause,
@sAction, @sJobtype, @sPriority,
dbo.F_DATE_TO_CHAR(@dReported, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dStart, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dMatavail, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dDue, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dCompleted, N'DD-MON-YYYY HH24:MI:SS' ),
@nDowntime, @nLabtotal, @nMattotal, @sShift,
@nFailureusage, @nMeterreading, @sMetuom, @nMaxcost,
@sScreener, @sRoutedfrom, @sRoutereason,@sWarranty,
dbo.F_DATE_TO_CHAR(@dOkwinend, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dNearwinstart, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dGenwinstart, N'DD-MON-YYYY' ),
@nOkwinendval,
@nNearwinbegval,@nGenwinbegval,@sRoutestatus,@sRouterstatus,
@nAcd, @sRejectreason,@sEnteredby, @sFailure,
@nDowntimehrs,@sSafety, @sObjcriticality,@sMpproj,
@nTransorgid,@sTranscode, @nTransgroup, @sCn,
@sReopened, @nPpopk, @nPpmrev, @nRouterev,
@sOrg, @sClass_org, @sObject_org, @sLocation_org,
@sSchedgrp,
dbo.F_DATE_TO_CHAR(@dRequeststart, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dSchedend, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dRequestend, N'DD-MON-YYYY' ),
@sMastercal,@nStep, @nSeq, @sTrigevent,
@sOldrstatus,@sOldstatus, @sNewrstatus, @sNewstatus,
@sConflict, @sConflictresolved,@sConflictdesc,@sPackage,
@sCode_alias,
dbo.F_DATE_TO_CHAR(@dCreated, N'DD-MON-YYYY HH24:MI:SS' ), @sCreatedby,
dbo.F_DATE_TO_CHAR(@dUpdated, N'DD-MON-YYYY HH24:MI:SS' ),@sUpdatedby , @sContname,
@sContphone,@sContemail, @sContnotes, @sFollowup,
@nUpdatecount,@sServicerequest,@sPerson,@sPrint,
@sBillable, @sPerioduom, @nMeterdue2, @sMetuom2,
@sRouteparent, @sSyslevel, @sSourcecode, @sSourcesystem,
dbo.F_DATE_TO_CHAR(@dInterface, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dLastcal, N'DD-MON-YYYY' ),
dbo.F_DATE_TO_CHAR(@dSopeffective, N'DD-MON-YYYY' ),
@sCalstatus, @nINCREMENT, @nPRECISION, @sPIDNO,
@sPIDDRAWING, @nSERVICEPERC,
@sSOP, @sSRQCALLNAME, @sSRQCUSTOMER, @sSRQLEVEL1 ,
@sFirstbill, @sJecategory, @sJesource, @sGltransferflag,
dbo.F_DATE_TO_CHAR(@dGltransfer,N'DD-MON-YYYY HH24:MI:SS'), @sMultiequip, @sAgreement,
@sSigpb, @sSigrb, dbo.F_DATE_TO_CHAR(@dLasttimepb,'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dLasttimerb,'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dCompletedtrunc,'DD-MON-YYYY'), @sPerformedby, @sReviewedby,
@nMeterinterval, @nMeterinterval2,
@sWorkaddress,
dbo.F_DATE_TO_CHAR(@dPfpromisedate, N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dTfpromisedate, N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dTfdatecompleted, N'DD-MON-YYYY HH24:MI:SS'), @sEquipmentusability,
@sEquipmentusability_org, @sProvider, @sProvider_org,
@sServiceproblem, @sServiceproblem_org, @sServicecategory,
@sServicecategory_org,@sUdfchar01,
@sUdfchar02, @sUdfchar03, @sUdfchar04, @sUdfchar05,
@sUdfchar06, @sUdfchar07, @sUdfchar08, @sUdfchar09,
@sUdfchar10, @sUdfchar11, @sUdfchar12, @sUdfchar13,
@sUdfchar14, @sUdfchar15, @sUdfchar16, @sUdfchar17,
@sUdfchar18, @sUdfchar19, @sUdfchar20, @sUdfchar21,
@sUdfchar22, @sUdfchar23, @sUdfchar24, @sUdfchar25,
@sUdfchar26, @sUdfchar27, @sUdfchar28, @sUdfchar29,
@sUdfchar30, @nUdfnum01, @nUdfnum02, @nUdfnum03,
@nUdfnum04, @nUdfnum05,
dbo.F_DATE_TO_CHAR(@dUdfdate01,N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dUdfdate02,N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dUdfdate03,N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dUdfdate04,N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dUdfdate05,N'DD-MON-YYYY HH24:MI:SS'), @sUdfchkbox01,
@sUdfchkbox02, @sUdfchkbox03, @sUdfchkbox04, @sUdfchkbox05,
@nFrompoint, @sFromrefdesc, @sFromgeoref, @nTopoint,
@sTorefdesc, @sTogeoref, @sPerformonweek, @nPerformonday,
@sAlert, @sProductionrequest, @nProductionrequestrev,
@sProductionrequest_org, @sProdorder, @sProdpriority,
@sAccountingentity,
dbo.F_DATE_TO_CHAR(@dProductionstart, N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dProductionend, N'DD-MON-YYYY HH24:MI:SS'),
dbo.F_DATE_TO_CHAR(@dLaststatusupdate, N'DD-MON-YYYY HH24:MI:SS'),
@sMp, @sMporg, @nMprev, @nMpseq,
dbo.F_DATE_TO_CHAR(@dMeterduedate,N'DD-MON-YYYY'),
dbo.F_DATE_TO_CHAR(@dMeterduedate2,N'DD-MON-YYYY') )
-- end
declare c_act cursor local for select ACT_EVENT, ACT_ACT, ACT_SUPPLIER, ACT_START,
ACT_TIME, ACT_NEWSTART, ACT_HIRE, ACT_ORDERED,
ACT_FIXH, ACT_MINHOURS, ACT_MRC, ACT_TRADE,
ACT_SHIFT, ACT_PERSONS, ACT_PROJECT, ACT_PROJBUD,
ACT_ROUTE, ACT_DURATION, ACT_NEWDUR, ACT_MAXDUR,
ACT_EST, ACT_REM, ACT_NT, ACT_NTRATE,
ACT_OT, ACT_OTRATE, ACT_PURRATE, ACT_TASK,
ACT_MATLIST, ACT_SPECIAL, ACT_GRAPH, ACT_LEVEL,
ACT_ORDER, ACT_ORDLINE, ACT_ORDTYPE, ACT_ORDRTYPE,
ACT_SCHEDHRS, ACT_LATESTSCHED, ACT_COMPLETED, ACT_MATLREV,
ACT_TASKREV, ACT_QTY, ACT_UOM, ACT_REQ,
ACT_REQLINE, ACT_SUPPLIER_ORG, ACT_ORDER_ORG, ACT_RPC,
ACT_WAP, ACT_TPF, ACT_MANUFACT, ACT_SYSLEVEL,
ACT_ASMLEVEL, ACT_COMPLEVEL, ACT_CLASS, ACT_CLASS_ORG,
ACT_PERCOMPLETE, ACT_UPDATECOUNT, ACT_SOURCECODE, ACT_SOURCESYSTEM, ACT_PERRESP, ACT_WARRANTY,
ACT_RELATEDWO, ACT_NOTE, ACT_DEFERMAINTENANCE, ACT_DEFERREDIRECTMATS,
ACT_DEFERREDORIGWO, ACT_DEFERREDORIGACT, ACT_UDFCHAR01, ACT_UDFCHAR02,
ACT_UDFCHAR03, ACT_UDFCHAR04, ACT_UDFCHAR05, ACT_UDFCHAR06,
ACT_UDFCHAR07, ACT_UDFCHAR08, ACT_UDFCHAR09, ACT_UDFCHAR10,
ACT_UDFCHAR11, ACT_UDFCHAR12, ACT_UDFCHAR13, ACT_UDFCHAR14,
ACT_UDFCHAR15, ACT_UDFCHAR16, ACT_UDFCHAR17, ACT_UDFCHAR18,
ACT_UDFCHAR19, ACT_UDFCHAR20, ACT_UDFCHAR21, ACT_UDFCHAR22,
ACT_UDFCHAR23, ACT_UDFCHAR24, ACT_UDFCHAR25, ACT_UDFCHAR26,
ACT_UDFCHAR27, ACT_UDFCHAR28, ACT_UDFCHAR29, ACT_UDFCHAR30,
ACT_UDFNUM01, ACT_UDFNUM02, ACT_UDFNUM03, ACT_UDFNUM04,
ACT_UDFNUM05, ACT_UDFDATE01, ACT_UDFDATE02, ACT_UDFDATE03,
ACT_UDFDATE04, ACT_UDFDATE05, ACT_UDFCHKBOX01, ACT_UDFCHKBOX02,
ACT_UDFCHKBOX03, ACT_UDFCHKBOX04, ACT_UDFCHKBOX05
FROM r5activities
WHERE act_event = @sCode
open c_act
fetch next from c_act into @sActevent, @nActact, @sActsupplier, @dActstart,
@nActtime, @dActnewstart, @sActhire, @sActordered,
@sActfixh, @sActminhours, @sActmrc, @sActtrade,
@sActshift, @nActpersons, @sActproject, @sActprojbud,
@sActroute, @nActduration, @nActnewdur, @nActmaxdur,
@nActest, @nActrem, @nActnt, @nActntrate,
@nActot, @nActotrate, @nActpurrate, @sActtask,
@sActmatlist, @sActspecial, @sActgraph, @nActlevel,
@sActorder, @nActordline, @sActordtype, @sActordrtype,
@nActschedhrs, @dActlatestsched, @sActcompleted, @nActmatlrev,
@nActtaskrev, @nActqty, @sActuom, @sActreq,
@nActreqline, @sActsupplier_org, @sActorder_org, @sActrpc,
@sActwap, @sActtpf, @sActmanufact, @sActsyslevel,
@sActasmlevel, @sActcomplevel, @sActclass, @sActclass_org,
@nActpercomplete, @nActupdatecount, @sActSourcecode, @sActSourcesys, @sActPerresp, @sActWarranty,
@sActrelatedwo, @sActnote, @sActdefermaintenance, @sActdeferredirectmats, @sActdeferredorigwo,
@sActdeferredorigact, @sActudfchar01, @sActudfchar02, @sActudfchar03,
@sActudfchar04, @sActudfchar05, @sActudfchar06, @sActudfchar07,
@sActudfchar08, @sActudfchar09, @sActudfchar10, @sActudfchar11,
@sActudfchar12, @sActudfchar13, @sActudfchar14, @sActudfchar15,
@sActudfchar16, @sActudfchar17, @sActudfchar18, @sActudfchar19,
@sActudfchar20, @sActudfchar21, @sActudfchar22, @sActudfchar23,
@sActudfchar24, @sActudfchar25, @sActudfchar26, @sActudfchar27,
@sActudfchar28, @sActudfchar29, @sActudfchar30, @nActudfnum01,
@nActudfnum02 , @nActudfnum03, @nActudfnum04, @nActudfnum05,
@dActudfdate01, @dActudfdate02, @dActudfdate03, @dActudfdate04,
@dActudfdate05, @sActudfchkbox01, @sActudfchkbox02, @sActudfchkbox03,
@sActudfchkbox04, @sActudfchkbox05
while @@FETCH_STATUS = 0
begin
execute @nReturn = R5O7_O7MAXSEQ @sSeqno1 OUTPUT, N'EREC', N'1', @sChk1 OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_act
deallocate c_act
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETUSER @sGetUser OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_act
deallocate c_act
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETSTYPE @sGetStype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_act
deallocate c_act
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7GTTIME @dGtTime OUTPUT, @sOrg
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_act
deallocate c_act
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYNUM @sCertnum OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_act
deallocate c_act
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYTYPE @sCerttype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_act
deallocate c_act
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
INSERT INTO r5elecarchive ( ELA_CODE, ELA_USER, ELA_DATE, ELA_SIGNTYPE, ELA_CERTIFYNUM, ELA_CERTIFYTYPE,
ELA_SCODE, ELA_ENTITY,ELA_ENTCODE,ELA_ENTORG, ELA_STATUS, ELA_PARENT,
ELA_FLD1, ELA_FLD2, ELA_FLD3, ELA_FLD4,
ELA_FLD5, ELA_FLD6, ELA_FLD7, ELA_FLD8,
ELA_FLD9, ELA_FLD10, ELA_FLD11, ELA_FLD12,
ELA_FLD13, ELA_FLD14, ELA_FLD15, ELA_FLD16,
ELA_FLD17, ELA_FLD18, ELA_FLD19, ELA_FLD20,
ELA_FLD21, ELA_FLD22, ELA_FLD23, ELA_FLD24,
ELA_FLD25, ELA_FLD26, ELA_FLD27, ELA_FLD28,
ELA_FLD29, ELA_FLD30, ELA_FLD31, ELA_FLD32,
ELA_FLD33, ELA_FLD34, ELA_FLD35, ELA_FLD36,
ELA_FLD37, ELA_FLD38, ELA_FLD39, ELA_FLD40,
ELA_FLD41, ELA_FLD42, ELA_FLD43, ELA_FLD44,
ELA_FLD45, ELA_FLD46, ELA_FLD47, ELA_FLD48,
ELA_FLD49, ELA_FLD50, ELA_FLD51, ELA_FLD52,
ELA_FLD53, ELA_FLD54, ELA_FLD55, ELA_FLD56,
ELA_FLD57, ELA_FLD58, ELA_FLD59, ELA_FLD60, ELA_FLD61, ELA_FLD62,
ELA_FLD63, ELA_FLD64, ELA_FLD65, ELA_FLD66, ELA_FLD67, ELA_FLD68,
ELA_FLD69, ELA_FLD70, ELA_FLD71, ELA_FLD72, ELA_FLD73, ELA_FLD74,
ELA_FLD75, ELA_FLD76, ELA_FLD77, ELA_FLD78, ELA_FLD79, ELA_FLD80,
ELA_FLD81, ELA_FLD82, ELA_FLD83, ELA_FLD84, ELA_FLD85, ELA_FLD86,
ELA_FLD87, ELA_FLD88, ELA_FLD89, ELA_FLD90, ELA_FLD91, ELA_FLD92,
ELA_FLD93, ELA_FLD94, ELA_FLD95, ELA_FLD96, ELA_FLD97, ELA_FLD98,
ELA_FLD99, ELA_FLD100, ELA_FLD101, ELA_FLD102, ELA_FLD103, ELA_FLD104,
ELA_FLD105, ELA_FLD106, ELA_FLD107, ELA_FLD108, ELA_FLD109, ELA_FLD110,
ELA_FLD111, ELA_FLD112, ELA_FLD113 )
VALUES ( @sSeqno1, @sGetUser, @dGtTime, @sGetStype,
@sCertnum, @sCerttype,
null, N'ACT', @nActact, @sOrg, null, @sSeqno,
@sActevent, @nActact, @sActsupplier, dbo.F_DATE_TO_CHAR(@dActstart, N'DD-MON-YYYY' ),
@nActtime, dbo.F_DATE_TO_CHAR(@dActnewstart, N'DD-MON-YYYY' ), @sActhire, @sActordered,
@sActfixh, @sActminhours, @sActmrc, @sActtrade,
@sActshift, @nActpersons, @sActproject, @sActprojbud,
@sActroute, @nActduration, @nActnewdur, @nActmaxdur,
@nActest, @nActrem, @nActnt, @nActntrate,
@nActot, @nActotrate, @nActpurrate, @sActtask,
@sActmatlist, @sActspecial, @sActgraph, @nActlevel,
@sActorder, @nActordline, @sActordtype, @sActordrtype,
@nActschedhrs, dbo.F_DATE_TO_CHAR(@dActlatestsched, N'DD-MON-YYYY' ), @sActcompleted, @nActmatlrev,
@nActtaskrev, @nActqty, @sActuom, @sActreq,
@nActreqline, @sActsupplier_org, @sActorder_org, @sActrpc,
@sActwap, @sActtpf, @sActmanufact, @sActsyslevel,
@sActasmlevel, @sActcomplevel, @sActclass, @sActclass_org,
@nActpercomplete, @nActupdatecount, @sActSourcecode, @sActSourcesys, @sActPerresp, @sActWarranty,
@sActrelatedwo, @sActnote, @sActdefermaintenance, @sActdeferredirectmats, @sActdeferredorigwo,
@sActdeferredorigact, @sActudfchar01, @sActudfchar02, @sActudfchar03, @sActudfchar04, @sActudfchar05,
@sActudfchar06, @sActudfchar07, @sActudfchar08, @sActudfchar09, @sActudfchar10,
@sActudfchar11, @sActudfchar12, @sActudfchar13, @sActudfchar14, @sActudfchar15,
@sActudfchar16, @sActudfchar17, @sActudfchar18, @sActudfchar19, @sActudfchar20,
@sActudfchar21, @sActudfchar22, @sActudfchar23, @sActudfchar24, @sActudfchar25,
@sActudfchar26, @sActudfchar27, @sActudfchar28, @sActudfchar29, @sActudfchar30,
@nActudfnum01, @nActudfnum02 , @nActudfnum03, @nActudfnum04, @nActudfnum05,
dbo.F_DATE_TO_CHAR(@dActudfdate01, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dActudfdate02, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dActudfdate03, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dActudfdate04, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dActudfdate05, N'DD-MON-YYYY HH24:MI:SS' ),
@sActudfchkbox01, @sActudfchkbox02, @sActudfchkbox03, @sActudfchkbox04, @sActudfchkbox05)
fetch next from c_act into @sActevent, @nActact, @sActsupplier, @dActstart,
@nActtime, @dActnewstart, @sActhire, @sActordered,
@sActfixh, @sActminhours, @sActmrc, @sActtrade,
@sActshift, @nActpersons, @sActproject, @sActprojbud,
@sActroute, @nActduration, @nActnewdur, @nActmaxdur,
@nActest, @nActrem, @nActnt, @nActntrate,
@nActot, @nActotrate, @nActpurrate, @sActtask,
@sActmatlist, @sActspecial, @sActgraph, @nActlevel,
@sActorder, @nActordline, @sActordtype, @sActordrtype,
@nActschedhrs, @dActlatestsched, @sActcompleted, @nActmatlrev,
@nActtaskrev, @nActqty, @sActuom, @sActreq,
@nActreqline, @sActsupplier_org, @sActorder_org, @sActrpc,
@sActwap, @sActtpf, @sActmanufact, @sActsyslevel,
@sActasmlevel, @sActcomplevel, @sActclass, @sActclass_org,
@nActpercomplete, @nActupdatecount, @sActSourcecode, @sActSourcesys, @sActPerresp, @sActWarranty,
@sActrelatedwo, @sActnote, @sActdefermaintenance, @sActdeferredirectmats, @sActdeferredorigwo,
@sActdeferredorigact, @sActudfchar01, @sActudfchar02, @sActudfchar03,
@sActudfchar04, @sActudfchar05, @sActudfchar06, @sActudfchar07,
@sActudfchar08, @sActudfchar09, @sActudfchar10, @sActudfchar11,
@sActudfchar12, @sActudfchar13, @sActudfchar14, @sActudfchar15,
@sActudfchar16, @sActudfchar17, @sActudfchar18, @sActudfchar19,
@sActudfchar20, @sActudfchar21, @sActudfchar22, @sActudfchar23,
@sActudfchar24, @sActudfchar25, @sActudfchar26, @sActudfchar27,
@sActudfchar28, @sActudfchar29, @sActudfchar30, @nActudfnum01,
@nActudfnum02 , @nActudfnum03, @nActudfnum04, @nActudfnum05,
@dActudfdate01, @dActudfdate02, @dActudfdate03, @dActudfdate04,
@dActudfdate05, @sActudfchkbox01, @sActudfchkbox02, @sActudfchkbox03,
@sActudfchkbox04, @sActudfchkbox05
end
close c_act
deallocate c_act
declare c_evtp cursor local for select EPO_EVENT, EPO_LINE, EPO_OBJECT, EPO_OBRTYPE,
EPO_OBTYPE, EPO_POINT, EPO_POINTTYPE, EPO_ASPECT,
EPO_AVGSLOPE, EPO_COMMENT, EPO_CREATE, EPO_DATE,
EPO_FINDING, EPO_JOB, EPO_LASTSLOPE, EPO_MAX,
EPO_MAXDATE, EPO_MAXPPM, EPO_MAXSTWO, EPO_MAXTOL,
EPO_MAXTOLDATE, EPO_METHOD, EPO_MIN, EPO_MINDATE,
EPO_MINPPM, EPO_MINSTWO, EPO_MINTOL, EPO_MINTOLDATE,
EPO_RESULT, EPO_RRESULT, EPO_STWO, EPO_TYPE,
EPO_UOM, EPO_VALUE, EPO_OBJECT_ORG, EPO_INSPECTOR,
EPO_LOCATION, EPO_CLASS, EPO_CLASS_ORG, EPO_CODE,
EPO_ORIGEVENT, EPO_NIDATEMINCRIT, EPO_NIDATEMINTOL, EPO_NIDATEMAXCRIT,
EPO_NIDATEMAXTOL, EPO_ASSESSEDSLOPE, EPO_VARCHAR1, EPO_VARCHAR2,
EPO_VARCHAR3, EPO_VARCHAR4, EPO_VARCHAR5, EPO_VARCHAR6,
EPO_VARCHAR7, EPO_VARCHAR8, EPO_VARCHAR9, EPO_VARCHAR10,
EPO_VARNUM1, EPO_VARNUM2, EPO_VARNUM3, EPO_VARNUM4,
EPO_VARNUM5, EPO_VARNUM6, EPO_VARNUM7, EPO_VARNUM8,
EPO_VARNUM9, EPO_VARNUM10, EPO_VARDATE1, EPO_VARDATE2,
EPO_VARDATE3, EPO_VARDATE4, EPO_VARDATE5, EPO_CONFRATING,
EPO_PARENT, EPO_COMPLETED, EPO_PROCESSED, EPO_RISK,
EPO_UPDATECOUNT, EPO_CREATED, EPO_UPDATED, EPO_VARCHAR11,
EPO_VARCHAR12, EPO_VARCHAR13, EPO_VARCHAR14, EPO_VARCHAR15, EPO_VARCHAR16,
EPO_VARCHAR17, EPO_VARCHAR18, EPO_VARCHAR19, EPO_VARCHAR20, EPO_VARCHAR21,
EPO_VARCHAR22, EPO_VARCHAR23, EPO_VARCHAR24, EPO_VARCHAR25, EPO_VARCHAR26,
EPO_VARCHAR27, EPO_VARCHAR28, EPO_VARCHAR29, EPO_VARCHAR30, EPO_VARCHAR31,
EPO_VARCHAR32, EPO_VARCHAR33, EPO_VARCHAR34, EPO_VARCHAR35, EPO_VARNUM11,
EPO_VARNUM12, EPO_VARNUM13, EPO_VARNUM14, EPO_VARNUM15, EPO_VARNUM16,
EPO_VARNUM17, EPO_VARNUM18, EPO_VARNUM19, EPO_VARNUM20, EPO_VARNUM21,
EPO_VARNUM22, EPO_VARNUM23, EPO_VARNUM24, EPO_VARNUM25, EPO_VARNUM26,
EPO_VARNUM27, EPO_VARNUM28, EPO_VARNUM29, EPO_VARNUM30, EPO_VARNUM31,
EPO_VARNUM32, EPO_VARNUM33, EPO_VARNUM34, EPO_VARNUM35, EPO_VARDATE6,
EPO_VARDATE7, EPO_VARDATE8, EPO_VARDATE9, EPO_VARDATE10, EPO_VARDATE11,
EPO_VARDATE12, EPO_VARDATE13, EPO_VARDATE14, EPO_VARDATE15
FROM r5eventpoints
WHERE epo_event = @sCode
open c_evtp
fetch next from c_evtp into @sEpoevent, @nEpoline, @sEpoobject, @sEpoobrtype,
@sEpoobtype, @sEpopoint, @sEpopointtype, @sEpoaspect,
@nEpoavgslope, @sEpocomment, @sEpocreate, @dEpodate,
@sEpofinding, @sEpojob, @nEpolastslope, @nEpomax,
@dEpomaxdate, @sEpomaxppm, @sEpomaxstwo, @nEpomaxtol,
@dEpomaxtoldate, @sEpomethod, @nEpomin, @dEpomindate,
@sEpominppm, @sEpominstwo, @nEpomintol, @dEpomintoldate,
@sEporesult, @sEporresult, @sEpostwo, @sEpotype,
@sEpouom, @nEpovalue, @sEpoobject_org, @sEpoinspector,
@sEpolocation, @sEpoclass, @sEpoclass_org, @sEpocode,
@sEpoorigevent, @dEponidatemincrit, @dEponidatemintol, @dEponidatemaxcrit,
@dEponidatemaxtol, @nEpoassessedslope, @sEpovarchar1, @sEpovarchar2,
@sEpovarchar3, @sEpovarchar4, @sEpovarchar5, @sEpovarchar6,
@sEpovarchar7, @sEpovarchar8, @sEpovarchar9, @sEpovarchar10,
@nEpovarnum1, @nEpovarnum2, @nEpovarnum3, @nEpovarnum4,
@nEpovarnum5, @nEpovarnum6, @nEpovarnum7, @nEpovarnum8,
@nEpovarnum9, @nEpovarnum10, @dEpovardate1, @dEpovardate2,
@dEpovardate3, @dEpovardate4, @dEpovardate5, @sEpoconfrating,
@sEpoparent, @sEpocompleted, @sEpoprocessed, @sEporisk,
@nEpoupdatecount, @dEpoCreated, @dEpoUpdated,
@sEpovarchar11, @sEpovarchar12, @sEpovarchar13, @sEpovarchar14,
@sEpovarchar15, @sEpovarchar16, @sEpovarchar17, @sEpovarchar18,
@sEpovarchar19, @sEpovarchar20, @sEpovarchar21, @sEpovarchar22,
@sEpovarchar23, @sEpovarchar24, @sEpovarchar25, @sEpovarchar26,
@sEpovarchar27, @sEpovarchar28, @sEpovarchar29, @sEpovarchar30,
@sEpovarchar31, @sEpovarchar32, @sEpovarchar33, @sEpovarchar34, @sEpovarchar35,
@nEpovarnum11, @nEpovarnum12, @nEpovarnum13, @nEpovarnum14,
@nEpovarnum15, @nEpovarnum16, @nEpovarnum17, @nEpovarnum18,
@nEpovarnum19, @nEpovarnum20, @nEpovarnum21, @nEpovarnum22,
@nEpovarnum23, @nEpovarnum24, @nEpovarnum25, @nEpovarnum26,
@nEpovarnum27, @nEpovarnum28, @nEpovarnum29, @nEpovarnum30,
@nEpovarnum31, @nEpovarnum32, @nEpovarnum33, @nEpovarnum34, @nEpovarnum35,
@dEpovardate6, @dEpovardate7, @dEpovardate8, @dEpovardate9, @dEpovardate10,
@dEpovardate11, @dEpovardate12, @dEpovardate13, @dEpovardate14, @dEpovardate15
while @@FETCH_STATUS = 0
begin
execute @nReturn = R5O7_O7MAXSEQ @sSeqno1 OUTPUT, N'EREC', N'1', @sChk1 OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_evtp
deallocate c_evtp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETUSER @sGetUser OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_evtp
deallocate c_evtp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETSTYPE @sGetStype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_evtp
deallocate c_evtp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7GTTIME @dGtTime OUTPUT, @sOrg
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_evtp
deallocate c_evtp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYNUM @sCertnum OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_evtp
deallocate c_evtp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYTYPE @sCerttype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_evtp
deallocate c_evtp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
INSERT INTO r5elecarchive ( ELA_CODE, ELA_USER, ELA_DATE, ELA_SIGNTYPE, ELA_CERTIFYNUM, ELA_CERTIFYTYPE,
ELA_SCODE, ELA_ENTITY,ELA_ENTCODE,ELA_ENTORG, ELA_STATUS, ELA_PARENT,
ELA_FLD1, ELA_FLD2, ELA_FLD3, ELA_FLD4,
ELA_FLD5, ELA_FLD6, ELA_FLD7, ELA_FLD8,
ELA_FLD9, ELA_FLD10, ELA_FLD11, ELA_FLD12,
ELA_FLD13, ELA_FLD14, ELA_FLD15, ELA_FLD16,
ELA_FLD17, ELA_FLD18, ELA_FLD19, ELA_FLD20,
ELA_FLD21, ELA_FLD22, ELA_FLD23, ELA_FLD24,
ELA_FLD25, ELA_FLD26, ELA_FLD27, ELA_FLD28,
ELA_FLD29, ELA_FLD30, ELA_FLD31, ELA_FLD32,
ELA_FLD33, ELA_FLD34, ELA_FLD35, ELA_FLD36,
ELA_FLD37, ELA_FLD38, ELA_FLD39, ELA_FLD40,
ELA_FLD41, ELA_FLD42, ELA_FLD43, ELA_FLD44,
ELA_FLD45, ELA_FLD46, ELA_FLD47, ELA_FLD48,
ELA_FLD49, ELA_FLD50, ELA_FLD51, ELA_FLD52,
ELA_FLD53, ELA_FLD54, ELA_FLD55, ELA_FLD56,
ELA_FLD57, ELA_FLD58, ELA_FLD59, ELA_FLD60,
ELA_FLD61, ELA_FLD62, ELA_FLD63, ELA_FLD64,
ELA_FLD65, ELA_FLD66, ELA_FLD67, ELA_FLD68,
ELA_FLD69, ELA_FLD70, ELA_FLD71, ELA_FLD72,
ELA_FLD73, ELA_FLD74, ELA_FLD75, ELA_FLD76,
ELA_FLD77, ELA_FLD78, ELA_FLD79, ELA_FLD80,
ELA_FLD81, ELA_FLD82, ELA_FLD83, ELA_FLD84,
ELA_FLD85, ELA_FLD86, ELA_FLD87, ELA_FLD88,
ELA_FLD89, ELA_FLD90, ELA_FLD91, ELA_FLD92,
ELA_FLD93, ELA_FLD94, ELA_FLD95, ELA_FLD96,
ELA_FLD97, ELA_FLD98, ELA_FLD99, ELA_FLD100,
ELA_FLD101, ELA_FLD102, ELA_FLD103, ELA_FLD104,
ELA_FLD105, ELA_FLD106, ELA_FLD107, ELA_FLD108,
ELA_FLD109, ELA_FLD110, ELA_FLD111, ELA_FLD112,
ELA_FLD113, ELA_FLD114, ELA_FLD115, ELA_FLD116,
ELA_FLD117, ELA_FLD118, ELA_FLD119, ELA_FLD120,
ELA_FLD121, ELA_FLD122, ELA_FLD123, ELA_FLD124,
ELA_FLD125, ELA_FLD126, ELA_FLD127, ELA_FLD128,
ELA_FLD129, ELA_FLD130, ELA_FLD131, ELA_FLD132,
ELA_FLD133, ELA_FLD134, ELA_FLD135, ELA_FLD136,
ELA_FLD137, ELA_FLD138, ELA_FLD139)
VALUES ( @sSeqno1, @sGetUser, @dGtTime, @sGetStype,
@sCertnum, @sCerttype,
null, N'INRH', @sEpocode, @sOrg, null, @sSeqno,
@sEpoevent, @nEpoline, @sEpoobject, @sEpoobrtype,
@sEpoobtype, @sEpopoint, @sEpopointtype, @sEpoaspect,
@nEpoavgslope, @sEpocomment, @sEpocreate, dbo.F_DATE_TO_CHAR(@dEpodate, N'DD-MON-YYYY HH24:MI:SS' ),
@sEpofinding, @sEpojob, @nEpolastslope, @nEpomax,
dbo.F_DATE_TO_CHAR(@dEpomaxdate, N'DD-MON-YYYY HH24:MI:SS' ),
@sEpomaxppm, @sEpomaxstwo, @nEpomaxtol,
dbo.F_DATE_TO_CHAR(@dEpomaxtoldate, N'DD-MON-YYYY HH24:MI:SS' ),
@sEpomethod, @nEpomin, dbo.F_DATE_TO_CHAR(@dEpomindate, N'DD-MON-YYYY HH24:MI:SS' ),
@sEpominppm, @sEpominstwo, @nEpomintol, dbo.F_DATE_TO_CHAR(@dEpomintoldate, N'DD-MON-YYYY HH24:MI:SS' ),
@sEporesult, @sEporresult, @sEpostwo, @sEpotype,
@sEpouom, @nEpovalue, @sEpoobject_org, @sEpoinspector,
@sEpolocation, @sEpoclass, @sEpoclass_org, @sEpocode,
@sEpoorigevent,
dbo.F_DATE_TO_CHAR(@dEponidatemincrit, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEponidatemintol, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEponidatemaxcrit, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEponidatemaxtol, N'DD-MON-YYYY HH24:MI:SS' ), @nEpoassessedslope,
@sEpovarchar1, @sEpovarchar2, @sEpovarchar3, @sEpovarchar4,
@sEpovarchar5, @sEpovarchar6, @sEpovarchar7, @sEpovarchar8,
@sEpovarchar9, @sEpovarchar10, @nEpovarnum1, @nEpovarnum2,
@nEpovarnum3, @nEpovarnum4, @nEpovarnum5, @nEpovarnum6,
@nEpovarnum7, @nEpovarnum8, @nEpovarnum9, @nEpovarnum10,
dbo.F_DATE_TO_CHAR(@dEpovardate1, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpovardate2, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEpovardate3, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpovardate4, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEpovardate5, N'DD-MON-YYYY HH24:MI:SS' ), @sEpoconfrating, @sEpoparent ,
@sEpocompleted, @sEpoprocessed, @sEporisk, @nEpoupdatecount,
@sEpovarchar11, @sEpovarchar12, @sEpovarchar13, @sEpovarchar14,
@sEpovarchar15, @sEpovarchar16, @sEpovarchar17, @sEpovarchar18,
@sEpovarchar19, @sEpovarchar20, @sEpovarchar21, @sEpovarchar22,
@sEpovarchar23, @sEpovarchar24, @sEpovarchar25, @sEpovarchar26,
@sEpovarchar27, @sEpovarchar28, @sEpovarchar29, @sEpovarchar30,
@sEpovarchar31, @sEpovarchar32, @sEpovarchar33, @sEpovarchar34, @sEpovarchar35,
@nEpovarnum11, @nEpovarnum12, @nEpovarnum13, @nEpovarnum14,
@nEpovarnum15, @nEpovarnum16, @nEpovarnum17, @nEpovarnum18,
@nEpovarnum19, @nEpovarnum20, @nEpovarnum21, @nEpovarnum22,
@nEpovarnum23, @nEpovarnum24, @nEpovarnum25, @nEpovarnum26,
@nEpovarnum27, @nEpovarnum28, @nEpovarnum29, @nEpovarnum30,
@nEpovarnum31, @nEpovarnum32, @nEpovarnum33, @nEpovarnum34, @nEpovarnum35,
dbo.F_DATE_TO_CHAR(@dEpovardate6, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpovardate7, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEpovardate8, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpovardate9, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEpovardate10, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpovardate11, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEpovardate12, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpovardate13, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEpovardate14, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpovardate15, N'DD-MON-YYYY HH24:MI:SS' ),
dbo.F_DATE_TO_CHAR(@dEpoCreated, N'DD-MON-YYYY HH24:MI:SS' ), dbo.F_DATE_TO_CHAR(@dEpoUpdated, N'DD-MON-YYYY HH24:MI:SS' ))
fetch next from c_evtp into @sEpoevent, @nEpoline, @sEpoobject, @sEpoobrtype,
@sEpoobtype, @sEpopoint, @sEpopointtype, @sEpoaspect,
@nEpoavgslope, @sEpocomment, @sEpocreate, @dEpodate,
@sEpofinding, @sEpojob, @nEpolastslope, @nEpomax,
@dEpomaxdate, @sEpomaxppm, @sEpomaxstwo, @nEpomaxtol,
@dEpomaxtoldate, @sEpomethod, @nEpomin, @dEpomindate,
@sEpominppm, @sEpominstwo, @nEpomintol, @dEpomintoldate,
@sEporesult, @sEporresult, @sEpostwo, @sEpotype,
@sEpouom, @nEpovalue, @sEpoobject_org, @sEpoinspector,
@sEpolocation, @sEpoclass, @sEpoclass_org, @sEpocode,
@sEpoorigevent, @dEponidatemincrit, @dEponidatemintol, @dEponidatemaxcrit,
@dEponidatemaxtol, @nEpoassessedslope, @sEpovarchar1, @sEpovarchar2,
@sEpovarchar3, @sEpovarchar4, @sEpovarchar5, @sEpovarchar6,
@sEpovarchar7, @sEpovarchar8, @sEpovarchar9, @sEpovarchar10,
@nEpovarnum1, @nEpovarnum2, @nEpovarnum3, @nEpovarnum4,
@nEpovarnum5, @nEpovarnum6, @nEpovarnum7, @nEpovarnum8,
@nEpovarnum9, @nEpovarnum10, @dEpovardate1, @dEpovardate2,
@dEpovardate3, @dEpovardate4, @dEpovardate5, @sEpoconfrating,
@sEpoparent, @sEpocompleted, @sEpoprocessed, @sEporisk,
@nEpoupdatecount, @dEpoCreated, @dEpoUpdated,
@sEpovarchar11, @sEpovarchar12, @sEpovarchar13, @sEpovarchar14,
@sEpovarchar15, @sEpovarchar16, @sEpovarchar17, @sEpovarchar18,
@sEpovarchar19, @sEpovarchar20, @sEpovarchar21, @sEpovarchar22,
@sEpovarchar23, @sEpovarchar24, @sEpovarchar25, @sEpovarchar26,
@sEpovarchar27, @sEpovarchar28, @sEpovarchar29, @sEpovarchar30,
@sEpovarchar31, @sEpovarchar32, @sEpovarchar33, @sEpovarchar34, @sEpovarchar35,
@nEpovarnum11, @nEpovarnum12, @nEpovarnum13, @nEpovarnum14,
@nEpovarnum15, @nEpovarnum16, @nEpovarnum17, @nEpovarnum18,
@nEpovarnum19, @nEpovarnum20, @nEpovarnum21, @nEpovarnum22,
@nEpovarnum23, @nEpovarnum24, @nEpovarnum25, @nEpovarnum26,
@nEpovarnum27, @nEpovarnum28, @nEpovarnum29, @nEpovarnum30,
@nEpovarnum31, @nEpovarnum32, @nEpovarnum33, @nEpovarnum34, @nEpovarnum35,
@dEpovardate6, @dEpovardate7, @dEpovardate8, @dEpovardate9, @dEpovardate10,
@dEpovardate11, @dEpovardate12, @dEpovardate13, @dEpovardate14, @dEpovardate15
end
close c_evtp
deallocate c_evtp
declare c_etp cursor local for select ETP_EVENT, ETP_OBJECT, ETP_OBJECT_ORG, ETP_SEQ, ETP_TESTPOINT,
ETP_TESTPOINTUOM, ETP_DEVICETOLFROM, ETP_DEVICETOLTO, ETP_STANDARD, ETP_STANDARDUOM,
ETP_OUTPUT, ETP_OUTPUTUOM, ETP_DEVICEREADING, ETP_DEVIATION, ETP_STATUS,
ETP_DEVICETOLFROMAL, ETP_DEVICETOLTOAL, ETP_STANDARDAL, ETP_STANDARDUOMAL, ETP_OUTPUTAL,
ETP_OUTPUTUOMAL, ETP_DEVICEREADINGAL, ETP_DEVIATIONAL, ETP_STATUSAL, ETP_COMMENTS,
ETP_NOTUSED, ETP_UPDATECOUNT, ETP_UPDATED, ETP_SQLIDENTITY
FROM r5evttestpoints
WHERE etp_event = @sCode
open c_etp
fetch next from c_etp into @sEvent_etp, @sObject_etp, @sObject_org_etp, @nSeq, @nTestpoint_etp,
@sTestpointuom_etp, @nDevicetolfrom_etp, @nDevicetolto_etp, @nStandard_etp, @sStandarduom_etp,
@nOutput_etp, @sOutputuom_etp, @nDevicereading_etp, @nDeviation_etp, @sStatus_etp,
@nDevicetolfromal_etp, @nDevicetoltoal_etp, @nStandardal_etp, @sStandarduomal_etp, @nOutputal_etp,
@sOutputuomal_etp, @nDevicereadingal_etp, @nDeviational_etp, @sStatusal_etp, @sComments_etp,
@sNotused_etp, @nUpdatecount_etp, @dUpdated_etp, @nSqlidentity_etp
while @@FETCH_STATUS = 0
begin
execute @nReturn = R5O7_O7MAXSEQ @sSeqno1 OUTPUT, N'EREC', N'1', @sChk1 OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_etp
deallocate c_etp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETUSER @sGetUser OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_etp
deallocate c_etp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETSTYPE @sGetStype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_etp
deallocate c_etp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7GTTIME @dGtTime OUTPUT, @sOrg
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_etp
deallocate c_etp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYNUM @sCertnum OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_etp
deallocate c_etp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYTYPE @sCerttype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_etp
deallocate c_etp
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
INSERT INTO r5elecarchive (
ELA_CODE, ELA_USER, ELA_DATE, ELA_SIGNTYPE,
ELA_CERTIFYNUM, ELA_CERTIFYTYPE, ELA_SCODE, ELA_ENTITY,
ELA_ENTCODE, ELA_ENTORG, ELA_STATUS, ELA_PARENT,
ELA_FLD1, ELA_FLD2, ELA_FLD3, ELA_FLD4,
ELA_FLD5, ELA_FLD6, ELA_FLD7, ELA_FLD8,
ELA_FLD9, ELA_FLD10, ELA_FLD11, ELA_FLD12,
ELA_FLD13, ELA_FLD14, ELA_FLD15, ELA_FLD16,
ELA_FLD17, ELA_FLD18, ELA_FLD19, ELA_FLD20,
ELA_FLD21, ELA_FLD22, ELA_FLD23, ELA_FLD24,
ELA_FLD25, ELA_FLD26, ELA_FLD27, ELA_FLD28 )
VALUES( @sSeqno1, @sGetUser, @dGtTime, @sGetStype,
@sCertnum, @sCerttype, null, N'ETP',
@sObject_etp, @sOrg, null, @sSeqno,
@sEvent_etp, @sObject_etp, @sObject_org_etp, @nSeq,
@nTestpoint_etp, @sTestpointuom_etp, @nDevicetolfrom_etp, @nDevicetolto_etp,
@nStandard_etp, @sStandarduom_etp, @nOutput_etp, @sOutputuom_etp,
@nDevicereading_etp, @nDeviation_etp, @sStatus_etp, @nDevicetolfromal_etp,
@nDevicetoltoal_etp, @nStandardal_etp, @sStandarduomal_etp, @nOutputal_etp,
@sOutputuomal_etp, @nDevicereadingal_etp,@nDeviational_etp, @sStatusal_etp,
@sComments_etp, @sNotused_etp, @nUpdatecount_etp,
dbo.F_DATE_TO_CHAR( @dUpdated_etp, N'DD-MON-YYYY HH24:MI:SS' ))
fetch next from c_etp into @sEvent_etp, @sObject_etp, @sObject_org_etp, @nSeq, @nTestpoint_etp,
@sTestpointuom_etp, @nDevicetolfrom_etp, @nDevicetolto_etp, @nStandard_etp, @sStandarduom_etp,
@nOutput_etp, @sOutputuom_etp, @nDevicereading_etp, @nDeviation_etp, @sStatus_etp,
@nDevicetolfromal_etp, @nDevicetoltoal_etp, @nStandardal_etp, @sStandarduomal_etp, @nOutputal_etp,
@sOutputuomal_etp, @nDevicereadingal_etp, @nDeviational_etp, @sStatusal_etp, @sComments_etp,
@sNotused_etp, @nUpdatecount_etp, @dUpdated_etp, @nSqlidentity_etp
end
close c_etp
deallocate c_etp
declare c_est cursor local for select EST_PK, EST_EVENT, EST_PART, EST_PART_ORG, EST_OBJECT,
EST_OBJECT_ORG, EST_LOT, EST_NEXTCALDATE, EST_UPDATECOUNT, EST_UPDATED,
EST_SQLIDENTITY
FROM r5evtstandards
WHERE est_event = @sCode
open c_est
fetch next from c_est into @nPk_est, @sEvent_est, @sPart_est, @sPart_org_est, @sObject_est,
@sObject_org_est, @sLot_est, @dNextcaldate_est, @nUpdatecount_est, @dUpdated_est,
@nSqlidentity_est
while @@FETCH_STATUS = 0
begin
execute @nReturn = R5O7_O7MAXSEQ @sSeqno1 OUTPUT, N'EREC', N'1', @sChk1 OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_est
deallocate c_est
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETUSER @sGetUser OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_est
deallocate c_est
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETSTYPE @sGetStype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_est
deallocate c_est
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7GTTIME @dGtTime OUTPUT, @sOrg
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_est
deallocate c_est
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYNUM @sCertnum OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_est
deallocate c_est
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
execute @nReturn = O7ESIGN_GETCERTIFYTYPE @sCerttype OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_est
deallocate c_est
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
INSERT INTO r5elecarchive (
ELA_CODE, ELA_USER, ELA_DATE, ELA_SIGNTYPE,
ELA_CERTIFYNUM, ELA_CERTIFYTYPE, ELA_SCODE, ELA_ENTITY,
ELA_ENTCODE, ELA_ENTORG, ELA_STATUS, ELA_PARENT,
ELA_FLD1, ELA_FLD2, ELA_FLD3, ELA_FLD4,
ELA_FLD5, ELA_FLD6, ELA_FLD7, ELA_FLD8,
ELA_FLD9, ELA_FLD10 )
VALUES( @sSeqno1, @sGetUser, @dGtTime, @sGetStype,
@sCertnum, @sCerttype, null, N'EST',
@nPk_est, @sOrg, null, @sSeqno,
@nPk_est, @sEvent_est, @sPart_est, @sPart_org_est,
@sObject_est, @sObject_org_est, @sLot_est, dbo.F_DATE_TO_CHAR( @dNextcaldate_est, N'DD-MON-YYYY' ),
@nUpdatecount_est,
dbo.F_DATE_TO_CHAR( @dUpdated_est, N'DD-MON-YYYY HH24:MI:SS' ))
fetch next from c_est into @nPk_est, @sEvent_est, @sPart_est, @sPart_org_est, @sObject_est,
@sObject_org_est, @sLot_est, @dNextcaldate_est, @nUpdatecount_est, @dUpdated_est,
@nSqlidentity_est
end
close c_est
deallocate c_est
execute @nReturn = O7ARCHIVE N'EVNT', @sCode, @sOrg, @sSeqno
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
end
end
set @sObjtemp = @sObject + N'#' + @sObject_org
set @sPpmtemp = @sPpm + N'#' + cast( @nPpmrev as nvarchar(38) )
if coalesce( @sObject, N'x' ) <> coalesce( @sOldObject, N'x' ) or
coalesce( @sStandwork, N'x' ) <> coalesce( @sOldStandwork, N'x' ) or
coalesce( @nPpmrev, -1) <> coalesce( @nOldPpmrev, -1)
begin
if coalesce( @nPpmrev, -1) <> coalesce( @nOldPpmrev, -1)
begin
declare c_dae cursor local for SELECT dae_document, doc_rtype
FROM r5documents, r5docentities
WHERE dae_rentity = N'EVNT'
AND dae_code = @sCode
AND dae_document = doc_code
open c_dae
fetch next from c_dae into @sDaeDocument, @sDaeRtype
while @@fetch_status = 0
begin
DELETE FROM r5docentities
WHERE dae_code = @sCode
AND dae_rentity = N'EVNT'
AND dae_document = @sDaeDocument
IF @sDaeRtype = N'D'
DELETE FROM r5documents
WHERE doc_code = @sDaeDocument
fetch next from c_dae into @sDaeDocument, @sDaeRtype
end
close c_dae
deallocate c_dae
end
execute @nReturn = O7COPYDOCS @sCode, @sOrg, N'EVNT', @sObjtemp, @sPpmtemp, @sStandwork, NULL, @sCheckresult OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
begin
close c_ins_del
deallocate c_ins_del
drop table #tEvents_posupd_evt
rollback transaction
return
end
end
fetch next from c_ins_del into @sCode, @sDesc, @sType, @sRtype, @dDate,
@sStatus, @sRstatus, @sMrc, @sLtype, @sLocation,
@sCostcode, @sPrinted, @sProject, @sProjbud, @sObtype,
@sObrtype, @sObject, @sStandwork, @sPpm, @nFreq,
@sIsstype, @sMeter, @nMeterdue, @sClass, @sRoute,
@sSession, @nSchedno, @sPlanprio, @sFixed, @nSlack,
@dTarget, @dNewtarget, @dEarlystart, @dLateend, @dMustend,
@dLastplan, @nDuration, @nNewdur, @sPlanstatus, @sParent,
@sDepend, @sOrigin, @sReqm, @sCause, @sAction,
@sJobtype, @sPriority, @dReported, @dStart, @dMatavail,
@dDue, @dCompleted, @nDowntime, @nLabtotal, @nMattotal,
@sShift, @nFailureusage, @nMeterreading, @sMetuom, @nMaxcost,
@sScreener, @sRoutedfrom, @sRoutereason, @sWarranty, @dOkwinend,
@dNearwinstart, @dGenwinstart, @nOkwinendval, @nNearwinbegval, @nGenwinbegval,
@sRoutestatus, @sRouterstatus, @nAcd, @sRejectreason, @sEnteredby,
@sFailure, @nDowntimehrs, @sSafety, @sObjcriticality, @sMpproj,
@nTransorgid, @sTranscode, @nTransgroup, @sCn, @sReopened,
@nPpopk, @nPpmrev, @nRouterev, @sOrg, @sClass_org,
@sObject_org, @sLocation_org, @sSchedgrp, @dRequeststart, @dSchedend,
@dRequestend, @sMastercal, @nStep, @nSeq, @sTrigevent,
@sOldrstatus, @sOldstatus, @sNewrstatus, @sNewstatus, @sConflict,
@sConflictresolved, @sConflictdesc, @sPackage, @sCode_alias, @dCreated,
@sCreatedby, @dUpdated, @sUpdatedby, @sContname, @sContphone,
@sContemail, @sContnotes, @sFollowup, @nUpdatecount, @sServicerequest,
@sPerson, @sPrint, @sBillable, @sPerioduom, @nMeterdue2,
@sMetuom2, @sRouteparent, @nIdentity,
@sSyslevel, @sSourcecode, @sSourcesystem,
@dInterface, @dLastcal, @dSopeffective, @sCalstatus,
@nINCREMENT, @nPRECISION, @sPIDNO, @sPIDDRAWING, @nSERVICEPERC,
@sSOP, @sSRQCALLNAME, @sSRQCUSTOMER, @sSRQLEVEL1,
@sFirstbill, @sJecategory, @sJesource, @sGltransferflag,@dGltransfer,
@sMultiequip, @sAgreement, @sSigpb, @sSigrb, @dLasttimepb, @dLasttimerb,
@sOldCode, @sOldDesc, @sOldType, @sOldRtype, @dOldDate,
@sOld_Status, @sOld_Rstatus, @sOldMrc, @sOldLtype, @sOldLocation,
@sOldCostcode, @sOldPrinted, @sOldProject, @sOldProjbud, @sOldObtype,
@sOldObrtype, @sOldObject, @sOldStandwork, @sOldPpm, @nOldFreq,
@sOldIsstype, @sOldMeter, @nOldMeterdue, @sOldClass, @sOldRoute,
@sOldSession, @nOldSchedno, @sOldPlanprio, @sOldFixed, @nOldSlack,
@dOldTarget, @dOldNewtarget, @dOldEarlystart, @dOldLateend, @dOldMustend,
@dOldLastplan, @nOldDuration, @nOldNewdur, @sOldPlanstatus, @sOldParent,
@sOldDepend, @sOldOrigin, @sOldReqm, @sOldCause, @sOldAction,
@sOldJobtype, @sOldPriority, @dOldReported, @dOldStart, @dOldMatavail,
@dOldDue, @dOldCompleted, @nOldDowntime, @nOldLabtotal, @nOldMattotal,
@sOldShift, @nOldFailureusage, @nOldMeterreading, @sOldMetuom, @nOldMaxcost,
@sOldScreener, @sOldRoutedfrom, @sOldRoutereason, @sOldWarranty, @dOldOkwinend,
@dOldNearwinstart, @dOldGenwinstart, @nOldOkwinendval, @nOldNearwinbegval, @nOldGenwinbegval,
@sOldRoutestatus, @sOldRouterstatus, @nOldAcd, @sOldRejectreason, @sOldEnteredby,
@sOldFailure, @nOldDowntimehrs, @sOldSafety, @sOldObjcriticality, @sOldMpproj,
@nOldTransorgid, @sOldTranscode, @nOldTransgroup, @sOldCn, @sOldReopened,
@nOldPpopk, @nOldPpmrev, @nOldRouterev, @sOldOrg, @sOldClass_org,
@sOldObject_org, @sOldLocation_org, @sOldSchedgrp, @dOldRequeststart, @dOldSchedend,
@dOldRequestend, @sOldMastercal, @nOldStep, @nOldSeq, @sOldTrigevent,
@sOldOldrstatus, @sOldOldstatus, @sOldNewrstatus, @sOldNewstatus, @sOldConflict,
@sOldConflictresolved, @sOldConflictdesc, @sOldPackage, @sOldCode_alias, @dOldCreated,
@sOldCreatedby, @dOldUpdated, @sOldUpdatedby, @sOldContname, @sOldContphone,
@sOldContemail, @sOldContnotes, @sOldFollowup, @nOldUpdatecount, @sOldServicerequest,
@sOldPerson, @sOldPrint, @sOldBillable, @sOldPerioduom, @nOldMeterdue2,
@sOldMetuom2, @sOldRouteparent, @dOldInterface,
@dOldLasttimepb, @dOldLasttimerb, @dCompletedtrunc, @sPerformedby, @sReviewedby,
@nMeterinterval, @nMeterinterval2,
@sWorkaddress, @dPfpromisedate, @dTfpromisedate, @dTfdatecompleted, @sEquipmentusability,
@sEquipmentusability_org, @sProvider, @sProvider_org,
@sServiceproblem, @sServiceproblem_org, @sServicecategory,
@sServicecategory_org, @sUdfchar01,
@sUdfchar02, @sUdfchar03, @sUdfchar04, @sUdfchar05,
@sUdfchar06, @sUdfchar07, @sUdfchar08, @sUdfchar09,
@sUdfchar10, @sUdfchar11, @sUdfchar12, @sUdfchar13,
@sUdfchar14, @sUdfchar15, @sUdfchar16, @sUdfchar17,
@sUdfchar18, @sUdfchar19, @sUdfchar20, @sUdfchar21,
@sUdfchar22, @sUdfchar23, @sUdfchar24, @sUdfchar25,
@sUdfchar26, @sUdfchar27, @sUdfchar28, @sUdfchar29,
@sUdfchar30, @nUdfnum01, @nUdfnum02, @nUdfnum03,
@nUdfnum04, @nUdfnum05, @dUdfdate01, @dUdfdate02,
@dUdfdate03, @dUdfdate04, @dUdfdate05, @sUdfchkbox01,
@sUdfchkbox02, @sUdfchkbox03, @sUdfchkbox04, @sUdfchkbox05,
@nFrompoint, @sFromrefdesc, @sFromgeoref, @nTopoint,
@sTorefdesc, @sTogeoref, @sPerformonweek, @nPerformonday,
@sAlert, @sProductionrequest, @nProductionrequestrev, @sProductionrequest_org,
@sProdorder, @sProdpriority, @sAccountingentity, @dProductionstart, @dProductionend,
@dLaststatusupdate, @sMp, @sMporg, @nMprev, @nMpseq, @dMeterduedate, @dMeterduedate2
end
close c_ins_del
--O7EVT.POSSTMT functionality here
declare c_posstmt cursor local for select EVT_CODE, EVT_DESC, EVT_TYPE, EVT_RTYPE, EVT_DATE,
EVT_STATUS, EVT_RSTATUS, EVT_MRC, EVT_LTYPE, EVT_LOCATION,
EVT_COSTCODE, EVT_PRINTED, EVT_PROJECT, EVT_PROJBUD, EVT_OBTYPE,
EVT_OBRTYPE, EVT_OBJECT, EVT_STANDWORK, EVT_PPM, EVT_FREQ,
EVT_ISSTYPE, EVT_METER, EVT_METERDUE, EVT_CLASS, EVT_ROUTE,
EVT_SESSION, EVT_SCHEDNO, EVT_PLANPRIO, EVT_FIXED, EVT_SLACK,
EVT_TARGET, EVT_NEWTARGET, EVT_EARLYSTART, EVT_LATEEND, EVT_MUSTEND,
EVT_LASTPLAN, EVT_DURATION, EVT_NEWDUR, EVT_PLANSTATUS, EVT_PARENT,
EVT_DEPEND, EVT_ORIGIN, EVT_REQM, EVT_CAUSE, EVT_ACTION,
EVT_JOBTYPE, EVT_PRIORITY, EVT_REPORTED, EVT_START, EVT_MATAVAIL,
EVT_DUE, EVT_COMPLETED, EVT_DOWNTIME, EVT_LABTOTAL, EVT_MATTOTAL,
EVT_SHIFT, EVT_FAILUREUSAGE, EVT_METERREADING, EVT_METUOM, EVT_MAXCOST,
EVT_SCREENER, EVT_ROUTEDFROM, EVT_ROUTEREASON, EVT_WARRANTY, EVT_OKWINEND,
EVT_NEARWINSTART, EVT_GENWINSTART, EVT_OKWINENDVAL, EVT_NEARWINBEGVAL, EVT_GENWINBEGVAL,
EVT_ROUTESTATUS, EVT_ROUTERSTATUS, EVT_ACD, EVT_REJECTREASON, EVT_ENTEREDBY,
EVT_FAILURE, EVT_DOWNTIMEHRS, EVT_SAFETY, EVT_OBJCRITICALITY, EVT_MPPROJ,
EVT_TRANSORGID, EVT_TRANSCODE, EVT_TRANSGROUP, EVT_CN, EVT_REOPENED,
EVT_PPOPK, EVT_PPMREV, EVT_ROUTEREV, EVT_ORG, EVT_CLASS_ORG,
EVT_OBJECT_ORG, EVT_LOCATION_ORG, EVT_SCHEDGRP, EVT_REQUESTSTART, EVT_SCHEDEND,
EVT_REQUESTEND, EVT_MASTERCAL, EVT_STEP, EVT_SEQ, EVT_TRIGEVENT,
EVT_OLDRSTATUS, EVT_OLDSTATUS, EVT_NEWRSTATUS, EVT_NEWSTATUS, EVT_CONFLICT,
EVT_CONFLICTRESOLVED, EVT_CONFLICTDESC, EVT_PACKAGE, EVT_CODE_ALIAS, EVT_CREATED,
EVT_CREATEDBY, EVT_UPDATED, EVT_UPDATEDBY, EVT_CONTNAME, EVT_CONTPHONE,
EVT_CONTEMAIL, EVT_CONTNOTES, EVT_FOLLOWUP, EVT_UPDATECOUNT, EVT_SERVICEREQUEST,
EVT_PERSON, EVT_PRINT, EVT_BILLABLE, EVT_PERIODUOM, EVT_METERDUE2,
EVT_METUOM2, EVT_ROUTEPARENT, EVT_SQLIDENTITY, EVT_MULTIEQUIP, oldst, oldrt
from r5events r, #tEvents_posupd_evt t
where r.evt_sqlidentity = t.rkey
open c_posstmt
fetch next from c_posstmt into @sCode, @sDesc, @sType, @sRtype, @dDate,
@sStatus, @sRstatus, @sMrc, @sLtype, @sLocation,
@sCostcode, @sPrinted, @sProject, @sProjbud, @sObtype,
@sObrtype, @sObject, @sStandwork, @sPpm, @nFreq,
@sIsstype, @sMeter, @nMeterdue, @sClass, @sRoute,
@sSession, @nSchedno, @sPlanprio, @sFixed, @nSlack,
@dTarget, @dNewtarget, @dEarlystart, @dLateend, @dMustend,
@dLastplan, @nDuration, @nNewdur, @sPlanstatus, @sParent,
@sDepend, @sOrigin, @sReqm, @sCause, @sAction,
@sJobtype, @sPriority, @dReported, @dStart, @dMatavail,
@dDue, @dCompleted, @nDowntime, @nLabtotal, @nMattotal,
@sShift, @nFailureusage, @nMeterreading, @sMetuom, @nMaxcost,
@sScreener, @sRoutedfrom, @sRoutereason, @sWarranty, @dOkwinend,
@dNearwinstart, @dGenwinstart, @nOkwinendval, @nNearwinbegval, @nGenwinbegval,
@sRoutestatus, @sRouterstatus, @nAcd, @sRejectreason, @sEnteredby,
@sFailure, @nDowntimehrs, @sSafety, @sObjcriticality, @sMpproj,
@nTransorgid, @sTranscode, @nTransgroup, @sCn, @sReopened,
@nPpopk, @nPpmrev, @nRouterev, @sOrg, @sClass_org,
@sObject_org, @sLocation_org, @sSchedgrp, @dRequeststart, @dSchedend,
@dRequestend, @sMastercal, @nStep, @nSeq, @sTrigevent,
@sOldrstatus, @sOldstatus, @sNewrstatus, @sNewstatus, @sConflict,
@sConflictresolved, @sConflictdesc, @sPackage, @sCode_alias, @dCreated,
@sCreatedby, @dUpdated, @sUpdatedby, @sContname, @sContphone,
@sContemail, @sContnotes, @sFollowup, @nUpdatecount, @sServicerequest,
@sPerson, @sPrint, @sBillable, @sPerioduom, @nMeterdue2,
@sMetuom2, @sRouteparent, @nIdentity, @sMultiequip, @sOldst, @sOldrt
while @@FETCH_STATUS = 0
begin
insert into R5EVENTS_PASSTHROUGH( procname, mode, oldrstatus, code, ppm, ppmrev, rstatus, freq, object, object_org, perioduom, metuom, metuom2, status,
route, ppopk, routeparent, multiequip, completed, reqm, action, cause, failure, isstype, oldroute, dateinserted, reported )
values ( N'O7POSEVT', N'UPD', @sOldst, @sCode, @sPpm, @nPpmrev,
@sRstatus, @nFreq, @sObject, @sObject_org, @sPerioduom,
@sMetuom, @sMetuom2, @sStatus, @sRoute, @nPpopk, @sRouteparent, @sMultiequip, @dCompleted,
@sReqm,@sAction,@sCause,@sFailure, @sIsstype, @sOldrt, @dToday, @dReported )
fetch next from c_posstmt into @sCode, @sDesc, @sType, @sRtype, @dDate,
@sStatus, @sRstatus, @sMrc, @sLtype, @sLocation,
@sCostcode, @sPrinted, @sProject, @sProjbud, @sObtype,
@sObrtype, @sObject, @sStandwork, @sPpm, @nFreq,
@sIsstype, @sMeter, @nMeterdue, @sClass, @sRoute,
@sSession, @nSchedno, @sPlanprio, @sFixed, @nSlack,
@dTarget, @dNewtarget, @dEarlystart, @dLateend, @dMustend,
@dLastplan, @nDuration, @nNewdur, @sPlanstatus, @sParent,
@sDepend, @sOrigin, @sReqm, @sCause, @sAction,
@sJobtype, @sPriority, @dReported, @dStart, @dMatavail,
@dDue, @dCompleted, @nDowntime, @nLabtotal, @nMattotal,
@sShift, @nFailureusage, @nMeterreading, @sMetuom, @nMaxcost,
@sScreener, @sRoutedfrom, @sRoutereason, @sWarranty, @dOkwinend,
@dNearwinstart, @dGenwinstart, @nOkwinendval, @nNearwinbegval, @nGenwinbegval,
@sRoutestatus, @sRouterstatus, @nAcd, @sRejectreason, @sEnteredby,
@sFailure, @nDowntimehrs, @sSafety, @sObjcriticality, @sMpproj,
@nTransorgid, @sTranscode, @nTransgroup, @sCn, @sReopened,
@nPpopk, @nPpmrev, @nRouterev, @sOrg, @sClass_org,
@sObject_org, @sLocation_org, @sSchedgrp, @dRequeststart, @dSchedend,
@dRequestend, @sMastercal, @nStep, @nSeq, @sTrigevent,
@sOldrstatus, @sOldstatus, @sNewrstatus, @sNewstatus, @sConflict,
@sConflictresolved, @sConflictdesc, @sPackage, @sCode_alias, @dCreated,
@sCreatedby, @dUpdated, @sUpdatedby, @sContname, @sContphone,
@sContemail, @sContnotes, @sFollowup, @nUpdatecount, @sServicerequest,
@sPerson, @sPrint, @sBillable, @sPerioduom, @nMeterdue2,
@sMetuom2, @sRouteparent, @nIdentity, @sMultiequip, @sOldst, @sOldrt
end
close c_posstmt
deallocate c_posstmt
/*====== changes for 38145 enahancement ===========*/
IF @sRstatus IN ( N'Q', N'R' ) AND @sOld_Rstatus = @sRstatus AND (@sOldObject != @sObject OR @sOldObject_org != @sObject_org)
begin
EXECUTE @nReturn = o7gterpe @sOldObject, @sOldObject_org, N'-', @sResobj OUTPUT, @sResorg OUTPUT
declare objresources cursor local for select ors_resource from r5objresources where ors_object = @sResobj and ors_object_org = @sResorg
open objresources
fetch next from objresources into @sResource
while @@FETCH_STATUS = 0
begin
DELETE FROM r5eventresources WHERE ere_event = @sCode AND ere_resource = @sResource
fetch next from objresources into @sResource
end
close objresources
deallocate objresources
execute @nReturn = O7DFLT @nIcr OUTPUT, N'INCRLINO', @sChk OUTPUT
if @@ERROR <> 0 or @nReturn <> 0
return
EXECUTE @nReturn = o7gterpe @sObject, @sObject_org, N'-', @sResobj OUTPUT, @sResorg OUTPUT
IF @sResobj IS NOT NULL AND @sResorg IS NOT NULL
BEGIN
SET @nSeq = 0
DECLARE c_ors CURSOR LOCAL FOR
SELECT ors_resource, ors_cooldowntime, ors_warmuptime, ors_active
FROM r5objresources
WHERE ors_object = @sResobj
AND ors_object_org = @sResorg
AND ors_active = N'+'
OPEN c_ors
FETCH NEXT FROM c_ors INTO @sResource, @nCooldowntime, @nWarmuptime, @sActive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nSeq = @nSeq + 1
INSERT INTO r5eventresources( ere_event, ere_resource, ere_cooldowntime,
ere_warmuptime, ere_operationseq, ere_active )
VALUES( @sCode, @sResource, @nCooldowntime,
@nWarmuptime, @nSeq * ISNULL( @nIcr, 1 ), @sActive )
FETCH NEXT FROM c_ors INTO @sResource, @nCooldowntime, @nWarmuptime, @sActive
END
CLOSE c_ors
END
END
/*====== End of changes for 38145 enahancement ===========*/
end
drop table #tEvents_posupd_evt
deallocate c_ins_del
end
secondly your trigger should be like this
ALTER TRIGGER [dbo].[R5EVENTS_TRIGGER2]
ON [dbo].[R5EVENTS]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE R5OBJECTS
SET R5OBJECTS.OBJ_UDFCHAR07 = INSERTED.EVT_REQM, R5OBJECTS.OBJ_UDFCHAR09 = INSERTED.EVT_COMPLETED
FROM INSERTED, R5OBJECTS
WHERE INSERTED.EVT_OBJECT = R5OBJECTS.OBJ_CODE AND INSERTED.EVT_STATUS = 'C'
END
BEGIN
UPDATE R5EVENTS
SET R5EVENTS.EVT_UDFCHAR01 = INSERTED.EVT_UPDATEDBY
FROM R5EVENTS, INSERTED
WHERE R5EVENTS.EVT_CODE = INSERTED.EVT_CODE
END
ASKER
Hi Brichsoft
Thanks for your speedy reply
Will check out the link and hopefully understand it
The second part you are correct should only pick up from changed row
what do I need to do to ensure it updated the data in line with the changed row?
Gordon
Thanks for your speedy reply
Will check out the link and hopefully understand it
The second part you are correct should only pick up from changed row
what do I need to do to ensure it updated the data in line with the changed row?
Gordon
In trigger, only changed row are stored in inserted/deleted table.
when you update any data, there will be two tables inside trigger.(That u know it)
Deleted - Your old row is stored in this table.
Inserted - Your new updated row will store in this table.
e.g.
Table1
col1 col2
abc 12
pqr 23
i have table with two columns
when i run - update table1 set col1 = 'TEST' where col2 = 12
data of abc value of col1 will be stored in deleted
data of TEST value will be stored in inserted
ASKER
Hi Brichsoft
Well the updated statement seens to have cured both issues, will carry on with some more test and come back to you
Gordon
Well the updated statement seens to have cured both issues, will carry on with some more test and come back to you
Gordon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good input
For your 1st Q.
- check out this link.you may need to set which trigger needs to fire first.
http://www.mssqltips.com/tip.asp?tip=1409
For your 2nd Q.
- You are not using inserted / deleted table in trigger.Ideally trigger should only on changed row.