In this article we will look into how to create etext template. When we have a requirement to generate a fixed length or delimited output files using BI Publisher or HCM Extract we will use the e-text template to achieve this requirement.
I have come across a lot of people who are new to etext template and asked me to write an article on it with the basics and hence this article.
e-text template is also similar to rtf (Rich Text Format) template and will be created in word document and the file extension will also be .rtf, however it will be used to generate text ouputs (.txt format). It will also be uploaded to report the same way but with a different type of ‘e-text template’.
Sample Delimited e-text template looks like:
Sample Fixed Position based e-text template looks like:
Format Setup: This is the section that we define at the starting of the template and the settings in this section are applicable throughout the template.
There are two types of e-text templates:
FIXED_POSITION_BASED: FIXED_POSITION_BASED templates are used for fixed-length record formats. Ex: EFTs (Electronic Funds Transfer) and Payment files. If a field length is more, then the field will be truncated to the field length specified.
DELIMITER_BASED: Designated delimiters are used to separate the data fields. Ex: Comma (,), Pipe (|), tab (CHR(09)). If a field is empty, two delimiters will appear next to each other.
<OUTPUT CHARACTER SET>: This specifies the character set to be used when generating the output. There are multiple types of Character sets like ASCII, UTF-8, iso-8859-1
<NEW RECORD CHARACTER>: After writing every line, this character would be written to the output file. Normally it will have Carriage Return (newline) character. In some cases when client needs a different one, we will use a different new record character (; or / etc)
Format Data Records: This section comes up after the setup section and the data in this section will impact only this section. We normally use this section to write the logic that writes data to output file. Header/Detail/Trailer records. We can also write multiple records to output file for each record on the xml file. Explanation of commands that are part of Format Data are listed below. This is a full exhaustive list.
- <LEVEL>: This represents the XML group tag name from the XML file. If you want to write the header record, then use DATA_DS which is the highest level tag in the XML file and as it occurs only once in the XML file, the header record will also be written to output file only once. If we use any other group tag which is repeated multiple times, then the attributes within that level will be repeated on the output file. Ex; DATA_DS, etc.
- <DISPLAY CONDITION>: This is used to filter the records from XML file. For example lets see for each employee we have two rows on XML file one with ASSIGNMENT_TYPE of E and ET. And if we want to write only the data for E type, then we write ASSIGNMENT_TYPE = ‘E’ in the display condition. If we do not want to write any condition there, we can either exclude this row or write 1=1 which is always true and hence all records are written to output file. Ex: 1=1, ASSIGNMENT_TYPE = ‘E’, etc.
- <NEW RECORD>: This is a unique identifier in e-text template and it just assigns a name to each block. It is not displayed anywhere on the output and it does not exist in the XML file too. Used to uniquely identify each block/table in etext template. Ex: Header, Detail, Trailer, etc.
- <MAXIMUM LENGTH>:This is used to specify the maximum length of the attribute. If the attribute length is more than this value, then the attribute value will be truncated. Ex: 10, 100, 15, 50, etc. For DELIMITER_BASED templates only.
- <FORMAT>: This specifies the format of the attribute value. Alpha – Characters, Number – Numbers, Date – Date. We can also override the format of the attribute over here. Ex: “Number, 9999.99”, “Date,YYYYMMDD”, “NUMBER,000000000”, etc.
- <DATA>: This specifies the attribute XML tag from the XML file. This is case sensitive and need to copied from the sample XML file carefully. You can traverse on the XML file using the XPATH commands and provide an attribute at a different level. You can also convert the attribute to different formats and write if conditions, decode statements, instring, concatenations, substring etc. Ex: PRIMARY_HOME_PHONE, TO_CHAR(DATE,’YYYYMMDD’), DECODE(GENDER,’M’,’Male’,’F’,’Female’,’Unknown’), SUBSTRING(NAME,1,30) etc.
- <COMMENTS>:This is used to note down any comments for this attribute. If there are any changes to attributes/an attribute is added at a later date, then this comments can be used to note down them for better understanding at later point of time.
- <END LEVEL>: Any <LEVEL> should have a corresponding <END LEVEL> to complete the loop. If we do not add <END LEVEL> there are high chances that etext template does not work as desired and will run into issues or add some junk characters to the output.
- <POSITION>: This is used to input the starting position of the attribute on the output file. This is mainly used in fixed length e-text templates. Ex: 10, 15, 35, etc.
- <LENGTH>:This is used to specify the length of the attribute on the output file for fixed position templates. If the attribute length is more than this value, it will truncate the attribute value.
- <PAD>: This is used in fixed length etext templates to provide padding to the attribute value if the attribute value is less than the length specified. We can do the left padding or right padding and we can also mention the padding character to be space/0/any other character. Ex: “R, ‘ ‘”, “L, ‘0’”, etc.
- <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.
- <END DEFINE SEQUENCE>: signifies the end of the sequence command.
- <DEFINE CONCATENATION>: defines a concatenation of child level item that can be referenced as a string 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.
If you have any questions, please feel free to reach out to me by posting in comments section.
If you are interested in learning Fusion Technical tools go through this post
If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM