Snippets

Jimbo's picture

Creating an ArrayList in VBA Throws Out Of Memory Error

Out of memoryA snippet of code that worked fine for years suddenly stopped working on a new laptop and began throwing "Out of memory" errors and then "Automation error" messages. The new computer has quadruple the memory that the old computer had, so that couldn't be the problem.

Jimbo's picture

Read Table Data from SAP into Excel Using RFCs

Data FlowThe ability to put into the hands of users an easy way to tease out of SAP data directly from tables has always been difficult in larger organizations because BASIS teams tend to disable SQ00 "because performance". Considering that larger organizations have dozens of front end servers and a query will consume a tiny percentage of one front end server's capacity for some seconds leads many to believe that the purpose of blocking this access is to eliminate support calls; eliminating a feature is easier than walking a user through creating a dataset and building a query.

In scenarios where the source tables are less than one million records long and the data can be knit together using vlookups, Excel can be used to tease data out of SAP using the RFC_READ_TABLE function. Where the data exceeds one million records, the preferred method is to use a similar function in Access and articles explaining that capacity are listed here.

Jimbo's picture

Create an Access Database File and Import a Text File to a Table Within Using VBA

Access' 2GB file size limit has been a thorn in the side of developers for years, but it is possible to circumvent. One option is to create link tables to external text files instead of importing them, but this snippet of code explains how to create a disposable Access database to hold the data from just one table.

Jimbo's picture

Convert RPT files into Excel or Text using VBA

On a large enough project, requests for extracts too large to handle by copy-pasting in the clipboard will arise and need to be exported to an RPT file, but users don't know how to handle RPT files and have a reasonable expectation that the data will arrive in an easy-to-use format like Excel.

Jimbo's picture

Know Which MRP Type Not To Use

Plant StockAccording to SAP's support portal, the "central role of MRP is to monitor stocks and in particular, to automatically create procurement proposals for purchasing and production (planned orders, purchase requisitions or delivery schedules)." This makes perfect sense, but doesn't explain when SAP will reject an MRP view by throwing a "Specify a valid MRP procedure" error.

Jimbo's picture

Convert Text to Sentence Case

Harmonizing data often includes transforming names and addresses to UPPER CASE, but some clients opt for Sentence Case wherein only the first letter of each word is capitalized. Translating everything to upper case is very easy and now Sentence Case can, too.

This snippet is written for the scripting language that comes with BODS. It is akin to Java, but with cumbersome spelled out begin and end brackets.

Jimbo's picture

Automatically Populate Select-Options In Selection Screen

This example comes from an LSMW object that facilitated the cleanup of duplicate Customer Masters from a client's production system by monitoring those blocked for sales, those that were hidden and those with a popup message instructing users to transact with the customer through the survivor Customer Master to see when all of the associated transactional documents had been settled.

Jimbo's picture

Call Transactions from LSMW by Clicking on Document Numbers in a Report

While meaningful reporting can be invaluable for high-level downstream reporting and granular upstream reporting, it is also useful for the data conversion specialist loading the data. A user can copy-paste a document number or a Master Data number from a report into other transactions to view them, but a faster and more accurate way to do the same thing is by letting the user click on the number and having LSMW open the transaction directly to the desired object.

Jimbo's picture

Send Popup Message to SAP Users with LSMW

Popup mountain goatsHidden deep in SAP is a way to send a popup messages to users who are logged on. This can be a convenient way to reach out to a user who has open an object that needs to be updated--especially if that user is in another country.

An easy way to send a popup message to another user is by launching the SE37 transaction and running the TH_POPUP function. An even easier way that does not require access to SE37 and lists the users who are currently logged on is by using an LSMW object as a user-friendly wrapper for the TH_POPUP function.

Jimbo's picture

How to Use a Drop Down Menu in LSMW

Drop down mountain goatsLSMW objects are usually written by the data migration specialists who will use them and do not require documentation as the programmer knows exactly what the software does. Often they are cobbled together in the 11th hour to triage issues discovered during a system test, UAT or even go-live with no time to include a helpful interface.

Sometimes, though, the tools are left behind to be used by professionals who are not programmers or database administrators and the interface can be used in lieu of training. This value-add makes an LSMW object more useful as anybody can use it instead of just the programmer who wrote it.

Mercifully, LSMW allows for the INITIALIZATION code to be executed in the GLOBAL_DATA section Field Mapping and Conversion Rules. That is were the parameter and the code to populate the drop down menu are added.

Jimbo's picture

Loop Through Source Fields to Populate Target Fields

