S/4HANA Cloud Integration – An instructive Example

Background

In the last months I was involved in a couple S/4HANA Cloud (S4HC from here-on) projects, where I supported integration topics. Recently I got a number of requests, to give an overview on S4HC Integration. After some theoretic talk I like show the following example, which illustrates nicely how quickly integrations with S4HC can be build.

The customer I worked with in this case had purchased a S4HC Version, which could create Purchase Requisitions (PRs) as well as Purchase Orders (POs), but did not had the more “advanced” capabilities like MRP. Neverthless they had plenty of materials, which needed frequent replenishment and were not very excited by the thought of having to create 50+ PRs manually each day. Changing the S4HC version was not an option, so the idea was, to build some simple tool outside the S4HC system and integrate it via APIs. To show them the possibilities I put together a PoC using Excel as a frontend.

I am sure most have seen the frequently presented Integration Overview Slide for S4HC – it shows the different types of integrations possible . The most flexible (=fun) ones are the integrations via APIs, which we used in the PoC:
.

The architecture of the PoC is the following:

The PoC basically does three steps

1. Read the current inventory for all Materials,
2. Determines in Excel, which Materials have an inventory below a certain threshold
3. Create an PR via API call into S4HC for each material identified in step 2

The PoC uses two types of APIs:

• To read the current inventory I am using an Customer CDS View which I “ODATA enabled”
• To create the PR I use the whitelisted API “Process Purchase Requisition” (aka as  SAP_COM_0102) – you can find the full catalog of whitelisted API at api.sap.com (one of the few URLs so intuitively named that I can remember without bookmark)

The more technical preparation Steps

To enable any kind of APIs we need to create a Communication User user and a Communication System. You might have already some on your system, if not they can be quickly created:

1. 1. To create the User, which will later be used to authenticate when performing the API call, I use the App “Maintain Communication Users¨¨. I just enter a User Name, Description and some very secretive password. We could also use a certificate for authentication, but we want to keep things simple for this example:
      .
      
      .
   2. To create a Communication System (which is more a formality for API call scenarios) I use App “Communication System” – I enter a creative name for “System ID” and “System Name” and put “localhost” for the “Host Name”. Finally I assign the Communication User I just created as Inbound User and save the Scenario.
      .
      
      ..

That’s it, all Prep Steps done – we are ready to start with the the fun part!

Setup Integration based on the Custom CDS View

With all preparations out of the way it is time to setup the first API

1. First I need to create the Custom CDS view, showing the material inventory. I open the App “Custom CDS Views“, which can be found in the group “Extensibility”. I have to find a useful standard View containing inventory information, which I can build my Custom CDS view on. Usually you will find some good candidates when using the Open Search in the top. First I tried “Inventory” – not very promising results. Next I search for “Stock” and it brings up a good candidate with the very promising name “I_MaterialStock”.
   ..
2. After selecting the View “I_MaterialStock” and clicking ¨Create¨ I give my newborn Custom CDS View a Name and Label and mark the ¨¨ODATA¨ checkbox. Marking this checkbox will allow me to publish the Custom CDS view outside the borders of the S4HC system and view it’s data from any web-browser in the world – exactly what we need.
   ..
3. The standard view has 100s of fields, but I only need some material info plus the inventory – so I only select 4 required fields and also include all the key fields – otherwise the CDS Views tend to misbehave / aggregation is not working correctly.
   ..
4. As I am interested in latest stock levels I use aggregation, which I can configure on the tab “Field Properties”. Afterwards we save and publish.
   ..
5. To expose the view outside the S4HC system I need to create a “Custom Communication Scenario”, which I can do in the app of the similar name.
   .
6. I click “New” in the App, enter a “Scenario ID” as well as creative description. This opens the new Custom Scenario in Edit Mode. All I got to do is to add my previously created Custom CDS View as Inbound Service and Click on Save and Publish. As the system will do some work like determining all the required authorizations for the customer CDS view, generate some elements, etc it usually takes a few minutes until the new Custom Communication Scenario will show as published. Time to get a coffee…
   ..
7. Welcome back…  The the next step, where everything comes together, is to create the “Communication Arrangement”, for which the wise SAP Developers have again created an own App with the same name. The Communication Arrangement combines the Communication Scenarios (Customer or Standard) with the Communication System (which has the Communication user in it’s belly) and finally exposes our API to the world. In this step I will create the Communication Arrangement based on our Custom Communication Scenario. First I click “New” in the app and then choose our Custom Communication Scenario and click Create:
   ..
