eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IRC_RECRUITMENT_SUMMARY_PKG dependencies on IRC_VAC_SUMMARY

Line 46: delete from irc_vac_summary_details;

42: log('Entering procedure INIT_FULL_REFRESH');
43: --
44: SAVEPOINT INIT_FULL_REFRESH_START;
45: --
46: delete from irc_vac_summary_details;
47: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
48: delete from irc_vac_summary;
49: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');
50: delete from irc_vac_managers;

Line 47: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');

43: --
44: SAVEPOINT INIT_FULL_REFRESH_START;
45: --
46: delete from irc_vac_summary_details;
47: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
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');

Line 48: delete from irc_vac_summary;

44: SAVEPOINT INIT_FULL_REFRESH_START;
45: --
46: delete from irc_vac_summary_details;
47: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
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;

Line 49: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');

45: --
46: delete from irc_vac_summary_details;
47: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');
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');

Line 55: --into irc_vac_summary table for targetted vacancies

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: --
58: insert into irc_vac_summary
59: (

Line 58: insert into irc_vac_summary

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: --
58: insert into irc_vac_summary
59: (
60: vacancy_id,manager_id, recruiter_id, organization_id, budget_measurement_value,
61: processed_status, last_update_date
62:

Line 71: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');

67: where vac.vacancy_id in (
68: select vac1.vacancy_id from per_all_vacancies vac1 where vac1.status NOT IN('CLOSED','CLO')
69: and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
70: );
71: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');
72: log('Leaving procedure INIT_FULL_REFRESH');
73: log_break;
74: exception
75: when others then

Line 88: --insert any newly identified vacancies into irc_vac_summary table

84: log('Entering procedure INIT_INCREMENTAL_REFRESH');
85: --
86: SAVEPOINT INIT_INCREMENTAL_REFRESH_START;
87: --
88: --insert any newly identified vacancies into irc_vac_summary table
89: insert into irc_vac_summary
90: (
91: vacancy_id,processed_status
92: )

Line 89: insert into irc_vac_summary

85: --
86: SAVEPOINT INIT_INCREMENTAL_REFRESH_START;
87: --
88: --insert any newly identified vacancies into irc_vac_summary table
89: insert into irc_vac_summary
90: (
91: vacancy_id,processed_status
92: )
93: select vac.vacancy_id, 'NEW'

Line 97: select vacancy_id from irc_vac_summary

93: select vac.vacancy_id, 'NEW'
94: from per_all_vacancies vac
95: where
96: vac.vacancy_id not in(
97: select vacancy_id from irc_vac_summary
98: )
99: and vac.vacancy_id in(
100: select vacancy_id from per_all_vacancies where status not in ('CLOSED','CLO')
101: and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))

Line 103: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');

99: and vac.vacancy_id in(
100: select vacancy_id from per_all_vacancies where status not in ('CLOSED','CLO')
101: and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
102: );
103: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');
104: --delete all the vacancies from irc_vac_summary which do not fulfill the required criteria;
105: delete from irc_vac_summary ivs
106: where ivs.vacancy_id not in(
107: select vacancy_id from per_all_vacancies where status not in ('CLOSED','CLO')

Line 104: --delete all the vacancies from irc_vac_summary which do not fulfill the required criteria;

100: select vacancy_id from per_all_vacancies where status not in ('CLOSED','CLO')
101: and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
102: );
103: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');
104: --delete all the vacancies from irc_vac_summary which do not fulfill the required criteria;
105: delete from irc_vac_summary ivs
106: where ivs.vacancy_id not in(
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))

Line 105: delete from irc_vac_summary ivs

101: and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate))
102: );
103: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY successfully');
104: --delete all the vacancies from irc_vac_summary which do not fulfill the required criteria;
105: delete from irc_vac_summary ivs
106: where ivs.vacancy_id not in(
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: );

Line 110: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY successfully');

106: where ivs.vacancy_id not in(
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');

Line 111: --delete all the data from irc_vac_summary_details,irc_vac_recruiters,irc_vac_managers

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);

