[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
--insert into bixtest
--values(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' : '||p_msg);
PROCEDURE INSERT_DNIS
IS
BEGIN
INSERT /*+ APPEND */ INTO BIX_DM_DNIS
(
dnis_id, dnis, last_update_date, last_updated_by, creation_date
)
SELECT BIX_DM_DNIS_S.NEXTVAL, DNIS, g_sysdate, g_user_id, g_sysdate
FROM
(
SELECT DISTINCT decode(DIRECTION, 'OUTBOUND', 'OUTBOUND', DNIS) DNIS
FROM BIX_MEDIAS_FOR_WORKER WORK
WHERE NOT EXISTS
(SELECT 1
FROM BIX_DM_DNIS DNIS
WHERE WORK.DNIS = DNIS.DNIS
)
AND DNIS IS NOT NULL
);
END INSERT_DNIS;
select budget_amount,currency_code into p_leads_amount, p_currency_code
from as_sales_leads where SALES_LEAD_ID = p_lead_id;
select total_amount,currency_code,1 into
p_opp_amount, p_currency_code,p_opp_won
from
as_leads_all a,
as_statuses_vl asv
where a.lead_id = p_opp_id and
a.status = asv.status_code and
asv.win_loss_indicator = 'W';
SELECT source_code_id, ARC_SOURCE_CODE_FOR
INTO p_source_code_id, l_source_code_for
FROM ams_source_codes
WHERE source_code = p_source_code;
select campaign_id
into p_campaign_id
from ams_campaigns_all_b
where source_code = p_source_code;
select schedule_id, campaign_id
into p_schedule_id, p_campaign_id
from AMS_CAMPAIGN_SCHEDULES_B
where source_code = p_source_code;
select c.campaign_id,c.schedule_id, code.source_code_id, d.dialing_method
INTO p_campaign_id,p_schedule_id,p_source_code_id,p_dialing_method
from iec_g_list_subsets a,
ams_act_lists b,
AMS_CAMPAIGN_SCHEDULES_B c,
ams_list_headers_all d,
ams_source_codes code
where
a.list_header_id = b.list_header_id AND
b.list_used_by_id = c.schedule_id AND
b.list_act_type = 'TARGET' AND
b.list_used_by = 'CSCH' AND
b.list_header_id = d.list_header_id AND
a.list_subset_id = p_source_item_id AND
code.source_code = c.source_code;
SELECT max(milcs_id), max(end_date_time)
INTO l_max_milcs_id, p_max_talk_end_date_time
FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS, JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
WHERE media_id = p_media_id
AND resource_id = p_resource_id
AND SEGS.milcs_type_id = TYPES.milcs_type_id
AND milcs_code = 'WITH_AGENT';
SELECT min(resource_id)
INTO p_earliest_agent
FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS, JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
WHERE SEGS.media_id = p_media_id
AND SEGS.milcs_type_id = TYPES.milcs_type_id
AND TYPES.milcs_code = 'WITH_AGENT'
AND SEGS.start_date_time = (select min(start_date_time)
FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS,
JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
WHERE SEGS.media_id = p_media_id
AND SEGS.milcs_type_id = TYPES.milcs_type_id
AND TYPES.milcs_code = 'WITH_AGENT'
);
SELECT min(start_date_time), max(end_date_time)
INTO p_min_talk_start, p_max_talk_end
FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS,
JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
WHERE SEGS.media_id = p_media_id
AND SEGS.milcs_type_id = TYPES.milcs_type_id
AND TYPES.milcs_code = 'WITH_AGENT';
'BIX_CALL_UPDATE_SUBWORKER',
NULL,
NULL,
FALSE,
p_worker_no);
Delete BIX_WORKER_JOBS WHERE OBJECT_NAME = 'BIX_CALL_DETAILS_F';
INSERT /* APPEND */
INTO bix_medias_for_worker
( MEDIA_ID
,DIRECTION
,START_DATE_TIME
,END_DATE_TIME
,SOURCE_ITEM_ID
,MEDIA_ITEM_TYPE
,MEDIA_ABANDON_FLAG
,MEDIA_TRANSFERRED_FLAG
,ANI
,DNIS
,SERVER_GROUP_ID
,CLASSIFICATION
,WORKER_NUMBER
,STATUS
)
SELECT
MEDIA_ID
--, nvl(DIRECTION, 'INBOUND')
,decode(DIRECTION,NULL,'INBOUND','N/A','INBOUND',DIRECTION)
, START_DATE_TIME
, END_DATE_TIME
, SOURCE_ITEM_ID
, decode(MEDIA_ITEM_TYPE,'TELE_WEB','TELE_WEB_CALLBACK',MEDIA_ITEM_TYPE)
, MEDIA_ABANDON_FLAG
, MEDIA_TRANSFERRED_FLAG
, ANI
,decode(DIRECTION,'OUTBOUND','OUTBOUND', DNIS) DNIS
, SERVER_GROUP_ID
, CLASSIFICATION
, 1
, 'NO WORKER DEFINED'
--med.media_id, 1, 'NO WORKER DEFINED'
FROM JTF_IH_MEDIA_ITEMS MED
WHERE med.last_update_date BETWEEN g_collect_start_date
AND g_collect_end_date
AND med.active = 'N'
AND (
med.direction IN ('INBOUND','OUTBOUND') OR
med.media_item_type = 'UNSOLICITED' -- if unsolicited it may have NULL direction
)
AND
(
med.media_item_type = 'TELE_INB' or
med.media_item_type = 'TELE_DIRECT' or
--002 commenting out "Telephone" media type as it is meant for
--"Outbound Telphony" which has been obsoleted.
--med.media_item_type = 'TELEPHONE' or
med.media_item_type = 'CALL' or
med.media_item_type = 'TELE_MANUAL' or
med.media_item_type = 'TELE_WEB' or
med.media_item_type = 'TELE_WEB_CALLBACK' or
med.media_item_type = 'UNSOLICITED'
);
insert_dnis;
SELECT max(ranking)
INTO g_no_of_jobs
FROM bix_medias_for_worker;
UPDATE bix_medias_for_worker
SET worker_number = x,
status = 'UNASSIGNED'
WHERE worker_number = 1
AND status = 'NO WORKER DEFINED'
--AND rownum < 10
AND rownum <= ceil(g_no_of_jobs/g_required_workers)
--AND ranking > ceil(g_no_of_jobs/g_required_workers)*(x-1)
--AND ranking <= ceil(g_no_of_jobs/g_required_workers)*x
;
INSERT INTO BIX_WORKER_JOBS
(
OBJECT_NAME,
START_DATE_RANGE,
END_DATE_RANGE,
WORKER_NUMBER,
STATUS
)
VALUES
(
'BIX_CALL_DETAILS_F',
sysdate,
sysdate,
x,
'UNASSIGNED'
);
l_total_rows_deleted NUMBER := 0;
l_rows_deleted NUMBER := 0;
DELETE BIX_CALL_DETAILS_F
WHERE request_id = g_worker(i)
AND last_update_date >= g_sysdate
AND rownum <= g_commit_chunk_size ;
l_rows_deleted := SQL%ROWCOUNT;
l_total_rows_deleted := l_total_rows_deleted + l_rows_deleted;
IF (l_rows_deleted < g_commit_chunk_size) THEN
EXIT;
DELETE BIX_CALL_DETAILS_F
WHERE request_id = g_request_id
AND last_update_date >= g_sysdate
AND rownum <= g_commit_chunk_size ;
l_rows_deleted := SQL%ROWCOUNT;
l_total_rows_deleted := l_total_rows_deleted + l_rows_deleted;
IF (l_rows_deleted < g_commit_chunk_size) THEN
EXIT;
PROCEDURE insert_half_hour_rows (
p_collect_start_date IN DATE,
p_collect_end_date IN DATE,
p_worker_no IN NUMBER
)
IS
CURSOR get_call_info IS
SELECT
MEDIA.media_id MEDIA_ID,
MEDIA.server_group_id SERVER_GROUP_ID,
MEDIA.CLASSIFICATION CLASSIFICATION,
MEDIA.dnis DNIS,
MEDIA.direction DIRECTION,
MEDIA.media_item_type MEDIA_ITEM_TYPE,
MEDIA.media_start_time MEDIA_START_TIME,
MEDIA.media_end_time MEDIA_END_TIME,
MEDIA.abandon_flag ABANDON_FLAG ,
MEDIA.transfer_flag TRANSFER_FLAG,
MEDIA.source_item_id SOURCE_ITEM_ID,
--MEDIA.MILCS_ID MILCS_ID,
MEDIA.resource_id RESOURCE_ID,
--MEDIA.segment_type SEGMENT_TYPE,
--MEDIA.segment_start_time SEGMENT_START_TIME,
--MEDIA.segment_end_time SEGMENT_END_TIME,
MEDIA.MAX_AGENT_TALK_END MAX_TALK_END_TIME,
MEDIA.FIRST_AGENT FIRST_AGENT,
MEDIA.CALL_TALK_START CALL_TALK_START,
MEDIA.CALL_TALK_END CALL_TALK_END,
INTACT.INTERACTION_ID INTERACTION_ID,
INTACT.OUTCOME_ID OUTCOME_ID,
INTACT.RESULT_ID RESULT_ID,
INTACT.REASON_ID REASON_ID,
first_value(INTACT.PARTY_ID)
over(partition by nvl(intact.media_id,media.media_id)
order by intact.int_end_time DESC NULLS LAST
) PARTY_ID,
first_value(INTACT.SOURCE_CODE)
over(partition by nvl(intact.media_id,media.media_id)
order by intact.int_end_time DESC NULLS LAST
) SOURCE_CODE,
INTACT.INT_START_TIME INT_START_TIME,
INTACT.INT_END_TIME INT_END_TIME,
INTACT.MAX_AGENT_INT_END MAX_INT_END_TIME,
INTACT.ACTIVITY_ID ACTIVITY_ID,
INTACT.ACT_START_TIME ACT_START_TIME,
INTACT.ACTION_ID ACTION_ID,
INTACT.ACTION_ITEM_ID ACTION_ITEM_ID,
INTACT.DOC_REF DOC_REF,
INTACT.DOC_ID DOC_ID,
MEDIA.ROUTE_MILCS_ID,
MEDIA.ROUTE_SEGS_START_TIME,
MEDIA.ROUTE_SEGS_END_TIME,
MEDIA.IVR_MILCS_ID,
MEDIA.IVR_SEGS_START_TIME,
MEDIA.IVR_SEGS_END_TIME,
MEDIA.FIRST_QUEUE,
MEDIA.LAST_QUEUE,
MEDIA.QUEUE_MILCS_ID,
MEDIA.QUEUE_SEGS_START_TIME,
MEDIA.QUEUE_SEGS_END_TIME,
MEDIA.AGENT_MILCS_ID,
MEDIA.AGENT_SEGS_START_TIME,
MEDIA.AGENT_SEGS_END_TIME
FROM
(
SELECT /*+ use_nl(MED,IVR_SEGS,ROUTE_SEGS,QUEUE_SEGS,AGENT_SEGS) */
MED.media_id MEDIA_ID,
MED.server_group_id SERVER_GROUP_ID,
MED.CLASSIFICATION CLASSIFICATION,
MED.dnis DNIS,
MED.direction DIRECTION,
MED.media_item_type MEDIA_ITEM_TYPE,
MED.start_date_time MEDIA_START_TIME,
MED.end_date_time MEDIA_END_TIME,
MED.media_abandon_flag ABANDON_FLAG ,
MED.MEDIA_TRANSFERRED_FLAG TRANSFER_FLAG,
MED.source_item_id SOURCE_ITEM_ID,
--SEGS.MILCS_ID MILCS_ID,
ROUTE_SEGS.MILCS_ID ROUTE_MILCS_ID,
ROUTE_SEGS.START_DATE_TIME ROUTE_SEGS_START_TIME,
ROUTE_SEGS.END_DATE_TIME ROUTE_SEGS_END_TIME,
IVR_SEGS.MILCS_ID IVR_MILCS_ID,
IVR_SEGS.START_DATE_TIME IVR_SEGS_START_TIME,
IVR_SEGS.END_DATE_TIME IVR_SEGS_END_TIME,
FIRST_VALUE(QUEUE_SEGS.MILCS_ID)
OVER(PARTITION BY QUEUE_SEGS.MEDIA_ID
ORDER BY QUEUE_SEGS.START_DATE_TIME ASC NULLS LAST
) FIRST_QUEUE,
FIRST_VALUE(QUEUE_SEGS.MILCS_ID)
OVER(PARTITION BY QUEUE_SEGS.MEDIA_ID
ORDER BY QUEUE_SEGS.START_DATE_TIME DESC NULLS LAST
) LAST_QUEUE,
QUEUE_SEGS.MILCS_ID QUEUE_MILCS_ID,
QUEUE_SEGS.START_DATE_TIME QUEUE_SEGS_START_TIME,
QUEUE_SEGS.END_DATE_TIME QUEUE_SEGS_END_TIME,
AGENT_SEGS.MILCS_ID AGENT_MILCS_ID,
AGENT_SEGS.START_DATE_TIME AGENT_SEGS_START_TIME,
AGENT_SEGS.END_DATE_TIME AGENT_SEGS_END_TIME,
AGENT_SEGS.resource_id RESOURCE_ID,
--SEGTYPES.milcs_code SEGMENT_TYPE,
--SEGS.start_date_time SEGMENT_START_TIME,
--SEGS.end_date_time SEGMENT_END_TIME,
--
--use decode to make sure segment id of 5 (with_agent) comes first
--
FIRST_VALUE(AGENT_SEGS.RESOURCE_ID)
OVER(PARTITION BY AGENT_SEGS.MEDIA_ID
ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
AGENT_SEGS.START_DATE_TIME
) FIRST_AGENT,
FIRST_VALUE(AGENT_SEGS.START_DATE_TIME)
OVER(PARTITION BY AGENT_SEGS.MEDIA_ID
ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
AGENT_SEGS.START_DATE_TIME
) CALL_TALK_START,
FIRST_VALUE(AGENT_SEGS.END_DATE_TIME)
OVER(PARTITION BY AGENT_SEGS.MEDIA_ID
ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
AGENT_SEGS.END_DATE_TIME DESC NULLS LAST
) CALL_TALK_END,
FIRST_VALUE(AGENT_SEGS.END_DATE_TIME)
OVER(PARTITION BY AGENT_SEGS.MEDIA_ID, AGENT_SEGS.RESOURCE_ID
ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
AGENT_SEGS.END_DATE_TIME DESC NULLS LAST
) MAX_AGENT_TALK_END
--FROM (JTF_IH_MEDIA_ITEMS MED LEFT OUTER JOIN
--JTF_IH_MEDIA_ITEM_LC_SEGS AGENT_SEGS
--ON MED.media_id = AGENT_SEGS.media_id
--)
--LEFT OUTER JOIN
--JTF_IH_MEDIA_ITM_LC_SEG_TYS SEGTYPES
--ON SEGS.MILCS_TYPE_ID = SEGTYPES.MILCS_TYPE_ID
FROM
bix_medias_for_worker MED,
(select *
from jtf_ih_media_item_lc_segs
where milcs_type_id = 1) IVR_SEGS,
(select *
from jtf_ih_media_item_lc_segs
where milcs_type_id = 4) ROUTE_SEGS,
(select *
from jtf_ih_media_item_lc_segs
where milcs_type_id = 3) QUEUE_SEGS,
(select *
from jtf_ih_media_item_lc_segs
where milcs_type_id = 5) AGENT_SEGS
WHERE MED.worker_number = p_worker_no
AND MED.status = 'IN PROCESS'
--WHERE MED.last_update_date BETWEEN g_collect_start_date
-- AND g_collect_end_date
AND med.media_id = ROUTE_SEGS.media_id (+)
and med.media_id = IVR_SEGS.media_id (+)
and med.media_id = QUEUE_SEGS.media_id (+)
and med.media_id = AGENT_SEGS.media_id (+)
--AND MED.active = 'N'
--AND MED.direction IN ('INBOUND','OUTBOUND')
--AND
--(
--MED.media_item_type = 'TELE_INB' or
--MED.media_item_type = 'TELE_DIRECT' or
--MED.media_item_type = 'TELEPHONE' or
--MED.media_item_type = 'CALL' or
--MED.media_item_type = 'TELE_MANUAL' or
--MED.media_item_type = 'TELE_WEB'
--)
) MEDIA LEFT OUTER JOIN
(
select distinct INTERACTION_ID,
MEDIA_ID,
RESOURCE_ID,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
INT_START_TIME,
INT_END_TIME,
ACTIVITY_ID,
ACT_START_TIME,
ACTION_ID,
ACTION_ITEM_ID,
DOC_REF,
DOC_ID,
PARTY_ID,
SOURCE_CODE,
MAX_AGENT_INT_END
from (
SELECT /*+ FIRST_ROWS */
INT.INTERACTION_ID INTERACTION_ID,
--MED.MEDIA_ID MEDIA_ID,
WORK.MEDIA_ID MEDIA_ID,
INT.RESOURCE_ID RESOURCE_ID,
INT.OUTCOME_ID OUTCOME_ID,
INT.RESULT_ID RESULT_ID,
INT.REASON_ID REASON_ID,
INT.START_DATE_TIME INT_START_TIME,
INT.END_DATE_TIME INT_END_TIME,
ACT.ACTIVITY_ID ACTIVITY_ID,
ACT.START_DATE_TIME ACT_START_TIME,
ACT.ACTION_ID ACTION_ID,
ACT.ACTION_ITEM_ID ACTION_ITEM_ID,
ACT.DOC_REF DOC_REF,
ACT.DOC_ID DOC_ID,
INT.PARTY_ID PARTY_ID,
INT.SOURCE_CODE SOURCE_CODE,
first_value(INT.END_DATE_TIME)
over(partition by
--med.media_id,
work.media_id,
int.resource_id
order by int.end_date_time DESC NULLS LAST
) MAX_AGENT_INT_END
FROM
--JTF_IH_MEDIA_ITEMS MED,
BIX_MEDIAS_FOR_WORKER WORK,
JTF_IH_INTERACTIONS INT LEFT OUTER JOIN JTF_IH_ACTIVITIES ACT
ON INT.interaction_id = ACT.interaction_id
--WHERE MED.last_update_date BETWEEN p_collect_start_date
--AND p_collect_end_date
--AND INT.start_date_time BETWEEN p_collect_start_date-1
--AND p_collect_end_date+1
--AND MED.active = 'N'
--AND MED.direction IN ('INBOUND','OUTBOUND')
--AND MED.media_id = int.productive_time_amount
WHERE int.productive_time_amount = work.media_id
AND work.status = 'IN PROCESS'
AND work.worker_number = p_worker_no
--AND
--(
--MED.media_item_type = 'TELE_INB' or
--MED.media_item_type = 'TELE_DIRECT' or
--MED.media_item_type = 'TELEPHONE' or
--MED.media_item_type = 'CALL' or
--MED.media_item_type = 'TELE_MANUAL' or
--MED.media_item_type = 'TELE_WEB'
--)
UNION ALL
SELECT /*+ FIRST_ROWS */
INT.INTERACTION_ID INTERACTION_ID,
ACT.MEDIA_ID MEDIA_ID,
INT.RESOURCE_ID RESOURCE_ID,
INT.OUTCOME_ID OUTCOME_ID,
INT.RESULT_ID RESULT_ID,
INT.REASON_ID REASON_ID,
INT.START_DATE_TIME INT_START_TIME,
INT.END_DATE_TIME INT_END_TIME,
ACT.ACTIVITY_ID ACTIVITY_ID,
ACT.START_DATE_TIME ACT_START_TIME,
ACT.ACTION_ID ACTION_ID,
ACT.ACTION_ITEM_ID ACTION_ITEM_ID,
ACT.DOC_REF DOC_REF,
ACT.DOC_ID DOC_ID,
INT.PARTY_ID PARTY_ID,
INT.SOURCE_CODE SOURCE_CODE ,
first_value(INT.END_DATE_TIME)
over(partition by act.media_id,int.resource_id
order by int.end_date_time DESC NULLS LAST
) MAX_AGENT_INT_END
FROM
--JTF_IH_MEDIA_ITEMS MED,
BIX_MEDIAS_FOR_WORKER WORK,
JTF_IH_INTERACTIONS INT, JTF_IH_ACTIVITIES ACT
--WHERE MED.last_update_date BETWEEN p_collect_start_date
--AND p_collect_end_date
--AND MED.active = 'N'
WHERE INT.interaction_id = ACT.interaction_id
AND ACT.media_id = WORK.media_id
AND work.status = 'IN PROCESS'
AND work.worker_number = p_worker_no
--AND MED.direction IN ('INBOUND','OUTBOUND')
--AND MED.media_id = ACT.media_id
--AND
--(
--MED.media_item_type = 'TELE_INB' or
--MED.media_item_type = 'TELE_DIRECT' or
--MED.media_item_type = 'TELEPHONE' or
--MED.media_item_type = 'CALL' or
--MED.media_item_type = 'TELE_MANUAL' or
--MED.media_item_type = 'TELE_WEB'
--)
)
) INTACT
ON (MEDIA.MEDIA_ID = INTACT.MEDIA_ID
AND INTACT.RESOURCE_ID = decode(media.agent_milcs_id,NULL,INTACT.resource_id,
MEDIA.resource_id)
)
GROUP BY
MEDIA.media_id,
MEDIA.server_group_id,
MEDIA.CLASSIFICATION,
MEDIA.dnis,
MEDIA.direction,
MEDIA.media_item_type,
MEDIA.media_start_time,
MEDIA.media_end_time,
MEDIA.abandon_flag,
MEDIA.transfer_flag,
MEDIA.source_item_id,
--MEDIA.MILCS_ID,
MEDIA.resource_id,
--MEDIA.segment_type,
--MEDIA.segment_start_time,
--MEDIA.segment_end_time,
INTACT.INTERACTION_ID,
INTACT.OUTCOME_ID,
INTACT.RESULT_ID,
INTACT.REASON_ID,
INTACT.INT_START_TIME,
INTACT.INT_END_TIME,
INTACT.ACTIVITY_ID,
INTACT.ACT_START_TIME,
INTACT.ACTION_ID,
INTACT.ACTION_ITEM_ID,
INTACT.DOC_REF,
INTACT.DOC_ID,
INTACT.MEDIA_ID,
INTACT.RESOURCE_ID,
MEDIA.MAX_AGENT_TALK_END,
MEDIA.FIRST_AGENT,
MEDIA.CALL_TALK_START,
MEDIA.CALL_TALK_END,
INTACT.MAX_AGENT_INT_END,
INTACT.PARTY_ID,
INTACT.SOURCE_CODE,
MEDIA.ROUTE_MILCS_ID,
MEDIA.ROUTE_SEGS_START_TIME,
MEDIA.ROUTE_SEGS_END_TIME,
MEDIA.IVR_MILCS_ID,
MEDIA.IVR_SEGS_START_TIME,
MEDIA.IVR_SEGS_END_TIME,
MEDIA.FIRST_QUEUE,
MEDIA.LAST_QUEUE,
MEDIA.QUEUE_MILCS_ID,
MEDIA.QUEUE_SEGS_START_TIME,
MEDIA.QUEUE_SEGS_END_TIME,
MEDIA.AGENT_MILCS_ID,
MEDIA.AGENT_SEGS_START_TIME,
MEDIA.AGENT_SEGS_END_TIME
ORDER BY MEDIA.media_id,
--MEDIA.MILCS_ID,
--MEDIA.media_start_time, MEDIA.resource_id, MEDIA.SEGMENT_TYPE,
INTACT.INTERACTION_ID, INTACT.ACTIVITY_ID
;
write_log('Start of the procedure insert_half_hour_rows at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
--'Start insert_half_hour_rows ');
SELECT nvl(max(decode(positive_response_flag,'Y',1,0)),0)
INTO l_target_record.agentcall_pr_count(counter)
FROM jtf_ih_results_b
WHERE result_id = l_target_record.result_id(counter);
INSERT /*+ APPEND */
INTO bix_call_details_stg STG
(
time_id ,
period_type_id ,
period_start_date ,
period_start_time ,
day_of_week ,
direction ,
media_item_type ,
resource_id ,
party_id ,
classification_value ,
dnis_name ,
server_group_id ,
campaign_id ,
schedule_id ,
outcome_id ,
result_id ,
reason_id ,
source_code_id ,
dialing_method ,
partition_key ,
call_calls_offered_total ,
call_calls_offered_above_th ,
call_calls_handled_total ,
call_calls_handled_above_th ,
call_calls_abandoned ,
call_calls_abandoned_us ,
call_calls_transferred ,
call_ivr_time ,
call_route_time ,
call_queue_time ,
CALL_TOT_QUEUE_TO_ABANDON ,
call_tot_queue_to_answer ,
call_talk_time ,
CALL_CONT_CALLS_OFFERED_NA ,
CALL_CONT_CALLS_HANDLED_TOT_NA ,
agent_talk_time_nac ,
agent_wrap_time_nac ,
agent_calls_tran_conf_to_nac ,
AGENT_CONT_CALLS_HAND_NA ,
AGENT_CONT_CALLS_TC_NA ,
agent_calls_handled_total ,
agent_calls_handled_above_th ,
agent_calls_answered_by_goal ,
agent_sr_created ,
agent_leads_created ,
agent_leads_amount ,
agent_leads_converted_to_opp ,
agent_opportunities_created ,
agent_opportunities_won ,
agent_opportunities_won_amount ,
agent_opportunities_cross_sold ,
agent_opportunities_up_sold ,
agent_opportunities_declined ,
agent_opportunities_lost ,
agent_preview_time ,
agentcall_orr_count ,
agentcall_pr_count ,
agentcall_contact_count
)
VALUES
(
to_char(l_target_record.period_start_date(k),'J'),
1,
l_target_record.period_start_date(k),
l_target_record.period_start_time(k),
l_target_record.day_of_week(k),
l_target_record.direction(k),
l_target_record.media_item_type(k),
l_target_record.resource_id(k),
l_target_record.party_id(k),
l_target_record.classification_value(k),
l_target_record.dnis_name(k),
l_target_record.server_group_id(k),
l_target_record.campaign_id(k),
l_target_record.schedule_id(k),
l_target_record.outcome_id(k),
l_target_record.result_id(k),
l_target_record.reason_id(k),
l_target_record.source_code_id(k),
l_target_record.dialing_method(k),
l_target_record.partition_key(k),
l_target_record.call_calls_offered_total(k),
l_target_record.call_calls_offered_above_th(k),
l_target_record.call_calls_hand_tot(k),
l_target_record.call_calls_hand_above_th(k),
l_target_record.call_calls_abandoned(k),
l_target_record.call_calls_abandoned_us(k),
l_target_record.call_calls_transferred(k),
l_target_record.call_ivr_time(k),
l_target_record.call_route_time(k),
l_target_record.call_queue_time(k),
l_target_record.CALL_TOT_QUEUE_TO_ABANDON(k),
l_target_record.call_tot_queue_to_answer(k),
l_target_record.call_talk_time(k),
l_target_record.call_cont_calls_offered_na(k),
l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(k),
l_target_record.agent_talk_time_nac(k),
l_target_record.agent_wrap_time_nac(k),
l_target_record.agent_calls_tran_conf_to_nac(k),
l_target_record.agent_cont_calls_hand_na(k),
l_target_record.agent_cont_calls_tc_na(k),
l_target_record.agent_calls_handled_total(k),
l_target_record.agent_calls_handled_above_th(k),
l_target_record.agent_calls_answered_by_goal(k),
l_target_record.agent_sr_created(k),
l_target_record.agent_leads_created(k),
l_target_record.agent_leads_amount(k),
l_target_record.agent_leads_converted_to_opp(k),
l_target_record.agent_opportunities_created(k),
l_target_record.agent_opportunities_won(k),
l_target_record.agent_opportunities_won_amount(k),
l_target_record.agent_opportunities_cross_sold(k),
l_target_record.agent_opportunities_up_sold(k),
l_target_record.agent_opportunities_declined(k),
l_target_record.agent_opportunities_lost(k),
l_target_record.agent_preview_time(k),
l_target_record.agentcall_orr_count(k),
l_target_record.agentcall_pr_count(k),
l_target_record.agentcall_contact_count(k)
)
;
SELECT
--l_target_record.media_id(k) media_id,
to_char(l_target_record.period_start_date(k),'J') time_id,
-1 period_type_id,
l_target_record.period_start_date(k) period_start_date,
l_target_record.period_start_time(k) period_start_time,
l_target_record.day_of_week(k) day_of_week,
l_target_record.direction(k) direction,
l_target_record.media_item_type(k) media_item_type,
l_target_record.resource_id(k) resource_id,
l_target_record.party_id(k) party_id,
l_target_record.classification_value(k) classification_value,
l_target_record.dnis_name(k) dnis_name,
l_target_record.server_group_id(k) server_group_id,
l_target_record.campaign_id(k) campaign_id,
l_target_record.schedule_id(k) schedule_id,
l_target_record.outcome_id(k) outcome_id,
l_target_record.result_id(k) result_id,
l_target_record.reason_id(k) reason_id,
l_target_record.source_code_id(k) source_code_id,
l_target_record.dialing_method(k) dialing_method,
l_target_record.partition_key(k) partition_key,
l_target_record.call_calls_offered_total(k) call_calls_offered_total,
l_target_record.call_calls_offered_above_th(k) call_calls_offered_above_th,
l_target_record.call_calls_hand_tot(k) call_calls_handled_total,
l_target_record.call_calls_hand_above_th(k) call_calls_handled_above_th,
l_target_record.call_calls_abandoned(k) call_calls_abandoned,
l_target_record.call_calls_transferred(k) call_calls_transferred,
l_target_record.call_ivr_time(k) call_ivr_time,
l_target_record.call_route_time(k) call_route_time,
l_target_record.call_queue_time(k) call_queue_time,
l_target_record.CALL_TOT_QUEUE_TO_ABANDON(k) CALL_TOT_QUEUE_TO_ABANDON,
l_target_record.call_tot_queue_to_answer(k) call_tot_queue_to_answer,
l_target_record.call_talk_time(k) call_talk_time,
l_target_record.call_cont_calls_offered_na(k) call_cont_calls_offered_na,
l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(k) CALL_CONT_CALLS_HANDLED_TOT_NA,
l_target_record.agent_talk_time_nac(k) agent_talk_time_nac,
l_target_record.agent_wrap_time_nac(k) agent_wrap_time_nac,
l_target_record.agent_calls_tran_conf_to_nac(k) agent_calls_tran_conf_to_nac,
l_target_record.agent_cont_calls_hand_na(k) AGENT_CONT_CALLS_HAND_NA,
l_target_record.agent_cont_calls_tc_na(k) AGENT_CONT_CALLS_TC_NA,
l_target_record.agent_calls_handled_total(k) agent_calls_handled_total,
l_target_record.agent_calls_handled_above_th(k) agent_calls_handled_above_th,
l_target_record.agent_calls_answered_by_goal(k) agent_calls_answered_by_goal,
l_target_record.agent_sr_created(k) agent_sr_created,
l_target_record.agent_leads_created(k) agent_leads_created,
l_target_record.agent_leads_amount(k) agent_leads_amount,
l_target_record.agent_leads_converted_to_opp(k) agent_leads_converted_to_opp,
l_target_record.agent_opportunities_created(k) agent_opportunities_created,
l_target_record.agent_opportunities_won(k) agent_opportunities_won,
l_target_record.agent_opportunities_won_amount(k) agent_opportunities_won_amount,
l_target_record.agent_opportunities_cross_sold(k) agent_opportunities_cross_sold,
l_target_record.agent_opportunities_up_sold(k) agent_opportunities_up_sold,
l_target_record.agent_opportunities_declined(k) agent_opportunities_declined,
l_target_record.agent_opportunities_lost(k) agent_opportunities_lost,
l_target_record.agent_preview_time(k) agent_preview_time,
l_target_record.agentcall_orr_count(k) agentcall_orr_count,
l_target_record.agentcall_pr_count(k) agentcall_pr_count,
l_target_record.agentcall_contact_count(k) agentcall_contact_count
FROM DUAL
) SUMM
ON
(
stg.time_id = summ.time_id
--AND stg.media_id = summ.media_id
AND stg.period_type_id = summ.period_type_id
AND stg.period_start_date = summ.period_start_date
AND stg.period_start_time = summ.period_start_time
AND stg.day_of_week = summ.day_of_week
AND stg.direction = summ.direction
AND stg.media_item_type = summ.media_item_type
AND stg.resource_id = summ.resource_id
AND stg.party_id = summ.party_id
AND stg.classification_value = summ.classification_value
AND stg.dnis_name = summ.dnis_name
AND stg.server_group_id = summ.server_group_id
AND stg.campaign_id = summ.campaign_id
AND stg.schedule_id = summ.schedule_id
AND stg.outcome_id = summ.outcome_id
AND stg.result_id = summ.result_id
AND stg.reason_id = summ.reason_id
AND stg.source_code_id = summ.source_code_id
AND stg.dialing_method = summ.dialing_method
AND stg.partition_key = summ.partition_key
)
WHEN MATCHED
THEN
UPDATE
SET
stg.call_calls_offered_total = nvl(stg.call_calls_offered_total,0) +
nvl(summ.call_calls_offered_total,0),
stg.call_calls_offered_above_th = nvl(stg.call_calls_offered_above_th,0) +
nvl(summ.call_calls_offered_above_th,0),
stg.call_calls_handled_total = nvl(stg.call_calls_handled_total,0) +
nvl(summ.call_calls_handled_total,0),
stg.call_calls_handled_above_th = nvl(stg.call_calls_handled_above_th,0) +
nvl(summ.call_calls_handled_above_th,0),
stg.call_calls_abandoned = nvl(stg.call_calls_abandoned,0) +
nvl(summ.call_calls_abandoned,0),
stg.call_calls_transferred = nvl(stg.call_calls_transferred,0) +
nvl(summ.call_calls_transferred,0),
stg.call_ivr_time = nvl(stg.call_ivr_time,0) +
nvl(summ.call_ivr_time,0),
stg.call_route_time = nvl(stg.call_route_time,0) +
nvl(summ.call_route_time,0),
stg.call_queue_time = nvl(stg.call_queue_time,0) +
nvl(summ.call_queue_time,0),
stg.CALL_TOT_QUEUE_TO_ABANDON = nvl(stg.CALL_TOT_QUEUE_TO_ABANDON,0) +
nvl(summ.CALL_TOT_QUEUE_TO_ABANDON,0),
stg.call_tot_queue_to_answer = nvl(stg.call_tot_queue_to_answer,0) +
nvl(summ.call_tot_queue_to_answer,0),
stg.call_talk_time = nvl(stg.call_talk_time,0) +
nvl(summ.call_talk_time,0),
stg.call_cont_calls_offered_na = nvl(stg.call_cont_calls_offered_na,0) +
nvl(summ.call_cont_calls_offered_na,0),
stg.call_cont_calls_handled_tot_na = nvl(stg.call_cont_calls_handled_tot_na,0) +
nvl(summ.call_cont_calls_handled_tot_na,0),
stg.agent_talk_time_nac = nvl(stg.agent_talk_time_nac,0) +
nvl(summ.agent_talk_time_nac,0),
stg.agent_wrap_time_nac = nvl(stg.agent_wrap_time_nac,0) +
nvl(summ.agent_wrap_time_nac,0),
stg.agent_calls_tran_conf_to_nac = nvl(stg.agent_calls_tran_conf_to_nac,0) +
nvl(summ.agent_calls_tran_conf_to_nac,0),
stg.AGENT_CONT_CALLS_HAND_NA = nvl(stg.AGENT_CONT_CALLS_HAND_NA,0) +
nvl(summ.AGENT_CONT_CALLS_HAND_NA,0),
stg.AGENT_CONT_CALLS_TC_NA = nvl(stg.AGENT_CONT_CALLS_TC_NA,0) +
nvl(summ.AGENT_CONT_CALLS_TC_NA,0),
stg.agent_calls_handled_total = nvl(stg.agent_calls_handled_total,0) +
nvl(summ.agent_calls_handled_total,0),
stg.agent_calls_handled_above_th = nvl(stg.agent_calls_handled_above_th,0) +
nvl(summ.agent_calls_handled_above_th,0),
stg.agent_calls_answered_by_goal = nvl(stg.agent_calls_answered_by_goal,0) +
nvl(summ.agent_calls_answered_by_goal,0),
stg.agent_sr_created = nvl(stg.agent_sr_created,0) +
nvl(summ.agent_sr_created,0),
stg.agent_leads_created = nvl(stg.agent_leads_created,0) +
nvl(summ.agent_leads_created,0),
stg.agent_leads_amount = nvl(stg.agent_leads_amount,0) +
nvl(summ.agent_leads_amount,0),
stg.agent_leads_converted_to_opp = nvl(stg.agent_leads_converted_to_opp,0) +
nvl(summ.agent_leads_converted_to_opp,0),
stg.agent_opportunities_created = nvl(stg.agent_opportunities_created,0) +
nvl(summ.agent_opportunities_created,0),
stg.agent_opportunities_won = nvl(stg.agent_opportunities_won,0) +
nvl(summ.agent_opportunities_won,0),
stg.agent_opportunities_won_amount = nvl(stg.agent_opportunities_won_amount,0)+
nvl(summ.agent_opportunities_won_amount,0),
stg.agent_opportunities_cross_sold = nvl(stg.agent_opportunities_cross_sold,0) +
nvl(summ.agent_opportunities_cross_sold,0),
stg.agent_opportunities_up_sold = nvl(stg.agent_opportunities_up_sold,0) +
nvl(summ.agent_opportunities_up_sold,0),
stg.agent_opportunities_declined = nvl(stg.agent_opportunities_declined,0) +
nvl(summ.agent_opportunities_declined,0),
stg.agent_opportunities_lost = nvl(stg.agent_opportunities_lost,0) +
nvl(summ.agent_opportunities_lost,0),
stg.agent_preview_time = nvl(stg.agent_preview_time,0) +
nvl(summ.agent_preview_time,0),
stg.agentcall_orr_count = nvl(stg.agentcall_orr_count,0) +
nvl(summ.agentcall_orr_count,0),
stg.agentcall_pr_count = nvl(stg.agentcall_pr_count,0) +
nvl(summ.agentcall_pr_count,0),
stg.agentcall_contact_count = nvl(stg.agentcall_contact_count,0) +
nvl(summ.agentcall_contact_count,0),
stg.last_update_date = g_sysdate,
stg.last_updated_by = g_user_id
WHEN NOT MATCHED
THEN
INSERT
(
--media_id ,
time_id ,
period_type_id ,
period_start_date ,
period_start_time ,
day_of_week ,
direction ,
media_item_type ,
resource_id ,
party_id ,
classification_value ,
dnis_name ,
server_group_id ,
campaign_id ,
schedule_id ,
outcome_id ,
result_id ,
reason_id ,
source_code_id ,
dialing_method ,
partition_key ,
call_calls_offered_total ,
call_calls_offered_above_th ,
call_calls_handled_total ,
call_calls_handled_above_th ,
call_calls_abandoned ,
call_calls_transferred ,
call_ivr_time ,
call_route_time ,
call_queue_time ,
CALL_TOT_QUEUE_TO_ABANDON ,
call_tot_queue_to_answer ,
call_talk_time ,
CALL_CONT_CALLS_OFFERED_NA ,
call_cont_calls_handled_tot_na ,
agent_talk_time_nac ,
agent_wrap_time_nac ,
agent_calls_tran_conf_to_nac ,
AGENT_CONT_CALLS_HAND_NA ,
AGENT_CONT_CALLS_TC_NA ,
agent_calls_handled_total ,
agent_calls_handled_above_th ,
agent_calls_answered_by_goal ,
agent_sr_created ,
agent_leads_created ,
agent_leads_amount ,
agent_leads_converted_to_opp ,
agent_opportunities_created ,
agent_opportunities_won ,
agent_opportunities_won_amount ,
agent_opportunities_cross_sold ,
agent_opportunities_up_sold ,
agent_opportunities_declined ,
agent_opportunities_lost ,
agent_preview_time ,
agentcall_orr_count ,
agentcall_pr_count ,
agentcall_contact_count ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date
)
values
(
--summ.media_id ,
summ.time_id ,
summ.period_type_id ,
summ.period_start_date ,
summ.period_start_time ,
summ.day_of_week ,
summ.direction ,
summ.media_item_type ,
summ.resource_id ,
summ.party_id ,
summ.classification_value ,
summ.dnis_name ,
summ.server_group_id ,
summ.campaign_id ,
summ.schedule_id ,
summ.outcome_id ,
summ.result_id ,
summ.reason_id ,
summ.source_code_id ,
summ.dialing_method ,
nvl(summ.partition_key,'NA') ,
summ.call_calls_offered_total ,
summ.call_calls_offered_above_th ,
summ.CALL_CALLS_HANDLED_TOTAL ,
summ.CALL_CALLS_HANDLED_ABOVE_TH ,
summ.call_calls_abandoned ,
summ.call_calls_transferred ,
summ.call_ivr_time ,
summ.call_route_time ,
summ.call_queue_time ,
summ.CALL_TOT_QUEUE_TO_ABANDON ,
summ.call_tot_queue_to_answer ,
summ.call_talk_time ,
summ.CALL_CONT_CALLS_OFFERED_NA ,
summ.CALL_CONT_CALLS_HANDLED_TOT_NA ,
summ.agent_talk_time_nac ,
summ.agent_wrap_time_nac ,
summ.agent_calls_tran_conf_to_nac ,
summ.AGENT_CONT_CALLS_HAND_NA ,
summ.AGENT_CONT_CALLS_TC_NA ,
summ.agent_calls_handled_total ,
summ.agent_calls_handled_above_th ,
summ.agent_calls_answered_by_goal ,
summ.agent_sr_created ,
summ.agent_leads_created ,
summ.agent_leads_amount ,
summ.agent_leads_converted_to_opp ,
summ.agent_opportunities_created ,
summ.agent_opportunities_won ,
summ.agent_opportunities_won_amount ,
summ.agent_opportunities_cross_sold ,
summ.agent_opportunities_up_sold ,
summ.agent_opportunities_declined ,
summ.agent_opportunities_lost ,
summ.agent_preview_time ,
summ.agentcall_orr_count ,
summ.agentcall_pr_count ,
summ.agentcall_contact_count ,
g_user_id ,
g_sysdate ,
g_user_id ,
g_sysdate ,
g_user_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
g_sysdate
);
write_log('Error in insert_half_hour_rows : Error : ' || sqlerrm);
--'End insert_half_hour_rows ');
END insert_half_hour_rows;
INSERT
INTO BIX_CALL_DETAILS_F SUMM
(
TIME_ID,
PERIOD_TYPE_ID,
PERIOD_START_DATE,
PERIOD_START_TIME,
DAY_OF_WEEK,
DIRECTION,
MEDIA_ITEM_TYPE,
CLASSIFICATION_VALUE,
DNIS_NAME,
SERVER_GROUP_ID,
RESOURCE_ID,
CAMPAIGN_ID,
SCHEDULE_ID,
SOURCE_CODE_ID,
DIALING_METHOD,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
PARTY_ID,
PARTITION_KEY,
CALL_CALLS_OFFERED_TOTAL,
CALL_CALLS_OFFERED_ABOVE_TH,
CALL_CALLS_HANDLED_TOTAL,
CALL_CALLS_HANDLED_ABOVE_TH,
CALL_CALLS_ABANDONED,
CALL_CALLS_TRANSFERRED,
CALL_IVR_TIME,
CALL_ROUTE_TIME,
CALL_QUEUE_TIME,
CALL_TOT_QUEUE_TO_ABANDON,
CALL_TOT_QUEUE_TO_ANSWER,
CALL_TALK_TIME,
CALL_CONT_CALLS_OFFERED_NA,
CALL_CONT_CALLS_HANDLED_TOT_NA,
AGENT_TALK_TIME_NAC,
AGENT_WRAP_TIME_NAC,
AGENT_CALLS_TRAN_CONF_TO_NAC,
AGENT_CALLS_HANDLED_TOTAL,
AGENT_CALLS_HANDLED_ABOVE_TH,
AGENT_CALLS_ANSWERED_BY_GOAL,
AGENT_SR_CREATED,
AGENT_LEADS_CREATED,
AGENT_LEADS_AMOUNT,
AGENT_LEADS_CONVERTED_TO_OPP,
AGENT_OPPORTUNITIES_CREATED,
AGENT_OPPORTUNITIES_WON,
AGENT_OPPORTUNITIES_WON_AMOUNT,
AGENT_OPPORTUNITIES_CROSS_SOLD,
AGENT_OPPORTUNITIES_UP_SOLD,
AGENT_OPPORTUNITIES_DECLINED,
AGENT_OPPORTUNITIES_LOST,
AGENT_PREVIEW_TIME,
AGENTCALL_ORR_COUNT,
AGENTCALL_PR_COUNT,
AGENTCALL_CONTACT_COUNT,
AGENT_CONT_CALLS_HAND_NA,
AGENT_CONT_CALLS_TC_NA
)
SELECT
TIME_ID,
PERIOD_TYPE_ID,
PERIOD_START_DATE,
PERIOD_START_TIME,
DAY_OF_WEEK,
DIRECTION,
MEDIA_ITEM_TYPE,
CLASSIFICATION_VALUE,
DNIS_NAME,
SERVER_GROUP_ID,
RESOURCE_ID,
CAMPAIGN_ID,
SCHEDULE_ID,
SOURCE_CODE_ID,
DIALING_METHOD,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
PARTY_ID,
PARTITION_KEY,
SUM(CALL_CALLS_OFFERED_TOTAL) CALL_CALLS_OFFERED_TOTAL,
SUM(CALL_CALLS_OFFERED_ABOVE_TH) CALL_CALLS_OFFERED_ABOVE_TH,
SUM(CALL_CALLS_HANDLED_TOTAL) CALL_CALLS_HANDLED_TOTAL,
SUM(CALL_CALLS_HANDLED_ABOVE_TH) CALL_CALLS_HANDLED_ABOVE_TH,
SUM(CALL_CALLS_ABANDONED) CALL_CALLS_ABANDONED,
SUM(CALL_CALLS_TRANSFERRED) CALL_CALLS_TRANSFERRED,
SUM(CALL_IVR_TIME) CALL_IVR_TIME,
SUM(CALL_ROUTE_TIME) CALL_ROUTE_TIME,
SUM(CALL_QUEUE_TIME) CALL_QUEUE_TIME,
SUM(CALL_TOT_QUEUE_TO_ABANDON) CALL_TOT_QUEUE_TO_ABANDON,
SUM(CALL_TOT_QUEUE_TO_ANSWER) CALL_TOT_QUEUE_TO_ANSWER,
SUM(CALL_TALK_TIME) CALL_TALK_TIME,
SUM(CALL_CONT_CALLS_OFFERED_NA) CALL_CONT_CALLS_OFFERED_NA,
SUM(CALL_CONT_CALLS_HANDLED_TOT_NA) CALL_CONT_CALLS_HANDLED_TOT_NA,
SUM(AGENT_TALK_TIME_NAC) AGENT_TALK_TIME_NAC,
SUM(AGENT_WRAP_TIME_NAC) AGENT_WRAP_TIME_NAC,
SUM(AGENT_CALLS_TRAN_CONF_TO_NAC) AGENT_CALLS_TRAN_CONF_TO_NAC,
SUM(AGENT_CALLS_HANDLED_TOTAL) AGENT_CALLS_HANDLED_TOTAL,
SUM(AGENT_CALLS_HANDLED_ABOVE_TH) AGENT_CALLS_HANDLED_ABOVE_TH,
SUM(AGENT_CALLS_ANSWERED_BY_GOAL) AGENT_CALLS_ANSWERED_BY_GOAL,
SUM(AGENT_SR_CREATED) AGENT_SR_CREATED,
SUM(AGENT_LEADS_CREATED) AGENT_LEADS_CREATED,
SUM(AGENT_LEADS_AMOUNT) AGENT_LEADS_AMOUNT,
SUM(AGENT_LEADS_CONVERTED_TO_OPP) AGENT_LEADS_CONVERTED_TO_OPP,
SUM(AGENT_OPPORTUNITIES_CREATED) AGENT_OPPORTUNITIES_CREATED,
SUM(AGENT_OPPORTUNITIES_WON) AGENT_OPPORTUNITIES_WON,
SUM(AGENT_OPPORTUNITIES_WON_AMOUNT) AGENT_OPPORTUNITIES_WON_AMOUNT,
SUM(AGENT_OPPORTUNITIES_CROSS_SOLD) AGENT_OPPORTUNITIES_CROSS_SOLD,
SUM(AGENT_OPPORTUNITIES_UP_SOLD) AGENT_OPPORTUNITIES_UP_SOLD,
SUM(AGENT_OPPORTUNITIES_DECLINED) AGENT_OPPORTUNITIES_DECLINED,
SUM(AGENT_OPPORTUNITIES_LOST) AGENT_OPPORTUNITIES_LOST,
SUM(AGENT_PREVIEW_TIME) AGENT_PREVIEW_TIME,
SUM(AGENTCALL_ORR_COUNT) AGENTCALL_ORR_COUNT,
SUM(AGENTCALL_PR_COUNT) AGENTCALL_PR_COUNT,
SUM(AGENTCALL_CONTACT_COUNT) AGENTCALL_CONTACT_COUNT,
NVL(SUM(AGENT_CONT_CALLS_HAND_NA),-1) AGENT_CONT_CALLS_HAND_NA
FROM bix_call_details_stg stg
GROUP BY
TIME_ID,
PERIOD_TYPE_ID,
PERIOD_START_DATE,
PERIOD_START_TIME,
DAY_OF_WEEK,
DIRECTION,
MEDIA_ITEM_TYPE,
CLASSIFICATION_VALUE,
DNIS_NAME,
SERVER_GROUP_ID,
RESOURCE_ID,
CAMPAIGN_ID,
SCHEDULE_ID,
SOURCE_CODE_ID,
DIALING_METHOD,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
PARTY_ID,
PARTITION_KEY
;
SELECT
TIME_ID,
PERIOD_TYPE_ID,
PERIOD_START_DATE,
PERIOD_START_TIME,
DAY_OF_WEEK,
DIRECTION,
MEDIA_ITEM_TYPE,
CLASSIFICATION_VALUE,
DNIS_NAME,
SERVER_GROUP_ID,
RESOURCE_ID,
CAMPAIGN_ID,
SCHEDULE_ID,
SOURCE_CODE_ID,
DIALING_METHOD,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
PARTY_ID,
PARTITION_KEY,
SUM(CALL_CALLS_OFFERED_TOTAL) CALL_CALLS_OFFERED_TOTAL,
SUM(CALL_CALLS_OFFERED_ABOVE_TH) CALL_CALLS_OFFERED_ABOVE_TH,
SUM(CALL_CALLS_HANDLED_TOTAL) CALL_CALLS_HANDLED_TOTAL,
SUM(CALL_CALLS_HANDLED_ABOVE_TH) CALL_CALLS_HANDLED_ABOVE_TH,
SUM(CALL_CALLS_ABANDONED) CALL_CALLS_ABANDONED,
SUM(CALL_CALLS_ABANDONED_US) CALL_CALLS_ABANDONED_US,
SUM(CALL_CALLS_TRANSFERRED) CALL_CALLS_TRANSFERRED,
SUM(CALL_IVR_TIME) CALL_IVR_TIME,
SUM(CALL_ROUTE_TIME) CALL_ROUTE_TIME,
SUM(CALL_QUEUE_TIME) CALL_QUEUE_TIME,
SUM(CALL_TOT_QUEUE_TO_ABANDON) CALL_TOT_QUEUE_TO_ABANDON,
SUM(CALL_TOT_QUEUE_TO_ANSWER) CALL_TOT_QUEUE_TO_ANSWER,
SUM(CALL_TALK_TIME) CALL_TALK_TIME,
--decode(PERIOD_TYPE_ID,-1,SUM(CALL_CONT_CALLS_OFFERED_NA),0) CALL_CONT_CALLS_OFFERED_NA,
--decode(period_type_id,-1,SUM(CALL_CONT_CALLS_HANDLED_TOT_NA),0) CALL_CONT_CALLS_HANDLED_TOT_NA,
decode(PERIOD_TYPE_ID,1,SUM(CALL_CONT_CALLS_OFFERED_NA),0) CALL_CONT_CALLS_OFFERED_NA,
decode(period_type_id,1,SUM(CALL_CONT_CALLS_HANDLED_TOT_NA),0) CALL_CONT_CALLS_HANDLED_TOT_NA,
SUM(AGENT_TALK_TIME_NAC) AGENT_TALK_TIME_NAC,
SUM(AGENT_WRAP_TIME_NAC) AGENT_WRAP_TIME_NAC,
SUM(AGENT_CALLS_TRAN_CONF_TO_NAC) AGENT_CALLS_TRAN_CONF_TO_NAC,
SUM(AGENT_CALLS_HANDLED_TOTAL) AGENT_CALLS_HANDLED_TOTAL,
SUM(AGENT_CALLS_HANDLED_ABOVE_TH) AGENT_CALLS_HANDLED_ABOVE_TH,
SUM(AGENT_CALLS_ANSWERED_BY_GOAL) AGENT_CALLS_ANSWERED_BY_GOAL,
SUM(AGENT_SR_CREATED) AGENT_SR_CREATED,
SUM(AGENT_LEADS_CREATED) AGENT_LEADS_CREATED,
SUM(AGENT_LEADS_AMOUNT) AGENT_LEADS_AMOUNT,
SUM(AGENT_LEADS_CONVERTED_TO_OPP) AGENT_LEADS_CONVERTED_TO_OPP,
SUM(AGENT_OPPORTUNITIES_CREATED) AGENT_OPPORTUNITIES_CREATED,
SUM(AGENT_OPPORTUNITIES_WON) AGENT_OPPORTUNITIES_WON,
SUM(AGENT_OPPORTUNITIES_WON_AMOUNT) AGENT_OPPORTUNITIES_WON_AMOUNT,
SUM(AGENT_OPPORTUNITIES_CROSS_SOLD) AGENT_OPPORTUNITIES_CROSS_SOLD,
SUM(AGENT_OPPORTUNITIES_UP_SOLD) AGENT_OPPORTUNITIES_UP_SOLD,
SUM(AGENT_OPPORTUNITIES_DECLINED) AGENT_OPPORTUNITIES_DECLINED,
SUM(AGENT_OPPORTUNITIES_LOST) AGENT_OPPORTUNITIES_LOST,
SUM(AGENT_PREVIEW_TIME) AGENT_PREVIEW_TIME,
SUM(AGENTCALL_ORR_COUNT) AGENTCALL_ORR_COUNT,
SUM(AGENTCALL_PR_COUNT) AGENTCALL_PR_COUNT,
SUM(AGENTCALL_CONTACT_COUNT) AGENTCALL_CONTACT_COUNT,
--decode(period_type_id,-1,SUM(AGENT_CONT_CALLS_HAND_NA),-1) AGENT_CONT_CALLS_HAND_NA,
--decode(period_type_id,-1,SUM(AGENT_CONT_CALLS_TC_NA),0) AGENT_CONT_CALLS_TC_NA
decode(period_type_id,1,SUM(AGENT_CONT_CALLS_HAND_NA),-1) AGENT_CONT_CALLS_HAND_NA,
decode(period_type_id,1,SUM(AGENT_CONT_CALLS_TC_NA),0) AGENT_CONT_CALLS_TC_NA
FROM bix_call_details_stg stg
GROUP BY
TIME_ID,
PERIOD_TYPE_ID,
PERIOD_START_DATE,
PERIOD_START_TIME,
DAY_OF_WEEK,
DIRECTION,
MEDIA_ITEM_TYPE,
CLASSIFICATION_VALUE,
DNIS_NAME,
SERVER_GROUP_ID,
RESOURCE_ID,
CAMPAIGN_ID,
SCHEDULE_ID,
SOURCE_CODE_ID,
DIALING_METHOD,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
PARTY_ID,
PARTITION_KEY
) STG
ON
(
summ.PERIOD_TYPE_ID = stg.PERIOD_TYPE_ID
AND summ.PERIOD_START_DATE = stg.PERIOD_START_DATE
AND summ.PERIOD_START_TIME = stg.PERIOD_START_TIME
AND summ.DAY_OF_WEEK =stg.DAY_OF_WEEK
AND summ.PARTITION_KEY = stg.PARTITION_KEY
AND summ.DIRECTION = stg.DIRECTION
AND summ.MEDIA_ITEM_TYPE=stg.MEDIA_ITEM_TYPE
AND summ.RESOURCE_ID = stg.RESOURCE_ID
AND summ.PARTY_ID = stg.PARTY_ID
AND summ.CLASSIFICATION_VALUE = stg.CLASSIFICATION_VALUE
AND summ.DNIS_NAME = stg.DNIS_NAME
AND summ.SERVER_GROUP_ID = stg.SERVER_GROUP_ID
AND summ.CAMPAIGN_ID = stg.CAMPAIGN_ID
AND summ.SCHEDULE_ID = stg.SCHEDULE_ID
AND summ.OUTCOME_ID = stg.OUTCOME_ID
AND summ.RESULT_ID = stg.RESULT_ID
AND summ.REASON_ID = stg.REASON_ID
AND summ.SOURCE_CODE_ID = stg.SOURCE_CODE_ID
AND summ.DIALING_METHOD = stg.DIALING_METHOD
AND summ.TIME_ID = stg.TIME_ID
)
WHEN MATCHED
THEN
UPDATE
SET
summ.call_calls_offered_total = nvl(summ.call_calls_offered_total,0) + nvl(stg.call_calls_offered_total,0),
summ.call_calls_offered_above_th = nvl(summ.call_calls_offered_above_th,0) +nvl(stg.call_calls_offered_above_th,0),
summ.call_calls_handled_total = nvl(summ.call_calls_handled_total,0) +
nvl(stg.call_calls_handled_total,0),
summ.call_calls_handled_above_th = nvl(summ.call_calls_handled_above_th,0) +
nvl(stg.call_calls_handled_above_th,0),
summ.call_calls_abandoned = nvl(summ.call_calls_abandoned,0) + nvl(stg.call_calls_abandoned,0),
summ.call_calls_abandoned_us = nvl(summ.call_calls_abandoned_us,0) + nvl(stg.call_calls_abandoned_us,0),
summ.call_calls_transferred = nvl(summ.call_calls_transferred,0) + nvl(stg.call_calls_transferred,0),
summ.call_ivr_time = nvl(summ.call_ivr_time,0) + nvl(stg.call_ivr_time,0),
summ.call_route_time = nvl(summ.call_route_time,0) + nvl(stg.call_route_time,0),
summ.call_queue_time = nvl(summ.call_queue_time,0) + nvl(stg.call_queue_time,0),
summ.CALL_TOT_QUEUE_TO_ABANDON = nvl(summ.CALL_TOT_QUEUE_TO_ABANDON,0) + nvl(stg.CALL_TOT_QUEUE_TO_ABANDON,0),
summ.call_tot_queue_to_answer = nvl(summ.call_tot_queue_to_answer,0) + nvl(stg.call_tot_queue_to_answer,0),
summ.call_talk_time = nvl(summ.call_talk_time,0) + nvl(stg.call_talk_time,0),
summ.call_cont_calls_offered_na = nvl(summ.call_cont_calls_offered_na,0) +
nvl(stg.call_cont_calls_offered_na,0),
summ.CALL_CONT_CALLS_HANDLED_TOT_NA = nvl(summ.CALL_CONT_CALLS_HANDLED_TOT_NA,0) +
nvl(stg.CALL_CONT_CALLS_HANDLED_TOT_NA,0),
summ.agent_talk_time_nac = nvl(summ.agent_talk_time_nac,0) + nvl(stg.agent_talk_time_nac,0),
summ.agent_wrap_time_nac = nvl(summ.agent_wrap_time_nac,0) + nvl(stg.agent_wrap_time_nac,0),
summ.agent_calls_tran_conf_to_nac = nvl(summ.agent_calls_tran_conf_to_nac,0) + nvl(stg.agent_calls_tran_conf_to_nac,0),
summ.AGENT_CONT_CALLS_HAND_NA = nvl(summ.AGENT_CONT_CALLS_HAND_NA,0) + nvl(stg.AGENT_CONT_CALLS_HAND_NA,0),
summ.AGENT_CONT_CALLS_TC_NA = nvl(summ.AGENT_CONT_CALLS_TC_NA,0) + nvl(stg.AGENT_CONT_CALLS_TC_NA,0),
summ.agent_calls_handled_total = nvl(summ.agent_calls_handled_total,0) + nvl(stg.agent_calls_handled_total,0),
summ.agent_calls_handled_above_th = nvl(summ.agent_calls_handled_above_th,0) + nvl(stg.agent_calls_handled_above_th,0),
summ.agent_calls_answered_by_goal = nvl(summ.agent_calls_answered_by_goal,0) + nvl(stg.agent_calls_answered_by_goal,0),
summ.agent_sr_created = nvl(summ.agent_sr_created,0) + nvl(stg.agent_sr_created,0),
summ.agent_leads_created = nvl(summ.agent_leads_created,0) + nvl(stg.agent_leads_created,0),
summ.agent_leads_amount = nvl(summ.agent_leads_amount,0) + nvl(stg.agent_leads_amount,0),
summ.agent_leads_converted_to_opp = nvl(summ.agent_leads_converted_to_opp,0) + nvl(stg.agent_leads_converted_to_opp,0),
summ.agent_opportunities_created = nvl(summ.agent_opportunities_created,0) + nvl(stg.agent_opportunities_created,0),
summ.agent_opportunities_won = nvl(summ.agent_opportunities_won,0) + nvl(stg.agent_OPPORTUNITIES_won,0),
summ.agent_opportunities_won_amount = nvl(summ.agent_opportunities_won_amount,0)+nvl(stg.agent_opportunities_won_amount,0),
summ.agent_opportunities_cross_sold = nvl(summ.agent_opportunities_cross_sold,0) + nvl(stg.agent_opportunities_cross_sold,0),
summ.agent_opportunities_up_sold = nvl(summ.agent_opportunities_up_sold,0) + nvl(stg.agent_opportunities_up_sold,0),
summ.agent_opportunities_declined = nvl(summ.agent_opportunities_declined,0) + nvl(stg.agent_opportunities_declined,0),
summ.agent_opportunities_lost = nvl(summ.agent_opportunities_lost,0) + nvl(stg.agent_opportunities_lost,0),
summ.agent_preview_time = nvl(summ.agent_preview_time,0) + nvl(stg.agent_preview_time,0),
summ.agentcall_orr_count = nvl(summ.agentcall_orr_count,0) + nvl(stg.agentcall_orr_count,0),
summ.agentcall_pr_count = nvl(summ.agentcall_pr_count,0) + nvl(stg.agentcall_pr_count,0),
summ.agentcall_contact_count = nvl(summ.agentcall_contact_count,0) + nvl(stg.agentcall_contact_count,0),
summ.last_update_date = g_sysdate,
summ.last_updated_by = g_user_id,
summ.request_id = g_request_id
WHEN NOT MATCHED
THEN
INSERT
(
summ.time_id ,
summ.period_type_id ,
summ.period_start_date ,
summ.period_start_time ,
summ.day_of_week ,
summ.direction ,
summ.media_item_type ,
summ.resource_id ,
summ.party_id ,
summ.classification_value ,
summ.dnis_name ,
summ.server_group_id ,
summ.campaign_id ,
summ.schedule_id ,
summ.outcome_id ,
summ.result_id ,
summ.reason_id ,
summ.source_code_id ,
summ.DIALING_METHOD ,
summ.partition_key ,
summ.call_calls_offered_total ,
summ.call_calls_offered_above_th ,
summ.call_calls_handled_total ,
summ.call_calls_handled_above_th ,
summ.call_calls_abandoned ,
summ.call_calls_abandoned_us ,
summ.call_calls_transferred ,
summ.call_ivr_time ,
summ.call_route_time ,
summ.call_queue_time ,
summ.CALL_TOT_QUEUE_TO_ABANDON ,
summ.call_tot_queue_to_answer ,
summ.call_talk_time ,
summ.CALL_CONT_CALLS_OFFERED_NA ,
summ.CALL_CONT_CALLS_HANDLED_TOT_NA ,
summ.agent_talk_time_nac ,
summ.agent_wrap_time_nac ,
summ.agent_calls_tran_conf_to_nac ,
summ.AGENT_CONT_CALLS_HAND_NA ,
summ.AGENT_CONT_CALLS_TC_NA ,
summ.agent_calls_handled_total ,
summ.agent_calls_handled_above_th ,
summ.agent_calls_answered_by_goal ,
summ.agent_sr_created ,
summ.agent_leads_created ,
summ.agent_leads_amount ,
summ.agent_leads_converted_to_opp ,
summ.agent_opportunities_created ,
summ.agent_opportunities_won ,
summ.agent_opportunities_won_amount ,
summ.agent_opportunities_cross_sold ,
summ.agent_opportunities_up_sold ,
summ.agent_opportunities_declined ,
summ.agent_opportunities_lost ,
summ.agent_preview_time ,
summ.agentcall_orr_count ,
summ.agentcall_pr_count ,
summ.agentcall_contact_count ,
summ.created_by ,
summ.creation_date ,
summ.last_updated_by ,
summ.last_update_date ,
summ.last_update_login ,
summ.request_id ,
summ.program_application_id ,
summ.program_id ,
summ.program_update_date
)
values
(
stg.time_id ,
stg.period_type_id ,
stg.period_start_date ,
stg.period_start_time ,
stg.day_of_week ,
stg.direction ,
stg.media_item_type ,
stg.resource_id ,
stg.party_id ,
stg.classification_value ,
stg.dnis_name ,
stg.server_group_id ,
stg.campaign_id ,
stg.schedule_id ,
stg.outcome_id ,
stg.result_id ,
stg.reason_id ,
stg.source_code_id ,
stg.dialing_method ,
stg.partition_key ,
stg.call_calls_offered_total ,
stg.call_calls_offered_above_th ,
stg.CALL_CALLS_HANDLED_TOTAL ,
stg.CALL_CALLS_HANDLED_ABOVE_TH ,
stg.call_calls_abandoned ,
stg.call_calls_abandoned_us ,
stg.call_calls_transferred ,
stg.call_ivr_time ,
stg.call_route_time ,
stg.call_queue_time ,
stg.CALL_TOT_QUEUE_TO_ABANDON ,
stg.call_tot_queue_to_answer ,
stg.call_talk_time ,
stg.CALL_CONT_CALLS_OFFERED_NA ,
stg.CALL_CONT_CALLS_HANDLED_TOT_NA ,
stg.agent_talk_time_nac ,
stg.agent_wrap_time_nac ,
stg.agent_calls_tran_conf_to_nac ,
stg.AGENT_CONT_CALLS_HAND_NA ,
stg.AGENT_CONT_CALLS_TC_NA ,
stg.agent_calls_handled_total ,
stg.agent_calls_handled_above_th ,
stg.agent_calls_answered_by_goal ,
stg.agent_sr_created ,
stg.agent_leads_created ,
stg.agent_leads_amount ,
stg.agent_leads_converted_to_opp ,
stg.agent_opportunities_created ,
stg.agent_opportunities_won ,
stg.agent_opportunities_won_amount ,
stg.agent_opportunities_cross_sold ,
stg.agent_opportunities_up_sold ,
stg.agent_opportunities_declined ,
stg.agent_opportunities_lost ,
stg.agent_preview_time ,
stg.agentcall_orr_count ,
stg.agentcall_pr_count ,
stg.agentcall_contact_count ,
g_user_id ,
g_sysdate ,
g_user_id ,
g_sysdate ,
g_user_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
g_sysdate
);
--'Before UPDATE of FACT table ');
UPDATE BIX_CALL_DETAILS_F FACT1
SET
(
AGENT_CONT_CALLS_HAND_NA,
AGENT_CONT_CALLS_TC_NA,
CALL_CONT_CALLS_OFFERED_NA,
CALL_CONT_CALLS_HANDLED_TOT_NA,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) =
(
SELECT
NVL(MAX(AGENT_CONT_CALLS_HAND_NA),0),
NVL(MAX(AGENT_CONT_CALLS_TC_NA),0),
NVL(MAX(CALL_CONT_CALLS_OFFERED_NA),0),
NVL(MAX(CALL_CONT_CALLS_HANDLED_TOT_NA),0),
g_sysdate,
g_user_id
FROM BIX_CALL_DETAILS_F FACT2
WHERE FACT1.TIME_ID = FACT2.TIME_ID
AND FACT2.PERIOD_TYPE_ID = 1
AND FACT2.PERIOD_START_TIME = '00:00'
AND FACT1.DAY_OF_WEEK = FACT2.DAY_OF_WEEK
AND FACT1.DIRECTION = FACT2.DIRECTION
AND FACT1.MEDIA_ITEM_TYPE = FACT2.MEDIA_ITEM_TYPE
AND FACT1.RESOURCE_ID = FACT2.RESOURCE_ID
AND FACT1.PARTY_ID = FACT2.PARTY_ID
AND FACT1.CLASSIFICATION_VALUE = FACT2.CLASSIFICATION_VALUE
AND FACT1.DNIS_NAME = FACT2.DNIS_NAME
AND FACT1.SERVER_GROUP_ID = FACT2.SERVER_GROUP_ID
AND FACT1.CAMPAIGN_ID = FACT2.CAMPAIGN_ID
AND FACT1.SCHEDULE_ID = FACT2.SCHEDULE_ID
AND FACT1.OUTCOME_ID = FACT2.OUTCOME_ID
AND FACT1.RESULT_ID = FACT2.RESULT_ID
AND FACT1.REASON_ID = FACT2.REASON_ID
AND FACT1.SOURCE_CODE_ID = FACT2.SOURCE_CODE_ID
AND FACT1.DIALING_METHOD = FACT2.DIALING_METHOD
AND FACT1.PARTITION_KEY = FACT2.PARTITION_KEY
)
WHERE REQUEST_ID = G_REQUEST_ID
AND AGENT_CONT_CALLS_HAND_NA < 0
AND LAST_UPDATE_DATE >= G_SYSDATE
AND FACT1.PERIOD_TYPE_ID > 1
AND ROWNUM <= 50000;
--'AFter UPDATE of fact table ');
INSERT /*+ APPEND */ INTO bix_call_details_stg STG
(
STG.TIME_ID,
STG.PERIOD_TYPE_ID,
STG.PERIOD_START_DATE,
STG.PERIOD_START_TIME,
STG.DAY_OF_WEEK,
STG.DIRECTION,
STG.MEDIA_ITEM_TYPE,
STG.RESOURCE_ID,
STG.PARTY_ID,
STG.CLASSIFICATION_VALUE,
STG.DNIS_NAME,
STG.SERVER_GROUP_ID,
STG.CAMPAIGN_ID,
STG.SCHEDULE_ID,
STG.OUTCOME_ID,
STG.RESULT_ID,
STG.REASON_ID,
STG.SOURCE_CODE_ID,
STG.DIALING_METHOD,
STG.PARTITION_KEY,
STG.CALL_CALLS_OFFERED_TOTAL,
STG.CALL_CALLS_OFFERED_ABOVE_TH,
STG.CALL_CALLS_HANDLED_TOTAL,
STG.CALL_CALLS_HANDLED_ABOVE_TH,
STG.CALL_CALLS_ABANDONED,
STG.CALL_CALLS_ABANDONED_US,
STG.CALL_CALLS_TRANSFERRED,
STG.CALL_IVR_TIME,
STG.CALL_ROUTE_TIME,
STG.call_queue_time,
STG.CALL_TOT_QUEUE_TO_ABANDON,
STG.call_tot_queue_to_answer,
STG.CALL_TALK_TIME,
STG.AGENT_TALK_TIME_NAC,
STG.AGENT_WRAP_TIME_NAC,
STG.AGENT_CALLS_TRAN_CONF_TO_NAC,
STG.AGENT_CONT_CALLS_HAND_NA, --FORCE THIS TO NULL SO THAT THE UPDATE WORKS
STG.AGENT_CALLS_HANDLED_TOTAL,
STG.AGENT_CALLS_HANDLED_ABOVE_TH,
STG.AGENT_CALLS_ANSWERED_BY_GOAL,
STG.AGENT_SR_CREATED,
STG.AGENT_LEADS_CREATED,
STG.AGENT_LEADS_AMOUNT,
STG.AGENT_LEADS_CONVERTED_TO_OPP,
STG.AGENT_OPPORTUNITIES_CREATED,
STG.AGENT_OPPORTUNITIES_WON,
STG.AGENT_OPPORTUNITIES_WON_AMOUNT,
STG.AGENT_OPPORTUNITIES_CROSS_SOLD,
STG.AGENT_OPPORTUNITIES_UP_SOLD,
STG.AGENT_OPPORTUNITIES_DECLINED,
STG.AGENT_OPPORTUNITIES_LOST,
STG.AGENT_PREVIEW_TIME,
STG.AGENTCALL_ORR_COUNT,
STG.AGENTCALL_PR_COUNT,
STG.AGENTCALL_CONTACT_COUNT,
STG.CREATED_BY,
STG.CREATION_DATE,
STG.LAST_UPDATED_BY,
STG.LAST_UPDATE_DATE,
STG.LAST_UPDATE_LOGIN,
STG.REQUEST_ID,
STG.PROGRAM_APPLICATION_ID,
STG.PROGRAM_ID,
STG.PROGRAM_UPDATE_DATE
)
(
SELECT
--decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
--decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), ftd.ent_year_id),
--ftd.ent_qtr_id), ftd.ent_period_id), ftd.week_id), ftd.report_date_julian),
--decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
--decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null),
--128), 64), 32), 16), 1),
--decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
--decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_date(null),
--min(ftd.ent_year_start_date)),min(ftd.ent_qtr_start_date)),
--min(ftd.ent_period_start_date)), min(ftd.week_start_date)),
--min(stg.period_start_date)),
decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), ftd.ent_year_id),
ftd.ent_qtr_id), ftd.ent_period_id), ftd.week_id),
decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null),
128), 64), 32), 16),
decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_date(null),
min(ftd.ent_year_start_date)),min(ftd.ent_qtr_start_date)),
min(ftd.ent_period_start_date)), min(ftd.week_start_date)),
'00:00',
DAY_OF_WEEK,
DIRECTION,
MEDIA_ITEM_TYPE,
RESOURCE_ID,
PARTY_ID,
CLASSIFICATION_VALUE,
DNIS_NAME,
SERVER_GROUP_ID,
CAMPAIGN_ID,
SCHEDULE_ID,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
SOURCE_CODE_ID,
DIALING_METHOD,
PARTITION_KEY,
SUM(CALL_CALLS_OFFERED_TOTAL),
SUM(CALL_CALLS_OFFERED_ABOVE_TH),
SUM(CALL_CALLS_HANDLED_TOTAL),
SUM(CALL_CALLS_HANDLED_ABOVE_TH),
SUM(CALL_CALLS_ABANDONED),
SUM(CALL_CALLS_ABANDONED_US),
SUM(CALL_CALLS_TRANSFERRED),
SUM(CALL_IVR_TIME),
SUM(CALL_ROUTE_TIME),
SUM(call_queue_time),
SUM(CALL_TOT_QUEUE_TO_ABANDON),
SUM(call_tot_queue_to_answer),
SUM(CALL_TALK_TIME),
SUM(AGENT_TALK_TIME_NAC),
SUM(AGENT_WRAP_TIME_NAC),
SUM(AGENT_CALLS_TRAN_CONF_TO_NAC),
--NULL, SUM(AGENT_CONT_CALLS_HAND_NA),
-1,
SUM(AGENT_CALLS_HANDLED_TOTAL),
SUM(AGENT_CALLS_HANDLED_ABOVE_TH),
SUM(AGENT_CALLS_ANSWERED_BY_GOAL),
SUM(AGENT_SR_CREATED),
SUM(AGENT_LEADS_CREATED),
SUM(AGENT_LEADS_AMOUNT),
SUM(AGENT_LEADS_CONVERTED_TO_OPP),
SUM(AGENT_OPPORTUNITIES_CREATED),
SUM(AGENT_OPPORTUNITIES_WON),
SUM(AGENT_OPPORTUNITIES_WON_AMOUNT),
SUM(AGENT_OPPORTUNITIES_CROSS_SOLD),
SUM(AGENT_OPPORTUNITIES_UP_SOLD),
SUM(AGENT_OPPORTUNITIES_DECLINED),
SUM(AGENT_OPPORTUNITIES_LOST),
SUM(AGENT_PREVIEW_TIME),
SUM(AGENTCALL_ORR_COUNT),
SUM(AGENTCALL_PR_COUNT),
SUM(AGENTCALL_CONTACT_COUNT),
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
g_user_id,
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate
FROM bix_call_details_stg stg,
fii_time_day ftd
WHERE stg.time_id = ftd.report_date_julian
AND stg.period_type_id = 1
GROUP BY
DAY_OF_WEEK,
DIRECTION,
MEDIA_ITEM_TYPE,
RESOURCE_ID,
PARTY_ID,
CLASSIFICATION_VALUE,
DNIS_NAME,
SERVER_GROUP_ID,
CAMPAIGN_ID,
SCHEDULE_ID,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
SOURCE_CODE_ID,
DIALING_METHOD,
PARTITION_KEY,
ROLLUP (
ftd.ent_year_id,
ftd.ent_qtr_id,
ftd.ent_period_id,
ftd.week_id
--ftd.report_date_julian)
)
HAVING
--decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
--decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), 128), 64), 32), 16), 1) IS NOT NULL
--)
decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null)
,128), 64), 32), 16) IS NOT NULL
)
;
SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
NVL(sum(decode(status,'FAILED', 1, 0)),0),
NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
count(*)
INTO l_unassigned_cnt,
l_failed_cnt,
l_wip_cnt,
l_completed_cnt,
l_total_cnt
FROM BIX_WORKER_JOBS
WHERE worker_number = p_worker_no
AND object_name = 'BIX_CALL_DETAILS_F';
UPDATE BIX_WORKER_JOBS
SET status = 'IN PROCESS'
WHERE object_name = 'BIX_CALL_DETAILS_F'
AND worker_number = p_worker_no;
UPDATE bix_medias_for_worker
SET status = 'IN PROCESS'
--worker_number = p_worker_no
WHERE status = 'UNASSIGNED'
AND worker_number = p_worker_no;
insert_half_hour_rows (
g_collect_start_date,
g_collect_end_date,
p_worker_no
);
UPDATE bix_medias_for_worker
SET status = 'COMPLETED'
WHERE status = 'IN PROCESS'
AND worker_number = p_worker_no;
UPDATE BIX_WORKER_JOBS
SET status = 'COMPLETED'
WHERE object_name = 'BIX_CALL_DETAILS_F'
AND status = 'IN PROCESS'
AND worker_number = p_worker_no;
UPDATE BIX_WORKER_JOBS
SET status = 'FAILED'
WHERE object_name = 'BIX_CALL_DETAILS_F'
AND status = 'IN PROCESS'
AND worker_number = p_worker_no;
UPDATE bix_medias_for_worker
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';
/* Inserting PR count only into bix_call_details for the purpose of marketing mv */
INSERT /*+ APPEND */
INTO bix_call_details_stg STG
(
time_id ,
period_type_id ,
period_start_date ,
period_start_time ,
source_code_id ,
party_id ,
partition_key ,
day_of_week ,
Direction ,
agentcall_pr_count ,
call_calls_offered_total ,
call_calls_offered_above_th ,
call_calls_handled_total ,
call_calls_handled_above_th ,
call_calls_abandoned ,
call_calls_abandoned_us ,
call_calls_transferred ,
call_ivr_time ,
call_route_time ,
call_queue_time ,
CALL_TOT_QUEUE_TO_ABANDON ,
call_tot_queue_to_answer ,
call_talk_time ,
CALL_CONT_CALLS_OFFERED_NA ,
CALL_CONT_CALLS_HANDLED_TOT_NA ,
agent_talk_time_nac ,
agent_wrap_time_nac ,
agent_calls_tran_conf_to_nac ,
AGENT_CONT_CALLS_HAND_NA ,
AGENT_CONT_CALLS_TC_NA ,
agent_calls_handled_total ,
agent_calls_handled_above_th ,
agent_calls_answered_by_goal ,
agent_sr_created ,
agent_leads_created ,
agent_leads_amount ,
agent_leads_converted_to_opp ,
agent_opportunities_created ,
agent_opportunities_won ,
agent_opportunities_won_amount ,
agent_opportunities_cross_sold ,
agent_opportunities_up_sold ,
agent_opportunities_declined ,
agent_opportunities_lost ,
agent_preview_time ,
agentcall_orr_count
)
(
select to_char(trunc(start_date_time),'J')
,1,trunc(start_date_time),'00:00',source_code_id ,
a.party_id,
'PR',
to_char(trunc(start_date_time),'D') ,
'N/A',
count(*) ,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,0
from jtf_ih_interactions a, jtf_ih_results_b b
where a.result_id=b.result_id
and a.end_date_time between g_collect_start_date and g_collect_end_date
and b.positive_response_flag='Y'
and a.active='N'
group by trunc(start_date_time),source_code_id,a.party_id
);
SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
NVL(sum(decode(status,'COMPLETED',1,0)),0),
NVL(sum(decode(status,'IN PROCESS',1,0)),0),
NVL(sum(decode(status,'FAILED',1,0)),0),
count(*)
INTO l_unassigned_cnt,
l_completed_cnt,
l_wip_cnt,
l_failed_cnt,
l_tot_cnt
FROM BIX_WORKER_JOBS
WHERE object_name = 'BIX_CALL_DETAILS_F';
p_message => 'Update Calls summary package failed in OLTP cleanup : error : ' || g_errbuf,
p_period_from => g_collect_start_date,
p_period_to => g_collect_end_date);