Skip to Content
SAP Business Planning and Consolidation, version for SAP NetWeaver

How to perform mass upload of validation business rules into BPC 7.5 for NetWeaver - Part 1

Applies to

SAP Business Planning and Consolidation 7.5 version for NetWeaver

Summary

Business has requested to set up and maintain a significant number of validation business rules in BPC NW. It might be time consuming to enter the validation business rules via BPC Admin Client as well as there is a risk of typo errors when doing this manually.

This document is primarily designed for BPC NW Administrators in order to simplify their work.

In Part 1 of this document it will be explained how to create underlying program to use during upload. In How to perform mass upload of validation business rules into BPC 7.5 for NetWeaver - Part 2  it will be shown how to create CSV files with validation rules and upload them.

Background Information

When there is a considerably significant number of validation business rules which are to be entered in the system it might become a nightmare for a BPC NW Administrator to enter them into the system manually. Entering validation business rules via BPC Admin Client might take long time and a risk of typos exists.

It might be considered to be easier to maintain validation business rules in a separate MS Excel workbook and then upload them into the system. This document will demonstrate a procedure how to upload validation business rules from local CSV files.

The concept of the approach is the following:

  1. Prepare validation business rules in MS Excel using the provided templates.
  2. Save MS Excel files as CSV.
  3. Upload the files using the provided program via NetWeaver.
  4. Run validation of the uploaded business rules via BPC Admin Client.

Notice that the program performs minimum validation of the entered data. The full validation is performed at step 4 via standard functionality of BPC Admin Client.

The program was built and tested on the following configuration. With other Service Packs it was not tested.

  • BPC 7.5 for NetWeaver SP11
  • SAP NetWeaver 7.0.1. SP05.

Prerequisites

Required/recommended expertise or prior knowledge

  • SAP BusinessObjects Planning and Consolidation 7.5, version for SAP NetWeaver
  • ABAP programming skills
  • Access to SAP NetWeaver transaction codes: SE16, SE38, SE80.
  • Developer key for NetWeaver user.

Step-by-Step Procedure

Create ZUJ_VALIDATION_CSV_UPLOAD


The following steps describe how to implement the desired functionality.

  1. Log on to NetWeaver.
  2. Enter ABAP Editor (transaction SE38)


  3. In the Program field enter the name of the program, for example ZUJ_VALIDATION_CSV_UPLOAD.
  4. Choose (Create). You reach the ABAP: Program Properties <Name of Program> Change screen.
  5. Enter the title of the program.
  6. Under Type in the Attributes field, choose Executable Program and then Save.



  7. You reach the Create Object Catalog Entry dialog box.
  8. In the Attributes field under Package enter $TMP and save the program as a Local Object.



  9. The following screen appears with REPORT  ZUJ_VALIDATION_CSV_UPLOAD.



  10. Select all content from line 1 to line 10 and replace it with the code provided in at the bottom of this document. The result should be the following.




  11. Check the code by clicking on Check.
  12. Activate the program.



  13. The program is ready to be executed, however it is good to maintain labels for selection screens. Go to Text Symbols from the menu as shown below.



  14. On Selection Texts tab enter the texts as shown below


    P_APPL Appset ID
    P_APPSET Application ID
    XLSFILED Validation rule detail file
    XLSFILEH Validation rule header file

  15. Activate and return to the previous screen.



    The program is fully ready. If you click Execute you should see the following screen.

ZUJ_VALIDATION_CSV_UPLOAD

*&---------------------------------------------------------------------*
*& Report  ZUJ_VALIDATION_CSV_UPLOAD
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZUJ_VALIDATION_CSV_UPLOAD.

CONSTANTS:  l_tab_name_h  TYPE tabname VALUE 'UJP_VALIDATIONH',
l_tab_name    TYPE tabname VALUE 'UJP_VALIDATION'.

TYPES:  BEGIN OF VALIDATION_RULE_HEADER,
MANDT            TYPE MANDT,
APPSET_ID        TYPE UJ_APPSET_ID,
APPLICATION_ID   TYPE UJ_APPL_ID,
SEQ              TYPE UJ_SMALLINT,
VALIDATION_ID    TYPE UJ_VALIDATION_ID,
VAL_CHECK        TYPE UJ_VALIDATION_CHECK_TYPE,
R_SELECTION      TYPE UJ_SELECTION,
R_DESTINATION    TYPE UJ_SELECTION,
PERIOD           TYPE UJ_ID,
MAX_AMOUNT       TYPE UJ_SMALLINT,
COMMNT           TYPE UJ_DESC,
END OF VALIDATION_RULE_HEADER.

