Include Batch expiration in Material Requirement Planning

Why does Batch Expiry critical in MRP?

Life science (Pharmaceutical/Food/Chemical) products always have limited shelf life and also have limited environment exposure life. Product has to be manufactured and sold/consumed before expiry of its shelf life. I have been into SAP production planning and supply chain consulting practice for the last 20 years. This has been a pain area since decades for planning and manufacturing business function. This is important to find out a systematic solution to account batch level expiry of components in enterprise level material requirement planning in order to streamline material availability on time manufacturing lines and minimize waste due to expiry. This blog will help the SAP community to solve the long pending problem of  non-accountability of  batches with expiry dates in Net requirement calculation of SAP-MRP.

SAP’s MRP engine is the most important and complex component in SAP’s production planning module. Also this would help SAP developers and consultants simplified framework to enhance  MRP program to suffice client’s need.

Before going into further technical details of the blog, let us understand few key elements of planning.

What is SAP’s MRP engine?

The SAP MRP engine (Material Requirement Planning) is used to plan procure or produce the required material quantities on time for in-house purposes or for fulfilling customer demands or forecasted volume. In manufacturing, the function of MRP is to guarantee material availability on time. MRP engine plans the supply based on requirements and considering the current stock in hand and meets the shortages.

What is Time phased Net requirement Calculation?

In simple terms, we add up the orders scheduled to arrive and the material we have in store and then subtract it from the gross requirements. After netting, this value is the total amount of materials we need for the next production cycle. Again, a simple net requirements formula can explain that.

Time Phased Net requirements= Time phased Gross requirements- (scheduled receipts+ On-hand inventory)

We calculate this value meticulously by looking into the scheduled receipts and on-hand stock over the planning horizon.

What are Shelf Life and Expiry date?

As per 21 CFR Part 211 – Good Manufacturing Practice for Finished Pharmaceuticals established requirements concerning the expiration date on a drug product and stability testing to assure the appropriateness of that date. Each chemical or drug product or APIs(Active pharmaceutical)  chemical and physical property retains its permissible characteristics up to a finite number of days which is determined empirically basis on clinical and laboratory experiments and trials.

Expiry Date  = Date of Manufacturing + Shelf Life ( usually maintained in number days)

What is the use case?

Standard SAP – MRP engine performs net requirement calculation by comparing all issue and receipt MRP elements within a plant inside the predefined planning horizon and specified MRP configuration parameters. MRP takes all types of stocks and incoming receipts such as Purchase orders, production orders, Purchase Requisitions (within planning fence), and planned orders (within planning time fence) as firm receipts. However, standard MRP programs don’t take material stock at batch level granularity, hence batch level stock parameters ( Expiry Date, Used by Date, Shelf life, etc.) always stay outside MRP calculation. This resulted into manufacturing interruption due to material shortage despite running MRP planning on a regular basis.

Batch expiry plays an important role in precise material requirement calculation along the time horizon. This becomes very critical for the Chemical and pharmaceutical industry where material replenishment lead time is high and shelf life falls within the planning horizon. MRP is unable to meet the intended purpose of material requirement planning to ensure “guaranteed material availability on time”

This user requirement was raised by a global leading pharmaceutical company during their implementation of SAP as a global manufacturing and sourcing system. The planning team was looking forward to third party bolt-on solution to solve batch expiration in MRP. We had taken up this use case and did MRP enhancement to consider material stocks in a material, plant, and batch level granularity in MRP calculation. Developed a robust solution by considering stock at batches level till its future expiry date comes in net requirement calculation logic of standard MRP program. Even though the stock is available in standard SAP irrespective of its expiry. however, our plugin solution will keep the material batch stock aside beyond its expiry and create production or procurement proposals in order to ensure material guaranteed material availability.

Additionally, this solution brought visibility of batch expiry in the stock requirement list (MD04), MRP List(MD05), and other relevant reports as well.

This solution saved $25 Million USD per year per plant. Also, this SAP enhancement has been included in the global rollout template.

Please refer to this Diagram to see the MRP Calculation logic

Fig. 1.0 -Time phased available stock for MRP

Please refer to this diagram (Fig. 1.0) to understand the standard process of determining time-phased available stock as outlined above, which is to subtract expired date stock on the expiry date and on-hand inventory from the gross requirements.

Fig. 2.0 -Time phased Net Requirement Calculation and Standard MRP results

Fig. 3.0 – Enhanced Time phased Net Requirement Calculation and MRP results

Fig. 3.0 – Enhanced Time phased Net Requirement Calculation and MRP results

What are the functional requirements to set the context of this blog ?

