Inventory Valuation report customization

mkpjsr

Hi all,
I want to customize the inventory Valuation report (1001) to have one more field "Unit Cost". This unit cost should be take the rate from the last purchase invoice of that item within that month. For eg. if I will give date filter as 01-Apr-2010..30-Apr-2010 then it it should give me the rate from the given date and not the recent rate.
So I have written the below mentioned code to fetch the last rate but how and where i should write the date filter to get the rate from the entered date filter.

StartDate->Date  // value is comming from date filter
EndDate->Date  // value is comming from date filter
rate ->Decimal

PurchInvLine.RESET;
PurchInvLine.SETRANGE("No.","Value Entry"."Item No.");
//PurchInvLine.SETRANGE("Posting Date",Startdate,EndDate);
PurchInvLine.SETFILTER(Quantity,'>0');
IF PurchInvLine.FINDLAST THEN
Rate := PurchInvLine."Unit Cost";

This filter is not working

PurchInvLine.SETRANGE("Posting Date",Startdate,EndDate);

can anybody tell me where to put the date filter

ssingla

The code posted by you have the line commented. How will it work?

mkpjsr

ssingla wrote:

The code posted by you have the line commented. How will it work?

I have commented that line because there was no effect of that line. I am getting the same result with or without that line of code.

vijay_g

it's caused of data in "Purch. Inv Line" could have placed only between startdate and enddate so you are not getting any difference by comment or noncomment this line.
Do you know what does means of this line?

mkpjsr

vijay_g wrote:

Do you know what does means of this line?

As far as I know, it will set a filter for the given date range. And it will show Rate from those transactions only between the date range.
In my requirement if there is no transaction for a particular item between that range then it should take the rate from its previous month's transaction.

ssingla

mkpjsr wrote:

As far as I know, it will set a filter for the given date range. And it will show Rate from those transactions only between the date range.
In my requirement if there is no transaction for a particular item between that range then it should take the rate from its previous month's transaction.

Then you should use '0D' insteat of StartDate.
Why Can't you use Last Direct Cost on the Item card?

mkpjsr

ssingla wrote:

Then you should use '0D' insteat of StartDate.
Why Can't you use Last Direct Cost on the Item card?

what will happen, if i want to see the report of past date (say 1 year or 2 year back), it will always show me the current Unit Cost...

ssingla

Though I never had doubt but being a novice technical I decided to test it and found it working.
Here's the code I used

PurchInvLine.SETCURRENTKEY(Type,"No.");
PurchInvLine.SETRANGE(Type,PurchInvLine.Type::Item);
PurchInvLine.SETRANGE("No.","Value Entry"."Item No.");
if Item.GETFILTER("Location Filter") <> '' then
  PurchInvLine.SETFILTER(PurchInvLine."Location Code",Item.GETFILTER("Location Filter"));
PurchInvLine.SETRANGE("Posting Date",StartDate,EndDate);
IF PurchInvLine.FINDLAST THEN
  ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity (Base)"
ELSE
  ItemRate:=0;

P.S. The key can be further optimized.

mkpjsr

ssingla wrote:

Though I never had doubt but being a novice technical I decided to test it and found it working.
Here's the code I used

PurchInvLine.SETCURRENTKEY(Type,"No.");
PurchInvLine.SETRANGE(Type,PurchInvLine.Type::Item);
PurchInvLine.SETRANGE("No.","Value Entry"."Item No.");
if Item.GETFILTER("Location Filter") <> '' then
  PurchInvLine.SETFILTER(PurchInvLine."Location Code",Item.GETFILTER("Location Filter"));
PurchInvLine.SETRANGE("Posting Date",StartDate,EndDate);
IF PurchInvLine.FINDLAST THEN
  ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity (Base)"
ELSE
  ItemRate:=0;

P.S. The key can be further optimized.

hi thanx for the reply but in your code why you have written

ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity

why r u multiplying the Quantity with Quantity (Base)

PurchInvLine.Quantity*PurchInvLine."Quantity

Also, if there is no transaction for an item in the given month then it will show the rate as 0 but i want to find the Rate for that item as well from the previous transactions available.

for eg. if there is no transaction for item say "item 1" between 01-04-2010 and 30-04-2010 then the report should search of the rate between 01-03-2010 and 30-03-2010 if not found then again it should go search between 01-02-2010 and 30-02-2010 and so on.

so, should i put the same filter in else part by subtracting Startdate and EndDate by one month but if there is no record then how will it go to the previous month again.

kindly suggest.

vijay_g

try it..

IF PurchInvLine.FINDLAST THEN
  ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity (Base)"
ELSE begin
newPurchInvLine.SETRANGE("Posting Date",0D,StartDate-1);
IF PurchInvLine.FINDLAST THEN
  ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity (Base)"
end;

and this code has been written to achieve "Direct Unit Cost" based on "base unit of measure"

ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity

mkpjsr

vijay_g wrote:

try it..

IF PurchInvLine.FINDLAST THEN
  ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity (Base)"
ELSE begin
newPurchInvLine.SETRANGE("Posting Date",0D,StartDate-1);
IF PurchInvLine.FINDLAST THEN
  ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity (Base)"
end;

and this code has been written to achieve "Direct Unit Cost" based on "base unit of measure"

ItemRate:=PurchInvLine."Direct Unit Cost"/PurchInvLine.Quantity*PurchInvLine."Quantity

thanx a lot..

But still I am confused how PurchInvLine.Quantity*PurchInvLine."Quantity (base) will work because both these fields contain the same value.
for eg.. If value of QTY is 10, it will become 100 before dividing with "Direct Unit Cost"...

vijay_g

The concept of Base quantity field is associated with "Base unit of measure" in item card.
let's see

base UOM = KG
UOM = QTL

if you select QTL in unit of measure in line then your Quantity is 1 in line and base Quantity will be 100.
in your case both comes same cause of you have selected base unit of measure in line.

hope now it's clear....!