Set Up Command Tables

TEMPLATE TYPE Command

This command specifies the type of template.

There're two types: FIXED_POSITION_BASED and DELIMITER_BASED.

Use the FIXED_POSITION_BASED templates for fixed-length record formats, such as EFTs. In these formats, all fields in a record are a fixed length. If data is shorter than the specified length, then it's padded. If longer, it's truncated. The system specifies the default behavior for data padding and truncation. Examples of fixed position based formats are EFTs in Europe, and NACHA ACH file in the U.S.

In a DELIMITER_BASED template, data is never padded and only truncated when it reaches a maximum field length. Empty fields are allowed (when the data is null). Designated delimiters are used to separate the data fields. If a field is empty, two delimiters are displayed next to each other. Examples of delimited-based templates are EDI formats such as ASC X12 820 and UN EDIFACT formats - PAYMUL, DIRDEB, and CREMUL.

In EDI formats, a record is sometimes referred to as a segment. An EDI segment is treated the same as a record. Start each segment with a new record command and give it a record name. You should have a data field specifying the segment name as part of the output data immediately following the new record command.

For DELIMITER_BASED templates, you insert the appropriate data field delimiters in separate rows between the data fields. After every data field row, you insert a delimiter row. You can insert a placeholder for an empty field by defining two consecutive delimiter rows.

Empty fields are often used for syntax reasons: you must insert placeholders for empty fields so that the fields that follow can be properly identified.

There're different delimiters to signify data fields, composite data fields, and end of record. Some formats allow you to choose the delimiter characters. In all cases you should use the same delimiter consistently for the same purpose to avoid syntax errors.

In DELIMITER_BASED templates, the <POSITION> and <PAD> columns do not apply. They're omitted from the data tables.

Some DELIMITER_BASED templates have minimum and maximum length specifications. In those cases Oracle Payments validates the length.

DEFINE LEVEL Command

Some formats require specific additional data levels that are not in the data extract. For example, some formats require that payments be grouped by payment date. Using the Define Level command, a payment date group can be defined and referenced as a level in the template, even though it isn't in the input extract file.

When you use the Define Level command you declare a base level that exists in the extract. The Define Level command inserts a new level, one level higher than the base level of the extract. The new level functions as a grouping of the instances of the base level.

The Define Level command is a setup command, therefore it must be defined in the setup table. It has three subcommands:

BASE LEVEL command - defines the level (XML element) from the extract that the new level is based on. The Define Level command must always have one and only one base level subcommand.
GROUPING CRITERIA - defines the XML extract elements that are used to group the instances of the base level to form the instances of the new level. The parameter of the grouping criteria command is a comma-separated list of elements that specify the grouping conditions.

The order of the elements determines the hierarchy of the grouping. The instances of the base level are first divided into groups according to the values of the first criterion, then each of these groups is subdivided into groups according to the second criterion, and so on. Each of the final subgroups is considered as an instance of the new level.
GROUP SORT ASCENDING or GROUP SORT DESCENDING - defines the sorting order of the group. Insert the <GROUP SORT ASCENDING> or <GROUP SORT DESCENDING> command row anywhere between the <DEFINE LEVEL> and <END DEFINE LEVEL> commands. The parameter of the sort command is a comma-separated list of elements by which to sort the group.
GROUP SORT ASCENDING NUMBER or GROUP SORT DESCENDING NUMBER - defines the sorting order of the numeric group. Insert the <GROUP SORT ASCENDING NUMBER> or <GROUP SORT DESCENDING NUMBER> command row anywhere between the <DEFINE LEVEL> and <END DEFINE LEVEL> commands. The parameter of the sort command is a comma-separated list of elements for sorting.

For example, the following table shows five payments under a batch.

Payment Instance	PaymentDate (grouping criterion 1)	PayeeName (grouping criterion 2)
Payment1	PaymentDate1	PayeeName1
Payment2	PaymentDate2	PayeeName1
Payment3	PaymentDate1	PayeeName2
Payment4	PaymentDate1	PayeeName1
Payment5	PaymentDate1	PayeeName3

