Calculations with multiple constraints

Thanks for getting back to me! Here is the sample data.


NOTE: Output_code=Stockcode and Description=Description_2 are same columnsfrom Product table. Names changed as 1 code&Description belongs to the Finished Product (HVT1KG), while the 2nd one belongs to the product components (Labour, Cheese etc)

1. HDR is just a table where Finished Product is assigned a Billcode, so its easier to define the Finished Product components, without referenceing the same Product table in the process.

2. Finished Product.Standardcost is calculated by multiplying each ingridient's  Product.Standardcost by LINES.Quantity

3. The snapshot is basically the LINES table (Without Stdcost and WAC)

4. WAC is only applicable to a handfull of Products, the rest uses standard. (i.e for  HVT1KG all components cost to be calculated based on standard, EXCEPT For CHSRR, the cost of which will depend n the date pf the transsaction, as WAAC changes monthly.

4. WAC SUPP is a table to avoide many to many relationship between LINES and WAC, as neither have unique values:(one LINES.Stockcode can be a component in many finished products and one WACCostTable.BOMStockCode appears many times in the table as Wac cost changes monthly.

Requirement.

We have Sales fact table, which is connected to Product table.

IF Finished Sold Product has a component, which is listed in the WAC table, use the cost from WAC table, instead of Standard cost.
WAC will depend on the Product Stockcode and the date of the transacation. 

I hope this makes it clearer.
Thanks for your time.