topic Re: Interpreting DBTABLOG entries in Application Development and Automation Discussions

Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 09:55:31 GMT

Hi,

I need to write a report which lists the changes to two SAP tables QPMK,CRHD through Tcodes (QS21,QS23) and (CRQ1,CRQ2).

The change log is activated .

I knew that DBTABLOG contains some information about these changes.

Also, SCU3 is a Tcode where we can go and analyse the changes for a period of time.

report RSVTPROT also gives us the same in a better way.

>with all that said, I am developing a report whose logic should be on same grounds as report RSVTPROT. But RSVTPROT is program aimed at any table user chooses.This led to a very general code which is unnecessary for me.

The layout needed is totally different from that of list generated by executing RSVTPROT.

I need a way to get the information about the changes to the table , OLD_and_NEW values for the fields, user ...etc.

How Do I do that.

Please give me your valuable suggessions....

Thanks in advance.,

Sri..........

Message was edited by: Sri Velagapudi

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 11:12:40 GMT

Hi,

Have you tried the tables CDHDR and CDPOS? These store the document changes for most documents.

Try this:

In table CDHDR, try typing in the tcodes for which you want the changelog, and get the values of OBJECTCLAS and OBJECTID. Using these values you can select from CDPOS, and will get the old and new values for each of the changed variables.

Hope this helps.

Sudha

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 11:45:41 GMT

Hi,

I developed a similar report showing old and new

values for any changes on table field.

You can use dbtabprt,dbtablog table to get

old values and cdhdr cdpos table to get new values

of table field changes.

Regards

Amole

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 12:28:21 GMT

I am familiar with CDHDR and CDPOS .... They are not keeping track of the changes to these tables.

I think this is because, the ChangeLogs for these tables have been activated manually by us .

QPMK/CHDR are not those main tables (like MARA) which are activated and changes can be tracked in CDHDR/CDPOS.

I also saw that there are no entries in DBTABPRT.

Only DBTABLOG is keeping track of the changes, I donno how to get the values of changed fields and field names because of the way it stores.

Also I checked couple of Func modules like DBLOG_READ. I did not find a F-Module which will give what I need.

Thanks for your replies ....

Sri.

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 12:49:54 GMT

Hi,

Try following logic.

Get folowing details from DBTABLOG table

logdate,logtime,logid,

tablename,logkey(Contains your key data )

logdata(Contains non-key values but are stored in

hexadecimal format)

1) Use nametab_get function module to get all key fields

2) Build dynamic select statement

if tablename = 'QPMK'

( To get new value)

select * from

into table it_qpmk

from table QPMK

where <key fields> = logkey.

else

( To get new value)

select * from

into table it_crhd

from table crhd

where <key fields> = logkey.

I am assuming that you want to display only change

entries of two table qpmk,crhd.

Regards

Amole

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 13:27:00 GMT

Amole,

I think your logic may give us values of only one change as we are going by the KEY (for QPMK/CHDR).

But the report is to give every change in the entries of the tables

1)By period (Begin date to End date

2)By username and then by the Key too..

This means, suppose an entry in QPMK has underwent 12 changes in a span of 4 months. The report should give OLD and NEW values on all 12 occurences when i choose those 4 months period. In the same manner , changes made by a user.

If I understood your logic in a wrong way, do let me know.

As its only two tables , I do know the key fields for them and that helps in avoiding the Function Module.....But thanks for informing me about one useful function module.......I just played with it....

---Thanks for all the effort and still seems we havent reached a solution.

I tried to do this since 2 days and then I posted it here.

Waiting for a solution..

Sri ,,,,

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 13:38:33 GMT

Hi,

Logic will track all the changes from_date to

to_date.

Select all the record from dbtablog table

where date in s_date.

loop at it_dblog.

for each log key
select from table

endloop.

I had same requirement in our project,but I had to

display changes to all table.I worked on

3.1 h version where dbtabprt table contained all

change information.In above verisons it is replaced

by dbtablog table.

I can send you sample code. give me ur personal

email ID(Code is of 3.1 H version just replace

the table with ur version).

Please try above logic you will get more idea.

Regards

Amole

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 14:24:52 GMT

OK I shall give it a shot and then reply you.

Sri.

Re: Interpreting DBTABLOG entries

Former Member — Mon, 16 Jan 2006 14:26:11 GMT

My email id is jagadsri@yahoo.com

I missed that in my previous reply.

Thanks Amole.

Re: Interpreting DBTABLOG entries

Former Member — Mon, 07 May 2007 20:37:14 GMT

Hi Amole,

Can you kindly also send me the same code in case you have it.

My email is karthik.boyapalli@gmail.com.

Regards,

Karthik

Re: Interpreting DBTABLOG entries

Former Member — Mon, 07 May 2007 20:47:08 GMT

I Used CDHDR and CDPOS Table for Material Changes

REPORT ZQA_MATERIAL_CHANGE_LIST no standard page heading line-size 132.

