Automation & Reporting

SAP Automation of Orders Creation for Retail

Automate Purchase Order Creation in SAP with SAP GUI Scripting Tool using Visual Basic

Article originally published on Medium.

I. Purchase Order Creation in SAP

1. What is a Purchase Order (PO)?

In SAP Retail, a purchase order is a commercial document indicating types, quantities, agreed prices and delivery information (locations, time) for products or services.

Scenario

As a Store Manager, you want to order 10 Pcs of an article (SAP Code: 145654789 ) at the agreed price of 200$ to be delivered on 20.09.2020 in WH01 by Supplier XXX-XXX (Vendor Code: 15487).

Your store belongs to the purchase organization (Code: PORG ) of the Retail Company (Code: RTCP) with a dedicated purchasing manager (Code: PRGP).

Edit: You can find a Youtube version of this article with animations in the link below.

2. How to do PO Creation in SAP?

ME21N transaction can be used to operate PO Creation

Launch Transaction ME21N

SAP Transaction ME21N for Purchase Order Creation — (Image by Author)

To create our Purchase Order we need to fill

Vendor Code: 15487
Purchase Organization: PORG
Purchase Group: PRGP
Company Code: RTCP
Material: 145654789
PO Qty: 10
Plant: WH01
Delivery Date: 20.09.2020

2. Validation and Save

SAP Transaction ME21N for Purchase Order Creation (Step 2) — (Image by Author)

After Enter key is pressed, SAP loads missing PO information from Purchase Info Records, Master Data linked to SAP Code and Purchase Organization. We can then save the PO in the system with a PO Number.

3. Checking in ME23N

After PO creation, we can check in ME23N PO is created and extract the PO Number for records.

SAP Transaction ME21N for Purchase Order Creation (Step 3) — (Image by Author)

4. Manual Sub-Tasks

To perform this PO creation, you have a set of manual sub-tasks to be operated. I’ll separate these tasks into two categories:

1. Manual Data Inputs: Filling Forms, Clicking on buttons, Uploading Data and any other action of information transfer from User to SAP GUI
2. Manual Data Extraction: Checking Results, Downloading Report, Exporting Tables or any other action of information transfer from SAP GUI to User

For the current example, the transaction form filling task can be placed in category 1 and PO Number Extraction in category 2.

3. How to Automate PO Creation using SAP GUI Scripting?

The target is to fully Automate the process with Visual Basic script in an Excel File where Inputs Data will be extracted, and Output Data will be recorded.

Target = Fully Automated Bot

A list of articles with respective plants in an Excel Spreadsheet:

Excel Input File with PO Creation Parameters — (Image by Author)

Column A/../H: Plant, Purchasing Group, Vendor Code, Material Code, Barcode, Quantity, Purchasing Organization to be typed in PO Creation Form [1]
Column E: PO Number to be extracted from Creation Report ME23N [2]

Automation Bot performing Purchase Order Creation — (Image by Author)

🔗

You can find the full code in this Github repository: Link

2. Excel VBA Script

Looking at the two simple examples presented in Part 1, we understand that our VBA Code needs to include three parts:

Setting Up Connection with SAP GUI: Procedure Create_SAP_Session
Scripting to Perform Action: Function named PO_Function to perform a PO Creation
Looping in Excel File: Procedure named PO_Process to loop through all lines to create all listed PO

Declare Public Variables

Option Explicit
'Variables for SAP GUI Tool
Public SapGuiAuto, WScript, msgcol
Public objGui As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession
Public objSBar As GuiStatusbar
Public objSheet As Worksheet
'Variables for Functions
Public Plant, SAP_CODE, Listing_Procedure As String
Dim W_System
Dim iCtr As Integer

Setting Up Connection with SAP GUI

Function Create_SAP_Session() As Boolean
'Function to Connect with SAP GUI Sessions

'(1) Variables for Session Creation
Dim il, it
Dim W_conn, W_Sess, tcode, Transac, Info_System
Dim N_Gui As Integer
Dim A1, A2 As String
tcode = Sheets(1).Range("B3") 'Get Transaction Code

'(2) Get System Name in Cell(2,1) of Sheet1
If mysystem = "" Then
W_System = Sheets(1).Cells(2, 2)
Else
W_System = mysystem
End If

'(3) If we are already connected to a Session we exit this function
If W_System = "" Then
Create_SAP_Session = False
Exit Function
End If

'(4) If Object Session is not null and the system is matching with the one we target: we use this object
If Not session Is Nothing Then
If session.Info.SystemName & session.Info.Client = W_System Then
Create_SAP_Session = True
Exit Function
End If
End If

