Normal Business Scenario:
In an ERP Model, Production Planner need to keep a tab on the amount of material available as and when required for smooth functioning of the production cycle. SAP provides MRP run to assist Production Planner with determination of material shortage.
A shortage We can see the Plant stock: 10003318.20 (This amount still has the in-transit amount of 5232) Total Plant Stock: 9998086.20 exists for a material if the material is subject to demand-driven planning and if the total quantity of material requirements (sales orders, stock transfer requirements, production requirements, and forecast requirements) up to a certain point in time exceeds the total quantity of material receipts (inventory, production orders, purchase orders, delivery schedule lines, firmed planned orders, and firmed purchase requisitions).
In HANA DB SAP optimized the MRP Run and provide us MRP Live. MRP Live uses a single AMDP Procedure to calculate shortage, create planned order and requisition number.
Ref: SAP Note 2268085 for further advantages of MRP live over classical MRP.
Issue faces while using MRP live:
There was a scenario where we had to consider all the materials those are in transit. The issue with this in-transit materials is that unless the receiver provides an acknowledgement the amount is not deducted from the plant stock. So, this in-transit amount which is no more in the plant physically continues to feature in the plant stock and hence the planning returns incorrect data.
Solution provide by SAP is AMDP BADi implementation
PPH_MRP_RUN_BADI =>MDPS_ADJUST was implemented using an AMDP Class. We used the table EBEW (Sales Order Stock Valuation) to get the in-transit stock and planned to deduct the same from the Plant stock which was available in the changing parameter of the above BADi implementation method
But the main hurdle while implementing the changes was inside AMDP there is no scope for ABAP coding. So, reading, looping, work areas are few of the many options that are not available. You need to use SQL script to handle the same. Unless you are a sql script developer that is a difficult proposition. So we tried to leverage the native SQL script SELECT statement and its new features as a work around for the sql script loop /read.
1. In MD04 t-code:
We can see the Plant stock: 10003318.20 (This amount still has the in-transit amount of 5232) Total Plant Stock: 9998086.20
We can get the In-transit amount from EBEW table:
2. We create one custom table: With material plant combination we will have the plant stock amount calculated (logic used in the calculation explained in the next step) in the field LABST.
3. The custom table gets populated in another enhancement which gets called before the ABAP Managed Db procedure is triggered. The enhancement is in the class-method CL_PPS4_MRP_DISPATCHER->MAIN. Here we select the plant stock and deduct the in-transit stock fetched from EBEW and store the same info in the table against the material plant combination. To avoid unnecessary data in the table we clear the table after AMDP is completely executed. For that we can use the post implicit enhancement of the MAIN method. So, we have a custom table where we have the calculated value that needs to be used while creating Planning order/ requisition number etc.
4. In the AMDP BADi Class method: The trick lies in the Select statement where we have use case syntax in the select statement and joined the existing CT_MDPS internal table with the custom database table that we populated in the previous step. This select will replace the Internal table record corresponding to type WB i.e. Plant Stock with the value that we calculated in the custom table. Incase nothing is found in the custom table then the value in the internal table won’t be replaced.
Basically, a series of if-else, loop and read statement can do the same stuff using the sql script. However, using the new Select seems to be a better and easier option.
ct_mdps = Select a.the_index , a.matnr , a.werks , a.berid , a.berty , a.plaab , a.planr , a.dat00 , a.delkz, a.vrfkz , a.plumi ,
case when a.delkz = ‘WB’ AND b.labst <> 0 then b.labst else a.mng01 end as mng01,
a.mng02 , a.mng03 , a.dat01 , a.dat02 , a.dat03 , a.webaz , a.fix01 a.fix02 , a.baart , a.beskz , a.sobes , a.wrk01 , a.wrk02 , a.lgort , a.delnr , a.delps , a.delet , a.vpzuo , a.zuvkz , a.vervp , a.rsnum , a.sernr , a.paltr , a.techs , a.verid , a.knttp , a.kzvbr , a.sobkz , a.kdauf , a.kdpos , a.pspel , a.cuobj , a.aufnr , a.verto , a.qunum , a.qupos , a.lifnr , a.ekorg , a.ebeln , a.ebelp , a.glmng , a.arsnr , a.arsps , a.kapfx , a.monkz , a.vrpla , a.pbdnr , a.kzbws , a.mdmng , a.wamng , a.edgno , a.ematn , a.dbskz , a.stlty , a.stlnr , a.stlkn , a.stpoz , a.plifz , a.reslo , a.prio_urg, a.prio_req , a.sgt_scat , a.sgt_rcat , a.mdbs_amng01, a.xt_fix_dyn from :ct_mdps as a
left OUTER JOIN zpp_ebew_sum as b on a.matnr = b.matnr AND a.werks = b.werks ;
Other possible alternative which developer might think was available:
a> Find and use a standard table directly instead of calculating the value in a custom table. We avoided this approach because in all standard transparent table or CDS view or ABAP view used in the SQL select was returning incorrect data in the quantity field. ABAP query on the other hand was working fine. We didn’t find this issue with the custom table, hence we used custom table.
b> Memory ID Import export needs SQL Script Query which we were trying to avoid in the first place.
c> To avoid creating a custom table in step 2 and populating the custom table in step 3 as we have done here, try using HANA proxy table instead of using ECC table EBEW inside the AMDP BADI. You can get the proxy table name from the Menu path Extras -> Proxy object in the SE11 t-code.