Skip to Content

Hello All,

In this document, I would like to familiarize all the readers on how we can customize Journal Entry Voucher Excel template to add extension fields or existing fields from WSDL.


  1. Prerequisite
  2. Adding Existing Fields
  3. Adding Extension Fields
  4. Appendix


1) Prerequisite

To modify Journal entry Voucher template, we will nee following or similar tools

  • A Zip tool (i.e WinRar, WinZip,7-Zip)
  • A tool to Modify XML Files


First we will need to download the journal entry voucher excel from the system. to Understand how excel works, Open the Template with Excel.

Then switch to Developer tab and click on “Source”.

Developer-Source.jpg


If you don’t have the developer tab in your Excel, please go to “File” -> “Options” -> “Customize Ribbon” and enable the check box for developer:

Options.jpg


Now you should be able to see the underlying data structure on the right side. Please choose the XML Map “SAP_BYD_MAIN” to see the correct structure:

Xml maps.jpg

The Structure shows all fields which are currently available in the data source of the excel sheet. Note that this is only a subset of the data structure of the web service. So if we want to add additional fields of the service , we will need to adjust this structure first.


The bold attributes are mapped to cells in the sheet. To determine which cell and attribute is mapped to, simply click the corresponding attribute and excel will select the cell on the sheet. This works also vice versa. To Bind or unbind a cell, right click on an attribute and select “Map element” or “Remove element”. You can also Drag and drop to map attributes to specific cells on the sheet.

Xml Maps 2.jpg


2) Adding Existing fields

To adjust the data structure and the template, the data structure needs to be enhanced first. To do this, find the Excel sheet on your hard drive and change its extension to .zip. Then, open it with your preferred Zip-Tool and change to the folder “xl”:

Zip xmlmaps.jpg


Please extract the file “xmlMaps.xml” and open it with your preferred XML-editor (Notepad++ in this case). This file is very long as it contains many type definitions. The part which is interesting for this guide is the definition of the complex type “AccountingEntryFlatType”, around line 1350:


Edit.jpg


As you can see, this structure represents the tree in the Excel sheet, for example:


Strucutre.jpg

In this context, new fields can now be added. As an example the field “DraftIndicator” should be added, which controls if the created Journal Entry Voucher is created in draft mode or posted directly (the default is posted directly). This field is of the type “Indicator”, which is already defined in the schema, therefore we can use it without further customization. If you want to use a field whose data type is not present in the schema, you need to add it manually or use another compatible data type (eg. Note for string types).

To add this indicator, you add the following line (e.g. after ExternalID):


<xs:element maxOccurs=“1” minOccurs=“0” name=“DraftIndicator” type=“tns:Indicator”/>

This adds a new field to the data structure, which will be transferred “as is” to the underlying web service. If we want to add more complex logic behind a field, you can also use XML transformations before the upload to the service is done. In this case please search for the element “<Transformation direction=”in”>” in the xmlMaps.xml.

As this is a simple boolean field, process is now finished. Save the xml-File and re-add it to the zip-File (which was the Excel sheet before) – make sure you replace the existing file. If this is successful, rename the file ending to “xlsx” again and open it in Excel. As we can see, the DraftIndicator now appears in the tree listing.

The last step which is now necessary to make this field functional, is to add it to the sheet – map it to a cell.To do this, simply drag the field to a desired cell on header level. The field is now bound and – after specifying a proper heading – ready to use.


Drag.jpg

Please note: This field is of type “Indicator”, so you will need to supply a bool value (true, false).

3) Adding Extension Fields

To add extension fields to the Excel sheet is a little bit more complicated, but possible. First, we’ll need to create an extension field in the adaptation mode for the Journal Entry Voucher. Please read the system documentation for details on this topic.

To add this field to the Excel sheet, follow the first steps of adding an existing field, extract the xmlMaps.xml and open it in your editor. Now, depending on the place where we added the field (Header or Item level), we either need to add a field in the Header structure AccountingEntryFlatType or in the Item sub-node. In this example, we are going for a header field. Add a field to this structure with a name we can identify later, ideally named after the field:

<xs:element minOccurs=“0” name=“ExcelExtensionOnRoot” type=“xs:string” />

As this field is handled differently in the system, there is one more change in the xmlMaps.xml necessary, in the transformations section.


Xml Maps 3.jpg


Please find the element

Element.jpg

and add the following code snipplet before this element:

