PCard Definition File

Use this option to create the file format for importing data from the reconciliation files received from your PCard provider. The format tells the system how records are stored in the provider's file so it can find the data required for reconciling PCard transactions.

Important
You must create a file format before you can use other PCard options. All options, except PCard Profile, must be linked to a format.

Menu Path: From the PCard Processing menu, select Reference Tables. Then, select the PCard Definition File option.

Pages Used in Setting Up File Formats

The PC Definition File option uses two pages for setting up a file format:

PCard Definition File	Displays the formats stored in your system, enables you to add and update formats, and provides access to the PCard Definition page for defining a format's record types.
PCard Definition	Enables you to add and update the individual record types that make up a file format, for example, File Header and Batch Trailer.

These pages are described in the following sections.

PCard Definition File Page

This is the initial page that displays when you select the PCard Definition File option.

Page Sections

Following are descriptions of the sections in the PCard Definition File page:

Layout Definition Information	Displays information on using the PCard Definition File page.
List (untitled)	Displays the file formats stored in your system.
General Information	Displays a format's basic data when you select the format in the List section. The General Information section is also used to enter data when you are setting up or changing a format.
Layout Record Types	Displays the record types contained in a file format. If a format has already been started or completed, the numbers to the right indicate how many records of each record type have been set up. This section also includes action buttons for creating and updating records for the different record types.

File Elements

Three elements need to be accounted for in a format: files, records, and fields. While you may be familiar with these terms, it may be helpful to review some basics:

A file is a collection of records. It can also be a collection of smaller files. For example, a PCard file can contain multiple files if transactions are stored by dates, PCard numbers, or some other criteria. These smaller files are referred to as batch files.
In addition to storing PCard transaction records, a file can contain header and trailer records at the beginning and end of the file. Usually, a header provides general information on the file's records, while a trailer summarizes its contents. Batch files usually have their own header and trailer records.
Records are divided into fields. For example, a PCard record can include data such as a PCard number, transaction date, transaction amount, merchant's name, and so forth. Each data element is stored in a different field.

Record Type Buttons

A PCard format record provides a map to the data stored in the reconciliation files received from your PCard provider. A format can include up to five types of records, as indicated by the following action buttons in the PCard Definition File page's Layout Record Types section:

File Header	Defines the fields for the file's header. A header might include your organization's account number, the transaction period's beginning and end dates, and the period's beginning balance.
Batch Header	Defines the fields for the headers in batch files, such as PCard number, transaction dates, and account number.
Detail Data	Defines the fields for individual PCard transactions, such as merchant codes and transaction dates, amounts, and descriptions.
Batch Trailer	Defines the fields for the trailer records in batch files. These fields often provide totals for a batch's transactions, such as the total number of transactions and their total charges.
File Trailer	Defines fields summarizing the file's contents, such as number of batches, number of transactions, total charges, and ending balance.

You must set up a separate record for each type of information within a record type. For example, if a file trailer includes three fields, you must use the File Trailer button to create a record for each field.

Format Examples

Following are three examples of how files can be structured. Keep in mind that the format used by your PCard provider will probably be different.

Example 1	Example 2	Example 3
File Header	File Header	File Header
Detail Data	Batch Header	Batch Header - Line 1
File Trailer	Detail Data	Batch Header - Line 2
	Batch Trailer	Detail Data
	File Trailer	Batch Trailer
		File Trailer

With the second and third examples, files will usually contain multiple batches, organized by the PCard number, employee ID, or other criteria. For example, if the batches store records by transaction date, each batch would contain all of the transactions for the date specified in the batch header.

PCard Definition Page

Use this page to set up and maintain the individual record types in a file format, such as Detail Data and File Trailer.

Page Sections

All Record Type buttons display the PCard Definition page, which includes the following sections:

