[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_AGENT_STATE
(P_RESOURCE_ID IN NUMBER
,P_DIAL_SERVER_ID IN NUMBER
,P_CAMPAIGN_ID IN NUMBER
,P_CPN_SCHEDULE_ID IN NUMBER
,P_STATUS IN VARCHAR2
,P_STATUS_REASON IN VARCHAR2
,P_START_TIME IN VARCHAR2
)
AS
l_start_time DATE;
insert into iec_rep_agent_status (
resource_Id,
dial_server_id,
campaign_id,
campaign_schedule_id,
status,
status_reason,
status_start_time,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
security_group_id,
object_version_number )
values(
P_RESOURCE_ID,
P_DIAL_SERVER_ID,
P_CAMPAIGN_ID,
P_CPN_SCHEDULE_ID,
P_STATUS,
P_STATUS_REASON,
l_start_time,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
0,
0
);
END UPDATE_AGENT_STATE;
PROCEDURE UPDATE_AGENT_OUTCOME_DETAILS
( P_RESOURCE_ID IN NUMBER
,P_DIAL_SERVER_ID IN NUMBER
,P_CAMPAIGN_ID IN NUMBER
,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
,P_OUTCOME_ID IN NUMBER
,P_RESULT_ID IN NUMBER
,P_RESULT_COUNT IN NUMBER
,P_FTC_ABANDON_COUNT IN NUMBER
,P_MESSAGE_PLAYED_COUNT IN NUMBER
,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
,P_CONTACT_FLAG IN VARCHAR2
,P_TOTAL_IDLE_TIME IN NUMBER
,P_TOTAL_WAIT_TIME IN NUMBER
,P_TOTAL_TALK_TIME IN NUMBER
,P_TOTAL_WRAPUP_TIME IN NUMBER
,P_TOTAL_BREAK_TIME IN NUMBER
,P_CALLS_OFFERED IN NUMBER
,P_PRED_CALLS_OFFERED IN NUMBER
,P_LOGIN_AGENT_COUNT IN NUMBER
)
AS
l_result_count NUMBER := 0;
insert into iec_rep_agent_cpn_details (
agent_cpn_detail_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
resource_Id,
dial_server_id,
campaign_id,
campaign_schedule_id,
total_login_time,
total_activity_time,
total_idle_time,
total_wait_time,
total_wrapup_time,
total_talk_time,
calls_offered,
predictive_calls_offered,
outcome_id,
result_id,
result_count,
positive_response_flag,
security_group_id,
object_version_number,
current_status,
current_Status_begin_time,
contact_flag,
login_agent_count
)
values(
iec_rep_agent_cpn_details_s.nextval,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
P_RESOURCE_ID,
P_DIAL_SERVER_ID,
P_CAMPAIGN_ID,
P_CAMPAIGN_SCHEDULE_ID,
l_login_time,
l_activity_time,
P_TOTAL_IDLE_TIME,
P_TOTAL_WAIT_TIME,
P_TOTAL_WRAPUP_TIME,
P_TOTAL_TALK_TIME,
P_CALLS_OFFERED,
P_PRED_CALLS_OFFERED,
P_OUTCOME_ID,
P_RESULT_ID,
l_result_count,
P_POSITIVE_RESPONSE_FLAG,
0,
0,
'5',
sysdate,
P_CONTACT_FLAG,
P_LOGIN_AGENT_COUNT
);
UPDATE_CPN_AGT_OUTCOME_DETAILS( P_DIAL_SERVER_ID
,P_CAMPAIGN_ID
,P_CAMPAIGN_SCHEDULE_ID
,P_OUTCOME_ID
,P_RESULT_ID
,l_result_count
,P_FTC_ABANDON_COUNT
,P_MESSAGE_PLAYED_COUNT
,P_POSITIVE_RESPONSE_FLAG
,P_CONTACT_FLAG
,P_TOTAL_IDLE_TIME
,P_TOTAL_WAIT_TIME
,P_TOTAL_TALK_TIME
,P_TOTAL_WRAPUP_TIME
,P_TOTAL_BREAK_TIME
,P_CALLS_OFFERED
,P_PRED_CALLS_OFFERED
);
END UPDATE_AGENT_OUTCOME_DETAILS;
PROCEDURE UPDATE_AGENT_CURRENT_STATE
( P_RESOURCE_ID IN NUMBER
,P_DIAL_SERVER_ID IN NUMBER
,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
,P_CURRENT_STATUS IN VARCHAR2
,P_CURRENT_STATUS_BEGIN_TIME IN VARCHAR2
)
AS
l_current_status_begin_time DATE;
-- Update all rows to '5' first and then set
-- the supplied cpn value with the "STATE".
-- '5' is state 'OUT' in fnd_lloups with lookup_type = 'BIX_DM_AGENT_STATUS'
--
Update iec_rep_agent_cpn_details /*+ index(iec_rep_agent_cpn_details iec_rep_agent_cpn_details_N1) */
set CURRENT_STATUS = '5'
where resource_Id = P_RESOURCE_ID
and dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id <> P_CAMPAIGN_SCHEDULE_ID;
Update iec_rep_agent_cpn_details /*+ index(iec_rep_agent_cpn_details iec_rep_agent_cpn_details_N1) */
set CURRENT_STATUS = P_CURRENT_STATUS,
current_status_begin_time = l_current_status_begin_time
where resource_Id = P_RESOURCE_ID
and dial_server_id = P_DIAL_SERVER_ID;
Update iec_rep_agent_cpn_details /*+ index(iec_rep_agent_cpn_details iec_rep_agent_cpn_details_N1) */
set CURRENT_STATUS = P_CURRENT_STATUS,
current_status_begin_time = l_current_status_begin_time
where resource_Id = P_RESOURCE_ID
and dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
END UPDATE_AGENT_CURRENT_STATE;
PROCEDURE UPDATE_CPN_AGT_OUTCOME_DETAILS
( P_DIAL_SERVER_ID IN NUMBER
,P_CAMPAIGN_ID IN NUMBER
,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
,P_OUTCOME_ID IN NUMBER
,P_RESULT_ID IN NUMBER
,P_RESULT_COUNT IN NUMBER
,P_FTC_ABANDON_COUNT IN NUMBER
,P_MESSAGE_PLAYED_COUNT IN NUMBER
,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
,P_CONTACT_FLAG IN VARCHAR2
,P_TOTAL_IDLE_TIME IN NUMBER
,P_TOTAL_WAIT_TIME IN NUMBER
,P_TOTAL_TALK_TIME IN NUMBER
,P_TOTAL_WRAPUP_TIME IN NUMBER
,P_TOTAL_BREAK_TIME IN NUMBER
,P_CALLS_OFFERED IN NUMBER
,P_PRED_CALLS_OFFERED IN NUMBER
)
AS
l_result_count NUMBER := 0;
select dialing_method into l_dialing_method from
iec_g_executing_lists_v where schedule_id = P_CAMPAIGN_SCHEDULE_ID;
select max( nvl( total_login_time, 0 ) ),
max( nvl( total_activity_time, 0 ) ),
max( nvl( total_idle_time, 0 ) ),
max( nvl( total_wait_time, 0 ) ),
max( nvl( total_talk_time, 0 ) ),
max( nvl( total_wrapup_time, 0 ) ),
max( nvl( calls_offered, 0 ) ),
max( nvl( predictive_calls_offered, 0 ) ),
max(nvl( longest_idle_time, 0 )),
max( nvl( longest_wait_time, 0 ) ),
max(nvl( longest_talk_time, 0 ) ),
max(nvl( longest_wrapup_time, 0 )),
max(nvl( shortest_idle_time, 0 )),
max(nvl( shortest_wait_time, 0 )),
max(nvl( shortest_talk_time, 0 )),
max(nvl( shortest_wrapup_time, 0 ))
into
l_mx_login_time,
l_mx_activity_time,
l_mx_idle_time,
l_mx_wait_time,
l_mx_talk_time,
l_mx_wrapup_time,
l_mx_calls_offered,
l_mx_pred_calls_offered,
l_ln_idle_time,
l_ln_wait_time,
l_ln_talk_time,
l_ln_wrapup_time,
l_sh_idle_time,
l_sh_wait_time,
l_sh_talk_time,
l_sh_wrapup_time
from iec_rep_campaign_details
where dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
-- dbms_output.put_line( 'After Select...<'|| l_mx_idle_time||'> <'||l_mx_login_time);
update iec_rep_campaign_details
set
agent_outcome_id = P_OUTCOME_ID,
agent_result_id = P_RESULT_ID,
dialing_mode = l_dialing_method,
agent_result_count = nvl( agent_result_count, 0 ) + l_result_count,
FTC_ABANDONMENT_COUNT = nvl(FTC_ABANDONMENT_COUNT, 0) + P_FTC_ABANDON_COUNT,
MESSAGE_PLAYED_COUNT = nvl(MESSAGE_PLAYED_COUNT, 0) + P_MESSAGE_PLAYED_COUNT,
positive_response_flag = P_POSITIVE_RESPONSE_FLAG,
contact_flag = P_CONTACT_FLAG,
last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = sysdate
where dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID
and (
(
( agent_outcome_id = P_OUTCOME_ID and agent_result_id = P_RESULT_id )
OR
( agent_outcome_id = -999999 and agent_result_id = -999999 )
)
AND
predictive_outcome_id = -999999
);
-- dbms_output.put_line( 'Inserting a new row as the outcomes are different...');
update iec_rep_campaign_details
set total_login_time = nvl( l_mx_login_time, 0 ) + l_login_time,
total_activity_time = nvl( l_mx_activity_time, 0 )+ l_activity_time,
total_idle_time = nvl( l_mx_idle_time, 0 ) + P_TOTAL_IDLE_TIME,
total_wait_time = nvl( l_mx_wait_time, 0 ) + P_TOTAL_WAIT_TIME,
total_talk_time = nvl( l_mx_talk_time, 0 ) + P_TOTAL_TALK_TIME,
total_wrapup_time = nvl( l_mx_wrapup_time, 0 ) + P_TOTAL_WRAPUP_TIME,
longest_idle_time = l_ln_idle_time,
longest_wait_time = l_ln_wait_time,
longest_talk_time = l_ln_talk_time,
longest_wrapup_time = l_ln_wrapup_time,
shortest_idle_time = l_sh_idle_time,
shortest_wait_time = l_sh_wait_time,
shortest_talk_time = l_sh_talk_time,
shortest_wrapup_time = l_sh_wrapup_time,
dialing_mode = l_dialing_method,
last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = sysdate
where dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
END UPDATE_CPN_AGT_OUTCOME_DETAILS;
PROCEDURE UPDATE_CPN_AGENT_STATS
( P_DIAL_SERVER_ID IN NUMBER
,P_CAMPAIGN_ID IN NUMBER
,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
,P_NUM_LOGIN_AGENTS IN NUMBER
,P_NUM_CPN_LOGIN_AGENTS IN NUMBER
,P_PREVIEW_DIALS IN NUMBER
,P_TIMED_PREVIEW_DIALS IN NUMBER
,P_PROGRESSIVE_DIALS IN NUMBER
,P_PREDICTIVE_DIALS IN NUMBER
,P_MANUAL_DIALS IN NUMBER
,P_PREDICTIVE_DIAL_FHQ_COUNT IN NUMBER
,P_CURRENT_IN_FHQ IN NUMBER
,P_NUM_AGENTS_ON_CALL IN NUMBER
,P_NUM_AGENTS_IN_WRAPUP IN NUMBER
,P_NUM_AGENTS_AVAILABLE IN NUMBER
,P_NUM_AGENTS_IDLE IN NUMBER
,P_NUM_AGENTS_ON_BREAK IN NUMBER
,P_NUM_VOICE_DETECTED IN NUMBER
,P_CALLS_OFFERED IN NUMBER
,P_PRED_CALLS_OFFERED IN NUMBER
)
AS
l_mx_login_agent NUMBER := 0;
select max( nvl( max_login_agent,0) ),
max( nvl( cpn_max_login_agent, 0) ),
max( nvl( preview_dials, 0 ) ),
max( nvl( timed_preview_dials, 0 ) ),
max( nvl( progressive_dials, 0 ) ),
max( nvl( manual_dials, 0 ) ),
max( nvl( predictive_dials, 0 ) ),
max( nvl( num_voice_detected, 0 ) ),
max( nvl( calls_offered, 0 )),
max( nvl( predictive_calls_offered, 0 ))
into
l_mx_login_agent,
l_mx_cpn_login_agent,
l_mx_preview_dials,
l_mx_timed_preview_dials,
l_mx_progressive_dials,
l_mx_manual_dials,
l_mx_predictive_dials,
l_mx_voice_detected,
l_mx_calls_offered,
l_mx_pred_calls_offered
from iec_rep_campaign_details
where ( dial_server_id = P_DIAL_SERVER_ID OR dial_server_id = 0 )
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
insert into iec_rep_campaign_details (
campaign_rep_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
campaign_id,
campaign_schedule_id,
dial_server_id,
dialing_mode,
total_login_time,
total_activity_time,
num_login_agent,
max_login_agent,
cpn_num_login_agent,
cpn_max_login_agent,
total_idle_time,
total_wait_time,
total_wrapup_time,
total_talk_time,
longest_idle_time,
longest_wait_time,
longest_talk_time,
longest_wrapup_time,
shortest_idle_time,
shortest_wait_time,
shortest_talk_time,
shortest_wrapup_time,
total_dials,
preview_dials,
timed_preview_dials,
progressive_dials,
predictive_dials,
manual_dials,
predictive_outcome_id,
predictive_result_id,
predictive_result_count,
predictive_dial_fhq_count,
calls_offered,
predictive_calls_offered,
agent_outcome_id,
agent_result_id,
agent_result_count,
positive_response_flag,
num_agents_on_call,
num_agents_in_wrapup,
num_agents_available,
num_agents_idle,
num_agents_on_break,
security_group_id,
object_version_number,
contact_flag,
num_cust_in_fhq,
num_voice_detected
)
values
(
iec_rep_campaign_details_s.nextval,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
P_CAMPAIGN_ID,
P_CAMPAIGN_SCHEDULE_ID,
P_DIAL_SERVER_ID,
'UNKN',
0,
0,
P_NUM_LOGIN_AGENTS,
P_NUM_LOGIN_AGENTS,
P_NUM_CPN_LOGIN_AGENTS,
P_NUM_CPN_LOGIN_AGENTS,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
l_mx_total_dials,
P_PREVIEW_DIALS,
P_TIMED_PREVIEW_DIALS,
P_PROGRESSIVE_DIALS,
P_PREDICTIVE_DIALS,
P_MANUAL_DIALS,
-999999,
-999999,
0,
P_PREDICTIVE_DIAL_FHQ_COUNT,
0,
0,
-999999,
-999999,
0,
'N',
P_NUM_AGENTS_ON_CALL,
P_NUM_AGENTS_IN_WRAPUP,
P_NUM_AGENTS_AVAILABLE,
P_NUM_AGENTS_IDLE,
P_NUM_AGENTS_ON_BREAK,
0,
0,
'N',
P_CURRENT_IN_FHQ,
0
);
update iec_rep_campaign_details
set num_login_agent = P_NUM_LOGIN_AGENTS,
max_login_agent = l_mx_login_agent,
cpn_num_login_agent = P_NUM_CPN_LOGIN_AGENTS,
cpn_max_login_agent = l_mx_cpn_login_agent,
total_dials = l_mx_total_dials,
preview_dials = l_mx_preview_dials,
timed_preview_dials = l_mx_timed_preview_dials,
progressive_dials = l_mx_PROGRESSIVE_DIALS,
predictive_dials = l_mx_PREDICTIVE_DIALS,
manual_dials = l_mx_MANUAL_DIALS,
num_voice_detected = nvl(l_mx_voice_detected, 0) + P_NUM_VOICE_DETECTED,
calls_offered = nvl( l_mx_calls_offered, 0 ) + P_CALLS_OFFERED,
predictive_calls_offered = nvl( l_mx_pred_calls_offered, 0 ) + P_PRED_CALLS_OFFERED,
predictive_dial_fhq_count = predictive_dial_fhq_count + P_PREDICTIVE_DIAL_FHQ_COUNT,
num_cust_in_fhq = P_CURRENT_IN_FHQ,
num_agents_on_call = P_NUM_AGENTS_ON_CALL,
num_agents_in_wrapup = P_NUM_AGENTS_IN_WRAPUP,
num_agents_available = P_NUM_AGENTS_AVAILABLE,
num_agents_idle = P_NUM_AGENTS_IDLE,
num_agents_on_break = P_NUM_AGENTS_ON_BREAK,
last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = sysdate
where ( dial_server_id = P_DIAL_SERVER_ID OR dial_server_id = 0 )
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
END UPDATE_CPN_AGENT_STATS;
PROCEDURE UPDATE_CPN_DIAL_STATS
( P_DIAL_SERVER_ID IN NUMBER
,P_CAMPAIGN_ID IN NUMBER
,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
,P_OUTCOME_ID IN NUMBER
,P_RESULT_ID IN NUMBER
,P_RESULT_COUNT IN NUMBER
,P_FTC_ABANDON_COUNT IN NUMBER
,P_MESSAGE_PLAYED_COUNT IN NUMBER
,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
,P_CONTACT_FLAG IN VARCHAR2
,P_TOTAL_DIALS IN NUMBER
)
AS
l_result_count NUMBER := 0;
insert into iec_rep_cpn_dial_stats (
cpn_dial_stats_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
campaign_id,
campaign_schedule_id,
dial_server_id,
data_start_time,
data_end_time,
total_dials,
outcome_id,
result_id,
result_count,
positive_response_flag,
security_group_id,
object_version_number,
contact_flag
)
values(
iec_rep_cpn_dial_stats_s.nextval,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
P_CAMPAIGN_ID,
P_CAMPAIGN_SCHEDULE_ID,
P_DIAL_SERVER_ID,
sysdate,
sysdate,
P_TOTAL_DIALS,
P_OUTCOME_ID,
P_RESULT_ID,
l_result_count,
P_POSITIVE_RESPONSE_FLAG,
0,
0,
P_CONTACT_FLAG
);
UPDATE_CPN_SYS_OUTCOME_DETAILS
( P_DIAL_SERVER_ID
,P_CAMPAIGN_ID
,P_CAMPAIGN_SCHEDULE_ID
,P_OUTCOME_ID
,P_RESULT_ID
,l_RESULT_COUNT
,P_FTC_ABANDON_COUNT
,P_MESSAGE_PLAYED_COUNT
,P_POSITIVE_RESPONSE_FLAG
,P_CONTACT_FLAG
,P_TOTAL_DIALS
);
END UPDATE_CPN_DIAL_STATS;
PROCEDURE UPDATE_CPN_SYS_OUTCOME_DETAILS
( P_DIAL_SERVER_ID IN NUMBER
,P_CAMPAIGN_ID IN NUMBER
,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
,P_PREDICTIVE_OUTCOME_ID IN NUMBER
,P_PREDICTIVE_RESULT_ID IN NUMBER
,P_PREDICTIVE_RESULT_COUNT IN NUMBER
,P_FTC_ABANDON_COUNT IN NUMBER
,P_MESSAGE_PLAYED_COUNT IN NUMBER
,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
,P_CONTACT_FLAG IN VARCHAR2
,P_TOTAL_DIALS IN NUMBER
)
AS
l_result_count NUMBER := 0;
select dialing_method into l_dialing_method from
iec_g_executing_lists_v where schedule_id = P_CAMPAIGN_SCHEDULE_ID;
select campaign_rep_id
into
l_rec_present
from iec_rep_campaign_details
where dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID
and predictive_outcome_id = P_PREDICTIVE_OUTCOME_ID
and predictive_result_id = P_PREDICTIVE_RESULT_ID
and nvl(agent_outcome_id, -999999 ) < 0;
update iec_rep_campaign_details
set predictive_outcome_id = P_PREDICTIVE_OUTCOME_ID,
predictive_result_id = P_PREDICTIVE_RESULT_ID,
dialing_mode = l_dialing_method,
predictive_result_count = nvl( predictive_result_count, 0 ) + l_result_count,
FTC_ABANDONMENT_COUNT = nvl(FTC_ABANDONMENT_COUNT, 0) + P_FTC_ABANDON_COUNT,
MESSAGE_PLAYED_COUNT = nvl(MESSAGE_PLAYED_COUNT, 0) + P_MESSAGE_PLAYED_COUNT,
positive_response_flag = P_POSITIVE_RESPONSE_FLAG,
contact_flag = P_CONTACT_FLAG,
total_dials = l_TOTAL_DIALS,
last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = sysdate
where campaign_rep_id = l_rec_present;
select campaign_rep_id
into
l_rec_present
from iec_rep_campaign_details
where dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID
and nvl(predictive_outcome_id, -999999 ) = -999999
and nvl(predictive_result_id, -999999) = -999999
and nvl(agent_outcome_id, -999999 ) < 0;
update iec_rep_campaign_details
set predictive_outcome_id = P_PREDICTIVE_OUTCOME_ID,
predictive_result_id = P_PREDICTIVE_RESULT_ID,
dialing_mode = l_dialing_method,
predictive_result_count = nvl( predictive_result_count, 0 ) + l_result_count,
FTC_ABANDONMENT_COUNT = nvl(FTC_ABANDONMENT_COUNT, 0) + P_FTC_ABANDON_COUNT,
MESSAGE_PLAYED_COUNT = nvl(MESSAGE_PLAYED_COUNT, 0) + P_MESSAGE_PLAYED_COUNT,
positive_response_flag = P_POSITIVE_RESPONSE_FLAG,
contact_flag = P_CONTACT_FLAG,
total_dials = l_TOTAL_DIALS,
last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = sysdate
where campaign_rep_id = l_rec_present;
-- dbms_output.put_line( 'After Select...<'|| l_mx_idle_time||'> <'||l_mx_login_time);
select max( nvl( total_login_time, 0 ) ),
max( nvl( total_activity_time, 0 ) ),
max( nvl( total_idle_time, 0 ) ),
max( nvl( total_wait_time, 0 ) ),
max( nvl( total_talk_time, 0 ) ),
max( nvl( total_wrapup_time, 0 ) ),
max( nvl( calls_offered, 0 ) ),
max( nvl( predictive_calls_offered, 0 ) ),
max(nvl( longest_idle_time, 0 )),
max( nvl( longest_wait_time, 0 ) ),
max(nvl( longest_talk_time, 0 ) ),
max(nvl( longest_wrapup_time, 0 )),
max(nvl( shortest_idle_time, 0 )),
max(nvl( shortest_wait_time, 0 )),
max(nvl( shortest_talk_time, 0 )),
max(nvl( shortest_wrapup_time, 0 ))
into
l_mx_login_time,
l_mx_activity_time,
l_mx_idle_time,
l_mx_wait_time,
l_mx_talk_time,
l_mx_wrapup_time,
l_mx_calls_offered,
l_mx_pred_calls_offered,
l_ln_idle_time,
l_ln_wait_time,
l_ln_talk_time,
l_ln_wrapup_time,
l_sh_idle_time,
l_sh_wait_time,
l_sh_talk_time,
l_sh_wrapup_time
from iec_rep_campaign_details
where dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
update iec_rep_campaign_details
set total_login_time = l_mx_login_time,
total_activity_time = l_mx_activity_time,
total_idle_time = l_mx_idle_time,
total_wait_time = l_mx_wait_time,
total_talk_time = l_mx_talk_time,
total_wrapup_time = l_mx_wrapup_time,
longest_idle_time = l_ln_idle_time,
longest_wait_time = l_ln_wait_time,
longest_talk_time = l_ln_talk_time,
longest_wrapup_time = l_ln_wrapup_time,
shortest_idle_time = l_sh_idle_time,
shortest_wait_time = l_sh_wait_time,
shortest_talk_time = l_sh_talk_time,
shortest_wrapup_time = l_sh_wrapup_time,
calls_offered = l_mx_calls_offered,
predictive_calls_offered = l_mx_pred_calls_offered,
dialing_mode = l_dialing_method,
last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
last_update_date = sysdate
where dial_server_id = P_DIAL_SERVER_ID
and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
END UPDATE_CPN_SYS_OUTCOME_DETAILS;
insert into iec_rep_campaign_details (
campaign_rep_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
campaign_id,
campaign_schedule_id,
dial_server_id,
dialing_mode,
total_login_time,
total_activity_time,
num_login_agent,
max_login_agent,
total_idle_time,
total_wait_time,
total_wrapup_time,
total_talk_time,
longest_idle_time,
longest_wait_time,
longest_talk_time,
longest_wrapup_time,
shortest_idle_time,
shortest_wait_time,
shortest_talk_time,
shortest_wrapup_time,
total_dials,
preview_dials,
progressive_dials,
predictive_dials,
manual_dials,
predictive_outcome_id,
predictive_result_id,
predictive_result_count,
predictive_dial_fhq_count,
calls_offered,
predictive_calls_offered,
agent_outcome_id,
agent_result_id,
agent_result_count,
positive_response_flag,
num_agents_on_call,
num_agents_in_wrapup,
num_agents_available,
num_agents_idle,
num_agents_on_break,
security_group_id,
object_version_number,
contact_flag,
num_cust_in_fhq,
num_voice_detected,
FTC_ABANDONMENT_COUNT,
MESSAGE_PLAYED_COUNT
)
values
(
iec_rep_campaign_details_s.nextval,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
P_CAMPAIGN_ID,
P_CAMPAIGN_SCHEDULE_ID,
P_DIAL_SERVER_ID,
'UNKN',
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
-999999,
-999999,
0,
0,
0,
0,
P_OUTCOME_ID,
P_RESULT_ID,
P_RESULT_COUNT,
P_POSITIVE_RESPONSE_FLAG,
0,
0,
0,
0,
0,
0,
0,
P_CONTACT_FLAG,
0,
0,
P_FTC_ABANDON_COUNT,
P_MESSAGE_PLAYED_COUNT
);
insert into iec_rep_campaign_details (
campaign_rep_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
campaign_id,
campaign_schedule_id,
dial_server_id,
dialing_mode,
total_login_time,
total_activity_time,
num_login_agent,
max_login_agent,
total_idle_time,
total_wait_time,
total_wrapup_time,
total_talk_time,
longest_idle_time,
longest_wait_time,
longest_talk_time,
longest_wrapup_time,
shortest_idle_time,
shortest_wait_time,
shortest_talk_time,
shortest_wrapup_time,
total_dials,
preview_dials,
progressive_dials,
predictive_dials,
manual_dials,
predictive_outcome_id,
predictive_result_id,
predictive_result_count,
predictive_dial_fhq_count,
calls_offered,
predictive_calls_offered,
agent_outcome_id,
agent_result_id,
agent_result_count,
positive_response_flag,
num_agents_on_call,
num_agents_in_wrapup,
num_agents_available,
num_agents_idle,
num_agents_on_break,
security_group_id,
object_version_number,
contact_flag,
num_cust_in_fhq,
num_voice_detected,
FTC_ABANDONMENT_COUNT,
MESSAGE_PLAYED_COUNT
)
values
(
iec_rep_campaign_details_s.nextval,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
sysdate,
NVL(FND_GLOBAL.conc_login_id,-1),
P_CAMPAIGN_ID,
P_CAMPAIGN_SCHEDULE_ID,
P_DIAL_SERVER_ID,
P_DIALING_Method,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
P_PREDICTIVE_RESULT_COUNT,
0,
0,
0,
0,
P_PREDICTIVE_OUTCOME_ID,
P_PREDICTIVE_RESULT_ID,
P_PREDICTIVE_RESULT_COUNT,
0,
0,
0,
-999999,
-999999,
0,
P_POSITIVE_RESPONSE_FLAG,
0,
0,
0,
0,
0,
0,
0,
P_CONTACT_FLAG,
0,
0,
P_FTC_ABANDON_COUNT,
P_MESSAGE_PLAYED_COUNT
);
PROCEDURE UPDATE_ITEM_CC_TZS_COUNTS (
X_ITM_CC_TZ_ID IN NUMBER,
X_STATUS OUT NOCOPY VARCHAR2
) is
L_RECORD_AVAIL_COUNT NUMBER;
SELECT NVL(COUNT(*), 0)
INTO L_RECORD_UNAVAIL_COUNT
FROM iec_g_return_entries a, IEC_G_MKTG_ITEM_CC_TZS C
WHERE C.ITM_CC_TZ_ID = a.itm_cc_tz_id
AND a.itm_cc_tz_id = X_ITM_CC_TZ_ID
AND NVL(a.DO_NOT_USE_FLAG, 'N') = 'N'
AND (C.CALLABLE_FLAG IS NULL
OR C.CALLABLE_FLAG <> 'Y'
OR C.LAST_CALLABLE_TIME < SYSDATE
OR (C.CALLABLE_FLAG = 'Y' AND C.LAST_CALLABLE_TIME > SYSDATE
AND a.callback_flag = 'Y' AND a.next_call_time > sysdate));
SELECT A.CALLABLE_FLAG, A.LAST_CALLABLE_TIME
INTO L_CALLABLE_FLAG, L_LAST_CALLABLE_TIME
FROM IEC_G_MKTG_ITEM_CC_TZS A
WHERE A.ITM_CC_TZ_ID = X_ITM_CC_TZ_ID;
SELECT NVL(COUNT(*), 0)
INTO L_RECORD_AVAIL_COUNT
FROM iec_g_return_entries a, IEC_G_MKTG_ITEM_CC_TZS C
WHERE C.ITM_CC_TZ_ID = a.itm_cc_tz_id
AND a.itm_cc_tz_id = X_ITM_CC_TZ_ID
AND NVL(a.DO_NOT_USE_FLAG, 'N') = 'N'
AND (C.CALLABLE_FLAG = 'Y' AND C.LAST_CALLABLE_TIME > SYSDATE
AND (NVL(a.callback_flag, 'N') = 'N'
OR a.next_call_time < sysdate));
UPDATE IEC_G_MKTG_ITEM_CC_TZS
SET
ORG_CALLABLE_FLAG = CALLABLE_FLAG,
ORG_LAST_UPDATE_DATE = LAST_UPDATE_DATE,
ORG_LAST_CALLABLE_TIME = LAST_CALLABLE_TIME,
COUNT_LAST_UPDATE_DATE = SYSDATE,
ORG_RECORD_COUNT = RECORD_COUNT,
RECORD_AVAILABLE = L_RECORD_AVAIL_COUNT,
RECORD_UNAVAILABLE = L_RECORD_UNAVAIL_COUNT
WHERE ITM_CC_TZ_ID = X_ITM_CC_TZ_ID;
END UPDATE_ITEM_CC_TZS_COUNTS;
SELECT A.ITM_CC_TZ_ID
FROM IEC_G_MKTG_ITEM_CC_TZS A
WHERE COUNT_LAST_UPDATE_DATE is NULL
OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE
OR A.ORG_CALLABLE_FLAG <> A.CALLABLE_FLAG
OR A.ORG_LAST_CALLABLE_TIME <> A.LAST_CALLABLE_TIME
OR A.ORG_RECORD_COUNT <> A.RECORD_COUNT
OR ((A.CALLABLE_FLAG <> 'Y' OR A.LAST_CALLABLE_TIME < SYSDATE)
AND RECORD_AVAILABLE > 0 )
OR (A.CALLABLE_FLAG = 'Y'
AND A.LAST_CALLABLE_TIME > SYSDATE
AND RECORD_UNAVAILABLE > 0
AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 3/(24*60))
OR (A.CALLABLE_FLAG = 'Y'
AND A.LAST_CALLABLE_TIME > SYSDATE
AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 5/(24*60))
OR A.COUNT_LAST_UPDATE_DATE < sysdate - 1;
UPDATE_ITEM_CC_TZS_COUNTS (
X_ITM_CC_TZ_ID => L_ITM_CC_TZ_ID,
X_STATUS => L_STATUS
);
SELECT A.ITM_CC_TZ_ID
FROM IEC_G_MKTG_ITEM_CC_TZS A
WHERE A.CAMPAIGN_SCHEDULE_ID in
(SELECT UNIQUE C.SCHEDULE_ID
FROM IEC_G_SCHEDULES_V c
WHERE C.CAMPAIGN_ID = X_CAMPAIGN_ID)
AND (A.COUNT_LAST_UPDATE_DATE is NULL
OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE
OR A.ORG_CALLABLE_FLAG <> A.CALLABLE_FLAG
OR A.ORG_LAST_CALLABLE_TIME <> A.LAST_CALLABLE_TIME
OR A.ORG_RECORD_COUNT <> A.RECORD_COUNT
OR ((A.CALLABLE_FLAG <> 'Y' OR A.LAST_CALLABLE_TIME < SYSDATE)
AND RECORD_AVAILABLE > 0 )
OR (A.CALLABLE_FLAG = 'Y'
AND A.LAST_CALLABLE_TIME > SYSDATE
AND RECORD_UNAVAILABLE > 0
AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 1/(24*60))
OR (A.CALLABLE_FLAG = 'Y'
AND A.LAST_CALLABLE_TIME > SYSDATE
AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 5/(24*60))
OR A.COUNT_LAST_UPDATE_DATE < sysdate - 1);
UPDATE_ITEM_CC_TZS_COUNTS (
X_ITM_CC_TZ_ID => L_ITM_CC_TZ_ID,
X_STATUS => L_STATUS
);
SELECT A.ITM_CC_TZ_ID
FROM IEC_G_MKTG_ITEM_CC_TZS A
WHERE A.CAMPAIGN_SCHEDULE_ID = X_SCHEDULE_ID
AND (A.COUNT_LAST_UPDATE_DATE is NULL
OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE
OR A.ORG_CALLABLE_FLAG <> A.CALLABLE_FLAG
OR A.ORG_LAST_CALLABLE_TIME <> A.LAST_CALLABLE_TIME
OR A.ORG_RECORD_COUNT <> A.RECORD_COUNT
OR ((A.CALLABLE_FLAG <> 'Y' OR A.LAST_CALLABLE_TIME < SYSDATE)
AND RECORD_AVAILABLE > 0 )
OR (A.CALLABLE_FLAG = 'Y'
AND A.LAST_CALLABLE_TIME > SYSDATE
AND RECORD_UNAVAILABLE > 0
AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 1/(24*60))
OR (A.CALLABLE_FLAG = 'Y'
AND A.LAST_CALLABLE_TIME > SYSDATE
AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 5/(24*60))
OR A.COUNT_LAST_UPDATE_DATE < sysdate - 1);
UPDATE_ITEM_CC_TZS_COUNTS (
X_ITM_CC_TZ_ID => L_ITM_CC_TZ_ID,
X_STATUS => L_STATUS
);