Skip to Content
Author's profile photo Jahnavi Teja Venati

Upload data from excel into Decision Table using BRF+ API’s

Hi All,

Let us upload values from excel into Decision table using the BRF+ API’S

Using the following code

PARAMETERS: ip_rule     TYPE rlgrapfilename OBLIGATORY.
CONSTANTSgc_appl_id   TYPE if_fdt_types=>id  VALUE ‘005056C000081EE58CD3919CB9A80957’,“Application GUID
            gc_dt_id    
TYPE if_fdt_types=>id  VALUE ‘001E671A4F281ED5BD97A46E4673360F’,“Decision Table GUID
            gc_xls      
TYPE string  VALUE ‘.xls’,
            gc_dir      
TYPE string  VALUE ‘c:\’,
            gc_*xls     
TYPE string  VALUE ‘*.xls’.


TYPES:BEGIN OF ts_msg_txt_data,
            level        
TYPE char24,
            age          
TYPE char24,
            pricing      
TYPE char24,
            base         
TYPE char24,
            currency_base
TYPE char24,
END OF ts_msg_txt_data.

DATAlt_files               TYPE                                      filetable,
       ls_files              
TYPE                                      file_table,
       iv_rcode              
TYPE                                      int4,
       iv_action             
TYPE                                      int4,
       lref_decision_table   
TYPE REF TO                         cl_fdt_decision_table,
       lref_factory          
TYPE REF TO                         if_fdt_factory,
       lt_excel              
TYPE STANDARD TABLE OF  ts_msg_txt_data,
       wa_excel              
TYPE                                      ts_msg_txt_data,
       lt_msg_data           
TYPE                                      if_fdt_decision_table=>ts_table_data,
       ls_msg_data           
LIKE LINE OF                          lt_msg_data,
       lv_row_no             
TYPE                                      int4,
       lts_range             
TYPE                                      if_fdt_range=>ts_range,
       ls_range              
LIKE LINE OF                         lts_range,
       lx_fdt                
TYPE REF TO                        cx_fdt,
       lt_message            
TYPE                                      if_fdt_types=>t_message,
       lv_message            
TYPE                                      string,
       lv_actv_failed        
TYPE                                      abap_bool.

FIELD-SYMBOLS: <lv_value>      TYPE          any,
               <lv_amount>    
TYPE          if_fdt_types=>element_amount,
               <ls_message>   
TYPE          if_fdt_types=>s_message.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR ip_rule.


CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
default_extension      
= gc_xls
file_filter            
= gc_*xls
initial_directory      
= gc_dir
CHANGING
file_table             
= lt_files
rc                     
= iv_rcode
user_action            
= iv_action
EXCEPTIONS
file_open_dialog_failed
= 1
cntl_error             
= 2
error_no_gui           
= 3
OTHERS                  = 4.
IF sysubrc <> 0.
    
MESSAGE WITH ‘file_open_dialog’.
ELSE.
READ TABLE lt_files INDEX 1 INTO ls_files.
IF sysubrc = 0.
     ip_rule
= ls_filesfilename.
ENDIF.
ENDIF.

