Configuring Advanced Bank Reconciliation Bank Statement Importing

Pre-requisites:Configuring Advanced Bank Reconciliation Bank Statement Importing

Since the most common way that the bank reconciliation files com to you as a text file, we need to have a way to massage the file into a format that Dynamics AX is able to use for the import. The way that this is done is through XML style sheet transformations (XSLT). Don’t worry though – you don’t have to hand craft this file – it’s already saved away for you within the AOT for you to download and start using.

1.To do this start off by opening up AOT.
Tip: If you are in AX and have developer rights then just press CTRL+D.
2.Within the AOT tree, expand out the Resources folder and you will see that there are a lot of goodies files away there for you to use.
3.Scroll down the list and you will see that there are a number of different bank transformation files that are available for you to use. The ones that we are interested are for the BAI2 format, and are the BAI2CSV_to_BAI2XML_xslt file which will convert the CSV file into an XML file, and also the BAI2XML_to_Reconciliation_xslt file that converts the XML file into something that AIF is able to read.
4.Right-mouse-click on the BAI2CSV_to_BAI2XML_xslt file and select the Open option.
5.This will open up a new resource window for the file.
6.Click on the File Type dropdown list and change it to XML document.
7.And then click on the Export button and save the file to your desktop.
8.After you are done, just close the window.
9.Next right-mouse-click on the BAI2XML_to_Reconciliation_xslt file and click on the Open menu item.
10.When the resource form is displayed, click on the File Type dropdown list and change it to XML document.
11.And then click on the Export button and save the file to your desktop.
12.After you are done, just close the window.

Now that we have the transformation files, we want to create a rule that will use the files to convert the CSV files into a format for the reconciliation to use.

1.To do this, click on the Tools menu item within AOT, select the Application Integration Framework menu item and select the Manage Transforms menu item.
2.When the Manage Transforms maintenance form is displayed, click on the New button in the menu bar to create a new record.
3.Give the new record a Name and Description of BAI2 CSV to XML.
4.Then click on the Load button to import in the XSLT file that you just downloaded from the Resources.
Tip: You may need to change the file type to XSLT files in order to see them though.
5.Select the BAI2CSV-to-BAI2XML.xslt file and click the Open button.
6.Now you will see all of the XSLT within the Content section.
7.Click on the New button in the menu bar to create another record.
8.Give the new record a Name and Description of BAI2 XML to Reconciliation.
9.Then click on the Load button to import in the XSLT file that you just downloaded from the Resources and this time select the BAI2XML-to-Reconciliation.xslt
Once you have done that you can exit from the form.

1.The importing of the Bank Reconciliation file is actually being done through AIF and the AIF ports and we need to configure one that will be used to turn our CSV files into Bank Statements within Dynamics AX. For the non-developer these may seem a little daunting to configure, but it’s not really that hard.
2.To do this, click on the Inbound Ports menu item within the Services and Application Integration Framework folder of the Setup group within the System Administration area page.
3.When the Inbound Ports maintenance form is displayed, click on the New button within the menu bar to create a new record.
4.Set the Port Name to AdvancedBankReconciliation and the Description to Advanced Bank Reconciliation.
5.Change the Adapter to File system adapter.
6.Now create a folder that you will put all of your bank statement files into.
7.Then click on the URI dropdown and when the Browse For Folder dialog box is displayed, navigate to the inbound file folder that you just created and click on the OK button.
8.Now you will see that the file path is in the URI field.
9.Next, we need to link the port with the bank services. To do this click on the Service Operations button within the Service Contract Customizations tab group.
10.When the Select Service Operations dialog box is displayed, select the following service operations and then click on the < button to select them:
11.BankStmtService.create, BankStmtService.delete, BankStmtService.find, BankStmtService.getChangedKeys, BankStmtService.getKeys, BankStmtService.
12.After they have been added to the Selected Service Operations click on the Close button to exit from the form.
13.Now expand out the Processing Options fast tab group.
14.Check the Transform All Requests flag and then click on the Inbound Transforms button.
15.When the Inbound Transforms maintenance form is displayed, click on the New button to create a new record.
16.From the Transform Name dropdown list, select the BAI2 CSV to XML record and press CTRL+S to save the record.
17.When the warning message is displayed, click on the Yes button.
18.Click on the New button again to create another record.
19.This time, select the BAI2 XML to Reconciliation record from the Transform Name dropdown list.
20.Again, save the record and then click on the Yes button when the warning dialog is displayed.
21.Finally select the BAI2 CSV to XML record and click on the Move Up button in the menu bar to make this the first transform. This is very important because this sets the order of the transforms and you need to convert the CSV file to XML and then to the Reconciliation format.
22.After you have done that, click on the Close button to exit from the form.
23.Now expand out the Troubleshooting fast tab group and change the Logging Mode to Original Document.
24.One thing to keep in mind is that AIF will run separately under the users ID, and as a result will chose the default partition and company that the user is assigned to. If you have multiple partitions and/or multiple companies then you may want to specify where you want this reconciliation to run. To do this, expand the Security fast tab group.
25.From the Restrict To Partition dropdown list select the partition that you want to use.
26.And then select the company that you want to apply this to from the Restrict To Company dropdown list.
27.Now all that you need to do is click the Activate button in the menu bar to make the port usable by AX.
28.If everything goes fine, then you will get an InfoLog telling you that everything worked great.
29.Once that is done, just click on the Close button to exit from the form.
Now we have all of the building blocks to start configuring our bank statements to use the import feature.

