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

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !