Flatternd BOM list of materials that can have BOMs of BOMs... in sql

Question

I need to get the price per piece of manufactured materials, for that I need the components quantities per materials. It is possible that every material can have components that also have components to the nth degree.

Material
    Child
    Child   
        Child
            Child
            Child
            ...
                ...
        Child
    Child
    ...

Every Child is also a material

I have two tables:

Table 1 is a list of Materials, with the current Version / Variant, and the Batch size for the BOM list

Table 2 has the BOM information and the net / gross quantity's needed based on the Batch size of table 1. Each child in Table 2 can also be a Material in Table 2 with its own children.

The issue is if a child is also a Material with its own BOM then the Child quantity's are based on the Child-Materials batchsize and not on the Parents Batch size. And here is where I struggle with the recursive query to get the right quantity's so I can divide it later correctly to get the quantity's for 1 piece.

As an example "mat3" has a child "mat1" which has its own BOM, to make 1000 pc of mat3 I need 475.40680 of mat1 (gross qty), if I want a BOM of mat3 flattened where mat1 is resolved I need the quantity's based on the 475.40680, but in the Table it is based on the batchsize of table 1 in this case also 1000.

It can also happen that a child BOM material is also already in the parent material, but that can be grouped later on if the qty are correct

and here is where I loose it with my recursive qry tries

Table 1 DATA
Material    Version     Variant     Batch
mat1        0           0           1000
mat2        3           0           5000
mat3        3           0           1000
125A3
138  
21   
33   
63C
31   
20   
....


Table 2 DATA
Material    Version Variant Child           Gross_qty   Net_qty
mat3        3       0       125A3           1010.00000  1000.00000
mat3        3       0       138             1010.00000  1000.00000
mat3        3       0       21              1020.00000  1000.00000
mat3        3       0       33              1030.00000  1000.00000
mat3        3       0       63C             166.86670   166.70000
mat3        3       0       mat1            475.40680   461.56000
mat3        3       0       31              27.66580    26.86000
mat3        3       0       20              1010.00000  1000.00000

mat1        0       0       59              37.10000    37.10000
mat1        0       0       61              50.00000    50.00000
mat1        0       0       106A            8.00000     8.00000
mat1        0       0       23A             8.00000     8.00000
mat1        0       0       17              4.50000     4.50000
mat1        0       0       101             0.90000     0.90000
mat1        0       0       05              890.20000   890.20000
mat1        0       0       95              0.10000     0.10000
mat1        0       0       93              1.20000     1.20000

Recursive qry tries (which also returns the child which is a BOM and should not be there), I also did not filter for version/variant, as my test case does not have that, but that's an easy add

WITH RPL (Material, Child, Gross_qty) AS
   (
      SELECT ROOT.Material, ROOT.Child, ROOT.Gross_qty
       FROM mbm ROOT
       WHERE ROOT.Material = 'mat3'
    UNION ALL
      SELECT PARENT.Material, CHILD.Child, CHILD.Gross_qty
       FROM RPL PARENT, mbm CHILD
       WHERE PARENT.Child = CHILD.Material
   )
SELECT Material, Child, Gross_qty
 FROM RPL
  ORDER BY Material, Child, Gross_qty;

Result:

Material        Child           Gross_qty
mat3            138             1010.00000
mat3            21              1020.00000
mat3            63C             166.86670
mat3            20              1010.00000
mat3            125A3           1010.00000
mat3            33              1030.00000
mat3            106A            8.00000
mat3            23A             8.00000
mat3            61              50.00000
mat3            59              37.10000
mat3            17              4.50000
mat3            93              1.20000
mat3            05              890.20000
mat3            101             0.90000
mat3            95              0.10000
mat3            31              27.66580
mat3            mat1            475.40680

the Result should not include a child that is also a BOM, but only its children, and with the correct quantities for 475.40680 and not for the 1000

so BOM children need to be divided by BOM Parent qty timed by Child qty of BOM root in this example divided by 1000 (batchsize of mat1) times 475.40680 ( qty of mat1 as child ) and in the end the quantities need to be divided by root Material Batchsize to get the amounts per piece.

I am kind of at a loss, and I am happy to make tmp tables or whatever, but right now I'm stuck. any help pointers etc. would be appreciated.