Skip to Content
Author's profile photo Sushil Kumar Goel

Extraction of 0SEM_BCS_10 (Preparation for Consolidation: Transfer of Data to SEM-BCS) into CSV File

Hi Friends,

This is my first blog, so, I thought of writing about one of the common requirements one can receive from customer to get extract of a consolidation datasource 0SEM_BCS_10 in a CSV file with the same format as it shows in BW. This scenario pops up when client don’t want to use BW anymore for viewing that data and just wanted data in a excel, so, came up with a solution that I am explaining later in this post.

 

Before jumping directly on the solution, I will explain in few lines how exactly the data was being fetched to BW before implementation of solution on ECC side.

In this BW scenario, the data is loaded to a cube via a DTP with adapter properties of Synchronous Extraction SAPI (for Direct Access and Tests). There can be code written in field routines of transformation mapped between data-source and the cube as well.

Now, here comes the solution code written on ECC side.

This code uses the extractor FAGL_GET_TT_DATA_LEAD provided by SAP for datasource 0SEM_BCS_10 in a simplified way.

Also, along with option of download, user can also display data in ALV grid directly only incase he/she has sufficient privileges.

*&---------------------------------------------------------------------*
*& Report  ZEXTRACT_SEM_BCS_10
*&---------------------------------------------------------------------*

REPORT ZEXTRACT_SEM_BCS_10.

TABLES: FAGLFLEXT, FAGL_EXTSTRUCT_LEAD, T001.

TYPE-POOLS: RSAOT, SBIWA.
TYPES: BEGIN OF TY_RESULT,
        FISCPER     TYPE FAGL_EXTSTRUCT_LEAD-FISCPER ,
        FISCVAR     TYPE FAGL_EXTSTRUCT_LEAD-FISCVAR ,
        RYEAR       TYPE FAGL_EXTSTRUCT_LEAD-RYEAR ,
        FISCPER3    TYPE RPMAX, "Posting Period
        RACCT       TYPE FAGL_EXTSTRUCT_LEAD-RACCT ,
        CHARTACCTS  TYPE FAGL_EXTSTRUCT_LEAD-CHARTACCTS ,
        RBUKRS      TYPE FAGL_EXTSTRUCT_LEAD-RBUKRS ,
        CURTYPE     TYPE FAGL_EXTSTRUCT_LEAD-CURTYPE ,
        RFAREA      TYPE FAGL_EXTSTRUCT_LEAD-RFAREA ,
        PPRCTR      TYPE FAGL_EXTSTRUCT_LEAD-PPRCTR ,
        PRCTR       TYPE FAGL_EXTSTRUCT_LEAD-PRCTR ,
        RVERS       TYPE FAGL_EXTSTRUCT_LEAD-RVERS ,
        VALUETYPE   TYPE FAGL_EXTSTRUCT_LEAD-VALUETYPE ,
        VALUTYP     TYPE FAGL_EXTSTRUCT_LEAD-VALUTYP ,
        DEPRAREA    TYPE C LENGTH 2,  "Depreciation Area real or derived
        RASSC       TYPE FAGL_EXTSTRUCT_LEAD-RASSC ,
        RASSC1      TYPE C LENGTH 4,  "Company Code of Partner
        SFAREA      TYPE FAGL_EXTSTRUCT_LEAD-SFAREA ,
        RMVCT       TYPE FAGL_EXTSTRUCT_LEAD-RMVCT ,
        BALANCE     TYPE FAGL_EXTSTRUCT_LEAD-BALANCE ,
        CREDIT      TYPE FAGL_EXTSTRUCT_LEAD-CREDIT ,
        DEBIT       TYPE FAGL_EXTSTRUCT_LEAD-DEBIT ,
        CURRUNIT    TYPE FAGL_EXTSTRUCT_LEAD-CURRUNIT, "Currency
        QUANTITY    TYPE FAGL_EXTSTRUCT_LEAD-QUANTITY ,
        QUANUNIT    TYPE FAGL_EXTSTRUCT_LEAD-QUANUNIT,
        KOKRS       TYPE FAGL_EXTSTRUCT_LEAD-KOKRS ,
        CS_ITEM     TYPE RACCT,
        CS_CHART    TYPE C LENGTH 2,
        CONS_UNIT   TYPE C LENGTH 6,
        AMOUNT      TYPE RR_UMSOL,
        RLDNR       TYPE FAGL_EXTSTRUCT_LEAD-RLDNR ,
 END OF TY_RESULT.

