Email SAP SQVI reports.

Many years from now I was involved in creating a client-server application, in those days this was a new concept, clients was Windows PC computers, and server was a backend computer of some kind. Often these apps didn’t work due to bad communications and buggy comms protocol those days you actually had to be a rocket scientist to successfully connect PCs to servers and make multi update apps work. It was 1990 and the app was an ordering system, retailers sent purchase orders to a wholesaler and received an acknowledgement in return. The twist with this system was the backend it was a DB2 system in an IBM mainframe. How the f-k should the Windows client connect to the mainframe DB2 system?. I came up with a solution, use mail. The dominant mainframe mail system MEMO (developed by the car manufacturer Volvo!) had a Windows client that had a reliable connection to the mainframe MEMO system.  I thought why not use a mail system for sending messages, it is designed for that. So I created an interface between MEMO and DB2. When a mail arrived I picked up the mail, translated it into appropriate SQLs and sent back an email with an order acknowledgment as an attachment that the Windows client software picked up from the mailbox in the PC. This system worked well, which is more than I could say about other Windows client-server apps I had seen at that time.
I have written a post on extracting data from SAP using SAP RFC and PHP . SAP RFC is a very reliable and fast and definitely the first choice for extracting (large) data from SAP. The problem with RFC communication you need an RFC enabled ABAP program or a BAPI on the SAP end. What if you do not have an RFC enabled program in SAP that give you the information you need? It is not a simple task to create an RFC enabled ABAP program.

In this post I  will schedule and send an SQVI   report by the mail to a target system.

SQVI is a user friendly report-generator in SAP. It is a simple, well relatively simple, thing to knock up an SQVI report. If you are not an ABAP programmer this is a more passable way to route data out of SAP, just bear in mind this is not the most efficient way to extract data from SAP.  
Automatic workflow exporting SQVI reports from SAP into MySQL
This is a picture of the workflow I have in mind. Create an automated workflow from the SQVI report into The Business Intelligence application the Data Warehouse. We start with an SQVI report. (I will not explain how you create SQVI reports, I barely know this myself).
The first thing we need to create is a ‘mail printer’, we use this printer to print the report.


Use SAP transaction SPAD  to create a new printer.
Here are screenshots from creating the printer  LassesMailPrinter .
1. Fire up SPAD and display output Devices.
2 press Create or Create using template (of you have a suitable printer definition to copy)
When the mail printer is created we put it into use.


I select report RUNE and then press Execute (you have to create a report of your own first)
Then I select Execute in Background  and fill in all parameters
Then I choose my mail printer LassesMailPrinter
That’s it. SAP will now create the report and print/send it to your specified email address.
This is the first part of my workflow, the way out of SAP. The second part is to bounce the mail to my FTP server. For that i need some help, we use Lotus Notes for mail and I do not talk Lotus Notes and there is also some security concerns about bouncing emails, to cut it short ‘I can not write this routing so I got help from a colleague.
But now I’m off for vacation and if I recall this when I’m coming back I will create the last part of my workflow from the FTP server into the Data Warehouse.  (It’s the green part of the picture in the beginning).


Telephone Sale

“You are looking for an IS/IT manager?” I have asked this ominous question a zillion times and the unsuspecting answer is always ‘yes?’. Then I finish the conversation with ” I’m sorry but he  is in our  IT-service company IT Help,  I do not know his name but please call our switchboard and ask for the manager of IT help . ThankyouandGoodbye.”

These days I am no longer responsible for IT and I can just hand salesmen over to others.

When I was IT manager I got lots of calls from telephone salesmen. On a bad day it could be more than 15 calls on a good day about five calls. To survive you got to find a strategy to limit both the number of calls and the time of each call. These salesmen are doing an honest (and very hard) work and deserves to be treated with respect.  Nevertheless you have to cut him/her short to be able to do your work.  These calls are especially annoying when you are in the middle of a ‘debugging session’ or some kind of thought process that demands thinking in many layers or structures, since it takes you time to go to the bottom. Being disturbed in the middle may force you to start all over again. Why didn’t I just turned off the telephone? I wanted to be a modern open ‘always online manager’ so I very seldom switched off my telephone.

