So we do both Auto-Approved in some Organizations and Manual in others.
We do use WOTRACK and LABREP.
I would probably do the Attribute Launch Point on GENAPPRSERVRECEIPT as you suggested. This way it would not update until labor was approved and locked into place.
We don't use Parent/Child or Labor on Tasks so these are out of the mix.
In the trigger, we do combine the date/time fields then pull the Min/Max values (see below)
I'm pretty decent with SQL, but not so good with Java.
It's the script part that I'm looking for.
Here's our current trigger (it's not perfect, but worked for the last decade):
FOR INSERT, UPDATE AS
/* SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.*/
SET NOCOUNT ON
DECLARE @v_refwo VARCHAR(10),
@v_siteid VARCHAR(8),
@v_startdate DATETIME,
@v_starttime DATETIME,
@v_finishdate DATETIME,
@v_finishtime DATETIME,
@v_inserted_start DATETIME,
@v_inserted_finish DATETIME,
@v_actstart DATETIME,
@v_actfinish DATETIME,
@v_first_labtrans_start DATETIME,
@v_last_labtrans_finish DATETIME,
@v_new_start DATETIME,
@v_new_finish DATETIME,
@v_approveby VARCHAR(30)
/*Get the relevant inserted values*/
SELECT
@v_refwo = REFWO,
@v_siteid = SITEID,
@v_startdate = STARTDATE,
@v_starttime = STARTTIME,
@v_finishdate = FINISHDATE,
@v_finishtime = FINISHTIME
FROM INSERTED where @v_approveby is not null
/* Set the inserted start and finish variables*/
SET @v_inserted_start = @v_startdate + @v_starttime
SET @v_inserted_finish = @v_finishdate + @v_finishtime
/* Get the earliest start and latest finish times from existing labtrans records.*/
SELECT @v_first_labtrans_start = MIN(Convert(datetime, Convert(char(10), DBO.v_LABTRANS.STARTDATE, 103) + ' ' + Convert(char(8), DBO.v_LABTRANS.STARTTIME, 108), 103) ),
@v_last_labtrans_finish = MAX(Convert(datetime, Convert(char(10), DBO.v_LABTRANS.FINISHDATE, 103) + ' ' + Convert(char(8), DBO.v_LABTRANS.finishtime, 108), 103) )
FROM DBO.v_LABTRANS
WHERE v_LABTRANS.siteid=@v_siteid and
v_LABTRANS.REFWO = @v_refwo
GROUP BY v_LABTRANS.REFWO
/* Set the new start date. Check if the inserted start date is earlier that the earliest existing labtrans record.*/
IF @v_first_labtrans_start IS NULL OR @v_inserted_start < @v_first_labtrans_start
SET @v_new_start = @v_inserted_start ELSE
SET @v_new_start = @v_first_labtrans_start
/* Set the new finish date. Check if the inserted finish date is later that the latest existing labtrans record.*/
IF @v_last_labtrans_finish IS NULL OR @v_inserted_finish > @v_last_labtrans_finish
SET @v_new_finish = @v_inserted_start ELSE
SET @v_new_finish = @v_last_labtrans_finish
UPDATE dbo.workorder
SET actstart = dateadd(hh,-3,@v_new_start),
actfinish = dateadd(hh,-3,@v_new_finish)
from dbo.workorder
WHERE workorder.siteid = @v_siteid and workorder.wonum = @v_refwo
------------------------------
Duane (Woodie) Wood
CMMS Manager
EMCOR Government Services| Inc.
Arlington VA
571-403-8940
------------------------------
Original Message:
Sent: 09-13-2018 10:57
From: Jason Uppenborn
Subject: Set Workorder Actual Start/Finish to First and Last Labtrans Entry
Steven Shull has already given a very good answer. But if you're trying to decide which automation scripting language to choose, I recommend going with Jython / Python (no material difference.. they're both Python running on a JVM) over going with JavaScript for a number of reasons, but here are the 3 main reasons:
- When IBM's examples / Technotes / etc aren't given in both Jython and JavaScript, they tend to be in Jython. So, IBM apparently prefers Jython.
- JavaScript's "delete" keyword conflicts with the "delete()" method on many Maximo classes.
- Jython's syntax is cleaner. It has less mandatory clutter than JavaScript.
------------------------------
Blessings,
Jason Uppenborn
Maximo Consultant
Ontracks Consulting
Original Message:
Sent: 09-12-2018 14:10
From: Duane (Woodie) Wood
Subject: Set Workorder Actual Start/Finish to First and Last Labtrans Entry
Currently we use a DB Trigger (SQL) to set the Work Order Actual Start & Finish Date/Time to the First and Last Labtrans Entry for a WO.
Purpose is to identify the real actual start and actual finish of work on the WO.
Example:
WO#123456 Labor 9/10/18 9:00am - This would be the ACTSTART
WO#123456 Labor 9/11/18 2:00pm
WO#123456 Labor 9/12/18 7:00am - This would be the ACTFINISH
I want to move this into an Automation Script.
Any suggestions on the scripting to use?
------------------------------
Duane (Woodie) Wood
CMMS Manager
EMCOR Government Services| Inc.
Arlington VA
571-403-8940
------------------------------