Line 112: --for vacancies which are not present in irc_vac_summary table

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);
116: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_MANAGERS successfully');

Line 113: delete from irc_vac_summary_details where vacancy_id not in (select vacancy_id from irc_vac_summary);

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);
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);

Line 114: log(SQL%ROWCOUNT||' rows deleted from table IRC_VAC_SUMMARY_DETAILS successfully');

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);
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');

Line 115: delete from irc_vac_managers where vacancy_id not in (select vacancy_id from irc_vac_summary);

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);
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: --

Line 117: delete from irc_vac_recruiters 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;

Line 165: select rowid from irc_vac_summary order by rowid;

161: start_rowid rowids_type;
162: end_rowid rowids_type;
163: --
164: cursor rowid_cursor is
165: select rowid from irc_vac_summary order by rowid;
166: --
167: rowids rowids_type;
168: l_batch_size pls_integer;
169: --

Line 197: --fetch the rowids from irc_vac_summary

193: init_incremental_refresh;
194: end if;
195: --commit the data so that it can be picked up by threads
196: commit;
197: --fetch the rowids from irc_vac_summary
198: open rowid_cursor;
199: fetch rowid_cursor bulk collect into rowids;
200: close rowid_cursor;
201: --

Line 203: log('Total number of rows in table IRC_VAC_SUMMARY: '||l_rowid_count);

199: fetch rowid_cursor bulk collect into rowids;
200: close rowid_cursor;
201: --
202: l_rowid_count :=rowids.COUNT;
203: log('Total number of rows in table IRC_VAC_SUMMARY: '||l_rowid_count);
204: --
205: if l_rowid_count <> 0 then
206: l_batch_size := round(l_rowid_count/l_num_workers);
207: --

Line 311: select distinct manager_id from irc_vac_summary where manager_id is not null

307: --
308: procedure refresh_mgr_rec(p_start_rowid rowid, p_end_rowid rowid) as
309: --+
310: cursor mgr_cursor is
311: select distinct manager_id from irc_vac_summary where manager_id is not null
312: and rowid between p_start_rowid and p_end_rowid;
313: --+
314: cursor rec_cursor is
315: select distinct recruiter_id from irc_vac_summary where recruiter_id is not null

Line 315: select distinct recruiter_id from irc_vac_summary where recruiter_id is not null

311: select distinct manager_id from irc_vac_summary where manager_id is not null
312: and rowid between p_start_rowid and p_end_rowid;
313: --+
314: cursor rec_cursor is
315: select distinct recruiter_id from irc_vac_summary where recruiter_id is not null
316: and rowid between p_start_rowid and p_end_rowid;
317: --
318: l_person_id number;
319: --

Line 332: (select vacancy_id from irc_vac_summary where manager_id=l_person_id and

328: exit when mgr_cursor%notfound;
329: insert into irc_vac_managers(vacancy_id,manager_id)
330: select distinct vac.vacancy_id,mgr.manager_id
331: from
332: (select vacancy_id from irc_vac_summary where manager_id=l_person_id and
333: rowid between p_start_rowid and p_end_rowid) vac,
334: (select distinct paf.person_id as manager_id
335: from
336: per_all_assignments_f paf

Line 374: (select vacancy_id from irc_vac_summary where recruiter_id=l_person_id and

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,
376: (select distinct paf.person_id as recruiter_id
377: from
378: per_all_assignments_f paf

Line 426: irc_vac_summary ivs,

422: cursor get_vacancy_ids is
423: select
424: distinct ivs.rowid,ivs.vacancy_id,ivs.processed_status
425: from
426: irc_vac_summary ivs,
427: per_all_vacancies pav,
428: per_all_assignments_f paf,
429: irc_offers iof
430: where

Line 446: from irc_vac_summary ivs

442: --pick up failed transactions
443: union
444: select
445: ivs.rowid,ivs.vacancy_id,ivs.processed_status
446: from irc_vac_summary ivs
447: where processed_status IN ('FAILED','NEW')
448: and ivs.rowid between p_start_rowid and p_end_rowid;
449: --
450: l_vacancy_id number;

Line 473: delete from irc_vac_summary_details where vacancy_id = l_vacancy_id;

469: exit when get_vacancy_ids%NOTFOUND;
470:
471: SAVEPOINT START_VACANCY_POPULATION;
472: --If summary data already exists for the vacancy then delete it
473: delete from irc_vac_summary_details where vacancy_id = l_vacancy_id;
474: --
475: update irc_vac_summary ivs set
476: (
477: manager_id, recruiter_id, organization_id, budget_measurement_value,last_update_date

Line 475: update irc_vac_summary ivs set

471: SAVEPOINT START_VACANCY_POPULATION;
472: --If summary data already exists for the vacancy then delete it
473: delete from irc_vac_summary_details where vacancy_id = l_vacancy_id;
474: --
475: update irc_vac_summary ivs set
476: (
477: manager_id, recruiter_id, organization_id, budget_measurement_value,last_update_date
478: )=
479: (select vac.manager_id,

Line 489: insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)

485: where ivs.vacancy_id = vac.vacancy_id
486: )
487: where ivs.vacancy_id = l_vacancy_id;
488: --
489: insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)
490: --
491: SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'TOTAL_APPLICATIONS'
492: FROM per_all_assignments_f pasg
493: WHERE pasg.assignment_type = 'A'

Line 650: --Populate summary count columns in irc_vac_summary table from data populated

646: from irc_offer_status_history
647: where offer_id = offer.offer_id
648: );
649: --
650: --Populate summary count columns in irc_vac_summary table from data populated
651: --irc_vac_summary_details tab
652: --
653: update irc_vac_summary vac
654: set

Line 651: --irc_vac_summary_details tab

647: where offer_id = offer.offer_id
648: );
649: --
650: --Populate summary count columns in irc_vac_summary table from data populated
651: --irc_vac_summary_details tab
652: --
653: update irc_vac_summary vac
654: set
655: ( total_applications,active_applications,filled_applications,

Line 653: update irc_vac_summary vac

649: --
650: --Populate summary count columns in irc_vac_summary table from data populated
651: --irc_vac_summary_details tab
652: --
653: update irc_vac_summary vac
654: set
655: ( total_applications,active_applications,filled_applications,
656: to_be_processed_applications,in_progress_applications, rejected_applications,
657: pending_for_approval_offers,approved_offers,extended_offers,accepted_offers,

Line 661: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),

657: pending_for_approval_offers,approved_offers,extended_offers,accepted_offers,
658: rejected_offers,on_hold_offers,withdrawn_offers,processed_status
659: ) = (
660: select
661: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
662: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),

Line 662: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),

658: rejected_offers,on_hold_offers,withdrawn_offers,processed_status
659: ) = (
660: select
661: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
662: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),

Line 663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),

659: ) = (
660: select
661: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
662: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),

Line 664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),

660: select
661: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
662: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),

Line 665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),

661: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
662: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),

Line 666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),

662: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),

Line 667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),

663: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),

Line 668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),

664: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),

Line 669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),

665: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
673: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),

Line 670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),

666: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
673: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
674: 'SUCCESS'

Line 671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),

667: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
673: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
674: 'SUCCESS'
675: from irc_vac_summary vac1

Line 672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),

668: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
673: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
674: 'SUCCESS'
675: from irc_vac_summary vac1
676: where vac1.vacancy_id = vac.vacancy_id

Line 673: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),

669: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
670: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
673: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
674: 'SUCCESS'
675: from irc_vac_summary vac1
676: where vac1.vacancy_id = vac.vacancy_id
677: )

Line 675: from irc_vac_summary vac1

671: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
672: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
673: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
674: 'SUCCESS'
675: from irc_vac_summary vac1
676: where vac1.vacancy_id = vac.vacancy_id
677: )
678: where vac.vacancy_id = l_vacancy_id;
679: --

Line 683: update irc_vac_summary set processed_status = 'FAILED' where vacancy_id = l_vacancy_id;

