Skip to Content

Work in Progress Report

Hi All,

How to write query for Work in Progress ie item wise Opening Qty,Receipts,issues and Closing Qty.

Kindly help me on this report.

Thanks&report,

P.Pratap

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jun 04, 2014 at 12:12 PM

    Hello

    Please try Below Links

    Thanks & regards

    Pankaj Jha

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 04, 2014 at 12:06 PM

    Hi,

    Please check Standard Inventory Reports .

    Thanks

    TAruna

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 04, 2014 at 12:14 PM

    Hi,

    plz check these links :

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 05, 2014 at 02:45 AM

    Hi Pratap

    to Create WIP Report you will find all the details you need in OINM Table

    Cheers

    Sandeep

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member P Pratap

      Hi Pratab,

      This Query is for Stored Procedure.

      You need to Declare 3 Variable.

      1. @whse

      2. @todate

      3. @fromdate

      For Query Checking replace @whse with your warehouse code and also change @todate and @fromdate

      --

      --

      Regards:::::

      Atul Chakraborty

  • Posted on Jun 05, 2014 at 08:57 AM

    Hi,

    Try this:

    Declare @fromdate as datetime
    Declare @Todate as datetime
    Declare @whse as nvarchar(25)
    set @fromdate = ( select min(Ta.[DocDate]) from OINM ta where Ta.[DocDate] >= [%0])
    set @Todate = ( select max(Tb.[DocDate]) from OINM tb where (Tb.[DocDate]) <= [%1])
    set @whse = (select max(Tc.[Warehouse]) from OINM tC where Tc.[Warehouse] = [%2])

    Select distinct(owhs.WhsName), a.ItemCode, a.Dscription,

    (select sum(InQty-OutQty) from OINM where oinm.itemcode=a.itemcode and

    OINM.Warehouse = @whse and oinm.DocDate<@fromdate) as 'Opening Quantity',

    (select sum(InQty) from OINM where oinm.itemcode=a.itemcode and

    OINM.Warehouse = @whse and oinm.DocDate >= @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (18, 20)) as 'Inward Quantity',

    (select sum(InQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

    oinm.DocDate = @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (67)) as 'Transfered In Quantity',

    (select sum(OutQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

    oinm.DocDate = @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (13,15)) as 'Sales Quantity',

    (select sum(OutQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

    oinm.DocDate = @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (67)) as 'Transfer Out Quantity',

    (select sum(InQty-OutQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

    oinm.DocDate<= @todate)as 'Closing Quantity',

    (select (sum(InQty-OutQty)* (Select Sum(AvgPrice) from OITW where a.ItemCode = OITW.ItemCode and

    OITW.WhsCode = @whse)) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

    oinm.DocDate<= @fromdate ) as 'Closing Value',

    (Select SUM((OINM.OutQty) * (OINM.Price)) From OINM where oinm.itemcode=a.itemcode and

    OINM.Warehouse = @whse and oinm.DocDate>= @todate and oinm.DocDate<= @fromdate and

    OINM.TransType IN (13,15)) as 'Sale Value'

    from oinm a inner join OWHS on a.Warehouse=OWHS.WhsCode

    where a.Warehouse=@whse

    group by a.ItemCode,a.Dscription,owhs.WhsName

    Order By a.ItemCode

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member P Pratap

      Hi Pratap,

      Please try below Query.

      DECLARE @whse AS NVARCHAR (200)

      DECLARE @todate AS DATE

      DECLARE @fromdate AS DATE

      set @fromdate = ( select min(Ta.[DocDate]) from OINM ta where Ta.[DocDate] >= [%0])

      set @todate = ( select max(Tb.[DocDate]) from OINM tb where (Tb.[DocDate]) <= [%1])

      set @whse = (select max(Tc.[Warehouse]) from OINM tC where Tc.[Warehouse] = [%2])

      Select distinct(owhs.WhsName), a.ItemCode, a.Dscription,

      (select sum(InQty-OutQty) from OINM where oinm.itemcode=a.itemcode and

      OINM.Warehouse = @whse and oinm.DocDate<@fromdate) as 'Opening Quantity',

      (select sum(InQty) from OINM where oinm.itemcode=a.itemcode and

      OINM.Warehouse = @whse and oinm.DocDate >= @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (18, 20)) as 'Inward Quantity',

      (select sum(InQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

      oinm.DocDate = @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (67)) as 'Transfered In Quantity',

      (select sum(OutQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

      oinm.DocDate = @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (13,15)) as 'Sales Quantity',

      (select sum(OutQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

      oinm.DocDate = @fromdate and OINM.DocDate <= @todate and OINM.TransType IN (67)) as 'Transfer Out Quantity',

      (select sum(InQty-OutQty) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

      oinm.DocDate<= @todate)as 'Closing Quantity',

      (select (sum(InQty-OutQty)* (Select Sum(AvgPrice) from OITW where a.ItemCode = OITW.ItemCode and

      OITW.WhsCode = @whse)) from OINM where oinm.itemcode=a.itemcode and OINM.Warehouse = @whse and

      oinm.DocDate<= @fromdate ) as 'Closing Value',

      (Select SUM((OINM.OutQty) * (OINM.Price)) From OINM where oinm.itemcode=a.itemcode and

      OINM.Warehouse = @whse and oinm.DocDate>= @todate and oinm.DocDate<= @fromdate and

      OINM.TransType IN (13,15)) as 'Sale Value'

      from oinm a inner join OWHS on a.Warehouse=OWHS.WhsCode

      where a.Warehouse=@whse

      group by a.ItemCode,a.Dscription,owhs.WhsName

      Order By a.ItemCode

      Hope this help 😊

      --

      --

      Regards::::

      Atul Chakraborty

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.