TYPES: TY_RESULT_TABLE TYPE TABLE OF TY_RESULT.

CONSTANTS: C_CHECKED(1)   VALUE 'X',
           C_CURTYPE(7)   VALUE 'CURTYPE',
           C_EQUAL(2)     VALUE 'EQ',
           C_INCLUDE(1)   VALUE 'I',
           C_FISCPER(7)   VALUE 'FISCPER',
           C_COMP(6)      VALUE 'RBUKRS',
           C_CS_CHART(2)  VALUE 'C1',
           C_ACTIVE(1)    VALUE 'A',
           C_TEN(2)       VALUE '10',
           C_BLANK(1)     VALUE '',
           C_UNIT(1)      VALUE 'U',
           C_ZERO(3)      VALUE '000',
           C_DELIMITER(1) VALUE ',',
           C_ERROR(1)     VALUE 'E',
           C_INFO(1)      VALUE 'I',
           C_FILE      TYPE  STRING VALUE 'File Directory',
           C_REQUNR    TYPE  SBIWA_S_INTERFACE-REQUNR VALUE 'TEST',
           C_ISOURCE   TYPE  SBIWA_S_INTERFACE-ISOURCE VALUE '0SEM_BCS_10',
           C_MAXSIZE   TYPE  SBIWA_S_INTERFACE-MAXSIZE VALUE '999999',
           C_INITFLAG  TYPE  SBIWA_S_INTERFACE-INITFLAG VALUE 'X' ,
           C_UPDMODE   TYPE  SBIWA_S_INTERFACE-UPDMODE VALUE 'F' .

DATA: WA_RESULT TYPE TY_RESULT.

DATA: IT_EXTRACT LIKE TABLE OF FAGL_EXTSTRUCT_LEAD,
      WA_EXTRACT TYPE FAGL_EXTSTRUCT_LEAD,
      E_T_FIELDS  TYPE RSAOT_T_OSFIELD,
      IT_FIELDS   TYPE SBIWA_T_FIELDS,
      G_WA_FIELDS TYPE SBIWA_S_FIELDS,
      IT_SELECT   TYPE SBIWA_T_SELECT,
      IT_FIELDCAT TYPE  SLIS_FIELDCAT_ALV OCCURS 0,
      WA_FIELDCAT TYPE SLIS_FIELDCAT_ALV,
      IT_RESULT LIKE TABLE OF WA_RESULT WITH HEADER LINE ,
      BEGIN OF IT_DOWNLOAD OCCURS 0,
        LINE TYPE STRING,
      END OF IT_DOWNLOAD,
      G_VALUE TYPE STRING,
      PT_FIELDCAT  TYPE LVC_T_FCAT.

FIELD-SYMBOLS: <FS_VALUE> TYPE ANY,
               <FS_EXTRACT> TYPE FAGL_EXTSTRUCT_LEAD,
               <FS_FIELDCAT> TYPE SLIS_FIELDCAT_ALV,
               <FS_FIELDS>   TYPE RSAOT_S_OSFIELD.

SELECT-OPTIONS: S_COMP FOR FAGLFLEXT-RBUKRS OBLIGATORY,		"Enter Company Code
                S_FISCP FOR FAGL_EXTSTRUCT_LEAD-FISCPER OBLIGATORY.	"Enter Fiscal Period

PARAMETERS: P_DCHK RADIOBUTTON GROUP RAD1,		"Download Radiobutton
            P_DISP RADIOBUTTON GROUP RAD1.		"Display Radiobutton

START-OF-SELECTION.

*check for authorization to display the data
  PERFORM AUTH_CHECK.

*Get the details of datasource
  CALL FUNCTION 'RSA1_SINGLE_OLTPSOURCE_GET'
    EXPORTING
      I_OLTPSOURCE = C_ISOURCE
    IMPORTING
      E_T_FIELDS   = E_T_FIELDS
    EXCEPTIONS
      NO_AUTHORITY = 1
      NOT_EXIST    = 2
      INCONSISTENT = 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.

*Get fields which are active and enabled for selection (same as in RSA3)
  LOOP AT E_T_FIELDS ASSIGNING <FS_FIELDS>.
    IF <FS_FIELDS>-SELECTION <> C_ACTIVE. "'A'.
      G_WA_FIELDS-FIELDNM = <FS_FIELDS>-FIELD.
      APPEND G_WA_FIELDS TO IT_FIELDS.
    ENDIF.
  ENDLOOP.