• All batches beyond their future expiry date should not be considered in planning as stock.
• SAP MRP program should recognize the batch expiration date / used-by date and decrement the availability of stock from Net-requirement calculation such that a planned order / purchase requisition could be generated by MRP run for a requirement for unfulfilled requirement.
• Then Subtract the calculated quantity  left over on or after future expiry date should be excluded from the net available stock in  MRP calculation.  however batches may  exist in the system and until those batches have been  written off.
• The batch numbers of the batches and their quantities must be displayed in the batch line (MRP Element) on the MRP Results/Evaluation Screen.
•  Enhance all stock requirement list and MRP list to show batch level stock and line for future dated expired batch as time series.
• Exclude the materials from this enhancement if the material is not batch mangled or not relevant for expiry.
•  Include only the batches with expiry date fall within the number of days maintained in the material master record field “Maximum Storage Period” from current date.

SAP admits that it’s does not consider stock at batch  granularity while pegging the requirements with supply/receipt elements. Standard SAP considers the expired batch as surplus quantities that are to be disposed of as waste and cannot be pegged to any requirement falling after to be expired batches. The purpose of this enhancement is that MRP planning program should dynamically take balance stock of the expired batches out of time phased Net requirement calculation, if those have not been pegged with any requirement before future expiry date. Arrange all issue and receipt elements in time series and expiring the expiring batch quantity of a batch (for all batches in general) that will expire on the expiration date, by taking into consideration consumptions of the same material before its expiration.

Technical flow

Fig. 4.0 – Technical Pseudo code flow

BADI / Enhancement Spot

Definition: MD_ADD_ELEMENTS

Implementation: ZPP_MRPMD_ADD_ELEMENTS

Method: ADD_CHANGE_ELEMENTS

Class: ZCL_IM_PP_MRPMD_ADD_ELEMENTS

Definition: MD_ADD_COL_EZPS

Implementation: ZPP_MRPMD_ADD_COL_EZPS

Method: ACTIVATE_ADD_COLUMNS, FILL_ADD_COLUMNS.

Class: ZCL_IM_PP_MRPMD_ADD_COL_EZPS

Enhancement Spot: ZPP_MRPELE_DT_FILL.

 Pseudo code

• The main transactions – MD01, MD02, MD03, MD04, MD05, MD06, MD07, MD41, MD42,MD43 MDBS, MSBT.

Enabling batch as MRP element.

• Implement and activate BADI MD_ADD_ELEMENTS.
• Add following logic in Method: ADD_CHANGE_ELEMENTS.
• Check material is batch managed or not in method import structure im_mt61d-xchpf = ‘X’.
• Fetch material type, plant and active flag from custom table ZPP_MRP_EXP_ACT by passing material type and plant and check active flag = ‘X’.
• If the above two statements are valid then fetch MATNR (Material Number), WERKS (Plant), CHARG (Batch) and CLABS (Quantity) from MCHB table for the input material and plant and store details in the internal table.
• Fetch MATNR, BATCH, VFDAT (Expiry date), FVDT6 (Use by Date) from MCH1 table for above selected materials and batches. Store the above details into a internal table T_STOCK.
• Sum the CLABS at MATNR, WERKS and CHARG level.
• Insert above expiry stock lines into Method output structure ch_copy_mdpsx.

ch_copy_mdpsx-plaab          = 2.  “Material requirements planning segment                                                                                             wa_ch_copy_mdpsx-vrfkz     = 'X'.   “Availability indicator                                                                         wa_ch_copy_mdpsx-delkz    = 'LC'.  “MRP element - Batch Stock                          wa_ch_copy_mdpsx-mng01 = CLABS (Quantity).                                       wa_ch_copy_mdpsx-plumi    = '-'. “Receipt/issue indicator                                             wa_ch_copy_mdpsx-baugr   = concatenate Batch and CLABS (Quantity) separated by ‘/’.

Populating Receipt/Requirements date.

Populate wa_ch_copy_mdpsx-dat00 with Use by date, If Use by date (FVDT6) is not initial or populate with Expiry date (VFDAT).
Export T_STOCK internal table details to ABAP Memory ID, These details will be used to fill added columns of MRP requirement list output screen.
Activate the method export flag indicating the MRP output structure changed. ch_changed = 'X'.

Adding and populating additional columns: to MRP output list .

• Implement and activate BADI MD_ADD_COL_EZPS

Adding additional columns to MRP output list.

