[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from irc_vac_summary_details;
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
delete from irc_vac_summary;
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');
delete from irc_vac_managers;
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
delete from irc_vac_recruiters;
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
insert into irc_vac_summary
(
vacancy_id,manager_id, recruiter_id, organization_id, budget_measurement_value,
processed_status, last_update_date
)
select vac.vacancy_id, vac.manager_id, vac.recruiter_id, vac.organization_id, vac.budget_measurement_value,
'NEW',l_start_date
from per_all_vacancies vac
where vac.vacancy_id in (
select vac1.vacancy_id from per_all_vacancies vac1 where vac1.status NOT IN('CLOSED','CLO')
and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
);
log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');
insert into irc_vac_summary
(
vacancy_id,processed_status
)
select vac.vacancy_id, 'NEW'
from per_all_vacancies vac
where
vac.vacancy_id not in(
select vacancy_id from irc_vac_summary
)
and vac.vacancy_id in(
select vacancy_id from per_all_vacancies where status not in ('CLOSED','CLO')
and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
);
log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');
delete from irc_vac_summary ivs
where ivs.vacancy_id not in(
select vacancy_id from per_all_vacancies where status not in ('CLOSED','CLO')
and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
);
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');
delete from irc_vac_summary_details where vacancy_id not in (select vacancy_id from irc_vac_summary);
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
delete from irc_vac_managers where vacancy_id not in (select vacancy_id from irc_vac_summary);
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
delete from irc_vac_recruiters where vacancy_id not in (select vacancy_id from irc_vac_summary);
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
select rowid from irc_vac_summary order by rowid;
select distinct manager_id from irc_vac_summary where manager_id is not null
and rowid between p_start_rowid and p_end_rowid;
select distinct recruiter_id from irc_vac_summary where recruiter_id is not null
and rowid between p_start_rowid and p_end_rowid;
insert into irc_vac_managers(vacancy_id,manager_id)
select distinct vac.vacancy_id,mgr.manager_id
from
(select vacancy_id from irc_vac_summary where manager_id=l_person_id and
rowid between p_start_rowid and p_end_rowid) vac,
(select distinct paf.person_id as manager_id
from
per_all_assignments_f paf
start with paf.person_id = l_person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type in ( 'E' , 'C' )
and paf.assignment_status_type_id not in
(
select
assignment_status_type_id
from
per_assignment_status_types
where per_system_status = 'TERM_ASSIGN'
)
connect by prior paf.supervisor_id = paf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type in ( 'E' , 'C' )
and paf.assignment_status_type_id not in
(
select
assignment_status_type_id
from
per_assignment_status_types
where per_system_status = 'TERM_ASSIGN'
))mgr ;
insert into irc_vac_recruiters (vacancy_id,recruiter_id)
select distinct vac.vacancy_id,rec.recruiter_id
from
(select vacancy_id from irc_vac_summary where recruiter_id=l_person_id and
rowid between p_start_rowid and p_end_rowid) vac,
(select distinct paf.person_id as recruiter_id
from
per_all_assignments_f paf
start with paf.person_id = l_person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type in ( 'E' , 'C' )
and paf.assignment_status_type_id not in
(
select
assignment_status_type_id
from
per_assignment_status_types
where per_system_status = 'TERM_ASSIGN'
)
connect by prior paf.supervisor_id = paf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type in ( 'E' , 'C' )
and paf.assignment_status_type_id not in
(
select
assignment_status_type_id
from
per_assignment_status_types
where per_system_status = 'TERM_ASSIGN'
))rec ;
select
distinct ivs.rowid,ivs.vacancy_id,ivs.processed_status
from
irc_vac_summary ivs,
per_all_vacancies pav,
per_all_assignments_f paf,
irc_offers iof
where
pav.vacancy_id = ivs.vacancy_id
and paf.vacancy_id(+) = pav.vacancy_id
and iof.vacancy_id(+) = pav.vacancy_id
and (
pav.last_update_date >= nvl(ivs.last_update_date,pav.last_update_date)
or
paf.last_update_date >= nvl(ivs.last_update_date,paf.last_update_date)
or
iof.last_update_date >= nvl(ivs.last_update_date,iof.last_update_date)
)
and ivs.rowid between p_start_rowid and p_end_rowid
--pick up failed transactions
union
select
ivs.rowid,ivs.vacancy_id,ivs.processed_status
from irc_vac_summary ivs
where processed_status IN ('FAILED','NEW')
and ivs.rowid between p_start_rowid and p_end_rowid;
l_last_update_date date;
delete from irc_vac_summary_details where vacancy_id = l_vacancy_id;
update irc_vac_summary ivs set
(
manager_id, recruiter_id, organization_id, budget_measurement_value,last_update_date
)=
(select vac.manager_id,
vac.recruiter_id,
vac.organization_id,
vac.budget_measurement_value,
l_start_date
from per_all_vacancies vac
where ivs.vacancy_id = vac.vacancy_id
)
where ivs.vacancy_id = l_vacancy_id;
insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)
--
SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'TOTAL_APPLICATIONS'
FROM per_all_assignments_f pasg
WHERE pasg.assignment_type = 'A'
AND pasg.vacancy_id = l_vacancy_id
--
union
--
SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'ACTIVE_APPLICATIONS'
FROM per_all_assignments_f pasg
WHERE pasg.assignment_type = 'A'
AND pasg.vacancy_id = l_vacancy_id
AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
--
union
--
SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'FILLED_APPLICATIONS'
FROM per_all_assignments_f pasg
WHERE pasg.assignment_type IN ('E','C')
AND pasg.vacancy_id = l_vacancy_id
--
union
--
SELECT DISTINCT ivla.vacancy_id,ivla.src_apl_asg_id,'FILLED_APPLICATIONS'
FROM per_all_assignments_f pasg,
per_vac_linked_assignments ivla
WHERE ivla.vacancy_id = l_vacancy_id
AND sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate)
AND ivla.tgt_apl_asg_id = pasg.assignment_id
AND pasg.assignment_type IN ('E','C')
--
union
--
SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'TO_BE_PROCESSED_APPLICATIONS'
FROM per_all_assignments_f pasg ,
irc_assignment_statuses ias,
per_assignment_status_types past
WHERE pasg.vacancy_id = l_vacancy_id
AND pasg.assignment_type = 'A'
AND pasg.assignment_id = ias.assignment_id
AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
AND not exists (select 1
from irc_assignment_statuses ias2
where ias2.assignment_id=pasg.assignment_id
and ias2.status_change_date>ias.status_change_date)
AND past.per_system_status = 'ACTIVE_APL'
AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
--
union
--
SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'IN_PROGRESS_APPLICATIONS'
FROM per_all_assignments_f pasg ,
irc_assignment_statuses ias,
per_assignment_status_types past
WHERE pasg.vacancy_id = l_vacancy_id
AND pasg.assignment_type = 'A'
AND pasg.assignment_id = ias.assignment_id
AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
AND not exists (select 1
from irc_assignment_statuses ias2
where ias2.assignment_id=pasg.assignment_id
and ias2.status_change_date>ias.status_change_date)
AND past.per_system_status IN ('INTERVIEW1', 'INTERVIEW2', 'OFFER','ACCEPTED')
AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
--
union
--
SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'REJECTED_APPLICATIONS'
FROM per_all_assignments_f pasg,
per_assignment_status_types past
WHERE pasg.vacancy_id = l_vacancy_id
AND pasg.assignment_status_type_id = past.assignment_status_type_id
AND pasg.assignment_type = 'A'
AND pasg.effective_end_date = (select max(paf2.effective_end_date) from per_all_assignments_f paf2 where paf2.assignment_id = pasg.assignment_id)
AND pasg.effective_end_date <= trunc(sysdate)
AND past.per_system_status <> 'ACCEPTED'
--
union
--
SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'PENDING_FOR_APPROVAL_OFFERS'
FROM irc_offers offer
WHERE offer.vacancy_id = l_vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'PENDING'
--
union
--
SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'APPROVED_OFFERS'
FROM irc_offers offer
WHERE offer.vacancy_id = l_vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'APPROVED'
--
union
--
SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'EXTENDED_OFFERS'
FROM irc_offers offer
WHERE offer.vacancy_id = l_vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'EXTENDED'
--
union
--
SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'ACCEPTED_OFFERS'
FROM irc_offers offer ,
irc_offer_status_history hist
WHERE offer.vacancy_id = l_vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'CLOSED'
AND hist.offer_id = offer.offer_id
AND hist.change_reason = 'APL_ACCEPTED'
AND hist.status_change_date =
(
select max(status_change_date)
from irc_offer_status_history
where offer_id = offer.offer_id
)
--
union
--
SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'REJECTED_OFFERS'
FROM irc_offers offer ,
irc_offer_status_history hist
WHERE offer.vacancy_id = l_vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'CLOSED'
AND hist.offer_id = offer.offer_id
AND hist.change_reason = 'APL_DECLINED'
AND hist.status_change_date =
(
select max(status_change_date)
from irc_offer_status_history
where offer_id = offer.offer_id
)
--
union
--
SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'ON_HOLD_OFFERS'
FROM irc_offers offer
WHERE offer.vacancy_id = l_vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'HOLD'
--
union
--
SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'WITHDRAWN_OFFERS'
FROM irc_offers offer ,
irc_offer_status_history hist
WHERE offer.vacancy_id = l_vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'CLOSED'
AND hist.offer_id = offer.offer_id
AND hist.change_reason = 'MGR_WITHDRAW'
AND hist.status_change_date =
(
select max(status_change_date)
from irc_offer_status_history
where offer_id = offer.offer_id
);
update irc_vac_summary vac
set
( total_applications,active_applications,filled_applications,
to_be_processed_applications,in_progress_applications, rejected_applications,
pending_for_approval_offers,approved_offers,extended_offers,accepted_offers,
rejected_offers,on_hold_offers,withdrawn_offers,processed_status
) = (
select
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
'SUCCESS'
from irc_vac_summary vac1
where vac1.vacancy_id = vac.vacancy_id
)
where vac.vacancy_id = l_vacancy_id;
update irc_vac_summary set processed_status = 'FAILED' where vacancy_id = l_vacancy_id;
delete from irc_vac_managers where vacancy_id in
(select vacancy_id from irc_vac_summary where rowid between p_start_rowid and p_end_rowid);
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
delete from irc_vac_recruiters where vacancy_id in
(select vacancy_id from irc_vac_summary where rowid between p_start_rowid and p_end_rowid);
log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)
--
SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'TOTAL_APPLICATIONS'
FROM per_all_assignments_f pasg,
irc_vac_summary ivs
WHERE pasg.assignment_type = 'A'
AND pasg.vacancy_id = ivs.vacancy_id
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'ACTIVE_APPLICATIONS'
FROM per_all_assignments_f pasg,
irc_vac_summary ivs
WHERE pasg.assignment_type = 'A'
AND pasg.vacancy_id = ivs.vacancy_id
AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'FILLED_APPLICATIONS'
FROM per_all_assignments_f pasg,
irc_vac_summary ivs
WHERE pasg.assignment_type IN ('E','C')
AND pasg.vacancy_id = ivs.vacancy_id
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivla.vacancy_id,ivla.src_apl_asg_id,'FILLED_APPLICATIONS'
FROM per_all_assignments_f pasg,
per_vac_linked_assignments ivla,
irc_vac_summary ivs
WHERE ivla.vacancy_id = ivs.vacancy_id
AND sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate)
AND ivla.tgt_apl_asg_id = pasg.assignment_id
AND pasg.assignment_type IN ('E','C')
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'TO_BE_PROCESSED_APPLICATIONS'
FROM per_all_assignments_f pasg ,
irc_assignment_statuses ias,
per_assignment_status_types past,
irc_vac_summary ivs
WHERE pasg.vacancy_id = ivs.vacancy_id
AND pasg.assignment_type = 'A'
AND pasg.assignment_id = ias.assignment_id
AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
AND not exists (select 1
from irc_assignment_statuses ias2
where ias2.assignment_id=pasg.assignment_id
and ias2.status_change_date>ias.status_change_date)
AND past.per_system_status = 'ACTIVE_APL'
AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'IN_PROGRESS_APPLICATIONS'
FROM per_all_assignments_f pasg ,
irc_assignment_statuses ias,
per_assignment_status_types past,
irc_vac_summary ivs
WHERE pasg.vacancy_id = ivs.vacancy_id
AND pasg.assignment_type = 'A'
AND pasg.assignment_id = ias.assignment_id
AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
AND not exists (select 1
from irc_assignment_statuses ias2
where ias2.assignment_id=pasg.assignment_id
and ias2.status_change_date>ias.status_change_date)
AND past.per_system_status IN ('INTERVIEW1', 'INTERVIEW2', 'OFFER','ACCEPTED')
AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'REJECTED_APPLICATIONS'
FROM per_all_assignments_f pasg,
per_assignment_status_types past,
irc_vac_summary ivs
WHERE pasg.vacancy_id = ivs.vacancy_id
AND pasg.assignment_status_type_id = past.assignment_status_type_id
AND pasg.assignment_type = 'A'
AND pasg.effective_end_date = (select max(paf2.effective_end_date) from per_all_assignments_f paf2 where paf2.assignment_id = pasg.assignment_id)
AND pasg.effective_end_date <= trunc(sysdate)
AND past.per_system_status <> 'ACCEPTED'
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'PENDING_FOR_APPROVAL_OFFERS'
FROM irc_offers offer,
irc_vac_summary ivs
WHERE offer.vacancy_id = ivs.vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'PENDING'
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'APPROVED_OFFERS'
FROM irc_offers offer,
irc_vac_summary ivs
WHERE offer.vacancy_id = ivs.vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'APPROVED'
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'EXTENDED_OFFERS'
FROM irc_offers offer,
irc_vac_summary ivs
WHERE offer.vacancy_id = ivs.vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'EXTENDED'
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'ACCEPTED_OFFERS'
FROM irc_offers offer ,
irc_offer_status_history hist,
irc_vac_summary ivs
WHERE offer.vacancy_id = ivs.vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'CLOSED'
AND hist.offer_id = offer.offer_id
AND hist.change_reason = 'APL_ACCEPTED'
AND hist.status_change_date =
(
select max(status_change_date)
from irc_offer_status_history
where offer_id = offer.offer_id
)
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'REJECTED_OFFERS'
FROM irc_offers offer ,
irc_offer_status_history hist,
irc_vac_summary ivs
WHERE offer.vacancy_id = ivs.vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'CLOSED'
AND hist.offer_id = offer.offer_id
AND hist.change_reason = 'APL_DECLINED'
AND hist.status_change_date =
(
select max(status_change_date)
from irc_offer_status_history
where offer_id = offer.offer_id
)
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'ON_HOLD_OFFERS'
FROM irc_offers offer,
irc_vac_summary ivs
WHERE offer.vacancy_id = ivs.vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'HOLD'
AND ivs.rowid between p_start_rowid and p_end_rowid
--
union
--
SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'WITHDRAWN_OFFERS'
FROM irc_offers offer ,
irc_offer_status_history hist,
irc_vac_summary ivs
WHERE offer.vacancy_id = ivs.vacancy_id
AND offer.latest_offer = 'Y'
AND offer.offer_status = 'CLOSED'
AND hist.offer_id = offer.offer_id
AND hist.change_reason = 'MGR_WITHDRAW'
AND hist.status_change_date =
(
select max(status_change_date)
from irc_offer_status_history
where offer_id = offer.offer_id
)
AND ivs.rowid between p_start_rowid and p_end_rowid;
log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY_DETAILS successfully');
update irc_vac_summary vac
set
( total_applications,active_applications,filled_applications,to_be_processed_applications,in_progress_applications, rejected_applications,
pending_for_approval_offers,approved_offers,extended_offers,accepted_offers,rejected_offers,on_hold_offers,withdrawn_offers,
processed_status
) = (
select
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
(select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
'SUCCESS'
from irc_vac_summary vac1
where
vac1.vacancy_id=vac.vacancy_id
)
WHERE vac.rowid between p_start_rowid and p_end_rowid;
log(SQL%ROWCOUNT||' rows updated in table IRC_VAC_SUMMARY successfully');
update irc_vac_summary set processed_status = 'FAILED'
where rowid between p_start_rowid and p_end_rowid;