Get old and new values from DBTABLOG table

[former_member578544]

Hi,

I am developing a report to display all changes to some fields of PKHD table over a date range. CDHDR & CDPOS do not capture the changes while DBTABLOG does. But the variable key field (LOGDATA) in DBTABLOG does hold encrypted values which need to be decrypted. Is there any FM or a way out to get them ?

Please let me know. Thanks a lot.

Regards

Neeraj

[former_member181962]

Try using the FM:

DBLOG_READ_TABLE

[raymond_giuseppi]

Use DBLOG_READ and then work with the data like in the following sample

* Constants (cf. SAP RSVTPTOP)
  CONSTANTS: type_i4 LIKE x031l-fieldtype VALUE 'AC',       "UF160698B
             type_i2 LIKE x031l-fieldtype VALUE 'A8',
             type_f  LIKE x031l-fieldtype VALUE '88'.       "UF160698E
* First - informations from directory
        REFRESH fld_list.
        CALL FUNCTION 'GET_FIELDTAB'
          EXPORTING
            langu                     = sy-langu
            only                      = ' '
            tabname                   = 'TEVEN'
            withtext                  = 'X'
*       IMPORTING
*         HEADER                      =
*         RC                        =
          TABLES
            fieldtab                  = fld_list
          EXCEPTIONS
            internal_error            = 1
            no_texts_found            = 2
            table_has_no_fields       = 3
            table_not_activ           = 4
            OTHERS                    = 5.
        LOOP AT fld_list INTO fld WHERE keyflag = 'X'.
          ADD fld-intlen TO keylen.
        ENDLOOP.
* Then extract data log
        REFRESH obj_list.
        obj-tab = 'TEVEN'.
        INSERT obj INTO  TABLE obj_list.
        CALL FUNCTION 'DBLOG_READ'
          EXPORTING
            from_day                   = s-aedtm-low
*           FROM_TIME                  = '000000'
            to_day                     = s-aedtm-high
*           TO_TIME                    = '235959'
            obj_list                   = obj_list
*         ACCESS_DATABASE            = 'X'
*         ACCESS_ARCHIVES            = ' '
*         AUTO_ARCH_SEL              = ' '
*         USER_LIST                  =
          CHANGING
            log_list                   = log_list
          EXCEPTIONS
            archive_access_error       = 1
            no_archives_found          = 2
            OTHERS                     = 3.
*   Extract data from returned tables
        LOOP AT log_list INTO log.
          LOOP AT fld_list INTO fld.
            IF fld-keyflag = 'X'.
              ASSIGN log-logkey+fld-offset(fld-intlen)
                TO <hexa> TYPE 'X'.
            ELSE.
              fld-offset = fld-offset - keylen.
              ASSIGN log-logdata+fld-offset(fld-intlen)
                TO <hexa> TYPE 'X'.
              fld-offset = fld-offset + keylen.
            ENDIF.
            CASE fld-inttype.
              WHEN 's'.
                f_type = type_i2.
              WHEN 'I'.
                f_type = type_i4.
              WHEN 'F'.
                f_type = type_f.
            ENDCASE.
            IF 'sIF' CS fld-inttype.
              feld = <hexa>.
              CALL FUNCTION 'DB_CONVERT_FIELD_TO_HOST'
                   EXPORTING
                        type        = f_type
                   CHANGING
                        field       = feld
                   EXCEPTIONS
                        wrong_param = 1
                        OTHERS      = 2.
              ASSIGN feld TO <hexa> TYPE 'X'.
            ENDIF.
            ASSIGN <hexa> TO <char> TYPE 'C'.
            teven+fld-offset(fld-intlen) = <char>.
          ENDLOOP.
*     Here structure teven is filled
        ENDLOOP.

Regards

[patrick_kelly3]

I recommend using field symbols instead of the offset technique. Below, I am expanding a single record from DBTABLOG for a custom table called ZZ1099. Note that DBTABLOG records don't give the "before and after" value -- only the "before" values. You have to combine them yourself.

FORM expand_logdata_line  USING p_line_in  TYPE dbtablog

                       CHANGING p_line_out TYPE gty_log.

  DATA:

    ls_1099   TYPE zz1099,

    lr_conv   TYPE REF TO cl_abap_conv_in_ce,

    lf_xdata  TYPE xstring.

  FIELD-SYMBOLS:

    <lfs_log>  TYPE dbtablog,

    <lfs_data> TYPE any,

    <lfs_desc> TYPE dfies.

  lr_conv = cl_abap_conv_in_ce=>create( ).

  DO.

    ASSIGN COMPONENT sy-index OF STRUCTURE ls_1099 TO <lfs_data>.

    IF sy-subrc NE 0.

      EXIT.

    ENDIF.

    READ TABLE gt_fldlist INDEX sy-index ASSIGNING <lfs_desc>.

    IF sy-subrc NE 0.

      MESSAGE e999(zj) WITH 'Field' sy-index 'description not found'.

    ENDIF.

    IF <lfs_desc>-keyflag EQ 'X'.

*     Extract ZZ1099 key field contents from logkey field of log structure

      <lfs_data> = p_line_in-logkey+<lfs_desc>-offset(<lfs_desc>-intlen).

    ELSE.

*     Extract ZZ1099 regular field contents from logdata field of log structure

      lf_xdata = p_line_in-logdata+<lfs_desc>-offset(<lfs_desc>-intlen).

      lr_conv->convert(

                 EXPORTING

                   input = lf_xdata

                 IMPORTING

                   data  = <lfs_data> ).

    ENDIF.

  ENDDO.

  MOVE-CORRESPONDING:

    p_line_in TO p_line_out,

    ls_1099   TO p_line_out.

ENDFORM.                    " EXPAND_LOGDATA_LINE

[Former Member]

Hi Patrick,

i am not Abap coder that's why forgive me i ask stupidly.

can you define please gty_log and gt_fldlist ? i mean in an example.

regards

[patrick_kelly3]

Not stupid at all!

GTY_LOG is a custom data type (structure) that I defined earlier in the program. It looks like this:

*   Log record - including ZZ1099 field contents

   BEGIN OF gty_log,

     logid         TYPE dbtablog-logid,       " Key field for DBTABLOG record

     logkey        TYPE dbtablog-logkey,      " Key fields of ZZ1099 record

     logdate       TYPE dbtablog-logdate,

     logtime       TYPE dbtablog-logtime,

     username      TYPE dbtablog-username,    " User ID of person who made change

     optype        TYPE dbtablog-optype,      " (U)pdate / (I)nsert / (D)elete

*   1099 fields - to be extracted from DBTABLOG-LOGDATA:

     zbukrs        TYPE zz1099-zbukrs,        " Company Code

     zlifnr        TYPE zz1099-zlifnr,        " Vendor number

     ztype         TYPE zz1099-ztype,         " Withholding Tax Code is a.ka. 1099 Type

     zfrom_date    TYPE zz1099-zfrom_date,    " Validity start - expected 01/01/20XX

     zto_date      TYPE zz1099-zto_date,      " Validity end   - excepted 12/31/20XX

     zcomp_name    TYPE zz1099-zcomp_name,    " Company name

     zcomp_tax_id  TYPE zz1099-zcomp_tax_id,  " Company Tax number

     zchuser       TYPE zz1099-zchuser,       " User who made last change

     zchdate       TYPE zz1099-zchdate,       " Date of last change

     zchtime       TYPE zz1099-zchtime,       " Time of last change

     zpruser       TYPE zz1099-zpruser,       " User who last printed

     zprdate       TYPE zz1099-zprdate,       " Date last printed

     zprtime       TYPE zz1099-zprtime,       " Time last printed

     zregion       TYPE zz1099-zregion,       " Region code (not a state abbreviation)

     zvend_name    TYPE zz1099-zvend_name,    " Vendor name

     zaddress1     TYPE zz1099-zaddress1,     " Address line 1

     zaddress2     TYPE zz1099-zaddress2,     " Address line 2

     zaddress3     TYPE zz1099-zaddress3,     " Address line 3

     zcity         TYPE zz1099-zcity,         " City

     zstate        TYPE zz1099-zstate,        " State

     zzip_code     TYPE zz1099-zzip_code,     " ZIP code

     zstatus       TYPE zz1099-zstatus,       " Status - (A)ctive or (I)nactive

     ztax_id       TYPE zz1099-ztax_id,       " Vendor tax number

     zext_1099_amt TYPE zz1099-zext_1099_amt, " Extracted amount

     zact_1099_amt TYPE zz1099-zact_1099_amt, " Actual amount

     zext_ytd_amt  TYPE zz1099-zext_ytd_amt,  " Extracted YTD amount

     zexuser       TYPE zz1099-zexuser,       " User who last extracted

     zexdate       TYPE zz1099-zexdate,       " Date last extracted

     zextime       TYPE zz1099-zextime,       " Time last extracted

   END OF gty_log.

Note that the first six fields in this structure are based on the standard SAP table DBTABLOG. However, the subsequent fields are based on the ZZ1099 table, which is a custom table that only exists in my company's system. In this example, I turned on change logging for the ZZ1099 table, and I am analyzing the changes to that table. That's why I included those fields.

