8 Creating eText Templates

8.1.1 Prerequisites

This section is intended for users who are familiar with EDI and EFT transactions. Preparers of eText templates require both functional and technical knowledge; that is, functional expertise to understand bank and country specific payment format requirements and sufficient technical expertise to understand XML data structure and eText specific coding syntax commands, functions, and operations.

8.2.1 Command Rows, Data Rows, and Data Column Header Rows

Figure 8-3 shows the placement of Command Rows, Data Rows, and Data Column Header Rows.

Figure 8-3 Command Rows, Data Rows, and Data Column Header Rows Placement

Description of "Figure 8-3 Command Rows, Data Rows, and Data Column Header Rows Placement"

Command rows are used to specify commands in the template. Command rows always have two columns: command name and command parameter. Command rows do not have column headings. The commands control the overall setup and record structures of the template.

Blank rows can be inserted anywhere in a table to improve readability. Most often they are used in the setup table, between commands. Blank rows are ignored by BI Publisher when the template is parsed.

8.2.2 Data Column Header Rows

Data column headers specify the column headings for the data fields (such as Position, Length, Format, Padding, and Comments). A column header row usually follows the Level command in a table (or the sorting command, if one is used). The data column header row must come before any data rows in the table. Additional empty column header rows can be inserted at any position in a table to improve readability. The empty rows are ignored at runtime.

The required data column header rows vary depending on the template type. See Section 8.5, "Structure of the Data Rows."

8.2.3 Data Rows

Data rows provide the values for the data column header attributes for each data field.

The content of the data rows varies depending on the template type. See Section 8.5, "Structure of the Data Rows."

8.4.1 Level Command

The level command associates a table with an XML element. The parameter for the level command is an XML element. The level is printed once for each instance the XML element appears in the data input file.

The level commands define the hierarchy of the template. For example, Payment XML data extracts are hierarchical. A batch can have multiple child payments, and a payment can have multiple child invoices. This hierarchy is represented in XML as nested child elements within a parent element. By associating the tables with XML elements through the level command, the tables also have the same hierarchical structure.

Similar to the closing tag of an XML element, the level command has a companion end-level command. The child tables must be defined between the level and end-level commands of the table defined for the parent element.

An XML element can be associated with only one level. All the records belonging to a level must reside in the table of that level or within a nested table belonging to that level. The end-level command is specified at the end of the final table.

Following is a sample structure of an EFT file record layout:

• FileHeaderRecordA

  • BatchHeaderRecordA

  • BatchHeaderRecordB

    PaymentRecordA

    PaymentRecordB

    • InvoiceRecordA

  • Batch FooterRecordC

  • BatchFooterRecordD

• FileFooterRecordB

Following would be its table layout:

Multiple records for the same level can exist in the same table. However, each table can only have one level defined. In the example above, the BatchHeaderRecordA and BatchHeaderRecordB are both defined in the same table. However, note that the END LEVEL for the Payment must be defined in its own separate table after the child element Invoice. The Payment END LEVEL cannot reside in the same table as the Invoice Level.

Note that you do not have to use all the levels from the data extract in the template. For example, if an extract contains the levels: RequestHeader > Batch > Payment > Invoice, you can use just the batch and invoice levels. However, the hierarchy of the levels must be maintained.

The table hierarchy determines the order that the records are printed. For each parent XML element, the records of the corresponding parent table are printed in the order they appear in the table. The system loops through the instances of the child XML elements corresponding to the child tables and prints the child records according to their specified order. The system then prints the records of the enclosing (end-level) parent table, if any.

For example, given the EFT template structure above, assume the input data file contains the following:

• Batch1

  • Payment1

    • Invoice1

    • Invoice2

  • Payment2

    • Invoice1

• Batch2

  • Payment1

    • Invoice1

    • Invoice2

    • Invoice3

This generates the printed records that are shown in Table 8-1.

8.4.2 New Record Command

The new record command signifies the start of a record and the end of the previous one, if any. Every record in a template must start with the new record command. The record continues until the next new record command, or until the end of the table or the end of the level command.

