Oracle SQL/PLSQL: Hierarchical recursive query with repeating data

Note: if you have trouble running the MATCH_RECOGNIZE stuff, it may be because you are running a (not too) old version of SQL*Developer. Try the latest version or use SQL*Navigator, TOAD, or SQL*Plus instead. The problem is the "?" character, which confuse SQL*Developer, since that is the character JDBC uses for bind variables.

You have got a data model problem. Namely, child records in your prod_conf_cost_struct_cvl table are not explicitly linked to their parent rows. This is why the "DEF" subassembly is causing problems. Without an explicit linkage, there is no way to compute the data cleanly.

You should correct this data model and add a parent_sequence_no to each record, so that (for example) you can tell that sequence_no 80 is a child of sequence_no 70, and not a child of sequence_no 20.

However, since I cannot assume you've got time or authority to change your data model, I'll answer the question with the data model as is.

First of all, let's add QTY and PURCH_CURR to your sample data.

with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
( 
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
select * from prod_conf_cost_struct_clv;

+----------+----------+-------------+---------+----------------+-----+------+-----+------------+
| MODEL_NO | REVISION | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR |
+----------+----------+-------------+---------+----------------+-----+------+-----+------------+
|       62 |        1 |           0 | XXX     | ABC            |   1 |      |   1 | GBP        |
|       62 |        1 |          10 | ABC     | 123            |   2 |      |   1 | GBP        |
|       62 |        1 |          20 | 123     | DEF            |   3 |      |   1 | GBP        |
|       62 |        1 |          30 | DEF     | 456            |   4 |  100 |   1 | GBP        |
|       62 |        1 |          40 | DEF     | 789            |   4 |   50 |   1 | GBP        |
|       62 |        1 |          50 | DEF     | 024            |   4 |   20 |   1 | GBP        |
|       62 |        1 |          60 | ABC     | 356            |   2 |      |   1 | GBP        |
|       62 |        1 |          70 | 356     | DEF            |   3 |      |   1 | GBP        |
|       62 |        1 |          80 | DEF     | 456            |   4 |  100 |   1 | GBP        |
|       62 |        1 |          90 | DEF     | 789            |   4 |   50 |   1 | GBP        |
|       62 |        1 |         100 | DEF     | 024            |   4 |   20 |   1 | GBP        |
+----------+----------+-------------+---------+----------------+-----+------+-----+------------+

NOTE: you don't show how multiple currencies would be represented in your test data, so my handling of that issue in this answer may be incorrect.

OK, so the first real thing we need to do is figure out the value of parent_sequence_no (which really should be in your table - see above). Since it's not in your table, we need to compute it. We will compute it as the sequence_no of the row having the highest sequence_no that is less than the current row and having a level (which I called lvl to avoid using the Oracle keyword) that is one less than the current row.

To find this value efficiently, we can use MATCH_RECOGNIZE functionality to describe what the parent row for each child should look like.

We will call the result set with this new parent_sequence_no column corrected_hierarchy.

with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
( 
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM   prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
  PARTITION BY model_no, revision
  ORDER BY sequence_no desc
  MEASURES (P.sequence_no) AS parent_sequence_no,
           c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  -- C => child row
  -- S* => zero or more siblings or children of siblings that might be 
  --           between child and its parent
  -- P? => parent row, which may not exist (e.g., for the root operation)
  PATTERN (C S* P?)
  DEFINE
    C AS 1=1,
    S AS S.lvl >= C.lvl,
    P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no )
SELECT * FROM corrected_hierarchy;

+----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+
| MODEL_NO | REVISION | PARENT_SEQUENCE_NO | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR |
+----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+
|       62 |        1 |                    |           0 | XXX     | ABC            |   1 |      |   1 | GBP        |
|       62 |        1 |                  0 |          10 | ABC     | 123            |   2 |      |   1 | GBP        |
|       62 |        1 |                 10 |          20 | 123     | DEF            |   3 |      |   1 | GBP        |
|       62 |        1 |                 20 |          30 | DEF     | 456            |   4 |  100 |   1 | GBP        |
|       62 |        1 |                 20 |          40 | DEF     | 789            |   4 |   50 |   1 | GBP        |
|       62 |        1 |                 20 |          50 | DEF     | 024            |   4 |   20 |   1 | GBP        |
|       62 |        1 |                  0 |          60 | ABC     | 356            |   2 |      |   1 | GBP        |
|       62 |        1 |                 60 |          70 | 356     | DEF            |   3 |      |   1 | GBP        |
|       62 |        1 |                 70 |          80 | DEF     | 456            |   4 |  100 |   1 | GBP        |
|       62 |        1 |                 70 |          90 | DEF     | 789            |   4 |   50 |   1 | GBP        |
|       62 |        1 |                 70 |         100 | DEF     | 024            |   4 |   20 |   1 | GBP        |
+----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+

Now, you could stop right there if you want. All you'd need to do is use the corrected_hierarchy logic in your calc_cost function, replacing

    and part_no in (
      select component_part
      ...

with

    and parent_sequence_no = sequence_no_

But, as @Def pointed out, you really don't need a PL/SQL function for what you are trying to do.

What you seem to be trying to do is print a hierarchical bill of materials, with the level cost of each item (level cost being the cost of the item's direct and indirect subcomponents).

Here is a query that does that, putting everything together:

with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
( 
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM   prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
  PARTITION BY model_no, revision
  ORDER BY sequence_no desc
  MEASURES (P.sequence_no) AS parent_sequence_no,
           c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (C S* P?)
  DEFINE
    C AS 1=1,
    S AS S.lvl >= C.lvl,
    P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no ),
sequence_hierarchy_costs as (
SELECT model_no,
       revision,
       min(sequence_no) sequence_no,
       purch_curr,
       sum(h.qty * h.cost) hierarchy_cost
FROM corrected_hierarchy h
WHERE 1=1
connect by model_no = prior model_no
and        revision = prior revision
and        parent_sequence_no = prior sequence_no
group by model_no, revision, connect_by_root sequence_no, purch_curr )
SELECT level,
       sys_connect_by_path(h.sequence_no, '->') path,
       shc.hierarchy_cost
FROM corrected_hierarchy h 
INNER JOIN sequence_hierarchy_costs shc ON shc.model_no = h.model_no and shc.revision = h.revision and shc.sequence_no = h.sequence_no and shc.purch_curr = h.purch_curr
WHERE h.model_no = 62
and   h.revision = 1
START WITH h.sequence_no = 20
connect by h.model_no = prior h.model_no
and        h.revision = prior h.revision
and        h.parent_sequence_no = prior h.sequence_no;

+-------+----------+----------------+
| LEVEL |   PATH   | HIERARCHY_COST |
+-------+----------+----------------+
|     1 | ->20     |            170 |
|     2 | ->20->30 |            100 |
|     2 | ->20->40 |             50 |
|     2 | ->20->50 |             20 |
+-------+----------+----------------+

You can see this would be a lot easier if parent_sequence_no were in your data model to begin with.

Assuming sequence_no column strictly follows tree deep first traversal, missing child/parent relation can be reconstructed in two ways. First we can find a parent sequence_no for every child or find an open interval of sequence_no for children of the parent. Using data provided in OP (no currency column)

with prod_conf_cost_struct_clv (model_no, revision, sequence_no, part_no, component_part, lvl, cost) as
( 
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100  FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50 FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20 FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100 FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50 FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20 FROM DUAL )
, hier as(
SELECT  model_no, revision, sequence_no, part_no, component_part, lvl, cost
 , (SELECT nvl(min(b.sequence_no), 2147483647/*max integer*/) 
    FROM prod_conf_cost_struct_clv b 
    WHERE a.lvl <> b.lvl-1
    AND a.sequence_no < b.sequence_no) child_bound_s_n
 , (SELECT max(b.sequence_no) 
    FROM prod_conf_cost_struct_clv b 
    WHERE a.lvl = b.lvl+1
    AND a.sequence_no > b.sequence_no) parent_s_n
FROM prod_conf_cost_struct_clv a
)
SELECT model_no, revision, sequence_no,parent_s_n,child_bound_s_n, part_no, component_part, lvl, cost
FROM hier;

Children of the row, say SEQUENCE_NO = 20 are in the (SEQUENCE_NO, CHILD_BOUND_S_N) open interval (20, 60).

MODEL_NO REVISION SEQUENCE_NO   PARENT_S_N  CHILD_BOUND_S_N PART_NO COMPONENT_PART  LVL COST
62       1            0                             20      XXX     ABC             1   
62       1           10          0                  30      ABC     123             2   
62       1           20         10                  60      123     DEF             3   
62       1           30         20                  40      DEF     456             4   100
62       1           40         20                  50      DEF     789             4    50
62       1           50         20                  60      DEF     024             4    20
62       1           60          0                  80      ABC     356             2   
62       1           70         60          2147483647      356     DEF             3   
62       1           80         70                  90      DEF     456             4   100
62       1           90         70                 100      DEF     789             4    50
62       1          100         70          2147483647      DEF     024             4    20

To minimize changes to the original calc_cost function the second way looks better suited here. So, again without currency data

CREATE FUNCTION calc_cost(
    model_no_ number, 
    revision_ number, 
    sequence_no_ in number
    --, currency_ in varchar2
  ) return number 
  is
    qty_ number := 0;
    cost_ number := 0;
    lvl_ number := 0;
  begin

    select 1 /*nvl(new_qty, qty)*/, cost, lvl
      into qty_, cost_, lvl_
    from prod_conf_cost_struct_clv
    where model_no = model_no_
      and revision = revision_
      and sequence_no = sequence_no_
      --and (purch_curr = currency_ or purch_curr is null)
      ;

    if cost_ is null then 
      select sum(calc_cost(model_no, revision, sequence_no/*, purch_curr*/)) into cost_ 
      from prod_conf_cost_struct_clv 
      where model_no = model_no_
        and revision = revision_
        --and (purch_curr = currency_ or purch_curr is null)
        and sequence_no > sequence_no_  
        and sequence_no < (SELECT nvl(min(b.sequence_no), 2147483647) 
                      FROM prod_conf_cost_struct_clv b 
                      WHERE lvl_ <> b.lvl-1
                      AND sequence_no_ < b.sequence_no);
    end if;
    return qty_ * cost_;
  exception when no_data_found then 
    return 0;
  end calc_cost;

And applying to the data above

SELECT calc_cost(62,1,20) FROM DUAL;

CALC_COST(62,1,20)
170

Using in the hierarchy query

with hier as(
 SELECT  model_no, revision, sequence_no, part_no, component_part, lvl, cost
   ,(SELECT nvl(min(b.sequence_no), 2147483647) 
     FROM prod_conf_cost_struct_clv b 
     WHERE a.lvl <> b.lvl-1
     AND a.sequence_no < b.sequence_no) child_bound_s_n
 FROM prod_conf_cost_struct_clv a
)
select level, sys_connect_by_path(sequence_no, '->') path, 
     calc_cost(model_no, revision, sequence_no) total_gbp
from hier
where model_no = 62
  and revision = 1
connect by sequence_no > prior sequence_no 
   and sequence_no < prior child_bound_s_n
  and prior model_no = 62
  and prior revision = 1
start with sequence_no = 20
order by sequence_no;

LEVEL   PATH    TOTAL_GBP
1   ->20        170
2   ->20->30    100
2   ->20->40    50
2   ->20->50    20

Do you actually need the function? It seems like what you are actually looking for is a calculation of a part and each of its components (and recursively their components). Try this:

SELECT sub.root_part, sum(price) AS TOTAL_PRICE
FROM (SELECT CONNECT_BY_ROOT t.part_no AS ROOT_PART, price
      FROM (SELECT DISTINCT model_no, revision, part_no, component_part, price
            FROM prod_conf_cost_struct_clv
            WHERE model_no = 62
            AND revision = 1 )t
      CONNECT BY PRIOR component_part = part_no
      --START WITH part_no = '123'
      ) sub
GROUP BY sub.root_part;

I commented out the START WITH, but you can put it back in if you are really looking for just that one ID.