When these calls started to become a nuisance, I developed a web-app for these salesmen where they could leave a message and I could called them up if I was interested. Then I talked with our switchboard staff asking them to instruct the sales guys to go to the web-app. But the switchboard guys could not do this, that would be all too time consuming. Then I asked for a special IT-manager sales telephone-number with an automatic reply instructing callers how to use my sales web-app. But that was against company policy! I then decided I should end these calls in less than two minutes. This is in fact harder than it sounds, a telephone salesman is a specialist of keeping a conversation going. It is very much a question of taking the initiative before the opponent have a chance to start his sales pitch. You must tell the opponent what to do.  Interrupt the guy when he has introduced himself, and tell him what he has to offer is not relevant for you and tell him to call the next one on his list. (You know what he/she want to offer after the first introduction.)  Remember to be polite, (it can be hard when the 15th guy call on a busy day).  It can also be someone searching for a job, they can be hard to spot after the first introduction they are also in a selling position. These guys not only deserves respect they also deserves some of your precious time.

Why does these salesmen deserves your respect in the first place? At least in my country, Sweden, we have traditionally a mild contempt for sales in general.  And I suffered from the same mild contempt for sales guys until I worked with pre- and post-sales support,  I then realized how hard and frustrating sales jobs actually are. Without a good sales-force a company is not worth a cent. Without the sales guys,  you and I would not have a job. Actually everyone deserves respect. Well almost, there are some real ...        

I wrote this post after yet another of these calls and I feel like a grumpy old man.


Load SAP forecasts into MySQL with RFC_READ_TABLE and PHP

Why do we not have the history of forecasts in the Data Warehouse? Because you have not asked for it.

Update: If this post is of interest you should also read this post.


This post describes how you can extract sales forecasts from SAP. How you can create dynamic SQL predicates for RFC_READ_TABLE. And how you can parallel process these predicates for performant extraction of data from SAP.


Forecasts in SAP is stored in the PROW table (forecast results). Or to be correct the Sales forecasts for our products and spare parts are stored in the PROW table. The key to this table is a sequence number PNUM2, a pointer to forecast results. PNUM2 does not tell you much. PNUM2 has to be translated into PNUM1 which is the key to the PROP table (forecast parameters). Unfortunately the PNUM1 key does not tell you much, it must be translated into MATNR and WERKS those fields are the key to products (and components). Translate PNUM1 into WERKS and MATNR can be done in table MAPR.
To find all  forecasts for a product you:
  1. Look for the product (WERKS & MATNR) in MAPR.  
  2. Take all PNUM1 in MAPR and find all corresponding rows in table PROP.
  3. Take all PNUM2 in PROP and find all  corresponding rows in PROW.
Material forecasts are stored in PROW table. But it is not indexed by material number. It is indexed by pointer. The link to PROW is through PROP through MAPR. MAPR-> Get Pointer for forecast Parameters (PNUM1)-> Read PROP ->Get Pointer for forecast results (PNUM2)->Read PROW using PNUM2.  RAVI KALYANA SUNDARAM
As you can see I started with the MPOP_V table, but after consulting Google  I changed that to MAPR.

Ok let’s fetch the data from SAP and import it into our  Data Warehouse.