A record is a construct for the organization of the elements belonging to a level. The record name is not associated with the XML input file.

A table can contain multiple records, and therefore multiple new record commands. All the records in a table are at the same hierarchy level. They are printed in the order in which they are specified in the table.

The new record command can have a name as its parameter. This name becomes the name for the record. The record name is also referred to as the record type. The name can be used in the COUNT function for counting the generated instances of the record. See the COUNT function Section 8.8.3, "Functions" for more information.

Consecutive new record commands (or empty records) are not allowed.

8.4.3 Sort Ascending and Sort Descending Commands

Use the sort ascending and sort descending commands to sort the instances of a level. Enter the elements that you want to sort by in a comma-separated list. This is an optional command. When used, it must come right after the (first) level command and it applies to all records of the level, even if the records are specified in multiple tables.

8.4.4 Display Condition Command

The display condition command specifies when the enclosed record or data field group should be displayed. The command parameter is a boolean expression. When it evaluates to true, the record or data field group is displayed. Otherwise the record or data field group is skipped.

The display condition command can be used with either a record or a group of data fields. When used with a record, the display condition command must follow the new record command. When used with a group of data fields, the display condition command must follow a data field row. In this case, the display condition applies to the rest of the fields through the end of the record.

Consecutive display condition commands are merged as AND conditions. The merged display conditions apply to the same enclosed record or data field group.

8.5.1 Position

Specifies the starting position of the field in the record. The unit is in number of characters. This column is only used with FIXED_POSITION_BASED templates.

8.5.2 Length/Maximum Length

Specifies the length of the field. The unit is in number of characters. For FIXED_POSITION_BASED templates, all the fields are fixed length. If the data is less than the specified length, it is padded. If the data is longer, it is truncated. The truncation always occurs on the right.

For DELIMITER_BASED templates, this value specifies the maximum length of the field. If the data exceeds the maximum length, it is truncated. Data is not padded if it is less than the maximum length.

8.5.3 Format Column

Specifies the data type and format setting. There are three accepted data types:

• Alpha

• Number

• Date

Refer to Section 8.9.4, "Field-Level Key Words" for their usage.

8.5.4 Number Data Type

Numeric data has three optional format settings: Integer, Decimal, or you can define a format mask. Specify the optional settings with the Number data type as follows:

• Number, Integer

• Number, Decimal

• Number, <format mask>

  For example:

  Number, ###,###.00

The Integer format uses only the whole number portion of a numeric value and discards the decimal. The Decimal format uses only the decimal portion of the numeric value and discards the integer portion.

The following table shows examples of how to set a format mask. When specifying the mask, # represents that a digit is to be displayed when present in the data; 0 represents that the digit placeholder is to be displayed whether data is present or not.

When specifying the format mask, the group separator must always be "," and the decimal separator must always be "." To alter these in the actual output, you must use the Setup Commands NUMBER THOUSANDS SEPARATOR and NUMBER DECIMAL SEPARATOR. See Section 8.6, "Setting Up Command Tables" for details on these commands.

Table 8-2 shows sample Data, Format Specifier, and Output. The Output assumes the default group and decimal separators.

8.5.5 Date Data Type

The Date data type format setting must always be explicitly stated. The format setting follows the SQL date styles, such as MMDDYY.

8.5.6 Mapping EDI Delimiter-Based Data Types to eText Data Types

Some EDI (DELIMITER_BASED) formats use more descriptive data types. These are mapped to the three template data types as shown in Table 8-3.

Assume the setup commands shown in Table 8-4.

Table 8-5 shows the Data, Format Specifier, and Output for this case. Note that the Format Specifier requires the use of the default separators, regardless of the setup command entries.

8.5.7 Pad

Pad applies to FIXED_POSITION_BASED templates only. Specify the padding side (L = left or R = right) and the character. Both numeric and alphanumeric fields can be padded. If this field is not specified, Numeric fields are left-padded with "0"; Alpha fields are right-padded with spaces.

Example usage:

• To pad a field on the left with a "0", enter the following in the Pad column field:

  L, '0'

• To pad a field on the right with a space, enter the following the Pad column field:

  R, ' '

