[Home] [Help]
MATERIALIZED VIEW: APPS.BIX_AO_CALL_DETAILS_MV
Source
(SELECT
grouping_id
(
bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,
bcd.server_group_id, dialing_method,
campaign_id, schedule_id, source_code_id,
resource_id, party_id,
outcome_id, result_id, reason_id
) G_ID,
(CASE
grouping_id
(
bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,
bcd.server_group_id,dialing_method,
campaign_id, schedule_id, source_code_id,
resource_id, party_id,
outcome_id, result_id, reason_id
)
WHEN 31 THEN 'C'
WHEN 15 THEN 'CR'
WHEN 24 THEN 'CORR'
WHEN 7 THEN 'CRP' ELSE 'UNKNOWN'
END
) row_type
,bcd.time_id time_id
,bcd.period_type_id period_type_id
,bcd.period_start_date period_start_date
,bcd.server_group_id server_group_id
,bcd.resource_id resource_id
,bcd.party_id party_id
,bcd.campaign_id campaign_id
,bcd.schedule_id schedule_id
,bcd.source_code_id source_code_id
,bcd.dialing_method dialing_method
,bcd.outcome_id outcome_id
,bcd.result_id result_id
,bcd.reason_id reason_id
,sum(bcd.call_calls_offered_total) call_calls_offered_total
,count(bcd.call_calls_offered_total) calls_offered_total_xcnt
,sum(bcd.call_calls_offered_above_th) call_calls_offered_above_th
,count(bcd.call_calls_offered_above_th) calls_offered_above_th_xcnt
,sum(bcd.call_calls_handled_total) call_calls_handled_total
,count(bcd.call_calls_handled_total) calls_handled_total_xcnt
,sum(bcd.call_calls_handled_above_th) call_calls_handled_above_th
,count(bcd.call_calls_handled_above_th) calls_handled_above_th_xcnt
,sum(bcd.call_calls_abandoned) call_calls_abandoned
,count(bcd.call_calls_abandoned) calls_abandoned_xcnt
,sum(bcd.call_calls_abandoned_us) call_calls_abandoned_us
,count(bcd.call_calls_abandoned_us) calls_abandoned_us_xcnt
,sum(bcd.call_calls_transferred) call_calls_transferred
,count(bcd.call_calls_transferred) calls_transferred_xcnt
,sum(bcd.call_ivr_time) call_ivr_time
,count(bcd.call_ivr_time) ivr_time_xcnt
,sum(bcd.call_route_time) call_route_time
,count(bcd.call_route_time) route_time_xcnt
,sum(bcd.call_queue_time) call_queue_time
,count(bcd.call_queue_time) queue_time_xcnt
,sum(bcd.call_tot_queue_to_abandon) call_tot_queue_to_abandon
,count(bcd.call_tot_queue_to_abandon) tot_queue_to_abandon_xcnt
,sum(bcd.call_tot_queue_to_answer) call_tot_queue_to_answer
,count(bcd.call_tot_queue_to_answer) tot_queue_to_answer_xcnt
,sum(bcd.call_talk_time) call_talk_time
,count(bcd.call_talk_time) talk_time_xcnt
,sum(bcd.call_cont_calls_offered_na) call_cont_calls_offered_na
,count(bcd.call_cont_calls_offered_na) cont_calls_offered_na_xcnt
,sum(bcd.call_cont_calls_handled_tot_na) call_cont_calls_handled_tot_na
,count(bcd.call_cont_calls_handled_tot_na) cont_calls_handled_tot_na_xcnt
,sum(bcd.agent_talk_time_nac) agent_talk_time_nac
,count(bcd.agent_talk_time_nac) talk_time_nac_xcnt
,sum(bcd.agent_wrap_time_nac) agent_wrap_time_nac
,count(bcd.agent_wrap_time_nac) wrap_time_nac_xcnt
,sum(bcd.agent_calls_tran_conf_to_nac) agent_calls_tran_conf_to_nac
,count(bcd.agent_calls_tran_conf_to_nac) calls_tran_conf_to_nac_xcnt
,sum(bcd.agent_calls_handled_total) agent_calls_handled_total
,count(bcd.agent_calls_handled_total) agent_handled_total_xcnt
,sum(bcd.agent_calls_handled_above_th) agent_calls_handled_above_th
,count(bcd.agent_calls_handled_above_th) agent_handled_above_th_xcnt
,sum(bcd.agent_calls_answered_by_goal) agent_calls_answered_by_goal
,count(bcd.agent_calls_answered_by_goal) agent_answered_by_goal_xcnt
,sum(bcd.agent_sr_created) agent_sr_created
,count(bcd.agent_sr_created) sr_created_xcnt
,sum(bcd.agent_leads_created) agent_leads_created
,count(bcd.agent_leads_created) leads_created_xcnt
,sum(bcd.agent_leads_amount) agent_leads_amount
,count(bcd.agent_leads_amount) leads_amount_xcnt
,sum(bcd.agent_leads_converted_to_opp) agent_leads_converted_to_opp
,count(bcd.agent_leads_converted_to_opp) leads_converted_to_opp_xcnt
,sum(bcd.agent_opportunities_created) agent_opportunities_created
,count(bcd.agent_opportunities_created) opportunities_created_xcnt
,sum(bcd.agent_opportunities_won) agent_opportunities_won
,count(bcd.agent_opportunities_won) opportunities_won_xcnt
,sum(bcd.agent_opportunities_won_amount) agent_opportunities_won_amount
,count(bcd.agent_opportunities_won_amount) opportunities_won_amount_xcnt
,sum(bcd.agent_opportunities_cross_sold) agent_opportunities_cross_sold
,count(bcd.agent_opportunities_cross_sold) opportunities_cross_sold_xcnt
,sum(bcd.agent_opportunities_up_sold) agent_opportunities_up_sold
,count(bcd.agent_opportunities_up_sold) opportunities_up_sold_xcnt
,sum(bcd.agent_opportunities_declined) agent_opportunities_declined
,count(bcd.agent_opportunities_declined) opportunities_declined_xcnt
,sum(bcd.agent_opportunities_lost) agent_opportunities_lost
,count(bcd.agent_opportunities_lost) opportunities_lost_xcnt
,sum(bcd.agent_preview_time) agent_preview_time
,count(bcd.agent_preview_time) preview_time_xcnt
,sum(bcd.agentcall_orr_count) agentcall_orr_count
,count(bcd.agentcall_orr_count) call_orr_count_xcnt
,sum(bcd.agentcall_pr_count) agentcall_pr_count
,count(bcd.agentcall_pr_count) call_pr_count_xcnt
,sum(bcd.agentcall_contact_count) agentcall_contact_count
,count(bcd.agentcall_contact_count) call_contact_count_xcnt
,sum(bcd.agent_cont_calls_hand_na) agent_cont_calls_hand_na
,count(bcd.agent_cont_calls_hand_na) cont_calls_hand_na_xcnt
,sum(bcd.agent_cont_calls_tc_na) agent_cont_calls_tc_na
,count(bcd.agent_cont_calls_tc_na) cont_calls_tc_na_xcnt
,count(*) xcnt
FROM BIX.BIX_CALL_DETAILS_F bcd
WHERE bcd.media_item_type = ('TELEPHONE')
and bcd.partition_key!='PR'
GROUP BY
grouping sets (
(
bcd.time_id ,bcd.period_type_id ,bcd.period_start_date,
bcd.server_group_id, dialing_method,
campaign_id, schedule_id, source_code_id
),
(
bcd.time_id ,bcd.period_type_id ,bcd.period_start_date,
bcd.server_group_id, dialing_method,
campaign_id, schedule_id, source_code_id,
resource_id
),
(
bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,
bcd.server_group_id, dialing_method,
campaign_id, schedule_id, source_code_id,
party_id,resource_id
),
(
bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,
bcd.server_group_id, dialing_method,
campaign_id, schedule_id, source_code_id,
outcome_id, result_id, reason_id
)
)
)