[Home] [Help]
PACKAGE BODY: APPS.HR_APPLICANT_DATAUPD
Source
1 Package Body hr_applicant_dataupd as
2 /* $Header: hraplupd.pkb 120.1 2006/01/11 11:43 irgonzal noship $ */
3 -- Package variables
4 --
5 g_RunUpdateMode_profile constant varchar2(30) := 'HR_APL_UPD_RUN_MODE';
6 g_ADPatchRunMode constant varchar2(100) := 'P';
7 g_CMRunMode constant varchar2(100) := 'D';
8
9 g_concProgramName constant varchar2(30) := 'HRAPLUPD1';
10 g_updateName constant varchar2(30) := 'HRAPLUPD1';
11 g_concPgrNameWrk constant varchar2(30) := 'HRAPLUPD1W';
12
13 e_LockFailure EXCEPTION;
14 e_NoDataFound EXCEPTION;
15 --
16 -- --------------------------------------------------------------------------+
17 -- --------------------< ConvertToApplicant >--------------------------------|
18 -- --------------------------------------------------------------------------+
19 -- Description:
20 -- This procedure converts person into applicant whenever if finds active
21 -- applicant assignments opened and the application has a termination date.
22 --
23 -- The following tables are updated:
24 -- + per_all_people_f
25 -- + per_person_type_usages_f
26 -- + per_applications
27 --
28 -- Scenario:
29 --
30 -- Application Records:
31 -- .
32 -- |--- APPL 1 ----| |---- APPL 2 -----|
33 -- .
34 -- Assignment Records: .
35 -- |----- ASG 1 -------> *** corrupted
36 -- .
37 -- ^application is already end dated
38 --
39 -- After running script person will become an Applicant
40 --
41 -- Application Records:
42 --
43 -- |--- APPL 1 ----| |---- APPL 2 -------------> is opened
44 --
45 -- Assignment Records:
46 -- |----- ASG 1 ------->
47 --
48 --
49 PROCEDURE ConvertToApplicant(p_start_rowid IN rowid
50 ,p_end_rowid IN rowid
51 ,p_rows_processed OUT nocopy number
52 )
53 IS
54 --
55 l_datetrack_mode varchar2(30);
56
57 e_ResourceBusy EXCEPTION;
58 PRAGMA EXCEPTION_INIT(e_ResourceBusy, -54);
59 --
60 CURSOR csr_get_application_details(p_start_rowid rowid, p_end_rowid rowid) IS
61 SELECT application_id, date_end, person_id, object_version_number
62 ,date_received, business_group_id
63 FROM per_applications appl
64 WHERE appl.rowid between p_start_rowid and p_end_rowid
65 AND date_end IS NOT NULL
66 AND EXISTS
67 (SELECT 'Y'
68 FROM per_all_assignments_f paf
69 WHERE paf.application_id = appl.application_id
70 AND paf.assignment_type = 'A'
71 AND paf.effective_end_date > appl.date_end)
72 ORDER BY date_end DESC;
73 --
74 CURSOR csr_lock_person(cp_person_id number, cp_termination_date date) IS
75 SELECT person_id, full_name, applicant_number,object_version_number
76 FROM per_all_people_f
77 WHERE person_id = cp_person_id
78 AND (effective_start_date > cp_termination_date
79 OR
80 cp_termination_date between effective_start_date
81 and effective_end_date)
82 for update nowait;
83 --
84 CURSOR csr_lock_ptu(cp_person_id number, cp_termination_date date) IS
85 SELECT null
86 FROM per_person_type_usages_f ptu
87 ,per_person_types ppt
88 WHERE person_id = cp_person_id
89 AND (effective_start_date > cp_termination_date
90 OR
91 cp_termination_date between effective_start_date
92 and effective_end_date)
93 AND ptu.person_type_id = ppt.person_type_id
94 AND ppt.system_person_type in ('APL','EX_APL')
95 for update of ptu.person_type_id nowait; -- #4919613
96 --
97 CURSOR csr_get_ended_asg(p_application_id number, p_termination_date date) IS
98 SELECT count(assignment_id)
99 FROM per_all_assignments_f paf
100 WHERE paf.application_id = p_application_id
101 AND paf.assignment_type = 'A'
102 AND paf.effective_end_date > p_termination_date
103 AND paf.effective_end_date <> hr_general.end_of_time
104 AND paf.effective_start_date =
105 (select max(effective_start_date) -- do not consider DT updates
106 from per_all_assignments_f paf2
107 where paf2.assignment_id = paf.assignment_id
108 and paf2.effective_end_date > p_termination_date);
109 --
110 CURSOR csr_get_affected_asg(p_application_id number, p_termination_date date) IS
111 SELECT count(assignment_id)
112 FROM per_all_assignments_f paf
113 WHERE paf.application_id = p_application_id
114 AND paf.assignment_type = 'A'
115 AND paf.effective_end_date > p_termination_date
116 AND paf.effective_start_date =
117 (select max(effective_start_date) -- do not consider DT updates
118 from per_all_assignments_f paf2
119 where paf2.assignment_id = paf.assignment_id
120 and paf2.effective_end_date > p_termination_date);
121 --
122 --
123 l_count number;
124 l_appl_rec csr_get_application_details%ROWTYPE;
125 l_failed_apl per_applications.application_id%TYPE;
126 l_person_rec csr_lock_person%ROWTYPE;
127 l_failed_person_id per_all_people_f.person_id%TYPE;
128 l_failed_full_name per_all_people_f.full_name%TYPE;
129 l_rowcount number;
130 l_rowcount_ended number;
131 l_continue_process boolean;
132 l_validation_start_date date;
133 l_validation_end_date date;
134 l_per_object_version_number per_all_people_f.object_version_number%TYPE;
135 l_proc constant varchar2(100) := 'ConvertToApplicant';
136 --
137 l_rows_processed number;
138 --
139 BEGIN
140
141 hr_utility.trace('Entering: '||l_proc);
142 l_rowcount := 0;
143 l_rows_processed := 0;
144 l_failed_apl := null;
145 l_failed_person_id := null;
146 l_failed_full_name := null;
147 l_continue_process := true;
148 l_count := 1;
149 --
150 While l_continue_process LOOP
151 --
152 l_continue_process := false;
153 for l_appl_rec in csr_get_application_details(p_start_rowid, p_end_rowid) loop
154 --
155 BEGIN
156 --
157 l_rows_processed := l_rows_processed + 1;
158 l_failed_apl := l_appl_rec.application_id;
159 l_failed_person_id := l_appl_rec.person_id;
160 --
161 -- ---------------------------------------------------------- +
162 -- Lock application record
163 -- ---------------------------------------------------------- +
164 hr_utility.trace(' 10: locking application '||l_appl_rec.application_id);
165 --
166 begin
167 per_apl_shd.lck
168 (p_application_id => l_appl_rec.application_id
169 ,p_object_version_number => l_appl_rec.object_version_number
170 );
171 exception
172 when others then
173 raise e_ResourceBusy;
174
175 end;
176 -- ------------------------------------------------------------ +
177 -- Lock person records
178 -- ------------------------------------------------------------ +
179 hr_utility.trace(' 20: locking PER and PTU records');
180 --
181 open csr_lock_person(l_appl_rec.person_id, l_appl_rec.date_end);
182 fetch csr_lock_person into l_person_rec;
183 close csr_lock_person;
184 -- ------------------------------------------------------------ +
185 -- lock the PTU records
186 -- ------------------------------------------------------------ +
187 open csr_lock_ptu(l_appl_rec.person_id, l_appl_rec.date_end);
188 close csr_lock_ptu;
189 -- ------------------------------------------------------------ +
190 -- update Person and PTU records
191 -- ------------------------------------------------------------ +
192 hr_utility.trace(' 30: update person and ptu records');
193 --
194 -- Fix for bug 4095315 starts here.
195 --
196 l_per_object_version_number := l_person_rec.object_version_number;
197 --
198 hr_applicant_internal.Update_PER_PTU_Records
199 (p_business_group_id => l_appl_rec.business_group_id
200 ,p_person_id => l_appl_rec.person_id
201 ,p_effective_date => l_appl_rec.date_received
202 ,p_applicant_number => l_person_rec.applicant_number
203 ,p_APL_person_type_id => null
204 ,p_per_effective_start_date => l_validation_start_date
205 ,p_per_effective_end_date => l_validation_end_date
206 ,p_per_object_version_number => l_per_object_version_number --bug 4095315
207 );
208 -- ---------------------------------------------------------- +
209 -- update the application
210 -- ---------------------------------------------------------- +
211 hr_utility.trace(' 40: update application');
212 --
213 per_apl_upd.upd
214 (p_application_id => l_appl_rec.application_id
215 ,p_object_version_number => l_appl_rec.object_version_number
216 ,p_effective_date => l_appl_rec.date_received
217 ,p_date_end => NULL
218 ,p_termination_reason => NULL
219 );
220 --
221 exception
222 --
223 when TIMEOUT_ON_RESOURCE OR e_ResourceBusy then
224 if RunUpdateMode = g_ADPatchRunMode then
225 --
226 IF l_count = 4 then
227 l_continue_process := FALSE;
228 raise e_LockFailure;
229 l_count := 1;
230 else
231 l_continue_process := TRUE;
232 l_count := l_count+1;
233 --
234 end if;
235 --
236 else
237 --
238 l_continue_process := FALSE;
239 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed to process application '||l_failed_apl||
240 ' (person id = '||l_failed_person_id||')');
241 raise e_LockFailure;
242 --
243 end if;
244 --
245 when others then
246 raise;
247 end;
248 --
249 end loop; -- applications found
250 --
251 -- l_count := 1;
252 --
253 end loop; -- infinite while loop.
254 --
255 -- Settting OUT parameters
256 --
257 p_rows_processed := l_rows_processed;
258 --
259 -- Commit the changes.
260 --
261 commit;
262 --
263 hr_utility.trace(' Leaving: '||l_proc);
264 --
265 EXCEPTION
266 when TIMEOUT_ON_RESOURCE OR e_ResourceBusy then
267 -- The required resources are used by some other process.
268 if RunUpdateMode = g_ADPatchRunMode then
269 --
270 -- Fix for bug 4205784.comment out the following code.
271 --
272 --raise;
273 hr_utility.trace('Failed to process application '||l_failed_apl||
274 ' (person id = '||l_failed_person_id||')');
275
276 else
277 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed to process application '||l_failed_apl||
278 ' (person id = '||l_failed_person_id||')');
279 raise e_LockFailure;
280
281 end if;
282 when OTHERS then
283 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while processing application '||l_failed_apl||
284 ' (person id = '||l_failed_person_id||')');
285 raise;
286
287 END ConvertToApplicant;
288 --
289 -- --------------------------------------------------------------------------+
290 -- -----------------< Update_APL_using_LTU >---------------------------------|
291 -- --------------------------------------------------------------------------+
292 --
293 PROCEDURE Update_APL_using_LTU
294 (errbuf OUT nocopy varchar2
295 ,retcode OUT nocopy number
296 ,p_this_worker IN number
297 ,p_total_workers IN number
298 ,p_table_owner IN varchar2
299 ,p_table_name IN varchar2
300 ,p_update_name IN varchar2
301 ,p_batchsize IN number)
302 IS
303
304 l_any_rows_to_process boolean;
305 l_start_rowid rowid;
306 l_end_rowid rowid;
307 l_rows_processed number;
308 --
309 BEGIN
310 --
311 ad_parallel_updates_pkg.initialize_rowid_range(
312 ad_parallel_updates_pkg.ROWID_RANGE,
313 p_table_owner,
314 p_table_name,
315 p_update_name,
316 p_this_worker,
317 p_total_workers,
318 p_batchsize, 0);
319
320 ad_parallel_updates_pkg.get_rowid_range(
321 l_start_rowid,
322 l_end_rowid,
323 l_any_rows_to_process,
324 p_batchsize,
325 TRUE);
326 --
327 while (l_any_rows_to_process = TRUE)
328 loop
329 hr_applicant_dataupd.ConvertToApplicant(l_start_rowid, l_end_rowid, l_rows_processed);
330
331 ad_parallel_updates_pkg.processed_rowid_range(
332 l_rows_processed,
333 l_end_rowid);
334 --
335 -- commit transaction here
336 --
337 commit;
338 --
339 -- get new range of rowids
340 --
341 ad_parallel_updates_pkg.get_rowid_range(
342 l_start_rowid,
343 l_end_rowid,
344 l_any_rows_to_process,
345 p_batchsize,
346 FALSE);
347
348 end loop;
349 --
350 END Update_APL_using_LTU;
351 --
352 -- --------------------------------------------------------------------------+
353 -- Update_APL_inCM_Manager
354 -- --------------------------------------------------------------------------+
355 -- This is run as a concurrent program
356 --
357 PROCEDURE Update_APL_inCM_Manager
358 (p_errbuf out nocopy varchar2
359 ,p_retcode out nocopy varchar2
360 ,X_batch_size in number
361 ,X_Num_Workers in number
362 ,p_process_All in varchar2
363 ,p_caller in varchar2 -- MB: Move parameter up so it occurs before
364 -- optional parameters.
365 ,p_apl_id in number default 0
366 --,p_caller in varchar2
367 ) IS
368 --
369 cursor csr_get_apl_rowid(cp_apl_id number) is
370 select rowid
371 from per_applications
372 where application_id = cp_apl_id;
373
374 l_apl_rowid rowid;
375 --
376 l_product varchar2(30);
377 l_table_name varchar2(30);
378 l_status varchar2(30);
379 l_industry varchar2(30);
380 l_retstatus boolean;
381 l_table_owner varchar2(30);
382 l_update_name varchar2(30);
383 l_any_rows_to_process boolean;
384 l_start_rowid rowid;
385 l_rows_processed number;
386 req_data varchar2(240);
387 BEGIN
388 --
389 l_product := 'PER';
390 l_table_name := 'PER_APPLICATIONS';
391 l_update_name := g_updateName; -- this matches name used in ADPATCH script
392 --
393 -- get schema name of the table for ROWID range processing
394 --
395 l_retstatus := fnd_installation.get_app_info(
396 l_product, l_status, l_industry, l_table_owner);
397 IF ((l_retstatus = FALSE)
398 OR (l_table_owner is null)) THEN
399 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
400 END IF;
401 fnd_file.put_line(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
402 fnd_file.put_line(FND_FILE.LOG, ' UpdateName : '||l_update_name);
403 fnd_file.put_line(FND_FILE.LOG, ' p_caller : '||p_caller);
404 --
405 IF p_process_ALL = 'Y' THEN
406 --
407 --
408 -- Manager processing
409 --
410 req_data := fnd_conc_global.request_data; --
411 if req_data is not null then
412 --
413 -- indicate that the named update process has started processing
414 --
415 hr_update_utility.setUpdateProcessing(p_update_name => l_update_name);
416 --
417 -- set status of complete in the PAY_UPGRADE_STATUS table
418 --
419 hr_update_utility.setUpdateComplete(p_update_name => l_update_name);
420 --
421 else
422 /* We are not on a restart therefore if we are running from
423 ** a manual submission delete the PAY_UPGRADE_STATUS record.
424 */
425 if p_caller = 'F' then
426 delete from pay_upgrade_status
427 where upgrade_definition_id =
428 (select upgrade_definition_id
429 from pay_upgrade_definitions
430 where short_name = l_update_name);
431 fnd_file.put_line(FND_FILE.LOG,' ** Upgrade Status row deleted successfully **');
432
433 -- If we are called from a manual submission then we need to run the
434 -- data update even if it has run before. Therefore the LTU update
435 -- name needs to be a new previously unused value so concat the
436 -- current date and time.
437 --
438 l_update_name := l_update_name||'_'||to_char(sysdate,'DDMMRRHH24MISS');
439 end if;
440 end if;
441 AD_CONC_UTILS_PKG.submit_subrequests(
442 X_errbuf => p_errbuf,
443 X_retcode => p_retcode,
444 X_WorkerConc_app_shortname => l_product,
445 X_WorkerConc_progname => g_concPgrNameWrk, -- worker SRS
446 X_batch_size => X_batch_size,
447 X_Num_Workers => X_Num_Workers,
448 X_Argument4 => p_process_ALL,
449 X_Argument5 => p_caller,
450 X_Argument6 => l_update_name,
451 X_Argument7 => p_apl_id
452 );
453
454 ELSE
455 --
456 -- process ONE application: no need to invoke the LTU mechanism
457 --
458 BEGIN
459 open csr_get_apl_rowid(p_apl_id);
460 fetch csr_get_apl_rowid into l_apl_rowid;
461 if csr_get_apl_rowid%NOTFOUND then
462 close csr_get_apl_rowid;
463 fnd_file.put_line(FND_FILE.LOG, '**************');
464 fnd_file.put_line(FND_FILE.LOG, 'Application ID: '||p_apl_id||' not found.');
465 raise e_NoDataFound;
466 else
467 close csr_get_apl_rowid;
468 hr_applicant_dataupd.ConvertToApplicant(l_apl_rowid, l_apl_rowid, l_rows_processed);
469 end if;
470 --
471 p_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
472 EXCEPTION
473 WHEN e_LockFailure THEN
474 p_retcode := AD_CONC_UTILS_PKG.CONC_WARNING;
475 WHEN OTHERS THEN
476 p_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
477 raise;
478 END;
479
480 END IF;
481 --
482 END Update_APL_inCM_Manager;
483
484 --
485 -- --------------------------------------------------------------------------+
486 -- Update_APL_inCM_Worker
487 -- --------------------------------------------------------------------------+
488 -- This is run as a concurrent program
489 --
490 PROCEDURE Update_APL_inCM_Worker
491 (p_errbuf out nocopy varchar2
492 ,p_retcode out nocopy varchar2
493 ,X_batch_size in number
494 ,X_Worker_Id in number
495 ,X_Num_Workers in number
496 ,p_process_All in varchar2
497 ,p_caller in varchar2 -- Move parameter up so it occurs before
498 -- optional parameters.
499 ,p_updateName in varchar2
500 ,p_apl_id in number default 0
501 --,p_caller in varchar2
502 ) IS
503 --
504 cursor csr_get_apl_rowid(cp_apl_id number) is
505 select rowid
506 from per_applications
507 where application_id = cp_apl_id;
508
509 l_apl_rowid rowid;
510 --
511 l_product varchar2(30);
512 l_table_name varchar2(30);
513 l_status varchar2(30);
514 l_industry varchar2(30);
515 l_retstatus boolean;
516 l_table_owner varchar2(30);
517 l_any_rows_to_process boolean;
518 l_start_rowid rowid;
519 l_rows_processed number;
520 req_data varchar2(240);
521 BEGIN
522 --
523 l_product := 'PER';
524 l_table_name := 'PER_APPLICATIONS';
525 --
526 -- get schema name of the table for ROWID range processing
527 --
528 l_retstatus := fnd_installation.get_app_info(
529 l_product, l_status, l_industry, l_table_owner);
530 IF ((l_retstatus = FALSE)
531 OR (l_table_owner is null)) THEN
532 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
533 END IF;
534 fnd_file.put_line(FND_FILE.LOG, ' X_Worker_Id : '||X_Worker_Id);
535 fnd_file.put_line(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
536 fnd_file.put_line(FND_FILE.LOG, ' updateName : '||p_updateName);
537 --
538 -- Worker processing
539 --
540 BEGIN
541 hr_applicant_dataupd.Update_APL_using_LTU
542 (errbuf => p_errbuf
543 ,retcode => p_retcode
544 ,p_this_worker => X_worker_id
545 ,p_total_workers => X_num_workers
546 ,p_table_owner => l_table_owner
547 ,p_table_name => l_table_name
548 ,p_update_name => p_updateName
549 ,p_batchsize => X_batch_size);
550 --
551 p_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
552 EXCEPTION
553 WHEN e_LockFailure THEN
554 p_retcode := AD_CONC_UTILS_PKG.CONC_WARNING;
555 WHEN OTHERS THEN
556 p_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
557 raise;
558 END;
559 --
560 END Update_APL_inCM_Worker;
561 --
562 -- --------------------------------------------------------------------------+
563 -- ValidateRun
564 -- --------------------------------------------------------------------------+
565 --
566 PROCEDURE ValidateRun(p_result OUT nocopy varchar2) IS
567 l_result varchar2(10);
568 BEGIN
569 l_result := hr_update_utility.isUpdateComplete
570 (p_app_shortname => g_concProgramName
571 ,p_function_name => null
572 ,p_business_group_id => null
573 ,p_update_name => g_updateName);
574 --
575 if l_result = 'FALSE' then
576 p_result := 'TRUE';
577 else
578 p_result := 'FALSE';
579 end if;
580 --
581 END ValidateRun;
582 --
583 -- --------------------------------------------------------------------------+
584 -- RunUpdateMode
585 -- --------------------------------------------------------------------------+
586 -- Returns the value of the profile option:
587 -- + P: run within adpatch
588 -- + D: run when concurrent program is re-started (deferred process)
589 --
590 -- If profile value is not set, then returns 'ADPATCH'
591 --
592 FUNCTION RunUpdateMode RETURN varchar2 IS
593 --
594 l_value varchar2(100);
595 l_defined boolean;
596
597 BEGIN
598 --
599 l_value := FND_PROFILE.value(g_RunUpdateMode_profile);
600 if l_value is NULL then
601 return(g_ADPatchRunMode);
602 else
603 return(l_value);
604 end if;
605 --
606 END RunUpdateMode;
607 --
608 -- --------------------------------------------------------------------------+
609 -- isADPATCHMode
610 -- --------------------------------------------------------------------------+
611 FUNCTION isADPATCHMode return boolean IS
612 BEGIN
613 return(RunUpdateMode = g_ADPatchRunMode);
614 END isADPATCHMode;
615 --
616 -- --------------------------------------------------------------------------+
617 -- isDEFERMode
618 -- --------------------------------------------------------------------------+
619 FUNCTION isDEFERMode return boolean IS
620 BEGIN
621 return(RunUpdateMode = g_CMRunMode);
622 END isDEFERMode;
623 --
624 end hr_applicant_dataupd;