Stored Procedures
Time Metrics
When creating a serialized incident, users can click the Operational History link to select from previously entered time metrics (values) for that serial number. By default, this list consists of time values contained within XFRACAS for the serial number. If desired, you can instead pull time value data from another database for use during incident creation. XFRACAS supports this by attempting to call a stored procedure in the XFRACAS database on the fly.
Note: If you want to use this procedure for your XFRACAS implementation, please request assistance from ReliaSoft Support to enable the “XFRACAS - Run Stored Procedure for System Time Metrics” preference.
The procedure is named XFRACASGetTimeMetrics and has four arguments. The first is an incoming argument for the serial number (a string) and the second through fourth are return arguments (numbers) to store the data for the three time metrics. In the default XFRACAS system, the three time metrics are set to 0. Once the stored procedure is modified, the return_value variable must be set to 1 in order for the values to be used.
As an example, below is a very simple Oracle stored procedure that returns values from tables named SYSTEM_HOURS, SYSTEM_STARTS and SYSTEM_KWRUNHRS:
Create OR Replace PROCEDURE XFRACASGetTimeMetrics
(
Sn IN NVARCHAR2;
tm1 OUT NUMBER,
tm2 OUT NUMBER,
tm3 OUT NUMBER
RETURN_VALUE OUT NUMBER)
AS
BEGIN
tm1 := SELECT hours FROM SYSTEM_HOURS WHERE serial_num = SN;
tm2 := SELECT starts FROM SYSTEM_STARTS WHERE serial_num = SN;
tm3 := SELECT kwrunhrs FROM SYSTEM_KWRUNHRS WHERE serial_num = SN;
RETURN_VALUE := 1;
END;
Below is the SQL Server stored procedure that does the same (i.e., returns values from tables named SYSTEM_HOURS, SYSTEM_STARTS, and SYSTEM_KWRUNHRS):
CREATE PROC XFRACASGetTimeMetrics
(
@SN NVARCHAR(100),
@TM1 FLOAT OUTPUT,
@TM2 INT OUTPUT,
@TM3 FLOAT OUTPUT,
@RETURN_VALUE INT OUTPUT
)
AS
BEGIN
SET @TM1 = SELECT hours FROM SYSTEM_HOURS WHERE serial_num = @SN;
SET @TM2 = SELECT starts FROM SYSTEM_STARTS WHERE serial_num = @SN;
SET @TM3 = SELECT kwrunhrs FROM SYSTEM_KWRUNHRS WHERE serial_num = @SN;
SET @RETURN_VALUE = 1
END;
When using this functionality, if the stored procedure called succeeds and returns data, the user will see rows fetched from XFRACASGetTimeMetrics. This row can be selected like any other to use the listed time values in the incident.
Importing via External Process
As discussed in Map and Import Existing Records, importing existing data is typically handled from within the XFRACAS interface by means of XML imports. If, however, you are using an external process to bring data into the system, you will use a stored procedure to increment the unique table ID assigned to each record and the entity display ID for each transactional record. For example, you can use a stored procedure to import data from a Distributed Control System (DCS) or a Supervisory Control and Data Acquisition (SCADA) system.
The procedure is named RS_GetIDValue and has three arguments, as follows:
- The first is an incoming argument for the table name. In Oracle, this must be entered in ALL CAPITAL LETTERS.
- The second is an incoming argument for the entity ID. If there is no entity ID, you must enter NULL for this argument.
- The third is a return argument that stores the value to a specified variable.
SQL Server Templates
The following template calls the stored procedure with an entity ID. Note that you will replace TABLENAME with the actual table name and XX with the actual entity ID.
DECLARE @NextID int
BEGIN
--table_name, entity_id, @NextID OUTPUT is the variable for the id_num output
EXECUTE RS_GetIDValue 'TABLENAME',XX, @NextID OUTPUT;
PRINT @NextID;
END;
The following template calls the stored procedure without an entity ID. Note that you will replace TABLENAME with the actual table name.
DECLARE @NextID int
BEGIN
--table_name, @NextID OUTPUT is the variable for the id_num output
EXECUTE RS_GetIDValue 'TABLENAME', NULL, @NextID OUTPUT;
PRINT @NextID;
END;
Oracle Templates
The following template calls the stored procedure with an entity ID. Note that you will replace XXX with the table name in ALL CAPS, and replace x with the entity ID.
SET SERVEROUTPUT ON;VARIABLE NEWID NUMBER;
EXEC RS_GetIDValue('XXX',x, :NEWID);
BEGIN
DBMS_OUTPUT.PUT_LINE(:NEWID);
END;
The following template calls the stored procedure without an entity ID. Note that you will replace XXX with the table name in ALL CAPS.
SET SERVEROUTPUT ON;
VARIABLE NEWID NUMBER;
EXEC RS_GetIDValue('XXX',NULL, :NEWID);
BEGIN
DBMS_OUTPUT.PUT_LINE(:NEWID);
END;