GL Upload program

[Former Member]

Hi Guru's

I am new to Abap. I have a gl upload program that 1 file at a time as it can only take 1 header line.

I want to change this program so that multiple GL files can be uploaded.

The first line of the excel file is a header then followed by it's line item, the same logic within the excel file will continue.

How to I write a code that can successfully check the file and group post correctly?

[arivazhagan_sivasamy]

Hi ntobeko Nala,

How are you uploading file?

Using BDC or BAPI.

If its BDC, separate code have to be used.

Arivazhagan S

[former_member191728]

can u please tell the file format of ur excel file

how are you posting the FI document using BDC or BAPI

[Former Member]

Hi Nala,

Your current program takes one file with one header and mulitple line items. You now need one file with mulitple headers and corresponding line items. Right. Since in your post you have mentioned - 'I want to change this program so that multiple GL files can be uploaded.'

You will need to understand how your current program works, if you can easily modify and make it to work for multiple line items well and good. Else you will have to start afresh.

For eg, you need to link corresponding headers and their line items. Next you move your headers into header table. Loop at header table and read corresponding line items, use this header-line item data set into your current logic. hence in this way you will loop through all your headers and use the current program to acheive your req.(this may not be as simple as it sounds )

Regards,

DN.

[Former Member]

Hi See below my current code.

I am trying to see if I need to create a new code or update the existing one.

*&---------------------------------------------------------------------*

*& Report  ZFI_AUTO_GL_UPLOAD

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT  ZFI_AUTO_GL_UPLOAD.

DATA: i_tab           type string OCCURS 0 WITH HEADER LINE.

DATA: w_path          LIKE ibipparms-path.

DATA: w_file          TYPE string.

DATA: w_first         TYPE c.

DATA: w_counter       type i.

DATA: w_message       like SM04DIC-POPUPMSG.

DATA: BEGIN OF it_gl_head occurs 1,

                it_doc_date            type string,

                it_posting_date        type string,

                it_gl_reference        type string,

                it_header_text         type string,

                it_document_type(2)    type c,

                it_company_code        type string,

                it_currency            type string,

                it_journal_total       type string,

                it_documentnr          type string,

                it_bus_act             type string,

       END OF it_gl_head.

DATA: BEGIN OF it_gl_item occurs 200,

                it_gl             type string,

                it_dr_cr          type string,

                it_gl_amount(10)  type c,

                it_tax_code       type string,

                it_cost_code      type string,

                it_order(12)      type c,

                it_wbs            type string,

                it_personnelnr    type string,

                it_text           type string,

                it_postingKey     type string,

                it_dummy          type string,

       END OF it_gl_item.

data: itab_gl_header   like BAPIACHE08.

data: accountgl        LIKE bapiacgl08 OCCURS 0 WITH HEADER LINE.

data: itab_gl_currency LIKE bapiaccr08 OCCURS 0 WITH HEADER LINE.

data: itab_gl_return   like bapiret2 occurs 0 with header line.

data: extension1       like BAPIEXTC occurs 0 with header line.

data: year             LIKE  INRI-TOYEAR.

data string_length     type i.

data gl_length         type i.

data field_stat_group  like skb1-fstag.

*Prelim posting FM

data: T_VBKPF like  FVBKPF occurs 0 with header line,

       T_VBSEC like  FVBSEC occurs 0 with header line,

       T_VBSEG like  FVBSEG occurs 0 with header line,

       T_VBSET like  FVBSET occurs 0 with header line.

data:  documentnr   like vbkpf-belnr,

        company_code like vbkpf-bukrs,

        fisyear      like vbkpf-gjahr.

data : lw_extension like BAPIEXTC occurs 0 with header line.

data : li_extension like BAPIEXTC occurs 0 with header line.

data: lv_zzcust01(100) type c,

       lv_count(100) type c.

data: t_wbs        like prps-pspnr.

data: t_ext_wbs    like prps-posid.

data: t_prefix(2)  type c.

data: t_mid        type string.

data: t_end        type string.

data: t_mid6(6)    type c.

data: t_end4(4)    type c.

data: t_recon_acc  like lfb1-akont.

data: t_vendornr(10)   type c.

CLEAR: it_gl_head[].

CLEAR: it_gl_item[].

INITIALIZATION.

   CLEAR:   it_gl_head, it_gl_item, w_path, w_file.

   REFRESH: it_gl_head, it_gl_item.

   SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE tmain.

   PARAMETERS: PATH LIKE rlgrap-filename OBLIGATORY DEFAULT 'c:\'.

   PARAMETERS: COMPANY type bukrs OBLIGATORY DEFAULT '1000'.

   SELECTION-SCREEN END OF BLOCK b1.

   DATA: scheck TYPE c.

   scheck = zcl_ehsv_util=>check_authority( im_object = 'ZFI' ) .

   IF scheck =  'N'.

     MESSAGE 'You have no authorization to execute this program.' TYPE 'E'.

   ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR PATH.

   CALL FUNCTION 'F4_FILENAME'

     IMPORTING

       file_name = w_path.

   IF NOT w_path IS INITIAL.

     path = w_path.

   ENDIF.

   w_file = path.

   w_counter = 0.

START-OF-SELECTION.

   CALL FUNCTION 'GUI_UPLOAD'

     EXPORTING:

       filename                = w_file

      TABLES

       data_tab                = i_tab

     EXCEPTIONS

       file_open_error         = 1

       file_read_error         = 2

       no_batch                = 3

       gui_refuse_filetransfer = 4

       invalid_type            = 5

       no_authority            = 6

       unknown_error           = 7

       bad_data_format         = 8

       header_not_allowed      = 9

       separator_not_allowed   = 10

       header_too_long         = 11

       unknown_dp_error        = 12

       access_denied           = 13

       dp_out_of_memory        = 14

       disk_full               = 15

       dp_timeout              = 16

       OTHERS                  = 17.

   IF sy-subrc <> 0.

     MESSAGE ID sy-msgid TYPE 'I' NUMBER sy-msgno

           WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

   ELSE.

     Read table i_tab index 1.

     SPLIT i_tab AT ',' into  it_gl_head-it_doc_date

                              it_gl_head-it_posting_date

                              it_gl_head-it_gl_reference

                              it_gl_head-it_header_text

                              it_gl_head-it_document_type

                              it_gl_head-it_company_code

                              it_gl_head-it_currency

                              it_gl_head-it_journal_total.

     "Get Logical System Name

     DATA: log_sys TYPE tbdls-logsys.

     CALL FUNCTION 'OWN_LOGICAL_SYSTEM_GET'

       IMPORTING

         own_logical_system = log_sys.

     "Build the document header

     itab_gl_header-obj_type   = 'BKPFF'.

     year = it_gl_head-it_posting_date+0(4).

*    required for FM to read document created.

     fisyear = it_gl_head-it_posting_date+0(4).

     company_code = it_gl_head-it_company_code.

     "Build the Object Key

     PERFORM build_obj_key USING it_gl_head-it_document_type it_gl_head-it_company_code  year

           CHANGING itab_gl_header-obj_key.

     string_length = STRLEN( itab_gl_header-obj_key ) - 8.

     documentnr = itab_gl_header-obj_key+0(string_length).

     "Build rest of header information

     it_gl_head-it_documentnr  = itab_gl_header-obj_key+0(string_length).

     itab_gl_header-obj_sys    = log_sys.

     itab_gl_header-username   = sy-uname.

     itab_gl_header-comp_code  = it_gl_head-it_company_code.

     itab_gl_header-pstng_date = it_gl_head-it_posting_date.

     itab_gl_header-trans_date = it_gl_head-it_posting_date.

     itab_gl_header-fisc_year  = it_gl_head-it_posting_date+0(4).

     itab_gl_header-doc_date   = it_gl_head-it_doc_date.

     itab_gl_header-header_txt = it_gl_head-it_header_text.

     itab_gl_header-doc_type   = it_gl_head-it_document_type.

     itab_gl_header-FIS_PERIOD = it_gl_head-it_posting_date+4(2).

     itab_gl_header-ref_doc_no = it_gl_head-it_gl_reference.

     LOOP AT i_tab.

       Read table i_tab.

       if SY-TABIX > 1.

         SPLIT i_tab AT ',' into  it_gl_item-it_gl

                                  it_gl_item-it_dr_cr

                                  it_gl_item-it_gl_amount

                                  it_gl_item-it_tax_code

                                  it_gl_item-it_cost_code

                                  it_gl_item-it_order

                                  it_gl_item-it_wbs

                                  it_gl_item-it_personnelnr

                                  it_gl_item-it_text

                                  it_gl_item-it_postingKey

                                  it_gl_item-it_dummy.