8.5.8 Data

Specifies the XML element from the data extract that is to populate the field. The data column can simply contain the XML tag name, or it can contain expressions and functions. For more information, see Section 8.8, "Expressions, Control Structures, and Functions."

8.5.9 Tag

Acts as a comment column for DELIMITER_BASED templates. It specifies the reference tag in EDIFACT formats, and the reference IDs in ASC X12.

8.5.10 Comments

Use this column to note any free form comments to the template. Usually this column is used to note the business requirement and usage of the data field.

8.6.1 TEMPLATE TYPE Command

This command specifies the type of template. There are 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 is padded. If longer, it is 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 has reached 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 are 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 are omitted from the data tables.

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

8.6.2 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 is not 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 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.

For example, Table 8-6 shows five payments under a batch.

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:

The five payments generate the four groups (instances) shown in Table 8-7 for the new level.

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 are 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. Following are two examples for these scenarios based on the five payments and grouping conditions previously illustrated. Table 8-8 and Table 8-9 show the generated output.

Scenario 1: Group Records Only

EFT File Structure:

• BatchRec

  • PaymentGroupHeaderRec

  • PaymentGroupFooterRec

Scenario 2: Group Records and Individual Records

EFT File Structure:

BatchRec

• PaymentGroupHeaderRec

  • PaymentRec

• PaymentGroupFooterRec

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 thePaymentAmount (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.

8.6.3 DEFINE SEQUENCE Command

The DEFINE SEQUENCE command define 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, and MAXIMUM:

8.6.4 RESET AT LEVEL

The RESET AT LEVEL subcommand defines where the sequence resets its starting number. It 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).

8.6.5 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 does not 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.

8.6.6 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.

8.6.7 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.

8.6.8 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.

8.6.9 Delimiter Subcommand

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

8.6.10 Using the SUBSTR Function

Use the SUBSTR function to break down concatenated strings into smaller strings that can be placed into different fields. For example,Table 8-10 shows five invoices in a payment.

Using the concatenation definition shown in Table 8-11:

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

8.6.11 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 is 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 do not 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 are delimiters in the data, you can use the escape character "?" to retain their meaning. For example,

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

Which source?? equates to Which source?

Note that the escape character itself must be escaped if it is 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:

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.

8.6.12 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.

8.6.13 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.

8.6.14 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:

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 Section 8.5.3, "Format Column."

8.6.15 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:

8.8.1 Expressions

Expressions can be used in the data column for data fields and some command parameters. An expression is a group of XML extract fields, literals, functions, and operators. Expressions can be nested. An expression can also include the "IF" control structure. When an expression is evaluated it always generates a result. Side effects are not allowed for the evaluation. Based on the evaluation result, expressions are classified into the following three categories:

• Boolean Expression - an expression that returns a boolean value, either true or false. This kind expression can be used only in the "IF-THEN-ELSE" control structure and the parameter of the display condition command.

• Numeric Expression - an expression that returns a number. This kind of expression can be used in numeric data fields. It can also be used in functions and commands that require numeric parameters.

• Character Expression - an expression that returns an alphanumeric string. This kind of expression can be used in string data fields (format type Alpha). They can also be used in functions and command that require string parameters.

8.8.2 Control Structures

The only supported control structure is "IF-THEN-ELSE". It can be used in an expression. The syntax is:

IF <boolean_expressionA> THEN
   <numeric or character expression1>
[ELSIF <boolean_expressionB THEN
   <numeric or character expression2>]
...
[ELSE
   <numeric or character expression3]
END IF

Generally the control structure must evaluate to a number or an alphanumeric string. The control structure is considered to a numeric or character expression. The ELSIF and ELSE clauses are optional, and there can be as many ELSIF clauses as necessary. The control structure can be nested.

The IN predicate is supported in the IF-THEN-ELSE control structure. For example:

IF PaymentAmount/Currency/Code IN ('USD', 'EUR', 'AON', 'AZM') THEN      
   PayeeAccount/FundsCaptureOrder/OrderAmount/Value * 100   
