Document Name/Version: TRoR_0001/ v1.0
Objective: To upload data from multiple excel sheets.
Description: This program is used to upload data from multiple excel sheets at a time.
Search Keywords / Tags: N/A.
SAP Module: All modules
Business Area: All Areas
Object Category: Report/Interface
SAP Version: ECC 6.0
Author: Sravan Kumar Routhu
Complexity: Medium
Client/Customer/s: Johnson & Johnson
Object List:
No.
|
Object Name
|
Object Type
|
Description
|
1.
|
YSAN_MULTI_EXCELS
|
Report/Interface
|
This program is used to upload data from multiple Excel sheets.
|
1. Code + Comments for YSAN_MULTI_EXCELS
*&---------------------------------------------------------------------*
*& Report YSAN_MULTI_EXCELS
*&
*&---------------------------------------------------------------------*
REPORT ysan_multi_excels LINE-SIZE 200.
TYPE-POOLS: soi .
TYPES: BEGIN OF ty_inbdeliv_pos,
vbeln TYPE lips-vbeln,
posnr TYPE lips-posnr,
vgbel TYPE lips-vgbel,
vgpos TYPE lips-vgpos,
lifex TYPE likp-lifex,
END OF ty_inbdeliv_pos.
TYPES: BEGIN OF ty_inbdeliv_gtd,
vbeln TYPE likp-vbeln,
lifex TYPE likp-lifex,
END OF ty_inbdeliv_gtd.
TYPES: BEGIN OF ty_screen_itab,
invoice TYPE vbrk-vbeln, "Invoice
ponumb TYPE ekpo-ebeln, "PO number
poitem TYPE ekpo-ebelp, "PO item
matnr TYPE ekpo-matnr, "PO material
menge TYPE ekpo-menge, "PO quantity
custamt TYPE bapi_fwbas, "Custom amount
vatrat(10) TYPE c, "VAT Rate
vatamt TYPE bapi_fwbas, "VAT Amount
gtdmtnr TYPE lips-matnr, "GTD Material number
vbeln TYPE lips-vbeln, "Inbound Delivery
posnr TYPE lips-posnr, "Inbound Delivery Item
END OF ty_screen_itab.
TYPES: BEGIN OF ty_itabpos,
ebeln TYPE ekpo-ebeln, "PO Number
ebelp TYPE lips-vgpos, "PO item
matnr TYPE ekpo-matnr, "Product ID
menge TYPE ekpo-menge, "Qty
END OF ty_itabpos.
TYPES: BEGIN OF ty_itabgtd,
lifex TYPE likp-lifex, "Invoice
matnr TYPE lips-matnr, "Material
vatrat(10) TYPE c , "VAT Rate
vatamt TYPE bapi_fwbas, "VAT Amount
custamt TYPE bapi_fwbas, "Custom amount
gtdmtnr TYPE lips-matnr, "GTD Material number
END OF ty_itabgtd.
TYPES: BEGIN OF wa_tab,
po_number TYPE bstnr,
po_item TYPE ebelp,
gl_account TYPE hkont,
tax_code TYPE mwskz,
item_amount TYPE bapiwrbtr,
item_text TYPE sgtxt,
END OF wa_tab.
TYPES: BEGIN OF t_file.
INCLUDE STRUCTURE bapi_incinv_create_item.
TYPES: tax_base_amount TYPE bapi_fwbas
,vbeln TYPE vbeln
,posnr TYPE posnr
,item_amount_vat TYPE bapiwrbtr
,row TYPE numc04.
TYPES: END OF t_file.
*------------------------------------------------------------------------
Internal tables Declaration
*------------------------------------------------------------------------
DATA: it_inbdeliv_gtd TYPE TABLE OF ty_inbdeliv_gtd WITH HEADER LINE,
it_inbdeliv_pos TYPE TABLE OF ty_inbdeliv_pos WITH HEADER LINE,
it_screen_itab TYPE TABLE OF ty_screen_itab WITH HEADER LINE,
it_screen_itab3 TYPE TABLE OF ty_screen_itab WITH HEADER LINE,
it_screen_itab4 TYPE TABLE OF ty_screen_itab WITH HEADER LINE,
it_screen_itab5 TYPE TABLE OF ty_screen_itab WITH HEADER LINE.
DATA: g_headerdata LIKE bapi_incinv_create_header
,it_gldata TYPE STANDARD TABLE OF bapi_incinv_create_gl_account
,wa_gldata TYPE bapi_incinv_create_gl_account
,it_itemdata TYPE STANDARD TABLE OF bapi_incinv_create_item
,h_wwert TYPE bkpf-wwert
,h_country(100)
,h_pay_dat(23)
,h_dop(264)
,fi_ok(1).
DATA: i_gtdheader TYPE soi_generic_table,
i_po TYPE soi_generic_table,
i_gtd TYPE soi_generic_table,
wa_data TYPE soi_generic_item,
g_header_text(256).
DATA: user TYPE usr01.
DATA: datfm TYPE dd07v.
DATA: dcpfm TYPE dd07v.
DATA: langu LIKE sy-langu.
DATA: it_itabpos TYPE TABLE OF
ty_itabpos WITH HEADER LINE,
it_itabgtd TYPE TABLE OF
ty_itabgtd WITH HEADER LINE.
DATA: it_tab_file TYPE TABLE OF t_file
,it_tab_file_all TYPE TABLE OF t_file
,wa_tab_file TYPE t_file.
DATA: it_tab2 TYPE STANDARD TABLE OF wa_tab.
DATA: wa_tab2 TYPE wa_tab.
DATA: it_lines TYPE STANDARD TABLE OF tline WITH HEADER LINE
,l_tdname TYPE thead-tdname
,l_head TYPE thead.
DATA l_cnt_file TYPE int4.
DATA: lf_exist TYPE c.
DATA: count TYPE i
,count_1 TYPE i
,count_2 TYPE p DECIMALS 2
,count_3 TYPE i
,l_max_gtd TYPE int4
,curr_doc TYPE i
,next_doc TYPE i.
DATA: gv_date(2) TYPE n,
gv_month(2) TYPE n,
gv_year(4) TYPE n.
DATA: gv_check TYPE c,
gv_qty TYPE menge_d,
gv_count TYPE i.
DATA: oref_container TYPE REF TO cl_gui_custom_container,
iref_control TYPE REF TO i_oi_container_control,
iref_document TYPE REF TO i_oi_document_proxy,
iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
iref_error TYPE REF TO i_oi_error.
DATA: v_document_url TYPE c LENGTH 256,
i_sheets TYPE soi_sheets_table,
wa_sheets TYPE soi_sheets,
i_data TYPE soi_generic_table,
i_ranges TYPE soi_range_list,
p_rows TYPE i VALUE 100,
p_cols TYPE i VALUE 100.
PARAMETERS : p_file TYPE c LENGTH 256 DEFAULT 'C:\Documents and Settings\sjetty\Desktop\sravan\GTD_NEW.XLS'.
START-OF-SELECTION.
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = iref_control
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
CREATE OBJECT oref_container
EXPORTING
container_name = 'CONT'
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5
OTHERS = 6.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH 'Error while creating container'.
ENDIF.
CALL METHOD iref_control->init_control
EXPORTING
inplace_enabled = 'X'
r3_application_name = 'EXCEL CONTAINER'
parent = oref_container
IMPORTING
error = iref_error
EXCEPTIONS
javabeannotsupported = 1
OTHERS = 2.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
CALL METHOD iref_control->get_document_proxy
EXPORTING
document_type = soi_doctype_excel_sheet
IMPORTING
document_proxy = iref_document
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
CONCATENATE 'FILE://' p_file INTO v_document_url.
CALL METHOD iref_document->open_document
EXPORTING
document_title = 'Excel'
document_url = v_document_url
open_inplace = 'X'
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL METHOD iref_document->get_spreadsheet_interface
EXPORTING
no_flush = ' '
IMPORTING
error = iref_error
sheet_interface = iref_spreadsheet.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL METHOD iref_spreadsheet->get_sheets
EXPORTING
no_flush = ' '
IMPORTING
sheets = i_sheets
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
LOOP AT i_sheets INTO wa_sheets.
CALL METHOD iref_spreadsheet->select_sheet
EXPORTING
name = wa_sheets-sheet_name
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
EXIT.
ENDIF.
CALL METHOD iref_spreadsheet->set_selection
EXPORTING
top = 1
left = 1
rows = p_rows
columns = p_cols.
CALL METHOD iref_spreadsheet->insert_range
EXPORTING
name = 'Test'
rows = p_rows
columns = p_cols
no_flush = ''
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
EXIT.
ENDIF.
REFRESH i_data.
CALL METHOD iref_spreadsheet->get_ranges_data
EXPORTING
all = 'X'
IMPORTING
contents = i_data
error = iref_error
CHANGING
ranges = i_ranges.
DELETE i_data WHERE value IS INITIAL OR value = space.
CASE wa_sheets-sheet_name.
WHEN 'GTD Header'.
i_gtdheader[] = i_data[]. "Store GTD Header tab into internal table
WHEN 'PO'.
i_po[] = i_data[]. "Store PO tab into internal table
WHEN 'GTD'.
i_gtd[] = i_data[]. "Store GTD into internal table
ENDCASE.
ENDLOOP.
CALL METHOD iref_document->close_document
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
ENDIF.
CALL METHOD iref_document->release_document
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
DATA: l_item_text TYPE sgtxt.
ENDIF.
*Passing data to respective tables
LOOP AT i_gtdheader INTO wa_data.
CASE wa_data-row.
WHEN 8.
IF wa_data-column = 9.
g_headerdata-diff_inv = wa_data-value. "Customs office (vendor #)
SHIFT g_headerdata-diff_inv RIGHT DELETING TRAILING space.
TRANSLATE g_headerdata-diff_inv USING ' 0'.
ELSEIF wa_data-column = 12.
SPLIT wa_data-value AT '.' INTO gv_month gv_date gv_year.
CONCATENATE gv_year gv_month gv_date INTO g_headerdata-doc_date.
ENDIF.
WHEN 9.
IF wa_data-column = 9.
g_headerdata-item_text = wa_data-value. "CUSTOMS DECL # ("???"+?, 26 characters max)
g_header_text = g_headerdata-item_text.
ELSEIF wa_data-column = 12.
SPLIT wa_data-value AT '.' INTO gv_month gv_date gv_year.
CONCATENATE gv_year gv_month gv_date INTO g_headerdata-pstng_date. "Posting date
ENDIF.
WHEN 10.
IF wa_data-column = 9.
g_headerdata-ref_doc_no = wa_data-value. " PAYMENT DOCS ## (16 characters max)
ELSEIF wa_data-column = 12.
SPLIT wa_data-value AT '.' INTO gv_month gv_date gv_year.
CONCATENATE gv_year gv_month gv_date INTO h_wwert.
ENDIF.
WHEN 11.
IF wa_data-column = 9.
h_pay_dat = wa_data-value.
l_item_text = g_headerdata-item_text.
CONCATENATE g_headerdata-item_text h_pay_dat INTO g_headerdata-item_text
SEPARATED BY space. " PAYMENT DOCS DATES (23 characters max)
ELSEIF wa_data-column = 12.
h_country = wa_data-value.
CONCATENATE l_item_text h_country INTO h_dop
SEPARATED BY space.
ENDIF.
WHEN 12.
IF wa_data-column = 9.
g_headerdata-header_txt = wa_data-value. "INVOICES INCLUDED IN DECLARATION (25 characters max)
ENDIF.
WHEN 13.
IF wa_data-column = 9.
TRANSLATE wa_data-value USING ',.'.
g_headerdata-del_costs = wa_data-value. " Unplanned delivery costs (RUB)
ENDIF.
ENDCASE.
ENDLOOP.
* PO tab
LOOP AT i_po INTO wa_data.
IF wa_data-row > 1.
CASE wa_data-column.
WHEN '1' .
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = wa_data-value
IMPORTING
output = it_itabpos-ebeln.
WHEN '2' .
it_itabpos-ebelp = wa_data-value.
WHEN '3' .
it_itabpos-matnr = wa_data-value.
WHEN '4' .
it_itabpos-menge = wa_data-value.
APPEND it_itabpos.
ENDCASE .
ENDIF.
ENDLOOP.
* GTD tab
LOOP AT i_gtd INTO wa_data.
IF wa_data-row > 1.
CASE wa_data-column .
WHEN '1' .
it_itabgtd-lifex = wa_data-value.
WHEN '2' .
it_itabgtd-matnr = wa_data-value.
WHEN '3' .
IF wa_data-value = '0'.
wa_data-value = '0%'.
ENDIF.
it_itabgtd-vatrat = wa_data-value.
WHEN '4' .
REPLACE ALL OCCURRENCES OF ',' IN wa_data-value WITH '.'.
it_itabgtd-vatamt = wa_data-value.
WHEN '5' .
REPLACE ALL OCCURRENCES OF ',' IN wa_data-value WITH '.'.
it_itabgtd-custamt = wa_data-value.
WHEN '6' .
it_itabgtd-gtdmtnr = wa_data-value.
APPEND it_itabgtd.
ENDCASE .
ENDIF.
ENDLOOP.
LOOP AT it_itabgtd.
WRITE :/ it_itabgtd-lifex ,
it_itabgtd-matnr ,
it_itabgtd-vatrat,
it_itabgtd-vatamt ,
it_itabgtd-custamt ,
it_itabgtd-gtdmtnr .
ENDLOOP.
Input Screen:
Sample input file:
No comments:
Post a Comment