679: --
680: exception
681: when others then
682: ROLLBACK TO START_VACANCY_POPULATION;
683: update irc_vac_summary set processed_status = 'FAILED' where vacancy_id = l_vacancy_id;
684: p_retcode := 2;
685: log('Error occured while processing vacancy with vacancy_id: '||l_vacancy_id);
686: log('Record has been marked as failed');
687: log('Initial record status was: '||l_processed_status);

Line 700: (select vacancy_id from irc_vac_summary where rowid between p_start_rowid and p_end_rowid);

696: --This is required to handle any hierarchical changes for
697: --employees like terminations,manager-reassignments etc.
698: --
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);

Line 704: (select vacancy_id from irc_vac_summary where rowid between p_start_rowid and p_end_rowid);

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 );
708: --

Line 729: --For vacancies present in irc_vac_summary table, populate irc_vac_summary_details table

725: log('Entering procedure POPULATE_SUMMARY_TABLES_FULL');
726: --
727: SAVEPOINT START_POPULATION;
728: --
729: --For vacancies present in irc_vac_summary table, populate irc_vac_summary_details table
730: --
731: insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)
732: --
733: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'TOTAL_APPLICATIONS'

Line 731: insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)

727: SAVEPOINT START_POPULATION;
728: --
729: --For vacancies present in irc_vac_summary table, populate irc_vac_summary_details table
730: --
731: insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)
732: --
733: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'TOTAL_APPLICATIONS'
734: FROM per_all_assignments_f pasg,
735: irc_vac_summary ivs

Line 735: irc_vac_summary ivs

731: insert into irc_vac_summary_details (vacancy_id,assignment_id,summary_type)
732: --
733: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'TOTAL_APPLICATIONS'
734: FROM per_all_assignments_f pasg,
735: irc_vac_summary ivs
736: WHERE pasg.assignment_type = 'A'
737: AND pasg.vacancy_id = ivs.vacancy_id
738: AND ivs.rowid between p_start_rowid and p_end_rowid
739: --

Line 744: irc_vac_summary ivs

740: union
741: --
742: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'ACTIVE_APPLICATIONS'
743: FROM per_all_assignments_f pasg,
744: irc_vac_summary ivs
745: WHERE pasg.assignment_type = 'A'
746: AND pasg.vacancy_id = ivs.vacancy_id
747: AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
748: AND ivs.rowid between p_start_rowid and p_end_rowid

Line 754: irc_vac_summary ivs

750: union
751: --
752: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'FILLED_APPLICATIONS'
753: FROM per_all_assignments_f pasg,
754: irc_vac_summary ivs
755: WHERE pasg.assignment_type IN ('E','C')
756: AND pasg.vacancy_id = ivs.vacancy_id
757: AND ivs.rowid between p_start_rowid and p_end_rowid
758: --

Line 764: irc_vac_summary ivs

760: --
761: SELECT DISTINCT ivla.vacancy_id,ivla.src_apl_asg_id,'FILLED_APPLICATIONS'
762: FROM per_all_assignments_f pasg,
763: per_vac_linked_assignments ivla,
764: irc_vac_summary ivs
765: WHERE ivla.vacancy_id = ivs.vacancy_id
766: AND sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate)
767: AND ivla.tgt_apl_asg_id = pasg.assignment_id
768: AND pasg.assignment_type IN ('E','C')

Line 777: irc_vac_summary ivs

773: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'TO_BE_PROCESSED_APPLICATIONS'
774: FROM per_all_assignments_f pasg ,
775: irc_assignment_statuses ias,
776: per_assignment_status_types past,
777: irc_vac_summary ivs
778: WHERE pasg.vacancy_id = ivs.vacancy_id
779: AND pasg.assignment_type = 'A'
780: AND pasg.assignment_id = ias.assignment_id
781: AND ias.assignment_status_type_id = past.assignment_status_type_id(+)

Line 796: irc_vac_summary ivs

792: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'IN_PROGRESS_APPLICATIONS'
793: FROM per_all_assignments_f pasg ,
794: irc_assignment_statuses ias,
795: per_assignment_status_types past,
796: irc_vac_summary ivs
797: WHERE pasg.vacancy_id = ivs.vacancy_id
798: AND pasg.assignment_type = 'A'
799: AND pasg.assignment_id = ias.assignment_id
800: AND ias.assignment_status_type_id = past.assignment_status_type_id(+)

