Bank Acc. Reconciliation – Dynamics 365 BC Table Reference

Dynamics 365 BC Table Reference Tables Company Tables BBank Acc. Reconciliation

Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/273

Table Type: Company

Table Name: Bank Acc. Reconciliation
Database Table Name: CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972

Table Number: 273

Due to how Dynamics BC tables and columns are named, square brackets need to be wrapped around the table name and column names. Below are example queries showing how Bank Acc. Reconciliation can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

   [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns by name

SELECT

   [Bank Account No_]
   ,[Statement No_]
   ,[Statement Ending Balance]
   ,[Statement Date]
   ,[Balance Last Statement]
   ,[Bank Statement]
   ,[Statement Type]
   ,[Shortcut Dimension 1 Code]
   ,[Shortcut Dimension 2 Code]
   ,[Post Payments Only]
   ,[Import Posted Transactions]
   ,[Copy VAT Setup to Jnl_ Line]
   ,[Allow Duplicated Transactions]
   ,[Dimension Set ID]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]

FROM

   [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns including those from all joined tables

SELECT
   *
FROM
   [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bank Acc. Reconciliation]
LEFT JOIN
   [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
      ON
         [Bank Acc. Reconciliation].[Shortcut Dimension 1 Code] = [Dimension].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
      ON
         [Bank Acc. Reconciliation].[Shortcut Dimension 2 Code] = [Dimension].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Dimension Set Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension Set Entry]
      ON
         [Bank Acc. Reconciliation].[Dimension Set ID] = [Dimension Set Entry].[Dimension Set ID]
LEFT JOIN
   [User] AS [User]
      ON
         [Bank Acc. Reconciliation].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [Bank Acc. Reconciliation].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in Bank Acc. Reconciliation table

Field No.	Field Name	Field Caption	Class	Type	Length	SQL Column Name	SQL DataType
1	Bank Account No.	Bank Account No.	Normal	Code	20	Bank Account No_	nvarchar(40)
	Key to join to the Bank Account table. Show/hide example query SELECT [Bank Acc. Reconciliation].[Bank Account No_] ,[Bank Account].[No_] FROM [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bank Acc. Reconciliation] LEFT JOIN [CRONUS UK Ltd_$Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bank Account] ON [Bank Acc. Reconciliation].[Bank Account No_] = [Bank Account].[No_]
2	Statement No.	Statement No.	Normal	Code	20	Statement No_	nvarchar(40)
3	Statement Ending Balance	Statement Ending Balance	Normal	Decimal	12	Statement Ending Balance	decimal(38,38)
4	Statement Date	Statement Date	Normal	Date	4	Statement Date	datetime
5	Balance Last Statement	Balance Last Statement	Normal	Decimal	12	Balance Last Statement	decimal(38,38)
6	Bank Statement	Bank Statement	Normal	BLOB	8	Bank Statement	image
7	Total Balance on Bank Account	Total Balance on Bank Account	FlowField	Decimal	12
8	Total Applied Amount	Total Applied Amount	FlowField	Decimal	12
9	Total Transaction Amount	Total Transaction Amount	FlowField	Decimal	12
10	Total Unposted Applied Amount	Total Unposted Applied Amount	FlowField	Decimal	12
11	Total Difference	Total Difference	FlowField	Decimal	12
12	Total Paid Amount	Total Paid Amount	FlowField	Decimal	12
13	Total Received Amount	Total Received Amount	FlowField	Decimal	12
20	Statement Type	Statement Type	Normal	Option	4	Statement Type	int
	Available options are: “Bank Reconciliation” “Payment Application”
21	Shortcut Dimension 1 Code	Shortcut Dimension 1 Code	Normal	Code	20	Shortcut Dimension 1 Code	nvarchar(40)
	Key to join to the Dimension table. Show/hide example query SELECT [Bank Acc. Reconciliation].[Shortcut Dimension 1 Code] ,[Dimension].[Code] FROM [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bank Acc. Reconciliation] LEFT JOIN [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension] ON [Bank Acc. Reconciliation].[Shortcut Dimension 1 Code] = [Dimension].[Code]
22	Shortcut Dimension 2 Code	Shortcut Dimension 2 Code	Normal	Code	20	Shortcut Dimension 2 Code	nvarchar(40)
	Key to join to the Dimension table. Show/hide example query SELECT [Bank Acc. Reconciliation].[Shortcut Dimension 2 Code] ,[Dimension].[Code] FROM [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bank Acc. Reconciliation] LEFT JOIN [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension] ON [Bank Acc. Reconciliation].[Shortcut Dimension 2 Code] = [Dimension].[Code]
23	Post Payments Only	Post Payments Only	Normal	Boolean	4	Post Payments Only	tinyint
24	Import Posted Transactions	Import Posted Transactions	Normal	Option	4	Import Posted Transactions	int
	Available options are: ” “ “Yes” “No”
25	Total Outstd Bank Transactions	Total Outstd Bank Transactions	FlowField	Decimal	12
26	Total Outstd Payments	Total Outstd Payments	FlowField	Decimal	12
27	Total Applied Amount Payments	Total Applied Amount Payments	FlowField	Decimal	12
28	Bank Account Balance (LCY)	Bank Account Balance ($)	FlowField	Decimal	12
29	Total Positive Adjustments	Total Positive Adjustments	FlowField	Decimal	12
30	Total Negative Adjustments	Total Negative Adjustments	FlowField	Decimal	12
31	Total Positive Difference	Total Positive Difference	FlowField	Decimal	12
32	Total Negative Difference	Total Negative Difference	FlowField	Decimal	12
33	Copy VAT Setup to Jnl. Line	Copy Tax Setup to Jnl. Line	Normal	Boolean	4	Copy VAT Setup to Jnl_ Line	tinyint
50	Bank Account Name	Bank Account Name	FlowField	Text	100
51	Allow Duplicated Transactions	Allow Duplicated Transactions	Normal	Boolean	4	Allow Duplicated Transactions	tinyint
480	Dimension Set ID	Dimension Set ID	Normal	Integer	4	Dimension Set ID	int
	Key to join to the Dimension Set Entry table. Show/hide example query SELECT [Bank Acc. Reconciliation].[Dimension Set ID] ,[Dimension Set Entry].[Dimension Set ID] FROM [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bank Acc. Reconciliation] LEFT JOIN [CRONUS UK Ltd_$Dimension Set Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension Set Entry] ON [Bank Acc. Reconciliation].[Dimension Set ID] = [Dimension Set Entry].[Dimension Set ID]
2000000000	$systemId	System ID	Normal	GUID	16	$systemId	uniqueidentifier
2000000001	SystemCreatedAt	Created At	Normal	DateTime	8	$systemCreatedAt	datetime
2000000002	SystemCreatedBy	Created By	Normal	GUID	16	$systemCreatedBy	uniqueidentifier
	Key to join to the User table. Show/hide example query SELECT [Bank Acc. Reconciliation].[$systemCreatedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$Bank Acc_ Reconciliation$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bank Acc. Reconciliation] LEFT JOIN [User] AS [User] ON [Bank Acc. Reconciliation].[$systemCreatedBy] = [User].[User Security ID]
2000000003	SystemModifiedAt	Modified At	Normal	DateTime	8	$systemModifiedAt	datetime
2000000004	SystemModifiedBy	Modified By	Normal	GUID	16	$systemModifiedBy	uniqueidentifier

Dynamics 365 BC Table Reference Tables Company Tables BBank Acc. Reconciliation

Leave a Reply Cancel reply