Monday 30 June 2014

AX2012 R2 - Import BAI2 Bank Statement

Overview

Advanced bank reconciliation is a new feature of AX2012, available from R2 onwards.

This new feature allows for the import of bank statements that can be automatically reconciled from within AX2012. This blog shows how to setup the InBound port to import and transform a BAI2 bank statement file.

BAI2 File Format


The Bank Administration Institute (BAI) file format is used to electronically transmit transaction data from a bank to an organisation. Bank Administration Institute version 2 (BAI2) splits the payment amount into separate invoice references and corresponding payments.

Note : BAI2 files are the file formats not always provided by some banking corporations, and as such bank file formats may need to be developed to suit the customer’s needs. The BAI2 file format is used here for demonstration purposes only.

The BAI2 file format is a structure text file with headers, lines and footer records. Each field is separated by a comma similar to a CSV file.

With a multi date file format, each date must be contained within its own section, with a header and line for each section.

Sample file can be found here : Download Sample File
Example 1 – Single date file

Example 2 – Multi date file

AIF Setup


In the AOT, under the Resources node, locate the BAI2 files. These files translate the electronic bank statements from their original format to a format that Microsoft Dynamics AX can use.

The files that are required are called :
  • AOT\Resources\BAI2CSV_to_BAI2XML_xslt 
  • AOT\Resources\BAI2XML_to_Reconciliation_xlst
Right-click the BAI2CSV_to_BAI2XML_xslt file, and then click Open

In the Preview form, select XML document in the File type field. 


Click Export to generate XSLT templates and save the template. Close the form.

Repeat the process for the BAI2XML_to_Reconciliation_xlst file.


Whilst in the development environment, click the Tools menu option and select “Application integration framework > Manage transforms”



Create a new record, give this a name and an appropriate description, ensure that the type = “XSL”, then click load. Load the BAI2CSV_to_BAI2XML_xslt file.
Create a second record and load the BAI2XML_to_Reconciliation_xlst file.

Close the form and close the development environment.

InBound Ports


Before you can receive the BAI2 statement electronically, you must register custom services and adapters, manage operations and inbound transforms, and activate inbound ports.

Browse to System administration > Setup > Services and Application Integration Framework

Click New to create a new inbound port.

In the Port name and Description fields, enter a suitable name and description of the inbound port. In the Adapter field, select File system adapter as the adapter name. In the URL field, enter a path for receiving electronic data.

On the Service contract customisations FastTab, click Service operations to open the Select service operations form.

Add in the following bank service operations to the “Selected service operations” section.

On the Processing options FastTab, select the Transform all requests check box to include inbound transforms. Then click the “Inbound transforms” button.

Click New to create a transform. Select the first of the transforms that you have already created previously.
Create a second record and add the second transform file that you have created.



These files should be in the following order to process the BAI2 file correctly.
  1. BAI2CSV_to_BAI2XML_xslt
  2. BAI2XML_to_Reconciliation_xlst
Close the form.

On the Troubleshooting FastTab, set the logging mode to “Original document”.



On the Security FastTab, select the partition and company that this Inbound port relates to.

Once complete, click the “Activate” button to finish, this may take some minutes to complete. Close the forms when finished.

This completes the InBound port setup and AX2012 R2 is now ready to import and transform a BAI2 file.

