Shulou(Shulou.com)06/03 Report--

Note: this article was originally written by the author and was first published in the first class of AZSAP: https://mp.weixin.qq.com/s/9yuDJohp1iNRR-hHTyT9WQ

In the first half of the year, as soon as I joined an operation and maintenance project, I received a job assignment from the head of the SAP operation and maintenance department of the customer's China headquarters, saying that I was asked to help optimize an inventory report, which probably needed to be redesigned and redeveloped.

The project is a private enterprise project, which was launched several years ago and is currently in the stage of operation and maintenance. A feature of SAP projects in private enterprises, you know, is that there are many kinds of enhanced development, so that the SAP system is mainly used as a database! Many standard functions are not used, and some standard function module are encapsulated in shells and then used in some custom features. For external consultants like me who did not participate in the early implementation process and joined halfway through the operation and maintenance of the project a few years later, they were assigned to optimize the problems left over by history as soon as they entered the project.

What shall I do? I am here to help customers solve problems and create value for customers, not to drink tea and chat. Therefore, although it is difficult to do, it is necessary to face up to the difficulties.

Since we want to optimize the report, it is necessary to understand the past and present situation of the current report.

First of all, the author learned something about this report with the client's internal consultant. After communicating with the internal consultant, I learned that the report has two hard injuries, the first of which is that the performance is super slow and basically unusable. I tested and found that the query date range, even from yesterday to today 2 days, the report will still run to death, reported to ABAP Runtime Error. This is really hard to use, how uncomfortable it is for the business unit! The second difficulty is that the inventory amount at the end of the period does not match the inventory amount in the balance sheet of the financial account.

Then it is necessary to find out the causes of the two major injuries in the report.

It is reported that the slow performance is due to the complexity of the report function and the large amount of data. From the first version of the report program to now, the report program has been changed countless times, added a lot of logic, and the function is becoming more and more complex. We should not only be able to query the purchase, sale and storage by batch, but also according to the storage location; we should not only query the inventory quantity, but also calculate the amount and area; and the calculation of its area also involves the classified view data in the material master data and batch master data; coupled with more than 40 factory codes in the customer system, over time, there is a huge amount of data on the movement of goods. The author asked the developer colleagues to help analyze the code and found that one of its performance bottlenecks takes too much time to query the purchase, sale and storage data of batches.

In order to find the root cause of the discrepancy between the initial final inventory balance and the account balance table, I also asked my colleagues to help analyze the code. After investigation, the report adopts a backward method to calculate the opening inventory, and its general logic is to first get the current real-time inventory of the system, and then calculate the goods movement data between the current date, the end date and the beginning date. Then calculate the quantity and amount of inventory at the beginning and end of the period. At the same time, we find that when calculating the inventory balance, the current reporting program only takes into account the changes in inventory value brought about by the movement of goods, but fails to take into account the movement of non-goods, such as modifying the cost price of materials, such as the change in inventory value caused by invoice verification, which is the reason for the difference between the inventory balance and the inventory balance.

After finding the root cause of the unserviceability of the current report, the author began the design of report optimization.

First of all, I cut the report function to increase the query granularity. I no longer support the purchase, sale and storage query by batch by storage location level, but only at the factory level. This avoids performance problems due to the fine granularity of the query in the case of large amount of data and complex logic. Of course, these suggestions need to be approved by the business team. The customer business team has suffered from this report for a long time, so the other side gladly agreed to my optimization proposal.

Secondly, I decided to use the forward method to get the ending balance, first capture the opening balance according to the MBEWH/MSEG/MKPF/BSIM table, and then grab the inventory change data caused by the movement of goods and other transaction codes within the query date, and calculate the ending balance. If the backward method is adopted, there may be performance problems. For example, if the business wants to query the purchase, sale and inventory of the period from 2015-01-01 to 2015-01-31, it needs to get the current inventory (now July 2018). Then calculate the difference between the moving amount of goods and the moving inventory of non-goods during the period from the current date to 2015-01-31 (more than three years). Then calculate the end-of-period balance, and then calculate the opening balance, there is no doubt that the performance will be slow in the system with a large amount of data. Using the forward method, we first calculate the opening balance of 2015-01-01, which can grab the data directly from some tables. Then we can get the opening inventory by querying the goods movement data and the non-goods movement inventory amount difference data in a very small period of time, and then query the inventory change data in the month from 2015-01-01 to 2015-01-31. Get the inventory balance for 2015-01-31. In this way, the amount of data that the report program needs to query is significantly reduced, of course, the performance is better.

Finally, when calculating the balance at the beginning of the period, it is necessary to go to the BSIM table to grab the data of the change in inventory value caused by the movement of goods. The author summarizes that it is necessary to grab the amount in the financial voucher with the voucher type of ML/PR/RE in the BSIM table, summarize it according to the debit and credit, and include the inventory difference amount field of non-goods moving within the specified query date, which participates in the calculation of the final balance. At the same time, the author inquires the information on the Internet and learns that in the purchase, sale and storage statements of some projects, often because the amounts in the three types of financial vouchers in the BSIM table are not taken into account, the balance of the purchase, sale and storage statement is inconsistent with the data of the account balance statement, which seems to be one of the common problems in the practice of SAP projects.

Under the guidance of these big ideas, the development of the new purchase, sale and storage report, the performance is so fast that the business staff are overjoyed, and the data consistent with the balance table of the financial account also makes the financial business staff no longer grieve for the purchase, sale and storage report.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.