TYPES:  BEGIN OF VALIDATION_RULE_DETAIL,
MANDT            TYPE MANDT,
APPSET_ID        TYPE UJ_APPSET_ID,
APPLICATION_ID   TYPE UJ_APPL_ID,
SEQ              TYPE UJ_SMALLINT,
VALIDATION_ID    TYPE UJ_VALIDATION_ID,
SIGN_L           TYPE UJ_SMALLINT,
ACCOUNT_L        TYPE UJ_ACCOUNT_ID,
SUBTABLES_L      TYPE UJ_FLOW_ID,
SIGN_R           TYPE UJ_SMALLINT,
ACCOUNT_R        TYPE UJ_ACCOUNT_ID,
SUBTABLES_R      TYPE UJ_FLOW_ID,
COMMNT           TYPE UJ_DESC,
END OF VALIDATION_RULE_DETAIL.

DATA :    L_RC          TYPE I,
USER_ACT      TYPE I,
VH_FILENAME   TYPE FILETABLE,
VD_FILENAME   TYPE FILETABLE,
V_HEADER      TYPE STANDARD TABLE OF UJP_VALIDATIONH,
V_DETAIL      TYPE STANDARD TABLE OF UJP_VALIDATION,
LINE_VH       LIKE LINE OF V_HEADER,
LINE_VD       LIKE LINE OF V_DETAIL,
lr_data       TYPE REF TO  data,
appset_id     TYPE uja_appl-appset_id,
appl_id       TYPE uja_appl-application_id,
lo_biz_rule   TYPE REF TO  if_uja_biz_rule.

DATA:     itab          TYPE TABLE OF STRING,
idat_h        TYPE TABLE OF VALIDATION_RULE_HEADER WITH HEADER LINE,
idat_d        TYPE TABLE OF VALIDATION_RULE_DETAIL WITH HEADER LINE.

DATA:     L_STR             TYPE STRING,
SEQ_AS_CHAR(5)    TYPE C,
SIGN_L_AS_CHAR(2) TYPE C,
SIGN_R_AS_CHAR(2) TYPE C,
MAX_CHAR(5)       TYPE C,
lt_message        TYPE uj0_t_message,
ls_message        TYPE UJ0_S_MESSAGE,
N_LINES_H         TYPE I,
N_LINES_D         TYPE I.

FIELD-SYMBOLS <FS> type any.

FIELD-SYMBOLS: <G_FS> TYPE ANY. "Global field symbol which will hold a line of rule

PARAMETERS :  XLSFILEH     TYPE STRING OBLIGATORY,
XLSFILED     TYPE STRING OBLIGATORY,
p_appset     TYPE UJA_APPSET_INFO-APPSET_ID OBLIGATORY,
p_appl       TYPE UJA_APPL-APPLICATION_ID OBLIGATORY.



AT SELECTION-SCREEN ON VALUE-REQUEST FOR XLSFILEH.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
EXPORTING
WINDOW_TITLE            = 'Select the validation rule header file'
DEFAULT_EXTENSION       = 'CSV'
FILE_FILTER             = '*.CSV'
CHANGING
FILE_TABLE              = VH_FILENAME
RC                      = L_RC
USER_ACTION             = USER_ACT
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR              = 2
ERROR_NO_GUI            = 3
NOT_SUPPORTED_BY_GUI    = 4
others                  = 5
.

IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

IF USER_ACT = '0'.
READ TABLE VH_FILENAME INDEX 1 INTO XLSFILEH.
ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR XLSFILED.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
EXPORTING
WINDOW_TITLE            = 'Select the validation rule detail file'
DEFAULT_EXTENSION       = 'CSV'
FILE_FILTER             = '*.CSV'
CHANGING
FILE_TABLE              = VD_FILENAME
RC                      = L_RC
USER_ACTION             = USER_ACT
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR              = 2
ERROR_NO_GUI            = 3
NOT_SUPPORTED_BY_GUI    = 4
others                  = 5
.

IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

IF USER_ACT = '0'.
READ TABLE VD_FILENAME INDEX 1 INTO XLSFILED.
ENDIF.



START-OF-SELECTION.

*validation of appset and application IDs
*  appset_id = p_appset.
*  appl_id = p_appl.
call method cl_uja_appset=>get_appset_appl_caption
exporting
i_appset_id      = p_appset
i_application_id = p_appl
importing
e_appset_id      = appset_id
e_application_id = appl_id
changing
ct_message       = lt_message.
read table lt_message transporting no fields with key msgty = 'E'.
"if sy-tabix <> 0 then either appset or appl or combination of the ids is wrong
if sy-tabix <> 0.
LOOP AT lt_message INTO ls_message.
WRITE ls_message-MESSAGE.
ENDLOOP.
EXIT.
endif.

*BREAK-POINT.
*EXIT.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD
EXPORTING
FILENAME                = XLSFILEH
*    FILETYPE                = 'ASC'
*    HAS_FIELD_SEPARATOR     = ','
*    HEADER_LENGTH           = 0
READ_BY_LINE            = 'X'
*    DAT_MODE                = SPACE
*    CODEPAGE                = SPACE
*    IGNORE_CERR             = ABAP_TRUE
*    REPLACEMENT             = '#'
*    VIRUS_SCAN_PROFILE      =
*  IMPORTING
*    FILELENGTH              =
*    HEADER                  =
CHANGING
DATA_TAB                = itab
EXCEPTIONS
FILE_OPEN_ERROR         = 1
FILE_READ_ERROR         = 2
NO_BATCH                = 3
GUI_REFUSE_FILETRANSFER = 4
INVALID_TYPE            = 5
NO_AUTHORITY            = 6
UNKNOWN_ERROR           = 7
BAD_DATA_FORMAT         = 8
HEADER_NOT_ALLOWED      = 9
SEPARATOR_NOT_ALLOWED   = 10
HEADER_TOO_LONG         = 11
UNKNOWN_DP_ERROR        = 12
ACCESS_DENIED           = 13
DP_OUT_OF_MEMORY        = 14
DISK_FULL               = 15
DP_TIMEOUT              = 16
NOT_SUPPORTED_BY_GUI    = 17
ERROR_NO_GUI            = 18
others                  = 19
.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
EXIT.
ENDIF.


N_LINES_H = 0.
LOOP AT itab INTO L_STR.
IF SY-TABIX = 1. "SKIP THE FIRST (HEADER) LINE
CONTINUE.
ENDIF.
CLEAR idat_h.

SPLIT L_STR AT ',' INTO "idat_h-MANDT
"idat_h-APPSET_ID
"idat_h-APPLICATION_ID
SEQ_AS_CHAR "hold seq in char variable because SPLIT will not convert it to number
idat_h-VALIDATION_ID
idat_h-VAL_CHECK
idat_h-R_SELECTION
idat_h-R_DESTINATION
idat_h-PERIOD
MAX_CHAR"idat_h-MAX_AMOUNT
idat_h-COMMNT.
idat_h-MANDT = ''. "keep empty
idat_h-APPSET_ID = appset_id.
idat_h-APPLICATION_ID = appl_id.
idat_h-SEQ = SEQ_AS_CHAR. "place the char SEQ_AS_CHAR to structure idat_h-SEQ
idat_h-MAX_AMOUNT = MAX_CHAR.
APPEND idat_h TO V_HEADER.
N_LINES_H = N_LINES_H + 1.
ENDLOOP.



CLEAR itab.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD
EXPORTING
FILENAME                = XLSFILED
*    FILETYPE                = 'ASC'
*    HAS_FIELD_SEPARATOR     = ','
*    HEADER_LENGTH           = 0
READ_BY_LINE            = 'X'
*    DAT_MODE                = SPACE
*    CODEPAGE                = SPACE
*    IGNORE_CERR             = ABAP_TRUE
*    REPLACEMENT             = '#'
*    VIRUS_SCAN_PROFILE      =
*  IMPORTING
*    FILELENGTH              =
*    HEADER                  =
CHANGING
DATA_TAB                = itab
EXCEPTIONS
FILE_OPEN_ERROR         = 1
FILE_READ_ERROR         = 2
NO_BATCH                = 3
GUI_REFUSE_FILETRANSFER = 4
INVALID_TYPE            = 5
NO_AUTHORITY            = 6
UNKNOWN_ERROR           = 7
BAD_DATA_FORMAT         = 8
HEADER_NOT_ALLOWED      = 9
SEPARATOR_NOT_ALLOWED   = 10
HEADER_TOO_LONG         = 11
UNKNOWN_DP_ERROR        = 12
ACCESS_DENIED           = 13
DP_OUT_OF_MEMORY        = 14
DISK_FULL               = 15
DP_TIMEOUT              = 16
NOT_SUPPORTED_BY_GUI    = 17
ERROR_NO_GUI            = 18
others                  = 19
.
*data str type string.
IF SY-SUBRC <> 0.
* CONCATENATE sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO str.
* write str.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
* EXIT.
ENDIF.

