Skip to Content

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 :

design overview.GIF

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 .

***********************************************************************************************************************************

Front screen.png

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 SYSUBRC  <> 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.

***********************************************************************************************************************************

Single or multiple selection.png

  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
= SYUCOMM.

  CASE W_OK_CODE .
   
WHEN ‘BACK’.
     
LEAVE TO SCREEN 0.
   
WHEN ‘RETURN’.
     
LEAVE TO SCREEN 0.
   
WHEN ‘EXIT’.
     
LEAVE PROGRAM.
 
ENDCASE.

  IF SSCRFIELDSUCOMM EQ ‘MUL’.
   
PERFORM MUL_ENTRIES.
 
ENDIF.

  IF SSCRFIELDSUCOMM 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 TAB3CURRENT_LINE.

    MODULE SET_LINE_INPUT.

  ENDLOOP.


PROCESS AFTER
INPUT.

  LOOP AT IS_FILE .

    FIELD WA_IS_FILEFILENAME
  
MODULE TAB3_MODIFY ON REQUEST.

  ENDLOOP.

  MODULE USER_COMM.

PROCESS ON VALUE-REQUEST.
 
FIELD WA_IS_FILEFILENAME 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 TAB3CURRENT_LINE.
 
IF SYSUBRC = 0 .

    MODIFY IS_FILE
     
FROM WA_IS_FILE
     
INDEX TAB3CURRENT_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 SYUCOMM.

    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 SYSTEPL.

*—Reading table line number—*

  CALL FUNCTION ‘DYNP_GET_STEPL’
   
IMPORTING
      POVSTEPL       
= G_CURR_LINE
   
EXCEPTIONS
      STEPL_NOT_FOUND
= 1
     
OTHERS          = 2.
 
IF SYSUBRC <> 0.
   
MESSAGE ID SYMSGID TYPE SYMSGTY NUMBER SYMSGNO
           
WITH SYMSGV1 SYMSGV2 SYMSGV3 SYMSGV4.
 
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 SYSUBRC  <> 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_FILEFILENAME.
   
TRANSLATE WA_IS_FILEFILENAME TO UPPER CASE.

    APPEND WA_IS_FILE TO IS_FILE .

  ENDLOOP.

ENDMODULE.                 ” INPUT_FILE  INPUT

***********************************************************************************************************************************

  multiple selection screen.png

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_FIELDCATALOGFIELDNAME   = ‘FUNCTIONAL_AREA’.
  IS_FIELDCATALOGSELTEXT_M   = ‘FUNCTIONAL_AREA’.
  IS_FIELDCATALOGCOL_POS     = 0.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘COSTCENTER’.
  IS_FIELDCATALOGSELTEXT_M   = ‘COSTCENTER’.
  IS_FIELDCATALOGCOL_POS     = 1.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘COST_ELEMENT’.
  IS_FIELDCATALOGSELTEXT_M   = ‘COST_ELEMENT’.
  IS_FIELDCATALOGCOL_POS     = 2.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘COMPANY_CODE’.
  IS_FIELDCATALOGSELTEXT_M   = ‘COMPANY_CODE’.
  IS_FIELDCATALOGCOL_POS     = 3.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘FISCAL_YEAR’.
  IS_FIELDCATALOGSELTEXT_M   = ‘FISCAL_YEAR’.
  IS_FIELDCATALOGCOL_POS     = 4.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘LOCAL_CURRENCY’.
  IS_FIELDCATALOGSELTEXT_M   = ‘LOCAL_CURRENCY’.
  IS_FIELDCATALOGCOL_POS     = 5.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘BAL_LC’.
  IS_FIELDCATALOGSELTEXT_M   = ‘Balance LOCAL’.
  IS_FIELDCATALOGCOL_POS     = 6.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘GROUP_CURR_KEY’.
  IS_FIELDCATALOGSELTEXT_M   = ‘GROUP CURR KEY’.
  IS_FIELDCATALOGCOL_POS     = 7.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

  IS_FIELDCATALOGFIELDNAME   = ‘GROUP_CURR’.
  IS_FIELDCATALOGSELTEXT_M   = ‘Balance in Group Curr’.
  IS_FIELDCATALOGCOL_POS     = 8.
  APPEND IS_FIELDCATALOG TO IS_FIELDCATALOG.
  CLEAR  IS_FIELDCATALOG.

DATA LO_W_PROGRM TYPE SYREPID.

  LO_W_PROGRM = SYREPID.

  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 SYSUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

  ENDIF.

***********************************************************************************************************************************

Error log.png

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.

  CLEARLO_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 SYSUBRC 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 SYSUBRC = 0.
    MESSAGE ‘Process has been succesfully Triggerd’ TYPE ‘I’ DISPLAY LIKE ‘S’.
    CALL TRANSACTION ‘TCODE’.
  ENDIF.

***********************************************************************************************************************************

GEC_NGCF_LOAD.JPG

Step 4 : Load data to datasource and DSO

DSO data.GIF

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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

    1. mohit gupta Post author

      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

      (0) 

Leave a Reply