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