cancel
Showing results for 
Search instead for 
Did you mean: 

Stock Report Query

Former Member
0 Kudos

Dear Query expert-er.

I need a Query Stock Report in SAP B1

1.Item number

2.Description in foreign Language.

3.Description

4.opening stock Qty

5.Opening stock value

6.Material Inward

7.Material Return

8.Material issues (Production Issues & Goods Issues)

9.Closing Stock

Following Query Report Need. help me

Thanks

Selva

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Dear Selva,

Try:

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Whse nvarchar(10)

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

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

select @Whse = 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 AND N1.TransType IN ('16','20','59')

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 AND N1.TransType IN ('21','60')

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

Thanks,

Gordon

Former Member
0 Kudos

Dear sir,

I want a query

Opening stock +   Grpo    -   Goods return-Goods Issues=Closing stock

         100     +     1000     -        40        -     900           =      160

Following Query need please help me

Thanks

selva

Former Member
0 Kudos

I am trying to make modification an excel report as from Inventory Audit Report Criteria  the table OINM & OITM I had used to find the results. I have attached a picture which I need as a output into SAP system.On the below is my desire output needed from the Query but not getting proper one. Any one have any suggestions for this.

Right now getting this

KennedyT21
Active Contributor
0 Kudos

Try This

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Whse nvarchar(10)
select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'
select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'
select @Whse = 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

Regards

Kennedy

Former Member
0 Kudos

hai Kennedy,

Query reports working fine.but i need Instock Qty GRPO, Goods Return Qty  ,Out Qty Only Goods Issues not delivery and others marketing documents.

Thanks

Selva

former_member351438
Participant
0 Kudos

Hello kennedy.t

How to use this code in HANA?

JasonLax
Product and Topic Expert
Product and Topic Expert
0 Kudos

Getting Started with SCN is for general SAP Community Network related discussions only.

To get a quick (or any) response, create your new discussion in a space related to your query.  This way it will be visible to topic experts who will then see and reply to it.  The SCN Site Index contains a full listing of all SCN spaces.

Former Member
0 Kudos

Dear all following stock report quer need.

1.foreign Name   -pvc16mm

2.Item code        -10000010

3.description       -pvc pipe

4.Opening stock  -100

5.GRPO             -1000

6.Goods Return  -40

7.goods Issues   -900

8.closing stock   -160

Followings reports query need.please guide me

thanks

Selva

JasonLax
Product and Topic Expert
0 Kudos

: To get a quick (or any) response, create a new discussion in a space related to your query.  This way it will be visible to topic experts who will then see and reply to it.  The SCN Site Index contains a full listing of all SCN spaces.