Inventory in Warehouse Report

[kedalenechong]

Hi all

Any idea how to get the result of Inventory In Warehouse Report with a end date selection?

Kedalene Chong

[Former Member · Accepted solution]

Hi,

You might try this.

SELECT

T3.WhsCode 'Whse Code', T3.WhsName 'Whse Name', T2.ItmsGrpNam, T0.[ItemCode], T1.[ItemName],

sum(T0.[InQty] - T0.[OutQty]) 'OnHand'

FROM OINM T0 

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

INNER JOIN OWHS T3 ON T0.[Warehouse] = T3.WhsCode

WHERE

T0.[DocDate] <= '[%0]' and

T3.WhsCode = '[%1]' and

T2.ItmsGrpNam = '[%2]'

GROUP BY T3.WhsCode, T3.WhsName, T2.ItmsGrpNam, T0.[ItemCode], T1.[ItemName]

HAVING (sum(T0.[InQty] - T0.[OutQty]) <> 0)

ORDER BY T3.WhsCode, T2.ItmsGrpNam, T0.[ItemCode]

Hope Helps!

Regards,

[kedalenechong · Accepted solution]

Hi Mark

Your Query result is correct for Stock on hand by warehouse with selection of historical date option.

Is it possible to include Committed and Purchase Ordered Qty as well?

Thanks a lot!

Kedalene

[Former Member · Accepted solution]

Try this one.

SELECT

T3.WhsCode 'Whse Code', T3.WhsName 'Whse Name', T2.ItmsGrpNam, T0.[ItemCode], T1.[ItemName],

sum(T0.[InQty] - T0.[OutQty]) 'OnHand', T4.IsCommited 'Committed', T4.OnOrder 'Ordered'

FROM OINM T0 

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

INNER JOIN OWHS T3 ON T0.[Warehouse] = T3.WhsCode

INNER JOIN OITW T4 ON T1.ItemCode = T4.ItemCode AND T3.WhsCode = T4.WhsCode

WHERE

T0.[DocDate] <= '[%0]' and

T3.WhsCode = '[%1]' and

T2.ItmsGrpNam = '[%2]'

GROUP BY T3.WhsCode, T3.WhsName, T2.ItmsGrpNam, T0.[ItemCode], T1.[ItemName], T4.IsCommited, T4.OnOrder

HAVING (sum(T0.[InQty] - T0.[OutQty]) <> 0)

ORDER BY T3.WhsCode, T2.ItmsGrpNam, T0.[ItemCode]

Regards,

[kedalenechong · Accepted solution]

Hi Mark

Thanks your Query is almost perfect except Open PO Qty as at Historical Date is not listed.

Kedalene

[Former Member · Accepted solution]

Hi,

What do you mean by 'Open PO Qty as at Historical Date'?

Regards,

[kedalenechong · Accepted solution]

Hi Mark

I mean your Query result is showing Open PO posted after the selected Date range.

Stock On Hand Qty is correct.

Kedalene

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T0.[IsCommited], T0.[OnOrder],max(T3.[DocDate]) as LastReceiptdate,max( T5.[DocDate]) as Lastissuedate

FROM OITM T0  left JOIN OINM T1 ON T0.ItemCode = T1.ItemCode left JOIN IGN1 T2 ON T0.ItemCode = T2.ItemCode left JOIN OIGN T3 ON T2.DocEntry = T3.DocEntry left JOIN DLN1 T4 ON T0.ItemCode = T4.ItemCode left JOIN ODLN T5 ON T4.DocEntry = T5.DocEntry

WHERE  T1.docdate <= [%1] and

GROUP BY T0.[ItemCode], T0.[ItemName], T0.[OnHand], T0.[IsCommited], T0.[OnOrder]

Thanks & Regards,

Nagarajan

[kedalenechong]

Hi Nagarajan

This error appears when executing your Query

[KennedyT21]

Try This

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T0.[IsCommited], T0.[OnOrder],max(T3.[DocDate]) as LastReceiptdate,max( T5.[DocDate]) as Lastissuedate

FROM OITM T0  left JOIN OINM T1 ON T0.ItemCode = T1.ItemCode left JOIN IGN1 T2 ON T0.ItemCode = T2.ItemCode left JOIN OIGN T3 ON T2.DocEntry = T3.DocEntry left JOIN DLN1 T4 ON T0.ItemCode = T4.ItemCode left JOIN ODLN T5 ON T4.DocEntry = T5.DocEntry

WHERE  T1.docdate <= [%1]

GROUP BY T0.[ItemCode], T0.[ItemName], T0.[OnHand], T0.[IsCommited], T0.[OnOrder]

Rgds

Kennedy

[kothandaraman_nagarajan]

Hi,

The condition 'and' is not removed. Try this:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T0.[IsCommited], T0.[OnOrder],max(T3.[DocDate]) as LastReceiptdate,max( T5.[DocDate]) as Lastissuedate

