How to do validation in Dynamic table update program?

[Former Member]

Hi Experts,

I have a requirement to update any table by giving the table name, and an input file with table primary key and the field value need to update.

I am updating the table using the below code. Here I am getting the Primary keys and fields of the table using FM CACS_GET_TABLE_FIELDS.

But how can I check the full primary key of the table is available in the input file and corresponding entry is present in the table?

*--- Selection Screen
PARAMETERS: p_table TYPE dd02l-tabname OBLIGATORY.
PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY MEMORY ID ysm.

*--- Declarations for dynamic data
DATA gt_data TYPE REF TO data.
DATA GS_DATA TYPE REF TO data.

FIELD-SYMBOLS: <ft_data> TYPE STANDARD TABLE,
<FS_DATA> TYPE ANY.

START-OF-SELECTION.
*--- Create dynamic internal table
CREATE DATA gt_data TYPE TABLE OF (p_table).
ASSIGN gt_data->* TO <ft_data>.
*--- Create dynamic work area
CREATE DATA gs_data TYPE (p_table).
ASSIGN gs_data->* TO <fs_data>.
*--- Get Excel data in internal table
PERFORM f_convert_excel.
*--- Upload data to database table
PERFORM f_upload_table.
END-OF-SELECTION.

*&---------------------------------------------------------------------*
*& Form F_CONVERT_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM f_convert_excel .

DATA lt_raw_data TYPE truxs_t_text_data.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = lt_raw_data
i_filename = P_file
TABLES
i_tab_converted_data = <FT_DATA>
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
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. " F_CONVERT_EXCEL

*&---------------------------------------------------------------------*
*& Form upload_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM f_upload_table.
SET UPDATE TASK LOCAL. " Switch to local update
LOOP AT <ft_data> ASSIGNING <fs_data>.
MODIFY (P_TABLE) FROM <FS_DATA>.
IF SY-SUBRC = 0.
GV_SUCCESS = GV_SUCCESS + 1.
ELSE.
delete <ft_data> index sy-tabix.
GV_ERROR = GV_ERROR + 1.
ENDIF.
ENDLOOP.
ENDFORM. "upload_data

Regards,

Dipin

[Former Member]

hi dipin

This fm can be used to get primary key of any table fm 'BDL_DDIF_TABL_GET'

[Former Member]

Hi Abdul Raheem,

Yes we can use FM 'BDL_DDIF_TABL_GET’ or ‘CACS_GET_TABLE_FIELDS’ to get the primary keys of the table. But my requirement is,

I am giving the data need to modify in an excel sheet and reading it into internal table <FT_DATA>.

Now how can I validate the given data (<FT_DATA>) is available in the table?

For example I need to update table FKKVKP, the primary keys of the table are VKONT and GPART and I need to update the Contract account name (VKBEZ) from Dummy Acc to Dummy Acc2.

But before changing this I need to check the given entry in the file (<FT_DATA>) is available in table FKKVKP i.e. the given VKONT and GPART is present in the table FKKVKP.

How can I do this validation?

Regards,

Dipin

[jayanthi_jayaraman]

Hi,

Once you get the internal table filled by using 'TEXT_CONVERT_XLS_TO_SAP', compare it with FM CACS_GET_TABLE_FIELDS , T_KEYFIELD parameter. If the internal table is filled with fields mentioned in T_KEYFIELD, proceed further in updating.

But for checking whether the respective entry is already present, you may need to write select statement again. Rather than that, you can use modify statement which will automatically modify if the key values are already present.

But if your requirement is not to update the existing record, then you need to get all the entries from your database and do comparison with internal table key field using loop and delete the internal table records if it is in database already and then use insert statement for inserting the records which are not available.

I hope you are handling only custom tables here.

[Former Member]

Hi Jayanthi,

Thanks a lot for the reply. But how can I write a select statement here? I need to update different tables at different times. How can I write the where condition here (because where condition will be changing for one table to another)? Is there any dynamic way to this? Could you please help me with the logic of that select statement.

Regards,

Dipin

[jayanthi_jayaraman]

Hi Dipin,

F4_CONV_SELOPT_TO_WHERECLAUSE FM which help you to convert the value to dynamically.

Populate I_SELOPT as follows

I_SELOPT-SHLPNAME = You need to fill table name

I_SELOPT-SHLPFIELD      = you need to fill field name

I_SELOPT-SIGN           = I
I_SELOPT-OPTION         = EQ
I_SELOPT-LOW            = your internal table value for key field

APPEND  I_SELOPT.

Use FM 'F4_CONV_SELOPT_TO_WHERECLAUSE', pass I_selopt and get v_where.
 
Here V_WHERE will have the dynamic where clause.

   SELECT * FROM (fill table name dynamically)
             INTO table itab WHERE (V_WHERE).

Bu this way, you can check the entries in table with excel.

[Former Member]

Hi Jayanthi / Vnkat,

The Dynamic where condition creation is working. Can I please have one more help.

Here I am getting the number of key fields in to variable lv_key_cnt.

<ft_data> is having the data need to update in the table. I am looping <ft_data> into <fs_data>.

Here gs_keyfield-fieldname will be changing every time.

How can I move the <fs_data>-primary_key to gs_selopt_tab-low?

DESCRIBE TABLE gt_keyfield LINES lv_key_cnt.

