Best way to get part cost for specific PartBin

Epicor ERP 10
1

I have a BAQ that creates the Inventory Value of QOH, with the GL acct used for that inventory

I originally just used the Part.CostMethod to determine which fields of PartCost to sum up to get the unit cost.

But this fails when different sites use different part cost methods. It’s not that we use different methods per site (all sites use AVG), but that some get inadvertently set to a different cost method.

So I tweaked my BAQ to make it pull the cost related to the plant the Bin belongs to. This required adding tables 9 and 10 and the realtion ships circled in red (in the pict below).

image
image854×394 24.9 KB

But this slows the query significantly. Which I’m guessing is mostly do to that loop of relationships between tables 1, 9, & 10.

Is there a more efficient way to get the partcost associated with a specific warehouse bin?

Luckily we have only one cost file…

2

Instead of joining tables 1 and 10, set criteria on table 10 to equal the table 1 values. This should improve the speed.

1 Like
3

@ckrusen this does not help you now, I like to setup the plantid using numbers to avoid case senativty issues on material transactions. I also make the costid for each plant match the plantid.

Hopefully this helps someone in the future.

1 Like
4

I ve got similat query, and i ve got PartBin -> PlantWhse -> PartCost… to get the the cost by siteID

1 Like
5

Ah-Ha!!! I often overlook PlantWhse!