Today I had an issue importing the output of NetSuite’s positive pay feature into Bank of America’s CashPro. The default output is fixed length format without any decimals in the value and date formatted at MMddyy. I don’t even think the CashPro system will accept an amount without decimals or the date not formatted as MM/dd/yyyy.
The fixed length values were as such:
[ACCOUNT][ISSUE(O)/VOID(V)][CHECK NUMBER][AMOUNT(no decimals)][DATE MMddyy][PAYEE]
Sample output with stock NetSuite functionality:
|
1 2 |
123456789123H 061215 123456789123O0000012345000000058333061115 Widget Vendor |
My old uploads were working fine as comma separated values, so I set out to try and make the advanced printing tool output in the CSV as I had been using.
The CSV format was as such:
[ACCOUNT],[ROUTING],[ISSUE(I)/CANCEL(C)],[CHECK NUMBER],[AMOUNT(decimals)],[DATE MM/dd/yyyy]
There were 5 main issues.
1. Adding the commas to separate the values
Adding a comma between every ${value} was simple enough to fix this issue
|
1 |
${value1},${value2},${value3} |
2. Inserting the bank routing number (the default export doesn’t have this)
Pretty simple to just hard code the value as Bank of America’s routing number to the output as such
|
1 |
,121000358, |
3. Changing the issue/cancel value (the stock was O/V but my imports use I/C)
This was just coded as prety simple check function at the top of the formatting, so I just need to change the alpha values for the function
|
1 2 3 4 5 6 7 8 |
<#function getVoidCheckIndicator payment> <#assign value = "I"> <#assign reversalDate = payment.reversaldate> <#if reversalDate?has_content> <#assign value = "C"> </#if> <#return value> </#function> |
4. Change the amount formatting to include a decimal
A bit of research and a StackOverflow answer led me to correcting this one by adding ?string(“0.00”) to the end of the getAmount(payment) value
|
1 |
${setPadding(getAmount(payment)?string("0.00"),"left","0",12)} |
5. Change the date format to long year with slashes to separate
Increased length of the date to 10, changed yy to yyyy, and adding slashes was easy enough,
|
1 |
${setLength(payment.trandate?string("MM/dd/yyyy"),10)} |
The resulting advanced template is below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<#-- format specific processing --> <#function getVoidCheckIndicator payment> <#assign value = "I"> <#assign reversalDate = payment.reversaldate> <#if reversalDate?has_content> <#assign value = "C"> </#if> <#return value> </#function> <#function getChequeNumber payment> <#assign value = ""> <#if payment.recordtype == "cashrefund"> <#assign value = payment.otherrefnum> <#else> <#assign value = payment.tranid> </#if> <#return value> </#function> <#-- template building --> #OUTPUT START# ${setPadding(cbank.custpage_pp_custrecord_2663_acct_num,"left","0",12)}H${setLength("",22)}${setLength(pfa.custrecord_2663_file_creation_timestamp?date?string("MMddyy"),6)}${setLength("",69)} <#list payments as payment> <#assign entity = entities[payment_index]> ${setPadding(cbank.custpage_pp_custrecord_2663_acct_num,"left","0",12)},121000358,${setLength(getVoidCheckIndicator(payment),1)},${setPadding(getChequeNumber(payment),"left","0",10)},${setPadding(getAmount(payment)?string("0.00"),"left","0",12)},${setLength(payment.trandate?string("MM/dd/yyyy"),10)} </#list> #OUTPUT END# |
Sample output of above template below:
|
1 2 |
123456789123H 061215 123456789123,121000358,I,0000012345,000000583.33,06/11/2015 |
Bank of America CashPro import profile setup:
Very minor change, but a good one to get started off on. I could not find much documentation about the positive pay formatting, and this was my first experience with the advanced templates.
Recent Comments