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