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 comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Jun 04, 2014 at 12:12 PM

    Hello

    Please try Below Links

    Thanks & regards

    Pankaj Jha

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 04, 2014 at 12:06 PM

    Hi,

    Please check Standard Inventory Reports .

    Thanks

    TAruna

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 04, 2014 at 12:14 PM

    Hi,

    plz check  these links :

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    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 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

  • 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 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