on 2018 Jul 11 4:43 PM
Dear SAP-Experts,
while creating monthly balance we have a lot of accounting vouchers, which have to be posted manually at this time. The mentioned accounting vouchers include general ledger accounts and personal accounts (vendors/customers). Currently a quick recording with FB01->Fast Data Entry per copy & past from excel file is not possible, because it allows only the G/L accounts.
There is only the option to record the personal accounts individually in “Other line items” block which is grossly inefficient ( time-consuming).
My aim is to write a little report to record the G/L and personal accounts from an excel file. The excel file contains the following 9 column:
The next 9 fields will be reading from selection screen for every row of excel file:
The bellow report is a modified version of an example from internet (see here) which use the BAPI "BAPI_ACC_DOCUMENT_POST".
SPAN {
font-family: "Courier New";
font-size: 10pt;
color: #000000;
background: #FFFFFF;
}
.L0S31 {
font-style: italic;
color: #808080;
}
.L0S32 {
color: #3399FF;
}
.L0S33 {
color: #4DA619;
}
.L0S52 {
color: #0000FF;
}
.L0S55 {
color: #800080;
}
.L0S70 {
color: #808080;
}
REPORT zreport_test.
TABLES:
bkpf.
CONSTANTS:
lc_waers TYPE waers VALUE 'EUR',
lc_bus_act TYPE glvor VALUE 'RFBU',
lc_acctype_glaccount LIKE tbsl-koart VALUE 'S',
lc_acctype_creditor LIKE tbsl-koart VALUE 'K',
lc_acctype_debitor LIKE tbsl-koart VALUE 'D'.
TYPES:
BEGIN OF gty_gl_account,
bs TYPE newbs,
konto TYPE newko,
betrag_soll TYPE wrbtr,
betrag_haben TYPE wrbtr,
stkz TYPE mwskz,
bwa TYPE rmvct,
kst TYPE kostl,
text TYPE sgtxt,
xmwst TYPE xmwst,
END OF gty_gl_account.
DATA:
lv_rc TYPE i,
lt_file_table TYPE filetable,
lt_raw TYPE truxs_t_text_data,
ls_file_table LIKE LINE OF lt_file_table,
lt_gl_account TYPE STANDARD TABLE OF gty_gl_account,
ls_gl_account LIKE LINE OF lt_gl_account.
SELECTION-SCREEN BEGIN OF BLOCK param WITH FRAME TITLE text-001.
PARAMETERS:
p_bukrs LIKE bkpf-bukrs,
p_blart LIKE bkpf-blart,
p_waers LIKE bkpf-waers DEFAULT lc_waers,
p_bldat LIKE bkpf-bldat,
p_budat LIKE bkpf-budat,
p_monat LIKE bkpf-monat,
p_xblnr LIKE bkpf-xblnr,
p_bktxt LIKE bkpf-bktxt.
SELECTION-SCREEN END OF BLOCK param.
SELECTION-SCREEN BEGIN OF BLOCK param1 WITH FRAME TITLE text-002.
PARAMETERS p_fname TYPE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK param1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = 'Please choose the excel file'
file_filter = '*.XLS)|*.XLSX'
CHANGING
file_table = lt_file_table
rc = lv_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc EQ 0.
READ TABLE lt_file_table INTO ls_file_table INDEX 1.
WRITE ls_file_table-filename TO p_fname.
ELSE.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = abap_true
i_tab_raw_data = lt_raw
i_filename = p_fname
TABLES
i_tab_converted_data = lt_gl_account
EXCEPTIONS
conversion_failed = 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.
ELSE.
PERFORM accountrecording USING lt_gl_account.
ENDIF.
FORM accountrecording USING VALUE(ls_gl_account) LIKE lt_gl_account.
DATA:
l_obj_type TYPE bapiache09-obj_type,
l_obj_key TYPE bapiache09-obj_key,
l_obj_sys TYPE bapiache09-obj_sys,
ls_docheader TYPE bapiache09,
lt_gl_accountpositions TYPE TABLE OF bapiacgl09,
ls_gl_accountpositions TYPE bapiacgl09,
lt_debitorpositions TYPE TABLE OF bapiacar09,
ls_debitorpositions TYPE bapiacar09,
lt_creditorpositions TYPE TABLE OF bapiacap09,
ls_creditorpositions TYPE bapiacap09,
lt_amaountinfos TYPE TABLE OF bapiaccr09,
ls_amaountinfos TYPE bapiaccr09,
lt_bapi_messages TYPE TABLE OF bapiret2,
ls_posnr TYPE posnr_acc,
lt_tax_data TYPE TABLE OF rtax1u15,
ls_tax_data TYPE rtax1u15,
ls_tmp_account LIKE LINE OF ls_gl_account,
lv_kunnr TYPE newko,
wrbtr TYPE wrbtr,
docnum TYPE belnr_d,
lv_posting_key TYPE tbsl.
*-----------------------------------------------------------------------
* Initialize
*-----------------------------------------------------------------------
CLEAR: lt_gl_accountpositions[],
lt_debitorpositions[],
lt_amaountinfos[],
lt_bapi_messages[],
ls_docheader.
*-----------------------------------------------------------------------
* Document header fill once
*-----------------------------------------------------------------------
ls_docheader-doc_date = p_bldat.
ls_docheader-doc_type = p_blart.
ls_docheader-comp_code = p_bukrs.
ls_docheader-pstng_date = p_budat.
ls_docheader-fis_period = p_monat.
ls_docheader-ref_doc_no = p_xblnr.
ls_docheader-header_txt = p_bktxt.
ls_docheader-bus_act = lc_bus_act.
ls_docheader-username = sy-uname.
*-----------------------------------------------------------------------
* Calculate tax data
*-----------------------------------------------------------------------
LOOP AT ls_gl_account INTO ls_tmp_account.
IF ls_tmp_account-betrag_soll IS NOT INITIAL.
wrbtr = ls_tmp_account-betrag_soll.
ELSE.
wrbtr = ls_tmp_account-betrag_haben.
ENDIF.
CALL FUNCTION 'CALCULATE_TAX_FROM_GROSSAMOUNT'
EXPORTING
i_bukrs = p_bukrs
i_mwskz = ls_tmp_account-stkz
i_waers = p_waers
i_wrbtr = wrbtr
TABLES
t_mwdat = lt_tax_data
EXCEPTIONS
OTHERS = 16.
IF sy-subrc NE 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
READ TABLE lt_tax_data INTO ls_tax_data INDEX 1.
ENDIF.
*-----------------------------------------------------------------------
* Find out the posting key
*-----------------------------------------------------------------------
CALL FUNCTION 'FI_POSTING_KEY_DATA'
EXPORTING
i_bschl = ls_tmp_account-bs
IMPORTING
e_tbsl = lv_posting_key.
CASE lv_posting_key-koart.
*-----------------------------------------------------------------------
* 1. Create line items is debitor
*-----------------------------------------------------------------------
WHEN lc_acctype_debitor.
ADD 10 TO ls_posnr.
CLEAR: ls_debitorpositions, ls_amaountinfos.
ls_debitorpositions-itemno_acc = ls_posnr.
ls_debitorpositions-customer = lv_kunnr.
ls_debitorpositions-item_text = ls_tmp_account-text.
ls_amaountinfos-itemno_acc = ls_posnr.
ls_amaountinfos-currency = p_waers.
ls_amaountinfos-amt_doccur = wrbtr.
ls_amaountinfos-amt_base = wrbtr.
APPEND: ls_debitorpositions TO lt_debitorpositions,
ls_amaountinfos TO lt_amaountinfos.
*-----------------------------------------------------------------------
* 2. Create line items for gl account
*-----------------------------------------------------------------------
WHEN lc_acctype_glaccount.
ADD 10 TO ls_posnr.
CLEAR: ls_gl_accountpositions, ls_amaountinfos.
ls_gl_accountpositions-itemno_acc = ls_posnr.
ls_gl_accountpositions-gl_account = ls_tmp_account-konto.
ls_gl_accountpositions-costcenter = ls_tmp_account-kst.
ls_gl_accountpositions-item_text = ls_tmp_account-text.
ls_gl_accountpositions-tax_code = ls_tmp_account-stkz.
ls_amaountinfos-itemno_acc = ls_posnr.
ls_amaountinfos-currency = p_waers.
ls_amaountinfos-amt_doccur = ls_tax_data-kawrt * - 1.
ls_amaountinfos-amt_base = ls_tax_data-kawrt * -1.
APPEND: ls_gl_accountpositions TO lt_gl_accountpositions,
ls_amaountinfos TO lt_amaountinfos.
*-----------------------------------------------------------------------
* 2. Create line items for creditor
*-----------------------------------------------------------------------
WHEN lc_acctype_creditor.
ADD 10 TO ls_posnr.
CLEAR: ls_creditorpositions, ls_amaountinfos.
ls_creditorpositions-itemno_acc = ls_posnr.
ls_creditorpositions-vendor_no = '0231000187'.
ls_creditorpositions-item_text = ls_tmp_account-text.
ls_amaountinfos-itemno_acc = ls_posnr.
ls_amaountinfos-currency = p_waers.
ls_amaountinfos-amt_doccur = wrbtr.
ls_amaountinfos-amt_base = wrbtr.
APPEND: ls_creditorpositions TO lt_creditorpositions,
ls_amaountinfos TO lt_amaountinfos.
ENDCASE.
ENDLOOP.
*-----------------------------------------------------------------------
* BAPI call
*-----------------------------------------------------------------------
CALL FUNCTION 'BAPI_ACC_DOCUMENT_POST'
EXPORTING
documentheader = ls_docheader
IMPORTING
obj_type = l_obj_type
obj_key = l_obj_key
obj_sys = l_obj_sys
TABLES
accountgl = lt_gl_accountpositions
accountreceivable = lt_debitorpositions
accountpayable = lt_creditorpositions
currencyamount = lt_amaountinfos
return = lt_bapi_messages.
*-----------------------------------------------------------------------
* BAPI message output and commit
*-----------------------------------------------------------------------
DESCRIBE TABLE lt_bapi_messages LINES DATA(lv_linenr).
IF lv_linenr > 1.
ROLLBACK WORK.
CALL FUNCTION 'C14ALD_BAPIRET2_SHOW'
TABLES
i_bapiret2_tab = lt_bapi_messages.
ELSE.
COMMIT WORK.
ENDIF.
ENDFORM.
But I get the bellow error message:
I checked the SKA1 and SKB1 table and the accounts are exist in chart of accounts Z000.
How can I solve the problem?
Request clarification before answering.
Hello Emal,
Instead of writing an ABAP program, we use an LSMW with SAP standard program RFBIBL00 and it works in all of our situations; is that a possibility for you?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Tammy,
Thank you for your contribution. At this point in time I cann't answer your question properly. Because I have to read about Legacy System Migration Workbench and the Batch Input (program RFBIBL00).
But If someone know any solution to my report’s problem as stated above please do not hesitate to give an answer.
Kind regards,
Emal
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
111 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.