ABAP Program to delete the requests from the WO DSO’s

Applies to:

SAP BW 3.5x / Business Intelligence 7.3x.

Author Bio

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.

Business scenario:

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:

User Manual:

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.

Validations:

• 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

Executing the program

In the Selection screen, enter the WO DSO’s names and the values for Date To and Date From as shown above.

Before Image:

DSO1

DSO2

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.

Program Output:

After the program completion a message will trigger showing the details of the request deleted from the WO DSO’s as shown above.

After Image:

DSO1

DSO2

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.

Before image:

DSO1

DSO2

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.

Program Output:

After Image:
DSO1

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 field name Date To Click on the button highlighted above, a new window will appear.In this window choose Dynamic Date calculation.

Now click on the button highlighted above, a new screen will appear, here choose current date.

Code:

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.

Text Symbols:

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

Selection Texts:

P_DATEFR     Date From

P_DATETO     Date To

P_REQCNT    Request Count

S_DSO          DSOs Name

Enhancements:

The program can be modified to include standard DSO’s as well in its scope..