Compressing Warehouse Entry
15th May 2012
There have been many topics on compression/deleting subledger/ ledger in NAV. The table that I would like to talk about is the Warehouse Entry. If a location is setup with bins or zones, you can see entries recording every activity that has been performed for an item/bin/zone/unit of measure/Lot/Serial in warehouse entry. The table assigns consecutive numbers to all entries, and the entries appear by entry number order. The activities include every removal and placement of items in the bins and every adjustment registered to the bins. Dynamics NAV creates the warehouse entries whenever you post or register a warehouse document or journal. As you can already guess, this table can quickly grow very large as users register transactions. Standard NAV provides a method to compress warehouse entries. This process is running in CAL is very slow and time consuming process. I’ve built the following solution that deletes all the warehouse entries and recreates them with a SQL script and a NAV processing report.
The process I followed is as follows.
In Object Designer, find Warehouse Entry table design and saved it as a new table. I called it “Warehouse Entry Compressed” with Table ID 50085. The purpose is that if you have done some customization and added some custom fields they all exist in the new table.
Afterwards run the SQL script that will populate this new table. If you have custom fields, you will need to include them in the SQL script.
INSERT INTO [dbo].[Cronus$Warehouse Entry Compressed]
([Entry No_]
,[Journal Batch Name]
,[Line No_]
,[Registering Date]
,[Location Code]
,[Zone Code]
,[Bin Code]
,[Description]
,[Item No_]
,[Quantity]
,[Qty_ (Base)]
,[Source Type]
,[Source Subtype]
,[Source No_]
,[Source Line No_]
,[Source Subline No_]
,[Source Document]
,[Source Code]
,[Reason Code]
,[No_ Series]
,[Bin Type Code]
,[Cubage]
,[Weight]
,[Journal Template Name]
,[Whse_ Document No_]
,[Whse_ Document Type]
,[Whse_ Document Line No_]
,[Entry Type]
,[Reference Document]
,[Reference No_]
,[User ID]
,[Variant Code]
,[Qty_ per Unit of Measure]
,[Unit of Measure Code]
,[Serial No_]
,[Lot No_]
,[Warranty Date]
,[Expiration Date]
,[Phys Invt Counting Period Code]
,[Phys Invt Counting Period Type]
)
Select
[Entry No_]
,[Journal Batch Name]
,[Line No_]
,[Registering Date]
,LocationCode
,[Zone Code]
,BinCode
,[Description]
,ItemNo
,SQuantity
,QtyBase
,[Source Type]
,[Source Subtype]
,[Source No_]
,[Source Line No_]
,[Source Subline No_]
,[Source Document]
,[Source Code]
,[Reason Code]
,[No_ Series]
,[Bin Type Code]
,[Cubage]
,[Weight]
,[Journal Template Name]
,[Whse_ Document No_]
,[Whse_ Document Type]
,[Whse_ Document Line No_]
,[Entry Type]
,[Reference Document]
,[Reference No_]
,[User ID]
,[Variant Code]
,[Qty_ per Unit of Measure]
,UOM
,SerialNo
,LotNo
,[Warranty Date]
,[Expiration Date]
,[Phys Invt Counting Period Code]
,[Phys Invt Counting Period Type]
From
(
Select *
From
(Select [Item No_] as ItemNo,[Location Code] as LocationCode,Sum([Quantity]) as SQuantity, SUM([Qty_ (Base)]) as QtyBase,
[Bin Code] as BinCode,[Lot No_] as LotNo,[Serial No_] as SerialNo, [Unit of Measure Code] as UOM,
(SELECT Top 1 [Entry No_]
FROM [dbo].[Cronus$Warehouse Entry] as WEntry
where WE.[Item No_] = WEntry.[Item No_] and
WE.[Bin Code]= WEntry.[Bin Code] and
WE.[Lot No_]= WEntry.[Lot No_] and
WE.[Location Code] = WEntry.[Location Code] and
WE.[Unit of Measure Code] = WEntry.[Unit of Measure Code] and
WE.[Serial No_] = WEntry.[Serial No_]
order by [Entry No_] desc) AS EntryNo
from [dbo].[Cronus$Warehouse Entry] as WE
group by [Item No_],[Location Code],[Bin Code],[Lot No_],[Serial No_],[Unit of Measure Code]
) as Summary
where QtyBase 0) as Summary2
Left Join
[dbo].[Cronus$Warehouse Entry]
on (Summary2.EntryNo = [Entry No_])
Then run report 50085 Compressed Warehouse Entry. This report deletes all the warehouse entries and recreates them based on compressed entries.
There are many advantages to cleaning up this table. It improves performance, concurrency and saves space.
Here is the link for the text object and sql script.
Posted in Dynamics NAV | Comments Off
