- SAP Managed Tags:
Key Words
SAP S/4HANA Cloud, essentials edition; Excel; API;
Introduction
This blog focuses on the implementation of the Fixed Asset master data creation (https://api.sap.com/api/FIXEDASSETCREATEMAIN/overview) Application Programming Interface (API) in SAP S/4HANA Cloud, essentials edition system through Excel using a Macro in VBA.
The same principle could apply to any other business object (e.g. Fixed Asset Modification)
Recommended approach
SAP recommends, in alignment with the implementing SAP S/4HANA with a Cloud Mindset and the Golden Rules, to use the following approach
Standard Operation Apps in SAP Best Practices:
“Manage Fixed Assets” App and create them manually
Standard Load Apps
Unfortunately for Fixed Assets, there is no Standard App to load.
Nothing appears in the Road Map Explorer as of August 2020 for SAP S/4HANA Cloud, essentials edition
“Migrate your data” App for Fixed assets (refer to Migration Objects Help and ) (Please note that as of 2008 the “Migrate your Data” is a regular App, so it’s no longer included in the “Manage Your Solution” App)
In order to create the “static” portion of the Fixed
Important Note: The “Migrate your data” App is designed, as the name suggests, that
Use Modern Integration Technologies (SAP Extensibility Explorer)
Side-by-Side
SAP Intelligent Robotic Process Automation (iRPA)
API’s for Integration <- SCOPE OF THIS BLOG!!!
As you can see, the scope of this blog should NOT be your first alternative to solve a business problem, but it’s an option if you have exhausted all the other approaches AND you consider the pre-requisites/caveats included in this blog.
When to use API’s
In principle, an API is a current and future strategy for integration within SAP Technology. API’s are very robust and reliable technology to integrate between systems with pre-delivered objects. You
Important considerations:
- As stated, before in the recommended approach, this should not be the first alternative
- All the regular controls, error handling
- The security of an API approach would be based on the API user. The Password for the API User will be prompted & validated while uploading the fixed assets.
Benefits:
- Robust
Important considerations
- Error handling
How to Enable API’s through Excel:
With all the above statements being said, here is one of the alternatives to use an API in integration with an Excel Spreadsheet:
Pre-requisites
1. Assuming you have setup the communication arrangement for “Finance - Fixed Asset Integration (SAP_COM_0563)” in S/4HANA Cloud
2. Communication User: API_USER, and its password, we will reference it as API_USER_PWD below in all this blog.
3. API Endpoint: https://myXXXXX-api.s4hana.ondemand.com/sap/bc/srt/scs_ext/sap/fixedassetcreatemain
4. Batchsize: how many records do you want to upload per batch. It can be any number but will be good if you evaluate based on your performance tuning exercise. For this writeup, batchsize is kept as 100.
5. Using the Postman or SOAPUI, please make sure the above API endpoint is reachable, and make sure you have executed the fixed asset upload via postman/soapui, and have your payload as your reference starting point for this Excel integration coding.
Step 1: Prepare your payload in postman
While you prepare the sample upload, please make sure it can be run, and you are able to upload via postman/or soapui.
Sample Postman Payload will look like as shown below:
Step 2: Create a new Excel workbook, with a sheet named “FixedAssets”.
Identify the fields from the above payload, that can go into the excel as data, and create a worksheet “FixedAssets” in Excel workbook and create few sample data ready.
We have following fields captured, along with data types
Below is the excel sheet, will look like
Step3: Create a new worksheet with the name “Payload”, and “RunStatus”.
If you would like to debug or find out the result of the upload, you can find more details here in these worksheets.
They will look like as below:
Worksheet : Payload, will have a TextFrame element, that will hold the complete soap request payload.
Worksheet: “RunStatus” - in this worksheet, the excel VBA macro will put the Fixed Asset ID, and the status whether uploaded or not.
Note: Since it is the batch upload, the program currently may not know if all of the assets are uploaded or not, but if the API call fails, you will see here the “Failed” status for all the assets.
Step 4: Create a new worksheet “Metadata”
Let us keep the API information here, so it should be easy to switch the endpoint to a different environment/system.
Create following text as shown below in screenshot, along with a Upload button.
API_URL = Endpoint url for the API
Username => Communication User thats assigned to the API, you can locate this in the S/4HANA Cloud app “Communcation Arrangements”.
Batchsize = you can default this to 100, but you can increase this as and when your volume of the excel data increases.
Upload Button=> a button with a text. We will write code to upload the data in VBA and the macro code (VBA) will be triggered when anyone clicks on this button.
Step 5: Create a VBA Macro
Rightclick on Button, and assign macro, then Edit, you will be landing in a “Microsoft Visual Basic for Applications – FixedAssets.xlsm” code editor.
Lets modularize the flow of the logic into different functions. This can be written in many different ways, so please feel free to take your own approach here, but the concepts below will be the same.
Global Variables
Function: CleanupData
Goal: At initial run, this function will clear the following data in the excel workbook.
Worksheet: “Payload” : The TextElement data will be cleared.
Worksheet: “RunStatus” : The AssetId, and the status values will be cleared/deleted.
Function: GenerateGUID
Goal: This function will create a unique GUID that’s required for the SOAP Request “MessageId”. It will be used in the payload as shown below
We can construct the GUID in several ways, please evaluate this logic below.
uuid = "uuid:" & WorksheetFunction.Concat(WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 4294967295#), 8), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 65535), 4), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(16384, 20479), 4), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(32768, 49151), 4), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 65535), 4), WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 4294967295#), 8))
Function: preparePayload
Goal: When we call the SOAP API Endpoint, we need the payload as Document object. There are several ways one can execute the API call, but it is recommended to construct a Document Object, and further use this object as the SOAP Request body.
Since we design this as batch upload, this function takes currentrow, endrow, numofrows, fieldNames, and lastColumn as input, and return the Document Object of type DOMDocument60
You can use DOMDocument60 supported methods to create the root node of the SOAP request, and match your sample payload, subsequently build the payload using the document object as shown below.
Set root = doc.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
doc.appendChild root
As you see above screenshot, the uuid is retrieved from GenerateUUID function that we have seen in previous steps.
And you can also see how the document object is built, with several helper methods provided in Visual Basic, for eg: appendChild, createNode etc
The value for every node, for example, if you want to read every row, for the respective fields for your payload, will be using ActiveWorkbook.Sheets helper method, provided in visual basic.
Below screenshot you can find out how every row in “FixedAssets” worksheet, is being read, and further is used to construct the Document object.
In similar ways, the data read, can be written into the Document object using nodes and elements as shown below.
Sample code for the FixedAssetCreateMainBulkRequestMessage
Finally, return the Document object that you have built, to the caller as shown below
Function: PostFixedAssetBatchData
Goal: Call the SOAP based webservice, and get the response, log the response.
Posting a SOAP Request or any Webservice (including ODATA) is possible via XMLHTTP60 in Visual basic, and below you can see the SOAP request is triggered with the Document object that we built in previous steps.
Function: UploadDataToS4
Now we have all the necessary basic building blocks, now it is time for connecting the User Interface (the Upload button) to our macro.
This function helps to initiate the call, and continue the process flows as below:
CleanupData() ==> preparePayload() ==> PostFixedAssetBatchData() ==> update Results/logs.
On “Metadata” worksheet, please make sure you link this function to get called anytime anyone clicks on the “Upload” button.
You can also determine if there are enough records (Fixed Assets) available to send all data in one batch, or you may have to call the API via multiple batches.
Below screenshot self-explains the logic for the batch determination, as well as the process flow.
Troubleshooting
When you are running the Excel Upload program, if the system does not find any Self-signed certificate installed in the default path, it may throw connectivity errors with the API, or it may not upload any data successfully.
To overcome this issue, please install the Fiddler tool/software, an opensource request interceptor, which installs automatically the self-signed SSL certificates, and will serve your request to the API Server. Once the development completes, you can request your system administrator to install the Self-signed certificate or SSL Certificates available to execute the program from Excel workbook macros.
Conclusion
This is a proof of concept, built for making use of the Excel Sheet to mass upload the data to S/4HANA Cloud, with the help of the API, that’s supporting “SOAP” prototype as well. In addition to the “SOAP”, the same execution logic, will work also for “ODATA” based APIs as well.
This approach would help for any mass upload or create/change any data (as long as you have the API available), one can build such Excel based program, and can achieve what the business wants.
APPENDIX
Below you can find the reference help documents more on this topic.
3 Comments