• Add following logic in Method ACTIVATE_ADD_COLUMNS.
• Check material is batch managed or not in method import structure imt61d-xchpf = ‘X’.
• Fetch material type, plant and active flag from custom table ZPP_MRP_MAT_PLANT_ACTIVE by passing material type and plant and check active flag = ‘X’.
• If above two statements are valid then.
• Add two columns to the MRP output list. ez1_mode = ez2_mode = ‘1’.
• Add column headings to the added columns. button1_ez = ‘Expiry Date’ and button2_ez = ‘Use By Date’.

Populating additional columns to the MRP output list.

• Add following logic in Method d:
• Check material is batch managed or not in method import structure imt61d-xchpf = ‘X’.
• Fetch material type, plant and active flag from custom table ZPP_MRP_MAT_PLANT_ACTIVE by passing material type and plant and check active flag = ‘X’.
• If TCodes equals ‘MD05’ and ‘MD06’, the following logic will be executed
• Import ABAP Memory ID and store it in the internal table T_STOCK, Which is exported in BADI MD_ADD_ELEMENTS.
• Read batch number from internal table T_STOCK with current MRP line in the method export structure ie3mdpslx-baugr.
• Convert Expiry date (T_STOCK-VFDAT) and Use by date(T_STOCK-FVDT6) into external date format using function module ‘CONVERT_DATE_TO_EXTERNAL’ and store these values into method export fields emdezx_usex1 and emdezx_usex2.
• For TCodes other than MD04 and MD07, the following logic will be executed.
• Get batch number from the method import field ie3mdpslx-baugr. Use the function module ‘ISM_SPLIT_STRING’ to split the batch and quantity,
• Fetch expiry date (MCH1-VFDAT) and Use by date (MCH1-FVDT6) from table MCH1 by passing material number (imt61d-matnr) and Batch.
• Convert Expiry date (MCH1-VFDAT) and Use by date (MCH1-FVDT6) into external date format using function module ‘CONVERT_DATE_TO_EXTERNAL’ and store these values into method export fields emdezx_usex1 and emdezx_usex2.

Populating MRP Element Data field in Stock/Requirement List and MRP output list.

• Implement enhancement spot in the function module ‘EXTRAKT_AUS_MDPSX_BILDEN’ to populate MRP Element data files. Click Enhancement source code button and click on show implicit enhancement option.

• Right click on implicit Enhancement spot and click on create. Give Enhancement implementation name ZPP_MRPELE_DT_FILL.
• Check if current line is expiry stock line (‘LC’) and (TCODE = ‘MD04’ or ‘MD05’ or ‘MD06’ or ‘MD07’ or ‘MD41’ or ‘MD42’ or ‘MDBT’) and EMDPS-BAUGR (BATCH ‘/’ quantity value populated in BADI MD_ADD_ELEMENTS) is not initial.
• MOVE EMDPS-BAUGR TO iextra.

Creating Custom Table to restrict enhancement at Material type and Plant level.

• Create a custom maintainable table ZPP_MRP_EXP_ACT (Name will be changed based on Coding standards) with the following field – MTART, WERKS and ACTIVE_FLAG.
• Create a TCODE YPP01 (Name will be changed based on Coding standards) for maintaining table.

Adding a node in SPRO Screen to maintain the table ZPP_MRP_EXP_ACT.

• Create a node for table maintenance of ZPP_MRP_EXP_ACT in the following path of TCODE SPRO.
• Use TCODE SIMGH to create a node in SPRO->Production->Material Requirements Planning. Steps to create a node in SPRO explained in the attached document.

Post implementation Value proposition

1. This enhancement reduced the 80% material shortage related problem of API( Pharmaceutical active ingredient) manufacturing units.
2. Also help in reducing wastage of expensive API and other chemicals
3. This solution add  25 M USD worth of value in their major manufacturing facilities.

Transaction level Illustration

User can see demand and supply situation at batch level granularity where the system automatically consumes the batches and reduce the ‘net available material qty’ per requirement date from those batches whose expiration date is less than the requirement date.

        Batch expiry date and use by date are to be displayed in additional columns in the Stock requirements list (MD04, MD07) & MRP list (MD05, MD06).

Batch numbers of the expired batches and their quantities are to be displayed in the batch line (MRP Element) on the MRP Results/Evaluation Screen.

On an average typical global pharmaceutical/Chemical companies incur  6% to 10% waste due to expiry which is worth of few hundred million USD per year. This use case not only  helps to solve batch expiry issues in SAP and but also provide a comprehensive framework to enhance standard SAP’s MRP  functionality to suffice customized business needs.

Stay tuned, there will be more complex business problems to be uncovered in next upcoming blogs on Global inventory deployment and  SAP MII (SAP Manufacturing Integration and Intelligence).