GT_FLDLIST was also defined earlier in the program. It has type DFIES_TAB, and it describes the fields of the ZZ1099 table. I populate it using the following code:

* Get description of ZZ1099 fields

   gt_zz1099_fieldlist  =  CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( 'ZZ1099' ) )->get_ddic_field_list( ).

If you use this code, replace 'ZZ1099' with the name of the custom table whose changes you are concerned with.

I hope that helps a little.

[Former Member]

thank you so much Patrick ,

i know it will sound stupid again but this time i couldn't give any meaning of
gt_tablename_fieldlist = CAST cl_abap_structdesc (cl_abap_typedescr =>descibe_by_name('tablename'))->get_ddic_field_list().

i have defined gt_ldlist type as dfies_tab like you wrote but gt_tablename_fieldlist this variable i couldnt understand. and also code with CAST program can not read.

thank you in advance.

Erdem

PS: like i wrote before i am quite bad at abap.

[patrick_kelly3]

I mistyped that second example. I meant to write GT_FLDLIST, not GT_ZZ1099_FIELDLIST.

Concerning the code to populate GT_FLDLIST, I notice the code in your question doesn't match what I posted. It should say

CAST cl_abap_structdescr(

but you have written

CAST cl_abap_structdesc (

Also, the CAST operator is relatively new, and if you have a version of SAP prior to EhP 7 (I think), it may not be available. If that is the case, you can use the following to accomplish the same thing:

  DATA:

    gr_structdescr TYPE REF TO cl_abap_structdescr.

  TRY.

    gr_structdescr ?= cl_abap_typedescr=>describe_by_name( 'ZZ1099' ).

  CATCH cx_sy_move_cast_error.

*    Parameter suppplied to DESCRIBE_BY_NAME does not describe a structure

  ENDTRY.

  gt_fldlist = gr_structdescr->get_ddic_field_list( ).

[former_member774352]

Hey,

What type is <hexa> and <char>?

This piece of code doesn't have any data declaration in it.

Thanks.

[matt]

slipknot94 I'm guessing Raymond thought it was pretty obvious what the types need to be. E.g. FIELD-SYMBOLS <hexa> TYPE x.

The post is fifteen years old. Nowadays you might like to use

ASSIGN log-logkey+fld-offset(fld-intlen) TO FIELD-SYMBOLS(<hexa>) TYPE 'X'.

[raymond_giuseppi]

Nowadays you could also submit report RSVTPROT (the reports behind SCU3) with parameter alv_grid set and get the ALV data back (many threads/discussions already on class CL_SALV_BS_RUNTIME_INFO)

Actually in this old report of mine, definition of field-symbols was short...

  FIELD-SYMBOLS: <hexa>, <char>, <time>.

(Every other field definition can be find in the FM which use them)

So I already used code such as (explicit TYPE in the ASSIGN statement) as posted in my answer, nowadays add the brackets for a dynamic definition as matthew.billingham wrote.

FIELD-SYMBOLS: <hexa>. " in an Abap World far far away
ASSIGN log-logdata+fld-offset(fld-intlen) TO <hexa> TYPE 'X'. " once upon a time

ASSIGN log-logdata+fld-offset(fld-intlen) TO (<hexa>) TYPE 'X'. " nowadays

[former_member578544]

Thank a lot for all the help. It has helped me. Thanks again.

[Former Member]

Hi Neeraj ,

i have similar need .

wher i need to read new and old values from a 'Z' table .

i have ticked the option 'LOGTABLECHANGE' in technical settings.

But now i can see changes in tcode 'SCU3' but i want to create a program so that i can see the changes in the program output by giving the table name and date time in the selection screen of the program .

can you provide ur program .

Thnaks .

[former_member578544]

Solved.

[Former Member]

Hi Neeraj,

I was hoping if you could share with me how you implemented the code. I also have the same requirement right now and I really need your help. Anyway, I understand the logic of the sample code posted above. The only problem I have is how to declare the variables used in the sample code. Example, the fld_list, fld, obj_list and etc. I am having some difficulty in identifying on how to type these variables. I would much appreciate if you can share a working program which has this functionality.

I hope you can help me with this.

Thanks.

~ Ric

[Former Member]

Hi Neeraj,

I am also facing the same issue.

Could you please tell me how you got the new and old values from FM READ_DBLOG?

Thanks,

Menka

[Former Member]

Hello,

We can use the FM  /BEV2/ED_READ_DBLOG to get the detail information.

Thanks

Ritesh

[rgore]

There is already a Transaction called SCU3 to analyze the Table Data Change Logs. So why do you want to create your own report?