The other day I needed to calculate number of workdays in a 60 days period for our Tierp Factory .
Since I had to do this in our BI system I needed the shop calendar for Tierp from SAP. I started to look around for some procedure to extract the shop/factory calendar from SAP. To my surprise I didn’t find anything. The closest I found was the bapi DATE_CONVERT_TO_FACTORYDATE. This piece of code takes a date and gives the shop calendar equivalent, i.e. it tells you if the date is a work day or not and the closest work day if it is a holiday. By exposing a range of dates to date_convert_to_factorydate you can create a shop calendar. I also found some interesting tables in SAP where I could extract the information I needed, but I decided to have some fun with my PHP job scheduler and call date_convert_to_factorydate repeatedly for a long date span, and assemble the shop calendar with the result. I needed to feed the date_convert_to_factorydate with the id of the Tierp factory calendar, a range of dates , and a flag telling date_convert_to_factorydate to look forward or backward for closest work day.
The id is stored in SAP TFACD table, the date span could easily be created by a function and I wanted a backward search. Then I just had to call SAP once for each date in the span, for this I could use a job iterator, which would do the job, but with a nasty side effect, since the job calling SAP would repeat itself it would log on and log off from SAP once for each date. Instead I pushed down the iterator to the SAP communicator sap2.php, which logs on once and then iterates through the dates and appends the results. After this its just a simple matter of loading the result into our BI systems mySQL database. OK here we go:
For you who have followed my posts about my PHP job scheduler and the SAP communications examples . The interesting pieces here are the PHP functions to generate the start of the calendar in <tag name=’CALSTART’...> and the use of the iterator <mydriver> which creates a date span, which the script sap2.php will iterate through and call date_convert_to_factorydate once for each date. The second job in this schedule createCalendar just loads the result into MySQL.
Here you see part of the created shop calendar:
Example - The date 2012-07-08 is a holiday and the closest work day is 2012-07-06.
If you take the time to study the first job getCalendar you will notice there is actually quite a lot FUNctionality packed in there.
My first use of this shop calendar is an advanced formatted Excel sheet, which I will create and email in subsequent jobs. That will be fun too.