Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/461
Table Type: Company
Table Name: Prepayment Inv. Line Buffer
Database Table Name: CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972
Table Number: 461Due 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 Prepayment Inv. Line Buffer can be queried.
SELECT
*
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972]
SELECT
[G_L Account No_]
,[Line No_]
,[Amount]
,[Description]
,[Gen_ Bus_ Posting Group]
,[Gen_ Prod_ Posting Group]
,[VAT Bus_ Posting Group]
,[VAT Prod_ Posting Group]
,[VAT Amount]
,[VAT Calculation Type]
,[VAT Base Amount]
,[Amount (ACY)]
,[VAT Amount (ACY)]
,[VAT Base Amount (ACY)]
,[VAT Difference]
,[VAT _]
,[VAT Identifier]
,[Global Dimension 1 Code]
,[Global Dimension 2 Code]
,[Job No_]
,[Amount Incl_ VAT]
,[Tax Area Code]
,[Tax Liable]
,[Tax Group Code]
,[Invoice Rounding]
,[Adjustment]
,[VAT Base Before Pmt_ Disc_]
,[Orig_ Pmt_ Disc_ Possible]
,[Location Code]
,[Dimension Set ID]
,[Job Task No_]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972]
SELECT
*
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
ON
[Prepayment Inv. Line Buffer].[Global Dimension 1 Code] = [Dimension].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
ON
[Prepayment Inv. Line Buffer].[Global Dimension 2 Code] = [Dimension].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Tax Area$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Area]
ON
[Prepayment Inv. Line Buffer].[Tax Area Code] = [Tax Area].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Tax Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Group]
ON
[Prepayment Inv. Line Buffer].[Tax Group Code] = [Tax Group].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Prepayment Inv. Line Buffer].[Location Code] = [Location].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Dimension Set Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension Set Entry]
ON
[Prepayment Inv. Line Buffer].[Dimension Set ID] = [Dimension Set Entry].[Dimension Set ID]
LEFT JOIN
[User] AS [User]
ON
[Prepayment Inv. Line Buffer].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Prepayment Inv. Line Buffer].[SystemModifiedBy] = [User].[User Security ID]
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|
| 1 | G/L Account No.  | G/L Account No. | Normal | Code | 20 | G_L Account No_ | nvarchar(40) |
| 2 | Line No.  | Line No. | Normal | Integer | 4 | Line No_ | int |
| 3 | Amount | Amount | Normal | Decimal | 12 | Amount | decimal(38,38) |
| 4 | Description | Description | Normal | Text | 100 | Description | nvarchar(200) |
| 5 | Gen. Bus. Posting Group | Gen. Bus. Posting Group | Normal | Code | 20 | Gen_ Bus_ Posting Group | nvarchar(40) |
| Key to join to the Gen. Business Posting Group table.SELECT
[Prepayment Inv. Line Buffer].[Gen_ Bus_ Posting Group]
,[Gen. Business Posting Group].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Gen_ Business Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Gen. Business Posting Group]
ON
[Prepayment Inv. Line Buffer].[Gen_ Bus_ Posting Group] = [Gen. Business Posting Group].[Code] |
| 6 | Gen. Prod. Posting Group | Gen. Prod. Posting Group | Normal | Code | 20 | Gen_ Prod_ Posting Group | nvarchar(40) |
| Key to join to the Gen. Product Posting Group table.SELECT
[Prepayment Inv. Line Buffer].[Gen_ Prod_ Posting Group]
,[Gen. Product Posting Group].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Gen_ Product Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Gen. Product Posting Group]
ON
[Prepayment Inv. Line Buffer].[Gen_ Prod_ Posting Group] = [Gen. Product Posting Group].[Code] |
| 7 | VAT Bus. Posting Group | VAT Bus. Posting Group | Normal | Code | 20 | VAT Bus_ Posting Group | nvarchar(40) |
| Key to join to the VAT Business Posting Group table.SELECT
[Prepayment Inv. Line Buffer].[VAT Bus_ Posting Group]
,[VAT Business Posting Group].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$VAT Business Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Business Posting Group]
ON
[Prepayment Inv. Line Buffer].[VAT Bus_ Posting Group] = [VAT Business Posting Group].[Code] |
| 8 | VAT Prod. Posting Group | VAT Prod. Posting Group | Normal | Code | 20 | VAT Prod_ Posting Group | nvarchar(40) |
| Key to join to the VAT Product Posting Group table.SELECT
[Prepayment Inv. Line Buffer].[VAT Prod_ Posting Group]
,[VAT Product Posting Group].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$VAT Product Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Product Posting Group]
ON
[Prepayment Inv. Line Buffer].[VAT Prod_ Posting Group] = [VAT Product Posting Group].[Code] |
| 9 | VAT Amount | Tax Amount | Normal | Decimal | 12 | VAT Amount | decimal(38,38) |
| 10 | VAT Calculation Type | VAT Calculation Type | Normal | Option | 4 | VAT Calculation Type | int |
| Available options are:
- “Normal VAT”
- “Reverse Charge VAT”
- “Full VAT”
- “Sales Tax”
|
| 11 | VAT Base Amount | Tax Base Amount | Normal | Decimal | 12 | VAT Base Amount | decimal(38,38) |
| 12 | Amount (ACY) | Amount (ACY) | Normal | Decimal | 12 | Amount (ACY) | decimal(38,38) |
| 13 | VAT Amount (ACY) | Tax Amount (ACY) | Normal | Decimal | 12 | VAT Amount (ACY) | decimal(38,38) |
| 14 | VAT Base Amount (ACY) | Tax Base Amount (ACY) | Normal | Decimal | 12 | VAT Base Amount (ACY) | decimal(38,38) |
| 15 | VAT Difference | Tax Difference | Normal | Decimal | 12 | VAT Difference | decimal(38,38) |
| 16 | VAT % | Tax % | Normal | Decimal | 12 | VAT _ | decimal(38,38) |
| 17 | VAT Identifier | Tax Identifier | Normal | Code | 20 | VAT Identifier | nvarchar(40) |
| 19 | Global Dimension 1 Code | Global Dimension 1 Code | Normal | Code | 20 | Global Dimension 1 Code | nvarchar(40) |
| Key to join to the Dimension table.SELECT
[Prepayment Inv. Line Buffer].[Global Dimension 1 Code]
,[Dimension].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
ON
[Prepayment Inv. Line Buffer].[Global Dimension 1 Code] = [Dimension].[Code] |
| 20 | Global Dimension 2 Code | Global Dimension 2 Code | Normal | Code | 20 | Global Dimension 2 Code | nvarchar(40) |
| Key to join to the Dimension table.SELECT
[Prepayment Inv. Line Buffer].[Global Dimension 2 Code]
,[Dimension].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
ON
[Prepayment Inv. Line Buffer].[Global Dimension 2 Code] = [Dimension].[Code] |
| 21 | Job No. | Job No. | Normal | Code | 20 | Job No_ | nvarchar(40) |
| Key to join to the Job table.SELECT
[Prepayment Inv. Line Buffer].[Job No_]
,[Job].[No_]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Job$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job]
ON
[Prepayment Inv. Line Buffer].[Job No_] = [Job].[No_] |
| 22 | Amount Incl. VAT | Amount Incl. Tax | Normal | Decimal | 12 | Amount Incl_ VAT | decimal(38,38) |
| 24 | Tax Area Code | Tax Area Code | Normal | Code | 20 | Tax Area Code | nvarchar(40) |
| Key to join to the Tax Area table.SELECT
[Prepayment Inv. Line Buffer].[Tax Area Code]
,[Tax Area].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Tax Area$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Area]
ON
[Prepayment Inv. Line Buffer].[Tax Area Code] = [Tax Area].[Code] |
| 25 | Tax Liable | Tax Liable | Normal | Boolean | 4 | Tax Liable | tinyint |
| 26 | Tax Group Code | Tax Group Code | Normal | Code | 20 | Tax Group Code | nvarchar(40) |
| Key to join to the Tax Group table.SELECT
[Prepayment Inv. Line Buffer].[Tax Group Code]
,[Tax Group].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Tax Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Group]
ON
[Prepayment Inv. Line Buffer].[Tax Group Code] = [Tax Group].[Code] |
| 27 | Invoice Rounding | Invoice Rounding | Normal | Boolean | 4 | Invoice Rounding | tinyint |
| 28 | Adjustment | Adjustment | Normal | Boolean | 4 | Adjustment | tinyint |
| 29 | VAT Base Before Pmt. Disc. | Tax Base Before Pmt. Disc. | Normal | Decimal | 12 | VAT Base Before Pmt_ Disc_ | decimal(38,38) |
| 30 | Orig. Pmt. Disc. Possible | Original Pmt. Disc. Possible | Normal | Decimal | 12 | Orig_ Pmt_ Disc_ Possible | decimal(38,38) |
| 31 | Location Code | Location Code | Normal | Code | 10 | Location Code | nvarchar(20) |
| Key to join to the Location table.SELECT
[Prepayment Inv. Line Buffer].[Location Code]
,[Location].[Code]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Prepayment Inv. Line Buffer].[Location Code] = [Location].[Code] |
| 480 | Dimension Set ID | Dimension Set ID | Normal | Integer | 4 | Dimension Set ID | int |
| Key to join to the Dimension Set Entry table.SELECT
[Prepayment Inv. Line Buffer].[Dimension Set ID]
,[Dimension Set Entry].[Dimension Set ID]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Dimension Set Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension Set Entry]
ON
[Prepayment Inv. Line Buffer].[Dimension Set ID] = [Dimension Set Entry].[Dimension Set ID] |
| 1001 | Job Task No. | Job Task No. | Normal | Code | 20 | Job Task No_ | nvarchar(40) |
| 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.SELECT
[Prepayment Inv. Line Buffer].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Prepayment Inv_ Line Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Prepayment Inv. Line Buffer]
LEFT JOIN
[User] AS [User]
ON
[Prepayment Inv. Line Buffer].[$systemCreatedBy] = [User].[User Security ID] |
| 2000000003 | SystemModifiedAt | Modified At | Normal | DateTime | 8 | $systemModifiedAt | datetime |
| 2000000004 | SystemModifiedBy | Modified By | Normal | GUID | 16 | $systemModifiedBy | uniqueidentifier |
