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
Be the first to leave a comment
You must be Logged on to comment or reply to a post.