Skip to Content
author's profile photo Former Member
Former Member

Stock Movements report between selected Dates and Itemgroup criteria

Hi Everyone,

I have been struggling to create a query on action in SAP Business One to show a stock movements report with "Opening Balances, Purchases, Goods In adjustments, Sales, Goods Out adjustments and Closing Balances" as columns per each item under a selected itemgroup.

I tried a couple of attached queries, but it just brings a blank screen without even displaying any errors. Can anyone help me on this ASAP.

Thanks

Raahat

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jan 23, 2014 at 01:01 AM

    Hi,

    Try second query, its working for me:

    Declare @fromDate Datetime

    Declare @ToDate Datetime

    set @FromDate =

    (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')

    set @ToDate =

    (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')

    select * from

    (

    SELECT T0.itemcode,

    min(T0.Dscription) as 'Item Description',

    min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,

    (isnull((

    Select sum(isnull(inqty,0))

    from OINM O1

    where O1.itemcode=T1.itemcode

    and O1.Warehouse=W1.Whscode

    and O1.docdate<@FromDate ),0)-

    isnull((

    Select sum(isnull(outqty,0))

    from OINM O1

    where O1.itemcode=T1.itemcode

    and O1.Warehouse=W1.Whscode

    and O1.docdate<@FromDate),0)

    ) as [Opening Stock],

    isnull((

    Select sum(isnull(inqty,0))

    from OINM O1

    where O1.itemcode=T1.itemcode

    and O1.Warehouse=W1.Whscode

    and O1.docdate>=@FromDate

    and O1.docdate<=@ToDate and O1.inqty>0

    and O1.transtype in (20,18)),0

    ) as [Purchase Quantity],

    isnull((

    Select sum(isnull(outqty,0))

    from OINM O1

    where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

    and O1.docdate>=@FromDate and O1.docdate<=@ToDate

    and O1.outqty>0 and O1.transtype in (21,19)),0

    ) as [Purchase Return Quantity],

    isnull((

    Select sum(isnull(outqty,0))

    from OINM O1

    where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

    and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0

    and O1.transtype in (13,15)),0

    ) as [sale Quatity],

    (isnull

    ((

    Select sum(isnull(inqty,0))

    from OINM O1

    where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

    and O1.docdate<=@ToDate),0

    )-

    isnull((

    Select sum(isnull(outqty,0))

    from OINM O1

    where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

    and O1.docdate<=@ToDate),0)

    ) as [Closing Stock]

    FROM OINM T0

    INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

    INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

    INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod

    INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode

    INNER JOIN OLCT C1 ON W1.Location=C1.Code

    Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location

    ) a

    where (a.[Opening Stock]

    +a.[Purchase Quantity]

    + a.[Purchase Return Quantity]

    +a.[sale Quatity]+a.[Closing Stock]

    ) !=0

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.