In the template, construct the setup table as follows to create a level called PaymentsByPayDatePayee from the base level "Payment" grouped according to Payment Date and Payee Name. Add the Group Sort Ascending command to sort each group by PaymentDate and PayeeName:

Payment Group	Level
`<DEFINE LEVEL>`	PaymentsByPayDatePayee
`<BASE LEVEL>`	Payment
`<GROUPING CRITERIA>`	PaymentDate, PayeeName
`<GROUP SORT ASCENDING>`	PaymentDate, PayeeName
`<END DEFINE LEVEL>`	PaymentsByPayDatePayee

The five payments generate the four groups (instances) shown in the following table for the new level:

Payment Group Instance	Group Criteria	Payments in Group
Group1	PaymentDate1, PayeeName1	Payment1, Payment4
Group2	PaymentDate1, PayeeName2	Payment3
Group3	PaymentDate1, PayeeName3	Payment5
Group4	PaymentDate2, PayeeName1	Payment2

The order of the new instances is the order in which the records print. When evaluating the multiple grouping criteria to form the instances of the new level, the criteria can be thought of as forming a hierarchy. The first criterion is at the top of the hierarchy, the last criterion is at the bottom of the hierarchy.

Generally there're two kinds of format-specific data grouping scenarios in EFT formats. Some formats print the group records only; others print the groups with the individual element records nested inside groups. The following tables are two examples for these scenarios based on the five payments and grouping conditions previously illustrated and show the generated output:

Scenario 1: Group Records Only

EFT File Structure:

BatchRec
- PaymentGroupHeaderRec
- PaymentGroupFooterRec

Record Sequence	Record Type	Description
1	BatchRec	NA
2	PaymentGroupHeaderRec	For group 1 (PaymentDate1, PayeeName1)
3	PaymentGroupFooterRec	For group 1 (PaymentDate1, PayeeName1)
4	PaymentGroupHeaderRec	For group 2 (PaymentDate1, PayeeName2)
5	PaymentGroupFooterRec	For group 2 (PaymentDate1, PayeeName2)
6	PaymentGroupHeaderRec	For group 3 (PaymentDate1, PayeeName3)
7	PaymentGroupFooterRec	For group 3 (PaymentDate1, PayeeName3)
8	PaymentGroupHeaderRec	For group 4 (PaymentDate2, PayeeName1)
9	PaymentGroupFooterRec	For group 4 (PaymentDate2, PayeeName1)

Scenario 2: Group Records and Individual Records

EFT File Structure:

BatchRec

PaymentGroupHeaderRec
- PaymentRec
PaymentGroupFooterRec

Record Sequence	Record Type	Description
1	BatchRec	NA
2	PaymentGroupHeaderRec	For group 1 (PaymentDate1, PayeeName1)
3	PaymentRec	For Payment1
4	PaymentRec	For Payment4
5	PaymentGroupFooterRec	For group 1 (PaymentDate1, PayeeName1)
6	PaymentGroupHeaderRec	For group 2 (PaymentDate1, PayeeName2)
7	PaymentRec	For Payment3
8	PaymentGroupFooterRec	For group 2 (PaymentDate1, PayeeName2)
9	PaymentGroupHeaderRec	For group 3 (PaymentDate1, PayeeName3)
10	PaymentRec	For Payment5
11	PaymentGroupFooterRec	For group 3 (PaymentDate1, PayeeName3)
12	PaymentGroupHeaderRec	For group 4 (PaymentDate2, PayeeName1)
13	PaymentRec	For Payment2
14	PaymentGroupFooterRec	For group 4 (PaymentDate2, PayeeName1)

Once defined with the Define Level command, the new level can be used in the template in the same manner as a level occurring in the extract. However, the records of the new level can only reference the base level fields that are defined in its grouping criteria. They cannot reference other base level fields other than in summary functions.

For example, the PaymentGroupHeaderRec can reference the PaymentDate and PayeeName in its fields. It can also reference the PaymentAmount (a payment level field) in a SUM function. However, it cannot reference other payment level fields, such as PaymentDocName or PaymentDocNum.

