Skip to content

The GBKMUT file and the other ES accounting tables

Adapted from the Leahy Consulting technical paper "How to use the GBKMUT and other ES accounting files", updated April 2007. The field information was obtained from copyrighted Exact Software sources.

GBKMUT is the final destination of every transaction that affects the general ledger in Macola® ES. It is typically as large as all your other Macola® files put together, and it is where any serious Crystal report or SQL query about the general ledger has to go.

It is also tedious to work with, because most of the field names are in Dutch. A few are in English — generally fields added later, or fields whose Dutch name happens to match. The tables below are what makes it usable.

The other tables you will need

Table What it holds
GRTBK Chart of accounts — one record per account number, with the number and description
KSPREK Cost centers — one record per cost center, with the number and description
KSDREK Cost units — the companion to the cost center table
ARCUSFIL Customers. This is a SQL view, not a table
APVENFIL Vendors. Also a SQL view

The fields you will actually use

What you want GBKMUT field
Account number reknr
Account description (in GRTBK) oms25_o
Cost center kstplcode
Cost unit kstdrcode
G/L date of the transaction datum
Month number periode
Year bkjrcode
Document date docdate
Amount — document or budget bdr_hfl
Customer number debnr
Vendor number crdnr
Document number docnumber
Project number project
Budget version budvers
Item number artcode
Item quantity aantal
Journal number dagbknr
Reference oms25

Extracting budget data

Selection criteria:

{gbkmut.transtype} = "B" and {gbkmut.entryorigin} = "U"

The file holds budget data for every year, period and budget version, so also select the right:

  • budget version (bud_vers)
  • budget year (bkjrcode)
  • budget period (periode)

For a project budget, select on the project code (project) as well.

Extracting actual data

Selection criteria:

Not (isnull({gbkmut.project})) and {gbkmut.transtype} in ['N', 'C', 'P']

The file holds actual data for every year and period, so also select the right year (bkjrcode) and month (periode). For a project, select on project.

Where each entry screen field ends up

This is the part most people are looking for. In Macola® ES the field name on the entry screen, the column name in the views, and the column name in the table are three different things. Progression stored this information in open item files whose column names matched the data entry fields; ES does not.

Accounts payable

Transaction Field on entry screen Column in table Column in view
Voucher Voucher number Faktuurnr Our Reference
Voucher Invoice number Docnumber Your Reference
Voucher Purchase order number Bkstnr_sub SO Number
Voucher Reference — defaults to AP Vch: xxxxxxxx OMS25 Description
Credit memo Voucher number applied to — not the credit memo number Faktuurnr Our Reference
Credit memo Apply To (voucher invoice number) Docnumber Your Reference
Credit memo Purchase order number Bkstnr_sub SO Number
Credit memo Reference — defaults to AP Crm: xxxxxxxx OMS25 Description
Cancellation voucher Voucher number applied to — not the cancellation voucher number Faktuurnr Our Reference
Cancellation voucher Invoice number Docnumber Your Reference
Cancellation voucher Purchase order number Bkstnr_sub SO Number
Cancellation voucher Reference — defaults to AP XVch: xxxxxxx OMS25 Description
Check Voucher number the check is applied to Faktuurnr Our Reference
Check Invoice number the check is applied to Docnumber Your Reference
Check Purchase order number of the voucher Bkstnr_sub SO Number
Check Description — defaults to AP Vch: xxxxxxx OMS25 Description

Purchase order

Transaction Field on entry screen Column in table Column in view
P/O receipt Receiver number Faktuurnr Our Reference
P/O receipt Purchase order number and item description Docnumber Your Reference
P/O receipt Purchase order number Bkstnr_sub SO Number
P/O receipt PO RCPT (default) OMS25 Description

Accounts receivable

Transaction Field on entry screen Column in table Column in view
A/R sales Document number Faktuurnr Our Reference
A/R sales Purchase order number Docnumber Your Reference
A/R sales Reference — defaults to AR Inv: xxxxxxxx OMS25 Description
Debit memo Apply To number Faktuurnr Our Reference
Debit memo Purchase order number Docnumber Your Reference
Debit memo Reference — AR Dbm: xxxxxxxx OMS25 Description
Credit memo Apply To number Faktuurnr Our Reference
Credit memo Purchase order number Docnumber Your Reference
Credit memo Reference — defaults to AR Crm: xxxxxxxx OMS25 Description
Finance charge Document number Faktuurnr Our Reference
Finance charge Purchase order number Docnumber Your Reference
Finance charge Reference — defaults to AR Fch: xxxxxxxx OMS25 Description
Service invoice Document number Faktuurnr Our Reference
Service invoice Purchase order number Docnumber Your Reference
Service invoice Reference — defaults to SV Inv: xxxxxxxx OMS25 Description
Service credit memo Apply To number Faktuurnr Our Reference
Service credit memo Purchase order number Docnumber Your Reference
Service credit memo Reference — defaults to SV Crm: xxxxxxxx OMS25 Description

Order entry

