*&---------------------------------------------------------------------*
*& Report ZOB08_UPLOAD
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZOB08_UPLOAD.
TYPE-POOLS : SLIS.
TABLES : SSCRFIELDS.
TYPES : BEGIN OF TY_MAKE_EXCEL,
COLUMN01(25),
COLUMN02(25),
COLUMN03(25),
COLUMN04(25),
COLUMN05(25),
COLUMN06(25),
COLUMN07(25),
COLUMN08(25),
END OF TY_MAKE_EXCEL.
TYPES : BEGIN OF TY_ERROR,
LNO(5) TYPE N,
MSG(255),
END OF TY_ERROR.
DATA : IT_MAKE_EXCEL TYPE STANDARD TABLE OF TY_MAKE_EXCEL,
WA_MAKE_EXCEL LIKE LINE OF IT_MAKE_EXCEL,
IT_GET_EXCEL TYPE STANDARD TABLE OF ALSMEX_TABLINE,
WA_GET_EXCEL LIKE LINE OF IT_GET_EXCEL,
IT_FINAL TYPE STANDARD TABLE OF BAPI1093_0,
WA_FINAL LIKE LINE OF IT_FINAL,
IT_ERROR TYPE STANDARD TABLE OF TY_ERROR,
WA_ERROR LIKE LINE OF IT_ERROR,
WA_OB08 LIKE BAPI1093_0,
RETURN LIKE BAPIRET2.
DATA : IT_FCAT TYPE SLIS_T_FIELDCAT_ALV,
WA_FCAT LIKE LINE OF IT_FCAT,
WA_LAYOUT TYPE SLIS_LAYOUT_ALV.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001. "File Selection
PARAMETER : P_FILE TYPE RLGRAP-FILENAME.
SELECTION-SCREEN END OF BLOCK B1.
SELECTION-SCREEN: FUNCTION KEY 1.
INITIALIZATION.
DATA:FUNCTXT TYPE SMP_DYNTXT.
CLEAR FUNCTXT.
FUNCTXT-TEXT = TEXT-002. "Download Excel Format
SSCRFIELDS-FUNCTXT_01 = FUNCTXT.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
PERFORM GET_F4_FILE.
AT SELECTION-SCREEN.
CASE SSCRFIELDS-UCOMM.
WHEN 'FC01'.
PERFORM EXCEL_FORMAT.
WHEN 'ONLI'.
IF P_FILE IS INITIAL.
MESSAGE TEXT-003 TYPE 'E'. "Please Select File Path
ENDIF.
ENDCASE.
START-OF-SELECTION.
PERFORM GET_EXCEL.
PERFORM GET_DATA_FROM_EXCEL.
PERFORM SAVE_RECORD.
IF IT_ERROR[] IS NOT INITIAL.
PERFORM BUILD_FIELDCAT_ERROR.
PERFORM SHOW_MSG.
ENDIF.
*&---------------------------------------------------------------------*
*& Form GET_F4_FILE
*&---------------------------------------------------------------------*
FORM GET_F4_FILE .
CALL FUNCTION 'F4_FILENAME'
EXPORTING
PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = 'P_FILE'
IMPORTING
FILE_NAME = P_FILE.
ENDFORM. " GET_F4_FILE
*&---------------------------------------------------------------------*
*& Form EXCEL_FORMAT
*&---------------------------------------------------------------------*
FORM EXCEL_FORMAT .
REFRESH : IT_MAKE_EXCEL.
CLEAR : WA_MAKE_EXCEL.
CONSTANTS : LV_DEXT TYPE STRING VALUE 'XLS',
LV_FNAME TYPE STRING VALUE 'OB08_UPLOAD',
LV_FDIREC TYPE STRING VALUE 'c:\temp\'.
DATA : LD_FILENAME TYPE STRING,
LD_PATH TYPE STRING,
LD_FULLPATH TYPE STRING,
LD_RESULT TYPE I.
WA_MAKE_EXCEL-COLUMN01 = TEXT-004. "Exchange Rate Type (ExRt)
WA_MAKE_EXCEL-COLUMN02 = TEXT-005. "Valid From (Valid From)
WA_MAKE_EXCEL-COLUMN03 = TEXT-006. "Indirect Quoted Exchange Rate (Indir.quot)
WA_MAKE_EXCEL-COLUMN04 = TEXT-007. "Indirect Ratio for the "From" Currency Units (Ratio(from))
WA_MAKE_EXCEL-COLUMN05 = TEXT-008. "Indirect Ratio for the "To" Currency Units (From)
WA_MAKE_EXCEL-COLUMN06 = TEXT-009. "Direct Quoted Exchange Rate (Dir.quot.)
WA_MAKE_EXCEL-COLUMN07 = TEXT-010. "Direct Ratio for the "From" Currency Units (Ratio (to))
WA_MAKE_EXCEL-COLUMN08 = TEXT-011. "Direct Ratio for the "To" Currency Units (To)
APPEND WA_MAKE_EXCEL TO IT_MAKE_EXCEL.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
EXPORTING
DEFAULT_EXTENSION = LV_DEXT
DEFAULT_FILE_NAME = LV_FNAME
INITIAL_DIRECTORY = LV_FDIREC
CHANGING
FILENAME = LD_FILENAME
PATH = LD_PATH
FULLPATH = LD_FULLPATH
USER_ACTION = LD_RESULT
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 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.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
FILENAME = LD_FULLPATH
FILETYPE = 'ASC'
WRITE_FIELD_SEPARATOR = 'X'
TABLES
DATA_TAB = IT_MAKE_EXCEL[]. "need to declare and populate
ENDFORM. " EXCEL_FORMAT
*&---------------------------------------------------------------------*
*& Form GET_EXCEL
*&---------------------------------------------------------------------*
FORM GET_EXCEL .
REFRESH : IT_GET_EXCEL[].
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FILE
I_BEGIN_COL = '1'
I_BEGIN_ROW = '2'
I_END_COL = '8'
I_END_ROW = '999'
TABLES
INTERN = IT_GET_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.
IF IT_GET_EXCEL IS INITIAL.
MESSAGE 'No data Found in Excel!' TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM. " GET_EXCEL
*&---------------------------------------------------------------------*
*& Form GET_DATA_FROM_EXCEL
*&---------------------------------------------------------------------*
FORM GET_DATA_FROM_EXCEL .
LOOP AT IT_GET_EXCEL INTO WA_GET_EXCEL.
CASE WA_GET_EXCEL-COL.
WHEN '0001'.
WA_FINAL-RATE_TYPE = WA_GET_EXCEL-VALUE.
WHEN '0002'.
CONCATENATE WA_GET_EXCEL-VALUE+6(4) WA_GET_EXCEL-VALUE+3(2) WA_GET_EXCEL-VALUE+0(2) INTO WA_FINAL-VALID_FROM.
WHEN '0003'.
WA_FINAL-EXCH_RATE_V = WA_GET_EXCEL-VALUE.
WHEN '0004'.
IF WA_FINAL-EXCH_RATE_V IS NOT INITIAL.
WA_FINAL-FROM_FACTOR_V = WA_GET_EXCEL-VALUE.
ELSE.
WA_FINAL-FROM_FACTOR = WA_GET_EXCEL-VALUE.
ENDIF.
WHEN '0005'.
WA_FINAL-FROM_CURR = WA_GET_EXCEL-VALUE.
WHEN '0006'.
WA_FINAL-EXCH_RATE = WA_GET_EXCEL-VALUE.
WHEN '0007'.
IF WA_FINAL-EXCH_RATE_V IS NOT INITIAL.
WA_FINAL-TO_FACTOR_V = WA_GET_EXCEL-VALUE.
ELSE.
WA_FINAL-TO_FACTOR = WA_GET_EXCEL-VALUE.
ENDIF.
WHEN '0008'.
WA_FINAL-TO_CURRNCY = WA_GET_EXCEL-VALUE.
ENDCASE.
AT END OF ROW.
APPEND WA_FINAL TO IT_FINAL.
CLEAR WA_FINAL.
ENDAT.
CLEAR : WA_GET_EXCEL.
ENDLOOP.
ENDFORM. " GET_DATA_FROM_EXCEL
*&---------------------------------------------------------------------*
*& Form SAVE_RECORD
*&---------------------------------------------------------------------*
FORM SAVE_RECORD .
DATA : LV_EXL_LNO.
LOOP AT IT_FINAL INTO WA_FINAL.
LV_EXL_LNO = SY-TABIX + 1.
WA_OB08-RATE_TYPE = WA_FINAL-RATE_TYPE.
WA_OB08-FROM_CURR = WA_FINAL-FROM_CURR.
WA_OB08-TO_CURRNCY = WA_FINAL-TO_CURRNCY.
WA_OB08-VALID_FROM = WA_FINAL-VALID_FROM.
WA_OB08-EXCH_RATE = WA_FINAL-EXCH_RATE.
WA_OB08-FROM_FACTOR = WA_FINAL-FROM_FACTOR.
WA_OB08-TO_FACTOR = WA_FINAL-TO_FACTOR.
WA_OB08-EXCH_RATE_V = WA_FINAL-EXCH_RATE_V.
WA_OB08-FROM_FACTOR_V = WA_FINAL-FROM_FACTOR_V.
WA_OB08-TO_FACTOR_V = WA_FINAL-TO_FACTOR_V.
IF WA_OB08-EXCH_RATE IS NOT INITIAL AND WA_OB08-EXCH_RATE_V IS NOT INITIAL.
WA_ERROR-LNO = LV_EXL_LNO.
WA_ERROR-MSG = TEXT-012. "Both (Direct Quoted Exchange Rate) and (Indirect Quoted Exchange Rate) is maintained
APPEND WA_ERROR TO IT_ERROR.
CLEAR : WA_ERROR.
CONTINUE.
ELSEIF WA_OB08-EXCH_RATE IS INITIAL AND WA_OB08-EXCH_RATE_V IS INITIAL.
WA_ERROR-LNO = LV_EXL_LNO.
WA_ERROR-MSG = TEXT-013. "Please maintain (Direct Quoted Exchange Rate) or (Indirect Quoted Exchange Rate)
APPEND WA_ERROR TO IT_ERROR.
CLEAR : WA_ERROR.
CONTINUE.
ENDIF.
CALL FUNCTION 'BAPI_EXCHANGERATE_CREATE'
EXPORTING
EXCH_RATE = WA_OB08
UPD_ALLOW = 'X'
IMPORTING
RETURN = RETURN.
IF RETURN-TYPE = 'E'.
WA_ERROR-LNO = LV_EXL_LNO.
CALL FUNCTION 'FORMAT_MESSAGE'
EXPORTING
ID = RETURN-ID
LANG = SY-LANGU
NO = RETURN-NUMBER
V1 = RETURN-MESSAGE_V1
V2 = RETURN-MESSAGE_V2
V3 = RETURN-MESSAGE_V3
V4 = RETURN-MESSAGE_V4
IMPORTING
MSG = WA_ERROR-MSG
EXCEPTIONS
NOT_FOUND = 1
OTHERS = 2.
APPEND WA_ERROR TO IT_ERROR.
ENDIF.
CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'.
CLEAR : WA_ERROR, WA_OB08, WA_FINAL.
ENDLOOP.
IF IT_ERROR[] IS INITIAL.
MESSAGE 'All Data Saved Successfully!' TYPE 'S'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM. " SAVE_RECORD
*&---------------------------------------------------------------------*
*& Form BUILD_FIELDCAT_ERROR
*&---------------------------------------------------------------------*
FORM BUILD_FIELDCAT_ERROR .
REFRESH : IT_FCAT[].
CLEAR WA_FCAT.
WA_FCAT-FIELDNAME = 'LNO'.
WA_FCAT-SELTEXT_L = TEXT-014. "Excel Line No.
WA_FCAT-EMPHASIZE = 'C610'.
APPEND WA_FCAT TO IT_FCAT.
CLEAR WA_FCAT.
WA_FCAT-FIELDNAME = 'MSG'.
WA_FCAT-SELTEXT_L = TEXT-015. "Error Message
APPEND WA_FCAT TO IT_FCAT.
WA_LAYOUT-COLWIDTH_OPTIMIZE = 'X'.
ENDFORM. " BUILD_FIELDCAT_ERROR
*&---------------------------------------------------------------------*
*& Form SHOW_MSG
*&---------------------------------------------------------------------*
FORM SHOW_MSG .
IF IT_ERROR IS NOT INITIAL.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = SY-REPID
IS_LAYOUT = WA_LAYOUT
IT_FIELDCAT = IT_FCAT
TABLES
T_OUTTAB = IT_ERROR
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.
ENDIF.
ENDFORM. " SHOW_MSG
Selection Screen
