1 PACKAGE BODY PER_BG_NUMBERING_METHOD_PKG AS
2 /* $Header: pebgnuma.pkb 120.1 2009/12/22 06:31:55 skura ship $ */
3 --
4 -- Package Variables
5 --
6 g_X_EMPprofileset boolean;
7 g_X_APLprofileset boolean;
8 g_X_CWKprofileset boolean;
9 --
10 g_debug boolean := hr_utility.debug_enabled;
11 g_pkg_name varchar2(30) := 'per_bg_numbering_method_pkg';
12 g_max_person_number number := 999999999999999999999999999999;
13 g_max_global_person_num number := 999999999999999999999999999;
14 --
15 -- Enh 2931775
16 g_emp_sequence_name varchar2(30) := 'PER_GLOBAL_EMP_NUM_S';
17 g_apl_sequence_name varchar2(30) := 'PER_GLOBAL_APL_NUM_S';
18 g_cwk_sequence_name varchar2(30) := 'PER_GLOBAL_CWK_NUM_S';
19 --
20 g_xbg_EMPNum_profile varchar2(30) := 'PER_GLOBAL_EMP_NUM';
21 g_xbg_APLNum_profile varchar2(30) := 'PER_GLOBAL_APL_NUM';
22 g_xbg_CWKNum_profile varchar2(30) := 'PER_GLOBAL_CWK_NUM';
23 --
24 g_bg_context_name varchar2(30) := 'Business Group Information';
25 g_automatic_method varchar2(1) := 'A';
26 --
27 g_EMP_Num_FF_cached boolean := FALSE;
28 g_EMP_Num_FF_ID_cache ff_formulas_f.formula_name%TYPE := null;
29 --
30 g_APL_Num_FF_cached boolean := FALSE;
31 g_APL_Num_FF_ID_cache ff_formulas_f.formula_name%TYPE := null;
32 --
33 g_CWK_Num_FF_cached boolean := FALSE;
34 g_CWK_Num_FF_ID_cache ff_formulas_f.formula_name%TYPE := null;
35
36 --
37 -- ---------------------------------------------------------------------------+
38 -- Local Procedures
39 -- ---------------------------------------------------------------------------+
40 -- Write_Log: writes messages to the LOG file generated by Concurrent Mg.
41 -- ---------------------------------------------------------------------------+
42 PROCEDURE Write_Log
43 ( p_message varchar2 ) IS
44 --
45 c_proc_name varchar2(100) := g_pkg_name||'.write_log';
46 BEGIN
47 FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 --
52 -- Exception raised whilst writing the log file...
53 --
54 hr_utility.set_location(c_proc_name,1000);
55 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
56 RAISE;
57 END Write_Log;
58 -- ---------------------------------------------------------------------------+
59 -- ---------------------------------------------------------------------------+
60 -- Get_Max_applicant_number: - Applicants -
61 -- Returns the maximum numeric value being used in a business group.
62 -- ---------------------------------------------------------------------------+
63 FUNCTION get_max_applicant_number
64 (p_business_group_id IN per_all_people.business_group_id%TYPE)
65 RETURN NUMBER IS
66 --
67 TYPE l_applicant_number_type IS TABLE OF per_all_people_f.applicant_number%TYPE
68 INDEX BY BINARY_INTEGER;
69 --
70 l_applicant_number_tab l_applicant_number_type;
71 l_max_applicant_number NUMBER := 0;
72 --
73 cursor csr_applicants is
74 select applicant_number
75 from per_all_people_f
76 where business_group_id = p_business_group_id
77 and applicant_number is not null;
78 --
79 BEGIN
80 OPEN csr_applicants;
81 LOOP
82 -- bulk collect 500 rows at a time
83 FETCH csr_applicants BULK COLLECT INTO l_applicant_number_tab LIMIT 500;
84 -- loop through each element
85 FOR i IN l_applicant_number_tab.FIRST..l_applicant_number_tab.LAST LOOP
86 BEGIN
87 -- perform a number conversion and then max comparison
88 -- if this fails because the applicant_number contains alpha chars
89 -- then the VALUE_ERROR exception will be raised and handled
90 IF TO_NUMBER(l_applicant_number_tab(i)) > l_max_applicant_number THEN
91 -- conversion succeeded, store new max number
92 if(instr(upper(l_applicant_number_tab(i)),'E') = 0 ) then --Added for bug9178149
93 l_max_applicant_number := TO_NUMBER(l_applicant_number_tab(i));
94 end if;
95 END IF;
96 EXCEPTION
97 WHEN VALUE_ERROR THEN
98 -- conversion error, applicant number must contain an alpha char so ignore
99 NULL;
100 END;
101 END LOOP;
102 EXIT WHEN csr_applicants%NOTFOUND;
103 END LOOP;
104 CLOSE csr_applicants;
105 RETURN(l_max_applicant_number);
106 EXCEPTION
107 WHEN OTHERS THEN
108 -- this exception is most probably raised from a VALUE_ERROR when
109 -- l_applicant_number_tab.FIRST fails due to a fetch returning no
110 -- rows.
111 -- close the cursor if its open
112 IF csr_applicants%ISOPEN THEN
113 CLOSE csr_applicants;
114 END IF;
115 RETURN(l_max_applicant_number);
116 END get_max_applicant_number;
117 -- ---------------------------------------------------------------------------+
118 -- ---------------------------------------------------------------------------+
119 -- Get_Max_employee_number: - Employees -
120 -- Returns the maximum numeric value being used in a business group.
121 -- ---------------------------------------------------------------------------+
122 --
123 FUNCTION get_max_employee_number
124 (p_business_group_id IN per_all_people.business_group_id%TYPE)
125 RETURN NUMBER IS
126 --
127 TYPE l_employee_number_type IS TABLE OF per_all_people_f.employee_number%TYPE
128 INDEX BY BINARY_INTEGER;
129 --
130 l_employee_number_tab l_employee_number_type;
131 l_max_employee_number NUMBER := 0;
132 --
133 cursor csr_employees is
134 select employee_number
135 from per_all_people_f
136 where business_group_id = p_business_group_id
137 and employee_number is not null;
138 --
139 BEGIN
140 OPEN csr_employees;
141 LOOP
142 -- bulk collect 500 rows at a time
143 FETCH csr_employees BULK COLLECT INTO l_employee_number_tab LIMIT 500;
144 -- loop through each element
145 FOR i IN l_employee_number_tab.FIRST..l_employee_number_tab.LAST LOOP
146 BEGIN
147 -- perform a number conversion and then max comparison
148 -- if this fails because the employee_number contains alpha chars
149 -- then the VALUE_ERROR exception will be raised and handled
150 --
151 IF TO_NUMBER(l_employee_number_tab(i)) > l_max_employee_number THEN
152 -- conversion succeeded, store new max number
153 if(instr(upper(l_employee_number_tab(i)),'E') = 0 ) then --Added for bug9178149
154 l_max_employee_number := TO_NUMBER(l_employee_number_tab(i));
155 end if;
156 END IF;
157 EXCEPTION
158 WHEN VALUE_ERROR THEN
159 -- conversion error, employee number must contain an alpha char so ignore
160 NULL;
161 END;
162 END LOOP;
163 EXIT WHEN csr_employees%NOTFOUND;
164 END LOOP;
165 CLOSE csr_employees;
166 RETURN(l_max_employee_number);
167 EXCEPTION
168 WHEN OTHERS THEN
169 -- this exception is most probably raised from a VALUE_ERROR when
170 -- l_employee_number_tab.FIRST fails due to a fetch returning no
171 -- rows.
172 -- close the cursor if its open
173 IF csr_employees%ISOPEN THEN
174 CLOSE csr_employees;
175 END IF;
176 RETURN(l_max_employee_number);
177 END get_max_employee_number;
178 -- ---------------------------------------------------------------------------+
179 -- ---------------------------------------------------------------------------+
180 -- Get_Max_CWK_number: - Contingent Workers -
181 -- Returns the maximum numeric value being used in a business group.
182 -- ---------------------------------------------------------------------------+
183 --
184 FUNCTION get_max_cwk_number
185 (p_business_group_id IN per_all_people.business_group_id%TYPE)
186 RETURN NUMBER IS
187 --
188 TYPE l_cwk_number_type IS TABLE OF per_all_people_f.npw_number%TYPE
189 INDEX BY BINARY_INTEGER;
190 --
191 l_cwk_number_tab l_cwk_number_type;
192 l_max_cwk_number NUMBER := 0;
193 --
194 cursor csr_cwk is
195 select npw_number
196 from per_all_people_f
197 where business_group_id = p_business_group_id
198 and npw_number is not null;
199 --
200 BEGIN
201 OPEN csr_cwk;
202 LOOP
203 -- bulk collect 500 rows at a time
204 FETCH csr_cwk BULK COLLECT INTO l_cwk_number_tab LIMIT 500;
205 -- loop through each element
206 FOR i IN l_cwk_number_tab.FIRST..l_cwk_number_tab.LAST LOOP
207 BEGIN
208 -- perform a number conversion and then max comparison
209 -- if this fails because the npw_number contains alpha chars
210 -- then the VALUE_ERROR exception will be raised and handled
211 --
212 IF TO_NUMBER(l_cwk_number_tab(i)) > l_max_cwk_number THEN
213 -- conversion succeeded, store new max number
214 if(instr(upper(l_cwk_number_tab(i)),'E') = 0 ) then --Added for bug9178149
215 l_max_cwk_number := TO_NUMBER(l_cwk_number_tab(i));
216 end if;
217 END IF;
218 EXCEPTION
219 WHEN VALUE_ERROR THEN
220 -- conversion error, cwk number must contain an alpha char so ignore
221 NULL;
222 END;
223 END LOOP;
224 EXIT WHEN csr_cwk%NOTFOUND;
225 END LOOP;
226 CLOSE csr_cwk;
227 RETURN(l_max_cwk_number);
228 EXCEPTION
229 WHEN OTHERS THEN
230 -- this exception is most probably raised from a VALUE_ERROR when
231 -- l_employee_number_tab.FIRST fails due to a fetch returning no
232 -- rows.
233 -- close the cursor if its open
234 IF csr_cwk%ISOPEN THEN
235 CLOSE csr_cwk;
236 END IF;
237 RETURN(l_max_cwk_number);
238 END get_max_cwk_number;
239 -- ---------------------------------------------------------------------------+
240 -- ---------------------------------------------------------------------------+
241 PROCEDURE convert_to_auto_gen_method
242 (errbuf OUT nocopy varchar2
243 ,retcode OUT nocopy number
244 ,p_business_group_id IN number
245 ,p_person_type IN varchar2
246 ) IS
247 --
248 -- Local variables
249 --
250 c_proc_name varchar2(100) := g_pkg_name||'.convert_to_auto_gen_method';
251
252 e_ResourceBusy EXCEPTION;
253 PRAGMA EXCEPTION_INIT(e_ResourceBusy, -54);
254
255 TYPE t_bgRecord IS RECORD
256 (
257 org_id HR_ORGANIZATION_INFORMATION.Organization_id%TYPE,
258 emp_method HR_ORGANIZATION_INFORMATION.Org_information2%TYPE,
259 apl_method HR_ORGANIZATION_INFORMATION.Org_information3%TYPE,
260 cwk_method HR_ORGANIZATION_INFORMATION.Org_information16%TYPE
261 );
262
263 l_max_num_found number := 0;
264 l_message varchar2(200) := null;
265 l_rows_updated number;
266 l_continue_flag boolean := FALSE;
267 l_use_sequence boolean := FALSE;
268
269
270 l_organization_id per_all_people_f.business_group_id%TYPE;
271 l_rec_per_bg_groups t_bgRecord;
272 --
273 -- Returns the current method of number generation
274 --
275 cursor csr_method(cp_bg_id per_all_people.business_group_id%TYPE) is
276 SELECT organization_id, Org_information2, Org_information3, Org_information16
277 from hr_organization_information
278 where organization_id = cp_bg_id
279 and ORG_INFORMATION_CONTEXT = g_bg_context_name
280 FOR UPDATE of Org_information3 -- method_of_generation_apl_num
281 , Org_information2 -- method_of_generation_emp_num
282 , Org_information16 NOWAIT; -- method_of_generation_cwk_num
283 --
284 -- Returns next value stored per person type
285 --
286 cursor csr_next_value(cp_bg_id per_all_people.business_group_id%TYPE
287 ,cp_person_type per_person_types.system_person_type%TYPE) is
288 SELECT business_group_id
289 from per_number_generation_controls
290 where business_group_id = cp_bg_id
291 and type = cp_person_type
292 FOR UPDATE of next_value NOWAIT;
293 --
294 --
295 BEGIN
296 --hr_utility.trace_on(null,'oracle');
297
298 hr_utility.set_location('Entering: '||c_proc_name,1);
299 hr_utility.trace('Parameters:');
300 hr_utility.trace(' business_group_id = '||to_char(p_business_group_id));
301 hr_utility.trace(' person type = '||p_person_type);
302
303 --
304 BEGIN
305 -- Lock per_all_people_f to ensure person records are not created/updated/deleted
306 --
307 hr_utility.set_location(c_proc_name,2);
308 l_rows_updated := 0;
309 l_organization_id := p_business_group_id;
310 l_use_sequence := Global_person_numbering(p_person_type);
311
312 LOCK TABLE per_all_people_f
313 IN EXCLUSIVE MODE NOWAIT;
314 --
315 open csr_method(p_business_group_id);
316 fetch csr_method into l_rec_per_bg_groups;
317
318 if csr_method%FOUND then
319
320 hr_utility.set_location(c_proc_name,5);
321 if l_use_sequence then
322 l_continue_flag := TRUE;
323 hr_utility.set_location(c_proc_name,7);
324 else
325 open csr_next_value(p_business_group_id, p_person_type);
326 fetch csr_next_value into l_organization_id;
327 l_continue_flag := csr_next_value%FOUND;
328 end if;
329
330 if l_continue_flag then
331
332 hr_utility.set_location(c_proc_name,10);
333 -- -------------------------------------------------------------+
334 -- Processing Applicants +
335 -- -------------------------------------------------------------+
336 if p_person_type = 'APL' then
337
338 hr_utility.set_location(c_proc_name,12);
339 l_max_num_found := get_max_applicant_number(p_business_group_id);
340
341 UPDATE HR_ORGANIZATION_INFORMATION
342 SET Org_information3 = g_automatic_method -- method_of_generation_apl_num
343 WHERE organization_id = l_organization_id
344 AND ORG_INFORMATION_CONTEXT = g_bg_context_name;
345
346 hr_utility.set_location(c_proc_name,14);
347 l_rows_updated := SQL%ROWCOUNT;
348
349 -- -------------------------------------------------------------+
350 -- Processing Employees +
351 -- -------------------------------------------------------------+
352 elsif p_person_type = 'EMP' then
353
354 hr_utility.set_location(c_proc_name,16);
355 -- needs to check whether CWK method = 'Based on Employee'
356 -- if yes, then need to greatest(empno, cwkno)
357 --
358 if l_rec_per_bg_groups.cwk_method = 'E' then
359 hr_utility.set_location(c_proc_name,17);
360
361 l_max_num_found := greatest(get_max_employee_number(p_business_group_id)
362 ,get_max_cwk_number(p_business_group_id));
363 else
364 l_max_num_found := get_max_employee_number(p_business_group_id);
365 end if;
366
367 UPDATE HR_ORGANIZATION_INFORMATION
368 SET Org_information2 = g_automatic_method -- method_of_generation_emp_num
369 WHERE organization_id = l_organization_id
370 AND ORG_INFORMATION_CONTEXT = g_bg_context_name;
371
372 hr_utility.set_location(c_proc_name,18);
373 l_rows_updated := SQL%ROWCOUNT;
374 -- -------------------------------------------------------------+
375 -- Processing Contingent Workers +
376 -- -------------------------------------------------------------+
377 elsif p_person_type = 'CWK' then
378
379 hr_utility.set_location(c_proc_name,20);
380 -- needs to check whether CWK method = 'Based on Employee'
381 -- if yes, then need to greatest(empno, cwkno)
382 --
383 if l_rec_per_bg_groups.cwk_method = 'E' then
384 hr_utility.set_location(c_proc_name,21);
385
386 l_max_num_found := greatest(get_max_employee_number(p_business_group_id)
387 ,get_max_cwk_number(p_business_group_id));
388 else
389 l_max_num_found := get_max_cwk_number(p_business_group_id);
390 end if;
391
392 UPDATE HR_ORGANIZATION_INFORMATION
393 SET Org_information16 = g_automatic_method -- method_of_generation_cwk_num
394 WHERE organization_id = l_organization_id
395 AND ORG_INFORMATION_CONTEXT = g_bg_context_name;
396
397 hr_utility.set_location(c_proc_name,22);
398 l_rows_updated := SQL%ROWCOUNT;
399 end if;
400 -- -------------------------------------------------------------+
401 -- Update NEXT VALUE +
402 -- -------------------------------------------------------------+
403 if (l_max_num_found + 1 > g_max_person_number)
404 or (l_use_sequence and l_max_num_found > g_max_global_person_num)
405 then
406
407 hr_utility.set_message(800,'PER_289925_MAX_VALUE');
408 hr_utility.raise_error;
409 else
410 if l_rows_updated > 0 and l_max_num_found is not null then
411 hr_utility.set_location(c_proc_name,24);
412
413 if l_use_sequence then
414 -- alter sequence
415 hr_utility.set_location(c_proc_name,25);
416 set_global_sequence(p_person_type,l_max_num_found);
417 else
418 hr_utility.set_location(c_proc_name,26);
419 UPDATE per_number_generation_controls
420 SET next_value = l_max_num_found + 1
421 WHERE business_group_id = l_organization_id
422 and type = p_person_type;
423 if csr_next_value%ISOPEN then
424 close csr_next_value;
425 end if;
426 end if;
427 end if;
428 end if;
429
430 hr_utility.set_location(c_proc_name,27);
431
432 end if; -- continue flag?
433
434 end if; -- csr_method cursor
435 --
436 hr_utility.set_location(c_proc_name,28);
437 hr_utility.trace(' MAX value found = '||to_char(l_max_num_found));
438 --
439 close csr_method;
440 EXCEPTION
441 when TIMEOUT_ON_RESOURCE OR e_ResourceBusy then
442 hr_utility.set_location(c_proc_name,29);
443 -- The required resources are used by some other process.
444
445 hr_utility.set_message(800,'PER_289849_RESOURCE_BUSY');
446 hr_utility.raise_error;
447
448 END; -- Lock table
449 hr_utility.set_location('Leaving: '||c_proc_name,35);
450 --hr_utility.trace_off;
451
452 END convert_to_auto_gen_method;
453 -- -------------------------------------------------------------------------- +
454 -- -------------------------------------------------------------------------- +
455 -- SET_GLOBAL_SEQUENCE: |
456 -- Alters the global sequence for a specific person type based on last |
457 -- number parameter. |
458 -- -------------------------------------------------------------------------- +
459 PROCEDURE SET_GLOBAL_SEQUENCE(p_person_type IN varchar2
460 ,p_last_number IN NUMBER)
461 IS
462 PRAGMA AUTONOMOUS_TRANSACTION;
463 -- this is required since DDL commands execute implicit commits
464 -- if for some reason, this procedure fails the calling procedure
465 -- should be rolled back.
466 --
467 l_max_number number;
468 c_proc_name varchar2(100) := g_pkg_name||'.set_global_sequence';
469 l_seq_owner varchar2(30);
470 l_seq_name varchar2(30);
471 l_seq_increment number;
472 l_string varchar2(1000);
473 l_PrevSettings varchar2(1000);
474 l_mynextval number;
475 l_currval number;
476 l_seq_last number;
477 l_cache_size number;
478 l_min_value number;
479 l_max_value number;
480 l_cycleflag varchar2(1);
481 l_cache_arg varchar2(100);
482 l_alterflag number := 0;
483 -- 3385104 start
484 l_status varchar2(50);
485 l_industry varchar2(50);
486 l_owner varchar2(50);
487 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
488 l_industry, l_owner);
489 --3385104 end
490 --
491 cursor csr_seq_details(cp_seq_name varchar2) IS
492 SELECT sequence_owner, last_number, cache_size,
493 min_value, max_value, cycle_flag
494 FROM all_sequences
495 WHERE sequence_name = cp_seq_name
496 and sequence_owner = l_owner;
497 -- added owner condition for fix of bug 3385104
498 --
499 BEGIN -- main set_global_sequence
500 if g_debug then
501 hr_utility.trace('Entering :'||c_proc_name);
502 end if;
503 -- -------------------------------------------+
504 -- get the sequence name based on person type |
505 -- -------------------------------------------+
506 if p_person_type = 'EMP' then
507 l_seq_name := g_emp_sequence_name;
508 elsif p_person_type = 'APL' then
509 l_seq_name := g_apl_sequence_name;
510 elsif p_person_type = 'CWK' then
511 l_seq_name := g_cwk_sequence_name;
512 end if;
513 -- ---------------------------------------+
514 -- get current sequence details |
515 -- ---------------------------------------+
516 open csr_seq_details(l_seq_name);
517 fetch csr_seq_details into l_seq_owner, l_seq_last, l_cache_size
518 , l_min_value, l_max_value, l_cycleflag;
519 close csr_seq_details;
520 -- ----------------------------------------+
521 -- set details regarding original settings |
522 -- ----------------------------------------+
523 IF (l_cache_size = 0) THEN
524 l_cache_arg := ' ';
525 ELSE
526 l_cache_arg := ' CACHE ' || l_cache_size;
527 END IF;
528 --
529 --
530 l_PrevSettings := 'ALTER SEQUENCE ' || l_seq_owner || '.' || l_seq_name
531 || ' INCREMENT BY 1 ' ||
532 l_cache_arg || ' MAXVALUE ' || to_char(l_max_value)
533 || ' MINVALUE ' || to_char(l_min_value);
534 -- -----------------------------------------------------------+
535 -- get current value from sequence to calculate the increment |
536 -- -----------------------------------------------------------+
537 l_mynextval := 0;
538 l_currval := 0;
539 l_string := 'SELECT ' || l_seq_owner || '.' || l_seq_name ||
540 '.NEXTVAL FROM sys.dual';
541
542 EXECUTE IMMEDIATE l_string INTO l_mynextval;
543
544 l_mynextval := l_mynextval - 1;
545 l_currval := l_mynextval; -- store this value in case of a rollback
546 --
547 if g_debug then
548 hr_utility.trace('Current Value = '||to_char(l_mynextval));
549 end if;
550 -- ---------------------------------------+
551 -- calculate the increment |
552 -- ---------------------------------------+
553 if l_mynextval < p_last_number then
554 l_alterflag := l_alterflag + 1;
555
556 l_seq_increment := p_last_number - l_mynextval -1;
557 --
558 if g_debug then
559 hr_utility.trace('Altering sequence increment to ' || l_seq_increment);
560 end if;
561 --
562 l_string := 'ALTER SEQUENCE ' || l_seq_owner || '.' || l_seq_name
563 || ' INCREMENT BY ' ||
564 l_seq_increment ||
565 ' NOCACHE NOMAXVALUE';
566
567 EXECUTE IMMEDIATE l_string;
568 -- -----------------------------------------------------+
569 -- force the update of the sequence into the new range |
570 -- -----------------------------------------------------+
571 if g_debug then
572 hr_utility.trace('Forcing sequence increment');
573 end if;
574 --
575 l_string := 'SELECT ' || l_seq_owner || '.' || l_seq_name ||
576 '.nextval FROM sys.dual';
577
578 EXECUTE IMMEDIATE l_string INTO l_mynextval;
579 l_alterflag := l_alterflag + 1;
580 -- ---------------------------------------+
581 -- return sequence to previous settings |
582 -- ---------------------------------------+
583 if g_debug then
584 hr_utility.trace('Reseting sequence increment');
585 end if;
586
587 EXECUTE IMMEDIATE l_PrevSettings;
588 l_alterflag := l_alterflag + 1;
589 --
590 end if; -- l_mynextval < p_last_number
591 if g_debug then
592 hr_utility.trace('Leaving : '||c_proc_name);
593 end if;
594 EXCEPTION
595 when others then
596 --
597 -- Rollback sequence changes
598 --
599 if l_alterflag > 1 and l_mynextval > 0 and l_currval > 0 then
600 l_seq_increment := l_currval - l_mynextval;
601 l_string := 'ALTER SEQUENCE ' || l_seq_owner || '.' || l_seq_name
602 || ' INCREMENT BY ' ||
603 l_seq_increment || ' NOCACHE ';
604
605 EXECUTE IMMEDIATE l_string;
606 --
607 l_string := 'SELECT ' || l_seq_owner || '.' || l_seq_name ||
608 '.nextval FROM sys.dual';
609
610 EXECUTE IMMEDIATE l_string INTO l_mynextval;
611 -- return to previous settings
612 EXECUTE IMMEDIATE l_PrevSettings;
613 if g_debug then
614 hr_utility.trace('Leaving: sequence rollback done =>'||c_proc_name);
615 end if;
616 end if;
617 --
618 --
619 RAISE;
620 END SET_GLOBAL_SEQUENCE;
621 -- -------------------------------------------------------------------------- +
622 -- -------------------------------------------------------------------------- +
623 -- CONVERT_TO_GLOBAL_SEQUENCE: - this is run as a Conc. Request - |
624 -- Enables the global sequence for a specific person type. |
625 -- This sequence will be use to generate person numbers and will be shared |
626 -- among all business groups. |
627 -- -------------------------------------------------------------------------- +
628 PROCEDURE convert_to_global_sequence
629 (errbuf OUT nocopy varchar2
630 ,retcode OUT nocopy number
631 ,p_person_type IN varchar2
632 ) IS
633
634 l_max_number number;
635 c_proc_name varchar2(100) := g_pkg_name||'.convert_to_global_sequence';
636 --
637 l_message varchar2(2000);
638 l_success boolean := FALSE;
639 l_session_date date;
640 --
641 e_ResourceBusy EXCEPTION;
642 PRAGMA EXCEPTION_INIT(e_ResourceBusy, -54);
643 e_ProfileUpdFailed EXCEPTION;
644 e_SeqError EXCEPTION;
645 --
646 cursor csr_max_emp_num(cp_person_type varchar2) is
647 select max(next_value)
648 from per_number_generation_controls png
649 ,hr_organization_information hoi
650 where png.business_group_id = hoi.organization_id
651 and hoi.ORG_INFORMATION_CONTEXT = g_bg_context_name
652 and hoi.Org_information2 = g_automatic_method
653 and png.type = cp_person_type;
654
655 cursor csr_max_apl_num(cp_person_type varchar2) is
656 select max(next_value)
657 from per_number_generation_controls png
658 ,hr_organization_information hoi
659 where png.business_group_id = hoi.organization_id
660 and hoi.ORG_INFORMATION_CONTEXT = g_bg_context_name
661 and hoi.Org_information3 = g_automatic_method
662 and png.type = cp_person_type;
663
664 cursor csr_max_cwk_num(cp_person_type varchar2) is
665 select max(next_value)
666 from per_number_generation_controls png
667 ,hr_organization_information hoi
668 where png.business_group_id = hoi.organization_id
669 and hoi.ORG_INFORMATION_CONTEXT = g_bg_context_name
670 and hoi.Org_information16 = g_automatic_method
671 and png.type = cp_person_type;
672 --
673 BEGIN
674 l_session_date := HR_GENERAL.Effective_Date;
675
676 if Global_person_numbering(p_person_type) then
677 --
678 -- profile option is already set
679 --
680 hr_utility.set_message(800,'PER_289182_GLOBALNUM_SET');
681 l_message := fnd_message.get();
682
683 Write_Log(l_message);
684 retcode := 1; -- raise warning
685 else
686 l_max_number := 1;
687 if g_debug then
688 hr_utility.set_location('Entering: '||c_proc_name,1);
689 end if;
690 -- Lock per_all_people_f to ensure person records are not created/updated/deleted
691 --
692 LOCK TABLE per_all_people_f
693 IN EXCLUSIVE MODE NOWAIT;
694 --
695 -- altering profile option
696 --
697 if p_person_type = 'EMP' then
698 if not fnd_profile.save(g_xbg_EMPNum_profile,'Y','SITE') then
699 RAISE e_ProfileUpdFailed;
700 end if;
701 open csr_max_emp_num(p_person_type);
702 fetch csr_max_emp_num into l_max_number;
703 close csr_max_emp_num;
704
705 elsif p_person_type = 'APL' then
706 if not fnd_profile.save(g_xbg_APLNum_profile,'Y','SITE') then
707 RAISE e_ProfileUpdFailed;
708 end if;
709 open csr_max_apl_num(p_person_type);
710 fetch csr_max_apl_num into l_max_number;
711 close csr_max_apl_num;
712
713 elsif p_person_type = 'CWK' then
714 if not fnd_profile.save(g_xbg_CWKNum_profile,'Y','SITE') then
715 RAISE e_ProfileUpdFailed;
716 end if;
717 open csr_max_cwk_num(p_person_type);
718 fetch csr_max_cwk_num into l_max_number;
719 close csr_max_cwk_num;
720
721 end if; -- person types
722 --
723 -- ------------------------------------------------------------------- +
724 -- Alter the sequence |
725 -- ------------------------------------------------------------------- +
726 -- Sequence is altered based on next_value.
727 ---
728 Set_Global_Sequence(p_person_type, l_max_number);
729 --
730 if Get_PersonNumber_Formula(p_person_type,l_session_date) is not null then
731 --
732 -- custom algorithm is enabled
733 --
734 hr_utility.set_message(800,'PER_449102_NUMGEN_FF_SET');
735 l_message := fnd_message.get();
736
737 Write_Log(l_message);
738 retcode := 1; -- raise warning
739 end if;
740 end if; -- profile option set?
741 if g_debug then
742 hr_utility.set_location('Leaving: '||c_proc_name,35);
743 end if;
744 --
745 EXCEPTION
746 when TIMEOUT_ON_RESOURCE OR e_ResourceBusy then
747 if g_debug then
748 hr_utility.set_location(c_proc_name,29);
749 end if;
750 -- The required resources are used by some other process.
751
752 hr_utility.set_message(800,'PER_289849_RESOURCE_BUSY');
753 hr_utility.raise_error;
754
755 when e_ProfileUpdFailed then
756
757 hr_utility.set_message(800,'PER_289181_SEQUENCE_FAIL'); -- change message
758 hr_utility.raise_error;
759
760 END convert_to_global_sequence;
761 -- ---------------------------------------------------------------------------+
762 -- Global_person_numbering: |
763 -- This function returns TRUE when "global person numbering" profile |
764 -- option for a particular person type is set to "yes". |
765 -- If this profile option is set, then the global sequence is being used |
766 -- to generate automatic person numbers. |
767 -- ---------------------------------------------------------------------------+
768 FUNCTION Global_person_numbering(p_person_type IN varchar2)
769 RETURN BOOLEAN IS
770
771 l_value varchar2(100);
772 l_defined boolean;
773 l_result boolean;
774 --
775 -- FND_PROFILE.get_specific is required to read from the database
776 -- in case Conc. Program that changes profile value already ran.
777 -- The value is cached once the profile value is set to 'Y';
778 -- FND_PROFILE.value is not used because it reads from cache thus existing
779 -- forms sessions will get old values.
780 --
781 BEGIN
782 l_result := FALSE;
783 if p_person_type = 'EMP' then
784 if g_X_EMPprofileset is null then
785 --
786 FND_PROFILE.get_specific(name_z => g_xbg_EMPNum_profile
787 ,val_z => l_value
788 ,defined_z => l_defined);
789 if l_defined and nvl(l_value,'N')= 'Y' then
790 g_X_EMPprofileset := TRUE;
791 l_result := TRUE;
792 end if;
793 else
794 return(g_X_EMPprofileset);
795 end if;
796 --
797 -- Applicants
798 --
799 elsif p_person_type = 'APL' then
800 if g_X_APLprofileset is null then
801 --
802 FND_PROFILE.get_specific(name_z => g_xbg_APLNum_profile
803 ,val_z => l_value
804 ,defined_z => l_defined);
805 if l_defined and nvl(l_value,'N')= 'Y' then
806 g_X_APLprofileset := TRUE;
807 l_result := TRUE;
808 end if;
809 else
810 return(g_X_APLprofileset);
811 end if;
812 --
813 -- Contingent Workers
814 --
815 elsif p_person_type = 'CWK' then
816 if g_X_CWKprofileset is null then
817 --
818 FND_PROFILE.get_specific(name_z => g_xbg_CWKNum_profile
819 ,val_z => l_value
820 ,defined_z => l_defined);
821 if l_defined and nvl(l_value,'N')= 'Y' then
822 g_X_CWKprofileset := TRUE;
823 l_result := TRUE;
824 end if;
825 else
826 return(g_X_CWKprofileset);
827 end if;
828
829 else
830 l_result := FALSE;
831 end if;
832 return(l_result);
833
834 END Global_person_numbering;
835 --
836 -- ---------------------------------------------------------------------------+
837 -- ---------------------------------------------------------------------------+
838 FUNCTION GetGlobalPersonNum(p_person_type IN varchar2)
839 RETURN number IS
840 l_next_value number;
841 c_proc_name varchar2(100) := g_pkg_name||'.GetGlobalPersonNum';
842 BEGIN
843 l_next_value := null;
844 if p_person_type = 'EMP' then
845 select PER_GLOBAL_EMP_NUM_S.NEXTVAL
846 into l_next_value
847 from dual;
848 elsif p_person_type = 'APL' then
849 select PER_GLOBAL_APL_NUM_S.NEXTVAL
850 into l_next_value
851 from dual;
852
853 elsif p_person_type = 'CWK' then
854 select PER_GLOBAL_CWK_NUM_S.NEXTVAL
855 into l_next_value
856 from dual;
857
858 end if;
859 if g_debug then
860 hr_utility.set_location(c_proc_name,20);
861 end if;
862 return (l_next_value);
863 END GetGlobalPersonNum;
864 --
865 -- --------------------------------------------------------------------- +
866 -- Name: Get_PersonNumber_Formula
867 -- Purpose: Retrieves the fast formula id defined for person number
868 -- generation.
869 -- Returns: formula id is successful, null otherwise.
870 -- --------------------------------------------------------------------- +
871 FUNCTION Get_PersonNumber_Formula(p_person_type varchar2
872 ,p_effective_date date)
873 RETURN number IS
874 --
875 l_formula_id number;
876 l_formula_name ff_formulas_f.formula_name%TYPE;
877 e_NoFormula exception;
878 --
879 BEGIN
880 l_formula_id := null;
881 begin
882 if p_person_type = 'EMP' then
883 l_formula_name := 'EMP_NUMBER_GENERATION';
884 elsif p_person_type = 'APL' then
885 l_formula_name := 'APL_NUMBER_GENERATION';
886 elsif p_person_type = 'CWK' then
887 l_formula_name := 'CWK_NUMBER_GENERATION';
888 else
889 raise e_NoFormula;
890 end if;
891 --
892 select ff.formula_id
893 into l_formula_id
897 -- we ignore the Legislation Code
894 from ff_formulas_f ff
895 where ff.formula_name = l_formula_name
896 and ff.business_group_id = 0 -- global FF defined in Setup BG
898 and p_effective_date between ff.effective_start_date and
899 ff.effective_end_date;
900 exception
901
902 when no_data_found or e_NoFormula then
903
904 l_formula_id := null;
905 end;
906 return (l_formula_id);
907 --
908 END Get_PersonNumber_Formula;
909 -- --------------------------------------------------------------------- +
910 -- Name: Execute_Get_Person_Number_FF
911 -- Purpose: Execute fast formula in order to generate next person number.
912 -- Returns: Next person number
913 -- --------------------------------------------------------------------- +
914 FUNCTION EXECUTE_GET_PERSON_NUMBER_FF(
915 p_formula_id number
916 ,p_effective_date date
917 ,p_business_group_id number
918 ,p_person_type varchar2
919 ,p_legislation_code varchar2
920 ,p_person_id number
921 ,p_person_number varchar2
922 ,p_party_id number
923 ,p_date_of_birth date
924 ,p_start_date date
925 ,p_national_id per_all_people_f.national_identifier%TYPE)
926 RETURN VARCHAR2 IS
927
928 l_proc varchar2(100) := g_pkg_name||'.execute_get_person_number_ff';
929 l_inputs ff_exec.inputs_t;
930 l_outputs ff_exec.outputs_t;
931 l_user_message varchar2(1) := 'N';
932 l_invalid_message fnd_new_messages.message_text%TYPE;
933 l_person_number varchar2(30);
934 e_WrongParameters exception;
935 BEGIN
936 hr_utility.set_location('Entering: '||l_proc,1);
937 --
938 -- Initalize the formula.
939 --
940 ff_exec.init_formula
941 (p_formula_id => p_formula_id
942 ,p_effective_date => p_effective_date
943 ,p_inputs => l_inputs
944 ,p_outputs => l_outputs);
945
946 hr_utility.set_location(l_proc, 45);
947
948 --
949 -- Assign the inputs.
950 --
951 for i_input in l_inputs.first..l_inputs.last
952 loop
953
954 if l_inputs(i_input).name = 'BUSINESS_GROUP_ID' then
955 l_inputs(i_input).value := p_business_group_id;
956 elsif l_inputs(i_input).name = 'LEGISLATION_CODE' then
957 l_inputs(i_input).value := p_legislation_code;
958 elsif l_inputs(i_input).name = 'PERSON_TYPE' then
959 l_inputs(i_input).value := p_person_type;
960 elsif l_inputs(i_input).name = 'PERSON_ID' then
961 l_inputs(i_input).value := p_person_id;
962 elsif l_inputs(i_input).name = 'PERSON_NUMBER' then
963 l_inputs(i_input).value := p_person_number;
964 elsif l_inputs(i_input).name = 'PARTY_ID' then
965 l_inputs(i_input).value := p_party_id;
966 elsif l_inputs(i_input).name = 'DATE_OF_BIRTH' then
967 l_inputs(i_input).value := fnd_date.date_to_canonical(p_date_of_birth);
968 elsif l_inputs(i_input).name = 'START_DATE' then
969 l_inputs(i_input).value := fnd_date.date_to_canonical(p_start_date);
970 elsif l_inputs(i_input).name = 'NATIONAL_ID' then
971 l_inputs(i_input).value := p_national_id;
972
973 else
974 hr_utility.trace('2020 name = '||l_inputs(i_input).name);
975 raise e_WrongParameters;
976 end if;
977 end loop;
978 hr_utility.set_location(l_proc,50);
979 --
980 -- Run the formula.
981 --
982 ff_exec.run_formula(l_inputs, l_outputs);
983 --
984 -- Assign the outputs.
985 --
986 for i_output in l_outputs.first..l_outputs.last
987 loop
988 if l_outputs(i_output).name = 'NEXT_NUMBER' then
989
990 if l_outputs(i_output).value = 'FAILED' then
991 l_user_message := 'Y';
992 else
993 l_person_number := l_outputs(i_output).value;
994 end if;
995
996 elsif l_outputs(i_output).name = 'INVALID_MSG' then
997 --
998 -- Here any customer-defined messages are set and
999 -- raised after this loop.
1000 --
1001 l_invalid_message := l_outputs(i_output).value;
1002 else
1003 raise e_WrongParameters;
1004 end if;
1005
1006 end loop;
1007 --
1008 -- If the Fast Formula raises a user-defined error message,
1009 -- raise the error back to the user. Here the message is
1010 -- truncated to 30 characters because that is the limit
1011 -- in the calling program.
1012 --
1013 if l_user_message = 'Y' then
1014 hr_utility.set_message(800, substr(l_invalid_message, 1, 30));
1015 hr_utility.raise_error;
1016 end if;
1017
1018 hr_utility.set_location(l_proc,55);
1019
1020 return l_person_number;
1021
1022 exception
1023
1024 when e_WrongParameters then
1025 --
1026 -- The inputs / outputs of the Fast Formula are incorrect
1027 -- so raise an error.
1028 --
1029 hr_utility.set_message(800,'HR_449943_BAD_FF_DEFINITION');
1030 hr_utility.raise_error;
1031
1032 END EXECUTE_GET_PERSON_NUMBER_FF;
1033 --
1034 --
1035 END PER_BG_NUMBERING_METHOD_PKG;
[Home] [Help]