*GET currency type 10 value for Comapny Code Curreny
  PERFORM GET_SELECTION_CRITERIA USING C_CURTYPE
                                            C_INCLUDE
                                            C_EQUAL
                                            C_TEN  
                                            C_BLANK."''.

*GET fical period for selection
  LOOP AT  S_FISCP.
    PERFORM GET_SELECTION_CRITERIA USING C_FISCPER
                                              S_FISCP-SIGN
                                              S_FISCP-OPTION
                                              S_FISCP-LOW
                                              S_FISCP-HIGH.
  ENDLOOP.

*GET the company codes for selection
  LOOP AT S_COMP.
    PERFORM GET_SELECTION_CRITERIA USING C_COMP
                                              S_COMP-SIGN
                                              S_COMP-OPTION
                                              S_COMP-LOW
                                              S_COMP-HIGH.
  ENDLOOP.

*initialize the read process first
  CALL FUNCTION 'FAGL_GET_TT_DATA_LEAD'
    EXPORTING
      I_REQUNR                     = C_REQUNR
      I_ISOURCE                    = C_ISOURCE
      I_MAXSIZE                    = C_MAXSIZE
      I_INITFLAG                   = C_INITFLAG
      I_UPDMODE                    = C_UPDMODE
    TABLES
      I_T_SELECT                   = IT_SELECT
      I_T_FIELDS                   = IT_FIELDS
    EXCEPTIONS
      NO_MORE_DATA                 = 1
      ERROR_PASSED_TO_MESS_HANDLER = 2
      CANCELED_BY_USER             = 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.

*read the data now
  CALL FUNCTION 'FAGL_GET_TT_DATA_LEAD'
    EXPORTING
      I_REQUNR                     = C_REQUNR
    TABLES
      I_T_FIELDS                   = IT_FIELDS
      E_T_DATA                     = IT_EXTRACT
    EXCEPTIONS
      NO_MORE_DATA                 = 1
      ERROR_PASSED_TO_MESS_HANDLER = 2
      CANCELED_BY_USER             = 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.


*This loop is for making changes in data and transform it into BW data as per calculation/modificatoions written in transformation at your end or some new logic as per customer requirement.
  LOOP AT IT_EXTRACT ASSIGNING <FS_EXTRACT>.
    MOVE-CORRESPONDING <FS_EXTRACT> TO IT_RESULT.
*   Calculation of Amount
    IF <FS_EXTRACT>-VALUTYP < 1.
      IT_RESULT-AMOUNT = <FS_EXTRACT>-DEBIT - <FS_EXTRACT>-CREDIT.
    ENDIF.

*   Calculaton of CS_ITEM
    CONCATENATE <FS_EXTRACT>-RACCT+1(6) C_ZERO INTO IT_RESULT-CS_ITEM.

*   Calcualtion of CS_CHART
    IT_RESULT-CS_CHART = C_CS_CHART.

*   Calculation of Consolidation Unit
    CONCATENATE C_UNIT <FS_EXTRACT>-RBUKRS INTO IT_RESULT-CONS_UNIT.

*   Calculation of Posting Period
    IT_RESULT-FISCPER3 = <FS_EXTRACT>-FISCPER+4(3).

    APPEND IT_RESULT.
    CLEAR IT_RESULT.
  ENDLOOP.

  IF P_DCHK <> C_CHECKED.
    PERFORM F_FIELD_CATALOG.
    PERFORM DISPLAY_DATA.
  ELSE.
    LOOP AT IT_RESULT.
      DO.
        ASSIGN COMPONENT SY-INDEX OF STRUCTURE IT_RESULT TO <FS_VALUE>.
        IF SY-SUBRC <> 0.
          EXIT.
        ENDIF.
        IF SY-INDEX = 1.
          IT_DOWNLOAD-LINE = <FS_VALUE>.
        ELSE.
          G_VALUE = <FS_VALUE>.
          CONCATENATE IT_DOWNLOAD-LINE C_DELIMITER G_VALUE
                    INTO IT_DOWNLOAD-LINE.
        ENDIF.
      ENDDO.
      APPEND IT_DOWNLOAD.
      CLEAR: IT_DOWNLOAD, G_VALUE.
    ENDLOOP.
    PERFORM FCSV_DOWNLOAD.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  DISPLAY_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM DISPLAY_DATA .

  DATA: L_LAYOUT  TYPE SLIS_LAYOUT_ALV,
        L_PROGRAM TYPE SY-REPID.


  L_PROGRAM = SY-REPID.
  L_LAYOUT-COLWIDTH_OPTIMIZE = C_CHECKED.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      I_CALLBACK_PROGRAM = L_PROGRAM
      IS_LAYOUT          = L_LAYOUT
      IT_FIELDCAT        = IT_FIELDCAT
    TABLES
      T_OUTTAB           = IT_RESULT 
    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.

