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|| 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||||||| 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?

Jimbo's picture

Export SAP internal table to pipe-delimited text

/image.php?image=misc/pipegoatbig.jpg|/image.php?image=misc/pipegoat.jpg| 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

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).

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 table PLPO table.

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.

One way to get the contents of a table is to export it to a text file and then import it into Access. This doesn't work so well and tends to be a cumbersome manual process comprised of several step each prone to error.

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.

Jimbo's picture

Produce a reconciliation report for material movement including financials

Reconciling inventory loads can be a daunting task involving complex table joins, custom queries and massive Excel spreadsheets. Accounting professionals will seldom turn down enriched data that saves them hours of mind-numbing number crunching and this snippet generates a report with almost all the information that they need.

Jimbo's picture

Export SAP tables to tab-delimited UTF-8 text file with header row

It never hurts to take a snapshot of high-profile tables in the production system before making changes. This task can be a manual task that is performed each day or broken down into object-specific tasks that automatically export pertinent tables as part of the Convert Data step.

Jimbo's picture

Find Finished Goods without Batch Classification

A recent requirement called for a report that listed all finished goods (material type: FERT) without a batch classification assignment. Normally this would be a five-minute task in SQ00 to create a query, but linking the MARA table to the INOB table in a meaningful way is forbidden by SAP. By adding a few lines of code it is possible to produce the same report using LSMW.

Jimbo's picture

Ensure that user settings are aligned with source data

SAP expects date and number information to be formatted a certain way, but it varies based upon local standards and is configured for each client. When writing software and testing in a development system the software depends on the localized standards in the programmer's User Profile. The User Profile can be changed using transaction SU3 or by clicking through the menu with System → User Profile → Own Data.

Jimbo's picture

Determine tax jurisdictions for customers during conversion step

Pre-validating source data during the conversion step in LSMW is a great way to provide meaningful reports to the resources who are extracting and transforming data from legacy systems. Finding errors in the source data and correcting them before attempting to load ensures a smoother ETL process.

Jimbo's picture

Display remaining time during long-running conversions

When performing pre-validations on data and comparing it against existing data the job tends to run a long time. LSMW by default will indicate the progress of the conversion step after every 500 records, but it doesn't indicate how much longer the task will run. While unnecessary, it is a fun way to plan for coffee breaks.

Percent complete SAPGUI 7.10 and earlier
Percent complete SAPGUI 7.20 and after

Jimbo's picture

Remove special characters from strings in ABAP.

Data sometimes comes with special characters that are expressly forbidden by SAP. Some fields are very specific and will except a very small character set. Having a tool to remove those special characters from free-text can save a lot of headaches, but removing those same forbidden characters from fields that are not free-text, can cause more headaches.

Jimbo's picture

Determine what classification materials are assigned to.

When assigning classifications to materials it helps to check to see if the material is already assigned to a classification. In some cases materials can have just one type of classification assigned to it. For example, materials cannot have multiple batch classifications assigned to them.

Jimbo's picture

Predetermine the order of selectable views in MM02 transaction.

Making changes to materials using a recording can be difficult because of the Select Views screen that pops up providing a list of available views. SAP's method for determining the order of selectable views is difficult to understand even after hours spent examining all the tables involved. In the end, the simplest and therefore (according to Occam) best solution is to call SAP's functions for the purpose.

Jimbo's picture

Set a break point in LSMW to use SAP's debugger.

Sometimes problems in source code are too complicated to resolve without stepping through the code while it is executing. Adding a break point to the ABAP code in LSMW is easy and provides an invaluable way to debug complicated problems.

Jimbo's picture

Add an index to reports generated in LSMW

The Convert Data step in LSMW is a great place to validate data with bits of ABAP and to produce a report that enables those responsible for cleansing to easily find problems with the source data. Catching errors at this point and excluding erroneous records from the load is always preferable to letting bad data pass through the conversion process and then allowing it to fail during the load only to then use the error log as a reporting tool.

Jimbo's picture

Add parameters to an LSMW object and describe them.

Often the behaviour of an LSMW object needs to change quickly to fit requirements as a project progresses. Modifying the source code in a development system and then transporting the LMSW object to a quality or production system takes time. Writing multiple LSMW objects for slightly different tasks is cumbersome and hard-to-manage and it duplicates effort.

Jimbo's picture

De-duplicate records in an Access table using VBA

Performing transformations on legacy data often entails removing duplicates from legacy data, especially when working with data from multiple legacy systems. The access feature Totals, often denoted by the Greek symbol Sigma (Σ), can be used for this, but there is a subtle difference between identifying (or counting) unique records and removing duplicates. Also, creating an Access query marries it to a single table with a fixed format; each table in need of de-duplication requires its own query.

Jimbo's picture

Export directly to Excel from Access using VBA

Excel has a lot of tendencies that make it a less-than-desirable tool for data conversion. Too often data is corrupted by Excel's tendency to convert numbers from one international standard to another, to convert long numeric values to scientific notation and to convert dates from one format to another. The insufferable habit of trimming off leading zeros makes Excel near worthless for handling many types of SAP data.

Jimbo's picture

Identify malformed postal codes in LSMW

Staring at a spreadsheet of ten thousand customers looking for invalid data is like looking for defective hay in a haystack. SAP can cut the time required for validation to a small fraction of what it would normally take by identifying problem records in the source data. The resource that is responsible for correcting invalid data (potentially the same person performing the ETL) will be grateful for the help.

Jimbo's picture

How to replace invalid, malformed postal codes with valid dummy values

Many legacy systems have little or no validation on the postal code and SAP has very strict, country-specific validation that cannot be circumvented. Validating the postal codes in LSMW as part of the Convert Data phase is always a good idea and is a great way to identify defects in the data and provide feedback to the client. Early in the implementation it is better to transport the master data with dummy postal codes to allow the processing of downstream transactional data.