In this blog, we are going to see how to generate output using eText Template in Fusion BI Publisher I am going to show this using sample data. Carriage Return. Add a Header table which is given below. For headers, give the header name within quotes ex: 'Emplid'. Anonymous 10 March at Aaron M 29 October at Hassan 30 November at Rajya Lakshmi 26 November at Unknown 7 January at Hassan 19 February at Anonymous 6 February at Unknown 20 February at Hassan 29 February at Anonymous 5 May at Dakota Buck 15 December at Unknown 6 February at Joe 8 February at Unknown 19 March at Anonymous 25 April at It has three subcommands:.
The Define Level command must always have one and only one base level subcommand. 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. The parameter of the sort command is a comma-separated list of elements by which to sort the group.
The five payments generate the four groups instances shown in Table for the new level. Table The Five Payments Generated. 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 and Table show the generated output. Table Example of Group Records Only. 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. They can be declared in any order. 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. It is a mandatory subcommand. In some cases the sequence is reset outside the template. For example, a periodic sequence may be defined to reset by date. 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. 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. 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.
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 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. 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.
The delimiter subcommand specifies the delimiter to separate the concatenated items in the string. Use the SUBSTR function to break down concatenated strings into smaller strings that can be placed into different fields. For example, Table shows five invoices in a payment. Table Example of Five Invoices in a Payment. Table Concatenation Definition. Some formats require a different character set than the one that was used to enter the data in Oracle Applications. 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 example,. 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. 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. Output Length Mode can be set to "character" or "byte". 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 ". Ensure to set the appropriate format mask for the field to be displayed. For more information on formatting numbers, see Section 8.
Enter the command as follows:. When the actual data does not fill the specified block size, the remainder of the block is filled with a specified filler character.
Enter a name for this block. Figure Example of Filer Block Usage. This section describes the rules and usage for expressions in the template.
It also describes supported control structures and functions. 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. 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. It can be used in an expression. The syntax is:. 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 control structure can be nested.
For example:. 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. 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. 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.
This is used in nested expressions and in commands display condition and group by. Used only with the "IF" control structure. 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. 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. The field to be operated on must always be at a lower level than the level on which the function was declared.
Returns a sequence in which all but one of a set of duplicate values, based on value equality, have been deleted. This section lists the reserved key word and phrases and their usage. The supported operators are defined and the rules for referencing XML extract fields and using literals. 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" will always refer to the EFT record.
The database record will be 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:. 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 the reset at level as Batch. To continue numbering across batches, define the reset 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. 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. The increment basis subcommand specifies if the sequence should be incremented based on record or extract instances.
Note that for levels with multiple records, if you use the level-based increment all the records in the level will have the same sequence number. The record-based increment will assign 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. 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. 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 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. The item subcommand specifies the operation used to generate each item.
An item is a child-level expression that will be concatenated together to generate the concatenation string. The delimiter subcommand specifies the delimiter to separate the concatenated items in the string. 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:. Some formats require a different character set than the one that was used to enter the data in Oracle Applications.
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 will not allow certain characters. To ensure that known invalid characters will not be transmitted in your 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 example,. 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 would be:. The invalid character command has a single parameter that is a string of invalid characters that will cause 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 will be checked in terms of the output character set. If no invalid characters are found, the system will proceed to formatting. 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.
The default thousands or group separator is a comma "," and the default decimal separator is ". Use the Number Thousands Separator command and the Number Decimal Separator command to specify separators other than the defaults. For example, to define ". For more information on formatting numbers, see Format Column.
This section describes the rules and usage for expressions in the template. It also describes supported control structures and functions. 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 will always generate 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. 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. It can be used in an expression. The syntax is:. 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 control structure can be nested. For example:. 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 will increase 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.
The function has one argument. If the argument is a level, the function will count 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 will return the total number of payments in the batch.
If the argument is a record type, the count function will count all the generated records of the child level record type belonging to the current level instance. This is used in nested expressions and in commands display condition and group by. Used only with the "IF" control structure. 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.
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. The field to be operated on must always be at a lower level than the level on which the function was declared. This section lists the reserved key word and phrases and their usage. The supported operators are defined and the rules for referencing XML extract fields and using literals.
Associates the table with an XML element and specifies the hierarchy of the table. Can be used at the end of a table or in a standalone table. It denotes that the sequence number is to be reset outside the template. LEVEL increments the sequence only for a new instance of the level. Takes the integer part of the number. Takes the decimal part of the number.
They specify the "IF" control structure expressions. There are two groups of operators: the boolean test operators and the expression operators. They can be used only with the IF control structure. They can be used in any expression.
XML elements can be used in any expression.
0コメント