Creation of FI documents from an Excel template
This tutorial shows you how you can create an Excel template based on 2 sheets (documents headers + line items) and use the XLR3 Data Injector to convert its data into transactional records that may be injected into SAP R/3 using the FB01 transaction.
Step 1. Definition of the Excel template.
We create 2 Excel sheets. One for the documents headers named HEADER and one for the documents line items called Line Items. The definition of the sheets is the following:
As you can see we have foreseen a column for the fields document date, document type, company code, posting date and currency in the document header data sheet.
We included at the level of the line items the fields posting key, account, amount and profit center. The first column is the document index in the header Excel sheet to which the line items relate. This column is used to link line items to header data.
Step 2. Creation of the record type.
We create a record type using the transaction recorder for transaction FB01 and modify it according to our needs (deletion of unnecessary fields and repeated screens) so that we finally obtain:
Step 3. Definition of the XLR3Script generation code.
The XLR3Script generation code to convert the data provided in our Excel sheet into valid records for SAP is the following:
Sheet1 = CREATE_SHEET('Header', 1)
Sheet2 = CREATE_SHEET('Line Items', 1)
Document_Number = 0
LOOP AT Sheet1.Rows INTO SheetRow
Document_Number = Document_Number + 1
Record = CREATE_RECORD('FB01_1')
Screen0 = Record.Add_Screen('SAPMF05A', '0100', 0)
Screen0.Field('BDC_OKCODE') = '/00'
Screen0.Field('BKPF-BLDAT') = SheetRow.'BKPF-BLDAT'
Screen0.Field('BKPF-BLART') = SheetRow.'BKPF-BLART'
Screen0.Field('BKPF-BUKRS') = SheetRow.'BKPF-BUKRS'
Screen0.Field('BKPF-BUDAT') = SheetRow.'BKPF-BUDAT'
Screen0.Field('BKPF-WAERS') = SheetRow.'BKPF-WAERS'
Screen0.Field('FS006-DOCID') = '*'
FirstLine = 1
LOOP AT Sheet2.Rows INTO SheetRow1
IF SheetRow1[0] = Document_Number
IF FirstLine = 1
Screen0.Field('RF05A-NEWBS') = SheetRow1.'RF05A-NEWBS'
Screen0.Field('RF05A-NEWKO') = SheetRow1.'RF05A-NEWKO'
FirstLine = 0
ENDIF
Screen1 = Record.Add_Screen('SAPMF05A', '0300', 0)
Screen1.Field('BDC_OKCODE') = '/00'
Screen1.Field('BSEG-WRBTR') = SheetRow1.'BSEG-WRBTR'
LastLine = 0
IF (&LOOP_INDEX& < Sheet2.Rows.Count - 1)
NextRow = Sheet2.Rows[&LOOP_INDEX& + 1]
IF NextRow[0] <> Document_Number
LastLine = 1
ENDIF
ELSE
LastLine = 1
ENDIF
IF LastLine = 0
Screen1.Field('BDC_OKCODE') = '/00'
Screen1.Field('RF05A-NEWBS') = NextRow.'RF05A-NEWBS'
Screen1.Field('RF05A-NEWKO') = NextRow.'RF05A-NEWKO'
ELSE
Screen1.Field('BDC_OKCODE') = '=BU'
CLEAR Screen1.Field('RF05A-NEWBS')
CLEAR Screen1.Field('RF05A-NEWKO')
ENDIF
Screen2 = Record.Add_Screen('SAPLKACB', '0002', 0)
Screen2.Field('BDC_OKCODE') = '=ENTE'
Screen2.Field('COBL-PRCTR') = SheetRow1.'COBL-PRCTR'
ENDIF
ENDLOOP
ENDLOOP
| 