During the build of this application, research provided insight into the frustrations that many BI developers are having with the replication of this report outside of SAP. It also gives insight into the demand for this most valuable report being placed into an “in-memory” application like Qlikview or other tools like MS PowerBI or Tableau. The following post describes a possible way to achieve Multi-level Order reporting in Qlikview or any other BI Reporting platform that is the flavour of the month. It also explains how the report can be snapshot based so that the users are able to see the changes that have happened to the production plan since the last report run.
Finally, the information generated provides inputs to many production planning transaction codes in SAP and hence the replication of this report can serve as a foundation for many other valuable reports in the production planning and manufacturing areas related to SAP.
The SAP Multi-level Order Report – What is it?
Within production planning, the Multi-level Order report does a collective availability check for all the components in an order rather than for an individual order component. The collective term is used as it is run against multiple orders at the same time rather than for a single order. The benefit of the SAP report is that results are displayed multi-level style in the same manner of an exploded Bill of Materials.
The report is the result of checking the production plan, a stock check for each required component and all replenishment events for those same components. The result is basically a list of errors that need to be resolved, Purchase Orders that need to be expedited and a reschedule of all relevant production orders and planned orders that make input components to fufill an order
The MD4C report is used by a few people in the production planning cycle, however the people that use the report use it extensively. To run the report against a large set of open orders takes a significant amount of processing power and there are common instances of this report taking more than an hour to run. The SAP layout is intuitive however as with most data heavy SAP outputs, the users just want the data in Excel so that they can perform filters, searches or other functions much easier.
Building this Report in a BI environment
This report can be built from either RAW underlying SAP tables or using a mixture of BAPI extracts alongside SAP tables. Using the BAPI’s extracts remove a bit of data modelling complexity however some system administrators will discourage the use of BAPI extracts so both options will be listed here. The BAPI’s can also take a while to run and can lead to a delay in refresh times.
This report can be replicated in any BI tool, we have found that Qlikview 12 has been the best in terms of performance and ability to make a flexible user output with variables and selection buttons that can manipulate filters and column displays. Microsoft PowerBI and Tableau has also produced decent results. Please see Appendix A (in the full version of this report) for a data flow / tool selection matrix.
Qlikview also has an SAP connector to extract the data from SAP, however other tools such as Knime, Lavastorm and Alteryx can be used to extract this information and perform the data transformation steps.
RAW SAP tables are often placed into a data staging area, however these are often refreshed on a daily basis and this can lead to the reporting becoming rapidly obsolete as Purchase Orders are raised, deliveries made and new planned orders being raised. A single planned order being created could in theory impact this entire report.
Source Tables Required
The below tables are the main tables required from SAP. There also supplemental tables which are more static in nature and are needed once off (such as T001L, Exception message descriptions etc). The supplemental tables are not listed here.
|MARC||Plant Specific Material Data||Obtain procurement types, lead times etc|
|MAKT||Material Descriptions||Obtain test for materials (for display)|
|MARD||Storage Location Data for Materials||Obtain current stock on hand|
|AFKO||Order Header Data||Obtain production order information (times, qty etc)|
|AFPO||Order Item Data||Obtain production order information (times, qty etc)|
|PLAF||Planned Orders||Obtain planned order information (times, qty etc)|
|EKKO||Purchase Order Header Data||Obtain vendors and PO status|
|EKPO||Purchase Order Item Data||Obtain PO line item detail (material, qty, status)|
|EKET||Purchase Order Schedule Lines Data||Obtain schedules (open amount, delivery dates)|
|EKES||Purchase Order Confirmations||Supplemental information (confirmation dates, no’s)|
|VBBE||Sales Order Requirements||Provides a list of open sales orders|
|STKO||BoM Header||Bill of Materials Information|
|STPO||BoM Items||Bill of Materials Information|
|STAS||Item Selection Table||Bill of Materials Information|
|MAST||BoM Materials to BoM Link Table||Bill of Materials Information|
|RESB||Reservation Requirements Table||Obtain all open reservations and supplemental data|
|LFA1||Vendor A Segment||Obtain Vendor master data (names, addresses etc)|