Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/5847
Table Type: Company
Table Name: Average Cost Calc. Overview
Database Table Name: CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972
Table Number: 5847Due 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 Average Cost Calc. Overview can be queried.
SELECT
*
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972]
SELECT
[Entry No_]
,[Type]
,[Valuation Date]
,[Item No_]
,[Location Code]
,[Variant Code]
,[Cost is Adjusted]
,[Attached to Entry No_]
,[Attached to Valuation Date]
,[Level]
,[Item Ledger Entry No_]
,[Posting Date]
,[Entry Type]
,[Document Type]
,[Document No_]
,[Document Line No_]
,[Description]
,[Quantity]
,[Applied Quantity]
,[Cost Amount (Expected)]
,[Cost Amount (Actual)]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972]
SELECT
*
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Average Cost Calc. Overview]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Average Cost Calc. Overview].[Location Code] = [Location].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
ON
[Average Cost Calc. Overview].[Variant Code] = [Item Variant].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Sales Header Archive$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Sales Header Archive]
ON
[Average Cost Calc. Overview].[Document Type] = [Sales Header Archive].[Document Type]
AND
[Average Cost Calc. Overview].[Document No_] = [Sales Header Archive].[No_]
LEFT JOIN
[CRONUS UK Ltd_$Sales Header$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Sales Header]
ON
[Average Cost Calc. Overview].[Document Type] = [Sales Header].[Document Type]
AND
[Average Cost Calc. Overview].[Document No_] = [Sales Header].[No_]
LEFT JOIN
[User] AS [User]
ON
[Average Cost Calc. Overview].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Average Cost Calc. Overview].[SystemModifiedBy] = [User].[User Security ID]
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|
| 1 | Entry No.  | Entry No. | Normal | Integer | 4 | Entry No_ | int |
| 2 | Type  | Type | Normal | Option | 4 | Type | int |
| Available options are:
- “Closing Entry”
- “Increase”
- “Applied Increase”
- “Applied Decrease”
- “Decrease”
- “Revaluation”
|
| 3 | Valuation Date | Valuation Date | Normal | Date | 4 | Valuation Date | datetime |
| 4 | Item No. | Item No. | Normal | Code | 20 | Item No_ | nvarchar(40) |
| Key to join to the Item table.SELECT
[Average Cost Calc. Overview].[Item No_]
,[Item].[No_]
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Average Cost Calc. Overview]
LEFT JOIN
[CRONUS UK Ltd_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Item]
ON
[Average Cost Calc. Overview].[Item No_] = [Item].[No_] |
| 5 | Location Code | Location Code | Normal | Code | 10 | Location Code | nvarchar(20) |
| Key to join to the Location table.SELECT
[Average Cost Calc. Overview].[Location Code]
,[Location].[Code]
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Average Cost Calc. Overview]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Average Cost Calc. Overview].[Location Code] = [Location].[Code] |
| 6 | Variant Code | Variant Code | Normal | Code | 10 | Variant Code | nvarchar(20) |
| Key to join to the Item Variant table.SELECT
[Average Cost Calc. Overview].[Variant Code]
,[Item Variant].[Code]
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Average Cost Calc. Overview]
LEFT JOIN
[CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
ON
[Average Cost Calc. Overview].[Variant Code] = [Item Variant].[Code] |
| 7 | Cost is Adjusted | Cost is Adjusted | Normal | Boolean | 4 | Cost is Adjusted | tinyint |
| 11 | Attached to Entry No. | Attached to Entry No. | Normal | Integer | 4 | Attached to Entry No_ | int |
| Key to join to the Item Ledger Entry table.SELECT
[Average Cost Calc. Overview].[Attached to Entry No_]
,[Item Ledger Entry].[Entry No_]
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Average Cost Calc. Overview]
LEFT JOIN
[CRONUS UK Ltd_$Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Ledger Entry]
ON
[Average Cost Calc. Overview].[Attached to Entry No_] = [Item Ledger Entry].[Entry No_] |
| 12 | Attached to Valuation Date | Attached to Valuation Date | Normal | Date | 4 | Attached to Valuation Date | datetime |
| 13 | Level | Level | Normal | Integer | 4 | Level | int |
| 21 | Item Ledger Entry No. | Item Ledger Entry No. | Normal | Integer | 4 | Item Ledger Entry No_ | int |
| Key to join to the Item Ledger Entry table.SELECT
[Average Cost Calc. Overview].[Item Ledger Entry No_]
,[Item Ledger Entry].[Entry No_]
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Average Cost Calc. Overview]
LEFT JOIN
[CRONUS UK Ltd_$Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Ledger Entry]
ON
[Average Cost Calc. Overview].[Item Ledger Entry No_] = [Item Ledger Entry].[Entry No_] |
| 22 | Posting Date | Posting Date | Normal | Date | 4 | Posting Date | datetime |
| 23 | Entry Type | Entry Type | Normal | Option | 4 | Entry Type | int |
| Available options are:
- “Purchase”
- “Sale”
- “Positive Adjmt.”
- “Negative Adjmt.”
- “Transfer”
- “Consumption”
- “Output”
- ” “
- “Assembly Consumption”
- “Assembly Output”
|
| 24 | Document Type | Document Type | Normal | Option | 4 | Document Type | int |
| Available options are:
- ” “
- “Sales Shipment”
- “Sales Invoice”
- “Sales Return Receipt”
- “Sales Credit Memo”
- “Purchase Receipt”
- “Purchase Invoice”
- “Purchase Return Shipment”
- “Purchase Credit Memo”
- “Transfer Shipment”
- “Transfer Receipt”
- “Service Shipment”
- “Service Invoice”
- “Service Credit Memo”
|
| 25 | Document No. | Document No. | Normal | Code | 20 | Document No_ | nvarchar(40) |
| 26 | Document Line No. | Document Line No. | Normal | Integer | 4 | Document Line No_ | int |
| 27 | Description | Description | Normal | Text | 100 | Description | nvarchar(200) |
| 31 | Quantity | Quantity | Normal | Decimal | 12 | Quantity | decimal(38,38) |
| 32 | Applied Quantity | Applied Quantity | Normal | Integer | 4 | Applied Quantity | int |
| 33 | Cost Amount (Expected) | Cost Amount (Expected) | Normal | Decimal | 12 | Cost Amount (Expected) | decimal(38,38) |
| 34 | Cost Amount (Actual) | Cost Amount (Actual) | Normal | Decimal | 12 | Cost Amount (Actual) | decimal(38,38) |
| 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
[Average Cost Calc. Overview].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Average Cost Calc_ Overview$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Average Cost Calc. Overview]
LEFT JOIN
[User] AS [User]
ON
[Average Cost Calc. Overview].[$systemCreatedBy] = [User].[User Security ID] |
| 2000000003 | SystemModifiedAt | Modified At | Normal | DateTime | 8 | $systemModifiedAt | datetime |
| 2000000004 | SystemModifiedBy | Modified By | Normal | GUID | 16 | $systemModifiedBy | uniqueidentifier |
