Price Worksheet Line – Dynamics 365 BC Table Reference

Dynamics 365 BC Table Reference Tables Company Tables PPrice Worksheet Line

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

Table Type: Company

Table Name: Price Worksheet Line
Database Table Name: CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972

Table Number: 7022

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 Price Worksheet Line can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

   [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns by name

SELECT

   [Price List Code]
   ,[Line No_]
   ,[Source Type]
   ,[Source No_]
   ,[Parent Source No_]
   ,[Source ID]
   ,[Asset Type]
   ,[Asset No_]
   ,[Variant Code]
   ,[Currency Code]
   ,[Work Type Code]
   ,[Starting Date]
   ,[Ending Date]
   ,[Minimum Quantity]
   ,[Unit of Measure Code]
   ,[Amount Type]
   ,[Unit Price]
   ,[Cost Factor]
   ,[Unit Cost]
   ,[Line Discount _]
   ,[Allow Line Disc_]
   ,[Allow Invoice Disc_]
   ,[Price Includes VAT]
   ,[VAT Bus_ Posting Gr_ (Price)]
   ,[VAT Prod_ Posting Group]
   ,[Asset ID]
   ,[Line Amount]
   ,[Price Type]
   ,[Description]
   ,[Status]
   ,[Direct Unit Cost]
   ,[Source Group]
   ,[Product No_]
   ,[Assign-to No_]
   ,[Assign-to Parent No_]
   ,[Variant Code Lookup]
   ,[Unit of Measure Code Lookup]
   ,[Existing Line]
   ,[Existing Unit Price]
   ,[Existing Direct Unit Cost]
   ,[Existing Unit Cost]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]

FROM

   [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972]

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

SELECT
   *
FROM
   [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line]
LEFT JOIN
   [CRONUS UK Ltd_$Price List Header$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price List Header]
      ON
         [Price Worksheet Line].[Price List Code] = [Price List Header].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
      ON
         [Price Worksheet Line].[Variant Code] = [Item Variant].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Currency$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Currency]
      ON
         [Price Worksheet Line].[Currency Code] = [Currency].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Work Type$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Work Type]
      ON
         [Price Worksheet Line].[Work Type Code] = [Work Type].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Inventory Period$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Inventory Period]
      ON
         [Price Worksheet Line].[Ending Date] = [Inventory Period].[Ending Date]
LEFT JOIN
   [User] AS [User]
      ON
         [Price Worksheet Line].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [Price Worksheet Line].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in Price Worksheet Line table

Field No.	Field Name	Field Caption	Class	Type	Length	SQL Column Name	SQL DataType
1	Price List Code	Price List Code	Normal	Code	20	Price List Code	nvarchar(40)
	Key to join to the Price List Header table. Show/hide example query SELECT [Price Worksheet Line].[Price List Code] ,[Price List Header].[Code] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [CRONUS UK Ltd_$Price List Header$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price List Header] ON [Price Worksheet Line].[Price List Code] = [Price List Header].[Code]
2	Line No.	Line No.	Normal	Integer	4	Line No_	int
3	Source Type	Assign-to Type	Normal	Option	4	Source Type	int
	Available options are: “All” “All Customers” “Customer” “Customer Price Group” “Customer Disc. Group” “All Vendors” “Vendor” “All Jobs” “Job” “Job Task” “Campaign” “Contact”
4	Source No.	Assign-to No.	Normal	Code	20	Source No_	nvarchar(40)
5	Parent Source No.	Assign-to Parent No.	Normal	Code	20	Parent Source No_	nvarchar(40)
6	Source ID	Assign-to ID	Normal	GUID	16	Source ID	uniqueidentifier
7	Asset Type	Product Type	Normal	Option	4	Asset Type	int
	Available options are: ” “ “Item” “Item Discount Group” “Resource” “Resource Group” “Service Cost” “G/L Account”
8	Asset No.	Product No.	Normal	Code	20	Asset No_	nvarchar(40)
9	Variant Code	Variant Code	Normal	Code	10	Variant Code	nvarchar(20)
	Key to join to the Item Variant table. Show/hide example query SELECT [Price Worksheet Line].[Variant Code] ,[Item Variant].[Code] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant] ON [Price Worksheet Line].[Variant Code] = [Item Variant].[Code]
10	Currency Code	Currency Code	Normal	Code	10	Currency Code	nvarchar(20)
	Key to join to the Currency table. Show/hide example query SELECT [Price Worksheet Line].[Currency Code] ,[Currency].[Code] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [CRONUS UK Ltd_$Currency$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Currency] ON [Price Worksheet Line].[Currency Code] = [Currency].[Code]
11	Work Type Code	Work Type Code	Normal	Code	10	Work Type Code	nvarchar(20)
	Key to join to the Work Type table. Show/hide example query SELECT [Price Worksheet Line].[Work Type Code] ,[Work Type].[Code] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [CRONUS UK Ltd_$Work Type$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Work Type] ON [Price Worksheet Line].[Work Type Code] = [Work Type].[Code]
