Exporting data from / to SAP R/3 system Banging your head with names like VBELN, VGBEL, KAPOL ?
Would
you like to have all your company’s relevant data from SAP in your own tables in SQL Server, and do the reporting queries yourself?
Well, it’s possible. I know it is because I did it.
Our administration employees had become sick and tired from trying to retrieve data from our SAP R/3 system. As our company grew bigger, the management demanded more and more reports and summaries, which were impossible to execute without a special configuration.
So why not to configure SAP? Money! It takes heaps of hours to do even the smallest task, multiplied by the enormous hour rates of SAP consultants…
So how can you do it?
Step I
Choose the
application software and database interfaces. We chose Visual Basic as the application, and SQL Server as the database.
Step II
Establish a gateway to transfer data from the SAP db to your own database.
In SQL Server you can do it using the Import wizard that creates a package called DTS. The DTS can then be called from the application.
We set a refresh interval of 30 minutes between each DTS update call. That means that once in 30 minutes the application runs the DTSs – imports the SAP relevant data into our own SQL Server tables. The interval is good enough for most of the reports we needed. For others – we query online.
Step III
Find where the SAP interesting tables are. That’s the most difficult and time-consuming task: in our SAP (R/3 4.6c) we’ve found more than 20000 tables. There are a lot (and not enough) websites that map the location of SAP data. For example: KAPOL, KONH and KONP handle the conditions – therefore knowing them you can export all the price lists. EKPO holds the purchasing data.
Use Google a lot, and don’t be afraid from the weird names of the tables and columns. It’s just the way it is.
If your SAP is on Oracle, there are a few good and cheap (50$) softwares that have a nice querying interface into the database. Buy one and save a lot from your time.
Step IV
After processing some data in your own database, you’ll see how fast and cheap it is for you now to respond to the management needs of reporting and statuses. Shortly after that you’ll be interested in importing data into the SAP system.
The first reaction of 99% of the people will be: no way! It is indeed not recommended to write directly to the database. In a lot of cases SAP holds the same data in more than one table (faster to query, redundancy).
But one of the nicest tools SAP has is called: BAPI.
A BAPI is a function that acts with the SAP system, and can be called by external applications, i.e. – from Visual Basic.
There is a enormous number of BAPI functions that you can activate. In order to have the complete list of your system BAPIs, login to SAP R/3 and use the transaction:
BAPI.
For example: I used successfully a BAPI called BAPI_PO_GETDETAIL in order to import data about the purchase orders text details, and an object called SalesOrder / CreateFromData, in order to easily “push” new
sales order into the system.
The latter solved the Logistics team problem of entering large orders into the system. Since I could enter them myself using the VB application – I could make my own interface (pasting the lines from Excel or from our web CRM module) – and the new orders were loaded to the SAP system within a fraction of a second.
During all the process you need to be focused on what has to be improved – using your own tools, and what is better to be kept in SAP and even worthy an SAP consultant expensive configuration.
Don’t be afraid of the monster – if you have the time it is possible to make your own bypass for exporting, and keep SAP running in the background as a robust database.