The SAP iRPA bot 'Automated Upload of Manual Entries via API' with scope item ID 4CA (we will use 4CA to denote this bot in the rest of this post) is one of the most popular finance bots delivered by SAP S/4HANA team. With this bot, you can create journal entries in a batch using spreadsheets as data sources.
The spreadsheets accepted by the bot must have a predefined format. Usually, you can download the template file from the corresponding page of the bot in SAP Intelligent RPA Store, and fill in the actual data of your journal entries to be created. However, in its latest version, the bot 4CA provides a new template file called 'Master Template'. The new template can provide more capability and flexibility for creating journal entries, but the way to use it is a bit different from the templates in previous versions.
In this blog, we will provide a tutorial to demonstrate how to customize your template for the bot using the master template.
After acquiring the 4CA bot from the bot store, you can download the master template file from the package.
This is an Excel spreadsheet with macros (extension .xlsm). When you open the file, you should be able to see a spreadsheet with eight worksheets. In case you see a warning about macros in the file, just dismiss it.
There are eight worksheets in the file, in which the first two contain documentation and configurational data, and the rest six contain the structure of the input data of the bot 4CA. The following table describes the contents in each spreadsheet:
Note sheets 3 to 8 correspond to the structure of the input data of the API (Journal Entry - Post (Synchronous)) used by the 4CA bot to created journal entries. For more information on the API, please refer to its technical document on SAP Help Portal.
The master template is not meant to be used as the input file of the 4CA bot directly, but a source for the users to generate specific templates. Since the master template contains all the data fields supported by the API, and most of them are not mandatory fields to create the journal entry, it makes sense for users to work with simpler templates which only contain the necessary fields for their business scenarios.
In order to generate a specific template with only relevant fields from the master template, you need to configure the template types and the data fields relevant to each type in the worksheet Field List. As examples, there are already three predefined template types in the master template file:
You can select these types from the drop-down list in cell B4:
The name of the template type consists of two parts: the code and the description, separated by a hyphen. For example, the first type has code AB and description Journal Entry, the second type has code DR and description Customer Invoice, etc.. You may notice that the codes are identical to the corresponding document type codes in SAP S/4HANA, but you can actually use any other strings for the codes.
Whether a data field is relevant to a certain type of template is defined in column F (Relevant to Template) in the table below the drop-down list.
This column lists all the template types (only codes) in which the data field should appear, separated by semicolons. For example, the field PostingDate (row 10 in the screenshot above) has values AB;DR;KR in column F, which means it should appear in all the three types of templates. On the other hand, the field Debtor in the screenshot below has a single value DR in this column, which means it should only appear in the template for customer invoices.
After selecting a template type, you can click on the button Generate Template next to the drop-down list to generate a template of selected type. You will be asked to choose a path to save the template file. The file generated will only contain the data fields (columns) which are relevant to its type. As a result, if a data sheet has no fields, it will be invisible in the generated template file.
Let's take an example to choose the template type AB - Journal Entry and generate the template. In the resulting file, you will have only four worksheets (except the first Field List sheet) which are Header, Items, Tax Items, and Withholding Tax Items, since none of the fields in the sheets Debtor Items and Creditor Items are relevant to this template type.
You can now fill in the new template file with the data needed and use it as the input file of the 4CA bot to create new journal entries.
It is possible to add your own template types to the master template. To do this, you first need to add the code - description pair to the master template. All available types are listed in column K of the worksheet Field List. You can already see the four predefined selections in this column:
To add your own type, you just need to add a new value of code - description pair in the same column right below the last one which is available. For example, if you want to add a new type Asset Posting with code AA*1, you may just add the new pair AA - Asset Posting to the column as follows:
As already mentioned before, the code AA can be anything else, and we only choose it for easier understanding. Also remember to put a space before and after the hyphen. After you added the new value, a new item appears in the drop-down list in cell B4.
Till this step, you have successfully created a customized template type. To connect the new type to certain data fields, you still need to add the code of the new type to column F of the field list table. It is very simple to do this: if a data field is relevant to the new template type, just add the new code AA in the corresponding row in column F, as shown below:
To keep it simple, here we only mark the first several data fields as relevant to the new type AA.
By saving the changes to the master template file, you now successfully configured your new template type. If you choose the new type from the drop-down list and click Generate Template button, a new template file will be created with exactly the fields which are relevant to it.
*1: Note that the API to post journal entries used by the 4CA bot does not actually support the entry type asset posting. We just use this type as an example to show how to customize the template.
The master template provided by the latest version of the 4CA bot provides the maximum flexibility to customize your own templates. You can even define different types of templates to use in different business scenarios. To sum up, creating a specific type of template consists the following simple steps:
The spreadsheets accepted by the bot must have a predefined format. Usually, you can download the template file from the corresponding page of the bot in SAP Intelligent RPA Store, and fill in the actual data of your journal entries to be created. However, in its latest version, the bot 4CA provides a new template file called 'Master Template'. The new template can provide more capability and flexibility for creating journal entries, but the way to use it is a bit different from the templates in previous versions.
In this blog, we will provide a tutorial to demonstrate how to customize your template for the bot using the master template.
The structure of the master template
After acquiring the 4CA bot from the bot store, you can download the master template file from the package.
Download page of the master template file
This is an Excel spreadsheet with macros (extension .xlsm). When you open the file, you should be able to see a spreadsheet with eight worksheets. In case you see a warning about macros in the file, just dismiss it.
The master template file
There are eight worksheets in the file, in which the first two contain documentation and configurational data, and the rest six contain the structure of the input data of the bot 4CA. The following table describes the contents in each spreadsheet:
| # | Sheet name | Contents in the sheet |
| 1 | User Guide | Information on how to use the master template file. |
| 2 | Field List | A table to list all the fields in subsequent sheets, together with their attributes including type, length, etc. You can generate a specific template from the master template using the information and controls here. |
| 3 | Header | The data fields in the header of the journal entry. |
| 4 | Items | The data fields in the general line items of the journal entry. |
| 5 | Debtor Items | The data fields in the customer line items of the journal entry. |
| 6 | Creditor Items | The data fields in the vendor line items of the journal entry. |
| 7 | Tax Items | The data fields in the tax related line items of the journal entry. |
| 8 | Withholding Tax Items | The data fields in the withholding tax line items of the journal entry. |
Note sheets 3 to 8 correspond to the structure of the input data of the API (Journal Entry - Post (Synchronous)) used by the 4CA bot to created journal entries. For more information on the API, please refer to its technical document on SAP Help Portal.
Using the master template to create specific templates
The master template is not meant to be used as the input file of the 4CA bot directly, but a source for the users to generate specific templates. Since the master template contains all the data fields supported by the API, and most of them are not mandatory fields to create the journal entry, it makes sense for users to work with simpler templates which only contain the necessary fields for their business scenarios.
In order to generate a specific template with only relevant fields from the master template, you need to configure the template types and the data fields relevant to each type in the worksheet Field List. As examples, there are already three predefined template types in the master template file:
- AB - Journal Entry
- DR - Customer Invoice
- KR - Supplier Invoice
You can select these types from the drop-down list in cell B4:
Choosing a predefined template type
The name of the template type consists of two parts: the code and the description, separated by a hyphen. For example, the first type has code AB and description Journal Entry, the second type has code DR and description Customer Invoice, etc.. You may notice that the codes are identical to the corresponding document type codes in SAP S/4HANA, but you can actually use any other strings for the codes.
Whether a data field is relevant to a certain type of template is defined in column F (Relevant to Template) in the table below the drop-down list.
The relevance of a field to template types
This column lists all the template types (only codes) in which the data field should appear, separated by semicolons. For example, the field PostingDate (row 10 in the screenshot above) has values AB;DR;KR in column F, which means it should appear in all the three types of templates. On the other hand, the field Debtor in the screenshot below has a single value DR in this column, which means it should only appear in the template for customer invoices.
After selecting a template type, you can click on the button Generate Template next to the drop-down list to generate a template of selected type. You will be asked to choose a path to save the template file. The file generated will only contain the data fields (columns) which are relevant to its type. As a result, if a data sheet has no fields, it will be invisible in the generated template file.
Let's take an example to choose the template type AB - Journal Entry and generate the template. In the resulting file, you will have only four worksheets (except the first Field List sheet) which are Header, Items, Tax Items, and Withholding Tax Items, since none of the fields in the sheets Debtor Items and Creditor Items are relevant to this template type.
Worksheets in a template with Journal Entry type
You can now fill in the new template file with the data needed and use it as the input file of the 4CA bot to create new journal entries.
Adding your customized template types
It is possible to add your own template types to the master template. To do this, you first need to add the code - description pair to the master template. All available types are listed in column K of the worksheet Field List. You can already see the four predefined selections in this column:
Definition of predefined template types
To add your own type, you just need to add a new value of code - description pair in the same column right below the last one which is available. For example, if you want to add a new type Asset Posting with code AA*1, you may just add the new pair AA - Asset Posting to the column as follows:
Adding a new template type AA - Asset Posting
As already mentioned before, the code AA can be anything else, and we only choose it for easier understanding. Also remember to put a space before and after the hyphen. After you added the new value, a new item appears in the drop-down list in cell B4.
A new option in the drop down list
Till this step, you have successfully created a customized template type. To connect the new type to certain data fields, you still need to add the code of the new type to column F of the field list table. It is very simple to do this: if a data field is relevant to the new template type, just add the new code AA in the corresponding row in column F, as shown below:
Defining the fields relevant to type AA
To keep it simple, here we only mark the first several data fields as relevant to the new type AA.
By saving the changes to the master template file, you now successfully configured your new template type. If you choose the new type from the drop-down list and click Generate Template button, a new template file will be created with exactly the fields which are relevant to it.
New template of type AA - Asset Posting
*1: Note that the API to post journal entries used by the 4CA bot does not actually support the entry type asset posting. We just use this type as an example to show how to customize the template.
Conclusion
The master template provided by the latest version of the 4CA bot provides the maximum flexibility to customize your own templates. You can even define different types of templates to use in different business scenarios. To sum up, creating a specific type of template consists the following simple steps:
- Add the type code and description to the master template;
- Add the type code to all the data fields which are relevant to the new template type;
- Select the new template type from the drop down list;
- Generate and save the new template.
- SAP Managed Tags:
1 Comment
