Creating Change Documents for a Specific Field of a Database Table

In this blog i will try to demonstrate how you can track any change in a particular field of a database table. As you may already know, in some cases it is very crucial to see the change history of fields. So that we can analyze any problem easier and also, we can see what happened before the case that we are trying to analyze and find out the root cause.

Let’s start with creating a very simple database table and determine one of the fields inside it as trackable. By this way the changes (including creation and deletion) will be recorded in a particular table. (We will see which table it is 😊)

I won’t bother you with creating a table step by step and I will just display the database table I have created. ( Below picture ↓ )

In this table there will be some data of employees and we will track the changes in the field ‘MAIL’. Whenever a new employee is created, or an existing one is deleted or the mail of this existing employee is updated, we will be able to see these changes.

For this purpose the next step is, going inside the data element that we have used for the field ‘MAIL’ and checking the checkbox ‘Change Document’. ( Below picture ↓ )

So, by this way our journey of tracking the changes for this field has started. Right after this step we need to create a ‘Change Document Object’. To create it we go to the T-Code SCDO, write any name starting with ‘Y’ or ‘Z’ and then press the create button. ( Below picture ↓ )

Next, we write down the name of the table in which there is at least one field that we want to track the changes of it. We had created the table ZMC_TABLE_001 for this purpose. Now the only thing we need to do is, writing down the name of the table, checking all 3 checkboxes and then pressing the ‘Generate’ button. (Tricky point: if we don’t check these checkboxes, then we will be able to see only the changes for an entry which already exists. We won’t be able to see any old and new values during any creation or deletion. But it is also important to see with which value inside the MAIL field was the entry initially created or when it was deleted, what was the last value of this MAIL field.) ( Below picture ↓ )

If this screen appears please press the ‘Yes’ button. ( Below picture ↓ )

In the upcoming screen i give the name of the Change Object (ZMC_CDO_001) also to the field ‘Function Group’. (If you have another function group, you can also use it.) Also i change the letter ‘Y’ with the letter ‘Z’ since i am used to it. Then i press the ‘Generate’ button. ( Below picture ↓ )

I press the ‘Yes’ button. ( Below picture ↓ )

All i need is, to activate the generated Change Document by pressing the ‘Activate’ button. ( Below picture ↓ )

As you can see, everything has been created and activated. (Please pay attention to the Function Module ZMC_CDO_001_WRITE_DOCUMENT and the structure ZZMC_TABLE_001. Because these objects will be used inside the report.) ( Below picture ↓ )

Up to this point, half of the work is successfully done. In the next part I will just track any kind of changes in the ‘MAIL’ field. Let’s create a simple report and preserve the changes which occur in case of insert, update or delete activities. Here is the report that I will use to create, update, or delete an entry. ( Below code sample ↓ )

REPORT zmc_rep_001.

SELECTION-SCREEN BEGIN OF BLOCK a1 WITH FRAME TITLE text-001 NO INTERVALS.

PARAMETERS: p_id    TYPE zmc_id,

            p_name  TYPE zmc_name,

            p_sname TYPE zmc_sname,

            p_mail  TYPE zmc_mail,

            p_del   AS CHECKBOX.

SELECTION-SCREEN END OF BLOCK a1.

DATA: gs_old       TYPE zmc_table_001,

      gs_new       TYPE zmc_table_001,

      gt_table_old TYPE TABLE OF zzmc_table_001,

      gt_table_new TYPE TABLE OF zzmc_table_001,

      gt_cdtxt     TYPE TABLE OF cdtxt,

      gt_cdpos     TYPE cdpos_tab,

      go_alv       TYPE REF TO cl_salv_table,

      gv_process.

IF p_id IS INITIAL.

  MESSAGE 'Please enter a valid ID.' TYPE 'S' DISPLAY LIKE 'E'.

  RETURN.

ENDIF.

IF p_del IS NOT INITIAL.

  SELECT SINGLE * FROM zmc_table_001 INTO gs_old WHERE id = p_id.

  IF gs_old IS NOT INITIAL.

    DELETE FROM zmc_table_001 WHERE id = p_id.

    gv_process = 'D'.

  ELSE.

    MESSAGE 'No entry exists with the given ID.' TYPE 'S' DISPLAY LIKE 'E'.

  ENDIF.

ELSE.

  SELECT SINGLE * FROM zmc_table_001 INTO gs_old WHERE id = p_id.

  IF gs_old IS NOT INITIAL.

    UPDATE zmc_table_001 SET name    = p_name

                             surname = p_sname

                             mail    = p_mail WHERE id = p_id.

    gs_new-id      = p_id.

    gs_new-mail    = p_mail.

    gs_new-name    = p_name.

    gs_new-surname = p_sname.

    gv_process = 'U'.

  ELSE.

    gs_new-id      = p_id.

    gs_new-name    = p_name.

    gs_new-surname = p_sname.

    gs_new-mail    = p_mail.

    INSERT zmc_table_001 FROM gs_new.

    gv_process = 'I'.

  ENDIF.

ENDIF.

IF gs_new IS NOT INITIAL.

  gs_new-mandt = sy-mandt.

  APPEND gs_new TO gt_table_new.

ENDIF.

IF gs_old IS NOT INITIAL.

  gs_old-mandt = sy-mandt.

  APPEND gs_old TO gt_table_old.

ENDIF.

CALL FUNCTION 'ZMC_CDO_001_WRITE_DOCUMENT'

  EXPORTING

    objectid           = 'ZMC_CDO_001'

    tcode              = sy-tcode

    utime              = sy-uzeit

    udate              = sy-datum

    username           = sy-uname

    upd_zmc_table_001  = gv_process

  TABLES

    icdtxt_ZMC_CDO_001 = gt_cdtxt

    xzmc_table_001     = gt_table_new

    yzmc_table_001     = gt_table_old.

CALL FUNCTION 'CHANGEDOCUMENT_READ_ALL'

  EXPORTING

    I_OBJECTCLASS              = 'ZMC_CDO_001'

    I_TABLENAME                = 'ZMC_TABLE_001'

  IMPORTING

    ET_CDPOS                   = gt_cdpos

  EXCEPTIONS

    MISSING_INPUT_OBJECTCLASS  = 1

    MISSING_INPUT_HEADER       = 2

    NO_POSITION_FOUND          = 3

    WRONG_ACCESS_TO_ARCHIVE    = 4

    TIME_ZONE_CONVERSION_ERROR = 5

    READ_TOO_MANY_ENTRIES      = 6

    OTHERS                     = 7.

 cl_salv_table=>factory( IMPORTING r_salv_table = go_alv

                         CHANGING  t_table      = gt_cdpos ).

 go_alv->display( ).

In this report, user can delete or update an existing entry, or create a new one. In all 3 cases, change documents will be created and stored in the standard table CDPOS via the function module ‘ZMC_CDO_001_WRITE_DOCUMENT’ which was created during the generation of the change object ‘ZMC_CDO_001’. Using the last function module ‘CHANGEDOCUMENT_READ_ALL’, all change documents can be extracted from the table CDPOS.

Let’s test our report (create, update, and delete an entry) and see the result.

◉ Create an entry. ( Below picture ↓ )

Since a new entry was created in the table ZMC_TABLE_001, also a change document was created for the change in the MAIL field of this new entry. (Please see that the change indicator is ‘J’ which refers to Insert.) ( Below picture ↓ )

◉ Update an entry. ( Below picture ↓ )

Since an existing entry was updated in the table ZMC_TABLE_001, also a change document was created for the update in the MAIL field of this existing entry. (Please see that the change indicator is ‘U’ which refers to Update.) ( Below picture ↓ )

◉ Delete an entry ( Below picture ↓ )

Since an existing entry was deleted in the table ZMC_TABLE_001, also a change document was created for the deletion in the MAIL field of this deleted entry. (Please see that the change indicator is ‘E’ which refers to Delete.) ( Below picture ↓ )

Important point: The ‘Change Indicator’ field is a very strong signal to understand what happened inside the database table. Additional to Change Indicator field, the ‘Table Key’ field helps us to see the value of key field (or fields if multiple) for the processed entry. At this point we need to understand the difference between 2 cases. The first one is deleting an entry entirely, the second one is updating an existing entry with a blank in the MAIL field. The Change Indicator is the only difference we will see.

Let’s see with an example in which I will create a new entry first and update it with blank MAIL field. First create a new entry. ( Below picture ↓ )

The new entry was created. ( Below picture ↓ )

Now I will update this entry with a blank MAIL field. ( Below picture ↓ )

The entry was updated. Please pay attention to the difference between the lines 3 and 5. From the line 3 it is obvious that an entry, whose key field was ‘123’, was deleted and the MAIL field of this deleted entry was ‘johndoe_new@example.com’. But from the line 5 we can understand that an entry, whose key field was ‘124’, was updated and the MAIL field of this entry was changed from ‘johndoe@example.com’ to blank. ( Below picture ↓ )

Summary: I will try to summarize all the steps one by one, so you can easily follow them.

• Determine the field (or fields) for which you want to create change documents and check the checkbox ‘Change Document’ inside the field’s data element.
• Go to the transaction code SCDO.
• Write a new name of Change Document Object and press Create button.
• Add the table name in which there exists the field that you want to track the changes of it and then click all the checkboxes at the right side of the table name.
• Press Generate button.
• If needed press Yes button.
• Use the name of Change Document Object also as the name Function Group. (Optional, you can use another Function Group also.).
• Press the Yes button.
• Activate everything.
• Create, update or delete an entry in the database table and after doing the change create a Change Document using the function module, which was created for you. (ZMC_CDO_001_WRITE_DOCUMENT).
• Read the Change Document using the function module CHANGEDOCUMENT_READ_ALL.