There is no problem to pick up all rows for a product or spare part, the problem arises when you download all forecasts for a plant. Using my PHP job scheduler  I quickly set up jobs to extract data from MAPR using RFC_READ_TABLE. To test extracting via a pointer (PNUM1 and PNUM2), I downloaded about 137,000 rows with RFC_READ_TABLE from PROP selecting on date.
But how to extract the corresponding rows from the PROW table? Here I only could select on PNUM2. Our PROW table contains a hell of lot of rows. I decided to to play a little with RFC_READ_TABLE and parallel process selection on PNUM2 from the 137,000 rows PROP table.
First I created a job genArray  that creates a selection predicate for each PNUM2 in the PROP table.
select distinct concat("PNUM2 EQ '",PNUM2,"'") as TEXT from @C_DB.FCC_PROP order by PNUM2;
Then I split the result into chunks that SAP program RFC_READ_TABLE can munch. I decided 10,000 ‘ PNUM2 or predicates ’ was a reasonable chunk to send over to SAP. The program mergesplit_array.php  in job splitArray  takes the result array from genArray and chops it up into 10,000 (2 * 5000) chunk arrays that we feed into the <forevery> job iterator in job getPROW . Here you can see the schedule with the jobs defined.
The SAP forecast schedule. The splitArray job generates arrays with SQL predicates use in the getProw job.  
The split of the SQL result array is not a standard function so I had to write the mergesplit_array.php program used in job splitArray , here it is.  
I removed top documentation and compacted the code a bit, as you see the processing code is quite small.
Now I only had to run the schedule. With a cold cache it takes about one minute  to extract 1,629,660 rows  from PROW and insert them into a Data Warehouse table by 14 parallel tasks each of them with an SQL where clause with 10,000 ‘PNUM2=n OR ’ predicates.  
I consider this test extremely successful. Now we only have to denormalise the MAPR,PROP and PROW tables and create one usable history file and load that forecast history into a graphical user interface and create some snazzy graphics. It will take me an hour or two to create a proper production schedule out of this once the users decide they want this. Then we create a Qlikview  app for presentation. If and when this is done I post some graphics showing forecast history diagrams.  
More examples can be found here .


Outsourcing people

I didn’t participate myself in my first encounter with outsourcing. I left the company 2 years before it actually happened. But I had lots of friends still in the company and I assisted in the handover process. It was ‘my’ old company Atlas Copco Data AB that was outsourced to the company Ericson. AC Data at that time 1987 managed the computer operations of Atlas Copco. The reason for outsourcing the operations were mainly size not efficiency,  Atlas Copco’s operations were streamlined and slimmed down to a minimum, the operation were automated, the personal staff  highly professional.

This was one of the first large outsourcing deal in Sweden  and there were a shortage of skilled IBM mainframe technicians. AC Data could negotiate from a strong position, the CEO actually let the employees participate in negotiations and one of the conditions were all interested should have a new job at the outsourcing company (Ericson) equal or better than he/she had at AC Data. During this time I happened to meet the CEO and he said ‘I never seen people so motivated to lay themselves off’. Very few were happy about the transition to Ericson, but that was more a fact that they all knew each other since many years and now they were split up in a new huge organization. This was probably as happy an outsourcing can be for the employees.

The next encounter with outsourcing hit me more direct I was working for a big computer service bureau; Esselte Datacenter. The owner negotiated the conditions without any of us employees  were involved at all. My (still) good friend the CEO resigned and in came a consultant CEO. He was probably a good guy, but his mission was clear. ‘Do the transition to as fast as possible, only those the outsourcing company wants will be offered a job’. Of about 120 employees we were a handful that got offers in the end and we all thankfully accepted , 1992 Sweden had hit a severe recession and jobs were not to be found. Yes I was very thankful for the offer. At the time I was CTO and become very involved in the handover. I knew that I was important for the continuous operation. In the middle of the transfer I handed in my resignation. This was a calculated risk I had no other job, but as I expected I was offered  a nice bonus if I promised to stay a year, later I understood I could easily have doubled that bonus, but anyway it was a nice bonus. (I demanded the same bonus for a colleague, which to my surprise was granted.)  

