SD extraction - Reversal records and enhanced characteristics

[Former Member]

I recently discovered a problem with delta records coming from extractor 2LIS_08TRTLP (LES-Shipment Documents) but the issue I think could relate to any of the SD extractors. In a general sense, the issue is that it seems like there's a problem for enhanced characteristics IF they can be changed by the user in R/3 after an SD document has been created AND someone wants to restrict them in some way in BW queries.

Here's what is happening:

I enhanced the 2LIS_08TRTLP extractor to include the Actual Goods Issue Date field from the delivery and am populating this via an R/3 user exit. What's happening sometimes is this scenario:

Day 1 - a user creates a shipment document and does NOT put it in status '6' (Shipment Start) (which PGIs the deliveries). In our nightly load, a record flows over to BW with the actual goods issue date blank. Everything is fine.

Day 2 - the user sets the shipment into status '6', the delivery gets PGIed. Two records flow over to BW - one is a new record with the actual goods issue date populated. The second record is a reversal of the first record, but because this is flowing through the user exit, this ALSO has the actual goods issue date populated, even though the original record had it blank! This is the problem.

What then happens is in the InfoCube, the two records with the same goods issue date get added together to equal zero (one reverses the other) and all that is left is the original record with a blank issue date. Reporting is fine as long as the actual goods issue date is not a restriction in the query - if it is, then the results are incorrect and the weights/volumes are zero.

I understand the issues around adding enhanced key figures to a DataSource and the need the manage the reversal records via the ROCANCEL field, but what should someone do in this case to deal with the enhanced characteristics? I can't tell it to always leave the goods issue date blank on reversal records, because it is possible for an R/3 user to change a different field after it is PGIed and then I would need a reversal to flow through with the GI date on it.

What we are proposing is creating an ODS object to sit in-between R/3 and the InfoCube to manage the deltas. But I am little bit concerned about this approach in light of the (lengthy) OSS note 440166 which spells out a long procedure to be done if using ODS objects with the Shipment DataSources. Yet we can't see any other way around this scenario. We're thinking of just creating an ODS with the proper keys and not doing all of the instructions in OSS note 440166.

Has anyone ever run into this problem before?

Thank you,

Chris

[kumar_gudiseva2]

I think we may have problem as (for example) the shipment costs are split at delivery item level. If we set the key figure in ODS to additive without adding the Delivery & Delivery item as key in ODS these key figures may be wrong.

Kumar Gudiseva.

[kumar_gudiseva2]

We are using the ODS layer for all 08 datasources and implemented the recommendations of note 440166.

The issues I see are from including the Delivery & Delivery item as key in the ODS which creates multiple records per shipment or shipment cost item.We have counters(like number of shipments) and Key figures(condition values like fuel surcharge, Demurrage or Detention) appended using user-exits. In the case of counters we use exception aggregation and for Key figures we use counters(counter = 1 for every non-zero value of the key figure) to average them out on the front end.

Kumar Gudiseva.

[Former Member]

Hi Kumar -

It seems like we might be okay - in my tests as long as I added 0RECORDMODE to the InfoSource and mapped this to ROCANCEL, it seemed the ODS handled the "R" reversal records correctly... if an "R" record came in along with a regular record for the same key, it ignored the "R" record. If it came in by itself, it deleted the data by deleting the record from the active table during ODS activation. Pretty impressive.

So for the 08 datasources, if we decide to use the ODS layer in our BW system WITHOUT implementing the R/3 changes specified in OSS note 440166, do you think we'd run into any problems?

Thanks for your help,

Chris

[Former Member]

Hi Chris,

What you can do in the exit is

- check whether it is a cancellation record

- If yes, find the entry for this record in change document tables (CDHDR and CDPOS) to get the last GI DATE value.

- Set this date in the GI DATE field. (If you do not find it in change doc table, let it be initial value).

For records other than cancellation one (eg new), let the original logic remain.

If the change document doesn't have this data, you may have to devise some other logic.

The URL contains the first part of the weblog on similar topic, it may be of help http://www.ko-india.com/content/weblogs/weblog_custom_fields_1.pdf

Message was edited by: Ajay Das

[Former Member]

Thank you this looks like an excellent white paper. It's given me an option to consider on the extraction side of things.

It looks like we'll decide between making these changes to read these tables (these tables are HUGE in our production environment however so I'm concerned about what this would do to the extraction performance?)... or perhaps create an ODS layer with the disadvantage that deleted shipment documents would not be reflected in BW since "D" records are not sent by this extractor only "R" records... we're asking our business users if this is acceptable or not and how often they delete shipments.

I'd appreciate any other thoughts you have on this decision we're wrestling with...

Thanks!

Chris

[Former Member]

You are right, you will have to take into account the performance hit due to read on change document tables.

For ODS layer also, there will be additional administration and resource needs, however I assume you have more flexibility in your BW than R/3.

One crude variant of earlier option I mentioned is

- If the document changes only once from status<>6 to status=6 (ie this change record is going only once to BW), in the user-exit you can skip the part where it reads the change document tables. In this case, in the user-exit, check whether it is a cancellation record, and if so, set the GI date to initial, otherwise let the original logic for GI date execute.

This will work if such change on R/3 is one time (ie date changes once from blank to a value). If there are multiple changes in the GI date, you will still have the same problem.

Other option might be to enhance the communication structure itself (so you get the GI date in the extractor) and populate it using appropriate LIS exit. Am not too sure if there is any such exit for this. In this case you will always get the GI date from the transaction. A similar example (for a purchasing doc) can be seen in the example here /people/sanyam.kapur/blog/2005/04/30/custom-fields-and-bw-extractors-making-a-mixed-marriage-work-part-ii

cheers,