N_LINES_D = 0.
LOOP AT itab INTO L_STR.
IF SY-TABIX = 1. "SKIP THE FIRST (HEADER) LINE
CONTINUE.
ENDIF.
CLEAR idat_d.

SPLIT L_STR AT ',' INTO "idat_d-MANDT
"idat_d-APPSET_ID
"idat_d-APPLICATION_ID
SEQ_AS_CHAR "hold seq in char variable because SPLIT will not convert it to number
idat_d-VALIDATION_ID
SIGN_L_AS_CHAR "idat_d-SIGN_L
idat_d-ACCOUNT_L
idat_d-SUBTABLES_L
SIGN_R_AS_CHAR "idat_d-SIGN_R
idat_d-ACCOUNT_R
idat_d-SUBTABLES_R
idat_d-COMMNT.
idat_d-MANDT = ''. "keep empty
idat_d-APPSET_ID = appset_id.
idat_d-APPLICATION_ID = appl_id.
idat_d-SEQ = SEQ_AS_CHAR. "place the char SEQ_AS_CHAR to structure idat_h-SEQ
idat_d-SIGN_L = SIGN_L_AS_CHAR.
idat_d-SIGN_R = SIGN_R_AS_CHAR.
APPEND idat_d TO V_DETAIL.
N_LINES_D = N_LINES_D + 1.
ENDLOOP.

*BREAK-POINT.
data: t_str     type string,
ans(8)    type c.
L_STR = N_LINES_H.
CONCATENATE L_STR ' rules and' INTO L_STR.
t_str = N_LINES_D.
CONCATENATE L_STR ' ' t_str ' detailed rules will be updated for application ' appl_id ' in appset ' appset_id INTO L_STR.

CALL FUNCTION 'POPUP_TO_CONFIRM'
EXPORTING
TITLEBAR                    = 'Please confirm the update of validation business rules'
*   DIAGNOSE_OBJECT             = ' '
TEXT_QUESTION               = L_STR
TEXT_BUTTON_1               = 'Yes'"(001)
ICON_BUTTON_1               = 'ICON_OKEY'
TEXT_BUTTON_2               = 'Cancel'"(002)
ICON_BUTTON_2               = 'ICON_CANCEL'
DEFAULT_BUTTON              = '2'
DISPLAY_CANCEL_BUTTON       = ''
*   USERDEFINED_F1_HELP         = ' '
START_COLUMN                = 25
START_ROW                   = 6
*   POPUP_TYPE                  =
*   IV_QUICKINFO_BUTTON_1       = ' '
*   IV_QUICKINFO_BUTTON_2       = ' '
IMPORTING
ANSWER                      = ans
* TABLES
*   PARAMETER                   =
EXCEPTIONS
TEXT_NOT_FOUND              = 1
OTHERS                      = 2
.
CASE ans.
WHEN 1.
CONCATENATE 'The update has been confirmed and will be proceeded for application ' appl_id ' in appset ' appset_id into L_STR.
WRITE: / L_STR.
WHEN 2.
CONCATENATE 'The update has been canceled and nothing has been updated for application ' appl_id ' in appset ' appset_id into L_STR.
WRITE: / L_STR.
EXIT.
ENDCASE.

IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


lo_biz_rule  = cl_uja_admin_mgr=>get_biz_rules( i_appset_id = appset_id
i_appl_id   = appl_id ).

* Delete exising validation rules from the system
cl_uj_obj_dao=>delete_all(  i_appset_id = appset_id
i_appl_id   = appl_id
i_tabname   = l_tab_name_h ).
* Update validation rules from internal table to the system
call method cl_uj_obj_dao=>set_tab_data
exporting
i_tabname = l_tab_name_h
it_data   = V_HEADER.

L_STR = N_LINES_H.
CONCATENATE L_STR ' rules are successfully written to validation header table' INTO L_STR.
WRITE: / L_STR.

* Delete exising validation rules from the system
cl_uj_obj_dao=>delete_all(  i_appset_id = appset_id
i_appl_id   = appl_id
i_tabname   = l_tab_name ).
* Update validation rules from internal table to the system
call method cl_uj_obj_dao=>set_tab_data
exporting
i_tabname = l_tab_name
it_data   = V_DETAIL.

L_STR = N_LINES_D.
CONCATENATE L_STR ' detail rules are successfully written to validation detail table' INTO L_STR.
WRITE: / L_STR.
*  BREAK-POINT.

Now, let's move on to How to perform mass upload of validation business rules into BPC 7.5 for NetWeaver - Part 2.

April 11, 2012 at 13:34 PM
2 Likes

1 replies