I am trying to produce an eText Output File containing Cheque Data to be sent to a third party system. The file contains a number of cheques each consisting of several header records followed by a repeating group of REMIT Lines records. I have used a Delimiter Based Template with nested tables. A sample file should look like this:
The first table LEVEL is OutboundPayment and the nested table level is DocumentPayable (standard Payment Process XML). This produces a file in the correct structure however my problem is that the Remittance Lines needs to be fixed format. This fine for the Alpha columns but I can not get the Amounts to format correctly. I have set the Format for my Amount elements to Alpha but:
1) TO_CHAR(ElementName, '99,999,990.00') does not work. It is ignored and an amount of 600 is shown as 600 not 600.00. Likewise 815.2 is not shown as 815.20
2) FORMAT_NUMBER(ElementName, "99,999,990.00") caused wierd output and I have strings of ???????????s at the start of these lines.
3) Changing the format to Number, ##,###,##0.00 DOES work but then the fields are not fixed width and I can't use LPAD.
4) Finally I tried to rewrite the Template as FIXED_POSITION_BASED and although the numbers now format correctly and are correctly left-padded to 13 chars this only works if I have the tables separate so I end up with all the header records first and then all the REMIT Lines at the end of the file. If I embed the DocumentPayable Table in the OutboundPayment Table as I did in the DELIMITER_BASED template I get absolutely NO OUTPUT whatsoever so it seems that nested tables are not supported in FIXED_POSITION_BASED templates.
I have been wrestling with this for two days now and my deadline is today so any help or advice greatly appreciated.