Extending the Journal Entry Voucher Upload (Excel)
When analyzing a customer request, I got the impression that the Custom Codes of the Fine-Tuning Activity Account Assignment Types could be a good fit to their requirement. So, I configured a list of values for Custom Code 1 and made it available in postings and logistical objects - wherever it was needed. My colleague Employee as Additional Account Assignment for Project Tasks how to use Account Assignment Types. In our example, we use a customer-specific one.wrote in his article
So, now I have added the possibility to use this code at SAP Business ByDesign's user interface - but there is one additional need, to use it in the Journal Entry Voucher from Microsoft Excel. In the following, I will describe how to modify the predelivered upload excel.
Adapting the Excel Workbook
- At first, you need an XML Editor (e.g. Notepad++).
- Retrieve the Excel sheet provided by SAP. To get it, just go to the Work Center General Ledger, Work Center View Journal Entry Voucher, and select New > Journal Entry Voucher from Microsoft Excel. When it opens, just save it to your disk.
- Adapt Microsoft Excel. Right-click at the ribbon-bar and choose Customize the Ribbon. At the screen that pops up, activate the Developer ribbon.
Check Availability of Fields
- Open the Excel file, select the table where you would enter a posting and click on Source in the Developer ribbon.
- Compare the fields with those that are in the XML source. It might be that the field you require is there - but just not mapped to a field in the table.
If this is the case, you can add it with drag and drop (drop it to the empty column right of the item table).
If not - proceed with the next step.
- Close Microsoft Excel.
- Rename the Excel file - it should end with zip (instead of xlsx).
- Extract it. It will create a directory structure.
- In directory xl, open the file xmlMaps.xml with your XML editor.
- Locate the node Item within the node AccountingEntryFlatType.
- Add some lines within this node, e.g. after DebitCreditCode:
<xs:element maxOccurs="1" minOccurs="0" name="CUSTOM_CODE1" type="tns:CodingBlockCustomField1Code" />
It should look like this:
- Additionally, we will have to declare the types CodingBlockCustomField?Codes. This is declared in an upper part, node Schema16. I added my lines after type declaration AccountingBusinessTransactionTypeCode
<xs:minLength value="1" />
<xs:maxLength value="5" />
- Close the editor. In File Explorer, go up one directory level and "zip" it again - containing your modified xmlMaps.xml.
- Rename the zip file to xlsx.
Add Field to Journal Entry Line Items
- Open the file in Microsoft Excel.
- Set the cursor into the Journal Entry Line Items and open the map (click on Source - see above).
- Now, you will see the fields we have just added.
- Now, you can add the Custom Code 1 with drag and drop.
- Rename the column title.
- You are done. Your are now able to upload data using the Custom Code 1 (in this example).
In case you want to use the Journal Entry Voucher Excel with different, predelivered fields, here are the parameters you will need to add them.
|Field||Element Name||Element Type / Type Name||Min / Max||Type Representation||Remark|
|Custom Code 1||CUSTOM_CODE1||CodingBlockCustomField1Code||1 / 5||Code|
|Custom Code 2||CUSTOM_CODE2||CodingBlockCustomField2Code||1 / 5||Code|
|Custom Code 3||CUSTOM_CODE3||CodingBlockCustomField3Code||1 / 5||Code|
|Customer-specific Text||CUSTOM_TEXT1||CodingBlockCustomText||1 / 40||Text|
|Employee||EmployeeID||EmployeeID||1 / 20||Identifier|
|Partner Segment||PartnerSegmentID||OrganisationalCentreID||1 / 20||Identifier||Type scheme is already declared.|
|Partner Profit Center||PartnerProfitCentreID||OrganisationalCentreID||1 / 20||Identifier||Type scheme is already declared.|
|Local Currency Amount||LocalCurrencyAmount||Amount||Amount||Type scheme is already declared.|
|Line Item Currency Amount||LineItemCurrencyAmount||Amount||Amount||Type scheme is already declared.|
|Quantity UOM||QuantityTypeCode||QuantityTypeCode||1 / 10||Code|
|Fund||FundID||FundID||1 / 35||Identifier|
|Function||FunctionID||FunctionID||1 / 35||Identifier|