Skip to Content
0
Former Member
Nov 23, 2009 at 09:26 AM

Inventory status report

859 Views

Dear All,

I Need inventory report showing the item code and item description and a UDF. as under

I need to have selection criteria of WAREHOUSE NAME AND FROM DATE and TO DATE, and in the report in Row wise it needs to show me the

Following Details of Items.

1) Opening Balances of (From Date)

2) Received Quantity In the Period of (From Date and To Date)

3) Issue Quantity In the Period of (From Date and To Date)

4) FInal Stock of (To Date)

I

i tried below query but it gives me selection of Group name. Kindly advice on below query

Declare @FromDate Datetime

Declare @ToDate Datetime

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 @Whse = (Select Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '[%2]')

Select

@Whse as 'Warehouse',

a.Itemcode,

max(a.Dscription) as ItemName,

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

Where

N1.DocDate < @FromDate

and N1.Warehouse = @Whse

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

Where

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

N1.Inqty >0

and N1.Warehouse = @Whse

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

Where

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

N1.OutQty > 0

and N1.Warehouse = @Whse

Group By

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

where

a.ItemCode=I1.ItemCode

Group By

a.Itemcode

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

Order By a.Itemcode

looking forward for support