External File Table

by vandberg published Feb 24, 2012 05:05 PM, last modified Jun 23, 2017 02:37 PM
Component Instructions

Function:

Maintain setup information for loading transactions from external systems. 

Operation:

From the menu, select:

Main Menu > UWO Student Financials > External Files > External File Table 

Select or enter the external file code. -- See Using Search Dialogs

External File Table 

Assign an External File code to each different file this system will manage.  You may assign any code you choose.

Enter a Description of each file.

For SFTP Before Processing, enter the appropriate SFTP application that transfers the files to be loaded.

Enter the File Name of the file the process will look for and load.  To handle files whose names change each time, such as those that include a date in the file name, you may enter wildcard characters.

  •  A question mark (?) will match any single character.
  • An asterisk (*) will match any number of characters.

When using wildcard characters, be sure to use enough other characters that the process only selects the files you intend it to.

While the load program PeopleSoft provides is flexible about the position of fields, it only accepts fixed-position files of a limited length.  If the external system  can not produce a suitable transaction file, the file can be reformatted by entering the appropriate values for File Layout - As Received and File Layout - Ready to Load.  Besides moving fields, this can handle converting CSV or XML files to fixed-position ones.  If no reformatting is required, enter the same value for both.

Check the Omit First Line of File box if the first line of the file should be skipped.  Use this with CSV files that contain column headings in the first line.

Check the Reverse Payment Sign box if payments in the transaction file are negative amounts.  Some external systems, including StarRez, use negative amounts for payments to indicate they are credits, but for the load program, they must be positive amounts or they will reduce the payments.

Enter  email addresses in E-Mail To where messages should be sent if there are problems loading the files.  If you enter more than one email address, separate them with semicolons (;).

To avoid loading the same file more than once, the remote files can be renamed or deleted after they have been processed by selecting that choice for Action After Processing File and entering the SFTP application that does the rename or delete in SFTP After Processing.

Enter any actions in the grid which should be performed on the specified fields.

Under Line Field, enter the name of the field as it is define in the File Layouts.

Under Line Action, enter the action which should be performed.

  • Numbers only - truncate -- Remove any characters which are not digits and truncate the field to the length in File Layout - Ready to Load.  This can be used to convert a date/time value to a date or to change a self-service User ID to an EmplID by removing the initial "W".
  • Store the field -- Store the field from the external file.  This allows reports of external transactions to include that data.  Only use this for fields that the standard load process already stores.

Old Method

External File Table 2

There are two processes which find and load external files.  The current method uses Start External File Loading (z_sfa041e) and the older method uses Find External Files (z_sfa041a).  The old method should eventually be phased out, but the set-up values for it can still be entered until that happens

  •  SFTP Before Processing, File Layout - As Received, File Layout - Ready to Load, Action After Processing File and SFTP After Processing only apply to the current method.  
  • Rename External Files and Reformat only apply to the old method.

Check Rename External Files if the file has the same name every time it is created.  The files will be renamed with the date/time as part of their name.  This allows copies of the files to be kept for reference if there are problems.

Do not check Rename External Files if the file has a different name each time it is created.  This will allow the program to compare the name to those it has already processed, to avoid loading a file more than once.

Enter the Reformat code if the file must be reformatted, otherwise leave this blank.  The positions of the fields are specified in the external file layout, so normally it should not be necessary to reformat the file.  However, the load program will not read more than 120 characters in a line, so the file must be reformatted if needed fields are past that limit.  The load program will not match EMPLID's to students if they do not have leading zeros.  Those can be added by reformatting the file.  The amount fields do not require leading zeros.  See Reformat File Setup

Additional Information:

See the system instructions -- Load External Charges 

There are a number of values that must be entered for each external system. -- See List of Values