1 PACKAGE BODY HR_BALANCE_FEEDS as
2 /* $Header: pybalfed.pkb 120.5 2006/08/10 13:33:59 alogue noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 hr_balance_feeds
11 Purpose
12
13 This package supports the maintenance of balance feeds either by
14 generating them according to system events ie. adding a balance
15 classification or providing utilities to allow the manual creation of
16 balance feeds. A balance feed is an intersection between a balance and an
17 input value and the following basic rules must be met :
18
19 1. Legislation / Business group must match (see table below).
20 2. The units of the balance must match thst of the input value and the
21 output currency of the input value must match the balances currency if
22 monetary units are involved.
23 3. There are other specific rules which affect the eligibility for
24 creating balance feeds ie. adding a Pay Value, adding a Sub
25 Classification Rule, etc...
26
27 NB. all system generated balance feeds must match on the Pay Value
28 input value.
29
30 The following table lists the combinations of business group /
31 startup data that will match to create balance feeds subject to the
32 other conditions being met ie. units etc...
33
34 Input Value | Balance Type | Balance Feed
35 Bg Id Leg Code | Bg Id Leg Code | Bg Id Leg Code
36 --------------------------------------------------------
37 1 | 1 | 1
38 1 | GB | 1
39 GB | 1 | 1
40 GB | GB | GB
41 1 | 2 | NO MATCH
42 --------------------------------------------------------
43
44 where Bus Grp 1 and Bus Grp 2 have a legislation of GB.
45
46 Notes
47
48 History
49 01-Mar-94 J.S.Hobbs 40.0 Date created.
50 15-Mar-94 J.S.Hobbs 40.1 Corrected the setting of legislation code
51 and business group id for balance feeds.
52 15-Mar-94 J.S.Hobbs 40.2 Added to group by clause in ins_bf_bal_class
53 to allow the change 40.1 to work !
54 02-Jun-94 J.S.Hobbs 40.3 Fixed G844. The matching of input values to
55 balance types now works correctly when
56 there is a difference in ownership eg.
57 legislation input value and user balance
58 etc... Also corrected the setting of
59 business group / legislation on balance feeds
60 created due to sub classification rules.
61 19-Aug-94 J.S.Hobbs 40.6 Fixed G1243 Corrected problem with creating
62 balance feeds that span business groups.
63 25-Aug-94 J.S.Hobbs 40.7 Fixed G1268 Corrected problem with cursor
64 csr_bal_feed in bf_chk_proc_run_results
65 where it was failing to find the balance
66 feed..
67 23-Nov-94 rfine 40.8 Suppressed index on business_group_id
68 16-Jul-95 D.Kerr 40.9 Modified the manual_bal_feeds_exist
69 function to support initial balance feeds.
70 01-Mar-99 J. Moyano 115.1 MLS changes. Added references to _TL tables.
71 10-Feb-00 A.Logue 115.3 Utf8 support : pay_input_values_f.name
72 extended to 80 characters.
73 14-Feb-01 M.Reid 115.4 Rewrote csr_proc_run_result due to CBO
74 choosing non-optimal plan
75 23-Mar-01 J.Tomkins 115.5 Bug. 1366796. Added Error 72033 regarding
76 delete next change for element type with
77 input values. Amended del_bf_input_value to
78 include this message.
79 11-Jun-01 M.Reid 115.6 Bug 1783351. Removed suppression of BG id
80 for CBO to allow the view to be merged.
81 30-JUL-02 RThirlby 115.7 Bug 2430399 Added p_mode parameter to
82 ins_bf_bal_class, so can be called from
83 hr_legislation, and not raise an error in
84 ins_bal_feed, if the feed already exists.
85 05-AUG-2002 RThirlby 115.8 Removed development debug statements.
86 31-Oct-02 A.Logue 115.9 Performance fix in cursor
87 csr_bf_upd_del_sub_class_rule. Bug 2649208.
88 10-Dec-02 A.Logue 115.10 Performance fix to cursor csr_bal_feed
89 in bf_chk_proc_run_results. Bug 2668076.
90 29-JAN-03 RThirlby 115.11 Bug 2767760 - Issues with creation of feeds
91 due to translated pay value input value name
92 being used, instead of the base table name.
93 Search for this bug no. for further details.
94 NB - also fixed a compilation error caused
95 by a change in version 115.10.
96 14-APR-2003 RThirlby 115.12 Bug 2888183. Added p_mode parameter to
97 ins_bf_sub_class_rule and ins_bf_pay_value,
98 so they can be called from
99 hr_legislation_elements, and not raise an
100 error in ins_bal_feed, if the feed already
101 exists.
102 24-FEB-2005 M.Reid 115.13 Bug 4187885. Added no unnest hint as 10g
103 workaround for ST bug 3120429
104 22-NOV-2005 A.Logue 115.14 Rewrote csr_proc_run_result due to CBO
105 choosing non-optimal plan
106 29-DEC-2005 A.Logue 115.15 Rewrote csr_proc_run_result due to CBO
107 choosing non-optimal plan. Bug 4914604.
108 17-JAN-2006 A.Logue 115.16 Reimplemented csr_proc_run_result for
109 balance feed creation for performance.
110 Bug 4958471.
111 15-FEB-2006 A.Logue 115.17 Further performance enhancments to
112 feed insertion code.
113 Bug 5040393.
114 10-AUG-2006 A.Logue 115.18 Disable changed balance value check in
115 bf_chk_proc_run_results for new balance feeds
116 if CHANGED_BALANCE_VALUE_CHECK
117 pay_action_parameter set to N. Bug 5442547.
118 ============================================================================*/
119 --
120 ------------------------------------------------------------------------------
121 -- NAME --
122 -- hr_balance_feeds.lock_balance_type --
123 -- --
124 -- DESCRIPTION --
125 -- Takes a row level lock out on a specified balance type. --
126 ------------------------------------------------------------------------------
127 --
128 procedure lock_balance_type
129 (
130 p_balance_type_id number
131 ) is
132 --
133 cursor csr_lock_balance
134 (
135 p_balance_type_id number
136 ) is
137 select bt.balance_type_id
138 from pay_balance_types bt
139 where bt.balance_type_id = p_balance_type_id
140 for update;
141 --
142 v_balance_type_id number;
143 --
144 begin
145 --
146 -- Lock the balance type. This is used by balance feed code to ensure that
147 -- the balance feeds being manipulated cannot be changed by another process
148 -- ie. all balance feed code requires an exclusive lock on the relevent
149 -- balance type before processing can start.
150 open csr_lock_balance(p_balance_type_id);
151 fetch csr_lock_balance into v_balance_type_id;
152 if csr_lock_balance%notfound then
153 close csr_lock_balance;
154 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
155 hr_utility.set_message_token('PROCEDURE',
156 'hr_balance_feeds.lock_balance_type');
157 hr_utility.set_message_token('STEP','1');
158 hr_utility.raise_error;
159 else
160 close csr_lock_balance;
161 end if;
162 --
163 end lock_balance_type;
164 --
165 ------------------------------------------------------------------------------
166 -- NAME --
167 -- hr_balance_feeds.bal_classifications_exist --
168 -- --
169 -- DESCRIPTION --
170 -- Returns TRUE if a balance classification exists. --
171 ------------------------------------------------------------------------------
172 --
173 function bal_classifications_exist
174 (
175 p_balance_type_id number
176 ) return boolean is
177 --
178 cursor csr_classifications_exist is
179 select bcl.classification_id
180 from pay_balance_classifications bcl
181 where bcl.balance_type_id = p_balance_type_id;
182 --
183 v_classification_id number;
184 --
185 begin
186 --
187 open csr_classifications_exist;
188 fetch csr_classifications_exist into v_classification_id;
189 if csr_classifications_exist%found then
190 close csr_classifications_exist;
191 return (TRUE);
192 else
193 close csr_classifications_exist;
194 return (FALSE);
195 end if;
196 --
197 end bal_classifications_exist;
198 --
199 ------------------------------------------------------------------------------
200 -- NAME --
201 -- hr_balance_feeds.manual_bal_feeds_exist --
202 -- --
203 -- DESCRIPTION --
204 -- Returns TRUE if a manual balance feed exists. --
205 -- A balance type has a manual balance feed if it has balance feeds whose --
206 -- associated element classification is not a balance initialization one --
207 -- and if the balance type has no associated balance classifications --
208 ------------------------------------------------------------------------------
209 --
210 function manual_bal_feeds_exist
211 (
212 p_balance_type_id number
213 ) return boolean is
214 --
215 cursor csr_manual_feeds_exist is
216 select bf.balance_feed_id
217 from pay_balance_feeds_f bf,
218 pay_input_values_f inv,
219 pay_element_types_f elt,
220 pay_element_classifications ec
221 where bf.balance_type_id = p_balance_type_id
222 and bf.input_value_id = inv.input_value_id
223 and inv.element_type_id = elt.element_type_id
224 and elt.classification_id = ec.classification_id
225 and nvl(ec.balance_initialization_flag,'N') = 'N'
226 and not exists
227 (select null
228 from pay_balance_classifications bc
229 where bc.balance_type_id = bf.balance_type_id);
230 --
231 v_bal_feed_id number;
232 --
233 begin
234 --
235 open csr_manual_feeds_exist;
236 fetch csr_manual_feeds_exist into v_bal_feed_id;
237 if csr_manual_feeds_exist%found then
238 close csr_manual_feeds_exist;
239 return (TRUE);
240 else
241 close csr_manual_feeds_exist;
242 return (FALSE);
243 end if;
244 --
245 end manual_bal_feeds_exist;
246 --
247 ------------------------------------------------------------------------------
248 -- NAME --
249 -- hr_balance_feeds.pay_value_name --
250 -- --
251 -- DESCRIPTION --
252 -- Returns the translated name for the 'Pay Value'. --
253 ------------------------------------------------------------------------------
254 --
255 function pay_value_name return varchar2 is
256 --
257 v_pay_value_name varchar2(80);
258 --
259 begin
260 --
261 begin
262 select hl.meaning
263 into v_pay_value_name
264 from hr_lookups hl
265 where hl.lookup_type = 'NAME_TRANSLATIONS'
266 and hl.lookup_code = 'PAY VALUE';
267 exception
268 when no_data_found then
269 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
270 hr_utility.set_message_token('PROCEDURE',
271 'hr_balance_feeds.pay_value_name');
272 hr_utility.set_message_token('STEP','1');
273 hr_utility.raise_error;
274 end;
275 --
276 return v_pay_value_name;
277 --
278 end pay_value_name;
279 --
280 ------------------------------------------------------------------------------
281 -- NAME --
282 -- hr_balance_feeds.is_pay_value --
283 -- --
284 -- DESCRIPTION --
285 -- Returns TRUE if input value is a Pay Value. --
286 ------------------------------------------------------------------------------
287 --
288 function is_pay_value
289 (
290 p_input_value_id number
291 ) return boolean is
292 --
293 cursor csr_pay_value
294 (
295 p_input_value_id number,
296 p_pay_value_name varchar2
297 ) is
298 select iv.input_value_id
299 from pay_input_values_f iv
300 where iv.input_value_id = p_input_value_id
301 and iv.name = p_pay_value_name;
302 --
303 v_input_value_id number;
304 v_pay_value_name varchar2(80);
305 --
306 begin
307 --
308 -- Get translated name for pay value
309 -- v_pay_value_name := hr_balance_feeds.pay_value_name;
310 -- Bug 2767760 - search for this bug number for full explanation of these
311 -- changes.
312 -- Set variable to base table pay value input value name
313 v_pay_value_name := 'Pay Value';
314 --
315 open csr_pay_value
316 (p_input_value_id,
317 v_pay_value_name);
318 fetch csr_pay_value into v_input_value_id;
319 if csr_pay_value%found then
320 close csr_pay_value;
321 return (TRUE);
322 else
323 close csr_pay_value;
324 return (FALSE);
325 end if;
326 --
327 end is_pay_value;
328 --
329 ------------------------------------------------------------------------------
330 -- NAME --
331 -- hr_balance_feeds.is_primary_class --
332 -- --
333 -- DESCRIPTION --
334 -- Returns if the classification is primary / sub classification. --
335 ------------------------------------------------------------------------------
336 --
337 function is_primary_class
338 (
339 p_classification_id number
340 ) return boolean is
341 --
342 v_parent_classification_id number;
343 --
344 begin
345 --
346 -- Check to see if classification is primary or secondary.
347 begin
348 select ecl.parent_classification_id
349 into v_parent_classification_id
350 from pay_element_classifications ecl
351 where ecl.classification_id = p_classification_id;
352 exception
353 when no_data_found then
354 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
355 hr_utility.set_message_token('PROCEDURE',
356 'hr_balance_feeds.is_primary_class');
357 hr_utility.set_message_token('STEP','1');
358 hr_utility.raise_error;
359 end;
360 --
361 if v_parent_classification_id is null then
362 return (TRUE);
363 else
364 return (FALSE);
365 end if;
366 --
367 end is_primary_class;
368 --
369 ------------------------------------------------------------------------------
370 -- NAME --
371 -- hr_balance_feeds.bal_feed_end_date --
372 -- --
373 -- DESCRIPTION --
374 -- Returns the correct end date for a balance feed. It takes into account --
375 -- the end date of the input value and also any future balance feeds. --
376 ------------------------------------------------------------------------------
377 --
378 function bal_feed_end_date
379 (
380 p_balance_feed_id number,
381 p_balance_type_id number,
382 p_input_value_id number,
383 p_session_date date,
384 p_validation_start_date date
385 ) return date is
386 --
387 v_next_bal_feed_start_date date;
388 v_max_inp_val_end_date date;
389 v_bal_feed_end_date date;
390 --
391 begin
392 --
393 -- Make sure that no balance classifications exist when creating a manual
394 -- balance feed
395 if hr_balance_feeds.bal_classifications_exist(p_balance_type_id) then
396 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
397 hr_utility.set_message_token('PROCEDURE',
398 'hr_balance_feeds.bal_feed_end_date');
399 hr_utility.set_message_token('STEP','1');
400 hr_utility.raise_error;
401 end if;
402 --
403 -- Get the start date of the earliest future balance feed if it exists.
404 begin
405 select min(bf.effective_start_date)
406 into v_next_bal_feed_start_date
407 from pay_balance_feeds_f bf
408 where bf.balance_type_id = p_balance_type_id
409 and bf.input_value_id = p_input_value_id
410 and bf.effective_end_date >= p_session_date
411 and bf.balance_feed_id <> nvl(p_balance_feed_id,0);
412 exception
413 when no_data_found then
414 null;
415 end;
416 --
417 -- If there are no future balance feeds , get the max end date of the
418 -- input value.
419 if v_next_bal_feed_start_date is null then
420 begin
421 select max(iv.effective_end_date)
422 into v_max_inp_val_end_date
423 from pay_input_values_f iv
424 where iv.input_value_id = p_input_value_id;
425 exception
426 when no_data_found then
427 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
428 hr_utility.set_message_token('PROCEDURE',
429 'hr_balance_feeds.bal_feed_end_date');
430 hr_utility.set_message_token('STEP','2');
431 hr_utility.raise_error;
432 end;
433 v_bal_feed_end_date := v_max_inp_val_end_date;
434 else
435 v_bal_feed_end_date := v_next_bal_feed_start_date - 1;
436 end if;
437 --
438 -- Trying to open up a balance feed that would either overlap with an
439 -- existing balance feed or extend beyond the lifetime of the input value
440 -- on which it is based.
441 if v_bal_feed_end_date < p_validation_start_date then
442 if v_next_bal_feed_start_date is null then
443 -- Trying to extend the end date of the balance feed past the end date
444 -- of the input value.
445 hr_utility.set_message(801, 'HR_7048_BAL_FEED_PAST_INP_VAL');
446 else
447 -- Trying to extend the end date of the balance feed such that it will
448 -- overlap with existing balance feeds.
449 hr_utility.set_message(801, 'HR_7047_BAL_FEED_FUT_EXIST');
450 end if;
451 hr_utility.raise_error;
452 end if;
453 --
454 return v_bal_feed_end_date;
455 --
456 end bal_feed_end_date;
457 --
458 ------------------------------------------------------------------------------
459 -- NAME --
460 -- hr_balance_feeds.ins_bal_feed --
461 -- --
462 -- DESCRIPTION --
463 -- Creates a balance feed.
464 -- Parameter p_mode added so the procedure can be called from the form or --
465 -- from the startup data deliver mechanism (hr_legislation, pelegins.pkb). --
466 -- In FORM mode the procedure will not change. In 'startup' mode, if a feed --
467 -- already exists, then the error will not be raised, and the code will --
468 -- continue its loop for creating balance feeds.
469 ------------------------------------------------------------------------------
470 --
471 procedure ins_bal_feed
472 (
473 p_effective_start_date date,
474 p_effective_end_date date,
475 p_business_group_id number,
476 p_legislation_code varchar2,
477 p_balance_type_id number,
478 p_input_value_id number,
479 p_scale number,
480 p_legislation_subgroup varchar2,
481 p_mode varchar2 default 'FORM'
482 ) is
483 --
484 begin
485 --
486 -- Create a balance feed making sure that a balance feed does not already
487 -- exist.
488 insert into pay_balance_feeds_f
489 (balance_feed_id,
490 effective_start_date,
491 effective_end_date,
492 business_group_id,
493 legislation_code,
494 balance_type_id,
495 input_value_id,
496 scale,
497 legislation_subgroup,
498 last_update_date,
499 last_updated_by,
500 last_update_login,
501 created_by,
502 creation_date)
503 select
504 pay_balance_feeds_s.nextval,
505 p_effective_start_date,
506 p_effective_end_date,
507 p_business_group_id,
508 p_legislation_code,
509 p_balance_type_id,
510 p_input_value_id,
511 p_scale,
512 p_legislation_subgroup,
513 trunc(sysdate),
514 0,
515 0,
516 0,
517 trunc(sysdate)
518 from sys.dual
519 where not exists
520 (select null
521 from pay_balance_feeds_f bf
522 where bf.input_value_id = p_input_value_id
523 and bf.balance_type_id = p_balance_type_id
524 and p_effective_start_date <= bf.effective_end_date
525 and p_effective_end_date >= bf.effective_start_date);
526 --
527 -- Check to see if a balance feed was created. If not then an existing
528 -- balance feed overlapped with the one being created.
529 if sql%rowcount = 0 then
530 if p_mode = 'FORM' then
531 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
532 hr_utility.set_message_token('PROCEDURE',
533 'hr_balance_feeds.ins_bal_feed');
534 hr_utility.set_message_token('STEP','1');
535 hr_utility.raise_error;
536 elsif p_mode = 'STARTUP' then
537 hr_utility.set_location('hr_balance_feeds.ins_bal_feed', 10);
538 else -- p_mode is something other than FORM or Startup - error
539 hr_utility.set_location('hr_balance_feeds.ins_bal_feed', 20);
540 end if;
541 end if;
542 --
543 end ins_bal_feed;
544 --
545 ------------------------------------------------------------------------------
546 -- NAME --
547 -- hr_balance_feeds.ins_bf_bal_class --
548 -- --
549 -- DESCRIPTION --
550 -- Creates balance feeds when a balance classification has been added. --
551 ------------------------------------------------------------------------------
552 --
553 procedure ins_bf_bal_class
554 (
555 p_balance_type_id number,
556 p_balance_classification_id number,
557 p_mode varchar2 default 'FORM'
558 ) is
559 --
560 --
561 -- Finds all balance feeds that should be created as the direct result of
562 -- adding a classification to a balance NB the first part of the UNION
563 -- deals with primary classifications and the second paret deals with
564 -- secondary classifications ie. they are mutually exclusive.
565 --
566 cursor csr_pay_value_bal_class
567 (
568 p_balance_classification_id number,
569 p_pay_value_name varchar2
570 ) is
571 select bt.balance_type_id,
572 iv.input_value_id,
573 bc.scale,
574 min(iv.effective_start_date) effective_start_date,
575 max(iv.effective_end_date) effective_end_date,
576 nvl(iv.business_group_id,bt.business_group_id) business_group_id,
577 decode(nvl(iv.business_group_id,bt.business_group_id),
578 null, nvl(iv.legislation_code,bt.legislation_code),
579 null) legislation_code
580 ,bt.balance_name
581 ,ec.classification_name
582 ,et.element_name
583 from pay_input_values_f iv,
584 pay_element_types_f et,
585 pay_element_classifications ec,
586 pay_balance_classifications bc,
587 pay_balance_types bt,
588 per_business_groups_perf ivbg,
589 per_business_groups_perf btbg
590 where bc.balance_classification_id = p_balance_classification_id
591 and ec.classification_id = bc.classification_id
592 and ec.parent_classification_id is null
593 and bt.balance_type_id = bc.balance_type_id
594 and et.classification_id = ec.classification_id
595 and iv.element_type_id = et.element_type_id
596 and iv.name = p_pay_value_name
597 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
598 and (bt.balance_uom <> 'M' or
599 (bt.balance_uom = 'M' and
600 bt.currency_code = et.output_currency_code))
601 and iv.effective_start_date between et.effective_start_date
602 and et.effective_end_date
603 /*
604 Join are to get the legislation code for the business groups of the
605 balance and input value being matched.
606 */
607 and iv.business_group_id = ivbg.business_group_id (+)
608 and bt.business_group_id = btbg.business_group_id (+)
609 /*
610 Match on business group OR
611 Business groups do not match so try to match on legislation NB.
612 need to protect against the case where the business groups are
613 different but share the same legislation code.
614 */
615 and (
616 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
617 (
618 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
619 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
620 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
621 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
622 )
623 )
624 group by bt.balance_type_id,
625 iv.input_value_id,
626 bc.scale,
627 nvl(iv.business_group_id,bt.business_group_id),
628 decode(nvl(iv.business_group_id,bt.business_group_id),
629 null, nvl(iv.legislation_code,bt.legislation_code),
630 null)
631 ,bt.balance_name
632 ,ec.classification_name
633 ,et.element_name
634 union
635 select bt.balance_type_id,
636 iv.input_value_id,
637 bc.scale,
638 scr.effective_start_date,
639 scr.effective_end_date,
640 nvl(iv.business_group_id,
641 nvl(scr.business_group_id,
642 bt.business_group_id)) business_group_id,
643 decode(nvl(iv.business_group_id,
644 nvl(scr.business_group_id,
645 bt.business_group_id)),
646 null, nvl(iv.legislation_code,
647 nvl(scr.legislation_code,
648 bt.legislation_code)),
649 null) legislation_code
650 ,bt.balance_name
651 ,ec.classification_name
652 ,et.element_name
653 from pay_sub_classification_rules_f scr,
654 pay_element_types_f et,
655 pay_input_values_f iv,
656 pay_element_classifications ec,
657 pay_balance_classifications bc,
658 pay_balance_types bt,
659 per_business_groups_perf ivbg,
660 per_business_groups_perf btbg
661 where bc.balance_classification_id = p_balance_classification_id
662 and ec.classification_id = bc.classification_id
663 and ec.parent_classification_id is not null
664 and bt.balance_type_id = bc.balance_type_id
665 and scr.classification_id = ec.classification_id
666 and et.element_type_id = scr.element_type_id
667 and iv.element_type_id = et.element_type_id
668 and iv.name = p_pay_value_name
669 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
670 and (bt.balance_uom <> 'M' or
671 (bt.balance_uom = 'M' and
672 bt.currency_code = et.output_currency_code))
673 and scr.effective_start_date between et.effective_start_date
674 and et.effective_end_date
675 and scr.effective_start_date between iv.effective_start_date
676 and iv.effective_end_date
677 /*
678 Join are to get the legislation code for the business groups of the
679 balance and input value being matched.
680 */
681 and iv.business_group_id = ivbg.business_group_id (+)
682 and bt.business_group_id = btbg.business_group_id (+)
683 /*
684 Match on business group OR
685 Business groups do not match so try to match on legislation NB.
686 need to protect against the case where the business groups are
687 different but share the same legislation code.
688 */
689 and (
690 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
691 (
692 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
693 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
694 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
695 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
696 )
697 );
698 --
699 v_pay_value_name varchar2(80);
700 --
701 begin
702 --
703 -- Lock balance type to ensure balance feeds are consistent.
704 hr_balance_feeds.lock_balance_type
705 (p_balance_type_id);
706 --
707 -- Make sure that no manual balance feeds exist when creating a balance
708 -- classification.
709 if hr_balance_feeds.manual_bal_feeds_exist(p_balance_type_id) then
710 --
711 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
712 hr_utility.set_message_token('PROCEDURE',
713 'hr_balance_feeds.ins_bf_bal_class');
714 hr_utility.set_message_token('STEP','1');
715 hr_utility.raise_error;
716 --
717 end if;
718 --
719 -- Get translated name for the pay value.
720 -- v_pay_value_name := hr_balance_feeds.pay_value_name;
721 --
722 -- Bug 2767760 - search for this bug number for full explanation of these
723 -- changes.
724 -- Set variable to base table pay value input value name
725 v_pay_value_name := 'Pay Value';
726 --
727 for v_iv_rec in csr_pay_value_bal_class
728 (p_balance_classification_id,
729 v_pay_value_name) loop
730 --
731 hr_utility.trace('bt: '||v_iv_rec.balance_name);
732 hr_utility.trace('clas: '||v_iv_rec.classification_name);
733 hr_utility.trace('et: '||v_iv_rec.element_name);
734 -- Create balance feed.
735 hr_balance_feeds.ins_bal_feed
736 (v_iv_rec.effective_start_date,
737 v_iv_rec.effective_end_date,
738 v_iv_rec.business_group_id,
739 v_iv_rec.legislation_code,
740 v_iv_rec.balance_type_id,
741 v_iv_rec.input_value_id,
742 v_iv_rec.scale,
743 null,
744 p_mode);
745 --
746 end loop;
747 --
748 end ins_bf_bal_class;
749 --
750 ------------------------------------------------------------------------------
751 -- NAME --
752 -- hr_balance_feeds.upd_del_bf_bal_class --
753 -- --
754 -- DESCRIPTION --
755 -- When updating or deleting a balance classification cascade to linked --
756 -- balance feeds NB. the parameter p_mode is used to specify which ie. --
757 -- 'UPDATE' or 'DELETE'. --
758 ------------------------------------------------------------------------------
759 --
760 procedure upd_del_bf_bal_class
761 (
762 p_mode varchar2,
763 p_balance_classification_id number,
764 p_scale number
765 ) is
766 --
767 --
768 -- Find all balance feeds that are linked to the balance classification.
769 --
770 cursor csr_bal_feeds_bal_class
771 (
772 p_balance_classification_id number
773 ) is
774 select bf.rowid row_id
775 from pay_balance_feeds_f bf,
776 pay_balance_classifications bc,
777 pay_balance_types bt,
778 pay_element_classifications ec
779 where bc.balance_classification_id = p_balance_classification_id
780 and bt.balance_type_id = bc.balance_type_id
781 and bf.balance_type_id = bt.balance_type_id
782 and ec.classification_id = bc.classification_id
783 and ((ec.parent_classification_id is null and
784 exists
785 (select null
786 from pay_element_types_f et,
787 pay_input_values_f iv
788 where iv.input_value_id = bf.input_value_id
789 and et.element_type_id = iv.element_type_id
790 and et.classification_id = bc.classification_id))
791 or (ec.parent_classification_id is not null and
792 exists
793 (select null
794 from pay_sub_classification_rules_f scr,
795 pay_input_values_f iv
796 where iv.input_value_id = bf.input_value_id
797 and scr.element_type_id = iv.element_type_id
798 and scr.classification_id = bc.classification_id)))
799 for update;
800 --
801 begin
802 --
803 -- Find all affected balance feeds.
804 for v_bf_rec in csr_bal_feeds_bal_class(p_balance_classification_id) loop
805 --
806 if p_mode = 'UPDATE' then
807 --
808 update pay_balance_feeds_f bf
809 set bf.scale = p_scale
810 where bf.rowid = v_bf_rec.row_id;
811 --
812 elsif p_mode = 'DELETE' then
813 --
814 delete from pay_balance_feeds_f bf
815 where bf.rowid = v_bf_rec.row_id;
816 --
817 else
818 --
819 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
820 hr_utility.set_message_token('PROCEDURE',
821 'hr_balance_feeds.upd_del_bf_bal_class');
822 hr_utility.set_message_token('STEP','1');
823 hr_utility.raise_error;
824 --
825 end if;
826 --
827 end loop;
828 --
829 end upd_del_bf_bal_class;
830 --
831 ------------------------------------------------------------------------------
832 -- NAME --
833 -- hr_balance_feeds.ins_bf_pay_value --
834 -- --
835 -- DESCRIPTION --
836 -- Creates balance feeds when a pay value is created. --
837 ------------------------------------------------------------------------------
838 --
839 procedure ins_bf_pay_value
840 (
841 p_input_value_id number
842 ,p_mode varchar2 default 'FORM'
843 ) is
844 --
845 --
846 -- Finds all balance feeds that should be created as a direct result of
847 -- creating a pay value NB. only searches for balance classifications that
848 -- match the primary classification of the element type.
849 --
850 cursor csr_bal_types_prim_class
851 (
852 p_input_value_id number
853 ) is
854 select bt.balance_type_id,
855 bc.scale,
856 iv.effective_start_date,
857 iv.effective_end_date,
858 nvl(iv.business_group_id,bt.business_group_id) business_group_id,
859 decode(nvl(iv.business_group_id,bt.business_group_id),
860 null, nvl(iv.legislation_code,bt.legislation_code),
861 null) legislation_code
862 from pay_balance_types bt,
863 pay_balance_classifications bc,
864 pay_element_types_f et,
865 pay_input_values_f iv,
866 per_business_groups_perf ivbg,
867 per_business_groups_perf btbg
868 where iv.input_value_id = p_input_value_id
869 and et.element_type_id = iv.element_type_id
870 and bc.classification_id = et.classification_id
871 and bt.balance_type_id = bc.balance_type_id
872 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
873 and (bt.balance_uom <> 'M' or
874 (bt.balance_uom = 'M' and
875 bt.currency_code = et.output_currency_code))
876 and iv.effective_start_date between et.effective_start_date
877 and et.effective_end_date
878 /*
879 Join are to get the legislation code for the business groups of the
880 balance and input value being matched.
881 */
882 and iv.business_group_id = ivbg.business_group_id (+)
883 and bt.business_group_id = btbg.business_group_id (+)
884 /*
885 Match on business group OR
886 Business groups do not match so try to match on legislation NB.
887 need to protect against the case where the business groups are
888 different but share the same legislation code.
889 */
890 and (
891 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
892 (
893 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
894 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
895 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
896 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
897 )
898 )
899 for update of bt.balance_type_id;
900 --
901 --
902 -- Finds all balance feeds that should be created as a direct result of
903 -- creating a pay value NB. only searches for balance classifications that
904 -- match any sub classification of the element type.
905 --
906 cursor csr_bal_types_sub_class
907 (
908 p_input_value_id number
909 ) is
910 select bt.balance_type_id,
911 bc.scale,
912 scr.effective_start_date,
913 scr.effective_end_date,
914 nvl(iv.business_group_id,
915 nvl(scr.business_group_id,
916 bt.business_group_id)) business_group_id,
917 decode(nvl(iv.business_group_id,
918 nvl(scr.business_group_id,
919 bt.business_group_id)),
920 null, nvl(iv.legislation_code,
921 nvl(scr.legislation_code,
922 bt.legislation_code)),
923 null) legislation_code
924 from pay_input_values_f iv,
925 pay_balance_classifications bc,
926 pay_balance_types bt,
927 pay_element_types_f et,
928 pay_sub_classification_rules_f scr,
929 per_business_groups_perf ivbg,
930 per_business_groups_perf btbg
931 where iv.input_value_id = p_input_value_id
932 and et.element_type_id = iv.element_type_id
933 and scr.element_type_id = et.element_type_id
934 and bc.classification_id = scr.classification_id
935 and bt.balance_type_id = bc.balance_type_id
936 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
937 and (bt.balance_uom <> 'M' or
938 (bt.balance_uom = 'M' and
939 bt.currency_code = et.output_currency_code))
940 and iv.effective_start_date between et.effective_start_date
941 and et.effective_end_date
942 /*
943 Join are to get the legislation code for the business groups of the
944 balance and input value being matched.
945 */
946 and iv.business_group_id = ivbg.business_group_id (+)
947 and bt.business_group_id = btbg.business_group_id (+)
948 /*
949 Match on business group OR
950 Business groups do not match so try to match on legislation NB.
951 need to protect against the case where the business groups are
952 different but share the same legislation code.
953 */
954 and (
955 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
956 (
957 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
958 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
959 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
960 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
961 )
962 )
963 for update of bt.balance_type_id;
964
965 begin
966 --
967 -- Create balance feeds for balance types that has a balance classification
968 -- that matches that of the element type.
969 for v_bt_rec in csr_bal_types_prim_class
970 (p_input_value_id) loop
971 --
972 -- Create balance feed.
973 hr_balance_feeds.ins_bal_feed
974 (v_bt_rec.effective_start_date,
975 v_bt_rec.effective_end_date,
976 v_bt_rec.business_group_id,
977 v_bt_rec.legislation_code,
978 v_bt_rec.balance_type_id,
979 p_input_value_id,
980 v_bt_rec.scale,
981 null,
982 p_mode);
983 --
984 end loop;
985 --
986 -- Create balance feeds for balance types that have a balance classification
987 -- that matches sub classification rules for the element type.
988 for v_bt_rec in csr_bal_types_sub_class
989 (p_input_value_id) loop
990 --
991 -- Create balance feed.
992 hr_balance_feeds.ins_bal_feed
993 (v_bt_rec.effective_start_date,
994 v_bt_rec.effective_end_date,
995 v_bt_rec.business_group_id,
996 v_bt_rec.legislation_code,
997 v_bt_rec.balance_type_id,
998 p_input_value_id,
999 v_bt_rec.scale,
1000 null,
1001 p_mode);
1002 --
1003 end loop;
1004 --
1005 end ins_bf_pay_value;
1006 --
1007 ------------------------------------------------------------------------------
1008 -- NAME --
1009 -- hr_balance_feeds.ins_bf_sub_class_rule --
1010 -- --
1011 -- DESCRIPTION --
1012 -- Creates automatic balance feeds when a sub classification rule is added. --
1013 ------------------------------------------------------------------------------
1014 --
1015 procedure ins_bf_sub_class_rule
1016 (
1017 p_sub_classification_rule_id number
1018 ,p_mode varchar2 default 'FORM'
1019 ) is
1020 --
1021 --
1022 -- Finds balance feeds that should be created as a direct result of
1023 -- creating a sub classification rule ie. find any balance classifications
1024 -- that match.
1025 --
1026 cursor csr_pay_value_sub_class_rule
1027 (
1028 p_sub_classification_rule_id number,
1029 p_pay_value_name varchar2
1030 ) is
1031 select bt.balance_type_id,
1032 iv.input_value_id,
1033 bc.scale,
1034 scr.effective_start_date,
1035 scr.effective_end_date,
1036 nvl(iv.business_group_id,
1037 nvl(scr.business_group_id,
1038 bt.business_group_id)) business_group_id,
1039 decode(nvl(iv.business_group_id,
1040 nvl(scr.business_group_id,
1041 bt.business_group_id)),
1042 null, nvl(iv.legislation_code,
1043 nvl(scr.legislation_code,
1044 bt.legislation_code)),
1045 null) legislation_code
1046 from pay_sub_classification_rules_f scr,
1047 pay_element_types_f et,
1048 pay_input_values_f iv,
1049 pay_balance_classifications bc,
1050 pay_balance_types bt,
1051 per_business_groups_perf ivbg,
1052 per_business_groups_perf btbg
1053 where scr.sub_classification_rule_id = p_sub_classification_rule_id
1054 and bc.classification_id = scr.classification_id
1055 and bt.balance_type_id = bc.balance_type_id
1056 and et.element_type_id = scr.element_type_id
1057 and iv.element_type_id = et.element_type_id
1058 and iv.name = p_pay_value_name
1059 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
1060 and (bt.balance_uom <> 'M' or
1061 (bt.balance_uom = 'M' and
1062 bt.currency_code = et.output_currency_code))
1063 and scr.effective_start_date between et.effective_start_date
1064 and et.effective_end_date
1065 and scr.effective_start_date between iv.effective_start_date
1066 and iv.effective_end_date
1067 /*
1068 Join are to get the legislation code for the business groups of the
1069 balance and input value being matched.
1070 */
1071 and iv.business_group_id = ivbg.business_group_id (+)
1072 and bt.business_group_id = btbg.business_group_id (+)
1073 /*
1074 Match on business group OR
1075 Business groups do not match so try to match on legislation NB.
1076 need to protect against the case where the business groups are
1077 different but share the same legislation code.
1078 */
1079 and (
1080 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
1081 (
1082 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
1083 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
1084 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
1085 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
1086 )
1087 )
1088 for update of bt.balance_type_id;
1089 --
1090 v_pay_value_name varchar2(80);
1091 --
1092 begin
1093 --
1094 -- Get translated name for the pay value.
1095 -- v_pay_value_name := hr_balance_feeds.pay_value_name;
1096 --
1097 -- Bug 2767760 - variable is set to the base table value for iv name. We must
1098 -- use base table values rather than translation table names, because if an
1099 -- input_value is created in a French instance, the translated name for all
1100 -- installed languages will be the original French name.
1101 -- If a change is then made in a US instance, the function pay_value_name
1102 -- will return the seeded lookup for pay value input value name as 'Pay Value'.
1103 -- When this value is passed in as the translated iv name, no rows will be
1104 -- returned, as the translated input value name will be the French name
1105 -- 'Valeur salaire'. Thus all cursors in this package that use the translated
1106 -- iv name have been changed to search for the base table name, and the
1107 -- variable for the pay value input value name will also be that of the base
1108 -- table - 'Pay Value'.
1109 --
1110 -- get the base table name for the pay value
1111 v_pay_value_name := 'Pay Value';
1112 --
1113 for v_bt_rec in csr_pay_value_sub_class_rule
1114 (p_sub_classification_rule_id,
1115 v_pay_value_name) loop
1116 --
1117 -- Create balance feed.
1118 hr_balance_feeds.ins_bal_feed
1119 (v_bt_rec.effective_start_date,
1120 v_bt_rec.effective_end_date,
1121 v_bt_rec.business_group_id,
1122 v_bt_rec.legislation_code,
1123 v_bt_rec.balance_type_id,
1124 v_bt_rec.input_value_id,
1125 v_bt_rec.scale,
1126 null,
1127 p_mode);
1128 --
1129 end loop;
1130 --
1131 end ins_bf_sub_class_rule;
1132 --
1133 ------------------------------------------------------------------------------
1134 -- NAME --
1135 -- hr_balance_feeds.del_bf_input_value --
1136 -- --
1137 -- DESCRIPTION --
1138 -- Adjusts or removes balance feeds when an input value is deleted NB. --
1139 -- when shortening an input value all related balance feeds are shortened. --
1140 -- When extending a balance feed then only automatic balance feeds are --
1141 -- extended. --
1142 ------------------------------------------------------------------------------
1143 --
1144 procedure del_bf_input_value
1145 (
1146 p_input_value_id number,
1147 p_dt_mode varchar2,
1148 p_validation_start_date date,
1149 p_validation_end_date date
1150 ) is
1151 --
1152 c_eot constant date := to_date('31/12/4712','DD/MM/YYYY');
1153 --
1154 -- Find all balance feeds for an input value.
1155 cursor csr_bal_feeds_zap
1156 (
1157 p_input_value_id number
1158 ) is
1159 select bf.rowid row_id
1160 from pay_balance_feeds_f bf,
1161 pay_balance_types bt
1162 where bf.input_value_id = p_input_value_id
1163 and bt.balance_type_id = bf.balance_type_id
1164 for update;
1165 --
1166 -- Find all balance feeds for an input value that start after a specified
1167 -- date.
1168 cursor csr_bal_feeds_delete
1169 (
1170 p_input_value_id number,
1171 p_validation_start_date date
1172 ) is
1173 select bf.rowid row_id
1174 from pay_balance_feeds_f bf,
1175 pay_balance_types bt
1176 where bf.input_value_id = p_input_value_id
1177 and bf.effective_start_date >= p_validation_start_date
1178 and bt.balance_type_id = bf.balance_type_id
1179 for update;
1180 --
1181 -- Find all balance feeds for an input value that straddles a specified
1182 -- date.
1183 cursor csr_bal_feeds_update
1184 (
1185 p_input_value_id number,
1186 p_validation_start_date date
1187 ) is
1188 select bf.rowid row_id
1189 from pay_balance_feeds_f bf,
1190 pay_balance_types bt
1191 where bf.input_value_id = p_input_value_id
1192 and bf.effective_end_date >= p_validation_start_date
1193 and bt.balance_type_id = bf.balance_type_id
1194 for update;
1195 --
1196 -- Find the latest balance feed records for all balance feeds for an input
1197 -- value NB. it only selects balance feeds which were automatically
1198 -- created.
1199 cursor csr_bal_feeds_extend
1200 (
1201 p_input_value_id number
1202 ) is
1203 select bf.rowid row_id
1204 from pay_balance_feeds_f bf,
1205 pay_balance_types bt
1206 where bf.input_value_id = p_input_value_id
1207 and bt.balance_type_id = bf.balance_type_id
1208 and bf.effective_end_date =
1209 (select max(bf2.effective_end_date)
1210 from pay_balance_feeds_f bf2
1211 where bf2.balance_feed_id = bf.balance_feed_id)
1212 and exists
1213 (select null
1214 from pay_balance_classifications bc
1215 where bc.balance_type_id = bf.balance_type_id)
1216 for update;
1217 --
1218 begin
1219 --
1220 -- Input value is being removed so all balance feeds for the input value
1221 -- have to be removed.
1222 if p_dt_mode = 'ZAP' then
1223 --
1224 for v_bf_rec in csr_bal_feeds_zap
1225 (p_input_value_id) loop
1226 --
1227 delete from pay_balance_feeds_f bf
1228 where bf.rowid = v_bf_rec.row_id;
1229 --
1230 end loop;
1231 --
1232 -- Input value is being shortened so all balance feeds for the input value
1233 -- that would exist past the new end date of the input value have to be
1234 -- shortened. All balance feeds that exist after the new end date have to
1235 -- be removed.
1236 elsif p_dt_mode = 'DELETE' then
1237 --
1238 for v_bf_rec in csr_bal_feeds_delete
1239 (p_input_value_id,
1240 p_validation_start_date) loop
1241 --
1242 delete from pay_balance_feeds_f bf
1243 where bf.rowid = v_bf_rec.row_id;
1244 --
1245 end loop;
1246 --
1247 for v_bf_rec in csr_bal_feeds_update
1248 (p_input_value_id,
1249 p_validation_start_date) loop
1250 --
1251 update pay_balance_feeds_f bf
1252 set bf.effective_end_date = p_validation_start_date - 1
1253 where bf.rowid = v_bf_rec.row_id;
1254 --
1255 end loop;
1256 --
1257 -- Input value is being extended so all automatic balance feeds that were
1258 -- set according to the end date of the input value will have to be
1259 -- extended NB. manual balance feeds are not extended.
1260 elsif (p_dt_mode = 'DELETE_NEXT_CHANGE' and
1261 p_validation_end_date = c_eot)
1262 or p_dt_mode = 'FUTURE_CHANGE' then
1263 --
1264 -- See if input value being extended is the Pay Value NB. automatic
1265 -- balance feeds are only created for the Pay Value. Extend the balance
1266 -- feed to the new end date of the input value.
1267 if hr_balance_feeds.is_pay_value(p_input_value_id) then
1268 --
1269 for v_bf_rec in csr_bal_feeds_extend
1270 (p_input_value_id) loop
1271 --
1272 update pay_balance_feeds_f bf
1273 set bf.effective_end_date = p_validation_end_date
1274 where bf.rowid = v_bf_rec.row_id;
1275 --
1276 end loop;
1277 --
1278 end if;
1279 --
1280 else
1281 --
1282 if p_dt_mode = 'DELETE_NEXT_CHANGE' then
1283 hr_utility.set_message(801,'HR_72033_CANNOT_DNC_RECORD');
1284 hr_utility.raise_error;
1285 else
1286 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1287 hr_utility.set_message_token('PROCEDURE','hr_balance_feeds.del_bf_input_value');
1288 hr_utility.set_message_token('STEP','1');
1289 hr_utility.raise_error;
1290 end if;
1291 --
1292 end if;
1293 --
1294 end del_bf_input_value;
1295 --
1296 ------------------------------------------------------------------------------
1297 -- NAME --
1298 -- hr_balance_feeds.del_bf_sub_class_rule --
1299 -- --
1300 -- DESCRIPTION --
1301 -- Adjusts or removes balance feeds when a sub classification rule is --
1302 -- deleted NB. this only affects automatic balance feeds. --
1303 ------------------------------------------------------------------------------
1304 --
1305 procedure del_bf_sub_class_rule
1306 (
1307 p_sub_classification_rule_id number,
1308 p_dt_mode varchar2,
1309 p_validation_start_date date,
1310 p_validation_end_date date
1311 ) is
1312 --
1313 --
1314 -- Find all balance feeds that were created as a direct result of adding
1315 -- the sub classification rule.
1316 --
1317 cursor csr_bal_feeds_sub_class_rule
1318 (
1319 p_sub_classification_rule_id number,
1320 p_pay_value_name varchar2
1321 ) is
1322 select bf.rowid row_id
1323 from pay_sub_classification_rules_f scr,
1324 pay_input_values_f iv,
1325 pay_balance_feeds_f bf,
1326 pay_balance_classifications bc,
1327 pay_balance_types bt
1328 where scr.sub_classification_rule_id = p_sub_classification_rule_id
1329 and iv.element_type_id = scr.element_type_id
1330 and iv.name = p_pay_value_name
1331 and bc.classification_id = scr.classification_id
1332 and bt.balance_type_id = bc.balance_type_id
1333 and bf.balance_type_id = bt.balance_type_id
1334 and bf.input_value_id = iv.input_value_id
1335 and bf.effective_start_date = scr.effective_start_date
1336 and bf.effective_end_date = scr.effective_end_date
1337 and scr.effective_start_date between iv.effective_start_date
1338 and iv.effective_end_date
1339 for update;
1340 --
1341 v_pay_value_name varchar2(80);
1342 --
1343 begin
1344 --
1345 -- Get translated name for pay value
1346 -- v_pay_value_name := hr_balance_feeds.pay_value_name;
1347 --
1348 -- Bug 2767760 - search for this bug number for full explanation of these
1349 -- changes.
1350 -- Set variable to base table pay value input value name
1351 v_pay_value_name := 'Pay Value';
1352 --
1353 -- Sub classification rule is being removed. Need to remove all automatic
1354 -- balance feeds that were created as a direct result of adding the sub
1355 -- classification rule.
1356 if p_dt_mode = 'ZAP' then
1357 --
1358 for v_bf_rec in csr_bal_feeds_sub_class_rule
1359 (p_sub_classification_rule_id,
1360 v_pay_value_name) loop
1361 --
1362 delete from pay_balance_feeds_f bf
1363 where bf.rowid = v_bf_rec.row_id;
1364 --
1365 end loop;
1366 --
1367 -- Sub classification rule is being shortened. Need to shorten all automatic
1368 -- balance feeds that were created as a direct result of adding the sub
1369 -- classification rule.
1370 elsif p_dt_mode = 'DELETE' then
1371 --
1372 for v_bf_rec in csr_bal_feeds_sub_class_rule
1373 (p_sub_classification_rule_id,
1374 v_pay_value_name) loop
1375 --
1376 update pay_balance_feeds_f bf
1377 set bf.effective_end_date = p_validation_start_date - 1
1378 where bf.rowid = v_bf_rec.row_id;
1379 --
1380 end loop;
1381 --
1382 -- Sub classification rule is being extended. Need to extend all automatic
1383 -- balance feeds that were created as a direct result of adding the sub
1384 -- classification rule NB. sub classification rules cannot be updated so
1385 -- 'DELETE_NEXT_CHANGE' will always open up a sub classification rule.
1386 elsif p_dt_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
1387 --
1388 for v_bf_rec in csr_bal_feeds_sub_class_rule
1389 (p_sub_classification_rule_id,
1390 v_pay_value_name) loop
1391 --
1392 update pay_balance_feeds_f bf
1393 set bf.effective_end_date = p_validation_end_date
1394 where bf.rowid = v_bf_rec.row_id;
1395 --
1396 end loop;
1397 --
1398 else
1399 --
1400 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1401 hr_utility.set_message_token('PROCEDURE',
1402 'hr_balance_feeds.del_bf_sub_class_rule');
1403 hr_utility.set_message_token('STEP','1');
1404 hr_utility.raise_error;
1405 --
1406 end if;
1407 --
1408 end del_bf_sub_class_rule;
1409 --
1410 ------------------------------------------------------------------------------
1411 -- NAME --
1412 -- hr_balance_feeds.bf_chk_proc_run_results --
1413 -- --
1414 -- DESCRIPTION --
1415 -- Detects if a change in a balance feed could result in a change of a --
1416 -- balance value ie. the period over which the balance feed changes --
1417 -- overlaps with a processed run result NB. the change in balance feed --
1418 -- couold be caused by a manual change, removing a sub classification etc.. --
1419 ------------------------------------------------------------------------------
1420 --
1421 function bf_chk_proc_run_results
1422 (
1423 p_mode varchar2,
1424 p_dml_mode varchar2,
1425 p_balance_type_id number,
1426 p_classification_id number,
1427 p_balance_classification_id number,
1428 p_balance_feed_id number,
1429 p_sub_classification_rule_id number,
1430 p_input_value_id number,
1431 p_validation_start_date date,
1432 p_validation_end_date date
1433 ) return boolean is
1434 --
1435 cursor csr_bf_ins_bal_class
1436 (
1437 p_balance_type_id number,
1438 p_classification_id number,
1439 p_pay_value_name varchar2
1440 ) is
1441 select iv.input_value_id,
1442 min(iv.effective_start_date) effective_start_date,
1443 max(iv.effective_end_date) effective_end_date
1444 from pay_input_values_f iv,
1445 pay_element_types_f et,
1446 pay_element_classifications ec,
1447 pay_balance_types bt,
1448 per_business_groups_perf ivbg,
1449 per_business_groups_perf btbg
1450 where bt.balance_type_id = p_balance_type_id
1451 and ec.classification_id = p_classification_id
1452 and ec.parent_classification_id is null
1453 and et.classification_id = ec.classification_id
1454 and iv.element_type_id = et.element_type_id
1455 and iv.name = p_pay_value_name
1456 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
1457 and (bt.balance_uom <> 'M' or
1458 (bt.balance_uom = 'M' and
1459 bt.currency_code = et.output_currency_code))
1460 and iv.effective_start_date between et.effective_start_date
1461 and et.effective_end_date
1462 /*
1463 Join are to get the legislation code for the business groups of the
1464 balance and input value being matched.
1465 */
1466 and iv.business_group_id = ivbg.business_group_id (+)
1467 and bt.business_group_id = btbg.business_group_id (+)
1468 /*
1469 Match on business group OR
1470 Business groups do not match so try to match on legislation NB.
1471 need to protect against the case where the business groups are
1472 different but share the same legislation code.
1473 */
1474 and (
1475 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
1476 (
1477 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
1478 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
1479 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
1480 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
1481 )
1482 )
1483 group by iv.input_value_id
1484 union
1485 select iv.input_value_id,
1486 scr.effective_start_date,
1487 scr.effective_end_date
1488 from pay_sub_classification_rules_f scr,
1489 pay_element_types_f et,
1490 pay_input_values_f iv,
1491 pay_element_classifications ec,
1492 pay_balance_types bt,
1493 per_business_groups_perf ivbg,
1494 per_business_groups_perf btbg
1495 where bt.balance_type_id = p_balance_type_id
1496 and ec.classification_id = p_classification_id
1497 and ec.parent_classification_id is not null
1498 and scr.classification_id = ec.classification_id
1499 and et.element_type_id = scr.element_type_id
1500 and iv.element_type_id = et.element_type_id
1501 and iv.name = p_pay_value_name
1502 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
1503 and (bt.balance_uom <> 'M' or
1504 (bt.balance_uom = 'M' and
1505 bt.currency_code = et.output_currency_code))
1506 and scr.effective_start_date between et.effective_start_date
1507 and et.effective_end_date
1508 and scr.effective_start_date between iv.effective_start_date
1509 and iv.effective_end_date
1510 /*
1511 Join are to get the legislation code for the business groups of the
1512 balance and input value being matched.
1513 */
1514 and iv.business_group_id = ivbg.business_group_id (+)
1515 and bt.business_group_id = btbg.business_group_id (+)
1516 /*
1517 Match on business group OR
1518 Business groups do not match so try to match on legislation NB.
1519 need to protect against the case where the business groups are
1520 different but share the same legislation code.
1521 */
1522 and (
1523 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
1524 (
1525 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
1526 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
1527 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
1528 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
1529 )
1530 );
1531 --
1532 cursor csr_bf_upd_del_bal_class
1533 (
1534 p_balance_classification_id number
1535 ) is
1536 select bf.balance_feed_id
1537 from pay_balance_feeds_f bf,
1538 pay_balance_classifications bc,
1539 pay_element_classifications ec
1540 where bc.balance_classification_id = p_balance_classification_id
1541 and bf.balance_type_id = bc.balance_type_id
1542 and ec.classification_id = bc.classification_id
1543 and ((ec.parent_classification_id is null and
1544 exists
1545 (select null
1546 from pay_element_types_f et,
1547 pay_input_values_f iv
1548 where iv.input_value_id = bf.input_value_id
1549 and et.element_type_id = iv.element_type_id
1550 and et.classification_id = bc.classification_id))
1551 or (ec.parent_classification_id is not null and
1552 exists
1553 (select null
1554 from pay_sub_classification_rules_f scr,
1555 pay_input_values_f iv
1556 where iv.input_value_id = bf.input_value_id
1557 and scr.element_type_id = iv.element_type_id
1558 and scr.classification_id = bc.classification_id)))
1559 and exists
1560 (select null
1561 from pay_run_results rr,
1562 pay_run_result_values rrv
1563 where rrv.input_value_id = bf.input_value_id
1564 and rr.run_result_id = rrv.run_result_id
1565 and rr.status like 'P%');
1566 --
1567 cursor csr_bal_feed
1568 (
1569 p_input_value_id number,
1570 p_validation_start_date date,
1571 p_validation_end_date date
1572 ) is
1573 select 1
1574 from dual
1575 where exists
1576 (select /*+ ORDERED USE_NL(rrv rr aa pa)
1577 INDEX(rrv PAY_RUN_RESULT_VALUES_PK) */ null
1578 from pay_run_result_values rrv,
1579 pay_run_results rr,
1580 pay_assignment_actions aa,
1581 pay_payroll_actions pa
1582 where rrv.input_value_id = p_input_value_id
1583 and rr.run_result_id = rrv.run_result_id
1584 and rr.status like 'P%'
1585 and aa.assignment_action_id = rr.assignment_action_id
1586 and pa.payroll_action_id = aa.payroll_action_id
1587 and pa.effective_date between p_validation_start_date
1588 and p_validation_end_date);
1589 --
1590 -- Finds all balance feeds that should be created as a direct result of
1591 -- creating a sub classification rule ie. find any balance classifications
1592 -- that match.
1593 cursor csr_bf_ins_sub_class_rule
1594 (
1595 p_classification_id number,
1596 p_pay_value_name varchar2
1597 ) is
1598 select iv.input_value_id,
1599 scr.effective_start_date,
1600 scr.effective_end_date
1601 from pay_sub_classification_rules_f scr,
1602 pay_element_types_f et,
1603 pay_input_values_f iv,
1604 pay_balance_classifications bc,
1605 pay_balance_types bt,
1606 per_business_groups_perf ivbg,
1607 per_business_groups_perf btbg
1608 where bc.classification_id = p_classification_id
1609 and bt.balance_type_id = bc.balance_type_id
1610 and et.element_type_id = scr.element_type_id
1611 and iv.element_type_id = et.element_type_id
1612 and iv.name = p_pay_value_name
1613 and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
1614 and (bt.balance_uom <> 'M' or
1615 (bt.balance_uom = 'M' and
1616 bt.currency_code = et.output_currency_code))
1617 and scr.effective_start_date between et.effective_start_date
1618 and et.effective_end_date
1619 and scr.effective_start_date between iv.effective_start_date
1620 and iv.effective_end_date
1621 /*
1622 Join are to get the legislation code for the business groups of the
1623 balance and input value being matched.
1624 */
1625 and iv.business_group_id = ivbg.business_group_id (+)
1626 and bt.business_group_id = btbg.business_group_id (+)
1627 /*
1628 Match on business group OR
1629 Business groups do not match so try to match on legislation NB.
1630 need to protect against the case where the business groups are
1631 different but share the same legislation code.
1632 */
1633 and (
1634 nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
1635 (
1636 nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
1637 nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
1638 nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
1639 nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
1640 )
1641 );
1642 --
1643 cursor csr_bf_upd_del_sub_class_rule
1644 (
1645 p_sub_classification_rule_id number,
1646 p_validation_start_date date,
1647 p_validation_end_date date
1648 ) is
1649 select bf.balance_feed_id
1650 from pay_sub_classification_rules_f scr,
1651 pay_input_values_f iv,
1652 pay_balance_feeds_f bf,
1653 pay_balance_classifications bc
1654 where scr.sub_classification_rule_id = p_sub_classification_rule_id
1655 and iv.element_type_id = scr.element_type_id
1656 and bc.classification_id = scr.classification_id
1657 and bf.balance_type_id = bc.balance_type_id
1658 and bf.input_value_id = iv.input_value_id
1659 and bf.effective_start_date = scr.effective_start_date
1660 and bf.effective_end_date = scr.effective_end_date
1661 and scr.effective_start_date between iv.effective_start_date
1662 and iv.effective_end_date
1663 and exists
1664 (select /*+ ORDERED*/
1665 null
1666 from pay_run_result_values rrv,
1667 pay_run_results rr,
1668 pay_assignment_actions aa,
1669 pay_payroll_actions pa
1670 where rrv.input_value_id = bf.input_value_id
1671 and rr.run_result_id = rrv.run_result_id
1672 and rr.status like 'P%'
1673 and aa.assignment_action_id = rr.assignment_action_id
1674 and pa.payroll_action_id = aa.payroll_action_id
1675 and pa.effective_date between p_validation_start_date
1676 and p_validation_end_date);
1677 --
1678 cursor csr_proc_run_result
1679 (
1680 p_input_value_id number,
1681 p_validation_start_date date,
1682 p_validation_end_date date
1683 ) is
1684 select 1
1685 from dual
1686 where
1687 exists (select /*+ FIRST_ROWS ORDERED
1688 USE_NL(rrv rr aa pa)
1689 INDEX(rrv PAY_RUN_RESULT_VALUES_PK)
1690 INDEX(rr PAY_RUN_RESULTS_PK)
1691 INDEX(aa PAY_ASSIGNMENT_ACTIONS_PK)
1692 INDEX(pa PAY_PAYROLL_ACTIONS_PK)
1693 */ 1
1694 from pay_run_result_values rrv,
1695 pay_run_results rr,
1696 pay_assignment_actions aa,
1697 pay_payroll_actions pa
1698 where rrv.input_value_id = p_input_value_id
1699 and rr.run_result_id = rrv.run_result_id
1700 and rr.status like 'P%'
1701 and aa.assignment_action_id = rr.assignment_action_id
1702 and pa.payroll_action_id = aa.payroll_action_id
1703 and pa.effective_date between p_validation_start_date
1704 and p_validation_end_date);
1705 --
1706 cursor csr_proc
1707 (
1708 p_validation_start_date date,
1709 p_validation_end_date date
1710 ) is
1711 select /*+ INDEX(pa pay_payroll_actions_n5)*/
1712 payroll_action_id
1713 from pay_payroll_actions pa
1714 where pa.effective_date between p_validation_start_date
1715 and p_validation_end_date
1716 and action_type in ('R', 'Q', 'B', 'I', 'V')
1717 order by payroll_action_id desc;
1718 --
1719 cursor csr_rrv_exists
1720 (
1721 p_input_value_id number
1722 ) is
1723 select 1
1724 from dual
1725 where
1726 exists (select 1
1727 from pay_run_result_values rrv
1728 where rrv.input_value_id = p_input_value_id);
1729 --
1730 cursor csr_proc_feed_result
1731 (
1732 p_payroll_action_id number,
1733 p_input_value_id number
1734 ) is
1735 select 1
1736 from dual
1737 where
1738 exists (select /*+ FIRST_ROWS ORDERED
1739 USE_NL(rr aa rrv)
1740 INDEX(rrv PAY_RUN_RESULT_VALUES_N50)
1741 INDEX(rr PAY_RUN_RESULTS_N50)
1742 INDEX(aa PAY_ASSIGNMENT_ACTIONS_N50)
1743 */ 1
1744 from pay_assignment_actions aa,
1745 pay_run_results rr,
1746 pay_run_result_values rrv
1747 where rrv.input_value_id = p_input_value_id
1748 and rr.run_result_id = rrv.run_result_id
1749 and rr.status like 'P%'
1750 and aa.assignment_action_id = rr.assignment_action_id
1751 and aa.payroll_action_id = p_payroll_action_id);
1752
1753 v_bf_id number;
1754 v_pay_value_name varchar2(80);
1755 v_rrv_found boolean := FALSE;
1756 v_rr_rec csr_proc_run_result%rowtype;
1757 v_pfr_rec csr_proc_feed_result%rowtype;
1758 v_iv_id number;
1759 v_rrv_exists number := -1;
1760 v_check_value pay_action_parameters.parameter_name%type;
1761 --
1762 begin
1763 --
1764 -- Get translated name for pay value
1765 -- v_pay_value_name := hr_balance_feeds.pay_value_name;
1766 -- Bug 2767760 - search for this bug number for full explanation of these
1767 -- changes.
1768 -- Set variable to base table pay value input value name
1769 v_pay_value_name := 'Pay Value';
1770 --
1771 if (p_mode = 'BALANCE_CLASSIFICATION' and
1772 p_dml_mode = 'UPDATE_DELETE' and
1773 p_balance_classification_id is not null) then
1774 --
1775 open csr_bf_upd_del_bal_class
1776 (p_balance_classification_id);
1777 fetch csr_bf_upd_del_bal_class into v_bf_id;
1778 if csr_bf_upd_del_bal_class%found then
1779 close csr_bf_upd_del_bal_class;
1780 return (TRUE);
1781 else
1782 close csr_bf_upd_del_bal_class;
1783 return (FALSE);
1784 end if;
1785 --
1786 elsif (p_mode = 'BALANCE_FEED' and
1787 p_dml_mode = 'UPDATE_DELETE' and
1788 p_balance_feed_id is not null) then
1789 --
1790 select distinct input_value_id
1791 into v_iv_id
1792 from pay_balance_feeds_f
1793 where balance_feed_id = p_balance_feed_id;
1794 --
1795 open csr_bal_feed
1796 (v_iv_id,
1797 p_validation_start_date,
1798 p_validation_end_date);
1799 fetch csr_bal_feed into v_bf_id;
1800 if csr_bal_feed%found then
1801 close csr_bal_feed;
1802 return (TRUE);
1803 else
1804 close csr_bal_feed;
1805 return (FALSE);
1806 end if;
1807 --
1808 elsif (p_mode = 'SUB_CLASSIFICATION_RULE' and
1809 p_dml_mode = 'UPDATE_DELETE' and
1810 p_sub_classification_rule_id is not null) then
1811 --
1812 open csr_bf_upd_del_sub_class_rule
1813 (p_sub_classification_rule_id,
1814 p_validation_start_date,
1815 p_validation_end_date);
1816 fetch csr_bf_upd_del_sub_class_rule into v_bf_id;
1817 if csr_bf_upd_del_sub_class_rule%found then
1818 close csr_bf_upd_del_sub_class_rule;
1819 return (TRUE);
1820 else
1821 close csr_bf_upd_del_sub_class_rule;
1822 return (FALSE);
1823 end if;
1824 --
1825 elsif (p_mode = 'SUB_CLASSIFICATION_RULE' and
1826 p_dml_mode = 'INSERT' and
1827 p_classification_id is not null) then
1828 --
1829 for v_iv_rec in csr_bf_ins_sub_class_rule
1830 (p_classification_id,
1831 v_pay_value_name) loop
1832 --
1833 open csr_proc_run_result
1834 (v_iv_rec.input_value_id,
1835 v_iv_rec.effective_start_date,
1836 v_iv_rec.effective_end_date);
1837 fetch csr_proc_run_result into v_rr_rec;
1838 if csr_proc_run_result%found then
1839 close csr_proc_run_result;
1840 v_rrv_found := TRUE;
1841 exit;
1842 else
1843 close csr_proc_run_result;
1844 end if;
1845 --
1846 end loop;
1847 --
1848 if v_rrv_found then
1849 return (TRUE);
1850 else
1851 return (FALSE);
1852 end if;
1853 --
1854 elsif (p_mode = 'BALANCE_CLASSIFICATION' and
1855 p_dml_mode = 'INSERT' and
1856 p_classification_id is not null) then
1857 --
1858 for v_iv_rec in csr_bf_ins_bal_class
1859 (p_balance_type_id,
1860 p_classification_id,
1861 v_pay_value_name) loop
1862 --
1863 open csr_proc_run_result
1864 (v_iv_rec.input_value_id,
1865 v_iv_rec.effective_start_date,
1866 v_iv_rec.effective_end_date);
1867 fetch csr_proc_run_result into v_rr_rec;
1868 if csr_proc_run_result%found then
1869 close csr_proc_run_result;
1870 v_rrv_found := TRUE;
1871 exit;
1872 else
1873 close csr_proc_run_result;
1874 end if;
1875 --
1876 end loop;
1877 --
1878 if v_rrv_found then
1879 return (TRUE);
1880 else
1881 return (FALSE);
1882 end if;
1883 --
1884 elsif (p_mode = 'BALANCE_FEED' and
1885 p_dml_mode = 'INSERT' and
1886 p_input_value_id is not null) then
1887 --
1888 --
1889 -- Check if this warning check has been disabled
1890 --
1891 begin
1892 select parameter_value
1893 into v_check_value
1894 from pay_action_parameters pap
1895 where pap.parameter_name = 'CHANGED_BALANCE_VALUE_CHECK';
1896
1897 exception
1898 when others then
1899 v_check_value := 'Y';
1900 end;
1901
1902 if v_check_value = 'N' then
1903 v_rrv_found := FALSE;
1904 else
1905 for proc in csr_proc
1906 (p_validation_start_date,
1907 p_validation_end_date) loop
1908
1909 if (v_rrv_exists = -1) then
1910 open csr_rrv_exists
1911 (p_input_value_id);
1912 fetch csr_rrv_exists into v_rrv_exists;
1913 if csr_rrv_exists%notfound then
1914 close csr_rrv_exists;
1915 exit;
1916 else
1917 close csr_rrv_exists;
1918 end if;
1919 end if;
1920
1921 open csr_proc_feed_result
1922 (proc.payroll_action_id,
1923 p_input_value_id);
1924 fetch csr_proc_feed_result into v_pfr_rec;
1925 if csr_proc_feed_result%found then
1926 close csr_proc_feed_result;
1927 v_rrv_found := TRUE;
1928 exit;
1929 else
1930 close csr_proc_feed_result;
1931 end if;
1932 end loop;
1933 end if;
1934 --
1935 if v_rrv_found then
1936 return (TRUE);
1937 else
1938 return (FALSE);
1939 end if;
1940 --
1941 else
1942 --
1943 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1944 hr_utility.set_message_token('PROCEDURE',
1945 'hr_balance_feeds.bf_chk_proc_run_results');
1946 hr_utility.set_message_token('STEP','1');
1947 hr_utility.raise_error;
1948 --
1949 end if;
1950 --
1951 end bf_chk_proc_run_results;
1952 --
1953 end HR_BALANCE_FEEDS;
[Home] [Help]