Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement