Permanent Fix For Performance Issues Report RM07MLBD (T Code : MB5B)

former_member596774 · ‎11-29-2018

Permanent Fix For Performance Issues Report RM07MLBD (T Code : MB5B)

Applies to:

It is independent of SAP Release Version.

Summary

The transaction MB5B (Stock on posting date) is used commonly in the business for monitoring day today stock and goods movement posting relevant activities. With increasing data in the Material Master and Material Document tables; sluggish performance related issues are experienced for this report. Following white paper will provide the process of fixing all performance issues for this report with functional details.

Author(s):        Sharabh C. Chaudhari

Company:        Vodafone India Services Pvt. Ltd.

Created on:      01 November 2018

Author Bio

Sharabh Chaudhari is an employee of Vodafone India Services Pvt. Ltd. He has total 13+ years of experience out of which 12 years of experience in SAP MM WM QM SRM Consulting. His experience includes 4 implementation and 4 support projects from different industries like Healthcare, Food and Beverage, Telecommunication, Auto component manufacturing and CNC Machine tool manufacturing industries.

Challenges experienced for runninG transaction MB5B (Stock on posting date)

User feels some problem during day to day inventory operations; for confirming or monitoring inventory relevant posting user checks transaction MB5B (Stock on Date). User is trying to fetch the report for multiple materials and multiple plants.

Reasons and situations when above errors appear

When too many individual material numbers are entered in selection screen for database selection then Runtime error “DBIF_RSQL_INVALID_RSQL” might appear.

Runtime of the report RM07MLBD is too long because it works following way.

If goods movements (GI or GR) are posting during running the report “RM07MLBD”; may return incorrect results. Please consider following example for such parallel processing.

Example:

Suppose transaction MB5B is processing 100 materials with near about 10000 material documents for each.

Result is inappropriate in above case as the Stock and Values are captured before the Goods movement and Material and FI document is considered for calculating stock for start and end date.

SOLUTION

Report RM07MLBD runs good for one “Material + Plant” combination at a time. Using this fact the solution can be built on logic as below.

Running MB5B for only one “Material + Plant” Combination at a time.

Collecting Outputs for each of these runs.

At the end these collected outputs are to be prepared for Large List Output.

For defining above solution 4 custom objects should be defined and 1 preexisting SAP table can be used. One need 3 custom reports and 1 custom database table with following specifications.

LOGIC FLOW

1. Database table ZZMB5B

Following fields should be defined in this table. Above table size can be decided on the number of entries in Valuation Table for Material (MBEW)

Material Number

Plant

Valuation Area

Key field for INDX Cluster Table

2. Custom Report ZZ_MB5B_Prepare:

This report design is aimed to fill up entries of “material + plant” combinations in the database table ZZMB5B. It is proposed to perform following activities in the given sequence.

Step1. Delete all entries from ZZMB5B table and INDX table which may exists from previous run of the mass data processing run of MB5B report.

Step2. Here it should be splitting its logic based on selected mode of transaction MB5B with following 2 options.

Stock type mode = Valuated mode

Add one entry in the table ZZMB5B from table MBEW as stock type mode is valuated stock.

Every new entry in table ZZMB5B should be assigned with unique 22 character string which can be used as a key for cluster table INDX.

3. Report ZZ_MB5B_Monitor

This program reads all the entries from table ZZMB5B sequentially and locks all entries or Material + Plant Combinations. The execution of the lock is performed on the basis of stock type mode selection in the report as given below.

3.1.1 When stock type mode = Valuated Stock
For every “Material + Valuation Area” combination determined by the system; are then blocked in table MBEW.
This locking is required so as to ensure no parallel goods movement or material change posting during the run of the functionality which is alternative for MB5B.
3.1.1 When stock type mode is “Other” then,
For every Material + Plant combination determined by the system; all of these combinations are then blocked in table MARC.
3.2 Only after locking all “material +plant” combinations in either MBEW or MARC table successfully; which are found in ZZMB5B table further processing should start.
3.2.1 Start report for RM07MLBD for current Material +Plant Combination or Material + Valuated area combination depending on required mode.
3.2.2 The output list created for each of the run is stored with generated key term (based on Material +Plant Combination) in INDX table.
3.2.3 Current entry should be deleted from table ZZMB5B.
3.2.4 Database updates are executed with COMMIT WORK AND WAIT.
3.2.5 The lock is released for the entry.
3.2.6 The next entry should be approached by the system for processing steps from 3.2.1 to 3.2.6

4. USE OF LOCK APPLICATION
- Locking entries ensures that no goods movements can be posted during runtime of the RM07MLBD for the “Material + Plant “Combination to be processed.
- This process ensures that all “Material + Plant” Combinations can be processed at the same time. So several in stances of this program can run in parallel at the same time so as to minimize the run duration for the report.
- Only few seconds are required for system to process particular Material + Plant Combination so minimum disruption to production system.
- This report can be started and run till there are entries in ZZMB5B.
- Even if report is terminated in between it can be started again.
5. REPORT ZZ_MB5B_PRINT
Once all combinations (Material + Plant) are processed from ZZMB5B table this report can be run to print saved lists from INDX cluster table and adds individual lists to complete list output.

SPECIAL Functionalities used by ABAP for development

DEMO CASE

Changes suggested Report ZZ_MB5B_Monitor over SAP suggested standard flow

The difference in SAP suggested flow and what is explained here is instead of locking all “Material + Plant” Combinations initially, these entries are to be locked one by one. Some of the features are as explained below.

Material + Plant combinations are locked and unlocked one by one so rests of the entries are free for goods movement processing.

Material + Plant Combinations are locked and unlocked one by one from ZZMB5B table also so as to avoid processing entry if in future user has access to ZZMB5B table.

Step of deletion of entries from ZZMB5B table at the end is excluded.

Material + Plant Combinations which are could not be processed (since cannot be locked) are marked with check box in table ZZMB5B

Because of 3^rd feature, Material + Plant combinations are assigned with Key field in ZZMB5B to join INX table. Its easy for print program to gather entries from INDX table for Combine the large Output.