FROM OITM T0  left JOIN OINM T1 ON T0.ItemCode = T1.ItemCode left JOIN IGN1 T2 ON T0.ItemCode = T2.ItemCode left JOIN OIGN T3 ON T2.DocEntry = T3.DocEntry left JOIN DLN1 T4 ON T0.ItemCode = T4.ItemCode left JOIN ODLN T5 ON T4.DocEntry = T5.DocEntry

WHERE  T1.docdate <= [%1]

GROUP BY T0.[ItemCode], T0.[ItemName], T0.[OnHand], T0.[IsCommited], T0.[OnOrder]

Thanks & Regards,

Nagarajan

[kedalenechong]

Hi Nagarajan

Your Query result is showing the current Inventory Stock Qty even if I input 1 Jan 2006 in your date selection criteria.

Kedalene

[kedalenechong]

Hi Kennedy

Your Query result is showing the current Inventory Stock Qty even if I input 1 Jan 2006 in your date selection criteria.

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT

T3.WhsCode 'Whse Code', T3.WhsName 'Whse Name', T2.ItmsGrpNam, T0.[ItemCode], T1.[ItemName],

sum(T0.[InQty] - T0.[OutQty]) 'OnHand',T1.[IsCommited], T1.[OnOrder]

FROM OINM T0 

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

left JOIN OWHS T3 ON T0.[Warehouse] = T3.WhsCode

WHERE

T0.[DocDate] <= '[%0]' and

T3.WhsCode = '[%1]' and

T2.ItmsGrpNam = '[%2]'

GROUP BY T3.WhsCode, T3.WhsName, T2.ItmsGrpNam, T0.[ItemCode], T1.[ItemName],T1.[IsCommited], T1.[OnOrder]

HAVING (sum(T0.[InQty] - T0.[OutQty]) <> 0)

ORDER BY T3.WhsCode, T2.ItmsGrpNam, T0.[ItemCode]

Thanks & Regards,

Nagarajan

[kedalenechong]

Hi Nagarajan

I tested your Query result is correct for Inventory Quantity on hand for warehouse and historical and current date selection but the Commited Qty is for all warehouse and not just the selected warehouse.

Thanks for your help

[KennedyT21]

Hi Kedalene

Try This

select a.ItemCode,a.[Name],SUM(a.[OB-Qty]) [OB-Qty],SUM(a.[OB-Value]) [OB-Value],

sum(a.[Issue]) [Issue] ,sum(a.[Receipt]) [Receipt],

SUM(a.[Cls-Qty]) [Cls-Qty], SUM([ClsValue]) [ClsValue]  from(

select t1.ItemCode,max(t1.dscription) [Name],

(sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [OB-Qty],

sum(isnull(t1.transvalue,0)) [OB-Value],0 [Issue], 0 [Receipt],

0 [Cls-Qty],0 [ClsValue]

from OINM t1 where

t1.docdate < '[%0]' and t1.[Warehouse] = '[%3]' group by t1.ItemCode

union all

select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],

(sum(isnull(t1.outqty,0))) [Issue],

(sum(isnull(t1.inqty,0))) [Receipt],

0 [Cls-Qty],0 [ClsValue]

from OINM t1

where t1.docdate >= '[%0]' and  t1.DocDate <= '[%1]' and t1.[Warehouse] = '[%3]'

group by t1.ItemCode

union all

select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],

0 [Issue],0 [Receipt],

(sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [Cls-Qty],

sum(isnull(t1.transvalue,0))  [ClsValue]

from OINM t1

where t1.DocDate <= '[%1]' and t1.[Warehouse] = '[%3]'

group by t1.ItemCode)a

group by a.ItemCode,a.[Name]

Hope Helpful

Regards

Kennedy

[kedalenechong]

Hi Kennedy

I need these Inventory Quantites for In Stock, Committed, Purchase Ordered, Available like Inventory Status Report with a date selection criteria like Inventory Audit Report.

[Former Member]

Hi Kedalene,

What do you mean by End Date.

Inventory Warehouse Report shows you Current Status of your Warehouse.

In Standard Inventory Warehouse Report does not Include Date Parameter as this Report needs to Show Current Status.

If You Want Inventory Warehouse Report with Data Parameter then you can Create with the halp of Queries from Queries Generator.

Hope this help

--

--

Regards::::

Atul Chakraborty

[Former Member]

Hi ,   Do you want closing balance for particular date?? -Rajesh N

[kedalenechong]

Hi Rajesh

I mean to know the cumulative Quantity status at any historical or current date.

Kedalene

[kothandaraman_nagarajan]

Hi,

What you mean end date selection? Provide an example to understand your requirement.

Thanks & Regards,

Nagarajan

[kedalenechong]

Hi Nagarajan

I mean to have an Inventory Status Report like the standard report but need a date selection criteria like Inventory Audit Report to be able to know the cumulative Quantity status at any historical date with a date selection criteria.

Kedalene

[Former Member]

Hi Kedalene,

check this

SAP B1 SQL H-IN Item Inventory Opening and Closing Stock per warehouse - Business One - SCN Wiki

Thanks

Mohammad Imran