Inventory Information

(0) Share

Report

Posted on by CILGroup

397

Hi,

What is the best way to get inventory information for a reporting system we are using that directly connects to our NAV database?

i.e. which tables hold the following info;

Stock on Hand

On order from Purchase

On order from Assembly

Allocated to Assembly

In transit

On back order

Reserved

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

Hello!

All information about stock you can get from table 32 "Item Ledger Entry" (Quantity) or 5802 "Value Entry" (if you need cost, sales amount etc)

In Location table there is a field "Use As In-Transit". In combination with 32 table it gives you In-transit quantity

On order from Purchase - table 39 with filters by Document Type

On order from Assembly - 901 "Assembly Line"

Info about reserved quantity there is in 337 table Reservation Entry

Was this reply helpful? Yes No
Suggested answer

RobertasR 5,004 on at

Like (1)

Report

Hi

Stock On Hand = Sum Table "Item Ledger Entry", field Quantity

On Order from Purchase = Sum Table "Purchase Line", field "Outstanding Qty. (Base)", where "Document Type" = Order and "Type" = Item

On Order from Assembly = Sum Table "Assembly Header", field "Remaining Quantity (Base)", where "Document Type" = Order

Allocated to Assembly = Sum Table "Assembly Line", field "Remaining Quantity (Base)", where "Document Type" = Order, and "Type" = Item

In Transit = Sum Table "Transfer Line", field "Qty. in Transit (Base)", where "Derived From Line No." = 0

On Back Order (I guess this is sales order not completed?) = Sum Table "Sales Line", field "Outstanding Qty. (Base)", where "Document Type" = Order, and "Type" = Item

Reserved is a bit different as reservations are usually 'double sided', that is reservation from sales order to ILE will show as two different lines. So you need to understand what kind of reservations you want to show, basically = Sum Table "Reservation Entry", field "Quantity (Base)", and apply filter on "Source Type" and "Source SubType", where "Reservation Status" = Reservation.

Hope that helps.

Robertas

Was this reply helpful? Yes No
Suggested answer

Amol Salvi 18,698 on at

Like (0)

Report

Check table Item Ledger entry for all required information.

Was this reply helpful? Yes No
CILGroup 397 on at

Like (0)

Report

Thank you all for the responses you have been extremely helpful!

Robertas, do we need to use the entire item ledger entries to get the stock on hand figure? what if we have thousands of transactions? it would be extremely slow just to get the current stock on hand figure wouldn't it?

Alex

Was this reply helpful? Yes No
Suggested answer

RobertasR 5,004 on at

Like (0)

Report

Hi Alex,

Good question. If you just want the current stock figure 'as is', then you could filter Open=TRUE, and use field "Remaining Quantity", instead of "Quantity". Combined with "Item No." filter there won't be too many entries for the given item.

However, if you want to view inventory at any given point of time then you need to go through all entries.

Was this reply helpful? Yes No
CILGroup 397 on at

Like (0)

Report

Ok thanks that all makes sense.

One final question regarding this, I know in NAV we can see flow fields, are they accessible from the BI system that directly connects to the database? Maybe this could help rather than having to sum the fields?

Was this reply helpful? Yes No
RobertasR 5,004 on at

Like (0)

Report

I don't think flowfields are available for external tools. In SQL table you'd probably see them as zeros

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Flow fields don't exist on SQL table level. You have to get data from the table which is the source for calculating field. For examle field "Inventory" in Item table is calculated on the basis of data in table "Item Ledger Entry".

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello Robertas,

Great detailed answer ! I have another question relevant to this one. I am trying to find a way to "view" the data in "Item Availability by Event Report" via ACCESS ODBC connection. From what I have found so far it seems this information is stored in temporary tables like "Inventory Page Data" and "Inventory Profile" (not sure which one of the two). In any case since both are temporary they are always empty when I connect via ODBC link on them. Is there an SQL statement I could run to generate the "Item Availability by Event Report" for a specific item using either Access or an SQL editor ?

Thanking you in advance

Kind Regards

Dimitris

Was this reply helpful? Yes No
Suggested answer

RobertasR 5,004 on at

Like (0)

Report

Hi Dimitris,

As you said, system fills in temporary tables when opening 'Item Availability by Event', using some complex filtering, searching, merging several tables, etc. There isn't any SQL statement that would replicate exactly that, and if you want to gather it via SQL then you'd need to create some SQL stored procedures, although that would be a big task.

Robertas

Was this reply helpful? Yes No