12	Starting Date	Starting Date	Normal	Date	4	Starting Date	datetime
13	Ending Date	Ending Date	Normal	Date	4	Ending Date	datetime
	Key to join to the Inventory Period table. Show/hide example query SELECT [Price Worksheet Line].[Ending Date] ,[Inventory Period].[Ending Date] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [CRONUS UK Ltd_$Inventory Period$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Inventory Period] ON [Price Worksheet Line].[Ending Date] = [Inventory Period].[Ending Date]
14	Minimum Quantity	Minimum Quantity	Normal	Decimal	12	Minimum Quantity	decimal(38,38)
15	Unit of Measure Code	Unit of Measure Code	Normal	Code	10	Unit of Measure Code	nvarchar(20)
16	Amount Type	Defines	Normal	Option	4	Amount Type	int
	Available options are: “Any” “Price” “Cost” “Discount”
17	Unit Price	Unit Price	Normal	Decimal	12	Unit Price	decimal(38,38)
18	Cost Factor	Cost Factor	Normal	Decimal	12	Cost Factor	decimal(38,38)
19	Unit Cost	Unit Cost	Normal	Decimal	12	Unit Cost	decimal(38,38)
20	Line Discount %	Line Discount %	Normal	Decimal	12	Line Discount _	decimal(38,38)
21	Allow Line Disc.	Allow Line Disc.	Normal	Boolean	4	Allow Line Disc_	tinyint
22	Allow Invoice Disc.	Allow Invoice Disc.	Normal	Boolean	4	Allow Invoice Disc_	tinyint
23	Price Includes VAT	Price Includes Tax	Normal	Boolean	4	Price Includes VAT	tinyint
24	VAT Bus. Posting Gr. (Price)	Tax Bus. Posting Gr. (Price)	Normal	Code	20	VAT Bus_ Posting Gr_ (Price)	nvarchar(40)
	Key to join to the VAT Business Posting Group table. Show/hide example query SELECT [Price Worksheet Line].[VAT Bus_ Posting Gr_ (Price)] ,[VAT Business Posting Group].[Code] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [CRONUS UK Ltd_$VAT Business Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Business Posting Group] ON [Price Worksheet Line].[VAT Bus_ Posting Gr_ (Price)] = [VAT Business Posting Group].[Code]
25	VAT Prod. Posting Group	Tax Prod. Posting Group	Normal	Code	20	VAT Prod_ Posting Group	nvarchar(40)
	Key to join to the VAT Product Posting Group table. Show/hide example query SELECT [Price Worksheet Line].[VAT Prod_ Posting Group] ,[VAT Product Posting Group].[Code] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [CRONUS UK Ltd_$VAT Product Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Product Posting Group] ON [Price Worksheet Line].[VAT Prod_ Posting Group] = [VAT Product Posting Group].[Code]
26	Asset ID	Asset ID	Normal	GUID	16	Asset ID	uniqueidentifier
27	Line Amount	Line Amount	Normal	Decimal	12	Line Amount	decimal(38,38)
28	Price Type	Price Type	Normal	Option	4	Price Type	int
	Available options are: “Any” “Sale” “Purchase”
29	Description	Description	Normal	Text	100	Description	nvarchar(200)
30	Status	Status	Normal	Option	4	Status	int
	Available options are: “Draft” “Active” “Inactive”
31	Direct Unit Cost	Direct Unit Cost	Normal	Decimal	12	Direct Unit Cost	decimal(38,38)
32	Source Group	Source Group	Normal	Option	4	Source Group	int
	Available options are: “All” “Customer” “Vendor” “Job”
33	Product No.	Product No.	Normal	Code	20	Product No_	nvarchar(40)
34	Assign-to No.	Assign-to No.	Normal	Code	20	Assign-to No_	nvarchar(40)
35	Assign-to Parent No.	Assign-to Parent No.	Normal	Code	20	Assign-to Parent No_	nvarchar(40)
36	Variant Code Lookup	Variant Code	Normal	Code	10	Variant Code Lookup	nvarchar(20)
37	Unit of Measure Code Lookup	Unit of Measure Code	Normal	Code	10	Unit of Measure Code Lookup	nvarchar(20)
100	Existing Line	Existing Line	Normal	Boolean	4	Existing Line	tinyint
101	Existing Unit Price	Existing Unit Price	Normal	Decimal	12	Existing Unit Price	decimal(38,38)
102	Existing Direct Unit Cost	Existing Direct Unit Cost	Normal	Decimal	12	Existing Direct Unit Cost	decimal(38,38)
103	Existing Unit Cost	Existing Unit Cost	Normal	Decimal	12	Existing Unit Cost	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. Show/hide example query SELECT [Price Worksheet Line].[$systemCreatedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$Price Worksheet Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Price Worksheet Line] LEFT JOIN [User] AS [User] ON [Price Worksheet Line].[$systemCreatedBy] = [User].[User Security ID]
2000000003	SystemModifiedAt	Modified At	Normal	DateTime	8	$systemModifiedAt	datetime
2000000004	SystemModifiedBy	Modified By	Normal	GUID	16	$systemModifiedBy	uniqueidentifier

Show/hide tables related from Price Worksheet Line table

Dynamics 365 BC Table Reference Tables Company Tables PPrice Worksheet Line

Leave a Reply Cancel reply