create table prods as (
  select 1 as prod_id,  null  as parent_id, 'ROOT1'    as prod_name from dual union
  select 2 as prod_id,  1     as parent_id, 'PROD11'   as prod_name from dual union
  select 3 as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 4 as prod_id,  3     as parent_id, 'PROD121'  as prod_name from dual union
  select 5 as prod_id,  3     as parent_id, 'PROD123'  as prod_name from dual union
  select 10 as prod_id, null  as parent_id, 'ROOT1'    as prod_name from dual union
  select 20 as prod_id, 10    as parent_id, 'PROD11'   as prod_name from dual union
  select 30 as prod_id, 10    as parent_id, 'PROD12'   as prod_name from dual union
  select 31 as prod_id, 10    as parent_id, 'PROD13'   as prod_name from dual union
  select 40 as prod_id, 30    as parent_id, 'PROD121'  as prod_name from dual union
  select 41 as prod_id, 30    as parent_id, 'PROD122'  as prod_name from dual union
  select 42 as prod_id, 30    as parent_id, 'PROD123'  as prod_name from dual 
);

-- product tree
select 
  p.prod_id                                 as prod_id, 
  connect_by_root p.prod_id                 as root_id, 
  connect_by_root p.prod_name               as root_prod, 
  rpad(' ', (level-1)*2, ' ')||p.prod_name  as prod_name, 
  sys_connect_by_path(p.prod_name, '|')     as prod_path
from prods p
start with parent_id is null 
connect by prior prod_id = parent_id;        

create table prod_map as
(
  -- map1
  select 'MAP_1'  map_id, 1     map_group, 1    map_depth, '|ROOT1'                 prod_path, 'root'   pos from dual union
  select 'MAP_1'  map_id, 1     map_group, 1    map_depth, '|ROOT1|PROD11'          prod_path, 'prod1'  pos from dual union 
  select 'MAP_1'  map_id, 1     map_group, 1    map_depth, '|ROOT1|PROD12'          prod_path, 'prod2'  pos from dual union 
  -- map2
  select 'MAP_2'  map_id, 1     map_group, 2    map_depth, '|ROOT1'                 prod_path, 'root'   pos from dual union
  select 'MAP_2'  map_id, 1     map_group, 2    map_depth, '|ROOT1|PROD11'          prod_path, 'prod1'  pos from dual union 
  select 'MAP_2'  map_id, 1     map_group, 2    map_depth, '|ROOT1|PROD12'          prod_path, 'prod2'  pos from dual union 
  select 'MAP_2'  map_id, 1     map_group, 2    map_depth, '|ROOT1|PROD13'          prod_path, 'prod3'  pos from dual union 
  -- map3
  select 'MAP_3'  map_id, null  map_group, null map_depth, '|ROOT1'                 prod_path, 'root'   pos from dual union 
  select 'MAP_3'  map_id, null  map_group, null map_depth, '|ROOT1|PROD12'          prod_path, 'prod1'  pos from dual union
  select 'MAP_3'  map_id, null  map_group, null map_depth, '|ROOT1|PROD12|PROD121'  prod_path, 'prod2'  pos from dual union 
  select 'MAP_3'  map_id, null  map_group, null map_depth, '|ROOT1|PROD12|PROD122'  prod_path, 'prod3'  pos from dual  
);

create table prod_map_qty as (
  select map_id, count(*) as qty from prod_map group by map_id
);

