Date calculations in eText templates

 

Aahh, eText. That most hated or Oracle templates. One area where this template really falls is in the area of date calculation, or date manipulations. Example a standard requirement is to calculate an end date, or proposed/expected end date for some transaction. Check is valid for only ..30 days from date of issue. cleartext.blogspot.com

The official documentation does no mention any way to x number of days to dates. But if you search on supportweb, you will come across the INCREASE_DATE function.

To add 30 days to today’s date, use INCREASE_DATE(SYSDATE(),30). To substract days, use a negative number for the offset. cleartext.blogspot.com

But that’s it, eText does not provide a way to get the month, year, or day from a date. So those will have to be calculated in whatever onpremise/cloud system is being used and the value must be present in the xml. eText also does not provide a way to add extra functions or XSLT functions in the templates, like in BIP RTF templates. cleartext.blogspot.com

 

 

Originally published on cleartext.blogspot.com

Sequence Numbers in eText templates

 

As I said earlier, Oracle’s eText template is really painful to work with. Its does not have support variables or arrays, so when the need arises to have something temporarily stored somewhere, you are lost. The one thing eText does have, is a method to generate sequence numbers. Basically you use the DEFINE_SEQUENCE command.

The define sequence command has four subcommands: reset at level, increment basis, start at, and maximum. 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.  cleartext.blogspot.com

To generate the sequnce numbers, use the SEQUENCE_NUMBER function in the template.

But what if you just want to keep count of something, and not count levels or records in the data ? Just leave out the INCREMENT_BASIS completely.  cleartext.blogspot.com

So now, whenever SEQUENCE_NUMBER function is called, the current value will be printed, and the counter will be increased by 1. This happens everytime the function is invoked.

There are some payment interfaces where the number has to be incremented in steps of 5 or 10. For example, one of the ADP checkprinter format template requires a counter to start at 30, and be incremented in steps of 5. So its 30, 35, 40, 45…

To do that the SEQUENCE_NUMBER function should be used with some mathematics.

 

 

Originally published on cleartext.blogspot.com

eText : Conditional NewLines

Continuing this series on my rants on eText template. I came across what looked like a pretty straightforward requirement from a customer. They wanted a conditional newline in the generated output. Instead of printing each XML record in a new line, the ask was to print four XML records in one text line. Batches of four. So a newline had to be printed after each 4 XML records. cleartext.blogspot.com

If you refer to the Oracle documentation on the eText template, there is a place to specify the New Record charachter.

The instruction ‘Carriage Return’ inserts a new line charachter just before the command <NEW RECORD> appears in the template. So conditionally insert a new line in a template, the Carriage Return has to be set to NULL. cleartext.blogspot.com

And then begins the tedious part. All through the template, whenever you need to insert a new line, you have to print the charachter using the CHR command. cleartext.blogspot.com
Example:  
Another One: 

And to conditionally print a new line after 4 xml records, the MOD command has to be used to calculate the position. Like so:


Its tedious, but thats the only way to do it in eText. cleartext.blogspot.com


cleartext.blogspot.com

eText woes

 

What is the worst report template to work with ? After 9 years of working on varous Oracle report template formats, I am happy and frustrated to report that that would the the eText BIP template from Oracle.

cleartext.blogspot.com

It is commonly used to generate Payment Texts, to be processed by banks and other financial institutions, like ADP. It is the only Oracle template which can generate output in .txt format. It has very little functions, and all the documentation can be summarized to this one page. That’s it. All the functions and settings which can be used on the eText template is right there. The output of this template is not meant to be human-read, it is always meant to be transmitted to a banking or payment system via secure ftp. From the initial version of the template system, no new function has been added. So it is kinda version independent, there is no dependency on what version of BIP you have installed. eText templates designed on older versions of BIP will happily work on newer versions. And vice versa. But this is probably the only good thing ever about this template format.

cleartext.blogspot.com

Whereas BIP RTF templates also have some functions supported, there are ways to inject some xslt/xdofx functions in different namespaces into them. So this gives rise to redundant, but useful bunch of functions. And there is an added functionality of writing xsl templates within BIP RTF templates. And if even that does not work, one can link in external jars with added functions. But none of these can be done on eText templates. You have to make do with what you have.

cleartext.blogspot.com

Well this makes working on eText templates extremely frustrating. And error prone. The fact that you are designing payment system integrations increases the seriousness of the situation. If there are even fractional errors during the processing, that would direct the banks to pay a different amount out.Scary.

The good thing is that there is a small community of eText developers/consulants which can be accessed via supportweb. Most of the usual issues one faces are documented with workarounds. If a requirement cannot be done , all you can do is blame Oracle and tell the customer.

Here is an issue: you use the Number command to print numbers in the eText output.

cleartext.blogspot.com

Internally the template uses the format_number function to convert the source into a number. But this function has a problem, it errors out if the source data has non-numbers in it. It cannot even handle a null. So if the PAYMENT tag in the xml contains null or any non-integer, the template will error out.

Simplest solution ? Just multiply the source with 1  !

Multiplying with 1 will work fine if PAYMENT has numbers in it. And in case of any non-number, it will just get converted to 0 ! No errors thrown !

Weird. But it works, no need to do a separate check of null or isnumber.

 

Originally published on cleartext.blogspot.com