This article shows, how to upload CATS data from a SQL server table by using SSIS and Xtract IS BAPI.
For creating Time Sheet entries in SAP, we use BAPI_CATIMESHEETMGR_INSERT. The important input fields are PROFILE and TESTRUN (import parameters) and table CATSRECORDS_IN. The results will be available in tables CATSRECORDS_OUT and RETURN.
The RETURN table contains different types of messages, for example warning, information, error etc. If the input records do not have any errors, then the table CATSRECORDS_OUT will be populated with the same number of input records, otherwise it will not contain any entries.
Note: When we post records using this BAPI, it is to be noted that only records that do not contain errors are posted. If an error occurs during posting, none of the data – including records that do not contain errors – is posted. Therefeore it is advisable to include only a small number of records in each posting.
For example, let us populate 10 entries to CATSRECORDS_IN and execute the BAPI.
If there is no error then CATSRECORDS_OUT will contain 10 entries.
If two entries have errors, then CATSRECORDS_OUT will be empty, but we can identify the erroneous entries with the help of RETURN table.
The erroneous entries can be easily identified with the ROW fields of table RETURN. If the second and third entries have errors then the ROW of table RETURN will be populated as 2 and 3.
By using Xtract IS BAPI, with the help of three SQL tables, we can fill the input details and read the output tables.
The three tables, to be created in SQL database are for CATSRECORD_IN and for CATSRECORD_OUT and RETURN. We need to fill the import parameters PROFILE with an appropriate value and TESTRUN as SPACE too.
Here is an overview of the data flow task.
Step-by-step explanation #
Step 1: Prepare input Table in SQL Databse
Create a SQL table for CATSRECORDS_IN with the following fields:
Table Name: BAPICATSINPUT
Fill the table with sample data:
Step 2: Create Xtract IS BAPI Component
For creating the Xtract IS BAPI component, we need to create the data flow task and necessary OLE DB and XTRACT connections.
After this, create an OLE DB Source and map it with the already created BAPICATSINPUT table.
Then create the BAPI component and assign the BAPI BAPI_CATIMESHEETMGR_INSERT .
For example, let us provide the PROFILE “TEST” and TESTRUN “SPACE”” as import parameters. We should map the input type of CATSRECORD_IN as “Pipeline” and also the output type of CATSRECORD_OUT and RETURN as “Pipeline”.
Then map corresponding fields of CATSRECORD_IN with the fields of SQL table BAPICATSINPUT.
Step3: Prepare the output tables in the SQL Database
Create two OLE DB destinations for CATSRECORDS_OUT and RETURN tables of the BAPI.
Let us create BAPICATSRETURN and BAPICATSOUT tables for these OLE DB destinations and map the corresponding fields.
Step 4: Execute the application
Now that the design is finished let us execute the application.
The output will be as follows. From the above diagram, we would be able to see that for n number of records of CATSRECORDS_IN, there is exactly the same number of records in CATSRECORDS_OUT. So the data has been updated successfully.
Now let us check the SQL tables BAPICATSOUTPUT and BAPICATSRETURN.
There is only an information message.
Step 5: Check the output and identify errors
Now let us check the SAP transaction CAT3 (Time Sheet: Display Times)
We have to provide PROFILE, WORKDATE and EMPLOYEENUMBER.
Let us now execute entries with errors.
Populate the table CATSRECORDS_IN with the following entries.
Here the Attendance or Absence Type is 0005 instead of 0001 for the second and fourth entries. Let us check the output now.
Here the CATSRECORDS_OUT does not have any entries. So it is clear that data is not updated.
Let us query the table RETURN to identify the error.
By checking the ROW numbers we can easily identify the erroneous records. From the message, it is clear that the attendance/absence type (0005) is not maintained.