Layout Definition Information	Displays basic instructions for adding a new field to the format record or for updating an existing field.
Fields Defined for this Record Type	Displays format records for fields that have already been defined for the current record type. This section is also referred to as the List section.
Field Information	Displays fields for adding or updating a format record. To add a record, click Add New. To update a format, select a record in the List section, and then click OK.
Count/Sum Information	Displays checkboxes for using a field in a count or sum. For example, if your format calls for the total charges in a batch, select the Include in Batch Count checkbox for the Amount field in the batch's detail record. This enables the system to add all the amounts within the batch.

Record Formats

The records within a file can be formatted in two ways:

Fixed Length

Each field in a record has a defined length. For example, a Transaction Amount field might have a set length of 10 positions, including two decimal places (cents).

If the decimal point is not counted in the length, the decimal point is assumed. For example, the largest amount that can be stored in a 10-position field is 99,999,999.99. If the decimal point counts as one of the positions, the maximum would be 9,999,999.99. Commas are not stored.

The location of a field in a fixed length record is determined by the field's start and stop positions. For example, if a record's first field is 12 characters long, the field's start position is 1 and stop position 12. The start position of the next field would be 13.

Delimited

Fields vary in length from record to record. The insertion of a delimiter character determines where one field ends and the next one begins. Two common delimiters are commas and tabs, though other characters can be used. Delimited fields do not use start and stop positions.

Basic Field Formats

The fields within a record can be formatted in three basic ways:

Right Justified	Data is stored on the right-hand side of a field. Numeric fields (Integers and Decimal Numbers) are usually right justified.
Left Justified	Data is stored on the left-hand side of a field. Usually, character fields are left justified.
Date Format	Dates can be stored in a variety of formats, for example, MMDDYY (month-day-year) or YYYYMMDD (year-month-day).

If the data in a fixed length field does not fill the entire field, you may need to tell the system to add zeroes at the beginning for right-justified fields or characters at the end for left-justified fields. For example, with zero filling, $75 would be stored as 0000007500 in a right-justified field with 10 positions. If fill characters are not required, the unused positions are left blank.

Format Example

Following is an example of a PCard file format, organized by record type. The file specifications from your provider will differ in layout and content, but will be similar in terms of the general type of information provided.

	Field	Format	Length	Start	Stop
File Header	Period Start Date	MMDDYYYY	8 numeric	01	08
	Period End Date	MMDDYYYY	8 numeric	09	16
	Account #	Right justified, 0 fill	16 numeric	17	32
	Client Name	Left justified, X fill	30 character	33	62
Batch Headers	PCard #	Right justified, 0 fill	12 numeric	01	12
	Cardholder Name	Left justified, X fill	35 character	13	47
Detail Records	Transaction Date	MMDDYYYY	8 numeric	01	08
	Merchant #	Right justified, 0 fill	8 numeric	09	16
	Amount	Right justified, decimal	10 numeric	17	26
	Description	Left justified, X fill	20 character	27	46
Batch Trailers	Total Transactions	Right justified, 0 fill	6 numeric	01	06
	Total Amount	Right justified, decimal	10 numeric	07	16
File Trailer	Total Batches	Right justified, 0 fill	6 numeric	01	06
	Total Transactions	Right justified, 0 fill	6 numeric	07	12
	Total Amount	Right justified, decimal	10 numeric	13	22

Planning Your File Layout

Before creating your PCard file format, review the information from your provider and develop a scheme for organizing the specifications.

Consider setting up a spreadsheet or a chart such as the one in the previous example to map the elements of your format. Using a spreadsheet or chart will help prevent errors and will also speed the entry process. Once all components are filled in, you can begin creating your records.

Important
Each field in a file layout must have its own format record set up with a specific Record Type button. For example, if a file header includes four fields, you would have to set up a record for each of these using the PCard Definition File page's File Header button.

Adding a PCard file format

