SAP BW Create own reversal entry

Lately, all my posts started with "In my current project", so now something else, even if it was developed in the current project. The problem we are facing with is that we get a data extraction which deliver us only the new data records, not the reverse data record.

So we need to build your "own reversal entry" to set the values of a record to 0. For this we write our ADSO in itself with a formula which delivers the negative value.

I build a transformation which the formula above and the magic will be done in the DTP filter. First, let me briefly explain our data model. We use the following layers:

• Acquisition Layer
• Propagation Layer
• Transformation Layer

But back to the magic. The routine is writen in the DTP Transformation Layer in Transformation Layer.

First I determine all requests from the Acquistion Layer DSO. 

1
2

"Determine all requests from source DSO
SELECT DISTINCT ( reqtsn ) FROM /bic/avf0aperia1 INTO TABLE lt_requests.

After I have all requests in my internal table, I sort it descending to get the right order.

1
2

"Sort Table descending
SORT lt_requests DESCENDING BY request.

Now I select the first request. It is the newst.

1
2
3
4

LOOP AT lt_requests INTO ls_requests FROM 1 TO 1.
  "Puffer last request
  lv_last_request = ls_requests-request.
ENDLOOP.

Now we know the right request and select all data from the advanced DSO (Aquisition Layer).

1

SELECT DISTINCT field1 field2 calyear field3 FROM DSO_AL INTO TABLE lt_new_data WHERE reqtsn = lv_last_request.

After we now have all necessary information in our internal table, we have to fill the DTP filter.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

DATA: l_idx LIKE sy-tabix.

READ TABLE l_t_range WITH KEY fieldname = 'field2'.
l_idx = sy-tabix.
LOOP AT lt_new_data ASSIGNING <fs_new_data>.
  l_t_range-fieldname = 'field2'.
  l_t_range-iobjnm    = 'field2'.
  l_t_range-sign      = 'I'.
  l_t_range-option    = 'EQ'.
  l_t_range-low       = <fs_new_data>-field2.
  IF l_idx <> 0.
    MODIFY l_t_range INDEX l_idx.
  ELSE.
    APPEND l_t_range.
  ENDIF.
ENDLOOP. 

After I execute the DTP and activate the request in the transformation layer advanced DSO you see there are only 18 entries which are corrected.

When you now look into the active table of the advanced DSO, you see all values which are shown above are now 0.

The changelog also show the new, the before and the after image for the entries. So you can understand what was booked.

And when we now execute all DTPs to write the data from the Acquistion Layer to the Tranformation Layer, we see that there are only 18 entries which are new.

And also the new booked values are the same we have above in our second extract.

As you can see it is very easy to create your own reversal entry if the source system don't deliver one. I hope someone can need it.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.