Interesting question, especially thinking about thousands of parts.
I’ve used Project to include material costs, but only on small quantities like PC Servers.
Project will allow you to group material resources if you create/use custom fields used for grouping. For example, one custom field might be the highest level grouping (Pipe, Paving Stones, Electrical), a second custom field could be the next level grouping (UPVC Pipe, Copper pipe, etc.) , and so on using as many custom fields as necessary. The benefit of this is that the parts could be grouped/sorted/filtered on any of the codes. You can also create a single multi-level drop down list that would essentially have ALL the potential values and use that one field to categorize each material resource. This approach may have less grouping/sorting/filtering capabilities.
Unfortunately, in both these scenarios your resource sheet (pool) would still need to have individual rows for every material resource. Why? Because each material resource would have different Cost Per use values, Material Labels, and so on.
To avoid having to create the entire parts list in every schedule, you could potentially create one parts list and use that as a shared resource pool across many schedules. But I still don’t think this will get you where you want to go.
But let me ask this, is there a reason the material resources have to be at the smallest part level? Could they be at an assembly level? For example, the Filter System.
Basically Project is a scheduling tool and as such you can attach material resources to points in the schedule and project will include those material resources as part of the project cost. Project can even be used to forecast when those materials will be needed. However, the down side, each material resource must be a separate row in the Resource Sheet.
From what I’ve seen and experimented with, Project is not a parts/sub-assembly costing tool and if you’re trying to use it as such, I think the net result will be way more complicated than you want and not provide the benefits you are searching for.
With all that said, I’d suggest you experiment with custom fields to see if these will provide the needed benefits. My gut feeling is that it will end up being more work than benefit if you try to do this in Project but not knowing all the nuances of what you are looking for, I’d start there.