I've managed to change the cost nature for freight to use a formula rather than a flat uplift, and to then calculate based on a figure I put into a custom field in the BPSUPPLIER table.

That works when the receipt is for the FULL PO QUANTITY.

However, partial receipts use the figure calculated for the FULL PO QUANTITY (unsuprising since that is what I used on my formula).

However when I try to calculate the formula using QTY_0 and NETPRI_0 from the PRECEIPTD table instead of PORDERQ and PORDERP, I get an error reported on my costing formula.

Any ideas?

Hi Jules, would you mind posting the full details of the formula you are trying to use and the error.

David

HI David

Thanks for the reply.

My formula the current status is 

([F:PTD]NETPRI*[F:PTH]CHGCOE*[F:PTD]QTYSTU)*([F:BPS]ZFREIGHTBAND/100)

which fails to put any costs at all onto the Receipt, and causes the Application Error message

"Formula FRE : Problem in the calculation formula of a cost belonging to a cost structure"

when I attempt to recalculate the costs on the Purchase Order (actually, when I try to save the PO record; it accepts my attempt to recalculate without any error message)/

Prior to that I was using 

([F:POP]NETPRI*[F:POH]CHGCOE*[F:POQ]QTYSTU)*([F:BPS]ZFREIGHTBAND/100)

which correctly calculates the cost on the purchase order, but then applies the full PO cost to each Receipt rather than the pro-rata amount based on the receipt quantity. It works on the Receipt also, but only when it's for the full PO quantity. 

We get partial receipts quite commonly, which is why we need a formula that works for them.

In both cases, ZFREIGHTBAND is a custom field I have added to the BPSUPPLIER table to hold a MD1 data type variable to hold the freight uplift for that supplier, so that we can increase the landed costs by that (as a percentage of the line price, ideally of the receipt). Again, it all works fine when I apply it to the PO line quantity and price values (and the PO header exchange rate) but fails when I try to do the same for the Receipt.

Hope that helps

Thanks

Jules

Hi Jules,

I did a test and agree it errors when using the receipt tables in the formula. I have looked at the training document and this does agree with this as the following is an exert explaining what tables are possible to use in the formula: Define a formula using the item, supplier, and context table (ITM, ITP, POQ, POP, POH, POC, BPS, and PPK). 

I did another quick test using a COST set to use cost per unit.  That worked as that cost was taken per unit receipted.

Other than that I recommend discussing the details with you Business Partner as to best achieve what you need

David

Hi David

Hmm, ok. Thanks for your help; that gives me some ideas to explore further.

Can I please ask you to clarify what you mean by this?

"I did another quick test using a COST set to use cost per unit.  That worked as that cost was taken per unit receipted."

Are you saying you set up a new Cost with a Product Cost Calculation set to Amount per unit the, in the Cost Structure, you have called that up with a unit rate (say, $1 per 10 x Units) and the calculation works based on the receipt Qty?

It's interesting that can use the receipt quantity but the formula cannot. I am now wondering whether some combination or calculation of the POC and POQ receipt quantity field might not work. 

I have asked our reseller the question but I know our consultant is very busy - he's the one that suggested I could use formulas  - and this is at the limit of what he knows. I suspect we may need to ask them for some development work to achieve what we need (which is the ability to use the Receipt tables in a cost formula)..Hopefully it won't be too much of a stretch...

I'll let you know how I get on

Thanks again

Jules

Hi Jules, 

This was the test. I used COST008 in SEED (Product Cost Calculation set to Amount per unit)

In my Cost Structure I added COST008 with a value of 8 GBP

I raised the PO for 10 at £2 (the supplier currency was GBP) the Direct cost total was £80 on the PO.

I partially receipted 3 and the Direct cost total was £24 (3 X 8)

If that is what you want then this should work.

Regards

David

Hi David

No, not quite. In our industry we pay for shipping from the far East and not our suppliers (it's cheaper that way) but we want to reflect that in the landed costs of stock (so we can compare profit margins for similar products bought from different suppliers more fairly. Also, we want to provision for the likely freight costs in our accounts so that when our carriers invoice us we have the money set aside (as it were). 

Some of this varies by supplier, and some by product. The product-specific element is Duty and that's easily handled - we have a cost structure element as a % of receipt value set to show the import commodity codes (duty rates) which has been working well, despite using over 300 different cost structures (reflecting our mix of products and the granularity of the HMRC commodity coding). 

But the supplier specific element is the freight, which is tricky to do at the product-supplier (i.e. the cost structure) level because it leads to a 2D array of duty and freight rates. That leads to complexity and the risk of getting it wrong (particularly with Brexit coming, when all imports will be subject to duty and not just non-EU ones.

We were hoping to use our custom supplier field in a formula, but because the system only allows us to use PO table fields for the calculation, we're not usefully going to be able to run this using a formula and will have to:

1. use the 2D array of frieght rates vs dury rates i.e. we would set up as many cost structures for EACH commodity code as we have freight uplift percentages - in theory, if we use 5 different freight percentages, we would need 1500+ cost structures -  and select the right ones for EACH ITMBPS record. Good job I have nothing else to do *ahem*
2. Put in a change request to our reseller see if we can get a code change to allow the cost structure formula to reference receipt tables and still correctly calculate landed costs. 

Thank you so much for your help, though. 

Best Regards

Jules