Item Ledger Entry => Warehouse Entry

Stevenrlp

Please could someone help, I have been searching and trying for weeks to resolve this issue but I have yet to find an answer that works.

I need some way of reporting inventory with a bin locations, that's it in a nutshell, every other warehouse system I have used in the past this has been simple and out of the box but like with everything to do with M$ Nav it has to be extremely difficult and overly complicated.

Currently I am using a SQL statement to join the item journal entry table where Remaining Quantity > 0 to the last warehouse entry record which matches the Item No, Lot No, Serial No, Location, but now i have realised that due to warehousing being turned on items without serials are being incorrectly reported as being in the GOODS OUT bin location.

Is there a way of reporting correctly the bin locations of items in the Item Ledger Entry table?

Any feedback would greatly be appreciated.

Thanks

Steve

jglathe

Hi Steven,

I'm afraid it's bad news. The warehouse entries are practically independent of the item ledger entries, meaning you have no link you can rely on. The only table that has a field to look up where things really are is bin content. To calculate the quantity at date by item, location and bin, you could do with the warehouse entries alone, like this:

select [Item No_]
	, [Location Code]
	, [Bin Code]
	, sum([Quantity]) as Inventory
from [dbo].[<YourCompanyName>$Warehouse Entry]
where [Registering Date] <= '<YourDate>'
group by cube([Item No_]
	, [Location Code]
	, [Bin Code])
having (sum([Quantity]) <> 0) and ([Item No_] <> '')
  and (([Item No_] <> '') and ([Location Code] <> ''))
order by [Item No_]
	, [Location Code]
	, [Bin Code]

There's a catch, however. This only works for locations where you have warehouse functionality enabled... so for the general case you need to do a check first (in location) and build the list by location, either over item ledger entries or warehouse entries, depending on the settings in location.

with best regards

Jens

[edit: typos, afterthoughts etc.]

Stevenrlp

Thanks for your reply, i was playing around with a very similar SQL query the other day but was not able to get the desired output, i will give your statement a try, thanks for the help!!

Stevenrlp

Update: it seems i may have what i need now, well it will have to do for now i guess?

SELECT t1.[Item No_]
   , t1.[Location Code]
   , t2.[Bin Code] 
   , CONVERT(INT,t1.[Remaining Quantity]) [Remaining Quantity]
   ,t1.[Serial No_]
   ,t1.[Lot No_]
      FROM [NSCNAV].[dbo].[NSC UK$Item Ledger Entry] t1, 
             [NSCNAV].[dbo].[NSC UK$Warehouse Entry] t2
      INNER JOIN [NSCNAV].[dbo].[NSC UK$Item] t4
            ON [Item No_] = t4.[No_]
      WHERE (t4.[Item Tracking Code]='FULL') AND (t1.[Remaining Quantity]>0 AND
               t2.[Entry No_] = (SELECT MAX([Entry No_]) [Entry No_] 
                                          FROM [NSCNAV].[dbo].[NSC UK$Warehouse Entry] t3
                                          WHERE t1.[Item No_]=t3.[Item No_] AND
                                                      t1.[Location Code]=t3.[Location Code] AND
                                                      t1.[Lot No_]=t3.[Lot No_] AND
                                                      t1.[Serial No_]= t3.[Serial No_]))
UNION ALL

SELECT [Item No_]
   , [Location Code]
   , [Bin Code]
   , CONVERT(INT,SUM([Quantity])) [Remaining Quantity]
   ,NULL as [Serial No_]
   ,t1.[Lot No_]
   FROM [NSCNAV].[dbo].[NSC UK$Warehouse Entry] t1
   INNER JOIN [NSCNAV].[dbo].[NSC UK$Item] t2
     ON t2.No_=[Item No_]
   WHERE t2.[Item Tracking Code]='LOT'
   GROUP BY [Item No_]
     , [Location Code]
     , [Bin Code]
     ,t1.[Lot No_]
   HAVING (SUM([Quantity])> 0)
   ORDER BY [Item No_]
     , [Location Code]
     , [Bin Code]

jglathe

Hi Steven,

under certain circumstances, yes. You would get the same result by going over the warehouse entries only, and join to your tracking codes (if that's a filter). You could also use the tracking code for the grouping (that's what I would prefer). This would still require that all locations have warehousing activated. Otherwise these locations would be missing in both queries.

with best regards

Jens

Stevenrlp

Thanks, all locations within this company are using warehousing, i can't really use the tracking code for groupings as i would need to return serial numbers and when you group this would not be possible, thanks for your help it has been extremely helpful in my endeavours.