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 rlgrap–filename OBLIGATORY.
CONSTANTS: gc_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.
DATA: lt_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 sy–subrc <> 0.
MESSAGE WITH ‘file_open_dialog’.
ELSE.
READ TABLE lt_files INDEX 1 INTO ls_files.
IF sy–subrc = 0.
ip_rule = ls_files–filename.
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 sy–subrc <> 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_range–r_low_value TYPE if_fdt_types=>element_text.
ASSIGN ls_range–r_low_value->* TO <lv_value>.
<lv_value> = wa_excel–level.
INSERT ls_range INTO TABLE ls_msg_data–ts_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_range–r_low_value TYPE if_fdt_types=>element_text.
ASSIGN ls_range–r_low_value->* TO <lv_value>.
<lv_value> = wa_excel–age.
INSERT ls_range INTO TABLE ls_msg_data–ts_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_data–r_value TYPE if_fdt_types=>element_text.
ASSIGN ls_msg_data–r_value->* TO <lv_value>.
<lv_value> = wa_excel–pricing.
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_data–r_value TYPE if_fdt_types=>element_amount.
ASSIGN ls_msg_data–r_value->* TO <lv_amount>.
<lv_amount>–number = wa_excel–base .
<lv_amount>–currency = wa_excel–currency_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.
While execution of report provide the input file as shown in below screen shot and Click on Execute.
We get the following output.
Now in Decision table we get the values in BRF+ Workbench as shown in below screen .
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?
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
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?
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?
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:
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
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.
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.
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
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:
-> Expert Users
-> "Normal" User
=> This custom transaction is then usually hooked into IMG as single source of configuration
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
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
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?