Microsoft Dynamics NAV (Archived)

Identifying Expired Materials Through Item Ledger Entries Table

(1) Share

Report

Posted on by Community Member

Hi All,

To identify expired materials I've been running a filter on the Item Ledger Entries Table that looks like this:

Field Filter

Item No. 430*

Entry Type Output

Posting Date 01/01/01..05/02/14 (2 year shelf life and no possibility of being made before 01/01/01)

Remaining Quantity 0.001..100,000 (basically any non zero remaining quantity)

The problem I'm having is if the the item has had a positive journal adjustment this increase in quantity is not reflected in the remaining quantity field of the output line. So I can run this filter and not identify expired material if its quantity is entirely due to positive journal adjustments.

Any thoughts on how I can fix this?

Thanks!

Patrick

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (3)

keoma 32,729 on at

Like (0)

Report

how do you define expired items ? no positive adjustments since date x ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hey Jonathan,

They're defined as anything older than their shelf life, starting from the original production date. Any positive adjustment would just be due to cycle counting and would not reset the the clock.

The issue I'm having boils down to the Remaining Quantity field on the output line can be zero (and therefore not show up in this filter) but quantity for this item/lot still remains.

Thanks!

Was this reply helpful? Yes No
Alexander Ermakov 28,096 on at

Like (1)

Report

But, making the positive adjustment would not actually reset their shelf life, right? Meaning, if the adjustment has been posted on 30.04.2015, the item itself could have been made earlier and thus still has a risk of being non-valid already?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes exactly.

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,096 on at

Like (1)

Report

Than this means you do not have real production date of the item that has been posted to the system as a positive adjustment, unless there are some other attributes you assign to this item that would allow you to track that. E.g., there is "Expiration Date" field available; you need to post your positive adjustment the way it is filled in ILE table with a correct date. Or, to assign the adjusted quantity the same lot number as for the initially produced lot that is adjusted during stock-taking.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Alexander thanks for your thoughts!

The positive adjustments are assigned and effected on the specific, initial lot.

I may not be articulating my problem adequately. When I filter the Item Ledger Entries Table by entry type Output, the Remaining Quantity field (on the line for the initial production order) for specific lots can be zero, even when quantity of that same lot is not zero due to positive adjustments (the sum of the the positive adjustments equals the volume displayed in Bin Contents Table).

I'm wondering if there is a better way to query for expired product. Or does this issue indicate a deeper problem with our system?

-Patrick

Was this reply helpful? Yes No
Verified answer

Alex A 2,900 on at

Like (1)

Report

Hi Patrick, to do this properly you would need to label or code the physical product with an expiration date, and also set up the Item in NAV with an Item Tracking Code using Lot Number or Serial Number. On the Item Tracking Code card is an option to do Positive and Negative Adjustment Tracking.

Was this reply helpful? Yes No
Verified answer

Alex Chow 4,481 on at

Like (0)

Report

Hi Patrick,

Filtering on the Posting Date would not give you what you need because that's the transaction date of the item entry, not when it's first produced.

What it sounds like you need is a 2nd field so you can keep track of the date that the item will expire. As Alexander suggested, utilizing the expiration date in NAV will help you keep track of the expiration date.

TDLR; filter on the expiration date, not posting date.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

[quote]The problem I'm having is if the the item has had a positive journal adjustment this increase in quantity is not reflected in the remaining quantity field of the output line. So I can run this filter and not identify expired material if its quantity is entirely due to positive journal adjustments. [/quote]

What's up with this?

If you make a positive adjustment, why isn't that reflected in the remaining quantity field?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Here's a screen shot of whats going on. The remaining quantity field in the output line for lot 001 of item 730001 is 0 but the actually remaining quantity is the sum of the two positive adjustments. I was missing the quantity accounted for by the positive adjustments because I was filtering just the non zero output lines..

Not sure why it does it this way (you'd think the remaining quantity should be the same for all lines referring to the same lot) but I was able to get what I need by assigning expiration time frames in the item card (thanks for the help on that btw) and then filtering by that.

Was this reply helpful? Yes No