GE Proficy Plant Applications ResultSets

ResultSets are the primary interface mechanism between stored procedures and the frontend in GE Proficy Plant Applications. They provide structured, real-time database updates through the messaging infrastructure rather than direct SQL operations.

See https://www.gevernova.com/software/documentation/proficy-plant-applications/

Overview

What are ResultSets?

ResultSets allow you to add real-time messages to:

Model-based custom SQL Stored Procedures
Variable Stored Procedure Calculations

Instead of directly updating the Plant Applications database with SQL INSERT and UPDATE statements, ResultSets use the Plant Applications Messaging infrastructure to:

Make database changes safely
Notify currently running Clients of changes in real-time
Trigger dependent calculations and workflows

The Challenge: No Meaningful Headers

Critical Issue: When stored procedures return ResultSets, they return only data without meaningful column headers. This makes debugging extremely difficult as developers must know the expected field order and meaning by consulting documentation or the ResultSetConfig table.

ResultSet Architecture

Configuration

Configuration Table: ResultSetConfig table defines all available ResultSet types
Format: ResultSets return data in a specific field order (Order 0, 1, 2, etc.)

Messaging System

Plant Applications employs a messaging system where ResultSets:

Provide access to the messaging infrastructure
Enable real-time client notifications
Support multi-user concurrent operations
Maintain data consistency across the application

ResultSet Types

Type 2: Variable Values ResultSet

The Variable Values ResultSet captures variable measurements and updates within Plant Applications.

Operations: Add and Update (no Delete - use NULL to effectively delete)

Field Structure:

Order	Field Name	Data Type	Source/Values	Description
0	Result Set Type	INT	Always `2`	Identifies this as Variable Values ResultSet
1	Variable Id	INT	`Tests.Var_Id`	References the variable being measured
2	Production Unit Id	INT	`Variables.PU_Id`	Production unit context
3	User Id	INT	`Tests.User_Id`	User entering/updating the value
4	Cancelled	INT	`0` = False `1` = True	Whether the test is cancelled
5	Value	VARCHAR(50)	`Tests.Result`	The actual measurement value
6	Timestamp	VARCHAR(50)	`Tests.Result_On`	When the measurement was taken
7	Transaction Type	INT	`1` = Add `2` = Update	Type of database operation
8	Update Type	INT	`0` = Pre-Update `1` = Post-Update	Update phase (affects dependent calculations)

Important Notes:

No Delete Function: To delete variable values, update the value to NULL
Post-Update Impact: When update type is post-update (1), dependent calculations won’t trigger automatically
Pre-Update Usage: Use pre-update (0) when you need dependent calculations to fire

Quality Module Usage

The Quality Module makes heavy use of Variables and Calculations that call stored procedures. The interface back to the frontend is encoded in the types of ResultSets returned by the stored procedures.

Common Quality Module Patterns:

Variable measurements (test results, inspections)
Statistical process control (SPC) calculations
Quality limits and specifications
Defect tracking and non-conformance
Sampling plans and test procedures

Alternative Approaches

HotAdds

In rare cases where:

Multiple updates occur within one stored procedure
The local stored procedure relies on having an “ID” returned before the “Next Step”

Sites may bypass ResultSets and call SpServer_DBMgr* stored procedures directly.

Pattern:

Call SpServer_DBMgr* stored procedure (bypasses ResultSet)
Followed by a post-update ResultSet
Use when simple pre-update ResultSets are insufficient

SpServer_DBMgr* stored procedures are generally used “in place of a ResultSet” for complex multi-step operations.

Best Practices for Creating Stored Procedures

When creating stored procedures that use ResultSets:

Create a table variable for the ResultSet with a field for each parameter
Allow default values for some fields where appropriate
Create local variables for each field that the ResultSet will update
Add flow control variables for program flow and error checking
Use consistent naming matching the ResultSet field order

Example Pattern

-- Create table variable for ResultSet  
DECLARE @VariableResults TABLE (  
    ResultSetType INT,  
    VariableId INT,  
    ProductionUnitId INT,  
    UserId INT,  
    Cancelled INT,  
    Value VARCHAR(50),  
    Timestamp VARCHAR(50),  
    TransactionType INT,  
    UpdateType INT  
)  
  
-- Populate with precise values  
INSERT INTO @VariableResults  
SELECT  
    2 as ResultSetType,  -- Type 2 = Variable Values  
    Var_Id,  
    PU_Id,  
    @UserId,  
    0 as Cancelled,  
    CAST(Result as VARCHAR(50)),  
    CONVERT(VARCHAR(50), GETDATE(), 121),  
    1 as TransactionType,  -- 1 = Add  
    0 as UpdateType  -- 0 = Pre-Update  
FROM Variables  
WHERE [conditions]  
  
-- Return ResultSet  
SELECT * FROM @VariableResults

Debugging ResultSets

Common Issues

Unknown Field Order: Without headers, you must know the expected field positions
Type Mismatches: Data types must match exactly what the ResultSet expects
Missing Required Fields: All fields in the expected order must be present
Null Handling: Some fields may not accept NULL values

Debugging Strategies

Consult ResultSetConfig Table: Query the database configuration
Reference Documentation: Use official GE Vernova documentation (ProficyPAResultSets.chm)
Use Test Queries: Create test stored procedures to verify field order
Add Logging: Create audit trails in custom tables for troubleshooting
Version Control: Track stored procedure changes with clear comments

Querying ResultSetConfig

-- Find ResultSet configuration  
SELECT * FROM ResultSetConfig  
WHERE ResultSetType = 2  -- Variable Values  
  
-- List all ResultSet types  
SELECT DISTINCT ResultSetType, Description  
FROM ResultSetConfig  
ORDER BY ResultSetType

Core Tables for Quality Module

Tests: Quality test results and measurements
Variables: Variable definitions and specifications
ResultSetConfig: ResultSet type definitions
Production Units: Manufacturing context
Users: User information for audit trails

Quality-Specific Tables

Quality Limits
Sampling Plans
Test Procedures
Non-Conformance Records
Statistical Control Charts

Documentation References

Official Documentation

Result Sets PDF: Plant Applications 8.2 - Result Sets
SQL Programming: Plant Applications 2023 - SQL Programming
Help File: ProficyPAResultSets.chm (included with installation)

Community Resources

Key Takeaways

ResultSets are the Interface: They encode the contract between stored procedures and the frontend
Field Order Matters: Without headers, you must know the exact field order
Real-Time Updates: ResultSets use messaging infrastructure for live updates
Quality Module Dependency: The Quality Module heavily relies on ResultSets for calculations
Documentation is Critical: Keep ResultSet field definitions documented for your team
Use Pre-Update for Cascades: Set UpdateType = 0 when you need dependent calculations to trigger

Additional Notes

Known ResultSet Types

Based on Plant Applications modules, other ResultSet types likely include:

Production Events
Downtime Records
Material Consumption
Waste Tracking
Work Order Updates
Route Management
Non-Conformance Records

Note: The complete list of ResultSet types with field definitions is available in the official Result Sets PDF documentation (46 pages). Contact GE Vernova support or consult the installed help files for comprehensive field mappings.

Version Compatibility

This documentation is based on:

Plant Applications 8.2
Plant Applications 2023
Plant Applications 2024

Field structures may vary between versions. Always consult version-specific documentation.

Explorer

GE Proficy Plant Applications ResultSets