Oracle SQL/PLSQL: Hierarchical query to split and sum value based on currency

Question

With any luck I can describe my requirement without confusing myself...

I have a multi-level dataset where users can flag which records to summarise into different totals.

Data Entry Rules:

1. IF 'Base Unit' column (True/False) is checked (i.e. True) then 'Add for Option #' and 'Remove for Option #' must be NULL.
2. IF 'Add for Option #' or 'Remove for Option #' is set then 'Base Unit' column must be FALSE.
3. IF 'Add for Option #' is set then; the option list (letters A-Z, separated by semi-colons) cannot include a letter that is in the same structure at a different level (for example for Sequence No = 300, 'Add for Option #' could not include 'B' or 'D'. Same rule applies for the 'Remove for Option #' column.
4. IF 'Add for Option #' or 'Remove for Option #' is set then the same row cannot contain the same option (i.e. letter), for example; for Sequence No = 300, 'Add for Option #' could not include 'B' or 'D'.

The 'Purch Cost/Curr' column is only populated for the lowest level for each structure - these values are already multiplied by the Qty.

I need a PLSQL function that will: - Dependent on parameter (is_base_ / option_), find all rows where the 'Base Unit' column = is_base_ OR ('Add for Option #' column or 'Remove for Option #' column contain option_). For example:

select sequence_no,
       part_no,
       component_part,
       base_unit
       add_for_option,
       remove_for_option
from prod_conf_cost_struct
where (base_unit = is_base_ and base_unit = 'True' 
    or (add_for_option = option_ or remove_for_option = option_));

• Loop through all rows returned by the query above and summarise PER CURRENCY the total of the 'Purch Cost/Curr' column, for each level in the structure. The main challenge I face is that once I have the total for a Level 2 row I cannot multiple that by the 'Qty' at that level before continuing up the structure.

  • If calculating the total for an Option the 'Remove for Option #' value must be subtracted from the 'Add for Option #' value.

Here is my NEW attempt:

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

  select nvl(new_qty, qty), purch_cost 
    into qty_, cost_ 
  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 part_no in (
        select component_part
        from prod_conf_cost_struct_clv
        where model_no = model_no_
          and revision = revision_
          and sequence_no = sequence_no_);
  end if;
  return qty_ * cost_;
exception when no_data_found then 
  return 0;
end calc_cost;

In addition to the 'Sequence No' column there is 'Model No' and 'Revision'.

MODEL_NO - REVISION - SEQUENCE_NO would make up the composite key.

select level, sys_connect_by_path(sequence_no, '->') path, 
     calc_cost(model_no, revision, sequence_no, 'GBP') total_gbp,
     calc_cost(model_no, revision, sequence_no, 'EUR') total_eur,
     calc_cost(model_no, revision, sequence_no, 'USD') total_usd
     calc_cost(model_no, revision, sequence_no, '???') total_other
from prod_conf_cost_struct_clv
where model_no = 35
  and revision = 2
connect by prior component_part = part_no
  and prior model_no = 30
  and prior revision = 2
start with sequence_no = 22500
order by sequence_no

I require the totals per currency summarised like this (when the Base/Option column is blank it will summarise for when BASE_UNIT = True):

Answer 1

I am afraid you confused everyone :)

While your requirements are partially hard to understand I think that there is one thing I would do if I had to deal with such task. I would write recursive function calculating cost from any part of the tree up to the leaves.

Here is the demonstration on data similiar to yours:

select prod.*, level, sys_connect_by_path(seq, '->') path, 
       calc_cost(comp) total
  from prod connect by prior comp = part
  start with base = 1;


   SEQ PART COMP        QTY       COST CURR       BASE AFO RFO   LEVEL PATH             TOTAL
------ ---- ---- ---------- ---------- ---- ---------- --- --- ------- ----------- ----------
     1 A    A1            5                          1               1 ->1                850
     2 A1   A11           3                          0 B             2 ->1->2             114
     3 A11  A111          4          2 EUR           0     B;D       3 ->1->2->3            8
     4 A11  A112          2         15 EUR           0               3 ->1->2->4           30
     5 A1   A12           8          7 EUR           0               2 ->1->5              56
    11 B    B1            5                          1               1 ->11               870
    12 B1   B11           3                          0               2 ->11->12           174
    13 B11  B111          4         12 GBP           0               3 ->11->12->13        48
    14 B11  B112          2          5 GBP           0               3 ->11->12->14        10

