Excel Templates

Excel templates are used as the main data source for journals as Excel is a familiar tool to many people and allows easy data entry of multiple lineitems.

Example Journal

The attached file is a simple example showing a journal using the map format (See General Format Information)

Download an example here

Field Mapping

In general the fields are mapped using the SAP technical names that can be sourced in structure BBKPF, BKDF and BBSEG. Some fields do need special handling as described below due to differences between BSEG (SAP Field/lineitems) and BBSEG (Template Field/Batch-input lineitems)

Special Field Mapping

Local Ledger

To post to local ledger groups (NewGL only) you enter transaction code as FB01L in the template and add field BKPF-LDGRP to the template [Map file should contain FLDID=BKPF_LDGRP]

Exchange Rates

In SAP the entry of exchange rates is controlled by your customising settings. Promenta should accept the same format of exchange rates that your SAP system (via FB01) accepts. This includes referencing table TCURP to setup prefixes for values.

Example when TCURP is empty (this is the most common scenario)

• Multiplier : 999.99  eg 1.54
• Divider : /999.99   eg /1.54

Example when TCURP is set as P=multiple and M=divider

• Multiplier : P999.99  eg P1.54
• Divider : M999.99   eg M1.54

Custom fields

Fields added via the standard SAP process (OXK1) should work without any additional configuration as long as the fields added appear in both BSEG and BBSEG. If fields have been added in a different way then additional chargeable consultancy will be required to establish the best approach to dealing with those fields.

Comments (Since SP24-1)

You can have comments in the lineitems by setting the posting key (NEWBS) column to text COMMENT. The entire lineitem will be ignored.
In addition entries with blank posting keys are also treated as comment rows.

Clear Data (Since SP24-3)

Sometimes SAP defaults data such as cost centres, WBS, Profit centres into the FB01 transaction. In some cases customer may wish to clear (IE blank out) these default values for business reasons.
If a forward slash (/) is entered into the upload file as the value for a field the solution will attempt to clear the value.

Restrictions :
The clear function can only clear default values that appear in the FB01 screen on entry. Values that come in when a button is pressed cannot be cleared.
The clear function cannot circumvent the required field checks of SAP – if a required field is cleared an error will occur.
Substitution rules may put values in at posting time – this cannot be prevented.

Note that this is only supported for specific fields listed below:
Cost Centre
Profit Centre
WBS

Multiple Documents – Format

Since SP27-2 uploaded journals can contain more than one journal entry (ie multiple documents) – however they all follow a single workflow approval path.

Tax Entry

Promenta works in line with SAP tax entry process – please see SAP Note 15788 for more details.

In summary there are three ways to populate taxes in the excel sheet as below. If you “mix” the method you will get errors on simulation. Please pay attention to balancing calculation as it is different depending on the method used.

Option 1 : Automatic calculation

Set the “Calculate Tax” field in the header (BKPF-XMWST) to be value X. This will enable SAP to calculate the tax against the entered tax codes. You cannot enter any tax amounts on lineitems – only enter tax codes.

Option 2 : Manual entry of total

You can enter the total tax to be paid and allow the SAP system to automatically distribute the tax over the different tax codes.

Here you must enter the vendor/customer as the FIRST lineitem and you can enter the total tax in field BSEG-WMWST.

You cannot enter other tax amounts for other lineitems – you will get an error

The balancing of the document is slightly different = Vendor/Customer amount minus total tax (as total tax will have opposite posting key to vendor/customer lineitem) and sum of the other lineitems.

Option 3 : Manual entry of all tax

You can enter the tax amount against each GL with a tax code. The first lineitem cannot be a vendor/customer line with a tax value. 

You should not enter a tax amount against any vendor/customer line as it will be ignored by SAP and give a balance error in Promenta.

Balancing of the document = Vendor/Customer amount minus sum of all lineitems and sum of all tax amounts (tax amount and GL amounts use same posting key to determine debit/credit)