Item Journal Line – Dynamics 365 BC Table Reference

Dynamics 365 BC Table Reference Tables Company Tables IItem Journal Line

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

Table Type: Company

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

Table Number: 0

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 Item Journal Line can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

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

Show/hide an example select of all columns by name

SELECT

   [timestamp]
   ,[Journal Template Name]
   ,[Journal Batch Name]
   ,[Line No_]
   ,[Item No_]
   ,[Posting Date]
   ,[Entry Type]
   ,[Source No_]
   ,[Document No_]
   ,[Description]
   ,[Location Code]
   ,[Inventory Posting Group]
   ,[Source Posting Group]
   ,[Quantity]
   ,[Invoiced Quantity]
   ,[Unit Amount]
   ,[Unit Cost]
   ,[Amount]
   ,[Discount Amount]
   ,[Salespers__Purch_ Code]
   ,[Source Code]
   ,[Applies-to Entry]
   ,[Item Shpt_ Entry No_]
   ,[Shortcut Dimension 1 Code]
   ,[Shortcut Dimension 2 Code]
   ,[Indirect Cost _]
   ,[Source Type]
   ,[Shpt_ Method Code]
   ,[Reason Code]
   ,[Recurring Method]
   ,[Expiration Date]
   ,[Recurring Frequency]
   ,[Drop Shipment]
   ,[Transaction Type]
   ,[Transport Method]
   ,[Country_Region Code]
   ,[New Location Code]
   ,[New Shortcut Dimension 1 Code]
   ,[New Shortcut Dimension 2 Code]
   ,[Qty_ (Calculated)]
   ,[Qty_ (Phys_ Inventory)]
   ,[Last Item Ledger Entry No_]
   ,[Phys_ Inventory]
   ,[Gen_ Bus_ Posting Group]
   ,[Gen_ Prod_ Posting Group]
   ,[Entry_Exit Point]
   ,[Document Date]
   ,[External Document No_]
   ,[Area]
   ,[Transaction Specification]
   ,[Posting No_ Series]
   ,[Unit Cost (ACY)]
   ,[Source Currency Code]
   ,[Document Type]
   ,[Document Line No_]
   ,[Order Type]
   ,[Order No_]
   ,[Order Line No_]
   ,[Dimension Set ID]
   ,[New Dimension Set ID]
   ,[Assemble to Order]
   ,[Job No_]
   ,[Job Task No_]
   ,[Job Purchase]
   ,[Job Contract Entry No_]
   ,[Variant Code]
   ,[Bin Code]
   ,[Qty_ per Unit of Measure]
   ,[New Bin Code]
   ,[Unit of Measure Code]
   ,[Derived from Blanket Order]
   ,[Qty_ Rounding Precision]
   ,[Qty_ Rounding Precision (Base)]
   ,[Quantity (Base)]
   ,[Invoiced Qty_ (Base)]
   ,[Level]
   ,[Flushing Method]
   ,[Changed by User]
   ,[Cross-Reference No_]
   ,[Originally Ordered No_]
   ,[Originally Ordered Var_ Code]
   ,[Out-of-Stock Substitution]
   ,[Item Category Code]
   ,[Nonstock]
   ,[Purchasing Code]
   ,[Product Group Code]
   ,[Item Reference No_]
   ,[Planned Delivery Date]
   ,[Order Date]
   ,[Value Entry Type]
   ,[Item Charge No_]
   ,[Inventory Value (Calculated)]
   ,[Inventory Value (Revalued)]
   ,[Variance Type]
   ,[Inventory Value Per]
   ,[Partial Revaluation]
   ,[Applies-from Entry]
   ,[Invoice No_]
   ,[Unit Cost (Calculated)]
   ,[Unit Cost (Revalued)]
   ,[Applied Amount]
   ,[Update Standard Cost]
   ,[Amount (ACY)]
   ,[Correction]
   ,[Adjustment]
   ,[Applies-to Value Entry]
   ,[Invoice-to Source No_]
   ,[Type]
   ,[No_]
   ,[Operation No_]
   ,[Work Center No_]
   ,[Setup Time]
   ,[Run Time]
   ,[Stop Time]
   ,[Output Quantity]
   ,[Scrap Quantity]
   ,[Concurrent Capacity]
   ,[Setup Time (Base)]
   ,[Run Time (Base)]
   ,[Stop Time (Base)]
   ,[Output Quantity (Base)]
   ,[Scrap Quantity (Base)]
   ,[Cap_ Unit of Measure Code]
   ,[Qty_ per Cap_ Unit of Measure]
   ,[Starting Time]
   ,[Ending Time]
   ,[Routing No_]
   ,[Routing Reference No_]
   ,[Prod_ Order Comp_ Line No_]
   ,[Finished]
   ,[Unit Cost Calculation]
   ,[Subcontracting]
   ,[Stop Code]
   ,[Scrap Code]
   ,[Work Center Group Code]
   ,[Work Shift Code]
   ,[Serial No_]
   ,[Lot No_]
   ,[Warranty Date]
   ,[New Serial No_]
   ,[New Lot No_]
   ,[New Item Expiration Date]
   ,[Item Expiration Date]
   ,[Package No_]
   ,[New Package No_]
   ,[Return Reason Code]
   ,[Price Calculation Method]
   ,[Warehouse Adjustment]
   ,[Direct Transfer]
   ,[Phys Invt Counting Period Code]
   ,[Phys Invt Counting Period Type]
   ,[Shipment Method Code]
   ,[Overhead Rate]
   ,[Single-Level Material Cost]
   ,[Single-Level Capacity Cost]
   ,[Single-Level Subcontrd_ Cost]
   ,[Single-Level Cap_ Ovhd Cost]
   ,[Single-Level Mfg_ Ovhd Cost]
   ,[Rolled-up Material Cost]
   ,[Rolled-up Capacity Cost]
   ,[Rolled-up Subcontracted Cost]
   ,[Rolled-up Mfg_ Ovhd Cost]
   ,[Rolled-up Cap_ Overhead Cost]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]

FROM

   [CRONUS UK Ltd_$Item Journal 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_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line]
LEFT JOIN
   [CRONUS UK Ltd_$Item$c526b3e9-b8ca-4683-81ba-fcd5f6b1472a] AS [Item]
      ON
         [Item Journal Line].[Item No_] = [Item].[No_]
LEFT JOIN
   [CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
      ON
         [Item Journal Line].[Location Code] = [Location].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Country_Region$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Country_Region]
      ON
         [Item Journal Line].[Country_Region Code] = [Country_Region].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Job$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job]
      ON
         [Item Journal Line].[Job No_] = [Job].[Job No_]
LEFT JOIN
   [CRONUS UK Ltd_$Job Task$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job Task]
      ON
         [Item Journal Line].[Job Task No_] = [Job Task].[Job Task No_]
LEFT JOIN
   [CRONUS UK Ltd_$Unit of Measure$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Unit of Measure]
      ON
         [Item Journal Line].[Unit of Measure Code] = [Unit of Measure].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Item Category$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Category]
      ON
         [Item Journal Line].[Item Category Code] = [Item Category].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Product Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Product Group]
      ON
         [Item Journal Line].[Product Group Code] = [Product Group].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Shipment Method$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Shipment Method]
      ON
         [Item Journal Line].[Shipment Method Code] = [Shipment Method].[Code]

Show/hide columns in Item Journal Line table

Column ID	Column Name	Data Type
1	timestamp	timestamp
2	Journal Template Name	nvarchar(20)
3	Journal Batch Name	nvarchar(20)
4	Line No_	int
5	Item No_	nvarchar(40)
	Key to join to the Item table. Show/hide example query SELECT [Item Journal Line].[Item No_] ,[Item].[No_] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Item$c526b3e9-b8ca-4683-81ba-fcd5f6b1472a] AS [Item] ON [Item Journal Line].[Item No_] = [Item].[No_]
6	Posting Date	datetime
7	Entry Type	int
8	Source No_	nvarchar(40)
9	Document No_	nvarchar(40)
10	Description	nvarchar(200)
11	Location Code	nvarchar(20)
	Key to join to the Location table. Show/hide example query SELECT [Item Journal Line].[Location Code] ,[Location].[Code] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location] ON [Item Journal Line].[Location Code] = [Location].[Code]
