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

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

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.

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

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

Setting Up Connection with SAP GUI

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 As String, SAP_CODE As String, Listing_Procedure As String, N As Long)
' 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 and fill 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) Get 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:01"))
session.findById("wnd[0]/tbar[0]/btn[3]").press
End 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) Variables for Listing Process
Dim W_Src_Ord As Object
Dim W_Ret As Boolean
Dim N As Integer
Dim SAP_CODE As String
Dim Plant As String
Dim Listing_Procedure As String

' (2) Connect to a system
Set objSheet = ActiveWorkbook.Sheets(1)
W_Ret = Create_SAP_Session()

' (3) If Create_SAP_Session returns Nothing, display message
If Not W_Ret Then
    MsgBox "Not connected to client", vbCritical + vbOKOnly
    Exit Sub
End If

' (4) Loop through all lines
N = 2
While Sheets("Listing").Cells(N, 1).Value <> ""
    
    ' (5) Get Parameters from Excel Sheet
    SAP_CODE = Sheets("Listing").Cells(N, 1).Value
    Plant = Sheets("Listing").Cells(N, 2).Value
    Listing_Procedure = Sheets("Listing").Cells(N, 3).Value
    
    ' (6) Call the function
    Call Listing_Function(Plant, SAP_CODE, Listing_Procedure, N)
    
    ' (7) Confirm with a "V"
    Sheets("Listing").Cells(N, 4).Value = "V"
    
    ' Move to the next row
    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

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