Skip to Content

Hi Friends,

This is my first blog, so, I thought of writing about one of the requirement I received from my customer to get extract of a consolidation datasource 0SEM_BCS_10 in a CSV file with the same format as it shows in BW. As they didn’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 BW, the data was being loaded to a cube via a DTP with adapter properties of Synchronous Extraction SAPI (for Direct Access and Tests). There was also some code written in field routines of transformation mapped between data-source and the cube.

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

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply