cancel
Showing results for 
Search instead for 
Did you mean: 

Modify cost calculation method

Former Member
0 Kudos

I understand that in 2004b, B1 only supports FIFO, standard, and moving average. I was wondering if anyone here knows if I can use SDK to modify the cost calcuation by specifying a price. For example..the user creates do GRPO in this order: $10, $15, $20.. and on the AR side, the user would like to issue Sales Delivery by specifying a price..eg: $15. Is this possible by means of SDK? If so where can i get more information on this issue? thanks in advance for all your help.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

David... Thank you! I will give this careful consideration and work to incorporate the non-stock COGM using the price lists and special discounts. I'll need to do a few tests to see which values overwrite others and if some discounts are combined to create a larger discount.

Kind Regards,

Scott

Former Member
0 Kudos

Scott

If its of any interest, I got this to work with an extended version of the query thats in this thread <i>(theres also a mistake Ive spotted)</i>. My aim was to enable a GM calculation for a drop ship warehouse. What happens is that the field is filled with the last purchase price of the item for this customer, so Im sure that you should be able to build it in.

What may be of interest was that I also put a FMS attached to the gross profit window of the marketing document. In here I put a pop up for the user to check the real last invoiced prices. You trigger the FMS with a Shift+F2 and you recieve a list of value, date, PO comment. Works quite nicely and is very user friendly.

Best wishes

Dave

Former Member
0 Kudos

Scott

If its of any interest, I got this to work with an extended version of the query thats in this thread <i>(theres also a mistake Ive spotted)</i>. My aim was to enable a GM calculation for a drop ship warehouse. What happens is that the field is filled with the last purchase price of the item for this customer, so Im sure that you should be able to build it in.

What may be of interest was that I also put a FMS attached to the gross profit window of the marketing document. In here I put a pop up for the user to check the real last invoiced prices. You trigger the FMS with a Shift+F2 and you recieve a list of value, date, PO comment. Works quite nicely and is very user friendly.

Best wishes

Dave

Answers (2)

Answers (2)

Former Member
0 Kudos

David, I loved the wet lettuce comment... You are so right.

My company is going live with SBO this weekend. We still do not have a suitable bit of SDK code written for calculating the "correct" COGM value and need something soon so that our margin calculations are accurate at the time that a Sales Order is prepared. We are using the Moving Average method.

As such, for Inventory that is stocked, we are fine since the Item Master "Price" field is populated by the Inventory Valuation report to be placed in the marketing documents' COGM field.

However, we still need to pull the cost of non-stock items and stocked items with a current quantity of zero. Since we are managing our inventory in Items Groups setup for each of our primary vendors, we have a price list for each. This would be simple enough to pull. However, there are special prices that may be setup for particular BPs, Item Groups, Properties and Manufacturers.

I'm confused at this point how to combine all the possible discounting scenarios. If you have already prepared some code that handles this, I would greatly appreciate a copy. In the near future, we will also consider BOMs; although, we will handle these off-system until we have the BOM items prepared.

Thank you.

Scott

Former Member
0 Kudos

Scott .. good news is its not even SDK. Have included the code at the bottom of the message. For the COGM of non stock items/out of stcok, Im sure you can work out a method for identifying a price and modifying the query that Ive included. Its just a case of defining your business processe and modifying the logic.

Hope this is of some use. Let me know how your getting on..

Dave

PS Caution : In my view B1 is still setup very badly with regard to FMS and fields being viewed in a transaction. Unfortunately to make it work you have to set the field as visible and active. As a result the user can still update this field manually if he wants to.

IF (SELECT T0.InvntItem FROM OITM T0 WHERE T0.ItemCode = $[$38.1.0]) = 'Y' AND $[$38.39.0] <> 'I'

SELECT T0.AvgPrice, $[$38.39.0] FROM OITM T0 WHERE T0.ItemCode = $[$38.1.0] FOR BROWSE

ELSE

SELECT SUM (T4.Quantity * T2.AvgPrice) FROM ITT1 T4 INNER JOIN OITT T1 ON T4.Father = T1.Code INNER JOIN OITM T2 ON T4.Code = T2.ItemCode WHERE T1.TreeType = 'S' AND T4.Father = $[$38.1.0]

Former Member
0 Kudos

if I understand your question correctly you may not even have to use the SDK. In the marketing documents you have the COGM field (this field is not stored in the tables but is used during the processing of the document), you can use a query on this field and it will overwrite the cost of goods calculated via the document settings.

I use this field to get a correct calculation of the margin for a moving average priced system using sales boms.

Please note this is effecting only the margin calculation and not the financial postings.

Dave

FOA
Advisor
Advisor
0 Kudos

Hi Dave,

What do you mean by COGM? Looks like the name of a table more than a field. Where can i see this field? Your approach sounds interesting.

Former Member
0 Kudos

Actually my question is your second point. I would like to be able to enter a cost for the journal posting. I know this cannot be done by B1 itself, but curious to know whether it is possible by sdk?

FOA
Advisor
Advisor
0 Kudos

Hi Alan, why dont you use the JournalEntries and JournalEntries_Lines object? In the properties you will find all the fields of the Journal Entries Form.

Best regards,

Felipe

Former Member
0 Kudos

Go to say the AR invoice document... go to form settings you will see there is a row called COGM. This you will not find in the table as its only used during calculation.

Normally this field is filled with the value defined from the settings e.g. last purchase price etc.

Last purchase price is about as much use as a 'wet lettuce' to a business one system running moving average price.

If you put a query on this field it will override the COGM calculation. My query enters the correct moving average price or the sum of the components for a sales bom (as this is still not fully supported in B1).

Former Member
0 Kudos

Alan

Youve slightly lost me here in your comment, can you clarify.. whats the scenario ?

Dave

Former Member
0 Kudos

Dave,

For example, the follow GRPO were created in this order:

item A, 1 QTY, $10 unit price

item A, 1 QTY, $15 unit price

item A, 1 QTY, $20 unit price.

When issuing AR invoice, suppose I am using FIFO, the JE will credit the stock account $10 because of FIFO.. I would like to know would it be possible to specify a cost, say for example, $15 for this AR invoice. And later when i post a second AR invoice, I can choose from $10 or $20... So in general, I dont' want FIFO, moving average, or standard cost calcuation..I want to be able to specify a cost in any order I want. What you've suggested to me about the COGM field is only for gross profit calculation.

Former Member
0 Kudos

Ive heard this one before... but thats another story..

Straight answer is theres probably no easy way of doing this in a standard way (it also probably not GAAP compliant).

You might be able to do it in the SDK some how with a standard price method, and change the standard price prior to posting, but that would be very messy.

What weve proposed (but still to implement) before as a solution to meet both US GAAP and business requirements is to allow financial postings based on moving average price. Batch manage all stock. On sale of the stock the purchase price is transfered into the COGM field (via the batch number selected). This can still be edited if you wish to change the price (as I guess for you like us this is an exception). Result is financially the system is correct, commerically the sales statistics are correct.

Hope this helps.

Dave