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