42 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi Andy,

    I get the following error when trying to import the bank statement file in the BAI2 format. The error I get is as follows:

    Error occurred while processing file inside transform pipeline: The file exists.
    An error occurred while importing data. Check the order of Inbound Transforms or refer AIF Exception Log for details.
    .txt importing failed.
    1 files are found with error.

    I have had a look at the AIF exception log and the message displayed is the same as the first line on the error. Are you able to shed any light into this? Also would you happen to have a breakdown of the BAI2 file in terms of the field names as per AX field names as I am writing my own BAI2 file to import. (I don't actually have one). I'm testing with one line that im trying to import.

    Kind Regards

    ReplyDelete
    Replies
    1. Check that the transforms are in the correct order, also has the troubleshooting been set to "Orginal document" ?

      Delete
    2. Hi Andy, when i am trying to import a csv file of BAI2 format for one transaction code on transaction type (16) is going to debit instead of credit. The amounts should go to credits. May i know the reason for this. is there any setup available for this or any code change needed.

      Thank in advance
      Raja

      Delete
  3. Hi,

    If possible, please share the Transformation mapping code form this above file import.

    Br Abbas

    ReplyDelete
  4. Can anyone provide me the sample bank statement files for AX 2012 bank reconciliation

    ReplyDelete
    Replies
    1. Dinesh, please send me a PM with your email details and I will send you a sample file.

      Delete
  5. Can you please provide me sample bank statement files for Ax 2012

    ReplyDelete
    Replies
    1. I've added a download link just above the images of the sample files on this page, please feel free to download this one.
      It is a multi day file for a USD bank account, if you wish to use this with other currency bank accounts then replace all references to USD in the file with the currency of your choice.
      remember though that the file contains opening/closing balances, so if you change any of the values then this will mean those balances will need to be changed to match.

      Delete
  6. Hi, I received the below error while processing BAI file
    "Error occurred while processing file inside transform pipeline: Input string was not in a correct format."

    Do you have any idea or solution.

    ReplyDelete
    Replies
    1. Sounds like either the BAI file is in the incorrect format, or you have the transforms in the wrong order. Try using the sample file here on the blog in your tests, if it works then your setup is correct and its the file you are using that is at fault.

      If you do use my test file ensure that the currency in my file matches the currency of your test bank account.

      Delete
    2. Thanks Andy for your reply.

      But i can able to process some of march files. where as April files i am getting issue. I compared both files there are in same format.

      Delete
    3. After the Hotfix update its working fine.

      Delete
    4. HI Suneel

      am facing same problem could you please share hotfix number

      Delete
  7. Hi
    I did the same steps and setup for MT940 with consider to select MT940 in Resources and when I import the Statement I got this massage (0 bank statement is imported from C:\Users\axadmin\Desktop\B\SWIFT_MT940.txt.)
    any Idea
    Thank in Advance

    ReplyDelete
    Replies
    1. Hello Abdullah,

      did you find any solution about this message(0 bank statement is imported from C:\MT940.txt.), i am also getting same info message.

      Thank you in Advance.

      Delete
  8. Hi

    Could you please suggest what action should be taken for below error.

    When i try to import the file it throws me error as " Error occurred while processing file inside transform pipeline: The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.".

    Thanks in Advance

    ReplyDelete
    Replies
    1. From what the error message staes it looks like the date/time is in a format that AX doesn't recognise. Would recommend amending it in the file to see if that works.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Arun, all I can suggest is that you fiddle with the date/time to see what part of it is causing you an issue. The sample file in this blog has a date format that works, I do however, suspect that its the tiem element that may be giving you an issue.

      Delete
    4. Hi Andy

      Thanks for Suggestion

      Thanks

      Delete
  9. My issue is little weird. I have done this on two servers..one with AX demo company data-usmf and other with our company setups.

    1.The one with AX demo data without restriction to company 'usmf' worked just fine. FYI -I tested the import only on usmf
    2.The server with our data(which has five entities a,b,c,d,DAT)I am trying to run this adv.bank.recon on company A and works only if restrict to company A is selected. If its blank meaning partial is selected and company is blank, i am getting below errors.

    An error occurred while importing data. Check the order of Inbound Transforms or refer AIF Exception Log for details

    The order seems correct on Inbound Transforms -- i have double checked that. If the order is not correct it wouldn't have worked at all.

    would appreciate any suggestions on this.
    Thanks in advance.

    ReplyDelete
    Replies
    1. Yes, there is an issue with using the same Inbound Port for multiple legal entities. To get around this issue I had to create a new Inbound Port for each legal entity. This seemed to work for me.

      Delete
  10. Thanks for your reply Andy

    ReplyDelete
  11. hello
    I have this error

    Input file does not conform to messageset schema.

    thanks in avance

    ReplyDelete
    Replies
    1. Yassine,

      Looks like the issue could be with your bank statement file, you will need to check that it's fformat is correct

      Delete
  12. Hi Andy, I have setup my BAI2 file as per the single file format above and I can import the file successfully. However, the transaction lines in my file are coming through in the debit column of the bank statement form rather than the credit column. I have tested another file by adding a (-) to the transaction lines and this transfers as a negative debit. I'm hoping you might be able to advise where I'm going wrong here?

    ReplyDelete
    Replies
    1. LadyAXAus, the transaction type is controlled by the code on the line, for each line "16" there will follow a transaction type. I use the codes 399 & 699, please substitute the code you are using for the opposite of these.

      Delete
    2. Hi Andy, i am also facing the same issue here . for one transaction code of type 16 the amounts are going to debit instead of debit. If i change the code 466 amount is going to credit. i did not understand what is the issue here. Kindly help me on this . is there any setup available or any code change is required here.

      Thanks
      Raja

      Delete
  13. Hello Andy ,

    I am using this functionality for 25 bank accounts, one per each company in AX. I am having below error coming for specific files..

    Error:Transform was unsuccessful during bulk i.
    Subsystem:AifImportUtil-processFile
    Exception message:Error occurred while processing file inside transform pipeline: Input string was not in a correct format.

    ReplyDelete
  14. Hi Andy, I got this message when importing, "Error occurred while processing file inside transform pipeline: Data at the root level is invalid. Line 1, position 1.". I have used your sample, dou you have any suggestion?

    ReplyDelete
    Replies
    1. I can only suggest that you step through the setups again to ensure that one step has not been missed, the "Troubleshooting" step is one that is often missed. Otherwise check that the currency in the file and the currency of the bank account match.

      Delete
  15. Thanks Andy. It helped me in configuration.

    ReplyDelete
  16. HI EXPERTS
    PLEASE HELP I DID PERFORM ALL ACTION AS YOU DESCRIBED IN DETAIL BUT FOR TEST PURPOSE I AM UNABLE TO FIND HOW FILE WILL BE CREATE HOW DATA WILL BE ENTER IN BAI2 FILE I WANT TO CREATE FILE MANUALLY AND THAN IMPORT IT.
    YOUR RESPONSES WILL BE APPRECIATE THANK YOU

    ReplyDelete
    Replies
    1. Hi Haris,
      Normally the file would be created by the sending bank, but the URL below describes how the file is formatted and what is expected on each line.

      http://www.sepaforcorporates.com/swift-for-corporates/bai2-format-specification/

      Regards
      Andy

      Delete
  17. we are using the advanced reconciliation In AX2012R3 using the advance bank reconciliation functionality, in Reconciled transactions inquiry, the fields Bank statement and Bank statement date are empty.

    ReplyDelete
  18. Hello Andy, I have followed your instruction to configured Advance reconciliation (BAI2) in AX2012 R3 CU10. While importing the BAI2 file, it is not picking the opening balance, though there is an opening and closing balance there in BAI2 file. Could you please suggest me what I am missing?

    ReplyDelete
  19. Could not get anything to import. Tried multiple files, even tried to create from scratch, but kept getting the 'check the transform' error. I completely removed the transforms from the inbound port I was setting up and then re-added them. Took 3 separate files after that and the data did not have to match the bank or existing transactions in the system.

    ReplyDelete
  20. Hi Andy,

    I have done the setups in line with the steps mentioned above in your blog. However, I am getting the following error while trying to import the BAI2 statement that is attached in the blog.

    An error occurred while importing data. Check the order of Inbound Transforms or refer AIF Exception Log for details.

    The inbound transforms are in the following order in the configuration:
    1. BAI2 CSV to XML
    2. BAI2 XML to Recon

    Please advise.

    ReplyDelete
    Replies
    1. Hi Abhishek,

      I have encountered the same after following all the setups mentioned. Also, I have looked the Exceptions under System administration > Periodic > Services and Application Integration Framework > Exceptions. I found there the exception message for the error "There is no service with namespace = 'http://tempuri.org' and external name = 'BankStmtService'." - under Subsystem column it says "AifInboundProcessingService-processRequest"

      Hi Andy,

      I don't know what to do after this. Please advise.

      Best regards,
      Monch

      Delete
  21. This comment has been removed by a blog administrator.

    ReplyDelete
  22. This comment has been removed by a blog administrator.

    ReplyDelete