with
prod_list as (
  select 
    p.prod_id                             as prod_id, 
    connect_by_root p.prod_id             as root_id, 
    connect_by_root p.prod_name           as root_prod, 
    p.prod_name                           as prod_name, 
    sys_connect_by_path(p.prod_name, '|') as prod_path,
    level as lvl
  from prods p
  start with parent_id is null 
  connect by prior prod_id = parent_id                        
)
select * from (
    -- RANK result by map_group, map_depth
    -- get one with maximum map_depth (highest rank)
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY root_id, map_group ORDER BY map_depth DESC) rank
    FROM 
      ( 
        -- pivot product list from tree form to single row form
        select 
          map_id, map_group, map_depth, 
          unmapped_prod_id, unmapped_prod_path,
          root_prod, root_id, 
          root_pid, prod1_pid, prod2_pid, prod3_pid, prod4_pid
        from 
          (
            -- product list with mapped and unmapped products, tree form, ready for pivoting 
            select 
              v.map_id, v.map_group, v.map_depth, 
              v.unmapped_prod_id, v.unmapped_prod_path,
              v.root_prod, v.root_id, v.prod_id, v.pos
            from 
              (
                -- join product list with mapping tree table
                select
                  vv.map_id,vv.map_group, vv.map_depth, 
                  -- unmapped id
                  case 
                    when vv.prod_id is null then p.prod_id
                    else null
                  end unmapped_prod_id,
                  -- unmapped path
                  case 
                    when vv.prod_id is null then p.prod_path
                    else null
                  end unmapped_prod_path,
                  p.root_prod, p.root_id, p.prod_id, p.prod_path,
                  vv.pos
                from prod_list p
                left join -- return all products, including not matched by mapping table
                  (
                    -- get matched combinations of products 
                    select 
                      vmap.* 
                    from 
                      (
                        -- find matched products combinations by mapping tree table
                        select 
                          m.*, pp.root_id, pp.prod_id,
                          sum(1) over (partition by m.map_id, pp.root_id) as qty  -- qty of matched product paths for ID
                        from prod_map m
                        join prod_list pp on 
                          pp.prod_path = m.prod_path  --  same product path
--                              and ......                  --  additional checks (states, parameters, etc.) 
                      ) vmap
                      -- get fully matched combinations (qty of matched products == qty of products by mapping table)
                      join prod_map_qty q on q.map_id = vmap.map_id and q.qty = vmap.qty
                  ) vv on vv.prod_id = p.prod_id
              ) v
          ) 
          -- PIVOT result to single row for each mapped_id
          PIVOT ( max(prod_id) AS pid FOR (pos) IN 
            (
             'root' as root,
             'prod1' as prod1, 
             'prod2' as prod2, 
             'prod3' as prod3, 
             'prod4' as prod4
            )
          ) -- end PIVOT 
      ) t
  ) res
  WHERE ( res.map_group is null ) 
        OR 
        -- for multiple map_group - get one with highest priority (highest rank)
        ( res.map_group is not null and res.rank = 1 )
order by root_id, map_id;

select * from product_tree full join prod_map using (prod_path)

   ROOT_ID MAP_I MISSING_PRODS
---------- ----- ---------------
         1 MAP_1 4, 5
        10 MAP_1 31, 40, 41, 42
        10 MAP_2 40, 41, 42
        10 MAP_3 20, 31, 42

select 
  root_id, map_id,
  unmapped_id, unmapped_path,
  root_pid, prod1_pid, prod2_pid, prod3_pid, prod4_pid
from 
  (select 
    root_id, map_id, unmapped_id, unmapped_path, pos, prod_id,
    rank() over (partition by root_id, map_group order by map_depth desc) group_rank
   from (select root_id,
               decode(cnt, cnt_root_map, map_id) map_id,
               decode(cnt, cnt_root_map, null, prod_id) unmapped_id,
               decode(cnt, cnt_root_map, null, prod_path) unmapped_path,
               map_group,
               map_depth,
               prod_id,
               decode(cnt, cnt_root_map, pos, null) pos,
               rank() over(partition by root_id, prod_id  order by decode(cnt, cnt_root_map, 1)) map_rank
          from (select pt.*,
                       pm.*,
                       count(pm.map_prod_path) over(partition by pt.root_id, pm.map_id) cnt_root_map
                  from product_tree pt
                  left join (select map_id,
                                   map_group,
                                   map_depth,
                                   prod_path map_prod_path,
                                   pos,
                                   count(*) over(partition by map_id) cnt
                              from prod_map) pm
                    on pt.prod_path = pm.map_prod_path)) where map_rank = 1)
          -- PIVOT result to single row for each map_id
          PIVOT ( max(prod_id) AS pid FOR (pos) IN 
            (
             'root' as root,
             'prod1' as prod1, 
             'prod2' as prod2, 
             'prod3' as prod3, 
             'prod4' as prod4
            )
          ) -- end PIVOT 
 where group_rank = 1
 order by root_id, map_id;
 

create table prods as (
  select 1 as prod_id,  null  as parent_id, 'ROOT1'    as prod_name from dual union
  select 2 as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 3 as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 4 as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 5 as prod_id,  2     as parent_id, 'PROD121'  as prod_name from dual union
  select 6 as prod_id,  3     as parent_id, 'PROD121'  as prod_name from dual union
  select 7 as prod_id,  4     as parent_id, 'PROD121'  as prod_name from dual union
  select 8 as prod_id,  1     as parent_id, 'PROD13'   as prod_name from dual union
  select 9 as prod_id,  8     as parent_id, 'PROD121'  as prod_name from dual
);

