on 07-07-2010 8:52 AM
Hi,
We have our financial audit going on at the moment, the auditors are requesting a report showing any stock that hasnt moved at all during the last year. Is there any easy way of getting this data off SAP? I know there is the inactive items but this only shows sales transactions i need to take a look at purchase and production transactions too? Any ideas?
HI David,
I found this for sale invoices. You may try by changing the table names and see if it works for you.. Also, you can change the days from 60- 365. Not sure if the same query would work for production. I think it would be better if you try to create a query report from OINM table.
select max(t1.docdate) as 'Last Inv Date', t0.itemcode, t0.dscription
from inv1 t0 inner join oinv t1 on t0.docentry = t1.docentry
where t1.doctype = 'I'
group by t0.itemcode, t0.dscription
having max(t1.docdate) <= getdate() - 60
order by t0.itemcode
Thanks,
Joseph
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi David,
You can try this one, it will check the latest entry in OINM for each itemcode. You can select the cut off date when running the query in B1. The OINM will register any stock movement. Orders and Quotations are not included as no stock movement takes place.
The first query is by Item.
select itemcode, max(docdate) as 'Last Date of Stock Movement'
from oinm t0
where itemcode not in (select itemcode from oinm where docdate >= '[%0]')
group by itemcode
order by itemcode
The second will check per item and warehouse.
select itemcode, warehouse, max(docdate) as 'Last Date of Stock Movement'
from oinm t0
where itemcode not in (select itemcode from oinm where docdate >= '[%0]')
group by itemcode, warehouse
order by itemcode
Hope it helps,
Jesper
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.