Generic Model 601-Archive Value Triggered

Generic Models 600, 601, 602, and 603 are used to trigger the calling of a custom SQL Stored Procedure. The custom Stored Procedure allows the flexibility to use Result Sets to create, update, and delete data in the Plant Applications database. See the section on Result Sets for further explanation of their features.

Model 601 Description

When the historian tag value selected for the trigger tag changes, the model calls the defined stored procedure. The stored procedure has specific parameters that must be defined. The model passes data into those parameters and receives a message indicating success or failure. Historian values can also be passed into the stored procedure. The sampling type is a numeric value and can be found in the Sampling_Type table in the Plant Applications SQL database. Result Set queries are created in SQL and the Event Manager Service handles these Result Sets as real time messages in Plant Applications. For information on result sets, see Result Sets.

Model 601 Properties

The following Model properties are set up using the Plant Applications Administrator:

Property	Description
Maximum Run Time (Seconds)	optional: Type the number of seconds you want the model to run. The default is 0 (zero), which means the model will not time out. Typically, you will want to limit the run time of the model only if troubleshooting the model (for example, one of the stored procedures is in an infinite loop).
Model Processing Group	Used for multithreading. See the Multithreading topic for more information.
TriggerTag	Tag used to trigger the calling of the Stored Procedure.
Local SP Name	Stored Procedure called when the model is triggered.
Tag#1 (Optional)	Tag#1 used to pass values into stored procedure.
Tag#1 Sampling Type (Optional)	The Sampling Type used to acquire a value for Tag#1. The Sampling Type is a numeric value and can be found in the Sampling_Type table.
Tag#......(Optional)	Values can be retrieved for up to 50 Tags (Optional)

Model 601 Stored Procedure Parameters

The following parameters are required at the beginning of the Stored Procedure that is called by Model 601. The stored procedure must have the same input and output parameters as defined and the parameter variables can be named whatever is desired but the order must be maintained. Up to 100 Historian Tag values can be passed into the Stored Procedure.

SQL Variable Name	Parameter Description
@ReturnStatus int OUTPUT,	Flag to indicate Success or Failure (1-Success,0-Failure)
@ReturnMessage varchar(255) OUTPUT,	Error Message to Write to Log File
@EC_Id int,	Input to the Stored Procedure indicating the EC_Id of the model from the Event_Configuration table.
@TriggerTimestamp datetime,	The New Time of the Triggering Tag
@Hist1Value varchar(30),	The Value For Tag 1 (Optional)
@Hist2Value varchar(30),	The Value For Tag 2 (Optional)
@Hist3Value varchar(30),	The Value For Tag 3 (Optional)
	Optional values can be retrieved for up to 50 Tags

To control how far back in time the Event Manager service looks for tag changes after a reload or restart of the Event Manager service, you can use Result Set 51 in your stored procedure. In the result set, use 'JumpToTime' for the ParameterName and for the ParameterValue, use the timestamp for how far back in time you want to look for tag changes. For more information, see the topic Result Set 51 in the online help.

Sample Code

/****** Object: Stored Procedure dbo.spLocal_PM1ScalEvents Script Date: 8/29/00 12:18:39 PM ******/