8. Next I will add the Communication System I created earlier and very importantly take note of the Service URL, which I can use to access our newly created API. Finally I Save and release a sigh of joy as I have created our first API.
   ..
9. Let’s give it a test by trying to read the inventory. Later I will do this in Excel, but for now I will use a simple browser. It’s best to open a new Browser Window in incognito/private mode, so we don’t use any existing session – because I need to login via the Communication User I have created. I enter the URL from the last step and it’s typically helpful to add the “?saml2=disabled” parameter to the URL, to avoid any issues with SCI or other SAML authentication systems. It’s also important that I click Cancel in any type of Certification Selection Dialog. This should bring up an Authentication Dialog where I can enter the topsecret credentials from the Communication User I have created.
   .
   .
   If everything works I should get an overview about the Objects in our oDATA service.
   .
   .
   I just have one Object. I find the href parameter for that Object and add it to the URL. With the extended URL I trigger the browser again and might have to wait for a bit as the CDS view returns thousand of records.Voila, we see some inventory records. Usually the browser will show it nicely XML formatted  (or also in JSON if you add parameter $format=json) – but at least on my Laptop Chrome struggles, because there are sooo many records – I should definitely create my first purchase requisition for a new laptop when all is done.
   .
   .
   The main reason though, why I get so many records is, that we see the stock levels at each movement and need to aggregate further. Mid 2017 the aggregation was also not working correctly, but this seems to have been fixed in the latest releases. To get just one stock level per material I add a select statement to the URL. If we add parameter ‘?$select=Material,MatlWrhsStkQtyInMatlBaseUnit’ to the URL things look much more tidy
   .

This is a bit simplified, in case you have multiple plants, want to filter out certain stock types you would further add to the select.

Setup an Integration based on the Standard Communication Arrangement

After setting up the API to read inventory we also need to setup the API to create the PRs. This is much more straight forward, as we are using a Standard Scenario and don’t need to create all the Custom Objects.

1. Again I open App “Communication Arrangement” and click Create. This time I pick the standard template scenario “SAP_COM_0102”.
   .
   
   .
2. In the maintenance screen I just have to add the Communication System, which will automatically fill the Communication User Name. Finally we Save and learn the Service URL by heart for later use.
   .
   
   .
3. If I test that Service URL in a browser (again make sure to cancel Certificate Dialogs and use the credentials of the Communication User we created), I will see the different objects, the PR API provides.With a little research in the API catalog (you remember it’s available at api.sap.com) I found, that A_PurchaseRequisitionHeader is the object I need to create a PR and in the XML we see that we can use A_PurchaseRequisitionHeader/to_PurchaseReqnItem  for the item information.
   .
   
   .
   
   .
   
   .
   I also note down the returned XML fragments, which we will help later to create the XML body to pass the properties of the PR via the API call. To get some good example I can fetch the data for an existing PR (you might want to create a dummy one via the FIORI App in the system directly, which has the characteristics you want)

Setup Client to use perform API calls

Hooray! I am done with all steps in the S4HC System to setup the two APIs. Now I have to work on the client side. As mentioned before I will use Excel as Client. The recent versions of Excel actually have an inbuilt oDATA adapter (if your IT department hasn’t awarded you with a recent Excel release you have to install an Add-on called Power Query first – can be downloaded from the Microsoft site – just google it). Therefore connecting to our oDATA API is quite straight forward:

1. I choose the Data Ribbon -> New Query -> From Other Sources -> From oDATA feed
   .
2. In the dialog I enter the URL I recorded when testing the Inventory oDATA service ending in sap/opu/odata/sap/YY1_MATERIAL_STOCK_CDS/YY1_MATERIAL_STOCK/?$select=Material,MatlWrhsStkQtyInMatlBaseUnit&saml2=disabled and click Ok
   .
   .
3. Make sure to cancel any Certificate Popups you might see – finally Excel complains, that it can not authenticate. I switch to the Basic Tab and enter the credentials of the Communication User (after entering them for so many times I know the 64 digit random password now by heart!). Again I will need a bit of patience, because Excel will reach out to S4HC and fetch a sample the Inventory information.
   .
   .
4. The Sample looks spectacular, so I click Load and Excel will load a table with the complete inventory records.
   ..
   .
   Tip: You can save this Excel locally and reopen and refresh it at a later time to get the latest data. S4HC customers don’t have the possibility to quickly dump the data of a table via transaction SE16 into a file. Using this Excel oDATA Adapter and the Communication Scenarios is actually an alternative to quickly extract Data from a S4HC systems – I have used that multiple times for example to allow customers to get access to all the BP data via Communication Scenario SAP_COM_0008.
   .
5. Next I created a simple Excel table, which list the material of interest with re-order point and re-order quantity. If the inventory falls below the re-order point a PR should be created with the re-order quantity. The table also does a VLOOKUP for the inventory of the materials of interest  from the oDATA download sheet and has a simple formula to determine if re-ordering is necessary
   .
   
   .
6. Setting the logic to trigger the PR creation API is actually a bit more tricky. For this we need to create some simple Excel Macro, which triggers the oDATA call. I threw together the following VBA code, which basically loops through the table of the re-order materials candidates and if it finds a material, where a PR is required (Column E >0) it triggers an API call into the S4HC system. For these calls it is using the PR API we have created previously. Because S4HC uses a Cross-Site Request Forgery Protection as described in the Online Help , we have to do a GET request to receive security token before doing the actual POST request creating the PR. Here is the VBA coding:
   .

   Const START_ROW_OUT = 2
   Const START_ROW_MATERIAL = 2
   Const SHEET_TITLE_MATERIAL = "2. Comp Quant<->Reorder point"
   Const SHEET_TITLE_PR = "3. Trigger Re-Ordering"
   Const SHEET_TITLE_META = "METADATA"
   Const TAG_MAT = "{MATERIAL}"
   Const TAG_QUANT = "{QUANTITY}"
   
   Public Sub trigger_re_order()
   Set pr_call_return = CreateObject("Scripting.Dictionary")
   Dim n As Integer
   Dim token As String
   Dim out_row As Integer
    Sheets(SHEET_TITLE_PR).Range("A2:D100").Select
       Selection.ClearContents
   Sheets(SHEET_TITLE_PR).Cells(1, 1).Select
   out_row = START_ROW_OUT
   n = START_ROW_MATERIAL
   token = get_token(Sheets(SHEET_TITLE_META).Cells(3, 2).Value, Sheets(SHEET_TITLE_META).Cells(4, 2).Value)
   While Sheets(SHEET_TITLE_MATERIAL).Cells(n, 1) <> ""
   If Sheets(SHEET_TITLE_MATERIAL).Cells(n, 5) > 0 Then
   Sheets(SHEET_TITLE_PR).Cells(out_row, 1) = Sheets(SHEET_TITLE_MATERIAL).Cells(n, 1)
   Sheets(SHEET_TITLE_PR).Cells(out_row, 2) = Sheets(SHEET_TITLE_MATERIAL).Cells(n, 5)
   Sheets(SHEET_TITLE_PR).Cells(out_row, 4) = "Creating PR..."
   Set pr_call_return = trigger_odata_call(Sheets(SHEET_TITLE_MATERIAL).Cells(n, 1), Sheets(SHEET_TITLE_MATERIAL).Cells(n, 5), Sheets(SHEET_TITLE_META).Cells(3, 2).Value, Sheets(SHEET_TITLE_META).Cells(4, 2).Value, token)
   Sheets(SHEET_TITLE_PR).Cells(out_row, 3) = pr_call_return("pr_num")
   Sheets(SHEET_TITLE_PR).Cells(out_row, 4) = pr_call_return("Code")
   out_row = out_row + 1
   End If
   n = n + 1
   Wend
   
   End Sub
   
   Public Function trigger_odata_call(mat As String, quant As Integer, usr As String, pw As String, token As String) As Object
   Dim request As New SyncWebRequest
   Dim resp As String
   Dim stat As String
   Dim pr_num As String
   Set pr_call_return = CreateObject("Scripting.Dictionary")
   request.AjaxPost Sheets(SHEET_TITLE_META).Cells(2, 2).Value, gen_xml_body(mat, quant), usr, pw, token
   resp = request.Response
   stat = request.Status
   If (stat = "201") Then
   pr_call_return.Add "Code", "Creation Successful!"
   Else
   pr_call_return.Add "Code", "Creation failed! Return Code: " + stat
   End If
   If (InStr(1, resp, "<d:PurchaseRequisition>") > 0 And (InStr(1, resp, "</d:PurchaseRequisition>") > InStr(1, resp, "<d:PurchaseRequisition>"))) Then
   pr_num = Mid(resp, InStr(1, resp, "<d:PurchaseRequisition>") + Len("<d:PurchaseRequisition>"), InStr(1, resp, "</d:PurchaseRequisition>") - InStr(1, resp, "<d:PurchaseRequisition>") - Len("<d:PurchaseRequisition>"))
   pr_call_return.Add "pr_num", pr_num
   Else
   pr_call_return.Add "pr_num", "NA"
   End If
   Set trigger_odata_call = pr_call_return
   End Function
   
   Public Function get_token(usr As String, pw As String)
   Dim request As New SyncWebRequest
   Dim res As String
   request.AjaxGet Sheets(SHEET_TITLE_META).Cells(2, 2).Value, usr, pw
   get_token = request.ResponseHeader
   End Function
   
   Public Function gen_xml_body(mat As String, quant As Integer)
   Dim xml_in As String
   xml_in = Sheets(SHEET_TITLE_META).Cells(1, 2)
   xml_out = Replace(xml_in, TAG_MAT, mat)
   xml_out = Replace(xml_out, TAG_QUANT, quant)
   gen_xml_body = xml_out
   End Function
   
   Public Sub refresh_table()
   ActiveWorkbook.RefreshAll
   End Sub
   

   .

