Delete Previous request

[Former Member]

Hello Everyone,

I have a custom ODS into which i do an init delta and then load delta data everynight. Before loading a new load, i want to delete the earlier request in the ODS.

Since this is a custom ODS, i can't choose the 'Delete earlier content' option in the 'Data targets' tab of InfoPackage. How do i delete the earlier request for this custom ODS.

Could someone help me.

Thanks.

[Former Member · Accepted solution]

Hi Sachin,

Actually speaking why do you ever want to do that? There are 2 reasons for that:

1. Your data in the ODS would get overwritten each time a new request comes in for the same set of key fields.

2. Secondly you are doing a delta load which means you are pulling in only the updated/changed records.

Bye

Dinesh

[Former Member · Accepted solution]

Sachin,

You need to write some ABAP code.

[Former Member · Accepted solution]

Hello Dinesh,

We have another layer of ODS into which we will do full loads from the first layer of ODS. So, we will still have our data. For the first layer of ODS, we would like to delete prevoius request before every new load.

Govind - Where do we write the code. could you be more clear.

Thanks.

[Former Member]

That code provided by £ukasz Krys , might not work as we cannot compare system date with UTC Timestamp . First we need to convert that UTC Time stamp to a regular system timestamp and then get the date part out of it.

[Former Member]

I am not sure if that worked for you or not , but just in case you need to try , I had a similar requirement but I was doing that for the cubes. May be you can have a look and make some modifications .

-

---

program conversion_routine.

• Type pools used by conversion program

• Global code used by conversion rules

$$ begin of global - insert your declaration only below this line -

TABLES: RSREQICODS,RSSELDONE.

DATA: IT_RSREQICODS TYPE SORTED TABLE OF RSREQICODS

WITH NON-UNIQUE KEY RNR

WITH HEADER LINE.

DATA: WA_RSSELDONE type RSSELDONE.

$$ end of global - insert your declaration only before this line -

• -------------------------------------------------------------------

• InfoCube = ZCUBE

• -------------------------------------------------------------------

form compute_ZCUBE

tables l_t_request_to_delete structure rsreqdelstruc

using l_request like rsreqdone-rnr

changing p_subrc like sy-subrc.

*Insert Source Code to decide if requests should be deleted.

*All Requests in table l_t_request_to_delete will be deleted

*from Infocube ZCUBE.

*Add new requests if you want to delete more (from this cube).

*Remove requests you did not want to be deleted.

$$ begin of routine - insert your code only below this line -

DATA: v_time_stamp TYPE timestamp,

v_DATE TYPE sy-datum,

v_TIME TYPE sy-uzeit,

v_TIMEZONE TYPE ttzz-tzone.

*GET THE MOST RECENT REQUEST # LOADING FROM 8ZODS AND DELETE THAT

*FROM ZCUBE

SELECT *

INTO TABLE IT_RSREQICODS

FROM RSREQICODS

WHERE TABNAME = 'ZCUBE'

AND TYP = 'I'.

*

LOOP AT it_RSREQICODS.

SELECT SINGLE *

FROM RSSELDONE

INTO WA_RSSELDONE

WHERE RNR = it_RSREQICODS-RNR

AND SOURCE = '8ZODS'.

IF SY-SUBRC EQ 0.

v_time_stamp = it_RSREQICODS-timestamp.

CONVERT TIME STAMP v_time_stamp TIME ZONE v_TIMEZONE

INTO DATE v_DATE TIME v_TIME.

IF v_DATE LT sy-datum.

l_t_request_to_delete-rnr = IT_RSREQICODS-RNR.

APPEND l_t_request_to_delete.

ENDIF.

ENDIF.

ENDLOOP.

CLEAR p_subrc.

$$ end of routine - insert your code only before this line -

endform.

[Former Member]

Hi Sudeepta,

I have not yet tried the code Krys gave. Thanks for the code. I will update everyone how it went.

Thanks a lot.

[Former Member]

See the topic

and read my reply.

[Former Member]

Hi Krys,

I will try that and update you.

Thanks.