Using the Flat File Utility

This chapter provides an overview of the Flat File utility and discusses how to:

Process inbound flat files.
Initiate file processing.
Test inbound flat file processing.

Understanding the Flat File Utility

When external systems send flat files to you for inbound transactions, you must develop complementary processes to translate incoming files into messages or translate outbound messages into files.

The flow for inbound file processing by using the Flat File utility is:

The utility receives a flat file in the form of a file layout object from an external system.

The flat file consists of either:
- The relevant data.
- An index file that contains pointers to the data.
  
  Each index file lists the names of a set of data files to be processed. These files contain the application data, which is in one of the following formats: fixed record, Comma Separated Values (CSV), or XML.
The utility reads the file that is submitted for processing:
- If the file is an index file, the Flat File utility loads the list of data files that are associated with each index file to be processed into a parameter table.
- If it is a single data file, the utility inserts the single data file into a parameter table.
  
  Note. If additional fields in the file layout are not in the message definition, the additional fields are ignored during the copying of the flat file data to the message and are not included in the message.
The utility loops through the list of data files to be processed and reads each data file.
The utility copies the row sets of the data files into the message.
The utility publishes the message.
The subscribing systems receive the message and initiate normal inbound data processing.

Processing Inbound Flat Files

You use the file layout definition to read and write from flat files.

To process inbound flat files:

Determine the necessary format of the inbound data.

If there’s an industry standard, use it for your file definition.

If there's no industry standard, create a file layout object that mirrors your message object.
Identify the inbound process and its standard message.
Analyze the vendor’s file structure and compare it to the standard message.

Questions to answer include:
- Can you use an existing message, or do you need to create a new one?
- Can the customer conform to an existing integration point, or do you need to create one (along with corresponding subscription PeopleCode)?
Create the message definition.
Create a file layout definition with the same structure as the message definition to support the vendor file format.

The hierarchical structure of the data in the File Layout Definition must match that of the message definition. For example, suppose a message has three levels: level zero, containing Record A, level one, containing records B and C, and level two, containing record D.

All file layouts that are associated with this message must also have record A in level zero, record B and C in level one, and record D in level two.

Note. The file layout does not need to contain the exact same fields as the message definition.

For every record in your file layout, add a new file field, AUDIT_ACTN, as the first field in the record (except when the field already exists in the application table).

You can associate more than one file layout to a single message. For example, vendor A may have a different number of fields than vendor B, so you may have two file layouts: one for A and one for B.

Specify the file ID uniquely to include a row in a file, which is necessary in mapping the data to its proper record. Include start and end points when dealing with more than one record in a file layout.

Note. Each record in the file layout has a file record ID attribute. Do not confuse this with the file layout ID. The file layout ID determines whether a new layout is encountered for multiple file layout processing.

When you subscribe to the message and normal inbound data processing begins, you can invoke the SetDefault PeopleCode function to set the default values for fields that were not present in the input file.
Update or create the inbound file rule pages.
Create subscription PeopleCode in PeopleSoft Application Designer to process the message.

Have the standard inbound process subscribe to and process the message normally. The standard message definition should have a subscription process that initiates the normal inbound processing for that object to which you hook your application logic to process the file data.
Test the inbound flat file processing.

Note. You can process multiple inbound flat files at one time. By specifying an inbound index file as part of the Flat File utility parameters, the system reads all input files within the index file and uses the associated file layout object and message to convert the data. Similarly, specify a wildcard in the filename in the inbound file rule component, but make sure that all files that meet the wildcard criteria correspond to the file layout and message mapping that are defined.

Initiating File Processing

This section discusses how to:

Set up inbound flat file processing.
Initiate inbound flat file processing.

Pages Used to Initiate File Processing

Page Name	Object Name	Navigation	Usage
File Inbound	EO_FILE_INBOUND	Enterprise Components, Integration Definitions, Inbound File Rule	Set up inbound flat file processing.
Inbound File	EO_FILETOMSG	Enterprise Components, Integration Definitions, Initiate Processes, Inbound File Publish	Initiate inbound flat file processing. This file-to-message processing function reads the file rowset and publishes it as a message.

Setting Up Inbound Flat File Processing

Access the File Inbound page.

File Identifier

Displays the inbound file that you are associating with the rule.

Inbound File