The DEFINE LEVEL command must always have one and only one grouping criteria subcommand. The DEFINE LEVEL command has a companion END DEFINE LEVEL command. The subcommands must be specified between the DEFINE LEVEL and END DEFINE LEVEL commands. They can be declared in any order.

DEFINE SEQUENCE Command

The DEFINE SEQUENCE command defines a sequence that can be used in conjunction with the SEQUENCE_NUMBER function to index either the generated EFT records or the extract instances (the database records). The EFT records are the physical records defined in the template. The database records are the records from the extract. To avoid confusion, the term record always refers to the EFT record. The database record is referred to as an extract element instance or level.

The DEFINE SEQUENCE command has four subcommands: RESET AT LEVEL, INCREMENT BASIS, START AT, MINIMUM, and MAXIMUM:

RESET AT LEVEL

The RESET AT LEVEL subcommand defines where the sequence resets its starting number.

RESET AT LEVE is a mandatory subcommand. For example, to number the payments in a batch, define RESET AT LEVEL as Batch. To continue numbering across batches, define RESET AT LEVEL as RequestHeader.

In some cases the sequence is reset outside the template. For example, a periodic sequence may be defined to reset by date. In these cases, the PERIODIC_SEQUENCE keyword is used for the RESET AT LEVEL. The system saves the last sequence number used for a payment file to the database. Outside events control resetting the sequence in the database. For the next payment file run, the sequence number is extracted from the database for the start at number (see start at subcommand).

INCREMENT BASIS

The INCREMENT BASIS subcommand specifies if the sequence should be incremented based on record or extract instances. The allowed parameters for this subcommand are RECORD and LEVEL.

Enter RECORD to increment the sequence for every record.

Enter LEVEL to increment the sequence for every new instance of a level.

Note that for levels with multiple records, if you use the level-based increment, then all the records in the level have the same sequence number. The record-based increment assigns each record in the level a new sequence number.

For level-based increments, the sequence number can be used in the fields of one level only. For example, suppose an extract has a hierarchy of batch > payment > invoice and you define the INCREMENT BASIS by level sequence, with reset at the batch level. You can use the sequence in either the payment or invoice level fields, but not both. You cannot have sequential numbering across hierarchical levels.

However, this rule doesn't apply to increment basis by record sequences. Records can be sequenced across levels.

For both increment basis by level and by record sequences, the level of the sequence is implicit based on where the sequence is defined.

MINIMUM

Specifies the minimum sequence number.

If MINIMUM isn't declared, the minimum sequence number os set as 1 by default.

Specify this value when you want the minimum sequence number to be a value other than 1.

Define Concatenation Command

Use the define concatenation command to concatenate child-level extract elements for use in parent-level fields.

For example, use this command to concatenate invoice number and due date for all the invoices belonging to a payment for use in a payment-level field.

The define concatenation command has three subcommands: base level, element, and delimiter.

Base Level Subcommand

The base level subcommand specifies the child level for the operation.

For each parent-level instance, the concatenation operation loops through the child-level instances to generate the concatenated string.

Element Subcommand

The element subcommand specifies the operation used to generate each element. An element is a child-level expression that is concatenated to generate the concatenation string.

Delimiter Subcommand

The delimiter subcommand specifies the delimiter to separate the concatenated items in the string.

Use the SUBSTR Function

Use the SUBSTR function to break down concatenated strings into smaller strings that can be placed into different fields.

For example, the following table shows five invoices in a payment.

Invoice	InvoiceNum
1	car_parts_inv0001
2	car_parts_inv0002
3	car_parts_inv0003
4	car_parts_inv0004
5	car_parts_inv0005

Using the concatenation definition shown in the following table:

Level	Definition
<DEFINE CONCATENATION>	ConcatenatedInvoiceInfo
<BASE LEVEL>	Invoice
<ELEMENT>	InvoiceNum
<DELIMITER>	','
<END DEFINE CONCATENATION>	ConcatenatedInvoiceInfo

