Bank Receipts/Payments
Bank Receipts/Payments move money in and out of Bank Accounts. Some of the fields used cannot be seen on the Bank Record activity screen but are used elsewhere in the system. The key fields that header records are grouped on are InvRef and Date.
Field Mapping (mandatory fields in RED)
|
E2S Field |
SAGE Field |
Location within Sage |
Data Type |
Notes |
|
|
ID |
N/A |
N/A |
int |
Used internally and not uploaded to Sage |
|
|
AccountRef |
A/C |
Bank Record - Activity |
nvarchar(8) |
Bank Account this Receipt/Payment is for |
|
|
InvRef |
Ref |
Bank Record - Activity - Transaction Line |
nvarchar(30) |
A key field for this tab. This controls the creation of each header record. Lines with identical AccountRef/InvRef/Date will be grouped as one |
|
|
ExtRef |
Internal ref |
n/a |
nvarchar(30) |
Not visible on Bank Activity screen |
|
|
Date |
Date |
Bank Record - Activity - Transaction Line |
datetime |
Also a key field - records are grouped on the InvRef and this field |
|
|
TaxCode |
n/a Tax Code |
n/a |
nvarchar(2) |
Not visible on the Bank Activity Screen or Nominal Ledger |
|
|
Value |
n/a Amount |
n/a |
decimal(18, 2) |
Not visible on the Bank Activity Screen or Nominal Ledger |
|
|
VATAmount |
n/a combined with above |
n/a |
decimal(18, 2) |
Not visible on the Bank Activity Screen or Nominal Ledger |
|
|
Details |
Details |
Bank Record - Activity - Transaction Line |
nvarchar(60) |
|
|
|
NomCode |
n/a |
n/a |
nvarchar(8) |
Not visible on the Bank Activity Screen |
|
|
Dept |
n/a Dept |
n/a |
nvarchar(2) |
Not visible on the Bank Activity Screen |
|
|
ProjectCode |
n/a - Project Code |
n/a |
8nvarchar() |
Not visible on the Bank Activity Screen or Nominal Ledger - only in Project Activity |
|
|
CostCode* |
n/a - Cost Code |
n/a |
nvarchar(8) |
Not visible on the Bank Activity Screen or Nominal Ledger - only in Project Activity |
|
|
Fund |
Funds |
n/a |
nvarchar(4) |
Not visible on the Bank Activity Screen - Also visible in Funds Activity screen |
|
|
ExchangeRate |
n/a |
n/a |
decimal(18, 2) |
Not visible on the Bank Activity Screen or Nominal Ledger |
|
|
Status |
N/A |
N/A |
nvarchar(8) |
|
NB: If the Sage company is set-up for foreign currency transactions and if the transfer is for a foreign currency bank account then the ExchangeRate in the XLS/Database is used if present and the rate for the currency on the account is used if not.
* Receipts only
Script to create the table(s)...
CREATE TABLE [dbo].[BankPayments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExtRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[TaxCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [decimal](18, 2) NULL,
[VATAmount] [decimal](18, 2) NULL,
[Details] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dept] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fund] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExchangeRate] [decimal](18, 2) NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BankPayments_Status] DEFAULT (N'New'),
CONSTRAINT [PK_BankPayments] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----
CREATE TABLE [dbo].[BankReceipts](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExtRef] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[TaxCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [decimal](18, 2) NULL,
[VATAmount] [decimal](18, 2) NULL,
[Details] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dept] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fund] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExchangeRate] [decimal](18, 2) NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BankReceipts_Status] DEFAULT (N'New'),
CONSTRAINT [PK_BankReceipts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]