1 package body hr_element_links as
2 /* $Header: pyelelnk.pkb 115.2 99/07/17 05:58:54 porting ship $ */
3 --
4 /*
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1989 Oracle Corporation UK Ltd., *
9 * Richmond, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24 --
25 Name : hr_element_links
26 --
27 Description : This package holds procedures and functions related to the
28 following tables :
29 PAY_ELEMENT_LINKS_F
30 --
31 Uses : hr_element_links
32 hr_input_values
33 Used By : n/a
34 --
35 Test List
36 ---------
37 Procedure Name Date Test Id Status
38 +----------------------------+----------+-----------+-------+--------------+
39 chk_mutual_exclusivity M Dyer 19-Jan-1993 1 Complete
40 chk_mutual_exclusivity M Dyer 20-Jan-1993 2 Complete
41 +----------------------------+----------+-----------+-------+--------------+
42 chk_element_links M Dyer 08-Feb-1993 1 Complete
43 chk_element_links M Dyer 21-Apr-1993 2 Complete
44 chk_element_links M Dyer 29-Apr-1993 3 Complete
45 +----------------------------+----------+-----------+-------+--------------+
46 chk_upd_element_links M Dyer 08-Feb-1993 1 Complete
47 chk_upd_element_links M Dyer 21-Apr-1993 1 Complete
48 +----------------------------+----------+-----------+-------+--------------+
49 chk_del_element_links M Dyer 09-Feb-1993 1 Complete
50 chk_del_element_links M Dyer 26-Mar-1993 2 Complete
51 +----------------------------+----------+-----------+-------+--------------+
52 ins_3p_element_link M Dyer 09-Feb-1993 1 Complete
53 +----------------------------+----------+-----------+-------+--------------+
54 upd_3p_element_links M Dyer 09-Feb-1993 1 Complete
55 +----------------------------+----------+-----------+-------+--------------+
56 del_3p_element_links M Dyer 09-Feb-1993 1 Complete
57 --
58 --
59 --
60 Change List
61 -----------
62 Date Name Vers Bug No Description
63 +-----------+-------------+-------+----------+-----------------------------+
64 20-jan-1993 M Dyer Increased length of local
65 variables on segment values
66 for mutual exclusivity.
67 08-Feb-1993 M Dyer 30.1 Added chk_element_links
68 and chk_upd_element_links
69 09-Feb-1993 M Dyer 30.2 Added chk_del_element_link
70 ins_3p_element_link
71 upd_3p_element_links
72 del_3p_element_links
73 17-Feb-93 J.S.Hobbs 30.10 Altered insert_alu and
74 create_standard_entries_el.
75 03-Mar-1993 J.S.Hobbs 30.11 Removed get_termination_date,
76 get_entry_start_date_qc and
77 create_rec_element_entry.
78 They all exist in hrentmnt
79 package.
80 26-Mar-1993 M Dyer 30.12 No delete allowed for element
81 links that have recurring
82 additional entries.
83 21-Apr-1993 M Dyer 30.13 Changes made to chk_element
84 _links for distributed element
85 _s.
86 29-Apr-1993 30.14 Change made to chk_element_links
87 to calculate end date.
88 07-Jun-1993 J.S.Hobbs 30.23 Corrected
89 create_standard_entries_el.
90 21-Jun-1993 M Dyer 30.24 changes made to chk_mutual
91 exclusivity to return end date
92 of link. Now called from
93 chk_element_link.
94 22-Jul-1993 M Dyer 30.25 B112 If the costable type is
95 distributed then the pay value
96 only can be costed.
97 B113 If the costable type is
98 distributed then an empty
99 distribution set cannot be
100 selected.
101 17-Sep-1993 J.S.Hobbs 40.01/ B230/ Replaced
102 30.28 X26 hrentmnt.get_termination_date
103 with a call to
104 hr_entry.entry_asg_pay_link_dates
105 as it is more comprehensive.
106 05-Oct-1993 M Kaddir 40.2 X21 Changed chk_element_link
107 and chk_mutual_exclusivity
108 to include two new link
109 criteria:
110 - Employment Category and
111 - Pay Basis
112 22-Oct-1993 J.S.Hobbs 40.3 -Changed ins_3p_element_link
113 to cope with two new
114 criteria ie. PAY_BASIS_ID and
115 EMPLOYMENT_CATEGORY.
116 -Removed
117 create_standard_entries_el
118 and recoded in hrentmnt with
119 a new prcoedure called
120 maintain_entries_el.
121 -Changed insert_alu so that
122 it uses
123 hr_entry.entry_asg_pay_link_dates.
124 25-Jan-94 N Simpson - Changed chk_mutual_exclusivity
125 to restrict element links
126 checked to the user's
127 business group. G525
128 17-Feb-94 N Simpson Added procedure link_flag_updated and modified upd_3p_element_link
129 to allow update of standard
130 link flag from No to Yes, thus
131 allowing changes to be made to
132 the input value defaults before
133 the creation of standard entries
134 1 Mar 94 N Simpson B400 -- Added check that all
135 mandatory input values have
136 defaults, before creating
137 standard entries.
138 --
139 4 May 95 N Simpson B280150 Included location check in
140 chk_mutual_exclusivity. The
141 flag had been set but was not
142 included in the test for
143 exclusivity.
144 ###########################################################################
145 --
146 04-Mar-1994 C.Swan Moved from 10.0 as a result
147 of the 10->10G merge.
148 07-Mar-1994 C.Swan Removed leading "####" from
149 above line, due to
150 Autoinstall objecting.
151 --
152 23 Mar 94 N Simpson B445 amended set_locations which
153 incorrectly stated the package
154 name, and added an extra
155 set_location call
156 23-Nov-1994 J.S.Hobbs G1707 Replaced fnd_common_lookups with hr_lookups.
157 24-Nov-1994 R.M.Fine G1725 Suppressed index on business_group_id
158 07-Feb-1996 N.Simpson G336502 Redirected some procedures to the new package
159 pay_element_links_pkg. This will fix the bug
160 and avoid the need for dual maintenance in
161 the future. Ideally, this package will
162 eventually be dropped completely.
163 24-FEB-1999 J. Moyano 115.1 MLS changes. Procedures upd_3p_element_links
164 and chk_link_input_values affected.
165 Has this package been drooped already?
166
167 */
168 --
169 /*
170 NAME
171 chk_mutual_exclusivity
172 DESCRIPTION
173 This function checks that the elements are mutually exclusive. This
174 means that all links to an element must be guaranteed to be exclusive
175 of eachother. If they are not then there is a danger that someone
176 will be assigned to the same element twice in different ways
177 NOTES
178 18-JUN-1993 M Dyer:
179 Date out parameter added. This returns the latest date on which the
180 element is still mutually exclusive. The validation end date will be
181 returned if the link is found to be exclusive of all other links. The
182 procedure will be called from chk_element_links which brings it into
183 line with the other validation procedures.
184 07-FEB-1996 N Simpson
185 Removed code and redirected to new package pay_element_links_pkg to
186 avoid dual maintenance.
187 */
188 --
189 PROCEDURE
190 chk_mutual_exclusivity(p_element_type_id in number,
191 p_element_link_id in number,
192 p_validation_start_date in date,
193 p_validation_end_date in date,
194 p_greatest_end_date out date,
195 p_organization_id in number,
196 p_people_group_id in number,
197 p_job_id in number,
198 p_position_id in number,
199 p_grade_id in number,
200 p_location_id in number,
201 p_link_to_all_payrolls_flag in varchar2,
202 p_payroll_id in number,
203 p_employment_category in varchar2,
204 p_pay_basis_id in number,
205 p_business_group_id in number) is
206 --
207 begin
208 --
209 p_greatest_end_date := pay_element_links_pkg.max_end_date (
210 --
211 p_element_type_id,
212 p_element_link_id,
213 p_validation_start_date,
214 p_validation_end_date,
215 p_organization_id,
216 p_people_group_id,
217 p_job_id,
218 p_position_id,
219 p_grade_id,
220 p_location_id,
221 p_link_to_all_payrolls_flag,
222 p_payroll_id,
223 p_employment_category,
224 p_pay_basis_id,
225 p_business_group_id);
226 --
227 end chk_mutual_exclusivity;
228 --
229 --
230 /*
231 NAME
232 get_greatest_end_date (OBSOLETE)
233 */
234 --
235 FUNCTION get_greatest_end_date(p_element_type_id in number,
236 p_business_group_id in number,
237 p_link_to_all_payrolls_flag in varchar2,
238 p_payroll_id in number,
239 p_greatest_link_date in date)
240 return date is
241 --
242 proc_name CONSTANT varchar2(40) := 'hr_element_links.get_greatest_end_date';
243 --
244 begin
245 --
246 hr_utility.set_location( proc_name, 1);
247 --
248 -- This procedure is obsolete. The call should be redirected to
249 -- pay_element_links_pkg.max_end_date. Left error message in case there are any outstanding
250 -- calls that I missed.
251 --
252 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
253 hr_utility.set_message_token ('PROCEDURE',proc_name);
254 hr_utility.set_message_token ('STEP','1');
255 hr_utility.raise_error;
256 --
257 end get_greatest_end_date;
258 --
259 /*
260 NAME
261 chk_element_links
262 DESCRIPTION
263 This procedure checks to see if any distributed links are themselves in
264 distribution sets
265 */
266 --
267 PROCEDURE chk_element_links(p_element_type_id in number,
268 p_element_link_id in number,
269 p_val_start_date in date,
270 p_val_end_date in out date,
271 p_business_group_id in number,
272 p_legislation_code in varchar2,
273 p_costable_type in varchar2,
274 p_organization_id in number,
275 p_people_group_id in number,
276 p_job_id in number,
277 p_position_id in number,
278 p_grade_id in number,
279 p_location_id in number,
280 p_payroll_id in number,
281 p_link_to_all_payrolls_flag in varchar2,
282 p_element_set_id in number,
283 p_balancing_keyflex_id in number,
284 p_classification_id in number,
285 p_employment_category in varchar2,
286 p_pay_basis_id in number) is
287 --
288 l_validation_ok varchar2(1) := 'Y';
289 l_max_element_date date;
290 l_max_payroll_date date;
291 begin
292 --
293 Hr_utility.set_location('hr_element_links.chk_element_links', 1);
294 --
295 -- Element links cannot be distributed if the element is part of a
296 -- distribution set.
297 if p_costable_type = 'D' then
298 --
299 hr_utility.set_location('hr_element_links.chk_element_links', 2);
300 --
301 if p_element_set_id is null then
302 --
303 hr_utility.set_message(801,'PAY_6699_LINK_NO_DIST_SET');
304 hr_utility.raise_error;
305 --
306 end if;
307 --
308 begin
309 --
310 select 'N'
311 into l_validation_ok
312 from sys.dual
313 where exists
314 (select 1
315 from pay_element_set_members esm,
316 pay_element_sets es
317 where esm.element_type_id = p_element_type_id
318 and es.element_set_id = esm.element_set_id
319 and es.element_set_type = 'D');
320 --
321 exception
322 when NO_DATA_FOUND then NULL;
323 end;
324 --
325 if l_validation_ok = 'N' then
326 hr_utility.set_message(801,'PAY_6462_LINK_DIST_IN_DIST');
327 hr_utility.raise_error;
328 end if;
329 --
330 -- The distribution set must be credit or debit according to what the
331 -- primary classification is of the element.
332 --
333 begin
334 --
335 select 'N'
336 into l_validation_ok
337 from sys.dual
338 where exists
339 (select 1
340 from pay_element_set_members esm,
341 pay_element_classifications ec,
342 pay_element_classifications ec2
343 where ec.classification_id = p_classification_id
344 and esm.element_set_id = p_element_set_id
345 and ec2.classification_id = esm.classification_id
346 and ec2.costing_debit_or_credit <> ec.costing_debit_or_credit);
347 --
348 exception
349 when NO_DATA_FOUND then null;
350 end;
351 --
352 if l_validation_ok = 'N' then
353 hr_utility.set_message(801,'PAY_6700_LINK_CRE_OR_DEB');
354 hr_utility.raise_error;
355 end if;
356 --
357 -- If the link is distributed then the distribution set must have some
358 -- Elements in it.
359 --
360 begin
361 --
362 select 'N'
363 into l_validation_ok
364 from pay_element_sets es
365 where es.element_set_id = p_element_set_id
366 and exists
367 (select 1
368 from pay_element_set_members esm,
369 pay_element_types_f et
370 where es.element_set_id = esm.element_set_id
371 and esm.element_type_id = et.element_type_id
372 and p_val_start_date between
373 et.effective_start_date and et.effective_end_date);
374 --
375 exception
376 when NO_DATA_FOUND then
377 hr_utility.set_message(801,'PAY_6916_LINK_EMPTY_DIST_SET');
378 hr_utility.raise_error;
379 end;
380 end if;
381 --
382 -- If the link is costed, Fixed costed or Distributed then the balancing
383 -- keyflex must be populated.
384 --
385 if (p_costable_type <> 'N') and
386 (p_balancing_keyflex_id is null) then
387 --
388 hr_utility.set_message(801,'PAY_6698_LINK_BAL_KEYFLEX_MAN');
389 hr_utility.raise_error;
390 --
391 end if;
392 --
393 -- Determine the greatest end date of the element link
394 --
395 p_val_end_date := pay_element_links_pkg.max_end_date (
396 p_element_type_id,
397 p_element_link_id,
398 p_val_start_date,
399 p_val_end_date,
400 p_organization_id,
401 p_people_group_id,
402 p_job_id,
403 p_position_id,
404 p_grade_id,
405 p_location_id,
406 p_link_to_all_payrolls_flag,
407 p_payroll_id,
408 p_employment_category,
409 p_pay_basis_id,
410 p_business_group_id);
411 --
412 end chk_element_links;
413 --
414 --
415 /*
416 NAME
417 chk_upd_element_links
418 DESCRIPTION
419 The costable type of a link can only be updated over all time if there
420 are no entries in existence for this link.
421 */
422 --
423 PROCEDURE chk_upd_element_links(p_element_link_id in number,
424 p_update_mode in varchar2,
425 p_val_start_date in date,
426 p_val_end_date in date,
427 p_old_costable_type in varchar2,
428 p_costable_type in varchar2) is
429 --
430 l_validation_ok varchar2(1) := 'Y';
431 l_validation_not_ok varchar2(1) := 'N';
432 --
433 begin
434 -- We need to check to see if costable type is begin updated.
435 -- This cannot happen if there are any entries in existence for this link
436 -- and must happen over all time for the link.
437 --
438 --
439 hr_utility.set_location('hr_element_links.chk_upd_element_links', 1);
440 --
441 If p_old_costable_type <> p_costable_type then
442 if p_update_mode <> 'CORRECTION' then
443 hr_utility.set_message(801,'PAY_6466_LINK_NO_COST_UPD2');
444 hr_utility.raise_error;
445 end if;
446 --
447 begin
448 --
449 select 'N'
450 into l_validation_ok
451 from sys.dual
452 where exists
453 (select 1
454 from pay_element_entries_f ee
455 where p_element_link_id = ee.element_link_id);
456 --
457 exception
458 when NO_DATA_FOUND then NULL;
459 end;
460 --
461 if l_validation_ok = 'N' then
462 hr_utility.set_message(801,'PAY_6465_LINK_NO_COST_UPD1');
463 hr_utility.raise_error;
464 end if;
465 --
466 --
467 hr_utility.set_location('hr_element_links.chk_upd_element_links', 2);
468 --
469 begin
470 --
471 select 'Y'
472 into l_validation_not_ok
473 from sys.dual
474 where p_val_start_date =
475 (select min(effective_start_date)
476 from pay_element_links_f
477 where p_element_link_id = element_link_id)
478 and p_val_end_date =
479 (select max(effective_end_date)
480 from pay_element_links_f
481 where p_element_link_id = element_link_id);
482 --
483 exception
484 when NO_DATA_FOUND then NULL;
485 end;
486 --
487 if l_validation_not_ok = 'N' then
488 hr_utility.set_message(801,'PAY_6466_LINK_NO_COST_UPD2');
489 hr_utility.raise_error;
490 end if;
491 end if;
492 --
493 end chk_upd_element_links;
494 --
495 --
496 /*
497 NAME
498 chk_del_element_links
499 DESCRIPTION
500 This procedure checks to see whether element links can be deleted.
501 They cannot be deleted if there are any non recurring entries in the
502 validation period. They can be deleted if any recurring entries exist
503 but this will result in these entries being lost forever and a warning
504 message will be given to this effect.
505 */
506 --
507 PROCEDURE chk_del_element_link(p_element_link_id in varchar2,
508 p_val_start_date in date,
509 p_val_end_date in date,
510 p_warning_message in out varchar2) is
511 --
512 l_delete_ok varchar2(1) := 'Y';
513 --
514 begin
515 --
516 hr_utility.set_location('hr_element_links.chk_del_element_link', 1);
517 --
518 --
519 -- No delete is allowed if there are non-recurring entries in the validation
520 -- period.
521 begin
522 --
523 select 'N'
524 into l_delete_ok
525 from sys.dual
526 where exists
527 (select 1
528 from pay_element_types_f et,
529 pay_element_entries_f ee,
530 pay_element_links_f el
531 where p_element_link_id = el.element_link_id
532 and el.element_type_id = et.element_type_id
533 and et.processing_type = 'N'
534 and ee.element_link_id = el.element_link_id
535 and p_val_start_date <= ee.effective_end_date
536 and p_val_end_date >= ee.effective_start_date);
537 --
538 exception
539 when NO_DATA_FOUND then NULL;
540 end;
541 --
542 if l_delete_ok = 'N' then
543 hr_utility.set_message(801,'PAY_6467_LINK_NO_DEL_LINKS');
544 hr_utility.raise_error;
545 end if;
546 --
547 -- Even if the element type is recurring there may have been additional
548 -- entries created. These will prevent delete.
549 --
550 begin
551 --
552 select 'N'
553 into l_delete_ok
554 from sys.dual
555 where exists
556 (select 1
557 from pay_element_entries_f ee
558 where p_element_link_id = ee.element_link_id
559 and ee.entry_type = 'D'
560 and p_val_start_date <= ee.effective_end_date
561 and p_val_end_date >= ee.effective_start_date);
562 --
563 exception
564 when NO_DATA_FOUND then NULL;
565 end;
566 --
567 if l_delete_ok = 'N' then
568 hr_utility.set_message(801,'PAY_6639_LINK_NO_DEL_ADD_ENTRY');
569 hr_utility.raise_error;
570 end if;
571 --
572 end chk_del_element_link;
573 --
574 PROCEDURE insert_alu(p_mode varchar2,
575 p_id_flex_num number,
576 p_business_group_id number,
577 p_people_group_id number,
578 p_element_link_id number,
579 p_assignment_id number,
580 p_effective_start_date date,
581 p_effective_end_date date) is
582 --
583 begin
584 --
585 pay_asg_link_usages_pkg.insert_ALU (
586 --
587 p_business_group_id,
588 p_people_group_id,
589 p_element_link_id,
590 p_effective_start_date,
591 p_effective_end_date);
592 --
593 end insert_alu;
594 --
595 --
596 /*
597 NAME
598 ins_costing_segments
599 DESCRIPTION
600 This procedure will update the pay_cost_allocation_keyflex table with
601 the concatenated costing keyflexes. It should always be called when
602 a new costed, fixed or distributed element link is created and also
603 when one of these fields have been updated.
604 */
605 PROCEDURE ins_costing_segments(
606 p_cost_allocation_keyflex_id varchar2,
607 p_displayed_cost_keyflex varchar2,
608 p_balancing_keyflex_id varchar2,
609 p_displayed_balancing_keyflex varchar2) is
610 --
611 begin
612 --
613 -- We only need to do the updating if there is a costing keyflex there.
614 --
615 if p_cost_allocation_keyflex_id is not null then
616 --
617 update pay_cost_allocation_keyflex
618 set concatenated_segments = p_displayed_cost_keyflex
619 where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
620 and concatenated_segments is null;
621 --
622 end if;
623 --
624 if p_balancing_keyflex_id is not null then
625 --
626 update pay_cost_allocation_keyflex
627 set concatenated_segments = p_displayed_balancing_keyflex
628 where cost_allocation_keyflex_id = p_balancing_keyflex_id
629 and concatenated_segments is null;
630 --
631 end if;
632 --
633 end ins_costing_segments;
634 --
635 /*
636 NAME
637 ins_3p_element_link
638 DESCRIPTION
639 This procedure inserts link input values when an element link is
640 created. It will also insert Assignment link usages and Standard
641 recurring entries.
642 */
643 --
644 procedure ins_3p_element_link
645 (
646 p_element_link_id in number,
647 p_element_type_id in number,
648 p_val_start_date in date,
649 p_val_end_date in date,
650 p_standard_link_flag in varchar2,
651 p_payroll_id in number,
652 p_link_to_all_payrolls_flag in varchar2,
653 p_job_id in number,
654 p_grade_id in number,
655 p_position_id in number,
656 p_organization_id in number,
657 p_people_group_id in number,
658 p_location_id in number,
659 p_pay_basis_id in number,
660 p_employment_category in varchar2,
661 p_qual_age in number,
662 p_qual_length_of_service in number,
663 p_qual_units in varchar2,
664 p_costable_type in varchar2,
665 p_pay_value_name in varchar2,
666 p_id_flex_num in number,
667 p_business_group_id in number,
668 p_legislation_code in varchar2
669 ) is
670 --
671 -- Cursor returns a row if a mandatory input value for a standard link has no
672 -- default value
673 --
674 cursor csr_link_defaults is
675 select 1
676 from pay_link_input_values_f LINK,
677 pay_input_values_f TYPE
678 where link.element_link_id = p_element_link_id
679 and link.input_value_id = type.input_value_id
680 and type.mandatory_flag = 'Y'
681 and p_standard_link_flag = 'Y'
682 and ((link.default_value is null and type.hot_default_flag = 'N')
683 or (type.default_value is null and link.default_value is null
684 and type.hot_default_flag = 'Y'));
685 --
686 v_dummy number;
687 --
688 begin
689 --
690 hr_utility.set_location('hr_element_links.ins_3p_element_link', 1);
691 --
692 -- Call create link input value
693 hr_input_values.create_link_input_value(
694 'INSERT_LINK',
695 p_element_link_id,
696 NULL,
697 NULL,
698 p_costable_type,
699 p_val_start_date,
700 p_val_end_date,
701 NULL,
702 NULL,
703 NULL,
704 NULL,
705 NULL,
706 p_legislation_code,
707 p_pay_value_name,
708 p_element_type_id);
709 --
710 --
711 hr_utility.set_location('hr_element_links.ins_3p_element_link', 2);
712 --
713 pay_asg_link_usages_pkg.insert_ALU (
714 --
715 p_business_group_id,
716 p_people_group_id,
717 p_element_link_id,
718 p_val_start_date,
719 p_val_end_date);
720 --
721 --
722 hr_utility.set_location('hr_element_links.ins_3p_element_link', 3);
723 --
724 -- Error if standard link has a mandatory input value with no default.
725 --
726 open csr_link_defaults;
727 fetch csr_link_defaults into v_dummy;
728 if csr_link_defaults%found then
729 hr_utility.set_message (801,'HR_7095_INPVAL_NO_STD_DEFLT');
730 hr_utility.raise_error;
731 end if;
732 close csr_link_defaults;
733 --
734 hr_utility.set_location('hr_element_links.ins_3p_element_link', 4);
735 --
736 -- Create standard entries
737 --
738 if p_standard_link_flag = 'Y' then
739 hrentmnt.maintain_entries_el
740 (p_business_group_id,
741 p_element_link_id,
742 p_element_type_id,
743 p_val_start_date,
744 p_val_end_date,
745 p_payroll_id,
746 p_link_to_all_payrolls_flag,
747 p_job_id,
748 p_grade_id,
749 p_position_id,
750 p_organization_id,
751 p_location_id,
752 p_pay_basis_id,
753 p_employment_category,
754 p_people_group_id);
755 end if;
756 --
757 end ins_3p_element_link;
758 --
759 --
760 /*
761 NAME
762 upd_3p_element_link
763 DESCRIPTION
764 This procedure updates the costing flag on the link input values
765 according to the costable type on the element link.
766 */
767 --
768 PROCEDURE upd_3p_element_links(p_element_link_id in number,
769 p_val_start_date in date,
770 p_val_end_date in date,
771 p_pay_value_name in varchar2,
772 p_old_costable_type in varchar2,
773 p_costable_type in varchar2,
774 p_payroll_id number,
775 p_business_group_id number,
776 p_location_id number,
777 p_grade_id number,
778 p_link_to_all_payrolls_flag varchar2,
779 p_organization_id number,
780 p_position_id number,
781 p_job_id number,
782 p_element_type_id number,
783 p_pay_basis_id number,
784 p_employment_category number,
785 p_people_group_id number,
786 p_old_link_flag in varchar2,
787 p_link_flag in varchar2) is
788 --
789 begin
790 -- If the costable type is updated from costed or Fixed to Distributed or
791 -- not costed then we need to make all the link input values not costed.
792 --
793 hr_utility.set_location('hr_element_links.upd_3p_element_link', 1);
794 --
795 if (p_old_costable_type = 'C' or p_old_costable_type = 'F')
796 and (p_costable_type = 'D' or p_costable_type = 'N') then
797 --
798 update pay_link_input_values_f
799 set costed_flag = 'N'
800 where costed_flag = 'Y'
801 and p_element_link_id = element_link_id;
802 --
803 -- If the costable type is changed from non_costed or distributed to fixed or
804 -- costed then the pay_value will become costed.
805 elsif (p_old_costable_type = 'D' or p_old_costable_type = 'N') and
806 (p_costable_type = 'F' or p_costable_type = 'C') then
807 --
808 update pay_link_input_values_f liv
809 set liv.costed_flag = 'Y'
810 where p_element_link_id = liv.element_link_id
811 and liv.input_value_id =
812 (select iv.input_value_id
813 from pay_input_values_f_tl iv_tl,
814 pay_input_values_f iv
815 where liv.input_value_id = iv.input_value_id
816 and iv.input_value_id = iv_tl.input_value_id
817 and iv_tl.name = p_pay_value_name
818 and userenv('LANG') = iv_tl.language
819 and p_val_start_date between
820 iv.effective_start_date and iv.effective_end_date);
821 --
822 end if;
823 --
824 hr_utility.set_location('hr_element_links.upd_3p_element_link', 2);
825 --
826 -- Create standard entries if standard link flag is updated to 'Y'
827 --
828 if p_old_link_flag = 'N' and p_link_flag = 'Y' then
829 hr_element_links.link_flag_updated (p_element_link_id);
830 hrentmnt.maintain_entries_el
831 (p_business_group_id,
832 p_element_link_id,
833 p_element_type_id,
834 p_val_start_date,
835 p_val_end_date,
836 p_payroll_id,
837 p_link_to_all_payrolls_flag,
838 p_job_id,
839 p_grade_id,
840 p_position_id,
841 p_organization_id,
842 p_location_id,
843 p_pay_basis_id,
844 p_employment_category,
845 p_people_group_id);
846 end if;
847 --
848 end upd_3p_element_links;
849 --
850 PROCEDURE delete_entry_values(
851 p_element_entry_id in number,
852 p_delete_mode in varchar2,
853 p_val_session_date in date,
854 p_val_start_date in date,
855 p_val_end_date in date) is
856 --
857 begin
858 --
859 if p_delete_mode = 'ZAP' then
860 --
861 delete from pay_element_entry_values_f
862 where element_entry_id = p_element_entry_id;
863 --
864 elsif p_delete_mode = 'DELETE' then
865 --
866 --
867 hr_utility.set_location('hr_element_links.entry_values', 1);
868 --
869 -- delete all future records
870 delete from pay_element_entry_values_f
871 where element_entry_id = p_element_entry_id
872 and effective_start_date > p_val_session_date;
873 --
874 -- update current records so that the end date is the session date
875 update pay_element_entry_values_f
876 set effective_end_date = p_val_session_date
877 where element_entry_id = p_element_entry_id
878 and p_val_session_date between
879 effective_start_date and effective_end_date;
880 --
881 end if;
882 --
883 -- Element entry_values will not be 'opened up' on delete next change
884 --
885 end delete_entry_values;
886 --
887 /*
888 NAME
889 del_3p_element_link
890 DESCRIPTION
891 This procedure deletes link input values in line with the deletion on
892 element link. It will also delete Assignment link usages and recurring
893 entries.
894 */
895 --
896 PROCEDURE del_3p_element_links(
897 p_element_link_id in number,
898 p_delete_mode in varchar2,
899 p_val_session_date in date,
900 p_val_start_date in date,
901 p_val_end_date in date,
902 p_id_flex_num in number,
903 p_business_group_id in number,
904 p_people_group_id in number) is
905 --
906 v_end_of_time date := to_date('31/12/4712','DD/MM/YYYY');
907 v_alu_start_date date;
908 v_alu_end_date date;
909 --
910 l_on_final_record varchar2(1) := 'N';
911 -- Cursor to select all the entries which are available for delete
912 -- This will lock these records and determine which entry values are
913 -- Going to be deleted.
914 CURSOR get_element_entries(
915 p_element_link_id number,
916 p_val_start_date date,
917 p_val_end_date date) is
918 select ee.element_entry_id element_entry_id,
919 ee.effective_start_date start_date,
920 ee.effective_end_date end_date
921 from pay_element_entries_f ee
922 where p_element_link_id = ee.element_link_id
923 and p_val_start_date <= ee.effective_end_date
924 and p_val_end_date >= ee.effective_start_date
925 for update;
926 --
927 begin
928 --
929 hr_utility.set_location('hr_element_links.del_3p_element_link', 1);
930 --
931 for entry_rec in get_element_entries(
932 p_element_link_id,
933 p_val_start_date,
934 p_val_end_date) loop
935 --
936 hr_element_links.delete_entry_values(
937 entry_rec.element_entry_id,
938 p_delete_mode,
939 p_val_session_date,
940 entry_rec.start_date,
941 entry_rec.end_date);
942 end loop;
943 --
944 --
945 if p_delete_mode = 'ZAP' then
946 --
947 delete from pay_link_input_values_f
948 where element_link_id = p_element_link_id;
949 --
950 delete from pay_element_entries_f
951 where element_link_id = p_element_link_id;
952 --
953 delete from pay_assignment_link_usages_f
954 where element_link_id = p_element_link_id;
955 --
956 elsif p_delete_mode = 'DELETE' then
957 --
958 --
959 hr_utility.set_location('hr_element_links.del_3p_element_link', 2);
960 --
961 -- delete all future records
962 delete from pay_link_input_values_f
963 where element_link_id = p_element_link_id
964 and effective_start_date > p_val_session_date;
965 --
966 -- update current records so that the end date is the session date
967 update pay_link_input_values_f
968 set effective_end_date = p_val_session_date
969 where element_link_id = p_element_link_id
970 and p_val_session_date between
971 effective_start_date and effective_end_date;
972 --
973 --
974 hr_utility.set_location('hr_element_links.del_3p_element_link', 3);
975 --
976 --
977 -- delete all future records
978 delete from pay_element_entries_f
979 where element_link_id = p_element_link_id
980 and effective_start_date > p_val_session_date;
981 --
982 -- update current records so that the end date is the session date
983 update pay_element_entries_f
984 set effective_end_date = p_val_session_date
985 where element_link_id = p_element_link_id
986 and p_val_session_date between
987 effective_start_date and effective_end_date;
988 --
989 hr_utility.set_location('hr_element_links.del_3p_element_link', 4);
990 --
991 -- delete all future records
992 delete from pay_assignment_link_usages_f
993 where element_link_id = p_element_link_id
994 and effective_start_date > p_val_session_date;
995 --
996 -- update current records so that the end date is the session date
997 update pay_assignment_link_usages_f
998 set effective_end_date = p_val_session_date
999 where element_link_id = p_element_link_id
1000 and p_val_session_date between
1001 effective_start_date and effective_end_date;
1002 --
1003 -- DELETE_NEXT_CHANGE will only affect the input value records if we are on
1004 -- The final record of the element link. In this case the final input value
1005 -- records will need to be extended to the end of time.
1006 -- Element entries will not be 'opened up' on delete next change
1007 --
1008 elsif p_delete_mode = 'DELETE_NEXT_CHANGE' then
1009 --
1010 --
1011 hr_utility.set_location('hr_element_links.del_3p_element_link', 5);
1012 --
1013 begin
1014 --
1015 select 'Y'
1016 into l_on_final_record
1017 from pay_element_links_f et1
1018 where p_element_link_id = et1.element_link_id
1019 and p_val_session_date between
1020 et1.effective_start_date and et1.effective_end_date
1021 and et1.effective_end_date =
1022 (select max(et2.effective_end_date)
1023 from pay_element_links_f et2
1024 where p_element_link_id = et2.element_link_id);
1025 --
1026 exception
1027 when NO_DATA_FOUND then NULL;
1028 end;
1029 --
1030 if l_on_final_record = 'Y' then
1031 --
1032 --
1033 hr_utility.set_location('hr_element_links.del_3p_element_link', 5);
1034 --
1035 update pay_link_input_values_f iv1
1036 set iv1.effective_end_date = p_val_end_date
1037 where p_element_link_id = iv1.element_link_id
1038 and iv1.effective_end_date =
1039 (select max(iv2.effective_end_date)
1040 from pay_link_input_values_f iv2
1041 where iv2.link_input_value_id = iv1.link_input_value_Id);
1042 --
1043 end if;
1044 --
1045 -- If we are to do 'NEXT_CHANGE_DELETE' on the ALUs we need to delete all ALUs
1046 -- and re_insert them.
1047 --
1048 delete from pay_assignment_link_usages_f
1049 where element_link_id = p_element_link_id;
1050 --
1051 select min(effective_start_date), greatest(max(effective_end_date),p_val_end_date)
1052 into v_alu_start_date, v_alu_end_date
1053 from pay_element_links_f
1054 where element_link_id = p_element_link_id;
1055 --
1056 --
1057 pay_asg_link_usages_pkg.insert_ALU (
1058 --
1059 p_business_group_id,
1060 p_people_group_id,
1061 p_element_link_id,
1062 v_alu_start_date,
1063 v_alu_end_date);
1064 --
1065 -- No 'FUTURE_CHANGE_DELETE' allowed.
1066 --
1067 end if;
1068 --
1069 end del_3p_element_links;
1070 --
1071 /* TITLE
1072 chk_link_input_values
1073 DESCRIPTION
1074 This procedure checks against the corresponding input value to ensure
1075 that the validation is correct for the link input value. This includes
1076 hot defaulted values.
1077 */
1078 --
1079 PROCEDURE chk_link_input_values(
1080 p_input_value_id in numbeR,
1081 p_legislation_code in varchar2,
1082 p_costable_type in varchar2,
1083 p_costed_flag in varchar2,
1084 p_validation_start_date in datE,
1085 p_validation_end_date in datE,
1086 p_min_value in varchar2,
1087 P_Max_value in varchar2,
1088 p_default_value in varchar2,
1089 p_warning_or_error in varchar2) is
1090 --
1091 L_Validation_check varchar2(1) := 'N';
1092 proc_name COnstant VArchar2(40) := 'chk_link_input_values';
1093 --
1094 -- Cursor to select details of input values.
1095 CURSOR get_input_value(p_input_value number,
1096 p_validation_start_date date,
1097 p_validation_end_date date) iS
1098 select iV.Lookup_type lookup_typE,
1099 iv_tl.name name,
1100 iv.formula_id formula_iD,
1101 iv.default_value default_valuE,
1102 iv.min_value min_valuE,
1103 iv.max_value max_valuE,
1104 iv.hot_default_flag hot_default_flag
1105 from pay_input_values_f_tl iv_tl,
1106 pay_input_values_f iv
1107 where iv.input_value_id = iv_tl.input_value_id
1108 and iv.input_value_id = p_input_value_id
1109 and userenv('LANG') = iv_tl.language
1110 and iv.effective_start_date <= p_validation_end_date
1111 and iv.effective_end_date >= p_validation_start_date;
1112 begiN
1113 --
1114 hr_utility.set_location(proc_name, 1);
1115 hr_utility.trace(to_char(p_validation_start_date));
1116 hr_utility.trace(to_char(p_validation_end_date));
1117 --
1118 -- First we need to get some details about the input value.
1119 --
1120 FOR iv_rec in get_input_value(p_input_value_id,
1121 p_validation_start_date,
1122 p_validation_end_date) loop
1123 -- We have retrieved a date-tracked record from the input values
1124 -- now we need to check it for validity.
1125 -- First, if formula or lookuP vaLIDation is specified then there can be
1126 -- no MAX or min
1127 if ((iV_rec.formula_iD is not null) or
1128 (iv_rec.lookup_typE Is not null)) and
1129 ((P_max_value is not NULL) or
1130 (p_min_value is not NULL)) THEN
1131 --
1132 HR_utility.sET_message(801, 'PAY_6170_INPVAL_VAL_COMB');
1133 hr_utility.raise_error;
1134 --
1135 end if;
1136 --
1137 hr_utility.set_location(proc_name, 2);
1138 -- if there is a default specified for a lookup validated input value
1139 -- then we must check to see if it is valid.
1140 if (iv_rec.lookup_type is not null) and
1141 (p_default_value is not null) then
1142 --
1143 begin
1144 --
1145 select 'Y' into
1146 l_validation_check
1147 from sys.dual
1148 where exists
1149 (select 1
1150 from hr_lookups
1151 where lookup_type = iv_rec.lookup_type
1152 and lookup_code = p_default_value);
1153 --
1154 exception
1155 when NO_DATA_FOUND then null;
1156 end;
1157 --
1158 if L_Validation_check = 'N' then
1159 --
1160 HR_utility.set_message(801, 'PAY_6171_INPVAL_NO_LOOKUP');
1161 hr_utility.raise_error;
1162 --
1163 end if;
1164 --
1165 end if;
1166 --
1167 hr_utility.set_location(proc_name, 3);
1168 -- If the hot default flag is yes we need to check for the max, min and
1169 -- default values being less than 59 characters. This is to allow for
1170 -- quotes being put round them at the lower level
1171 if (iv_rec.hot_default_flag = 'Y') and
1172 ((length(p_default_value) > 58) or
1173 (length(p_min_value) > 58) or
1174 (length(p_max_value) > 58)) then
1175 --
1176 hr_utility.set_message(801,'PAY_6616_INPVAL_HOT_LESS_58');
1177 hr_utility.raise_error;
1178 --
1179 end if;
1180 --
1181 -- If the costable type is 'D' then only the pay value can be
1182 -- be costed
1183 if p_costable_type = 'D' and
1184 iv_rec.name <> hr_input_values.get_pay_value_name
1185 (p_legislation_code) and
1186 p_costed_flag = 'Y' then
1187 --
1188 HR_utility.set_message(801, 'PAY_6404_INPVAL_NO_COST_LINK');
1189 hr_utility.raise_error;
1190 --
1191 end if;
1192 --
1193 end loop;
1194 --
1195 -- if either max or min is entered then there must be a warning or error
1196 -- flag
1197 if ((p_min_value is not null) or
1198 (p_max_value is not null)) and
1199 (p_warning_or_error is null) then
1200 --
1201 HR_utility.set_message(801, 'PAY_6170_INPVAL_VAL_COMB');
1202 hr_utility.raise_error;
1203 --
1204 end if;
1205 --
1206 end chk_link_input_values;
1207 --
1208 procedure LINK_FLAG_UPDATED (p_link_id number) is
1209 --
1210 v_dummy number(1);
1211 --
1212 cursor csr_element_entries is
1213 select 1
1214 from pay_element_entries_f
1215 where element_link_id = p_link_id;
1216 --
1217 begin
1218 --
1219 open csr_element_entries;
1220 fetch csr_element_entries into v_dummy;
1221 if csr_element_entries%found then
1222 close csr_element_entries;
1223 hr_utility.set_message (801,'HR_7089_ELEMENTS_ENTRIES_EXIST');
1224 hr_utility.raise_error;
1225 end if;
1226 close csr_element_entries;
1227 --
1228 end link_flag_updated;
1229 --
1230 end hr_element_links;
[Home] [Help]