cancel
Showing results for 
Search instead for 
Did you mean: 

Quick recording of general ledger and personal accounts with a reports.

0 Kudos
547

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:

  • Posting key for the next line Item (RF05A-NEWBS)
  • Account or match code for the next line item (RF05A-NEWKO)
  • Amount in document currency from debit (BSEG-WRBTR)
  • Amount in document currency from credit (BSEG-WRBTR)
  • Tax on sales/purchases code (BSEG-MWSKZ)
  • Transaction type (COBL-RMVCT)
  • Cost center (COBL-KOSTL)
  • Item text (BSEG-SGTXT)
  • Calculate tax automatically (BKPF-XMWST)

The next 9 fields will be reading from selection screen for every row of excel file:

  • Company Code (BKPF- BUKRS)
  • Document Type (BKPF- BLART)
  • Document Date in Document (BKPF- BLDAT)
  • Posting Date in the Document (BKPF- BUDAT)
  • Fiscal Period (BKPF- MONAT)
  • Accounting Document Number (BKPF- BELNR)
  • Reference Document Number (BKPF- XBLNR)
  • Document Header Text (BKPF- BKTXT)
  • Currency Key (BKPF-WAERS)

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?

Accepted Solutions (0)

Answers (2)

Answers (2)

TammyPowlas
Active Contributor

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?

0 Kudos

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