loop goatWhen creating particularly-complex LSMW objects with dozens of fields in a source file, it can become very repetitive to validate every field if the requirement is to only populate fields with values when the field is populated with a value. This simple snippet of code loops through the fields in the source structure and moves the values in those fields to fields in the target structure where the field of the name is identical.

Jimbo's picture

Predict Tax Classification Layout on Master Data Screens

The ability to predict what fields will appear on what screens during batch input prevents the batch from failing when it stops to force the user to populate a mandatory field or errors out in background processing because an expected field isn't on the screen.

Jimbo's picture

Predict a Field Status While Preparing Batch Input

Fortune tellerMost fields in master data transactions can be set to suppressed, optional or required for the purpose of controlling the integrity and quality of the data as it is entered. During an SAP implementation, it is not uncommon for resources behind the scenes to discover the purpose of fields in master data and, with unfettered access to transactions like OMSG, OBD2 and OBD3, change the status of fields such that LSMW objects written for loading and updating master data are rendered useless.

A field that is suddenly mandatory on screen will cause batch entry to stop and wait for the field to be populated. When a batch tries to populate a field that has been suppressed, the system throws a warning instead of an error, but that warning requires the batch to be run in foreground or "display errors only", usually with the coffee cup method.

Knowing in advance whether a screen field will be suppressed or required allows for the field to be left blank or populated so that batch loads are not interrupted by warnings or errors. LSMW objects that can predict the presence or absence of a screen field do not have to be hard-coded for every possible contingency.

Jimbo's picture

Update Descriptions of G/L Accounts by Language with a Recording

Sleepy goatG/L Accounts are as mysterious to programmers as ABAP is to plumbers. They are used for some dark, esoteric purposes that only accountants can understand.

The requirement to update the description of G/L Accounts in only one language lead to the discovery that SAP automatically maintains the description in all the languages running in SAP at the time a G/L Account is created. The descriptions are stored in the SKAT table as a short (TXT20) and long (TXT50) text field.

Jimbo's picture

Validate Profit Centers Against Company Code

Loading Material Masters using Standard Batch/Direct Input can be a daunting task because of the hundreds of hierarchical dependencies required to create a single Material Master. Like so many other fields, it is easy enough to validate PRCTR before attempting to load.

In this case, the system threw the following error messages: No postings can be made to profit center 1501500 in company code 0130. and Profit center 1501500 cannot receive postings in company code 0130. Predicting this error prior to loading for go-live so that it can be resolved before the hectic weekend when it is least convenient will naturally make the programmer a hero.

Jimbo's picture

Catch Carriage Returns and Line Feeds in Source Data

One of the great features of Excel is the ability to add carriage returns to the contents of a cell in order to represent multiple lines of content in the cell. This can wreak havoc in source data as carriage returns and line feeds can be read in as ASCII or Unicode characters and then corrupt data in LSMW's proprietary table storage formats.

The "BDC_INSERT, Transaction code .. is invalid" message (number 00338) does not give much advice on how to resolve the problem except to "Contact the person in charge." The solution might be even simpler than that.

Jimbo's picture

Validate Valuation Class Based On Material Type

One of the most important things that a Data Migration team can do is produce validation reports that explain in great detail any issues that will prevent the loading of source data into an SAP system before trying to load it. This tremendous value-add is what separates great data migration resources from mediocre ones.

Even the most detailed army of pre-load validation routines can still miss small inconsistencies between systems due to customization and configuration. Dealing with disperate material types over a landscape of development, quality assurance and production system can still allow errors to sneak through--like inconsistencies between Material Type and Valuation Class.

Jimbo's picture

Assign a Standard Task to a Position in PPOME Using Functions

Hierarchical mountain goatsThe interface provided by SAP in the PFTC transaction provides almost nothing for creating recordings that can be used to load HR data in a batch. Until now, there has been very little documentation on how to access the HRP1000 and HPR1001 tables directly using off-the-shelf functions provided with SAP.

Jimbo's picture

Asign a User to a Position in PPOME Using Functions

Hierarchical mountain goatsThe interfaces provided by SAP in PPOM and PPOME provide almost nothing for creating recordings that can be used to load HR data in a batch. Until now, there has been very little documentation on how to access the HRP1000 and HPR1001 tables directly using off-the-shelf functions provided with SAP.

Jimbo's picture

Convert SAP Internal Dates to Date Format of Current User

Developing a recording-based LSMW object to be used by multiple resources during a multinational SAP implementation can be cumbersome because of user-specific date and decimal formats. If dates in the source data are expected in a certain format then ensuring that the user has the correct profile settings is enough to avoid errors caused by formatting issues.

Jimbo's picture