ENDFORM.      "DISPLAY_DATA

*&---------------------------------------------------------------------*
*&      Form  fcsv_download
*&---------------------------------------------------------------------*
FORM FCSV_DOWNLOAD.
  DATA: L_FILENAME TYPE STRING,
        L_PATH TYPE STRING,
        L_FULLPATH TYPE STRING,
        L_RESULT TYPE I,
        L_FNAME TYPE STRING.


  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
    EXPORTING
      WINDOW_TITLE      = C_FILE "'File Directory'
      DEFAULT_EXTENSION = 'csv'
      INITIAL_DIRECTORY = 'C:\'  "You can also define intitial directory as per your requirement.
    CHANGING
      FILENAME          = L_FILENAME
      PATH              = L_PATH
      FULLPATH          = L_FULLPATH
      USER_ACTION       = L_RESULT.

  CHECK L_RESULT <> 9.

  L_FNAME = L_FULLPATH.


*download file in excel in CSV format
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      WRITE_FIELD_SEPARATOR   = 'X'
      FILENAME                = L_FNAME
      FILETYPE                = 'ASC'
    TABLES
      DATA_TAB                = IT_DOWNLOAD 
    EXCEPTIONS
      FILE_WRITE_ERROR        = 1
      NO_BATCH                = 2
      GUI_REFUSE_FILETRANSFER = 3
      INVALID_TYPE            = 4
      NO_AUTHORITY            = 5
      UNKNOWN_ERROR           = 6
      HEADER_NOT_ALLOWED      = 7
      SEPARATOR_NOT_ALLOWED   = 8
      FILESIZE_NOT_ALLOWED    = 9
      HEADER_TOO_LONG         = 10
      DP_ERROR_CREATE         = 11
      DP_ERROR_SEND           = 12
      DP_ERROR_WRITE          = 13
      UNKNOWN_DP_ERROR        = 14
      ACCESS_DENIED           = 15
      DP_OUT_OF_MEMORY        = 16
      DISK_FULL               = 17
      DP_TIMEOUT              = 18
      FILE_NOT_FOUND          = 19
      DATAPROVIDER_EXCEPTION  = 20
      CONTROL_FLUSH_ERROR     = 21
      OTHERS                  = 22.
  IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.


ENDFORM.                    " FCSV_DOWNLOAD

*&---------------------------------------------------------------------*
*&      Form  GET_SELECTION_CRITERIA
*&---------------------------------------------------------------------*
FORM GET_SELECTION_CRITERIA  USING    P_FIELDNAME
                                           P_SIGN
                                           P_OPTION
                                           VALUE(P_LOW)
                                           VALUE(P_HIGH).

  DATA: L_WA_SELECT   TYPE SBIWA_S_SELECT.

  L_WA_SELECT-FIELDNM = P_FIELDNAME.
  L_WA_SELECT-SIGN    = P_SIGN.
  L_WA_SELECT-OPTION  = P_OPTION.
  L_WA_SELECT-LOW     = P_LOW.
  L_WA_SELECT-HIGH    = P_HIGH.
  APPEND L_WA_SELECT TO IT_SELECT.

ENDFORM.                    " GET_SELECTION_CRITERIA

*&---------------------------------------------------------------------*
*&      Form  AUTH_CHECK
*&---------------------------------------------------------------------*
FORM AUTH_CHECK.
  DATA: BEGIN OF LT_BUKRS OCCURS 0,
            BUKRS TYPE BUKRS,
        END OF LT_BUKRS.

  SELECT BUKRS FROM T001 INTO CORRESPONDING FIELDS OF TABLE LT_BUKRS
         WHERE BUKRS IN S_COMP.
  LOOP AT LT_BUKRS.