Column total contains component costs, for instance for B1 it is 5 * (3 * (4 * 12 + 2 * 5)) which is 870.

Function and sample data are like here:

create or replace function calc_cost(i_comp in varchar2) return number is
  v_qty number := 0;
  v_cost number := 0;
begin
  select qty, cost into v_qty, v_cost from prod where comp = i_comp;
  if v_cost is null then 
    select sum(calc_cost(comp)) into v_cost from prod where part = i_comp;
  end if;
  return v_qty * v_cost;
exception when no_data_found then 
  return 0;
end;

Data:

create table prod(seq, part, comp, qty, cost, curr, base, afo, rfo) as (
    select  1, 'A',   'A1',   5, null, null,  1, null, null  from dual union all
    select  2, 'A1',  'A11',  3, null, null,  0, 'B',  null  from dual union all
    select  3, 'A11', 'A111', 4,    2, 'EUR', 0, null, 'B;D' from dual union all
    select  4, 'A11', 'A112', 2,   15, 'EUR', 0, null, null  from dual union all
    select  5, 'A1',  'A12',  8,    7, 'EUR', 0, null, null  from dual union all
    select 11, 'B',   'B1',   5, null, null,  1, null, null  from dual union all
    select 12, 'B1',  'B11',  3, null, null,  0, null, null  from dual union all
    select 13, 'B11', 'B111', 4,   12, 'GBP', 0, null, null  from dual union all
    select 14, 'B11', 'B112', 2,    5, 'GBP', 0, null, null  from dual );

You did not specify if there can be different currencies for the same part / component and, if so, how would be output like. Anyway you could find those currencies and make calculations for each currency indepedently. You need to add second parameter to function and write something like where part = i_comp and curr = i_curr or curr is null.

Also for ask_for_option / remove_for_option you can probably deal with them in case when.

I see that you put much effort in this problem but in the current form of your question it's hard to answer better. You should provide sample data, not only image and show us exactly what output you expect depending on user choices.

But I hope this function may help you solve the problem. I assumed that if cost is not null then we are in leaf otherwise function looks for subcomponents recursively.

---

Edit:

Lets say that seq = 14 was in EUR not GBP

update prod set curr = 'EUR' where seq = 14;

As I said, exact solution depends on the output you need. If you know all possible currencies then you can modify function to handle currency and show costs like here:

create or replace function calc_cost(i_comp in varchar2, i_curr in varchar2) 
  return number is

  v_qty number := 0;
  v_cost number := 0;
begin

  select qty, cost into v_qty, v_cost from prod 
    where comp = i_comp and (curr = i_curr or curr is null);

  if v_cost is null then 
    select sum(calc_cost(comp, i_curr)) into v_cost 
      from prod where part = i_comp and (curr = i_curr or curr is null);
  end if;
  return v_qty * nvl(v_cost, 0);
exception when no_data_found then 
  return 0;
end;

---

select seq, part, comp, qty, cost, curr, 
       calc_cost(comp, 'EUR') eur, calc_cost(comp, 'GBP') gbp
  from prod 
  connect by part = prior comp
  start with part = 'B';

   SEQ PART COMP        QTY       COST CURR        EUR        GBP
 ----- ---- ---- ---------- ---------- ---- ---------- ----------
    11 B    B1            5                        150        720
    12 B1   B11           3                         30        144
    13 B11  B111          4         12 GBP           0         48
    14 B11  B112          2          5 EUR          10          0      

Part B costs 150 EUR and 720 GBP.

You can find all distinct currencies in interesting part of data, join them with your table and call function this way. The result will be that for each seq you get as many rows as distinct currencies are. Then you can use listagg() and present values as 150 EUR; 720 GBP in one cell.

You could also create some type object and modify function to return table of tuples (currency, cost_in_this_currency). The question is how do you want to show data. Or you could convert values to common currency, but for that you need daily table of ratios.