Downtime Events

The Downtime result sets do not have a post-update option so you should not attempt to manually insert records and then issue a result set. If you do, you risk undoing changes that you have already made. For example, if you open an event and issue the result set and then close the event a second later before the DBMgr has had a chance to process the result set, the DBMgr will end up reopening the event when it does get around to processing the result set.

Example

DECLARE	@MachineDown     int,
	       @PUId	     int,
	       @LocationId     int,
	       @ReasonId1      int,
	       @StartTime      datetime

DECLARE @DowntimeEvents TABLE (
ResultSetType	int DEFAULT 5,
PUId	         int NULL,
SourcePUId	   int NULL,
StatusId	     int NULL,
FaultId	      int NULL,
ReasonLevel1	 int NULL,
ReasonLevel2	 int NULL,
ReasonLevel3	 int NULL,
ReasonLevel4	 int NULL,
ProdRate	     int NULL,
Duration	     float NULL,
TransType	    int Default 1,
StartTime	    varchar(50) NULL,
EndTime	      varchar(50) NULL,
TEDetId	      int NULL)
SELECT @PUId = PU_Id
FROM Prod_Units
WHERE PU_Desc = 'MyUnit'

SELECT @LocationId = PU_Id 
FROM Prod_Units
WHERE PU_Desc = 'MyLocation'

SELECT @ReasonId1 = Event_Reason_Id 
FROM Event_Reasons
WHERE Event_Reason_Name = 'MyReason'

IF @MachineDown = 1 BEGIN
-- The following opens the downtime event 
INSERT INTO @DowntimeEvents (	PUId,
                                   SourcePUId, 
                                   ReasonLevel1, 
                                   StartTime)
VALUES (@PUId,
        @LocationId, 
        @ReasonId1,
         convert(varchar(50), getdate(), 120))
END
ELSE
BEGIN
-- Get the current downtime event 
 SELECT @StartTime = Start_Time 
 FROM Timed_Event_Details 
 WHERE PU_Id = @PUId
 AND Start_Time <= getdate() 
 AND End_Time IS NULL

-- The following closes the downtime event
INSERT INTO @DowntimeEvents (   TransType,
                                PUId,
                                StartTime, 
                                EndTime)
 
VALUES (	4,
              @PUId, 
              @StartTime,
              convert(varchar(50), getdate(), 120))
END

-- Output results
SELECT	     ResultSetType, 
                 PUId,
                 SourcePUId, 
                 StatusId, 
                 FaultId, 
                 ReasonLevel, 
                 ReasonLeve2, 
                 ReasonLeve3, 
                 ReasonLeve4, 
                 ProdRate, 
                 Duration , 
                 TransType, 
                 StartTime, 
                 EndTime,
                 TEDetId 
FROM @DowntimeEvents