CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_PATH
I_BEGIN_COL = '1'
I_BEGIN_ROW = '1'
I_END_COL = '18'
I_END_ROW = '65535'
TABLES
INTERN = I_EXCEL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 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.
*&---------------------------------------------------------------------*
*& Report ZBEN_SFTEST01 *
*& *
*&---------------------------------------------------------------------*
*& *
*& *
*&---------------------------------------------------------------------*
REPORT ZBEN_SFTEST01.
TABLES: EKKO, EKPO, EBAN, T024.
DATA: BEGIN OF I_EKKO OCCURS 0,
ZYEAR(4) TYPE C,
ZMONTH(2) TYPE C,
EBELN LIKE EKKO-EBELN,
BSTYP LIKE EKKO-BSTYP,
BSART LIKE EKKO-BSART,
AEDAT LIKE EKKO-AEDAT,
EKGRP LIKE EKKO-EKGRP,
END OF I_EKKO.
DATA: BEGIN OF I_EKPO OCCURS 0,
EBELN LIKE EKPO-EBELN,
EBELP LIKE EKPO-EBELP,
LOEKZ LIKE EKPO-LOEKZ,
PSTYP LIKE EKPO-PSTYP,
KNTTP LIKE EKPO-KNTTP,
MENGE LIKE EKPO-MENGE,
MEINS LIKE EKPO-MEINS,
BANFN LIKE EKPO-BANFN,
BNFPO LIKE EKPO-BNFPO,
END OF I_EKPO.
DATA: BEGIN OF I_EBAN OCCURS 0,
BANFN LIKE EBAN-BANFN,
BNFPO LIKE EBAN-BNFPO,
BSART LIKE EBAN-BSART,
BSTYP LIKE EBAN-BSTYP,
FRGDT LIKE EBAN-FRGDT,
PREIS LIKE EBAN-PREIS,
PEINH LIKE EBAN-PEINH,
WAERS LIKE EBAN-WAERS,
END OF I_EBAN.
DATA: BEGIN OF I_T024 OCCURS 0,
EKGRP LIKE T024-EKGRP,
EKNAM LIKE T024-EKNAM,
END OF I_T024.
DATA: BEGIN OF GT_RPT OCCURS 0,
ZYEAR(4) TYPE C,
ZMONTH(2) TYPE C,
EKGRP LIKE EKKO-EKGRP,
EKNAM LIKE T024-EKNAM,
EBELN LIKE EKKO-EBELN,
EBELP LIKE EKPO-EBELP,
AEDAT LIKE EKKO-AEDAT,
MENGE LIKE EKPO-MENGE,
MEINS LIKE EKPO-MEINS,
BANFN LIKE EKPO-BANFN,
BNFPO LIKE EKPO-BNFPO,
FRGDT LIKE EBAN-FRGDT,
PREIS LIKE EBAN-PREIS,
PEINH LIKE EBAN-PEINH,
AMOUNT LIKE EBAN-PREIS,
WAERS LIKE EBAN-WAERS,
END OF GT_RPT.
DATA: Z_TABIX LIKE SY-TABIX.
DATA: X_TABIX LIKE SY-TABIX.
DATA: W_TABIX LIKE SY-TABIX.
DATA: FM_NAME TYPE RS38L_FNAM.
DATA: WA_EKKO LIKE EKKO OCCURS 0 WITH HEADER LINE.
DATA:
G_FNAME TYPE RS38L_FNAM,
G_CONTROL_PARAM TYPE SSFCTRLOP,
G_COMPOSER_PARAM TYPE SSFCOMPOP,
G_OUTPUT_INFO TYPE SSFCRESCL.
DATA: BEGIN OF SUBTOTAL OCCURS 0,
ZYEAR(4) TYPE C,
ZMONTH(2) TYPE C,
EKGRP LIKE EKKO-EKGRP,
EKNAM LIKE T024-EKNAM,
MENGE LIKE EKPO-MENGE,
MEINS LIKE EKPO-MEINS,
AMOUNT LIKE EBAN-PREIS,
WAERS LIKE EBAN-WAERS,
END OF SUBTOTAL.
DATA: SUB_TMP LIKE SUBTOTAL OCCURS 0 WITH HEADER LINE.
************************************************************************
* SELECT-OPTIONS / PARAMETERS *
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK BK1 WITH FRAME.
SELECT-OPTIONS: S_DATE FOR EKKO-AEDAT(6) DEFAULT '200007' TO '200410' OBLIGATORY, "
S_EKGRP FOR EKKO-EKGRP.
SELECT-OPTIONS: S_DATES FOR SY-DATUM NO-DISPLAY.
SELECTION-SCREEN END OF BLOCK BK1.
AT SELECTION-SCREEN.
REFRESH S_DATES.
LOOP AT S_DATE.
MOVE-CORRESPONDING S_DATE TO S_DATES.
S_DATES-LOW+6(2) = '01'.
IF S_DATE-HIGH IS INITIAL.
S_DATES-HIGH = S_DATES-LOW.
ENDIF.
S_DATES-HIGH+6(2) = '31'.
IF S_DATES(3) = 'IEQ'.
S_DATES(3) = 'IBT'.
ELSEIF S_DATES(3) = 'INE'.
S_DATES(3) = 'INB'.
ELSEIF S_DATES(3) = 'EEQ'.
S_DATES(3) = 'EBT'.
ELSEIF S_DATES(3) = 'ENE'.
S_DATES(3) = 'ENB'.
ENDIF.
COLLECT S_DATES.
ENDLOOP.
************************************************************************
* START-OF-SELECTION Event *
************************************************************************
START-OF-SELECTION.
PERFORM GET_DATA_FROM_DATABASE.
PERFORM MERGE_ITABS.
************************************************************************
* END-OF-SELECTION Event *
************************************************************************
END-OF-SELECTION.
PERFORM OUTPUT_REPORT.
*&---------------------------------------------------------------------*
*& Form GET_DATA_FROM_DATABASE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM GET_DATA_FROM_DATABASE .
SELECT EBELN BSTYP BSART AEDAT EKGRP
INTO CORRESPONDING FIELDS OF TABLE I_EKKO
FROM EKKO
WHERE AEDAT IN S_DATES
AND EKGRP IN S_EKGRP
AND LOEKZ EQ ''.
SORT I_EKKO BY EBELN.
IF NOT I_EKKO[] IS INITIAL.
SELECT EKGRP EKNAM
INTO CORRESPONDING FIELDS OF TABLE I_T024
FROM T024
FOR ALL ENTRIES IN I_EKKO
WHERE EKGRP = I_EKKO-EKGRP.
SORT I_T024 BY EKGRP.
SELECT EBELN EBELP LOEKZ PSTYP KNTTP MENGE MEINS BANFN BNFPO
INTO CORRESPONDING FIELDS OF TABLE I_EKPO
FROM EKPO
FOR ALL ENTRIES IN I_EKKO
WHERE EBELN = I_EKKO-EBELN
AND LOEKZ EQ ''
AND BANFN NE ''
AND BNFPO NE ''.
ENDIF.
SORT I_EKPO BY EBELN.
IF NOT I_EKPO[] IS INITIAL.
SELECT BANFN BNFPO BSART BSTYP FRGDT PREIS PEINH WAERS
INTO CORRESPONDING FIELDS OF TABLE I_EBAN FROM EBAN
FOR ALL ENTRIES IN I_EKPO
WHERE BANFN = I_EKPO-BANFN
AND BNFPO = I_EKPO-BNFPO
AND FRGDT > '00000000'.
ENDIF.
SORT I_EBAN BY BANFN BNFPO.
ENDFORM. " GET_DATA_FROM_DATABASE
*&---------------------------------------------------------------------*
*& Form MERGE_ITABS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM MERGE_ITABS .
LOOP AT I_EKPO.
MOVE-CORRESPONDING I_EKPO TO GT_RPT.
APPEND GT_RPT. CLEAR GT_RPT.
ENDLOOP.
LOOP AT GT_RPT.
X_TABIX = SY-TABIX.
READ TABLE I_EKKO WITH KEY EBELN = GT_RPT-EBELN
BINARY SEARCH.
IF SY-SUBRC = 0.
GT_RPT-ZYEAR = I_EKKO-AEDAT(4).
GT_RPT-ZMONTH = I_EKKO-AEDAT+4(2).
GT_RPT-AEDAT = I_EKKO-AEDAT.
GT_RPT-EKGRP = I_EKKO-EKGRP.
MODIFY GT_RPT INDEX X_TABIX TRANSPORTING ZYEAR ZMONTH AEDAT EKGRP.
ENDIF.
READ TABLE I_EBAN WITH KEY BANFN = GT_RPT-BANFN
BNFPO = GT_RPT-BNFPO
BINARY SEARCH.
IF SY-SUBRC = 0.
GT_RPT-FRGDT = I_EBAN-FRGDT.
GT_RPT-PREIS = I_EBAN-PREIS.
GT_RPT-PEINH = I_EBAN-PEINH.
GT_RPT-WAERS = I_EBAN-WAERS.
MODIFY GT_RPT INDEX X_TABIX TRANSPORTING FRGDT PREIS PEINH WAERS.
ENDIF.
READ TABLE I_T024 WITH KEY EKGRP = GT_RPT-EKGRP.
IF SY-SUBRC = 0.
GT_RPT-EKNAM = I_T024-EKNAM.
MODIFY GT_RPT INDEX X_TABIX TRANSPORTING EKNAM.
ENDIF.
CLEAR GT_RPT.
ENDLOOP.
LOOP AT GT_RPT.
W_TABIX = SY-TABIX.
GT_RPT-AMOUNT = GT_RPT-MENGE * GT_RPT-PREIS / GT_RPT-PEINH.
MODIFY GT_RPT INDEX W_TABIX TRANSPORTING AMOUNT.
MOVE-CORRESPONDING GT_RPT TO SUBTOTAL.
APPEND SUBTOTAL. CLEAR SUBTOTAL.
ENDLOOP.
LOOP AT SUBTOTAL.
COLLECT SUBTOTAL INTO SUB_TMP.
ENDLOOP.
SORT GT_RPT BY ZYEAR ZMONTH EKGRP EBELN EBELP.
SORT SUB_TMP BY ZYEAR ZMONTH EKGRP.
ENDFORM. " MERGE_ITABS
*&---------------------------------------------------------------------*
*& Form OUTPUT_REPORT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM OUTPUT_REPORT .
CALL FUNCTION 'SSF_FUNCTION_MODULE_NAME'
EXPORTING
FORMNAME = 'ZF_BEN_TEST01'
VARIANT = ''
DIRECT_CALL = ''
IMPORTING
FM_NAME = FM_NAME
EXCEPTIONS
NO_FORM = 1
NO_FUNCTION_MODULE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
ENDIF.
G_CONTROL_PARAM-LANGU = SY-LANGU.
G_COMPOSER_PARAM-TDDEST = 'LP01'.
CALL FUNCTION FM_NAME
EXPORTING
YTESTV = 'SAP Information System Co.'
CONTROL_PARAMETERS = G_CONTROL_PARAM
OUTPUT_OPTIONS = G_COMPOSER_PARAM
USER_SETTINGS = ' '
IMPORTING
JOB_OUTPUT_INFO = G_OUTPUT_INFO
TABLES
GT_ITEM = GT_RPT
SUB_ITEM = SUB_TMP
EXCEPTIONS
FORMATTING_ERROR = 1
INTERNAL_ERROR = 2
SEND_ERROR = 3
USER_CANCELED = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
ENDIF.
ENDFORM. " OUTPUT_REPORT