Closing stock as off a particular date

[Former Member]

How to write a query for closing stock (in quantity) for all items by group by warehouse for a particular date?

Thank you.

[former_member583013]

Please close this Question if answered.

Best wishes

Suda

[former_member204969]

You can use the OINM table. It contains every inventory transaction with date, and you can sum until the required date.

[Former Member]

I know i can use OINM table, but i do not know how to wrtite that query, please help. thank you.

[former_member583013]

Try this Query

SELECT T0.ItemCode, SUM(T0.InQty-T0.OutQty) AS 'Issue Quantity' FROM [dbo].[OINM] T0 WHERE T0.ItemCode = '[%0]' AND T0.CreateDate <= '[%1]'

GROUP BY T0.ItemCode

NOTE: If you want this to be Run for all Items then remote this portion <i><b>T0.ItemCode = '[%0]' AND</b></i> from the Query

Suda

[former_member186095]

Hello Ming-Ming,

Hope you coul be fair and and wait for Istvan Koros's answer since you only write to him and not ask other to answer your problem. I am not trying to prevent other to answer your problem cause it is probably very important and need quick answer to you but once again it is good to wait the person you have asked except you did not reply to one of the others answer.

Rgds,

[Former Member]

thank for your comment, hmmm... but I haven't read his second reply till reading yours..., so the point awarded.... ??? anyway thanks for the comment, i will take note

[former_member204969]

When I saw your second question, I saw that Suda has shown you a query you can start with. It's OK.

[Former Member]

Hi there!

Can you describe your business needs more precisely?

Regards,

Anton.

[Former Member]

the standard stock count in SAP B1 (Inventory>> Inventory Transaction >> Initital Quantity, Inventory Tracking, Stock Posting) is "real time" and not back-dated and the screen will display all items irrespective whether there is stock balance in the system. Anyone has a query where able to have the stock balance generated at any cut-off date, i.e. the stock count can be back-dated and only display those items with quantity (in the system) as at that cut-off date .

Thank you.