We are trying to create a report with SQL that rolls up a Bill of Material and are having issues. We are using the connect_by and start_with function but the total cost is not being multiplied by the parent quantity.
Does anyone have any experience with this?
Example Data_
*Part No Parent Part No Quantity Cost Total Unit Cost*
A Null 2 Blank Blank
B A 3 Blank Blank
C B 4 Blank Blank
D C 2 $10 $20
E C 1 $5 $5
Expected Results_
*Part No Parent Part No Quantity Cost Total Unit Cost*
A Null 2 Derived from sum of children ($300) $600 ($300 x 2)
B A 3 Derived from sum of children ($100) $300 ($100 x 3)
C B 4 Derived from sum of children ($25) $100 ($25 x 4)
D C 2 $10 $20
E C 1 $5 $5
CASE WHEN total_unit_cost IS NULL THEN
quantity * (SELECT SUM(total_cost)
FROM (SELECT part_no,
parent_part_no,
total_unit_cost * quantity total_cost
FROM mce.GDAIS_PROPOSAL_HEADER_V a,
mce.GDAIS_PROPOSAL_HIERARCHY_V b
WHERE
a.PROPOSAL_NO = 'TKPRICE5' AND
CONNECT BY PRIOR aa.part_no = aa.parent_part_no
START WITH aa.parent_part_no = c.part_no