0 Replies Latest reply: Jul 19, 2012 5:20 AM by 950507 RSS

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

    950507
      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.