<xsl:template match=“ExcelExtensionOnRoot”>

   <xsl:element name = ”ExcelExtensionOnRoot” namespace=http://sap.com/xi/AP/CustomerExtension/BYD/A0014>

       <xsl:value-of select=“.”/>

   </xsl:element>

</xsl:template>

Please make sure to replace the values with your specific values. In the first line, replace the value in the match-attribute with the element name you used in the step above. Now we can save the XML file and integrate it back into the Excel sheet.

In the last step you need to bind the field to an Excel cell, please see the second chapter for an explanation of this process.


4) Appendix

4.1) Journal Entry Voucher Excel Template

  • Go to General Ledger work center
  • Select Journal Entry Voucher view
  • Click on New and select Journal Entry Voucher from Microsoft Excel
  • Save the template as Local Copy

4.2) WSDL File

Replace the stars you’re your actual system number:

https://my******.sapbydesign.com/sap/bc/srt/wsdl/sdef_MANAGEACCOUNTINGENTRYIN/wsdl11/ws_policy/document?sap-vhost=my****…

Or

  • Go to Application and user management work center
  • Service explorer View
  • Search for Service name : ManageJournalEntryIn
  • Click on Download WSDL
To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

  1. Andreas Czech

    Hi Harshal,

    is it possible to use Extension Fields if you are using the Soap webservice directly (without the Excel-Upload)? For example we try to use Extension fields for General Ledger entries with the Service ManageJournalEntryIn. But it seems that this is not possible.

    Regards

    Andreas

    (0) 
    1. Harshal Vakil Post author

      Hello Andreas,

      Yes it is possible to extension field in the structure of web serivce.

      In order to use the extension field with the web service you will need to modify the request and the response structure of the web service.

      1. Go to the work center/view. (i.e New Journal Entry Voucher)
      2. Click on Adapt->Enter Adoption Mode.  (Available for the technical user on the top Right Corner beside help and log off buttons)
      3. Click on Adapt and select -> Edit Screen
      4. Locate extension field which you have created from the section of the screen in the right pane.
      5. Under the field properties, Click on the link Further Usage for the selected Extension field
      6. Go to tab Services.
      7. Select the service with direction Inbound (i.e ManageAccountingEntryIn)
      8. Here you will see the column Field available. If this is checked for particular service then the field is already available in the web service structure. If Field available is not checked, Click on Add Field button to add the field to the request structure and also enhance the corresponding query

      After Adding the field to the web service, you can use the extension field.

      I hope this will help you. Let me know if you need further details.

      Regards,

      Harshal Vakil

      (0) 
  2. Lewis Peters

    Harshal,

    When I attempt to follow your steps for adding extension fields I get an error. As you state, I add the following code snippet (with my values substituted in):

    <xsl:template match=“TestDocumentDateItemLevel”>

        <xsl:element namespace=http://sap.com/xi/AP/CustomerExtension/BYD/A0014  >

            <xsl:value-of select=“.”/>

        </xsl:element>

    </xsl:template>


    To the item level structure. I’ve added this just between the beginning of the XSL template section denoted by the ‘contains local name’ line, and the end of the previous XSL template section. I then add the appropriate line to the items level in AccountingEntryFlatType:

    <xs:element maxOccurs=“1” minOccurs=“0” name=“TestDocumentDateItemLevel” type=“tns:Date”/>

    But when I change the spreadsheet from .zip back to .xlsx and open it, it says that the template is corrupt. What am I doing wrong?

    Corrupt Template.png

    Lewis

    (0) 
    1. Harshal Vakil Post author

      Hi Lewis,

      Yes, it is possible to add the extension field in the excel template.

      Since the error is generic, I can only assume that the issue is due to the extension field. May be the code in xml is not at the correct place or needs some corrections.

      Anyways I will test this and let you know.

      In the mean time, I had this excel from the past testing where I added the employee ID as field on the screen and was able to get this field in the Jev excel template as well.

      Can you send me the mail (You can find it in my profile), I can provide you this example file.

      Regards,

      Harshal

      (0) 
  3. Lewis Peters

    As a side note, after communicating with Harshal about this, if, after adding the extension field from the source into the spreadsheet, you come across this message:

    Incompatible Formatting.png

    Choose the ‘Use existing formatting’ option, that works.

    Lewis

    (0) 
      1. Lewis Peters

        Hi Bin,

        My guess is that your XML file refers to an extension field that doesn’t exist — have you made a typo? Without seeing your work it’s impossible for me to be sure.

        Lewis

        (0) 
        1. Benny Huang

          Hi Lewis,

          My config:

              

                

                 I just change:

                 <!– / added by bin.huang on 06/06/2016 –>

                 <xs:element minOccurs=”0″ maxOccurs=”1″ name=”xm_description” type=”xs:string”/>

                 <!– Changed by bin.huang on 06/06/2016 –>

                 <xsl:template match=”xm_description”>

          <xsl:element name=”xm_description” namespace=”http://sap.com/xi/AP/CustomerExtension/BYD/A0014“>

          <xsl:value-of select=”.”/>

          </xsl:element>

                 </xsl:template>

                 What is wrong? Thanks.

          (0) 
            1. Benny Huang

              Hi Lewis,

                   Yes, Harshal sent me an example, but it did not work when I follow the step in my system.

                   Could you tell me your email, I sent the step and the result to you.

                   Thanks.

              (0) 
  4. Kalaiselvan S

    Hi Harshal,

    Could you please help me out, how to achieve the journal entry upload excel to adding the extension field.

    I followed the above steps, but after convert the zip to excel, what i can do for the next step.

    Please elaborate the steps to achieve this.

    Thanks,

    Kalaiselvan S.

     

     

    (0) 
  5. Benny Huang

    Hi experts,

    I want to use the “Sales Order from Microsoft Excel®” template, and add the price field to the item.

    I feel the field PriceAndTaxCalculationItem->ItemMainPrice->Rate->DecimalValue is the price at wsdl. Is it ok?

    I add to xmlMaps.xml with:

    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”PriceAndTaxCalculationItem”>
    <xs:complexType>
    <xs:sequence>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”ItemMainPrice”>
    <xs:complexType>
    <xs:sequence>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”Rate”>
    <xs:complexType>
    <xs:sequence>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”DecimalValue” type=”tns:DecimalValue”/>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”MeasureUnitCode” type=”tns:MeasureUnitCode”/>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”CurrencyCode” type=”tns:CurrencyCode”/>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”BaseDecimalValue” type=”tns:DecimalValue”/>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”BaseMeasureUnitCode” type=”tns:MeasureUnitCode”/>
    <xs:element minOccurs=”0″ maxOccurs=”1″ name=”BaseCurrencyCode” type=”tns:CurrencyCode”/>
    </xs:sequence>
    <xs:attribute name=”actionCode” type=”tns:ActionCode”/>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    <xs:attribute name=”actionCode” type=”tns:ActionCode”/>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    <xs:attribute name=”actionCode” type=”tns:ActionCode”/>
    </xs:complexType>
    </xs:element>

     

    <xs:simpleType name=”DecimalValue”>
    <xs:annotation>
    <xs:appinfo source=”http://sap.com/xi/OriginalLang”>EN</xs:appinfo>
    </xs:annotation>
    <xs:restriction base=”xs:decimal”>
    <xs:totalDigits value=”28″ />
    <xs:fractionDigits value=”6″ />
    </xs:restriction>
    </xs:simpleType>

     

    <xsl:template match=”//PriceAndTaxCalculationItem”>
    <xsl:copy>
    <xsl:attribute name=”actionCode”>
    <xsl:value-of select=”’04′”/>
    </xsl:attribute>
    <xsl:for-each select=”@*”>
    <xsl:copy/>
    </xsl:for-each>
    <xsl:apply-templates/>
    </xsl:copy>
    </xsl:template>
    <xsl:template match=”//ItemMainPrice”>
    <xsl:copy>
    <xsl:attribute name=”actionCode”>
    <xsl:value-of select=”’04′”/>
    </xsl:attribute>
    <xsl:for-each select=”@*”>
    <xsl:copy/>
    </xsl:for-each>
    <xsl:apply-templates/>
    </xsl:copy>
    </xsl:template>
    <xsl:template match=”//Rate”>
    <xsl:copy>
    <xsl:attribute name=”actionCode”>
    <xsl:value-of select=”’04′”/>
    </xsl:attribute>
    <xsl:for-each select=”@*”>
    <xsl:copy/>
    </xsl:for-each>
    <xsl:apply-templates/>
    </xsl:copy>
    </xsl:template>

     

    But the running raise:Missing element ‘DecimalValue’, what is the problem?

     

    Best Regards,

    Benny

     

    (0) 

Leave a Reply