Extending the Journal Entry Voucher Upload (Excel)
Tags:
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 articleSo, 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
Preparation
- 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.
Adapt XMLmap
- 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:simpleType name="CodingBlockCustomField1Code">
<xs:annotation>
<xs:appinfo source="http://sap.com/esi/RepresentationTerm">Code</xs:appinfo>
</xs:annotation>
<xs:restriction base="xs:token">
<xs:minLength value="1" />
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
- 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).
Appendix
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 | Quantity | Quantity | Quantity | ||
Quantity UOM | QuantityTypeCode | QuantityTypeCode | 1 / 10 | Code | |
Fund | FundID | FundID | 1 / 35 | Identifier | |
Function | FunctionID | FunctionID | 1 / 35 | Identifier |