topic Fetching the modified records from a master data table in Application Development and Automation Discussions

Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 05:21:44 GMT

Hi,

I am currently using a internal table to fetch the records from a master data table and manipulate it and storing in a custom table.

But I have an issue over the performance.It takes almost 3 hours for the program to get executed as the master data table keeps growing everyday with new records and also the existing records are modified.

I use date as a parameter to fetch the new records.Can you give me an idea to check for the modified records in master data table and placing it in the internal table?

Please help me out.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 05:27:58 GMT

check these tables cdpos,cdheader for changing records.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 05:44:35 GMT

Hi Srinivasan,

If the standard table contains date for field (eg. Date of Modification), then to improve performance, you can create a secondary index on this field of that table. By doing so, it will drastically improve the performance.

In such case, you can only select those records which are greater then some last date, and work on those records only. So in a way, you will be working on the DELTA records only.

regards,

amit m.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 05:49:49 GMT

Hi Amit,

Thanks a lot for your reply.

Can you please tell me whether there is any function module that has master data table name as an input and internal table with the changed and new records as output?

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 06:14:30 GMT

Hi again,

I don't think there will be any general FM for all / any master table. But why do you need such FM. We can write a simple select query on that table and get the records, which are modified since last date.

regards,

amit m.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 06:21:16 GMT

Say,if I want to fetch the records for a partcular date I can do that using a select query with a where clause with date as a parameter.But what I have is posting date in my master data table.So in case if an existing record is changed,how can I get that?

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 06:35:14 GMT

Hi again,

If you are talking about FI records contained in table BKPF, then apart from posting date, there are two other fields, which technically determine when the record was last changed/updated.

AEDAT

UPDDT

May be those can be useful, if it is bkpf.

regards,

amit m.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 06:49:57 GMT

I use a master data table from SAP BW.I have only one date criterion which is the Posting date.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 06:55:15 GMT

database logs table changes in the table DBTABPRT.

In the table do the Setting the Log data changes flag , also you must also set the rec/clientparameter.

You can view these logs using the transaction SCU3.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 06:58:14 GMT

Hi again,

For such kind of scenarios, we necessarily must have a DATE field, (field containing when the record was last updated/modified) in the required table. Even BW uses the same concept when it does the delta upload. Other wise it is very difficult to detect the changes only (also called delta).

In your case, if no such thing is available, then i don't think we have much options other than comparing every row (comparing the primary key, and also the remaining fields). If the row is found, but not matching then it means it is modified. If the row is not found, then it means it is added.

regards,

amit m.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 07:04:06 GMT

Hi Amit,

I could find the Log changes check box in the Technical settings of the master data table?

Can you let me know how to set the client parameter and where it is?

And in what way is it related to the select query?

Regards,

G.Srinivasan

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 07:59:15 GMT

Hi again,

There are two tables

DBTABLOG

DBTABPRT

(I cud not check the records bcos table logging is not active in my system. This is generally done by the basis team, at the server/client level).

These tables, contain the original table name changed, and also the date time changed.

Hope this helps.

Regards,

Amit Mittal.

Re: Fetching the modified records from a master data table

Former Member — Wed, 13 Jan 2010 08:40:41 GMT

You can view these logs using the transaction SCU3.

Re: Fetching the modified records from a master data table

Former Member — Wed, 21 Apr 2010 04:51:21 GMT

Thanks