create table product_tree as
select 
  prod_id,
  parent_id,
  connect_by_root p.prod_id                 as root_id, 
  connect_by_root p.prod_name               as root_prod, 
  p.prod_name                               as prod_name, 
  rpad(' ', (level-1)*2, ' ')||p.prod_name  as prod_name_pad, 
  sys_connect_by_path(p.prod_name, '|')     as prod_path
from prods p
start with parent_id is null 
connect by prior prod_id = parent_id;        

PROD_NAME_PAD	PROD_PATH
=============   =========
ROOT1	        |ROOT1
  PROD12	|ROOT1|PROD12
    PROD121	|ROOT1|PROD12|PROD121
  PROD12	|ROOT1|PROD12
    PROD121	|ROOT1|PROD12|PROD121
  PROD12	|ROOT1|PROD12
    PROD121	|ROOT1|PROD12|PROD121
  PROD13	|ROOT1|PROD13
    PROD121	|ROOT1|PROD13|PROD121

create table prod_map as
(
  select 'MAP_4'  map_id, 1  map_group, 1 map_depth, '|ROOT1'                 prod_path, 'root'   pos from dual union 
  select 'MAP_4'  map_id, 1  map_group, 1 map_depth, '|ROOT1|PROD12'          prod_path, 'prod1'  pos from dual union
  select 'MAP_4'  map_id, 1  map_group, 1 map_depth, '|ROOT1|PROD12|PROD121'  prod_path, 'prod2'  pos from dual
);

|ROOT2
|ROOT2|PROD12
|ROOT2|PROD12|PROD121
|ROOT2|PROD12|PROD122

ROOT_ID	LEAF_ID	MAP_ID	PROD_ID	PROD_PATH
100	120		100	|ROOT2
100	120		110	|ROOT2|PROD12
100	120		120	|ROOT2|PROD12|PROD121
100	130		100	|ROOT2
100	130		110	|ROOT2|PROD12
100	130		130	|ROOT2|PROD12|PROD122

ROOT_ID	LEAF_ID	MAP_ID	PROD_ID	PROD_PATH
1	60	MAP_4	1	|ROOT1
1	60	MAP_4	3	|ROOT1|PROD12
1	60	MAP_4	6	|ROOT1|PROD12|PROD121
1	60	MAP_4	60	|ROOT1|PROD12|PROD122
1	70	MAP_4	1	|ROOT1
1	70	MAP_4	4	|ROOT1|PROD12
1	70	MAP_4	7	|ROOT1|PROD12|PROD121
1	70	MAP_4	70	|ROOT1|PROD12|PROD122

product_tree as (
select
  prod_id,
<...skip...>
  connect_by_isleaf                         as isleaf,
  --
  count(*) over (partition by sys_connect_by_path(p.prod_name, '|')) as qty
from prods p
start with parent_id is null
connect by prior prod_id = parent_id),
super_tree as (
  select -1 leaf_id, pt1.*
    from product_tree pt1
   where pt1.qty = 1 
  union all  
  select pt1.prod_id leaf_id, pt2.*
    from product_tree pt1
    left join product_tree pt2
      on pt1.unique_path like pt2.unique_path || '%' 
   where pt1.qty > 1 and pt1.isleaf = 1 
),

ROOT_ID	LEAF_ID	MAP_ID	PROD_ID	PROD_PATH
1	60	MAP_4	1	|ROOT1
1	60	MAP_4	3	|ROOT1|PROD12
1	60	MAP_4	6	|ROOT1|PROD12|PROD121
1	60	MAP_4	60	|ROOT1|PROD12|PROD122
1	70	MAP_4	1	|ROOT1
1	70	MAP_4	4	|ROOT1|PROD12
1	70	MAP_4	7	|ROOT1|PROD12|PROD121
1	70	MAP_4	70	|ROOT1|PROD12|PROD122
100	-1	MAP_5	100	|ROOT2
100	-1	MAP_5	110	|ROOT2|PROD12
100	-1	MAP_5	120	|ROOT2|PROD12|PROD121
100	-1	MAP_5	130	|ROOT2|PROD12|PROD122