Monthly Open Balance & Closing Balance of Inventory Stock Report

[former_member1133820]

Dear Experts,

Can anyone guide me on how to create an Open Balance & Closing Balance of Inventory Stock Report?

They don't want to use Inventory Audit Report to do it as it is not flexible enough.

The parameters of the report are shown below:

ProductName OpenBalanceTotal GoodsReceiptQty GoodsIssueQty ClosingBalanceTotal

Thanks in advance.

Xavi

[former_member1133820 · Accepted solution]

I've try to work on the suggestion give in other threads but got errors on it. Below is the query given:

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 = 'Finished Goods')
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

When I try to run this query, I get the following error messages:

Msg 102, Level 15, State 1, Line 17

Incorrect syntax near 'Balance'.

Msg 102, Level 15, State 1, Line 27

Incorrect syntax near 'Balance'.

Msg 102, Level 15, State 1, Line 42

Incorrect syntax near 'Balance'.

Msg 102, Level 15, State 1, Line 59

Incorrect syntax near 'Balance'.

Kindly guide me on this as I'm really new to SAP B1.

Thanks.

[Former Member · Accepted solution]

Try 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 = 'Finished Goods')
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.OpeningBalance) as 'OpeningBalance',
sum(a.[IN]) as Receipt,
sum(a.OUT) as Issue,
(sum(a.OpeningBalance) + sum(a.[IN]) - Sum(a.OUT)) as Closing,
MAX(a.Price) * (sum(a.OpeningBalance) + 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 'OpeningBalance',
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 'OpeningBalance',
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 'OpeningBalance',
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

Thanks,

Gordon

[former_member1133820 · Accepted solution]

Hi Gordon.

I've tried it in SAP but still not success.

Below is the error messages that I got:

Msg 102, Level 15, State 1, Line 15

Incorrect syntax near 'Balance'.

Msg 156, Level 15, State 1, Line 26

Incorrect syntax near the keyword 'IN'.

Msg 156, Level 15, State 1, Line 41

Incorrect syntax near the keyword 'IN'.

Msg 156, Level 15, State 1, Line 58

Incorrect syntax near the keyword 'IN'.

Thanks.

[Former Member · Accepted solution]

Updated above query. Try again.

[former_member1133820 · Accepted solution]

Hi, Gordon.

Already tried but still got some error messages:

Msg 156, Level 15, State 1, Line 16

Incorrect syntax near the keyword 'IN'.

Msg 156, Level 15, State 1, Line 26

Incorrect syntax near the keyword 'IN'.

Msg 156, Level 15, State 1, Line 41

Incorrect syntax near the keyword 'IN'.

Msg 156, Level 15, State 1, Line 58

Incorrect syntax near the keyword 'IN'.

Thanks,

Xavi

[Former Member · Accepted solution]

Try again.

[former_member1133820 · Accepted solution]

Still cannot..

Is it possible that the error messages are caused by different version of MSSQL server?

I'm using MSSQL 2005.

[Former Member · Accepted solution]

I have tested on SQL Server 2005. It works.

[former_member1133820 · Accepted solution]

Thanks, Gordon.

I tried it for several times and it works finally.

[Former Member · Accepted solution]

Dear Gordon,

I tried to use this report. But it seems the unit price is wrong and hence to total value of closing stock on that particular date.

Rgds,

Kannan

[Former Member · Accepted solution]

Hi Gordon

You Query is fantastic!

But i would like to add few more parameters:

1. I would like to have the choice to choose 1 warehouse or all, i have tried to add Where S3.Warehouse = '[%3]' or '[%3]' = '' but it is not working

2. Similarly, i would like to have the choice to select one specific item (by its item code) or all items

Thanks in advance for your help

[Former Member]

Hi Expert,

I am Jumping on this tread, please help me.

How to show Opening Qty, Receipt Qty, Issue Qty, closing Qty on monthly basis for the year of 2013 ?

I mean for 31st Jan 2013 what is Opening Qty, Receipt Qty, Issue Qty, closing Qty||

28th Fab 2013 what is Opening Qty, Receipt Qty, Issue Qty, closing Qty ||

31st March 2013 what is Opening Qty, Receipt Qty, Issue Qty, closing Qty||

and so on till 31st Dec 2013 within same report.

Earlier help appreciable.

Regards

Arun Singh