Convert normal dates into SAP dates using VBA

Mountain Goat CalendarWhen dealing with the underlying functionality of ABAP, there tends to be just one valid date format: YYYYMMDD. Slashes and dashes throw SAP's functions into conniptions and the many international formats only make things more confusing.

Converting regular date formats into YYYYMMDD in ABAP is easy enough, but handling it in VBA is even easier. This snippet also checks to see if there is a time embedded with the date separated by space and throws out the time.

Jimbo's picture

Writing and Reading Text to Local File on SAP Server

/image.php?image=misc/officegoateatingplant.jpg|http://i.imgur.com/aSE9zOt.jpg|https://s-media-cache-ak0.pinimg.com/236x/39/14/83/391483b27948d36fa0e150628f890467.jpgStoring information in a discreet file on the SAP server is a great way to remember parameters in a program without having to hard-code them in the declaration. Discreet files on the server is also a great way to store data for custom tables or for deltas, but it can cause some issues--more on that later.

A real world example

This code was used to produce a proprietary EDI file to be uploaded to an external travel agency's web portal on a daily basis. The discreet file on the server stored the records that had already been transmitted to the travel agency so that the daily report was reduced down to a delta.

Jimbo's picture

Send Email From SAP Using ABAP

Many times throughout a career in Legacy Data Migration, the convenience of automatically sending output to an email recipient will be strong enough to give it a try. Updating data or a log file on a network share automatically during an Access macro or LSMW conversion step is perfect for most data, but sometimes, email is the only solution.

Naturally, this code is couched in a form and included in Jimbo's LMSW Toolbox for convenience, but it requires a table to be passed in so this is also an explanation of how to pass a table into a form. For whatever reason, the tables declaration must be declared before declaring any using parameters.

Jimbo's picture

Make ABAP Launch Excel and Populate Worksheet with Internal Table

/image.php?image=launchexcel/listsave.png|/image.php?image=misc/stackgoat.jpg&width=300The ability to instantly view the contents of an internal table in Excel without having to pass it through the clipboard is a great time saver and a big hit with clients. Not only does it obviate the need to perform a cumbersome System→List→Save→Local File → Clipboard routine, but also the Columns to Text step in Excel with its own complex set of individual steps.

The XXL_FULL_API function has a demanding interface with eighteen parameters including six unique internal tables. All of these values must be aligned exactly or the function will throw an error.

Jimbo's picture

Import Unicode UTF-8 Text Directly into Access Using VBA

http://www.interestingfacts.org/wp-content/uploads/2008/08/goats.jpg|https://oediku.files.wordpress.com/2011/07/kambing-panjat-pohon-05.jpg|https://media-cdn.tripadvisor.com/media/photo-s/05/d0/f3/bb/marrakech-guided-day.jpg|http://photoworkshopadventures.com/blog/wp-content/uploads/2015/09/MJCX2470-Edit-Michael-Chinnici-150501.jpg|http://i3.mirror.co.uk/incoming/article6547896.ece/ALTERNATES/s615/PAY-The-goats-in-the-tree.jpg|http://media.mnn.com/assets/images/2015/03/GoatsInTress02-e-0302.jpg|https://media-cdn.tripadvisor.com/media/photo-s/05/d0/f3/bb/marrakech-guided-day.jpg|http://ww3.hdnux.com/photos/40/20/23/8455418/5/920x920.jpgThe power, convenience and autmation of MS Access makes it the perfect tool for manipulating data during a data migration project. Its native support makes it even more powerful when dealing with an international project.

Manually importing extracted data opens the door to errors, delays and failed projects. A common best practice is to eliminate manual steps whenever possible and including the import as a step in a macro eliminates the risk of that step being forgotten.

Jimbo's picture

Search for a value in a field in every table that the field appears.

searching mountain goatThe ability to find every occurrence of a master data object in every SAP table is a great way for programmers to track down how those are used, but there are many unique values in SAP besides KUNNR, LIFNR and MATNR. How convenient would it be to have a Where used button for any value in SAP that lists every occurrence in any table?

It's possible to accomplish this with just a short snippet of code and a little effort. Start by setting up some parameters in the GLOBAL_DATA section of a new LSMW object (or report in SE38). Putting the parameters in a block with some comments makes it prettier and easier to use.

Jimbo's picture

Export SAP internal table to pipe-delimited text

/image.php?image=misc/pipegoatbig.jpg|/image.php?image=misc/pipegoat.jpg|https://s-media-cache-ak0.pinimg.com/originals/9f/83/4a/9f834a995d8a7ae67986ac887c53112c.jpgIn the 21st century, it is still possible to come across the requirement to pass data in the form of pipe-delimited text. The pipe or ¦ character (ASCII code 166) is preferred by many programmers because it exists in all operating systems on any system and occurs very infrequently in data.

