When Master Data loads are scheduled as a Full Load into BW and when records are deleted in the source system, the data will still exist in the P Table. This is because BW only overwrites the records from the Data Source into the P Table.
When we have to report on Master Data, these deleted entries will also show up in the report. One of the easiest options to manage this within BW would be to set a deletion flag for these deleted records. This can be done by comparing data from P Table with the Source Package and by excluding those from the report. Furnished below are the detailed steps for the implementation of the same.
- Add an info object to set an indicator for deleted records
- Select the data from the P table and compare with the data in the SOURCE_PACKAGE and set indicator = X if a match exists
- Delete data from the target internal table where indicator = X
- Find the current load request number
- Find the last request number of the info provider and check if it matches with the current load request number if yes then that will be our last package
- Append the data from target internal table to result in the package
Explanation of the Code
Let us consider an example where a Full extraction of Master Data is done from KNVP (SAP Customer Master Partner Functions Table). The code is implemented in the Start and End Routine of the BW transformation, between Data Source and Master Data Info object.
//Ensure that select from P Table is run only once during the processing of the first Data Package. Note that the internal table should be declared globally
IF IT_PTAB IS INITIAL.
//Select Data from P Table into Internal Table
SELECT * FROM /BIC/PSCCCUSPFN
INTO TABLE IT_PTAB
WHERE OBJVERS = ‘A’
//Loop Data from the P Table to find which of the records are available in the source package
LOOP AT IT_PTAB INTO WA_PTAB .
V_TABIX = SY-TABIX.
READ TABLE SOURCE_PACKAGE ASSIGNING <SOURCE_FIELDS>
WITH KEY VKORG = WA_PTAB-SALESORG
VTWEG = WA_PTAB-DISTR_CHAN
SPART = WA_PTAB-DIVISION
PARVW = WA_PTAB-/BIC/ZPARVW
PARZA = WA_PTAB-/BIC/ZPARZA
KUNNR = WA_PTAB-/BIC/SCCCUSPFN
//Set a flag when data from P Table matches with the Source Package
MODIFY IT_PTAB INDEX V_TABIX FROM WA_PTAB TRANSPORTING DEL_FLAG.
//Deleting all the records where the source and p table match
DELETE IT_PTAB WHERE DEL_FLAG = ‘X’.
//This code gets the current load request number, and this will execute in run time
CALL METHOD P_R_REQUEST->GET_REQUID
R_REQUID = I_REQUID.
//Below Select statement is used to get the last data package ID
INTO TABLE TS_ITAB
WHERE REQUID EQ I_REQUID
AND ISLAST EQ ‘X’.
//We have to append the deleted records with flag only when last package executes
IF SY-SUBRC = 0.
LOOP AT IT_PTAB INTO WA_PTAB .
LV_COUNT = LV_COUNT + 1.
CLEAR WA_RESULT-DEL_FLAG .
CLEAR WA_PTAB-DEL_FLAG .
//Update all the required fields here
WA_RESULT-DEL_FLAG = ‘D’..
WA_RESULT-RECORD = COUNT .
COUNT = COUNT + 1.
APPEND WA_RESULT TO IT_RESULT
//Exit when all the records in IT_PTAB is processed
IF LV_COUNT = LV_LINES.
RESULT_PACKAGE = IT_RESULT.
Know more about Visual BI Solutions SAP BW Services offerings here.