[Home] [Help]
MATERIALIZED VIEW: APPS.BIX_AI_CALL_DETAILS_MV
Source
(SELECT /* 12.0: bug#4526784 */ grouping_id
(bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,bcd.period_start_time ,
bcd.media_item_type ,bcd.server_group_id,classification_value,dnis_name,party_id,resource_id,
bcd.outcome_id,bcd.result_id,bcd.reason_id) G_ID,
(CASE grouping_id
(bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,bcd.period_start_time ,
bcd.media_item_type ,bcd.server_group_id,classification_value,dnis_name,party_id,resource_id,
bcd.outcome_id,bcd.result_id,bcd.reason_id)
WHEN 7 THEN 'CDPR'
WHEN 23 THEN 'CDR'
WHEN 63 THEN 'C'
ELSE 'CORR' END) row_type
,bcd.time_id time_id
,bcd.period_type_id period_type_id
,bcd.period_start_date period_start_date
,bcd.period_start_time period_start_time
,bcd.media_item_type media_item_type
,bcd.classification_value classification_value
,bcd.dnis_name dnis_name
,bcd.server_group_id server_group_id
,bcd.resource_id resource_id
,bcd.outcome_id outcome_id
,bcd.result_id result_id
,bcd.reason_id reason_id
,bcd.party_id party_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_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.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 IN ('TELE_INB','TELE_DIRECT','TELE_MANUAL','TELE_WEB_CALLBACK','UNSOLICITED')
and bcd.partition_key != 'PR'
GROUP BY grouping sets((bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,bcd.period_start_time ,
bcd.media_item_type ,bcd.server_group_id,classification_value,dnis_name,party_id,
resource_id) /*CDPR*/
,(bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,bcd.period_start_time ,
bcd.media_item_type ,bcd.server_group_id,classification_value,dnis_name,resource_id) /*CDR*/
,(bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,bcd.period_start_time ,
bcd.media_item_type ,bcd.server_group_id,classification_value) /*C*/
,(bcd.time_id ,bcd.period_type_id ,bcd.period_start_date ,bcd.period_start_time ,
bcd.media_item_type ,bcd.server_group_id,classification_value,bcd.outcome_id ,
bcd.result_id,bcd.reason_id,bcd.dnis_name) /*CORR*/
)
)