The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select round(sum(abandoned_count)/sum(number_of_interactions)*100, 2) abandoned_level,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select round(sum(interactions_answered_live)/sum(number_of_interactions)*100, 2) service_level,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select round(sum(speed_to_answer)/sum(number_of_interactions), 2) avg_speed_to_answer,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select round(sum(talk_time)/sum(talk_time + idle_time)*100, 2) occupancy_rate,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select round(sum(talk_time)/sum(interactions_answered_live)/60,2) avg_talk_time,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select round(sum(wait_time_to_abandon)/sum(number_of_interactions), 2) avg_wait_time_to_abandon,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select round(sum(talk_time)/sum(talk_time + idle_time) * 100, 2) utilization_rate,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
Select sum(interactions_answered_live) calls_ansd,
to_char(hour,'MON-YYYY') month,
interaction_center_id center_id
from bix_sum_grp_cls
group by to_char(hour,'MON-YYYY'), interaction_center_id;
SELECT call_center_name center_name
FROM bix_call_center_v
WHERE call_center_id = p_center_id;
SELECT tv.target_level_short_name
, tv.target_level_name
, tv.target_level_id
, tv.plan_name
, tv.org_level_value_id
, tv.time_level_value_id
, tv.target
-- , tv.computed_target_short_name
, tv.range1_low
, tv.range1_high
, tv.range2_low
, tv.range2_high
, tv.range3_low
, tv.range3_high
, tv.notify_resp1_id
, tv.notify_resp1_short_name
, tv.notify_resp2_id
, tv.notify_resp2_short_name
, tv.notify_resp3_id
, tv.notify_resp3_short_name
, tv.dim1_level_value_id
FROM BISFV_TARGETS tv
WHERE tv.target_level_short_name = p_target_level_short_name
AND tv.time_level_value_id like '%' || p_time || '%'
AND tv.org_level_value_id = p_organization_id
AND tv.dim1_level_value_name = p_center_id;
SELECT workflow_item_type, workflow_process_short_name
INTO l_workflow_item_type, l_workflow_process
FROM bisbv_target_levels
WHERE target_level_short_name = p_target_level_short_name;
BIS_ACTUAL_PUB.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_Indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);