Skip to end of metadata
Go to start of metadata

(Back to H-IN Inventory Module SQL Posted to Wiki Listing - H-IN Inventory Module SQL Posted to Wiki

TYPE:Frequently requested

AUTHOR NAME: Balakumar Viswanathan

PURPOSE AND BACKGROUND:  This SQL was created in SAP 8.8 PL17. This query is used to find the Opening and closing of the Inventory (item) within a date range and warehouse selection. If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation. 

Here is a screen print of the SQL 

Copy and Paste this SQL:

--H-IN Item Inventory Opening and Closing Stock per warehouse Ver 1 VB 2011 03 25

--DESCRIPTION:  SQL displays the Opening and Closing Stock of the items with warehouse selection.

--AUTHOR(s):
--Version 1 Balakumar Viswanathan 2011 03 25

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



  • No labels

8 Comments

  1. Unknown User (ig53w3o)

    Hi,

    If I need this report for all warehouses instead by warehouse wise, what changes can be done in this query???

    Thanks in advance.

    Sanjay

    1. Former Member

      Also was looking for this query and just addapted it as follows:

       

      Now I just need to adapt the input for a sql query on excel.

       

      Thank you Balakumar.

       

      Declare @FromDate Datetime
      Declare @ToDate Datetime

      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 a.Warehouse 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 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 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
      Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1
      where a.ItemCode=I1.ItemCode and a.ItemCode >= 'P00000000' and a.ItemCode <= 'P99999999'
      Group By a.Warehouse, a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

  2. Former Member

    Hello Sanjay - good question - I would suggest you copy the SQL into the Core Forum and ask that exact same question.  You can also reference this Wiki page (so they can see the screen shot) and you will get your question/change request resolved quicker that way.  See number 5 on this Wiki page - SAP B1 SQL Tips and Tricks .

    Regards - Zal

  3. Unknown User (104luslf3)

    Hello,

    your Query works good but column "OpeningBalance" is empty here :-(

    I couldn't find your table "a" -please tell me in which table I find the OpeningBalance

    Thank you and greetings from Germany

  4. Unknown User (iryekuw)

    Hi Deni,

    table a is referred to following part :

    (Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))
    asOpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1
    WhereN1.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
    andN1.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

    Fromdbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0
    andN1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a

     Regards

  5. What is the use of I1 table in the above query, when it is not referenced from any of the columns?

  6. Former Member

    I need the cost and accumulated value along with the result set. kindly guide.

    Awais

  7. Former Member

    Hello Experts,

    Your Query is Very Useful to US. 

    But we Want a Entry Time In This query. 

    at what date inventory comes in Warehouse.

    Please anyone help me on this.

    Thanks,