ELSIF PaymentAmount/Currency/Code IN ('BHD', 'IQD', 'KWD') THEN      
   PayeeAccount/FundsCaptureOrder/OrderAmount/Value * 1000   
ELSE      
   PayeeAccount/FundsCaptureOrder/OrderAmount/Value   
END IF; 

8.8.3 Functions

Following is the list of supported functions:

• SEQUENCE_NUMBER - is a record element index. It is used in conjunction with the Define Sequence command. It has one parameter, which is the sequence defined by the Define Sequence command. At runtime it increases its sequence value by one each time it is referenced in a record.

• COUNT - counts the child level extract instances or child level records of a specific type. Declare the COUNT function on a level above the entity to be counted. The function has one argument. If the argument is a level, then the function counts all the instances of the (child) level belonging to the current (parent) level instance.

  For example, if the level to be counted is Payment and the current level is Batch, then the COUNT returns the total number of payments in the batch. However, if the current level is RequestHeader, the COUNT returns the total number of payments in the file across all batches. If the argument is a record type, the count function counts all the generated records of the (child level) record type belonging to the current level instance.

• INTEGER_PART, DECIMAL_PART - returns the integer or decimal portion of a numeric value. This is used in nested expressions and in commands (display condition and group by). For the final formatting of a numeric field in the data column, use the Integer/Decimal format.

• IS_NUMERIC - boolean test whether the argument is numeric. Used only with the "IF" control structure.

• TRUNCATE - truncate the first argument - a string to the length of the second argument. If the first argument is shorter than the length specified by the second argument, the first argument is returned unchanged. This is a user-friendly version for a subset of the SQL substr() functionality.

• SUM - sums all the child instance of the XML extract field argument. The field must be a numeric value. The field to be summed must always be at a lower level than the level on which the SUM function was declared.

• MIN, MAX - find the minimum or maximum of all the child instances of the XML extract field argument. The field must be a numeric value. The field to be operated on must always be at a lower level than the level on which the function was declared.

• FORMAT_DATE - Formats a date string to any desirable date format. For example:

  FORMAT_DATE("1900-01-01T18:19:20", "YYYY/MM/DD HH24:MI:SS")

  produces the following output:

  1900/01/01 18:19:20

• FORMAT_NUMBER - Formats a number to display in desired format. For example:

  FORMAT_NUMBER("1234567890.0987654321", "999,999.99")

  produces the following output:

  1,234,567,890.10

• MESSAGE_LENGTH - returns the length of the message in the EFT message.

• RECORD_LENGTH - returns the length of the record in the EFT message.

• INSTR - returns the numeric position of a named character within a text field.

• SYSDATE, DATE - gets Current Date and Time.

• POSITION - returns the position of a node in the XML document tree structure.

• REPLACE - replaces a string with another string.s

• CONVERT_CASE - converts a string or a character to UPPER or LOWER case.

• CHR - gets the character representation of an argument, which is an ASCII value.

• LPAD, RPAD - generates left or right padding for string values.

• AND, OR, NOT - operator functions on elements.

• DISTINCT_VALUES - equivalent to the XPATH function DISTINCT-VALUES. Returns a sequence in which all but one of a set of duplicate values, based on value equality, have been deleted. Usage: distinct_values(fieldname).

