Hi All,

What is the session of GRN and table name of GRN data?

Plz Help...
Thanks...

If by GRN you mean Goods Received Note, please check this:-

Session :- whinh3412m100

Table :- whinh210 , whinh310 , whinh312

Hi All,

Can anyone give me exact SQL query to calculate 'cost of material consumed' based on GRN tables i.e. how can i calculate 'Opening stock + purchases(GRN) - Closing Stock'?

Please Help...

Hi,

Please specify what exactly do you want, what are your input fields, have you started writing your code?

Values like Opening , Closing etc can be obtained from table whinr110.

Like for opening balance,

select whinr110.qhnd,
whinr110.item
from whinr110
where whinr110.trdt < {:trdt.f} }| trdt.f is the date of which you want the opening balance
and whinr110.item = {:item.f} | item.f is the item of which you want the opening balance
selectdo
endselect

opening=whinr110.qhnd | variable opening now has the opening stock of item.f

In this way, you can calculate the recieved quantity, issued quantity and then calculate your closing.

I suggest you to design your form, design your report layout and start writing your code. If you get stuck, post your problems here.

All the best.

Thanks for reply.
I want to display month wise value of 'Cost of Material Consumption' in P & L Report.

You can take the Receipt No & Receipt Line fields from table whinr110 and check
the whinh312 table with the two fields.

In whinh312, you will find Order details, and according to your requirement, you have to fetch the related tables to calculate the prices.

e.g.

select whinr110.*
from whinr110
where whinr110.trdt inrange {:date.f} and {date.t}
selectdo
select whinh312.*
from whinh312
where whinh312._index1 = {:whinr110.rcno,:whinr110.rcln}
selectdo
| Here call a function to get order prices e.g. if your order origin is purchase, you can get the prices from tdpur401
endselect
endselect

Every organization have their own requirement, accordingly you need to design your code.