We are starting to use Landed costs. I would like to make a GI that will show all inventory availability and the total cost (purchase receipt and landed cost (if there is a landed cost for the purchase receipt).
Similar to how the inventory lot/serial history works. it will show receipt cost, then if you check the box it will so the cost with landed cost included. I would like that cost with landed cost included.
Does anyone know the best way to get that total cost?
Best answer by livl
Hi!
I have a GI here that does calculate based on the purchase receipts. We use the acu-container add on that splits up the the tariffs and customs duty for us. we also use “COST” as an allocation variable for all of our landed cost codes so its relatively simple math to figure out the proportion of landed costs to apply to each item. the ONLY issue is finding the final landed cost since the way my tables are joined. I have to “SUM” all of the landed cost amounts. however i am using “min” to find the unit costs and the tariff amounts. since i cant sum and min at the same time,
@mna10 The Average Cost on Price/Cost tab of the Stock Item screen should have all of the calculations done and rolled into it.
If you are looking for something historical reference, i.e., what was the landed cost for an item for a specific purchase receipt, I don’t have anything and would love the same thing for our company.
The problem I run into when to use the landed cost amount vs the billed amount and the creating a system that “knows” how landed cost valuation method. Then, there is still the challenge of linking everything together.
hi @abrunner78 , thanks but we are using lots and looking to get the true cost per lot (PO receipt cost + landed cost). this shows on the inventory lot/serial history screen but looking to show in a GI instead.
As someone working on a similar GI, I recommend starting with the following tables:
POLandedCostReceiptLine - this has the allocated LC amount
POReceiptLine - to bring the PO receipt details, like inventoryID etc.
InventoryItem - to bring any inventory ID details that aren’t available in the POReceiptLine table.
CurrencyInfo - to link the exchange rate of applicable transactions if you are purchasing in different currencies from your base currency.
I used formulas to calculate the unit cost column and grouped by POReceiptLine.InventoryID & POReceipt.ReceiptNbr so that the landed cost column would show a total of all LC allocated per InventoryID & PO Receipt. Other tables that you may find useful are INItemCost & POReceiptLandedCostDetail.
@mna10 Here you go. There are some extra tables, as I am still tweaking a few things. 😄 Please share any ideas you may have. We are moving to lot/serial number tracking soon, so any discoveries you make would be extremely helpful.
Hi @reneed Thanks! some of our items were done via receipts vs POreceipts being we were fixing valuation methods. These are the ones we are having an issue with for the landed costs.
@mna10 Maybe you could try bringing in the INRegister table, since it contains the child DAC POReceipt. This way you could have the GI show both receipts and POreceipts. This might be effective for you, especially if the receipts contain the final item cost.
I have a GI here that does calculate based on the purchase receipts. We use the acu-container add on that splits up the the tariffs and customs duty for us. we also use “COST” as an allocation variable for all of our landed cost codes so its relatively simple math to figure out the proportion of landed costs to apply to each item. the ONLY issue is finding the final landed cost since the way my tables are joined. I have to “SUM” all of the landed cost amounts. however i am using “min” to find the unit costs and the tariff amounts. since i cant sum and min at the same time,
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.