======================================================================

*
Program Name : ZQA_MATERIAL_CHANGE_LIST *
*
Description : NA *
*
Author : NA *
Date : NA *
*

----

MODIFICATION HISTORY *

----

DATE | AUTHOR | CHANGE # | DESCRIPTION OF MODIFICATION *

|
|
|--

02/07/07| Seshu | DEVK921926 | Date and Time as Ranges *
02/19/07| Seshu | DEVK922062 | added Material number as range *

************************************************************************

======================================================================

tables: bdcp, mara, makt.

data: i_cdhdr like cdhdr occurs 0 with header line,

i_cdshw like cdshw occurs 0 with header line.

SELECTION SCREEN ELEMENTS ------------------------------------------*

SELECTION-SCREEN BEGIN OF BLOCK one

WITH FRAME TITLE text-001.

Select-options: s_datum for sy-datum obligatory.

select-options : s_time for sy-uzeit.

Start of change Seshu 02/19/2007
Reason - Added Material Number as Range Option

*select-options : p_matnr for mara-matnr.

select-options s_matnr for mara-matnr.

select-options: S_usernm for sy-uname.

SELECTION-SCREEN END OF BLOCK one.

SELECTION-SCREEN BEGIN OF BLOCK two

WITH FRAME TITLE text-002.

parameters: r_all radiobutton group one default 'X',

r_erps radiobutton group one,

r_select radiobutton group one.

SELECTION-SCREEN: BEGIN OF LINE, POSITION 5.

parameter p_fname like bdcp-fldname.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK two.

ranges r_objectid for cdhdr-objectid.

List of fields transmitted to ERPsy-Daisy

select-options so_fname for i_cdshw-fname no-display.

START OF SELECTION --------------------------------------------------*

START-OF-SELECTION.

if r_select is initial.

clear p_fname.

endif.

perform set_erpsy_daisy_fields.

perform get_data.

perform write_report.

SUBROUTINES ---------------------------------------------------------*

&----

*& Form get_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM get_data.

data: v_objectid like cdhdr-objectid.

move p_matnr to v_objectid.

loop at s_matnr.

move : s_matnr-low to r_objectid-low,

s_matnr-high to r_objectid-high,

s_matnr-option to r_objectid-option,

s_matnr-sign to r_objectid-sign.

append r_objectid.

endloop.

**if v_objectid is initial.

*select * from cdhdr into table i_cdhdr

where USERNAME in s_usernm
and OBJECTCLAS = 'MATERIAL'
and UDATE in s_datum
and utime in s_time.

*else.

select * from cdhdr into table i_cdhdr

where OBJECTID in r_objectid

and USERNAME in s_usernm

and OBJECTCLAS = 'MATERIAL'

and UDATE in s_datum

and utime in s_time.

*endif.

if sy-subrc ne 0.

message e000(zwave) with 'No Data Found for given Selection'.

endif.

ENDFORM. " get_data

&----

*& Form write_report

&----

text

----

--> p1 text
<-- p2 text

----

FORM write_report.

data: v_fname(60) type c,

v_changenr like i_cdhdr-changenr,

v_count type i value 0.

sort i_cdhdr by tcode changenr.

list materials created ---------------------------------------

if not s_datum-high is initial.

uline.

format color col_heading intensified on.

write: / sy-vline,

'Materials created since',

25 s_datum-low ,39 'to',43 s_datum-high,

132 sy-vline.

format color off.

uline.

else.

uline.

format color col_heading intensified on.

write: / sy-vline,

'Materials created since',

s_datum-low ,

132 sy-vline.

format color off.

ULINE.

endif.

loop at i_cdhdr where tcode = 'MM01' or

tcode = 'MM11'.

perform write_header_line using i_cdhdr.

endloop.

uline.

list materials deleted ---------------------------------------

if not s_datum-high is initial.

uline.

format color col_heading intensified on.

write: / sy-vline,

'Materials flagged for deletion since',

43 s_datum-low ,55 'to',59 s_datum-high,

132 sy-vline.

format color off.

uline.

else.

uline.

format color col_heading intensified on.

write: / sy-vline,

'Materials flagged for deletion since',

s_datum-low,

132 sy-vline.

format color off.

uline.

endif.

loop at i_cdhdr where tcode = 'MM06' or

tcode = 'MM16'.

perform write_header_line using i_cdhdr.

endloop.

uline.

display detail for material changes ---------------------------

skip.

if not s_datum-high is initial.

uline.

format color col_heading intensified on.

write: / sy-vline,

'Materials changed since',

28 s_datum-low ,39 'to',43 s_datum-high,

132 sy-vline.

format color off.

else.

uline.

format color col_heading intensified on.

write: / sy-vline,

'Materials changed since',

s_datum-low ,

132 sy-vline.

format color off.

endif.

display material change transactions (immediate and change)

loop at i_cdhdr where tcode = 'MM02' or

tcode = 'MM12'.