12	Inventory Posting Group	nvarchar(40)
13	Source Posting Group	nvarchar(40)
14	Quantity	decimal(38,38)
15	Invoiced Quantity	decimal(38,38)
16	Unit Amount	decimal(38,38)
17	Unit Cost	decimal(38,38)
18	Amount	decimal(38,38)
19	Discount Amount	decimal(38,38)
20	Salespers__Purch_ Code	nvarchar(40)
21	Source Code	nvarchar(20)
22	Applies-to Entry	int
23	Item Shpt_ Entry No_	int
24	Shortcut Dimension 1 Code	nvarchar(40)
25	Shortcut Dimension 2 Code	nvarchar(40)
26	Indirect Cost _	decimal(38,38)
27	Source Type	int
28	Shpt_ Method Code	nvarchar(20)
29	Reason Code	nvarchar(20)
30	Recurring Method	int
31	Expiration Date	datetime
32	Recurring Frequency	varchar(32)
33	Drop Shipment	tinyint
34	Transaction Type	nvarchar(20)
35	Transport Method	nvarchar(20)
36	Country_Region Code	nvarchar(20)
	Key to join to the Country_Region table. Show/hide example query SELECT [Item Journal Line].[Country_Region Code] ,[Country_Region].[Code] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Country_Region$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Country_Region] ON [Item Journal Line].[Country_Region Code] = [Country_Region].[Code]
37	New Location Code	nvarchar(20)
38	New Shortcut Dimension 1 Code	nvarchar(40)
39	New Shortcut Dimension 2 Code	nvarchar(40)
40	Qty_ (Calculated)	decimal(38,38)
41	Qty_ (Phys_ Inventory)	decimal(38,38)
42	Last Item Ledger Entry No_	int
43	Phys_ Inventory	tinyint
44	Gen_ Bus_ Posting Group	nvarchar(40)
45	Gen_ Prod_ Posting Group	nvarchar(40)
46	Entry_Exit Point	nvarchar(20)
47	Document Date	datetime
48	External Document No_	nvarchar(70)
49	Area	nvarchar(20)
50	Transaction Specification	nvarchar(20)
51	Posting No_ Series	nvarchar(40)
52	Unit Cost (ACY)	decimal(38,38)
53	Source Currency Code	nvarchar(20)
54	Document Type	int
55	Document Line No_	int
56	Order Type	int
57	Order No_	nvarchar(40)
58	Order Line No_	int
59	Dimension Set ID	int
60	New Dimension Set ID	int
61	Assemble to Order	tinyint
62	Job No_	nvarchar(40)
	Key to join to the Job table. Show/hide example query SELECT [Item Journal Line].[Job No_] ,[Job].[Job No_] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Job$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job] ON [Item Journal Line].[Job No_] = [Job].[Job No_]
63	Job Task No_	nvarchar(40)
	Key to join to the Job Task table. Show/hide example query SELECT [Item Journal Line].[Job Task No_] ,[Job Task].[Job Task No_] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Job Task$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job Task] ON [Item Journal Line].[Job Task No_] = [Job Task].[Job Task No_]
64	Job Purchase	tinyint
65	Job Contract Entry No_	int
66	Variant Code	nvarchar(20)
67	Bin Code	nvarchar(40)
68	Qty_ per Unit of Measure	decimal(38,38)
69	New Bin Code	nvarchar(40)
70	Unit of Measure Code	nvarchar(20)
	Key to join to the Unit of Measure table. Show/hide example query SELECT [Item Journal Line].[Unit of Measure Code] ,[Unit of Measure].[Code] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Unit of Measure$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Unit of Measure] ON [Item Journal Line].[Unit of Measure Code] = [Unit of Measure].[Code]
71	Derived from Blanket Order	tinyint
72	Qty_ Rounding Precision	decimal(38,38)
73	Qty_ Rounding Precision (Base)	decimal(38,38)
74	Quantity (Base)	decimal(38,38)
75	Invoiced Qty_ (Base)	decimal(38,38)
76	Level	int
77	Flushing Method	int
78	Changed by User	tinyint
79	Cross-Reference No_	nvarchar(40)
80	Originally Ordered No_	nvarchar(40)
81	Originally Ordered Var_ Code	nvarchar(20)
82	Out-of-Stock Substitution	tinyint
83	Item Category Code	nvarchar(40)
	Key to join to the Item Category table. Show/hide example query SELECT [Item Journal Line].[Item Category Code] ,[Item Category].[Code] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Item Category$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Category] ON [Item Journal Line].[Item Category Code] = [Item Category].[Code]
84	Nonstock	tinyint
85	Purchasing Code	nvarchar(20)
86	Product Group Code	nvarchar(20)
	Key to join to the Product Group table. Show/hide example query SELECT [Item Journal Line].[Product Group Code] ,[Product Group].[Code] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Product Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Product Group] ON [Item Journal Line].[Product Group Code] = [Product Group].[Code]