• INCREASE_DATE - increments a date by the number of days specified.

  Usage:

  increase_date(.//date, 2)

  returns a date value two days after the value of .//date

• DECREASE_DATE - decreases a date by the number of days specified.

  Usage:

  decrease_date(.//date, 2)

  returns a date value two before the value of .//date

• Other SQL functions include the following. Use the syntax corresponding to the SQL function.

  • TO_DATE

  • LOWER

  • UPPER

  • LENGTH

  • GREATEST

  • LEAST

  • DECODE

  • CEIL

  • ABS

  • FLOOR

  • ROUND

  • CHR

  • TO_CHAR

  • SUBSTR

  • LTRIM

  • RTRIM

  • TRIM

  • IN

  • TRANSLATE

8.9.1 Key Words

There are four categories of key words and key word phrases:

• Command and column header key words

• Command parameter and function parameter key words

• Field-level key words

• Expression key words

8.9.2 Command and Column Header Key Words

The following key words must be used as shown: enclosed in <>s and in all capital letters with a bold font.

• <LEVEL> - the first entry of a data table. Associates the table with an XML element and specifies the hierarchy of the table.

• <END LEVEL> - declares the end of the current level. Can be used at the end of a table or in a standalone table.

• <POSITION> - column header for the first column of data field rows, which specifies the starting position of the data field in a record.

• <LENGTH> - column header for the second column of data field rows, which specifies the length of the data field.

• <FORMAT> - column header for the third column of data field rows, which specifies the data type and format setting.

• <PAD> - column header for the fourth column of data field rows, which specifies the padding style and padding character.

• <DATA> - column header for the fifth column of data field rows, which specifies the data source.

• <COMMENT> - column header for the sixth column of data field rows, which allows for free form comments.

• <NEW RECORD> - specifies a new record.

• <DISPLAY CONDITION> - specifies the condition when a record should be printed.

• <TEMPLATE TYPE> - specifies the type of the template, either FIXED_POSITION_BASED or DELIMITER_BASED.

• <OUTPUT CHARACTER SET> - specifies the character set to be used when generating the output.

• <NEW RECORD CHARACTER> - specifies the character(s) to use to signify the explicit and implicit new records at runtime.

• <DEFINE LEVEL> - defines a format-specific level in the template.

• <BASE LEVEL> - subcommand for the define level and define concatenation commands.

• <GROUPING CRITERIA> - subcommand for the define level command.

• <END DEFINE LEVEL> - signifies the end of a level.

• <DEFINE SEQUENCE> - defines a record or extract element based sequence for use in the template fields.

• <RESET AT LEVEL> - subcommand for the define sequence command.

• <INCREMENT BASIS> - subcommand for the define sequence command.

• <START AT> - subcommand for the define sequence command.

• <MAXIMUM> - subcommand for the define sequence command.

• <MAXIMUM LENGTH> - column header for the first column of data field rows, which specifies the maximum length of the data field. For DELIMITER_BASED templates only.

• <END DEFINE SEQUENCE> - signifies the end of the sequence command.

• <DEFINE CONCATENATION> - defines a concatenation of child level elements that can be referenced as a string in the parent level fields.

• <ELEMENT> - subcommand for the define concatenation command.

• <DELIMITER> - subcommand for the define concatenation command.

• <END DEFINE CONCATENATION> - signifies the end of the define concatenation command.

• <SORT ASCENDING> - format-specific sorting for the instances of a level.

• <SORT DESCENDING> - format-specific sorting for the instances of a level.

8.9.3 Command Parameter and Function Parameter Key Words

These key words must be entered in all capital letters, nonbold fonts.

• PERIODIC_SEQUENCE - used in the reset at level subcommand of the define sequence command. It denotes that the sequence number is to be reset outside the template.

• FIXED_POSITION_BASED, DELIMITER_BASED - used in the template type command, specifies the type of template.

• RECORD, LEVEL - used in the increment basis subcommand of the define sequence command. RECORD increments the sequence each time it is used in a new record. LEVEL increments the sequence only for a new instance of the level.

8.9.4 Field-Level Key Words

• Alpha - in the <FORMAT> column, specifies the data type is alphanumeric.

• Number - in the <FORMAT> column, specifies the data type is numeric.

• Integer - in the <FORMAT> column, used with the Number key word. Takes the integer part of the number. This has the same functionality as the INTEGER function, except the INTEGER function is used in expressions, while the Integer key word is used in the <FORMAT> column only.

• Decimal - in the <FORMAT> column, used with the Number key word. Takes the decimal part of the number. This has the same functionality as the DECIMAL function, except the DECIMAL function is used in expressions, while the Decimal key word is used in the <FORMAT> column only.

• Date - in the <FORMAT> column, specifies the data type is date.

• L, R- in the <PAD> column, specifies the side of the padding (Left or Right).

8.9.5 Expression Key Words

Key words and phrases used in expressions must be in capital letters and bold fonts.

• IF THEN ELSE IF THEN ELSE END IF - these key words are always used as a group. They specify the "IF" control structure expressions.

• IS NULL, IS NOT NULL - these phrases are used in the IF control structure. They form part of boolean predicates to test if an expression is NULL or not NULL.

8.9.6 Operators

There are two groups of operators: the boolean test operators and the expression operators. The boolean test operators include: "=", "<>", "<", ">", ">=", and "<=". They can be used only with the IF control structure. The expression operators include: "()", "||", "+", "-", and "*". They can be used in any expression.

Table 8-12 lists the operators and describes their usage.

8.9.7 Reference to XML Extract Fields and XPATH Syntax

XML elements can be used in any expression. At runtime they are replaced with the corresponding field values. The field names are case-sensitive.

When the XML extract fields are used in the template, they must follow the XPATH syntax. This is required so that the BI Publisher engine can correctly interpret the XML elements.

There is always an extract element considered as the context element during the BI Publisher formatting process. When BI Publisher processes the data rows in a table, the level element of the table is the context element. For example, when BI Publisher processes the data rows in the Payment table, Payment is the context element. The relative XPATH you use to reference the extract elements are specified in terms of the context element.

For example to refer to the PayeeName element in a Payment data table, specify the following relative path:

Payee/PayeeInfo/PayeeName

Each layer of the XML element hierarchy is separated by a backslash "/". You use this notation for any nested elements. The relative path for the immediate child element of the level is just the element name itself. For example, you can use TransactionID element name as is in the Payment table.

To reference a parent level element in a child level table, you can use the "../" notation. For example, in the Payment table if you must reference the BatchName element, you can specify ../BatchName. The "../" provides Batch as the context; in that context you can use the BatchName element name directly as BatchName is an immediate child of Batch. This notation goes up to any level for the parent elements. For example if you must reference the RequesterParty element (in the RequestHeader) in a Payment data table, you can specify the following:

../../TrxnParties/RequesterParty

You can always use the absolute path to reference any extract element anywhere in the template. The absolute path starts with a backslash "/". For the PayeeName in the Payment table example above, you have the following absolute path: /BatchRequest/Batch/Payment/Payee/PayeeInfo/PayeeName

The absolute path syntax provides better performance.

The identifiers defined by the setup commands such as define level, define sequence and define concatenation are considered to be global. They can be used anywhere in the template. No absolute or relative path is required. The base level and reset at level for the setup commands can also be specified. BI Publisher can find the correct context for them.

If you use relative path syntax, then you should specify it relative to the base levels in the following commands:

• The element subcommand of the define concatenation command

• The grouping criteria subcommand of the define level command

The extract field reference in the start at subcommand of the define sequence command should be specified with an absolute path.

The rule to reference an extract element for the level command is the same as the rule for data fields. For example, if you have a Batch level table and a nested Payment level table, then you can specify the Payment element name as-is for the Payment table. Because the context for evaluating the Level command of the Payment table is the Batch.

However, if you skip the Payment level and you have an Invoice level table directly under the Batch table, then you must specify Payment/Invoice as the level element for the Invoice table.

The XPATH syntax required by the template is very similar to UNIX/LINUX directory syntax. The context element is equivalent to the current directory. You can specify a file relative to the current directory or you can use the absolute path which starts with a "/".

Finally, the extract field reference as the result of the grouping criteria sub-command of the define level command must be specified in single quotes. This tells the BI Publisher engine to use the extract fields as the grouping criteria, not their values.

8.9.8 Notes on Viewing eText Output from a Browser

If the report data contains Simplified Chinese characters and the <OUTPUT CHARACTER SET> is set to GBK, then the Chinese characters do not display properly in Internet Explorer 7 with gbk2312 encoding. This issue may also occur in other non-English encodings as well, such as native Japanese and Korean. The output renders appropriately with Firefox 3.5, when setting the character set to be GBK in the eText template and setting the browser encoding to be GBK or GB2312. You can work around this issue by setting <OUTPUT CHARACTER SET> to utf-8. Note that this is a browser display issue only. The text file is generated correctly.