Query to generate inventory of particular date, including vendor,base_ref

[Former Member]

Hi,

Below is the query that I get from SCN and modify it to meet with own requirement, but it seem that it doesn't work, can anyone help?

I want to include T0.BASE_REF, T0.CardCode, T0.CardName

SELECT T1.WhsName, T0.ItemCode, Max(T0.Dscription) 'Item Name', SUM(T0.InQty-T0.OutQty) 'On Hand', T0.BASE_REF, T0.CardCode, T0.CardName

FROM OINM T0

JOIN OWHS T1 ON  T1.WhsCode=T0.Warehouse

WHERE T0.[DocDate] <=[%0] AND T0.[CardCode] = [%1]

GROUP BY T1.WhsName,T0.ItemCode, T0.BASE_REF, T0.CardCode, T0.CardName

Having SUM(T0.InQty-T0.OutQty) > 0

Thanks

[ashishrbyadav]

Query is not giving proper data.

[Former Member]

Hi,

It is impossible to include those info into this query. The only purpose of this query is to give you an inventory status for each item in previous date.

Tell us what you want to return. We can create it for you.

Thanks,

Gordon

[Former Member]

Hi,

What I really want is a report that can work like audit report and can display the document customer/vendor name or code.

Can this be done?

Thanks

[former_member186095]

Hi,

Inventory audit report is to display the stock movement and item cost based on whse on certain date and other selection criteria or parameter.

You can try to enhance this following query to add the vendor code:

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @itemgrp nvarchar(10)

select @FromDate = min(tx.Docdate) from dbo.OINM tx where tx.Docdate >='10/01/2012'

select @ToDate = max(sx.Docdate) from dbo.OINM sx where sx.Docdate <='10/30/2012'

select @itemgrp = Max(s3.ItmsGrpCod) from dbo.OINM S2 inner join oitm s3 on s2.itemcode = s3.ItemCode

Where S3.ItmsGrpCod = '112'

Select a.Itemcode, max(a.Dscription) as ItemName, a.Warehouse,

sum(a.OpeningBalance) as OpeningBalance, sum(a.INq) as 'IN', sum(a.OUT) as OUT,

((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing ,

(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM

from( Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))

as OpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1

inner join OITM n2 on n2.ItemCode = n1.ItemCode

Where N1.DocDate < @FromDate and n2.ManBtchNum <> 'y'

and N2.ItmsGrpCod = @itemgrp

Group By N1.Warehouse,N1.ItemCode,

N1.Dscription

Union All

select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,

sum(N1.inqty) , 0 as OUT From dbo.OINM N1

inner join OITM n2 on n2.ItemCode = n1.ItemCode

Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate

and N1.Inqty >0  and n2.ManBtchNum <> 'y'

and N2.ItmsGrpCod = @itemgrp

Group By N1.Warehouse,N1.ItemCode,N1.Dscription

Union All

select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT

From dbo.OINM N1

inner join OITM n2 on n2.ItemCode = n1.ItemCode

Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0 and n2.ManBtchNum <> 'y'

and N2.ItmsGrpCod = @itemgrp

Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1

where a.ItemCode=I1.ItemCode

Group By a.Itemcode , a.Warehouse

Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

--order by a.Warehouse, a.ItemCode

Good luck

Rgds,

JM

[KennedyT21]

Hi Olga..

Can u explain in detail why you required the cardcode and cardname.. as the query you have posted you have cardcode and cardname what is missing form it

Regards

Kennedy

[Former Member]

Hi,

Actually I want to find alternative to the default inventory audit report in SAP B1.

What I really want is a report that can work like audit report and can display the document customer/vendor name or code.

Can this be done?

Thanks

[kothandaraman_nagarajan]

Hi,

The BP code and BP name is GL account and its name. It is not like OCRD card code and card name.

Please advice your exact requirement.

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

That is ok, as long as can display the BP code and name, it should be ok.

Thanks

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T1.WhsName, T0.ItemCode, Max(T0.Dscription) 'Item Name', SUM(T0.InQty-T0.OutQty) 'On Hand', T0.BASE_REF, T0.CardCode, T0.CardName

FROM OINM T0

JOIN OWHS T1 ON  T1.WhsCode=T0.Warehouse

WHERE T0.[DocDate] <= '[%0]' AND T0.[CardCode] = '[%1]'

GROUP BY T1.WhsName,T0.ItemCode, T0.BASE_REF, T0.CardCode, T0.CardName

Having SUM(T0.InQty-T0.OutQty) > 0

Thanks & Regards,

Nagarajan