1.To do start off doing this, click on the Bank Statement Format menu item within the Bank Reconciliation folder of the Setup group within the Cash And Bank Management area page.
2.When the Bank Statement Format maintenance form is displayed, click on the New button within the menu bar to create a new record.
3.Set the Statement Format and the Name to BAI2.
4.Then select the AdvancedBankReconciliation port from the Inbound Port dropdown list.
5.Now just click the Close button to exit from the form.

The final setup task that we need to perform now is to enable the Advanced Bank Reconciliation feature on our bank.

1.To do this, click on the Bank Accounts menu item within the Common group of the Cash And Bank Management area page.
2.When the Bank Accounts list page is displayed, double click on the bank account that you want to use to import your bank statements into.
3.When the Bank Account maintenance form is displayed, expand the Reconciliation fast tab group and then click the Advanced Bank Reconciliation flag.
4.You will get a warning that says that once this is turned on then you cannot turn it off, and click on the OK button.
5.This will enable all of the fields now within the Reconciliation fast tab group.
6.Click on the Statement Format dropdown list and select the BAI2 statement format that you just created and you are done.
7.Now click on the Close button to exit from the form.

There is one quick housekeeping matter that you need to check on before we start importing in our bank statements, and that is that the number sequences have been configured for the Advanced Bank Reconciliation.

1.To do this, click on the Cash And Bank Management Parameters menu item within the Setup group of the Cash And Bank Management area page.
2.When the Cash And Bank Management Parameters maintenance form is displayed, switch to the Number Sequences page and make sure that the Statement ID, Download ID, and Reconciliation ID number sequences are configured.

Now we can import our bank statements.

1.Start off by putting your bank statement files into the Inbound Port folder that you created earlier on.
2.Now click on the Bank Statements menu item within the Common group of the Cash And Bank Management area page.
3.When the Bank Statements list page is displayed, click on the Statement button within the New group of the Bank Statement ribbon bar.
4.This will open up the Import Bank Statements dialog box.
5.From the Bank Account dropdown list, select the bank account that you want to import the statement into. Notice that only the bank accounts that have been enabled for the Advanced Bank Reconciliation show up here.
6.The Statement Format will also automatically populate from the Bank Account.
7.Click on the folder icon to the right of the File Folder field and select the inbound ports folder where the bank statements are stored away in.
8.Now click on the folder icon to the right of the Select File field.
9.This will allow you to select the individual statement that you want to import and then click on the Open button.
10.Now all you need to do is click on the OK button to start the import.
11.If everything goes well then you will get an InfoLog that says that the statement was loaded.
12.When you refresh the Bank Statements list page you will see that there is a new bank statement.
13.And if you drill into the statement you will see that the detail is also loaded in.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>