Skip to content

Macola® ES BankTransactions table reference

Adapted from the Leahy Consulting table definitions workbook "Macola® ES Bank Transactions Table Definitions", updated 15 July 2019.

BankTransactions is where Macola® ES holds cash receipts, payments, bank statement lines and everything else that moves through Cash Flow. Anyone writing a Crystal report or a SQL query against cash needs the column names and, more often, the code values — the four code sets below are what turn a row into something a person can read.

Status codes

Code Meaning
A Authorized
C Entered
D Deposited
J Journalized
P Processed
R Matched
U Unallocated
V Void

Type codes

Code Meaning
C Template
N Non-financial transaction
P Bank statement header
S Cashflow
V VerwInfo
W Term

Transaction types

Code Meaning
A Receipt
B Fulfillment
C Sales credit note
D Debit memo / finance charge
E Revaluation
F Discount / surcharge
G Counts
H Return fulfillment
J Return receipt
K Sales invoice
L Labor hours
M Machine hours
N Other
O POS sales invoice
P Prepayment
Q Purchase credit note
R Refund
S Reversal credit note
T Purchase invoice
U Credit surcharge
W Payroll
X Year / period closing
Y Payment
Z Cash receipt

Payment methods

Code Meaning
A American Express
B Bill of exchange
C Cheques
D Debit transfer
E EuroCard / MasterCard
I Diners Club
M MasterCard
N Other
S Discover
T Credit transfer
V VISA

Columns

Ninety columns, in alphabetical order.

Column Description Notes Length
AdvanceInvoiceNumber Advance invoice 8
AmountDC Amount in default currency Amount in local/home currency 8
AmountTC Transaction currency amount Amount is alternate currency 8
Approved Approved 8
Approved2 Approved2 8
Approver Approver 10
Approver2 Approver2 10
BatchNumber Batch no. 4
bedrnr Company number 3
Blocked Blocked 1
cnt_id Contact ID 16
CreditCardAuthCode Authorization code 20
CreditCardResult Credit card result 20
CreditCardTransID Tansaction ID 25
CreditorNumber Creditor number Vendors 6
DebtorNumber Debtor number Customers 6
DepositDate Deposit date 8
DepositNumber Deposit number 8
Description Description Varies depending on source of trx 40
DocAttachmentID Attachments 16
DocumentID Document 16
DueDate Due date When transaction is due 8
EntryNumber Entry number 8
ExchangeRate Exchange rate Currency exchange rate 8
ExtraCurrencyAmount ExtraCurrencyAmount 8
ExtraCurrencyCode ExtraCurrencyCode 3
FileName File name 12
HumanResourceID Resource 4
ImportAutoMatch Import auto match 1
InstrumentBank Instrument bank 50
InstrumentReference Instrument reference 4
InstrumentStatus Instrument status 1
InvoiceCode Invoice code 3
InvoiceDate Invoice Date Self explanatory 8
InvoiceNumber Invoice number 30
Journalized Journalized 8
Journalizer Journalizer 10
LedgerAccount Ledger account 9
LinkID Link ID 4
MatchID Match ID 4
MaturityDays Maturity days 8
OffsetAddressLine1 Offset address line 1 30
OffsetAddressLine2 Offset address line 2 30
OffsetAddressLine3 Offset address line 3 30
OffsetBankAccount Offset bank account 34
OffsetBankCountry Offset bank country 3
OffsetBankName Offset bank 30
OffsetBankSWIFTCode Offset bank SWIFT code 11
OffsetCity Offset city 30
OffsetCountryCode Offset country code 3
OffsetIdentificationNumberBank Offset identification number bank 8
OffsetLedgerAccountNumber Offset ledger account number Looks like 1st segment of GL acct 9
OffsetName Offset name Customer/Vendor Name 50
OffsetPostalCode Offset postal code 30
OffsetReference Offset reference 16
OrderNumber Order number OE or PO number? 8
OwnBankAccount Own bank account reference 34
OwnBankAccountRef Own bank account 34
OwnReference Own reference 16
PaymentCondition Payment condition Terms Code (net 30, etc) 2
PaymentDays Payment days 8
PaymentMethod Payment Method ****See Codes Below 1
PaymentType Payment type 1
Prepayment Prepayment 1
Processed Processed 8
ProcessingDate Processing date Posted Date? 8
Processor Processor 10
ReportingDate Reporting date 8
SequenceNumber Sequence number 5
StatementDate Statement date 8
StatementLineNumber Statement line number 6
StatementNumber Statement number 10
StatementType Statement type 1
Status Status *See Codes Below 1
SupplierInvoiceNumber Your reference 40
syscreated syscreated 8
syscreator syscreator 4
sysguid sysguid 16
sysmodified sysmodified 8
sysmodifier sysmodifier 4
TaxInvoiceDate Tax invoice date 8
TaxInvoiceNumber Tax invoice number 30
TCCode Transaction currency code Currency code for this trx 3
TermPercentage Percentage of payment term 1
TransactionNumber Instrument no. 20
TransactionType Transaction type *** See Codes Below 1
Type Type ** See Codes Below 1
ValueDate Value date Use this date and not the invoicke date 8
VATCode VAT code 3
verwijder Delete 1
Warehouse Warehouse code Location 4

Tip

Related. The general ledger side of the same job — the GBKMUT table and the ES entry-screen field names that map onto it — is in The GBKMUT file and the other ES accounting tables.

Support & contact

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