I have trying to calculate a roll-up of Bill of Materials (BOM) using SQL with the model and connect by clauses. The aim in the first
instance is to calculate the roll up of the cost for each leaf item to each level in the hierarchy. Weight or cost or some other property times the
quantity is to be rolled up.
I am developing a simple application in APEX 4.2.6.00.03 against Oracle Database 12.1.0.2.0
See for some examples of BOM roll-ups.
Ref [1]: https://community.oracle.com/thread/1125784
Ref [2]: https://community.oracle.com/thread/2520973
Consider the BOM and the use of the model clause from Ref [1]
WITH t_data AS
(SELECT 'A' AS part_no, NULL AS parent_part_no, 2 AS quantity, NULL AS cost FROM DUAL UNION ALL
SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity, NULL AS cost FROM DUAL UNION ALL
SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity, NULL AS cost FROM DUAL UNION ALL
SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity, 10 AS cost FROM DUAL UNION ALL
SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity, 5 AS cost FROM DUAL
) ,
t_tree AS
(SELECT part_no , parent_part_no , connect_by_root part_no root_part_no , level lvl , quantity , cost
FROM t_data
CONNECT BY parent_part_no = prior part_no
START WITH parent_part_no IS NULL
)
SELECT part_no "Part No" , parent_part_no "Parent Part No" , quantity "Quantity" , cost "Cost" , total_cost "Total Unit Cost"
FROM t_tree model partition BY (root_part_no) dimension BY (parent_part_no, part_no) measures (lvl,quantity,cost,0 total_cost) ( total_cost[ANY,ANY]
ORDER BY lvl DESC,part_no = quantity[cv(),cv()] * ( NVL(cost[cv(),cv()],0) + NVL(SUM(total_cost)[cv(part_no),ANY],0) ) , cost[ANY,ANY]
ORDER BY lvl DESC, part_no = total_cost[cv(),cv()] / quantity[cv(),cv()] )
ORDER BY root_part_no ,
parent_part_no nulls FIRST ,
part_no
With the BOM heirarchy, without the rollup being
PART_NO PARENT_PART_NO ROOT_PART_NO LVL QUANTITY COST
------- -------------- ------------ --- -------- ----
A A 1 2
B A A 2 3
C B A 3 4
D C A 4 2 10
E C A 4 1 5
Which gives the output of
Part No Parent Part No Quantity Cost Total Unit Cost
------- -------------- -------- ---- ---------------
A 2 300 600
B A 3 100 300
C B 4 25 100
D C 2 10 20
E C 1 5 5
This data is represents a specific type of hierarchy, where there are no duplicates of the sub-assemblies.
There can be duplicates of a leaf (Connect_BY_ISLEAF) parts, but there are no duplicates of the sub-assemblies.
For example, consider the data set of:
WITH t_data AS
(SELECT 'A' AS part_no, NULL AS parent_part_no, 2 AS quantity, NULL AS cost FROM DUAL UNION ALL
SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity, NULL AS cost FROM DUAL UNION ALL
SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity, NULL AS cost FROM DUAL UNION ALL
SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity, NULL AS cost FROM DUAL UNION ALL
SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity, 10 AS cost FROM DUAL UNION ALL
SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity, 5 AS cost FROM DUAL
)
,
t_tree AS
(SELECT part_no , parent_part_no , connect_by_root part_no root_part_no , level lvl , quantity , cost
FROM t_data
CONNECT BY parent_part_no = prior part_no
START WITH parent_part_no IS NULL
)
SELECT part_no "Part No" , parent_part_no "Parent Part No" , quantity "Quantity" , cost "Cost" , total_cost "Total Unit Cost"
FROM t_tree model partition BY (root_part_no) dimension BY (parent_part_no, part_no) measures (lvl,quantity,cost,0 total_cost) ( total_cost[ANY,ANY]
ORDER BY lvl DESC,part_no = quantity[cv(),cv()] * ( NVL(cost[cv(),cv()],0) + NVL(SUM(total_cost)[cv(part_no),ANY],0) ) , cost[ANY,ANY]
ORDER BY lvl DESC, part_no = total_cost[cv(),cv()] / quantity[cv(),cv()] )
ORDER BY root_part_no ,
parent_part_no nulls FIRST ,
part_no
The BOM hierarchy, without the roll-up being
PART_NO PARENT_PART_NO ROOT_PART_NO LVL QUANTITY COST
------- -------------- ------------ --- -------- ----
A A 1 2
B A A 2 3
C B A 3 4
D C A 4 2 10
E C A 4 1 5
B A A 2 3
C B A 3 4
D C A 4 2 10
E C A 4 1 5
ie
Assembly B is represented twice. This approach is typical of an assembly operation, where assembly 'B' in the first instance is installed with one tool set.
For operational reasons, the second Assembly B uses a different tool for assembly. The gross BOM is the same, but splitting the BOM into two
instances is required for a non-bom reason.
Adding this second assembly causes the model statement to fail with
ORA-32638: Non unique addressing in MODEL dimensions
32638. 00000 - "Non unique addressing in MODEL dimensions"
*Cause: The address space defined for the MODEL (partition by and dimension by
expressions) do not uniquely identify each cell.
*Action: Rewrite the MODEL clause. Using UNIQUE SINGLE REFERENCE
option might help.
And adding the 'UNIQUE SINGLE REFERENCE' just causes further errors
Any suggestions on how a BOM rollup could be undertaken, if there are duplicate sub-assemblies?
The second part of the problem, once the above problem is address, is that I am also rolling up the constituent components of the parts to perform a hazardous materials analysis, similar to ROHS. Thus the leaf parts may have Fe (0.015 kg), Sn (0.001 kg), ... and based on these amounts and quantities, the total will be rolled up to each sub assembly. The expected output will be the BOM hierarchy with pivot columns for each of the rolled up properties.
Part No Parent Part No Quantity Cost Total Unit Cost Mass Sn Fe
------- -------------- -------- ---- --------------- ----- ----- ----
A 2 300 600 3.648 0.048 3.60
B A 3 100 300 1.824 0.024 1.80
C B 4 25 100 0.608 0.008 0.6
D C 2 10 20 0.002 0.002 0
E C 1 5 5 0.15 0.0 0.15
Any ideas on how to address these issues?