on 08-23-2010 4:29 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
103 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.