/image%2F1473637%2F20170207%2Fob_adae32_profilbild.png)
Upload-Tool für Hauptbuchbelege (SAPGUI)
Update 13.04.26: Damit das Programm "fiorisiert" werden kann, muss der Zugriff auf die Excel-Datei von OLE auf einen alternativen Zugriff umgestellt werden. OLE ist nur im Kontext der SAP-GUI unterstützt, das Programm in seiner ursprünglich geposteten Form wäre als fiorisierte GUI-App nicht lauffähig. Deshalb wurde der komplette Upload auf die sog. XCO API umgestellt. Das Tool ist nun auch in der FIORI-Welt ausführbar.
Eine viel nachgefragte Funktion im FI ist die Möglichkeit, Belege in Excel aufzubereiten und unter SAP hochzuladen, z.B. für Sachkonten-Umbuchungen im Rahmen von Abschlusstätigkeiten. Unter S/4 (ab Rel. 1511) gibt es hierfür eine Fiori-App zum Upload von Hauptbuchbelegen via Excel-Template, ab Release 1809 gibt es diese auch für kreditorische Belege...
Bis dahin hilft meines Wissens tatsächlich nur eine selbstgebaute Upload-Funktion oder ggf. ein 3rd Party-Produkt.
Ein einfaches Programm inkl. Testlauf-/Prüffunktion findet sich in diesem Artikel. Hinweise zur Installation und zum Format der Quell-Datei finden sich am Ende des Codings...
Werbung
*&---------------------------------------------------------------------*
*& Report ZFI_UPL_DOCUMENTS
*----------------------------------------------------------------------*
* Function : Upload documents from Excel *
*----------------------------------------------------------------------*
* Description : Upload & posting of documents via Excel *
* Author : S.Rohde Consenso Consulting *
* Date/Version : 23.01.2023 1.0
*----------------------------------------------------------------------*
* SRO190723 Enable postings in foreign currency
* SRO030226 Upgrade to S/4 & new Excel Interface
* SRO300326 Ignore negative values in Excel file, sign is determined
* by posting key only
REPORT zfi_upl_documents.
***********************************************************************
******************** GLOBAL DATA **************************************
***********************************************************************
CONSTANTS: gc_delimiter(1) TYPE c VALUE '~'.
SET EXTENDED CHECK OFF.
INCLUDE:
sbal_constants,
<icon>.
SET EXTENDED CHECK ON.
TYPES: gty_currency_amount TYPE TABLE OF bapiaccr09.
DATA:
g_s_log TYPE bal_s_log,
g_log_handle TYPE balloghndl,
g_t_log_handle TYPE bal_t_logh,
g_s_display_profile TYPE bal_s_prof.
DATA: g_dummy TYPE c.
DATA: data_tab TYPE TABLE OF string.
TYPES: BEGIN OF gty_xl_sheet,
col_a TYPE string,
col_b TYPE string,
col_c TYPE string,
col_d TYPE string,
col_e TYPE string,
col_f TYPE string,
col_g TYPE string,
col_h TYPE string,
col_i TYPE string,
col_j TYPE string,
col_k TYPE string,
col_l TYPE string,
col_m TYPE string,
col_n TYPE string,
col_o TYPE string,
col_p TYPE string,
col_q TYPE string,
col_r TYPE string,
col_s TYPE string,
col_t TYPE string,
col_u TYPE string,
col_v TYPE string,
col_w TYPE string,
col_x TYPE string,
col_y TYPE string,
col_z TYPE string,
END OF gty_xl_sheet.
TYPES: gtt_it_xl TYPE STANDARD TABLE OF gty_xl_sheet WITH DEFAULT KEY.
TYPES: BEGIN OF tax_item,
mwskz TYPE mwskz,
direct_tax TYPE abap_bool,
basis TYPE fwbas,
bashw TYPE hwbas,
steuer TYPE fwste,
steuhw TYPE hwste,
END OF tax_item.
DATA: git_taxitems TYPE TABLE OF tax_item,
gs_taxitem TYPE tax_item.
TYPES: BEGIN OF gty_doc,
doc(20) TYPE c,
bschl TYPE bschl,
umskz TYPE umskz,
bukrs TYPE bukrs,
newko TYPE rf05a-newko,
mwskz TYPE mwskz,
wrbtr(15) TYPE c,
waers TYPE waers,
netto TYPE c,
dmbtr(15) TYPE c,
kostl TYPE kostl,
sgtxt TYPE sgtxt,
blart TYPE blart,
bldat(10) TYPE c,
budat(10) TYPE c,
xblnr TYPE xblnr,
bumon TYPE bumon,
bktxt TYPE bktxt,
zuonr TYPE dzuonr,
netdt(10) TYPE c,
bewar TYPE bseg-bewar,
aufnr TYPE aufnr,
prctr TYPE prctr,
kidno TYPE bseg-kidno,
zterm TYPE bseg-zterm,
END OF gty_doc.
DATA: git_docs TYPE TABLE OF gty_doc,
gs_doc TYPE gty_doc.
DATA: gs_t001 TYPE t001,
g_rc TYPE sy-subrc.
DATA: g_tabix TYPE sytabix.
* Interface data for BAPI
DATA:
* Returnparameters of BAPIs
g_obj_type TYPE bapiache09-obj_type, "Object type
* Object key will be populated with Document number
* NNNNNNNNNNYYYYBBBB N = Doc Number, Y = Fiscal Year, B = Company code
g_obj_key TYPE bapiache09-obj_key, "Object key
g_obj_sys TYPE bapiache09-obj_sys, "log. System
* Document header
gs_documentheader TYPE bapiache09,
* G/L account item
git_accountgl TYPE TABLE OF bapiacgl09,
gs_accountgl TYPE bapiacgl09,
* AR item
git_accountrec TYPE TABLE OF bapiacar09,
gs_accountrec TYPE bapiacar09,
* AP items
git_accountpay TYPE TABLE OF bapiacap09,
gs_accountpay TYPE bapiacap09,
* Tax items
git_accounttax TYPE TABLE OF bapiactx09,
gs_accounttax TYPE bapiactx09,
* Amount information
git_currencyamount TYPE TABLE OF bapiaccr09,
gs_currencyamount TYPE bapiaccr09,
* BAPI mesages
git_return TYPE TABLE OF bapiret2,
gs_return TYPE bapiret2.
DATA:
g_itemno TYPE posnr_acc. "Line item number
*&---------------------------------------------------------------------*
*& SELECTION-SCREEN
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE TEXT-b01.
PARAMETERS: pa_fnam LIKE rlgrap-filename OBLIGATORY.
" PARAMETERS: pa_shtyp TYPE zde_shtyp DEFAULT '2' NO-DISPLAY.
SELECTION-SCREEN END OF BLOCK b01.
SELECTION-SCREEN BEGIN OF BLOCK b02 WITH FRAME TITLE TEXT-b02.
PARAMETERS: pa_test AS CHECKBOX DEFAULT 'X'.
SELECTION-SCREEN END OF BLOCK b02.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_fnam.
PERFORM f4_filename.
*-----------------------------------------------------------------------
START-OF-SELECTION.
*-----------------------------------------------------------------------
CLEAR git_return[].
PERFORM init_protocol.
PERFORM upload_data.
PERFORM precheck_data CHANGING g_rc.
IF g_rc = 0.
PERFORM process_data.
ENDIF.
PERFORM add_msg_to_prot.
PERFORM log_display.
*&---------------------------------------------------------------------*
*& Form F4_FILENAME
*&---------------------------------------------------------------------*
*& F4 Help Excel-file
*&---------------------------------------------------------------------*
FORM f4_filename .
CALL FUNCTION 'FAA_FILE_F4'
EXPORTING
i_default_extension = 'XLS'
IMPORTING
e_filename = pa_fnam
EXCEPTIONS
interface_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.
PERFORM msg_add USING probclass_high.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form UPLOAD_DATA
*&---------------------------------------------------------------------*
*& Upload Excel spreadsheet & structurize data
*&---------------------------------------------------------------------*
FORM upload_data .
DATA: lt_bin_data TYPE w3mimetabtype.
DATA: lt_sheet_data TYPE gtt_it_xl.
DATA: l_fnam TYPE string.
l_fnam = pa_fnam.
CALL METHOD cl_gui_frontend_services=>gui_upload
EXPORTING
filename = l_fnam
filetype = 'BIN'
CHANGING
data_tab = lt_bin_data.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
* solix -> xstring
DATA(lv_xsts_xlsx) = cl_bcs_convert=>solix_to_xstring( it_solix = lt_bin_data ).
* Excel-Dokument
DATA(o_xl) = xco_cp_xlsx=>document->for_file_content( lv_xsts_xlsx )->read_access( ).
* 1. Sheet
DATA(o_sheet) = o_xl->get_workbook( )->worksheet->at_position( 1 ).
IF abap_true = o_sheet->exists( ).
DATA(o_sel_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to( )->get_pattern( ).
CLEAR lt_sheet_data[].
DATA(o_result) = o_sheet->select( o_sel_pattern
)->row_stream(
)->operation->write_to( REF #( lt_sheet_data )
)->set_value_transformation( xco_cp_xlsx_read_access=>value_transformation->string_value
)->if_xco_xlsx_ra_operation~execute( ).
IF abap_true = o_result->succeeded.
LOOP AT lt_sheet_data ASSIGNING FIELD-SYMBOL(<ls_sheet>).
CHECK sy-tabix > 1.
gs_doc-doc = <ls_sheet>-col_a.
gs_doc-bschl = <ls_sheet>-col_b.
gs_doc-umskz = <ls_sheet>-col_c.
gs_doc-bukrs = <ls_sheet>-col_d.
gs_doc-newko = <ls_sheet>-col_e.
gs_doc-mwskz = <ls_sheet>-col_f.
gs_doc-wrbtr = <ls_sheet>-col_g.
gs_doc-waers = <ls_sheet>-col_h.
gs_doc-netto = <ls_sheet>-col_i.
gs_doc-dmbtr = <ls_sheet>-col_j.
gs_doc-kostl = <ls_sheet>-col_k.
gs_doc-sgtxt = <ls_sheet>-col_l.
gs_doc-blart = <ls_sheet>-col_m.
gs_doc-bldat = <ls_sheet>-col_n.
gs_doc-budat = <ls_sheet>-col_o.
gs_doc-xblnr = <ls_sheet>-col_p.
gs_doc-bumon = <ls_sheet>-col_q.
gs_doc-bktxt = <ls_sheet>-col_r.
gs_doc-zuonr = <ls_sheet>-col_s.
gs_doc-netdt = <ls_sheet>-col_t.
gs_doc-bewar = <ls_sheet>-col_u.
gs_doc-aufnr = <ls_sheet>-col_v.
gs_doc-prctr = <ls_sheet>-col_w.
gs_doc-kidno = <ls_sheet>-col_x.
gs_doc-zterm = <ls_sheet>-col_y.
APPEND gs_doc TO git_docs.
ENDLOOP.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form INIT_PROTOCOL
*&---------------------------------------------------------------------*
*& Initialize application protocol
*&---------------------------------------------------------------------*
FORM init_protocol .
* create a log
g_s_log-extnumber = 'Document-Upload'(ext).
CALL FUNCTION 'BAL_LOG_CREATE'
EXPORTING
i_s_log = g_s_log
IMPORTING
e_log_handle = g_log_handle
EXCEPTIONS
OTHERS = 1.
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.
*--------------------------------------------------------------------
* FORM ADD_MSG_TO_PROT
*--------------------------------------------------------------------
FORM add_msg_to_prot.
DATA:
l_s_msg TYPE bal_s_msg,
l_context TYPE bal_s_ex01.
DATA:
l_s_return LIKE LINE OF git_return.
LOOP AT git_return INTO l_s_return.
* define data of message for Application Log
l_s_msg-msgty = l_s_return-type.
l_s_msg-msgid = l_s_return-id.
l_s_msg-msgno = l_s_return-number.
l_s_msg-msgv1 = l_s_return-message_v1.
l_s_msg-msgv2 = l_s_return-message_v2.
l_s_msg-msgv3 = l_s_return-message_v3.
l_s_msg-msgv4 = l_s_return-message_v4.
* add this message to log file
CALL FUNCTION 'BAL_LOG_MSG_ADD'
EXPORTING
* I_LOG_HANDLE =
i_s_msg = l_s_msg
EXCEPTIONS
log_not_found = 0
OTHERS = 1.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDLOOP.
ENDFORM.
FORM log_display.
DATA:
l_s_display_profile TYPE bal_s_prof,
l_s_fcat TYPE bal_s_fcat.
* get standard display profile
CALL FUNCTION 'BAL_DSP_PROFILE_SINGLE_LOG_GET'
IMPORTING
e_s_display_profile = l_s_display_profile
EXCEPTIONS
OTHERS = 1.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
* for display variants add report id
l_s_display_profile-disvariant-report = sy-repid.
* when you use also other ALV lists in your report,
* please specify a handle to distinguish between the display
* variants of these different lists, e.g:
l_s_display_profile-disvariant-handle = 'LOG'.
* show log file with modified output profile
* - we specify the display profile since we created our own
* - we do not specify any filter (like I_S_LOG_FILTER, ...,
* I_T_MSG_HANDLE) since we want to display all messages available
CALL FUNCTION 'BAL_DSP_LOG_DISPLAY'
EXPORTING
* I_S_LOG_FILTER =
* I_T_LOG_CONTEXT_FILTER =
* I_S_MSG_FILTER =
* I_T_MSG_CONTEXT_FILTER =
* I_T_LOG_HANDLE =
* I_T_MSG_HANDLE =
i_s_display_profile = l_s_display_profile
EXCEPTIONS
OTHERS = 1.
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.
*--------------------------------------------------------------------
* FORM MSG_ADD_FREE_TEXT
*--------------------------------------------------------------------
FORM msg_add_free_text USING VALUE(i_text) TYPE c.
* add this message to log file
CALL FUNCTION 'BAL_LOG_MSG_ADD_FREE_TEXT'
EXPORTING
* I_LOG_HANDLE =
i_msgty = 'S'
i_text = i_text
EXCEPTIONS
log_not_found = 0
OTHERS = 1.
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.
*&---------------------------------------------------------------------*
*& Form INIT
*&---------------------------------------------------------------------*
*& Initialization
*&---------------------------------------------------------------------*
FORM init .
CLEAR: git_accountgl[],
git_accountpay[],
git_accountrec[],
git_accounttax[],
git_currencyamount[],
git_taxitems[].
CLEAR: g_itemno, gs_documentheader.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form POST_DOCUMENT
*&---------------------------------------------------------------------*
*& Post document
*&---------------------------------------------------------------------*
FORM post_document .
DATA: l_error TYPE abap_bool VALUE abap_false.
DATA: lit_return TYPE TABLE OF bapiret2.
*-----------------------------------------------------------------------
* BAPI-Call
*-----------------------------------------------------------------------
CALL FUNCTION 'BAPI_ACC_DOCUMENT_POST' "#EC CI_USAGE_OK[2628704]
EXPORTING
documentheader = gs_documentheader "#EC CI_USAGE_OK[2438131]
IMPORTING
obj_type = g_obj_type
obj_key = g_obj_key
obj_sys = g_obj_sys
TABLES
accountgl = git_accountgl
accountreceivable = git_accountrec
accountpayable = git_accountpay
accounttax = git_accounttax
currencyamount = git_currencyamount
return = lit_return.
LOOP AT lit_return TRANSPORTING NO FIELDS
WHERE type = 'A' OR type = 'E'.
l_error = abap_true.
ENDLOOP.
IF l_error = abap_false.
CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'.
ELSE.
CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK'.
ENDIF.
APPEND LINES OF lit_return TO git_return.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_DOCUMENT
*&---------------------------------------------------------------------*
*& check document (Test mode)
*&---------------------------------------------------------------------*
FORM check_document .
DATA: lit_return TYPE TABLE OF bapiret2.
CALL FUNCTION 'BAPI_ACC_DOCUMENT_CHECK' "#EC CI_USAGE_OK[2438131]
EXPORTING
documentheader = gs_documentheader "#EC CI_USAGE_OK[2628704]
TABLES
accountgl = git_accountgl
accountreceivable = git_accountrec
accountpayable = git_accountpay
accounttax = git_accounttax
currencyamount = git_currencyamount
return = lit_return.
APPEND LINES OF lit_return TO git_return.
ENDFORM.
Werbung
*&---------------------------------------------------------------------*
*& Form PROCESS_DATA
*&---------------------------------------------------------------------*
*& map excel data to BAPI structure & post / check documents
*&---------------------------------------------------------------------*
FORM process_data .
DATA: l_netto TYPE wrbtr,
l_nethw TYPE dmbtr,
l_brutto TYPE wrbtr,
l_bruthw TYPE dmbtr,
l_steuer TYPE fwste,
l_steuhw TYPE hwste,
l_basis TYPE fwbas,
l_bashw TYPE hwbas.
DATA: l_mwskz TYPE skb1-mwskz,
ls_bsl TYPE tbsl.
*-----------------------------------------------------------------------
* Items
*-----------------------------------------------------------------------
LOOP AT git_docs INTO gs_doc.
AT NEW doc.
PERFORM init.
*-----------------------------------------------------------------------
* document header
*-----------------------------------------------------------------------
READ TABLE git_docs INDEX sy-tabix INTO DATA(ls_doc).
CLEAR gs_documentheader.
gs_documentheader-bus_act = 'RFBU'.
gs_documentheader-username = sy-uname.
gs_documentheader-comp_code = ls_doc-bukrs.
PERFORM _convert_date_xlsx USING ls_doc-bldat.
gs_documentheader-doc_date = ls_doc-bldat.
PERFORM _convert_date_xlsx USING ls_doc-budat.
gs_documentheader-pstng_date = ls_doc-budat.
IF gs_doc-bumon IS NOT INITIAL AND ls_doc-bumon NE space.
gs_documentheader-fis_period = ls_doc-bumon.
ENDIF.
gs_documentheader-ref_doc_no = ls_doc-xblnr.
gs_documentheader-header_txt = ls_doc-bktxt.
gs_documentheader-doc_type = ls_doc-blart.
SELECT SINGLE * FROM t001 INTO gs_t001 WHERE bukrs = ls_doc-bukrs.
ENDAT.
g_tabix = sy-tabix.
ADD 1 TO g_itemno.
"begin of insertion SRO300326
gs_doc-wrbtr = abs( gs_doc-wrbtr ).
gs_doc-dmbtr = abs( gs_doc-dmbtr ).
"end of insertion SRO300326
SELECT SINGLE * FROM tbsl INTO ls_bsl WHERE bschl = gs_doc-bschl.
IF sy-subrc NE 0.
MESSAGE e034(f5) WITH gs_doc-bschl INTO g_dummy.
PERFORM msg_add USING probclass_high.
CONTINUE.
ENDIF.
CASE ls_bsl-koart.
WHEN 'S'.
CLEAR: gs_accountgl, gs_currencyamount.
CLEAR: l_brutto, l_bruthw.
gs_accountgl-itemno_acc = g_itemno.
PERFORM _convert USING gs_doc-newko gs_accountgl-gl_account 'HKONT'.
gs_accountgl-item_text = gs_doc-sgtxt.
IF gs_doc-kostl IS NOT INITIAL.
PERFORM _convert USING gs_doc-kostl gs_accountgl-costcenter 'KOSTL'.
ENDIF.
IF gs_doc-prctr IS NOT INITIAL.
PERFORM _convert USING gs_doc-prctr gs_accountgl-profit_ctr 'PRCTR'.
ENDIF.
IF gs_doc-aufnr IS NOT INITIAL.
PERFORM _convert USING gs_doc-aufnr gs_accountgl-orderid 'AUFNR'.
ENDIF.
gs_accountgl-alloc_nmbr = gs_doc-zuonr.
gs_accountgl-tax_code = gs_doc-mwskz.
gs_accountgl-cshdis_ind = abap_true.
IF gs_doc-wrbtr IS NOT INITIAL.
PERFORM _convert USING gs_doc-wrbtr l_brutto 'WRBTR'.
ENDIF.
IF gs_doc-dmbtr IS NOT INITIAL AND gs_doc-waers NE gs_t001-waers.
PERFORM _convert USING gs_doc-dmbtr l_bruthw 'DMBTR'.
ENDIF.
"Net amounts provided? --> Calculate gross-amounts
IF gs_doc-netto = abap_true.
PERFORM calculate_grossamounts USING gs_documentheader-comp_code gs_doc-waers gs_doc-mwskz
CHANGING l_brutto l_bruthw.
ENDIF.
IF ls_bsl-shkzg = 'H'.
MULTIPLY l_brutto BY -1.
MULTIPLY l_bruthw BY -1.
ENDIF.
* GL account taxable?
CLEAR l_mwskz.
SELECT SINGLE mwskz FROM skb1 INTO l_mwskz
WHERE saknr = gs_accountgl-gl_account
AND bukrs = gs_documentheader-comp_code. "#EC CI_DB_OPERATION_OK[2431747]
IF sy-subrc = 0 AND l_mwskz IS INITIAL.
"no tax allowed? --> clear tax code (same behaviour as FIORI app)
CLEAR: gs_doc-mwskz, gs_accountgl-tax_code.
ENDIF.
CLEAR: l_steuer, l_netto, l_basis, l_steuhw, l_nethw, l_bashw.
* with tax?
IF gs_doc-mwskz IS NOT INITIAL.
IF l_mwskz = '>' OR l_mwskz = '<'. "Direct Tax
CALL FUNCTION 'RE_CALCULATE_BASE_AMOUNT'
EXPORTING
i_taxam = l_brutto
i_mwskz = gs_doc-mwskz
i_waers = gs_doc-waers
i_bukrs = gs_documentheader-comp_code
IMPORTING
e_fwbas = l_basis.
l_steuer = l_netto = l_brutto.
IF gs_doc-waers NE gs_t001-waers.
CALL FUNCTION 'RE_CALCULATE_BASE_AMOUNT'
EXPORTING
i_taxam = l_bruthw
i_mwskz = gs_doc-mwskz
i_waers = gs_t001-waers
i_bukrs = gs_documentheader-comp_code
IMPORTING
e_fwbas = l_bashw.
l_steuhw = l_nethw = l_bruthw.
ENDIF.
ELSE.
CALL FUNCTION 'CALCULATE_TAX_FROM_GROSSAMOUNT'
EXPORTING
i_bukrs = gs_documentheader-comp_code
i_mwskz = gs_doc-mwskz
i_waers = gs_doc-waers
i_wrbtr = l_brutto
IMPORTING
e_fwste = l_steuer
EXCEPTIONS
OTHERS = 16.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO g_dummy.
PERFORM msg_add USING probclass_high.
ENDIF.
l_netto = l_brutto - l_steuer.
l_basis = l_netto.
IF gs_doc-waers NE gs_t001-waers AND gs_doc-dmbtr IS NOT INITIAL.
CALL FUNCTION 'CALCULATE_TAX_FROM_GROSSAMOUNT'
EXPORTING
i_bukrs = gs_documentheader-comp_code
i_mwskz = gs_doc-mwskz
i_waers = gs_t001-waers
i_wrbtr = l_bruthw
IMPORTING
e_fwste = l_steuhw
EXCEPTIONS
OTHERS = 16.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO g_dummy.
PERFORM msg_add USING probclass_high.
ENDIF.
l_nethw = l_bruthw - l_steuhw.
l_bashw = l_nethw.
ENDIF.
ENDIF.
*-----------------------------------------------------------------------
* collect corresponding tax line for each taxable GL account
*-----------------------------------------------------------------------
CLEAR gs_taxitem.
gs_taxitem-mwskz = gs_doc-mwskz.
gs_taxitem-steuer = l_steuer.
gs_taxitem-steuhw = l_steuhw.
gs_taxitem-basis = l_basis.
gs_taxitem-bashw = l_bashw.
IF l_mwskz = '>' OR l_mwskz = '<'. "Direct Tax
gs_taxitem-direct_tax = abap_true.
ELSE.
gs_taxitem-direct_tax = abap_false.
ENDIF.
COLLECT gs_taxitem INTO git_taxitems.
ELSE.
l_netto = l_brutto.
l_nethw = l_bruthw.
ENDIF.
IF NOT ( l_mwskz = '>' OR l_mwskz = '<' ). "Direct Tax
IF gs_doc-waers NE gs_t001-waers AND gs_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '00'. "transaction (foreign) currency
ENDIF.
gs_currencyamount-itemno_acc = gs_accountgl-itemno_acc.
gs_currencyamount-currency = gs_doc-waers.
gs_currencyamount-amt_doccur = l_netto. "#EC CI_FLDEXT_OK[2610650]
APPEND: gs_accountgl TO git_accountgl,
gs_currencyamount TO git_currencyamount.
IF gs_doc-waers NE gs_t001-waers AND gs_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '10'. "local currency
gs_currencyamount-itemno_acc = gs_accountgl-itemno_acc.
gs_currencyamount-currency = gs_t001-waers.
gs_currencyamount-amt_doccur = l_nethw. "#EC CI_FLDEXT_OK[2610650]
APPEND: gs_currencyamount TO git_currencyamount.
ENDIF.
ENDIF.
WHEN 'D'.
CLEAR: gs_accountrec, gs_currencyamount.
gs_accountrec-itemno_acc = g_itemno. "Positionsidentifier
PERFORM _convert USING gs_doc-newko gs_accountrec-customer 'KUNNR'.
gs_accountrec-item_text = gs_doc-sgtxt. "Belegtext
gs_accountrec-alloc_nmbr = gs_doc-zuonr. "Zuordnung
gs_accountrec-sp_gl_ind = gs_doc-umskz. "SHB-KZ
gs_accountrec-paymt_ref = gs_doc-kidno. "Zahlungsreferenz
IF gs_doc-netdt IS NOT INITIAL.
PERFORM _convert_date_xlsx USING gs_doc-netdt.
gs_accountrec-bline_date = gs_doc-netdt.
ENDIF.
IF gs_doc-zterm IS NOT INITIAL.
gs_accountrec-pmnttrms = gs_doc-zterm.
ELSE.
SELECT SINGLE zterm FROM knb1 INTO gs_accountrec-pmnttrms
WHERE kunnr = gs_accountrec-customer
AND bukrs = gs_doc-bukrs.
ENDIF.
IF gs_doc-waers NE gs_t001-waers AND gs_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '00'. "transaction (foreign) currency
ENDIF.
gs_currencyamount-itemno_acc = gs_accountrec-itemno_acc.
gs_currencyamount-currency = gs_doc-waers.
IF gs_doc-wrbtr IS NOT INITIAL.
PERFORM _convert USING gs_doc-wrbtr l_brutto 'WRBTR'.
IF ls_bsl-shkzg = 'H'.
MULTIPLY l_brutto BY -1.
ENDIF.
ENDIF.
gs_currencyamount-amt_doccur = l_brutto. "#EC CI_FLDEXT_OK[2610650]
APPEND: gs_accountrec TO git_accountrec,
gs_currencyamount TO git_currencyamount.
IF gs_doc-waers NE gs_t001-waers AND gs_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '10'. "local currency
gs_currencyamount-itemno_acc = gs_accountrec-itemno_acc.
gs_currencyamount-currency = gs_t001-waers.
IF gs_doc-dmbtr IS NOT INITIAL.
PERFORM _convert USING gs_doc-dmbtr l_bruthw 'DMBTR'.
IF ls_bsl-shkzg = 'H'.
MULTIPLY l_bruthw BY -1.
ENDIF.
ENDIF.
gs_currencyamount-amt_doccur = l_bruthw. "#EC CI_FLDEXT_OK[2610650]
APPEND gs_currencyamount TO git_currencyamount.
ENDIF.
WHEN 'K'.
CLEAR: gs_accountpay, gs_currencyamount.
gs_accountpay-itemno_acc = g_itemno. "Positionsidentifier
PERFORM _convert USING gs_doc-newko gs_accountpay-vendor_no 'LIFNR'.
gs_accountpay-item_text = gs_doc-sgtxt. "Belegtext
gs_accountpay-alloc_nmbr = gs_doc-zuonr. "Zuordnung
gs_accountpay-sp_gl_ind = gs_doc-umskz. "SHB-KZ
gs_accountpay-paymt_ref = gs_doc-kidno. "Zahlungsreferenz
IF gs_doc-netdt IS NOT INITIAL.
PERFORM _convert_date_xlsx USING gs_doc-netdt.
gs_accountpay-bline_date = gs_doc-netdt.
ENDIF.
IF gs_doc-zterm IS NOT INITIAL.
gs_accountpay-pmnttrms = gs_doc-zterm.
ELSE.
SELECT SINGLE zterm FROM lfb1 INTO gs_accountpay-pmnttrms
WHERE lifnr = gs_accountpay-vendor_no
AND bukrs = gs_doc-bukrs.
ENDIF.
IF gs_doc-waers NE gs_t001-waers AND gs_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '00'. "transaction (foreign) currency
ENDIF.
gs_currencyamount-itemno_acc = gs_accountpay-itemno_acc.
gs_currencyamount-currency = gs_doc-waers.
IF gs_doc-wrbtr IS NOT INITIAL.
PERFORM _convert USING gs_doc-wrbtr l_brutto 'WRBTR'.
IF ls_bsl-shkzg = 'H'.
MULTIPLY l_brutto BY -1.
ENDIF.
ENDIF.
gs_currencyamount-amt_doccur = l_brutto. "#EC CI_FLDEXT_OK[2610650]
APPEND: gs_accountpay TO git_accountpay,
gs_currencyamount TO git_currencyamount.
IF gs_doc-waers NE gs_t001-waers AND gs_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '10'. "local currency
gs_currencyamount-itemno_acc = gs_accountpay-itemno_acc.
gs_currencyamount-currency = gs_t001-waers.
IF gs_doc-dmbtr IS NOT INITIAL.
PERFORM _convert USING gs_doc-dmbtr l_bruthw 'DMBTR'.
IF ls_bsl-shkzg = 'H'.
MULTIPLY l_bruthw BY -1.
ENDIF.
ENDIF.
gs_currencyamount-amt_doccur = l_bruthw. "#EC CI_FLDEXT_OK[2610650]
APPEND gs_currencyamount TO git_currencyamount.
ENDIF.
ENDCASE.
AT END OF doc.
READ TABLE git_docs INDEX g_tabix INTO ls_doc.
*-----------------------------------------------------------------------
* Prepare collected tax lines & attach to current document
*-----------------------------------------------------------------------
LOOP AT git_taxitems INTO gs_taxitem.
ADD 1 TO g_itemno.
CLEAR: gs_accounttax, gs_currencyamount.
gs_accounttax-itemno_acc = g_itemno.
gs_accounttax-tax_code = gs_taxitem-mwskz.
gs_accounttax-direct_tax = gs_taxitem-direct_tax.
IF ls_doc-waers NE gs_t001-waers AND ls_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '00'.
ENDIF.
gs_currencyamount-itemno_acc = gs_accounttax-itemno_acc.
gs_currencyamount-currency = ls_doc-waers.
gs_currencyamount-amt_doccur = gs_taxitem-steuer. "#EC CI_FLDEXT_OK[2610650]
gs_currencyamount-amt_base = gs_taxitem-basis. "#EC CI_FLDEXT_OK[2610650]
APPEND: gs_accounttax TO git_accounttax,
gs_currencyamount TO git_currencyamount.
IF ls_doc-waers NE gs_t001-waers AND ls_doc-dmbtr IS NOT INITIAL.
gs_currencyamount-curr_type = '10'.
gs_currencyamount-itemno_acc = gs_accounttax-itemno_acc.
gs_currencyamount-currency = gs_t001-waers.
gs_currencyamount-amt_doccur = gs_taxitem-steuhw. "#EC CI_FLDEXT_OK[2610650]
gs_currencyamount-amt_base = gs_taxitem-bashw. "#EC CI_FLDEXT_OK[2610650]
APPEND: gs_currencyamount TO git_currencyamount.
ENDIF.
ENDLOOP.
PERFORM round_amounts USING g_itemno CHANGING git_currencyamount.
*-----------------------------------------------------------------------
* Post / Check document
*-----------------------------------------------------------------------
IF pa_test IS INITIAL.
PERFORM post_document.
ELSE.
PERFORM check_document.
ENDIF.
ENDAT.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form _CONVERT
*&---------------------------------------------------------------------*
*& Convert excel data to SAP format
*&---------------------------------------------------------------------*
*& --> in/out input / output
*& --> Dtel data element that determines the conversion
*&---------------------------------------------------------------------*
FORM _convert USING in out VALUE(dtel).
DATA: l_btr TYPE wrbtr.
IF dtel = 'WRBTR' OR dtel = 'DMBTR' AND in CO '0123456789 .'.
l_btr = in. "#EC CI_FLDEXT_OK[2610650]
out = l_btr. "#EC CI_FLDEXT_OK[2610650]
EXIT.
ENDIF.
CALL FUNCTION 'RS_CONV_EX_2_IN_DTEL'
EXPORTING
input_external = in "#EC CI_FLDEXT_OK[2610650]
dtel = dtel
IMPORTING
output_internal = out. "#EC CI_FLDEXT_OK[2610650]
IF sy-subrc <> 0.
MESSAGE a600(fr) WITH 'ERROR'(f03) sy-subrc 'at conversion'(f04) dtel.
ENDIF.
ENDFORM.
*--------------------------------------------------------------------
* FORM MSG_ADD
*--------------------------------------------------------------------
FORM msg_add USING VALUE(i_probclass) TYPE bal_s_msg-probclass.
DATA:
l_s_msg TYPE bal_s_msg.
* define data of message for Application Log
l_s_msg-msgty = sy-msgty.
l_s_msg-msgid = sy-msgid.
l_s_msg-msgno = sy-msgno.
l_s_msg-msgv1 = sy-msgv1.
l_s_msg-msgv2 = sy-msgv2.
l_s_msg-msgv3 = sy-msgv3.
l_s_msg-msgv4 = sy-msgv4.
l_s_msg-probclass = i_probclass.
* add this message to log file
CALL FUNCTION 'BAL_LOG_MSG_ADD'
EXPORTING
* I_LOG_HANDLE =
i_s_msg = l_s_msg
EXCEPTIONS
log_not_found = 0
OTHERS = 1.
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.
*&---------------------------------------------------------------------*
*& Form PRECHECK_DATA
*&---------------------------------------------------------------------*
* Precheck Data, whether local currency is consistently provided
*----------------------------------------------------------------------*
FORM precheck_data CHANGING p_rc.
DATA: l_mix(2) TYPE c.
LOOP AT git_docs INTO gs_doc.
AT NEW doc.
READ TABLE git_docs INDEX sy-tabix INTO DATA(ls_doc).
SELECT SINGLE * FROM t001 INTO gs_t001 WHERE bukrs = ls_doc-bukrs.
CLEAR l_mix.
ENDAT.
"Doc is given in foreign currency
IF gs_doc-waers NE gs_t001-waers.
IF gs_doc-dmbtr IS NOT INITIAL OR gs_doc-dmbtr CO '0,. '.
l_mix(1) = 'X'.
ELSE.
l_mix+1(1) = 'X'.
ENDIF.
ENDIF.
IF l_mix = 'XX'.
"sometimes amount in LC is provided and sometimes not within same document
"--> ERROR
p_rc = 4.
MESSAGE e800(f5) INTO g_dummy.
PERFORM msg_add USING probclass_high.
EXIT.
ELSE.
p_rc = 0.
ENDIF.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form _convert_date_xlsx
*&---------------------------------------------------------------------*
*& Datumsformat Excel in anständiges Datum umwandeln.
*&---------------------------------------------------------------------*
FORM _convert_date_xlsx CHANGING p_date TYPE char10.
DATA: lv_days TYPE i,
lv_date TYPE datum.
CHECK p_date IS NOT INITIAL.
CHECK p_date CO ' 0123456789.'.
lv_days = trunc( p_date ) - 2.
lv_date = '19000101'.
ADD lv_days TO lv_date.
p_date = lv_date.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form calculate_grossamounts
*&---------------------------------------------------------------------*
*& Calculate gross amounts from net amounts
*&---------------------------------------------------------------------*
*& --> p_bukrs
*& --> p_WAERS
*& --> p_MWSKZ
*& <-- p_BRUTTO
*& <-- p_BRUTHW
*&---------------------------------------------------------------------*
FORM calculate_grossamounts USING p_bukrs TYPE bukrs
p_waers TYPE waers
p_mwskz TYPE mwskz
CHANGING p_netto TYPE wrbtr
p_nethw TYPE dmbtr.
DATA: lt_mwdat TYPE TABLE OF rtax1u15,
l_fwste TYPE bset-fwste.
* transaction currency
CALL FUNCTION 'CALCULATE_TAX_FROM_NET_AMOUNT'
EXPORTING
i_bukrs = p_bukrs
i_mwskz = p_mwskz
i_waers = p_waers
i_wrbtr = p_netto
IMPORTING
e_fwste = l_fwste
TABLES
t_mwdat = lt_mwdat
EXCEPTIONS
OTHERS = 15.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
ADD l_fwste TO p_netto.
ENDIF.
CHECK p_nethw NE 0.
* company code currency
CALL FUNCTION 'CALCULATE_TAX_FROM_NET_AMOUNT'
EXPORTING
i_bukrs = p_bukrs
i_mwskz = p_mwskz
i_waers = p_waers
i_wrbtr = p_nethw
IMPORTING
e_fwste = l_fwste
TABLES
t_mwdat = lt_mwdat
EXCEPTIONS
OTHERS = 15.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
ADD l_fwste TO p_nethw.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form round_amounts
*&---------------------------------------------------------------------*
*& elimnate rounding differences
*&---------------------------------------------------------------------*
*& --> p_ITEMNO
*& <-- p_CURRENCY_AMOUNT
*&---------------------------------------------------------------------*
FORM round_amounts USING p_itemno TYPE posnr_acc
CHANGING p_currencyamount TYPE gty_currency_amount.
DATA: diff_00 TYPE bapidoccur,
diff_10 TYPE bapidoccur,
diff TYPE bapidoccur.
DATA: tabix_00 LIKE sy-tabix,
tabix_10 LIKE sy-tabix,
tabix LIKE sy-tabix.
LOOP AT p_currencyamount INTO DATA(ls_curr) WHERE curr_type = '00'.
ADD ls_curr-amt_doccur TO diff_00.
ENDLOOP.
LOOP AT p_currencyamount INTO ls_curr WHERE curr_type = '10'.
ADD ls_curr-amt_doccur TO diff_10.
ENDLOOP.
LOOP AT p_currencyamount INTO ls_curr WHERE curr_type = ' '.
ADD ls_curr-amt_doccur TO diff.
ENDLOOP.
CHECK diff <> 0 OR diff_00 <> 0 OR diff_10 <> 0.
READ TABLE p_currencyamount TRANSPORTING NO FIELDS WITH KEY itemno_acc = p_itemno
curr_type = '00'.
IF sy-subrc = 0.
tabix_00 = sy-tabix.
ENDIF.
READ TABLE p_currencyamount TRANSPORTING NO FIELDS WITH KEY itemno_acc = p_itemno
curr_type = '10'.
IF sy-subrc = 0.
tabix_10 = sy-tabix.
ENDIF.
READ TABLE p_currencyamount TRANSPORTING NO FIELDS WITH KEY itemno_acc = p_itemno
curr_type = ' '.
IF sy-subrc = 0.
tabix = sy-tabix.
ENDIF.
IF diff_00 <> 0.
p_currencyamount[ tabix_00 ]-amt_doccur = p_currencyamount[ tabix_00 ]-amt_doccur - diff_00.
ENDIF.
IF diff_10 <> 0.
p_currencyamount[ tabix_10 ]-amt_doccur = p_currencyamount[ tabix_10 ]-amt_doccur - diff_10.
ENDIF.
IF diff <> 0.
p_currencyamount[ tabix ]-amt_doccur = p_currencyamount[ tabix ]-amt_doccur - diff.
ENDIF.
ENDFORM.
Hinweise zur Installation:
Textelemente:
001 Verarbeitung der Datei
B01 Excel-Datei auswählen
B02 Testlauf ohne Buchen
Selektionstexte:
PA_FNAM Name der Datei
PA_TEST Testlauf - nur prüfen
Link zur Excel-Vorlage, die mit dem oben stehenden Programm verarbeitet werden kann.
https://drive.google.com/open?id=13XQEsF9yEa1wv6DE6SPn5zPNm8D9p9n-
Hinweis: die Vorlage ist an das Template für die S/4-FIORI-App "Hauptbuchbelege hochladen" angepasst. Es versteht sich wohl von selbst, dass Anpassungen am Programm gemacht werden müssen, wenn das Template abgeändert wird.
Werbung
Excel-Template
Diesen Post teilen
Um über die neuesten Artikel informiert zu werden, abonnieren:
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/image%2F1473637%2F20201125%2Fob_ccddc9_screenshot-at-nov-25-13-34-21.png)
/image%2F1473637%2F20200715%2Fob_3bedf3_plankat.png)
Kommentiere diesen Post