From the PCard Processing menu, select Reference Tables. Then, select PCard Definition File to display the PCard Definition File page.
Click Add New.
In the General Information section, the system assigns a number in the Bank Layout Code field to identify the format. Complete the other fields as needed. For details, refer to the Fields section below.
Click OK to save the format record.
In the Layout Record Types section, click an action button to display the PCard Definition page for adding fields to the format for a particular record type:
File Header
Batch Header
Detail Data
Batch Trailer
File Trailer
In the PCard Definition page, click Add New.
Complete the Field Information section. For details, refer below to the Fields section.
In the Count/Sum Information section, select the appropriate box if you need to include a count or sum in a File Count or Batch Count.
If you are adding more than one record, leave the Continuous checkbox selected. Otherwise, clear the box.
Click OK to save the field as part of the format record.
If the Continuous checkbox is selected, repeat Steps 7-10 for each additional record required for the current record type.
Otherwise, you access the Fields Defined for this Record Type section. You can update an existing record if desired by selecting the record and clicking OK. To save an update, click OK.
Click Back to return to the PCard File Definition page.
Repeat Steps 5-12 for each additional record type, or click Back to return to the PCard File Definition page's List section.

Updating a PCard format

From the PCard Processing menu, select Reference Tables. Then, select PCard Definition File to display the PCard Definition File page.
In the List section, select the format to update, and then click OK.
In the General Information section, update the fields as needed.
Click OK to save the record.
In the Layout Record Types section, click the action button for a record type that contains a record you want to update. The PCard Definition page displays.
In the PCard Definition page's Fields Defined for this Record Type section, select the record to update, and then click OK.
In the Field Information and Count/Sum Information sections, update the desired fields.
If you are changing more than one record, select the Continuous checkbox. Otherwise, leave the box blank.
Click OK to save your changes.
If the Continuous checkbox is selected, repeat Steps 7-9 to change the next record in the Fields Defined for this Record Type section.
Otherwise, you return to the Fields Defined for this Record Type section. You can repeat Steps 6-9 to change another record or proceed to the next step.
Click Back to return to the PCard Definition File page's Layout Record Types section.
Repeat Steps 5-11 for each record type containing records you need to update.
Click Back when you finish your changes to return to the PCard Definition File page's List section.

Deleting a PCard format

From the PCard Processing menu, select Reference Tables. Then, select PCard Definition File to display the PCard Definition File page.
In the List section, select the format you want to delete.
Click Delete.
In the Confirmation dialog, click Yes.

Important
You cannot delete a format that has PCards assigned, unless you delete the PCards first.

Deleting a record for a particular record type

From the PCard Processing menu, select Reference Tables. Then, select PCard Definition File to display the PCard Definition File page.
In the List section, select the desired format, and then click OK.
In the General Information section, click OK.
In the Layout Record Types section, click the action button for the record type containing the record you want to delete.
In the PCard Definition page's Fields Defined for this Record Type section, select the record to delete.
Click Delete.
In the Confirmation dialog, click Yes.

Generating the PCard File Definition and Layout report

From the PCard Processing menu, select Reference Tables. Then, select PCard Definition File to display the PCard Definition File page.
Click Print Multiple Batches to display the Print PCard Formats Selection page:
or
If you only want a report for one format, select the format in the List section, and then click Print. Proceed to Step 5.
In the Bank Layout Code field:
- To limit the report to one format, enter the format's Bank Layout Code.
- To select multiple formats, insert the pipe symbol between codes, for example, 111|123|130.
- To include all formats, leave the field blank.
Click OK.
In the Print window, specify how to generate the report, and then click OK. The report's default file name is pcdlayout.rpt.
The PCard File Definition and Layout report shows the records in a format and lists any errors detected when the format is applied against a reconciliation file. If errors are found, use the update procedure to correct them.

Fields

Following are descriptions of the fields in the Field Information and Count/Sum Information sections of the PCard Definition page.

Field	Description
PCard Definition File Page	This is the initial page that displays when you select the PCard Definition File option. The page includes three sections: Layout Definition Information, General Information, and Layout Record Types.
Layout Definition Information Section	Lists the file formats stored in your database. All of the section's fields are display only. When you select a format, its data displays in the General Information section.
Bank Layout Code	System-assigned number identifying a file format.
Bank Layout Name	User-defined name identifying the PCard provider associated with the file format.
Vendor Code	Code identifying the PCard provider in Fund Accounting's Vendor List.
General Information Section	You can access this section as follows: To update a file format, select a format in the Layout Definition Information section, and then click OK. To add a file format, click Add New on the toolbar.
Bank Layout Code	System-assigned number identifying a file format. Display only.
Bank Layout Name	Enter the name you want to apply to the format. Character/20
Default File Name	Enter the default name of the PCard files you receive. If your provider uses a unique name for each file submitted, leave the field blank. In this case, you should select N (No date used in default file name) in the next field.
Date in File Name?	Indicates whether a date is used in the file name. You can only access this field if you have entered a name in the Default File Name field. Select: N - No date used in default file name (default) S - System date as suffix to default file name P - System date as prefix to default file name If you do not enter a name in the Default File Name field, you cannot access this field, and the default (N - No date used in default file name) cannot be changed.
Delimiter	If the format fields are separated by a delimiter, enter the appropriate character. A comma is a common delimiter, as is the tab symbol (represented by the letter T). Delimiters are not used in fixed length fields. Instead, you must enter stop and start positions, so the system knows where fields begin and end.
Vendor Code	Identify your PCard provider by entering the appropriate code from the Vendor List. If you do not have a record for your provider, use Fund Accounting Vendor List option to add one. During the vendor check process, all PCard transactions in a reconciliation file will be combined into one check for the vendor entered in this field.
Layout Record Types Section	Use this section to review the current format's record types. Both of the section's fields are display only. The Create/View Record Types column on the far right displays action buttons for accessing the PCard Definition page for the record types indicated by the button titles.
Defined Types	Identifies a record type, for example, File Header or Batch Trailer.
Number of Data Items	Indicates how many records have been created for the associated record type.

PCard Definition Page

Use this page to update or add a record type for the current file format. You can display the page for a specific record type by clicking the corresponding action button in the PCard Definition File page's Layout Record Types section.

Field	Description
Fields Defined for This Record Type Section	Lists the existing fields for the current record layout type. Both of the section's fields are display only.
Field Name	Displays the name of the field. Field names are user defined.
Data Item	Identifies the data element associated with the field. For the list of data elements, refer to the description of the Data Item field in the Field Information section.

Field Information Section

The section's fields define a field format within a selected record type. You can access the section as follows:

To update an existing field, select the field in the Fields Defined for This Record Type section, and click OK.
To add a field, click Add New on the toolbar.

Field

Description

Field Name

Name of the field, as indicated in your specifications. A field name is not required, but generally you should enter one for identification purposes. Character/30

Line Position

Numeric position of the field within the current line. For example, if you are entering the fourth field for the first line of a Detail Data record, enter 4. Required.

Line Number

Number identifying the line you are setting up. For example, if you are adding the first line, enter 1. Fields usually have just one line, though some providers may use more than one for certain fields. Required.

Data Item10 - Sales Tax

Identifies the data element that will be loaded from the PCard file. Required.

0- Record Identifier	14 - Account	28 - User Defined Field 2
1 - Transaction Number	15 - Project	29 - User Defined Field 3
2 - PCard Number	16 - Project Account	30 - User Defined Field 4
3 - Layout Format	17 - Commodity	31 - User Defined Field 5
4 - Transaction ID	18 - SPI Vendor Number	32 - Merchant Name
5 - Transaction Date	19 - SPI Employee ID	33 - Merchant Address 1
6 - Date of Purchase	20 - Encumbrance Number	34 - Merchant Address 2
7 - Merchant ID	21 - Line Number	35 - Merchant City
8 - User ID	22 - Year	36 - Merchant State
9 - Amount of Sale	23 - Batch Payable Trans. No.	37 - Merchant Zip
10 - Sales Tax	24 - Payable Row ID	38 - Linked Transaction ID
11 - Use Tax	25 - Entry Number	99 - Count of Records
12 - Item Description	26 - Status Flag	100 - Full Account
13 - Key Organization ID	27 - User Defined Field 1

The following data elements are required in a bank layout: 2 - PCard Number, 5 - Transaction Date (or 6 - Date of Purchase), 7 - Merchant ID, and 9 - Amount of Sale.

22.4 and later.

If the Full Account Format option is enabled in the Fund Accounting Profile, then a field for 100 - Full Account can be included when defining the file layout for the file that is provided by the bank/PCard provider. In this case, 13 - Key Organization ID and 14 - Account will not be available.

Format

Defines the format for the current data item. Required.

Select:

1 -RIGHT - Right Justified

2 - LEFT - Left Justified

3 - Integer

4 - Decimal (cash)

5 - DDDMMYY - Date format

6 - DDMMYYYY - Date format

7 - MM-DD-YYYY - Date format

8 - MM/DD/YYYY - Date format

9 - MMDDYY - Date format

10 - MMDDYYYY - Date format

Line Type Value

Value that identifies a record as a batch header or batch trailer when the PCard file loads. For example, a header might be identified by the letter H or a trailer by the letter T. Enter the value indicated in your specifications, or leave the field blank if a line type is not specified.

Start Position

Number identifying the position in a record where a field begins. Required with fixed length fields.

The start position for the first field in a fixed length file is 1.
The start position for other fields is the sum of all preceding lengths, plus 1. For example, if the first field has 9 positions and the second field has 5 positions, the start position for the third field is 15 (9 + 5 + 1).
Leave this field blank if you are setting up a delimited file.

Stop Position

Number identifying the position in a record where a field ends. Required with fixed length files.

To determine the stop position, add the field's length to the stop position of the previous field. For example, if the previous field's stop position is 14 and the current field has 8 positions, the current field's stop position is 22 (8 + 14). Do not add the length to the current field's start position.
Leave this field blank if you are setting up a delimited file.

Field Length

Total number of positions the current field occupies. The system calculates the length based on the field's start and stop positions. Display-only field that only applies to fixed length (non-delimited) files.

For decimal fields, a decimal point may or may not be counted in the length, depending on your specifications. If a decimal point is included, be sure to select the Contains Decimal Point checkbox.
The length of dates depends on the format used. For example, the date format MMDDYYYY has 8 positions while MM/DD/YYYY has 10 positions.

Example: The following illustrates a fixed length transaction record with four fields. The numbers below indicate the position of each character and number.

Based on the illustration, the record's specifications would be as follows:

	Value	Format	Length	Start	Stop
PCard Number	432154326543	Left justified	12	1	12
Date	01/01/2018	MMDDYYYY	8	13	20
Merchant	6543	Left justified, X filled	8	21	28
Amount	50.25	Right justified, 0 filled	8	29	36

Contains Decimal Point

Checkbox indicating whether a decimal point is included in the length of a field that stores decimal numbers. This field only relates to fixed length files.

Leave the box blank if the decimal point is not stored. In the previous example, the transaction amount of $50.25 is stored as 00005025, in which case, the Contains Decimal Point checkbox would not be selected.
Select the checkbox if the decimal point is included in the field's length. With this setting, the transaction amount of $50.25 would be stored as 00050.25, provided that eight positions are allotted and zero filling applies.

Count Or Sum Information Section

Following are descriptions of the fields in this section:

Field

Description

Include in File Count

Checkbox indicating whether the current field should be used to count or sum certain records in a file.

Select the box for the field that is being counted or summed. For example, if you need to total all transactions in a file, you would select the box for the amount field in a detail record or the total amount field in a batch record.
Do not select the box for the field that stores the file total. File counts and sums are stored in either the file header or file trailer.
The box should be selected for one field in a batch header or batch trailer if the specifications call for a count of the number of batches in the file.

Include in Batch Count

Checkbox indicating whether the current field should be used to count or sum certain records in a batch.

For related information, refer to the description of the Include in File Count field. Keep in mind that Include in Batch Count field only applies to batch records, while the previous field applies to the entire file.