Query to show items not used in last 12 months

[Former Member]

Can anyone help with a query or is there a was in B1 to pull a report of item that have not had activity in the last 12 months?

[former_member204969 · Accepted solution]

You can start with this query to get the items without inventory movement:

Select T.ItemCode,T.ItemName
 From OITM T
 Where not exists (Select T0.ItemCode FROM OINM T0
   WHERE T0.ItemCode =T.ItemCode
       and  T0.DocDate > getdate()-365)

[former_member200818]

Check SBO Reports : Inventory -> Inventory Reports -> Inactive Items

Hope this helps.

Regards

Satish

[Former Member]

I tried the report and it listed some items that had transactions. I need it to display anything that has not been issued, received, sold, shipped or purchased.

[Former Member]

Hi Jim.

In your case, Istvan's query will be good match to meet you need.

Try the query to see if the results is what you want.

Regards,

Gordon

[Former Member]

I did try it and it displayed items that I had issued for production. It is possible that there are lots that have never changed in the time specified. I need it to be based on item number not lot specific of an item number.

[Former Member]

What do you mean "lots that have never changed in the time specified'? Do you refer to the warehouse?

Please elaborate more.

Gordon

[Former Member]

Because that report identified items that I know have been issue in the last month the only way I could think that it is showing up is that if for some reason a lot of a particular item has not moved in a year but other lots have.. I do not know if this is the case. Otherwise, the report is showing items that most definately have been issue in the last month.

[Former Member]

If your item definition do not link to GL account, it will not show up in the query results no matter what. Are you sure you issued them last month? Click on the orange arrow near the itemcode to dig into it and get the "Inventory Posting List". I am sure you will find none for inventory transaction.

[Former Member]

Sorry. I do mean "it will show up in the query results no matter what".

[Former Member]

I drilled into one item that I knew we are still using. The date on my inactive report was 01/01/07. This is what was displayed in inventory posting.

Posting Date Document Whse Qty Price after Disc. Balance

210070 1,329

1/2/2008 SO 3133 1 -560 $0.05 769

1/29/2008 ST 1 231 $0.05 1,000

1/31/2008 SO 3231 1 -585 $0.05 415

1/31/2008 SO 3235 1 -415 $0.05

2/7/2008 PD 2898 1 5,500 $0.00 5,500

2/8/2008 SO 3261 1 -642 $0.00 4,858

2/12/2008 PU 6801 1 $249.98 4,858

3/10/2008 SO 3429 1 -1,281 $0.05 3,577

3/19/2008 SO 3520 1 -1,335 $0.05 2,242

3/28/2008 PD 3065 1 5,500 $0.05 7,742

4/3/2008 PU 7204 1 ($0.27) 7,742

4/16/2008 SO 3756 1 -1,308 $0.05 6,434

5/2/2008 SO 3884 1 -1,265 $0.05 5,169

5/5/2008 SO 3946 1 -1,368 $0.05 3,801

7/10/2008 SO 4494 1 -643 $0.05 3,158

3,158

[Former Member]

I am talking about query. You are checking the "Inactive Report". The definition of that report do not meet you need.

Please try the query to see the results.

[Former Member]

Sorry Gordon,

You were correct. I was running the report. If I want to also include Onhand from OITW how do I modify the query?

[Former Member]

That can be done but it will use lots of system resources because both tables are large. Since it may lock up your system, I suggest you to try it in test environments.

You just need to modify the upper part of the query to:

Select T.ItemCode,T.ItemName, T0.Onhand, T0.WhsCode

From OITM T

Inner Join OITW T0 on T0.Itemcode = T.ItemCode

Because the results might be huge, you may need some more constrains like where T0.OnHand > 0 to the end of "Where" clause.

Gordon

[former_member583013]

Hi Jim,

The Query from Istvan will give you all the items which did not have a transaction affecting the Inventory G/L account. But two things to note:

An inventory transfer from one warehouse to another is also considered a transaction and though this item actually is not transacted with a BP will be excluded in this list and so are the Misc goods receipts and isssues. Sales Orders and Purchase Orders also do not get considered.

Understanding what all transactions are to be considered for your term USED in the last 12 months would help

Suda