Enter the index file name or the data file name. Specify the full path information. The PeopleCode program uses the %filepath_absolute variable when opening the file.

Index Flag

Select to distinguish between the index and the data file.

Status

Select whether this inbound file rule is Active or Inactive. The default value is Inactive.

File Layout ID

Select a layout to associate with the file.

LUWSize (logical unit of work size)

Enter the number of level zero rows that are in each message, to limit the message size. The output message is normally determined by the MaxMessageSize system parameter.

Program Name and Section

Select a PeopleSoft Application Engine program and section to invoke when the utility finishes processing data.

Create Message Header

Select to create a header message. Use the header message as a trigger in the subscription process to initialize tables before receiving the data messages. Default value is selected.

Create Message Trailer

Select to create a trailer message. Use the trailer message as a trigger in the subscription process to indicate that all the data messages have been received. Default value is selected.

File Layout

Definition Name and Message Name

If the File Layout ID field is blank, this field should contain only one entry.

If the File Layout ID field is not blank, this scroll area must contain an entry for each file layout definition name that is specified in the inbound file.

Note. Use the wildcards * and ? for the file name but not for the directory path. The file layout and message mapping must be valid for all files that meet the wildcard criteria.

Initiating Inbound Flat File Processing

Access the Inbound File page.

Parameters

File Identifier	Select or enter the name of the file identifier that you set up in the File Inbound page. The file identifier is tied to the publish rules.
Run	Click to run this request.

Publishing a New Message

The Inbound File page runs an Application Engine process that initiates the file-to-message processing. The file-to-message processing function reads the file rowset and publishes it as a message.

If an index file exists when the inbound conversion process runs, the Application Engine program loads the list of files to be converted into a parameter table and completes a commit. The Application Engine program uses the list of files within the parameter table to restart the processing if a particular flat file fails. If a single data file is provided, then the rowset processing immediately begins.

The file publish process goes through each of the rowsets of the file layout and copies them into the message row sets.

If the audit action (AUDIT_ACTN) exists in the file, it is copied to the PSCAMA record. If the audit action does not exist in the file, the publishing process uses the default value that is specified in the file layout field property.

The Flat File utility publishes a new message when one of the following exists:

Maximum message size is exceeded.
Logical unit of work publish size is reached.
A new file layout is detected.
End of file is reached.

The Application Engine program completes a commit every time a message is published from a file. After conversion, the flat file remains in the parameter table with a status of Processed.

Note. The file layout should exactly match the message layout (excluding the PSCAMA record) and should use the same character set as that used by the file: either American National Standards Institute or Unicode.

Testing Inbound Flat File Processing

To test inbound files:

Create a sample flat file, or ask the third-party vendor for a sample flat file.
Launch the Flat File utility.
1. Through the browser, sign in to PeopleSoft Internet Architecture.
2. Select Enterprise Components, Management, Inbound File Rule.
Run the Application Engine program to convert the sample flat file to a message by running Message Monitor.

Use Message Monitor to ensure the inbound file processing created a publish message that contains the sample flat file data.
1. Verify that the standard inbound subscription process received the message and processed it into the application tables.
2. Determine whether the values become the inherited values (if you used the inherited value feature in file layout).
3. Validate that the production or staging tables loaded with the correct field values.
  
  For production tables, look in the PeopleSoft application pages.
  
  For staging tables, use either the PeopleSoft application pages or run a query by using PeopleSoft Query.
4. Ensure that the date formats conform.

File Identifier	Displays the inbound file that you are associating with the rule.
Inbound File	Enter the index file name or the data file name. Specify the full path information. The PeopleCode program uses the %filepath_absolute variable when opening the file.
Index Flag	Select to distinguish between the index and the data file.
Status	Select whether this inbound file rule is Active or Inactive. The default value is Inactive.
File Layout ID	Select a layout to associate with the file.
LUWSize (logical unit of work size)	Enter the number of level zero rows that are in each message, to limit the message size. The output message is normally determined by the MaxMessageSize system parameter.
Program Name and Section	Select a PeopleSoft Application Engine program and section to invoke when the utility finishes processing data.
Create Message Header	Select to create a header message. Use the header message as a trigger in the subscription process to initialize tables before receiving the data messages. Default value is selected.
Create Message Trailer	Select to create a trailer message. Use the trailer message as a trigger in the subscription process to indicate that all the data messages have been received. Default value is selected.