START-OF-SELECTION.
**extraction of ceaser rules fom the excel file
CALL FUNCTION ‘UPLOAD_XLS_FILE_2_ITAB’
EXPORTING
i_filename
= ip_rule
TABLES
e_itab    
= lt_excel
EXCEPTIONS
file_error
= 1
OTHERS     = 2.
IF sysubrc <> 0.
MESSAGE ‘Unable to upload file ‘ TYPE  ‘E’.
ENDIF.
*generic factory instance
lref_factory
= cl_fdt_factory=>if_fdt_factory~get_instance( gc_appl_id ).
*get the expression the decision table guid
lref_decision_table ?= lref_factory
->get_expression(
iv_id
gc_dt_id ).
lref_decision_table
->if_fdt_transaction~enqueue( ).
DELETE ADJACENT DUPLICATES FROM lt_excel.
LOOP AT lt_excel INTO wa_excel .
lv_row_no
= lv_row_no + 1.
*level
CLEAR: ls_msg_data, ls_range, lts_range.
ls_msg_data
col_no = 1.
ls_msg_data
row_no = lv_row_no.
ls_range
position = 1.
ls_range
sign     = if_fdt_range=>gc_sign_include.
ls_range
option   = if_fdt_range=>gc_option_equal.
CREATE DATA ls_ranger_low_value TYPE if_fdt_types=>element_text.
ASSIGN ls_ranger_low_value->* TO <lv_value>.
<lv_value>
= wa_excellevel.
INSERT ls_range INTO TABLE ls_msg_datats_range.
INSERT ls_msg_data INTO TABLE lt_msg_data.
*age.
CLEAR: ls_msg_data, ls_range, lts_range.
ls_msg_data
col_no = 2.
ls_msg_data
row_no = lv_row_no.
ls_range
position = 1.
ls_range
sign     = if_fdt_range=>gc_sign_include.
ls_range
option   = if_fdt_range=>gc_option_equal.
CREATE DATA ls_ranger_low_value TYPE if_fdt_types=>element_text.
ASSIGN ls_ranger_low_value->* TO <lv_value>.
<lv_value>
= wa_excelage.
INSERT ls_range INTO TABLE ls_msg_datats_range.
INSERT ls_msg_data INTO TABLE lt_msg_data.
*pricing
CLEAR: ls_msg_data, ls_range, lts_range.
ls_msg_data
col_no = 3.
ls_msg_data
row_no = lv_row_no.
CREATE DATA ls_msg_datar_value TYPE if_fdt_types=>element_text.
ASSIGN ls_msg_datar_value->* TO <lv_value>.
<lv_value>
= wa_excelpricing.
INSERT ls_msg_data INTO TABLE lt_msg_data.
*base
CLEAR: ls_msg_data, ls_range, lts_range.
ls_msg_data
col_no = 4.
ls_msg_data
row_no = lv_row_no.
CREATE DATA ls_msg_datar_value TYPE if_fdt_types=>element_amount.
ASSIGN ls_msg_datar_value->* TO <lv_amount>.
<lv_amount>
number = wa_excelbase .
<lv_amount>
currency = wa_excelcurrency_base .
INSERT ls_msg_data INTO TABLE lt_msg_data.
UNASSIGN <lv_amount>
.
CLEAR wa_excel.
ENDLOOP.
IF lt_msg_data IS NOT INITIAL.
TRY.
lref_decision_table
->if_fdt_decision_table~set_table_data( its_data = lt_msg_data  ).
CATCH cx_fdt INTO lx_fdt.
IF lx_fdt IS NOT INITIAL.
WRITE : / ‘Creation of Decision table data failed with exception’.
ENDIF.
ENDTRY.
*    Perform deep activation & saving.
TRY.
lref_decision_table
->if_fdt_transaction~activate(
EXPORTING iv_deep              = abap_true
IMPORTING et_message           = lt_message
ev_activation_failed
= lv_actv_failed ).
CATCH cx_fdt INTO lx_fdt.
IF lx_fdt IS NOT INITIAL.
WRITE : ‘Activation failed with exception’.
ENDIF.
ENDTRY.
*  Save all the objects and release all locks.
IF lv_actv_failed EQ abap_true.
lref_decision_table
->if_fdt_transaction~dequeue(
EXPORTING iv_deep              = abap_true ).
WRITE : / ‘Deep activation failed’.                 
LOOP AT lt_message ASSIGNING <ls_message>.
MESSAGE ID <ls_message>msgid TYPE <ls_message>msgty NUMBER <ls_message>msgno
WITH <ls_message>msgv1 <ls_message>msgv2 <ls_message>msgv3 <ls_message>msgv4
INTO lv_message.
WRITE: / ‘Reason : -‘,lv_message.                 
ENDLOOP.
ELSE.
TRY.
lref_decision_table
->if_fdt_transaction~save(
EXPORTING iv_deep              = abap_true ).
CATCH cx_fdt INTO lx_fdt.
WRITE : / ‘Save failed with exception’.         
LOOP AT lx_fdt->mt_message ASSIGNING <ls_message>.
WRITE :/ <ls_message>text.
ENDLOOP.
ENDTRY.
lref_decision_table
->if_fdt_transaction~dequeue(
EXPORTING iv_deep              = abap_true ).
ENDIF.

ENDIF.
WRITE:‘DT Updated successfully’.


Input file has following values which need to be inserted into decision table.

/wp-content/uploads/2016/04/dt_1_930813.png

While execution of report provide the input file as shown in below screen shot and Click on Execute.

/wp-content/uploads/2016/04/dt_2_930814.png

We get the following output.

/wp-content/uploads/2016/04/dt_3_930869.png

Now in Decision table we get the values in BRF+ Workbench as shown in below screen .

