How to find the last (recent) purchase unit price ofany item

mkpjsr · 2010-06-14

Hi all,

I want to find out the latest purchase unit price of any item from "Value Entry" table on a report having "Item" and "Value Entry" data item.

Can anybody guide me.

matttrax · 2010-06-14

You don't even need the Value Entry table, although it is indirectly used. Just find the last Item Ledger Entry with Entry Type = Purchase. Do a CALCFIELDS on Cost Amount (Actual) and divide it by the quantity.

mkpjsr · 2010-06-14

matttrax wrote:

Just find the last Item Ledger Entry with Entry Type = Purchase.

that is what i wanted to know, how to find the last entry...

SLF25 · 2010-06-14

that is what i wanted to know, how to find the last entry...

http://www.mibuso.com/howtoinfo.asp?FileID=22

Search for FINDLAST

ufuk · 2010-06-14

mkpjsr wrote:

that is what i wanted to know, how to find the last entry...

It depends on your case. If latest entry no is enough then you can directly find last record with the primary key. If latest posting date is your criteria then you have to set related key (ex: posting date) to find the last record. Be aware that a record having last entry no do not have to be the record having latest posting date.

mkpjsr · 2010-06-16

ufuk wrote:

mkpjsr wrote:

that is what i wanted to know, how to find the last entry...

It depends on your case. If latest entry no is enough then you can directly find last record with the primary key. If latest posting date is your criteria then you have to set related key (ex: posting date) to find the last record. Be aware that a record having last entry no do not have to be the record having latest posting date.

In my case i have to find the latest posting date where Item Ledger Entry Type is Purchase, then what i should do, i have written the following code but getting Rate as 0

Rate-> decimal

ValueEntry.RESET;
ValueEntry.SETFILTER(ValueEntry."Item Ledger Entry Type",'%1',ValueEntry."Item Ledger Entry Type"::Purchase);
ValueEntry.SETRANGE(ValueEntry."Item No.",ValueEntry."Item No.");
IF ValueEntry.FINDLAST THEN
  ValueEntry.CALCFIELDS(ValueEntry."Cost Amount (Actual)");
  if ValueEntry."Invoiced Quantity">0 then
  Rate:=ValueEntry."Cost Amount (Actual)"/ValueEntry."Invoiced Quantity";

Alex_Chow · 2010-06-16

matttrax wrote:

You don't even need the Value Entry table, although it is indirectly used. Just find the last Item Ledger Entry with Entry Type = Purchase. Do a CALCFIELDS on Cost Amount (Actual) and divide it by the quantity.

The item ledger cost amount (actual) includes any landed cost (item charges). So it does not reflect the actual cost the vendor invoiced you at.

ufuk · 2010-06-16

Costing method affects the way value entry records created. For ex: if you use standard method then the variance of cost is inserted as an other record so you have to be careful when getting cost from value entry. You can use both tables by filtering the appropriate fields but basically you can use Item Ledger Entry Cost Amount (Actual) field which is already referencing Value Entry Cost Amount (Actual) field.

You can modify code like:

ItemLedgEntry.RESET;
ItemLedgEntry.SETCURRENKEY("Item No.","Entry Type")
ItemLedgEntry.SETRANGE("Item No.",pItemNo); // pass as parameter
ItemLedgEntry.SETRANGE("Entry Type",ItemLedgEntry."Entry Type"::Purchase);
ItemLedgEntry.SETFILTER(Invoiced Quantity,'>0'); //exclude returns
IF ItemLedgEntry.FINDLAST THEN BEGIN
  ItemLedgEntry.CALCFIELDS("Cost Amount (Actual)");
  Rate := ItemLedgEntry."Cost Amount (Actual)"/ItemLedgEntry."Invoiced Quantity";
END;

Alex_Chow · 2010-06-16

Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

The easist way is to get the last purchase invoice line record.

ufuk · 2010-06-17

Alex Chow wrote:

Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

The easist way is to get the last purchase invoice line record.

You're right. If you want to get the price on invoice, invoice line is the best place. But he said he wanted to get it from Value Entry meaning like want to get all acquisition cost. As you said this cost includes charges and also variances of standard cost.

mkpjsr · 2010-06-17

Alex Chow wrote:

Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

The easist way is to get the last purchase invoice line record.

yes, i wrote the following code and my problem is resolved::

PurchInvLine.RESET;
PurchInvLine.SETRANGE("No.","Value Entry"."Item No."); // pass as parameter
PurchInvLine.SETFILTER(Quantity,'>0'); //exclude returns
IF PurchInvLine.FINDLAST THEN
BEGIN
IF PurchInvLine."Tax Group Code"='CST%' THEN
Rate := (PurchInvLine."Amount To Vendor"+PurchInvLine."Tax Amount"+PurchInvLine."Charges To Vendor")/ PurchInvLine.Quantity
ELSE
Rate := (PurchInvLine."Amount To Vendor"+PurchInvLine."Charges To Vendor")/ PurchInvLine.Quantity;
END;

matttrax · 2010-06-17

Alex Chow wrote:

Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

This is true. It is the sum of all value entries related to the item ledger entry, so item charges, revaluation journals, etc.

If you want to get the last price you paid for something, as the title suggests, then you should look at the purchase invoice line or value entry (I prefer always getting monetary amounts from ledger tables like value entry myself). If you just want the cost of the item do the CALCFIELDS on Item Ledger Entry.

Alex_Chow · 2010-06-17

matttrax wrote:

If you just want the cost of the item tier do the CALCFIELDS on Item Ledger Entry for that costing tier.

Corrected.

Andwian · 2010-06-18

What about just using the Last Direct Cost field on Item data Item?

Alex_Chow · 2010-06-18

Andwian wrote:

What about just using the Last Direct Cost field on Item data Item?

This can be changed by positive adjustments.

Andwian · 2010-06-19

Alex Chow wrote:

Andwian wrote:

What about just using the Last Direct Cost field on Item data Item?

This can be changed by positive adjustments.

Ah yes, you were right, Alex!

Howdy, Stranger!

Quick Links

Categories

How to find the last (recent) purchase unit price ofany item

Answers