select
  cr.dcheck "Date|W100",
  cr.product_caption "Client product|W300",
  cr.dial_code_country||','||cr.dial_code_region||','||cr.dial_code_type "Destination|W300",
  cr.r_rule "Routing rule|W250",
  cr.vol_rule * 100 / nullif(ca.vol_all,0) "Traffic part, (%)|AR|NS2"
from bas_product_v ppr
left join
(
  select
    c.dcheck dcheck,
    pr.product_caption product_caption,
    pr.product_id product_id,
    dc.dial_code_country dial_code_country,
    dc.dial_code_region dial_code_region,
    dc.dial_code_type dial_code_type,
    sum(c.sum_duration) vol_all
  from sta_cube_day c
  inner join bas_voip_poi vp on c.src_poi_id = vp.voip_poi_id
  inner join bas_product_v pr on vp.voip_poi_product_id = pr.product_id
  inner join bas_dial_code dc on c.src_dial_code = dc.dial_code
  where c.group_id = rpt_pub_utils.get_voice_group_id('src_poi_id,src_dial_code')
  and c.dcheck >= trunc(sysdate) - 1
  and c.dcheck < trunc(sysdate)
  group by
    c.dcheck,
    pr.product_caption,
    pr.product_id,
    dc.dial_code_country,
    dc.dial_code_region,
    dc.dial_code_type
) ca on ca.product_id = ppr.product_id
inner join
(
  select
    c.dcheck dcheck,
    pr.product_caption product_caption,
    pr.product_id product_id,
    dc.dial_code_country dial_code_country,
    dc.dial_code_region dial_code_region,
    dc.dial_code_type dial_code_type,
    rr.rule_descr||' (ID = '||rr.rule_id||')' r_rule,
    sum(c.sum_duration) vol_rule
  from sta_cube_day c
  inner join bas_voip_poi vp on c.src_poi_id = vp.voip_poi_id
  inner join bas_product_v pr on vp.voip_poi_product_id = pr.product_id
  inner join bas_dial_code dc on c.src_dial_code = dc.dial_code
  inner join rut_rule rr on c.rule_id = rr.rule_id
  where c.group_id = rpt_pub_utils.get_voice_group_id('src_poi_id,src_dial_code,rule_id')
  and c.dcheck >= trunc(sysdate) - 1
  and c.dcheck < trunc(sysdate)
  group by
    c.dcheck,
    pr.product_caption,
    pr.product_id,
    dc.dial_code_country,
    dc.dial_code_region,
    dc.dial_code_type,
    rr.rule_descr,
    rr.rule_id
) cr on cr.product_id = ppr.product_id
     and ca.dial_code_country = cr.dial_code_country
     and ca.dial_code_region = cr.dial_code_region
     and ca.dial_code_type = cr.dial_code_type
where ppr.product_direction = 0
and ppr.product_type = 1
and (:p_prod is null or  instr(','||:p_prod||',',','||ppr.product_id||',') > 0)
and (:p_dest is null or :p_dest = '*' or cr.dial_code_country||','||cr.dial_code_region||','||cr.dial_code_type like replace(:p_dest, '*', '%'))
order by 1,2,3,4,5