csv file validations in sap BPC 7.5 nw

[Former Member]

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.

*&---------------------------------------------------------------------**& 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_DIALOGEXPORTING
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_ACTEXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
NOT_SUPPORTED_BY_GUI = 4others = 5
.
IF SY-SUBRC <> 0.MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNOWITH 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_DIALOGEXPORTING
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_ACTEXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
NOT_SUPPORTED_BY_GUI = 4others = 5
.
IF SY-SUBRC <> 0.MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNOWITH 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_captionexporting
i_appset_id = p_appset
i_application_id = p_applimporting
e_appset_id = appset_id
e_application_id = appl_idchanging
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 wrongif 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_UPLOADEXPORTING
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 = itabEXCEPTIONS
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 = 18others = 19
.IF SY-SUBRC <> 0.MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNOWITH 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) LINECONTINUE.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_UPLOADEXPORTING
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 = itabEXCEPTIONS
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 = 18others = 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-MSGNOWITH 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) LINECONTINUE.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'EXPORTINGTITLEBAR = '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 = 1OTHERS = 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 systemcall method cl_uj_obj_dao=>set_tab_dataexporting
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 systemcall method cl_uj_obj_dao=>set_tab_dataexporting
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.