*       populate the items

         data: wa_acc_gl      LIKE  bapiacgl08 OCCURS 0 WITH HEADER LINE. clear: wa_acc_gl.

         data: wa_gl_currency TYPE  bapiaccr08. clear: wa_gl_currency.

         w_counter = w_counter + 10.

         wa_acc_gl-itemno_acc     = w_counter.

         lw_extension-field1 = 'BAPI-PARK'.

         lw_extension-field2 = w_counter.

         lw_extension-field3 = 'ZZCUST05'.

         lw_extension-field4 =  it_gl_item-it_dr_cr.

         append lw_extension to li_extension.

         if it_gl_item-it_gl+0(1) <> '6'.

           CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

             EXPORTING

               INPUT  = it_gl_item-it_gl

             IMPORTING

               OUTPUT = wa_acc_gl-gl_account.

         else.

           CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

             EXPORTING

               INPUT  = it_gl_item-it_gl

             IMPORTING

               OUTPUT = t_vendornr.

           select single akont

             into t_recon_acc

             from lfb1

             where lifnr = t_vendornr.

           if sy-subrc = 0.

             wa_acc_gl-gl_account = t_recon_acc.

             wa_acc_gl-VENDOR_NO = t_vendornr.

             lw_extension-field1 = 'BAPI-PARK'.

             lw_extension-field2 = w_counter.

             lw_extension-field3 = 'ZZCUST06'.

             concatenate it_gl_item-it_postingKey t_vendornr 'K' it_gl_head-it_posting_date into lw_extension-field4.

             append lw_extension to li_extension.

           endif.

         endif.

         select single fstag

         into field_stat_group

         from skb1

         where bukrs = it_gl_head-it_company_code and

               saknr = wa_acc_gl-gl_account.

         if field_Stat_group = 'Z030'.

           lw_extension-field1 = 'BAPI-PARK'.

           lw_extension-field2 = w_counter.

           lw_extension-field3 = 'ZZCUST03'.

           lw_extension-field4 = it_gl_item-it_personnelnr.

           append lw_extension to li_extension.

         endif.

         wa_acc_gl-comp_code      = it_gl_head-it_company_code.

         wa_acc_gl-pstng_date     = it_gl_head-it_posting_date.

         wa_acc_gl-doc_type       = it_gl_head-it_document_type.

         wa_acc_gl-fisc_year      = it_gl_head-it_posting_date+0(4).

         wa_acc_gl-fis_period     = it_gl_head-it_posting_date+4(2).

         if it_gl_item-it_cost_code <> '0'.

           wa_acc_gl-costcenter     = it_gl_item-it_cost_code.

         else.

           wa_acc_gl-costcenter  = ''.

         endif.

         if it_gl_item-it_order <> '0'.

           CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

             EXPORTING

               INPUT  = it_gl_item-it_order

             IMPORTING

               OUTPUT = wa_acc_gl-orderid.

         else.

           wa_acc_gl-orderid  = ''.

         endif.

         wa_acc_gl-wbs_element = ''.

         append wa_acc_gl to accountgl.

         lw_extension-field1 = 'BAPI-PARK'.

         lw_extension-field2 = w_counter.

         lw_extension-field3 = 'ZZCUST04'.

         lw_extension-field4 = it_gl_item-it_text.

         append lw_extension to li_extension.

         wa_acc_gl-ITEM_TEXT      = it_gl_item-it_text.

         wa_gl_currency-itemno_acc   = w_counter.

         wa_gl_currency-currency     = it_gl_head-it_currency.

         wa_gl_currency-amt_doccur   = it_gl_item-it_gl_amount.

         APPEND  wa_gl_currency to itab_gl_currency.

         lw_extension-field1 = 'BAPI-PARK'.

         lw_extension-field2 = w_counter.

         lw_extension-field3 = 'ZZCUST02'.

         lw_extension-field4 = it_gl_item-it_tax_code.

         append lw_extension to li_extension.

       endif.

     ENDLOOP.

     lw_extension-field1 = 'BAPI-PARK'.

     lw_extension-field2 = w_counter.

     lw_extension-field3 = 'ZZCUST01'.

     lw_extension-field4 = lv_zzcust01.

     append lw_extension to li_extension.

     CALL FUNCTION 'BAPI_ACC_GL_POSTING_CHECK'

       EXPORTING

         DOCUMENTHEADER = itab_gl_header

       TABLES

         ACCOUNTGL      = accountgl

         CURRENCYAMOUNT = itab_gl_currency

         RETURN         = itab_gl_return

         EXTENSION1     = li_extension.

     if sy-subrc = 0.

       CALL FUNCTION 'BAPI_ACC_GL_POSTING_POST'

         EXPORTING

           DOCUMENTHEADER = itab_gl_header

         IMPORTING

           obj_type       = itab_gl_header-obj_type

           obj_key        = itab_gl_header-obj_key

           obj_sys        = itab_gl_header-obj_sys

         TABLES

           ACCOUNTGL      = accountgl

           CURRENCYAMOUNT = itab_gl_currency

           RETURN         = itab_gl_return

           EXTENSION1     = li_extension.

       LOOP AT itab_gl_return WHERE type = 'E'.

         exit.

       ENDLOOP.

       IF sy-subrc EQ 0.

         loop at itab_gl_return.

           w_message =  'Journal Failed to create'.

           write: / itab_gl_return-id, itab_gl_return-number, itab_gl_return-message.

         endloop.

       ELSE.

         CLEAR itab_gl_return.

         REFRESH itab_gl_return.

         CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'

           EXPORTING

             WAIT   = 'X'

           IMPORTING

             return = itab_gl_return.

         if sy-subrc eq 0.

           CALL FUNCTION 'PRELIMINARY_POSTING_UPDATE'

             EXPORTING

               BELNR     = documentnr

               BUKRS     = company_code

               GJAHR     = fisyear

               WORKF     = ' '

             EXCEPTIONS

               CANCELLED = 1

               OTHERS    = 2.

           if sy-subrc = 0.

             CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'

               EXPORTING

                 WAIT   = 'X'

               IMPORTING

                 return = itab_gl_return.

             concatenate 'Document : ' it_gl_head-it_documentnr ' Created Successfully!' into w_message.

             MESSAGE w_message TYPE  'I'.

           else.

             concatenate 'Document : ' it_gl_head-it_documentnr  ' Created but not completed.  Investigate and correct' into w_message.

             MESSAGE w_message TYPE  'I'.

           endif.

         endif.

       ENDIF.

     endif.

   endif.

