Inventory Report

[Former Member]

Hi

my client want a report for inventory like (opening stock quantity,In quantity, out quantity, closing stock quantity, opening stock value , In value , out value, closing stock value ) Group wise .

Let we have 3 group A,B and C.

So result should be come .

Item Group, Opening Stock In Quantity, Out Quantity, Closing Stock Balance, Opening Stock Value, In Quantity Value, Out Qty value, Closing Stock Value ,

A 100 50 20 130 1000 500 200 1300

B 50 50 30 70 1000 1000 600 1400

C 20 25 10 35 200 250 100 350

Parameter Should be From Date, To Date, Item Group

Thanks & Regard

Khan

[Former Member]

Hi Khan,

Check this link.

Thanks,

Srujal Patel

[Former Member]

Thanks Srujal Patel

but this query showing only stock quantity not showing stock value

Thanks

Khan

[Former Member]

Hi

I am working on this query.

Try to achive other way

Go to Inventory / Inventory Reports / Inventory Audit Reports

Thanks,

Srujal Patel

[Former Member]

Hi suraj

Yes inventory Audit report giving me right result but not give the result All group wise in Summary report it giving all the transaction result but i want summary of all item group .

thanks & regard

Khan

[VamcParimisetti]

Hi KHAN,

Check This ,

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Group nvarchar(10)

Declare @Whse nvarchar(10)

Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')

Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')

Set @Group = (Select Max(s2.ItmsGrpCod) from dbo.OITB S2 Where S2.ItmsGrpNam = '[%2]')

Set @Whse = (Select Max(s3.Warehouse) from dbo.OINM S3 Where S3.Warehouse = '[%3]' )

Select

@Whse as 'Warehouse',

a.Itemcode,

max(a.Dscription) as 'Description',MAX(a.Price) as 'Price',

sum(a.[Opening Balance]) as [Opening Balance],

sum(a.[IN]) as [Receipt],

sum(a.OUT) as [Issue],

((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing,

( MAX(a.Price) * ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) ) as ClosingValue

from(

Select

N1.Warehouse,

N1.Itemcode,

N1.Dscription,N1.Price,

(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],

0 as [IN],

0 as OUT

From dbo.OINM N1

Where

N1.DocDate < @FromDate and N1.Warehouse = @Whse

Group By

N1.Warehouse,N1.ItemCode,N1.Dscription,N1.Price

Union All

select

N1.Warehouse,

N1.Itemcode,

N1.Dscription,N1.price,

0 as [Opening Balance],

sum(N1.inqty) as [IN],

0 as OUT

From dbo.OINM N1

Where

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

N1.Inqty >0

and N1.Warehouse = @Whse

Group By

N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price

Union All

select

N1.Warehouse,

N1.Itemcode,

N1.Dscription,N1.price,

0 as [Opening Balance],

0 as [IN],

sum(N1.outqty) as OUT

From dbo.OINM N1

Where

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

N1.OutQty > 0

and N1.Warehouse = @Whse

Group By

N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price) a, dbo.OITM I1

where

a.ItemCode=I1.ItemCode and

I1.ItmsGrpCod = @Group

Group By

a.Itemcode

Order By a.Itemcode

Regards,

PV