Loading Materials to Maintenance Work Orders
Digitalization Project (REPSOL)
Spare Parts Warehouse Area
Location: C.I. Tarragona
Date: January, 2023 - July, 2023
Objective: GP2024 Optimization
Team Members
| Name | Company | Contact |
|---|---|---|
| Carlos Gonzalez | REPSOL | cgonzalezv@repsol.com |
| Ángel A. Urbina | REPSOL | aaurbinas@repsol.com |
| Manuel Collado | ESERMAN | manuelcollado@eserman.com |
| Antonio Sabate | ESERMAN | antonio.sabate@servexternos.repsol.com |
"Chance favors the connected mind."
― Steven Johnson, Where Good Ideas Come from: The Natural History of Innovation
"There is immense power when a group of people with similar interests gets together to work toward the same goals. Players focus on the wins and the loses. Builders focus on the team and future of the vision. Let’s move from team player to team builder."
― Idowu Koyenikan, Wealth for All: Living a Life of Success at the Edge of Your Ability
In the highly competitive and challenging environments of a modern industrial company, the demand for continuous optimization of processes is a basic necessity. One of the ways to achieve these objectives is to allow the hidden value residing in the knowledge and experience of people collaborating daily in the different areas that make up each team to emerge. In our case, the spare parts warehouse services of the REPSOL Industrial Center in Tarragona.
The goal sought in this work has been the optimization of the management process for major shutdowns. We must thank all the staff of both REPSOL and ESERMAN for their willingness and open-mindedness that has allowed the development of this project.
Warehouse Team
REPSOL Industrial Center Tarragona, July 2024
Project Objective
Executive Summary
This project arises from the need to optimize the material dispatch process from the warehouse during peak material demand periods. (Mainly Plant Shutdowns)
This need arises due to the concurrence of processes in the warehouse that cause interference in the management of materials and, by extension, in the work of the services that are users of the same during the shutdowns.
Situation during shutdowns
- Large number of materials received.
- Large number of material reservations generated for maintenance orders.
- High number of materials being prepared to be removed in blocks.
- Withdrawal of blocks of materials prepared earlier.
- Generation of a high number of new purchase order requests.
- Need to carry out the material planning process to maintain the stock of the warehouses.
Experience shows that it is critical that the information available about the situation of materials in the SAP management system corresponds to reality in the shortest possible time. The previous systematic work caused delays in information management and bottlenecks in the internal processes of the warehouse.
In the framework of this project, the internal process of material preparation in the warehouse was analyzed, and several points of improvement were identified. As a result, several robots have been designed and built aimed at automating and simplifying the following activities:
Constructed Robots
- Automatic generation of support listings for the preparation of materials
- Automation of the consumption loading process to maintenance orders.
The work done is in line with the strategic line of digitalization adopted by Repsol.
0.1.1. Report Objective
The final goal of this report is to allow the results obtained in this project and the methodologies used to be known and transferred to other areas of the company that might need them in the future.
We believe that the dissemination of the lessons learned during the process of developing the adopted solutions will facilitate the process of implementing these same solutions or similar ones in areas with problems similar to those addressed.
This document summarizes the process followed in the development and implementation of the implemented solutions. It describes the challenges we have faced and how we have addressed them to achieve the final goal of optimizing the service to which we belong.
0.2. What has been done?
Based on the experience gathered in previous shutdowns, a working group was formed to try to optimize and solve the bottlenecks identified in the management of shutdowns. All this with a view to improving the workflow of the warehouse for the planned GP2024.
Main developments made
Generation of Material Listings to optimize the work of picking materials. A listing will be generated with the following information:
- Order: Work Order
- Operation: Operation within Order
- Reservation: Material Reservation
- Reservation Line: Reservation Line
- Material: Reserved Material
- Text: Material Description
- Required Amount: Necessary Quantity
- Taken Amount: Quantity Taken
- Prepare: Quantity to Prepare
- WAREHOUSE TYPE: Warehouse Type
- WAREHOUSE LOCATION: Location
- STOCKS: Stock
- BATCHES: Batch
- CLASS: Valuation Class
- BLOCK: Block
- GENERIC/SPECIFIC: Generic Specific
- Workstation: Workstation Example Format (TUB)
- QUANTITY: Quantity actually taken
- DESTINATION: Destination of the goods
- BATCH: Material Batch / if any
Automatic process of Mass loading of materials to Work Orders
- Significant reduction in the material management processes during shutdowns.
- Allows quick updating of the situation of served materials.
Impacts Generated
Some estimates of the impact obtained are summarized in the following table:
Benefits Obtained
Generation of Material Listings to optimize the work of picking materials. In the previous situation, the process was completely manual. Having the operator repeatedly consult the SAP system consulting several transactions with the goal of identifying available stocks and locations of materials. Once identified by the operator, he had to proceed to perform the manual picking. The implemented solution allows:
Freeing operators from the process of manual consultations in SAP for each material
Having an ordered listing in printed format with relevant information for automatic material picking
Mass loading of materials to Work Orders. In the previous situation, operators had to deduct material vouchers from the reserves manually, having to consult several SAP transactions. This generated delays of weeks from when the material was served until it was discounted in the system. The new system allows:
Allow the warehouse manager to easily introduce in an electronic format the real information of the materials prepared in the process
Automatic loading in SAP of materials to work orders
Delivery of two executable files to facilitate the work of the collaborating company. The developed solutions are delivered for use in the executable format in the SAP work environment of REPSOL to the contractor.
The technology used in the solution of these problems is easily replicable in other areas of the company where SAP is implemented.
The solutions provided are particularly relevant in processes where the development cost of solutions by external companies or the cost of using licenses of commercially marketed programs make the standard solutions proposed by the company unviable.
About the Project
This project arises from the need to optimize the material dispatch process from the warehouse when there are high peaks of material demand. (Mainly Plant Shutdowns) In such situations, it is critical that the information available about the materials in the management system SAP corresponds to reality.
The most significant changes made throughout the project are summarized below.
The complete project is documented at: Link
0.4. Version Control
Most important changes of the project.
| Version | Notable changes |
|---|---|
| 0.1 | • First version of the document |
| • Initial document design | |
| 0.2 | • Design contents |
| 1.0 | • First document version |
| 1.1 | • Review after operational tests |
Preface
Contents i
Executive Summary
| Summary | ii |
|---|---|
| 0.1 Project Objective | ii |
| 0.1.1 Report Objective | iii |
| 0.2 What has been done? | iii |
| 0.3 Generated Impacts | iv |
About the Project
0.4 Version Control v
List of Graphics
List of Tables viii
Introduction
| 1.1 Objective | 1 |
|---|---|
| 1.2 Followed Process | 1 |
| 1.2.1 What motivated us to generate this system? | 1 |
| 1.2.2 Study of Alternatives | 1 |
| 1.2.3 Self-Service Approach Analysis | 2 |
| 1.2.4 Proposed Solution | 3 |
Proposal
| 2.1 Implemented Solution | 4 |
|---|---|
| 2.1.1 Considerations to be taken into account for all solutions | 5 |
| 2.2 Generation of Work Listings | 6 |
| 2.2.1 Program Entry | 6 |
| 2.2.2 Program Output | 7 |
| 2.3 Loading of Prepared Materials to Maintenance Work Orders | 8 |
| 2.3.1 Entry | 8 |
| 2.3.2 Output | 8 |
Generation of Work Listings
| 3.1 Detail of Steps- (PDD) | 11 |
|---|---|
| 3.2 Program Code | 11 |
Loading Materials to WOs
| 4.1 Detail of Steps- (PDD) | 12 |
|---|---|
| 4.2 Program Code | 12 |
References
Used Resources 13
A Used Auxiliary Resources
A.1 Used Auxiliary Resources 13
B Source Codes
B.1 Code of Programs Generated to Solve the Problem 14
C Version Control
Version Control 15
| List of Graphics | 1.1 Applicability of RPA Technology | 2 |
|---|---|---|
| 2.1 Conceptual Scheme | 10 |
| List of Tables | 3 |
|---|---|
| Company Solutions | 3 |
| Comparative Alternatives | 3 |
| Conceptual Process Scheme | 9 |
| Version Control | 15 |
Objective
The objective of this report is to document in detail the process followed for the optimization of the preparation and loading process in Maintenance Work Orders of the Materials reserved for shutdowns. The process has been designed to be scalable and repeatable at the various Industrial Centers of Repsol that use the SAP management system. It is our intention to serve as an inspiration to other organizations within the company to be able to repeat a similar work system in their facilities.
Followed Process
What motivated us to generate this system?
The factors that originated this project.
Project Triggering Factors
Need to prepare a large number of materials reserved in multiple Work Orders for different contractors and within specific deadlines. Need to have real-time information on the situation of the various necessary materials at all times. Need to simplify the work required for the physical preparation of materials.
Study of Alternatives
The first thing that was done was to contact the ERP area managers at the digital HUB of REPSOL. It was identified that initially, these robotization initiatives were based on hiring external consultants to carry them out (Accenture, etc.). These companies leveraged the experience they had in other companies to generate the applications.
Followed Process
What conditions must a process meet to be robotized?
It is a repetitive task based on rules with structured data and frequent work peaks.
Figure 1.1: Applicability of RPA Technology
This fact meant that the costs were quite high, leaving this technology out of reach for internal processes whose profitability thresholds did not reach the defined minimum economic level.
To avoid this disadvantage, the company, in a second step, decided to bet that the robotization initiatives would be led by the company's own employees, providing REPSOL with the development tools. Being the own employees beneficiaries of the possible improvements who should lead the projects with their initiative and also should be responsible for providing support and use to the developed solutions.
It was thought that proceeding in this way would accelerate the process of implementing this type of solutions in sites not yet identified and simultaneously the implementation and maintenance costs would be significantly lower than those followed with the first implementations.
It is for this reason that the company made available to the employees two tools, one available within the Microsoft office 365 package (POWER AUTOMATE) aimed at automating processes involving Microsoft tools and another more sophisticated (DO IT YOURSELF) which as a main advantage allowed the interaction between multiple environments as in our case SAP, etc.
Self-Service Approach Analysis
The two tools proposed by the company present the following advantages and disadvantages. They are summarized in Table 1.1
Followed Process
| Capability | Only Windows Products | Possible to use with SAP |
|---|---|---|
| End User Cost | License Included | >5000 euros/year-user |
| Maintenance | No Support | No Support |
| Learning | Self-Training | Self-Training |
| Ease of Use | Simple | Medium |
The problem raised in our process involved the need for the tool to be used in SAP. Simultaneously, the cost of the solution for SAP prevented its immediate use because the estimated cost savings did not justify the high annual license cost required for its use.
Proposed Solution
Since the standard solutions proposed by the company did not adequately satisfy our needs, a systematic search for possible alternative solutions involving the two necessary aspects was initiated.
Necessary Requirements:
- Reduced cost
- Capability to be used with SAP
A search for different open-source solutions identified that open software tools presented a great potential not yet explored.
| POWER AUTOMATE | DO IT YOURSELF | PYTHON | |
|---|---|---|---|
| SAP | NO | YES | YES |
| LICENSE TYPE | PROPRIETARY | PROPRIETARY | FREE |
| LICENSE | INCLUDED | 5000 euros/year-user | FREE |
| SUPPORT | USER | USER | USER |
| COMPLEXITY | LOW | MEDIUM | HIGH |
| USE | Simple | Medium | Medium |
Contact was made with SAP experts and people in the systems organization who had had experience in RPA, identifying that there had already been approaches in the past using proprietary tools such as visualbasic that had given very good results.
Based on these previous experiences, it was decided to use more modern tools following the previous approach. Therefore, Python will be used in an Anaconda environment. This solution is in line with the corporate policy of training personnel in this tool. The analysis is condensed in Table 1.2.
2.1. Implemented Solution
Proposal 2 Once the technical options available were analyzed, it was decided to address the problem through the use of Python scripts in an Anaconda environment.
The approach adopted will be based on the subdivision of the global process of loading materials to maintenance work orders into two steps. This allows addressing the problem in a more optimal way. The steps developed are:
- Generation of listings to simplify the work of the personnel in charge of performing the pickings of materials.
- Process of loading prepared materials to maintenance work orders.
For the development of each of these steps, the work was carried out in the following way:
- Conceptualization of needs from the user's point of view.
- Development of Python program over SAP in the test environment.
- Validation of execution in the SAP test environment.
- Validation and verification by end users of the developed application.
- Test of tests in the production environment.
- Construction of executables of the programs.
- Pass to production.
2.1. Implemented Solution
2.1.1. Considerations to be taken into account for all solutions
The programs were developed in an Anaconda environment in Python. In a second step, they were compiled to allow execution on any computer.
Technical requirements production environment • Windows machine with SAP system installed. This system must have the script execution environment of the SAP GUI activated. • The user who runs the program must have an active SAP account with permissions to execute the necessary transactions for the loading of materials in work orders. • Before running the script, the main SAP GUI screen must have been activated. • It is not necessary to have Python installed on the machine from where the scripts will be executed.
These requirements are based on the fact that the programs developed in python will have been previously compiled into an executable to simplify the work. Thus, the end user will only have executable and auxiliary files that they must copy to their computer.
The programs have requirements for their operation that are described in the following sections:
Generation of Work Listings
The program is designed to simplify the work of mass picking of materials by generating an Excel file with the necessary information to simplify the manual picking operation.
Program Entry
The first step is the generation of a document that defines the batch of materials to be prepared by REPSOL personnel.
The list of materials on which the picking will be performed will be defined in an Excel file that will be the only entry to the process.
Input Data
Specifications
- Directory: Selected in the application
- File: "RESERVAS.xlsx" provided by REPSOL.
NOTE: The name of the provided file may vary. It will contain:
- Code: Material Code. Example Format (80021249)
- Description: Material Description
- OT: Work Order Number
- Operation: Operation Number
- Reservation: Reservation Number
- Reservation Position: Reservation Position. Example format (14)
- Quantity diff: Quantity of Material to Prepare
- UM: Unit of Measure
- Status line: Line status. example format (MACO)
- Block: Block. example format (B1)
- Generic/specific: Generic / Specific. example format (G)
- PstoTbjo: Workstation type. Example (TUB)
- SumOfQuantity diff: Quantity of Material to Prepare
- Class: OT Class (corrective, etc..)
- Area: Plant Area (POL)
- Specialty: Maintenance Specialty (ESTA,...)
- Technical Location: Technical Location (T-4-POL-22,..)
- Equipment: Equipment on which work is being done
- Equipment Description: Description in text of the equipment on which work is being done
The above information will feed the data generation program for picking.
Each time a new batch of materials needs to be prepared, the program will be executed.
In that process, the program will check the current stock of materials, available locations, and other necessary information for picking. An Excel file will be generated with a summary of the information that the operator needs to optimally perform their work on the materials in the batch.
As the manual picking process is developed, the operator will have to include the information of what has been done in the corresponding column of the file generated in the process.
Generation of Work Listings
QUANTITY: Actual quantity of material taken
DESTINATION: Location where the material will be left
BATCH: Data that may identify the batch of the material if necessary.
This will allow having an adequate file for the effective loading in Excel in SAP of the materials taken.
Program Output
The output generated in this step will be an Excel file that will contain all the relevant information for the picking process for the operator.
Technical Requirements
An Excel file with the necessary information for picking will be generated.
- Directory: /
- File: "DD-MM-YYYY-HH-MM-SS_PREPARAR.xlsx" Resulting file
NOTE: The information available in PREPARAR.xlsx will be:
| Order | Operation | Reservation | Reservation Line | Reserved Material | Material Description |
|---|---|---|---|---|---|
| Quantity to Prepare | Quantity to Prepare Global in a Block | Quantity to Prepare | UM, Unit of Measure | Warehouse Type | Warehouse Location |
| Stock | Batch | Valuation Class | Block | Generic Specific | Workstation. Example Format (TUB) |
| OT Class (corrective, etc..) | Plant Area (POL) | Maintenance Specialty (ESTA,...) | Technical Location (T-4-POL-22,..) | Equipment on which work is being done | Description in text of the equipment on which work is being done |
| Quantity actually taken | Destination of the goods | Information to identify the batch Material / if any |
2.3. Loading of Prepared Materials to Maintenance Work Orders.
Once the materials are prepared from the information in the 'PREPARAR.xlsx' file. The fields will be completed:
- QUANTITY: Quantity of material actually taken
- DESTINATION: Location where the materials will be left prepared
- BATCH: Information about the batch of the material if any.
Once that information is filled in the corresponding file, it will be used as an input to the program that performs the material entries in the system.
The executable program is run.
2.3.1. Entry
Technical Requirements
- Directory: Chosen in the application
- File: "DD-MM-YYYY-HH-MM-SS_PREPARAR.xlsx" Filled out by the operator
2.3.2. Output
The execution of the program will produce the following result
Loading of Prepared Materials to Maintenance Work Orders
Technical Requirements:
- Directory: /CARGAMATERIALES
- File: InformeEjecuccion.xlsx (Outputs). Information on the execution of the program
- 'Order' - Work Order
- 'Operation' - Operation
- 'Reservation' - Material Reservation
- 'Reservation Line' - Reservation Line
- 'Material' - Reserved Material
- 'Text' - Material Description
- Prepare - Quantity to Prepare
- PrepareGroupBlock - Quantity to Prepare Global in a Block
- UM - UM , Unit of Measure
- WAREHOUSE TYPE - Warehouse Type
- WAREHOUSE LOCATION - Location
- STOCKS - Stock
- BATCHES - Batch
- CLASS - Valuation Class
- BLOCK - Block
- GENERIC/SPECIFIC - Generic Specific
- 'Workstation' - Type of workstation
- MaintenanceClass - OT Class (corrective, etc..)
- Area - Plant Area (POL)
- Specialty - Maintenance Specialty (ESTA,...)
- TechnicalLocation - Technical Location (T-4-POL-22,..)
- Equipment - Equipment on which work is being done
- EquipmentText - Description in text of the equipment on which work is being done
- QUANTITY - Quantity actually taken
- DESTINATION - Destination of the goods
- BATCH - Batch Material / if any
- File: MAXIMO.log Logs with information on the material loading situation.
Operational Structure
Conceptual Scheme
| File Creation | File Passing |
|---|---|
| Program: GeneDocumentov5.py | Executablev5.py |
| Input: ENTRIES.xlsx | PREPARED.xlsx |
| Output: DATE_PREPARE.xlsx | DATE_OUTPUT.xlsx |
2.3. Loading of Prepared Materials to Maintenance Work Orders.
START MATERIALS TO PREPARE ENTRIES.xlsx GeneDocumentov4 DD-MM-YYYY-HH-MM-SS_PREPARAR.xlsx PICKING Executablev- DD-MM-YYYY-HH-MM-SS_OUTPUT.xlsx
Figure 2.1: Conceptual Scheme
Generation of Work Listings
3.1. Detail of Steps- (PDD)
The main steps are:
- REPSOL provides a file with the identification of reservations on which work will be performed.
- ESERMAN identifies the materials on which it wishes to perform the picking from the material listings provided by REPSOL previously.
- The program GeneDocumentoEXEv5 is executed to obtain the document for working on the pickings.
3.2. Program Code
Code in Annex
11
4.1. Detail of Steps- (PDD) Loading Materials to OTs 4
The objective of this step is to optimize the loading of materials for Work Orders using the Excel files generated in the previous point completed with the data obtained from the physical preparation of the materials carried out by the operators.
The main steps are:
- The operator who has performed the picking writes in the document the necessary information
- The GEjecuv4 program is executed which will deduct the reserved materials to the orders
- Possible errors are checked to identify problems such as deleted, canceled reservations, etc.
- Errors are corrected both physically and in the system if necessary.
4.2. Program Code
An executable in Python has been generated that will perform:
Reading Information of Prepared Materials
SAP Connection
For each served material
Generate Material Loading Document for each Reservation, Reservation Line, Batch, Valuation Class, Quantity, Destination.
Confirm Document
Generate Transport Order
Confirm Transport Order
Execution Report Generation
The code is available in the Annex
Auxiliary Resources Used
| Used Resources | A |
|---|---|
| External resources used | |
| (1) SAP Automation on 'Medium'. | |
| (2) Official SAP blog documentation on scripting. | |
| (3) SAP reference documentation 'how to use SAP gui with Python'. | |
| (4) Anaconda Package. | |
| (5) Python Language. | |
| (6) Source code of implemented solution. | |
| (7) Article on Medium REPSOL about RPA. | |
| (8) Introduction to Microsoft Power Automate. | |
| (9) Analysis of commercial RPA tools. | |
| (10) Scripting Tracker |
B.1. Code of Programs Generated to Solve the Problem
| Source Codes | Note |
|---|---|
| B | - With the goal of limiting the size of the report, the code used in the realization of the project is not annexed. - The generated code has a length of 60 pages. - If interested, please contact Angel A. Urbina from the Warehouse team |