Ever since the release of the Flexible Data Model in IBP Order-Based Planning, It was only possible to load Transactional Data into the OBP Datastore from an SAP ERP system( ECC or S/4 HANA ) via Real-time Integration. And, thus, there has been a continuous demand from customers to provide them with mechanisms to load transactional data into OBP from non-SAP sources as well.
With IBP 2402, SAP has allowed the integration of Stock and Purchasing Data from alternate sources using ODATA API. In this blog, I will explain the technical steps to build a utility in Python that can read purchase order details from an Excel sheet and load it into OBP.
Important Note: The Purchasing (/IBP/API_PURCHASING) OData API service is currently only available by individual customer request. To use it, create an incident on component SCM-IBP-INT-ODT-PUR, requesting SAP to enable the communication scenario SAP_COM_0955.
Coming now to the blog, For easy understanding, I have segregated the blog into 3 different parts
Establishing a Communication Arrangement in the SAP IBP System
- We need to create a communication system(Inbound Only) and a communication user that will authenticate that system.
Communication System
- The Communication Username and Password created in the below step will have to be used in the Python Code while making the GET and POST Requests.
Communication User
- Then we should create a communication arrangement based on the communication scenario SAP_COM_0955 and give the previously created communication system and user.
Communication Arrangement
- We need to create a communication system(Inbound Only) and a communication user that will authenticate that system.
Using Python language, reading data from MS Excel and massaging it to create a JSON Payload
- For the complete development, we make use of these Python libraries
- Pandas: To Read from Excel into Dataframes, massage the Data, and convert it into JSON Payload
- JSON: To Convert Strings to JSON payload
- Requests: To access ODATA API via GET and POST methods of the library
- Time: To introduce a Wait time after we COMMIT Results so that we can get the final processing status of the request
- For the complete development, we make use of these Python libraries
import requests
import pandas as pd
import json
import time
- We need to define the structure of our Excel sheet based on the mandatory field requirements specified for each document type ( PO/PR/STO/STR ) at this link. For a simplified user experience, I have created 2 worksheets in Excel. But you can choose to define it differently.
- Root: This worksheet contains Planning Area ID, Version ID, SourceLogical System, and Integration mode. This sheet will always contain one row.
PlanningAreaID VersionID SourceLogicalSystem IBPPurgDocIntegrationMode - Item/Schedule Line: This worksheet contains mandatory/optional fields required for creating a purchase order in OBP(per details provided in the link above)
IBPPurgDocType IBPPurgDocExt IBPPurgDocItem IBPPurgSOSAdditionalLaneID IBPPurgSOSModeOfTransport IBPGRProcgTmeInDays IBPPurgDocScheduleLine ProductID ShipToLocationID ShipFromLocationID IBPPurgDeliveryDateTime IBPPurgOrderedQuantity IBPPurgReceiptQuantity IBPPurgDocQuantityUnit
- Root: This worksheet contains Planning Area ID, Version ID, SourceLogical System, and Integration mode. This sheet will always contain one row.
- We Will have to insert a column for 'TransactionID' at the start of our JSON Payload. This 'TransactionID' will be fetched from the Response Object received from the GET request. We will see later, how that works out.
- Below Python code reads the 'Root' Excel worksheet into a Pandas DataFrame and inserts Transaction ID as a first column in that DataFrame
df1 = pd.read_excel(r"C:\Users\I573991\OneDrive - SAP SE\Python\ReadExternalFile\IBPPurgDocRootAsyncWrite.xlsx",sheet_name= 'Root')
df1.insert(0,"TransactionID",TransactionID)
- Next, we read the 'ScheduleLine' worksheet into a different DataFrame; and convert the Date into the timestamp format desired by the API. Then we Nest the Schedule Line details into each item.
df3['IBPPurgDeliveryDateTime'] = df3['IBPPurgDeliveryDateTime'].dt.strftime('%Y-%m-%dT%H:%M:%SZ')
df4 = (df3.groupby(["IBPPurgDocType","IBPPurgDocExt","IBPPurgDocItem","IBPPurgSOSAdditionalLaneID","IBPPurgSOSModeOfTransport","IBPGRProcgTmeInDays"],group_keys=True).apply(lambda x: x[["IBPPurgDocScheduleLine","ProductID","ShipToLocationID","ShipFromLocationID","IBPPurgDeliveryDateTime","IBPPurgOrderedQuantity","IBPPurgReceiptQuantity","IBPPurgDocQuantityUnit"]].to_dict('records'),include_groups = False).reset_index().rename(columns= {0:'_IBPPurgDocSchdLnAsyncWrite'}))
- We then merge the newly created DF4 with DF1 with a 'Cross' Join, nest the Item details under the Header/Root, and convert it into JSON Payload.
dfmerge = df1.merge(df4,'cross',None)
df5 = (dfmerge.groupby(['TransactionID','PlanningAreaID','VersionID','SourceLogicalSystem','IBPPurgDocIntegrationMode'],group_keys=True).apply(lambda x: x[["IBPPurgDocType","IBPPurgDocExt","IBPPurgDocItem","IBPPurgSOSAdditionalLaneID","IBPPurgSOSModeOfTransport","IBPGRProcgTmeInDays","_IBPPurgDocSchdLnAsyncWrite"]].to_dict('records'),include_groups = False).reset_index().rename(columns= {0:'_IBPPurgDocItemAsyncWrite'}))
j1 = (df5.to_json(orient='records',date_format = 'iso',indent =2,index=False))[1:-1]
- The JSON Payload J1, once generated, should look like this.
{
"TransactionID":"33a06853-4c49-1eee-b2df-351a7e3b070f",
"PlanningAreaID":"SAP7FAC",
"VersionID":"__BASELINE",
"SourceLogicalSystem":"OBPSRC",
"IBPPurgDocIntegrationMode":"UPSERT",
"_IBPPurgDocItemAsyncWrite":[
{
"IBPPurgDocType":"PO_ITM",
"IBPPurgDocExt":"20001001",
"IBPPurgDocItem":"000010",
"IBPPurgSOSAdditionalLaneID":"0_0001_3_5300011243_000000",
"IBPPurgSOSModeOfTransport":"DEF",
"IBPGRProcgTmeInDays":2,
"_IBPPurgDocSchdLnAsyncWrite":[
{
"IBPPurgDocScheduleLine":"0010",
"ProductID":"GG03_BOARD_A@QKV002",
"ShipToLocationID":"PLFA71@QKV002",
"ShipFromLocationID":"SUSUPPLIER71@QKV002",
"IBPPurgDeliveryDateTime":"2024-03-26T00:00:00Z",
"IBPPurgOrderedQuantity":100,
"IBPPurgReceiptQuantity":100,
"IBPPurgDocQuantityUnit":"EA"
},
{
"IBPPurgDocScheduleLine":"0020",
"ProductID":"GG03_BOARD_A@QKV002",
"ShipToLocationID":"PLFA71@QKV002",
"ShipFromLocationID":"SUSUPPLIER71@QKV002",
"IBPPurgDeliveryDateTime":"2024-03-27T00:00:00Z",
"IBPPurgOrderedQuantity":200,
"IBPPurgReceiptQuantity":200,
"IBPPurgDocQuantityUnit":"EA"
}
]
},
{
"IBPPurgDocType":"PO_ITM",
"IBPPurgDocExt":"20001001",
"IBPPurgDocItem":"000020",
"IBPPurgSOSAdditionalLaneID":"0_0001_3_5300011243_000000",
"IBPPurgSOSModeOfTransport":"DEF",
"IBPGRProcgTmeInDays":2,
"_IBPPurgDocSchdLnAsyncWrite":[
{
"IBPPurgDocScheduleLine":"0010",
"ProductID":"GG03_BOARD_A@QKV002",
"ShipToLocationID":"PLFA71@QKV002",
"ShipFromLocationID":"SUSUPPLIER71@QKV002",
"IBPPurgDeliveryDateTime":"2024-02-28T00:00:00Z",
"IBPPurgOrderedQuantity":150,
"IBPPurgReceiptQuantity":150,
"IBPPurgDocQuantityUnit":"EA"
},
{
"IBPPurgDocScheduleLine":"0020",
"ProductID":"GG03_BOARD_A@QKV002",
"ShipToLocationID":"PLFA71@QKV002",
"ShipFromLocationID":"SUSUPPLIER71@QKV002",
"IBPPurgDeliveryDateTime":"2024-03-29T00:00:00Z",
"IBPPurgOrderedQuantity":250,
"IBPPurgReceiptQuantity":250,
"IBPPurgDocQuantityUnit":"EA"
}
]
},
{
"IBPPurgDocType":"PO_ITM",
"IBPPurgDocExt":"20002002",
"IBPPurgDocItem":"000010",
"IBPPurgSOSAdditionalLaneID":"0_0001_3_5300011243_000000",
"IBPPurgSOSModeOfTransport":"DEF",
"IBPGRProcgTmeInDays":2,
"_IBPPurgDocSchdLnAsyncWrite":[
{
"IBPPurgDocScheduleLine":"0010",
"ProductID":"GG03_BOARD_A@QKV002",
"ShipToLocationID":"PLFA71@QKV002",
"ShipFromLocationID":"SUSUPPLIER71@QKV002",
"IBPPurgDeliveryDateTime":"2024-03-30T00:00:00Z",
"IBPPurgOrderedQuantity":300,
"IBPPurgReceiptQuantity":300,
"IBPPurgDocQuantityUnit":"EA"
},
{
"IBPPurgDocScheduleLine":"0020",
"ProductID":"GG03_BOARD_A@QKV002",
"ShipToLocationID":"PLFA71@QKV002",
"ShipFromLocationID":"SUSUPPLIER71@QKV002",
"IBPPurgDeliveryDateTime":"2024-04-02T00:00:00Z",
"IBPPurgOrderedQuantity":350,
"IBPPurgReceiptQuantity":350,
"IBPPurgDocQuantityUnit":"EA"
}
]
}
]
}
3. Using Python Again, Making GET and POST requests to establish a connection with SAP IBP, get Transaction ID, and Post that JSON Payload to IBP calling the ODATA API service
- The first step is to define the URL for your IBP System, URL for API Service, Username, and Password created earlier.
#Define Your URL Here
SERVER_URL = ''
DATA_URL = f"https://{SERVER_URL}/sap/opu/odata4/ibp/api_purchasing/srvd_a2x/ibp/api_purchasing/0001/IBPPurgDocRootAsyncWrite"
#Provide UserName Password here
USERNAME = ''
PASSWORD = ''
- Make a GET request to the API to fetch the Transaction ID and the CSRF Token.
data_get = requests.get(f"{DATA_URL}/SAP__self.GetTransactionID()",auth=(USERNAME, PASSWORD), verify=False,headers = {'x-csrf-token': 'Fetch'} )
if data_get.status_code == 200:
token = data_get.headers['x-csrf-token']
c = requests.utils.dict_from_cookiejar(data_get.cookies)
headers = {'x-csrf-token': token, 'Content-type': 'application/json'}
json1 = data_get.json()
df = pd.DataFrame.from_dict(pd.json_normalize(json1), orient='columns')
TransactionID = df['TransactionID'][0]
- Having inserted the Transaction ID to the JSON payload, we now POST it to IBP with the Username and password and the CSRF Token generated earlier in the GET call.
#POST JSON Payload
x = requests.post(DATA_URL,data=j4,auth=(USERNAME, PASSWORD), verify=False, headers = headers, cookies = c)
- Then we check the Status of the POST Request. It is returned in attribute STATUS_CODE and a value 201 in it implies that there is no issue with syntax for any field on the JSON and it is okay for processing to the Database.
- Only when we get a 201 response, we proceed with a COMMIT call on the same URL
if x.status_code == 201:
#Commit the POST Request
y = requests.post(f"{DATA_URL}/SAP__self.Commit",data=j5,auth=(USERNAME, PASSWORD), verify=False, headers = headers, cookies = c )
- In the last step, we have to check for the final status of Database processing as there can be issues with data being processed (e.g. missing master data). The below code helps us identify the same. We also introduced some wait time to let the transaction get processed completely before we check on the final status of it.
if y.status_code == 200:
time.sleep(20)
status_get = requests.get(f"https://pt6-001-api.wdf.sap.corp/sap/opu/odata4/ibp/api_purchasing/srvd_a2x/ibp/api_purchasing/0001/IBPPurgDocRootAsyncWrite/SAP__self.GetStatus(TransactionID={TransactionID})",auth=(USERNAME, PASSWORD), verify=False, headers = headers, cookies = c)
print(status_get.text)
print(status_get.status_code)
- A Status Code 200 here will confirm the successful posting of the transaction and the Transaction status will show as Processed.
{"@odata.context":"../$metadata#com.sap.gateway.srvd_a2x.ibp.api_purchasing.v0001.xIBPxD_PurgTransactionStatusR","@odata.metadataEtag":"W/\"20240213123138\"","TransactionStatus":"PROCESSED"}
200
- In case of a Transaction Status 'Processed with Errors', you can fetch the error details by making a Get call on Method 'IBPPurgDocMessageAsyncWrite' and process further the output by transforming it into JSON objects for better understanding and Display Purposes.
status = requests.get("https://pt6-001-api.wdf.sap.corp/sap/opu/odata4/ibp/api_purchasing/srvd_a2x/ibp/api_purchasing/0001/IBPPurgDocMessageAsyncWrite",data=j5,auth=(USERNAME, PASSWORD), verify=False, headers = headers, cookies = c)
print(status.text)
Purchase Orders posted in IBP can be viewed in the 'Projected Stock' Fiori Application
Projected Stock Fiori Application
More Details and technical aspects of API can be found at the link
A key point to note is that a maximum of 10000 records can be written for each request.
You can use the same utility to load stock data into IBP just by changing the API Details and providing mandatory fields for Stock in your Excel files. Details of the same can be found here.
Wrapping up, I would like to mention that I am not an expert in Python but I have tried to make use of the language to build a user-friendly utility here. There could be better ways of accomplishing the same task with better overall performance and one can do their code design.
I would appreciate it if you could leave your valuable thoughts and feedback for me to improve upon things here.
Regards
Gaurav Guglani
- SAP Managed Tags:
3 Comments
| User | Count |
|---|---|
| 8 | |
| 7 | |
| 6 | |
| 4 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 3 | |
| 3 |