*&---------------------------------------------------------------------*

*&      Form  build_obj_key

*&---------------------------------------------------------------------*

*       Builds the Key used in the settlement process: NEXT ORDER NO

*----------------------------------------------------------------------*

FORM build_obj_key USING doc_type comp_code fisc_year CHANGING obj_key.

   STATICS: s_t003       TYPE t003.

   DATA   : l_belnr      TYPE bsis-belnr.

   DATA   : c_rf_beleg   TYPE inri-object VALUE 'RF_BELEG'.

* Get the Document range and details for GL Docs

*  break martievs.

   IF s_t003-blart NE doc_type.

     CLEAR s_t003-numkr.

     SELECT SINGLE numkr FROM t003 INTO s_t003-numkr

                         WHERE blart EQ doc_type.

   ENDIF.

*  break martievs.

* Get the next number that will be created.

   CALL FUNCTION 'NUMBER_GET_NEXT'

     EXPORTING

       nr_range_nr             = s_t003-numkr

       object                  = c_rf_beleg

       subobject               = comp_code

       toyear                  = fisc_year

     IMPORTING

       number                  = l_belnr

     EXCEPTIONS

       interval_not_found      = 1

       number_range_not_intern = 2

       object_not_found        = 3

       OTHERS                  = 4.

*  break martievs.

   CASE sy-subrc.

     WHEN 1.

       MESSAGE 'Number_Get_Next: Interval Not found.'(nu1) TYPE 'E'.

     WHEN 2.

       MESSAGE 'Number_Get_Next: Number range not intern.'(nu2) TYPE 'E'.

     WHEN 3.

       MESSAGE 'Number_Get_Next: Object Not found.'(nu3) TYPE 'E'.

     WHEN 4.

       MESSAGE 'Number_Get_Next: Unknown error.'(nu4) TYPE 'E'.

   ENDCASE.

   CONCATENATE l_belnr comp_code fisc_year INTO obj_key.

ENDFORM.                    "get_new_awkey

[Former Member]

Hi Nala,

I think you can utilize the same code.... but you will need to fix it in such a way that it works for multiple headers and populates the header and corresponding line item details properly in the BAPI input tables. Quite similar to what i had tried to explain in my previous mail.

Please revert if still in doubt.

Regards,

DN.

[Former Member]

Hi

  if ur file is of type excel

GL1    10 item detail

           20 item details

GL2    10 item details

then you need to check the first COL contains data then it was heaedr data with item data

and if the first col is empty then it is line item related to the above header

[Former Member]

Hi thanks for the reply,

I am uploading using a CSV format.

I am using BAPI_ACC_GL_POSTING_POST.