Query for Opening And Closing Stock

[Former Member]

Hi Experts...

I need a report for Opening and Closing stocks and Goods Receipt and Goods Issue for Raw Materials and Finished Goods and Consumbles ..instead of going for different reports for each we need a combined report which can display for all Raw Materials, Finished Goods and Consumbles and we need it for a specific date range Material type(Raw MAterials,Finished Goods, Consumbales)

and below is the format i needed.

---------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                       RAW MATERIALS /FINISHED GOODS/CONSUMBALES
---------------------------------------------------------------------------------------------------------------------------------------------------------

                        Opening Stock                      Closing Stock   	 Goods Receipt             	Goods Issue
----------------------------------------------------------------------------------------------------------------------------------------------------------
                         Qty   Value                             Qty   Value                     Qty   Value                      Qty   Value
----------------------------------------------------------------------------------------------------------------------------------------------------------

Item Name
---------------------------------------------------------------------------------------------------------------------------------------------------------

Regards,

Vamsi.

[Former Member]

Hi Vamsi,

->> For GL account and business partner opening balances you can use this query:

SELECT T0.[TransId],T1.[Line_ID], T1.[Account],T1.[ShortName],
  T1.[Debit], T1.[Credit], T1.[RefDate]
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
WHERE T0.[TransType]='-2'

(The bookings of inventory initial quantities are with transType=58.)

->> To add closing balance at a given date try this:

declare @d datetime
Set @d=/* select x.RefDate from jdt1 x where x.RefDate=*/'[%0]'
SELECT T0.[TransId],T1.[Line_ID], T1.[Account],T1.[ShortName],
  (select sum(j.debit)-sum(j.credit) from JDT1 j
    where j.Account=T1.account and j.RefDate<=@d) Closing,
  T1.[Debit], T1.[Credit], T1.[RefDate]
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
WHERE T0.[TransType]='-2'

Regards,

Madhan.

[Former Member]

Hi Madhan,

I need the report for opening and closing stock in the inventory as well as Goods receipt and Goods issue..Not for the Opening and Closing Balance...

Pls post subsequent query...

Regards,

Vamsi.

Edited by: Parimis on Jan 20, 2010 5:11 AM

Edited by: Parimis on Jan 20, 2010 8:50 AM

[Former Member]

Hi Vamsi,

Check the thread,

Regards,

Madhan.

[Former Member]

Hi Madhan..

I dont need the GL Account Balance..

I need the Opening Stock and Closing Stock for every month and also Goods Receipt and Goods issue for all Raw Materials,Finished Goods and Consumbles.

Regards,

Vamsi.

[Former Member]

Hi Guys.

I need an Inventory stock report with Item name, Opening Stock,Goods issue Quantity,Goods Received ,Item Price,Location ,(Closing Stock)Total Value.

We need the input by Date Range and Item Group and Location(if Possible) Below is the Code i used to run the report.

Pls Update the Query...

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), sum(a.[Opening Balance]) as [Opening Balance], sum(a.[IN]) as [IN], sum(a.OUT) as OUT,max(a.Price) as 'Price', ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from dbo.OITM I1
Left JOIN (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 ON a.ItemCode=I1.ItemCode
where  I1.ItmsGrpCod = @Group
Group By a.Itemcode 
Order By a.Itemcode

Regards,

Vamsi.