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

SAP Blog

Kayıtlı Üye
Katılım
22 Ara 2017
Mesajlar
1,351
Tepki puanı
7
Puanları
6
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.

TEXT-ELEMENTS.jpg


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

SE11-Structure.jpg




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
1f600.png
)




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

Okumaya devam et...
 
Üst