SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

S/4HANA Cloud Integration – An instructive Example

Background

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:

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

The architecture of the PoC is the following:

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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)

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. 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:

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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”.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

4. The Standard CDS view creates multiple records for each material (one for each addition / subtraction). Therefore I need perform some aggregation, which I can configure on the tab “Field Properties”. Afterwards we save and publish.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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…

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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:

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

If everything works I should get an overview about the Objects in our oDATA service.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

The main reason though, why I get so many records is, that the aggregation is not working as I see the same material again multiple times. This sees like kind of a bug, at least no one could provide me yet a good explanation, why it’s happening. To get the aggregation working we need to add a select statement to the URL (don’t ask how long it took to figure that out!). If we add parameter ‘?$select=Material,MatlCnsmpnQtyInMatlBaseUnit’ to the URL things look much better

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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”.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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,MatlCnsmpnQtyInMatlBaseUnit&saml2=disabled and click Ok

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

4. The Sample looks spectacular, so I cick Load and Excel will load a table with the complete inventory records.

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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

SAP S/4HANA Cloud, SAP HANA, S/4HANA Cloud Integration

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>

Leave a Reply

Your email address will not be published. Required fields are marked *