Item Ledger Calculation

(0) Share

Report

Posted on by Liamh501

Hi there,

I'm trying to do the calculation of remaining quantity for each item per Location Code from the Item Ledger Entry table in SQL, but I'm running into a few obstacles.

Would this just be regarding negative and positive output (Entry Type)? I've thought about taking latest date but that's not a solid enough rule.

Any help would be greatly appreciated.

Thanks

Liam

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Nitin Verma 21,708 Moderator on at

Like (0)

Report

Hi,

There are other entry type like Purchase,Sale,Positive Adjmt.,Negative Adjmt.,Transfer,Consumption,Output, ,Assembly Consumption,Assembly Output

Thanks.

Was this reply helpful? Yes No
Liamh501 31 on at

Like (0)

Report

Hi Nitin,

Thanks for responding.

What I want is to get the latest remaining quantity for each item by location code, I just didn't know how to get there?

Thanks

Liam

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,708 Moderator on at

Like (0)

Report

Hi,

You can have an idea with the below link

stackoverflow.com/.../sql-query-get-top-sold-items-from-table-with-cols-saled-qty-item-no-no

Was this reply helpful? Yes No
Suggested answer

Dallefeld 211 User Group Leader on at

Like (2)

Report

Item Availability by Location has the Inventory as one of the fields.

Just take all item ledger entries with remaining quantity <>0 to Excel, run a quick pivot by location.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,697 Super User 2025 Season 2 on at

Like (1)

Report
Hi, If it is viewed in the NAV, I recommend referring to a standard Query of BC.

query 2554 "Items By Location" { QueryType = Normal; OrderBy = ascending (No); Caption = 'Items By Location'; QueryCategory = 'Item List'; elements { dataitem(Item; Item) { column(No; "No.") { Caption = 'No.'; } column(Description; Description) { } dataitem(Item_Ledger_Entry; "Item Ledger Entry") { DataItemLink = "Item No." = Item."No."; column(Location_Code; "Location Code") { Caption = 'Location Code'; } column(Remaining_Quantity; "Remaining Quantity") { Caption = 'Remaining Quantity'; Method = Sum; } } } } trigger OnBeforeOpen() begin end; }

Hope this helps as well.

Thanks.

ZHU

Was this reply helpful? Yes No
Liamh501 31 on at

Like (0)

Report

Thanks for this Kim. I'm doing a SQL query but still works this way.

Thanks again

Liam

Was this reply helpful? Yes No