Result Set 50 - Output File Creation

Overview

The Plant Applications File interface models allow for the export of structured or delimited data from Plant Applications into a file, and for the import of file data into Plant Applications. A Plant Applications calculation that is either time based or event based triggers the export of data, which is accomplished through calling a stored procedure (SP) from the calculation.  The SP does much of the work via its select and update queries, which gather up the information, often into temporary tables, and then writes it out to a file using Plant Applications specific Result Sets to generate the file, its name, and its format.  Imports are done by having files FTP’d to the Proficy Server and by configuring a specific model to watch a directory for a new file and specifying a stored procedure to be called by the model to handle the importing of the data into Plant Applications. 

Configuration

General configuration and specific configuration for each type of transfer for the Plant Applications File Interfaces will be performed in Plant Applications models and on variables in the Plant Applications Server using the Plant Applications Administrator.  (A Plant Applications model performs tasks according to configurable model attributes in order to accomplish a set of defined tasks.)

Startup and Shutdown

The Plant Applications File Interfaces run as a calculation inside the Calculation Manager Service or as a component or model inside of the Plant Applications Event Manager Service.  In order to start or stop the Plant Applications File Interfaces, the various models are activated or deactivated through the Plant Applications Administrator Configure Events option or by activating or deactivating the calculation.

Communication Protocol

The protocol of communication between the Plant Applications Server and other computers will be file transfer using FTP over TCP/IP.  It can be assumed that both the Proficy Server and other computers will have appropriately configured IP addresses and will have FTP services loaded, tested, and fully functional.  The actual transfer of the files can be done using the Plant Applications FTP Engine.  This is configured using the Plant Applications Administrator.  The testing of sending, retrieval, and deletion of files (Gets) using an FTP client is important to verify by using a program such as WS_FTP prior to commissioning this interface.  The Plant Applications FTP Engine is an FTP Client. 

Log Files

All messages logged by the by the Plant Applications File Interfaces are logged in the Plant Applications Calculation or Event Manager logfiles in the Proficy\Logfiles directory named EventMgr-01.Log or CalculationMgr-01.Log with the 01 being the version of the log file.  In the "Log" file there will be error messages and in the "Show" file EventMgr.Shw or CalculationMgr.Shw there is a summary of the current configuration.  By default the interface will log only error messages to the Log file including the date and time along with transaction specific data for the error.  The Show file will contain details of the current configuration of the interface including which models are activated and the model parameters.

Result Set 50 Description

Plant Applications data export to a flat file can occur when a calculation, an event, or time based trigger calls a stored procedure.  A Plant Applications calculation that is either time based or event based or an event triggers the export of data, which is accomplished through calling a stored procedure (SP) from the calculation.  The Stored Procedure does much of the work via its select and update queries, which gather up the information, often into temporary tables, and then writes it out to a file using the Plant Applications Result Set Type 50 to generate the file, its name, and its format.  The appropriate Plant Applications Service will interpret the result set and build the file.  Transfer of files to other computers is done by having files FTP’d to the other computer using the Plant Applications FTP Engine. 

INFORMATION: There is a maximum column size of 3000 characters.

Result Set 50 Output File Parameters

Order

Field Name

Values/Table Reference

0

Result Set Type

50

1

File Number

Allows the capability with one select statement.  The number of the file to include the Value in..

2

File Name

The name of the file to create for a specific file number.

3

Field Number

Not Utilized

4

Field Name

Not Utilized

5

Type

Alpha – pad the value with spaces up to the Length parameter.

Any value but Alpha (Numeric) – Gets treated like a number.  The number is formatted to the precision and the value is padded with spaces up to the Length parameter.

6

Length

Length of the Value used for padding spaces at the end.

7

Precision

Null

8

Value

The Value to be put in the file.

9

Carriage Return

Carriage Return for that line.  Yes = 1, No = 0

10

Construction Path

Drive and Directory for file construction.

11

Final Path

Drive and Directory for completed file.

12

Move Mask (NO PATH)

File mask of the file to move from the Construction Path to the Final Path.

13

Add Timestamp

0-No, 1-Short, 2-Full

See Also

Key Parts of a Result Set 50 Example

Create Table #FileOutput (

  FileNumber                  int,

  FileName                     varchar(20) NULL,

  FieldNumber                int,

  FieldName                   varchar(20),

  FieldType             varchar(20),

  FieldLength                  int,

  FieldPrecision   int NULL,

  FieldValue                    varchar(100) NULL,

  FieldCR                       int DEFAULT 0,

  FieldBuildPath  varchar(50) NULL,

  FieldFinalPath   varchar(50) NULL,

  FieldMoveMask             varchar(50) NULL,

  AddTimestamp             int        

)

Select @FileNumber = 1

Select @FileBuildPath = 'C:\Proficy\FileExport\Construction\'

Select @FileFinalPath = 'C:\Proficy\FileExport\Outgoing\'   

Select @FileNumber = @FileNumber + 1

Select @FieldNumber = 1

Select @FileName = @Prod_Code + '.SR'

Select @FieldType = ’r;Alpha’

Select @Msg = 'RECIPE ' + @Prod_Code + ';'

Select @MsgLength= len(@msg)

Select @FieldMoveMask = ’r;*.txt’

 

Insert Into #FileOutput (FileNumber, FileName, FieldNumber, FieldName, FieldType, FieldLength, FieldPrecision, FieldValue, FieldCR, FieldBuildPath, FieldFinalPath,FieldMoveMask,AddTimestamp)

      Values (@FileNumber,@FileName,@FieldNumber,@FieldName,'Alpha',@MsgLength,Null,'Value1’,1,@FileBuildPath,@FileFinalPath, @FieldMoveMask,0)

 

Insert Into #FileOutput (FileNumber, FileName, FieldNumber, FieldName, FieldType, FieldLength, FieldPrecision, FieldValue, FieldCR, FieldBuildPath, FieldFinalPath,FieldMoveMask,AddTimestamp)

      Values (@FileNumber,@FileName,@FieldNumber,@FieldName,'Alpha',1,Null, 'Value2’,1,@FileBuildPath,@FileFinalPath, @FieldMoveMask,0)

 

Insert Into #FileOutput (FileNumber, FileName, FieldNumber, FieldName, FieldType, FieldLength, FieldPrecision, FieldValue, FieldCR, FieldBuildPath, FieldFinalPath,FieldMoveMask,AddTimestamp)

      Values (@FileNumber,@FileName,@FieldNumber,@FieldName,'Alpha',1,Null, 'Value3’,1,@FileBuildPath,@FileFinalPath, @FieldMoveMask,0)

 

Drop Table #FileOutput

Select 50, * From #FileOutput Order By FileNumber, FieldNumber