'(5) If we are not connected to anything and GUI Object is Nothing we create one
If objGui Is Nothing Then
Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
End If'(6) Loop through all SAP GUI Sessions to find the one with the right transaction
For il = 0 To objGui.Children.Count - 1
Set W_conn = objGui.Children(il + 0)

For it = 0 To W_conn.Children.Count - 1
Set W_Sess = W_conn.Children(it + 0)
Transac = W_Sess.Info.Transaction
Info_System = W_Sess.Info.SystemName & W_Sess.Info.Client

'Check if Session Name and Transaction Code are matching then connect to it
If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System Then
'If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System And W_Sess.Info.Transaction = tcode Then
Set objConn = objGui.Children(il + 0)
Set session = objConn.Children(it + 0)
Exit For
End If
Next
Next

'(7) If we can't find Session with the right System Name and Transaction Code: display error message
If session Is Nothing Then
MsgBox "There is no active session found for " + W_System + " with transaction " + tcode + ".", vbCritical + vbOKOnly
Create_SAP_Session = False
Exit Function
End If

'(8) Turn on scripting mode
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject objGui, "on"
End If

'(9) Confirm connection to a session
Create_SAP_Session = TrueEnd Function

Create SAP GUI Object linked to a system and transaction code

Step 1 to 4: Connect to SAP following System Name that can be found in GUI Window (picture above)
Step 5: Create the SAP GUI Object
Step 6: Loop through all SAP Windows and link GUI Object to the one connected to transaction tcode
Step 7: Scripting Mode “on” like in the SAP GUI Recording Tool Output

Scripting to Perform Action

By asking Plant Code, SAP_Code and Listing Procedure to fill the form:

Step 1 to 2: Connect to ME21N Transaction
Steps 3 to 8: Filling the form and validate
Step 9: Save the PO
Steps 10 to 12: Go to ME23N to check the PO, get the PO# and paste it in the Excel File

Function PO_Function()

'(1) Declare Variables
Dim W_BPNumber, W_SearchTerm, PON
Dim lineitems As Long
Dim Sht_Name As String
Dim N_Lines As Integer
Dim t As Integer
Sht_Name = "PO"

'(2) Launch_Transaction
session.findById("wnd[0]").Maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "me21n"
session.findById("wnd[0]").sendVKey 0
Application.Wait (Now + TimeValue("0:00:1"))

'(3) Fill Vendor Codesession.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/ctxtMEPO_TOPLINE-SUPERFIELD").Text = Sheets(Sht_Name).Cells(2, 3)

'(4) PurchOrg Code
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT9/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1221/ctxtMEPO1222-EKORG").Text = Sheets(Sht_Name).Cells(2, 7)

'(5) Purch Group
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT9/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1221/ctxtMEPO1222-EKGRP").Text = Sheets(Sht_Name).Cells(2, 8)session.findById("wnd[0]").sendVKey 0

'(6) Loop for SAP Code
For t = 0 To N_Lines - 3

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EMATN[4," & t & "]").Text = Sheets(Sht_Name).Cells(t + 2, 4)
Next t

'(7) Loop for Quantities
For t = 0 To N_Lines - 3

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[" & "6," & t & "]").Text = Sheets(Sht_Name).Cells(t + 2, 6)
Next t

'(8) Loop for Plants
For t = 0 To N_Lines - 3

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-NAME1[15," & t & "]").Text = Sheets(Sht_Name).Cells(t + 2, 1)
Next t

'(9) Click Save Button
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[1]/usr/btnSPOP-VAROPTION1").press
session.findById("wnd[0]/sbar").DoubleClick

'(10) Leave and go to Me23n
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/okcd").Text = "me23n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0020/subSUB3:SAPLMEVIEWS:1100/subSUB1:SAPLMEVIEWS:4002/btnDYN_4000-BUTTON").press
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/txtMEPO_TOPLINE-EBELN").SetFocus
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/txtMEPO_TOPLINE-EBELN").caretPosition = 1

'(11) Copy PO# and paste it in Excel Filesession.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/txtMEPO_TOPLINE-EBELN").SetFocusPON = session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/txtMEPO_TOPLINE-EBELN").Text
N_Lines = 1
While Not (Sheets(Sht_Name).Cells(N_Lines, 1) = "")
N_Lines = N_Lines + 1
Sheets(Sht_Name).Cells(N_Lines, 9) = PON
Wend

'(12) Leave Ready to go back to Menu
session.findById("wnd[0]/tbar[0]/btn[3]").press

End Function

II. Conclusion and Next Steps

This example is a simple solution to automate the PO creation process, with a bit of customization it can be also used to modify a PO that has already been created.

In the next part we’re going to see how: