SAP BW 3.5x / Business Intelligence 7.3x.
Author(s) : Jai Gupta
Company : Infosys Limited
Created on : 05 November 2012
Jai Gupta works as Senior Systems Engineer in Infosys Limited. He has 2.7 years of experience in
SAP ABAP/BW. He has worked on Various Support and implementation Projects.
In our business scenario, we needed an ABAP program which can delete the data from the WO DSO’s based on the day interval or the number of the requests needed to be deleted from a given specified date. For eg if in a WO DSO’s we want to delete the request for September and October months, then we will provide the details as 1.09.2012 and 31.10.2012 for the deletion of data or if we want to delete the latest request for the month of October then we will put 31.10.2012 and request count as 1.In case two requests are needed to be deleted then we can give 2 as request count. Detail explanation is provided below:
As shown in the selection screen below, we have to specify the WO DSO’s name and Date-To as they are mandatory parameters.
Date-To: specify the date below which data will be deleted.
Date From: specify the date after which the data will be deleted.
Request Count: specify the number of requests we want to delete from the respective WO DSO’s.
Both these dates are inclusive and will be included during data deletion.
- Between Date From or Request count, only one of them should be mentioned
- Both Date From or Request count shouldn’t be mentioned simultaneously
- Date To should be greater than Date From
Demo1 :Using Date To and Date from in the selection screen
In the Selection screen, enter the WO DSO’s names and the values for Date To and Date From as shown above.
Expected Result: So according to the criteria specified above, request id 6919830 should get deleted from the DSO1 and request id 6919829 should get deleted from the DSO2.
After the program completion a message will trigger showing the details of the request deleted from the WO DSO’s as shown above.
Actual Result: Same as expected result.Requests 6919830 & 6919829 from DSO1 and DSO2 got deleted successfully as shown above.
Demo2 :Using Date To and Date from in the selection screen
Executing the program
In the selection screen enter the WO DSO’s details, then the Date To and after that Request Count. According to the above selection criteria, the first request present in the WO DSO’s corresponding to date less than equal to 23.10.2012 will get deleted from both the WO DSO’s . For e.g. if we have two requests which was updated on the WO DSO’ s on 23.10.2012 then the request which got loaded later deleted. Also if we don’t have any request loaded in WO DSO’s on 23.10.2012 then the requests for the date less than 23.10.2012 will be checked and deleted i.e. if we have request for date 22.10.2012 and 21.10.2012 then the 22.10.2012 request will be deleted depending upon the request count in the last loaded first deleted basis.
Expected Result: So according to the criteria specified above, request id 6837629 should get deleted from the DSO1 and request id 6837628 should get deleted from the DSO2.
Actual Result: Same as expected result.
Other Utilities: It can be used in process chains to delete the overlapping requests from the WO DSO’s.
As shown in the below screen, we have used the ABAP program to delete the over lapping requests from the WO DSO’s DSO1 and DSO2.
for the deletion of over lapping request create an variant for the program having the following values:
DSO : Name of the WO DSO’s from which you want to delete the request.
Date To: Current Date
Request Count: 1
Click on save button, a new screen will open. In this screen give the variant name and description as shown below:
Now click on the button highlighted above, a new screen will appear, here choose current date.
After doing all these save the variable and use it in the ABAP program in the process chain as shown below:
After all this, u can schedule the process chain to check the working of the ABAP program in the PC.After scheduling go to the logs as shown below, there you can see the messages regarding deletion of requests from the DSO1 and DSO2.
The code of the program is mentioned below:
*&---------------------------------------------------------------------* *& Report ZBW_ODSREQDEL_PLT3 *& *&---------------------------------------------------------------------* *& *& *&---------------------------------------------------------------------* REPORT zbw_odsreqdel_plt3. TABLES:rsiccont, rsreqdone,rsdodso. SELECTION-SCREEN: BEGIN OF BLOCK a WITH FRAME TITLE text-001. SELECT-OPTIONS: s_dso FOR rsdodso-odsobject NO INTERVALS OBLIGATORY. PARAMETERS: p_dateto TYPE sy-datum OBLIGATORY, p_datefr TYPE sy-datum , p_reqcnt TYPE i . SELECTION-SCREEN: END OF BLOCK a . TYPES: BEGIN OF t_dso, odsobject TYPE rsdodso-odsobject, odsotype TYPE rsdodso-odsotype, END OF t_dso. DATA: i_req_cube TYPE STANDARD TABLE OF rsiccont, i_req_comp TYPE STANDARD TABLE OF rsseldone, wa_req_cube LIKE rsiccont, wa_req_comp LIKE rsseldone, wa_req_comp1 LIKE rsseldone, i_dso TYPE STANDARD TABLE OF t_dso, wa_dso TYPE t_dso, l_cnt TYPE i, l_cnt2 TYPE i, l_diff TYPE i, l_message1 TYPE string, l_message2 TYPE string, l_message3 TYPE string, l_message4 TYPE string, l_message5 TYPE string, l_message6 TYPE string, l_timestamp1 TYPE timestamp, l_timestamp2 TYPE timestamp, l_tmzone TYPE tznzone, l_reqcnt TYPE i, l_reqcnt2(4) TYPE c. IF p_reqcnt IS NOT INITIAL AND p_datefr IS NOT INITIAL. MESSAGE text-005 TYPE 'E'. ENDIF. IF p_reqcnt IS INITIAL AND p_datefr IS INITIAL. MESSAGE text-006 TYPE 'E'. ENDIF. IF p_dateto LT p_datefr. MESSAGE text-007 TYPE 'E'. ENDIF. IF s_dso IS NOT INITIAL. SELECT odsobject odsotype FROM rsdodso INTO TABLE i_dso WHERE odsobject IN s_dso AND objvers = 'A'. SORT i_dso BY odsobject. DELETE ADJACENT DUPLICATES FROM i_dso COMPARING odsobject. DESCRIBE TABLE i_dso LINES l_cnt. IF p_dateto IS NOT INITIAL. l_tmzone = 'UTC'. CONVERT DATE p_dateto TIME '235959' INTO TIME STAMP l_timestamp1 TIME ZONE l_tmzone. IF p_datefr IS NOT INITIAL. CONVERT DATE p_datefr TIME '000001' INTO TIME STAMP l_timestamp2 TIME ZONE l_tmzone. SELECT * FROM rsiccont INTO TABLE i_req_cube WHERE icube IN s_dso AND ( timestamp LE l_timestamp1 AND timestamp GE l_timestamp2 ). SORT i_req_cube BY icube timestamp DESCENDING. DESCRIBE TABLE i_req_cube LINES l_cnt2. IF i_req_cube IS NOT INITIAL. CLEAR : l_message1,l_message2,wa_req_cube. LOOP AT i_req_cube INTO wa_req_cube. CALL FUNCTION 'RSSM_DELETE_REQUEST' EXPORTING request = wa_req_cube-rnr infocube = wa_req_cube-icube. CONCATENATE wa_req_cube-icube wa_req_cube-rnr l_message1 INTO l_message1 SEPARATED BY space. ENDLOOP. ENDIF. IF l_cnt NE l_cnt2 OR i_req_cube IS INITIAL. LOOP AT i_dso INTO wa_dso. READ TABLE i_req_cube WITH KEY icube = wa_dso-odsobject TRANSPORTING NO FIELDS. IF sy-subrc NE 0. CONCATENATE wa_dso-odsobject l_message2 INTO l_message2 SEPARATED BY space. ENDIF. ENDLOOP. ENDIF. ELSE. IF p_reqcnt IS NOT INITIAL. SELECT * FROM rsiccont INTO TABLE i_req_cube WHERE icube IN s_dso AND timestamp LE l_timestamp1. SORT i_req_cube BY icube timestamp DESCENDING. CLEAR: wa_dso,wa_req_cube,l_message1,l_message2. LOOP AT i_dso INTO wa_dso. l_reqcnt = p_reqcnt. LOOP AT i_req_cube INTO wa_req_cube WHERE icube = wa_dso-odsobject. CALL FUNCTION 'RSSM_DELETE_REQUEST' EXPORTING request = wa_req_cube-rnr infocube = wa_req_cube-icube. CONCATENATE wa_req_cube-icube wa_req_cube-rnr l_message1 INTO l_message1 SEPARATED BY space. l_reqcnt = l_reqcnt - 1. IF l_reqcnt LE 0. EXIT . ENDIF. ENDLOOP. IF l_reqcnt > 0. l_reqcnt2 = l_reqcnt. CONCATENATE wa_dso-odsobject l_reqcnt2 l_message2 INTO l_message2 SEPARATED BY space. ENDIF. ENDLOOP. ENDIF. ENDIF. ENDIF. IF l_message1 IS NOT INITIAL. CONCATENATE l_message1 text-008 INTO l_message4 SEPARATED BY space. IF l_message2 IS NOT INITIAL. CONCATENATE l_message2 text-009 INTO l_message5 SEPARATED BY space. CONCATENATE l_message4 l_message5 INTO l_message6 SEPARATED BY ','. MESSAGE l_message6 TYPE 'S'. ELSE. MESSAGE l_message4 TYPE 'S'. ENDIF. ELSEIF l_message2 IS NOT INITIAL. CONCATENATE l_message2 text-009 INTO l_message5 SEPARATED BY space. MESSAGE l_message5 TYPE 'S'. ENDIF. ENDIF.
001 Enter Criteria
002 Request count exceeds the no of requests currently in the DSO
003 Requests to be deleted exceeds the no of requests currently in the DSO
004 It is not a write optimized DSO
005 Either mention Request Count or Date-From
006 Enter Value in Request Count or Date-From
007 Date-To can not be less than Date-From
008 Requests deleted successfully
009 Requests are not deleted
P_DATEFR Date From
P_DATETO Date To
P_REQCNT Request Count
S_DSO DSOs Name
The program can be modified to include standard DSO’s as well in its scope..