7. To get the XML for the body of the PR API call needs a bit trail and error work, but can be done using the XML fragments we collected when doing our test calls earlier and the documentation from api.sap.com. I ended up with the following XML (with Placeholders for Material and Quantity). I good tool to play around to find the right XML format for the body is Postman.

<entry xml:base="/sap/opu/odata/sap/API_PURCHASEREQ_PROCESS_SRV/" xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/to_PurchaseReqnItem" type="application/atom+xml;type=feed" title="to_PurchaseReqnItem">
        <m:inline>
            <feed xml:base="/sap/opu/odata/sap/API_PURCHASEREQ_PROCESS_SRV/">
                <entry>
                   
                    <content type="application/xml">
                        <m:properties>
                            <!--<d:PurchaseRequisition></d:PurchaseRequisition>-->
                            <d:AccountAssignmentCategory />
                            <d:Material>{MATERIAL}</d:Material>
                            <d:MaterialGroup>L001</d:MaterialGroup>
                            <d:PurchasingDocumentCategory>B</d:PurchasingDocumentCategory>
                            <d:RequestedQuantity>{QUANTITY}</d:RequestedQuantity>
                            <d:BaseUnit>PC</d:BaseUnit>
                            <d:PurchaseRequisitionPrice>13.00</d:PurchaseRequisitionPrice>
                            <d:PurReqnPriceQuantity>1</d:PurReqnPriceQuantity>
                            <d:MaterialGoodsReceiptDuration>0</d:MaterialGoodsReceiptDuration>
                            <d:ReleaseCode />
                            <d:PurchaseRequisitionItem>10</d:PurchaseRequisitionItem>
                            <d:PurchasingOrganization />
                            <d:PurchasingGroup>001</d:PurchasingGroup>
                            <d:Plant>1710</d:Plant>
                            <d:SourceOfSupplyIsAssigned>false</d:SourceOfSupplyIsAssigned>
                            <d:SupplyingPlant />
                            <d:OrderedQuantity>0</d:OrderedQuantity>
                            <d:DeliveryDate>2017-04-03T00:00:00</d:DeliveryDate>
                            <d:CreationDate>2017-03-06T00:00:00</d:CreationDate>
                            <d:ProcessingStatus>N</d:ProcessingStatus>
                            <d:PurchasingInfoRecord />
                            <d:PurchasingDocument />
                            <d:Supplier />
                            <d:IsDeleted />
                            <d:FixedSupplier />
                            <d:RequisitionerName />
                            <d:CreatedByUser>D052213</d:CreatedByUser>
                            <d:PurReqCreationDate>2017-03-06T00:00:00</d:PurReqCreationDate>
                            <d:DeliveryAddressID>22889</d:DeliveryAddressID>
                            <d:ManualDeliveryAddressID />
                            <d:PurReqnItemCurrency>USD</d:PurReqnItemCurrency>
                            <d:MaterialPlannedDeliveryDurn>8</d:MaterialPlannedDeliveryDurn>
                            <d:PurchasingDocumentItem>0</d:PurchasingDocumentItem>
                            <d:DelivDateCategory>1</d:DelivDateCategory>
                            <d:MultipleAcctAssgmtDistribution />
                            <d:StorageLocation />
                            <d:PurReqnSSPRequestor />
                            <d:PurReqnSSPAuthor />
                            <d:PurchaseContract />
                            <d:PurReqnSourceOfSupplyType />
                            <d:PurchaseContractItem>0</d:PurchaseContractItem>
                            <d:ConsumptionPosting />
                            <d:PurReqnOrigin>R</d:PurReqnOrigin>
                            <d:PurReqnReleaseStatus>02</d:PurReqnReleaseStatus>
                            <d:PurReqnSSPCatalog />
                            <d:PurReqnSSPCatalogItem />
                            <d:PurReqnSSPCrossCatalogItem>0</d:PurReqnSSPCrossCatalogItem>
                            <d:IsPurReqnBlocked />
                            <d:ItemDeliveryAddressID />
                            <d:Language>EN</d:Language>
                            <d:IsClosed>false</d:IsClosed>
                            <d:ReleaseIsNotCompleted>false</d:ReleaseIsNotCompleted>
                            <d:ServicePerformer />
                            <d:ProductType />
                            <d:PurchaseRequisitionType>NB</d:PurchaseRequisitionType>
                            <d:PurchaseRequisitionStatus />
                            <d:ReleaseStrategy />
                            <d:PerformancePeriodStartDate m:null="true" />
                            <d:PerformancePeriodEndDate m:null="true" />
                            <d:CompanyCode>1710</d:CompanyCode>
                            <d:SupplierMaterialNumber />
                            <d:Batch />
                            <d:MaterialRevisionLevel />
                            <d:MinRemainingShelfLife>0</d:MinRemainingShelfLife>
                            <d:ItemNetAmount>0.00</d:ItemNetAmount>
                            <d:PurchasingDocumentSubtype />
                            <d:GoodsReceiptIsExpected>true</d:GoodsReceiptIsExpected>
                            <d:InvoiceIsExpected>true</d:InvoiceIsExpected>
                            <d:GoodsReceiptIsNonValuated>false</d:GoodsReceiptIsNonValuated>
                            <d:RequirementTracking />
                            <d:MRPController>001</d:MRPController>
                            <d:PurchaseRequisitionIsFixed>false</d:PurchaseRequisitionIsFixed>
                            <d:AddressID />
                            <d:PurchasingDocumentItemCategory>0</d:PurchasingDocumentItemCategory>
                            <d:PurchaseRequisitionItemText>Test Item Text</d:PurchaseRequisitionItemText>
                        </m:properties>
                    </content>
                </entry>
            </feed>
        </m:inline>
    </link>
    <content type="application/xml">
        <m:properties>
            <!--<d:PurchaseRequisition></d:PurchaseRequisition>-->
            <d:PurchaseRequisitionType>NB</d:PurchaseRequisitionType>
            <d:PurReqnDescription>My New Purchase Req</d:PurReqnDescription>
            <d:SourceDetermination>false</d:SourceDetermination>
        </m:properties>
    </content>
</entry>

Conclusion

That’s it, all parts of the PoC are done. I created a bit of Eye Candy in the Excel (Buttons, Tabs,..) and the PoC was ready for showtime.

Here is a Video showing the PoC in action:

You can download the Excel from from here to play around with it yourself. To keep my system admin happy I have changed the password of the communication user, so the Excel will not be functioning out of the box. Once you have created the APIs on your own S4HC system you can nevertheless update the PW and URL on the forth tab of the Excel Workbook and should be able to create some crazy PRs on your system.

An Excel based frontend is not very stable and it would be better to implement the logic in a tool like SAP SCP. Neverthless the PoC shows integrating with S4HC is quite easy and thanks to the Customer CDS and Standard Whitelisted APIs there are some nice opportunities to build extensions to S4HC – hopefully this tutorial has given you some inspirations.