How to update the primary key of any standard table in SAP with an example to update Customer Material Info records when substitute material is changed in Material determination?
Hello Everyone 🙂
We had a requirement to develop a functionality such that Whenever orders are manually entered, and the customer material is required to appear on the sales order, CMIR and MD (when working together) does not always retrieve the customer material properly.
So we need to make an Enhancement of VB12, VD52 so that whenever the substitute material in material determination(VB11/VB12) is changed, the corresponding material in the CMIR record (VD52) is also changed automatically. This will ensure that whenever a material is entered on the sales order, the link to the customer material is maintained.
All the details entered in VD52 create a Customer Info record and we can see the entries in KNMT table.
Understanding SAP behaviour : Normally when we create a CMIR record against a customer for the first time with the substitution material details the record is created successfully in KNMT table ( which stores the CMIR records) but when we change the details of substitution material in material determination the corresponding values in KNMT is not updated. We had a specific requirement to update those values.
Here the challenge what we would face is in updating the substitute material in KNMT since it is a primary key field.
We did some analysis and found that we cannot update a primary key of any record in SAP table. Finally we had come with an alternative, the only possible way to update primary key
Solution:
- Copy all the existing record which needs to be updated in an internal table.
- Delete the original records from KNMT table.
- Modify the internal table contents which we captured with new primary key.
- Insert the new records into KNMT table.
You all must be thinking we are done with the solution, Unfortunately there was no desired User Exit available to update the KNMT (CMIR ) table.We had searched for standard function modules to update and there was a single Function Module ‘RV_CUSTOMER_MATERIAL_UPDATE’ . Yes the function module was able to update the records what we had passed for DELETION in KNMT/INSERTION.
Problem: The challenge was updating more than one record at a time , system failed to update information. The records were partially updated or not.
Solution: We had debugged and found that system needed some time after a Database Commit. So we need to put an appropriate WAIT before it comes for processing the second record and after the FM updates the record put ‘COMMIT WORK and WAIT ‘ so that system get time to update and release the lock in KNMT entry.
Pseudocode :
- Find an enhancement point for material determination screen (VB11/VB12) , since this is the place where the substitute material is changed.
- Enhancement point : MV130F0Z- kondition_sichern_db
- Created two enhancement implementation one in the beginning of FORM and other in the end of FORM
- Find the key combination table for material determination for getting the vakey (KNUMH) to fetch the old material
- Check if the code is executed during ‘SAVE’
- Get the old material by passing KNUMH key in KONDD table
- 7. If the change in substitute material is for which combination and add the logic accordingly like SalesOrg/Material , Business Unit/Material etc.
- Store the details fetched to an Internal table
- In order to delete the records , we need to set the deletion indicator as ‘D’ in YKNMT which we will pass to the FM .
- Once the save in VB12 has made a system commit you will get the new KNUMH key
- Get the new substitute material from KONDD based on KNUMH key
- Modify the internal table contents copied earlier in 8 with new material number(primary key ).
- Call the FM and pass the data to YKNM for DELETION.
- Put a commit Work and Wait after DELETION.
- Call the FM and pass the data to YKNMY for INSERTION.
- Put a commit Work and Wait after INSERTION
- ** Add a command ‘ WAIT upto 5 seconds ‘ before processing the next record to buy some time to the system to update the value and release the lock
Program Logic :
** Enhancement Implementation 1 in the beginning of FORM
Declaration *
TYPES : BEGIN OF gty_knvv,
kunnr TYPE knvv–kunnr,
vkorg TYPE knvv–vkorg,
vtweg TYPE knvv–vtweg,
END OF gty_knvv.
TYPES : BEGIN OF gty_knmt,
vkorg TYPE knmt–vkorg,
kunnr TYPE knmt–kunnr,
matnr TYPE knmt–matnr,
END OF gty_knmt.
TYPES : BEGIN OF gty_bunit,
vkorg TYPE zal_bunit–vkorg,
vtweg TYPE zal_bunit–vtweg,
zzbusunit TYPE zal_bunit–zzbusunit,
oldmat TYPE kondd–smatn,
END OF gty_bunit.
TYPES : BEGIN OF gty_bunit_market,
vkorg TYPE zal_bunit–vkorg,
vtweg TYPE zal_bunit–vtweg,
spart TYPE zal_bunit–spart,
zzbusunit TYPE zal_bunit–zzbusunit,
oldmat TYPE kondd–smatn,
END OF gty_bunit_market.
TYPES : BEGIN OF gty_tvkwz,
vkorg TYPE tvkwz–vkorg,
vtweg TYPE tvkwz–vtweg,
werks TYPE tvkwz–werks,
END OF gty_tvkwz.
TYPES : BEGIN OF gty_vkorg,
vkorg TYPE zgg_default_vals–fval,
END OF gty_vkorg.
DATA : gst_vkorg TYPE gty_vkorg,
gt_vkorg TYPE TABLE OF gty_vkorg.
** Start of EDVK908845
TYPES : BEGIN OF gty_kondd_old,
knumh TYPE kondd–knumh,
smatn TYPE kondd–smatn,
END OF gty_kondd_old.
Data : gt_kondd_old TYPE TABLE OF gty_kondd_old,
gst_kondd_old TYPE gty_kondd_old.
** End of EDVK908845
DATA : tcatalog_tab LIKE tcatalog OCCURS 10 WITH HEADER LINE,
l_oldmatnr TYPE mara–matnr,
l_newmatnr TYPE mara–matnr .
DATA : xknmt LIKE vknmt OCCURS 0 WITH HEADER LINE,
xknmt1 LIKE vknmt OCCURS 0 WITH HEADER LINE .
DATA : yknmt LIKE vknmt OCCURS 0 WITH HEADER LINE,
yknmt1 LIKE vknmt OCCURS 0 WITH HEADER LINE.
DATA : gt_knmt TYPE TABLE OF knmt,
gt_knmt1 TYPE TABLE OF knmt,
gst_knmt1 TYPE knmt,
gst_knmt TYPE knmt.
TYPES : BEGIN OF gty_xvake,
knumh TYPE vake–knumh,
updkz TYPE updkz,
END OF gty_xvake.
** Start of EDVK908845
DATA : gt_xvake TYPE TABLE OF gty_xvake,
gst_xvake TYPE gty_xvake.
** End of EDVK908845
DATA : gst_knmt2 TYPE gty_knmt,
gt_knmt2 TYPE TABLE OF gty_knmt.
DATA : gt_knvv TYPE TABLE OF gty_knvv,
gst_knvv TYPE gty_knvv.
DATA : l_knumh TYPE kondd–knumh.
DATA : l_vkorg TYPE vbak–vkorg.
DATA : l_kunnr TYPE kna1–kunnr.
DATA : l_oldmat TYPE kondd–smatn,
l_newmat TYPE kondd–smatn.
DATA : gt_tvkwz TYPE TABLE OF gty_tvkwz,
gst_tvkwz TYPE gty_tvkwz.
DATA : l_busunit TYPE zal_bunit–zzbusunit,
gt_bunit TYPE TABLE OF gty_bunit,
gst_bunit TYPE gty_bunit,
gt_bunit_market TYPE TABLE OF gty_bunit_market,
gst_bunit_market TYPE gty_bunit_market.
DATA : l_werks TYPE tvkwz–werks,
l_vtweg TYPE zal_bunit–vtweg,
l_market TYPE zal_bunit–spart.
BREAK georgej.
* Constants
CONSTANTS: lc_sapmv130 TYPE program_id VALUE ‘SAPMV130’,
lc_funcname TYPE rs38l–name VALUE ‘RV_CUSTOMER_MATERIAL_UPDATE’.
REFRESH : xknmt , yknmt .
* Check if the code is executed during ‘SAVE’ and output determination type is ‘Z001’
IF fcode = ‘SICH’ AND xvake–kschl = ‘Z001’.
* getting the Knumh key
** Start of EDVK908845
** Getting the entries for retriving old material
LOOP AT xvake.
IF xvake–updkz = ‘U’.
gst_xvake–knumh = xvake–knumh.
APPEND gst_xvake to gt_xvake.
CLEAR gst_xvake.
endif.
ENDLOOP.
* Getting the old material by passing knumh key in kondd table
IF NOT gt_xvake[] IS INITIAL.
SELECT knumh
smatn
FROM kondd
INTO TABLE gt_kondd_old
FOR ALL ENTRIES IN gt_xvake
WHERE knumh EQ gt_xvake–knumh.
** End of EDVK908845
* If the change in substitute material is for Business Unit / Material entered
IF vake–kotabnr = ‘507’.
CLEAR : l_busunit ,l_knumh.
* Getting the Business Unit
l_busunit = xvake–vakey(5).
IF l_busunit IS NOT INITIAL.
SELECT vkorg
vtweg
zzbusunit
FROM zal_bunit INTO TABLE gt_bunit
WHERE zzbusunit EQ l_busunit.
IF gt_bunit[] IS NOT INITIAL.
* Picking the existing entries from KNMT based on old material
SELECT *
FROM knmt INTO TABLE gt_knmt
FOR ALL ENTRIES IN gt_bunit
WHERE vkorg = gt_bunit–vkorg
AND vtweg = gt_bunit–vtweg.
* AND matnr = l_oldmatnr.
ENDIF.
**start of EDVK908845
* deleting the irrelevant entries based on Sales org
LOOP AT gt_knmt INTO gst_knmt.
CLEAR gst_kondd_old.
READ TABLE gt_kondd_old INTO gst_kondd_old WITH key smatn = gst_knmt–matnr.
IF sy–subrc NE 0.
DELETE gt_knmt.
ENDIF.
CLEAR gst_knmt.
ENDLOOP.
ENDIF.
** End of EDVK908845
* If the change in substitute material is for Plant / Material entered
ELSEIF vake–kotabnr = ‘502’.
CLEAR : l_werks , l_knumh.
* getting the plant value
l_werks = xvake–vakey(4).
IF l_werks IS NOT INITIAL.
SELECT vkorg
vtweg
werks
FROM tvkwz INTO TABLE gt_tvkwz
WHERE werks EQ l_werks.
IF gt_tvkwz[] IS NOT INITIAL.
SELECT *
FROM knmt INTO TABLE gt_knmt
FOR ALL ENTRIES IN gt_tvkwz
WHERE vkorg = gt_tvkwz–vkorg
AND vtweg = gt_tvkwz–vtweg.
ENDIF.
**start of EDVK908845
*
LOOP AT gt_knmt INTO gst_knmt.
CLEAR gst_kondd_old.
READ TABLE gt_kondd_old INTO gst_kondd_old WITH key smatn = gst_knmt–matnr.
IF sy–subrc NE 0.
DELETE gt_knmt .
ENDIF.
CLEAR gst_knmt.
ENDLOOP.
ENDIF.
** end of EDVK908845
* Salesorg/Material
ELSEIF vake–kotabnr = ‘503’.
l_vkorg = vake–vakey(4).
l_kunnr = vake–vakey+4(10).
* Picking the existing entries from KNMT based on old material
** Start of EDVK908845
SELECT *
FROM knmt INTO TABLE gt_knmt
FOR ALL ENTRIES IN gt_kondd_old
WHERE vkorg = l_vkorg
AND kunnr = l_kunnr
AND matnr = gt_kondd_old–smatn.
*** End of EDVK908845
* Bunit/Salesirg/Market
ELSEIF vake–kotabnr = ‘508’.
CLEAR : l_busunit , l_vkorg , l_vtweg , l_market.
l_busunit = xvake–vakey(5).
l_vkorg = xvake–vakey+5(4).
l_vtweg = xvake–vakey+9(2).
l_market = xvake–vakey+11(2).
SELECT vkorg
vtweg
spart
zzbusunit
FROM zal_bunit INTO TABLE gt_bunit_market
WHERE vkorg EQ l_vkorg
AND vtweg EQ l_vtweg
AND spart EQ l_market
AND zzbusunit EQ l_busunit.
* Picking the existing entries from KNMT based on old material
IF gt_bunit_market[] IS NOT INITIAL.
SELECT *
FROM knmt INTO TABLE gt_knmt
FOR ALL ENTRIES IN gt_bunit_market
WHERE vkorg = gt_bunit_market–vkorg
AND vtweg = gt_bunit_market–vtweg.
” AND kunnr = l_kunnr
” AND matnr = l_oldmatnr.
ENDIF.
** Start of EDVK908845
loop at gt_knmt INTO gst_knmt.
CLEAR gst_kondd_old.
READ TABLE gt_kondd_old INTO gst_kondd_old WITH key smatn = gst_knmt–matnr.
IF sy–subrc NE 0.
DELETE gt_knmt.
ENDIF.
CLEAR gst_knmt.
ENDLOOP.
** End of EDVK908845
* Sales org and material
ELSEIF vake–kotabnr = ‘504’.
CLEAR l_vkorg.
l_vkorg = vake–vakey(4).
* Picking the existing entries from KNMT based on old material
** Start of EDVK908845
** Getting the old material number based in KNUMH key
SELECT *
FROM knmt INTO TABLE gt_knmt
FOR ALL ENTRIES IN gt_kondd_old
WHERE vkorg = l_vkorg
AND matnr = gt_kondd_old–smatn.
** End of EDVK908845
ENDIF.
ENDIF.
* Copying all the old entries to an internal table later to modify
gt_knmt1[] = gt_knmt[].
LOOP at gt_knmt1 INTO gst_knmt1.
gst_vkorg = gst_knmt1–vkorg.
APPEND gst_vkorg to gt_vkorg.
CLEAR gst_knmt1.
ENDLOOP.
* Set the deletion indicator as D in YKNMT to delete the records
LOOP AT gt_knmt INTO gst_knmt.
yknmt–vkorg = gst_knmt–vkorg.
yknmt–vtweg = gst_knmt–vtweg.
yknmt–kunnr = gst_knmt–kunnr.
yknmt–matnr = gst_knmt–matnr.
yknmt–kdmat = gst_knmt–kdmat.
yknmt–updkz = ‘D’.
** YKNMT-KZTLF = P_KZTLF.
** YKNMT-ANTLF = P_ANTLF.
APPEND yknmt.
CLEAR gst_knmt.
ENDLOOP.
ENDIF.
ENDENHANCEMENT.
*$*$-End: (1)
****************************************************************************************************************************
** Enhancement Implementation 2 in the end of FORM
DATA : gt_default_vals TYPE TABLE OF zgg_default_vals,
gst_default_vals TYPE zgg_default_vals.
** start of EDVK908845
TYPES : BEGIN OF gty_kondd_new,
knumh TYPE kondd–knumh,
smatn TYPE kondd–smatn,
END OF gty_kondd_new.
DATA : gt_kondd_new TYPE TABLE OF gty_kondd_new,
gst_kondd_new TYPE gty_kondd_new.
** End of EDVK908845
TYPES : BEGIN OF gty_mara,
matnr TYPE mara–matnr,
meins TYPE mara–meins,
END OF gty_mara.
DATA : gt_mara TYPE TABLE OF gty_mara,
gst_mara TYPE gty_mara.
data : l_tabix1 TYPE sy–tabix.
REFRESH : gt_xvake.
IF fcode = ‘SICH’ AND xvake–kschl = ‘Z001’.
*Start of 001
Wait up to 5 seconds.
*End of 001
* getting the defauklt values maintained in ZGG_DEFAULT table so as to restrict
* to favourable markets.
SELECT * FROM zgg_default_vals
INTO TABLE gt_default_vals
FOR ALL ENTRIES IN gt_vkorg
WHERE pname = sy–cprog
AND fname = ‘VKORG’
and fval = gt_vkorg–vkorg.
IF sy–subrc EQ 0.
* Knumh key is updated
** Start of EDVK908845
** Getting the material based on KNUMH key
loop at xvake .
if xvake–updkz = ‘U’.
gst_xvake–knumh = xvake–knumh.
APPEND gst_xvake to gt_xvake.
CLEAR gst_xvake.
ENDIF.
ENDLOOP.
if NOT GT_xvake[] is INITIAL.
*Getting the new material number based on new KNUMH key
SELECT knumh
smatn FROM kondd
INTO TABLE gt_kondd_new FOR ALL ENTRIES IN GT_xvake “(l_knumh, l_newmatnr)
WHERE knumh EQ GT_xvake–knumh. “l_knumh.
* Table 2 modified with nwew entries ready for insertion
LOOP AT gt_knmt1 INTO gst_knmt1.
CLEAR gst_kondd_old.
** Reading the old material table inorder to miodify the table with new material number
READ TABLE gt_kondd_old INTO gst_kondd_old WITH key smatn = gst_knmt1–matnr.
IF sy–subrc EQ 0.
CLEAR gst_kondd_new.
READ TABLE gt_kondd_new INto gst_kondd_new WITH key knumh = gst_kondd_old–knumh. “INDEX sy-tabix.
IF sy–subrc EQ 0.
gst_knmt1–matnr = gst_kondd_new–smatn.
MODIFY gt_knmt1 FROM gst_knmt1 TRANSPORTING matnr.
ENDIF.
ENDIF.
CLEAR gst_knmt1.
ENDLOOP.
** End of EDVK908845
* Add the entries in XKNMt and set the update indiactor as’I’ to insert the records
LOOP AT gt_knmt1 INTO gst_knmt1.
xknmt–vkorg = gst_knmt1–vkorg.
xknmt–vtweg = gst_knmt1–vtweg.
xknmt–kunnr = gst_knmt1–kunnr.
xknmt–matnr = gst_knmt1–matnr.
xknmt–erdat = sy–datum. ” passing the current date in the new entry Added in 001
xknmt–ernam = sy–uname. ” pasing the user name in the entry Added in 001
xknmt–kdmat = gst_knmt1–kdmat.
* xknmt-meins = gst_knmt1-meins.” passing the unit of measure
xknmt–updkz = ‘I’.
* XKNMT-KZTLF = P_KZTLF.
* XKNMT-ANTLF = P_ANTLF.
APPEND xknmt.
CLEAR gst_knmt1.
ENDLOOP.
** Start of 002
** Getting the unit of measure from master data
SELECT matnr
meins
FROM mara INTO TABLE gt_mara
FOR ALL ENTRIES IN xknmt
WHERE matnr eq xknmt–matnr.
** Modifying the internal XKNMT contenst which will update KNMT table
LOOP AT xknmt.
l_tabix1 = sy–tabix.
CLEAR gst_mara.
READ TABLE gt_mara INTO gst_mara WITH key matnr = xknmt–matnr.
if sy–subrc eq 0.
xknmt–meins = gst_mara–meins.” passing the unit of measure
MODIFY xknmt INDEX l_tabix1 TRANSPORTING meins.
endif.
CLEAR l_tabix1.
ENDLOOP.
** End of 002
* Picking the entries from KNMt so as to check if any similar records exist.
SELECT vkorg
kunnr
matnr
FROM knmt INTO TABLE gt_knmt2
FOR ALL ENTRIES IN xknmt
WHERE vkorg EQ xknmt–vkorg
AND kunnr EQ xknmt–kunnr
AND matnr EQ xknmt–matnr.
* looping to delete the entries of other market
LOOP AT xknmt.
READ TABLE gt_default_vals INTO gst_default_vals WITH KEY fval = xknmt–vkorg.
IF sy–subrc NE 0.
DELETE TABLE xknmt.
CLEAR gst_default_vals.
ENDIF.
ENDLOOP.
* Looping in XKNMT to Insert the record if it is unique
* YKNMT is for the records to be deleted from KNMT table
* Does the function exist and is it currently active?
CALL FUNCTION ‘Z_BA_FUNCTION_IS_ACTIVE’
EXPORTING
i_funcname = lc_funcname
i_mainprog = lc_sapmv130
EXCEPTIONS
ex_function_non_existant = 1
ex_function_not_active = 2
OTHERS = 3.
IF sy–subrc IS INITIAL.
**** FM to update KNMT table.
CALL FUNCTION lc_funcname
TABLES
xknmt_tab = xknmt1
yknmt_tab = yknmt
tcatalog_tab = tcatalog_tab
.
** The program level commit is already executed by system and we are adding additional commit
COMMIT WORK AND WAIT.
* WAIT UP TO 10 SECONDS.
CALL FUNCTION lc_funcname “iN UPDATE TASK “
TABLES
xknmt_tab = xknmt
yknmt_tab = yknmt1
tcatalog_tab = tcatalog_tab
.
COMMIT WORK and WAIT.
ENDIF.
Process and Output :
1. Material determination Screen : VB12
2.Enter the value for a material determination 
3. Choose the desired combination and add the substitute material details for the material
Save it.
4. Now Create a customer material information record for the first time using VD52 which will be updated in KNMT table
These records are saved and update in KNMT table.
5. We will try to see the entry for the substitute material maintained in step 3 for material ‘376631482’
6. As per the requirement we will change the substitute material in material determination which has to delete the record with old material number from KNMT
and update the new primary value
Change the material from ‘376631482’ to ‘A7388EU40’ in VD52.
Save it.
7. Check for old CMIR record for material : ‘376631482’ in KNMT table.
8. Let’s check for the new material which we added is updated in KNMT or not.
Yes , we have updated the CMIR record successfully. 🙂