Line 814: irc_vac_summary ivs

810: --
811: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'REJECTED_APPLICATIONS'
812: FROM per_all_assignments_f pasg,
813: per_assignment_status_types past,
814: irc_vac_summary ivs
815: WHERE pasg.vacancy_id = ivs.vacancy_id
816: AND pasg.assignment_status_type_id = past.assignment_status_type_id
817: AND pasg.assignment_type = 'A'
818: AND pasg.effective_end_date = (select max(paf2.effective_end_date) from per_all_assignments_f paf2 where paf2.assignment_id = pasg.assignment_id)

Line 827: irc_vac_summary ivs

823: union
824: --
825: SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'PENDING_FOR_APPROVAL_OFFERS'
826: FROM irc_offers offer,
827: irc_vac_summary ivs
828: WHERE offer.vacancy_id = ivs.vacancy_id
829: AND offer.latest_offer = 'Y'
830: AND offer.offer_status = 'PENDING'
831: AND ivs.rowid between p_start_rowid and p_end_rowid

Line 837: irc_vac_summary ivs

833: union
834: --
835: SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'APPROVED_OFFERS'
836: FROM irc_offers offer,
837: irc_vac_summary ivs
838: WHERE offer.vacancy_id = ivs.vacancy_id
839: AND offer.latest_offer = 'Y'
840: AND offer.offer_status = 'APPROVED'
841: AND ivs.rowid between p_start_rowid and p_end_rowid

Line 847: irc_vac_summary ivs

843: union
844: --
845: SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'EXTENDED_OFFERS'
846: FROM irc_offers offer,
847: irc_vac_summary ivs
848: WHERE offer.vacancy_id = ivs.vacancy_id
849: AND offer.latest_offer = 'Y'
850: AND offer.offer_status = 'EXTENDED'
851: AND ivs.rowid between p_start_rowid and p_end_rowid

Line 858: irc_vac_summary ivs

854: --
855: SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'ACCEPTED_OFFERS'
856: FROM irc_offers offer ,
857: irc_offer_status_history hist,
858: irc_vac_summary ivs
859: WHERE offer.vacancy_id = ivs.vacancy_id
860: AND offer.latest_offer = 'Y'
861: AND offer.offer_status = 'CLOSED'
862: AND hist.offer_id = offer.offer_id

Line 877: irc_vac_summary ivs

873: --
874: SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'REJECTED_OFFERS'
875: FROM irc_offers offer ,
876: irc_offer_status_history hist,
877: irc_vac_summary ivs
878: WHERE offer.vacancy_id = ivs.vacancy_id
879: AND offer.latest_offer = 'Y'
880: AND offer.offer_status = 'CLOSED'
881: AND hist.offer_id = offer.offer_id

Line 895: irc_vac_summary ivs

891: union
892: --
893: SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'ON_HOLD_OFFERS'
894: FROM irc_offers offer,
895: irc_vac_summary ivs
896: WHERE offer.vacancy_id = ivs.vacancy_id
897: AND offer.latest_offer = 'Y'
898: AND offer.offer_status = 'HOLD'
899: AND ivs.rowid between p_start_rowid and p_end_rowid

Line 906: irc_vac_summary ivs

902: --
903: SELECT DISTINCT ivs.vacancy_id,offer.applicant_assignment_id,'WITHDRAWN_OFFERS'
904: FROM irc_offers offer ,
905: irc_offer_status_history hist,
906: irc_vac_summary ivs
907: WHERE offer.vacancy_id = ivs.vacancy_id
908: AND offer.latest_offer = 'Y'
909: AND offer.offer_status = 'CLOSED'
910: AND hist.offer_id = offer.offer_id

Line 920: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY_DETAILS successfully');

916: where offer_id = offer.offer_id
917: )
918: AND ivs.rowid between p_start_rowid and p_end_rowid;
919: --
920: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY_DETAILS successfully');
921: --
922: --Populate summary count columns in irc_vac_summary table from data populated
923: --irc_vac_summary_details tab
924: --

