[Home] [Help]
PACKAGE BODY: APPS.IRC_RECRUITMENT_SUMMARY_PKG
Source
1 package body irc_recruitment_summary_pkg as
2 /* $Header: irrecsum.pkb 120.5 2011/05/23 14:34:18 amikukum noship $ */
3 --
4 procedure log(msg varchar2) as
5 begin
6 fnd_file.put_line(fnd_file.log,to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||': '||msg);
7 end;
8 --
9 --
10 procedure log_break as
11 begin
12 fnd_file.put_line(fnd_file.log,'-----------------------------------------------------------------------------');
13 end;
14 --
15 --
16 procedure save_profile as
17 --
18 PRAGMA AUTONOMOUS_TRANSACTION;
19 l_start_date varchar2(100);
20 l_profile_check boolean;
21 --
22 begin
23 l_start_date := to_char(sysdate,'DD-MON-YYYY HH24:MI:SS');
24 --
25 l_profile_check := fnd_profile.save (
26 x_name => 'IRC_REC_SUM_PROGRAM_LAST_RUN',
27 x_value => l_start_date,
28 x_level_name => 'SITE');
29 --
30 if l_profile_check = true then
31 log('Profile IRC_REC_SUM_PROGRAM_LAST_RUN set successfully to '||l_start_date);
32 end if;
33 --
34 commit;
35 --
36 end;
37 --
38 --
39 procedure init_full_refresh as
40 l_start_date date;
41 begin
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;
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 (
60 vacancy_id,manager_id, recruiter_id, organization_id, budget_measurement_value,
61 processed_status, last_update_date
62
63 )
64 select vac.vacancy_id, vac.manager_id, vac.recruiter_id, vac.organization_id, vac.budget_measurement_value,
65 'NEW',l_start_date
66 from per_all_vacancies vac
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
76 ROLLBACK TO INIT_FULL_REFRESH_START;
77 raise;
78 end;
79 --
80 procedure init_incremental_refresh as
81 begin
82 --
83 log_break;
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 )
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))
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 );
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');
119 --
120 log('Leaving procedure INIT_INCREMENTAL_REFRESH');
121 log_break;
122 exception
123 when others then
124 ROLLBACK TO INIT_INCREMENTAL_REFRESH_START;
125 raise;
126 end;
127 --
128 procedure populate_summary_tbl_main_conc( errbuf out nocopy varchar2
129 ,retcode out nocopy number
130 ,p_number_of_workers in number
131 ,p_mode in varchar2
132 )
133 as
134 --
135 --concurrent request related variables
136 l_return boolean;
137 l_phase varchar2(300);
138 l_status varchar2(300);
139 l_dev_phase varchar2(300);
140 l_dev_status varchar2(300);
141 l_message varchar2(300);
142 l_request_id number;
143 --
144 --
145 FULL_REFRESH varchar2(12) :='FULL_REFRESH';
146 INCREMENTAL_REFRESH varchar2(19):='INCREMENTAL_REFRESH';
147 --
148 l_num_workers number := nvl(p_number_of_workers,1);
149 l_mode varchar2(50) := nvl(p_mode,INCREMENTAL_REFRESH);
150 l_count pls_integer := 1;
151 l_errored_count pls_integer := 1;
152 --
153 TYPE concurrent_request_ids_type IS
154 TABLE OF number index by pls_integer;
155 --
156 TYPE rowids_type IS
157 TABLE OF rowid index by pls_integer;
158 --
159 concurrent_request_ids concurrent_request_ids_type;
160 errored_concurrent_request_ids concurrent_request_ids_type;
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 --
170 l_start_rowid_index pls_integer;
171 l_end_rowid_index pls_integer;
172 l_rowid_count pls_integer;
173 l_any_rows_to_process boolean:=true;
174 --
175 l_start_rowid rowid;
176 l_end_rowid rowid;
177 --
178 begin
179 log('Starting concurrent request');
180 log('Number of workers : '||p_number_of_workers);
181 log('Concurrent request mode : '||p_mode);
182 log('Last time when concurrent program was run :'||fnd_profile.value('IRC_REC_SUM_PROGRAM_LAST_RUN'));
183 save_profile;
184 log_break;
185 --
186 retcode:=0;
187 --
188 if l_mode = FULL_REFRESH then
189 init_full_refresh;
190 end if;
191 --
192 if l_mode = INCREMENTAL_REFRESH then
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 --
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 --
208 log('Batch Size for each thread: '||l_batch_size);
209 log_break;
210 --
211 l_start_rowid_index :=1;
212 --
213 while (l_any_rows_to_process = true)
214 loop
215 --
216 l_end_rowid_index :=l_start_rowid_index+l_batch_size;
217 --
218 if l_end_rowid_index >=l_rowid_count then
219 l_end_rowid_index := l_rowid_count;
220 l_any_rows_to_process := false;
221 end if;
222 --
223 l_start_rowid := rowids(l_start_rowid_index);
224 l_end_rowid := rowids(l_end_rowid_index);
225 --
226 l_request_id := FND_REQUEST.submit_request (
227 application =>'PER',
228 program =>'IRC_REC_SUMMARY_CHILD',
229 argument1 =>l_start_rowid,
230 argument2 =>l_end_rowid,
231 argument3 =>l_mode);
232 --
233 log('Submitted concurrent request having request_id: '||l_request_id);
234 log('Starting rowid: '||l_start_rowid);
235 log('Ending rowid: '||l_end_rowid);
236 log('Starting index: '||l_start_rowid_index);
237 log('Ending index: '||l_end_rowid_index);
238 log_break;
239 --
240 concurrent_request_ids(l_count):= l_request_id;
241 start_rowid(l_count):= l_start_rowid;
242 end_rowid(l_count):= l_end_rowid;
243 l_count:= l_count+1;
244 --
245 l_start_rowid_index := l_end_rowid_index+1;
246 end loop;
247 --issue a commit so that the submitted concurrent request gets picked up by the concurrent manager
248 commit;
249 --
250 end if;
251 --
252 log('Waiting for child processes to finish');
253 --
254 for i in concurrent_request_ids.first..concurrent_request_ids.last
255 loop
256 log_break;
257 log('Waiting for child process with request_id: '||concurrent_request_ids(i));
258 l_return := fnd_concurrent.wait_for_request (request_id => concurrent_request_ids(i),
259 interval => 10,
260 phase => l_phase,
261 status => l_status,
262 dev_phase => l_dev_phase,
263 dev_status => l_dev_status,
264 message => l_message
265 );
266 --
267 log('Return parameters for child process: ');
268 log(' status :'||l_status);
269 log(' phase :'||l_phase);
270 log(' dev_phase :'||l_dev_phase);
271 log(' dev_status :'||l_dev_status);
272 log(' message :'||l_message);
273 --
274 if l_status = 'Error' then
275 --
276 retcode := 2;
277 errored_concurrent_request_ids(l_errored_count):= concurrent_request_ids(i);
278 l_errored_count:=l_errored_count+1;
279 --
280 end if;
281 end loop;
282 --
283 log_break;
284 log('All child processes completed');
285 log_break;
286 --
287 if retcode = 2 then
288 log('Concurrent request ends with error.');
289 log('Child concurrent requests with the below REQUEST ID''s ended with error:');
290 for i in errored_concurrent_request_ids.first..errored_concurrent_request_ids.last
291 loop
292 log(' '||errored_concurrent_request_ids(i));
293 log('For the error message check the log of concurrent request');
294 end loop;
295 else
296 log('Concurrent request ends successfully');
297 end if;
298 --
299 EXCEPTION
300 WHEN others THEN
301 errbuf :=SQLERRM ;
302 retcode := 2;
303 log('Concurrent request ends with error');
304 log('Error Message: '||SQLERRM);
305 log('Error Number : '||SQLCODE);
306 end;
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
316 and rowid between p_start_rowid and p_end_rowid;
317 --
318 l_person_id number;
319 --
320 begin
321 --
322 log('Entering procedure REFRESH_MGR_REC');
323 --
324 --populate irc_vac_managers
325 open mgr_cursor;
326 loop
327 fetch mgr_cursor into l_person_id;
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
337 start with paf.person_id = l_person_id
338 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
339 and paf.primary_flag = 'Y'
340 and paf.assignment_type in ( 'E' , 'C' )
341 and paf.assignment_status_type_id not in
342 (
343 select
344 assignment_status_type_id
345 from
346 per_assignment_status_types
347 where per_system_status = 'TERM_ASSIGN'
348 )
349 connect by prior paf.supervisor_id = paf.person_id
350 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
351 and paf.primary_flag = 'Y'
352 and paf.assignment_type in ( 'E' , 'C' )
353 and paf.assignment_status_type_id not in
354 (
355 select
356 assignment_status_type_id
357 from
358 per_assignment_status_types
359 where per_system_status = 'TERM_ASSIGN'
360 ))mgr ;
361 end loop;
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;
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
379 start with paf.person_id = l_person_id
380 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
381 and paf.primary_flag = 'Y'
382 and paf.assignment_type in ( 'E' , 'C' )
383 and paf.assignment_status_type_id not in
384 (
385 select
386 assignment_status_type_id
387 from
388 per_assignment_status_types
389 where per_system_status = 'TERM_ASSIGN'
390 )
391 connect by prior paf.supervisor_id = paf.person_id
392 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
393 and paf.primary_flag = 'Y'
394 and paf.assignment_type in ( 'E' , 'C' )
395 and paf.assignment_status_type_id not in
396 (
397 select
398 assignment_status_type_id
399 from
400 per_assignment_status_types
401 where per_system_status = 'TERM_ASSIGN'
402 ))rec ;
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
412 when others then
413 log('Leaving procedure REFRESH_MGR_REC with error');
414 log('Error Message: '||SQLERRM);
415 log('Error Number : '||SQLCODE);
416 raise;
417 end;
418 --
419 --
420 procedure populate_summary_tables_incr(p_start_rowid rowid, p_end_rowid rowid, p_retcode out nocopy number) as
421 --+
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
431 pav.vacancy_id = ivs.vacancy_id
432 and paf.vacancy_id(+) = pav.vacancy_id
433 and iof.vacancy_id(+) = pav.vacancy_id
434 and (
435 pav.last_update_date >= nvl(ivs.last_update_date,pav.last_update_date)
436 or
437 paf.last_update_date >= nvl(ivs.last_update_date,paf.last_update_date)
438 or
439 iof.last_update_date >= nvl(ivs.last_update_date,iof.last_update_date)
440 )
441 and ivs.rowid between p_start_rowid and p_end_rowid
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;
451 l_last_update_date date;
452 l_processed_status varchar2(30);
453 l_start_date date;
454 l_rowid rowid;
455 --
456 begin
457 --
458 log('Entering procedure POPULATE_SUMMARY_TABLES_INCR');
459 --
460 SAVEPOINT START_POPULATION;
461 --
462 l_start_date := sysdate;
463 p_retcode := 0;
464 --
465 open get_vacancy_ids;
466 loop
467 begin
468 fetch get_vacancy_ids into l_rowid,l_vacancy_id,l_processed_status;
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
478 )=
479 (select vac.manager_id,
480 vac.recruiter_id,
481 vac.organization_id,
482 vac.budget_measurement_value,
483 l_start_date
484 from per_all_vacancies vac
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'
494 AND pasg.vacancy_id = l_vacancy_id
495 --
496 union
497 --
498 SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'ACTIVE_APPLICATIONS'
499 FROM per_all_assignments_f pasg
500 WHERE pasg.assignment_type = 'A'
501 AND pasg.vacancy_id = l_vacancy_id
502 AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
503 --
504 union
505 --
506 SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'FILLED_APPLICATIONS'
507 FROM per_all_assignments_f pasg
508 WHERE pasg.assignment_type IN ('E','C')
509 AND pasg.vacancy_id = l_vacancy_id
510 --
511 union
512 --
513 SELECT DISTINCT ivla.vacancy_id,ivla.src_apl_asg_id,'FILLED_APPLICATIONS'
514 FROM per_all_assignments_f pasg,
515 per_vac_linked_assignments ivla
516 WHERE ivla.vacancy_id = l_vacancy_id
517 AND sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate)
518 AND ivla.tgt_apl_asg_id = pasg.assignment_id
519 AND pasg.assignment_type IN ('E','C')
520 --
521 union
522 --
523 SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'TO_BE_PROCESSED_APPLICATIONS'
524 FROM per_all_assignments_f pasg ,
525 irc_assignment_statuses ias,
526 per_assignment_status_types past
527 WHERE pasg.vacancy_id = l_vacancy_id
528 AND pasg.assignment_type = 'A'
529 AND pasg.assignment_id = ias.assignment_id
530 AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
531 AND not exists (select 1
532 from irc_assignment_statuses ias2
533 where ias2.assignment_id=pasg.assignment_id
534 and ias2.status_change_date>ias.status_change_date)
535 AND past.per_system_status = 'ACTIVE_APL'
536 AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
537 --
538 union
539 --
540 SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'IN_PROGRESS_APPLICATIONS'
541 FROM per_all_assignments_f pasg ,
542 irc_assignment_statuses ias,
543 per_assignment_status_types past
544 WHERE pasg.vacancy_id = l_vacancy_id
545 AND pasg.assignment_type = 'A'
546 AND pasg.assignment_id = ias.assignment_id
547 AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
548 AND not exists (select 1
549 from irc_assignment_statuses ias2
550 where ias2.assignment_id=pasg.assignment_id
551 and ias2.status_change_date>ias.status_change_date)
552 AND past.per_system_status IN ('INTERVIEW1', 'INTERVIEW2', 'OFFER','ACCEPTED')
553 AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
554 --
555 union
556 --
557 SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'REJECTED_APPLICATIONS'
558 FROM per_all_assignments_f pasg,
559 per_assignment_status_types past
560 WHERE pasg.vacancy_id = l_vacancy_id
561 AND pasg.assignment_status_type_id = past.assignment_status_type_id
562 AND pasg.assignment_type = 'A'
563 AND pasg.effective_end_date = (select max(paf2.effective_end_date) from per_all_assignments_f paf2 where paf2.assignment_id = pasg.assignment_id)
564 AND pasg.effective_end_date <= trunc(sysdate)
565 AND past.per_system_status <> 'ACCEPTED'
566 --
567 union
568 --
569 SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'PENDING_FOR_APPROVAL_OFFERS'
570 FROM irc_offers offer
571 WHERE offer.vacancy_id = l_vacancy_id
572 AND offer.latest_offer = 'Y'
573 AND offer.offer_status = 'PENDING'
574 --
575 union
576 --
577 SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'APPROVED_OFFERS'
578 FROM irc_offers offer
579 WHERE offer.vacancy_id = l_vacancy_id
580 AND offer.latest_offer = 'Y'
581 AND offer.offer_status = 'APPROVED'
582 --
583 union
584 --
585 SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'EXTENDED_OFFERS'
586 FROM irc_offers offer
587 WHERE offer.vacancy_id = l_vacancy_id
588 AND offer.latest_offer = 'Y'
589 AND offer.offer_status = 'EXTENDED'
590 --
591 union
592 --
593 SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'ACCEPTED_OFFERS'
594 FROM irc_offers offer ,
595 irc_offer_status_history hist
596 WHERE offer.vacancy_id = l_vacancy_id
597 AND offer.latest_offer = 'Y'
598 AND offer.offer_status = 'CLOSED'
599 AND hist.offer_id = offer.offer_id
600 AND hist.change_reason = 'APL_ACCEPTED'
601 AND hist.status_change_date =
602 (
603 select max(status_change_date)
604 from irc_offer_status_history
605 where offer_id = offer.offer_id
606 )
607 --
608 union
609 --
610 SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'REJECTED_OFFERS'
611 FROM irc_offers offer ,
612 irc_offer_status_history hist
613 WHERE offer.vacancy_id = l_vacancy_id
614 AND offer.latest_offer = 'Y'
615 AND offer.offer_status = 'CLOSED'
616 AND hist.offer_id = offer.offer_id
617 AND hist.change_reason = 'APL_DECLINED'
618 AND hist.status_change_date =
619 (
620 select max(status_change_date)
621 from irc_offer_status_history
622 where offer_id = offer.offer_id
623 )
624 --
625 union
626 --
627 SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'ON_HOLD_OFFERS'
628 FROM irc_offers offer
629 WHERE offer.vacancy_id = l_vacancy_id
630 AND offer.latest_offer = 'Y'
631 AND offer.offer_status = 'HOLD'
632 --
633 union
634 --
635 SELECT DISTINCT offer.vacancy_id,offer.applicant_assignment_id,'WITHDRAWN_OFFERS'
636 FROM irc_offers offer ,
637 irc_offer_status_history hist
638 WHERE offer.vacancy_id = l_vacancy_id
639 AND offer.latest_offer = 'Y'
640 AND offer.offer_status = 'CLOSED'
641 AND hist.offer_id = offer.offer_id
642 AND hist.change_reason = 'MGR_WITHDRAW'
643 AND hist.status_change_date =
644 (
645 select max(status_change_date)
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
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,
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'),
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
676 where vac1.vacancy_id = vac.vacancy_id
677 )
678 where vac.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);
688 log('Error Message: '||SQLERRM);
689 log('Error Number : '||SQLCODE);
690 end ;
691 end loop;
692 --
693 close get_vacancy_ids;
694 --
695 --Do a full_refresh of manager and recruiter hierarchies
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);
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');
710 --
711 exception
712 when others then
713 rollback to START_POPULATION;
714 p_retcode := 2;
715 log('Leaving procedure POPULATE_SUMMARY_TABLES_INCR with error');
716 log('Error Message: '||SQLERRM);
717 log('Error Number : '||SQLCODE);
718 raise;
719 end;
720 --
721 procedure populate_summary_tables_full(p_start_rowid rowid, p_end_rowid rowid) as
722 --
723 begin
724 --
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'
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 --
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
749 --
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 --
759 union
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')
769 AND ivs.rowid between p_start_rowid and p_end_rowid
770 --
771 union
772 --
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(+)
782 AND not exists (select 1
783 from irc_assignment_statuses ias2
784 where ias2.assignment_id=pasg.assignment_id
785 and ias2.status_change_date>ias.status_change_date)
786 AND past.per_system_status = 'ACTIVE_APL'
787 AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
788 AND ivs.rowid between p_start_rowid and p_end_rowid
789 --
790 union
791 --
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(+)
801 AND not exists (select 1
802 from irc_assignment_statuses ias2
803 where ias2.assignment_id=pasg.assignment_id
804 and ias2.status_change_date>ias.status_change_date)
805 AND past.per_system_status IN ('INTERVIEW1', 'INTERVIEW2', 'OFFER','ACCEPTED')
806 AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date
807 AND ivs.rowid between p_start_rowid and p_end_rowid
808 --
809 union
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)
819 AND pasg.effective_end_date <= trunc(sysdate)
820 AND past.per_system_status <> 'ACCEPTED'
821 AND ivs.rowid between p_start_rowid and p_end_rowid
822 --
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
832 --
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
842 --
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
852 --
853 union
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
863 AND hist.change_reason = 'APL_ACCEPTED'
864 AND hist.status_change_date =
865 (
866 select max(status_change_date)
867 from irc_offer_status_history
868 where offer_id = offer.offer_id
869 )
870 AND ivs.rowid between p_start_rowid and p_end_rowid
871 --
872 union
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
882 AND hist.change_reason = 'APL_DECLINED'
883 AND hist.status_change_date =
884 (
885 select max(status_change_date)
886 from irc_offer_status_history
887 where offer_id = offer.offer_id
888 )
889 AND ivs.rowid between p_start_rowid and p_end_rowid
890 --
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
900 --
901 union
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
911 AND hist.change_reason = 'MGR_WITHDRAW'
912 AND hist.status_change_date =
913 (
914 select max(status_change_date)
915 from irc_offer_status_history
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 --
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
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'),
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
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);
957 --
958 commit;
959 --
960 log('Leaving procedure POPULATE_SUMMARY_TABLES_FULL');
961 --
962 exception
963 when others then
964 log('Error occured in procedure POPULATE_SUMMARY_TABLES_FULL');
965 rollback to START_POPULATION ;
966 raise;
967 end;
968 --
969 procedure populate_summary_tbl_conc(
970 errbuf out nocopy varchar2
971 ,retcode out nocopy number
972 ,p_start_rowid in varchar2
973 ,p_end_rowid in varchar2
974 ,p_mode in varchar2
975 ) as
976 --
977 FULL_REFRESH varchar2(12):='FULL_REFRESH';
978 INCREMENTAL_REFRESH varchar2(19):='INCREMENTAL_REFRESH';
979 l_retcode number:=0;
980 --
981 begin
982 log('Starting concurrent request');
983 log('Concurrent request mode : '||p_mode);
984 log('Starting rowid: '||p_start_rowid||' Ending rowid: '||p_end_rowid);
985 --
986 retcode:=0;
987 --
988 if p_mode = FULL_REFRESH then
989 populate_summary_tables_full(p_start_rowid, p_end_rowid);
990 end if ;
991 --
992 if p_mode = INCREMENTAL_REFRESH then
993 populate_summary_tables_incr(p_start_rowid, p_end_rowid, l_retcode);
994 retcode := l_retcode;
995 end if;
996 --
997 commit;
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;
1007 log('Concurrent request ends with error');
1008 log('Error Message: '||SQLERRM);
1009 log('Error Number : '||SQLCODE);
1010 end;
1011 --
1012 end;