topic Re: GL Upload program in Application Development and Automation Discussions

GL Upload program

Former Member — Thu, 21 Nov 2013 07:16:54 GMT

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?

Re: GL Upload program

arivazhagan_sivasamy — Thu, 21 Nov 2013 07:25:07 GMT

Hi ntobeko Nala,

How are you uploading file?

Using BDC or BAPI.

If its BDC, separate code have to be used.

Arivazhagan S

Re: GL Upload program

former_member191728 — Thu, 21 Nov 2013 08:34:23 GMT

can u please tell the file format of ur excel file

how are you posting the FI document using BDC or BAPI

Re: GL Upload program

Former Member — Thu, 21 Nov 2013 08:57:57 GMT

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.

Re: GL Upload program

Former Member — Thu, 21 Nov 2013 09:02:44 GMT

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

Re: GL Upload program

Former Member — Thu, 21 Nov 2013 10:13:54 GMT

Hi thanks for the reply,

I am uploading using a CSV format.

I am using BAPI_ACC_GL_POSTING_POST.

Re: GL Upload program

Former Member — Thu, 21 Nov 2013 10:15:22 GMT

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.

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.

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

*& 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

Re: GL Upload program

Former Member — Thu, 21 Nov 2013 13:17:41 GMT

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.