You can reference ConcatenatedInvoiceInfo in a payment level field. The string is as follows:

car_parts_inv0001,car_parts_inv0002,car_parts_inv0003,car_parts_inv0004,car_parts_inv0005

If you want to use only the first forty characters of the concatenated invoice information, then use either TRUNCATE function or the SUBSTR function as follows:

TRUNCATE(ConcatenatedInvoiceInfo, 40)

SUBSTR(ConctenatedInvoiceInfo, 1, 40)

Either of these statements result in:

car_parts_inv0001,car_parts_inv0002,car_

To isolate the next forty characters, use the SUBSTR function:

SUBSTR(ConcatenatedInvoiceInfo, 41, 40)

to get the following string:

parts_inv0003,car_parts_inv0004,car_par

Invalid Characters and Replacement Characters Commands

Some formats require a different character set than the one that was used to enter the data in Oracle Applications. For example, some German formats require the output file in ASCII, but the data was entered in German. If there's a mismatch between the original and target character sets you can define an ASCII equivalent to replace the original. For example, you would replace the German umlauted "a" with "ao".

Some formats don't allow certain characters. To ensure that known invalid characters are not transmitted in the output file, use the invalid characters command to flag occurrences of specific characters.

To use the replacement characters command, specify the source characters in the left column and the replacement characters in the right column. You must enter the source characters in the original character set. This is the only case in a format template in which you use a character set not intended for output. Enter the replacement characters in the required output character set.

For DELIMITER_BASED formats, if there're delimiters in the data, you can use the escape character "?" to retain their meaning. For example,

First name?+Last name equates to First name+Last name

Which source?? equates to Which source?

Note that the escape character itself must be escaped if it's used in data.

The replacement characters command can be used to support the escape character requirement. Specify the delimiter as the source and the escape character plus the delimiter as the target. For example, the command entry for the preceding examples is as follows:

<REPLACEMENT CHARACTERS>
+   ?+
?   ??
<END REPLACEMENT CHARACTERS>

The invalid character command has a single parameter that is a string of invalid characters that causes the system to error out.

The replacement character process is performed before or during the character set conversion. The character set conversion is performed on the XML extract directly, before the formatting. After the character set conversion, the invalid characters are checked in terms of the output character set. If no invalid characters are found, then the system proceeds to formatting.

Output Character Set and New Record Character Commands

Use the new record character command to specify the character(s) to delimit the explicit and implicit record breaks at runtime.

Each new record command represents an explicit record break. Each end of table represents an implicit record break. The parameter is a list of constant character names separated by commas.

Some formats contain no record breaks. The generated output is a single line of data. In this case, leave the new record character command parameter field empty.

If you do not define a "new record character" field in the template, then the system sets "\n" as default new record character.

Output Length Mode

Output Length Mode can be set to "character" or "byte".

When OUTPUT LENGTH MODE is set to "character", the output record length for each field is based on character length. When OUTPUT LENGTH MODE is set to "byte", the output record length for each field is based on byte length.

If no OUTPUT LENGTH MODE is setting is provided, "character" is used.

Number Thousands Separator and Number Decimal Separator

The default thousands (or group) separator is a comma (",") and the default decimal separator is a period ("."). Use the Number Thousands Separator command and the Number Decimal Separator command to specify separators other than the defaults.

For example, to define "." as the group separator and "," as the decimal separator, enter the commands as follows:

<NUMBER THOUSANDS SEPARATOR>	.
<NUMBER DECIMAL SEPARATOR>	 ,

Note that when you set "NUMBER DECIMAL SEPARATOR", you must also set "NUMBER THOUSANDS SEPARATOR". Ensure to set the appropriate format mask for the field to be displayed. For more information on formatting numbers, see Format Column.

CASE CONVERSION

Use CASE CONVERSION to convert strings from lowercase to uppercase for fields with format type ALPHA. This command is used with FIXED_POSITION_BASED templates.

Valid values are "UPPER" and "LOWER". Enter the command as follows:

<CASE CONVERSION> : UPPER