This discussion is archived
0 Replies Latest reply: Jul 19, 2012 3:20 AM by 950507 RSS

Can not get format Numbers in a Fixed Format in a Delimiter Based Template

950507 Newbie
Currently Being Moderated
Hello All,

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:

$$NEWCHQ
$$CHQNUM:123
$$DATE:12/07/2012
$$AMOUNT: 615.20
$$PAYEE:Payment Test
$$VENDORNUM:95432
$$ADDONE:Address Line 1
$$ADDTWO:Address Line 2
$$ADDTHREE:City
$$ADDFOUR:County
$$ADDFIVE:Post Code
$$REMIT:18JUL12 INVNUM VOUCHERNUM INVTYPE 99,999,990.00 99,999,990.00 99,999,990.00
$$REMIT:18JUL12 INVNUM VOUCHERNUM INVTYPE 99,999,990.00 99,999,990.00 99,999,990.00
$$NEWCHQ
$$CHQNUM:234

etc...

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.

Kind Regards,

Ian.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points