if exists (select * from sysobjects where id = object_id(N'[dbo].[spLocal_Model601]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[spLocal_Model601]

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

/****** Object: Stored Procedure dbo.spLocal_Model601 Script Date: 8/29/00 12:19:00 PM ******/

CREATE PROCEDURE spLocal_Model601

@ReturnStatus int OUTPUT,

@ReturnMessage varchar(255) OUTPUT,

@EC_Id int,

@TimeStamp varchar(30),

@Lot_Num varchar(30),

@Unit_Num varchar(30)

--Insert into local_info (field1,field2,field3) values (convert(varchar(30),@EC_Id),convert(varchar(30),@TimeStamp),convert(varchar(30),@Lot_Num))

-- Return

Declare

@MaxEventTime datetime,

@Event_Num varchar(30),

@UnitInfo varchar(30),

@PU_Id int,

@EUId int,

@EUTransaction_Type int,

@EUEvent_Id int,

@EUApplied_Product int,

@EUSource_Event int,

@EUEvent_Status int,

@EUConfirmed int,

@EUUser_Id int,

@EUPostUpdate int

-- Initialize variables.

Select @ReturnStatus = 1

Select @ReturnMessage = ''

Select @PU_Id = NULL

Select @PU_Id = PU_Id, @UnitInfo = Extended_Info From Event_Configuration Where EC_Id = @EC_Id

If (@PU_Id Is NULL)

Begin

Select @ReturnMessage = 'ECId=' + Convert(VarChar(5), @EC_Id) + '-PU_Id is not defined in Event_Configuration record - ' + Convert(varchar(5), @EC_Id)

Goto Errorc

End

/* // Events Result Set

// --------------------------------

// 0 - Result Set Type (1)

// 1 - Id (Used for ordering within the Stored Procedure)

// 2 - Transaction_Type

// 3 - Event_Id

// 4 - Event_Num

// 5 - PU_Id

// 6 - TimeStamp

// 7 - Applied_Product

// 8 - Source_Event

// 9 - Event_Status

// 10 - Confirmed

// 11 - User_Id (Added Later, May not be there)

// 12 - PostUpdate (Added Later, May not be there)

--Transaction_Type 1=Adds, 2=Updates, 3=Deletes

CREATE TABLE #EventUpdates (

EUId int,

EUTransaction_Type int,

EUEvent_Id int NULL,

EUEvent_Num Varchar(25),

EUPU_Id int,

EUTimeStamp varchar(25),

EUApplied_Product int Null,

EUSource_Event int Null,

EUEvent_Status int Null,

EUConfirmed int Null,

EUUser_Id int,

EUPostUpdate int

)

Select @EUTransaction_Type = 1

--Select @EUEvent_Id = 0

Select @EUApplied_Product = NULL

Select @EUEvent_Status = 5

Select @EUConfirmed = 1

Select @EUUser_Id = 6

Select @EUPostUpdate = 0

/**

Hot Add

Execute spServer_DBMgrUpdEvent

@EUId OUTPUT,

@NewEvent_Num, -- Event Num

@PU_Id, -- PU_Id

@EventTimestamp, -- Timestamp

@EUApplied_Product, -- Applied Product

@EUSource_Event, -- Source Id

@EUEvent_Status, -- Event Status

@EUTransaction_Type, -- @Transaction_Type int, -- 8: Input

0, -- @TransNum int, -- NewParam

@EUUser_Id, -- @UserId int, -- NewParam

Null, -- @CommentId int, -- NewParam

Null, -- @EventSubtypeId int, -- NewParam

Null, -- @TestingStatus int, -- NewParam

Null, -- @PropStartTime datetime, -- NewParam

Null, -- @PropEntryOn datetime, -- NewParam

0 -- @ReturnResultSet int -- NewParam

--Send insert to bus (post = 1, Pre = 0)

**/

Select @Event_Num = @UnitInfo + ltrim(rtrim(@Lot_Num)) + ltrim(rtrim(@Unit_Num))

--Select @NewEvent_Num = replace(replace(replace(right(convert(varchar(25),@Timestamp, 120),14), ' ',''),'-',''),':','')

Insert into #EventUpdates (EUId,EUTransaction_Type,EUEvent_Id,EUEvent_Num,EUPU_Id,EUTimeStamp,EUApplied_Product,EUSource_Event,EUEvent_Status,EUConfirmed,EUUser_Id,EUPostUpdate)

Values(@EUId,@EUTransaction_Type,@EUId,@Event_Num,@PU_Id,Convert(VarChar(30), @Timestamp, 120),@EUApplied_Product,NULL,@EUEvent_Status,@EUConfirmed,@EUUser_Id,@EUPostUpdate)

If (Select Count(EUPU_Id) From #EventUpdates) > 0

Begin

Select ResultType = 1, *

From #EventUpdates

Order By EUId

End

Drop Table #EventUpdates

Select @ReturnStatus = 1

Select @ReturnMessage = 'Event Created for: ' + @Event_Num

Return

Errorc:

Select @ReturnStatus = 0