The strategy for this outsourcing was very different from AC Data. The new CEO promised openness, we were all going to get all information about all aspects of the outsourcing deal, but in reality we only got information on  ‘as needed base’ and as late as possible. I had the impression we were deliberately stressed so we should not have the time to think but just do our work. Vague talk about job offers but no promises from the consultant CEO. I had one guy that was desperate, he had just bought a house with a hefty  mortgage,  three small kids and an unemployed wife. This guy I actually could help. I ‘promoted’ him to part time assembler programmer and made him responsible for a credit information system and persuaded the outsourcing company he was vital for the system, they bought it and he was given a job. To my grief I could not help more than one, there were many good guys there and there were a lot of tragedies. One guy told me he was considering take up smuggling (stolen?) cars between Spain and Morocco but that is another story. This outsourcing was not happy for anyone of us employees, not even those of us who had a new job to go to, I stayed just over a year at the outsourcing company.  From this outsourcing I learned leave do not stay one minute more than needed  when you are at the risk being outsourced. Even if you can gain some dough by staying it is not worth it.

Some years ago as an IT-manager of the company I’m working for, I had to outsource/lay off some of my staff, due to an anticipated financial crisis. They were not many but that is no consolation for those afflicted. And it is not a pleasant  task for the manager to lay off anyone, and these were good guys. I used my experience from the previous  encounters with outsourcing.

The first thing I did, on a Monday morning ( always tell bad news beginning of the week, NEVER EVER on a Friday ), was to tell the afflicted guys as straight as I could what was going to happen. I told them they were going to get laid off, this was inevitable, the chance they had was to impress the outsourcing company, since we not in a position to demand the outsourcing company to employ them. The reaction was not what I had anticipated. They just did not believe me! They thought they were more or less irreplaceable.  This was of course a mistake from their side, no employee is irreplaceable. Do not ever for a second believe you as an employee is irreplaceable, you are replaceable and expendable .

Next thing I did was to select an outsourcing company.  I made a list of the candidate outsourcing companies the best at the top of the list. I draw a line between those I thought I could work with and those (below the line) I didn’t thought I could work with and told my boss to pick a company above the line. He chose the second from the top, and we are still doing business with them.

My staff found jobs outside the company. They did what I should have done myself, but I tried to arrange for them to go to the outsourcer, but they were not interested.

Outsourcing and laying people off are sometimes necessary, it is your task as manager to do this as smooth and painless as possible for all. This is probably the most stressful task you will encounter as a manager, and you should not do this alone, you should not only engage the HR department but the whole management team if possible.

Never lie, never give false hope and never promise anything you cannot hold. Be frank, open and tell your staff as early as possible what is going to happen.  These are the best advice I can give you, I pray you never have to use them.


Using XBP to manage SAP jobs from PHP

SAP XBP vs 3, is the API for accessing SAP background processes. XBP requires you to log on to the XMI-XBP subsystem. XMI= External Monitor Interface , XBP= External Interface for Background Processing . This sounds awful complicated but It is not that complex. You logon to the XBP subsystem by calling 'BAPI_XMI_LOGON'  this is a normal BAPI call and then you just proceed with the XBP BAPI functions you like to use.

Before you proceed you should learn about XMI and XBP .

Calling the XMI and XBP BAPIs from PHP is no different from calling any other BAPIs or RFC functions from PHP. First you need to install an interface between PHP and SAP. Google on SAPRFC by Eduard Koucky, this project looks dead to me, but Axel  Bangert seems to have picked up SAPRFC. Piers Harding have created a Unicode aware interface SAPNWRFC.  You should Google around and pick the right interface for you. (I’m slowly migrating from SAPRFC to SAPNWRFC. Examples in this post are using Koucky’s original  SAPRFC, which is the simpler interface to use.)

I have written a   job scheduler in PHP , there I have created some functions of my own on top of the SAPRFC and SAPNWRFC interfaces, for logging on to SAP and calling SAP BAPI and RFC functions. The examples here are using my scheduler and my interface functions. Before you proceed you should enjoy my post   extract data from SAP , here you can learn a very useful technique how to use SAP’s transaction SE37 to set up call to SAP functions and transform the in- and out-put parameters to my job scheduler in PHP.

I use the SAP XBP interface to monitor SAP batch jobs or background processes, i.e. I check if SAP jobs have run (successfully) before I kick off my external PHP jobs. (XBP is not limited to monitor you can fully manage background processes in SAP via XBP.) Here is another example .

This somewhat artifical example loads all SAP jobs with names starting with ‘TOO’ into MySQL, if the job ‘TRPX_LE_WRHSEMONITOR_ALL’ was run successfully the during the last 2 days. Then an Excel sheet is created and mailed.

The important pieces of code here are:

the prereq, which checks if job(s) are run in SAP.

<xmi><session>XBP</session></xmi>, explicit log on to XMI/XBP

<schedule name='BatchList' logmsg='Automagically load of SAP rfc output  into mysql table(s)'>

        <tag><name>DW_DB</name><value>test</value></tag> <!-- MySql database -->

         <!-- only run if SAP job executed ok the last 2day -->

         <prereq type='sap' jobname='TRPX_LE_WRHSEMONITOR_ALL' username='*' executed='now-2d'/>


        <job name='get_Jobs' type='script' data='sap2.php'>








                                        ,('JOB_SELECT_PARAM',array('JOBNAME'=>'TOO*',     'USERNAME'=>'*','ABORTED'=>'X','FINISHED'=>'X','ACTIVE'=>'X', 'FROM_DATE'=>@TODAY))                                                                                 </import>








        <job name='generate_report' type='sql'>


                        USE @DW_DB;

                        SELECT *  from tooJobHeads;







        <job name='send_mail' type='sendmail'>



                <subject>Fyi: batch jobs and XMI</subject>



                This is an artificial example but it shows the Sap connectivity implemented in ACTADW.


                The mail you recieved contains two attachments this file (schedule.xml) and an SAP job status report.

                The schedule.xml file contains all commands necessary:

                        1 Check that the Sap job TRPX_LE_WRHSEMONITOR_ALL is sucessfully executed during the                        last 2 days, otherwise the execution is intercepted.

                                                2 Extract the information from SAP.

                        3 Load  the extract  into ACTADW.

                        4 Create a report.

                        5 Create this mail and send it.

                Lars Johansson











The PHP code for Logon to XMI


*  This function logs on to the Sap XMI subsystem


* Before you log on to the SAP XMI subsystem, you should log on to SAP with {@link connect2sap()}, and before you

* disconnect from SAP you should log off from XMI with {@link sapXmiLogoff()}.


* Syntax: <samp><xmi session='XAL'/></samp>

* @todo update SAP3.php

* @see sapXmiLogoff()

* @param array $context

* @param object $sap Handle to saprfc interface

* @return string $sessionid The XMI sessionid

* @uses callSapFunction()


