Work in Progress Report

[peddinti_pratap]

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

[kothandaraman_nagarajan]

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

[peddinti_pratap]

hi Nagarajan,

thanks for your reply,

it is showing only Opening and closing qty

Thanks&regards,

P.Pratap

[Former Member]

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

[Former Member]

Hi Pratap

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

Cheers

Sandeep

[peddinti_pratap]

hi all,

i have One Warehouse for Work in Progress ie GWH. i transferred stock from stores ware house(01,EWH) to WIP warehouse(GWH).

I need report like  Opening  Qty,Receipt Qty,Issue Qty and Closing Qty

Note:i need details for  GWH warehouse only.

Thanks&Regards,

P.Pratap

[Former Member]

Hi Pratap,

One way how you can do this is Monitoring the Movement types 261,262,101 and 102.

If you could check 261 movements which all have reported to GWH, you will get the total Inventory under production.

Same way if you could get 102 movements this would show you the total inventory reversed to GWH after receipt as FG, which could also be considered as WIP Inventory.

Now 262 will get you the the total GWH inventory(WIP) that is reversed from GI to production Order, thus which need to be excluded from GWH.

And same way 101 will give you the total inventory turned to be FG, which is not any more WIP inventory.

So that you Total GWH-Work In Progress Inventory will be

WIP = (261 +102) - (262+101)

Hope this would help you.

Regards,

Sankar

[Former Member]

Hi Pratap

There is a Standard Crystal Report in B1 Which Gives more or less the data you want "Stock Turnover Analysis" under Inventory Report.

Less you have to write a Query or Crystal Report

Cheers

Sandeep

[Former Member]

Hi Pratap,

Please check below query.

You need to modify according to your requirement.

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

[peddinti_pratap]

hi Atul Chakraborty,

it is showing error like must declare the scalar valuable"@whse"

thanks&regards,

P.Pratap

[Former Member]

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

[Former Member]

Hi,

plz check  these links :

[Former Member]

Hello

Please try Below Links

Thanks & regards

Pankaj Jha

[Former Member]

Hi,

Please check Standard Inventory Reports .

Thanks

TAruna