Deletion of selective data from DSO through Transformation

[Former Member]

Scope:

Generally Selective deletion from DSO is done manually. This Document explains how to automate selective deletion from DSO through ABAP Code in transformations.

Scenario:

               We have 2 Key fields and 4 data fields in DSO. We need to do selective deletion based on One of the key field value. Sample data is give as below.

Contents of Active table in DSO:

Uname(KF)	Bpno(KF)	DF1	DF2	DF3	DF4
A101	1	10	20	30	40
A101	2	11	21	31	41
A101	3	12	22	32	42
A101	4	13	23	33	43
A101	5	14	24	34	44
A102	1	15	25	35	45
A103	2	16	26	36	46
A103	5	17	27	37	47
A103	9	18	28	38	48

Above data is active table content of DSO. In next data load if we get new record whose Uname is A101 then it should delete all records from active table whose Uname is A101 and insert new records.

Records in New data load:

Uname	Bpno	DF1	DF2	DF3	DF4
A101	6	30	45	67	78
A101	5	55	54	23	54

Final contents of Active table:

Uname	Bpno	DF1	DF2	DF3	DF4
A101	6	30	45	67	78
A101	5	55	54	23	54
A102	1	15	25	35	45
A103	2	16	26	36	46
A103	5	17	27	37	47
A103	9	18	28	38	48

Procedure:

For Selective deletion of the records and Inserting new records following procedure is adopted.

1. Get unique usernames (Uname) from the new records which should be loaded into DSO.
2. For corresponding unique username (Uname) select all entries from Active table of DSO.
3. Now in the internal table we have records which need to be deleted (records which are selected from DSO active table) and records which need to be updated.
4. 4.Insert First set of records with record mode ‘D’, and second set without any manipulation.

   So, upon activation all the records in active table for which a username exists in New records which are to be loaded will be deleted.

Above mentioned procedure should be carried out in END ROUTINE.

For example:

If following records exists in DSO

Username	BPARTNER	DF1	DF2	DF3	DF4
A101	1	10	20	30	40
A101	2	11	21	31	41
A101	3	12	22	32	42
A101	4	13	23	33	43
A101	5	14	24	34	44

And following records are to be loaded

Username	BPARTNER	DF1	DF2	DF3	DF4
A101	6	30	45	67	78
A101	5	55	54	23	54

Then records Final records that will be inserted into result package are as follows:

Username	BPARTNER	DF1	DF2	DF3	DF4	Record Mode
A101	1	10	20	30	40	D
A101	2	11	21	31	41	D
A101	3	12	22	32	42	D
A101	4	13	23	33	43	D
A101	6	30	45	67	78
A101	5	55	54	23	54

Below is ABAP Code that should be written in END ROUTINE:

DATA: it_result1 TYPE STANDARD TABLE OF _ty_s_TG_1,
      it_result2 TYPE STANDARD TABLE OF _ty_s_TG_1,
      wa_result1 TYPE _ty_s_TG_1,
      wa_result2 TYPE _ty_s_TG_1,
      v_tabsize TYPE i,
       v_tabix TYPE i.


*Below code is to pull unique username's from flat file which is
*avaialable in reult_package(Flat file).
it_result1 = RESULT_PACKAGE.
sort it_result1 by USERNAME.
delete ADJACENT DUPLICATES FROM it_result1 COMPARING USERNAME.
*  To extract the exsisting contents of DSO's Active table.

select *  into CORRESPONDING FIELDS OF TABLE it_result2 from
<DSO ACTIVE TABLE>
   FOR ALL ENTRIES IN it_result1 where username = it_result1-username.


   DESCRIBE TABLE RESULT_PACKAGE LINES v_tabsize.


   loop at it_result2 into wa_result2 .

   READ TABLE it_result1 into wa_result1 WITH KEY username =
   wa_result2-username.

   if sy-subrc = 0 and wa_result1-BPARTNER <> wa_result2-BPARTNER.

     v_tabsize = v_tabsize + 1.

    wa_result2-RECORD = v_tabsize.
    wa_result2-RECORDMODE = 'D'.

    append wa_result2 to RESULT_PACKAGE.
  endif.
  CLEAR: wa_result2,
         wa_result1.
    endloop.

Advantages:

·        1.  Automates process of Selective deletion.

·        2. By using   selective deletion option in manage TAB of DSO, we can only delete data from active table but not from change log table, but this approach synchronizes both change log and active table in spite of selective deletion.

·       3.  Data between the DSO and its upper target (at any level) will also be synchronized automatically.

Related Content

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/5080c337-81e5-2c10-97af-8929424f3a4b

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/d032758c-a61c-2e10-eb9f-afb1460f920d

[Former Member]

Thanks for posting this, really helpful.  Only suggestion I'd make is that before doing:

select *  into CORRESPONDING FIELDS OF TABLE it_result2 from

<DSO ACTIVE TABLE>

   FOR ALL ENTRIES IN it_result1 where username = it_result1-username.

You should check if it_result1[] is not initial. If it is blank and it tries to select on it, it will do:

select *  into CORRESPONDING FIELDS OF TABLE it_result2 and select everything.  Just ran into this...

[sengupta13_amartya30]

Hi,

Is there any way to do the same but let say in a reverse manner, meaning let suppose we have huge amount of data in new load now I just wanted to move some selective data from new load table to active table in you case, and after successful transfer of those selected data into active table, the data in the new load table should get deleted automatically.

Can you tell How to acheive it?