Transaction Field on entry screen Column in table Column in view
O/E invoice Invoice number Faktuurnr Our Reference
O/E invoice Purchase order number Docnumber Your Reference
O/E invoice O/E order number Bkstnr_sub SO Number
O/E invoice OE Inv: xxxxxxxx OMS25 Description
O/E invoice — I/M distributions Invoice number Faktuurnr Our Reference
O/E invoice — I/M distributions Purchase order number Docnumber Your Reference
O/E invoice — I/M distributions O/E order number Bkstnr_sub SO Number
O/E invoice — I/M distributions OE CogsI Ord: xxxxxxx OMS25 Description
O/E credit memo Apply To Faktuurnr Our Reference
O/E credit memo Purchase order number Docnumber Your Reference
O/E credit memo Credit memo order number Bkstnr_sub SO Number
O/E credit memo OE CRM: xxxxxxxx OMS25 Description
O/E credit memo — I/M distributions Apply To Faktuurnr Our Reference
O/E credit memo — I/M distributions Purchase order number Docnumber Your Reference
O/E credit memo — I/M distributions Credit memo order number Bkstnr_sub SO Number
O/E credit memo — I/M distributions OE CogsC Ord: xxxxxxx OMS25 Description

Every field in GBKMUT

Alphabetical. The fields in bold are the ones you will use most often.

Field Description
aantal Item quantity
afldat Delivery date
amountcentral Amount in central currency
artcode Item number
bankacc Bank account number
BankTransactionGUID Bank transaction guid
bdrkredbep No longer used
bdrkredbp2 No longer used
bdr_hfl Actual or budget amount
bdr_val Amount in foreign currency
betaalref Payment reference
betcond No longer used
bkjrcode Financial year
bkstnr Entry number
bkstnr_sub Order number sub-administration
blockitem Authorized
btwper VAT percentage
btw_bdr_3 VAT amount in division currency
btw_code VAT code
btw_grond VAT base amount in division currency
btw_grval VAT base amount in foreign currency
btw_nummer VAT number
bud_vers Budget version
CashRegisterAccount Cash register
Checked Checked
Cmp_wwn Account guid
companycode Division code
comp_code Component
Correction Correction
crdnr Vendor number
currencyaliasac Division currency code
currencycode Default currency code
dagbknr Journal number
datum Transaction date
dbk_verwnr Journal posting number
debnr Customer number
Discount Discount percentage
DocAttachmentId Attachement ID
docdate Document date
docnumber Reference
documentid Document ID
EndTime End time
EntryGuid Entry guid
entryorigin Entry origin
exvalbdr Amount in cross currency
exvalcode Cross currency code
facode Fixed assets code
faktuurnr Our reference
freefield1 Free field 1
freefield2 Free field 2
freefield3 Free field 3
freefield4 Free field 4
freefield5 Free field 5
ID Unique identifier
IntArea Intrastat area code
IntComplete Intrastat complete
IntLandAssembly Country of assembly
IntLandDestOrig Intrastat standard Code
IntLandISo Intrastat ISO country
IntPort Intrastat sea-, airport
IntStandardCode Intrastat standard code
IntStatNr Intrastat statistical number
IntStatUnit Intrastat statistical units
IntSystem Intrastat system
IntTransA Intrastat transaction A
IntTransB Intrastat transaction B
IntTransportMethod Intrastat transportation method
IntTransShipment Intrastat transshipment search code
IntWeight Intrastat weight
koers Foreign currency exchange rate
koers3 Exchange rate outstanding item
kredbep No longer used
kstdrcode Cost unit
kstplcode Cost center
lastreminderdate Last reminder date
LinkedLine Line number linked
oms25 Description
oorsprong Origin
Orderdebtor Order debtor
Original_Quantity Original quantity
paymentmethod Payment method
PayrollCosts Costs
PayrollSubType Payroll sub type
periode Month
Pricelist Pricelist
project Project code
raplist Report number EU Sales List
rapnr Report number VAT declaration
rate Division currency exchange rate
ReconcileNumber Serial number
regel Line number
regelcode Code generated lines
reknr General ledger account number
remindercount Security level
reminderlayout Reminder layout
ReportingDate No longer used
res_id Human Resource ID
Reviewed Reviewed
Selcode Selection code
Shipment Shipment code
StartTime Start time
stat_nr Statement number
StockTrackingNumber Tracking number
storno Reversal entry
syscreated Creation date
syscreator Creator ID
sysguid Unique identifier
sysmodified Modification date
sysmodifier Modifier ID
TaxCode2 Tax code 2
TaxCode3 Tax code 3
TaxCode4 Tax code 4
TaxCode5 Tax code 5
TaxBasis2 Tax basis 2
TaxBasis3 Tax basis 3
TaxBasis4 Tax basis 4
TaxBasis5 Tax basis 5
TaxAmount2 Tax amount 2
TaxAmount3 Tax amount 3
TaxAmount4 Tax amount 4
TaxAmount5 Tax amount 5
tegreknr Offset G/L account number
timestamp Timestamp
TransactionGuid Transaction guid
TransactionGuid2 Transaction guid from parent transaction
transactiontype Transaction type
transsubtype Transaction subtype
transtype Transaction type
Unitcode Unit code
valcode Foreign currency code
vatamountcentral VAT amount in default currency (used in e-Synergy only)
vatbaseamountcentral VAT base amount in default currency
vervdatfak Invoice due date
vervdatkrd No longer used
vervdtkrd2 No longer used
verwerknrl Posting number
vlgn_gbk2 Sequence number 2
volgnr5 Sequence number
Warehouse Warehouse
Warehouse_Location Warehouse location
wisselkrs Cross currency exchange rate
verwijder No longer used

Fields marked “no longer used”

Several columns survive from older releases and are no longer populated — bdrkredbep, bdrkredbp2, betcond, kredbep, vervdatkrd, vervdtkrd2, ReportingDate and verwijder. Do not build a report on them.

Tip

Why the file is so large. GBKMUT is the single biggest reason a Macola® ES database slows down over the years. Macola® has a program that compresses it by merging similar records — see Making Macola® run faster.

Support & contact

Our team is glad to help with anything from a quick question to a full implementation.