It is absolutely possible to export to pipe-delimited text from SAP and it is really simple. The simple solution is to concatenate all the fields into a string and then append the string to a table.

Jimbo's picture

How to circumvent SAP's maximum permitted uninterrupted runtime

fast mountain goatTrimming the fat off of code in order to make programs run faster isn't at the top of the list of priorities for many ABAP developers because modern computers are so fast. However, when running complex programs, even the leanest code can run afoul of SAP's maximum permitted uninterrupted runtime limitation.

Jimbo's picture

Use a pop-up to populate a mandatory parameter

Using parameters is a great way to get last-minute data that does not necessarily need to be in the source data. Sometimes, clients forget to populate the parameter or will populate it with invalid data.

List of vendors

A pop-up window is a great way to get the required value. The values presented to the client can be validated in advance based on any condition that can be represented in ABAP.

Jimbo's picture

Validate Storage Type Indicator for Stock Placement and Removal

When loading the Warehouse Management view the Storage Type Indicator must exist in the T305 table. The functional team can create it using the SPRO transaction (see image on the right).

Checking to ensure that the value is populated prevents a false-negative by not checking for a blank LGNUM value. A simple SELECT SINGLE operation determines if the value is present in the T305 table.

Jimbo's picture

Validate externally asigned numbers for master data.

When creating master data it is a common practice to externally number data coming from legacy systems before loading into SAP. To create master data with externally assigned numbers in SAP requires a number range with the capacity to be externally assigned.

The numbering ranges for Master Data are stored in the NRIV table. Additionally, a flag in the same table denotes whether or not the number may be externally assigned during creation instead of automatically assigned from within SAP.

Jimbo's picture

Determine the order of Sequences and Operations for BDCs

Handling a Routing through a recording is often the easiest way to touch fields in Sequences and Operations. The only problem is determining the order of Sequences and Operations on the selection screens. This task is especially difficult after change numbers have been used to modify the Routing; the tables PLKO, PLPO, PLAS and PLFL are very sensitive to change numbers and their alignment with what appears on the screen is easily upset.
Sequences on a Sequences selection screen

Jimbo's picture

Get all the Details of a Routing Group regardless of Change Requests

Routings are an odd object within SAP in that changes don't necessarily delete records from the tables. Removing an operation with a change number causes the operation to stop appearing after the effective date of the change number, but the record remains in the PLPO table.

Jimbo's picture

Validate Commodity Codes in LSMW Before Loading

Hierarchical mountain goatsThe task of validating data often falls on the shoulders of the Data Conversion team because it is the last step before loading the data. Validating data is hard, time-consuming work so the opportunity to pass the buck may be too great for some resources.

Another great tactic is to wait until the end of the business day to hand off the data to the migration team. This allows for the resource preparing the source data to leave and not be involved in the time-consuming task of validating the data.

Jimbo's picture

Validate Alternative Units of Measure in LSMW Before Loading

Too often data is loaded into an SAP system only to fail. Documenting these failed records tends to be more difficult and less efficient than predicting what records will fail and, with that information, enriching a report based on the source data. A validation report with this information allows the team that extracts the data to apply corrective transformations before the data is loaded into an SAP system.

Jimbo's picture

Import tables directly into Access from SAP using RFCs

Access is an invaluable tool for data migration, transformation and manipulation. By calling a series of queries through a macro it is possible to automate all of the transformation steps and reduce the risk of corruption caused by manual processing.

Jimbo's picture

Filter out the base Unit of Measure when loading material views.

The RMDATIND program is a great tool for loading Material Master data, but in some screens it rejects any Unit of Measure that is the same as the base Unit of Measure for said Material Master. Replacing that Unit of Measure with a blank value resolves this easily and can be done with this short snippet of code.

Jimbo's picture

Validate Characteristics in a Class

Classification data is often provided in a hierarchical structure with characteristic names and values linked to a combination of material number and classification. This hierarchical format is the same for anything that needs to be classified including materials and documents.

It is never convenient to discover during the loading phase that a characteristic doesn't exist in the system or isn't assigned to the classification. This causes the CL20 transaction to fail and the only way to know that it failed is to comb through the error log.

Jimbo's picture

Check date validity in LSMW

All to often dates in source data are in a format that is incompatible with a client's user settings or has been corrupted when handled in Excel. An easy way to ensure that the source date formats can be loaded into SAP is by ratifying data standards at the beginning of the project.

Pages