Line 922: --Populate summary count columns in irc_vac_summary table from data populated

918: AND ivs.rowid between p_start_rowid and p_end_rowid;
919: --
920: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY_DETAILS successfully');
921: --
922: --Populate summary count columns in irc_vac_summary table from data populated
923: --irc_vac_summary_details tab
924: --
925: update irc_vac_summary vac
926: set

Line 923: --irc_vac_summary_details tab

919: --
920: log(SQL%ROWCOUNT||' rows inserted into table IRC_VAC_SUMMARY_DETAILS successfully');
921: --
922: --Populate summary count columns in irc_vac_summary table from data populated
923: --irc_vac_summary_details tab
924: --
925: update irc_vac_summary vac
926: set
927: ( total_applications,active_applications,filled_applications,to_be_processed_applications,in_progress_applications, rejected_applications,

Line 925: update irc_vac_summary vac

921: --
922: --Populate summary count columns in irc_vac_summary table from data populated
923: --irc_vac_summary_details tab
924: --
925: update irc_vac_summary vac
926: set
927: ( total_applications,active_applications,filled_applications,to_be_processed_applications,in_progress_applications, rejected_applications,
928: pending_for_approval_offers,approved_offers,extended_offers,accepted_offers,rejected_offers,on_hold_offers,withdrawn_offers,
929: processed_status

Line 932: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),

928: pending_for_approval_offers,approved_offers,extended_offers,accepted_offers,rejected_offers,on_hold_offers,withdrawn_offers,
929: processed_status
930: ) = (
931: select
932: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
933: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),

Line 933: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),

929: processed_status
930: ) = (
931: select
932: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
933: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),

Line 934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),

930: ) = (
931: select
932: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
933: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),

Line 935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),

931: select
932: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
933: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),

Line 936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),

932: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TOTAL_APPLICATIONS'),
933: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),

Line 937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),

933: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACTIVE_APPLICATIONS'),
934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),

Line 938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),

934: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='FILLED_APPLICATIONS'),
935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),

Line 939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),

935: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='TO_BE_PROCESSED_APPLICATIONS'),
936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),

Line 940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),

936: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='IN_PROGRESS_APPLICATIONS'),
937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
944: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),

Line 941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),

937: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_APPLICATIONS'),
938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
944: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
945: 'SUCCESS'

Line 942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),

938: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='PENDING_FOR_APPROVAL_OFFERS'),
939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
944: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
945: 'SUCCESS'
946: from irc_vac_summary vac1

Line 943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),

939: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='APPROVED_OFFERS'),
940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
944: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
945: 'SUCCESS'
946: from irc_vac_summary vac1
947: where

Line 944: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),

940: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='EXTENDED_OFFERS'),
941: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ACCEPTED_OFFERS'),
942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
944: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
945: 'SUCCESS'
946: from irc_vac_summary vac1
947: where
948: vac1.vacancy_id=vac.vacancy_id

Line 946: from irc_vac_summary vac1

942: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='REJECTED_OFFERS'),
943: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='ON_HOLD_OFFERS'),
944: (select count(*) from irc_vac_summary_details where vacancy_id = vac1.vacancy_id and summary_type='WITHDRAWN_OFFERS'),
945: 'SUCCESS'
946: from irc_vac_summary vac1
947: where
948: vac1.vacancy_id=vac.vacancy_id
949: )
950: WHERE vac.rowid between p_start_rowid and p_end_rowid;

Line 952: log(SQL%ROWCOUNT||' rows updated in table IRC_VAC_SUMMARY successfully');

948: vac1.vacancy_id=vac.vacancy_id
949: )
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);

Line 1002: update irc_vac_summary set processed_status = 'FAILED'

998: --
999: log('Concurrent request ends successfully');
1000: exception
1001: when others then
1002: update irc_vac_summary set processed_status = 'FAILED'
1003: where rowid between p_start_rowid and p_end_rowid;
1004: commit;
1005: errbuf := SQLERRM ;
1006: retcode := 2;