You either store a product price directly with the related product – or you setup a PriceLevels table related to Products with prices applicable at different points in time and/or for specific suppliers and/or levels.
This allows you to lookup the correct price for a line item simply by “looking into” the matching PriceLevels record, based on the product foreign key and e.g. the supplier foreign key from the order (or their level).
The product category is an attribute of a product and shouldn't have any impact on the lookup method; in that regard, a product should be just a product, not a “main product” or an “accessory”.
Using different Inventory tables and determining the applicable price via Case() gets you on a slippery slope where you will be constantly required to make re-adjustments. If you introduce a new level, or a new category, you must add new table(s) and/or tweak those calculations.
Using the correct data model OTOH simply works®, and will even allow non-developers to add new levels or categories, since they are encoded as data, not buried in the schema.
The prices are determined by the quantity purchased. So someone who purchases and accessory falls into first, third and fifth levels only. and conversely, the main products fall into the first, second, forth, sixth and seventh levels. If you look some of the price levels overlap, like 6-49 falls into the 6-99 level one is the main products like siding and the other is an accessory.
You can translate this list from your screenshot directly into a ProductsPrices table related to Products, holding the price, the min and the max quantity – and AFAICT, ignoring the type of a product and any level altogether.
Then create a relationship between LineItems and ProductsPrices where
LineItems::_fk_productID = ProductsPrices::_fk_productID
LineItems::quantity ≥ ProductsPrices::minQuantity
LineItems::quantity ≤ ProductsPrices::maxQuantity