[Home] [Help]
PACKAGE BODY: APPS.HR_EFC_INFO
Source
1 PACKAGE BODY hr_efc_info AS
2 /* $Header: hrefcinf.pkb 120.0 2005/05/31 00:01:00 appldev noship $ */
3 --
4 -- Local Variables
5 --
6 -- Global constants noting number of insert/update scripts in each phase.
7 -- g_update_step_10 CONSTANT NUMBER := 118;
8 -- g_update_step_20 CONSTANT NUMBER := 10;
9 -- g_update_step_30 CONSTANT NUMBER := 26;
10 -- g_update_step_40 CONSTANT NUMBER := 1;
11 -- g_update_step_50 CONSTANT NUMBER := 1;
12
13 -- Changed temporarily for the purpose of testing EFC process driver generation
14 g_update_step_10 CONSTANT NUMBER := 15;
15 g_update_step_20 CONSTANT NUMBER := 2;
16 g_update_step_30 CONSTANT NUMBER := 7;
17 g_update_step_40 CONSTANT NUMBER := 0;
18 g_update_step_50 CONSTANT NUMBER := 1;
19 --
20 -- Global constants noting number of recal scripts in each phase
21 g_recal_step_10 CONSTANT NUMBER := 9;
22 g_recal_step_20 CONSTANT NUMBER := 3;
23 g_recal_step_30 CONSTANT NUMBER := 2;
24 g_recal_step_40 CONSTANT NUMBER := 1;
25 g_recal_step_50 CONSTANT NUMBER := 1;
26 --
27 g_package varchar2(30) := 'hr_efc_info.';
28 g_name VARCHAR2(30);
29 g_bg NUMBER;
30 g_last_currency_code varchar2(15) := null;
31 g_return_currency_code varchar2(15) := null;
32 --
33 -- Cursors
34 -- Cursor to determine payment type and territory code.
35 --
36 CURSOR csr_fetch_payment_types(c_payment_type_id IN number) IS
37 SELECT ppt.payment_type_name
38 , ppt.territory_code
39 , ppt.category
40 FROM pay_payment_types ppt
41 WHERE ppt.payment_type_id = c_payment_type_id;
42 --
43 -- ----------------------------------------------------------------------------
44 -- |------------------------------< get_bg >----------------------------------|
45 -- ----------------------------------------------------------------------------
46 FUNCTION get_bg RETURN NUMBER IS
47 --
48 l_bg NUMBER := 0;
49 l_proc varchar2(72) := g_package || 'get_bg';
50 --
51 -- Cursor to determine business group
52 CURSOR csr_find_bg IS
53 SELECT act.business_group_id
54 FROM hr_efc_actions act
55 WHERE act.efc_action_status = 'P'
56 AND act.efc_action_type = 'C';
57 --
58 BEGIN
59 --
60 OPEN csr_find_bg;
61 FETCH csr_find_bg INTO l_bg;
62 IF csr_find_bg%NOTFOUND THEN
63 -- No current action, so cannot determine business group id
64 CLOSE csr_find_bg;
65 hr_utility.set_message(800,'PER_52701_EFC_UNDEFINED_BG_ERR');
66 hr_utility.raise_error;
67 END IF;
68 CLOSE csr_find_bg;
69 --
70 -- Return business_group_id;
71 RETURN l_bg;
72 END get_bg;
73 --
74 -- ----------------------------------------------------------------------------
75 -- |-----------------------------< get_chunk >--------------------------------|
76 -- ----------------------------------------------------------------------------
77 FUNCTION get_chunk RETURN NUMBER IS
78 --
79 l_chunk NUMBER := 100;
80 l_proc varchar2(72) := g_package || 'get_chunk';
81 --
82 CURSOR c_pap(g_name IN varchar2) IS
83 SELECT pap.parameter_value
84 FROM pay_action_parameters pap
85 WHERE pap.parameter_name = g_name;
86 --
87 l_pap c_pap%ROWTYPE;
88 --
89 BEGIN
90 --
91 OPEN c_pap('EFC_PROCESS_CHUNK_SIZE');
92 FETCH c_pap INTO l_pap;
93 IF c_pap%FOUND THEN
94 l_chunk := to_number(l_pap.parameter_value);
95 END IF;
96 CLOSE c_pap;
97 --
98 --
99 -- Return chunk size.
100 RETURN l_chunk;
101 END get_chunk;
102 --
103 -- ----------------------------------------------------------------------------
104 -- |-----------------------< get_bg_currency >--------------------------------|
105 -- ----------------------------------------------------------------------------
106 FUNCTION get_bg_currency(p_bg NUMBER) RETURN VARCHAR2 IS
107 --
108 l_currency VARCHAR2(150) := NULL;
109 --
110 CURSOR csr_bg(c_bg IN NUMBER) IS
111 SELECT pbg.currency_code
112 FROM per_business_groups pbg
113 WHERE pbg.business_group_id = c_bg;
114 --
115 BEGIN
116 --
117 OPEN csr_bg(p_bg);
118 FETCH csr_bg into l_currency;
119 IF csr_bg%NOTFOUND THEN
120 CLOSE csr_bg;
121 -- No BG currency, so error
122 -- fnd_message.set_name('PER','PER_52702_EFC_BG_CURR_IS_NULL');
123 -- fnd_message.raise_error;
124 -- Changed to raise an exception instead
125 RAISE currency_null;
126 END IF;
127 CLOSE csr_bg;
128 --
129 -- Return currency
130 RETURN l_currency;
131 END get_bg_currency;
132 --
133 -- ----------------------------------------------------------------------------
134 -- |--------------------------< process_table >-------------------------------|
135 -- ----------------------------------------------------------------------------
136 FUNCTION process_table(p_bg NUMBER) RETURN VARCHAR2 IS
137 --
138 l_currency VARCHAR2(150);
139 l_valid VARCHAR2(1) := 'N';
140 l_proc varchar2(72) := g_package || 'process_table';
141 --
142 BEGIN
143 --
144 l_currency := get_bg_currency(p_bg);
145 --
146 -- IF hr_currency_pkg.efc_is_ncu_currency(l_currency) THEN
147 -- l_valid := 'Y';
148 -- END IF;
149 -- The above has been commented out, to allow processing for business
150 -- groups that are based on non-NCU currency codes.
151 --
152 IF (l_currency IS NULL) THEN
153 -- Error will already have been raised if cannot find BG currency.
154 -- fnd_message.set_name('PER','PER_52702_EFC_BG_CURR_IS_NULL');
155 -- fnd_message.raise_error;
156 null;
157 ELSIF hr_currency_pkg.efc_is_ncu_currency(l_currency) THEN
158 l_valid := 'Y';
159 ELSE
160 -- Currency is non-NCU currency (and may be 'EUR')
161 l_valid := 'N';
162 END IF;
163 --
164 --
165 -- Return value
166 RETURN l_valid;
167 END process_table;
168 --
169 -- ----------------------------------------------------------------------------
170 -- |-------------------< validate_currency_code >-----------------------------|
171 -- ----------------------------------------------------------------------------
172 FUNCTION validate_currency_code
173 (p_currency_code in VARCHAR2) RETURN varchar2 IS
174 --
175 BEGIN
176 IF p_currency_code IS NULL THEN
177 return(NULL);
178 END IF;
179 --
180 IF ((p_currency_code <> g_last_currency_code) OR
181 (g_last_currency_code IS NULL)) THEN
182 -- Fetch return currency code
183 IF hr_currency_pkg.efc_is_ncu_currency(p_currency_code) THEN
184 g_return_currency_code := 'EUR';
185 ELSE
186 g_return_currency_code := p_currency_code;
187 END IF;
188 g_last_currency_code := p_currency_code;
189 END IF;
190 --
191 -- Return currency_code
192 RETURN g_return_currency_code;
193 END validate_currency_code;
194 --
195 -- ----------------------------------------------------------------------------
196 -- |---------------------< convert_aei_information >--------------------------|
197 -- ----------------------------------------------------------------------------
198 FUNCTION convert_aei_information
199 (p_value varchar2
200 ,p_currency varchar2
201 ,p_bg number) RETURN varchar2 IS
202 --
203 l_return varchar2(100);
204 --
205 BEGIN
206 --
207 IF p_value IS NULL THEN
208 l_return := NULL;
209 ELSE
210 IF p_currency IS NULL THEN
211 -- Use bg's currency
212 l_return := hr_currency_pkg.efc_convert_varchar2_amount
213 (hr_efc_info.get_bg_currency(p_bg)
214 ,p_value);
215 ELSE
216 l_return := hr_currency_pkg.efc_convert_varchar2_amount
217 (p_currency
218 ,p_value);
219 END IF;
220 END IF;
221 --
222 -- Return value
223 RETURN l_return;
224 END convert_aei_information;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |---------------------< convert_abs_information >--------------------------|
228 -- ----------------------------------------------------------------------------
229 FUNCTION convert_abs_information
230 (p_value varchar2
231 ,p_currency varchar2
232 ,p_bg number) RETURN varchar2 IS
233 --
234 l_return varchar2(100);
235 --
236 BEGIN
237 --
238 IF p_value IS NULL THEN
239 l_return := NULL;
240 ELSE
241 IF p_currency IS NULL THEN
242 -- Use bg's currency
243 l_return := hr_currency_pkg.efc_convert_varchar2_amount
244 (hr_efc_info.get_bg_currency(p_bg)
245 ,p_value);
246 ELSE
247 l_return := hr_currency_pkg.efc_convert_varchar2_amount
248 (p_currency
249 ,p_value);
250 END IF;
251 END IF;
252 --
253 -- Return value
254 RETURN l_return;
255 END convert_abs_information;
256 --
257 -- ----------------------------------------------------------------------------
258 -- |---------------------------< check_abs_currency >-------------------------|
259 -- ----------------------------------------------------------------------------
260 FUNCTION check_abs_currency(p_currency IN varchar2
261 ,p_bg IN number) RETURN varchar2 IS
262 --
263 BEGIN
264 --
265 IF (p_currency IS NULL) THEN
266 RETURN hr_efc_info.get_bg_currency(p_bg);
267 ELSE
268 RETURN p_currency;
269 END IF;
270 END check_abs_currency;
271 --
272 -- ----------------------------------------------------------------------------
273 -- |---------------------------< check_aei_currency >-------------------------|
274 -- ----------------------------------------------------------------------------
275 FUNCTION check_aei_currency(p_currency IN varchar2
276 ,p_bg IN number) RETURN varchar2 IS
277 --
278 BEGIN
279 --
280 IF (p_currency IS NULL) THEN
281 RETURN hr_efc_info.get_bg_currency(p_bg);
282 ELSE
283 RETURN p_currency;
284 END IF;
285 END check_aei_currency;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |------------------------< convert_num_value >-----------------------------|
289 -- ----------------------------------------------------------------------------
290 FUNCTION convert_num_value
291 (p_value IN VARCHAR2
292 ,p_bg IN NUMBER
293 ,p_context1 IN VARCHAR2
294 ,p_context2 IN VARCHAR2) RETURN varchar2 IS
295 --
296 l_return VARCHAR2(100);
297 --
298 BEGIN
299 --
300 IF p_value IS NULL THEN
301 -- Return NULL
302 l_return := NULL;
303 ELSE
304 -- Check whether or not we want to convert
305 IF hr_efc_info.validate_hr_summary
306 (p_context1,p_context2,p_bg) = 'Y' THEN
307 -- Convert according to BG's currency
308 l_return := hr_currency_pkg.efc_convert_varchar2_amount
309 (hr_efc_info.get_bg_currency(p_bg)
310 ,p_value);
311 ELSE
312 -- We dont want to convert, return original value
313 l_return := p_value;
314 END IF;
315 END IF;
316 --
317 -- Return value
318 RETURN l_return;
319 END convert_num_value;
320 --
321 -- ----------------------------------------------------------------------------
322 -- |-------------------------< check_num_currency >---------------------------|
323 -- ----------------------------------------------------------------------------
324 FUNCTION check_num_currency(p_bg IN NUMBER) RETURN varchar2 IS
325 --
326 BEGIN
327 RETURN hr_efc_info.get_bg_currency(p_bg);
328 END check_num_currency;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |--------------------------< convert_ppy_value >---------------------------|
332 -- ----------------------------------------------------------------------------
333 FUNCTION convert_ppy_value
334 (p_value IN number
335 ,p_currency IN varchar2) RETURN number IS
336 --
337 l_return NUMBER;
338 --
339 BEGIN
340 --
341 IF p_value IS NULL THEN
342 l_return := NULL;
343 ELSE
344 IF hr_currency_pkg.efc_is_ncu_currency(p_currency) THEN
345 l_return := hr_currency_pkg.efc_convert_number_amount
346 (p_currency
347 ,p_value);
348 ELSE
349 -- return value unchanged.
350 l_return := p_value;
351 END IF;
352 END IF;
353 --
354 -- Return value
355 RETURN l_return;
356 --
357 END convert_ppy_value;
358 --
359 -- ----------------------------------------------------------------------------
360 -- |------------------------< check_ppy_currency >----------------------------|
361 -- ----------------------------------------------------------------------------
362 FUNCTION check_ppy_currency(p_currency IN varchar2) RETURN varchar2 IS
363 --
364 BEGIN
365 RETURN p_currency;
366 END check_ppy_currency;
367 --
368 -- ----------------------------------------------------------------------------
369 -- |-----------------------< validate_total_workers >-------------------------|
370 -- ----------------------------------------------------------------------------
371 PROCEDURE validate_total_workers(p_action_id IN number
372 ,p_component_name IN varchar2
373 ,p_sub_step IN number
374 ,p_total_workers IN number
375 ,p_step IN varchar2 default 'C_UPDATE'
376 ) IS
377 --
378 -- Cursor to find no. of workers
379 CURSOR csr_check_workers(c_action_id IN number
380 ,c_component_name IN varchar2) IS
381 SELECT epc.total_workers
382 FROM hr_efc_process_components epc
383 WHERE epc.efc_action_id = c_action_id
384 AND epc.process_component_name = c_component_name;
385 --
386 -- Cursor to find sub phases
387 CURSOR csr_check_phases(c_action_id IN number
388 ,c_sub_step IN number
389 ,c_step IN varchar2
390 ) IS
391 SELECT 'Y'
392 FROM hr_efc_process_components epc
393 , hr_efc_workers efw
394 WHERE epc.efc_action_id = c_action_id
395 AND epc.step = c_step
396 AND epc.sub_step < c_sub_step
397 AND efw.efc_process_component_id = epc.efc_process_component_id
398 AND efw.worker_process_status = 'P';
399
400 --
401 -- Cursor to find no. of completed worrkers for a certain sub_step
402 CURSOR csr_check_rows(c_action_id IN number
403 ,c_sub_step IN number
404 ,c_step IN varchar2) IS
405 SELECT count(*)
406 FROM hr_efc_process_components epc
407 , hr_efc_workers efw
408 WHERE epc.efc_action_id = c_action_id
409 AND epc.step = c_step
410 AND epc.sub_step = c_sub_step
411 AND efw.efc_process_component_id = epc.efc_process_component_id
412 AND efw.worker_process_status = 'C';
413 --
414 l_total csr_check_workers%ROWTYPE;
415 l_exists varchar2(1);
416 l_rows number := 0;
417 l_expected number := 0;
418 l_phase number;
419 --
420 BEGIN
421 -- check step parameter
422 IF ((p_step <> 'C_UPDATE') and (p_step <> 'C_RECAL')) THEN
423 -- Incorrect parameter
424 hr_utility.set_message(800,'PER_52703_EFC_INVALID_STEP');
425 hr_utility.raise_error;
426 END IF;
427 --
428 OPEN csr_check_workers(p_action_id, p_component_name);
429 FETCH csr_check_workers INTO l_total;
430 --
431 IF ((csr_check_workers%FOUND) and
432 (l_total.total_workers <> p_total_workers)) THEN
433 -- Row exists, yet workers does not match - error
434 CLOSE csr_check_workers;
435 hr_utility.set_message(800,'PER_52713_EFC_INVALID_WORKERS');
436 hr_utility.raise_error;
437 END IF;
438 -- Close cursor.
439 CLOSE csr_check_workers;
440 --
441 -- Check whether we have the expected no. of complete workers for the
442 -- previous sub_step. If not, check if some workers are still processing.
443 l_phase := p_sub_step - 10;
444 OPEN csr_check_rows(p_action_id, l_phase, p_step);
445 FETCH csr_check_rows INTO l_rows;
446 CLOSE csr_check_rows;
447 --
448 IF (p_step = 'C_UPDATE') THEN
449
450 -- Work out if we have expected number of rows
451 IF p_sub_step = '20' THEN
452 l_expected := g_update_step_10 * p_total_workers;
453 ELSIF p_sub_step = '30' THEN
454 l_expected := g_update_step_20 * p_total_workers;
455 ELSIF p_sub_step = '40' THEN
456 l_expected := g_update_step_30 * p_total_workers;
457 ELSIF p_sub_step = '50' THEN
458 l_expected := g_update_step_40 * p_total_workers;
459 ELSE
460 l_expected := 0;
461 END IF;
462 ELSE
463 -- p_step = 'C_RECAL'
464 IF p_sub_step = '20' THEN
465 l_expected := g_recal_step_10 * p_total_workers;
466 ELSIF p_sub_step = '30' THEN
467 l_expected := g_recal_step_20 * p_total_workers;
468 ELSIF p_sub_step = '40' THEN
469 l_expected := g_recal_step_30 * p_total_workers;
470 ELSIF p_sub_step = '50' THEN
471 l_expected := g_recal_step_40 * p_total_workers;
472 ELSE
473 l_expected := 0;
474 END IF;
475 END IF;
476 --
477 IF (l_expected <> l_rows) THEN
478 -- Check whether any sub-phases exist for this BG that are not complete
479 OPEN csr_check_phases(p_action_id, p_sub_step,p_step);
480 FETCH csr_check_phases INTO l_exists;
481 IF csr_check_phases%FOUND THEN
482 -- sub-phase exists, so error
483 CLOSE csr_check_phases;
484 hr_utility.set_message(800,'PER_52704_EFC_PHASE_RUNNING');
485 hr_utility.raise_error;
486 ELSE
487 -- All rows are complete, perhaps worker entries missing?
488 IF l_expected > l_rows THEN
489 hr_utility.set_message(800,'PER_52714_EFC_HIST_ENTRIES_ERR');
490 hr_utility.raise_error;
491 ELSIF l_expected < l_rows THEN
492 hr_utility.set_message(800,'PER_52705_EFC_INCOMPLETE_HIST');
493 hr_utility.raise_error;
494 ELSE
495 -- Have number of rows expected, no error.
496 null;
497 END IF;
498 END IF;
499 -- Close cursor
500 CLOSE csr_check_phases;
501 END IF;
502 --
503 END validate_total_workers;
504 --
505 -- ----------------------------------------------------------------------------
506 -- |--------------------------< get_action_details >--------------------------|
507 -- ----------------------------------------------------------------------------
508 PROCEDURE get_action_details(p_efc_action_id OUT NOCOPY number
509 ,p_business_group_id OUT NOCOPY number
510 ,p_get_chunk OUT NOCOPY number
511 ) IS
512 --
513 CURSOR csr_fetch_details IS
514 SELECT act.efc_action_id
515 , act.business_group_id
516 FROM hr_efc_actions act
517 WHERE act.efc_action_status = 'P'
518 AND act.efc_action_type = 'C';
519 --
520 BEGIN
521 --
522 -- Fetch details from table.
523 OPEN csr_fetch_details;
524 FETCH csr_fetch_details INTO p_efc_action_id, p_business_group_id;
525 IF csr_fetch_details%ROWCOUNT > 1 THEN
526 -- error, more than one action being processed
527 CLOSE csr_fetch_details;
528 hr_utility.set_message(800,'PER_52715_EFC_MULTIPLE_ACTIONS');
529 hr_utility.raise_error;
530 ELSIF csr_fetch_details%NOTFOUND THEN
531 -- error, no action tro process
532 CLOSE csr_fetch_details;
533 hr_utility.set_message(800,'PER_52721_EFC_NO_CURRNT_ACTION');
534 hr_utility.raise_error;
535 END IF;
536 CLOSE csr_fetch_details;
537 --
538 -- Get chunk size
539 p_get_chunk := hr_efc_info.get_chunk;
540 --
541 END get_action_details;
542 --
543 -- ----------------------------------------------------------------------------
544 -- |---------------------------< insert_line >--------------------------------|
545 -- ----------------------------------------------------------------------------
546 --
547 -- Description:
548 -- Adds the line to the actual database table.
549 --
550 -- ----------------------------------------------------------------------------
551 PROCEDURE insert_line(p_line VARCHAR2
552 ,p_line_num NUMBER default null) IS
553
554 l_line_num number;
555
556 BEGIN
557 --
558 l_line_num := p_line_num;
559 --
560 IF l_line_num IS NULL THEN
561 l_line_num := g_efc_message_line;
562 g_efc_message_line := g_efc_message_line +1;
563 END IF;
564
565 INSERT INTO hr_api_user_hook_reports
566 (session_id,
567 line,
568 text)
569 VALUES
570 (userenv('SESSIONID'),
571 l_line_num,
572 p_line);
573 --
574 END insert_line;
575 --
576 -- ----------------------------------------------------------------------------
577 -- |---------------------------< add_output >---------------------------------|
578 -- ----------------------------------------------------------------------------
579 PROCEDURE add_output(p_param1 IN VARCHAR2
580 ,p_param2 IN VARCHAR2
581 ,p_param3 IN VARCHAR2
582 ,p_param4 IN VARCHAR2
583 ,p_line IN OUT NOCOPY NUMBER) IS
584 --
585 l_line varchar2(80);
586 --
587 BEGIN
588 --
589 l_line := rpad(p_param1,20) || ' ' ||
590 rpad(p_param2,20) || ' ' ||
591 rpad(p_param3,20) || ' ' ||
592 rpad(p_param4,5);
593 --
594 insert_line(l_line, p_line);
595 --
596 p_line := p_line + 1;
597 --
598 END add_output;
599 --
600 -- ----------------------------------------------------------------------------
601 -- |---------------------------< add_header >---------------------------------|
602 -- ----------------------------------------------------------------------------
603 PROCEDURE add_header(p_line IN OUT NOCOPY NUMBER) IS
604 --
605 l_line varchar2(80);
606 l_line_num number;
607 l_bg number(15);
608 l_bg_name varchar2(30);
609 --
610 CURSOR csr_bg_name(p_bg IN NUMBER) IS
611 SELECT pbg.name
612 FROM per_business_groups pbg
613 WHERE pbg.business_group_id = p_bg;
614 --
615 BEGIN
616 --
617 l_bg := hr_efc_info.get_bg;
618 open csr_bg_name(l_bg);
619 fetch csr_bg_name into l_bg_name;
620 close csr_bg_name;
621 --
622 l_line_num := p_line;
623 --
624 l_line := 'Business Group: ' || to_char(l_bg);
625 insert_line(l_line,l_line_num);
626 l_line_num := l_line_num + 1;
627 --
628 l_line := 'Business Group Name: '||l_bg_name;
629 insert_line(l_line,l_line_num);
630 l_line_num := l_line_num + 1;
631 --
632 l_line := rpad(' ',80);
633 insert_line(l_line, l_line_num);
634 l_line_num := l_line_num + 1;
635 --
636 l_line := rpad('RATE NAME',20) || ' ' ||
637 rpad('SALARY BASIS NAME',20) || ' ' ||
638 rpad('ELEMENT TYPE NAME',20) || ' ' ||
639 rpad('CURR',5);
640 insert_line(l_line, l_line_num);
641 l_line_num := l_line_num + 1;
642 --
643 l_line := '----------------------------------------------------------------------';
644 insert_line(l_line, l_line_num);
645 l_line_num := l_line_num + 1;
646 --
647 p_line := l_line_num;
648 END add_header;
649 --
650 -- ----------------------------------------------------------------------------
651 -- |-------------------------< validate_hr_summary >--------------------------|
652 -- ----------------------------------------------------------------------------
653 FUNCTION validate_hr_summary(p_colname VARCHAR2
654 ,p_item VARCHAR2
655 ,p_business_group_id NUMBER) RETURN VARCHAR2 IS
656 --
657 l_process varchar2(1) := 'N';
658 --
659 BEGIN
660 --
661 IF (p_colname = 'NUM_VALUE1') THEN
662 --
663 -- Oracle defined lookups
664 -- Bilan social data
665 IF p_item = 'ANNUAL_REMUNERATION'
666 OR p_item = 'MONTHLY_REMUNERATION'
667 OR p_item = 'DECEMBER_REMUNERATION'
668 OR p_item = 'ANNUAL_NON_MONTHLY_BONUSES'
669 OR p_item = '10_HIGHEST_REMUNERATION'
670 OR p_item = '10_PC_HIGHEST_REMUNERATION'
671 OR p_item = '10_PC_LOWEST_REMUNERATION'
672 OR p_item = 'OUTPUT_BASED_REMUNERATION'
673 OR p_item = 'TIME_BASED_REMUNERATION'
674 OR p_item = 'EMPLOYER_COST'
675 --
676 -- 2483 data
677 OR p_item = 'INTERNAL_EVENT_COSTS'
678 OR p_item = 'SKILLS_ASSESSMENT_COSTS'
679 OR p_item = 'TRAINING_COSTS_EXCL_REP'
680 OR p_item = 'TRAINING_PLAN_COST'
681 --
682 -- Add other Oracle defined Lookups
683 THEN
684 l_process := 'Y';
685 ELSE
686 -- Lookup may be user_defined
687 l_process := hr_efc_stubs.cust_validate_hr_summary
688 (p_colname => p_colname
689 ,p_item => p_item
690 ,p_business_group_id => p_business_group_id);
691 END IF;
692 ELSIF (p_colname = 'NUM_VALUE2') THEN
693 --
694 -- Check Oracle Defined Lookups (as above)
695 -- None.
696 --
697 -- Check User defined lookups
698 l_process := hr_efc_stubs.cust_validate_hr_summary
699 (p_colname => p_colname
700 ,p_item => p_item
701 ,p_business_group_id => p_business_group_id);
702 END IF;
703 --
704 -- Return value.
705 RETURN l_process;
706 --
707 END validate_hr_summary;
708 --
709 -- ----------------------------------------------------------------------------
710 -- |-----------------------< find_payment_map >-------------------------------|
711 -- ----------------------------------------------------------------------------
712 --
713 -- Description:
714 -- This procedure takes a payment_name, and a territory_code, and using these
715 -- will determine the new payment_type_id for the payment_type which maps on
716 -- to the original payment_type.
717 --
718 -- Post Success:
719 -- The procedure returns, as an out parameter, the payment_type_id of the
720 -- new payment type.
721 --
722 -- Post Failure:
723 -- The procedure returns, as an out parameter, a value of NULL for the new
724 -- payment type.
725 --
726 -- ----------------------------------------------------------------------------
727 PROCEDURE find_payment_map(p_payment_type IN VARCHAR2
728 ,p_territory_code IN VARCHAR2
729 ,p_category IN VARCHAR2
730 ,p_payment_type_id OUT NOCOPY NUMBER) IS
731 --
732 l_payment_type varchar2(80);
733 l_territory_code varchar2(2);
734 l_category varchar2(2);
735 l_to_currency varchar2(3);
736 l_changed_id boolean := true;
737 l_vc_const constant varchar2(9) := '$sysdef$';
738 --
739 -- Cursor to check if mapping exists
740 CURSOR csr_check_map_exists(c_payment_type IN VARCHAR2
741 ,c_territory_code IN VARCHAR2) IS
742 SELECT ppt.payment_type_id
743 , ppt.category
744 , ppt.currency_code
745 FROM pay_payment_types ppt
746 WHERE ppt.payment_type_name = c_payment_type
747 AND ppt.territory_code = c_territory_code;
748 --
749 CURSOR csr_check_map_exists_null(c_payment_type IN VARCHAR2) IS
750 SELECT ppt.payment_type_id
751 , ppt.category
752 , ppt.currency_code
753 FROM pay_payment_types ppt
754 WHERE ppt.payment_type_name = c_payment_type
755 AND ppt.territory_code IS NULL;
756 --
757 --
758 BEGIN
759 --
760 --
761 IF ((p_payment_type='FR Cash') AND
762 (p_territory_code='FR')) THEN
763 l_payment_type := 'Cash';
764 l_territory_code := NULL;
765 ELSIF ((p_payment_type = 'FR Cheque') AND
766 (p_territory_code = 'FR')) THEN
767 l_payment_type := 'Cheque';
768 l_territory_code := NULL;
769 ELSIF ((p_payment_type = 'FR Magnetic Tape') AND
770 (p_territory_code = 'FR')) THEN
771 l_payment_type := 'FR Magnetic Tape - EUR';
772 l_territory_code := 'FR';
773 ELSIF ((p_payment_type = 'Cheque - ITL') AND
774 (p_territory_code = 'IT')) THEN
775 l_payment_type := 'Cheque';
776 l_territory_code := NULL;
777 ELSIF ((p_payment_type = 'Direct Deposit - ITL') AND
778 (p_territory_code = 'IT')) THEN
779 l_payment_type := 'Direct Deposit - EUR';
780 l_territory_code := 'IT';
781 ELSIF ((p_payment_type = 'Cheque') AND
782 (p_territory_code = 'BE')) THEN
783 l_payment_type := 'Cheque';
784 l_territory_code := NULL;
785 ELSIF ((p_payment_type = 'Direct Deposit') AND
786 (p_territory_code = 'BE')) THEN
787 l_payment_type := 'Direct Deposit - EUR';
788 l_territory_code := 'BE';
789 ELSIF ((p_payment_type = 'Cash') AND
790 (p_territory_code IS NULL)) THEN
791 l_payment_type := 'Cash';
792 l_territory_code := NULL;
793 ELSIF ((p_payment_type = 'Cheque') AND
794 (p_territory_code IS NULL)) THEN
795 l_payment_type := 'Cheque';
796 l_territory_code := NULL;
797 ELSE -- Deal with customer mappings
798 hr_efc_stubs.chk_customer_mapping
799 (p_payment_type => p_payment_type
800 ,p_territory_code => p_territory_code
801 ,p_new_payment_type => l_payment_type
802 ,p_new_territory_code => l_territory_code
803 );
804 IF ((nvl(l_payment_type, l_vc_const)
805 = nvl(p_payment_type, l_vc_const)) AND
806 (nvl(l_territory_code, l_vc_const)
807 = nvl(p_territory_code, l_vc_const))) THEN
808 l_changed_id := FALSE;
809 END IF;
810 END IF;
811 --
812 IF (l_changed_id) THEN
813 -- Check mapping exists
814 IF l_territory_code IS NOT NULL THEN
815 OPEN csr_check_map_exists(l_payment_type, l_territory_code);
816 --
817 FETCH csr_check_map_exists INTO p_payment_type_id
818 , l_category
819 , l_to_currency;
820 IF csr_check_map_exists%NOTFOUND THEN
821 p_payment_type_id := NULL;
822 --
823 -- Otherwise, id has been inserted into p_payment_type_id.
824 END IF;
825 --
826 CLOSE csr_check_map_exists;
827 --
828 ELSE
829 --
830 OPEN csr_check_map_exists_null(l_payment_type);
831 --
832 FETCH csr_check_map_exists_null INTO p_payment_type_id
833 , l_category
834 , l_to_currency;
835 IF csr_check_map_exists_null%NOTFOUND THEN
836 p_payment_type_id := NULL;
837 --
838 -- Otherwise, id has been inserted into p_payment_type_id.
839 END IF;
840 --
841 CLOSE csr_check_map_exists_null;
842 --
843 END IF;
844 IF (nvl(l_category, l_vc_const) <>
845 nvl(p_category, l_vc_const)) THEN
846 -- Invalid mapping, set payment_type_id to null
847 p_payment_type_id := NULL;
848 END IF;
849 --
850 IF ((l_to_currency IS NOT NULL) AND (l_to_currency <> 'EUR')) THEN
851 -- Invalid mapping, set payment_type_id to null
852 p_payment_type_id := NULL;
853 END IF;
854 ELSE
855 -- payment_type_id has not changed - return null
856 p_payment_type_id := NULL;
857 END IF;
858 END find_payment_map;
859 --
860 --
861 -- ----------------------------------------------------------------------------
862 -- |-----------------------< chk_mapping_exists >-----------------------------|
863 -- ----------------------------------------------------------------------------
864 FUNCTION chk_mapping_exists(p_payment_type_id IN number) RETURN varchar2 IS
865 --
866 l_payment_type_name VARCHAR2(80);
867 l_territory_code VARCHAR2(2);
868 l_payment_type_id NUMBER;
869 l_category VARCHAR2(2);
870 --
871 BEGIN
872 --
873 -- Find payment_type_name, territory_code
874 open csr_fetch_payment_types(p_payment_type_id);
875 --
876 FETCH csr_fetch_payment_types INTO l_payment_type_name,
877 l_territory_code,
878 l_category;
879 --
880 IF csr_fetch_payment_types%NOTFOUND THEN
881 CLOSE csr_fetch_payment_types;
882 RETURN('N');
883 ELSE
884 CLOSE csr_fetch_payment_types;
885 -- Call procedure to return payment_type_id for mapped payment type
886 find_payment_map(p_payment_type => l_payment_type_name
887 ,p_territory_code => l_territory_code
888 ,p_category => l_category
889 ,p_payment_type_id => l_payment_type_id);
890 --
891 IF l_payment_type_id IS NULL THEN
892 RETURN('N');
893 ELSE
894 RETURN('Y');
895 END IF;
896 END IF;
897 --
898 END chk_mapping_exists;
899 --
900 -- ----------------------------------------------------------------------------
901 -- |---------------------------< find_map_id >--------------------------------|
902 -- ----------------------------------------------------------------------------
903 FUNCTION find_map_id(p_payment_type_id IN number) RETURN number IS
904 --
905 l_payment_type_name VARCHAR2(80);
906 l_territory_code VARCHAR2(2);
907 l_payment_type_id NUMBER;
908 l_category VARCHAR2(2);
909 --
910 --
911 BEGIN
912 --
913 -- Find payment_type_name, territory_code
914 OPEN csr_fetch_payment_types(p_payment_type_id);
915 --
916 FETCH csr_fetch_payment_types INTO l_payment_type_name
917 , l_territory_code
918 , l_category;
919 --
920 IF csr_fetch_payment_types%NOTFOUND THEN
921 l_payment_type_id := NULL;
922 ELSE
923 -- Call procedure to return payment_type_id for mapped payment type
924 find_payment_map(p_payment_type => l_payment_type_name
925 ,p_territory_code => l_territory_code
926 ,p_category => l_category
927 ,p_payment_type_id => l_payment_type_id);
928 END IF;
929 CLOSE csr_fetch_payment_types;
930 --
931 IF l_payment_type_id IS NULL THEN
932 hr_utility.set_message(800,'PER_52716_EFC_NO_PAYMENT_MAP');
933 hr_utility.raise_error;
934 END IF;
935 --
936 -- Return value
937 RETURN l_payment_type_id;
938 --
939 END find_map_id;
940 --
941 -- ----------------------------------------------------------------------------
942 -- |-------------------------< check_opm_currency >---------------------------|
943 -- ----------------------------------------------------------------------------
944 FUNCTION check_opm_currency(p_currency IN varchar2) RETURN varchar2 IS
945 BEGIN
946 RETURN p_currency;
947 END check_opm_currency;
948 --
949 -- ----------------------------------------------------------------------------
950 -- |-------------------------< check_pra_currency >---------------------------|
951 -- ----------------------------------------------------------------------------
952 FUNCTION check_pra_currency(p_currency IN varchar2) RETURN varchar2 IS
953 BEGIN
954 RETURN p_currency;
955 END check_pra_currency;
956 --
957 -- ----------------------------------------------------------------------------
958 -- |--------------------< insert_or_select_comp_row >-------------------------|
959 -- ----------------------------------------------------------------------------
960 PROCEDURE insert_or_select_comp_row
961 (p_action_id IN number
962 ,p_process_component_name IN varchar2
963 ,p_table_name IN varchar2
964 ,p_total_workers IN number
965 ,p_worker_id IN number
966 ,p_step IN varchar2
967 ,p_sub_step IN number
968 ,p_process_component_id OUT NOCOPY number) IS
969 --
970 l_lockhandle varchar2(128);
971 l_lock_result number;
972 l_exists varchar2(1);
973 --
974 -- Cursor to fetch existing component_id
975 --
976 CURSOR csr_fetch_comp_id(c_action_id IN number
977 ,c_process_component_name IN varchar2) IS
978 SELECT epc.efc_process_component_id
979 FROM hr_efc_process_components epc
980 WHERE epc.efc_action_id = c_action_id
981 AND epc.process_component_name = c_process_component_name;
982 --
983 BEGIN
984 -- Get lock handle for EFC lock
985 dbms_lock.allocate_unique(lockname => 'HR_EFC_PROCESS_COMPONENTS'
986 ,lockhandle => l_lockhandle);
987 --
988 LOOP
989 -- Attempt to take lock
990 l_lock_result := dbms_lock.request(lockhandle => l_lockhandle
991 ,lockmode => dbms_lock.x_mode
992 ,timeout => dbms_lock.maxwait
993 ,release_on_commit => TRUE
994 );
995 IF ((l_lock_result = 0) OR (l_lock_result = 4)) THEN
996 -- Have lock, exit loop
997 EXIT;
998 ELSIF ((l_lock_result =1) OR (l_lock_result = 2)) THEN
999 -- Lock timed out, or deadlock
1000 dbms_lock.sleep(p_worker_id);
1001 ELSE
1002 -- Parameter error or illegal lock handle, so error
1003 hr_utility.set_message(800,'PER_52717_EFC_PROC_LOCK_ERR');
1004 hr_utility.raise_error;
1005 END IF;
1006 END LOOP;
1007 --
1008 -- Have lock, so determine if row exists
1009 OPEN csr_fetch_comp_id(p_action_id, p_process_component_name);
1010 FETCH csr_fetch_comp_id INTO p_process_component_id;
1011 IF csr_fetch_comp_id%NOTFOUND THEN
1012 -- row does not exist, so insert row into process components table.
1013 --
1014 INSERT INTO hr_efc_process_components
1015 (efc_process_component_id
1016 ,efc_action_id
1017 ,process_component_name
1018 ,table_name
1019 ,total_workers
1020 ,step
1021 ,sub_step
1022 ,last_update_date
1023 ,last_updated_by
1024 ,last_update_login
1025 ,created_by
1026 ,creation_date)
1027 VALUES
1028 (hr_efc_process_components_s.nextval
1029 ,p_action_id
1030 ,p_process_component_name
1031 ,p_table_name
1032 ,p_total_workers
1033 ,p_step
1034 ,p_sub_step
1035 ,sysdate
1036 ,-1
1037 ,-1
1038 ,-1
1039 ,sysdate)
1040 RETURNING efc_process_component_id INTO p_process_component_id;
1041 END IF;
1042 CLOSE csr_fetch_comp_id;
1043 --
1044 -- Commit row, and release lock
1045 COMMIT;
1046 --
1047 --
1048 END insert_or_select_comp_row;
1049 --
1050 -- ----------------------------------------------------------------------------
1051 -- |-------------------< insert_or_select_worker_row >------------------------|
1052 -- ----------------------------------------------------------------------------
1053 PROCEDURE insert_or_select_worker_row
1054 (p_efc_worker_id OUT NOCOPY number
1055 ,p_status IN OUT NOCOPY varchar2
1056 ,p_process_component_id IN number
1057 ,p_process_component_name IN varchar2
1058 ,p_action_id IN number
1059 ,p_worker_number IN number
1060 ,p_pk1 IN OUT NOCOPY number
1061 ,p_pk2 IN OUT NOCOPY varchar2
1062 ,p_pk3 IN OUT NOCOPY varchar2
1063 ,p_pk4 IN OUT NOCOPY varchar2
1064 ,p_pk5 IN OUT NOCOPY varchar2
1065 ) IS
1066 --
1067 -- Cursor to check restart
1068 CURSOR csr_restart(c_action_id IN number
1069 ,c_worker_id IN number
1070 ,c_component IN varchar2) IS
1071 SELECT ewo.efc_worker_id
1072 , ewo.worker_process_status
1073 , ewo.pk1
1074 , ewo.pk2
1075 , ewo.pk3
1076 , ewo.pk4
1077 , ewo.pk5
1078 FROM hr_efc_process_components epc
1079 , hr_efc_workers ewo
1080 WHERE epc.efc_action_id = c_action_id
1081 AND epc.process_component_name = c_component
1082 AND epc.efc_process_component_id = ewo.efc_process_component_id
1083 AND ewo.worker_number = c_worker_id;
1084 --
1085 -- Cursor to determine SPID value
1086 CURSOR csr_fetch_spid IS
1087 SELECT p.spid
1088 FROM v$session s
1089 , v$process p
1090 WHERE s.audsid = userenv('SESSIONID')
1091 AND p.addr = s.paddr;
1092 --
1093 l_restart csr_restart%ROWTYPE;
1094 l_pk1 number := 0;
1095 l_spid varchar2(9);
1096 --
1097 BEGIN
1098 --
1099 IF nvl(p_status,'P') <> 'C' THEN
1100 p_status := 'P';
1101 END IF;
1102 --
1103 -- See if there is a restart row
1104 OPEN csr_restart(p_action_id, p_worker_number, p_process_component_name);
1105 FETCH csr_restart INTO l_restart;
1106 IF csr_restart%NOTFOUND THEN
1107 -- No row in worker table, so create a row.
1108 l_spid := NULL;
1109 -- always log the SPID, note that this does not mean
1110 -- that sql_trace was enabled
1111 -- find spid
1112 OPEN csr_fetch_spid;
1113 FETCH csr_fetch_spid INTO l_spid;
1114 CLOSE csr_fetch_spid;
1115 --
1116 INSERT INTO hr_efc_workers
1117 (efc_worker_id
1118 ,efc_process_component_id
1119 ,efc_action_id
1120 ,worker_number
1121 ,worker_process_status
1122 ,pk1
1123 ,pk2
1124 ,pk3
1125 ,pk4
1126 ,pk5
1127 ,spid
1128 ,last_update_date
1129 ,last_updated_by
1130 ,last_update_login
1131 ,created_by
1132 ,creation_date
1133 )
1134 VALUES
1135 (hr_efc_workers_s.nextval
1136 ,p_process_component_id
1137 ,p_action_id
1138 ,p_worker_number
1139 ,p_status
1140 ,l_pk1
1141 ,p_pk2
1142 ,p_pk3
1143 ,p_pk4
1144 ,p_pk5
1145 ,l_spid
1146 ,sysdate
1147 ,-1
1148 ,-1
1149 ,-1
1150 ,sysdate
1151 )
1152 RETURNING efc_worker_id INTO p_efc_worker_id;
1153 COMMIT;
1154 --
1155 -- Set return values
1156 p_pk1 := l_pk1;
1157 ELSE
1158 -- Row exists in HR_EFC_WORKERS table already, so set return values
1159 -- and also update SPID
1160 IF l_restart.worker_process_status = 'P' THEN
1161 -- Check if SQL_TRACE is on
1162 l_spid := NULL;
1163 -- always log the SPID, note that this does not mean
1164 -- that sql_trace was enabled
1165 OPEN csr_fetch_spid;
1166 FETCH csr_fetch_spid INTO l_spid;
1167 CLOSE csr_fetch_spid;
1168 --
1169 IF l_spid IS NOT NULL THEN
1170 -- update worker row
1171 UPDATE hr_efc_workers
1172 SET spid = l_spid
1173 WHERE efc_worker_id = l_restart.efc_worker_id;
1174 END IF;
1175 END IF;
1176 p_efc_worker_id := l_restart.efc_worker_id;
1177 p_status := l_restart.worker_process_status;
1178 p_pk1 := l_restart.pk1;
1179 p_pk2 := l_restart.pk2;
1180 p_pk3 := l_restart.pk3;
1181 p_pk4 := l_restart.pk4;
1182 p_pk5 := l_restart.pk5;
1183 --
1184 END IF;
1185 CLOSE csr_restart;
1186 --
1187 -- We now definitely have a row in HR_EFC_WORKERS table
1188 --
1189 END insert_or_select_worker_row;
1190 --
1191 -- ----------------------------------------------------------------------------
1192 -- |--------------------------< add_audit_row >-------------------------------|
1193 -- ----------------------------------------------------------------------------
1194 PROCEDURE add_audit_row (p_worker_id IN number
1195 ,p_column_name IN varchar2
1196 ,p_old_value IN varchar2
1197 ,p_new_value IN varchar2
1198 ,p_count IN OUT NOCOPY number
1199 ,p_currency IN varchar2
1200 ,p_last_curr IN OUT NOCOPY varchar2
1201 ,p_commit IN OUT NOCOPY boolean) IS
1202 --
1203 -- Cursor to select old values (if any) from audit table
1204 --
1205 CURSOR csr_find_row(c_worker_id IN number, c_column_name IN varchar2,
1206 c_currency IN varchar2) IS
1207 SELECT ewa.efc_worker_audit_id
1208 , ewa.number_of_rows
1209 FROM hr_efc_worker_audits ewa
1210 WHERE ewa.efc_worker_id = c_worker_id
1211 AND ewa.column_name = c_column_name
1212 AND ewa.currency_code = c_currency;
1213 --
1214 -- Cursor to fetch a unique id
1215 CURSOR csr_get_id IS
1216 SELECT hr_efc_worker_audits_s.nextval
1217 FROM dual;
1218 --
1219 l_audit_id number;
1220 l_rows number;
1221 --
1222 BEGIN
1223 -- Check if value has actually changed
1224 -- When the new and old values are different
1225 -- we can easily detect a conversion has occurred.
1226 -- If the new and old values are both zero
1227 -- then we also need to check if old currency
1228 -- is NCU. This will tell us the "meaning" of the
1229 -- zero has been converted to Euro.
1230 -- Null values which remain null values should not
1231 -- be counted.
1232 IF ((nvl(p_old_value,hr_api.g_varchar2) <>
1233 nvl(p_new_value,hr_api.g_varchar2)) OR
1234 (p_old_value = '0' AND p_new_value = '0'
1235 AND hr_currency_pkg.efc_is_ncu_currency(p_currency))) THEN
1236 -- Value has changed - check if we need to add/update a row
1237 IF ((p_currency = p_last_curr) OR (p_last_curr IS NULL)) THEN
1238 -- Increment count and return
1239 p_count := p_count + 1;
1240 p_last_curr := p_currency;
1241 ELSE
1242 -- Currency has changed, so flush contents to audit table
1243 OPEN csr_find_row(p_worker_id, p_column_name, p_last_curr);
1244 FETCH csr_find_row INTO l_audit_id, l_rows;
1245 IF csr_find_row%NOTFOUND THEN
1246 -- No current row in audit table, so add one
1247 CLOSE csr_find_row;
1248 --
1249 -- Insert row into audit table
1250 INSERT INTO hr_efc_worker_audits
1251 (efc_worker_audit_id
1252 ,efc_worker_id
1253 ,column_name
1254 ,currency_code
1255 ,number_of_rows
1256 ,last_update_date
1257 ,last_updated_by
1258 ,last_update_login
1259 ,created_by
1260 ,creation_date
1261 )
1262 VALUES
1263 (hr_efc_worker_audits_s.nextval
1264 ,p_worker_id
1265 ,p_column_name
1266 ,p_last_curr
1267 ,p_count
1268 ,sysdate
1269 ,-1
1270 ,-1
1271 ,-1
1272 ,sysdate
1273 );
1274 ELSE
1275 CLOSE csr_find_row;
1276 -- Update row in Audit table
1277 UPDATE hr_efc_worker_audits
1278 SET number_of_rows = l_rows + p_count
1279 WHERE efc_worker_audit_id = l_audit_id;
1280 END IF;
1281 -- Changes made to audit table, so indicate this, and reset variables
1282 p_commit := TRUE;
1283 p_count := 1;
1284 p_last_curr := p_currency;
1285 END IF;
1286 END IF;
1287 --
1288 END add_audit_row;
1289 --
1290 -- ----------------------------------------------------------------------------
1291 -- |-------------------------< flush_audit_details >--------------------------|
1292 -- ----------------------------------------------------------------------------
1293 PROCEDURE flush_audit_details
1294 (p_efc_worker_id IN number
1295 ,p_count IN OUT NOCOPY number
1296 ,p_last_curr IN OUT NOCOPY varchar2
1297 ,p_col_name IN varchar2
1298 ) IS
1299 --
1300 -- Cursor to check if row exists
1301 CURSOR csr_find_row(c_worker_id IN number, c_column_name IN varchar2,
1302 c_currency IN varchar2) IS
1303 SELECT ewa.efc_worker_audit_id
1304 , ewa.number_of_rows
1305 FROM hr_efc_worker_audits ewa
1306 WHERE ewa.efc_worker_id = c_worker_id
1307 AND ewa.column_name = c_column_name
1308 AND ewa.currency_code = c_currency;
1309 --
1310 l_audit_id number;
1311 l_rows number;
1312 --
1313 BEGIN
1314 --
1315 -- Only flush if we have to (ie. count is > 0)
1316 IF p_count > 0 THEN
1317 OPEN csr_find_row(p_efc_worker_id, p_col_name, p_last_curr);
1318 FETCH csr_find_row INTO l_audit_id, l_rows;
1319 IF csr_find_row%NOTFOUND THEN
1320 -- No current row in audit table, so add one
1321 CLOSE csr_find_row;
1322 --
1323 -- Insert row into audit table
1324 INSERT INTO hr_efc_worker_audits
1325 (efc_worker_audit_id
1326 ,efc_worker_id
1327 ,column_name
1328 ,currency_code
1329 ,number_of_rows
1330 ,last_update_date
1331 ,last_updated_by
1332 ,last_update_login
1333 ,created_by
1334 ,creation_date
1335 )
1336 VALUES
1337 (hr_efc_worker_audits_s.nextval
1338 ,p_efc_worker_id
1339 ,p_col_name
1340 ,p_last_curr
1341 ,p_count
1342 ,sysdate
1343 ,-1
1344 ,-1
1345 ,-1
1346 ,sysdate
1347 );
1348 ELSE
1349 CLOSE csr_find_row;
1350 -- Update row in Audit table
1351 UPDATE hr_efc_worker_audits
1352 SET number_of_rows = l_rows + p_count
1353 WHERE efc_worker_audit_id = l_audit_id;
1354 END IF;
1355 -- Changes made to audit table, so indicate this, and reset variables
1356 p_count := 0;
1357 p_last_curr := '';
1358 --
1359 END IF;
1360 END flush_audit_details;
1361 --
1362 -- ----------------------------------------------------------------------------
1363 -- |---------------------------< update_worker_row >--------------------------|
1364 -- ----------------------------------------------------------------------------
1365 PROCEDURE update_worker_row(p_efc_worker_id IN number
1366 ,p_pk1 IN number
1367 ,p_pk2 IN varchar2
1368 ,p_pk3 IN varchar2
1369 ,p_pk4 IN varchar2
1370 ,p_pk5 IN varchar2
1371 ) IS
1372 BEGIN
1373 --
1374 -- Update the worker row
1375 UPDATE hr_efc_workers
1376 SET pk1 = p_pk1
1377 , pk2 = p_pk2
1378 , pk3 = p_pk3
1379 , pk4 = p_pk4
1380 , pk5 = p_pk5
1381 WHERE efc_worker_id = p_efc_worker_id;
1382 --
1383 END update_worker_row;
1384 --
1385 -- ----------------------------------------------------------------------------
1386 -- |-------------------------< complete_worker_row >--------------------------|
1387 -- ----------------------------------------------------------------------------
1388 PROCEDURE complete_worker_row(p_efc_worker_id IN number
1389 ,p_pk1 IN number
1390 ,p_pk2 IN varchar2
1391 ,p_pk3 IN varchar2
1392 ,p_pk4 IN varchar2
1393 ,p_pk5 IN varchar2
1394 ) IS
1395 BEGIN
1396 --
1397 UPDATE hr_efc_workers
1398 SET pk1 = p_pk1
1399 , pk2 = p_pk2
1400 , pk3 = p_pk3
1401 , pk4 = p_pk4
1402 , pk5 = p_pk5
1403 , worker_process_status = 'C'
1404 WHERE efc_worker_id = p_efc_worker_id;
1405 --
1406 END complete_worker_row;
1407 --
1408 -- ----------------------------------------------------------------------------
1409 -- |------------------------< valid_budget_unit >-----------------------------|
1410 -- ----------------------------------------------------------------------------
1411 FUNCTION valid_budget_unit(p_uom IN VARCHAR2
1412 ,p_business_group_id IN NUMBER) RETURN VARCHAR2 IS
1413 --
1414 lc_process varchar2(1) := 'N';
1415 --
1416 BEGIN
1417 --
1418 -- example shown, remove if not required
1419 IF p_uom = 'MONEY' THEN
1420 lc_process := 'Y';
1421 -- ELSIF -- Code other allowed types here
1422 ELSE
1423 -- Check for customer specific units
1424 lc_process := hr_efc_stubs.cust_valid_budget_unit(p_uom
1425 ,p_business_group_id);
1426 END IF;
1427 --
1428 RETURN lc_process;
1429 --
1430 END valid_budget_unit;
1431 --
1432 -- ----------------------------------------------------------------------------
1433 -- |----------------------< delete_action_history >---------------------------|
1434 -- ----------------------------------------------------------------------------
1435 PROCEDURE delete_action_history IS
1436 --
1437 l_del_tab_sql varchar2(2000) :=
1438 'BEGIN
1439 LOOP
1440 DELETE FROM <TABLE> efc
1441 WHERE efc.efc_action_id = <ID>
1442 AND ROWNUM < <CHUNK>
1443 COMMIT;
1444 EXIT WHEN SQL%NOTFOUND;
1445 END LOOP;
1446 END;';
1447 --
1448 -- Cursor to determine efc_action_id
1449 CURSOR csr_get_action IS
1450 SELECT act.matching_efc_action_id
1451 FROM hr_efc_actions act
1452 WHERE act.efc_action_type = 'D'
1453 AND act.efc_action_status = 'P';
1454 --
1455 -- Cursor to find _efc tables
1456 CURSOR csr_get_efc_tables IS
1457 SELECT distinct tab.table_name
1458 FROM all_tables tab
1459 , all_tab_columns col
1460 , user_synonyms syn
1461 WHERE ((tab.table_name like '%_EFC'
1462 AND tab.table_name <> 'PAY_BALANCE_TYPES_EFC'
1463 AND tab.table_name <> 'PAY_ORG_PAYMENT_METHODS_F_EFC'
1464 AND hr_general.hrms_object(tab.table_name) = 'TRUE')
1465 OR tab.table_name = 'HR_EFC_ROUNDING_ERRORS')
1466 AND col.table_name = tab.table_name
1467 AND col.column_name = 'EFC_ACTION_ID'
1468 AND tab.table_name = syn.synonym_name
1469 AND tab.owner = syn.table_owner
1470 AND col.owner = tab.owner;
1471 --
1472 l_action_id number;
1473 l_cursor integer;
1474 l_return integer;
1475 l_sql varchar2(2000);
1476 l_chunk number;
1477 --
1478 BEGIN
1479 --
1480 -- get efc_action_id for current delete action.
1481 OPEN csr_get_action;
1482 FETCH csr_get_action INTO l_action_id;
1483 IF csr_get_action%NOTFOUND THEN
1484 --
1485 CLOSE csr_get_action;
1486 hr_utility.set_message(800,'PER_52718_EFC_NO_DELETE_ACTION');
1487 hr_utility.raise_error;
1488 --
1489 END IF;
1490 CLOSE csr_get_action;
1491 --
1492 -- Get chunk size, so we process in chunks
1493 l_chunk := hr_efc_info.get_chunk;
1494 l_del_tab_sql := replace(l_del_tab_sql,'<CHUNK>',l_chunk);
1495 --
1496 -- For each _efc table, delete the appropriate rows
1497 FOR c1 IN csr_get_efc_tables LOOP
1498 --
1499 -- Replace tokens in SQL
1500 l_sql := replace(l_del_tab_sql,'<TABLE>',c1.table_name);
1501 l_sql := replace(l_sql,'<ID>',l_action_id);
1502 --
1503 -- Execute sql
1504 l_cursor := dbms_sql.open_cursor;
1505 dbms_sql.parse(l_cursor, l_sql, DBMS_SQL.v7);
1506 l_return := dbms_sql.execute(l_cursor);
1507 dbms_sql.close_cursor(l_cursor);
1508 --
1509 END LOOP;
1510 --
1511 END delete_action_history;
1512 --
1513 -- ----------------------------------------------------------------------------
1514 -- |----------------------< insert_rounding_row >-----------------------------|
1515 -- ----------------------------------------------------------------------------
1516 PROCEDURE insert_rounding_row
1517 (p_action_id IN number
1518 ,p_source_id IN number
1519 ,p_source_table IN varchar2
1520 ,p_source_column IN varchar2
1521 ,p_rounding_amount IN number) IS
1522 --
1523 --
1524 BEGIN
1525 --
1526 --
1527 INSERT INTO hr_efc_rounding_errors
1528 (efc_rounding_error_id
1529 ,efc_action_id
1530 ,source_id
1531 ,source_table
1532 ,source_column
1533 ,rounding_amount
1534 ,last_update_date
1535 ,last_updated_by
1536 ,last_update_login
1537 ,created_by
1538 ,creation_date)
1539 VALUES
1540 (hr_efc_rounding_errors_s.nextval
1541 ,p_action_id
1542 ,p_source_id
1543 ,p_source_table
1544 ,p_source_column
1545 ,p_rounding_amount
1546 ,sysdate
1547 ,-1
1548 ,-1
1549 ,-1
1550 ,sysdate);
1551 --
1552 -- Commit row
1553 COMMIT;
1554 --
1555 --
1556 END insert_rounding_row;
1557 --
1558 -- ----------------------------------------------------------------------------
1559 -- |-------------------------< find_row_size >--------------------------------|
1560 -- ----------------------------------------------------------------------------
1561 --
1562 -- Description:
1563 -- Determines the size of a row, for a given table, by looking at the column
1564 -- definitions for that table in ALL_TAB_COLUMNS.
1565 -- Criteria for estimation are:
1566 -- - If the column is VARCHAR2, and a currency column, size is 3 bytes
1567 -- - If the column is VARCHAR2, size is (length of column)/3 bytes.
1568 -- - If the column type is NUMBER, size is (length of column)/2 bytes.
1569 --
1570 -- ----------------------------------------------------------------------------
1571 FUNCTION find_row_size(p_table IN VARCHAR2) return NUMBER IS
1572 --
1573 -- Cursor to find table details
1574 CURSOR csr_find_details(c_name IN varchar2) IS
1575 SELECT tab.column_name,
1576 tab.data_type,
1577 tab.data_length
1578 FROM all_tab_columns tab
1579 , user_synonyms syn
1580 WHERE tab.table_name = c_name
1581 AND tab.table_name = syn.synonym_name
1582 AND tab.owner = syn.table_owner;
1583 --
1584 l_table varchar2(30);
1585 l_rowsize number := 0;
1586 --
1587 BEGIN
1588 --
1589 l_table := upper(p_table);
1590 -- Check if customer version returns a value
1591 l_rowsize := hr_efc_stubs.cust_find_row_size(l_table);
1592 IF l_rowsize = 0 THEN
1593 --
1594 -- Customer version returns nothing, so work from our version
1595 FOR c1 IN csr_find_details(l_table) LOOP
1596 IF instr(c1.column_name, 'CURRENCY') <> 0 THEN
1597 -- Currency column
1598 l_rowsize := l_rowsize + 3;
1599 ELSE
1600 IF c1.data_type = 'VARCHAR2' THEN
1601 l_rowsize := l_rowsize + (c1.data_length/3);
1602 ELSIF c1.data_type = 'NUMBER' THEN
1603 l_rowsize := l_rowsize + (c1.data_length/2);
1604 ELSE
1605 l_rowsize := l_rowsize + c1.data_length;
1606 END IF;
1607 END IF;
1608 END LOOP;
1609 END IF;
1610 --
1611 -- Return rowsize
1612 RETURN l_rowsize;
1613 END find_row_size;
1614 --
1615 -- ----------------------------------------------------------------------------
1616 -- |--------------------------< clear_efc_report >----------------------------|
1617 -- ----------------------------------------------------------------------------
1618 --
1619 -- Description:
1620 -- This procedure is a cover for hr_api_user_hooks_utility.clear_hook_report.
1621 --
1622 -- ----------------------------------------------------------------------------
1623 PROCEDURE clear_efc_report IS
1624 --
1625 BEGIN
1626 --
1627
1628 -- clear table of messages
1629 hr_api_user_hooks_utility.clear_hook_report;
1630
1631 IF g_efc_error_message IS NOT NULL THEN
1632 hr_utility.set_message(g_efc_error_app,
1633 g_efc_error_message);
1634 g_efc_error_message := null;
1635 hr_utility.raise_error;
1636 END IF;
1637
1638 --
1639 END clear_efc_report;
1640 --
1641 --
1642 -- ----------------------------------------------------------------------------
1643 -- |---------------------< process_cross_bg_data >----------------------------|
1644 -- ----------------------------------------------------------------------------
1645 --
1646 -- Description:
1647 -- Determines if data spanning business groups will be converted.
1648 -- This will can be overriden by the function cust_process_cross_bg_data.
1649 -- By default, the data will be converted, unless overridden.
1650 --
1651 -- ----------------------------------------------------------------------------
1652 FUNCTION process_cross_bg_data RETURN varchar2 IS
1653 --
1654 BEGIN
1655 --
1656 IF NVL(hr_efc_stubs.cust_process_cross_bg_data, 'Y') = 'N' THEN
1657 RETURN 'N';
1658 ELSE
1659 RETURN 'Y';
1660 END IF;
1661 --
1662 END process_cross_bg_data;
1663 --
1664 --
1665 END hr_efc_info;