48: delete from irc_vac_summary;
49: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');
50: delete from irc_vac_managers;
51: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
52: delete from irc_vac_recruiters;
53: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
54: --Insert vacancy_id, manager_id, recruiter_id, organization_id, budget_measurement_value
55: --into irc_vac_summary table for targetted vacancies
56: l_start_date := sysdate;
49: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');
50: delete from irc_vac_managers;
51: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
52: delete from irc_vac_recruiters;
53: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
54: --Insert vacancy_id, manager_id, recruiter_id, organization_id, budget_measurement_value
55: --into irc_vac_summary table for targetted vacancies
56: l_start_date := sysdate;
57: --
107: select vacancy_id from per_all_vacancies where status not in ('CLOSED','CLO')
108: and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
109: );
110: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');
111: --delete all the data from irc_vac_summary_details,irc_vac_recruiters,irc_vac_managers
112: --for vacancies which are not present in irc_vac_summary table
113: delete from irc_vac_summary_details where vacancy_id not in (select vacancy_id from irc_vac_summary);
114: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
115: delete from irc_vac_managers where vacancy_id not in (select vacancy_id from irc_vac_summary);
113: delete from irc_vac_summary_details where vacancy_id not in (select vacancy_id from irc_vac_summary);
114: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
115: delete from irc_vac_managers where vacancy_id not in (select vacancy_id from irc_vac_summary);
116: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
117: delete from irc_vac_recruiters where vacancy_id not in (select vacancy_id from irc_vac_summary);
118: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
119: --
120: log('Leaving procedure INIT_INCREMENTAL_REFRESH');
121: log_break;
114: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
115: delete from irc_vac_managers where vacancy_id not in (select vacancy_id from irc_vac_summary);
116: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
117: delete from irc_vac_recruiters where vacancy_id not in (select vacancy_id from irc_vac_summary);
118: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
119: --
120: log('Leaving procedure INIT_INCREMENTAL_REFRESH');
121: log_break;
122: exception
362: --
363: close mgr_cursor;
364: --
365: log('Populated IRC_VAC_MANAGERS table');
366: --populate irc_vac_recruiters
367: open rec_cursor;
368: loop
369: fetch rec_cursor into l_person_id;
370: exit when rec_cursor%notfound;
367: open rec_cursor;
368: loop
369: fetch rec_cursor into l_person_id;
370: exit when rec_cursor%notfound;
371: insert into irc_vac_recruiters (vacancy_id,recruiter_id)
372: select distinct vac.vacancy_id,rec.recruiter_id
373: from
374: (select vacancy_id from irc_vac_summary where recruiter_id=l_person_id and
375: rowid between p_start_rowid and p_end_rowid) vac,
403: end loop;
404: --
405: close rec_cursor;
406: --
407: log('Populated IRC_VAC_RECRUITERS table');
408: --
409: log('Leaving procedure REFRESH_MGR_REC successfully');
410: --
411: exception
699: delete from irc_vac_managers where vacancy_id in
700: (select vacancy_id from irc_vac_summary where rowid between p_start_rowid and p_end_rowid);
701: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
702: --
703: delete from irc_vac_recruiters where vacancy_id in
704: (select vacancy_id from irc_vac_summary where rowid between p_start_rowid and p_end_rowid);
705: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
706: --
707: refresh_mgr_rec(p_start_rowid , p_end_rowid );
701: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');
702: --
703: delete from irc_vac_recruiters where vacancy_id in
704: (select vacancy_id from irc_vac_summary where rowid between p_start_rowid and p_end_rowid);
705: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_RECRUITERS successfully');
706: --
707: refresh_mgr_rec(p_start_rowid , p_end_rowid );
708: --
709: log('Leaving procedure POPULATE_SUMMARY_TABLES_INCR successfully');
950: WHERE vac.rowid between p_start_rowid and p_end_rowid;
951: --
952: log(SQL%ROWCOUNT||' rows updated in table IRC_VAC_SUMMARY successfully');
953: --
954: --Populate irc_vac_managers and irc_vac_recruiters table
955: --
956: refresh_mgr_rec(p_start_rowid,p_end_rowid);
957: --
958: commit;