Data for Sustainability & Productivity
Newsletter
Automation & Reporting

SAP Automation of Product Listing for Retail

Automate Product Listing in SAP with SAP GUI Scripting Tool using Visual Basic

SAP Automation of Product Listing for Retail

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

Material Listing Process in SAP Retail — (Image by Author)
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.

  1. Launch Transaction WSM3
Mass Listing Menu from Tcode “WSM3” — (Image by Author)

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.

Results of Listing of one SAP Code Variant (+ 1 Generic) — (Image by Author)

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.

  1. Objective: build a fully automated bot

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

Excel Input File with Material Codes and Plants — (Image by Author)
  • 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)
Automation Bot performing Listing of Articles Codes listed in the Excel File — (Image by Author)

2. Excel VBA Script

Based on the two examples presented in Part 1, we must build our solutions in three parts:

  1. Setting Up Connection with SAP GUI
    Procedure: Create_SAP_Session
  2. Scripting to Perform Action
    Function: Listing_Function to perform a listing
  3. Looping in Excel File
    Procedure: Listing_Process to loop through all lines
Declare Public Variables
Option Explicit'Variables for SAP GUI ToolPublic SapGuiAuto, WScript, msgcolPublic objGui As GuiApplicationPublic objConn As GuiConnectionPublic session As GuiSessionPublic objSBar As GuiStatusbarPublic objSheet As Worksheet'Variables for FunctionsPublic Plant, SAP_CODE, Listing_Procedure As StringDim W_SystemDim 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 CreationDim il, itDim W_conn, W_Sess, tcode, Transac, Info_SystemDim N_Gui As IntegerDim A1, A2 As Stringtcode = Sheets(1).Range("B3") 'Get Transaction Code'(2) Get System Name in Cell(2,1) of Sheet1If mysystem = "" ThenW_System = Sheets(1).Cells(2, 2)ElseW_System = mysystemEnd If'(3) If we are already connected to a Session we exit this functionIf W_System = "" ThenCreate_SAP_Session = FalseExit FunctionEnd If'(4) If Object Session is not null and the system is matching with the one we target: we use this objectIf Not session Is Nothing ThenIf session.Info.SystemName & session.Info.Client = W_System ThenCreate_SAP_Session = TrueExit FunctionEnd IfEnd If'(5) If we are not connected to anything and GUI Object is Nothing we create oneIf objGui Is Nothing ThenSet SapGuiAuto = GetObject("SAPGUI")Set objGui = SapGuiAuto.GetScriptingEngineEnd If'(6) Loop through all SAP GUI Sessions to find the one with the right transactionFor il = 0 To objGui.Children.Count - 1Set W_conn = objGui.Children(il + 0)For it = 0 To W_conn.Children.Count - 1Set W_Sess = W_conn.Children(it + 0)Transac = W_Sess.Info.TransactionInfo_System = W_Sess.Info.SystemName & W_Sess.Info.Client'Check if Session Name and Transaction Code are matching then connect to itIf 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 ThenSet objConn = objGui.Children(il + 0)Set session = objConn.Children(it + 0)Exit ForEnd IfNextNext'(7) If we can't find Session with the right System Name and Transaction Code: display error messageIf session Is Nothing ThenMsgBox "There is no active session found for " + W_System + " with transaction " + tcode + ".", vbCritical + vbOKOnlyCreate_SAP_Session = FalseExit FunctionEnd If'(8) Turn on scripting modeIf IsObject(WScript) ThenWScript.ConnectObject session, "on"WScript.ConnectObject objGui, "on"End If'(9) Confirm connection to a sessionCreate_SAP_Session = TrueEnd Function
System Code: T01220 — (Image by Author)

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 Listing_Function(Plant, SAP_CODE, Listing_Procedure, N)'Function for Listing'(1) if you want to maximize the screensession.findById("wnd[0]").Maximize:'(2) call the transactionsession.findById("wnd[0]/tbar[0]/okcd").Text = "wsm3"session.findById("wnd[0]").sendVKey 0'(3) ticking options on listingsession.findById("wnd[0]/usr/chkLSTFLMAT").Selected = Truesession.findById("wnd[0]/usr/chkLIEFWERK").Selected = True'(4) clear + filling Plant, SAP Code and Listing Proceduresession.findById("wnd[0]/usr/ctxtASORT-LOW").Text = ""session.findById("wnd[0]/usr/ctxtASORT-LOW").Text = Plantsession.findById("wnd[0]/usr/ctxtMATNR-LOW").Text = ""session.findById("wnd[0]/usr/ctxtMATNR-LOW").Text = SAP_CODEsession.findById("wnd[0]/usr/ctxtLSTFL").Text = ""session.findById("wnd[0]/usr/ctxtLSTFL").Text = Listing_Procedure'(5) validatesession.findById("wnd[0]/usr/chkLIEFWERK").SetFocussession.findById("wnd[0]/tbar[1]/btn[8]").press'(6) getting start date from window and paste it in column 5Sheets("Listing").Cells(N, 5) = session.findById("wnd[0]/usr/lbl[0,0]").Text'(7) wait 1 second and come back to transaction menuApplication.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
Sub Listing_Process()'Procedure to perform Listing'(1) Variable for Listing ProcessDim W_Src_OrdDim W_Ret As BooleanDim N As IntegerDim N_max As Integer'(2) Connect to a systemSet objSheet = ActiveWorkbook.Sheets(1)W_Ret = Create_SAP_Session()'(3) If Create_SAP_Session Return Nothing messageIf Not W_Ret ThenMsgBox "Not connected to client"End If'(4) Loop though all linesN = 2While Not (Sheets("Listing").Cells(N + 1, 1) = "")'(5) Get Parameters from Excel SheetSAP_CODE = Sheets("Listing").Cells(N, 1)Plant = Sheets("Listing").Cells(N, 2)Listing_Procedure = Sheets("Listing").Cells(N, 3)'(6) Call the functionCall Listing_Function(Plant, SAP_CODE, Listing_Procedure, N)'(7) Confirm with a "V"Sheets("Listing").Cells(N, 4) = "V"N = N + 1WendEnd 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
SAP Automation of Orders Creation for Retail | by Samir Saci
Automate Purchase Order Creation in SAP with SAP GUI Scripting Tool using Visual Basic
Samir SaciSamir 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

SAP Automation for Retail
Design RPA solutions using Visual Basic scripts built with SAP GUI Scripting Tool
Samir SaciSamir Saci

Read next