LOOP AT <ft_data> ASSIGNING <fs_data>.

DO lv_key_cnt TIMES.
READ TABLE gt_keyfield INTO gs_keyfield INDEX sy-index.
gs_selopt_tab-shlpname    = p_table.
gs_selopt_tab-shlpfield   = gs_keyfield-fieldname.
gs_selopt_tab-sign        = 'I'.
gs_selopt_tab-option      = 'EQ'.
* gs_selopt_tab-low                                    = <fs_data>-(gs_keyfield-fieldname).

APPEND gs_selopt_tab TO gt_selopt_tab.
ENDDO.

ENDLOOP.

Regards,

Dipin

[jayanthi_jayaraman]

Hi,

This is your value from excel to internal table.

[VenkatRamesh_V]

Hi,

" Getting No of key fields using FM

DESCRIBE TABLE it_key LINES lv_cnt.                                                                

DO lv_cnt times.

READ TABLE it_key into wa_key index sy-index.

"  Getting Table name

ASSIGN COMPONENT 'TABNAME'  OF STRUCTURE wa_key to <tablename>.     

"  Getting Fieldname

ASSIGN COMPONENT 'FIELDNAME'  OF STRUCTURE wa_key to <val2>.           

IF  <val2> IS ASSIGNED.

" Excel content  Internal Table

LOOP AT <ft_data> ASSIGNING <fs_data>.        

" matching  field name with excel field name                                            

ASSIGN COMPONENT <val2> OF STRUCTURE <fs_data>     to <val1>.      

  " getting excel value for the field name.   

if <val1> IS  ASSIGNED.                                                                           

" Logic to append table name fieldname and  field value.                                                                                 

endif.

ENDLOOP.

ENDIF.

ENDDO.

Hope it helpful.

Regards,

VenkatRamesh.V

[VenkatRamesh_V]

Hi Dipin,

This FM  CACS_GET_TABLE_FIELDS

Table T_KEYFIELD  Content of the  field  FIELDNAME(Key fields  )  are stored in column wise.

Data lv_cnt type i.

Field-symbols : <val1> type any,

                        <val2> type any.

Describe table t_keyfield lines lv_cnt.  "Considering mara table  2 key fields.

DO lv_cnt times.

READ TABLE it_key into wa_key index sy-index.      "mandt.

ASSIGN COMPONENT 'FIELDNAME'  OF STRUCTURE wa_key to <val2>.

IF  <val2> IS ASSIGNED.

LOOP AT <ft_data> ASSIGNING <fs_data>.

ASSIGN COMPONENT <val2> OF STRUCTURE <fs_data>     to <val1>.

if <val1> IS NOT ASSIGNED.                              " Blank fields.

delete <ft_data> index sy-tabix.

endif.

ENDLOOP.

ENDIF.

ENDDO.

Hope it helpful.

Regards,

Venkat.

[Former Member]

Hi Venkat,

Thanks a lot for the reply. Using this method we can check all the key fields of the table are given in the input file and it is not blank. Am I right? 

But how can I write a select statement here? I need to update different tables at different times. How can I write the where condition here (because where condition will be changing for one table to another)? Is there any dynamic way to this? Could you please help me with the logic of that select statement.

Regards,

Dipin

[paul_bakker2]

Hi,

Is this requirement for custom (Z) tables or SAP standard tables?

This is important, as it affects the suggested solution.

thanks

Paul

[Former Member]

Hi Paul,

The requirement is to edit some of the custom and standard transparent tables.

Regards,

Dipin

[paul_bakker2]

Is there any plan to use this program in a Production system? It doesn't sound like a good idea.

cheers

Paul

[matt]

Paul Bakker wrote:

Is there any plan to use this program in a Production system? It doesn't sound like a good idea.

cheers
Paul

It is an extraordinarly BAD idea!

If such a program is used to put data into standard SAP table you'll easily end up with a corrupted (and unusable) SAP system.

It is an extraordinarly BAD idea!

Without all kinds of safeguards and authorisation checks, a malicious user could use such a program to wreck your system. Also, an incompetent user of your system could also wreck it.

Frankly, the OP does not really have a requirement for a program to update any table from a spreadsheet or text file. It's just that someone somewhere thinks (incorrectly) that it's a good idea. Which it isn't.

[paul_bakker2]

I'll second that (assuming it is meant to be used in Production, and not some sandpit):

• Updating standard SAP tables is a bad idea.
• Writing a program to update standard SAP tables is an even worse idea.
• Writing a program to dynamically update standard SAP tables is the worst idea I've seen all year.

But then, this may be just a sandpit hack, a student assignment, or a gedankenexperiment.

What say you, dipin?

cheers

Paul

[Former Member]

Hi Paul, Matthew,

Mainly we are developing this program to update the custom tables and to update address field in one standard table, where some fields are updated wrongly during migration.

We are restricting this update only to very limited number of tables and we are assuring there are no critical tables and data are updating using this program. Also this program will be accessible only to very restricted number of SAP functional consultants.

Regards,

Dipin

[matt]

Great. That's what I like to here. Frankly though - if you're only updating a limited number of tables, then you should write one program for each table. It'll be far easier, less error-prone, quicker, cheaper and far more secure.

[Former Member]

Thanks every one for your help and advices

Regards,

Dipin