Set up an SAP program with proposed journal : Aico

Set up an SAP program with proposed journal

Modified on Mon, 24 Jul 2023 at 10:18 AM

This article will show you how to create a journal from data found in an SAP program/report.

Setting up this task involves setting a series of usually hidden headers in the closing task to set the journal header fields. To follow the steps in the article How to set up a manual closing task with proposed journal to see how.

Set up the Automation manager task to run the SAP program from which you will extract data to calculate a journal. This process is described here.

1. Select the correct template in the Aico document settings. For example, PROGRAMJRNL or PROGRAMAUTOJRNL. The template set up for your purposes may have a different name, but this will have been agreed with you as part of the implementation project.

6. Select a period and press Generate.

Note: It is not important which period you select as the purpose here to to generate the excel template that will then use to create the journal in all periods going forward.

In the center panel of the Aico Journal, you will see there are now two attachments (7). These are the SAP program reports you generated (8).

Click on the Excel spreadsheet to download it. Open the file.

The Excel spreadsheet will contain two tabs:

1. SAP data contains the output from the SAP report

Note: If your original report came from a file share (read this article for more information) this tab will be called File Data.

2. Aico data (which will be empty the first time you run the report) but contains all the mappings needed to import data into the Aico journal template.

Use this Excel spreadsheet to make a template which is used each time the task is run.

Initially, the Aico data sheet will be empty. However, it will contain all the mappings required to import the data into the Aico document, as shown below.

7. Save the workbook as, for example, “Import Template – Journal ABCD.xlsx”.

Note: The .xlsx format is mandatory.

This Excel workbook gives you the layout of the SAP report and the mappings to the Aico document.

It is then a case of using an Excel formula to transform the data from the SAP data sheet into the journal lines required on the Aico data sheet.

It is recommended to add a Calculation Sheet to the template to help the process of transforming the data. This is not mandatory and you can add the excel formula directly onto the Aico data sheet itself.

Warning! Do NOT add formula on to the SAP data sheet, it is cleared each time the task is run.

8. Next extract the data you are are interested in from the SAP data sheet onto the Calculation Sheet using the Excel formula.

Info: There are a few limitations to the Excel formulas that Aico can support. For more information, read this article.

Example Journal

In the example below, we want to post a provision of 12% of the closing balance from a specific General Ledger account.

Based on the report layout, we know we want to find the last number from the SAP data sheet G9 as this contains the closing balance of the account.

The steps below show how we can use standard Excel formula to find the data we need and calculate the 12% provision.

First we use a formula to see if the SAP data sheet G9 contains a number and if so, it brings it across to the Calculations sheet.

Next, we put a line number against each value found from the report and then extract the Amount from the adjacent largest line number which we know is the closing balance from the account from the FBL3N report from which we want to calculate our provision.

This formula assigns a line number where the adjacent amount cell has a value.

The below formula finds the highest line number and then extracts the adjacent amount in the document currency into cell D8.

Remember to drag your row formula down sufficiently to cover the potential number of line items that a report might contain which will be different each month – this has been tested up to 50,000 rows but there is only an Excel limitation to the number of rows.

Add the account details, item text and currency in the Aico data sheet. In the below image, the account and item text information have been added into the Aico data sheet.

Next we map the numbers from the Calculation sheet.

This formula brings the closing balance number from the calculation sheet and if that number is positive will add it as a debit to the first account, if negative it will be a credit.

Map or Empty the headers from the template.

The currency code is brought in from the SAP datasheet but all other headers are cleared and will be set as normal by Aico when the task is generated.

Note: It is possible to import data into the headers using this method so you must be careful to only leave data in the headers that you intend to go into the document. It can overwrite headers that are left editable.

The final step is to connect the import template that you have created to the Automation Manager task containing the report.

Upload the template as an attachment to the task. (The attachments area is at the very bottom of the task template.)

Lastly, set the excel template you have just uploaded as the Import Template in the Task parameters.

The rest of the settings for the task (Companies, schedule groups etc.) are all set in the normal way.