function sapXmiLogon($context,&$sap,$xmiType='XBP'){

        $log = $GLOBALS['log'];


        if (!$sap) return FALSE;

        $sapContact = $context['sapinfo'];

        switch ($xmiType) {










                $log->logit('Error',"Unknown SAP XMI session type=$xmiType");

                return FALSE;


        if ($fce->GetStatus() == SAPRFC_OK) {

                $log->logit('Info',sprintf("SAP XMI session established sessionid=%s",$fce->SESSIONID));

        } else {

                return FALSE;


        $sessionid = $fce->SESSIONID;


        return $sessionid;


And the code to evaluate the SAP prereq at the beginning of the the XML schedule example ‘BatchList’ above.


*  This function evaluates a Sap job predecessor by calling BAPI_XBP_JOB_SELECT


* The parm 'SINCE' allows a more relaxed call to BAPI_XBP_JOB_SELECT.

* <samp>

* <stmt type='sap' JOBNAME='TRPX_LE_WRHSEMONITOR_ALL' USERNAME='*' SINCE='now-1d'/>

* </samp>

* Parm 'SINCE' calls {@link tagday.php} to convert the argument to a point in time, which is translated into

* the parameters 'FROM_DATE' and 'FROM_TIME'. The FINISHED parameter will also be set ('X') as default.


* Parms to BAPI_XBP_JOB_SELECT: <br>

* 1  JOBNAME Background job name

* 2  USERNAME Initiator of job/step scheduling

* 3  FROM_DATE Planned Start Date for Background Job

* 4  FROM_TIME Planned start time for background Job

* 5  TO_DATE Planned Start Date for Background Job

* 6  TO_TIME Planned start time for background Job

* 7  NO_DATE Selection flag for jobs without start date

* 8  WITH_PRED Selection flag for jobs with start after predecessor

* 9  EVENTID Background Processing Event

* 10 EVENTPARM Background Event Parameters (Such as, Jobname/Jobcount)

* 11 PRELIM State of Background Job

* 12 SCHEDUL State of Background Job

* 13 READY State of Background Job

* 14 RUNNING State of Background Job

* 15 FINISHED State of Background Job

* 16 ABORTED State of Background Job


* @param array $context

* @param array $cb this is $schedule for schedule prereqs and $job for job prereqs

* @param array $stmt the schedule prerequisit statement

* @param object $sap Handle to saprfc interface

* @return int $jobcount no of executed predecessor jobs with status finished|complete, caller can test for TRUE

* @uses callSapFunction()

* @uses tagday.php


function checkSapPredecessor($context,$cb,$stmt,&$sap){

        $log = $GLOBALS['log'];


        if (!$sap) return FALSE;

        if(!array_key_exists('JOBNAME',$stmt)) return TRUE;

// We only check if sap is alive, and if we pass here sap is responding so...

        $sapContact = $context['sapinfo'];

        $jsp = array_intersect_key(array_change_key_case($stmt, CASE_UPPER)

                ,array('JOBNAME' => '','USERNAME' => '','FROM_DATE' => '','FROM_TIME' => '','TO_DATE' => ''

                ,'TO_TIME' => '','NO_DATE' => '','WITH_PRED' => '','EVENTID' => '','EVENTPARM' => ''

                ,'PRELIM' => '','SCHEDUL' => '','READY' => '','RUNNING' => '','FINISHED' => '','ABORTED' => ''


        if(array_key_exists('SINCE',$stmt) or array_key_exists('since',$stmt)){ //relaxed form

            $script = 'tagday.php';

            $proc = $context['opsproclib'];

            $parm = 'YmdHis'.$stmt['SINCE'];

            $log->logit('Info',"Calling $proc$script with parm=$parm");


* Require tagday.php to evaluate the executed statement


            $dtStr = require("$proc$script");

            $fromDate = substr($dtStr,0,8);

            $fromTime = substr($dtStr,8,6);

            if(!array_key_exists('FROM_DATE',$jsp)) $jsp['FROM_DATE'] = $fromDate;

            if(!array_key_exists('FROM_TIME',$jsp)) $jsp['FROM_TIME'] = $fromTime;

            if(!array_key_exists('FINISHED',$jsp)) $jsp['FINISHED'] = 'X';

            if(!array_key_exists('USERNAME',$jsp)) $jsp['USERNAME'] = '*';


        $log->logit('Info',vsprintf("Enter checkSap %s, %s, %s, %s, %s", $jsp));

            $log->mode('None'); //We do not wish to see SAP empty selections warning messages






            $log->reset('mode'); // Reset log to original verbose mode

        if ($fce->GetStatus() == SAPRFC_OK || $fce->GetStatus() == '99') {

                $jobcount = 0;


                while ( $fce->JOB_HEAD->Next() ) {



        } else {

                return FALSE;


        if(!$jobcount) $log->logit('Note',sprintf("SAP predecessor job %s not executed successfully after $fromDate $fromTime",$stmt['jobname']));

        return $jobcount;