Skip to main content

Increase efficiency and speed of your sap support tasks with the help of Microsoft Excel and VBA

Background
SAP Tcode SM30, is a very widely used transaction. Many IMG activities are simply update of table views using SM30. For example MRP controller IMG activity is nothing but sm30 on sap table view V_T024D. Apart from IMG activities, most of the SAP installations use many custom tables which are maintained using SM30. usually, the volume of such IMG activities in a support environment is a few records. As mentioned earlier, the amount of  MRP controllers that needs to be created when a new plant is created is usually huge. Here comes the problem. Many medium sized companies use a couple of 100 mrp controllers. The same problem happens when the business wants to add a new MRP controller to all existing plants. This again is easily a couple of hundreds. Moreover MRP controller IMG activity is not a table control; it has to be added one by one. This trick explained in this blog is very useful and saves time if the number of records exceeds hundred. This is limited to inserting new records. Editing records using SM30 is out of scope of this article.

Concept
Speed is achieved using Excel and VBA. Apart from some minor differences, all SM30 transactions have a standard structure. If you look at the recording of any SM30 transaction, you will notice the following structure

The tcode and the table/viewname and  OKCODE for selection of update option comprises the fixed portion of the recording. This is same for any SM30 view or table or even IMG. I have not encountered any thing different than this.
                                              T SM30                                         
SAPMSVMA 0100 X                                                                                                                                     
                                              BDC_CURSOR
                                              BDC_OKCODE /00
                                              VIEWNAME
                                              VIMDYNFLDS-LTD_DTA_NO X
SAPMSVMA 0100 X                                                                                                                                     
                                              BDC_CURSOR
                                              BDC_OKCODE =UPD
                                              VIEWNAME
                                              VIMDYNFLDS-LTD_DTA_NO X


This is s followed by multiple occurrences of BDC screens for each record added. This is different for each table/view.

For MRP controller it looks like this

SAPL0PP3 0060 X                  
                                     BDC_OKCODE =NEWL
SAPL0PP3 0061 X                  
                                     BDC_OKCODE =BACK
             V_T024D-WERKS
             V_T024D-DISPO
             V_T024D-DSNAM


All that is required is a small VBA script which should get the fixed portion first and build the variable portion of the recording merging with the actual data.

How to do steps

  • We need a microsoft excel workbook with three sheets
    • Sheet1 - Recording. This sheet should contain the skeleton of the sm30 recording. basically fixed portion and the repeating portion
Fixed portion


Repeating portion


    • Sheet 2 - Structure. This should contain some details required to generate the BDC
      • Program name - get it from the sm30 recording
      • Table name - the table or view name
      • screen number - of the repeating section. in some cases there 2 screen numbers (example MRP controller)
      • table field names
    • Sheet 3 - Data. Should have the actual data for the Sm30
  • Enter the details in Sheet Structure, from the recording


  •  Structure sheet should specify the links to data column and the field name. The structure below shows that the data sheet contains Plant in column A MRP controller in column B and Name in Column C.It is assumed that the data sheet contains column header and so row 1 in data sheet is ignored. 

    • Tables / Views where sm30 uses table control, requires suffix (01) to the field name. For example Weighting groups uses table control. field names should be
      • A - GEWGR(01)
      • B - COUN2(01)
      • C - GWFAK(01)

  • Copy the data into the DATA sheet of this workbook and run macro Generate_bdc_session. This macro will create a new sheet "Session" with the BDC data for the transaction. Save this sheet as a text file and import into SHDB and process using option "display Errors".
  • Important thing to note here is that the data is not saved. After the process is complete you can check the created data and save if all ok.
I am happy to share this sheet along with macro - send a request in the comment. I am happy if you refer back this blog.



Comments

Post a Comment

Popular posts from this blog

Vendor Consignment Business Process

Wikipedia offers a very simple definition for consignment . It is a process of placing a thing in the hand of another, but retaining the ownership until the goods are sold or used. In SAP consignment business process can be implemented very easily using the following steps. Activate Consignment inforecords using tcode OMEV. This activates the consignment prices to be picked up using the consignment info records. configure a special procurement type using IMG->Production->MRP->Master Data->Define special procurement type. In OBYC configure the consignment payables account (transaction KON). Assign the special procurement type created in step 2 to all the materials that are to use the consignment process. This is done in the MRP2 view of the material master maintenance transaction Create Consignment info record using tcode ME11. Select info category as consignment. Create general data, Purchase Org data and conditions. Conditions allow you to create time dependent prices. Cre...
16 comments
Read more

Rounding off of Amount fields in SAP

I remember, when I learnt COBOL, our instructor used to give us problems to solve. One of the problems often repeated in all programming classes was to write the code for rounding off numbers, without using built in functions. In SAP it is a bit tricky, as SAP stores amout fields as currency types. These fields are stored internally with 2 decimals, irrespective of the currency used. It can be easily done using write statement if you want to round the amount field in the report. How to do it if you want to round it store it as a currency amount field itself. This is a problem in SAP query reports. In my case the user wanted to amount fields to appear rounded to nearest integer. SAP has some built-in-functions like ceil, floor but they can not be used as it is, due to the fact that the amount is stored internally with 2 decimals. Most of the currencies are with 2 decimals and it is still not a problem. This can be easily achieved by the following operation. A constant need to be defin...
2 comments
Read more

LSMW Tip

This tip is very useful for Master Data LSMWs that updates many fields. Usually it happens that during a specific project we realise a need to update specific fields. We design these LSMWs in such a way that these values supplied in the input file is updated in the master file. Later on we try to reuse the LSMW for updating a subset if these fields. If we leave the values of other fields blank in the input file, depending on how the LSMW conversion rules were designed, the the master data will be updated with initial values. Common practice in many organisations is to create a new LSMW for the new set of fields. This is not required if the LSMW uses Direct Input. Direct input uses a special character to denote no value. The default is "/". This allows us to reuse the LSMW. Reformat the input text file and fill fields that are not to be disturbed with this character. For example if the LSMW is for updating the following fields 1. storage bin 2. picking area 3. reord...
2 comments
Read more