"You can ask Security Team to maintain one authority check object on basis of company code
    AUTHORITY-CHECK OBJECT 'F_BKPF_BUK'		
             ID 'BUKRS' FIELD LT_BUKRS-BUKRS
             ID 'ACTVT' FIELD '03'.   "Modified for display authorization
    IF SY-SUBRC <> 0.
      MESSAGE TEXT-024 TYPE C_ERROR DISPLAY LIKE C_INFO.
    ENDIF.
  ENDLOOP.

  IF P_DCHK EQ C_CHECKED.
*Check authorization for download
     AUTHORITY-CHECK OBJECT 'S_GUI'
              ID 'ACTVT' FIELD '61'.
     IF SY-SUBRC <> 0.
       MESSAGE TEXT-025 TYPE C_ERROR DISPLAY LIKE C_INFO.
     ENDIF.

   ENDIF.

ENDFORM.                    " AUTH_CHECK


*&---------------------------------------------------------------------*
*&      Form  F_FIELD_CATALOG.
*&---------------------------------------------------------------------*
FORM F_FIELD_CATALOG.

  CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
    EXPORTING
      I_STRUCTURE_NAME       = 'ZSEM_EXTRACT_STR'
    CHANGING
      CT_FIELDCAT            = IT_FIELDCAT[]
    EXCEPTIONS
      INCONSISTENT_INTERFACE = 1
      PROGRAM_ERROR          = 2
      OTHERS                 = 3.
  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 IT_FIELDCAT IS NOT INITIAL.

    LOOP AT IT_FIELDCAT ASSIGNING <FS_FIELDCAT>.
      IF <FS_FIELDCAT>-FIELDNAME EQ TEXT-007. "'DEPRAREA'.
        <FS_FIELDCAT>-SELTEXT_L = TEXT-008. "'Depreciation Area Real or Derived'.
        <FS_FIELDCAT>-SELTEXT_M = TEXT-009. "'Depr.Area Real/Dervd'.
        <FS_FIELDCAT>-SELTEXT_S = TEXT-010. "'Deprec.area'.

      ELSEIF <FS_FIELDCAT>-FIELDNAME EQ TEXT-011. "'RASSC1'.
        <FS_FIELDCAT>-SELTEXT_L = TEXT-012. "'Company Code of Partner'.
        <FS_FIELDCAT>-SELTEXT_M = TEXT-013. "'CompCode Partner'.
        <FS_FIELDCAT>-SELTEXT_S = TEXT-014. "'Partner Code'.

      ELSEIF <FS_FIELDCAT>-FIELDNAME EQ TEXT-015. "'CS_ITEM'.
        <FS_FIELDCAT>-SELTEXT_L = TEXT-016. "'Item'.
        <FS_FIELDCAT>-SELTEXT_M = TEXT-016. "'Item'.
        <FS_FIELDCAT>-SELTEXT_S = TEXT-016. "'Item'.

      ELSEIF <FS_FIELDCAT>-FIELDNAME EQ TEXT-017. "'CS_CHART'.
        <FS_FIELDCAT>-SELTEXT_L = TEXT-018. "'Consolidation Chart of Accounts'.
        <FS_FIELDCAT>-SELTEXT_M = TEXT-019. "'Cons Chart of Accts'.
        <FS_FIELDCAT>-SELTEXT_S = TEXT-020. "'Cons Chart of A/c'.

      ELSEIF <FS_FIELDCAT>-FIELDNAME EQ TEXT-021. "'CONS_UNIT'.
        <FS_FIELDCAT>-SELTEXT_L = TEXT-022. "'Consolidation Unit'.
        <FS_FIELDCAT>-SELTEXT_M = TEXT-023. "'Cons Unit'.
        <FS_FIELDCAT>-SELTEXT_S = TEXT-023. "'Cons Unit'.
      ENDIF.

    ENDLOOP.

  ENDIF.

ENDFORM.                    "F_FIELD_CATALOG

 

There a lots of text elements used in this program, so you can refer to below screenshot for the same.

Also, I have used one custom structure ZSEM_EXTRACT_STR (created in SE11). For fields and types, you can refer to below screenshot.

 

One can make it more user-friendly by making a custom t-code for this report. Same can be achieved by transaction SE93.

Regarding the authorization object ACTVT field description, once can always check in SAP transaction SUIM ( I didn’t remember the tcode earlier and was trying to SWIM in SAP for long time 😀 )

 

Note: This code worked fine as 0SEM_BCS_10 supports “delta only via full load

Thanks for reading the post. Please feel free to add comments / suggestions.

 

Cheers

Sushil

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.