retrieve change document line items

perform get_change_positions using i_cdhdr-changenr.

display each item matching field on parameter screen, only
ERPsy-Daisy materials, or all fields if both paramters blank

v_count = 0.

loop at i_cdshw.

if ( p_fname is initial or

i_cdshw-fname = p_fname ) and

i_cdshw-fname in so_fname.

write header line if this is the first line item from change
order to meet above criteria

add 1 to v_count.

if v_count = 1.

if i_cdhdr-changenr <> v_changenr.

if sy-index <> 1.

uline.

endif.

perform write_header_line using i_cdhdr.

endif.

v_changenr = i_cdhdr-changenr.

write change line

concatenate '(' i_cdshw-fname ')'

into v_fname.

concatenate i_cdshw-ftext v_fname

into v_fname separated by space.

write: / sy-vline.

if i_cdshw-chngind = 'U'. "update

write 12 'Change >'.

elseif i_cdshw-chngind = 'D' or

i_cdshw-chngind = 'I'.

write 12 'Deleted >'.

elseif i_cdshw-chngind = 'I'.

write 12 'Inserted >'.

endif.

write: v_fname,

132 sy-vline.

write: / sy-vline,

16 'Old Value:',

(40) i_cdshw-f_old,

132 sy-vline,

/ sy-vline,

16 'New Value:',

(40) i_cdshw-f_new,

132 sy-vline.

endif.

endloop.

uline.

ENDFORM. " write_report

&----

*& Form write_header_line

&----

text

----

--> p1 text
<-- p2 text

----

FORM write_header_line using x_cdhdr like cdhdr.

data: v_date(10) type c,

v_time(8) type c.

get material description

select single maktx

from makt

into makt-maktx

where matnr = x_cdhdr-objectid.

format color col_heading intensified off.

write: / sy-vline,

x_cdhdr-objectid+8(10) no-zero,

(40) makt-maktx,

x_cdhdr-tcode,

x_cdhdr-username,

x_cdhdr-udate,

x_cdhdr-utime,

132 sy-vline.

format color off.

ENDFORM. " write_header_line

&----

*& Form get_change_positions

&----

text

----

--> p1 text
<-- p2 text

----

FORM get_change_positions using x_chgnbr.

CALL FUNCTION 'CHANGEDOCUMENT_READ_POSITIONS'

EXPORTING

ARCHIVE_HANDLE = 0

CHANGENUMBER = x_chgnbr

TABLEKEY = ' '
TABLENAME = ' '
IMPORTING
HEADER =

TABLES

EDITPOS = i_cdshw

EDITPOS_WITH_HEADER =
EXCEPTIONS
NO_POSITION_FOUND = 1
WRONG_ACCESS_TO_ARCHIVE = 2
OTHERS = 3

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. " get_change_positions

&----

*& Form set_erpsy_daisy_fields

&----

text

----

--> p1 text
<-- p2 text

----

FORM set_erpsy_daisy_fields.

if r_erps = 'X'.

move: 'I' to so_fname-sign,

'EQ' to so_fname-option,

'BISMT' to so_fname-low.

append so_fname.

move 'MATNR' to so_fname-low.

append so_fname.

move 'MAKTX' to so_fname-low.

append so_fname.

MVKE fields

move 'VKORG' to so_fname-low.

append so_fname.

move 'VTWEG' to so_fname-low.

append so_fname.

move 'VMSTA' to so_fname-low.

append so_fname.

move 'VMSTA' to so_fname-low.

append so_fname.

move 'VRKME' to so_fname-low.

append so_fname.

move 'MSTAV' to so_fname-low.

append so_fname.

MARA fields

move 'MTART' to so_fname-low.

append so_fname.

move 'MATKL' to so_fname-low.

append so_fname.

move 'MEINS' to so_fname-low.

append so_fname.

move 'EAN11' to so_fname-low.

append so_fname.

MARC fields

move 'MTVFP' to so_fname-low.

append so_fname.

move 'MSTDV' to so_fname-low.

append so_fname.

move 'PLIFZ' to so_fname-low.

append so_fname.

move 'WERKS' to so_fname-low.

append so_fname.

move 'ZZDEPT' to so_fname-low.

append so_fname.

move 'ZZATP1' to so_fname-low.

append so_fname.

move 'ZZATP2' to so_fname-low.

append so_fname.

move 'ZZATP3' to so_fname-low.

append so_fname.

move 'ZZATP4' to so_fname-low.

append so_fname.

move 'ZZATP5' to so_fname-low.

append so_fname.

move 'ZZATP6' to so_fname-low.

append so_fname.

move 'ZZATP7' to so_fname-low.

append so_fname.

MARM fields

move 'UMREN' to so_fname-low.

append so_fname.

move 'UMRES' to so_fname-low.

append so_fname.

endif.

ENDFORM. " set_erpsy_daisy_fields

Reward Points if it is Helpful

Thanks

Seshu