SAP Automation of Product Listing for Retail
Automate Product Listing in SAP with SAP GUI Scripting Tool using Visual Basic
Automate Product Listing in SAP with SAP GUI Scripting Tool using Visual Basic
Article originally published on Medium.
I. Material Listing in SAP
1. What is Material Listing?
In SAP Retail, the Material listing is the operation that links one article to one assortment.
Definition: An assortment is an SAP Retail object to which materials are assigned (a process known as “listing”).
Example: SAP is used by your Retail Company with a scope including 20 stores named {ST01, ST02, … ST20}. These stores are delivered by Trucks from two Distribution Centers (DC) named {WH01, WH02}.
Scenario — you want to buy a new reference (SAP Code: 145654789) that will be delivered by the supplier in WH02 and only sold in ST04.
Listing Process
- Listing 145654789 in WH02: this reference can be received in this DC
- Listing 145654789 in ST04: this reference can be received and sold in ST04
Edit: You can find a Youtube version of this article with animations in the link below.
2. How to perform Material Listing?
WSM3 (Mass maintenance) transaction can be used to operate a mass listing of reference to a specific plant to control which assortments a store can purchase or sell.
A store cannot receive or sell an article that is not contained in one of its assortment.
- Launch Transaction WSM3
To list our article in the store we need to fill:
- Assortment: ST04
- Material: 145654789
- Listing Procedure: depending on your organization
- Listing Valid From: dd.mm.yyyy
- Listing Valid To: dd.mm.yyyy
- Selection Options
2. Validation and Check
Start by clicking on the green button on the up left of the page (Button 1).
SAP will load the Listing report where you can see Material Codes (+ Variant) and the respective plant where they are listed.
After validation, we need to check the record date is correct — this will prove that your listing is successfully done.
3. Manual Sub-Tasks
A set of manual sub-tasks have to be operated to perform listing, they can be split into two categories:
Manual Data Inputs
Filling Forms, Clicking on buttons, Uploading Data and any other action of information transfer from User to SAP GUI
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 Start Date checking in category 2.
3. How to Automate Material Listing using SAP GUI Scripting?
Our target is to fully Automate the process using a Visual Basic Bot script in an Excel File where Inputs Data will be extracted, and Output Data will be recorded.
- Objective: build a fully automated bot
A list of articles with respective plants in an Excel Spreadsheet:
- Column A/B/C: Material Code, Plant Code and Listing Procedure to be typed in Transaction Form [1]
- Column E: Start Date to be extracted from Listing Report [2] (to confirm that the listing is correctly done)
2. Excel VBA Script
Based on the two examples presented in Part 1, we must build our solutions in three parts:
- Setting Up Connection with SAP GUI
Procedure: Create_SAP_Session - Scripting to Perform Action
Function: Listing_Function to perform a listing - Looping in Excel File
Procedure: Listing_Process to loop through all lines
Declare Public Variables
'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 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
- Steps 1 to 4: Connect to SAP following System Name that can be found in GUI Window
- 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
Taking Plant Code, SAP_Code and Listing Procedure to fill the form:
- Steps 1 to 2: Connect to WSM3 Transaction
- Steps 3 to 5: Filling the form and validate
- Step 6: Extract Start Date from Listing Report to confirm procession
- Step 7: Leave the report for the next article
'Function for Listing
'(1) if you want to maximize the screen
session.findById("wnd[0]").Maximize:
'(2) call the transaction
session.findById("wnd[0]/tbar[0]/okcd").Text = "wsm3"
session.findById("wnd[0]").sendVKey 0
'(3) ticking options on listing
session.findById("wnd[0]/usr/chkLSTFLMAT").Selected = True
session.findById("wnd[0]/usr/chkLIEFWERK").Selected = True
'(4) clear + filling Plant, SAP Code and Listing Procedure
session.findById("wnd[0]/usr/ctxtASORT-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtASORT-LOW").Text = Plant
session.findById("wnd[0]/usr/ctxtMATNR-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtMATNR-LOW").Text = SAP_CODE
session.findById("wnd[0]/usr/ctxtLSTFL").Text = ""
session.findById("wnd[0]/usr/ctxtLSTFL").Text = Listing_Procedure
'(5) validate
session.findById("wnd[0]/usr/chkLIEFWERK").SetFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press
'(6) getting start date from window and paste it in column 5
Sheets("Listing").Cells(N, 5) = session.findById("wnd[0]/usr/lbl[0,0]").Text
'(7) wait 1 second and come back to transaction menu
Application.Wait (Now + TimeValue("0:00:1"))
session.findById("wnd[0]/tbar[0]/btn[3]").pressEnd Function
Looping in Excel File
Looping through all lines getting Plant Code, SAP_Code and Listing Procedure to call Listing_Function:
- Steps 1 to 3: Creation of SAP GUI Object
- Steps 4 to 6: Loop through the file and perform Input/Extract ActionsPublic
'Procedure to perform Listing
'(1) Variable for Listing Process
Dim W_Src_Ord
Dim W_Ret As Boolean
Dim N As Integer
Dim N_max As Integer
'(2) Connect to a system
Set objSheet = ActiveWorkbook.Sheets(1)
W_Ret = Create_SAP_Session()
'(3) If Create_SAP_Session Return Nothing message
If Not W_Ret Then
MsgBox "Not connected to client"
End If
'(4) Loop though all lines
N = 2
While Not (Sheets("Listing").Cells(N + 1, 1) = "")
'(5) Get Parameters from Excel Sheet
SAP_CODE = Sheets("Listing").Cells(N, 1)
Plant = Sheets("Listing").Cells(N, 2)
Listing_Procedure = Sheets("Listing").Cells(N, 3)
'(6) Call the function
Call Listing_Function(Plant, SAP_CODE, Listing_Procedure, N)
'(7) Confirm with a "V"
Sheets("Listing").Cells(N, 4) = "V"
N = N + 1
Wend
End Sub
II. Conclusion and Next Steps
WSM3 Transaction is helpful for Mass Listing when you have many articles to list in a single Plant.
But, to list a single article to many different plants, this Bot will be helpful. You only need to list the different plants in this Excel file where VBA Procedures/Functions are written.
In the next article, we’re going to tackle
- Purchase Order Creation
A purchase order is a document used to request items or services from a vendor at an agreed-upon price
Samir Saci
- Goods Transfer Orders Extraction
Listing transfer deliveries in the system in one data entry transaction
About Me
Let’s connect on Linkedin and Twitter, I am a Supply Chain Engineer that is using data analytics to improve logistics operations and reduce costs.
References
[1] Samir Saci, SAP Automation for Retail Using VB
Samir Saci
