cancel
Showing results for 
Search instead for 
Did you mean: 

need help in inventory pening and closing stock report

Former Member
0 Kudos

Hi,

I need help to get Inventory Opening and closing stock report.

I want the Inventory stock report from Sales Invoice, Sales Return, Purchase invoice and Purchase return only.

Is it possible to get the stock report fromabove 4 document?

the requirement is that it should not show inventory which is only delivered but nt invoiced and made GRPO but not invoice.

If it is posible to get stock report, please tell me

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

HI

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  TransType IN (13, 14, 18, 19)

                  AND 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  TransType IN (13, 14, 18, 19)

                  AND 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  TransType IN (13, 14, 18, 19)

                  AND 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

Venki

Former Member
0 Kudos

hi i use

Declare @FromDate Datetime
Declare @ToDate Datetime Declare @Group 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]')
 
Select a.Itemcode,a.Warehouse ,I1.frgnName as 'Part No.' ,max(a.Dscription),sum(a.[Opening Balance]) as [Opening Balance],
  sum(a.[IN]) as [IN],sum(a.OUT) as OUT,((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing
from(Select N1.Itemcode, N1.Warehouse, N1.Dscription,(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.TransType IN (13, 14, 18, 19)
  Group By N1.ItemCode,N1.Dscription, N1.Warehouse

  Union All
  select N1.Itemcode, N1.Warehouse, N1.Dscription,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.TransType IN (13, 14, 18, 19)
  Group By N1.ItemCode,N1.Dscription, N1.Warehouse
 
  Union All
  select N1.Itemcode, N1.Warehouse,N1.Dscription,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.TransType IN (13, 14, 18, 19)
  Group By N1.ItemCode,N1.Dscription, N1.Warehouse) a, dbo.OITM I1

where
a.ItemCode=I1.ItemCode and
I1.ItmsGrpCod = @Group and a.Warehouse = 'BLR-SAL' and I1.FirmCode = 26
Group By
a.Itemcode,I1.FrgnName,a.Warehouse
Having sum(a.[Opening Balance]) + sum(a.[IN]) + sum(a.OUT) > 0
Order By a.Itemcode

but it shows only 10-15 line item.

if wrong please correct to show all items.

Former Member
0 Kudos

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  TransType IN (13, 14, 18, 19)

                  AND 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  TransType IN (13, 14, 18, 19)

                  AND 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  TransType IN (13, 14, 18, 19)

                  AND 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

ORDER BY

       a.Itemcode

KennedyT21
Active Contributor
0 Kudos

Remove the condition in the where

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  TransType IN (13, 14, 18, 19)

                  AND 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  TransType IN (13, 14, 18, 19)

                  AND 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  TransType IN (13, 14, 18, 19)

                  AND 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

ORDER BY

       a.Itemcode

Regards

Kennedy

Johan_H
Active Contributor
0 Kudos

Hi Ketaki,

It could probably be done with a couple of queries, but they would be quite complicated.

In addition you could look into building a reporting tool of your own with MS SQL Server and/or a windows application.

Regards,

Johan

Former Member
0 Kudos

can anybody provide me query for that?

Former Member
0 Kudos

Hi,

You can use the Inventory Posting List and in the expanded, select the documents for which you want to see the postings.

Thanks,

Joseph