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 Former Member wrote in his article Employee as Additional Account Assignment for Project Tasks how to use Account Assignment Types. In our example, we use a customer-specific one.

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

Preparation

1. At first, you need an XML Editor (e.g. Notepad++).
2. 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.
3. 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

1. Open the Excel file, select the table where you would enter a posting and click on Source in the Developer ribbon.
   
2. 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

1. Close Microsoft Excel.
2. Rename the Excel file - it should end with zip (instead of xlsx).
3. Extract it. It will create a directory structure.
   
4. In directory xl, open the file xmlMaps.xml with your XML editor.
5. Locate the node Item within the node AccountingEntryFlatType.
6. 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:
   
7. 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>

      

8. Close the editor. In File Explorer, go up one directory level and "zip" it again - containing your modified xmlMaps.xml.
9. Rename the zip file to xlsx.

Add Field to Journal Entry Line Items

1. Open the file in Microsoft Excel.
2. Set the cursor into the Journal Entry Line Items and open the map (click on Source - see above).
3. Now, you will see the fields we have just added.
   
4. Now, you can add the Custom Code 1 with drag and drop.
5. Rename the column title.
   
6. 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.