[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
SELECT ceil(g_collect_end_date - g_collect_start_date)
INTO l_count
FROM dual;
Delete BIX_WORKER_JOBS WHERE OBJECT_NAME = 'BIX_AGENT_SESSION_F';
INSERT INTO BIX_WORKER_JOBS(OBJECT_NAME
, START_DATE_RANGE
, END_DATE_RANGE
, WORKER_NUMBER
, STATUS)
VALUES (
'BIX_AGENT_SESSION_F'
, l_start_date_range
, l_end_date_range
, l_count
, 'UNASSIGNED');
l_total_rows_deleted NUMBER := 0;
l_rows_deleted NUMBER := 0;
/* Delete all the rows inserted from subworkers */
IF (g_worker.COUNT > 0) THEN
FOR i IN g_worker.FIRST .. g_worker.LAST
LOOP
LOOP
DELETE bix_agent_session_f
WHERE request_id = g_worker(i)
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;
/* Deleting all rows inserted by this main program */
LOOP
DELETE bix_agent_session_f
WHERE request_id = g_request_id
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;
write_log('Number of rows deleted from bix_agent_session_f : ' || to_char(l_total_rows_deleted));
PROCEDURE insert_login_row(p_session_id in g_session_id_tab,
p_agent_id in g_resource_id_tab,
p_session_begin_date in g_begin_date_time_tab,
p_session_end_date in g_end_date_time_tab,
p_last_collect_date in g_last_collect_date_tab,
p_server_group_id in g_server_group_id_tab,
p_application_id in g_application_id_tab)
IS
TYPE login_time_tab is TABLE OF bix_agent_session_f.login_time%TYPE;
write_log('Start of the procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
SELECT trunc(l_begin_date)
INTO l_period_start
FROM DUAL;
/* Loop through the session record and insert a record for each half hour bucket */
WHILE ( l_period_start < l_end_date )
LOOP
j := j + 1;
/* Bulk insert all the rows in the staging area */
IF (l_agent_id.COUNT > 0) THEN
FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
INSERT /*+ append */ INTO bix_agent_session_stg (
agent_id
,server_group_id
,schedule_id
,campaign_id
,application_id
,time_id
,period_type_id
,period_start_date
,period_start_time
,day_of_week
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,login_time
,request_id
,program_application_id
,program_id
,program_update_date)
VALUES (
l_agent_id(i)
,l_server_group_id(i)
,-1
,-1
,l_application_id(i)
,to_number(to_char(l_period_start_date(i), 'J'))
,1
,TRUNC(l_period_start_date(i))
,'00:00'
,TO_NUMBER(TO_CHAR(l_period_start_date(i),'D'))
,g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_user_id
,decode(l_login_time(i), 0, to_number(null), l_login_time(i))
,g_request_id
,g_program_appl_id
,g_program_id
,g_sysdate);
write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
SELECT
l_session_id(i) session_id,
l_collect_date(i) curr_collect_date
FROM dual ) change
ON ( bis1.session_id = change.session_id )
WHEN MATCHED THEN
UPDATE SET
bis1.curr_collect_date = change.curr_collect_date
,bis1.last_update_date = g_sysdate
,bis1.last_updated_by = g_user_id
,bis1.program_update_date = g_sysdate
WHEN NOT MATCHED THEN INSERT (
bis1.session_id,
bis1.created_by,
bis1.creation_date,
bis1.last_updated_by,
bis1.last_update_date,
bis1.curr_collect_date,
bis1.request_id,
bis1.program_application_id,
bis1.program_id,
bis1.program_update_date )
VALUES (
change.session_id,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
change.curr_collect_date,
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate);
write_log('Finished procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in insert_login_row : Error : ' || sqlerrm);
END insert_login_row;
SELECT
iss.session_id session_id
,iss.resource_id resource_id
,iss.begin_date_time begin_date_time
,iss.end_date_time end_date_time
,bis1.last_collect_date last_collect_date
,nvl(res.server_group_id,-1) server_group_id
,decode(iss.application_id, 696, 696, 680, 680, 0) application_id
FROM
ieu_sh_sessions iss
,bix_sessions bis1
,jtf_rs_resource_extns res
WHERE iss.last_update_date > g_collect_start_date
AND iss.last_update_date <= g_collect_end_date
AND iss.session_id = bis1.session_id(+)
AND iss.resource_id = res.resource_id
AND iss.end_date_time IS NOT NULL
UNION ALL
SELECT
inv1.session_id session_id
,inv1.resource_id resource_id
,inv1.begin_date_time begin_date_time
,decode(max(mseg.start_date_time), to_date(null), inv1.begin_date_time, max(mseg.start_date_time))
end_date_time
,bis1.last_collect_date last_collect_date
,nvl(res.server_group_id,-1) server_group_id
,decode(inv1.application_id, 696, 696, 680, 680, 0)
application_id
FROM
( SELECT msegs.* FROM jtf_ih_media_item_lc_segs msegs
,jtf_ih_media_itm_lc_seg_tys segs
WHERE msegs.milcs_type_id = segs.milcs_type_id
AND segs.milcs_code IN
('EMAIL_FETCH'
,'EMAIL_REPLY'
,'EMAIL_DELETED'
,'EMAIL_OPEN'
,'EMAIL_REQUEUED'
,'EMAIL_REROUTED_DIFF_CLASS'
,'EMAIL_REROUTED_DIFF_ACCT'
,'EMAIL_SENT'
,'EMAIL_TRANSFERRED'
,'EMAIL_ASSIGN'
,'EMAIL_COMPOSE'
,'WITH_AGENT'
,'EMAIL_ESCALATED'
)
) mseg
,bix_sessions bis1
,jtf_rs_resource_extns res
,(
SELECT
iss1.session_id session_id
, iss1.resource_id resource_id
, iss1.application_id application_id
, iss1.begin_date_time begin_date_time
, iss1.end_date_time end_date_time
, min(iss2.begin_date_time) next_sess_begin_date_time
FROM
ieu_sh_sessions iss1
,ieu_sh_sessions iss2
WHERE iss1.active_flag = 'T'
AND iss1.resource_id = iss2.resource_id(+)
AND iss2.begin_date_time(+) > iss1.begin_date_time
GROUP BY iss1.session_id, iss1.resource_id, iss1.application_id, iss1.begin_date_time, iss1.end_date_time
) inv1
WHERE inv1.resource_id = res.resource_id
AND mseg.resource_id(+) = inv1.resource_id
AND mseg.start_date_time(+) >= inv1.begin_date_time
AND mseg.start_date_time(+) < nvl(inv1.next_sess_begin_date_time, g_sysdate)
AND inv1.session_id = bis1.session_id(+)
GROUP BY inv1.session_id, inv1.resource_id, inv1.begin_date_time, bis1.last_collect_date, res.server_group_id, inv1.application_id;
insert_login_row(
l_session_id,
l_resource_id,
l_begin_date_time,
l_end_date_time,
l_last_collect_date,
l_server_group_id,
l_application_id);
PROCEDURE insert_work_row(p_activity_id in g_activity_id_tab,
p_agent_id in g_resource_id_tab,
p_activity_begin_date in g_begin_date_time_tab,
p_activity_end_date in g_end_date_time_tab,
p_last_collect_date in g_last_collect_date_tab,
p_server_group_id in g_server_group_id_tab,
p_application_id in g_application_id_tab,
p_schedule_id in g_schedule_id_tab,
p_campaign_id in g_campaign_id_tab)
IS
TYPE work_time_tab is TABLE OF bix_agent_session_f.work_time%TYPE;
write_log('Start of the procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
SELECT trunc(l_begin_date)
INTO l_period_start
FROM DUAL;
/* Loop through the session record and insert a record for each half hour bucket */
WHILE ( l_period_start < l_end_date )
LOOP
j := j + 1;
/* Bulk insert all the rows in the staging area */
IF (l_agent_id.COUNT > 0) THEN
FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
INSERT /*+ append */ INTO bix_agent_session_stg (
agent_id
,server_group_id
,schedule_id
,campaign_id
,application_id
,time_id
,period_type_id
,period_start_date
,period_start_time
,day_of_week
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,work_time
,request_id
,program_application_id
,program_id
,program_update_date)
VALUES (
l_agent_id(i)
,l_server_group_id(i)
,l_schedule_id(i)
,l_campaign_id(i)
,l_application_id(i)
,to_number(to_char(l_period_start_date(i), 'J'))
,1
,TRUNC(l_period_start_date(i))
,'00:00'
,TO_CHAR(l_period_start_date(i),'D')
,g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_user_id
,decode(l_work_time(i), 0, to_number(null), l_work_time(i))
,g_request_id
,g_program_appl_id
,g_program_id
,g_sysdate);
write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
write_log('Finished procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in insert_work_row : Error : ' || sqlerrm);
END insert_work_row;
SELECT
isa.activity_id activity_id
,iss.resource_id resource_id
,isa.begin_date_time begin_date_time
/* ,nvl(isa.end_date_time, bis1.curr_collect_date) end_date_time*/
,nvl(isa.end_date_time, nvl(isamed.end_Date_time,isa.begin_Date_time)) end_date_time
,bis1.last_collect_date last_collect_date
,nvl(res.server_group_id,-1) server_group_id
,iss.application_id application_id
,decode(isa.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
schedule_id
,decode(isa.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
campaign_id
FROM
ieu_sh_sessions iss
,ieu_sh_activities isa
,bix_sessions bis1
,jtf_rs_resource_extns res
,ams_campaign_schedules_b csh
,(select parent_cycle_id,max(isamed.end_Date_time) end_date_time from ieu_sh_Activities isamed,ieu_sh_sessions isamedsess
where activity_type_code='MEDIA'
and isamedsess.last_update_date > g_collect_start_date-2 --dummy filter to force index scan
and isamed.last_update_date > g_collect_start_date
AND isamed.last_update_date <= g_collect_end_date
and isamedsess.session_id=isamed.session_id
and isamedsess.application_id=696
group by parent_cycle_id) isamed
WHERE isa.last_update_date > g_collect_start_date
AND iss.last_update_date > g_collect_start_date-2
AND isa.last_update_date <= g_collect_end_date
AND isa.activity_id=isamed.parent_cycle_id(+)
AND iss.application_id = 696
AND iss.session_id = isa.session_id
AND isa.activity_type_code = 'MEDIA_CYCLE'
AND iss.session_id = bis1.session_id
AND iss.resource_id = res.resource_id
AND decode(isa.category_type, 'CSCH', to_number(nvl(isa.category_value, -1)), -1) = csh.schedule_id(+);
insert_work_row(
l_activity_id,
l_resource_id,
l_begin_date_time,
l_end_date_time,
l_last_collect_date,
l_server_group_id,
l_application_id,
l_schedule_id,
l_campaign_id);
PROCEDURE insert_available_row(p_activity_id in g_activity_id_tab,
p_agent_id in g_resource_id_tab,
p_activity_begin_date in g_begin_date_time_tab,
p_activity_end_date in g_end_date_time_tab,
p_last_collect_date in g_last_collect_date_tab,
p_server_group_id in g_server_group_id_tab,
p_application_id in g_application_id_tab,
p_schedule_id in g_schedule_id_tab,
p_campaign_id in g_campaign_id_tab)
IS
TYPE available_time_tab is TABLE OF bix_agent_session_f.available_time%TYPE;
write_log('Start of the procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
SELECT trunc(l_begin_date)
INTO l_period_start
FROM DUAL;
/* Loop through the session record and insert a record for each half hour bucket */
WHILE ( l_period_start < l_end_date )
LOOP
j := j + 1;
/* Bulk insert all the rows in the staging area */
IF (l_agent_id.COUNT > 0) THEN
FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
INSERT /*+ append */ INTO bix_agent_session_stg (
agent_id
,server_group_id
,schedule_id
,campaign_id
,application_id
,time_id
,period_type_id
,period_start_date
,period_start_time
,day_of_week
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,available_time
,request_id
,program_application_id
,program_id
,program_update_date)
VALUES (
l_agent_id(i)
,l_server_group_id(i)
,l_schedule_id(i)
,l_campaign_id(i)
,l_application_id(i)
,to_number(to_char(l_period_start_date(i), 'J'))
,1
,TRUNC(l_period_start_date(i))
,'00:00'
,TO_CHAR(l_period_start_date(i),'D')
,g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_user_id
,decode(l_available_time(i), 0, to_number(null), l_available_time(i))
,g_request_id
,g_program_appl_id
,g_program_id
,g_sysdate);
write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
write_log('Finished procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
write_log('Error in insert_available_row : Error : ' || sqlerrm);
END insert_available_row;
SELECT
/*+ parallel(iss) parallel(isa1) parallel(isa2) parallel(bis1) parallel(res) parallel(csh)
pq_distribute(iss hash,hash) pq_distribute(isa1 hash,hash)
pq_distribute(isa2 hash,hash) pq_distribute(bis1 hash,hash)
pq_distribute(res hash,hash) pq_distribute(csh hash,hash)
use_hash(iss,isa1,isa2,bis1,res,csh) */
isa1.activity_id activity_id
,iss.resource_id resource_id
,isa1.begin_date_time begin_date_time
/* ,nvl(isa1.deliver_date_time, nvl(isa1.end_date_time, bis1.curr_collect_date)) */
,nvl(isa1.deliver_date_time, nvl(isa1.end_date_time, isa1.begin_date_time))
end_date_time
,bis1.last_collect_date last_collect_date
,nvl(res.server_group_id,-1) server_group_id
,iss.application_id application_id
,decode(isa2.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
schedule_id
,decode(isa2.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
campaign_id
FROM
ieu_sh_sessions iss
,ieu_sh_activities isa1
,ieu_sh_activities isa2
,bix_sessions bis1
,jtf_rs_resource_extns res
,ams_campaign_schedules_b csh
WHERE isa1.last_update_date > g_collect_start_date
AND isa1.last_update_date <= g_collect_end_date
AND iss.application_id = 696
AND iss.session_id = isa1.session_id
AND isa1.activity_type_code = 'MEDIA'
AND isa1.parent_cycle_id = isa2.activity_id
AND isa2.activity_type_code = 'MEDIA_CYCLE'
AND iss.session_id = bis1.session_id
AND iss.resource_id = res.resource_id
AND decode(isa2.category_type, 'CSCH', to_number(nvl(isa2.category_value, -1)), -1) = csh.schedule_id(+);
insert_available_row(
l_activity_id,
l_resource_id,
l_begin_date_time,
l_end_date_time,
l_last_collect_date,
l_server_group_id,
l_application_id,
l_schedule_id,
l_campaign_id);
INSERT /*+ append */ INTO bix_agent_session_stg
(agent_id,
server_group_id,
schedule_id,
campaign_id,
application_id,
time_id,
period_type_id,
period_start_date,
period_start_time,
day_of_week,
created_by,
creation_date,
last_updated_by,
last_update_date,
idle_time,
request_id,
program_application_id,
program_id,
program_update_date )
(SELECT
bas.agent_id,
bas.server_group_id,
-1,
-1,
bas.application_id,
bas.time_id,
bas.period_type_id,
bas.period_start_date,
bas.period_start_time,
bas.day_of_week,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
decode(nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0), 0, to_number(null),
nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0)),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate
FROM bix_agent_session_stg bas
WHERE bas.application_id = 696
GROUP BY
bas.agent_id,
bas.server_group_id,
bas.application_id,
bas.time_id,
bas.period_type_id,
bas.period_start_date,
bas.period_start_time,
bas.day_of_week);
/* Update bix_sessions to set the last collection date with the current collection date */
/* First update the rows inserted/updated from the workers */
IF (g_worker.COUNT > 0) THEN
FOR i IN g_worker.FIRST .. g_worker.LAST
LOOP
UPDATE bix_sessions
SET last_collect_date = curr_collect_date
WHERE request_id = g_worker(i);
/* Update all rows inserted/updated by this main program */
UPDATE bix_sessions
SET last_collect_date = curr_collect_date
WHERE request_id = g_request_id;
SELECT
bstg.agent_id agent_id,
bstg.server_group_id server_group_id,
bstg.schedule_id schedule_id,
bstg.campaign_id campaign_id,
bstg.application_id application_id,
bstg.time_id time_id,
bstg.period_type_id period_type_id,
bstg.period_start_date period_start_date,
bstg.period_start_time period_start_time,
bstg.day_of_week day_of_week,
sum(bstg.login_time) login_time,
sum(bstg.work_time) work_time,
sum(bstg.available_time) available_time,
sum(bstg.idle_time) idle_time,
nvl(sum(bstg.login_time), 0) * g_agent_cost agent_cost
FROM bix_agent_session_stg bstg
GROUP BY
bstg.agent_id,
bstg.server_group_id,
bstg.schedule_id,
bstg.campaign_id,
bstg.application_id,
bstg.time_id,
bstg.period_type_id,
bstg.period_start_date,
bstg.period_start_time,
bstg.day_of_week) change
ON ( bas.agent_id = change.agent_id
AND bas.server_group_id = change.server_group_id
AND bas.schedule_id = change.schedule_id
AND bas.campaign_id = change.campaign_id
AND bas.application_id = change.application_id
AND bas.time_id = change.time_id
AND bas.period_type_id = change.period_type_id
AND bas.period_start_date = change.period_start_date
AND bas.period_start_time = change.period_start_time
AND bas.day_of_week = change.day_of_week)
WHEN MATCHED THEN
UPDATE SET
bas.login_time = decode(nvl(change.login_time,0), 0, bas.login_time, nvl(bas.login_time, 0) + change.login_time)
,bas.work_time = decode(nvl(change.work_time,0), 0, bas.work_time, nvl(bas.work_time, 0) + change.work_time)
,bas.available_time = decode(nvl(change.available_time,0), 0, bas.available_time, nvl(bas.available_time,0)
+ change.available_time)
,bas.idle_time = decode(nvl(change.idle_time,0), 0, bas.idle_time, nvl(bas.idle_time, 0) + change.idle_time)
,bas.agent_cost = decode(nvl(change.agent_cost,0), 0, bas.agent_cost, nvl(bas.agent_cost, 0) + change.agent_cost)
,bas.last_update_date = g_sysdate
,bas.last_updated_by = g_user_id
,bas.program_update_date = g_sysdate
WHEN NOT MATCHED THEN INSERT
(bas.agent_id,
bas.server_group_id,
bas.schedule_id,
bas.campaign_id,
bas.application_id,
bas.time_id,
bas.period_type_id,
bas.period_start_date,
bas.period_start_time,
bas.day_of_week,
bas.created_by,
bas.creation_date,
bas.last_updated_by,
bas.last_update_date,
bas.login_time,
bas.work_time,
bas.available_time,
bas.idle_time,
bas.agent_cost,
bas.request_id,
bas.program_application_id,
bas.program_id,
bas.program_update_date )
VALUES (
change.agent_id,
change.server_group_id,
change.schedule_id,
change.campaign_id,
change.application_id,
change.time_id,
change.period_type_id,
change.period_start_date,
change.period_start_time,
change.day_of_week,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
decode(change.login_time, 0, to_number(null), change.login_time),
decode(change.work_time, 0, to_number(null), change.work_time),
decode(change.available_time, 0, to_number(null), change.available_time),
decode(change.idle_time, 0, to_number(null), change.idle_time),
decode(change.agent_cost, 0, to_number(null), change.agent_cost),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate);
INSERT /*+ append */ INTO bix_agent_session_stg
(agent_id,
server_group_id,
schedule_id,
campaign_id,
application_id,
time_id,
period_type_id,
period_start_date,
period_start_time,
day_of_week,
created_by,
creation_date,
last_updated_by,
last_update_date,
login_time,
work_time,
available_time,
idle_time,
request_id,
program_application_id,
program_id,
program_update_date )
(SELECT
bas.agent_id,
bas.server_group_id,
bas.schedule_id,
bas.campaign_id,
bas.application_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), 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',
bas.day_of_week,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
sum(bas.login_time),
sum(bas.work_time),
sum(bas.available_time),
sum(bas.idle_time),
g_request_id,
g_program_appl_id,
g_program_id,
g_sysdate
FROM bix_agent_session_stg bas,
fii_time_day ftd
WHERE bas.time_id = ftd.report_date_julian
AND bas.period_type_id = 1
GROUP BY
bas.agent_id,
bas.server_group_id,
bas.schedule_id,
bas.campaign_id,
bas.application_id,
bas.day_of_week,
ROLLUP (
ftd.ent_year_id,
ftd.ent_qtr_id,
ftd.ent_period_id,
ftd.week_id)
HAVING
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);
write_log('Total rows inserted in the staging area for day, month and year : ' || to_char(SQL%ROWCOUNT));
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 object_name = 'BIX_AGENT_SESSION_F';
UPDATE BIX_WORKER_JOBS
SET status = 'IN PROCESS',
worker_number = p_worker_no
WHERE status = 'UNASSIGNED'
AND rownum < 2
AND object_name = 'BIX_AGENT_SESSION_F';
SELECT start_date_range, end_date_range
INTO l_start_date_range, l_end_date_range
FROM BIX_WORKER_JOBS
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS'
AND object_name = 'BIX_AGENT_SESSION_F';
/* Update the status of job to 'COMPLETED' */
UPDATE BIX_WORKER_JOBS
SET status = 'COMPLETED'
WHERE status = 'IN PROCESS'
AND worker_number = p_worker_no
AND object_name = 'BIX_AGENT_SESSION_F';
UPDATE BIX_WORKER_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS'
AND object_name = 'BIX_AGENT_SESSION_F';
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_AGENT_SESSION_F';
write_log('Total Rows Inserted/Updated : ' || to_char(g_rows_ins_upd));