DT_4.png

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Paul Hardy
      Paul Hardy

      I may be a lunatic here, but is there not a button in BRF+ which lets you upload an excel sheet into a decision table?

      Author's profile photo Former Member
      Former Member

      Hi Paul,

      There is a button in BRF+ for the said functionality but then we can create the whole application in an easier way in BRF+. Its always good to know a method to do stuff programmatically.

      Thanks,

      Ravi K

      Author's profile photo Colleen Hebbert
      Colleen Hebbert

      disclaimer: I'm not a developer but have used BRF+ decision tables:

      adding to Paul's comment - have you hard coded the BRF+ application GUIDs so you are now only uploading for one specific decision table?

      CONSTANTSgc_appl_id   TYPE if_fdt_types=>id  VALUE'005056C000081EE58CD3919CB9A80957',"Application GUID
                  gc_dt_id    
      TYPE if_fdt_types=>id  VALUE'001E671A4F281ED5BD97A46E4673360F',"Decision Table GUID

      Your upload screen only allows you to select file but not specify which BRF+ decision table you want to update.

      If you have hard coded it then how have you created an additional value-add to using the upload button provided by SAP?

      Author's profile photo Christian Lechner
      Christian Lechner

      Hi,

      in general: from my experience there are scenarios where uploading a decision table programmatically and not via the BRFplus standard functionality in the BRFplus workbench exist. Here are two examples:

      • "Deep integration" of BRFplus in a classical Dynpro application - this means you enhance the process logic/entering of master data in an application by also entering something like a decision table and link that to the master data (like discount parameters for a specific customer)
      • Integration of BRFplus into IMG/SPRO: Sometimes there is the requirement to integrate BRFplus functionality into IMG/SPRO via "classical" Dynpro and not via WebDynpro. One example would be the upload of a decision table

      So from my experience, the necessity for this type of code exists.

      Nevertheless, some more story around the necessity of such a code within the blog would definitely add value. This includes also the constraints (hard coded UUID of decision table).


      BTW: There is also a class that does the job for you called CL_FDT_EXCEL that allows the down- and upload of an excel in BRFplus


      Cheers

      Christian

      Author's profile photo Jahnavi Teja Venati
      Jahnavi Teja Venati
      Blog Post Author

      Hi Hebbert,

      For easy understanding and to avoid huge code here. I have hard coded only one decision table GUID and one application GUID,provided we can include multiple decision tables(make sure all decision tables have same structures),I had similar kind of scenario where client required everything using a report and upload data into Decision tables in one go . 🙂

      Thank you,

      Jahnavi  Venati.

      Author's profile photo Jahnavi Teja Venati
      Jahnavi Teja Venati
      Blog Post Author

      Hi Paul,

      Yes there is a standard functionality available for the BRF+ Workbench for uploading an excel,but I had a situation where I need to upload the data into multiple decision tables of an BRF+ Application programmatically ,Where based on one of the fields from input excel we need to fill the different decision tables in an application.

      Thank you,

      Jahnavi Venati.

      Author's profile photo Paul Hardy
      Paul Hardy

      I would be very interested in a practical example of the use of BRF+ in the IMG using classical dynpro.

      I was always told that the advantage of a decision table in BRF+ over the usual customising tables you see in the IMG was that you could have formulas in individual cells, or calls to function modules, or even other BRF+ constructs.

      If on the other hand you are not using any of the advantages of what BRF+ offers over a regular customising table, then why not use a regular customising table?

      All this was supposed to be impossible to enter in a classical DYNPRO screen, which was why (so I was told) BRF+ was written in Web Dynpro.

      If you could have all the advantages of BRF+ and enter using classical Dynpro that would be good as then you would not have to wait half an hour watching a whirling circle every single time you enter a piece of data into the Web Dynpro screen.

      I gather that eventually UI5 will be the entry point for BRF+ and that will be a lot faster, but "eventually" could be some time away.

      Cheersy Cheers

      Paul

      Author's profile photo Christian Lechner
      Christian Lechner

      Hi Paul,

      to put things straight (perhaps my description was too sloppy): I did not mean to rebuild BRFplus functionality i. e. the BRFplus workbench within classical Dynpro.

      The usual use-cases concerning the usage are:

      • Use the BRFplus workbench (either straight or with some restrictions) via TA BRFplus
        -> Expert Users
      • Create a custom BRFplus transaction that guides the user to the right expression
        -> "Normal" User
        => This custom transaction is then usually hooked into IMG as single source of configuration
      • Rare cases (but existing): Extract some very specific functionality from BRFplus and call it programmatically. One example would be a report that allows you the upload of an excel that feeds a decision table.
        This report might also be located into the IMG

      Hope that clarifies the statement from above

      Concerning UI5: I am not a SAP guy, but I would guess that some UI5 stuff for dealing with BRFplus expressions will come in the context of S/4HANA

      BR

      Christian

      Author's profile photo Mike Pokraka
      Mike Pokraka

      Hi Paul,

      Interesting points, I am currently in an implementation where we are also using BRF+ for many IMG-style configurations, even for cases where there is no advantage over a regular customising table.

      The main reason: The business wants to maintain everything in one place, and they want change history. Even after I demoed how awkward it can be to maintain e.g. Plant-specific settings using a BRF+ decision table with 30-odd results, they were happy with the trade-off between inconvenience to work with something that is rarely changed and the convenience of being able to manage everything in one place.

      So even though I would ordinarily have done the 'pure config' parts in SM30's, I can see their point.

      Regards,

      Mike

      Author's profile photo Venkat Palepu
      Venkat Palepu

      Hi,

       

      I am looking for similar kind of solution where on providing the Application GUID, option to maintian decision table data. But in my case I do have multiple decision tables. Is there any link table which maps Application GUID with associated Decision table GUIDs?