Load Non Legacy System data to BW system using Flat File(.CSV) , make available for Reporting and Automate whole Process !
OVERVIEW : This Document provides you the solution for, how you can use non legacy data to SAP BW system using Multiple Flat file and automate the whole process.
design overview :
Step 1 : create a Report Program using SE38 , a user interface screen using ABAP for single or multiple File.
Code For genrating Selection Screen:
***********************************************************************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK DATA1 WITH FRAME TITLE TEXT-001.
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(20) TEXT-002.
PARAMETERS P_SFILE TYPE RLGRAP-FILENAME.
SELECTION-SCREEN POSITION 74.
SELECTION-SCREEN PUSHBUTTON (5) BUT2 USER-COMMAND MUL VISIBLE LENGTH 8.
SELECTION-SCREEN PUSHBUTTON (15) W_BUT USER-COMMAND CLK .
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN POSITION 25.
SELECTION-SCREEN COMMENT 1(20) TEXT-003.
PARAMETERS: P_TFILE LIKE SAPB-SAPPFAD LOWER CASE OBLIGATORY MODIF ID A DEFAULT C_APP_PATH.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(20) TEXT-004.
PARAMETERS: P_EVENT TYPE BTCUEV-EVENTID OBLIGATORY MODIF ID B DEFAULT C_EVENT_NAM.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(20) TEXT-005.
PARAMETERS: P_CHAIN TYPE RSPC_CHAIN MODIF ID C OBLIGATORY DEFAULT C_PROC_NAM.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN END OF BLOCK DATA1.
SELECTION-SCREEN BEGIN OF BLOCK DATA2 WITH FRAME TITLE TEXT-006 .
SELECTION-SCREEN BEGIN OF LINE .
SELECTION-SCREEN COMMENT 01(31) W_CAP .
SELECTION-SCREEN COMMENT 40(5) W_RED MODIF ID R.
SELECTION-SCREEN COMMENT 46(5) W_AMBER MODIF ID Y.
SELECTION-SCREEN COMMENT 53(5) W_GREEN MODIF ID G.
SELECTION-SCREEN PUSHBUTTON (15) REFRESH USER-COMMAND REFRESH MODIF ID F.
SELECTION-SCREEN END OF LINE .
SELECTION-SCREEN END OF BLOCK DATA2 .
***********************************************************************************************************************************
User can upload either single file or multiple file .
Code For Single Value input :
***********************************************************************************************************************************
* This code is for to browse single file from the Local machine *
***********************************************************************************************************************************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_SFILE.
CLEAR IS_FILE_NAMES[].
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
EXPORTING
INITIAL_DIRECTORY = ‘C:Temp’
MULTISELECTION = ‘X’
CHANGING
FILE_TABLE = IS_FILE_NAMES[]
RC = W_SUBRC
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
OTHERS = 4.
IF SY–SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
LOOP AT IS_FILE_NAMES INTO WA_TS_FILE_NAMES.
P_SFILE = WA_TS_FILE_NAMES.
CONDENSE P_SFILE .
ENDLOOP.
CLEAR IS_FILE_NAMES[].
REFRESH IS_FILE.
***********************************************************************************************************************************
For multiple selection you need to create a new screen using table control like below . I tried with select-option but it didnt work for me so created new screen.
Code For Multiple Selection :
***********************************************************************************************************************************
* This code is for to browse multiple file from the Local machine *
***********************************************************************************************************************************
AT SELECTION-SCREEN .
CLEAR W_OK_CODE.
W_OK_CODE = SY–UCOMM.
CASE W_OK_CODE .
WHEN ‘BACK’.
LEAVE TO SCREEN 0.
WHEN ‘RETURN’.
LEAVE TO SCREEN 0.
WHEN ‘EXIT’.
LEAVE PROGRAM.
ENDCASE.
IF SSCRFIELDS–UCOMM EQ ‘MUL’.
PERFORM MUL_ENTRIES.
ENDIF.
IF SSCRFIELDS–UCOMM EQ ‘REFRESH’.
PERFORM PC_STATUS_CHECK.
ENDIF.
FORM MUL_ENTRIES .
REFRESH CONTROL ‘TAB3’ FROM SCREEN 9000.
CALL SCREEN 9000 STARTING AT 25 2.
ENDFORM.
*screen code.
PROCESS BEFORE OUTPUT.
LOOP AT IS_FILE
INTO WA_IS_FILE
WITH CONTROL TAB3
CURSOR TAB3–CURRENT_LINE.
MODULE SET_LINE_INPUT.
ENDLOOP.
PROCESS AFTER INPUT.
LOOP AT IS_FILE .
FIELD WA_IS_FILE–FILENAME
MODULE TAB3_MODIFY ON REQUEST.
ENDLOOP.
MODULE USER_COMM.
PROCESS ON VALUE-REQUEST.
FIELD WA_IS_FILE–FILENAME MODULE INPUT_FILE.
MODULE SET_LINE_INPUT OUTPUT.
TAB3–LINES = 20.ENDMODULE.
CONTROLS: TAB3 TYPE TABLEVIEW USING SCREEN 9000.
MODULE TAB3_CHANGE_TC_ATTR OUTPUT.
EndModule.
MODIFY TABLEMODULE TAB3_MODIFY INPUT.
CLEAR WA_TS_FILE_TEM.
WA_IS_FILE_TEM1 = WA_IS_FILE.
READ TABLE IS_FILE INTO WA_TS_FILE_TEM INDEX TAB3–CURRENT_LINE.
IF SY–SUBRC = 0 .
MODIFY IS_FILE
FROM WA_IS_FILE
INDEX TAB3–CURRENT_LINE.
ELSE.
* APPEND wa_IS_FILE_TEM1 to IS_FILE .
ENDIF.
ENDMODULE.
MODULE USER_COMM INPUT.
SORT IS_FILE BY FILENAME.
DELETE ADJACENT DUPLICATES FROM IS_FILE[] COMPARING FILENAME.
CASE SY–UCOMM.
WHEN ‘F_OK’.
LEAVE TO SCREEN 0.
WHEN ‘ONLI’.
LEAVE TO SCREEN 0.
WHEN ‘ECAN’.
REFRESH IS_FILE.
LEAVE TO SCREEN 0.
ENDCASE.
ENDMODULE.
MODULE INPUT_FILE INPUT.
DATA : G_CURR_LINE LIKE SY–STEPL.
*—Reading table line number—*
CALL FUNCTION ‘DYNP_GET_STEPL’
IMPORTING
POVSTEPL = G_CURR_LINE
EXCEPTIONS
STEPL_NOT_FOUND = 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.
CLEAR IS_FILE_NAMES[].
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
EXPORTING
* WINDOW_TITLE =
* DEFAULT_EXTENSION =
* DEFAULT_FILENAME =
* FILE_FILTER =
INITIAL_DIRECTORY = ‘C:Temp’
MULTISELECTION = ‘X’
CHANGING
FILE_TABLE = IS_FILE_NAMES[]
RC = W_SUBRC
* USER_ACTION =
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
OTHERS = 4.
IF SY–SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
LOOP AT IS_FILE_NAMES INTO WA_TS_FILE_NAMES.
P_SFILE = WA_TS_FILE_NAMES.
WA_IS_FILE–FILENAME = WA_TS_FILE_NAMES.
CONDENSE WA_IS_FILE–FILENAME.
TRANSLATE WA_IS_FILE–FILENAME TO UPPER CASE.
APPEND WA_IS_FILE TO IS_FILE .
ENDLOOP.
ENDMODULE. ” INPUT_FILE INPUT
***********************************************************************************************************************************
Step 2 : Click on Check button to check flat files error. If there any error in any file then it will display in ALV grid display and ask user to correct file and load it again.
*Code For Error ALV Display
****************************************************************************************************************************
* This code will create ALV log to display error in specific format *
***********************************************************************************************************************************
* TS_TAB_ERROR conatins Error document which will be filled according to validation
* field catalog
REFRESH IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘FUNCTIONAL_AREA’.
IS_FIELDCATALOG–SELTEXT_M = ‘FUNCTIONAL_AREA’.
IS_FIELDCATALOG–COL_POS = 0.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘COSTCENTER’.
IS_FIELDCATALOG–SELTEXT_M = ‘COSTCENTER’.
IS_FIELDCATALOG–COL_POS = 1.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘COST_ELEMENT’.
IS_FIELDCATALOG–SELTEXT_M = ‘COST_ELEMENT’.
IS_FIELDCATALOG–COL_POS = 2.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘COMPANY_CODE’.
IS_FIELDCATALOG–SELTEXT_M = ‘COMPANY_CODE’.
IS_FIELDCATALOG–COL_POS = 3.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘FISCAL_YEAR’.
IS_FIELDCATALOG–SELTEXT_M = ‘FISCAL_YEAR’.
IS_FIELDCATALOG–COL_POS = 4.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘LOCAL_CURRENCY’.
IS_FIELDCATALOG–SELTEXT_M = ‘LOCAL_CURRENCY’.
IS_FIELDCATALOG–COL_POS = 5.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘BAL_LC’.
IS_FIELDCATALOG–SELTEXT_M = ‘Balance LOCAL’.
IS_FIELDCATALOG–COL_POS = 6.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘GROUP_CURR_KEY’.
IS_FIELDCATALOG–SELTEXT_M = ‘GROUP CURR KEY’.
IS_FIELDCATALOG–COL_POS = 7.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
IS_FIELDCATALOG–FIELDNAME = ‘GROUP_CURR’.
IS_FIELDCATALOG–SELTEXT_M = ‘Balance in Group Curr’.
IS_FIELDCATALOG–COL_POS = 8.
APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
CLEAR IS_FIELDCATALOG.
DATA LO_W_PROGRM TYPE SYREPID.
LO_W_PROGRM = SY–REPID.
CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
EXPORTING
I_CALLBACK_PROGRAM = LO_W_PROGRM
I_CALLBACK_TOP_OF_PAGE = ‘TOP_OF_PAGE’
IT_FIELDCAT = IS_FIELDCATALOG[]
TABLES
T_OUTTAB = TS_TAB_ERROR
EXCEPTIONS
PROGRAM_ERROR = 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.
***********************************************************************************************************************************
Step 3 : if All files are correct, no Error in records then user will click on execute button to load the data in application server and After loading data program will trigger process chain . once the data is load to application server after that data will be loaded to DSO and cube by process chain.
Load Data to application server
Code :
********************************************************************************************************************************
* Using this code we can load data to applicaiton server and further we will load to PSA *
***********************************************************************************************************************************
FORM LOAD_DATA_APP .
DATA: LO_FILENAME TYPE STRING , “FILEEXTERN,
LO_FILELINE TYPE RSDS_CHAR1000.
DATA: LO_FINAL TYPE STRING,
LO_PATH TYPE STRING.
DATA: LO_MESS(60).
DATA : LO_EXCEP1 TYPE REF TO CX_ROOT,
LO_TEXT TYPE CHAR100.
******************************************************************************************
**fetching file path
******************************************************************************************
CLEAR : LO_PATH,
W_FINAL1.
LO_PATH = ‘Application File Path’.
W_FINAL1 = ‘Application File’
TRANSLATE W_FINAL1 TO UPPER CASE.
CLEAR: LO_FINAL,
LO_FILENAME.
CONCATENATE LO_PATH W_FINAL1 INTO LO_FINAL SEPARATED BY ‘/’.
LO_FILENAME = LO_FINAL.
**********************opening data file.******************************
TRY.
OPEN DATASET LO_FILENAME FOR OUTPUT IN TEXT MODE ENCODING DEFAULT MESSAGE LO_MESS .
IF SY–SUBRC EQ 0.
UNASSIGN <FS_IS_TEXT_DATA>.
LOOP AT TS_TEXT_DATA ASSIGNING <FS_IS_TEXT_DATA>.
LO_FILELINE = <FS_IS_TEXT_DATA>.
TRANSFER LO_FILELINE TO LO_FILENAME.
ENDLOOP.
ELSE.
MESSAGE ‘File Path issue To Unix Directory’ TYPE ‘I’ DISPLAY LIKE ‘E’.
LEAVE TO SCREEN 0.
ENDIF.
* Close data set.
CLOSE DATASET LO_FILENAME.
CATCH CX_ROOT INTO LO_EXCEP1.
CALL METHOD LO_EXCEP1->IF_MESSAGE~GET_TEXT
RECEIVING
RESULT = LO_TEXT.
MESSAGE LO_TEXT TYPE ‘I’ DISPLAY LIKE ‘E’ .
LEAVE TO SCREEN 0.
ENDTRY.
ENDFORM.
***********************************************************************************************************************************
Trigger Process Chain :
Code
***********************************************************************************************************************************
DATA:
LO_EVENTID TYPE BTCEVENTID,
LO_EVENTPARM TYPE BTCEVTPARM,
LO_SERVER TYPE BTCSERVER.
CLEAR LO_EVENTID.
LO_EVENTID = ‘Write Event ID’. {Event id which will Trigger Process Chain}
CALL METHOD CL_BATCH_EVENT=>RAISE
EXPORTING
I_EVENTPARM = LO_EVENTPARM
I_SERVER = LO_SERVER
I_EVENTID = LO_EVENTID
EXCEPTIONS
EXCPT_RAISE_FAILED = 1
EXCPT_RAISE_FORBIDDEN = 3
EXCPT_UNKNOWN_EVENT = 4
EXCPT_NO_AUTHORITY = 5
OTHERS = 6.
IF SY–SUBRC = 0.
MESSAGE ‘Process has been succesfully Triggerd’ TYPE ‘I’ DISPLAY LIKE ‘S’.
CALL TRANSACTION ‘TCODE’.
ENDIF.
***********************************************************************************************************************************
Step 4 : Load data to datasource and DSO
By This way We can Automate the Whole Process and Data can be load to DSO and cube .
Non Legacy data will be available for BW reporting purpose.
Its little bit lengthy but very interesting.
Regards,
Sushant
Thank you Sushant 🙂 🙂 ... It is very effective when you need data massage , data filtering or data correction of the flat file data before loading to BW system.
Regards
Mohit
nice document.
Regards,
SM
Hi Mohit,
Its really helpful. Many users face this type of situation at one time. Thanks for posting readymade program.
regards,
Mohammed.
Fully ABAP Stuff 😉 . Although I could not understand much,I am sure it is going to help me if i get such requirement. Thanks for sharing this approach.
Hi Mohit,
Good one. Thanks for sharing..... but one suggestion, Please use appropriate comments in your coding.
Regards,
Krishna Chaitanya.
Hi Mohit,
Good information it will help for such requirement..thanks for sharing 🙂 .
Thank you Ganesh.... 🙂 thanks to all
Nice one. 🙂
Thanks for nice document. If possible, Can you please write full ABAP code with proper declaration syntax IS_FILE_NAMES , W_OK and many more so it give more insight and knowledge.