87	Item Reference No_	nvarchar(100)
88	Planned Delivery Date	datetime
89	Order Date	datetime
90	Value Entry Type	int
91	Item Charge No_	nvarchar(40)
92	Inventory Value (Calculated)	decimal(38,38)
93	Inventory Value (Revalued)	decimal(38,38)
94	Variance Type	int
95	Inventory Value Per	int
96	Partial Revaluation	tinyint
97	Applies-from Entry	int
98	Invoice No_	nvarchar(40)
99	Unit Cost (Calculated)	decimal(38,38)
100	Unit Cost (Revalued)	decimal(38,38)
101	Applied Amount	decimal(38,38)
102	Update Standard Cost	tinyint
103	Amount (ACY)	decimal(38,38)
104	Correction	tinyint
105	Adjustment	tinyint
106	Applies-to Value Entry	int
107	Invoice-to Source No_	nvarchar(40)
108	Type	int
109	No_	nvarchar(40)
110	Operation No_	nvarchar(20)
111	Work Center No_	nvarchar(40)
112	Setup Time	decimal(38,38)
113	Run Time	decimal(38,38)
114	Stop Time	decimal(38,38)
115	Output Quantity	decimal(38,38)
116	Scrap Quantity	decimal(38,38)
117	Concurrent Capacity	decimal(38,38)
118	Setup Time (Base)	decimal(38,38)
119	Run Time (Base)	decimal(38,38)
120	Stop Time (Base)	decimal(38,38)
121	Output Quantity (Base)	decimal(38,38)
122	Scrap Quantity (Base)	decimal(38,38)
123	Cap_ Unit of Measure Code	nvarchar(20)
124	Qty_ per Cap_ Unit of Measure	decimal(38,38)
125	Starting Time	datetime
126	Ending Time	datetime
127	Routing No_	nvarchar(40)
128	Routing Reference No_	int
129	Prod_ Order Comp_ Line No_	int
130	Finished	tinyint
131	Unit Cost Calculation	int
132	Subcontracting	tinyint
133	Stop Code	nvarchar(20)
134	Scrap Code	nvarchar(20)
135	Work Center Group Code	nvarchar(20)
136	Work Shift Code	nvarchar(20)
137	Serial No_	nvarchar(100)
138	Lot No_	nvarchar(100)
139	Warranty Date	datetime
140	New Serial No_	nvarchar(100)
141	New Lot No_	nvarchar(100)
142	New Item Expiration Date	datetime
143	Item Expiration Date	datetime
144	Package No_	nvarchar(100)
145	New Package No_	nvarchar(100)
146	Return Reason Code	nvarchar(20)
147	Price Calculation Method	int
148	Warehouse Adjustment	tinyint
149	Direct Transfer	tinyint
150	Phys Invt Counting Period Code	nvarchar(20)
151	Phys Invt Counting Period Type	int
152	Shipment Method Code	nvarchar(20)
	Key to join to the Shipment Method table. Show/hide example query SELECT [Item Journal Line].[Shipment Method Code] ,[Shipment Method].[Code] FROM [CRONUS UK Ltd_$Item Journal Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Journal Line] LEFT JOIN [CRONUS UK Ltd_$Shipment Method$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Shipment Method] ON [Item Journal Line].[Shipment Method Code] = [Shipment Method].[Code]
153	Overhead Rate	decimal(38,38)
154	Single-Level Material Cost	decimal(38,38)
155	Single-Level Capacity Cost	decimal(38,38)
156	Single-Level Subcontrd_ Cost	decimal(38,38)
157	Single-Level Cap_ Ovhd Cost	decimal(38,38)
158	Single-Level Mfg_ Ovhd Cost	decimal(38,38)
159	Rolled-up Material Cost	decimal(38,38)
160	Rolled-up Capacity Cost	decimal(38,38)
161	Rolled-up Subcontracted Cost	decimal(38,38)
162	Rolled-up Mfg_ Ovhd Cost	decimal(38,38)
163	Rolled-up Cap_ Overhead Cost	decimal(38,38)
164	$systemId	uniqueidentifier
165	$systemCreatedAt	datetime
166	$systemCreatedBy	uniqueidentifier
167	$systemModifiedAt	datetime
168	$systemModifiedBy	uniqueidentifier

Show/hide tables related from Item Journal Line table

Country_Region Item Item Category Job Job Task Location Product Group Shipment Method Unit of Measure

Dynamics 365 BC Table Reference Tables Company Tables IItem Journal Line

Leave a Reply Cancel reply